.png)
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.
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.
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:
Un-normalized form
First Normal Form
Second Normal Form
Third Normal Form
Boyce-Codd Normal Form
Fourth Normal Form
Fifth Normal Form
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:
The relation must be in 1st normal form.
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.
The relation must be in 2NF
The non-prime attributes should not have a transitive dependency.
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?
BCNF does not allow dependencies between attributes that belong to a candidate key
A relation is in BCNF if it is in 3NF and for every FD, LHS is super key
A relation is in BCNF if and only if in every non-trivial functional dependency X🡪Y, X is a super key
BCNF can lead to lossy decomposition or dependencies may be lost.
BCNF is free from redundancy.
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
It should satisfy BCNF
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.