HKBK College of Engineering
Dept. of Information Science and Engineering
18CSL58-DBMS Lab, ISE,HKBKCE. Dr. Syed Mustafa Page 1
Exercise-1 Library Database
Title Phone
Pub_Year
Book_id Name Address
Published
BOOK Publisher
by
No_of_Copies
Authored
by
BOOK_AUTHORS LIBRARY_PROGRAMME
Address
Programme_id
Auther_Name
me Programme_Name
e
18CSL58-DBMS Lab, ISE,HKBKCE. Dr. Syed Mustafa Page 2
Database(Relational) Schema - Library Database:
PUBLISHER
Name Address Phone
BOOK
Book_id Title Publisher_Name Pub_Year
BOOK_AUTHORS
Book_id Author_Name
LIBRARY_PROGRAMME
Programme_id Programme_Name Address
BOOK_COPIES
Book_id Programme_id No_of_Copies
BOOK_LENDING
Book_id Programme_id Card_No Date_Out Due_Date
18CSL58-DBMS Lab, ISE,HKBKCE. Dr. Syed Mustafa Page 3
Creating Tables(Relation) using SQL Query
Create table publisher (Name varchar(50) primary key, Address varchar(100),
1. Phone number(10));
Create table BOOK (Book_id number primary key, Title varchar(100),
2. Publisher_Name varchar(100) references publisher on delete cascade,
Pub_Year number(4));
Create table BOOK_AUTHORS (Book_id number, Author_Name
3. varchar(100), primary key(Book_id, Author_Name), foreign key(Book_id)
references BOOK on delete cascade );
Create table LIBRARY_PROGRAMME (Programme_id number primary key,
4.
Programme_Name varchar(100), Address varchar(100));
Create table BOOK_COPIES (Book_id number, Programme_id number,
No_of_Copies number, primary key(Book_id, Programme_id), foreign
key(Book_id) references BOOK on delete cascade , foreign
key(Programme_id) references LIBRARY_PROGRAMME on delete cascade );
(or)
Create table BOOK_COPIES
5. (
Book_id number, Programme_id number, No_of_Copies number,
primary key(Book_id, Programme_id),
foreign key(Book_id) references BOOK on delete cascade ,
foreign key(Programme_id) references LIBRARY_PROGRAMME on delete
cascade
);
Create table BOOK_LENDING (Book_id number, Programme_id number,
Card_No number, Date_Out date, Due_Date date,
Primary key(Book_id, Programme_id, Card_No),
6. foreign key(Book_id) references BOOK on delete cascade ,
foreign key(Programme_id) references LIBRARY_PROGRAMME on delete
cascade );
18CSL58-DBMS Lab, ISE,HKBKCE. Dr. Syed Mustafa Page 4
Inserting Data into Tables(Relation)-Database using SQL Query
insert into publisher values('TMH','Bangalore',8012345678);
1.
insert into publisher values('Addision Wesley','Mumbai',2212345678);
insert into publisher values('Pearson','chennai',4412345678);
insert into publisher values('Cengage','hyderabad',3312345678);
insert into publisher values('Oxford','New Delhi',1112345678);
select * from publisher;
NAME ADDRESS PHONE
TMH Bangalore 8012345678
Addision Wesley Mumbai 2212345678
Pearson chennai 4412345678
Oxford New Delhi 1112345678
Cengage hyderabad 3312345678
insert into BOOK values(100,'Unix Concepts and Applications','TMH',2005);
2.
insert into BOOK values(101, 'UNIX & Shell Programming','Pearson',2014);
insert into BOOK values(102, 'Core Python Applications Programming','Pearson',2015);
insert into BOOK values(103, 'Formal Languages and Automata Theory','Oxford',2012);
insert into BOOK values(104, 'Fundamentals of Database Systems','Pearson',2017);
select * from book;
BOOK_ID TITLE PUBLISHER_NAME PUB_YEAR
103 Formal Languages and Automata Theory Oxford 2012
104 Fundamentals of Database Systems Pearson 2017
100 Unix Concepts and Applications TMH 2005
101 UNIX & Shell Programming Pearson 2014
102 Core Python Applications Programming Pearson 2015
insert into book_authors values(100, 'Sumitabha Das');
3.
insert into book_authors values(101, 'Venkatesh Murthy');
insert into book_authors values(102, 'Wesley J Chun');
insert into book_authors values(103 , 'C K Nagpal');
insert into book_authors values(104 , 'Ramez Elmasri');
insert into book_authors values(104 , 'Shamkant B. Navathe');
18CSL58-DBMS Lab, ISE,HKBKCE. Dr. Syed Mustafa Page 5
select * from book_authors;
BOOK_ID AUTHOR_NAME
100 Sumitabha Das
101 Venkatesh Murthy
102 Wesley J Chun
103 C K Nagpal
104 Ramez Elmasri
104 Shamkant B. Navathe
4. insert into library_programme values(1,'CSE', 'Block A');
insert into library_programme values(2,'ISE', 'Block F');
insert into library_programme values(3,'ECE', 'Block B');
insert into library_programme values(4,'ME', 'Block D');
insert into library_programme values(5,'CIV', 'Block D');
select * from library_programme;
PROGRAMME_ID PROGRAMME_NAME ADDRESS
1 CSE Block A
3 ECE Block B
4 ME Block D
5 CIV Block D
2 ISE Block F
insert into book_copies values(100,1,5);
5.
insert into book_copies values(100,2,40);
insert into book_copies values(101,1,10);
insert into book_copies values(101,2,60);
insert into book_copies values(101,3,20);
insert into book_copies values(102,1,60);
insert into book_copies values(102,2,100);
insert into book_copies values(102,3,50);
insert into book_copies values(103,3,20);
insert into book_copies values(104,1,50);
18CSL58-DBMS Lab, ISE,HKBKCE. Dr. Syed Mustafa Page 6
select * from book_copies;
BOOK_ID PROGRAMME_ID NO_OF_COPIES
103 3 20
101 2 60
101 3 20
104 1 50
100 1 5
100 2 40
101 1 10
102 1 60
102 2 100
102 3 50
( ***Note: Use SQL Command Prompt DOS Shell Window to avoid date Error***)
6.
insert into book_lending values(100,1,200,'15-Oct-2019', '30-Oct-2019');
insert into book_lending values(101,1,200,'5-Sep-2020', '20-Sep-2020');
insert into book_lending values(102,1,300,'15-Jan-2017', '20-April-2017');
insert into book_lending values(101,1,300,'15-feb-2017', '20-may-2017');
insert into book_lending values(103,1,300,'15-march-2017', '20-april-2017');
insert into book_lending values(104,1,300,'15-april-2017', '20-jun-2017');
select * from book_lending;
BOOK_ID PROGRAMME_ID CARD_NO DATE_OUT DUE_DATE
100 1 200 10/15/2019 10/30/2019
101 1 200 09/05/2020 09/20/2020
102 1 300 01/15/2017 04/20/2017
101 1 300 02/15/2017 05/20/2017
104 1 300 04/15/2017 06/20/2017
103 1 300 03/15/2017 04/20/2017
18CSL58-DBMS Lab, ISE,HKBKCE. Dr. Syed Mustafa Page 7
Queries as per Lab Exercise
Retrieve details of all books in the library – id, title, name of publisher, authors,
Query 1. number of copies in each Programme, etc.
select bk.book_id, title, publisher_name, author_name, no_of_copies, programme_name
from book bk, book_authors ba, library_programme lp, book_copies bc
where bk.book_id=ba.book_id and bk.book_id=bc.book_id and bc. Programme_id=lp.
Programme_id
order by programme_name, bk.book_id;
BOOK_ID TITLE PUBLISHER_NAME AUTHOR_NAME NO_OF_COPIES PROGRAMME_NAME
100 Unix Concepts and Applications TMH Sumitabha Das 5 CSE
101 UNIX & Shell Programming Pearson Venkatesh Murthy 10 CSE
Core Python Applications
102 Pearson Wesley J Chun 60 CSE
Programming
104 Fundamentals of Database Systems Pearson Ramez Elmasri 50 CSE
Shamkant B.
104 Fundamentals of Database Systems Pearson 50 CSE
Navathe
101 UNIX & Shell Programming Pearson Venkatesh Murthy 20 ECE
Core Python Applications
102 Pearson Wesley J Chun 50 ECE
Programming
100 Unix Concepts and Applications TMH Sumitabha Das 40 ISE
101 UNIX & Shell Programming Pearson Venkatesh Murthy 60 ISE
Core Python Applications
102 Pearson Wesley J Chun 100 ISE
Programming
Get the particulars of borrowers who have borrowed more than 3 books, but
Query 2. from Jan 2017 to Jun 2017.
select card_no as borrower,count(*) as noofbooksborrowed from book_lending where
date_out between TO_DATE('01/01/2017', 'DD/MM/YYYY') and TO_DATE('30/06/2017',
'DD/MM/YYYY') group by card_no having count(*)>3;
BORROWER NOOFBOOKSBORROWED
300 4
Query 3. delete from book where book_id=103;
select * from book;
BOOK_ID TITLE PUBLISHER_NAME PUB_YEAR
104 Fundamentals of Database Systems Pearson 2017
100 Unix Concepts and Applications TMH 2005
101 UNIX & Shell Programming Pearson 2014
102 Core Python Applications Programming Pearson 2015
18CSL58-DBMS Lab, ISE,HKBKCE. Dr. Syed Mustafa Page 8
select * from book_authors;
BOOK_ID AUTHOR_NAME
100 Sumitabha Das
101 Venkatesh Murthy
102 Wesley J Chun
104 Ramez Elmasri
104 Shamkant B. Navathe
select * from book_lending;
BOOK_ID PROGRAMME_ID CARD_NO DATE_OUT DUE_DATE
100 1 200 10/15/2019 10/30/2019
101 1 200 09/05/2020 09/20/2020
102 1 300 01/15/2017 04/20/2017
101 1 300 02/15/2017 05/20/2017
104 1 300 04/15/2017 06/20/2017
Partition the BOOK table based on year of publication. Demonstrate its working
Query 4. with a simple query.
create view book_part as select book_id,pub_year from book;
select * from book_part;
BOOK_ID PUB_YEAR
104 2017
100 2005
101 2014
102 2015
Create a view of all books and its number of copies that are currently available
Query 5. in the Library.
create view book_view (book_id,Title, No_of_copies) as Select book.book_id, Title,
sum(No_of_Copies) from book, book_copies where book.book_id=book_copies.book_id
group by book.book_id,title order by book.book_id;
select * from book_view;
BOOK_ID TITLE NO_OF_COPIES
100 Unix Concepts and Applications 45
101 UNIX & Shell Programming 90
102 Core Python Applications Programming 210
104 Fundamentals of Database Systems 50
18CSL58-DBMS Lab, ISE,HKBKCE. Dr. Syed Mustafa Page 9
