Homework 3
CSCE 824 – Spring 2019
Due: March 19, 11:55 pm 2019 via
Dropbox
Name:
20 points
Design a Columbia-Kennel-Club
database that keeps information about the kennels in Columbia: their name,
address, and contact phone. For each
Kennel it keeps info about the dogs boarded in the kennel. Each dog is identified by its name and date
of birth. Each dog may have several
owners. For each owner, the database
stores the name and a single phone number.
There is also a single emergency contact information for each dog. In addition, for each dog, there is a list of
playtime activities.
There are functional
dependencies that hold on the database: the name and DOB of a dog together functionally
determine the owners and EmergencyPh. A dog may be boarded at different kennels and
they may have different play activities (or no play).
1. Draw the E-R diagram for the Columbia-Kennel-Club
database
2. Write the relation schemas of your
database
Is
your design in BCNF normal form?
3. Consider the publication Cong Yu
and H. V. Jagadish. 2008. XML schema refinement
through redundancy detection and normalization. The VLDB Journal 17, 2 (March
2008), 203-223. https://dl.acm.org/citation.cfm?id=1342417
a. Design an XML schema for the Columbia-Kennel-Club
database (Fig. 2 in reference paper)
b. Draw an example XML document (Fig.
1 in the reference paper)
c. Assign the @key values to the data
nodes
d. Write the generalized tree tuple
for pivot node dog in your XML tree
(Definition 5 in reference paper)
e. Consider the constraint that
whenever two dogs have the same name and the same date of birth, they must have
the same owners and emergency contact number.
i.
Show
an example in your XML document for the same dog name and date of birth in
different kennels.
ii.
Write
the XML FD (Definition 7 in reference paper) for this constraint
f. Normalize your XML tree