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

if new design has volume, Number as the primary key for one table then it must be the lossless ,depency preserving ,attribute preserving and in bcnf.

---

NF database New Satisfies that Old one Does NOT

BCNF ---> (Strongest)

3NF

2NF -----> ( Weakest)

Since, weakest is asked -> Ans: B.2NF

---

the reltion satisfies 2NF

Option B is correct

Answer 1

The actual design is in $\text{1NF}$ because there are partial dependencies in the given $\text{FD}$ set so the original $\text{DB}$ design is in $\text{1NF}$ but not $\text{2NF}$.

Now, the new design is removing all the partial dependencies so its in $\text{2NF}$

So, the weakest form that the new schema satisfies that the old one couldn't is $\text{2NF}$ answer is $B$.

Comments

Thanks @Abhrajyoti00

---

The original one is in  1NF but not in 2NF because a FD [(VOLUME ,NUMBER) --> YEAR] is violating 2NF as YEAR is an non prime attribute.

---

The new one is in 2nf, 3nf and Bcnf. But weakest normal form that the new database satisfies,but the old one does not is asked.

Answer 2

Original Database schema is not in 2NF, modified one is in BCNF.. so weakest form would be 2NF
Answer B

Comments

@raun220 Only one FD is applied on new relation : 

(VOLUME, NUMBER) -->  YEAR 
so it's in BCNF as Volume, Number is key for this relation

---

always read question properly we have to tell the weakest normal form possible
original one is in 1NF
after Modification schema is in BCNF( 1NF , 2NF , 3NF, BCNF)
So out of both we have to take lowest normal form  of modified schema which is not satisfied by orginal schema  its is 2NF

---

How is the new one removing partial dependency?

Answer 3

i m using shortcut terms ...

RE-DESIGN database :

 (v,n,s,e,t,y,p)  breaks into (v,n,s,e,t,p) and (v,n,y)

 (v,n,s,e,t,p) satisfy (v,n,s,e) ------> t  and (v,n,s,e)-----> p     note : candidate keys are v,n,s,e  prime attributes are (v,n )and non prime attributes are(s,e,t,p) . satisfy 2nf condition i.e. no partial dependency also satisfy 3nf and bcnf condition but weaker is 2nf.

 (v,n,y ) satisfy (v,n) ------> y    note : candidate keys are v,n  prime attributes are (v,n )and non prime attribute is (y)

satisfy 2nf condition i.e. no partial dependency also satisfy 3nf and bcnf condition but weaker is 2nf.

ORIGINAL-database :

in this middle fd not satisfy 2nf condition i.e. (not partial dependency)

ANS is 2nf

---

---

---

Comments

"satisfy 2nf condition i.e. no partial dependency also satisfy 3nf and bcnf condition but weaker is 2nf."

Then why not 1nf even more weaker?

---

@shivam

you said nonprime attributes are (s, e ,t , p) ,

but as i know non-prime attribute (attributes which are not part of any candidate key)

but here S and E is part of candidate key.

so according to me non- prime attribute will be (t,p, y)

Answer 4

Answer
Option B

Solution

Conditions for Normalization

1NF - All tuples are  in atomic form

2NF - Every tuples is fully functionally depended on  Candidate Key

3NF -  Transitivity should be avoided . Or  Non Prime attributes must not determine non - prime attributes

BCNF - All values are to be depended on superkey . Dependancy May NOT be preserved .

 

Given Data

Key : $ V,N,S,E $

 

From the diagram it is clear that $ Year $  is not fully functionally depended on Primary Key . So It is not in 2NF . 

The schema has to be divided into two to be in 2NF 

1.     V,N,Y
2. V,N,S,E,T,P

 

Here , the two tables are in BCNF by the conditions given above . 

Hence the required answer is 2NF .

Comments

nice explanation

Answer 5

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

Answer 6

<!--[if gte mso 9]> 16.00 <![endif]--><!--[if gte mso 9]> Normal 0 false false false EN-IN X-NONE X-NONE <![endif]--><!--[if gte mso 9]> <![endif]--><!--[if gte mso 10]> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-priority:99; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin-top:0cm; mso-para-margin-right:0cm; mso-para-margin-bottom:8.0pt; mso-para-margin-left:0cm; line-height:107%; mso-pagination:widow-orphan; font-size:11.0pt; font-family:"Calibri",sans-serif; mso-ascii-font-family:Calibri; mso-ascii-theme-font:minor-latin; mso-hansi-font-family:Calibri; mso-hansi-theme-font:minor-latin; mso-bidi-font-family:"Times New Roman"; mso-bidi-theme-font:minor-bidi; mso-font-kerning:1.0pt; mso-ligatures:standardcontextual; mso-fareast-language:EN-US;} <![endif]-->

 We can clearly understand the BCNF decomposition.

Process:

1)     First find the FD which doesn’t satisfy  the  condition of BCNF.  

     Lets say R(A,B,C) AàB then decomposition R1(A,B) and R(A,C) removing the RHS.If in the Question Asked about highest normal form then it is BCNF and here in the Question asking about weakest normal form therefore it is 2NF. (removing the partial dependencies).