NORMALIZATION CASE PROBLEM (20 POINTS)
In the following case description, the attribute names are included in parentheses
to clarify what each represents:
Pleasant Stay Kennels is a family-run kennel for dogs that would like to
computerize their reservation system. Dog owners call the kennel and make a
reservation for their dog. If an owner will be leaving multiple dogs at the kennel,
each dog has its own reservation. Each reservation is automatically assigned a
unique reservation number (rNo) and a starting date (start), ending date (end),
and daily boarding rate (rate) are recorded. The kennel keeps records on each
dog, including a unique pet ID number (pID), name (pName), and birth year
(pYear). The kennel also assigns each owner a unique ID number (oID) and
records their name (oName), and phone number (phone). While their dog is at
the kennel, the owner may request the kennel perform several services, such as
trimming, baths, or flea dips. The kennel would like the database to identify each
type of service with a unique ID (sID) and record its description (sDesc). The
cost of a service (sCost) may vary from reservation to reservation.
The following unnormalized relation has been developed based for this case. Do
not add any additional attributes as you complete the problem or delete any.
RESERVATION (rNo, start, end, rate, pID, pName, pYear, oID, oName, phone, sID,
sDesc, sCost)
Using the normalization process, produce a set of 1NF, 2NF, and 3NF relations
for the case described above. Discuss the steps that you take to reach each
normal form and show each set of relations. The following sample data is
provided to illustrate this case:
rNo start end rate pID pName pYear oID oName phone sID sDesc sCost
21456 6/1/02 6/4/02 15.00 4123 Fido 1993 49021 Joe Sims 999-1212 1 Trim $25
21456 6/1/02 6/4/02 15.00 4123 Fido 1993 49021 Joe Sims 999-1212 2 Bath $10
21457 6/1/02 6/4/02 15.00 4124 Spot 1993 49021 Joe Sims 999-1212
21458 6/2/02 6/8/02 20.00 2187 Mutt 1991 38124 May Lo 888-1515 3 Dip $15
21459 6/3/02 6/16/02 10.00 3987 Mini 2001 39218 Jim Sand 555-1414 2 Bath $8