Question

Examine the determinants of the functional dependencies. If any determinant is not a candidate key, the relation is not well formed. In this case: a. Place the columns of the functional dependency in a new relation of their own. b. Make the determinant of the functional dependency the primary key of the new relation. c. Leave a copy of the determinant as a foreign key in the original relation. d. Create a referential integrity constraint between the original relation and the new relation.

The Veterinary Office List Version Two King Std. Poodle 27-Feb-12 Down 3 Teddy 1-Feb-11 James Cashmier Std. Poodle 4 Fila 17-Jul-13 Downs 5 May 13 Frier Collie Mix 6 Cedro Cat Unknown 6 Jun 10 James 7 Woolley Unknown 11-Dec-09 Trent 8 Buster Border Collie 1 Jul-06 Evans Richard Marsha Richard Richard Miles 201-823-5467 Marsha Downs@so com Ear Infection 17-Aug-14 65.00 5-Sep-14 s 27.50 201-735-9812 Richard James com Nail Clip 201-823-5467 42.50 201-823-6578 One year shots 5 May 14 S 5-Sep-14 201-735-9812 lanes Somewherecom Nail Clip 201-735-9812 Richard [email protected] Skin Infection 3-Oct-14 35.00 201-634-7865 Miles, [email protected] Laceration Repair 5-Oct-14 127.00 210-634-2345 Hilary Evans omewhere.com Booster Shots 4-Nov-14 S 111.00

Answer 1

Solution: a) Placing the columas ofthe functionl dpadeayla sewrdasion OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail) b) The primary key of new relation is: OWNER OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail) c) Foreign key in original relation. PET(PetName, PetType, PetBreed, PetDOB, OwnerPhone) d) Referential integrity constraint. OwnerPhone in PET must exist in OjvnerPhone in OWNER.