You are designing a database for a sports hostel. There are three types of buildings for which a fixed
fee is charged on per room basis. No player can take two accommodations. You design the following table:
Considering that the P_ID is the primary key, the table is in which normal form?
Each correct answer represents a complete solution. Choose all that apply.
A.
BCNF
B.
1NF
C.
2NF
D.
3NF
Explanation:
The table designed is following the first and second normal forms. In the first normal form, a table
faithfully represents a relation and has no
repeating groups. As the designed table does not have any repeating groups it is in the first normal
form.
In the second normal form, no non-prime attribute in the table is functionally dependent on a
proper subset of a candidate key (primary key).
The designed table is following this normalization also.
Answer D is incorrect. According to the third normal form, every non-prime attribute is nontransitively dependent on every candidate
key in the table. Here, in this design, the hostel fee is transitively dependent on the primary key.
Hence, this table is not in 3NF.
Answer A is incorrect. The Boyce-Codd normal form (BCNF) is stricter than 3NF. According to BCNF,
each attribute must represent a fact about the key, the whole key, and nothing but the key. This is
not followed by the table designed in the question.
According to the third normal form, every non-prime attribute is nontransitively dependent on every candidate
key in the table. Here, in this design, the hostel fee is transitively dependent on the primary key.
Hence, this table is not in 3NF.
Answer A is incorrect. The Boyce-Codd normal form (BCNF) is stricter than 3NF. According to BCNF,
each attribute must represent a fact about the key, the whole key, and nothing but the key. This is
not followed by the table designed in the question.