Database Normalization Process From 1NF to 3NF

Part I

Part II

Original Table

First Normal Form (1NF):

First Normal Form (1NF) is the initial step in database normalization. In this form, a table is required to have a primary key, ensuring each record is unique. All columns must contain atomic values, and there should be no repeating groups or arrays within the table. Essentially, 1NF ensures that data is organized into a two-dimensional table with consistent and non-redundant values.

The table is already in 1NF since each cell contains atomic values.

Second Normal Form(2NF)

Building upon 1NF, Second Normal Form (2NF) addresses partial dependencies within a table. To qualify for 2NF, a table must first be in 1NF. Moreover, every non-prime attribute (attributes not part of the primary key) must be fully functionally dependent on the entire primary key, eliminating partial dependencies. This ensures that each column in the table contributes directly to the uniqueness of the primary key, promoting a more streamlined and efficient database structure.

To meet the requirements of Second Normal Form (2NF), we need to ensure that all non-key attributes are fully functionally dependent on the entire primary key. So, we can create a separate table for each primary key attribute, including its dependent attributes.

Step 1: Identify the Primary Key

The original table has a composite primary key {SID, CID} since it uniquely identifies each record, and the non-key attributes are {S_name, C_name, Grade, Faculty, F_phone}.

Step 2: Identify Partial Dependencies

• {SID} partially determines {S_name}.
• {CID} partially determines {C_name, Faculty, F_phone}.

Step 3: Create Separate Tables for Partial Dependencies

Course Table(2NF)

Enrolment Table (2NF)

Now, each table has a single primary key, and non-key attributes are fully functionally dependent on that key, meeting the requirements of 2NF.

Third Normal Form (3NF)

Third Normal Form (3NF) takes normalization a step further by addressing transitive dependencies. A transitive dependency occurs when a non-prime attribute is functionally dependent on another non-prime attribute, rather than on the primary key. A table must meet the requirements of 2NF before progressing to 3NF. In 3NF, non-prime attributes should not depend on other non-prime attributes, eliminating any transitive dependencies. This level of normalization enhances data integrity, reduces redundancy, and ensures that each column's data is solely dependent on the primary key. In essence, 3NF refines the organization of data, contributing to a more robust and reliable database design.

So, for Third Normal Form (3NF), we need to eliminate transitive dependencies.

Step 1: Identify Transitive Dependencies

• There is a transitive dependency between {CID} and {Faculty, F_phone} in the Course Table.

Step 2: Create New Tables to Remove Transitive Dependencies

`

Student Table (3NF)

Course Table(3NF)

Enrolment Table (3NF)

Are you struggling to keep up with the demands of your academic journey? Don't worry, we've got your back! Exam Question Bank is your trusted partner in achieving academic excellence for all kind of technical and non-technical subjects. Our comprehensive range of academic services is designed to cater to students at every level. Whether you're a high school student, a college undergraduate, or pursuing advanced studies, we have the expertise and resources to support you.

To connect with expert and ask your query click here Exam Question Bank