Question

1. The following table belongs to a pet boarding company. Each pet that is kept has a type (species) and weight, an owner and the information where it is kept (which pen). The fee depends on the pen. It also depends on the owner because owners can have special rates.   

Pet_id	Type		Weight	Pen	Fee	Owner_id	O_name
2800	Cat		9.2	P02	20	11	Susan
1739	Cat		7.5	P01	25	28	Brett
9981	Dog		3.3	P02	30	29	Jason

1. Identify the functional dependencies represented by the attributes shown in the table and choose the most appropriate (largest composite) primary key. Notation: pet_id -> type means pet_id determines the type.

2. Based on the dependencies, convert the table into 3^rd normal form by going through the standard procedure.

Answer 1

a. Functional dependencies as per given description:

Pet_id -> Type,Weight,Pen

Owner_id -> O_name

{Owner_id,Pen} -> Fee

Primary Key = {Pet_id Owner_id}

Explanation - Attribute set of {Pet_id Owner_id} generates all the attributes of the relation.

(Pet_id Owner_id)⁺ = {Pet_id Type Weight Pen Fee Owner_id O_name}

b. Conversion to 3rd Normal Form

Current relation is in First Normal Form as their are only single valued attributes.

The Relation is not in Second Normal Form because of partial dependency(Owner_id is a prime attribute and determines non prime attribute)

Tables after conversion to 2NF:

Table 1:

Primary Key = Pet_id

Pet_id -> Type,Weight,Pen

Pet_id

Type

Weight

Pen

Table 2:

Primary Key = Owner_id

Owner_id -> O_name

Owner_id

O_name

Table 3:

Primary Key = {Owner_id Pen}

{Owner_id,Pen} - > Fee

Owner_id

Pen

Fee

The above relation is in 3NF. (Their is no transitive dependency)