lab1

COSC 304 - Introduction to Database Systems
Lab 1: Querying using Relational Algebra

RelaX - Online Relational Algebra Tool

RelaX is an online relational algebra tool that we will use for the assignments. It allows entering relational algebra expressions and executing them to get results. The data set can be loaded from a GitHub Gist including the Bookstore data set (GIST: 367f41bb51110ef3c84bb5f906f2fb87) used for sample relational algebra queries in this lab.

Relational Algebra Practice

This lab has a practice component that is not graded and an assignment component.

The lab practices writing queries in relational algebra using a book database schema (GIST: 367f41bb51110ef3c84bb5f906f2fb87):

Book (isbn, pubDate, listPrice, publisher, title, copyright)
Author (firstName, lastName, address)
BookAuthor (isbn, firstName, lastName)
Bookstore (name, address)
Stock (storeName, isbn, storePrice, quantity)

The Author relation gives the author's first and last names and address. Each first name/last name pair is unique. The Book relation gives the isbn, title, copyright date, publication date, and recommended list price. Since a book can have multiple authors, the relation BookAuthor matches up authors (identified by name) with books (identified by ISBN). The Bookstore relation gives the name (key) and address of a book store. The Stock relation gives the bookstore name, the price, and the ISBN number of the book. listPrice and storePrice are real numbers. copyright and pubDate are integers (representing the year). All other attributes are strings.

Questions: (Write the answer in relational algebra)

1. Return all books (ISBN only) with a list price over $50.
2. Return all books (ISBN only) with a publish date before May. 11, 2020 or whose publisher is 'GenCo'.
3. Return all addresses (both for authors and bookstores).
4. Return all authors that have not published a book.
5. Return the list of books written by Elle Padimi.
6. Return the list of books written by Elle Padimi that cost less than $70.
7. Find all authors (firstName, lastName) who have written books that have been published after July 1, 2020.
8. Find all authors who have written more than one book.
9. Find pairs of books with different ISBNs but the same title. A pair should be listed only once; e.g., list (i,j) but not (j,i).
10. List all the books (ISBN only) that 'All Books' sells that 'Some Books' also sells.
11. List all the books (ISBN only) that 'All Books' sells that 'Some Books' sells for less.
12. Open question: You suggest an English question, and let's try answer it using relational algebra. You do not have to have an answer to your own question, but hopefully, you think you can answer it. Also, note that we cannot answer all questions using the subset of relational algebra that we have studied.

Answers:

1. π _isbn (σ_{listPrice > 50} (Book))
2. π _isbn (σ_{pubDate < date('2020-05-11') OR publisher = 'GenCo'} (Book))
3. π _address (Author) ∪   π_address (Bookstore)
4. π _{firstName, lastName} (Author) - π_{firstName, lastName} (BookAuthor)
5. π _isbn (σ_{firstName = 'Elle' and lastName = 'Padimi'} (BookAuthor))
6. π _isbn (σ_{firstName = 'Elle' and lastName = 'Padimi' and listPrice < 70} (BookAuthor ⋈ Book))
7. π _{firstName, lastName} (σ_{pubDate > date('2020-07-01')} (BookAuthor ⋈ Book))
8. π _{B1.firstName, B1.lastName} (σ_{B1.firstName = B2.firstName AND B1.lastName = B2.lastName AND B1.isbn != B2.isbn} (ρ B1 BookAuthor ⨯ ρ B2 BookAuthor))

   With a join:

   π _{B1.firstName, B1.lastName} (ρ B1 BookAuthor ⋈  _{B1.firstName = B2.firstName AND B1.lastName = B2.lastName AND B1.isbn != B2.isbn} ρ B2 BookAuthor)

9. π _{B1.isbn, B2.isbn} (σ_{B1.title = B2.title AND B1.isbn < B2.isbn} (ρ B1 Book ⨯ ρ B2 Book))

   With a join:

   π _{B1.isbn, B2.isbn} (ρ B1 Book ⋈  _{B1.title = B2.title AND B1.isbn < B2.isbn} ρ B2 Book)

10. π _isbn (σ_{storeName = 'All Books'} (Stock)) ∩   π_isbn(σ_{storeName = 'Some Books'} (Stock)) OR

    π _S1.isbn (σ_{S1.storeName = 'All Books' AND S2.storeName = 'Some Books' and S1.isbn=S2.isbn}(ρ S1 Stock ⨯ ρ S2 Stock))

    OR

    π _S1.isbn (σ_{S1.storeName = 'All Books'} (ρ S1 Stock) ⋈   _{S1.isbn=S2.isbn} σ_{S2.storeName = 'Some Books'} (ρ S2 Stock))

11. π _S1.isbn (σ_{S1.isbn = S2.isbn AND S2.storePrice < S1.storePrice AND S1.storeName = 'All Books' AND S2.storeName = 'Some Books'} (ρ S1 Stock ⨯ ρ S2 Stock))

    OR

    π _S1.isbn (σ_{S1.storeName = 'All Books'} (ρ S1 Stock) ⋈  _{S1.isbn = S2.isbn AND S2.storePrice < S1.storePrice} (σ_{S2.storeName = 'Some Books'} (ρ S2 Stock)))

Note: Cross-products ('x') should be replaced with joins for efficiency.

Challenge questions:

1. Find all the books published by Wiley that are in-stock at Amazon.
   Answer:
   

   π _isbn (σ_{publisher='Wiley' AND quantity > 0} (Book ⋈ σ_{storeName='Amazon'} (Stock)))

2. List the addresses of stores that have pairs of books with the same title and different ISBNs.
   

Lab 1 - Relational Algebra Assignment