CSC - Database The following database is given: BUYER Name Age Gender Alice 20 F
ID: 3605439 • Letter: C
Question
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.
5. (PROJECT (BUYER.Age)
(SELECT (Type = 'office')
(JOIN (ORDER.Buyer = BUYER.Name)
(JOIN (PRODUCT.PName = ORDER.Product) PRODUCT, ORDER)
BUYER))) (4 pts)
Similarly, do it step by step:
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
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
Explanation / Answer
Question 5:
(JOIN (PRODUCT.PName = ORDER.Product) PRODUCT, ORDER) =
Buyer
Product
Count
Alice
Pen
4
Alice
Notebook
2
Bob
Bike
1
Alice
Pan
1
Carol
Camera
1
Carol
Skateboard
1
(JOIN (ORDER.Buyer = BUYER.Name)
(JOIN (PRODUCT.PName = ORDER.Product) PRODUCT, ORDER)
BUYER) =
Buyer
Product
Count
Alice
Pen
4
Alice
Notebook
2
Bob
Bike
1
Alice
Pan
1
Carol
Camera
1
Carol
Skateboard
1
(SELECT (Type = 'office')
(JOIN (ORDER.Buyer = BUYER.Name)
(JOIN (PRODUCT.PName = ORDER.Product) PRODUCT, ORDER)
BUYER)) =
Buyer
Product
Count
Alice
Pen
4
Alice
Notebook
2
(PROJECT (BUYER.Age)
(SELECT (Type = 'office')
(JOIN (ORDER.Buyer = BUYER.Name)
(JOIN (PRODUCT.PName = ORDER.Product) PRODUCT, ORDER)
BUYER))) =
Question 6:
A =
Buyer
Product
Count
Alice
Pen
4
Alice
Notebook
2
Bob
Bike
1
Alice
Pan
1
Carol
Camera
1
Carol
Skateboard
1
B =
Buyer
Product
Count
Alice
Pen
4
C =
Buyer
Product
Alice
Pen
D =
Alice
Notebook
2
Bob
Bike
1
Alice
Pan
1
Carol
Camera
1
Carol
Skateboard
1
E =
C INTERSECTION E =
Question 7:
A =
B =
A - B =
Buyer
Product
Count
Type Price WeightAlice
Pen
4
Office 3 5Alice
Notebook
2
Office 10 100Bob
Bike
1
Transport 100 15000Alice
Pan
1
Kitchen 25 700Carol
Camera
1
Electronic 300 600Carol
Skateboard
1
Transport 50 1500