Database Normalization to Improve Data Efficiency

Normalization

What is Normalization Normalization is the process of minimizing redundancy from a relation or set of relations. Redundancy in relation may cause insertion, deletion, and update anomalies. So, it helps to minimize the redundancy in relations. Normal forms are used to eliminate or reduce redundancy in database tables. In database management systems (DBMS), normal forms are a series of guidelines that help to ensure that the design of a database is efficient, organized, and free from data anomalies. There are several levels of normalization, each with its own set of guidelines, known as normal forms.

Important Points Regarding Normal Forms First Normal Form (1NF): This is the most basic level of normalization. In 1NF, each table cell should contain only a single value, and each column should have a unique name. The first normal form helps to eliminate duplicate data and simplify queries. Second Normal Form (2NF): 2NF eliminates redundant data by requiring that each non-key attribute be dependent on the primary key. This means that each column should be directly related to the primary key, and not to other columns. Third Normal Form (3NF): 3NF builds on 2NF by requiring that all non-key attributes are independent of each other. This means that each column should be directly related to the primary key, and not to any other columns in the same table. Boyce-Codd Normal Form (BCNF): BCNF is a stricter form of 3NF that ensures that each determinant in a table is a candidate key. In other words, BCNF ensures that each non-key attribute is dependent only on the candidate key. Normal forms help to reduce data redundancy, increase data consistency, and improve database performance

Advantages of Normal Form Reduced data redundancy: Normalization helps to eliminate duplicate data in tables, reducing the amount of storage space needed and improving database efficiency. Improved data consistency: Normalization ensures that data is stored in a consistent and organized manner, reducing the risk of data inconsistencies and errors. Simplified database design: Normalization provides guidelines for organizing tables and data relationships, making it easier to design and maintain a database. Improved query performance: Normalized tables are typically easier to search and retrieve data from, resulting in faster query performance. Easier database maintenance: Normalization reduces the complexity of a database by breaking it down into smaller, more manageable tables, making it easier to add, modify, and delete data.

First Normal Form First Normal Form If a relation contain composite or multi-valued attribute, it violates first normal form or a relation is in first normal form if it does not contain any composite or multi-valued attribute. A relation is in first normal form if every attribute in that relation is singled valued attribute.

Example-2 Table 1 ID Name Courses --------------- --- 1 A c1, c2 2 E c3 3 M C2, c3 Table 2 ID Name Course ------------------ 1 A c1 1 A c2 2 E c3 3 M c2 3 M c3

Example 2 Cust Num Name Street Order Number 101 Jones, Sue 2 Mill Ave. M31, M98, M129 102 Hand, Jim 12 Dudley St. M56 103 Lee, Sandy 45 School St. M37, M40 104 Tan, Steve 67 Main St. M41

Example 3 Cust Num Name Street Order Number1 Order Number2 Order Number3 101 Jones, Sue 2 Mill Ave. M31 M98 M129 102 Hand, Jim 12 Dudley St. M56 Null Null 103 Lee, Sandy 45 School St. M37 M140 Null 104 Tan, Steve 67 Main St. M41 Null Null Order Number (Primary key) Cust Num (Foreign key) M31 101 M98 101 Cust Num (Primary key) Name Street M129 101 101 Jones, Sue 2 Mill Ave. M56 102 102 Hand, Jim 12 Dudley St. M37 103 103 Lee, Sandy 45 School St. M140 103 104 Tan, Steve 67 Main St. M41 104

Second Normal Form To be in second normal form, a relation must be in 1. First normal form 2. Relation must not contain any partial dependency. The second rule of normalization is that you must move those columns that do not depend on the primary key of the current table to a new table. A table is in the second normal form when it is in the first normal form and only contains columns that give you information about the key of the table A relation is in 2NF if it has No Partial Dependency, i.e., no non-prime attribute (attributes which are not part of any candidate key) is dependent on any proper subset of any candidate key of the table. Partial Dependency If the proper subset of candidate key determines non-prime attribute, it is called partial dependency.

Example Cust Num Name Street Order Number Order Date Order Amount 101 Jones, Sue 2 Mill Ave. M31 3/19/05 $400.87 101 Jones, Sue 2 Mill Ave. M98 8/13/05 $3,000.90 101 Jones, Sue 2 Mill Ave. M129 2/9/05 $919.45 102 Hand, Jim 12 Dudley St. M56 5/14/04 $1,000.50 103 Lee, Sandy 45 School St. M37 12/25/04 $299.89 103 Lee, Sandy 45 School St. M140 3/15/05 $299.89 104 Tan, Steve 67 Main St. M41 4/2/04 $2,300.56

Cust Num (Primary key) Name Street 101 Jones, Sue 2 Mill Ave. 102 Hand, Jim 12 Dudley St. 103 Lee, Sandy 45 School St. 104 Tan, Steve 67 Main St. Order Number (Primary key) Order Date Order Amount Cust Num (Foreign key) M31 3/19/05 $400.87 101 M98 8/13/05 $3,000.90 101 M129 2/9/05 $919.45 101 M56 5/14/04 $1,000.50 102 M37 12/25/04 $299.89 103 M140 3/15/05 $299.89 103 M41 4/2/04 $2,300.56 104

Example Example- -1 1 Employee_Id Department_Id Office Location MUJ001 T1 Agra MUJ002 S2 Jaipur MUJ003 M1 Delhi MUJ004 T3 Mumbai Employee_Id Department_Id Department_Id Office Location MUJ001 T1 T1 Agra MUJ002 S2 S2 Jaipur MUJ003 M1 M1 Delhi MUJ004 T3 T3 Mumbai

Third Normal Form Third Normal Form A relation is said to be in third normal form, 1. if we did not have any transitive dependency for non-prime attributes. 2. The basic condition with the Third Normal Form is that, the relation must be in Second Normal Form. Student_Id Student Name Subject_Id Subject Address Std001 Raj CS11 SQL Delhi Std002 Mohan CS12 C++ Kochi Std003 Arnav CS13 Java Agra Std004 Ram CS13 Java Jaipur

Student_Id Student Name Subject_Id Subject Address Std001 Raj CS11 SQL Delhi Std002 Mohan CS12 C++ Kochi Std003 Arnav CS13 Java Agra Std004 Ram CS14 DBMS Jaipur Student_Id Student Name Subject_Id Address Subject_Id Subject Std001 Raj CS11 Delhi CS11 SQL Std002 Mohan CS12 Kochi CS12 C++ Std003 Arnav CS13 Agra CS13 Java Std004 Ram CS14 Jaipur CS14 DBMS

BCNF (Boyce Codd Normal Form) BCNF (Boyce Codd Normal Form) This is also know as 3.5 normal form and higher version of 3NF, was developed by Raymond F. Boyce and Edgar F. Codd. Conditions for satisfying BCNF 1. Table should be in 3rd normal form 2. If every functional dependency is in the form X Y, the table is in BCNF. Here, X is the super key to the table. . S01 SQL Student_Id Subject Professor Prof. Sharma S02 Java Prof. Anand S02 C++ Prof. Amit S03 Java Prof. Anand S04 DBMS Prof. Ram

Student_Id Subject Professor S01 SQL Prof. Sharma S02 Java Prof. Anand S02 C++ Prof. Amit S03 Java Prof. Anand S04 DBMS Prof. Ram Student_Id Professor_id Professor_Id Subject Professor S01 PF01 PF01 SQL Prof. Sharma S02 PF02 PF02 Java Prof. Anand S02 PF03 PF03 C++ Prof. Amit S03 PF02 PF02 Java Prof. Anand S04 PF04 PF04 DBMS Prof. Ram