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 Normal Forms?
Answer) The different normal forms are:
1. Un-normalized form
2. First Normal Form
3. Second NormalForm
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 1 st normal form. Entries in the column should be of the same data type. Each row must be uniquely identifiable.
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 Method 2.
Question-5) What is 2NF in DBMS?
Answer) For a relation to be in 2 nd normal form , the following conditions must be satisfied by the relation:
1. The relation must be in 1 st 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?
Answer) 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
7. 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?
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
1.The 5 th Normal Form is satisfied when all tables are decomposed into as many tables as possible to avoid redundancy.
2. Conditions for a relation to be in 5NF o The relation should be in 4NF
It should not have join dependency
3.The 5 th Normal Form is also known as the Project Join Normal Form or Projected Normal Form
Question-9) 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-10) Define Transitive Dependency.
Answer) a. Transitive dependency
1. If A→B and B→C are two FDs, then A→C is called transitive dependency
b. If there is a transitive dependency, we have to decompose the relation into two
c. The transitive dependency can be solved by the following steps:
1. Move all items involved in the transitive dependency (B and C in the above example) to a new entity
2. Identify the primary key for the new entity iii. Place the primary key for the new entity as a foreign key on the original entity
d. The 3NF removes the transitive dependencies.