Question

Given the following relations:

PARTS(Pno, Pname, Qoh, Price, Olevel)
CUSTOMERS(Cno, Cname, Street, Zip, Phone)
EMPLOYEES(Eno, Ename, Zip, Hdate)
ZIP_CODES(Zip, City, State)
ORDERS(Ono, Cno, Eno, Received, Shipped)
ODETAILS(Ono, Pno, Qty)

Where Qoh stands for quantity on hand and Olevel stands for (re)order Level. The P, C, E and O single letter prefixes for attributes refer to the parts, customers, employees and orders relations, respectively.

a. Retrieve the names of customers who have ordered parts costing less than $150.00.

b. Retrieve the names of customers and the Pname of orders with a qty greater than 10.

For the above queries, draw an initial relational algebra query tree then transform the initial query tree created into a more efficient form using heuristic rules. Show your optimization one step at a time and explain the rationale of each optimization.

Answer 1

Answer A)-

The relation algebra query- (∏?????(??????.???=????????.??? Ո ????????.???=??????.??? Ո ?????????.???=??????.??? Ո ?????<150.00(????? ? ?????? ? ?????????))

Answer B)-

The relation algebra query- (∏?????,?????(??????.???=????????.??? Ո????????.???=??????.??? Ո ?????????.???=??????.??? Ո ???>10(????? ? ???????? ? ?????? ? ?????????))

1. — draw an initial relational algebra query tree for A and B -  

A)-

B)-

1. The initial query tree created into a more efficient form using heuristic rules.

• Avoid cross-product operation
• simplify number of tuples usable for join by executing select & project works earlier join operations

​​​​​​​

Note- Please do upvote, if any problem then comment in box sure I will help.