GATE CSE 2016 Set 1 | Question: 23

Question

A database of research articles in a journal uses the following schema.

$\text{(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, YEAR, PRICE)}$

The primary key is '$\text{(VOLUME, NUMBER, STARTPAGE, ENDPAGE)}$

and the following functional dependencies exist in the schema.

$\begin{align*}&(\text{VOLUME , NUMBER, STARTPAGE, ENDPAGE}) & \rightarrow \text{TITLE} \\ &(\text{VOLUME, NUMBER}) & \rightarrow  \text{ YEAR} \\ &(\text{VOLUME, NUMBER, STARTPAGE, ENDPAGE})        &\rightarrow  \text{PRICE} \end{align*}$

The database is redesigned to use the following schemas

$(\text{VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE}) \\ \text{(VOLUME, NUMBER, YEAR})$

Which is the weakest normal form that the new database satisfies, but the old one does not?

• A. $\text{1NF}$
• B. $\text{2NF}$
• C. $\text{3NF}$
• D. $\text{BCNF}$

Comments

Here is a trick, just observe what they’re asking and eventually you’ll find the answer to this question will be highest normal satisfied by earlier schema + 1.

Answer 1

Consider given relation in short form as R(V,N,S,E,T,Y,P).

FDs: { $VNSE\rightarrow T, VNSE\rightarrow P,VN\rightarrow Y$}

PK: VNSE

For relation R, $VN\rightarrow Y$ i.e $partofkey\rightarrow nonkey$ So, Partial Functional FD exist. Hence, R is not in 2NF.

R1(V,N,S,E,T,P) AND R2(V,N,Y)

R1 and R2 are in BCNF because for all FDs, $X\rightarrow Y$, X is a superkey in both R1 and R2.

But, they ask for weakest normal form that R1 and R2 satisfy but R does not, which is 2NF.

Correct Ans: (B)

Comments

Great Explanation