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