CS180 Database Management Systems - Winter 2003
Homework Assignment 1
Due in class on 21st Jan 2003
(Late homeworks will not be accepted.)
Instructions: Answer all the questions concisely below. Points will be taken off for unnecessarily long
answers. Please remember to include your name, student-id number, and email address in your homework
submission.
1. Exercise 4.2, page 127 of textbook.
2. Consider the following schema:
Invoices(invoice-no, cust-id, date)
Customers(cust-id, name, since, zipcode)
Items(invoice-no, item-no, qty, price)
The relation Invoices contains for each invoice, the id of the customer who made that invoice and the
date that invoice was made. The customer name, the date the customer made her first purchase, and
the zipcode where the customer resides is stored in the Customers relation. Each invoice contains
one or more purchased items. For every item purchased in an invoice, the quantity and price of that
item are also stored in the Items relation.
Write the following queries in relational algebra. Use only the relational operators taught in class so
far. If your relational algebra expression is huge, please break it down into smaller parts and explain
each part.
•Find the names of all customers who made some purchases during the day “11/22/02”.
•Find all invoices made during the days “11/22/02” and “11/23/02”.
•Find the names of all customers who made invoices during the days “11/22/02” and “11/23/02”.
You may assume that there is a relation R(date) with two tuples “11/22/02” and “11/23/02”. Can
R be constructed from Invoices?
•Find the names of all customer who made only one invoice during the date “11/22/02”. (Note:
The customer must make exactly one invoice.)
•Find the item number of the most expensive item in the invoice “N0938”.
3. Consider the following schema:
1
