DBMS 1 - LIBRARY DATABASE

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.

ENTITY RELATIONSHIP DIAGRAM

 SCHEMA DIAGRAM

CREATION OF TABLES

-- 1

CREATE TABLE PUBLISHER

(NAME VARCHAR(20) PRIMARY KEY,

PHONE INTEGER,

ADDRESS VARCHAR(20));

DESC PUBLISHER;

-- 2

CREATE TABLE BOOK

(BID INTEGER PRIMARY KEY,

TITLE VARCHAR(20),

PUBYEAR VARCHAR(20),

PUBNAME REFERENCES PUBLISHER(NAME) ON DELETE CASCADE);

DESC BOOK;

-- 3

CREATE TABLE BOOK_AUTHORS

(ANAME VARCHAR(20),

BID REFERENCES BOOK(BID) ON DELETE CASCADE,

PRIMARY KEY(BID, ANAME));

DESC BOOK_AUTHORS;

-- 4

CREATE TABLE LIBRARY_BRANCH

(BID INTEGER PRIMARY KEY,

BNAME VARCHAR(50),

ADDRESS VARCHAR(50));

DESC LIBRARY_BRANCH;

-- 5

CREATE TABLE BOOK_COPIES

(NOOFCOPIES INTEGER,

BID REFERENCES BOOK(BID) ON DELETE CASCADE,

BRANCHID REFERENCES LIBRARY_BRANCH(BRANCHID) ON DELETE CASCADE,

PRIMARY KEY (BID,BRANCHID));

DESC BOOK_COPIES;

-- 6

CREATE TABLE CARD

(CARDNO INTEGER PRIMARY KEY);

DESC CARD;

-- 7

CREATE TABLE BOOKLENDING

(DATEOUT DATE,

DUEDATE DATE,

BID REFERENCES BOOK(BID) ON DELETE CASCADE,

BRANCHID REFERENCES LIBRARY_BRANCH(BRANCHID) ON DELETE CASCADE,

CARDNO REFERENCES CARD(CARDNO) ON DELETE CASCADE,

PRIMARY KEY (BID,BRANCHID,CARDNO));

DESC BOOKLENDING;

INSERTION OF TABLES

-- 1

INSERT INTO PUBLISHER VALUES('MCGRAW-HILL',9191919191,'BANGALORE');

INSERT INTO PUBLISHER VALUES('PEARSON',8181818181,'NEWDELHI');

INSERT INTO PUBLISHER VALUES('RANDOM HOUSE',7171717171,'HYDERABAD');

INSERT INTO PUBLISHER VALUES('LIVRE',6161616161,'CHENNAI');

INSERT INTO PUBLISHER VALUES('PLANETA',5151515151,'BANGALORE');

SELECT * FROM PUBLISHER;

-- 2

INSERT INTO BOOK VALUES(1,'DBMS','JAN-2017','MCGRAW-HILL');

INSERT INTO BOOK VALUES(2,'ADBMS','JUN-2016','MCGRAW-HILL');

INSERT INTO BOOK VALUES(3,'CN','SEP-2016','PEARSON');

INSERT INTO BOOK VALUES(4,'CG','SEP-2015','PLANETA');

INSERT INTO BOOK VALUES(5,'OS','MAY-2016','PEARSON');

SELECT * FROM BOOK;

-- 3

INSERT INTO BOOK_AUTHORS VALUES('NAVATHE',1);

INSERT INTO BOOK_AUTHORS VALUES('NAVATHE',2);

INSERT INTO BOOK_AUTHORS VALUES('TANENBAUM',3);

INSERT INTO BOOK_AUTHORS VALUES('EDWARD ANGEL',4);

INSERT INTO BOOK_AUTHORS VALUES('GALVIN',5);

SELECT * FROM BOOK_AUTHORS;

-- 4

INSERT INTO LIBRARY_BRANCH VALUES(10,'RR NAGAR','BANGALORE');

INSERT INTO LIBRARY_BRANCH VALUES(11,'RNSIT','BANGALORE');

INSERT INTO LIBRARY_BRANCH VALUES(12,'RAJAJI NAGAR','BANGALORE');

INSERT INTO LIBRARY_BRANCH VALUES(13,'NITTE','MANGALORE');

INSERT INTO LIBRARY_BRANCH VALUES(14,'MANIPAL','UDUPI');

SELECT * FROM LIBRARY_BRANCH;

-- 5

INSERT INTO BOOK_COPIES VALUES(10,1,10);

INSERT INTO BOOK_COPIES VALUES(5,1,11);

INSERT INTO BOOK_COPIES VALUES(2,2,12);

INSERT INTO BOOK_COPIES VALUES(5,2,13);

INSERT INTO BOOK_COPIES VALUES(7,3,14);

INSERT INTO BOOK_COPIES VALUES(1,5,10);

INSERT INTO BOOK_COPIES VALUES(3,4,11);

SELECT * FROM BOOK_COPIES;

-- 6

INSERT INTO CARD VALUES(100);

INSERT INTO CARD VALUES(101);

INSERT INTO CARD VALUES(102);

INSERT INTO CARD VALUES(103);

INSERT INTO CARD VALUES(104);

SELECT * FROM CARD;

-- 7

INSERT INTO BOOKLENDING VALUES('01-JAN-17','01-JUN-17',1,10,101);

INSERT INTO BOOKLENDING VALUES('11-JAN-17','11-MAR-17',3,14,101);

INSERT INTO BOOKLENDING VALUES('21-FEB-17','21-APR-17',2,13,101);

INSERT INTO BOOKLENDING VALUES('15-MAR-17','15-JUL-17',4,11,101);

INSERT INTO BOOKLENDING VALUES('12-APR-17','12-MAY-17',1,11,104);

SELECT * FROM BOOKLENDING;

--QUERIES

-- QUERY 1

SELECT B.BID, B.TITLE, B.PUBNAME, A.ANAME,C.NOOFCOPIES,L.BRANCHID

FROM BOOK B, BOOK_AUTHORS A, BOOK_COPIES C, LIBRARY_BRANCH LIBRARY_BRANCH LIBRARY_BRANCH L

WHERE B.BID=A.BID

AND B.BID=C.BID

AND L.BRANCHID=C.BRANCHID;

 -- QUERY 2

SELECT CARDNO

FROM BOOKLENDING

WHERE DATEOUT BETWEEN '01-JAN-17' AND '01-JUN-17'

GROUP BY CARDNO

HAVING COUNT(*)>3;

-- QUERY 3

DELETE FROM BOOK

WHERE BID = 3;

-- QUERY 4

CREATE VIEW VPUBLICATION AS

SELECT PUBYEAR

FROM BOOK;

-- QUERY 5

CREATE VIEW VBOOKS AS

SELECTB.BID, B.TITLE, C.NOOFCOPIES

FROM

BOOK B, BOOK_COPIES C, LIBRARY_BRANCH L

WHEREB.BID=C.BID

AND C.BRANCHID=L.BRANCHID;