Question

2.1 Apply the normalization process to the Veterinary Office List–Version One relation shown in Figure 1-34 (see page 63 and below) to develop a set of normalized relations. Show the results of each of the steps in the normalization process. 1 PetName 2 King 3 Teddy 4 Fido 5 AJ 6 Cedro 7 Woolley 8 Buster 9 Jiddah PetType Dog Cat Dog Dog Cat Cat Dog Cat C P etBreed Std. Poodle Cashmier Std. Poodle Collie Mix Unknown Unknown Border Collie Abyssinian PetDOB OwnerLastName 27-Feb-14 Downs 1-Feb-13 James 17-Jul-15 Downs 5-May-15 Frier 6-Jun-12 James ??? James 11-Dec-11 Trent 1-Jul-08 Evans OwnerFirstName Marsha Richard Marsha Liz Richard Richard Miles Hilary G OwnerPhone 201-823-5467 201-735-9812 201-823-5467 201-823-6578 201-735-9812 201-735-9812 201-634-7865 201-634-2345 H OwnerEmail Marsha Downs@somewhere.com Richard James@somewhere.com Marsha.Downs@somewhere.com Liz. Frier@somewhere.com Richard James@somewhere.com Richard James@somewhere com Miles. Trent@somewhere.com Hilary. Evans@somewere.com

Answer 1

Normalization process for data:- Step-1 pet-and-Owner C pet Name, pet type , pet Bre pet DOB, Owner last Name, owner First name, own owney Email). Functional Dependencies.' pet Name( pet Type , pet Breed, pet dob, owner Last Name, Owner last Name, owner phone owner Email Owner Email → Cowner last name, owner first name owner phone) Owner phone → (owner Last Name, owner first name, Owne v Email ) pet-and owner Candidate keys: - pet Name üs every determinate a candidate key ndabe key NO - owner Email and owner phone are not Candidate keys Step-I Break into Solutions : owner and pet merlast Name owner first name owner phone OWNER ( ownerlast Name om Owner Email) PET ( pet Name, pet type, pet Breed, pet Das I { Foreign key}}

For owner Functional dependencies Owner Email → (owner last name, Owner first Name Owner phone) Owner phone → (ownever name , Owner First name, Owner Email) OWNER Candidate keys: Ownerphone, crunerEmail. is every determinals a candidate key? yes -- Owner Email and owner phone are 1. Candidate keys. Normalization complete. We can choose either Candidate key as primary key (A) If the use owner phone as primary key then OWNER (ownerphone, owner last name, Owner Firstwame, . owner Email) PET ( pet Name, pet type, pet Breed, pet dos, owner phone) Functional dependencies'- pet Name + (pet Type , pet Breed , pet dob, crwney phone) pet. Candidate keys: pet Name: 15 Every determinant a candidate key yes + pet Name is candidate key - Normalization done.

Final Rormatizat Final Normalised Relation:- OWNER ( owner phone , ownev lastrame, Owner first name, owner Email) PET ( pet name, pet type, pel Breed ,ptDos, ownerphone (B) if we use owner Email as primerry key then CWNER (owner phone, owner last name, Owner förstorame - Owner Email) PET ( pet Naime, pet Breed , pettype , pet dob, Owncy Email) Funetional Dependencies:- pet Name + (petType, pel Breed , pet bos , owner Email) pet Candidate keys : pet name is Every determinant a candidate key? yes + pet Name is Candidate key - Normalization done Final Normalization Relations OWNER (owner phone, Owney Lastrame, ownev firstname owner Email ) PET ( pet Name, pettype pet Breed , petDOR po okone v Email).