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 cell in R holds a set instead of an atomic value.  
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a nonprime attribute and X is not a proper subset of any key.
 
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a nonprime attribute and X is a proper subset of some key.  
R has a nontrivial functional dependency X→A, where X is not a superkey and A is a prime attribute. 
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 nontrivial FD and in which BC is not a Super key and A is a prime attribute.
Question 2 
A relation with only two attributes is always in BCNF.  
If all attributes of a relation are prime attributes, then the relation is in BCNF.  
Every relation has at least one nonprime attribute.  
BCNF decompositions preserve functional dependencies. 
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
in first normal form but not in second normal form  
in second normal form but not in third normal form  
in third normal form  
None of the above 
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 4 
Which normal form is considered adequate for normal relational database design?
2 NF  
5 NF  
4 NF  
3 NF 
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
not in 2NF  
in 2NF but not 3NF  
in 3NF but not in 2NF  
in both 2NF and 3NF 
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
Zero  
More than zero but less than that of an equivalent 3NF decomposition  
Proportional to the size of F^{+}  
Indetermine 
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 R_{1} = (L, M, N , P) and R_{2} = (M, O).
 (a) Is the above decomposition a losslessjoin decomposition? Explain.
(b) Is the above decomposition dependencypreserving? If not, list all the dependencies that are not preserved.
(c) What is the highest normal form satisfied by the above decomposition?
Theory Explanation is given below. 
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 (Roll_number, Course_number) → Grade
The relation (Roll_number, Name, Date_of_birth, Age) is:
in second normal form but not in third normal form  
in third normal form but not in BCNF  
in BCNF  
in none of the above 
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 rollNo, courseNo → grade name → rollNo rollNo → name
The highest normal form of this relation scheme is
2 NF  
3 NF  
BCNF  
4NF 
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 10 
Which one of the following statements about normal forms is FALSE?
BCNF is stricter than 3NF  
Lossless, dependencypreserving decomposition into 3NF is always possible
 
Lossless, dependencypreserving decomposition into BCNF is always possible  
Any relation with two attributes is in BCNF 
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 viceversa.
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 BoyceCode Normal Form (BCNF).
(b) Determine the minimal keys of relation R.
Theory Explanation. 
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
1 NF  
2 NF  
3 NF  
None 
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 decomposition of the relation R into the consistent relations according to the following two decomposition schemes.
D_{1}: R=[(P,Q,S,T); (P,T,X); (Q,Y); (Y,Z,W)]
D_{2}: R=[(P,Q,S);(T,X);(Q,Y);(Y,Z,W)]
Which one of the following options is correct?
D^{1}is a lossy decomposition, but D^{2}is a lossless decomposition.
 
Both D^{1}and D^{2}are lossy decompositions.  
Both D^{1}and D^{2}are lossless decompositions.  
D^{1}is a lossless decomposition, but D^{2}is a lossy decomposition. 
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
in BCNF  
in 3NF, but not in BCNF  
in 2NF, but not in 3NF  
not in 2NF 
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:
Transitive functional dependencies.  
Nontrivial functional dependencies involving prime attributes on the rightside.
 
Nontrivial functional dependencies involving prime attributes only on the leftside.
 
Nontrivial functional dependencies involving only prime attributes.  
Both (B) and (D). 
B) 3NF because right side is prime attribute.
C) Not in 3NF, because lets suppose ABC is a candidate key. Now consider
AB → Nonprime 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?
Any relation with two attributes is in BCNF  
A relation in which every key has only one attribute is in 2NF  
A prime attribute can be transitively dependent on a key in a 3NF relation  
A prime attribute can be transitively dependent on a key in a BCNF relation 
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?
Both Book and Collection are in BCNF  
Both Book and Collection are in 3NF only
 
Book is in 2NF and Collection is in 3NF  
Both Book and Collection are in 2NF only 
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?
VXYZ  
VWXZ  
VWXY  
VWXYZ 
Any superset of “VY” is a super key. So, option (B) does not contain “Y”.
Question 19 
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?
1NF  
2NF  
3NF  
BCNF 
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 nonprime 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 R_{1} and R_{2} 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 → B, B → CD  
A → B, B → C, C → D  
AB → C, C → AD  
A → BCD 
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?
I only  
Neither I nor II  
II only  
Both I and II 
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 singlevalued 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?
S1 is TRUE and S2 is FALSE.  
Both S1 and S2 are TRUE.  
S1 is FALSE and S2 is TRUE.  
Both S1 and S2 are FALSE. 
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 nontrivial Functional Dependency} then default BCNF.
Hence it’s proved that a Relation with two singlevalued 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}