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

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, 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

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

2. List all branches with the branch number, name, address, and name of the manager.

3. List all the books in the library system with the number, title, publication date, and précis.

4. List all the patrons with their number, name, and address.

5. List all authors who are still living with their number, name, and birth date.

Explanation / Answer

1)

CREATE VIEW LibraryNames (Name, Address, Phone) AS
SELECT Library_Name as Name,
Street_address ||','|| City ||',' || State ||','|| Zipcode As Address,
Phone FROM LIBRARY;

2)
CREATE VIEW Branches (Branch_number, Name, Address, Manager) AS
SELECT BRANCH_ID AS BRANCH_NUMBER,
BRANCH_NAME AS NAME, Street_address ||','|| City ||',' || State ||','|| Zipcode As Address,
BRANCH_MANAGER AS MANAGER
FROM BRANCH;

3)
CREATE VIEW BOOKS (NUMBER, TITLE, PUBLICATION_DATE, PRECIS) AS
SELECT BOOK_ID AS NUMBER, BOOK_TITLE AS TITLE, PUBLICATION_DATE, PRECIS FROM BOOK;

4)
CREATE VIEW PATRONS (NUMBER, NAME, ADDRESS) AS
SELECT PATRON_ID AS NUMBER, FIRST_NAME ||' '||LAST_NAME AS NAME,
Street_address ||','|| City ||',' || State ||','|| Zipcode As Address
FROM PATRON;

5)
CREATE VIEW AUTHORS (NUMBER, NAME, DOB) AS
SELECT AUTHOR_ID AS NUMBER,
FIRST_NAME ||' '|| LAST_NAME AS NAME,
DATE_OF_BIRTH AS DOB
FROM AUTHOR WHERE (DATE_OF_DEATH IS NULL);

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.