Database Normalization Example

Database Normalization is an important topic for GATE(CS/IT) exam. DBMS gate questions and answers based on Normalization are always asked in GATE Exam every year.

Objective of this Database Normalization Tutorial is to explain some previous year GATE exam database normalization example.

All the normalization questions discussed in this tutorial are asked in GATE(CS/IT) Previous Year.

Note – For better understanding of the solution of these dbms gate questions, students are requested to study the concepts of database normalization and different database normal forms.

Students can Read this Database Normalization Tutorial for the understanding of the normalization and different normal forms concepts

Database Normalization GATE Questions and Answers

Here we have discussed 1 Mark Questions dbms gate questions on Normalization asked in GATE previous year exam.

Questions are as shown in following picture.

Solution and Explanation

Solution and explanation for these dbms gate questions or database normalization example GATE exam Questions as shown in above picture is discussed here one bye one.

Solution of Question1.

The given Statement is false because decomposition in BCNF  guarantees to Loss less decomposition but does not guarantees to Dependency Preservation. We have seen many example for this.

Solution of Questions 2

BCNF is considered adequate for a normal relation database design. Since the BCNF is not given in any  the option and In most of the Practical example it has been seen that if a relation is in 3 NF then it is automatically in BCNF.

So option d 3 NF will be the right answer.

Solution of Question 3

Since by Taking the closure of EC we obtained all the attributes of the given relation So EC is the candidate key , we have solved this question in previous tutorial.

Solution of Question 4

When we decompose the R(A,B,C,D) in to R1(AB) and R2(CD) since there is no common attribute between R1 and R2 so there will be loss of some spurious tuples. SO this decomposition will not be loss less but it will be dependency preserved.

Solution of Question 5

When a relation is decomposed into BCNF then redundancy in the resulting set of relation will be almost Zero because for a relation to be in BCNF for every functional dependency there will be a Key on LHS. So duplicate will be eliminated. Redundancy reduction is an important advantage of DBMS.

Solution of Question 6

Let’s Check the given option one by one

• BCNF is more strict than 3 NF , Yes this statement is true because for a given relation to be in 3 NF for a functional dependency  X →Y we have two choice  either X should be the super key or Y should be prime attribute but in for a relation to be in BCNF X must e the super key.

• Option b is also true
• Option C is false because decomposition in BCNF  guarantees to Loss less decomposition but does not guarantees to Dependency Preservation.
• Option d is also true. If a relation has only two attributes then it will be BCNF.

Solution of Question 7

Every given relation must be in 1 NF if there is no composite attribute. Now check for 2 NF. To check for a relation in 2 NF first we find the candidate key of the relation Since F1 and F2 are not present on the right hand side of any functional dependency it means F1 and f2 are not determined by other so f1 and F2 must be the part of candidate key.

Now when we calculate the Closure of (F1F2) it means (F1F2)⁺ then we got all the attributes of the given relation. S0 {F1F2} will be the candidate key of this given relation.

But since F3 can be derived only from F1 and F4 can derive only from F2 alone it means F1→ F3 and F2→ F4 are the partial functional dependencies.

Since as per the definition of 2 NF We know  that a relation will be in 2 NF if there is no partial dependency. But in given relation there are two partial dependency so Given relation will not be in 2 NF. Since this is not  in 2 NF so it will not be in 3 NF.

So option a is correct given relation is in 1 NF.

Solution of Question 8

• Option a is not true because BCNF is more strict than 3 NF.
• Option b is also false because every non prime attribute of R is fully functionally dependent only on some key or R not on every key of R.
• Option C is true because if the given relation is in BCNF then left side of every functional dependency will be a super key and first condition of 3 NF says that either the Left side of FD should be a super key or the right side must be the prime attribute.
• Option d is false because if a relation is in BCNF the it will be also in 3 NF. So a relation can be in both BCNF and 3 NF simultaneously.

Solution of Question 9

Option b is true a prime attribute of a given relation must be in Some Candidate keys of given relation R. It not necessary that prime attribute must be present in all candidate keys of R.

Conclusion and Summary

In this database normalization example Tutorial for dbms gate questions, we have discussed  discussed 1 mark questions on normalization for the preparation of GATE CSE 2021 exam.

I hope this tutorial will be beneficial for GATE 2021 Aspirants.

I kindly request to readers please give your feedback and suggestion. If you find any mistake in this tutorial then comment.

Also Practice – (1) DBMS GATE Question on ER Diagram

(2) DBMS GATE Questions on SQL Queries