Exam 8: Query Processing

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. -Using the above relational schema, determine whether the following query is both type and semantically correct: SELECT CO.clientNo, cAddress FROM TransportReq TR, ClientOrder CO, Client C, Unit U, Trailer T WHERE CO.clientNo = C.clientNo AND TR.unitRegNo = U.unitRegNo AND TR.trailerNo = T.trailerNo AND maxCarryingWt < maxPayload AND LoadWeight < maxPayload;

Case Study 2 - Complete Pet Care For a more complete description, see Case Study 4 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 P.petNo, petName, oName, oAddress FROM Pet P, Owner O, Prescription P, Medication M WHERE PR.medNo = M.medNo AND PR.petNo = P.petNo AND P.ownerNo = O.ownerNo AND medName = ' Provac' AND unitsPerDay > 200 AND petDescription = 'Setter';

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. -Now assume that the relation Trailer given in Question 20.7 is horizontally fragmented as follows: Transform the relational algebra tree from Question 21.7 into a reduced query on fragments. Give a full explanation of the reasoning behind each step and state any transformation rules used during the reduction process.

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. -Using the above relational schema, determine whether the following query is both type and semantically correct: SELECT CO.orderNo, C.cAddress, OD.itemNo FROM OrderDetail OD, ClientOrder CO, Client C, Office O, Area A WHERE CO.clientNo = C.clientNo AND C.officeNo = O.officeNo AND O.areaNo = A.areaNo AND A.areaDescription = 'SE' AND C.cName = 'J. Smith';

Case Study 2 - Complete Pet Care For a more complete description, see Case Study 4 under Chapter 22. -Now assume that the relation Medication given in Question 20.4 is horizontally fragmented as follows: Transform the relational algebra tree from Question 20.4 into a reduced query on fragments. Give a full explanation of the reasoning behind each step and state any transformation rules used during the reduction process.

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.

Case Study 2 - Complete Pet Care For a more complete description, see Case Study 4 under Chapter 22. -Using the above relational schema, determine whether the following query is both type and semantically correct: SELECT P.petNo, petName, oName, O.TelNo FROM Pet P, Owner O, Prescription Pr, Medication M, Surgery S WHERE Pr.medNo = M.medNo AND S.surgeryNo = O.surgeryNo AND PR.petNo = P.petNo AND P.ownerNo = O.ownerNo AND M.medNo = 'J. Smith' AND adminMethod = 'Oral' AND S.surgeryNo = 100;

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';