CSC - Database The following database is given:

BUYER

Name	Age	Gender
Alice	20	Female
Bob	21	Male
Carol	18	Female

CARD

CardNum	Owner	Limit
1111	Alice	50
1234	Bob	10
4321	Bob	30
9999	Carol	1000

PRODUCT

PName	Type	Price	Weight
Pen	Office	3	5
Pencil	Office	2	3
Notebook	Office	10	400
Camera	Electronic	300	600
Bike	Transport	100	15000
Skateboard	Transport	50	1500
Pan	Kitchen	25	700

ORDER

Buyer	Product	Count
Alice	Pen	4
Alice	Notebook	2
Bob	Bike	1
Alice	Pan	1
Carol	Camera	1
Carol	Skateboard	1

Note:

Underline indicates Primary Key

Foreign Key (Buyer) in ORDER references BUYER (Name)

Foreign Key (Product in ORDER references PRODUCT (PName)

Foreign Key (Owner) in CARD references BUYER (Name)

Buyers can own one or more cards. Buyers can order one or more products.

In each of the following problems a Relational Algebra expression given.

You are asked to hand-compute the expression and type-in (or write-in) the result set below the expression.

6. Intersection Example

(PROJECT (PRODUCT.PName, ORDER.Buyer)

(SELECT (PRODUCT.Price <= 10)

(JOIN (PRODUCT.PName = ORDER.Product) PRODUCT, ORDER)))

(PROJECT (PRODUCT.PName, ORDER.Buyer)

(SELECT (ORDER.Count <= 2)

(JOIN (PRODUCT.PName = ORDER.Product) PRODUCT, ORDER)))

(4 pts)

Do it step by step

A= (JOIN (PRODUCT.PName = ORDER.Product) PRODUCT, ORDER)

B = (SELECT (PRODUCT.Price <= 10) A)

C = (PROJECT (PRODUCT.PName, ORDER.Buyer) B)

D = (SELECT (ORDER.Count <= 2) A)

E = (PROJECT (PRODUCT.PName, ORDER.Buyer) D)

Final Answer C INTERSECTION E

7. Set Difference Example

(PROJECT (PName) (SELECT (Weight >= 500) PRODUCT))

(PROJECT (Product) (SELECT (Buyer = 'Carol' OR Buyer = 'Alice') ORDER) (3 pts)

DO it step by step:

A = (PROJECT (PName) (SELECT (Weight >= 500) PRODUCT))

B= (PROJECT (Product) (SELECT (Buyer = 'Bob' OR Buyer = 'Alice') ORDER)

A B