CSC - Database The following database is given: BUYER Name Age Gender Alice 20 F
ID: 3605362 • 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.
1. (PROJECT (Product, Count) (SELECT (Buyer = 'Alice') ORDER)) (2 pts)
2. (PROJECT (PName, Price) (SELECT (Type = 'Office') PRODUCT)) (2 pts)
3. (PROJECT (PName)
(SELECT ((Type = 'Electronic' OR Type = 'Transport') AND Price <= 100) PRODUCT)) (2 pts)
4. (PROJECT (PName, Price)
(SELECT ((Buyer = 'Bob' OR Buyer = 'Carol') AND (Type = 'Transport'))
(JOIN (PRODUCT.PName = ORDER.Product) PRODUCT, ORDER)))
(3 pts)
Do it step by step
A = (JOIN (PRODUCT.PName = ORDER.Product) PRODUCT, ORDER)
B = (SELECT ((Buyer = 'Bob' OR Buyer = 'Carol') AND (Type = 'Transport')) A)
Result = (PROJECT (PName, Price) B)
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
Hi,
Answering the first 4 sub parts as per chegg policy, plesse post others as separate question
In relational algebra, SELECT is used to select records of tables and PROJECT is used to project them onto the output
1. (PROJECT (Product, Count) (SELECT (Buyer = 'Alice') ORDER))
Ans: This selects product and count columns frm ORDER where buyer='Alice' i.e
Pen 4
Notebook 2
2. (PROJECT (PName, Price) (SELECT (Type = 'Office') PRODUCT))
This selects PName and Price columns from PRODUCT where Type = 'Office' i.e
Pen 3
Pencil 2
Notebook 10
3. (PROJECT (PName)
(SELECT ((Type = 'Electronic' OR Type = 'Transport') AND Price <= 100) PRODUCT)) ans: this selects PName from product table where Type = 'Electronic' OR Type = 'Transport') AND Price <= 100 i.e
Bike
Skatebaord
4. lets do it step by step
Do it step by step
A = (JOIN (PRODUCT.PName = ORDER.Product) PRODUCT, ORDER)
joins product and order table over the condition, pName=product,
B = (SELECT ((Buyer = 'Bob' OR Buyer = 'Carol') AND (Type = 'Transport')) A)
now, select only those rows from A where Buyer = 'Bob' OR Buyer = 'Carol') AND (Type = 'Transport') and then selecting (PName, Price columns as given in project Result = (PROJECT (PName, Price) B)
it will be:
Bike 100
Skateboard 50
Thumbs up if this was helpful, otherwise let me know in comments