DBMS-Lab#2
Question#1
-
Retrieve
the required information using SQL language.
Give
a database schema for a library management system as the following
picture.
-
How
many copies of the book titled “********” are owned by the
library branch whose name is "******"?
-
How
many copies of the book titled "******" are owned by each
library branch?
-
Retrieve
the names of all borrowers who do not have any books checked out.
-
For
each book that is loaned out from the "******" branch and
whose DueDate is today, retrieve the book title, the borrower's
name, and the borrower's address.
-
For
each library branch, retrieve the branch name and the total number
of books loaned out from that branch.
-
Retrieve
the names, addresses, and number of books checked out for all
borrowers who have more than five books checked out.
create table Book (
BookId int not null primary key,
-> Title
varchar(255) not null,
->
PublisherName varchar(255) not null
-> );
create table
BookAuthors
-> (
-> BookId int
not null,
-> AuthorName
varchar(255) not null,
-> primary
key(BookId,AuthorName)
-> );
alter table
BookAuthors add foreign key(BookId) references Book(BookId);
create table
Publisher
-> (
-> Name
varchar(255) not null primary key,
-> Address
varchar(255) not null,
-> Phone
int(15) not null
-> );
alter table Book add
foreign key(PublisherName) references Publisher(Name);
create table
BookCopies
-> (
-> BookId int
not null,
-> BranchId
int not null,
->
No_of_copies int,
-> primary
key(BookId,BranchId),
-> foreign
key(BookId) references Book(BookId)
-> );
create table
LibraryBranch
-> (
-> BranchId
int not null primary key,
-> BranchName
varchar(255) not null,
-> Address
varchar(255) not null
-> );
alter table
BookCopies add foreign key(BranchId) references
LibraryBranch(BranchId);
create table
BookLoans
-> (
-> BookId int
not null,
-> BranchId
int not null,
-> CardNo int
not null,
-> DateOut
date not null,
-> DueDate
date not null,
-> primary
key(BookId,BranchId,CardNo),
-> foreign
key(BookId) references Book(BookId),
-> foreign
key(BranchId) references LibraryBranch(BranchId)
-> );
create table
Borrower
-> (
-> CardNo int
not null primary key,
-> Name
varchar(255) not null,
-> Address
varchar(255) not null,
-> Phone int
-> );
alter table
BookLoans add foreign key(CardNo) references Borrower(CardNo);
insert into
Publisher values('A','AA',123),
->
('B','BB',456),
->
('C','CC',789),
->
('D','DD',132),
->
('E','EE',312);
insert into Book
values(5,'fifth','C'),
->
(7,'seventh','D'),
->
(2,'second','B');
insert into
LibraryBranch values(1,'cse','csed'),
->
(2,'ce','cdept'),
->
(3,'ee','edept'),
->
(4,'it','csed'),
->
(5,'me','mdept');
insert into
BookCopies values(2,1,20),
-> (2,2,15),
-> (5,3,30),
-> (7,2,5),
-> (7,4,8);
-> (7,1,15);
insert into
Borrower values(1,'AAA','csed',111),
->
(2,'BBB','csed',222),
->
(3,'CCC','edept',333),
->
(4,'DDD','mdept',444),
->
(5,'EEE','edept',555);
insert into
BookAuthors values(2,'author2'),
->
(5,'author5'),
->
(7,'author7');
/*Inserting into
BookLoans table BookId,brid,cdno,dout,due */
insert into
BookLoans values(2,1,1,'2016-01-10','2016-01-13');
insert into
BookLoans values(2,4,1,'2016-01-09','2016-01-15');
insert into
BookLoans values(5,2,2,'2016-01-05','2016-01-13');
insert into
BookLoans values(5,3,2,'2016-01-06','2016-01-12');
insert into
BookLoans values(7,1,4,'2016-01-05','2016-01-10');
insert into
BookLoans values(7,1,5,'2016-01-06','2016-01-14');
insert into
BookLoans values(7,2,5,'2016-01-10','2016-01-13');
Ans1:--
mysql> select
BC.No_of_copies from BookCopies BC
-> where
BC.BookId=(select B.BookId from Book B where Title='fifth')
-> and
BC.BranchId=(select L.BranchId from LibraryBranch L where
BranchName='ee');
Or
select
BookCopies.No_of_copies from ((Book natural join BookCopies)natural
join LibraryBranch) where Title='fifth' and BranchName='ee';
+--------------+
| No_of_copies |
+--------------+
| 30 |
+--------------+
Ans2:-
mysql>select
BookCopies.No_of_copies ,LibraryBranch.BranchName from ((Book natural
join BookCopies)natural join LibraryBranch) where Title='second';
+--------------+------------+
| No_of_copies |
BranchName |
+--------------+------------+
| 20 | cse
|
| 15 | ce
|
+--------------+------------+
Ans3:-
mysql> select
B.Name from Borrower B
-> where
B.CardNo not in (select CardNo from BookLoans);
Or
select
Borrower.Name from Borrower left outer join BookLoans on
Borrower.CardNo=BookLoans.CardNo where BookLoans.CardNo is null;
+------+
| Name |
+------+
| CCC |
+------+
Ans4:-
mysql>select
Book.Title,Borrower.Name,Borrower.Address from BookLoans
-> inner join
Book on BookLoans.BookId=Book.BookId
-> inner join
LibraryBranch on BookLoans.BranchId=LibraryBranch.BranchId
-> inner join
Borrower on BookLoans.CardNo=Borrower.CardNo
-> where
LibraryBranch.BranchName='ce' and BookLoans.DueDate=curdate();
+---------+------+---------+
| Title | Name |
Address |
+---------+------+---------+
| fifth | BBB |
csed |
| seventh | EEE |
edept |
+---------+------+---------+
Ans5:-
mysql> select
LibraryBranch.BranchName,count(*) from BookLoans,LibraryBranch
-> where
BookLoans.BranchId=LibraryBranch.BranchId
-> group by
LibraryBranch.BranchName;
+------------+----------+
| BranchName |
count(*) |
+------------+----------+
| ce |
2 |
| cse |
3 |
| ee |
1 |
| it |
1 |
+------------+----------+
Ans6:-
select
Borrower.Name,Borrower.Address,count(*)
-> from
Borrower,BookLoans
-> where
BookLoans.CardNo=Borrower.CardNo
-> group by
BookLoans.CardNo
-> having
count(*)>1;
+------+---------+----------+
| Name | Address |
count(*) |
+------+---------+----------+
| AAA | csed |
2 |
| BBB | csed |
2 |
| EEE | edept |
2 |
+------+---------+----------+
Tables
mysql>show tables;
+------------------+
| Tables_in_lab2Q1 |
+------------------+
| Book |
| BookAuthors |
| BookCopies |
| BookLoans |
| Borrower |
| LibraryBranch |
| Publisher |
+------------------+
Book
Table
mysql> select *from Book;
+--------+---------+---------------+
| BookId | Title |
PublisherName |
+--------+---------+---------------+
| 2 | second | B
|
| 5 | fifth | C
|
| 7 | seventh | D
|
+--------+---------+---------------+
BookAuthors
Table
mysql> select *from
BookAuthors;
+--------+------------+
| BookId | AuthorName |
+--------+------------+
| 2 | author2 |
| 5 | author5 |
| 7 | author7 |
+--------+------------+
BookCopies
Table
mysql> select *from
BookCopies;
+--------+----------+--------------+
| BookId | BranchId |
No_of_copies |
+--------+----------+--------------+
| 2 | 1 |
20 |
| 2 | 2 |
15 |
| 5 | 3 |
30 |
| 7 | 1 |
15 |
| 7 | 2 |
5 |
| 7 | 4 |
8 |
+--------+----------+--------------+
BookLoans
Table
mysql> select *from
BookLoans;
+--------+----------+--------+------------+------------+
| BookId | BranchId | CardNo |
DateOut | DueDate |
+--------+----------+--------+------------+------------+
| 2 | 1 | 1 |
2016-01-10 | 2016-01-13 |
| 2 | 4 | 1 |
2016-01-09 | 2016-01-15 |
| 5 | 2 | 2 |
2016-01-05 | 2016-01-13 |
| 5 | 3 | 2 |
2016-01-06 | 2016-01-12 |
| 7 | 1 | 4 |
2016-01-05 | 2016-01-10 |
| 7 | 1 | 5 |
2016-01-06 | 2016-01-14 |
| 7 | 2 | 5 |
2016-01-10 | 2016-01-13 |
+--------+----------+--------+------------+------------+
Borrower
Table
mysql> select *from
Borrower;
+--------+------+---------+-------+
| CardNo | Name | Address |
Phone |
+--------+------+---------+-------+
| 1 | AAA | csed |
111 |
| 2 | BBB | csed |
222 |
| 3 | CCC | edept |
333 |
| 4 | DDD | mdept |
444 |
| 5 | EEE | edept |
555 |
+--------+------+---------+-------+
LibraryBranch
Table
mysql> select *from
LibraryBranch;
+----------+------------+---------+
| BranchId | BranchName |
Address |
+----------+------------+---------+
| 1 | cse | csed
|
| 2 | ce |
cdept |
| 3 | ee |
edept |
| 4 | it | csed
|
| 5 | me |
mdept |
+----------+------------+---------+
Publisher
Table
mysql> select *from
Publisher;
+------+---------+-------+
| Name | Address | Phone |
+------+---------+-------+
| A | AA | 123 |
| B | BB | 456 |
| C | CC | 789 |
| D | DD | 132 |
| E | EE | 312 |
+------+---------+-------+