Deck 8: Query Processing

Question 1

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

Answer 1

Query graph will show that OrderDetail has no join to the other relations, although it is connected to the Result. Should conclude that it is not semantically correct.

Question 2

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

Answer 2

Question 3

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;

Answer 3

Various problems with query:
surgeryNo is not part of Owner table;
medNo is not a textual attribute but an integer;
TelNo is not part of Owner (its oTelNo)

Question 4

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

Question 5

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.

Question 6

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;

Question 7

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.

Question 8

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.