This project will utilize SQL to create user views for the provided set of queries.

EXAMPLE:

1. List all libraries with the library name, address, and telephone number.

CREATE VIEW LibraryNames (Number, Name, Address, Phone) AS

SELECT Library#, LibraryName, Address, Phone

FROM LIBRARY

COUNTY LIBRARY SYSTEM

Use the following relations for the SQL queries for this project.

LIBRARY (LIBRARY_ID, LIBRARY_NAME, STREET_ADDRESS, CITY, STATE, ZIPCODE, PHONE, MANAGER_NAME)

BRANCH (BRANCH_ID, BRANCH_NAME, STREET_ADDRESS, CITY, STATE, ZIPCODE, PHONE, BRANCH_MANAGER, LIBRARY_ID)

FK LIBRARY_ID -> LIBRARY

PUBLISHER (PUBLISHER_ID, PUBLISHER_NAME, STREET_ADDRESS, CITY, STATE, ZIPCODE, PHONE)

BOOK (BOOK_ID, BOOK_TITLE, PUBLICATION_DATE, PUBLISHER_ID, PRECIS)

FK PUBLISHER_ID -> PUBLISHER

AUTHOR (AUTHOR_ID, FIRST_NAME, LAST_NAME, DATE_OF_BIRTH, DATE_OF_DEATH)

BOOKAUTHOR (BOOK_ID, AUTHOR_ID)

FK BOOK_ID -> BOOK

FK AUTHOR_ID -> AUTHOR

COPY (COPY_ID, BOOK_ID, COST, BRANCH_ID)

FK BOOK_ID -> BOOK

FK BRANCH_ID -> BRANCH

CATEGORY (CATEGORY_ID, CATEGORY_NAME)

BOOK_CATEGORY (BOOK_ID, CATEGORY_ID)

FK BOOK_ID -> BOOK

FK CATEGORY_ID -> CATEGORY

REVIEWER (REVIEWER_ID, FIRST_NAME, LAST_NAME)

REVIEW (REVIEW_ID, BOOK_ID, REVIEWER_ID, REVIEW_DATE, REVIEW)

FK BOOK_ID -> BOOK

FK REVIEWER_ID -> REVIEWER

PATRON (PATRON_ID, FIRST_NAME, LAST_NAME, STREET_ADDRESS, CITY, STATE, ZIPCODE, PHONE, FEE_BALANCE)

CHECKOUT (CHECKOUT_ID, COPY_ID, PATRON_ID, OUT_DATE, DUE_DATE, RETURN_DATE)

FK COPY_ID -> COPY

FK PATRON_ID -> PATRON

WAITLIST (BOOK_ID, PATRON_ID, BRANCH_ID, ON_DATE, OFF_DATE,)

FK BOOK_ID -> BOOK

FK PATRON_ID -> PATRON

FK BRANCH_ID -> BRANCH

QUERIES REQUIRED:

12. List the books that each patron currently has checked out with the patron number, patron name, telephone number, book number, and title.

Select PATRON_ID,FIRST_NAME,PHONE,BOOK_ID,BOOK_TITLE

From PATRON,CHECKOUT,COPY,BOOK

where PATRON.PATRON_ID=CHECKOUT.PATRON_ID

AND CHECKOUT.COPY_ID=COPY.COPY_ID

AND COPY.BOOK_ID=BOOK.BOOK_ID

13. Same as #12 except with the addition of the branch number and name.

14. Same as #12 except that the list should be for any book the patron has ever checked out.

15. Same as #12 except for all books that are past due along with the past due fee.

16. List the current total of past due fees for each library for all books that are still out.