Normalization

Question 1
   
A
Schema I
B
Schema II
C
Schema III
D
Schema IV
       Database-Management-System       Normalization       Gate 2018
Question 1 Explanation: 
Schema I:
Registration (rollno, courses) rollno → courses
For the given schema Registration ‘rollno’ is a primary key.
Left-side of the functional dependency is a superkey so, Registration is in BCNF.
Schema II:
Registrstion (rollno, courseid, email)
rollno, courseid → email
email → rollno
From the given schema the candidate key is (rollno + courseid).
There is no part of the key in the left hand of the FD’s so, it is in 2NF.
In the FD email→rollno, email is non-prime attribute but rollno is a prime attribute. So, it is not a transitive dependency. No transitive dependencies so, the schema is in 3NF.
But in the second FD email→rollno, email is not a superkey. So, it is violating BCNF.
Hence, the schema Registration is in 3NF but not in BCNF.
Schema III:
Registration (rollno, courseid, marks, grade)
rollno, courseid → marks, grade
marks → grade
For the schema the candidate key is (rollno + courseid).
There are no part of the keys are determining non-prime attributes. So, the schema is in 2NF. In the FD marks → grade, both the attributes marks and grade are non-prime. So, it is a transitive dependency. The FD is violating 3NF.
The schema Registration is in 2NF but not in 3NF.
Schema IV:
Registration (rollno, courseid, credit)
rollno, courseid → credit
courseid → credit
The candidate key is (rollno + courseid).
In the FD, courseid → credit, courseid is part of the key (prime attribute) and credit is non-prime. So, it is a partial dependency. The schema is violating 2NF.
Question 2
Which of the following is NOT a superkey in a relational schema with attributes V,W, X,Y, Z and primary key V Y?
A
VXYZ
B
VWXZ
C
VWXY
D
VWXYZ
       Database-Management-System       Normalization       2016 set-01
Question 2 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 3
 
A
1NF
B
2NF
C
3NF
D
BCNF
       Database-Management-System       Normalization       2016 set-01
Question 3 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 4
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-01)
Question 4 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}
Question 5
A
3
B
4
C
5
D
6
       Database-Management-System       Normalization       Gate 2013
Question 5 Explanation: 
 The attribute D is not part of any FD's. So D can be a candidate key or it may be part of the candidate key.
 Now D+ = {D}.
 Hence we have to add A,B,C,E,F,G,H to D and check which of them are Candidate keys of size 2.
AD+ = {ABCDEFGH}
BD+ = {ABCDEFGH}
ED+ = {ABCDEFGH}
FD+ = {ABCDEFGH}
 But CD+, GD+ and HD+ does not give all the attributes hence CD, GD and HD are not candidate keys.
 Hence no. of candidate keys are 4: AD, BD, ED, FD.
Question 6
A
in 1NF, but not in 2NF.
B
in 2NF, but not in 3NF.
C
in 3NF, but not in BCNF.
D
in BCNF.
       Database-Management-System       Normalization       Gate 2013
Question 6 Explanation: 
 The attribute D is not part of any FD's. So D can be a candidate key or it may be part of the candidate key.
 Now D+ = {D}.
 Hence we have to add A,B,C,E,F,G,H to D and check which of them are Candidate keys of size 2.
AD+ = {ABCDEFGH}
BD+ = {ABCDEFGH}
ED+ = {ABCDEFGH}
FD+= {ABCDEFGH}
 But CD+, GD+ and HD+ does not give all the attributes hence CD, GD and HD are not candidate keys.
 Here Candidate keys are AD, BD, ED and FD.
 A → BC, B → CFH and F → EG etc are partial dependencies.
 So given relation is in 1NF, but not in 2NF.
Question 7
Which of the following is TRUE?  
A
Every relation in 3NF is also in BCNF
B
A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R
C
Every relation in BCNF is also in 3NF
D
No relation can be in both BCNF and 3NF
       Database-Management-System       Normalization       Gate 2012
Question 7 Explanation: 
BCNF is a stronger version 3NF. So straight from definition of BCNF every relation in BCNF will also be in 3NF.
Question 8
 
A
The schema is in BCNF
B
The schema is in 3NF but not in BCNF
C
The schema is in 2NF but not in 3NF
D
The schema is not in 2NF
       Database-Management-System       Normalization       2009
Question 8 Explanation: 
From the given data the FDs will be
(Sid, Street) → Sname
As Sid is a primary key, then
(Sid, Street) will be super key.
Hence, it is in BCNF.
Question 9
 
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 9 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 10
 
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 10 Explanation: 
Candidate key is AB.
Since there is a partial dependency B→G.
So the relational schema R is Not in 2NF.
Question 11
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 11 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 12
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 12 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 t wo attributes (X, Y).
If (X→Y), X is a candidate key. It is in BCNF and vice-versa.
Question 13
 
A
2 NF
B
3 NF
C
BCNF
D
4NF
       Database-Management-System       Normalization       Gate-2004
Question 13 Explanation: 
Student Performance (name, courseNo, rollNo, grade)
name, courseNo  → grade →(I)
rollNo, courseNo → grade →(II)
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 14
 
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 14 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 15
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 15 Explanation: 
If a relation is in BCNF then there is no functional dependencies.
Question 16

Relation R is decomposed using a set of functional dependencies, F, and relation S is decomposed using another set of functional dependencies, G. One decomposition is definitely BCNF, the other is definitely 3NF, but it is not known which is which. To make a quaranteed identification, which one of the following tests should be used on the decompositions? (Assume that the closures of F and G are available).

A
Dependency-preservation
B
Lossless-join
C
BCNF definition
D
3NF definition
       Database-Management-System       Normalization       Gate-2002
Question 16 Explanation: 
If it is BCNF then it is 3NF. But the relation is in 3NF then it is need not to be in BCNF.
Question 17
 
A
Theory Explanation is given below.
       Database-Management-System       Normalization       Gate-2002
Question 18
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 18 Explanation: 
R1 ∩ R2 = ∅. This makes the decomposition lossless join, as all the attributes are keys, R1 ∩ R2 will be a key of the decomposed relations (lossless condition says the common attribute must be a key in atleast one of the decomposed relation). Now, even the original relation R isin 3NF (even BCNF) as all the attributes are prime attributes (in fact each attribute is a candidate key). Hence, any decomposition will also be in 3NF (even BCNF).
Question 19
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 19 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 20
     
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 20 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.
So answer is option (A).
Question 21
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 21 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.
There are 21 questions to complete.