Database Management System
Entity Relationship Model
Transaction And Concurrency Control
Files and Storage
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.
What is the need for Normalization.
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.
What are the different Normal Forms?
The different normal forms are:
i. Un-normalized form
ii. First Normal Form
iii. Second NormalForm
iv. Third Normal Form
v. Boyce-Codd Normal Form
vi. Fourth Normal Form
vii. Fifth Normal Form
viii. Domain Key Normal Form
Explain 1NF in DBMS with example.
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.
What is 2NF in DBMS?
For a relation to be in 2 nd normal form , the following conditions must be satisfied by the relation:
a. The relation must be in 1 st normal form.
b. 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.
What is 3NF in DBMS?
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
What is Boyce Codd Normal Form in DBMS?
a. BCNF does not allow dependencies between attributes that belong to a candidate key
b. A relation is in BCNF if it is in 3NF and for every FD, LHS is super key
c. A relation is in BCNF if and only if in every non-trivial functional dependency X→Y, X is a super key
d. BCNF can lead to lossy decomposition or dependencies may be lost.
e. BCNF is free from redundancy.
f. Steps to convert a relation into BCNF
i. Place the 2 candidate primary keys (which are dependent on each other) in separate entities
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.
Define Partial Dependency.
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.
Define Transitive Dependency.
a. Transitive dependency
i. 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:
i. Move all items involved in the transitive dependency (B and C in the above example) to a new entity
ii. 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.