The Gateway to Computer Science Excellence

+41 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?

- $1NF$
- $2NF$
- $3NF$
- $\text{BCNF}$

+39 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$.

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

+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

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.

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

When part of candidate key along with some non-key derive some non-key is it violate 2NF definition?

+47 votes

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

Answer B

Answer B

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

+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**

+3 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

- V,N,Y
- V,N,S,E,T,P

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

Hence the required answer is **2NF .**

- All categories
- General Aptitude 1.9k
- Engineering Mathematics 7.5k
- Digital Logic 2.9k
- Programming and DS 4.9k
- Algorithms 4.4k
- Theory of Computation 6.2k
- Compiler Design 2.1k
- Databases 4.1k
- CO and Architecture 3.4k
- Computer Networks 4.2k
- Non GATE 1.4k
- Others 1.4k
- Admissions 595
- Exam Queries 573
- Tier 1 Placement Questions 23
- Job Queries 72
- Projects 18

50,737 questions

57,385 answers

198,557 comments

105,368 users