Normalization: DBMS Class Notes

Updated: Aug 18

Mobiprep has created last-minute notes for all topics of Normalization to help you with the revision of concepts for your university examinations. So let’s get started with the lecture notes on Normalization.

  1. DBMS

  2. Data Modelling

  3. Database Architecture

  4. Relational Model

  5. Backup and Recovery

  6. Functional Dependencies

  7. Normalization

  8. Deadlock

  9. Transaction and Concurrency Control

  10. Files and Storage

  11. Relational Algebra

  12. Entity Relationship Model

  13. Indexing

Our team has curated a list of the most important questions asked in universities such as DU, DTU, VIT, SRM, IP, Pune University, Manipal University, and many more. The questions are created from the previous year's question papers of colleges and universities.

  1. Define Normalization.

  2. What is the need for Normalization?

  3. What are the different formal forms?

  4. Explain 1NF in DBMS with example.

  5. What is 2NF in DBMS?

  6. What is 3NF in DBMS?

  7. What is Boyce Codd Normal Form in DBMS?

  8. What are 4th and 5th normal forms in DBMS?

  9. Compare BCNF and 3NF.

  10. Define Partial Dependency.

  11. Define Transitive Dependence.

  12. Define Join Dependency.

  13. What are Multivalued Dependencies?

  14. What are the uses of Multivalued Dependencies?


Normalization


Question 1- Define Normalization.

Answer - The process of removal of duplicate or redundant data from the relation is called normalization. Normalization improves data integrity and data consistency. It also increases the storage efficiency of the database.


 

Question 2 - What is the need for Normalization?

Answer - The main aim of normalization is to decrease data redundancy and to achieve data integrity and data consistency. If the same data is stored in different places of a database, it may lead to data inconsistency, and insufficient use of storage. This decreases the efficiency of the database. Hence, normalization is very much important in the design of an efficient database.

 

Question 3 - What are the different formal forms?

Answer - The different normal forms are:

  1. Un-normalized form

  2. First Normal Form

  3. Second Normal Form

  4. Third Normal Form

  5. Boyce-Codd Normal Form

  6. Fourth Normal Form

  7. Fifth Normal Form

  8. Domain Key Normal Form

 

Question 4 - Explain 1NF in DBMS with example.

Answer - Every attribute in the table must be a single-valued attribute. If a relation contains a composite or multi-valued attribute, it violates 1st normal form. Entries in the column should be of the same data type. Each row must be uniquely identifiable.

Example:


Roll No.

Name

Course

1

Omkar

C/ C++

2

Harsh

Java

3

Sai

C/ DBMS

The above table has multi-valued Course attribute. Hence, the table is not in 1NF. To convert it into 1NF, one of the following methods can be followed.


Method 1

Primary Key: Roll No., Course

Roll No.

Name

Course

1

Omkar

C

1

Omkar

C++

2

Harsh

java

3

Sai

C

3

Sai

DBMS

Roll No.

Name

Course 1

Course 2

1

Omkar

C

C++

2

Harsh

Java

NULL

3

Sao

C

DBMS

Method 2

Primary Key: Roll No.

Roll No.

Course

1

C

1

C++

2

Java

3

C

3

DBMS

Method 3

Base table

Primary key: Roll No.


Roll No.

Name

1

Omkar

2

Harsh

3

Sai

Primary Key: Roll No., Course

Foreign Key: Roll No.,


From the above mentioned methods, the 3rd method is preferable. In the 3rd method, the given table is decomposed into two tables. So, data redundancy is removed.

 

Question 5 - What is 2NF in DBMS?

Answer - For a relation to be in 2nd normal form , the following conditions must be satisfied by the relation:

  1. The relation must be in 1st normal form.

  2. The relation must not contain any partial dependency. If the partial subset of candidate key determines non-prime attribute, it is called partial dependency.

To convert a relation that has a partial dependency to 2NF, we need to split the table into two tables. 2NF reduces the redundant data from getting stored in memory.


 

Question 6 - What is 3NF in DBMS?

Answer - For a relation to be in 3NF, the following conditions must be satisfied.

  1. The relation must be in 2NF

  2. The non-prime attributes should not have a transitive dependency.

  3. To satisfy 3NF, either LHS should be a super key or RHS should be a prime attribute.


 

Question 7 - What is Boyce Codd Normal Form in DBMS?

  1. BCNF does not allow dependencies between attributes that belong to a candidate key

  2. A relation is in BCNF if it is in 3NF and for every FD, LHS is super key

  3. A relation is in BCNF if and only if in every non-trivial functional dependency X🡪Y, X is a super key

  4. BCNF can lead to lossy decomposition or dependencies may be lost.

  5. BCNF is free from redundancy.

  6. Steps to convert a relation into BCNF

  • Place the 2 candidate primary keys (which are dependent on each other) in separate entities


 

Question 8 - What are 4th and 5th normal forms in DBMS?

Answer - 4NF

For a relation to be in 4NF

  1. It should satisfy BCNF

  2. It should not have a multi-valued dependency

It states that the table must not contain more than 1 multivalued dependency (can contain 1 multivalued dependency). 4NF eliminates independent many-to-one relationships between columns


5NF

  • The 5th Normal Form is satisfied when all tables are decomposed into as many tables as possible to avoid redundancy.

  • Conditions for a relation to be in 5NF

  • The relation should be in 4NF

  • It should not have join dependency

  • The 5th Normal Form is also known as the Project Join Normal Form or Projected Normal Form.


 

Question 9 - Compare BCNF and 3NF.

Answer

​3rd Normal Form

Boyce-Codd Normal Form

Transitive dependency of a non-prime attribute on a candidate key is removed from the relation.

For every FD, the LHS must be a super key.


If a relation is in 3NF, then it may or may not be in BCNF.

If a relation is in BCNF, then, the relation is in 3NF

A binary relation may or may not be in 3NF.

Every binary relation is in BCNF

​Results in lossless decomposition

Leads to lossy decomposition.

All the functional dependencies are preserved.

​All the FDs are not preserved


 

Question 10 - Define Partial Dependency.

Answer - If a part (or a partial subset) of a candidate key determines a non-prime attribute, it is called partial dependency.

Part of primary key 🡪 non-prime attribute

Partial dependencies are removed by the Second Normal Form.


 

Question 11 - Define Transitive Dependence.

Answer - 1. Transitive dependency

  • If A🡪B and B🡪C are two FDs, then A🡪C is called transitive dependency

2. If there is a transitive dependency, we have to decompose the relation into two

3. The transitive dependency can be solved by the following steps:

  • Move all items involved in the transitive dependency (B and C in the above example) to a new entity

  • Identify the primary key for the new entity

  • Place the primary key for the new entity as a foreign key on the original entity

4. The 3NF removes the transitive dependencies.


 

Question 12 - Define Join Dependency.

Answer - If the natural join of R1 and R2 which are the decomposition of R is a lossless decomposition, then join dependency exists. If a relation can be retrieved back by joining the tables which are the subsets of the original relation, then the relation is said to have Join Dependency. If breaking down the table leads to information loss, then, the join dependency does not exist.

 

Question 13 - What are Multivalued Dependencies?

(or)

Question 14 - What are the uses of Multivalued Dependencies?

Answer - If two independent attributes of a table depend on a third attribute, it is called multivalued dependency. There must be more than two attributes in a relation for a multivalued dependency to occur. The multivalued dependency introduces many-to-one relationships between columns. The 4NF removes the multivalued dependencies.

In multivalued dependency X🡪Y, X has many values of Y.





3 views0 comments