1
CS430/630 – Midterm
30 points, 75 minutes, 3 pages
For both questions, you are given the following relational schema.
Books(bid:integer, bname:string, author:string, year:integer, price:integer)
Orders(cid:integer, bid:integer, quantity:integer)
Customers(cid:integer, cname:string, age:integer, zipcode:string)
The meaning of attributes is as follows:
– bid: unique book identifier,
– bname: book name,
– author: book author,
– year: book publication year,
– price: book price,
– quantity: number of books purchased with an order,
– cid: unique customer identifier,
– cname: customer name,
– age: customer age,
– zipcode: customer address zipcode.
Question 1 (15 points)
Write relational algebra expressions for the following queries:
(a) Find the ages of customers who bought only books published after year 2000. Consider only
customers that bought at least one book.
(b) Find the names of customers who bought a book by “Edgar Codd”, but also never bought a book
more expensive than $100.
(c) Find the zipcodes of customers who bought in a single order at least 20 units of a book called
“Databases” authored by “Edgar Codd”.
(d) Find the name(s) and zipcode(s) of the customer(s) who bought the most expensive book sold
(note that there may be books that were never sold!).
(e) [630 only] Find the title(s) of the most expensive book(s) purchased by customer(s) with the
lowest age. Note that there may be age and price ties.
Note: for this question, you are NOT ALLOWED to use SQL, answers in SQL will not receive any marks.
Derive relational algebra expressions only.
Question 2 (15 points)
Write SQL queries for the following:
(a) List zipcodes of customers who bought at least one book for every publication year over the time
duration covered by the database. List each zipcode once.
(b) Find for each customer who spent a total of at least $10,000 at the store the price of the most
expensive book purchased by that customer.
(c) Find the distinct ages of customers who bought only books that contain “Databases” in the title
(assume they purchased at least one book).
(d) Find the ages(s) of the customers who bought the most expensive book(s) sold (note that some
books may have never been sold).
(e) [630 only] Find the name(s) of the most valuable customer(s), defined as the customers who spent
the most amount of money at the store.
2
Q1 Answer:
3
Q2 Answer: