Had a Questionon a normilaization problem in a Database class. i first need to

Apply the normalization process to the following relations. Show the following steps for each relation:

Show the candidate keys from the original relation.

Show the functional dependencies from the original relation.

Is any determinant not a candidate key? If so, show the following:

The new normalized relations.

The primary key in each new relation.

The foreign keys in the new relations.

The referential integrity constraints for the foreign keys.

Relation 1:

This relation is about home appliances, such as refrigerators, stoves, ovens, washers, dryers, etc.

APPLIANCE (ApplianceType, BrandName, ModelNumber, SerialNumber, OwnerName, OwnerAddress, OwnerPhone)

Assumptions:

Each model number is unique to its brand and type. For example, a Kenmore washer could have a model number of KEN-WASH-990.

Serial numbers are just numbers, like 12345678, so could be duplicated.

Owner phone numbers are unique.

State any other assumptions you make.

Relation 2:

This relation is about playgroups for children. Any child can belong to any playgroup. Each playgroup meets at a certain parents home. That parent is the playgroup leader.

PLAYGROUP (ParentName, ParentEmail, ParentAddress, ChildName, PlayGroup, GroupLeader)

Assumptions:

Each parent has a unique e-mail address.

Each playgroup has a unique name.

Each playgroup has only one leader.

State any other assumptions you make.

Here is an example of what steps it should look like out of this pet and owner table

The relation :

PET (Petname,PetType,PetBreed,PetDOB,OwnerLastnameOwnerFirstName,

OwnerPhone,OwnerEmail,Service,Date,Charge)

Step 1: Candidate Keys

The last functional dependency assumes a pet is seen at most on one day and that there is no standard charge for a service.

(PetName,Date)

Step 2 : Functional Dependecies

PetName (PetType, PetBreed, PetDOB, OwnerLastName, OwnerFirstName, OwnerPhone, OwnerEmail)

OwnerPhone (OwnerLastName, OwnerFirstName, OwnerEmail)

(PetName, Date) (Service, Charge)

Step 3:

Dividing the relation if determinant is not a candidate key

Is every determinant a candidate key ?

PetName and OwnerPhone are determinants but not candidate keys

OWNER (OwnerPhone, OwnerLastName, OwnerFirstName, OwnerEmail)

PET (PetName, PetType, PetBreed, PetDOB, OwnerPhone)

SERVICE (PetName, Date, Service, Charge)

OwnerPhone in PET must exist in OwnerPhone in OWNER

PetName in SERVICE must exist in PetName in PET