Slip5
Consider the following Entities and Relationships
Book (Book_no, title, author, price, year_published)
Customer (cid, cname, addr)
Relation between Book and Customer is Many to Many with quantity as
descriptive attribute.Constraint: Primary key, price should be >0.
Solution:-
Create a Database in 3NF & write queries for following.
•Display customer details from 'Mumbai'.
SQL>Select * from Customerb where addr='mumbai';
•Display author wise details of book.
SQL>Select author ,title from BOOK order by author;
•Display all customers who have purchased the books published in the year 2013.
SQL>Select * from Customerb,BOOK, Customerb_BOOK where Customerb.cid=Customerb_BOOK.cid and BOOK.BNO= Customerb_BOOK.BNO and year_published='2013';
•Display customer name that has purchased more than 3 books.
SQL>select count(BOOK.BNO),cname from Customerb,BOOK, Customerb_BOOK where Customerb.cid=Customerb_BOOK.cid and BOOK.BNO=Customerb_BOOK.BNO and BOOK.BNO>3 group by cname;
•Displaybook names having price between 100 and 200 and published in the year 2013.
SQL>Select BOOK.title from Customerb,BOOK, Customerb_BOOK where price>=100 and price<=200 and Customerb.cid=Customerb_BOOK.cid and BOOK.BNO= Customerb_BOOK.BNO;
