## Normalization

 Question 1

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 1 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 2

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

```Schema I: Registration(rollno, courses)
Field ‘courses’ is a set-valued attribute containing the set of
courses a student has registered for.
Non-trivial functional dependency
rollno → courses

Schema II: Registration (rollno, coursid, email)
Non-trivial functional dependencies:
rollno, courseid → email
email → rollno

Schema III: Registration (rollno, courseid, marks, grade)
Non-trivial functional dependencies:
rollno, courseid, → marks, grade

Schema IV: Registration (rollno, courseid, credit)
Non-trivial functional dependencies:
rollno, courseid → credit
courseid → credit
```

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
Database-Management-System       Normalization       GATE 2018       Video-Explanation
Question 2 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
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 3

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 3 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 4
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 4 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 5

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]
Question 5 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 6

Relation R has eight attributes ABCDEFGH . Fields of R contain only atomic values.

F = {CH→G, A→BC, B→CFG, 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
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.
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 7

Relation R has eight attributes ABCDEFGH . Fields of R contain only atomic values.

F = {CH→G, A→BC, B→CFG, 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.
Database-Management-System       Normalization       GATE 2013
Question 7 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.
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 8

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 8 Explanation:
BCNF is a stronger version 3NF. So straight from definition of BCNF every relation in BCNF will also be in 3NF.
 Question 9

Consider the following relational schema:

```
Suppliers(sid:integer, sname:string, city:string, street:string)
Parts(pid:integer, pname:string, color:string)
Catalog(sid:integer, pid:integer, cost:real)```

Assume that, in the suppliers relation above, each supplier and each street within a city has a unique name, and (sname, city) forms a candidate key. No other functional dependencies are implied other than those implied by primary and candidate keys. Which one of the following is TRUE about the above schema??

 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       GATE 2009
Question 9 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 10

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 10 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 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 two attributes (X, Y).
If (X→Y), X is a candidate key. It is in BCNF and vice-versa.
 Question 13

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

 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

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:

 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

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 17

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 17 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 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:
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 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

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

(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 22

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
Question 22 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 23

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
Question 23 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 24

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 24 Explanation:
Candidate key is AB.
Since there is a partial dependency B→G.
So the relational schema R is Not in 2NF.
 Question 25

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 25 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 26
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
Question 26 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 27
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 27 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 28
BCNF is not used for cases where a relation has
 A Two (or more) candidate keys B Two candidate keys and composite C The candidate key overlap D Two mutually exclusive foreign keys
Database-Management-System       Normalization       ISRO-2007
Question 28 Explanation:
A relation is in Boyce-Codd normal form if all attributes which are determinants are also candidate keys.
Transformation into Boyce-Codd normal form deals with the problem of overlapping keys.
 Question 29
Which normal form is based on the concept of ‘full functional dependency’ is
 A First Normal Form B Second Normal Form C Third Normal Form D Third Normal Form
Database-Management-System       Normalization       ISRO CS 2011
Question 29 Explanation:
A full functional dependency is a state of database normalization that equates to the normalization standard of Second Normal Form (2NF).
Second normal form (2NF) is a normal form used in database normalization.
To qualify for second normal form a relation must:
→be in first normal form (1NF)
→not have any non-prime attribute that is dependent on any proper subset of any candidate key of the relation.
A non-prime attribute of a relation is an attribute that is not a part of any candidate key of the relation.
 Question 30
Consider the following dependencies and the BOOK table in a relational database design. Determine the normal form of the given relation.
ISBN → Title
ISBN → Publisher
 A First Normal Form B Second Normal Form C Third Normal Form D BCNF
Database-Management-System       Normalization       ISRO CS 2013
Question 30 Explanation:
→All attributes in the given functional dependencies are atomic values So it is FIrst normal form.
→In order to check whether it is in Second normal form or not,fist we need to find the candidate key.
→After finding closure of attribute of ISBN from the above dependencies,the candidate key is ISBN.
→A relation is in 2NF if it is in 1NF and every non-prime attribute of the relation is dependent on the whole of every candidate key.
→The given dependencies satisfies second normal form rules as as non prime attribute of the relation is dependent on the whole of every candidate key.
→So the above dependencies are in Second normal form.
 Question 31
Consider the following table The table is in which normal form?
 A First Normal Form B Second Normal Form C Third Normal Form but not BCNF D Third Normal Form but BCNF
Database-Management-System       Normalization       ISRO CS 2014
Question 31 Explanation:
From the given figure, we can write functional dependencies as
AB→ CDE
C→ B
The candidate keys are "AB" and "AC".
The relation is not in BCNF because the functional dependencies C→ B do not contains the key AB or AC
So, the given relation is in 3NF but not in BCNF
 Question 32

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

a → c
b → d

The relation is in

 A First normal form but not in second normal form B Second normal form but not in third normal form C Third normal form D BCNF
Database-Management-System       Normalization       UGC-NET CS 2018 JUNE Paper-2
Question 32 Explanation:
Primary key of given relation is “ab”.
And there is a partial dependency exist in given FD’s so the given relation is in 1NF but not in second normal form.
 Question 33

A many-to-one relationship exists between entity sets r1 and r2. How will it be represented using functional dependencies if Pk(r) denotes the primary key attribute of relation r?

 A Pk(r1) → Pk(r2) B Pk(r2) → Pk(r1) C Pk(r2) → Pk(r1) and Pk(r1) → Pk(r2) D Pk(r2) → Pk(r1) or Pk(r1) → Pk(r2)
Database-Management-System       Normalization       UGC-NET CS 2018 JUNE Paper-2
Question 33 Explanation: Here, we have a many to one relationship between between Set(r1) and Set(r2).
→ Elements of Set(r2) can’t identify elements of Set(r1) because one value element in Set(r2) is pointing to more than one element of Set(r1).
→ So, we can’t say Pk(r2) → Pk(r1) but elements of Set(r1) are pointing to exactly one element of Set(r2) so we can say that Pk(r2) → Pk(r1) because r1 is uniquely identifying r2.
 Question 34
Normalization from which is based on transitive dependency is classified as:
 A First normal form B Second normal form C Fourth normal form D Third normal form
Database-Management-System       Normalization       Nielit Scientist-B IT 4-12-2016
Question 34 Explanation:
The table is in 3NF if and only if both of the following conditions hold:
1. The relation R (table) is in second normal form (2NF)
2. Every non-prime attribute of R is non-transitively dependent on every key of R.
 Question 35
The primary key is selected from the:
 A Composite keys B Determinants C Candidate keys D Foreign keys
Database-Management-System       Normalization       Nielit Scientist-B IT 4-12-2016
Question 35 Explanation:
● A candidate key is a column or a row of columns in a table which identifies any database record without utilizing any other data.
● Each table can have one or more candidate keys, but one candidate key is unique, and it is called the primary key.
 Question 36
Which one of the following statements about normal forms is FALSE?
 A BCNF is stricter than 3NF B Lossless,dependency preserving decomposition into BCNF is always possible C Lossless,dependency preserving decomposition into 3NF is always possible D Any relation with two attributes is BCNF
Database-Management-System       Normalization       Nielit Scientist-B IT 4-12-2016
Question 36 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 37
Process of analyzing relation schemas to achieve minimal redundancy and insertion or update anomalies is classified as:
 A normalized of data B denomination of data C isolation of data D denormalized of data
Database-Management-System       Normalization       Nielit Scientist-B CS 4-12-2016
Question 37 Explanation:
Normalization is a process of organizing the data in database to avoid data redundancy, insertion anomaly, update anomaly & deletion anomaly.
Most commonly used normal forms
First normal form(1NF)
Second normal form(2NF)
Third normal form(3NF)
Boyce & Codd normal form (BCNF)
 Question 38
Consider the following table : Faculty (facName, dept, office, rank, dateHired) (Assume that no faculty member within a single department has same name. Each faculty member has only one office identified in office. 3NF refers to third normal form and BCNF refers to Boyce-Codd Normal Form. Then Faculty is
 A Not in 3NF, in BCNF B In 3NF, not in BCNF C In 3NF, in BCNF D Not in 3NF, not in BCNF
Database-Management-System       Normalization       ISRO DEC 2017 22- Soon
Question 38 Explanation:
Possible FD’S for given instance are:
FacName ➝ dept,office
 Question 39

Which of the following relation schemas is definitely in BCNF?

 A R1(A,B) B R4(A,B,C,D,E) C R3(A,B,C,D) D R2(A,B,C)
Database-Management-System       Normalization       JT(IT) 2018 PART-B Computer Science
Question 39 Explanation:
BCNF properties
1. BCNF is free from redundancy.
2. If a relation is in BCNF, then 3NF is also also satisfied.
3. Every Binary Relation ( a Relation with only 2 attributes ) is always in BCNF.
4. Sometimes going for BCNF form may not preserve functional dependency.
 Question 40
For a database relation R(a,b,c,d) where the domains of a,b,c and d include only
atomic values, only the following functional dependencies and those that can be inferred
from them hold
A → c
B → d
The relation is in
 A 1 normal form but not in 2 normal form B 2 normal form but not in 3 normal form C 3 normal form D None of these
Database-Management-System       Normalization       Nielit Scientific Assistance IT 15-10-2017
Question 40 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 41
If a table is in BCNF and has no multivalued dependencies, then it is said to be in
 A 3NF B 2NF C 4NF D 5NF
Database-Management-System       Normalization       KVS DEC-2013
Question 41 Explanation:
BCNF
● A relation R is in BCNF if and only if it is in 3NF and no any prime attribute is transitively dependent on the primary key.
● An attribute C is transitively dependent on attribute A if there exist an attribute B such that A->B and B->C.
4NF
● A table is in the 4NF if it is in BCNF and has no multivalued dependencies.
 Question 42
Which of the following statements is NOT true with respect to database normalization?
 A A 4NF is more immune against logical inconsistencies than a 3NF table. B A 3NF table will have fewer anomalies than a 2NF table C A 3NF table is more vulnerable than a 2NF table D A database is said to be in 3NF if all its tables are in 3NF
Database-Management-System       Normalization       KVS DEC-2013
Question 42 Explanation:
→ A relation R is in 4NF if and only if the following conditions are satisfied:
1. It should be in the Boyce-Codd Normal Form (BCNF).
2. The table should not have any Multi-valued Dependency.
 Question 43
Which of the following statements best describes the main reasons for normalising relational database?
(i) To achieve physical data independence
(ii) To remove data anomalies (insertion,update,delete,anomalies)
(iii) To save space on disk.
 A (i),(ii) and (iii) B (i) and (ii) C (i) and (iii) D (ii) and (iii)
Database-Management-System       Normalization       KVS DEC-2017
Question 43 Explanation:
Normalising relational database reasons are
1. To remove data anomalies (insertion,update,delete,anomalies)
2. To save space on disk(It happens because of avoiding redundancy)
 Question 44
Match the following : A i-a, ii-c, iii-b, iv-d B i-d, ii-c, iii-a, iv-b C i-d, ii-c, iii-b, iv-a D i-a, ii-b, iii-c, iv-d E None of the above
Database-Management-System       Normalization       UGC NET CS 2004 Dec-Paper-2
Question 44 Explanation:
→ 2NF ensures no partial dependency in the relation.
→ A relation is in 3NF if it is in eliminates transitive dependency from the relation.
→ 4NF eliminates multivalued dependency from a relation.
→ 5NF eliminates join dependency from a table.
 Question 45
Multi-valued dependency among attribute is checked at which level ?
 A 2 NF B 3 NF C 4 NF D 5 NF
Database-Management-System       Normalization       UGC NET CS 2005 june-paper-2
Question 45 Explanation:
→ Multivalued dependency among attribute is checked in 4NF.
→ A relation is in 4NF if it is in BCNF and has no multivalued dependency.
→ A relation will contain Multivalued dependency if there exists a dependency A → B such that for a single value of A, multiple values of B exists.
 Question 46
The best normal form of relation scheme R(A, B, C, D) along with the set of functional dependencies F = {AB → C, AB → D, C → A, D → B} is
 A Boyce-Codd Normal form B Third Normal form C Second Normal form D First Normal form
Database-Management-System       Normalization       UGC NET CS 2014 Dec-Paper-2
Question 46 Explanation:
AB → C
AB → D
C → A
D → B
A​ +​ ={A}
B​ +​ ={B}
C​ +​ ={C,A}
D​ +​ ={D,B}
Since single attributes can't determine all the attributes of relation. Find closure of combination of two attributes to check whey could be the key for the relation or not.
AB​ +​ ={A,B,C,D}
AC​ +​ ={A,C}
BC​ +​ ={B,C,A,D}
AB​ +​ ={A,B,C,D}
AC​ +​ ={A,C}
BC​ +​ ={B,C,A,D}
BD​ +​ ={B,D}
CD​ +​ ={C,D,B,A}
So, AB,AD,BC,CD are the candidate keys for the given relation. All attributes are key attributes and since last two functional dependencies are violating BCNF property (that LHS if a functional dependency should be a super key) so the given relation is in 3NF only.
 Question 47
Which of the following statements is 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 3NF relation. D A prime attribute can be transitively dependent on a key in BCNF relation.
Database-Management-System       Normalization       UGC NET CS 2014 June-paper-2
Question 47 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 48
Which of the following is true ?
 A A relation in BCNF is always in 3NF. B A relation in 3NF is always in BCNF. C BCNF and 3NF are same. D A relation in BCNF is not in 3NF.
Database-Management-System       Normalization       UGC NET CS 2012 Dec-Paper-2
Question 48 Explanation:
Explanation: Question 49
Decomposition help in eliminating some of the problems of bad design
 A Redundancy B Inconsistencies C Anomalies D All of the above
Database-Management-System       Normalization       UGC NET CS 2011 June-Paper-2
Question 49 Explanation:
Decomposition help in eliminating redundancy, inconsistencies and anomalies.
 Question 50
Which normal form is considered as adequate for usual database design ?
 A 2NF B 3NF C 4NF D 5NF
Database-Management-System       Normalization       UGC NET CS 2013 June-paper-2
Question 50 Explanation:
3NF normal form is considered as adequate for usual database design. BCNF doesn't guarantee dependency preservation.
 Question 51
Match the following : A (a)-1, (b)-5, (c)-4, (d)-2, (e)-3 B (a)-1, (b)-5, (c)-2, (d)-3, (e)-4 C (a)-5, (b)-1, (c)-2, (d)-3, (e)-4 D (a)-5, (b)-4, (c)-3, (d)-1, (e)-2
Database-Management-System       Normalization       UGC NET CS 2010 June-Paper-2
Question 51 Explanation:
Garbage collection in→ Java
Nameless object→ Within a statement
Template support→ Generic programming
A forward reference→ defines a class
Derived class inherits from base class→ Member function
 Question 52
Match the following : A I-(a), II-(c), III-(b), IV-(d) B I-(d), II-(a), III-(b), IV-(c) C I-(c), II-(d), III-(a), IV-(b) D I-(d), II-(b), III-(a), IV-(C) E I-(c), II-(a), III-(b), IV-(d)
Database-Management-System       Normalization       UGC NET CS 2010 June-Paper-2
Question 52 Explanation:
→ 2NF ensures that the relation contains no partial dependency. A relation is in 2NF if it is in 1NF and have no partial dependency.
→ 3NF eliminates transitive dependency from the relation. A relation can be in 3NF if it is in 2NF and have no transitive dependency.
→ 4NF eliminates multivalued dependency from the relation. A relation is in 4NF if it is in BCNF and have no multivalued dependency.
→ 5NF ensures that the relation contains no join dependency. A relation is in 5NF if it is 4NF and ave no join dependency.
Note: Given question is wrong. We are added correct extra option. Excluded for correction.
 Question 53
Which of the following is true ?
 A A relation in 3NF is always in BCNF B A relation in BCNF is always in 3NF C BCNF and 3NF are totally different D A relation in BCNF is in 2NF but not in 3NF
Database-Management-System       Normalization       UGC NET CS 2008-june-Paper-2
Question 53 Explanation: Question 54
If a relation is in 2NF and 3NF forms then :
 A no non-prime attribute is functionally dependent on other non-prime attributes B no non-prime attribute is functionally dependent on prime attributes C all attributes are functionally independent D prime attribute is functionally independent of all non-prime attributes
Database-Management-System       Normalization       UGC NET CS 2007-Dec-Paper-2
Question 54 Explanation:
2NF: A relation is said to be in 2NF if there is no partial dependency which means a relation is said to be in 2NF if there is no Prime key attribute which is functionally defining a non-key prime attribute. But a prime attribute can functionally determine a prime attribute.
Example: Let us consider a relation R(A, B, C, D) and let’s take ABC is the primary key of the given relation R then A, B, C are the prime attributes and D is a non-key prime attribute.
Now for 2NF, B → D should not exist but B → A can exist.
3NF: A relation is said to be in 3NF if no transitive dependency exist in the relation i.e. either the LHS of a functional dependency should be a super key or the RHS of a functional dependency should be a Prime attribute.
Option(A) is correct it means the the LHS will always be a super key or both LHS or RHS of a functional dependency will be a prime key attribute. In both the cases the relation will be in 3NF and when the relation will be in 3NF it will automatically be in 2NF.
Option (B) is incorrect because according to this if there could be a case when in a functional dependency its LHS will not have prime key attribute and RHS will also not be a prime key attribute. So in that case the relation can't be in 3NF. so this option is not correct.
Option(C) is incorrect, if all attributes will be functionally independent then the conditions of being in 2NF and 3NF will not be satisfied. SSo this option is incorrect.
 Question 55
If every non-key attribute is functionally dependent on the primary key, then the relation is in __________ .
 A First normal form B Second normal form C Third normal form D Fourth normal form
Database-Management-System       Normalization       UGC NET CS 2017 Nov- paper-3
Question 55 Explanation:
1NF : A relation is in 1NF if it does not contain multi-value attributes and composite attributes.
2NF: A relation is in 2NF is there is no partial dependency exist in the relation. i.e.
primary_key → non-key attribute
prime_attribute → non-key attribute.
3NF : A relation is in 3NF if there is no transitive dependency or we can say that a relation is in 3NF if either LHS of a functional dependency is a super key or the RHS of the functional dependency is a prime key attribute.
4NF : A relation is in 4NF if there is no multivalued dependency exist in the relation.
Hence the correct answer is option (C)
NOTE: A prime key attribute is a attribute which is the part of primary key attributes. For example: If ABC is the the primary key then A,B,C, AB, AC, BC are the prime key attributes.
 Question 56
Consider a relation R (A, B, C, D, E, F, G, H), where each attribute is atomic, and following functional dependencies exist.
CH → G
A → BC
B → CFH
E → A
F → EG
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
Database-Management-System       Normalization       UGC NET CS 2017 Nov- paper-3
Question 56 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.
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 57
Find the normalization transformation that maps a window whose lower left corner is at (1, 1) and upper right corner is at (3, 5) onto a viewport that is the entire normalized device screen.
 A B C D Database-Management-System       Normalization       UGC NET CS 2017 Nov- paper-3
 Question 58
For a database relation R(A, B, C, D) where the domains of A, B, C and D include only atomic values, only the following functional dependencies and those that can be inferred from them are :
A → C
B → D
The relation R is in _______.
 A First normal form but not in second normal form. B Both in first normal form as well as in second normal form. C Second normal form but not in third normal form. D Both in second normal form as well as in third normal form.
Database-Management-System       Normalization       UGC NET CS 2017 Jan- paper-3
Question 58 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 59
In relational database, if a relation R is in BCNF, then which of the following is true about relation R?
 A R is in 4NF B R is not in 1NF C R is in 2Nf and not in 3NF D R is in 2NF and 3NF
Database-Management-System       Normalization       UGC NET June-2019 CS Paper-2
Question 59 Explanation:
If a relation R is in BCNF then R will be in 1NF, 2NF and 3NF. Since the requirement of a BCNF relation is that the left hand side of each functional dependency should be a super key because of this, neither there will be a partial dependency in relation R nor there will be transitive dependency.
 Question 60
Select the 'False' statement from the following statements about Normal Forms:
 A Lossless preserving decomposition into 3NF is always possible B Lossless preserving decomposition into BCNF is always possible C Any Relation with two attributes is in BCNF D BCNF is stronger than 3NF
Database-Management-System       Normalization       UGC NET CS 2015 June Paper-3
Question 60 Explanation:
Option(A) is true because 3NF preserves lossless decomposition and functional dependency.
Option(B) is false because BCNF ensures lossless decomposition but do not preserves functional dependency.
Option(C) is true because Any Relation with two attributes is in BCNF
Option(D) is true because BCNF ensure less number of duplicate values in a relation than 3NF.
 Question 61
The Relation Vendor Order (V_no, V_ord_no, V_name, Qty_sup, unit_price) is in 2NF because:
 A Non_key attribute V_name is dependent on V_no which is part of composite key B Non_key attribute V_name is dependent on Qty_sup C Key attribute Qty_sup is dependent on primary_key unit price D Key attribute V_ord_no is dependent on primary_key unit price E None of the above
Database-Management-System       Normalization       UGC NET CS 2015 June Paper-3
Question 61 Explanation:
A relation is in 2NF if there is no partial dependency in a relation. A relation will be in 2NF if the LHS of a functional dependency is either a primary key or a non- key attribute or if all the attributes of a relation are prime attributes.
Option(A) is not correct because Non_key attribute V_name is dependent on V_no which is part of composite key i.e there is a partial dependency exists because of V_no.
Option(B) is also not correct because there are chances of having a partial dependency because Qty_sup could be a prime attribute.
Option(C) and Option(D) are correct because in both the options Key attributes depend upon primary key.
 Question 62
Which of the following relation schema is always in BCNF?
 A R( A,B,C,D) B R( A,B,C) C R( A,B,C,D,E) D R( A,B)
Database-Management-System       Normalization       CIL Part - B
Question 62 Explanation:
BCNF Properties:
1. BCNF is the advance version of 3NF. It is stricter than 3NF.
2. A table is in BCNF if every functional dependency X → Y, X is the super key of the table.
3. For BCNF, the table should be in 3NF, and for every FD, LHS is super key.
4. Two (or) Binary tuples are always be in BCNF
 Question 63
If every non-key attribute functionally dependent on the primary key, then the relation will be in
 A First normal form B Second normal form C Third normal form D Fourth normal form
Database-Management-System       Normalization       ISRO CS 2020       Video-Explanation
Question 63 Explanation:
1. Let’s take a relation R(ABC) with functional dependencies {A → B, B → C, A → C}.
2. In the question it is not mentioned that non-prime attribute is only dependent on primary key.
3. So, the functional dependency B → C, is perfectly valid.
4. This relation is in 2NF but not in 3NF because of every non-key attribute is transitively dependent on the primary key. Here “A” will be a candidate key.
 Question 64
For a database Relation R (A,B,C,D) where the domains of A,B,C, and D only include atomic values, only the following functional dependency and those that can be inferred from them hold: A C, B D. The relation is in:
 A 1NF but not in 2NF B 2 NF but not in 3NF C 3NF but not in BCNF D BCNF but not in 4NF
Database-Management-System       Normalization       APPSC-2016-DL-CS
Question 64 Explanation:
Candidate key is AB.
And clearly there is partial functional dependency which is
A → C ,in which non prime attribute C is partially dependent on A
 Question 65
A 3NF table which does not have multiple overlapping candidate keys is said to be in -------------
 A 1NF B 2NF C 4NF D BCNF
Database-Management-System       Normalization       APPSC-2016-DL-CS
Question 65 Explanation:
If in 3NF table there is no multiple overlapping candidate keys then that table is also in BCNF.
 Question 66

If a relation is an 2NF and 3NF forms, then

 A no non-prime attribute is functionally dependent on other non-prime attributes B no non-prime attribute is functionally depend on the prime attributes C all attributes are functionally independent D prime attribute is functionally independent of all non-prime attributes
Database-Management-System       Normalization       APPSC-2016-DL-CA
Question 66 Explanation:
If a relation is an 2NF and 3NF forms, then no non-prime attribute is functionally dependent on other non-prime attributes because for 3NF the condition is that for A->B , either A is a super key or B is a prime attribute.
 Question 67

Which normal form is considered adequate for relational database design?

 A 2 NF B 3 NF C 4 NF D BCNF
Database-Management-System       Normalization       APPSC-2016-DL-CA
Question 67 Explanation:
Because 3NF is more restricted than 2NF and is always lossless join and dependency preserving unlike in BCNF.
 Question 68

A relation is in ____ form if every field consists only of atomic values, that is, not lists or sets.

 A First normal B Third normal C Second normal D Fourth normal
Database-Management-System       Normalization       CIL 2020
Question 68 Explanation:
If every field consists only of atomic values (i.e. no composite values) ,then that relation is in first normal form.
 Question 69

Partial dependencies are removed to achieve which normal form?

 A First normal form B Second normal form C BCNF D Third normal form
Database-Management-System       Normalization       APPSC-2012-DL CA
Question 69 Explanation:
In 2NF partial dependencies are not allowed. Hence partial dependencies are removed to achieve second normal form.
 Question 70
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 C Proportional to the size of F+ D Indeterminate
Database-Management-System       Normalization       APPSC-2012-DL-CS
Question 70 Explanation:
Redundancy in the resulting set of relations decomposed BCNF is zero.
 Question 71
Any binary relation is in
 A 1 NF B 2 NF C 3 NF D BCNF
Database-Management-System       Normalization       TNPSC-2012-Polytechnic-CS
Question 71 Explanation:
Every binary relation(relation with two attributes ) is always in BCNF.
Let there be relation R(C,D).
Possible functional dependencies are,
C->D (Candidate key C)
or
D->C (Candidate key is D)
or
CD->CD (Candidate key is CD)
No violation is there in any of the above three cases for BCNF.
 Question 72
Which normal form is considered adequate for normal relational database design?
 A 2NF B 4NF C 3NF D 5NF
Database-Management-System       Normalization       APPSC-2012-DL-CS
Question 72 Explanation:
3NF normal form is considered adequate for normal relational database design. A relational database table is often described as “normalized” if it is in the Third Normal Form because most of the 3NF tables are free of insertion, update, and deletion anomalies
 Question 73
“X is not a proper subset of any Key” is a 3NF violation called as:
 A Partial dependency B Total dependency C Transitive dependency D None of the above
Database-Management-System       Normalization       TNPSC-2017-Polytechnic-CS
Question 73 Explanation:
If X is not a proper subset of any key means there is no partial dependency. And if there is a violation then the only possibility left is transitive dependency, means if there is transitive dependencies then there is 3NF violation.
 Question 74
Which one of the following statements is FALSE?
 A A relation with two attributes is in BCNF B Lossless dependency preserving decomposition into BCNF is always possible C BCNF is stricter than 3NF D Lossless, dependency preserving decomposition into 3NF is always possible
Database-Management-System       Normalization       TNPSC-2017-Polytechnic-CS
Question 74 Explanation:
Option B is false because Lossless decomposition into BCNF is always possible but dependency preserving decomposition into BCNF is not always possible.
 Question 75

Consider the following relational schemas for a library database : Book (Title, Author, Catalog_no, Publisher, Year, Price) Collection(Title, Author, Catalog_no) with 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 schemas. Which one of the following is true ?
 A Both Book and Collection are in BCNF. B Both Book and Collection are in 3NF. C Book is in 2NF and Collection in 3NF. D Both Book and Collection are in 2NF.
Database-Management-System       Normalization       UGC NET CS 2014 June-paper-3
Question 75 Explanation:
Book (Title, Author, Catalog_no, Publisher, Year, Price)
(Author, Title) is the key .
Title, Author → Catalog_no
Catalog_no →Publisher
Here we are having a transitive dependency
(Key→non-key
Non-key→ non-Key)
Hence this relation is not in 3NF.
Collection(Title, Author, Catalog_no)
(Author, Title) is the key
Catalog_no → Title, Author
Since here LHS is not a Key so it is not in BCNF but since the RHS having prime key attribute so it is in 3NF.
 Question 76

Consider the schema R = {S, T, U, V} and the dependencies

S → T, T → U, U → V and V → S If R = (R1 and R2)

be a decomposition such that R1 ∩ R2 = φ then the decomposition is
 A not in 2NF B in 2NF but not in 3NF C in 3NF but not in 2NF D in both 2NF and 3NF
Database-Management-System       Normalization       UGC NET CS 2014 June-paper-3
Question 76 Explanation:
Decomposition of a relation R into R1 and R2 is said to be lossless only if R1 ∩ R2 ≠ φ and the common attribute of R1 and R2 should be the primary key in any of the two decomposed relations(i.e.R1 and R2).
A relation which is in 2NF is always lossless.
In question, it is given that R1 ∩ R2 = φ which is violating lossless decomposition condition. Hence the given relation R is not in 2NF.
 Question 77
Which of the following is false ?
 A Every binary relation is never be in BCNF. B Every BCNF relation is in 3NF. C 1 NF, 2 NF, 3 NF and BCNF are based on functional dependencies. D Multivalued Dependency (MVD) is a special case of Join Dependency (JD).
Database-Management-System       Normalization       UGC NET CS 2014 Dec - paper-3
 Question 78

What is the highest normal form of a relation R(A, B, C, D, E) with FD set?

{B → A, A → C, BC → D, AC→ BE}
 A 2NF B 3NF C BCNF D 4NF
Database-Management-System       Normalization       HCU PHD CS MAY 2019
Question 78 Explanation:
B → A
A → C
BC → D
AC → BE
B+ = BACDE
A+ = ACBED
So A & B are Candidate key.
There is no partial dependency, so in 2NF.
But in the BC → D, neither BC is key nor D is prime attribute, hence not in 3NF.
Note: Official Key given option-C is correct.
 Question 79

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
 A 2NF B BCNF C 4NF D 3NF
Database-Management-System       Normalization       HCU PHD CS 2018 December
Question 79 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 80
Which of the following statements is false?
 A Any relation with two attributes is in BCNF B A relation in which every key has one attribute is in 2NF C A prime attribute can be transitively dependent on a key in 3NF relation D A prime attribute can be transitively dependent on a key in BCNF relation
Database-Management-System       Normalization       HCU PHD CS MAY 2017
Question 80 Explanation:
Option A is true.
Option B is true because there cant be any partial functional dependencies.
Option C is true because the condition for 3NF is either left side of functional dependencies is super key or right side of functional dependencies is prime attribute.
Option D is false because the condition for BCNF is that left side of functional dependencies must be super key.
 Question 81
The condition- “there are no non-trivial functional dependencies of attributes on anything other than a superset of a candidate key", is both necessary and sufficient for a database to be in
 A 2nd Normal form B 3rd Normal form C PJNF D BCNF
Database-Management-System       Normalization       HCU PHD CS MAY 2016
Question 81 Explanation:
The condition for the relation to be in BCNF is that the left side of functional dependencies must be super key.
 Question 82
Which of the following is a bottom-up approach to design a database by examining relationships among attributes ?
 A Functional dependency B Database modelling C Normalization D Decomposition
Database-Management-System       Normalization       HCU PHD CS MAY 2016
Question 82 Explanation:
A well- known approach to database design that can be used as a bottom-up approach is normalization
 Question 83
Empdt1(emp code,name,street,city,state,pincode)is a schema. For any pincode, there is only one city and state. AIso for a given street, city and state, there is just one pincode. In normalization terms, empdtl is a relation in
 A 1NF oniy B 2NF and hence in lNF C 3NF and hence also in 2NF and 1NF D BCNF and hence also in 3NF and 2NF and 1NF
Database-Management-System       Normalization       HCU PHD CS MAY 2016
Question 83 Explanation:
Given,
Pincode → city, state
Street, city, state → pincode
The candidate keys are
(empcode, name, pincode)
and
(empcode, name, street, city, state)
Since in both the functional dependencies right side is prime attributes but left side is not super keys. So the given relation is in 3NF but not in BCNF.
 Question 84

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 guaranteed identification, which one of the following tests should be used on the decompositions? (Assume that the closure of F and G are available).
 A Lossless-join B BCNF definition C 3NF definition D Dependency-Preservation
Database-Management-System       Normalization       HCU PHD CS MAY 2014
Question 84 Explanation:
Since one decomposition is in 3NF so it will not satisfy the BCNF conditions.Hence BCNF tests should be used on the decompositions.
 Question 85
A relational scheme is in ...... if it is in 1NF and if aII non prime attributes are fully functionally dependent on the relation key
 A Second Normal Form B Boyce Codd Normal Form C F-ourth Normal Form D First Normal Form
Database-Management-System       Normalization       HCU PHD CS MAY 2014
Question 85 Explanation:
If a relation schema is in 1NF and if aII non prime attributes are fully functionally dependent on the relation key then the relation schema is in 2NF, because the condition for the relation to be in 2NF is that ,non prime attributes should not be partially functionally dependent on key.
 Question 86

Suppose relation R(A,B,C,D,E) has the functional dependencies

A→ B

BC → D

BE → C