A database of research articles in a journal uses the following schema.…
2016
A database of research articles in a journal uses the following schema.
(VOLUME, NUMBER, STARTPGE, ENDPAGE, TITLE, YEAR, PRICE)
The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema.
(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → TITLE
(VOLUME, NUMBER) → YEAR
(VOLUME, NUMBER, STARTPAGE, ENDPAGE) → PRICE
The database is redesigned to use the following schemas.
(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE)
(VOLUME, NUMBER, YEAR)
Which is the weakest normal form that the new database satisfies, but the old one does not?
- A.
1NF
- B.
2NF
- C.
3NF
- D.
BCNF
Attempted by 184 students.
Show answer & explanation
Correct answer: B
Answer: 2NF — The redesign removes a partial dependency that violated 2NF.
Explanation:
Original primary key: (VOLUME, NUMBER, STARTPAGE, ENDPAGE).
Functional dependency (VOLUME, NUMBER) → YEAR is a partial dependency because (VOLUME, NUMBER) is a proper subset of the composite primary key. Partial dependencies violate 2NF.
The redesign decomposes the schema into (VOLUME, NUMBER, YEAR) and (VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE). YEAR is now stored with its own key (VOLUME, NUMBER), eliminating the partial dependency.
Therefore the weakest normal form that the new database satisfies but the old one does not is 2NF. The redesign fixes the 2NF violation; while it may also satisfy higher normal forms, the minimal improvement is to 2NF.
A video solution is available for this question — log in and enroll to watch it.