Question
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,
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
17. List all books that patrons are waiting for with the book number, title, patron number, name, and branch name.
18. Show all the reviews for each book with the book number, title, review, reviewer name, and date of the review.
19. Show all the categories for each book with the book number, title, category number, and category name.
20. Show all the books for each category with the category number, category name, book number, and book title.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started