Database Storage & Design Chapter 23 Homework Question 204 Horizontally Fragmented Follows

PART IV

Chapter 23 Query Processing

Case Study 1 InstantBuy

Client (clientNo, cName, cAddress, cTelNo, cFaxNo, sex, DOB, officeNo)

Item (itemNo, itemDescription, itemSize, itemColor, itemPrice, itemType)

ItemType (itemType, typeDescription)

23.1 Using the above relational schema, determine whether the following query is both type and

semantically correct:

SELECT CO.orderNo, C.cAddress, OD.itemNo

23.2 Consider the above relational schema. Map the following query onto a relational algebra tree,

and then transform it into a reduced query:

SELECT CO.orderNo, C.cAddress, O.oAddress

FROM ClientOrder CO, Client C, Office O, Area A

WHERE CO.clientNo = C.clientNo AND

PART IV

CO.orderNo, cAddress, oAddress

PART IV

Case Study 2 Complete Pet Care

Owner (ownerNo, oName, oAddress, oTelNo)

Pet (petNo, petName, petDescription, petSex, petDOB, dateRegistered,

For a more complete description, see Case Study 4 under Chapter 22.

23.3 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

23.4 Consider the above relational schema. Map the following query onto a relational algebra tree,

and then transform it into a reduced query:

SELECT P.petNo, petName, oName, oAddress

FROM Pet P, Owner O, Prescription P, Medication M

WHERE PR.medNo = M.medNo AND

PART IV

Give a full explanation of the reasoning behind each step and state any transformation rules

used during the reduction process.

p.petNo, petName, oName, oAddress

unitsPerDay > 200

medName

p.petNo, petName, oName, oAddress

3 petNo

O

p.petNo, petName

p.petNo

23.5 Now assume that the relation Medication given in Question 20.4 is horizontally fragmented as follows:

and that Prescription is horizontally derived from Medication:

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.

PR1

PART IV

Case Study 3 Rapid Roads

Client (clientNo, cName, cAddress, cTelNo, cFaxNo, officeNo)

Unit (unitRegNo, unitDescription, maxPayload, officeNo)

For a more complete description, see Case Study 5 under Chapter 22.

23.6 Using the above relational schema, determine whether the following query is both type and

semantically correct:

SELECT CO.clientNo, cAddress

23.7 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

PART IV

maxPayload > maxCarryingWt

officeNo = 2

maxPayload > maxCarryingwt

3 officeNo

officeNo = 2

3 trailerNo

officeNo = 2

maxPayload > maxCarryingwt

trailerDescription, unitDescription

officeNo = 2

3unitRegNo

officeNo

trailerDescription, unitDescription, maxPayload, maxCarryingWt, officeNo

23.8 Now assume that the relation Trailer given in Question 20.7 is horizontally fragmented as

follows:

T1 = officeNo < 2(Trailer)

T2 = officeNo = 2(Trailer)

T3 = officeNo > 2(Trailer)

and that TransportReq is horizontally derived from Trailer:

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.

maxpayload > maxCarryingWt

3 trailerNo

O

officeNo = 2

PART IV

Using the fact that the Selection operation on (officeNo = 2) will not produce any tuples from

T1 and T3, this will simplify to the following tree: