edited by
16,328 views
62 votes
62 votes

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?

  1. $\text{1NF}$
  2. $\text{2NF}$
  3. $\text{3NF}$
  4. $\text{BCNF}$
edited by

5 Answers

Best answer
63 votes
63 votes
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$.
edited by
64 votes
64 votes
Original Database schema is not in 2NF, modified one is in BCNF.. so weakest form would be 2NF
Answer B
10 votes
10 votes

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




8 votes
8 votes

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 .

Answer:

Related questions

30 votes
30 votes
2 answers
1
Sandeep Singh asked Feb 12, 2016
7,068 views
Which of the following is NOT a superkey in a relational schema with attributes $V,W,X,Y,Z$ and primary key $V\;Y$?$VXYZ$$VWXZ$$VWXY$$VWXYZ$
23 votes
23 votes
2 answers
3
Sandeep Singh asked Feb 12, 2016
10,009 views
Which one of the following is NOT a part of the ACID properties of database transactions?AtomicityConsistencyIsolationDeadlock-freedom