dba1a

 Publisher Table –

CREATE TABLE PUBLISHER(
PUB_ID INT PRIMARY KEY,
PUB_NAME VARCHAR(20) UNIQUE,
ADDRESS VARCHAR(20),
PHONE INT);

 Book Table –

CREATE TABLE BOOK(
BOOK_ID INT PRIMARY KEY,
TITLE VARCHAR(20),
PUB_NAME VARCHAR(20),
FOREIGN KEY (PUB_NAME) REFERENCES PUBLISHER (PUB_NAME) ON DELETE
CASCADE,
PUB_YEAR INT);

 Library Program Table –

CREATE TABLE LIBRARY_PROGRAM(
PGM_ID INT PRIMARY KEY,
PGM_NAME VARCHAR(20),
ADDRESS VARCHAR(30));

 Book Authors Table –

CREATE TABLE BOOK_AUTHORS(
BOOK_ID INT,
AUTHOR_NAME VARCHAR(20),
PRIMARY KEY(BOOK_ID, AUTHOR_NAME),
FOREIGN KEY(BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE);

 Book Copies Table –

CREATE TABLE BOOK_COPIES(
BOOK_ID INT,
PGM_ID INT,
NO_OF_COPIES INT,
PRIMARY KEY(BOOK_ID, PGM_ID),
FOREIGN KEY (BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,
FOREIGN KEY (PGM_ID) REFERENCES LIBRARY_PROGRAM(PGM_ID) ON DELETE
CASCADE);

 Book Lending Table –

CREATE TABLE BOOK_LENDING(
BOOK_ID INT,
PGM_ID INT,
CARD_NO INT,
DATE_OUT DATE,
DUE_DATE DATE,
PRIMARY KEY(BOOK_ID,PGM_ID,CARD_NO),
FOREIGN KEY (BOOK_ID) REFERENCES BOOK(BOOK_ID) ON DELETE CASCADE,
FOREIGN KEY (PGM_ID) REFERENCES LIBRARY_PROGRAM(PGM_ID) ON DELETE
CASCADE);

Query to insert values into the table:

 Publisher table –
INSERT INTO PUBLISHER VALUES (501,'KVS','BANGALORE',9535616745);
INSERT INTO PUBLISHER VALUES (502,'WESTLAND','PUNE',8768916745);
INSERT INTO PUBLISHER VALUES (503,'RUPA','BANGALORE',6478989715);
INSERT INTO PUBLISHER VALUES (504,'GANGA','MUMBAI',9876985645);
INSERT INTO PUBLISHER VALUES (505,'HACHETTE','MATTUR',7013458745);

 Book Table –
INSERT INTO BOOK VALUES (001,'MCGRAW-HILL','GANGA',2001);
INSERT INTO BOOK VALUES (002,'MY ARTEMIS','KVS',2004);
INSERT INTO BOOK VALUES (003,'CHEMISTRY VOL 1','WESTLAND',2006);
INSERT INTO BOOK VALUES (004,'UPRISING','RUPA',2018);
INSERT INTO BOOK VALUES (005,'CHEMISTRY VOL 2','WESTLAND',2021);

 Library Program Table –
INSERT INTO LIBRARY_PROGRAM VALUES (101,'BOOK AXIS','BANGALORE');
INSERT INTO LIBRARY_PROGRAM VALUES (102,'BOOK SQUARE','PUNE');
INSERT INTO LIBRARY_PROGRAM VALUES (103,'CLAUS BOOKS','MUMBAI');
INSERT INTO LIBRARY_PROGRAM VALUES (104,'COMIC CON','PUNE');
INSERT INTO LIBRARY_PROGRAM VALUES (105,'FANDOM','BANGALORE');

 Book Authors Table –
INSERT INTO BOOK_AUTHORS VALUES (001, 'ASHISH C');
INSERT INTO BOOK_AUTHORS VALUES (002, 'ANEESHA');
INSERT INTO BOOK_AUTHORS VALUES (003, 'ADITYA KUL C');
INSERT INTO BOOK_AUTHORS VALUES (004, 'SAQUIB M');
INSERT INTO BOOK_AUTHORS VALUES (005, 'ARJUN S');

 Book Copies Table –
INSERT INTO BOOK_COPIES VALUES (001, 102, 40);
INSERT INTO BOOK_COPIES VALUES (002, 101, 18);
INSERT INTO BOOK_COPIES VALUES (003, 104, 53);
INSERT INTO BOOK_COPIES VALUES (004, 103, 4);
INSERT INTO BOOK_COPIES VALUES (005, 105, 20);

 Book Lending Table –
INSERT INTO BOOK_LENDING VALUES (001, 101, 5001, '21-SEP-2021', '19-OCT-2021');
INSERT INTO BOOK_LENDING VALUES (001, 102, 5002, '07-JAN-2017', '18-MAY-2017');
INSERT INTO BOOK_LENDING VALUES (002, 102, 5003, '02-FEB-2017', '22-MAR-2020');
INSERT INTO BOOK_LENDING VALUES (003, 103, 5004, '14-SEP-2016', '08-OCT-2021');
INSERT INTO BOOK_LENDING VALUES (005, 104, 5005, '18-JUN-2020', '14-AUG-2021');
INSERT INTO BOOK_LENDING VALUES (002, 102, 5002, '07-JAN-2017', '18-MAY-2017');
INSERT INTO BOOK_LENDING VALUES (003, 102, 5002, '02-FEB-2017', '22-MAR-2020');
INSERT INTO BOOK_LENDING VALUES (004, 102, 5002, '14-MAR-2017', '08-MAY-2019');

Query for given questions:

1) SELECT LP.PGM_NAME, B.BOOK_ID,TITLE, PUB_NAME, AUTHOR_NAME,
NO_OF_COPIES
FROM BOOK B, BOOK_AUTHORS BA, BOOK_COPIES BC, LIBRARY_PROGRAM LP,
PUBLISHER P
WHERE B.BOOK_ID = BA.BOOK_ID AND
BA.BOOK_ID = BC.BOOK_ID AND
BC.PGM_ID = LP.PGM_ID
GROUP BY LP.PGM_NAME, B.BOOK_ID, TITLE,
PUB_NAME, AUTHOR_NAME, NO_OF_COPIES;

2) SELECT CARD_NO FROM BOOK_LENDING
WHERE DATE_OUT > '01-JAN-2017' AND
DATE_OUT < '01-JUN-2017'
GROUP BY CARD_NO
HAVING COUNT (*) > 3;

3) DELETE FROM BOOK WHERE BOOK_ID = 001;

4) SELECT PUB_YEAR FROM BOOK;

5) CREATE VIEW BOOKS_AVAILABLE AS
SELECT B.BOOK_ID, B.TITLE, C.NO_OF_COPIES
FROM LIBRARY_PROGRAM L, BOOK B, BOOK_COPIES C
WHERE B.BOOK_ID = C.BOOK_ID AND
L.PGM_ID=C.PGM_ID;
SELECT * FROM BOOKS_AVAILABLE;