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? 

    1. If yes, justify your answer.
    2. If not, decompose your relations such that it is in BCNF 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

  1. Compare your normalized XML design with the normalized relational design