DATABASE MANAGEMENT SYSTEMS LABORATORY 10CSL57 VTU prg 4

The Eduladder is a community of students, teachers, and programmers just interested to make you pass any exams. So we solve previous year question papers for you.
See Our team
Wondering how we keep quality?
Got unsolved questions?

Ask Questions

Hey, We have just launched eduladder videos.Looking for creators and contributors Read Guideline

You are here:Open notes-->VTU-->DATABASE-MANAGEMENT-SYSTEMS-LABORATORY-10CSL57-VTU-prg-4

DATABASE MANAGEMENT SYSTEMS LABORATORY [10CSL57] VTU prg-4

4. The following tables are maintained by a book dealer.

AUTHOR (author-id:int, name:string, city:string, country:string)    
PUBLISHER (publisher-id:int, name:string, city:string, country:string)
CATALOG (book-id:int, title:string, author-id:int, publisher-id:int, category-id:int, year:int, price:int)
CATEGORY (category-id:int, description:string)
ORDER-DETAILS (order-no:int, book-id:int, quantity:int)
i. Create the above tables by properly specifying the primary keys and the foreign keys.
ii. Enter at least five tuples for each relation.
iii. Give the details of the authors who have 2 or more books in the catalog and the price of the books is greater than the average price of the books in the catalog and the year of publication is after 2000.
iv. Find the author of the book which has maximum sales.
v. Demonstrate how you increase the price of books published by a specific publisher by 10%.
vi. Generate suitable reports.
vii. Create suitable front end for querying and displaying the results.


4. The following tables are maintained by a book dealer.
AUTHOR (author-id:int, name:string, city:string, country:string)
PUBLISHER (publisher-id:int, name:string, city:string, country:string)
CATALOG (book-id:int, title:string, author-id:int, publisher-id:int, category-id:int, year:int, price:int)
CATEGORY (category-id:int, description:string)
ORDER-DETAILS (order-no:int, book-id:int, quantity:int)
i. Create the above tables by properly specifying the primary keys and the foreign keys.
CREATE TABLE AAUTHOR
( AUTHORID INTEGER PRIMARY KEY,
NAME VARCHAR(30) NOT NULL,
CITY VARCHAR(30) NOT NULL,
COUNTRY VARCHAR(30) NOT NULL);
CREATE TABLE PPUBLISHER
(PUBLISHERID INTEGER PRIMARY KEY,
NAME VARCHAR(30) NOT NULL,
CITY VARCHAR(30) NOT NULL,
COUNTRY VARCHAR(30) NOT NULL);
CREATE TABLE BOOKCATEGORY
(CATEGORYID INTEGER PRIMARY KEY,
DESCRIPTION VARCHAR(30) NOT NULL);
CREATE TABLE CCATALOG
(BOOKID INTEGER PRIMARY KEY,
TITLE VARCHAR(30) NOT NULL,
AUTHORID INTEGER NOT NULL,
PUBLISHERID INTEGER NOT NULL,
CATEGORYID INTEGER NOT NULL,
YEAROFPUBLISH INTEGER NOT NULL,
PRICE INTEGER NOT NULL,
FOREIGN KEY (AUTHORID) REFERENCES AAUTHOR(AUTHORID),
FOREIGN KEY (PUBLISHERID) REFERENCES PPUBLISHER(PUBLISHERID),
FOREIGN KEY (CATEGORYID) REFERENCES BOOKCATEGORY(CATEGORYID));
CREATE TABLE OORDERDETAILS
(ORDERNO INTEGER PRIMARY KEY,
BOOKID INTEGER NOT NULL,
QUANTITY INTEGER NOT NULL,
FOREIGN KEY (BOOKID) REFERENCES CCATALOG(BOOKID));

ii. Enter at least five tuples for each relation.

INSERT INTO AAUTHOR VALUES (1,'NAVATHE','ARLINGTON','USA');
INSERT INTO AAUTHOR VALUES (2,'RAGHU RAMAKRISHNAN','CALIFORNIA','USA');
INSERT INTO AAUTHOR VALUES (3,'DHAMDHERE','MUMBAI','INDIA');
INSERT INTO AAUTHOR VALUES (4,'BJARNE','NEW JERSY','USA');
INSERT INTO AAUTHOR VALUES (5,'TANENBAUM','AMSTERDAM','NETHERLAND');
INSERT INTO PPUBLISHER VALUES (1,'JOHN WILEY','NEW YORK','USA');
INSERT INTO PPUBLISHER VALUES (2,'PEARSON','BANGALORE','INDIA');
INSERT INTO PPUBLISHER VALUES (3,'O REILLY','NEW JERSY','USA');
INSERT INTO PPUBLISHER VALUES (4,'TMH','CALCUTTA','INDIA');
INSERT INTO PPUBLISHER VALUES (5,'JOHN WILEY','NEW DELHI','INDIA');
INSERT INTO BOOKCATEGORY VALUES (1,'DATABASE MANAGEMENT');
INSERT INTO BOOKCATEGORY VALUES (2,'OPERATING SYSTEMS');
INSERT INTO BOOKCATEGORY VALUES (3,'C++');
INSERT INTO BOOKCATEGORY VALUES (4,'COMPUTER NETWORKS');
INSERT INTO BOOKCATEGORY VALUES (5,'C');
INSERT INTO CCATALOG VALUES (1,'FUNDAMENTALS OF DBMS',1,2,1,2004,500);
INSERT INTO CCATALOG VALUES (2,'PRINCIPLES OF DBMS',2,1,1,2004,400);
INSERT INTO CCATALOG VALUES (3,'OPERATING SYSTEMS',3,4,2,2004,200);
INSERT INTO CCATALOG VALUES (4,'C++ BIBLE',4,5,3,2003,500);
INSERT INTO CCATALOG VALUES (5,'COMPUTER NETWORKS',5,3,4,2002,250);
INSERT INTO CCATALOG VALUES (6,'FUNDAMENTALS OF C',1,2,5,2004,700);
INSERT INTO CCATALOG VALUES (7,'OPERATING SYSTEMS 2',3,2,2,2001,600);
INSERT INTO OORDERDETAILS VALUES (1,1,1);
INSERT INTO OORDERDETAILS VALUES (2,2,1);
INSERT INTO OORDERDETAILS VALUES (3,3,1);
INSERT INTO OORDERDETAILS VALUES (4,4,1);
INSERT INTO OORDERDETAILS VALUES (5,5,1);
INSERT INTO OORDERDETAILS VALUES (6,6,7);
INSERT INTO OORDERDETAILS VALUES (7,7,9);
iii. Give the details of the authors who have 2 or more books in the catalog and the price of the books is greater than the average price of the books in the catalog and the year of publication is after 2000.
SELECT *
FROM AAUTHOR A
WHERE EXISTS
(SELECT A1.AUTHORID,COUNT(A1.AUTHORID)
FROM AAUTHOR A1,CCATALOG C
WHERE A1.AUTHORID=C.AUTHORID AND
A.AUTHORID=A1.AUTHORID AND
C.YEAROFPUBLISH > 2000 AND
C.PRICE > (SELECT AVG(PRICE)
FROM CCATALOG)
GROUP BY A1.AUTHORID
HAVING COUNT(A1.AUTHORID) >=2);

iv. Find the author of the book which has maximum sales.

SELECT DISTINCT A.NAME
FROM AAUTHOR A, CCATALOG C, OORDERDETAILS ODM
WHERE A.AUTHORID=C.AUTHORID AND ODM.BOOKID=C.BOOKID AND
EXISTS
(SELECT OD.BOOKID,SUM(OD.QUANTITY)
FROM OORDERDETAILS OD
WHERE OD.BOOKID=ODM.BOOKID
GROUP BY BOOKID
HAVING SUM(OD.QUANTITY)>= ALL
(SELECT SUM(QUANTITY)
FROM OORDERDETAILS
GROUP BY BOOKID));
v. Demonstrate how you increase the price of books published by a specific publisher by 10%.
UPDATE CCATALOG
SET PRICE = (1.1) * PRICE
WHERE AUTHORID = (SELECT AUTHORID
FROM AAUTHOR
WHERE NAME = 'NAVATHE');
vi. Generate suitable reports.
vii. Create suitable front end for querying and displaying the results




Editors




Join eduladder!