Gate Questions on Normalization

DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND

GATE PREPARATION

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 1
 Normalization
1. Consider a schema R(A, B, C, D) and functional dependencies A -> B and C -> D. Then the
decomposition of R into R1 (A, B) and R2(C, D) is (Gate – 2001)
(a) dependency preserving and lossless join
(b) lossless join but not dependency preserving
(c) dependency preserving but not lossless join
(d) not dependency preserving and not lossless join
Answer : C

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 2
 Normalization
Explanation :
While decomposing a relational table we must verify the following properties:
i) Dependency Preserving Property: A decomposition is said to be dependency preserving if F+=(F1 ∪ F2 ∪ .. Fn)+,
Where F+=total functional dependencies(FDs) on universal relation R, F1 = set of FDs of R1, and F2 = set of FDs of R2.
For the above question R1 preserves A->B and R2 preserves C->D. Since the FDs of universal relation R is preserved by R1
and R2, the decomposition is dependency preserving.

ii) Lossless-Join Property:

The decomposition is a lossless-join decomposition of R if at least one of the following functional dependencies is in F+:-
a) R1 ∩ R2 -> R1
b) R1 ∩ R2 -> R2
It ensures that the attributes involved in the natural join ( ) are a candidate key for at least one of the two relations. In the
above question schema R is decomposed into R1 (A, B) and R2(C, D), and R1 ∩ R2 is empty. So, the decomposition is not
lossless.

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 3
 Normalization

2. A table has fields F1, F2, F3, F4, and F5, with the following functional dependencies:
F1->F3
F2->F4
(F1,F2)->F5
in terms of normalization, this table is in (GATE-2005 (IT))
(a) 1NF (b) 2NF (c) 3NF (d) None of these

Answer : (a) – 1NF

Explanation:
Since the primary key is not given we have to derive the primary key of the table. Using the closure set of
attributes we get the primary key as (F1,F2). From functional dependencies, "F1->F3, F2->F4", we can
see that there is partial functional dependency therefore it is not in 2NF. Hence the table is in 1NF.

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 4
 Normalization
3. The maximum number of superkeys for the relation schema R(E, F, G, H) with E as the key
is _____. (Gate 2014 Set -02)
(a) 3
(b) 4
( c) 8
( d) 12
Answer : ( c) - 8

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 5
 Normalization

4. Refer the given table , (studentname, studentage) to be the key, the value of X Should not be equal to
(Gate 2014)

(a) 18
(b) 19
( c) 20
( d) 21

Answer : ( b) - 19

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 6
 Normalization
5. Consider a relational table with a single record for each registered student with the following attributes. (Gate 2011)
1. Registration_Num: Unique registration number of each registered student
2. UID: Unique identity number, unique at the national level for each citizen
3. BankAccount_Num: Unique account number at the bank. A student can have multiple account or join accounts. This attribute stores the primary
account number.
4. Name: Name of the student
5. Hostel_Room: Room number of the hostel
Which one of the following option is INCORRECT?
( a) BankAccount_Num is a candidate key
( b) Registration_Num can be a primary key

(c) UID is a candidate key if all students are from the same country

(d) If S is a superkey such that S∩UID is NULL then S∪UID is also a superkey

ANSWER: (a) - BankAccount_Num is a candidate key

In case two students hold joint account then Bank Account_Num will not uniquely determine other attributes.

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 7
 Normalization
6. The relation EMPDT1 is defined with attributes empcode(unique), name, street, city, state,
and pincode. For any pincode,there is only one city and state. Also, for any given street, city
and state, there is just one pincode. In normalization terms EMPDT1 is a relation in
(a) 1NF only
(b) 2NF and hence also in 1NF
(c) 3NF and hence also in 2NF and 1NF
(d) BCNF and hence also in 3NF, 2NF and 1NF

Answer: option (b)

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 8
 Normalization
7. The relation schema Student_Performance (name, courseNo, rollNo, grade) has the following FDs:
name,courseNo->grade
rollNo,courseNo->grade
name->rollNo
rollNo->name
The highest normal form of this relation scheme is (GATE-2004 )
(a) 2NF (b) 3NF (c) BCNF (d)4NF

Answer: option (b)

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 9
 Normalization
8. 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 supplier’s 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? (Gate
2009)
(a) The schema is in BCNF

(b) The schema is in 3NF but not in BCNF

C ( c ) The schema is in 2NF but not in 3NF

(d) The schema is not in 2NF
Answer: option (a)

D A relation is in BCNF if for every one of its dependencies X → Y, at least one of the following conditions hold:
X → Y is a trivial functional dependency (Y ⊆ X) X is a superkey for schema R Since (sname, city) forms a
candidate key, there is no non-tirvial dependency X → Y where X is not a superkey

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 10
 Normalization

9. 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 relations is. (Gate 2002)

( a) Zero
( b) More than zero but less than that of an equivalent 3NF decomposition
( c ) Proportional to the size of F+
(d ) Indeterminate

Answer: option (a)

If a relational schema is in BCNF then all redundancy based on functional dependency has been
removed,

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 11
 Normalization
10. 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 closures of F and G are
available).

( a ) Dependency-preservation
( b)Lossless-join
( c )BCNF definition
(d ) 3NF definition
Answer: option (c)

1st is wrong because dependency may be preserved by both 3NF and BCNF. 2nd is wrong Because both
3NF and BCNF decomposition can be lossless. 4th is wrong because 3NF and BCNF both are in 3NF also.

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 12
Dr. S.Sridevi M.E, Ph.D
Associate Professor,
Department of Information Technology
Thiagarajar College of Engineering
Madurai

TCE Online Course <<DATABASE MANAGEMENT SYSTEM: ROAD MAP TO PLACEMENT AND GATE PREPARATION>> 13