Problem Statement

1) Consider the following schema for a Library Database:

BOOK (Book_id, Title, Publisher_Name, Pub_Year)

BOOK_AUTHORS (Book_id, Author_Name)

PUBLISHER (Name, Address, Phone)

BOOK_COPIES (Book_id, Branch_id, No-of_Copies)

BOOK_LENDING (Book_id, Branch_id, Card_No, Date_Out, Due_Date)

LIBRARY_BRANCH (Branch_id, Branch_Name, Address)

Write SQL queries to

1. Retrieve details of all books in the library – id, title, name of publisher, authors, number

of copies in each branch, etc.

2. Get the particulars of borrowers who have borrowed more than 3 books, but from Jan

2017 to Jun 2017

3. Delete a book in BOOK table. Update the contents of other tables to reflect this data

manipulation operation.

4. Partition the BOOK table based on year of publication. Demonstrate its working with a

simple query.

5. Create a view of all books and its number of copies that are currently available in the

Library.

ER DIAGRAM LIBRARY DATABASE

Part-A Program-1 ER Diagram Library Database

Schema Diagram

Schema Diagram of Library Database

Creating Tables

1) PUBLISHER Table:

SQL> CREATE TABLE PUBLISHER

( NAME VARCHAR(18) PRIMARY KEY,

ADDRESS VARCHAR(10),

PHONE VARCHAR(10));

Table created.

2) BOOK Table

SQL> CREATE TABLE BOOK

( BOOK_ID INTEGER PRIMARY KEY,

TITLE VARCHAR(20),

PUBLISHER_NAME VARCHAR(20) REFERENCES PUBLISHER(NAME) ON DELETE CASCADE, PUB_YEAR NUMBER(4));

Table created.

3) BOOK AUTHORS Table:

SQL> CREATE TABLE BOOK_AUTHORS(

BOOK_ID INTEGER REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,

AUTHOR_NAME VARCHAR(20),

PRIMARY KEY(BOOK_ID));

Table created.

4) LIBRARY BRANCH Table:

SQL> CREATE TABLE LIBRARY_BRANCH

(BRANCH_ID INTEGER PRIMARY KEY,

BRANCH_NAME VARCHAR(18),

ADDRESS VARCHAR(15));

Table created.

5) LIBRARY BRANCH Table:

SQL> CREATE TABLE LIBRARY_BRANCH

( BRANCH_ID INTEGER PRIMARY KEY,

BRANCH_NAME VARCHAR(18),

ADDRESS VARCHAR(15));

Table created.

6) BOOK LENDING Table:

SQL> CREATE TABLE BOOK_LENDING

( BOOK_ID INTEGER REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,

BRANCH_ID INTEGER REFERENCES LIBRARY_BRANCH(BRANCH_ID) ON DELETE

CASCADE,

CARD_NO INTEGER,

DATE_OUT DATE,

DUE_DATE DATE,

PRIMARY KEY(BOOK_ID,BRANCH_ID,CARD_NO));

Table created.

Inserting Values

PUBLISHER:

SQL> INSERT INTO PUBLISHER VALUES('PEARSON','BANGALORE','9875462530');

SQL> INSERT INTO PUBLISHER VALUES('MCGRAW','NEWDELHI','7845691234');

BOOK

SQL> INSERT INTO BOOK VALUES(1111,'SE','PEARSON',2005);

SQL> INSERT INTO BOOK VALUES(2222,'DBMS','MCGRAW',2004);

SQL> INSERT INTO BOOK VALUES(3333,'ANOTOMY','PEARSON',2010);

BOOK AUTHORS

SQL> INSERT INTO BOOK_AUTHORS VALUES(1111,'SOMMERVILLE');

SQL> INSERT INTO BOOK_AUTHORS VALUES(2222,'NAVATHE');

SQL> INSERT INTO BOOK_AUTHORS VALUES(3333,'HENRY GRAY');

LIBRARY BRANCH

SQL> INSERT INTO LIBRARY_BRANCH VALUES(11,'CENTRAL TECHNICAL','MG ROAD');

SQL> INSERT INTO LIBRARY_BRANCH VALUES(22,'MEDICAL','BH ROAD');

SQL> INSERT INTO LIBRARY_BRANCH VALUES(33,'CHILDREN','SS PURAM');

BOOK COPIES

SQL> INSERT INTO BOOK_COPIES VALUES(1111,11,5);

SQL> INSERT INTO BOOK_COPIES VALUES(3333,22,6);

SQL> INSERT INTO BOOK_COPIES VALUES(4444,33,10);

BOOK ENDING

SQL> INSERT INTO BOOK_LENDING VALUES(2222,11,1,'10-JAN-2017','20-AUG-2017');

SQL> INSERT INTO BOOK_LENDING VALUES(3333,22,2,'09-JUL-2017','12-AUG-2017');

SQL> INSERT INTO BOOK_LENDING VALUES(4444,55,1,'11-APR-2017','09-AUG-2017');

Queries and Solutions

SQL> SELECT * FROM BOOK;

BOOK_ID TITLE PUBLISHER_NAME PUB_YEAR

---------- ----------------- ---------------- --------

1111 SE PEARSON 2005

2222 DBMS MCGRAW 2004

3333 ANOTOMY PEARSON 2010

SQL> SELECT * FROM BOOK_AUTHORS;

BOOK_ID AUTHOR_NAME

------- ------------

1111 SOMMERVILLE

2222 NAVATHE

3333 HENRY GRAY

SQL> SELECT * FROM PUBLISHER;

NAME ADDRESS PHONE

--------------- --------------- ----------

PEARSON BANGALORE 9875462530

MCGRAW NEWDELHI 7845691234

SAPNA BANGALORE 7845963210

SQL> SELECT * FROM BOOK_COPIES;

BOOK_ID BRANCH_ID NO_OF_COPIES

------- --------- ------------

1111 11 5

3333 22 6

4444 33 10

2222 11 12

SQL> SELECT * FROM BOOK_LENDING;

BOOK_ID BRANCH_ID CARD_NO DATE_OUT DUE_DATE

------- --------- -------- --------- ---------

2222 11 1 10-JAN-17 20-AUG-17

3333 22 2 09-JUL-17 12-AUG-17

4444 55 1 11-APR-17 09-AUG-17

2222 11 5 09-AUG-17 19-AUG-17

SQL> SELECT * FROM LIBRARY_BRANCH;

BRANCH_ID BRANCH_NAME ADDRESS

--------- ------------------- ----------

11 CENTRAL TECHNICAL MG ROAD

22 MEDICAL BH ROAD

33 CHILDREN SS PURAM