Case Study 3 - Rapid Roads
Client $\quad$ $\quad$

Essay

Case Study 3 - Rapid Roads
Client $\quad$$\quad$ (clientNo, cName, cAddress, cTelNo, cFaxNo, officeNo)
Unit $\quad$ $\quad$$\quad$ (unitRegNo, unitDescription, maxPayload, officeNo)
Trailer $\quad$ $\quad$$\quad$ (trailerNo, trailerDescription, trailerLength, maxCarryingWt, officeNo)
ClientOrder $\quad$$\quad$ (orderNo, clientNo, dateOrder, collectDate, collectAddress, deliveryDate, deliveryAddress, loadWeight, loadDescription)
TransportReq $\quad$ (orderNo, unitRegNo, trailerNo)
Staff $\quad$ $\quad$ $\quad$ (staffNo, sName, sAddress, sTelNo, sex, DOB, position, NIN, taxCode, salary, officeNo)
Office $\quad$ $\quad$ (officeNo, oAddress, oTelNo, oFaxNo, countryNo)
Country $\quad$ $\quad$ (countryNo, countryName)
For a more complete description, see Case Study 5 under Chapter 22.
-Consider the above relational schema. Map the following query onto a relational algebra tree, and then transform it into a reduced query:
SELECT trailerDescription, unitDescription
FROM Trailer T, Unit U, Office O, TransportReq TR
WHERE TR.trailerNo = T.trailerNo AND
TR.unitRegNo = U.unitRegNo AND
U.officeNo = O.officeNo AND
maxPayload > maxCarryingWt AND
O.officeNo = 2;
Give a full explanation of the reasoning behind each step and state any transformation rules used during the reduction process.