Question

This table is used by Mighty Paws Veterinary Clinic. Inspect this table and answer questions below and answer the following 4 questions:

PetId	PetName	OwnerName	OwnerPhone	PetType	Legs	CanFly	Favorite food
100	Fluffy	Mary Smith	614-897-5496	Cat	4	No	MeowMix, FancyCatTM
101	Casper	Mary Smith	614-897-5496	Cat	4	No	MeowMix
102	Lori	Jim Larson	740-851-0023	Parrott	2	Yes	Seeds
103	Marty	Kim Jones	212-505-8578	Python	0	No
104	Oscar	Tim Wang	614-410-5535	Dog	4	No	IAMS, Tim’s new sneakers

1. 1. List anomalies and provide examples using this table to support your point.
2. 2. Is this table in 1NF, 2NF, 3NF, BCNF?
3. 3. List functional dependencies? Are they partial or transitive? Explain.
4. 4. Complete all normalization steps (if applicable) in order to bring this design to BCNF.

Answer 1

A. The different anamoly present are update, Delete and Updation Anamoly.

Update Anamaly occur when updation of a certain attribute instance leads to inconsistency. For example if we update number of legs of cat to 3 , then it might lead to data inconsistency as there can be more than one cat and there is dependency between petType and no of legs

Deletion anamaly might lead to deletion of a record where it should not be deleted. Here if we delete petType Dog, then Owner information will also be deleted from database and all its related information will be deleted.

Insertion Anamaly happens when we are not able to add data to database because we of some missing data. If we have one more petType but no owner, then this cannot be added to added to database.

B. No the given relation is not in 1NF since it contains multivalued attribute favorite food. Since it is not in 1NF, it is not in 2NF , 3NF as well.

C. The primary key for the given relation is {PetID}. There is no FD of type X->Y where X is primary attribute. Therefore there is no partial Functional dependency.

Transitive Functional dependency is of type x->y where x is non key. Here transitive Functional dependency are

OwnerName -> OwnerPhone

PetType -> legs, canfly

D.

The relation contains multivalued attribute Favourite Food. Thus it is not in 1NF. Decomposing it to 1 NF

R1 =(PetID, FavoriteFood)

R2 = (PetID, PerName, OwnerName, OwnerPhone, PetType, Legs, CanFly)

Now R1 is in 2NF , 3NF and in BCNF.

R2 is not in 3NF since it contains partial FD. Decomposing it to 3NF ,

R21 = (OwnerName, OwnerPhone)

R22 =(PetType, legs, canfly)

R23 =(PetID, pet name, ownerName, petType)

Now here R1, R21, R22 and R23 are in 3NF and in BCNF as well.

If you have any questions comment down and please? upvote thanks