COMP$3278$ Introduction to Database Management Systems

Assignment $3-$ Relational Algebra, Functional Dependency & BCNF

Q$1$ Consider the following relational tables of FoodWonder, an online food delivery platform:

Restaurants $($rID$,$ name, cruise$)$

Customers $($CID$,$ name, VIP$)$

VIP is a boolean telling whether the customer is a VIP.

Drivers $($dID$,$ name$)$

Orders $($OID$,$ rID, CID, dID, orderTime, arrivaltime,

foodRating, driverRating$)$

Foreign keys:

rID referencing Restaurants $($YID$)$

CID referencing Customers $($CID$)$

dID referencing Drivers $($dID$)$

The time intervals $[$orderTime$,$ arrivaltime$]$ denotes the period from when the order is

placed by the customer $($denoted by CID$),$ to the time that the driver $($denoted by IID$)$ pass the

deliver to the customer.

Note that arrivalTime can be NULL for ongoing deliveries.

foodRating and driverRating are the ratings that the customer $($denoted by CID$)$ can give in

an order. The rating is an integer from $1($worst$)$ to $5($best$).$

Please give the expression trees with some optimizations $($You can use assignment operator

and more than one trees to answer each question$)$:

$[10\%]$ Retrieve the driver ID $($dID$)$ and name of the driver$($s$)$ who have completed

more than $100$ delivery orders.

$[10\%]$ Retrieve the restaurant ID $($rID$)$ and name of the restaurant$($s$)$ that are

categorized as "Thai" cruise restaurants and have an average food rating

$($foodRating$)$ of $4$ or above.

$[15\%]$ Retrieve the distinct driver ID $($dID$),$ the name of the first restaurant $($rlname$),$ and the

name of the second restaurant $(r2$ name$)$ for the drivers who have been $($or are currently$)$

delivering orders from two restaurants simultaneously, considering overlapping order time

intervals.

The r$1$name and $r2$ name represent the names of two distinct restaurants involved in

the orders.

$[15\%]$ Provide a query that utilizes the driverRating column and involves all four tables

$($Restaurants$,$ Customers, Drivers, and Orders$)$ in the query execution process.

After providing the textual description of the query, provide the expression tree with

some optimizations for that query.

Q$2[15\%]$ Given the relation schema $R(A,B,C,D,E)$ with the following functional dependencies set

$F=\{AE,BCA,DB,EC\}$ which hold $onR.$

Find and proof all candidate keys in $R.$

Q$3[15\%]$ Given the relation schema $R(A,B,C,D,E)$ with the following functional dependencies set

$F=\{ABE,BC,EBCD\}$ which hold $onR.$

If $R$ is decomposed to the following $R_1$ and $R_2$

$R_1=(A,B,D)$ and $R_2=(A,C,E)$

a$.$ Is the decomposition dependency$-$preserving? Explain your answer.

b$.$ Suggest two different ways to handle the unpreserved dependencies in the above

decomposition.

Q$4[15\%]$ Given the relation schema $R(A,B,C,D,E)$ with the following functional dependencies set

$F=\{ACB,CB,CE,DA\}$ which hold $onR.$

Give a lossless join and dependency preserving decomposition of $R$ into relations in BCNF$.$ Show

your steps and proof.