· 7 years ago · Oct 01, 2018, 04:34 AM
1-- MySQL dump 10.13 Distrib 5.7.23, for Linux (x86_64)
2--
3-- Host: localhost Database: libraryDB
4-- ------------------------------------------------------
5-- Server version 5.7.23-0ubuntu0.18.04.1
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10/*!40101 SET NAMES utf8 */;
11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12/*!40103 SET TIME_ZONE='+00:00' */;
13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18--
19-- Table structure for table `AUTHOR`
20--
21
22DROP TABLE IF EXISTS `AUTHOR`;
23/*!40101 SET @saved_cs_client = @@character_set_client */;
24/*!40101 SET character_set_client = utf8 */;
25CREATE TABLE `AUTHOR` (
26 `aid` int(11) NOT NULL AUTO_INCREMENT,
27 `name` varchar(45) NOT NULL,
28 `email` varchar(45) NOT NULL,
29 `phone_no` varchar(15) NOT NULL,
30 `spec_id` int(11) NOT NULL,
31 PRIMARY KEY (`aid`),
32 UNIQUE KEY `aid_UNIQUE` (`aid`),
33 UNIQUE KEY `email_UNIQUE` (`email`),
34 UNIQUE KEY `phone_no_UNIQUE` (`phone_no`),
35 KEY `fk_AUTHOR_spec_id_idx` (`spec_id`),
36 CONSTRAINT `fk_AUTHOR_spec_id` FOREIGN KEY (`spec_id`) REFERENCES `A_SPECIALIZATION` (`spec_id`) ON DELETE CASCADE ON UPDATE CASCADE
37) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
38/*!40101 SET character_set_client = @saved_cs_client */;
39
40--
41-- Dumping data for table `AUTHOR`
42--
43
44LOCK TABLES `AUTHOR` WRITE;
45/*!40000 ALTER TABLE `AUTHOR` DISABLE KEYS */;
46INSERT INTO `AUTHOR` VALUES (1,'Ken Coel','ken.coel@speedmail.com','1212121212',1),(2,'Ted Mosby','ted.mosby@himym.com','2323232323',2),(3,'Elijah Michaelson','elijah.michaelson@to.com','3434343434',3),(4,'Klaus Michaelson','klaus.michaelson@to.com','4545454545',4),(5,'Ian Somerhalder','ian.somerhalder@vwars.com','5656565656',5),(6,'Shruti Maheshwari','shruti.maheshwari@sit.com','6767676767',1),(7,'Shivam Kapoor','shivam.kapoor@gmail.com','7878787878',3);
47/*!40000 ALTER TABLE `AUTHOR` ENABLE KEYS */;
48UNLOCK TABLES;
49
50--
51-- Table structure for table `A_SPECIALIZATION`
52--
53
54DROP TABLE IF EXISTS `A_SPECIALIZATION`;
55/*!40101 SET @saved_cs_client = @@character_set_client */;
56/*!40101 SET character_set_client = utf8 */;
57CREATE TABLE `A_SPECIALIZATION` (
58 `spec_id` int(11) NOT NULL AUTO_INCREMENT,
59 `name` varchar(45) NOT NULL,
60 PRIMARY KEY (`spec_id`),
61 UNIQUE KEY `spec_id_UNIQUE` (`spec_id`),
62 UNIQUE KEY `name_UNIQUE` (`name`)
63) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
64/*!40101 SET character_set_client = @saved_cs_client */;
65
66--
67-- Dumping data for table `A_SPECIALIZATION`
68--
69
70LOCK TABLES `A_SPECIALIZATION` WRITE;
71/*!40000 ALTER TABLE `A_SPECIALIZATION` DISABLE KEYS */;
72INSERT INTO `A_SPECIALIZATION` VALUES (3,'Bakery'),(2,'Biochemistry'),(1,'Computer Science'),(4,'Criminal Law'),(5,'Management');
73/*!40000 ALTER TABLE `A_SPECIALIZATION` ENABLE KEYS */;
74UNLOCK TABLES;
75
76--
77-- Table structure for table `BOOKS`
78--
79
80DROP TABLE IF EXISTS `BOOKS`;
81/*!40101 SET @saved_cs_client = @@character_set_client */;
82/*!40101 SET character_set_client = utf8 */;
83CREATE TABLE `BOOKS` (
84 `bid` int(11) NOT NULL AUTO_INCREMENT,
85 `name` varchar(45) NOT NULL,
86 `price` float NOT NULL,
87 `lid` int(11) NOT NULL,
88 PRIMARY KEY (`bid`),
89 UNIQUE KEY `bid_UNIQUE` (`bid`),
90 KEY `fk_BOOKS_lid_idx` (`lid`),
91 CONSTRAINT `fk_BOOKS_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE
92) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
93/*!40101 SET character_set_client = @saved_cs_client */;
94
95--
96-- Dumping data for table `BOOKS`
97--
98
99LOCK TABLES `BOOKS` WRITE;
100/*!40000 ALTER TABLE `BOOKS` DISABLE KEYS */;
101INSERT INTO `BOOKS` VALUES (1,'Theory of Algorithmics',905.612,1),(2,'Humanoid Biochemistry',1099.67,2),(4,'The Indian Penal Code',2264.03,4),(5,'Management Science',840.926,5),(6,'Modern Computer Science',884.05,1),(7,'Transistor Tecnology',927.175,6),(453,'Chef Impress',722.334,3);
102/*!40000 ALTER TABLE `BOOKS` ENABLE KEYS */;
103UNLOCK TABLES;
104
105--
106-- Table structure for table `DEPARTMENT`
107--
108
109DROP TABLE IF EXISTS `DEPARTMENT`;
110/*!40101 SET @saved_cs_client = @@character_set_client */;
111/*!40101 SET character_set_client = utf8 */;
112CREATE TABLE `DEPARTMENT` (
113 `deptid` int(11) NOT NULL AUTO_INCREMENT,
114 `name` varchar(45) NOT NULL,
115 `I_name` varchar(45) NOT NULL,
116 `lid` int(11) NOT NULL,
117 PRIMARY KEY (`deptid`),
118 UNIQUE KEY `deptid_UNIQUE` (`deptid`),
119 KEY `fk_DEPARTMENT_lid_idx` (`lid`),
120 CONSTRAINT `fk_DEPARTMENT_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE
121) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
122/*!40101 SET character_set_client = @saved_cs_client */;
123
124--
125-- Dumping data for table `DEPARTMENT`
126--
127
128LOCK TABLES `DEPARTMENT` WRITE;
129/*!40000 ALTER TABLE `DEPARTMENT` DISABLE KEYS */;
130INSERT INTO `DEPARTMENT` VALUES (1,'Computer Science','Symbiosis Institute of Technology',1),(2,'Biochemistry','Symbiosis School of Biomedical Sciences',2),(3,'Bakery','Symbiosis School of Culinary Arts',3),(4,'Law','Symbiosis School of Law & Administration',4),(5,'Management','Symbiosis Institute of Business Management',5),(6,'Civil','Symbiosis Institute of Technology',1),(7,'E&TC','Symbiosis Institute of Technology Management',6),(8,'Biology','SSBS',2);
131/*!40000 ALTER TABLE `DEPARTMENT` ENABLE KEYS */;
132UNLOCK TABLES;
133
134--
135-- Table structure for table `EMPLOYEE`
136--
137
138DROP TABLE IF EXISTS `EMPLOYEE`;
139/*!40101 SET @saved_cs_client = @@character_set_client */;
140/*!40101 SET character_set_client = utf8 */;
141CREATE TABLE `EMPLOYEE` (
142 `eid` int(11) NOT NULL AUTO_INCREMENT,
143 `name` varchar(45) NOT NULL,
144 `email` varchar(45) NOT NULL,
145 `salary` float NOT NULL,
146 `lid` int(11) NOT NULL,
147 `deptid` int(11) NOT NULL,
148 PRIMARY KEY (`eid`),
149 UNIQUE KEY `eid_UNIQUE` (`eid`),
150 UNIQUE KEY `email_UNIQUE` (`email`),
151 KEY `fk_EMPLOYEE_lid_idx` (`lid`),
152 KEY `fk_EMPLOYEE_deptid_idx` (`deptid`),
153 CONSTRAINT `fk_EMPLOYEE_deptid` FOREIGN KEY (`deptid`) REFERENCES `DEPARTMENT` (`deptid`) ON DELETE CASCADE ON UPDATE CASCADE,
154 CONSTRAINT `fk_EMPLOYEE_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE
155) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
156/*!40101 SET character_set_client = @saved_cs_client */;
157
158--
159-- Dumping data for table `EMPLOYEE`
160--
161
162LOCK TABLES `EMPLOYEE` WRITE;
163/*!40000 ALTER TABLE `EMPLOYEE` DISABLE KEYS */;
164INSERT INTO `EMPLOYEE` VALUES (1,'Chandler Bing','chandler.bing@tf.com',10000,1,1),(2,'Harry Potter','harry.potter@hp.com',200000,2,2),(3,'Ron Weasley','ron.weasley@hp.com',50000,3,3),(4,'Hermione Granger','hermione.granger@hp.com',250000,4,4),(5,'Draco Malfoy','draco.malfoy@hp.com',70000,5,5);
165/*!40000 ALTER TABLE `EMPLOYEE` ENABLE KEYS */;
166UNLOCK TABLES;
167
168--
169-- Table structure for table `I_LIBRARY`
170--
171
172DROP TABLE IF EXISTS `I_LIBRARY`;
173/*!40101 SET @saved_cs_client = @@character_set_client */;
174/*!40101 SET character_set_client = utf8 */;
175CREATE TABLE `I_LIBRARY` (
176 `lid` int(11) NOT NULL AUTO_INCREMENT,
177 `L_name` varchar(45) NOT NULL,
178 `I_name` varchar(45) NOT NULL,
179 `city` varchar(45) NOT NULL,
180 `area` varchar(45) NOT NULL,
181 `slid` int(11) NOT NULL,
182 PRIMARY KEY (`lid`),
183 UNIQUE KEY `lid_UNIQUE` (`lid`),
184 KEY `fk_I_LIBRARY_slid_idx` (`slid`),
185 CONSTRAINT `fk_I_LIBRARY_slid` FOREIGN KEY (`slid`) REFERENCES `SIU_LIBRARY` (`slid`) ON DELETE CASCADE ON UPDATE CASCADE
186) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;
187/*!40101 SET character_set_client = @saved_cs_client */;
188
189--
190-- Dumping data for table `I_LIBRARY`
191--
192
193LOCK TABLES `I_LIBRARY` WRITE;
194/*!40000 ALTER TABLE `I_LIBRARY` DISABLE KEYS */;
195INSERT INTO `I_LIBRARY` VALUES (1,'SIT Library','Symbiosis Institute of Technology','Pune','Lavale',1),(2,'SSBS Library','Symbiosis School of Biomedical Sciences','Nashik','Aundh',2),(3,'SSCA Library','Symbiosis School of Culinary Arts','Bangalore','Hinjewadi',3),(4,'SSLA Library','Symbiosis School of Law & Administration','Bhubaneshwar','Koregaon Park',4),(5,'SIBM Library','Symbiosis Institute of Business Management','Agartala','Paud',5),(6,'SITM Library','Symbiosis Institute of Technology Management','Pune','Lavale',1),(7,'SCHC Library','Symbiosis Centre for Health Care','Pune','Lavale',1),(8,'SIOM Library','SIOM','Nashik','Aundh',2);
196/*!40000 ALTER TABLE `I_LIBRARY` ENABLE KEYS */;
197UNLOCK TABLES;
198
199--
200-- Table structure for table `MEMBER`
201--
202
203DROP TABLE IF EXISTS `MEMBER`;
204/*!40101 SET @saved_cs_client = @@character_set_client */;
205/*!40101 SET character_set_client = utf8 */;
206CREATE TABLE `MEMBER` (
207 `memid` int(11) NOT NULL AUTO_INCREMENT,
208 `lid` int(11) NOT NULL,
209 PRIMARY KEY (`memid`),
210 UNIQUE KEY `memid_UNIQUE` (`memid`),
211 KEY `fk_MEMBER_lid_idx` (`lid`),
212 CONSTRAINT `fk_MEMBER_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE
213) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1;
214/*!40101 SET character_set_client = @saved_cs_client */;
215
216--
217-- Dumping data for table `MEMBER`
218--
219
220LOCK TABLES `MEMBER` WRITE;
221/*!40000 ALTER TABLE `MEMBER` DISABLE KEYS */;
222INSERT INTO `MEMBER` VALUES (1,1),(6,1),(11,1),(2,2),(7,2),(3,3),(8,3),(4,4),(9,4),(5,5),(10,5),(12,6),(13,6);
223/*!40000 ALTER TABLE `MEMBER` ENABLE KEYS */;
224UNLOCK TABLES;
225
226--
227-- Table structure for table `NO_OF_COPIES`
228--
229
230DROP TABLE IF EXISTS `NO_OF_COPIES`;
231/*!40101 SET @saved_cs_client = @@character_set_client */;
232/*!40101 SET character_set_client = utf8 */;
233CREATE TABLE `NO_OF_COPIES` (
234 `bid` int(11) NOT NULL,
235 `lid` int(11) NOT NULL,
236 `copies` int(11) NOT NULL,
237 KEY `fk_NO_OF_COPIES_bid_idx` (`bid`),
238 KEY `fk_NO_OF_COPIES_lid_idx` (`lid`),
239 CONSTRAINT `fk_NO_OF_COPIES_bid` FOREIGN KEY (`bid`) REFERENCES `BOOKS` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE,
240 CONSTRAINT `fk_NO_OF_COPIES_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE
241) ENGINE=InnoDB DEFAULT CHARSET=latin1;
242/*!40101 SET character_set_client = @saved_cs_client */;
243
244--
245-- Dumping data for table `NO_OF_COPIES`
246--
247
248LOCK TABLES `NO_OF_COPIES` WRITE;
249/*!40000 ALTER TABLE `NO_OF_COPIES` DISABLE KEYS */;
250INSERT INTO `NO_OF_COPIES` VALUES (1,1,3),(2,2,4),(453,3,5),(4,4,6),(5,5,7);
251/*!40000 ALTER TABLE `NO_OF_COPIES` ENABLE KEYS */;
252UNLOCK TABLES;
253
254--
255-- Table structure for table `PUBLISHER`
256--
257
258DROP TABLE IF EXISTS `PUBLISHER`;
259/*!40101 SET @saved_cs_client = @@character_set_client */;
260/*!40101 SET character_set_client = utf8 */;
261CREATE TABLE `PUBLISHER` (
262 `pid` int(11) NOT NULL AUTO_INCREMENT,
263 `name` varchar(45) NOT NULL,
264 PRIMARY KEY (`pid`),
265 UNIQUE KEY `pid_UNIQUE` (`pid`),
266 UNIQUE KEY `name_UNIQUE` (`name`)
267) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
268/*!40101 SET character_set_client = @saved_cs_client */;
269
270--
271-- Dumping data for table `PUBLISHER`
272--
273
274LOCK TABLES `PUBLISHER` WRITE;
275/*!40000 ALTER TABLE `PUBLISHER` DISABLE KEYS */;
276INSERT INTO `PUBLISHER` VALUES (5,'Hallmark'),(3,'Nirali Prakashan'),(2,'Pearson Education'),(1,'Tata McGraw Hill'),(4,'Technical Publications'),(6,'Wiley Publications');
277/*!40000 ALTER TABLE `PUBLISHER` ENABLE KEYS */;
278UNLOCK TABLES;
279
280--
281-- Table structure for table `SELLER`
282--
283
284DROP TABLE IF EXISTS `SELLER`;
285/*!40101 SET @saved_cs_client = @@character_set_client */;
286/*!40101 SET character_set_client = utf8 */;
287CREATE TABLE `SELLER` (
288 `sid` int(11) NOT NULL AUTO_INCREMENT,
289 `name` varchar(45) NOT NULL,
290 `city` varchar(45) NOT NULL,
291 PRIMARY KEY (`sid`),
292 UNIQUE KEY `sid_UNIQUE` (`sid`)
293) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
294/*!40101 SET character_set_client = @saved_cs_client */;
295
296--
297-- Dumping data for table `SELLER`
298--
299
300LOCK TABLES `SELLER` WRITE;
301/*!40000 ALTER TABLE `SELLER` DISABLE KEYS */;
302INSERT INTO `SELLER` VALUES (1,'Elena Gilbert','Pune'),(2,'Hope Marshall','Nashik'),(3,'Jack Soloff','Bangalore'),(4,'Mike Ross','Bhubaneshwar'),(5,'Harvey Spectre','Agartala'),(6,'Pragati Book Store','Pune');
303/*!40000 ALTER TABLE `SELLER` ENABLE KEYS */;
304UNLOCK TABLES;
305
306--
307-- Table structure for table `SIU_LIBRARY`
308--
309
310DROP TABLE IF EXISTS `SIU_LIBRARY`;
311/*!40101 SET @saved_cs_client = @@character_set_client */;
312/*!40101 SET character_set_client = utf8 */;
313CREATE TABLE `SIU_LIBRARY` (
314 `slid` int(11) NOT NULL AUTO_INCREMENT,
315 `name` varchar(45) NOT NULL,
316 `location` varchar(45) NOT NULL,
317 `no_of_branches` int(11) NOT NULL,
318 PRIMARY KEY (`slid`),
319 UNIQUE KEY `slid_UNIQUE` (`slid`)
320) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
321/*!40101 SET character_set_client = @saved_cs_client */;
322
323--
324-- Dumping data for table `SIU_LIBRARY`
325--
326
327LOCK TABLES `SIU_LIBRARY` WRITE;
328/*!40000 ALTER TABLE `SIU_LIBRARY` DISABLE KEYS */;
329INSERT INTO `SIU_LIBRARY` VALUES (1,'SIU Pune','Pune',2),(2,'SIU Nashik','Nashik',1),(3,'SIU Bangalore','Bangalore',3),(4,'SIU Bhubaneshwar','Bhubaneshwar',1),(5,'SIU Agartala','Agartala',1);
330/*!40000 ALTER TABLE `SIU_LIBRARY` ENABLE KEYS */;
331UNLOCK TABLES;
332
333--
334-- Table structure for table `STAFF`
335--
336
337DROP TABLE IF EXISTS `STAFF`;
338/*!40101 SET @saved_cs_client = @@character_set_client */;
339/*!40101 SET character_set_client = utf8 */;
340CREATE TABLE `STAFF` (
341 `stid` int(11) NOT NULL AUTO_INCREMENT,
342 `name` varchar(45) NOT NULL,
343 `email` varchar(45) NOT NULL,
344 `memid` int(11) NOT NULL,
345 `deptid` int(11) NOT NULL,
346 `salary` float NOT NULL,
347 PRIMARY KEY (`stid`),
348 UNIQUE KEY `stid_UNIQUE` (`stid`),
349 UNIQUE KEY `email_UNIQUE` (`email`),
350 KEY `fk_STAFF_memid_idx` (`memid`),
351 KEY `fk_STAFF_deptid_idx` (`deptid`),
352 CONSTRAINT `fk_STAFF_deptid` FOREIGN KEY (`deptid`) REFERENCES `DEPARTMENT` (`deptid`) ON DELETE CASCADE ON UPDATE CASCADE,
353 CONSTRAINT `fk_STAFF_memid` FOREIGN KEY (`memid`) REFERENCES `MEMBER` (`memid`) ON DELETE CASCADE ON UPDATE CASCADE
354) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
355/*!40101 SET character_set_client = @saved_cs_client */;
356
357--
358-- Dumping data for table `STAFF`
359--
360
361LOCK TABLES `STAFF` WRITE;
362/*!40000 ALTER TABLE `STAFF` DISABLE KEYS */;
363INSERT INTO `STAFF` VALUES (1,'Rachel Green','rachel.green@tf.com',6,1,31250),(2,'Joey Tribbiani','joey.tribbiani@tf.com',7,2,80000),(3,'Monica Geller','monica.geller@tf.com',8,3,90000),(4,'Ross Geller','ross.geller@tf.com',9,4,100000),(5,'Ben Geller','ben.geller@tf.com',10,5,35000),(6,'Sahesh Bhupati','sahesh.bhupati@yahoo.com',13,6,62500);
364/*!40000 ALTER TABLE `STAFF` ENABLE KEYS */;
365UNLOCK TABLES;
366
367--
368-- Table structure for table `STUDENT`
369--
370
371DROP TABLE IF EXISTS `STUDENT`;
372/*!40101 SET @saved_cs_client = @@character_set_client */;
373/*!40101 SET character_set_client = utf8 */;
374CREATE TABLE `STUDENT` (
375 `stuid` int(11) NOT NULL AUTO_INCREMENT,
376 `name` varchar(45) NOT NULL,
377 `email` varchar(45) NOT NULL,
378 `memid` int(11) NOT NULL,
379 `deptid` int(11) NOT NULL,
380 PRIMARY KEY (`stuid`),
381 UNIQUE KEY `stuid_UNIQUE` (`stuid`),
382 UNIQUE KEY `email_UNIQUE` (`email`),
383 KEY `fk_STUDENT_memid_idx` (`memid`),
384 KEY `fk_STUDENT_deptid_idx` (`deptid`),
385 CONSTRAINT `fk_STUDENT_deptid` FOREIGN KEY (`deptid`) REFERENCES `DEPARTMENT` (`deptid`) ON DELETE CASCADE ON UPDATE CASCADE,
386 CONSTRAINT `fk_STUDENT_memid` FOREIGN KEY (`memid`) REFERENCES `MEMBER` (`memid`) ON DELETE CASCADE ON UPDATE CASCADE
387) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
388/*!40101 SET character_set_client = @saved_cs_client */;
389
390--
391-- Dumping data for table `STUDENT`
392--
393
394LOCK TABLES `STUDENT` WRITE;
395/*!40000 ALTER TABLE `STUDENT` DISABLE KEYS */;
396INSERT INTO `STUDENT` VALUES (1,'Shivani Patil','shivani.patil@sit.com',1,1),(2,'Kate Pearson','kate.pearson@tiu.com',2,2),(3,'Kevin Pearson','kevin.pearson@tiu.com',3,3),(4,'Randall Pearson','randall.pearson@tiu.com',4,4),(5,'Rebekah Pearson','rebekah.pearson@tiu.com',5,5),(6,'Donna Paulson','donna.paulson@sts.com',11,6),(7,'Louis Litt','louis.ltt@sts.com',12,7);
397/*!40000 ALTER TABLE `STUDENT` ENABLE KEYS */;
398UNLOCK TABLES;
399
400--
401-- Table structure for table `issue`
402--
403
404DROP TABLE IF EXISTS `issue`;
405/*!40101 SET @saved_cs_client = @@character_set_client */;
406/*!40101 SET character_set_client = utf8 */;
407CREATE TABLE `issue` (
408 `issue_id` int(11) NOT NULL AUTO_INCREMENT,
409 `memid` int(11) NOT NULL,
410 `bid` int(11) NOT NULL,
411 `lid` int(11) NOT NULL,
412 `issue_date` date NOT NULL,
413 `expected_return_date` date NOT NULL,
414 `actual_return_date` date NOT NULL,
415 PRIMARY KEY (`issue_id`),
416 UNIQUE KEY `issue_id_UNIQUE` (`issue_id`),
417 KEY `fk_issue_memid_idx` (`memid`),
418 KEY `fk_issue_bid_idx` (`bid`),
419 KEY `fk_issue_lid_idx` (`lid`),
420 CONSTRAINT `fk_issue_bid` FOREIGN KEY (`bid`) REFERENCES `BOOKS` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE,
421 CONSTRAINT `fk_issue_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE,
422 CONSTRAINT `fk_issue_memid` FOREIGN KEY (`memid`) REFERENCES `MEMBER` (`memid`) ON DELETE CASCADE ON UPDATE CASCADE
423) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;
424/*!40101 SET character_set_client = @saved_cs_client */;
425
426--
427-- Dumping data for table `issue`
428--
429
430LOCK TABLES `issue` WRITE;
431/*!40000 ALTER TABLE `issue` DISABLE KEYS */;
432INSERT INTO `issue` VALUES (1,1,1,1,'2018-01-01','2018-01-16','2018-01-12'),(2,6,2,1,'2018-02-01','2018-02-16','2018-02-20'),(3,2,453,3,'2018-03-01','2018-03-16','2018-03-12'),(4,7,4,2,'2018-04-01','2018-04-16','2018-04-20'),(5,3,5,3,'2018-05-01','2018-05-16','2018-05-12'),(6,12,7,6,'2018-06-01','2018-06-16','2018-06-12'),(7,9,4,4,'2018-06-01','2018-06-20','2018-06-20');
433/*!40000 ALTER TABLE `issue` ENABLE KEYS */;
434UNLOCK TABLES;
435
436--
437-- Table structure for table `purchase`
438--
439
440DROP TABLE IF EXISTS `purchase`;
441/*!40101 SET @saved_cs_client = @@character_set_client */;
442/*!40101 SET character_set_client = utf8 */;
443CREATE TABLE `purchase` (
444 `prid` int(11) NOT NULL AUTO_INCREMENT,
445 `lid` int(11) NOT NULL,
446 `sid` int(11) NOT NULL,
447 `pid` int(11) NOT NULL,
448 `bid` int(11) NOT NULL,
449 `quantity` int(11) NOT NULL,
450 `date` date NOT NULL,
451 `total_cost` float NOT NULL,
452 PRIMARY KEY (`prid`),
453 UNIQUE KEY `prid_UNIQUE` (`prid`),
454 KEY `fk_purchase_lid_idx` (`lid`),
455 KEY `fk_purchase_sid_idx` (`sid`),
456 KEY `fk_purchase_pid_idx` (`pid`),
457 KEY `fk_purchase_bid_idx` (`bid`),
458 CONSTRAINT `fk_purchase_bid` FOREIGN KEY (`bid`) REFERENCES `BOOKS` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE,
459 CONSTRAINT `fk_purchase_lid` FOREIGN KEY (`lid`) REFERENCES `I_LIBRARY` (`lid`) ON DELETE CASCADE ON UPDATE CASCADE,
460 CONSTRAINT `fk_purchase_pid` FOREIGN KEY (`pid`) REFERENCES `PUBLISHER` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE,
461 CONSTRAINT `fk_purchase_sid` FOREIGN KEY (`sid`) REFERENCES `SELLER` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE
462) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
463/*!40101 SET character_set_client = @saved_cs_client */;
464
465--
466-- Dumping data for table `purchase`
467--
468
469LOCK TABLES `purchase` WRITE;
470/*!40000 ALTER TABLE `purchase` DISABLE KEYS */;
471INSERT INTO `purchase` VALUES (1,1,1,1,1,3,'2017-01-01',2520),(2,2,2,2,2,4,'2017-01-01',4080),(3,3,3,3,453,5,'2017-01-01',3350),(4,1,4,4,4,6,'2017-01-01',12600),(5,5,5,5,5,7,'2017-12-01',5460);
472/*!40000 ALTER TABLE `purchase` ENABLE KEYS */;
473UNLOCK TABLES;
474
475--
476-- Table structure for table `sells`
477--
478
479DROP TABLE IF EXISTS `sells`;
480/*!40101 SET @saved_cs_client = @@character_set_client */;
481/*!40101 SET character_set_client = utf8 */;
482CREATE TABLE `sells` (
483 `sid` int(11) NOT NULL,
484 `bid` int(11) NOT NULL,
485 `pid` int(11) NOT NULL,
486 KEY `fk_sells_sid_idx` (`sid`),
487 KEY `fk_sells_bid_idx` (`bid`),
488 KEY `fk_sells_pid_idx` (`pid`),
489 CONSTRAINT `fk_sells_bid` FOREIGN KEY (`bid`) REFERENCES `BOOKS` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE,
490 CONSTRAINT `fk_sells_pid` FOREIGN KEY (`pid`) REFERENCES `PUBLISHER` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE,
491 CONSTRAINT `fk_sells_sid` FOREIGN KEY (`sid`) REFERENCES `SELLER` (`sid`) ON DELETE CASCADE ON UPDATE CASCADE
492) ENGINE=InnoDB DEFAULT CHARSET=latin1;
493/*!40101 SET character_set_client = @saved_cs_client */;
494
495--
496-- Dumping data for table `sells`
497--
498
499LOCK TABLES `sells` WRITE;
500/*!40000 ALTER TABLE `sells` DISABLE KEYS */;
501INSERT INTO `sells` VALUES (1,1,1),(2,2,2),(3,453,3),(4,4,4),(5,5,5),(6,2,2);
502/*!40000 ALTER TABLE `sells` ENABLE KEYS */;
503UNLOCK TABLES;
504
505--
506-- Table structure for table `writes`
507--
508
509DROP TABLE IF EXISTS `writes`;
510/*!40101 SET @saved_cs_client = @@character_set_client */;
511/*!40101 SET character_set_client = utf8 */;
512CREATE TABLE `writes` (
513 `bid` int(11) NOT NULL,
514 `aid` int(11) NOT NULL,
515 `pid` int(11) NOT NULL,
516 KEY `fk_writes_bid_idx` (`bid`),
517 KEY `fk_writes_aid_idx` (`aid`),
518 KEY `fk_writes_pid_idx` (`pid`),
519 CONSTRAINT `fk_writes_aid` FOREIGN KEY (`aid`) REFERENCES `AUTHOR` (`aid`) ON DELETE CASCADE ON UPDATE CASCADE,
520 CONSTRAINT `fk_writes_bid` FOREIGN KEY (`bid`) REFERENCES `BOOKS` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE,
521 CONSTRAINT `fk_writes_pid` FOREIGN KEY (`pid`) REFERENCES `PUBLISHER` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE
522) ENGINE=InnoDB DEFAULT CHARSET=latin1;
523/*!40101 SET character_set_client = @saved_cs_client */;
524
525--
526-- Dumping data for table `writes`
527--
528
529LOCK TABLES `writes` WRITE;
530/*!40000 ALTER TABLE `writes` DISABLE KEYS */;
531INSERT INTO `writes` VALUES (1,1,1),(2,2,2),(453,3,3),(4,4,4),(5,5,5),(6,6,1),(453,7,6);
532/*!40000 ALTER TABLE `writes` ENABLE KEYS */;
533UNLOCK TABLES;
534/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
535
536/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
537/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
538/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
539/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
540/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
541/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
542/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
543
544-- Dump completed on 2018-10-01 0:10:21