Case Study 1 - InstantBuy

Essay

Case Study 1 - InstantBuy
Client (clientNo, cName, cAddress, cTelNo, cFaxNo, sex, DOB, officeNo)
Item (itemNo, itemDescription, itemSize, itemColor, itemPrice, itemType)
ItemType (itemType, typeDescription)
ClientOrder (orderNo, clientNo, dateOrder, dateDeliver)
OrderDetail (orderNo, itemNo, noOfItems)
Staff (staffNo, sName, sAddress, sTelNo, sex, DOB, NIN, taxCode, salary, officeNo)
Office (officeNo, oAddress, oTelNo, oFaxNo, mgrStaffNo, areaNo)
Area (areaNo, areaDescription)
For a more complete description, see Case Study 3 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.Give a full explanation of the reasoning behind each step and state any transformation rules used during the reduction process.
SELECT CO.orderNo, C.cAddress, O.oAddress
FROM ClientOrder CO, Client C, Office O, Area A
WHERE CO.clientNo = C.clientNo AND
C.officeNo = O.officeNo AND
CO.dateDeliver < '1-Jun-96' AND
O.areaNo = A.areaNo AND
A.areaDescription = 'NE' AND
C.cName = 'J. Smith' AND
CO.dateOrder > '1-Jan-96';