5y7txfT7

1DROP DATABASE `libraryDB` IF EXISTS;
2CREATE DATABASE `libraryDB`;
3USE `libraryDB`;
4
5DROP TABLE IF EXISTS `AUTHOR`;
6CREATE TABLE `AUTHOR` (
7  `aid` int(11) NOT NULL AUTO_INCREMENT,
8  `name` varchar(45) NOT NULL,
9  `email` varchar(45) NOT NULL,
10  `phone_no` varchar(15) NOT NULL,
11  `spec_id` int(11) NOT NULL,
12  PRIMARY KEY (`aid`),
13  UNIQUE KEY `aid_UNIQUE` (`aid`),
14  UNIQUE KEY `email_UNIQUE` (`email`),
15  UNIQUE KEY `phone_no_UNIQUE` (`phone_no`),
16  KEY `fk_AUTHOR_spec_id_idx` (`spec_id`),
17  CONSTRAINT `fk_AUTHOR_spec_id` FOREIGN KEY (`spec_id`) REFERENCES `A_SPECIALIZATION` (`spec_id`) ON DELETE CASCADE ON UPDATE CASCADE
18) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
19
20DROP TABLE IF EXISTS `A_SPECIALIZATION`;
21CREATE TABLE `A_SPECIALIZATION` (
22  `spec_id` int(11) NOT NULL AUTO_INCREMENT,
23  `name` varchar(45) NOT NULL,
24  PRIMARY KEY (`spec_id`),
25  UNIQUE KEY `spec_id_UNIQUE` (`spec_id`),
26  UNIQUE KEY `name_UNIQUE` (`name`)
27) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
28
29DROP TABLE IF EXISTS `BOOKS`;
30CREATE TABLE `BOOKS` (
31  `bid` int(11) NOT NULL AUTO_INCREMENT,
32  `name` varchar(45) NOT NULL,
33  `price` float NOT NULL,
34  `lid` int(11) NOT NULL,
35  PRIMARY KEY (`bid`),
36  UNIQUE KEY `bid_UNIQUE` (`bid`),
37  KEY `fk_BOOKS_lid_idx` (`lid`),
38  CONSTRAINT `fk_BOOKS_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE
39) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
40
41DROP TABLE IF EXISTS `DEPARTMENT`;
42CREATE TABLE `DEPARTMENT` (
43  `deptid` int(11) NOT NULL AUTO_INCREMENT,
44  `name` varchar(45) NOT NULL,
45  `I_name` varchar(45) NOT NULL,
46  `lid` int(11) NOT NULL,
47  PRIMARY KEY (`deptid`),
48  UNIQUE KEY `deptid_UNIQUE` (`deptid`),
49  KEY `fk_DEPARTMENT_lid_idx` (`lid`),
50  CONSTRAINT `fk_DEPARTMENT_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE
51) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
52
53DROP TABLE IF EXISTS `EMPLOYEE`;
54CREATE TABLE `EMPLOYEE` (
55  `eid` int(11) NOT NULL AUTO_INCREMENT,
56  `name` varchar(45) NOT NULL,
57  `email` varchar(45) NOT NULL,
58  `salary` float NOT NULL,
59  `lid` int(11) NOT NULL,
60  `deptid` int(11) NOT NULL,
61  PRIMARY KEY (`eid`),
62  UNIQUE KEY `eid_UNIQUE` (`eid`),
63  UNIQUE KEY `email_UNIQUE` (`email`),
64  KEY `fk_EMPLOYEE_lid_idx` (`lid`),
65  KEY `fk_EMPLOYEE_deptid_idx` (`deptid`),
66  CONSTRAINT `fk_EMPLOYEE_deptid` FOREIGN KEY (`deptid`) REFERENCES `DEPARTMENT` (`deptid`) ON DELETE CASCADE ON UPDATE CASCADE,
67  CONSTRAINT `fk_EMPLOYEE_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE
68) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
69
70DROP TABLE IF EXISTS `I_LIBRARY`;
71CREATE TABLE `I_LIBRARY` (
72  `lid` int(11) NOT NULL AUTO_INCREMENT,
73  `L_name` varchar(45) NOT NULL,
74  `I_name` varchar(45) NOT NULL,
75  `city` varchar(45) NOT NULL,
76  `area` varchar(45) NOT NULL,
77  `slid` int(11) NOT NULL,
78  PRIMARY KEY (`lid`),
79  UNIQUE KEY `lid_UNIQUE` (`lid`),
80  KEY `fk_I_LIBRARY_slid_idx` (`slid`),
81  CONSTRAINT `fk_I_LIBRARY_slid` FOREIGN KEY (`slid`) REFERENCES `SIU_LIBRARY` (`slid`) ON DELETE CASCADE ON UPDATE CASCADE
82) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
83
84DROP TABLE IF EXISTS `MEMBER`;
85CREATE TABLE `MEMBER` (
86  `memid` int(11) NOT NULL AUTO_INCREMENT,
87  `lid` int(11) NOT NULL,
88  PRIMARY KEY (`memid`),
89  UNIQUE KEY `memid_UNIQUE` (`memid`),
90  KEY `fk_MEMBER_lid_idx` (`lid`),
91  CONSTRAINT `fk_MEMBER_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE
92) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
93
94DROP TABLE IF EXISTS `NO_OF_COPIES`;
95CREATE TABLE `NO_OF_COPIES` (
96  `bid` int(11) NOT NULL,
97  `lid` int(11) NOT NULL,
98  `copies` int(11) NOT NULL,
99  KEY `fk_NO_OF_COPIES_bid_idx` (`bid`),
100  KEY `fk_NO_OF_COPIES_lid_idx` (`lid`),
101  CONSTRAINT `fk_NO_OF_COPIES_bid` FOREIGN KEY (`bid`) REFERENCES `BOOKS` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE,
102  CONSTRAINT `fk_NO_OF_COPIES_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE
103) ENGINE=InnoDB DEFAULT CHARSET=latin1;
104
105DROP TABLE IF EXISTS `PUBLISHER`;
106CREATE TABLE `PUBLISHER` (
107  `pid` int(11) NOT NULL AUTO_INCREMENT,
108  `name` varchar(45) NOT NULL,
109  PRIMARY KEY (`pid`),
110  UNIQUE KEY `pid_UNIQUE` (`pid`),
111  UNIQUE KEY `name_UNIQUE` (`name`)
112) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
113
114DROP TABLE IF EXISTS `SELLER`;
115CREATE TABLE `SELLER` (
116  `sid` int(11) NOT NULL AUTO_INCREMENT,
117  `name` varchar(45) NOT NULL,
118  `city` varchar(45) NOT NULL,
119  PRIMARY KEY (`sid`),
120  UNIQUE KEY `sid_UNIQUE` (`sid`)
121) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
122
123DROP TABLE IF EXISTS `SIU_LIBRARY`;
124CREATE TABLE `SIU_LIBRARY` (
125  `slid` int(11) NOT NULL AUTO_INCREMENT,
126  `name` varchar(45) NOT NULL,
127  `location` varchar(45) NOT NULL,
128  `no_of_branches` int(11) NOT NULL,
129  PRIMARY KEY (`slid`),
130  UNIQUE KEY `slid_UNIQUE` (`slid`)
131) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
132
133DROP TABLE IF EXISTS `STAFF`;
134CREATE TABLE `STAFF` (
135  `stid` int(11) NOT NULL AUTO_INCREMENT,
136  `name` varchar(45) NOT NULL,
137  `email` varchar(45) NOT NULL,
138  `memid` int(11) NOT NULL,
139  `deptid` int(11) NOT NULL,
140  `salary` float NOT NULL,
141  PRIMARY KEY (`stid`),
142  UNIQUE KEY `stid_UNIQUE` (`stid`),
143  UNIQUE KEY `email_UNIQUE` (`email`),
144  KEY `fk_STAFF_memid_idx` (`memid`),
145  KEY `fk_STAFF_deptid_idx` (`deptid`),
146  CONSTRAINT `fk_STAFF_deptid` FOREIGN KEY (`deptid`) REFERENCES `DEPARTMENT` (`deptid`) ON DELETE CASCADE ON UPDATE CASCADE,
147  CONSTRAINT `fk_STAFF_memid` FOREIGN KEY (`memid`) REFERENCES `MEMBER` (`memid`) ON DELETE CASCADE ON UPDATE CASCADE
148) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
149
150DROP TABLE IF EXISTS `STUDENT`;
151CREATE TABLE `STUDENT` (
152  `stuid` int(11) NOT NULL AUTO_INCREMENT,
153  `name` varchar(45) NOT NULL,
154  `email` varchar(45) NOT NULL,
155  `memid` int(11) NOT NULL,
156  `deptid` int(11) NOT NULL,
157  PRIMARY KEY (`stuid`),
158  UNIQUE KEY `stuid_UNIQUE` (`stuid`),
159  UNIQUE KEY `email_UNIQUE` (`email`),
160  KEY `fk_STUDENT_memid_idx` (`memid`),
161  KEY `fk_STUDENT_deptid_idx` (`deptid`),
162  CONSTRAINT `fk_STUDENT_deptid` FOREIGN KEY (`deptid`) REFERENCES `DEPARTMENT` (`deptid`) ON DELETE CASCADE ON UPDATE CASCADE,
163  CONSTRAINT `fk_STUDENT_memid` FOREIGN KEY (`memid`) REFERENCES `MEMBER` (`memid`) ON DELETE CASCADE ON UPDATE CASCADE
164) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
165
166DROP TABLE IF EXISTS `issue`;
167CREATE TABLE `issue` (
168  `issue_id` int(11) NOT NULL AUTO_INCREMENT,
169  `memid` int(11) NOT NULL,
170  `bid` int(11) NOT NULL,
171  `lid` int(11) NOT NULL,
172  `issue_date` date NOT NULL,
173  `expected_return_date` date NOT NULL,
174  `actual_return_date` date NOT NULL,
175  PRIMARY KEY (`issue_id`),
176  UNIQUE KEY `issue_id_UNIQUE` (`issue_id`),
177  KEY `fk_issue_memid_idx` (`memid`),
178  KEY `fk_issue_bid_idx` (`bid`),
179  KEY `fk_issue_lid_idx` (`lid`),
180  CONSTRAINT `fk_issue_bid` FOREIGN KEY (`bid`) REFERENCES `BOOKS` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE,
181  CONSTRAINT `fk_issue_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE,
182  CONSTRAINT `fk_issue_memid` FOREIGN KEY (`memid`) REFERENCES `MEMBER` (`memid`) ON DELETE CASCADE ON UPDATE CASCADE
183) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
184
185DROP TABLE IF EXISTS `purchase`;
186CREATE TABLE `purchase` (
187  `prid` int(11) NOT NULL AUTO_INCREMENT,
188  `lid` int(11) NOT NULL,
189  `sid` int(11) NOT NULL,
190  `pid` int(11) NOT NULL,
191  `bid` int(11) NOT NULL,
192  `quantity` int(11) NOT NULL,
193  `date` date NOT NULL,
194  `total_cost` float NOT NULL,
195  PRIMARY KEY (`prid`),
196  UNIQUE KEY `prid_UNIQUE` (`prid`),
197  KEY `fk_purchase_lid_idx` (`lid`),
198  KEY `fk_purchase_sid_idx` (`sid`),
199  KEY `fk_purchase_pid_idx` (`pid`),
200  KEY `fk_purchase_bid_idx` (`bid`),
201  CONSTRAINT `fk_purchase_bid` FOREIGN KEY (`bid`) REFERENCES `BOOKS` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE,
202  CONSTRAINT `fk_purchase_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE,
203  CONSTRAINT `fk_purchase_pid` FOREIGN KEY (`pid`) REFERENCES `PUBLISHER` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE,
204  CONSTRAINT `fk_purchase_sid` FOREIGN KEY (`sid`) REFERENCES `SELLER` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE
205) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
206
207DROP TABLE IF EXISTS `sells`;
208CREATE TABLE `sells` (
209  `sid` int(11) NOT NULL,
210  `bid` int(11) NOT NULL,
211  `pid` int(11) NOT NULL,
212  KEY `fk_sells_sid_idx` (`sid`),
213  KEY `fk_sells_bid_idx` (`bid`),
214  KEY `fk_sells_pid_idx` (`pid`),
215  CONSTRAINT `fk_sells_bid` FOREIGN KEY (`bid`) REFERENCES `BOOKS` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE,
216  CONSTRAINT `fk_sells_pid` FOREIGN KEY (`pid`) REFERENCES `PUBLISHER` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE,
217  CONSTRAINT `fk_sells_sid` FOREIGN KEY (`sid`) REFERENCES `SELLER` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE
218) ENGINE=InnoDB DEFAULT CHARSET=latin1;
219
220DROP TABLE IF EXISTS `writes`;
221CREATE TABLE `writes` (
222  `bid` int(11) NOT NULL,
223  `aid` int(11) NOT NULL,
224  `pid` int(11) NOT NULL,
225  KEY `fk_writes_bid_idx` (`bid`),
226  KEY `fk_writes_aid_idx` (`aid`),
227  KEY `fk_writes_pid_idx` (`pid`),
228  CONSTRAINT `fk_writes_aid` FOREIGN KEY (`aid`) REFERENCES `AUTHOR` (`aid`) ON DELETE CASCADE ON UPDATE CASCADE,
229  CONSTRAINT `fk_writes_bid` FOREIGN KEY (`bid`) REFERENCES `BOOKS` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE,
230  CONSTRAINT `fk_writes_pid` FOREIGN KEY (`pid`) REFERENCES `PUBLISHER` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE
231) ENGINE=InnoDB DEFAULT CHARSET=latin1;