## Normalization

 Question 1

Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE?

 A A cell in R holds a set instead of an atomic value. B R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is not a proper subset of any key. C R has a nontrivial functional dependency X→A, where X is not a superkey and A is a non-prime attribute and X is a proper subset of some key. D R has a nontrivial functional dependency X→A, where X is not a superkey and A is a prime attribute.
Database-Management-System       Normalization       GATE 2020       Video-Explanation
Question 1 Explanation:
R(ABCD)
FDs:
AB → C
BC → A
(BD)+ = BD ✖
(ABD)+ = ABDC ✔
(CBD)+ = CBDA ✔
Candidate keys = {ABD, CBD}
• The relation R is in 3NF, as there are no transitive dependencies.
• The relation R is not in BCNF, because the left side of both the FD’s are not Super keys.
• In R, BC → A is a non-trivial FD and in which BC is not a Super key and A is a prime attribute.
 Question 2
In a relational data model, which one of the following statements is TRUE?
 A A relation with only two attributes is always in BCNF. B If all attributes of a relation are prime attributes, then the relation is in BCNF. C Every relation has at least one non-prime attribute. D BCNF decompositions preserve functional dependencies.
Database-Management-System       Normalization       GATE 2022       Video-Explanation
Question 2 Explanation:
A relation with only two attributes will always be in BCNF.
Example:
R(A, B).
Two functional dependencies possible for the relation: (1) A->B and (2) B->A
If there is no functional dependency, we can assume trivial functional dependencies like AB->A and AB->B.
In all cases, functional dependencies like A->B, A must be a key.
So they all will be in BCNF irrespective of the functional depencies set.
 Question 3

For a database relation R(a,b,c,d), where the domains a, b, c, d include only atomic values, only the following functional dependencies and those that can be inferred from them hold:

```   a → c
b → d  ```

This relation is

 A in first normal form but not in second normal form B in second normal form but not in third normal form C in third normal form D None of the above
Database-Management-System       Normalization       GATE 1997
Question 3 Explanation:
Candidate key is ab.
Since all a, b, c, d are atomic. So the relation is in 1NF.
Checking the FD's
a → c
b → d
We can see that there is partial dependencies. So it is not 2NF.
 Question 4

Which normal form is considered adequate for normal relational database design?

 A 2 NF B 5 NF C 4 NF D 3 NF
Database-Management-System       Normalization       GATE 1998
Question 4 Explanation:
3NF, is considered as adequate for normal relational database design, because we can have a 3NF decomposition which is dependency preserving and lossless (not possible for any higher forms).
 Question 5

Consider the schema R = (S T U V) and the dependencies S → T, T → U, U → V and V → S. Let R = (R1 and R2) be a decomposition such that R1 ∩ R2 ≠ ∅ . The decomposition is

 A not in 2NF B in 2NF but not 3NF C in 3NF but not in 2NF D in both 2NF and 3NF
Database-Management-System       Normalization       GATE 1999
Question 5 Explanation:
Since R1 ∩ R2 ≠ ∅, so the decomposition is lossless join. Now since all the attributes are keys, so R1 ∩ R2 will be a key of the decomposed relation.
And since every attribute is key so the decomposed relation will be in BCNF and hence in 3NF.
 Question 6

Relation R with an associated set of functional dependencies, F, is decomposed into BCNF. The redundancy (arising out of functional dependencies) in the resulting set of relations is

 A Zero B More than zero but less than that of an equivalent 3NF decomposition C Proportional to the size of F+ D Indetermine
Database-Management-System       Normalization       GATE 2002
Question 6 Explanation:
If a relation is in BCNF then there is no functional dependencies.
 Question 7

For relation R = (L, M, N , O, P), the following dependencies hold:

`   M → O NO → P P → L and L → MN `

R is decomposed into R1 = (L, M, N , P) and R2 = (M, O).

(a) Is the above decomposition a lossless-join decomposition? Explain.
(b) Is the above decomposition dependency-preserving? If not, list all the dependencies that are not preserved.
(c) What is the highest normal form satisfied by the above decomposition?
 A Theory Explanation is given below.
Database-Management-System       Normalization       GATE 2002
 Question 8

Consider the following functional dependencies in a database:

```  Data_of_Birth → Age
Age → Eligibility
Name → Roll_number
Roll_number → Name
Course_number → Course_name
Course_number → Instructor

The relation (Roll_number, Name, Date_of_birth, Age) is:

 A in second normal form but not in third normal form B in third normal form but not in BCNF C in BCNF D in none of the above
Database-Management-System       Normalization       GATE 2003
Question 8 Explanation:
Three FD's are valid from the above set of FD's for the given relation.
Date_of_Birth → Age
Name → Roll_number
Roll_number → Name
Candidate keys for the above are:
(Date_of_Birth, Name) and (Date_of_Birth, Roll_number)
Clearly, there is a partial dependency,
Date_of_Birth → Age
So, it is only in 1NF.
 Question 9

The relation scheme Student Performance (name, courseNo, rollNo, grade) has the following functional dependencies:

```   name, courseNo → grade
name → rollNo
rollNo → name ```

The highest normal form of this relation scheme is

 A 2 NF B 3 NF C BCNF D 4NF
Database-Management-System       Normalization       GATE 2004
Question 9 Explanation:
Student Performance (name, courseNo, rollNo, grade)
name → rollNo →(III)
rollNo → name →(IV)
Candidate keys: name, courseNo (or) rollNo
Its is not BCNF, because the relation III, there is no relationship from super key.
name → rollNo
It is not BCNF, name is not super key.
It belongs to 3NF, because if X→Y, Y is prime then it is in 3NF.
 Question 10

Which one of the following statements about normal forms is FALSE?

 A BCNF is stricter than 3NF B Lossless, dependency-preserving decomposition into 3NF is always possible C Lossless, dependency-preserving decomposition into BCNF is always possible D Any relation with two attributes is in BCNF
Database-Management-System       Normalization       GATE 2005
Question 10 Explanation:
Option A: BCNF is stricter than 3NF. In this all redundancy based on functional dependency has been removed.
Option B: Lossless, dependency preserving decomposition into 3NF is always possible.
Option C: It is false.
It is not possible to have dependency preserving in BCNF decomposition.
→ Let take an example, 3NF can't be decomposed into BCNF.
Option D: It is true.
Let consider two attributes (X, Y).
If (X→Y), X is a candidate key. It is in BCNF and vice-versa.
 Question 11

(a) Consider the relation scheme R(A, B, C) with the following functional dependencies:
A, B → C, C → A
Show that the scheme R is the Third Normal Form (3NF) but not in Boyce-Code Normal Form (BCNF).
(b) Determine the minimal keys of relation R.

 A Theory Explanation.
Database-Management-System       Normalization       GATE 1995
 Question 12

A table has fields Fl, F2, F3, F4, F5 with the following functional dependencies

` F1 → F3,  F2→ F4,  (F1.F2) → F5 `

In terms of Normalization, this table is in

 A 1 NF B 2 NF C 3 NF D None
Database-Management-System       Normalization       GATE 2005-IT
Question 12 Explanation:
F1 → F3 ......(i)
F2 → F4 ......(ii)
(F1⋅F2) → F5 .....(iii)
F1F2 is the candidate key.
F1 and F2 are the prime key.
In (i) and (ii) we can observe that the relation from P → NP which is partial dependency. So this is in 1NF.
 Question 13
Consider the relation R(P, Q, S, T, X, Y, Z, W) with the following functional dependencies Consider the decomposition of the relation R into the consistent relations according to the following two decomposition schemes.
D1: R=[(P,Q,S,T); (P,T,X); (Q,Y); (Y,Z,W)]
D2: R=[(P,Q,S);(T,X);(Q,Y);(Y,Z,W)]
Which one of the following options is correct?
 A D1is a lossy decomposition, but D2is a lossless decomposition. B Both D1and D2are lossy decompositions. C Both D1and D2are lossless decompositions. D D1is a lossless decomposition, but D2is a lossy decomposition.
Database-Management-System       Normalization       GATE 2021 CS-Set-1       Video-Explanation
Question 13 Explanation:

Given functional dependencies set:

PQ->X

P->YX

Q->Y

Y->ZW

• While merging the tables there should be some common attribute(s) and it should be a candidate key of one of the tables. • R1 should be merged with R2 because PT is a key of R2.
• R3 should be merged with PQSTX because Q is a key of R3.
• R4 should be merged with PQSTXY because Y is a key of R4. • R1 should be merged with R3 because Q is a key of R3.
• R4 should be merged with PQSY because Y is a key of R4.
• Now, there is no common attribute in between R2(TX) and PQSYZW.
• Hence, D2 is lossy decomposition.
 Question 14

Let R (A, B, C, D, E, P, G) be a relational schema in which the following functional dependencies are known to hold:

`AB → CD, DE → P, C → E, P → C and B → G.`
The relational schema R is
 A in BCNF B in 3NF, but not in BCNF C in 2NF, but not in 3NF D not in 2NF
Database-Management-System       Normalization       GATE 2008-IT
Question 14 Explanation:
Candidate key is AB.
Since there is a partial dependency B→G.
So the relational schema R is Not in 2NF.
 Question 15

Choose the correct alternatives (More than one may be correct).

Indicate which of the following statements are true: A relational database which is in 3NF may still have undesirable data redundancy because there may exist:

 A Transitive functional dependencies. B Non-trivial functional dependencies involving prime attributes on the right-side. C Non-trivial functional dependencies involving prime attributes only on the left-side. D Non-trivial functional dependencies involving only prime attributes. E Both (B) and (D).
Database-Management-System       Normalization       GATE 1990       Video-Explanation
Question 15 Explanation:
A) Transitive functional dependency, so not in 3NF.
B) 3NF because right side is prime attribute.
C) Not in 3NF, because lets suppose ABC is a candidate key. Now consider
AB → Non-prime attribute
which show it is not in 3NF
D) Involves only prime attribute, so right side should definitely contain only prime attribute. So in 3NF.
 Question 16

Which one of the following statements if FALSE?

 A Any relation with two attributes is in BCNF B A relation in which every key has only one attribute is in 2NF C A prime attribute can be transitively dependent on a key in a 3NF relation D A prime attribute can be transitively dependent on a key in a BCNF relation
Database-Management-System       Normalization       GATE 2007
Question 16 Explanation:
Rules for BCNF is:
i) It is in 3NF.
ii) For any dependency X→ Y
where X is a super key.
iii) Functional dependency has been removed.
Option D is false.
→ Because a prime attribute can’t be transitive dependent on a key in a BCNF relation.
 Question 17

Consider the following relational schemes for a library database:

Book(Title, Author, Catalog_ no, Publisher, Year, Price)
Collection (Title, Author, Catalog_no)

with in the following functional dependencies:

I. Title Author → Catalog_no
II. Catalog_no → Title Author Publisher Year
III. Publisher Title Year → Price

Assume {Author, Title} is the key for both schemes. Which of the following statements is true?

 A Both Book and Collection are in BCNF B Both Book and Collection are in 3NF only C Book is in 2NF and Collection is in 3NF D Both Book and Collection are in 2NF only
Database-Management-System       Normalization       GATE 2008
Question 17 Explanation:
Given that
Book(Title, Author, Catalog_no, Publisher, Year, Price)
Collection(Title, Author, Catalog_no)
I) Title Author ⟶ Catalog_no ⟶ BCNR
II) Catalog_no ⟶ Title, Author, Publisher, Year ⟶ 3NF
III) Publisher Title Year ⟶ Price ⟶ 2NF Book’s in 2NF
Collection is in 3NF.
 Question 18

Which of the following is NOT a superkey in a relational schema with attributes V, W, X, Y, Z and primary key VY?

 A VXYZ B VWXZ C VWXY D VWXYZ
Database-Management-System       Normalization       GATE 2016 [Set-1]       Video-Explanation
Question 18 Explanation:
It is given that “VY” is a primary key of the relational schema.
Any superset of “VY” is a super key. So, option (B) does not contain “Y”.
 Question 19
A database of research articles in a journal uses the following schema.(VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, YEAR, PRICE)
The primary key is (VOLUME, NUMBER, STARTPAGE, ENDPAGE) and the following functional dependencies exist in the schema. (VOLUME, NUMBER, STARTPAGE, ENDPAGE) → TITLE (VOLUME, NUMBER) → YEAR (VOLUME, NUMBER, STARTPAGE, ENDPAGE) → PRICE The database is redesigned to use the following schemas. (VOLUME, NUMBER, STARTPAGE, ENDPAGE, TITLE, PRICE) (VOLUME, NUMBER, YEAR) Which is the weakest normal form that the new database satisﬁes, but the old one does not?
 A 1NF B 2NF C 3NF D BCNF
Database-Management-System       Normalization       GATE 2016 [Set-1]       Video-Explanation
Question 19 Explanation:
Journal (V, N, S, E, T, Y, P)
V – VOLUME
N – NUMBER
S – STARTPAGE
E – ENDPAGE
T – TITLE
Y – YEAR
P – PRICE
Primary key: (V, N, S, E)
FD set:
(V, N, S, E) → T
(V, N) → Y
(V, N, S, E) → P
In (V, N) → Y; V, N is a part of the key and Y is non-prime attribute.
So, it is a partial dependency.
Now, the schema “Journal” is in 1NF but not in 2NF.
The database is redesigned as follows: Both R1 and R2 are in BCNF.
Therefore, 2NF is the weakest normal form that the new database satisfies, but the old one does not.
 Question 20

R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition?

 A A → B, B → CD B A → B, B → C, C → D C AB → C, C → AD D A → BCD
Database-Management-System       Normalization       GATE 2001
Question 20 Explanation:
We have, R (A, B, C, D) and the Functional Dependency set = {AB→C, C→AD}. We decompose it as R1(A, B, C) and R2(C, D). This preserves all dependencies and the join is lossless too, but the relation R1 is not in BCNF. In R1 we keep ABC together otherwise preserving {AB→C} will fail, but doing so also causes {C→A} to appear in R1. {C→A} violates the condition for R1 to be in BCNF as C is not a super key. Condition that all relations formed after decomposition should be in BCNF is not satisfied here.
 Question 21

Let the set of functional dependencies F = {QR → S, R → P, S → Q} hold on a relation schema X = (PQRS). X is not in BCNF. Suppose X is decomposed into two schemas Y and Z, where Y = (PR) and Z = (QRS).

Consider the two statements given below.

• I. Both Y and Z are in BCNF

II. Decomposition of X into Y and Z is dependency preserving and lossless

Which of the above statements is/are correct?

 A I only B Neither I nor II C II only D Both I and II
Database-Management-System       Normalization       GATE 2019       Video-Explanation
Question 21 Explanation:
Y = (PR)
R → P
R+ = RP
* In R → P, 'R' is a super key. So, Y is in BCNF.
Z = (QRS)
QR → S
S → Q
CK's = QR, RS
* In, S → Q, 'S' is not a super key. So, Z is not in BCNF.
* Y is in BCNF and Z is not in BCNF.
* 'R' is common attribute in the relations Y and Z. and R is candidate key for Y. So, the decomposition is lossless.
* The FD, R → P is applicable on Y and QR → S, S → Q are applicablein 2.
So, the decomposition is dependency preserving.
* Hence, Statement II is correct.
 Question 22

Given the following two statements:

```
S1: Every table with two single-valued
attributes is in 1NF, 2NF, 3NF and BCNF.

S2: AB->C, D->E, E->C is a minimal cover
for the set of functional dependencies
AB->C, D->E, AB->E, E->C.```

Which one of the following is CORRECT?

 A S1 is TRUE and S2 is FALSE. B Both S1 and S2 are TRUE. C S1 is FALSE and S2 is TRUE. D Both S1 and S2 are FALSE.
Database-Management-System       Normalization       GATE 2014 [Set-1]       Video-Explanation
Question 22 Explanation:
S1: True
If we can prove the relation is in BCNF then by default it would be in 1NF, 2NF, 3NF also.
Let R(AB) be a two attribute relation, then
If {A→B} exists then BCNF since {A}+ = AB = R
If {B→A} exists then BCNF since {B}+ = AB = R
If {A→B, B→A} exists then BCNF since A and B both are Super Key now.
If {No non-trivial Functional Dependency} then default BCNF.
Hence it’s proved that a Relation with two single-valued attributes is in BCNF hence it’s also in 1NF, 2NF, 3NF.
S2: False
The canonical cover for the given FD set is {AB→C, D→E, AB→E, E→C}. As we can see AB→E is not covered in minimal cover since {AB}+ = ABC in the given cover {AB→C, D→E, E→C}
There are 22 questions to complete.

Register Now