Homework 4: Relational Algebra and Calculus

In this homework you will practice writing relational algebra and calculus expressions.

Instructions

You should work on this assignment individually.

Problem 1: Relational Algebra

Consider the LIBRARY relational database schema shown below, which is used to keep track of books, borrowers, and book loans. Referential integrity constraints are shown as directed arcs. Write relational algebra expressions for the following queries:

  1. How many copies of the book titled “The Lost Tribe” are owned by the library branch whose name is ‘Sharpstown’?
  2. How many copies of the book titled “The Lost Tribe” are owned by each library branch?
  3. Retrieve the names of all borrowers who do not have any books checked out.
  4. For each book that is loaned out from the Sharpstown branch and whose Due_date is today, retrieve the book title, the borrower’s name, and the borrower’s address.
  5. For each library branch, retrieve the branch name and the total number of books loaned out from that branch.

Hint: When a foreign key references an attribute with the same name, you should join tables with a “natural join.”

Figure 8.14

Problem 2: Tuple Relational Calculus

Using the same LIBRARY schema as in the previous problem, write the following queries using tuple relational calculus:

  1. Retrieve the names of all books authored or coauthored by Stephen King.
  2. Retrieve the names of all borrowers who do not have any books checked out.
  3. For each book that is loaned out from the Sharpstown branch and whose Due_date is today, retrieve the book title, the borrower’s name, and the borrower’s address.

Submit

Upload your answers as a PDF using this LaTeX template. Include your answers within each question block. Do not make other changes to the template (e.g., don’t include your name). Each question block has a fixed position on the page, which helps with grading.

Note: If you are having difficulty using LaTeX, you can use a PDF editor to add your responses to the LaTeX template. However, I strongly recommend against this, since it is worth the investment to learn LaTeX.