We are building EduLadder(ELADR) - Protocol

The Eladr Protocol is a decentralized, security and efficiency enhanced Web3 noSQL database powered by IPFS as the data storage layer https://ipfs.io/, and the Cardano block chain as the rewards token platform, https://cardano.org/. It provides a JSON based, IPFS layer 2 solution for data indexing and retrieval in an 'append only' file system built with open source Node.js API libraries.

The ELADR token was designed to incentivize and reward community members as a proof of contribution. Token holders are also granted access to EduLadder.com premium features as well as associated ELADR token enabled apps.

WHITE PAPER Buy Now

Real Problems! Real Experts!

Join Our Telegram Channel !


The Eduladder is a community of students, teachers, and programmers. We help you to solve your academic and programming questions fast.
In eduladder you can Ask,Answer,Listen,Earn and Download Questions and Question papers.
Watch related videos of your favorite subject.
Connect with students from different parts of the world.
Apply or Post Jobs, Courses ,Internships and Volunteering opportunity. For FREE
See Our team
Wondering how we keep quality?
Got unsolved questions? Ask Questions
ELADR beta version launched

We launched Anonymous immutable internet on eladr protocol

For any question or query please joinOur Telegram Channel !


Try BETA
Youtube Videohttps://www.youtube.com/watch?v=ySLPZu3Jxro

Our Github Repo
FrontEnd BackEnd

We are looking for some great and cool people to work with us. Please sent your resume to admin@eduladder.com


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




You might like this video:Watch more here

Watch more videos from this user Here

Learn how to upload a video over here