CPSC 343 Database Theory and Practice Fall 2017

Views

Queries

Express each of the following queries using SQL. (Use only constructs supported by MySQL.) Make sure that your queries work no matter what rows are in the tables. Furthermore, there should be no duplicate column names in your results and all columns should have descriptive names. Finally, there should be no duplicates in the final result, but duplicate-elimination has a cost and so should only be used if duplicates can actually occur.

Any correct query which satisfies the points above is acceptable, but you should try to write as simple a solution as you can. (Simpler solutions are easier to reason about in order to check their correctness.)

BOOK(Book_id,Title,Publisher_name)
BOOK_AUTHORS(Book_id,Author_name)
PUBLISHER(Name,Address,Phone)
BOOK_COPIES(Book_id,Branch_id,No_of_copies)
BOOK_LOANS(Book_id,Branch_id,Card_no,Date_out,Due_date)
LIBRARY_BRANCH(Branch_id,Branch_name,Address)
BORROWER(Card_no,Name,Address,Phone)

Key attributes are underlined. The foreign key constraints are the following:

You can use the ex_library database to try out your queries.

  1. Retrieve information about all of the books: book id, title, author, publisher name, publisher address, publisher phone.

  2. Retrieve information about checked-out books: book id, title, library branch name, branch id, branch address, borrower name, borrower address, borrower card number, date out, date due.

  3. Retrieve information about each borrower's borrowings: borrower's name, card number, address, phone number, and number of books checked out.

  4. Find the high-volume borrowers: get the borrower's name, card number, address, phone number, and number of books checked out for borrowers who have checked out more than 10 books.

  5. Retrieve information about what books are currently available: library branch name, branch id, book id, book title, and the number of available copies of that book at that branch (= number of copies - number of copies checked out). This should include information about all books that a library owns at least one copy of (even if all those copies are checked out), and no information about books that branch does not own a copy of.

Creating a View

Views are relations derived from other relations (views or tables) in the database. They are specified by a query which defines the content of the view, and the system handles making sure this content is updated if the underlying tables change. Views can be used to simplify query-writing.

Most views are virtual views, where only the query that defines the view is stored. The actual relation is constructed as part of processing a query involving the view, and so the view does not take up system resources like tables do. There are also materialized views, in which the relations are actually constructed and maintained by the database. Materialized views also speed up query execution because the relation has already been created. MySQL does not support materialized views.

For this part, you will be working with your own copy of the library database example, named username_library2, where username is your user name. (You should have populated this database with data when you worked on the data modification exercises - if you haven't done that, do that so that you'll have some data to work with.) Start up MySQL Workbench if you haven't already, and make this the default schema.

Many queries in this database involve book information (author, title, and/or publisher) but this information is found in different tables. To create a view which brings it all together:

A tab will now open containing the beginnings of the CREATE VIEW command.

If it worked, you should see BOOK_INFO listed under the "Views" item under the database listing under "Schemas". You can also close the view-creation tab.

To test your new view, write and execute a query to retrieve all of the data in BOOK_INFO. (Or just right-click on the view name under "Schemas" and choose "Select Rows".)

If you need to edit the view, right-click on the view name under "Schemas" and choose "Alter View...". The CREATE VIEW command is displayed all on one line, but you can insert line breaks to make it easier to read while you edit. Modify the command as desired, then click "Apply". Close the view editor tab when you are done.

Repeat the same process to create four additional views, one for each of the queries you wrote for #2-5. Give each view a descriptive name.

A Few More Queries

Express each of the following queries using SQL. (Use only constructs supported by MySQL.) Make sure that your queries work no matter what rows are in the tables. Any correct query is acceptable as long as it satisfies these points, though you should always try to write as simple a solution as you can - and use the views you've constructed where appropriate!

  1. Find how many books each borrower has checked out.

  2. Find how many books each borrower has checked out for each author.

  3. Find the library branches and books for which all of the copies of that book at that branch are currently checked out.

  4. Find the author with the highest number of available copies of their books across all of the library branches. Include all the relevant authors if there is a tie.


Checking Your Work

Check back for solutions.


Valid HTML 4.01!