Normalization in SQL Server

Normalization in SQL Server

Introduction-

Normalization in SQL Server is a crucial concept in database design that aims to organize data efficiently, reduce redundancy, and maintain data integrity. It involves structuring relational databases to ensure that they meet certain criteria regarding the relationships between tables and the dependencies among their attributes.

 

Normalization's primary objectives are-

Eliminating Redundancy-

Redundant data storage wastes space and can lead to inconsistencies and anomalies when updating or deleting data. Normalization helps to minimize redundancy by breaking down large tables into smaller, more manageable ones.

Avoiding Update Anomalies-

Update anomalies occur when updating data in one place but not in others, leading to inconsistencies. Normalization helps prevent such anomalies by ensuring that data is stored in a way that makes it easy to update without risking inconsistency.

Ensuring Data Integrity-

Data integrity refers to the accuracy, consistency, and reliability of data stored in a database. Normalization contributes to data integrity by structuring data to reduce the risk of errors, inconsistencies, and inaccuracies.

 

Database Normalization-

Every database you construct should undergo the process of database normalization. Normal Forms are a collection of formal criteria and principles that are applied to a database design.

 

The disadvantages of data redundancy include-

• Data upkeep becomes difficult; data deletion and upgrades become problematic.
• It generates data discrepancies.
• Insert, update, and delete abnormalities become common. An update anomaly, for example, means that all copies of the same record, duplicated in different places in the database, must be updated to maintain the record consistent.
• Redundant data increases the size of a database and takes up an excessive amount of space on disk.

 

Normal Forms-

 

The goal of the following article is to present the essential information about database normalization. Although normalization is a broad topic, this article aims to give readers enough background knowledge to comprehend the first three types of database normalization.

• 1 NF, or First Normal Form
• Form 2 Normal, second (NF)
• Form 3: Third Normal (3 NF)

If a database satisfies the prerequisites of the first three normal forms, it is regarded as the third normal form.

 

First Normal Form (1NF)-

Ensures that each column in a table contains atomic (indivisible) values.

Example: Original Table:

| Student_ID | Name            | Courses            |

|------------|-----------------|--------------------|

| 1          | John Doe        | Math, Physics      |

| 2          | Jane Smith      | Chemistry, Biology |

 

After normalization to 1NF- 

Students Table-

| Student_ID | Name      |

|------------|-----------|

| 1          | John Doe  |

| 2          | Jane Smith|

 

Courses Table-

| Student_ID | Course   |

|------------|----------|

| 1          | Math     |

| 1          | Physics  |

| 2          | Chemistry|

| 2          | Biology  |

 

Second Normal Form (2NF)-

Every non-prime attribute is fully functionally dependent on the primary key.

Example-

 

Original Table-

 

| Student_ID | Course   | Instructor   |

|------------|----------|--------------|

| 1          | Math     | Mr. Smith    |

| 1          | Physics  | Mr. Johnson  |

| 2          | Chemistry| Mrs. Brown   |

| 2          | Biology  | Mr. Green    |

 

After normalization to 2NF-

Students Table-

| Student_ID | Name      |

|------------|-----------|

| 1          | John Doe  |

| 2          | Jane Smith|

 

Courses Table-

| Course   | Instructor |

|----------|------------|

| Math     | Mr. Smith  |

| Physics  | Mr. Johnson|

| Chemistry| Mrs. Brown |

| Biology  | Mr. Green  |

 

Enrollments Table-

| Student_ID | Course   |

|------------|----------|

| 1          | Math     |

| 1          | Physics  |

| 2          | Chemistry|

| 2          | Biology  |

 

Third Normal form (3NF)-

 

Every non-prime attribute is non-transitively dependent on the primary key.

Example-

Original Table-

 

| Student_ID | Course   | Instructor   | Instructor_Phone |

|------------|----------|--------------|------------------|

| 1          | Math     | Mr. Smith    | 555-1234         |

| 1          | Physics  | Mr. Johnson  | 555-5678         |

| 2          | Chemistry| Mrs. Brown   | 555-9876         |

| 2          | Biology  | Mr. Green    | 555-4321         |

 

After normalization to 3NF-

Students Table-

| Student_ID | Name      |

|------------|-----------|

| 1          | John Doe  |

| 2          | Jane Smith|

 

Course Table-

 

| Course   | Instructor |

|----------|------------|

| Math     | Mr. Smith  |

| Physics  | Mr. Johnson|

| Chemistry| Mrs. Brown |

| Biology  | Mr. Green  |

 

Instructor Table-

 

| Instructor   | Phone     |

|--------------|-----------|

| Mr. Smith    | 555-1234  |

| Mr. Johnson  | 555-5678  |

| Mrs. Brown   | 555-9876  |

| Mr. Green    | 555-4321  |

 

Enrollment Table-

 

| Student_ID | Course   |

|------------|----------|

| 1          | Math     |

| 1          | Physics  |

| 2          | Chemistry|

| 2          | Biology  |

 

These three normalization forms are the ones that are discussed the most. Higher normal forms do exist, albeit they are not as commonly employed in practice as Boyce-Codd Normal Form (BCNF) and Fourth Normal Form (4NF). In a relational database, each normalization form aids in reducing anomalies, redundancies, and preserving data integrity.