9 - Relational Algebra

Chuck, who recently began working for the Nature’s Vitamin store, wants to create a database to track his customers’ purchases. He starts to reflect on how other stores track purchases. He recalls that the hardware store always asks him for his zip code, and the dry cleaners always asks him for his phone number. He also knows that the video rental store gives him a card with a scan bar on the back and a membership number on the front. Last week when he went to the public library and had forgotten his library card, he used his social security number to pull up his record and check out some books. He wants to be able to track customer information (including what products each customer purchases on which days) and also be able to notify them of specials via e-mail or post.

1. What attributes should be included in his customer table?
2. What are the candidate keys? What is the primary key?
3. On what grounds did you select your primary key?

Given the data model below:

Fill in the remaining attributes. For product, include product name and price. For order line, include the number of each product purchased on the specific order. For order, include the date of the order. Decide whether you should include total cost of the order.

Product

• The primary key is a system generated prodid.
• There are no foreign keys.

Orderline

• The primary key is the composite of orderid and orderlineid.
• The foreign keys are prodid and orderid.

Order

• The primary key is a system generated orderid.
• The foreign key is customerid.

Customer

• The primary key for the customer table is a system generated customerid.
• The candidate keys for customer table are phone, SSN, or e-mail. Any of these candidate keys could become a primary key. The phone has not been chosen as a primary key because once a person disconnects a phone, the phone company might allot the same phone number to another person after some time. So it might not remain unique over a period of time. The SSN (social security number) has not been chosen as the primary key as some people are not willing to share their SSN unless it is absolutely necessary. Many customers might not find it necessary to share their SSN for a vitamin store. Email address could also become a primary key as it is unique at any given point of time, but as in the case of phone, different people can take the same e-mail address over a period of time as long as it is not used by anybody else. Also, people have tendency to keep changing their non-essential e-mail addresses.
• There are no foreign keys.

Order total can be derived by summing orderlineqty*prodprice.

customer

SELECT * FROM customer

customer [custname, custemail]

SELECT custname, custemail FROM customer

prodname WHERE price > 10

SELECT prodname FROM product WHERE prodprice > 10

(product WHERE price > 10) [prodname]

SELECT prodname FROM product
WHERE prodprice > 10

(customer WHERE custcity = 'Tampa')
INTERSECT
(customer WHERE custzip = '33456')

SELECT * FROM customer
WHERE custcity = 'Tampa'
AND custzip = '33456'

customer MINUS
((customer WHERE custzip code = '33456')
INTERSECT
(customer WHERE custzip code = '33454'))

SELECT * FROM customer
WHERE custzip NOT IN ('33456','33454')

(customer JOIN order WHERE
 customer.custid=order.custid)
	[custfname, custlname, custphone)

SELECT custfname, custlname, custphone
FROM customer, order
WHERE customer.custnum = order.custnum

((orderline[prodid, orderid]
DIVIDEBY
ORDER[orderid])
JOIN
product) [prodid, prodname]

SELECT prodid, prodname FROM product
WHERE NOT EXISTS
 (SELECT * FROM order
  WHERE NOT EXISTS
  (SELECT * FROM orderline
   WHERE orderline.protid = product.protid
    AND orderline.ordeidm = order.ordeidm))