· 6 years ago · Oct 21, 2019, 05:00 AM
1mysql> select * from book;
2+---------+------------------+----------------------+------+
3| book_id | title | publisher_name | date |
4+---------+------------------+----------------------+------+
5| 1 | The Grand Design | Infinity Books | 2012 |
6| 2 | Tintin | ABC Publications | 2002 |
7| 3 | Inception | ABC Publications | 2012 |
8| 4 | Deception | Twisted Publishers | 2012 |
9| 5 | Moana | Mukul Printing House | 2015 |
10| 6 | Bye World | Mukul Printing House | 2017 |
11+---------+------------------+----------------------+------+
126 rows in set (0.00 sec)
13
14mysql> select * from book_author;
15+---------+-----------------+
16| book_id | author_name |
17+---------+-----------------+
18| 1 | Stephen Hawking |
19| 2 | Serge |
20| 3 | Herge |
21| 4 | Herge |
22| 5 | Nair Mukul |
23| 6 | Nega M J |
24+---------+-----------------+
256 rows in set (0.00 sec)
26
27mysql> select * from publisher;
28+----------------------+------------+----------+
29| name | address | phone |
30+----------------------+------------+----------+
31| ABC Publications | California | 45614234 |
32| Infinity Books | New York | 26252428 |
33| Mukul Printing House | Delhi | 25547896 |
34| Nair Books | Mumbai | 56412123 |
35| Twisted Publishers | Toronto | 41424546 |
36+----------------------+------------+----------+
375 rows in set (0.00 sec)
38
39mysql> select * from book_copies;
40+---------+-----------+--------------+
41| book_id | branch_id | no_of_copies |
42+---------+-----------+--------------+
43| 1 | 201 | 10 |
44| 2 | 201 | 10 |
45| 5 | 204 | 14 |
46| 5 | 201 | 14 |
47| 3 | 201 | 14 |
48| 3 | 202 | 20 |
49| 3 | 203 | 26 |
50| 4 | 206 | 14 |
51| 4 | 205 | 14 |
52| 4 | 202 | 24 |
53+---------+-----------+--------------+
5410 rows in set (0.00 sec)
55
56mysql> select * from book_lending;
57+---------+-----------+---------+------------+------------+
58| book_id | branch_id | card_no | date_out | due_date |
59+---------+-----------+---------+------------+------------+
60| 1 | 201 | 1001 | 2017-01-02 | NULL |
61| 1 | 204 | 1004 | 2017-01-05 | 2017-06-26 |
62| 2 | 204 | 1004 | 2017-01-05 | 2017-06-26 |
63| 1 | 203 | 1004 | 2017-03-05 | 2017-08-26 |
64| 2 | 202 | 1005 | 2019-03-05 | 2019-08-26 |
65| 3 | 204 | 1005 | 2019-04-05 | 2019-07-20 |
66| 3 | 206 | 1005 | 2018-05-07 | 2019-01-20 |
67| 4 | 205 | 1003 | 2017-05-07 | 2018-01-20 |
68| 5 | 206 | 1003 | 2017-06-05 | 2018-02-02 |
69| 4 | 204 | 1002 | 2019-05-05 | 2019-07-25 |
70+---------+-----------+---------+------------+------------+
7110 rows in set (0.00 sec)
72
73mysql> select * from library_branch;
74+-----------+------------------+--------------+
75| branch_id | branch_name | address |
76+-----------+------------------+--------------+
77| 201 | Star Books | M G Road |
78| 202 | Sapna Book House | M G Road |
79| 203 | Sapna Book House | Indiranagar |
80| 204 | OM Books | Indiranagar |
81| 205 | OM Books | Hoodi |
82| 206 | Crossword | Charles Road |
83+-----------+------------------+--------------+
846 rows in set (0.00 sec)
85
86
87-- MySQL dump 10.13 Distrib 5.7.13, for Linux (i686)
88--
89-- Host: localhost Database: Mlibrary
90-- ------------------------------------------------------
91-- Server version 5.7.13-0ubuntu0.16.04.2
92
93/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
94/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
95/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
96/*!40101 SET NAMES utf8 */;
97/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
98/*!40103 SET TIME_ZONE='+00:00' */;
99/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
100/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
101/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
102/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
103
104--
105-- Table structure for table `book`
106--
107
108DROP TABLE IF EXISTS `book`;
109/*!40101 SET @saved_cs_client = @@character_set_client */;
110/*!40101 SET character_set_client = utf8 */;
111CREATE TABLE `book` (
112 `book_id` int(4) NOT NULL,
113 `title` varchar(20) DEFAULT NULL,
114 `publisher_name` varchar(20) DEFAULT NULL,
115 `date` int(4) DEFAULT NULL,
116 PRIMARY KEY (`book_id`),
117 KEY `publisher_name` (`publisher_name`),
118 CONSTRAINT `book_ibfk_1` FOREIGN KEY (`publisher_name`) REFERENCES `publisher` (`name`) ON DELETE SET NULL ON UPDATE CASCADE
119) ENGINE=InnoDB DEFAULT CHARSET=latin1;
120/*!40101 SET character_set_client = @saved_cs_client */;
121
122--
123-- Dumping data for table `book`
124--
125
126LOCK TABLES `book` WRITE;
127/*!40000 ALTER TABLE `book` DISABLE KEYS */;
128INSERT INTO `book` VALUES (1,'The Grand Design','Infinity Books',2012),(2,'Tintin','ABC Publications',2002),(3,'Inception','ABC Publications',2012),(4,'Deception','Twisted Publishers',2012),(5,'Moana','Mukul Printing House',2015),(6,'Bye World','Mukul Printing House',2017);
129/*!40000 ALTER TABLE `book` ENABLE KEYS */;
130UNLOCK TABLES;
131
132--
133-- Table structure for table `book_author`
134--
135
136DROP TABLE IF EXISTS `book_author`;
137/*!40101 SET @saved_cs_client = @@character_set_client */;
138/*!40101 SET character_set_client = utf8 */;
139CREATE TABLE `book_author` (
140 `book_id` int(4) NOT NULL,
141 `author_name` varchar(20) DEFAULT NULL,
142 PRIMARY KEY (`book_id`),
143 CONSTRAINT `book_author_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`book_id`) ON DELETE CASCADE ON UPDATE CASCADE
144) ENGINE=InnoDB DEFAULT CHARSET=latin1;
145/*!40101 SET character_set_client = @saved_cs_client */;
146
147--
148-- Dumping data for table `book_author`
149--
150
151LOCK TABLES `book_author` WRITE;
152/*!40000 ALTER TABLE `book_author` DISABLE KEYS */;
153INSERT INTO `book_author` VALUES (1,'Stephen Hawking'),(2,'Serge'),(3,'Herge'),(4,'Herge'),(5,'Nair Mukul'),(6,'Nega M J');
154/*!40000 ALTER TABLE `book_author` ENABLE KEYS */;
155UNLOCK TABLES;
156
157--
158-- Table structure for table `book_copies`
159--
160
161DROP TABLE IF EXISTS `book_copies`;
162/*!40101 SET @saved_cs_client = @@character_set_client */;
163/*!40101 SET character_set_client = utf8 */;
164CREATE TABLE `book_copies` (
165 `book_id` int(4) DEFAULT NULL,
166 `branch_id` int(4) DEFAULT NULL,
167 `no_of_copies` int(3) DEFAULT NULL,
168 KEY `book_id` (`book_id`),
169 KEY `branch_id` (`branch_id`),
170 CONSTRAINT `book_copies_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`book_id`) ON DELETE CASCADE ON UPDATE CASCADE,
171 CONSTRAINT `book_copies_ibfk_2` FOREIGN KEY (`branch_id`) REFERENCES `library_branch` (`branch_id`) ON DELETE CASCADE ON UPDATE CASCADE
172) ENGINE=InnoDB DEFAULT CHARSET=latin1;
173/*!40101 SET character_set_client = @saved_cs_client */;
174
175--
176-- Dumping data for table `book_copies`
177--
178
179LOCK TABLES `book_copies` WRITE;
180/*!40000 ALTER TABLE `book_copies` DISABLE KEYS */;
181INSERT INTO `book_copies` VALUES (1,201,10),(2,201,10),(5,204,14),(5,201,14),(3,201,14),(3,202,20),(3,203,26),(4,206,14),(4,205,14),(4,202,24);
182/*!40000 ALTER TABLE `book_copies` ENABLE KEYS */;
183UNLOCK TABLES;
184
185--
186-- Table structure for table `book_lending`
187--
188
189DROP TABLE IF EXISTS `book_lending`;
190/*!40101 SET @saved_cs_client = @@character_set_client */;
191/*!40101 SET character_set_client = utf8 */;
192CREATE TABLE `book_lending` (
193 `book_id` int(4) DEFAULT NULL,
194 `branch_id` int(4) DEFAULT NULL,
195 `card_no` int(5) DEFAULT NULL,
196 `date_out` date DEFAULT NULL,
197 `due_date` date DEFAULT NULL,
198 KEY `book_id` (`book_id`),
199 KEY `branch_id` (`branch_id`),
200 CONSTRAINT `book_lending_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`book_id`) ON DELETE CASCADE ON UPDATE CASCADE,
201 CONSTRAINT `book_lending_ibfk_2` FOREIGN KEY (`branch_id`) REFERENCES `library_branch` (`branch_id`) ON DELETE CASCADE ON UPDATE CASCADE
202) ENGINE=InnoDB DEFAULT CHARSET=latin1;
203/*!40101 SET character_set_client = @saved_cs_client */;
204
205--
206-- Dumping data for table `book_lending`
207--
208
209LOCK TABLES `book_lending` WRITE;
210/*!40000 ALTER TABLE `book_lending` DISABLE KEYS */;
211INSERT INTO `book_lending` VALUES (1,201,1001,'2017-01-02',NULL),(1,204,1004,'2017-01-05','2017-06-26'),(2,204,1004,'2017-01-05','2017-06-26'),(1,203,1004,'2017-03-05','2017-08-26'),(2,202,1005,'2019-03-05','2019-08-26'),(3,204,1005,'2019-04-05','2019-07-20'),(3,206,1005,'2018-05-07','2019-01-20'),(4,205,1003,'2017-05-07','2018-01-20'),(5,206,1003,'2017-06-05','2018-02-02'),(4,204,1002,'2019-05-05','2019-07-25');
212/*!40000 ALTER TABLE `book_lending` ENABLE KEYS */;
213UNLOCK TABLES;
214
215--
216-- Table structure for table `library_branch`
217--
218
219DROP TABLE IF EXISTS `library_branch`;
220/*!40101 SET @saved_cs_client = @@character_set_client */;
221/*!40101 SET character_set_client = utf8 */;
222CREATE TABLE `library_branch` (
223 `branch_id` int(4) NOT NULL,
224 `branch_name` varchar(20) DEFAULT NULL,
225 `address` varchar(20) DEFAULT NULL,
226 PRIMARY KEY (`branch_id`)
227) ENGINE=InnoDB DEFAULT CHARSET=latin1;
228/*!40101 SET character_set_client = @saved_cs_client */;
229
230--
231-- Dumping data for table `library_branch`
232--
233
234LOCK TABLES `library_branch` WRITE;
235/*!40000 ALTER TABLE `library_branch` DISABLE KEYS */;
236INSERT INTO `library_branch` VALUES (201,'Star Books','M G Road'),(202,'Sapna Book House','M G Road'),(203,'Sapna Book House','Indiranagar'),(204,'OM Books','Indiranagar'),(205,'OM Books','Hoodi'),(206,'Crossword','Charles Road');
237/*!40000 ALTER TABLE `library_branch` ENABLE KEYS */;
238UNLOCK TABLES;
239
240--
241-- Table structure for table `publisher`
242--
243
244DROP TABLE IF EXISTS `publisher`;
245/*!40101 SET @saved_cs_client = @@character_set_client */;
246/*!40101 SET character_set_client = utf8 */;
247CREATE TABLE `publisher` (
248 `name` varchar(20) NOT NULL,
249 `address` varchar(20) DEFAULT NULL,
250 `phone` int(10) DEFAULT NULL,
251 PRIMARY KEY (`name`)
252) ENGINE=InnoDB DEFAULT CHARSET=latin1;
253/*!40101 SET character_set_client = @saved_cs_client */;
254
255--
256-- Dumping data for table `publisher`
257--
258
259LOCK TABLES `publisher` WRITE;
260/*!40000 ALTER TABLE `publisher` DISABLE KEYS */;
261INSERT INTO `publisher` VALUES ('ABC Publications','California',45614234),('Infinity Books','New York',26252428),('Mukul Printing House','Delhi',25547896),('Nair Books','Mumbai',56412123),('Twisted Publishers','Toronto',41424546);
262/*!40000 ALTER TABLE `publisher` ENABLE KEYS */;
263UNLOCK TABLES;
264/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
265
266/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
267/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
268/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
269/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
270/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
271/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
272/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
273
274-- Dump completed on 2019-10-21 9:34:11
275
2761
277mysql> select * from book natural join book_author natural join book_copies natural join library_branch;
278+-----------+---------+------------------+----------------------+------+-----------------+--------------+------------------+--------------+
279| branch_id | book_id | title | publisher_name | date | author_name | no_of_copies | branch_name | address |
280+-----------+---------+------------------+----------------------+------+-----------------+--------------+------------------+--------------+
281| 201 | 1 | The Grand Design | Infinity Books | 2012 | Stephen Hawking | 10 | Star Books | M G Road |
282| 201 | 2 | Tintin | ABC Publications | 2002 | Serge | 10 | Star Books | M G Road |
283| 204 | 5 | Moana | Mukul Printing House | 2015 | Nair Mukul | 14 | OM Books | Indiranagar |
284| 201 | 5 | Moana | Mukul Printing House | 2015 | Nair Mukul | 14 | Star Books | M G Road |
285| 201 | 3 | Inception | ABC Publications | 2012 | Herge | 14 | Star Books | M G Road |
286| 202 | 3 | Inception | ABC Publications | 2012 | Herge | 20 | Sapna Book House | M G Road |
287| 203 | 3 | Inception | ABC Publications | 2012 | Herge | 26 | Sapna Book House | Indiranagar |
288| 206 | 4 | Deception | Twisted Publishers | 2012 | Herge | 14 | Crossword | Charles Road |
289| 205 | 4 | Deception | Twisted Publishers | 2012 | Herge | 14 | OM Books | Hoodi |
290| 202 | 4 | Deception | Twisted Publishers | 2012 | Herge | 24 | Sapna Book House | M G Road |
291+-----------+---------+------------------+----------------------+------+-----------------+--------------+------------------+--------------+
29210 rows in set (0.00 sec)
293
2942