1. Which normal form is considered adequate for normal relational database design?
   (a) 2NF           (b) 5NF          (c) 4NF         (d) 3NFAns: option (d)
   Explanation:

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.

GATE-2001

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

(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

Ans: option (c)

Explanation:

While decomposing a relational table we must verify the following properties:

2. 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 are 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.

   ---

GATE-2002

3. 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) Indeterminate

Ans: option (b)

Explanation:

Redundancy in BCNF is low when compared to 3NF. 

GATE-2005
4. 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

Ans: option (c)
Explanation:
Achieving Lossless and dependency-preserving decomposition property into BCNF is difficult.

GATE-2005 (IT)
5. 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
(a) 1NF             (b) 2NF           (c) 3NF           (d) None of these

Ans: option (a)
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 1NF. Hence the table is in 1NF.

GATE-2012

6. Which of the following is TRUE?

(a) Every relation in 2NF 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: option (c)

GATE-2003
7. Consider the following functional dependencies in a database.
Date_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

Ans: option (d)
Explanation:
For the given relation only some of the above FDs are applicable. The applicable FDs are given below:
Date_of_Birth->Age
Name->Roll_number
Roll_number->Name
Finding the closure set of attributes we get the candidate keys:(Roll_number,Date_of_Birth), and (Name,Date_of_Birth) .On selecting any one of the candidate key we can see that the FD Date_of_Birth->Age is a partial dependency. Hence the relation is in 1NF.

GATE-2004

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

(a) 2NF              (b) 3NF              (c) BCNF               (d)4NF

Ans: option (b)

Explanation:

With the help of closure set of attributes we can find the candidate keys: (name,courseNo) and (rollNo,courseNo).

A table is in 2NF if and only if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table. A non-prime attribute of a table is an attribute that is not a part of any candidate key of the table.

The only non-prime key attribute here is “grade”, which is fully dependent on the keys. Hence the relation is in 2NF.

A super key is a combination of prime attributes and one or more non-prime key attribute(s). It also uniquely identifies a record in a table. Primary key can be defined as super key with minimal attributes.

A table is in 3NF if and only if, for each of its functional dependencies X -> A, at least one of the following conditions holds:

* X contains A (that is, X -> A is trivial functional dependency), or

* X is a superkey, or

* A should be prime attribute.

Note: We check transitive dependency for only non-prime attributes.

In BCNF, left side of all FDs should be a superkey.

From the given FDs:

name->rollNo , name is not a superkey

rollNo->name, roll is not a superkey

Hence not BCNF.

GATE-2004 (IT)

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

Ans: option (b)

Explanation:

empcode is unique, therefore it is the primary key. Since the primary key consists of a single attribute there will be no partial dependency, hence the relation is in 2NF.

From the question we get the FDs as below:

pincode -> city, state

street,city,state -> pincode

From the FDs we can see that there are transitive dependencies, hence the table is not in 3NF.

GATE-2007
10.  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 3 NF relation.
(d) A prime attribute can be transitively dependent on a key in a BCNF relation.

Ans: option (d)

GATE-2008

11. Consider the following relational schemes for a library database:

Book (Title, Author, Catalog_no, Publisher, Year, Price)

Collection (Title, Author, Catalog_no)

With the following functional dependencies:

1. Title Author -> Catalog_no
2. 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

Ans: option (c)

Explanation:

The relation Collection is in BCNF: Its given that {Author, Title} is the key and there is only one functional dependency (FD) applicable to the relation Collection {i.e. Title Author –> Catalog_no}.

As per the definitions of the normal forms (given in the explanation of question no. 8) Book is in 2NF.

GATE-2008(IT)
12. Let R(A,B,C,D,E,P,G) be a relational schema in which the following FDs are known to hold:
AB->CD
DE->P
C->E
P->C
B->G
The relation schema R is
(a) in BCNF                                       (b) in 3NF, but not in BCNF
(c) in 2NF, but not in 3NF                   (d) not in 2NF

Ans: option (d)
Explanation:
From the closure set of attributes we can see that the key for the relation is AB. The FD B->G is a partial dependency, hence it is not in 2NF.

1. Let R= (A, B, C, D, E, F) be a relation scheme with the following dependencies: C->F, E->A, EC->D, A->B. Which of the following is a key for R?
   (a) CD             (b) EC           (c) AE            (d) ACAns: option (b)
   Explanation:
   Find the closure set of all the options give. If any closure covers all the attributes of the relation R then that is the key.
   Algorithm to find Closure Set
   Step1: Equate an attribute or attributes to X for which closure needs to be identified.
   Step2: Take each FD (functional dependency) one by one and check whether the left side of FD is available in X, if yes then add the right side attributes to X if it is not available.
   Step3: Repeat step 2 as many times as possible to cover all FD’s.
   Step4: After no more attributes can be added to X declare it as the closure set.

FDs:C->F, E->A, EC->D, A->B
Find closure set for CD.
X = CD
= CDF {C->F}
No more attributes can be added to X. Hence closure set of CD = CDF

Find closure set for EC.
X = EC
= ECF {C->F}
= ECFA {E->A}
= ECFAD {EC->D}
= ECFADB {A->B}
Closure set of EC covers all the attributes of the relation R.

GATE-2000

2. Given the following relation instance.

——-

X  Y  Z

——-

1  4  2

1  5  3

1  6  3

3  2  2

——-

Which of the following functional dependencies are satisfied by the instance?

(a) XY -> Z and Z -> Y  (b) YZ -> X and Y -> Z

(c) YZ -> X and X -> Z  (d) XZ -> Y and Y -> X

Ans: option (b)

Explanation:

Association among attributes is known as Functional Dependencies (FD). A FD X->Y require that the value of X uniquely determines the value of Y where X and Y are set of attributes.

For example,

Roll_No -> Name: the value of Roll_No uniquely determines the Name.

Roll_No, Book_No -> Issue_Date : In the case of library, Roll_No and Book_No can determine the Issue_Date of a book.

In option (a), its given Z->Y, it means that the value of Z uniquely determines the value of Y. But here the value 2 of Z, gives two different values of Y i.e. 4 and 2. Therefore this FD is not satisfied by the instance.

In option (c), its given X->Z, it means that the value of X uniquely determines the value of Z. But here the value 1 of X, gives two different values of Z i.e. 2 and 3. Therefore this FD is not satisfied by the instance.

n option (d), its given Y->X, here the value of Y uniquely determines the value of X. Therefore this FD is satisfied by the instance. Now take FD XZ->Y, here (1,3) cannot uniquely determine the value of Y. (1,3) gives two values for Y i.e. 5 and 6. Therefore this FD (XZ->Y) is not satisfied by the instance.

GATE-2002
3. From the following instance of a relational schema R(A, B, C), we can conclude that:
———-
A   B   C
———-
1   1   1
1   1   0
2   3   2
2   3   2
———-
(a) A functionally determines B and B functionally determines C
(b) A functionally determines B and B does not functionally determine C
(c) B does not functionally determine C
(d) A does not functionally determine B and B does not functionally determine C

Ans: option (b)

GATE-2005

4. Consider a relation scheme R = (A, B, C, D, E, H) on which the following functional dependencies hold: {A–>B, BC–>D, E–>C, D–>A}. What are the candidate keys of R?

(a) AE, BE          (b) AE, BE, DE

(c) AEH, BEH, BCH (d) AEH, BEH, DEH

Ans: option (d)

Explanation:

As explained in question 1, if any closure includes all attributes of a table then it becomes the candidate key.

Closure of AEH = AEHB   {A->B}

= AEHBC  {E->C}

= AEHBCD {BC->D}

GATE-2005(IT)
5. In a schema with attributes A, B, C, D and E, following set of functional dependencies are given:
A->B
A->C
CD->E
B->D
E->A
Which of the following functional dependencies is NOT implied by the above set?
(a) CD->AC            (b) BD->CD         (c) BC->CD          (d) AC->BC

Ans: option (b)
Explanation:
For every options given, find the closure set of left side of each FD. If the closure set of left side contains the right side of the FD, then the particular FD is implied by the given set.
Option (a): Closure set of CD = CDEAB. Therefore CD->AC can be derived from the given set of FDs.
Option (c): Closure set of BC = BCDEA. Therefore BC->CD can be derived from the given set of FDs.
Option (d): Closure set of AC = ACBDE. Therefore AC->BC can be derived from the given set of FDs.
Option (b): Closure set of BD = BD. Therefore BD->CD cannot be derived from the given set of FDs.

GATE-2006

6. The following functional dependencies are given:

AB->CD, AF->D, DE->F, C->G , F->E, G->A

Which one of the following options is false?

(a)CF⁺ = {ACDEFG}                             (b)BG⁺ = {ABCDG}

(c)AF⁺ = {ACDEFG}                             (d)AB⁺ = {ABCDFG}

Ans: option(c)

Explanation:

As explained in question 1, find the closure set of each options.
Option (d) is also false. AB⁺ = {ABCDG}.

1. Consider the following ER diagram.

The minimum number of tables needed to represent M, N, P, R1, R2 is
(a) 2  (b) 3  (c) 4  (d) 5

Ans: option (b)
Explanation:
All strong entities and weak entities will be converted into a table. Therefore we will have 3 tables:
M (M1,M2,M3,P1)
P (P1,P2)
N (N1,N2,P1) =>N is a weak entity and it is modified to include the primary key of P (i.e. P1).

2. Which of the following is a correct attribute set for one of the tables for the correct answer to the above question?
(a) {M1, M2, M3, P1}  (b) {M1, P1, N1, N2}  (c) {M1, P1, N1}        d) {M1, P1}

Ans: option (a)

GATE-2005

3. The following table has two attributes A and C where A is the primary key and C is the foreign key referencing A with on-delete cascade.

—–

A   C

—–

2   4

3   4

4   3

5   2

7   2

9   5

6   4

—–

The set of all tuples that must be additionally deleted to preserve referential integrity when the tuple (2,4) is deleted is:

(a) (3,4) and (6,4)            (b) (5,2) and (7,2)

(c) (5,2), (7,2) and (9,5)           (d) (3,4), (4,3) and (6,4)

Ans: option (c)

Explanation:

Note that C is a foreign key, referring A with delete on cascade. Therefore when (2,4) is deleted, all the rows with value 2 in field C also should be deleted. Hence (5,2) and (7,2) is also deleted. Now rows with value 5 and 7 in field C also should be deleted. Therefore (9,5) is also deleted.

GATE-2005
4. Let E1 and E2 be two entities in an ER diagram with simple single-valued attributes. R1 and R2 are two relationships between E1 and E2, where R1 is one-to-many and R2 is many-to-many. R1 and R2 do not have any attributes of their own. What is the minimum number of tables required to represent this situation in the relational model?
(a) 2  (b) 3  (c) 4  (d) 5

Ans: option (b)
Explanation:
Strong entities E1 and E2 should be converted into tables. For R1, which is one to many relation, there is no need of a separate table. Modify the “many” side to include the primary key of “one” side as foreign key. For R2, which is many to many relation, we require a separate table by including the primary key of E1 and E2 as foreign keys. Hence we require a minimum of 3 tables.

GATE-2012

5. Given the basic ER and relational models, which of the following is INCORRECT?

(a) An attribute of an entity can have more than one value

(b) An attribute of an entity can be composite

(c) In a row of a relational table, an attribute can have more than one value

(d) In a row of a relational table, an attribute can have exactly one value or a NULL value

Ans: option (c)

Explanation:

According to First Normal Form, an attribute cannot have multiple values.

GATE-2011
6. Consider a relational table with a single record for each registered student with the following attributes.
1. Registration_Number: Unique registration number of each registered student
2. UID: Unique Identity number, unique at the national level for each citizen
3. BankAccount_Number: Unique account number at the bank. A student can have multiple accounts or joint accounts. This attributes stores the primary account number
4. Name: Name of the Student
5. Hostel_Room: Room number of the hostel
Which of the following options is INCORRECT?
(a) BankAccount_Number is a candidate key
(b) Registration_Number 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 U UID is also a superkey

Ans: option (a)

Explanation:

Candidate Key: All unique value columns in a table are called candidate keys.

Its already specified in the question that “A student can have multiple accounts or joint accounts”. Hence if two students have a joint account, BankAccount_Number will be the same both the students. Hence BankAccount_Number cannot be a candidate key.

1. A relational schema for a train reservation database is given below.
   Passenger (pid, pname, age)
   Reservation (pid, class, tid)

What pids are returned by the following SQL query for the above instance of the tables?
SELECT pid
FROM Reservation
WHERE class ‘AC’ AND
EXISTS (SELECT *
FROM Passenger
WHERE age > 65 AND
Passenger. pid = Reservation.pid)
(a) 1, 0 (b) 1, 2 (c) 1, 3 (d) 1, 5

Ans: option (c)
Explanation:

The above query is an example of synchronized subquery or correlated subquery. A correlated sub-query is a sub-query that uses values from the outer query. The sub-query is evaluated once for each row processed by the outer query.

In the above query the outer query is
SELECT pid FROM Reservation WHERE class ‘AC’ AND EXISTS

And the subquery is,
SELECT * FROM Passenger WHERE age > 65 AND Passenger. pid = Reservation.pid

The correlated subquery is evaluated once for each row processed by the outer query. The outer query selects rows with pids: 0, 1, 5, 3, from Reservation table. Out of these, the subquery conditions are met only for 1 and 3.

GATE-2009

Common Data for Questions 2 and 3

2.  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)

Consider the following relational query on the above database:

SELECT S.sname

FROM Suppliers S

WHERE S.sid NOT IN (SELECT C.sid

FROM Catalog C

WHERE C.pid NOT IN (SELECT P.pid

FROM Parts P

WHERE P.color<> ‘blue’))

Assume that relations corresponding to the above schema are not empty. Which one of the following is the correct interpretation of the above query?

(a) Find the names of all suppliers who have supplied a non-blue part.

(b) Find the names of all suppliers who have not supplied a non-blue part.

(c) Find the names of all suppliers who have supplied only blue parts.

(d) Find the names of all suppliers who have not supplied only blue parts.

Ans: option (a)
Explanation:

“SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’” gives pid of parts, which are not blue. Note: “<>” indicates “not equal to”.

“SELECT C.sid FROM Catalog C WHERE C.pid NOT IN (SELECT P.pid FROM Parts P WHERE P.color<> ‘blue’)” gives sid of all suppliers who have supplied blue parts.

The whole query finally retrieves the name (sname) of suppliers, who have supplied a non-blue part.

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

Ans: option (a)