The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+32 votes
4.5k views

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. $1NF$
  2. $2NF$
  3. $3NF$
  4. $\text{BCNF}$
asked in Databases by Loyal (7.9k points)
edited by | 4.5k views

4 Answers

+33 votes
Best answer
The actual design is in $1NF$ coz there are partial dependencies in the given $FD$ set so the original DB design is in $1NF$ but not $2NF$.

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

So, the weakest form that the new schema satisfies that the old one couldn't is $2NF$ answer is $B$.
answered by Junior (679 points)
edited by
0
the new schema is violating 3NF right?
+9
New schema is BCNF.

1st and third dependency goes to first table and middle dependency goes to second table.In both only keys are determinant.So BCNF
0
Can you please explain why the old database is not in 2NF?

I followed the definition that for a table to be in 2NF, the non-key attributes mus be fully functional dependent on the key attribute.  Please tell me how the old table is not in 2NF.
0
If RHS contains non-prime attribute and LHS contains a part of key, 2NF is violated. (Volume, Number) is a part of the primary key and Year is a non-prime attribute. Hence, there is a partial dependency, which is a violation of 2NF
0

All BCNF is in 3NF, but all 3NF is not in BCNF from  

https://www.scribd.com/doc/23242133/All-BCNF-is-in-3NF-But-All-3NF-is-Not

So if it is BCNF then it is in 3NF. Is this correct someone verify  before gate exam pls. @Arjun sir

0
+41 votes
Original Database schema is not in 2NF, modified one is in BCNF.. so weakest form would be 2NF
Answer B
answered by Loyal (9.7k points)
edited by
+2

Thats the correct explaination..I missed that weakest form is asked.

0
sir, please explain

how modified one is in BCNF? i think it is  3NF
+8 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




answered by (145 points)
0
"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?
0
@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)
+1 vote

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 .

answered by Boss (22.6k points)
Answer:

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
47,931 questions
52,335 answers
182,382 comments
67,817 users