Question

Consider a relation named STUDENT_ ACCOMMODATION (StudentID, Buidling, AccommodationFee) as shown in the figure below. Explain why this relation is in 2NF but not in 3NF.             

StudentID	Building	AccommadtionFee
101	Alpha	$320
102	Betta	$250
103	Alpha	$320
104	Betta	$250
105	Gemma	$400

Answer 1

Solution:

From the table above we can conclude the following dependecies:

A) StudentID ---------> Building, AccommadtionFee

B) Building -----------> AccommadtionFee

Also from the functional dependencies we can conclude that StudentID is the candidate key.

According to 3NF definition, a functional dependency X-----> Y is in 3NF

1) If X is a super key

OR

2) Y is a prime attribute( each element of Y is part of candidate key)

So from the 3NF definition we can see that Building is not a superkey and AccommadtionFee is not a prime attribute . Therefore it is not in 3NF.

Also to check for 2NF the Functional dependencies should not have partial dependencies( a part of candidate key deriving a non prime attribute is called partial dependency)

In both the depencies (A & B) there is no partial dependency as

StudentID ---------> Building, AccommadtionFee . StudentID is a candidate key(not a part of candidate key). So it is not partial dependency.

  Building -----------> AccommadtionFee . Building is not a part of candidate key. So, it is also not a partial dependency.

So the functional dependencies don't have any partial dependencies. Therefore it is in 2NF.