Previous Year Questions: Normal Form | Database Management System (DBMS) - Computer Science Engineering (CSE) PDF Download

Q1: Which of the following statements about a relation R in first normal form (1NF) is/are TRUE ?  (2024 SET 1)
(a) R can have a multi-attribute key
(b) R cannot have a foreign key
(c) R cannot have a composite attribute
(d) R cannot have more than one candidate key
Ans: (a, c)
Sol: B ,D are straightforward false because we can have a foreign key in 1NF and also we can have more than one candidate key.
The correct answer is A and C.

Q2: In a relational data model, which one of the following statements is TRUE?  (2022)
(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
Ans: (a)
Sol: Option A : A relation with only two attributes is always in BCNF
let relation R(A,B), then possible FD’s are

1. A → B, in this case A is minimal Superkey, So this relation should be in BCNF
2. B → A, in this case B is minimal Superkey, So this relation should be in BCNF
3. AB → AB , in this case AB is minimal Superkey, So this relation should be in BCNF

So, given statement is Correct.
Alternatively we can think like, We can’t further divide a relation which had only two attributes, then it must be in BCNF.
Option B : If all the attributes of a relation are Prime attributes, then the relation is in BCNF
classic counter example : R(A,B,C) with FD set = {AB → C, C → A}
in this relation all attributes are prime but this relation is not in BCNF due to “ C → A ”
Correct statement is : If all the attributes in the relation are Prime attributes, then it is in 3NF.
Option C : Every relation has at least one non-prime attribute
counter example : R(A,B,C) with FD set = {AB → C, C → A}
in this relation all attributes are prime
Correct statement is : Every relation has atleast one prime attribute.
Option D : BCNF decompositions preserve functional dependencies
for any relation, we can guarantee there exist at least one decomposition which is in 3NF, lossless and Dependency Preserving. But we can not guarantee that at least one decomposition which is BCNF, lossless and Dependency Preserving.
Option A is correct

Q3: Suppose the following functional dependencies hold on a relation U with attributes P , Q , R , S , and T :  (2021 SET 2)
P → Q R
R S → T
Which of the following functional dependencies can be inferred from the above functional dependencies? [MSQ]
(a) PS →T
(b) R→T
(c) P → R
(d) PS →Q
Ans: (a, c, d )
Sol: Option A: ( P S ) ⁺ = P , Q , R , S , T . So, P S → T holds.
Option B: ( R ) ⁺ = R . So, R → T does not hold.
Option C: ( P ) ⁺ = P , Q , R . So, P → R holds.
Option D: ( P S ) ⁺ = P , Q , R , S , T . So, P S → Q holds.

Q4: Consider a relational table R that is in 3NF, but not in BCNF. Which one of the following statements is TRUE?  (2020)
(a)R has a nontrivial functional dependency X→A, where X is not a superkey and A is a prime attribute.
(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) A cell in R holds a set instead of an atomic value.
Ans: (a)
Sol: In 3NF where functional dependency is of type X→ Y
X can be the super key or Y can be the prime attribute
Whereas in BCNF where functional dependency is of type
X should be super key (BCNF is more strict compared to  3 N F)

• Option (C) says it has a partial dependency ( not even 2NF) .
•  Option (D) multiple values in a cell. i.e not atomice ( not even 1NF) 
• Option (B)  says X is not a super key and Y is not a prime attribute. Therefore not 3NF .

 Ans (A): Says X is not a super key but Y is a prime attribute. Satisfies one of the conditions of the 3NF formal definition. As X is not a Super Key it is not in BCNF .

Q5: Let the set of functional dependencies F = { Q R → 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: (2019)
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) Both I and II
(b) I only
(c) II only
(d) Neither I nor II
Ans: (c)
Sol: Y is in BCNF because binary attribute.
Z is not in BCNF because S → Q is in Z and S is not Super key.
Dependency Preserving:

• QR →S in Z
• R→ P is in Y
• S → Q is in 

So it is dependency preserving. Lossless:
Y ∩ Z = R which is is key of Y .
Only 2^nd is correct.
So Option C is the answer

Q6: Consider the following four relational schemas. For each schema, all non-trivial functional dependencies are listed. The underlined attributes are the respective primary keys.  (2018)

Which one of the relational schemas above is in 3NF but not in BCNF?
(a) Schema I
(b) Schema II
(c)Schema III
(d) Schema IV
Ans: (b)
Sol: rollno, courseid  → email
(rollno, courseid is a super key,so it comes under 3NF as well as BCNF).
email → rollno
Here, email is not a key though but rollno comes under prime-attribute. Hence it's in
3NF but not BCNF

Q7: The following functional dependencies hold true for the relational schema R{V,W,X,Y,Z}:  (2017 SET 1)
V → W
VW → X
Y →  VX
 Y → Z
Which of the following is irreducible equivalent for this set of functional dependencies ?
(a) V → W
V → X
Y → V
Y → Z
(b) V → W
W → X
Y →  V
Y →  Z
(c) V → W
V →  X
Y →  V
Y → X
Y → Z
(d) V → W
W →  X
Y →  V
Y → X
Y → Z
Ans: (a)
Sol: In option B and option D there is a dependency W → X which is not implied by the question and hence they are definitely wrong.
Now in option C ) Y → X can be removed as it can be implied as Y → V and V → X .
Hence, option (A) is correct.

Q8: A database of resear charticles in a journal uses the following schema.  (2016 SET 1)

Which is the weakest normal form that the new database satisfies, but the old one does not?
(a) 1NF
(b) 2NF
(c) 3NF
(d) BCNF
Ans: (b)
Sol: The actual design is in 1NF because 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 .

Q9: Consider the relation X(P,Q,R,S,T,U) with the following set of functional dependencies  (2015 SET 3)
 F= {{P,R} → {S,T}, {P,S,U} → {Q,R} }
Which of the following is the trivial functional dependency in F + F + , where  is closure of F ?
(a) {P,R} → {S,T}
(b) {P,R} → {R,T}
(c) {P,S } → {S}
(d) {P,S,U} → {Q}
Ans: (c)
Sol:  Option C is correct because { P , S } → { S }
for trivial FD, if X → Y then Y must be a subset of X and for non trivial FD X ∩ Y = ∅ .  and here { S } is subset of { P, S}
PS: Trivial means something which is always there. An attribute set always determines any of the component attributes and this is always true irrespective of the relation instance. Hence, this FD becomes trivial.

Q10:  A prime attribute of a relation scheme R is an attribute that appears  (2014 SET 3)
(a) in all candidate keys of R.
(b) in some candidate key of R.
(c) in a foreign keys of R
(d) only in the primary key of R
Ans: (b) 
Sol: The attributes of a candidate key are called the prime attributes. Suppose A B C is one candidate key of a Relation R ( A B C D E F G H ) .
Then the attributes A , B and C all are prime attributes. Similarly if A B D is also another candidate key in the same relation R , then D is also a prime attribute. And conversely, an attribute that does not occur in ANY candidate key is called a non-prime attribute.
 Answer (B).

Q11: Given the following two statements:  (2014 SET 1)
 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.
Ans: (a)
Sol: (A) S1 is TRUE and S2 is FALSE.
A relation with 2 attributes is always in BCNF
The two sets of functional dependencies are not the same. We can not derive A B → E from the 1 ^st set.

Q12: Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values.  (2013)
F={CH → G, A →BC, B → CFH, E→A, F→EG} is a set of functional dependencies (FDs) so that F⁺ is exactly the set of FDs that hold
for R.
The relation R is
(a) in 1NF, but not in 2NF.
(b) in 2NF, but not in 3NF.
(c) in 3NF, but not in BCNF.
(d) in BCNF
Ans: (a)
Sol: Here, candidate keys are A D , B D , E D and F D .
Partial dependency exists A → B C , B → C F H and F → E G etc. In the following FD s.
For example partial dependency A → C exists in A → B C and B → C and B → H in B → C F H . etc.
So, given relation is in 1NF ,but not in 2NF .
Correct Answer: A 

Q13: Relation R has eight attributes ABCDEFGH. Fields of R contain only atomic values.  (2013)
F={CH→G, A→BC, B→CFH, E→A, F→EG} is a set of functional dependencies (FDs) so that F ⁺is exactly the set of FDs that hold
for R.
How many candidate keys does the relation R have?
(a) 3
(b) 4
(c) 5
(d) 6
Ans: (b)
Sol: Here, we can see that D is not part of any F D ′ s , hence D must 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 .
We can proceed as: AD+= {A,B,C,D,E,F,G,H}
Similarly we see BD+ , ED+ and FD+ also gives us all the attributes. Hence, AD,BD,ED,FD are definitely the candidate keys.
But CD+, GD+ and HD+ doesnnt give all the attributes hence, C D , G D and H D are not candidate keys.
Also, we can't add any of A , B , E , F to C D , G D , H D as they will again give us superset of A D , B D , E D , F D .
Hence, we can only add among C , G , H to C D , G D , H D .
Adding C to G D and H D we get G C D , H C D . Taking closure and we will see they are not candidate keys.
Adding H to GD we get GHD which is also not a candidate key.(no more options with 3 attributes possible)
Now we need to check for candidate keys with 4 attributes. Since, only remaining options are C G H and we have to add D only possible key of size 4 is C G H D whose closure also doesn't give us all of the attributes in the relation (All possible options covered)
Hence, no of candidate keys are 4 : AD,BD,ED,FD.
Correct Answer: B

Q14: Which of the following is TRUE?  (2012)
(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
Ans: (c)
Sol:  The correct answer is "option 3".
Normalization is used to minimize redundancy from a set of relations.
It is used to organize data effectively in the database.
Normal forms are used to reduce redundancy from the database.
Third Normal form: A relation is said to be in third normal form if it is in 2NF & there must not exist any transition dependency.
Also, it must satisfy these properties:
1. For the function A → B, A should be a super key.
2. B should be a part of a key attribute or prime attribute i.e. B should be a part of a candidate key.
BCNF: A relation is said to be in Boyce-Codd normal form (BCNF) if it is in 3NF & must satisfy this property:
1. For the function A → B, A should be a super key.
Option 1: FALSE
Any relation in BCNF must be in 3NF.
Option 2: FALSE
A relation R is in 3NF if every non-prime attribute of R is fully functionally dependent on every key of R but this does not guarantee transitive dependency.
Option 3: TRUE
Every relation is BCNF must be in 3NF.
Option 4: FALSE
Any relation can be in both BCNF & 3NF if it satisfies the condition of BCNF.
Hence, the correct answer is “option 3”.