5.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}$

edited | 5.5k views

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$.
by Junior (701 points)
edited
0
the new schema is violating 3NF right?
+24
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
0
When part of candidate key along with some non-key derive some non-key is it violate 2NF definition?
+1
NO

partial dependency occurs only when

prime attribute which is part of candidate key $\implies$ non-prime attribute.

eg:-

$R(ABCD)$

$AB \rightarrow C$

$BC \rightarrow D$

here $AB$ is Candidate key $\implies$ $A,B$ are prime attributes.

$BC \rightarrow D$ is not a partial dependency.
0
So, BC -> D (non-key ->non-key) = transitive dependency ,so not in 3NF. am i correct?
0
yes
Original Database schema is not in 2NF, modified one is in BCNF.. so weakest form would be 2NF
by Loyal (9.7k points)
edited
+2

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

0

how modified one is in BCNF? i think it is  3NF
0

@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

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

by (141 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)

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 .

by Boss (21.5k points)
0
nice explanation