Foreign key bookid references book bookid on delete

Info icon This preview shows pages 4–7. Sign up to view the full content.

View Full Document Right Arrow Icon
FOREIGN KEY (BookId) REFERENCES BOOK (BookId) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE PUBLISHER ( Name VARCHAR(20) NOT NULL, Address VARCHAR(40) NOT NULL, Phone CHAR(12), PRIMARY KEY (Name) ); CREATE TABLE BOOK_COPIES ( BookId CHAR(20) NOT NULL, BranchId INTEGER NOT NULL, No_Of_Copies INTEGER NOT NULL, PRIMARY KEY (BookId, BranchId), FOREIGN KEY (BookId) REFERENCES BOOK (BookId) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (BranchId) REFERENCES BRANCH (BranchId) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE BORROWER ( CardNo INTEGER NOT NULL, Name VARCHAR(30) NOT NULL, Address VARCHAR(40) NOT NULL, Phone CHAR(12), PRIMARY KEY (CardNo) ); CREATE TABLE BOOK_LOANS ( CardNo INTEGER NOT NULL, BookId CHAR(20) NOT NULL,
Image of page 4

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
BranchId INTEGER NOT NULL, DateOut DATE NOT NULL, DueDate DATE NOT NULL, PRIMARY KEY (CardNo, BookId, BranchId), FOREIGN KEY (CardNo) REFERENCES BORROWER (CardNo) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (BranchId) REFERENCES LIBRARY_BRANCH (BranchId) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (BookId) REFERENCES BOOK (BookId) ON DELETE CASCADE ON UPDATE CASCADE ); CREATE TABLE LIBRARY_BRANCH ( BranchId INTEGER NOT NULL, BranchName VARCHAR(20) NOT NULL, Address VARCHAR(40) NOT NULL, PRIMARY KEY (BranchId) ); 8.11 Write SQL queries for the LIBRARY database queries given in Exercise 6.18. Answer: Below, we give one possible SQL query for each request. Other queries are also possible. (a) How many copies of the book titled The Lost Tribe are owned by the library branch whose name is "Sharpstown"? SELECT NoOfCopies FROM ( (BOOK NATURAL JOIN BOOK_COPIES ) NATURAL JOIN LIBRARY_BRANCH ) WHERE Title='The Lost Tribe' AND BranchName='Sharpstown' (b) How many copies of the book titled The Lost Tribe are owned by each library branch? SELECT BranchName, NoOfCopies FROM ( (BOOK NATURAL JOIN BOOK_COPIES ) NATURAL JOIN LIBRARY_BRANCH ) WHERE Title='The Lost Tribe' (c) Retrieve the names of all borrowers who do not have any books checked out. SELECT Name FROM BORROWER B WHERE NOT EXIST ( SELECT * FROM BOOK_LOANS L WHERE B.CardNo = L.CardNo ) (d) For each book that is loaned out from the "Sharpstown" branch and whose DueDate is today, retrieve the book title, the borrower's name, and the borrower's address. SELECT B.Title, R.Name, R.Address FROM BOOK B, BORROWER R, BOOK_LOANS BL, LIBRARY_BRANCH LB WHERE LB.BranchName='Sharpstown' AND LB.BranchId=BL.BranchId AND BL.DueDate='today' AND BL.CardNo=R.CardNo AND BL.BookId=B.BookId (e) For each library branch, retrieve the branch name and the total number of books loaned out from that branch. SELECT L.BranchName, COUNT(*) FROM BOOK_COPIES B, LIBRARY_BRANCH L WHERE B.BranchId = L.BranchId GROUP BY L.BranchName
Image of page 5
(f) Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out. SELECT B.CardNo, B.Name, B.Address, COUNT(*) FROM BORROWER B, BOOK_LOANS L WHERE B.CardNo = L.CardNo GROUP BY B.CardNo HAVING COUNT(*) > 5 (g) For each book authored (or co-authored) by "Stephen King", retrieve the title and the number of copies owned by the library branch whose name is "Central".
Image of page 6

Info icon This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 7
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern