Skip to main content

GATE Questions - DBMS - Normalization

Previous GATE questions with solutions on DBMS (Normalization) - CS/IT

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

Ans: option (d)
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.

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

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)
Redundancy in BCNF is low when compared to 3NF. For more details on BCNF: CLICK HERE

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)
Achieving Lossless and dependency-preserving decomposition property into BCNF is difficult. For details:

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

Ans: option (a)
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.

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)

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)
For the given relation only some of the above FDs are applicable. The applicable FDs are given below:
Finding the closure set of attributes we get the candidate keys:(Roll_number,Date_of_Birth), and (Name,Date_of_Birth) . 
ALGORITHM TO FIND THE CLOSURE SET: Check the explanation for question number 1:
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.

8. The relation schema Student_Performance (name, courseNo, rollNo, grade) has the following FDs:
The highest normal form of this relation scheme is
(a) 2NF              (b) 3NF              (c) BCNF               (d)4NF

Ans: option (b)
With the help of closure set of attributes we can find the candidate keys: (name,courseNo) and (rollNo,courseNo). 
ALGORITHM TO FIND THE CLOSURE SET: Check the explanation for question number 1:
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.

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

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)
Check the explanation of question 8.

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

Ans: option (c)
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.

12. Let R(A,B,C,D,E,P,G) be a relational schema in which the following FDs are known to hold: 
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)
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.


Popular posts from this blog

ORACLE 9i practice solutions

Created by BCL easyConverter SDK 3 (HTML Version)

Zoho Puzzle Questions With Answers

Measuring Time Logic Puzzle You are given with two ropes with variable width. However if we start burning both the ropes, they will burn at exactly same time i.e. an hour. The ropes are non-homogeneous in nature. You are asked to measure 45 minutes by using these two ropes.

How can you do it?

Please note that you can’t break the rope in half as it is being clearly stated that the ropes are non-homogeneous in nature.
Answer & Explanation Solution: 45 minutes

Explanation :
All you have to do is burn the first rope from both the ends and the second rope from one end only simultaneously. The first rope will burn in 30 minutes (half of an hour since we burned from both sides) while the other rope would have burnt half. At this moment, light the second rope from the other end as well. Where, the second rope would have taken half an hour more to burn completely, it will take just 15 minutes as we have lit it from the other end too.

Thus you have successfully calculated 30+15 = 45 minutes …

Hackerrank > SQL > Basic Select

01-Select All
Given a City table, whose fields are described as +-------------+----------+ | Field       | Type     | +-------------+----------+ | ID          | int(11)  | | Name        | char(35) | | CountryCode | char(3)  | | District    | char(20) | | Population  | int(11)  | +-------------+----------+
write a query that will fetch all columns for every row in the table.

My Solution
02-Select by ID
Given a City table, whose fields are described as