Thursday 14 January 2016

Some Recommended questions based on trie data structure for beginners:---
Phonelist
Dictionary
subxor 
Posted by at No comments:

Wednesday 13 January 2016


                                          DBMS-Lab#2
                                            Question#1

  1. Retrieve the required information using SQL language.
Give a database schema for a library management system as the following picture.
  1. How many copies of the book titled “********” are owned by the library branch whose name is "******"?
  2. How many copies of the book titled "******" are owned by each library branch?
  3. Retrieve the names of all borrowers who do not have any books checked out.
  4. 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.
  5. For each library branch, retrieve the branch name and the total number of books loaned out from that branch.
  6. 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 |
+------+---------+-------+
Posted by at No comments:
Subscribe to: Posts (Atom)