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. (GATE-2016) (2 Marks)
(VOLUME, NUMBER, STARTPAGE, 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 36 students.
Show answer & explanation
Correct answer: B
The original schema has a composite primary key (VOLUME, NUMBER, STARTPAGE, ENDPAGE). The functional dependency (VOLUME, NUMBER) → YEAR indicates a partial dependency because the determinant is a proper subset of the primary key. This violates 2NF, so the original schema is only in 1NF. The redesigned schemas decompose this into (VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE) and (VOLUME, NUMBER, YEAR). In both new relations, all non-prime attributes depend on the full primary key. Thus, the new database satisfies 2NF.
A video solution is available for this question — log in and enroll to watch it.