Relational Database Schema for a Library Database: BOOK(BKID, BK_TITLE, BK_LIST_
ID: 3796953 • Letter: R
Question
Relational Database Schema for a Library Database: BOOK(BKID, BK_TITLE, BK_LIST_PRICE, PBID) PUBLISHERCPBID, PB.NAME, PB_STREET, PB_ZIPCODE, PB.PHONE) AUTHOR(AUID, AU_NAME, AU_DOB) LIBRARY_BRANCH(LBID, LB_NAME, LB_STREET, LB_ZIPCODE) BORROWER(CRID, CR_NAME, CR_DOB, CR_STREET, CR_ ZIPCODE) ZIPCODE(ZIPCODE, CITY, STATE) BOOK_COPIES(BKID, LBID, NO_OF_COPIES) BOOK_AUTHORS(BKID, AUID) BOOK_LOANS(BK_ID, LB_ID, CR_ID, DATE_OUT, DUE_DATE) For each query below, write the Relational Algebra statement(s): List all the attributes for each Author born after January 18, 1976. List the Nome and Date-of-Birth for each Author. List the Title, List Price and Publisher's Name for each Book. List the ID & Name of each Publisher who has published at least one Book whose List Price was more than $200.00. List the ID & Name of each Publisher who has never published a Book whose List Price was more than $200.00. List the ID & Name of each Publisher who only publishes Books whose List Price is more than $200.00. List the Title, List Price and Publisher's Name for those Books whose List Price is less than $50.00 and the Publisher's Zipcode is 23284. List the Title, List Price and Publisher's Name for those Books whose List Price is less than $50.00 or the Publisher's Zipcode is 23284. List the Names and Date-of-Birth of those Borrowers who have borrowed at least one Book written by the Author whose Name is Marcus Garvey.Explanation / Answer
1. SELECT * FROM AUTHOR WHERE au_dob > '18/01/1986';
2. SELECT AU_NAME,AU_DOB FROM AUTHOR;
3. SELECT BOOK.BK_TITLE, BOOK.BK_LIST_PRICE, PUBLISHER.PB_NAME FROM (BOOK, PUBISHER) where BOOK.PBID = PUBLISHER.PBID;
4. SELECT DISTINCT PUBLISHER.PBID, PUBLISHER.PB_NAME from (PUBLISHER, BOOK) where BOOK.PBID=PUBLISHER.PBID and BOOK.BK_LIST_PRICE > 200;