Understanding Database Normalization

Database normalization is a critical aspect of relational database design, aimed at improving data integrity and organization by minimizing redundancy. The normalization process involves systematically organizing data to avoid certain types of anomalies that can occur during database operations. In this basic guide, we will explore the main normal forms - First Normal Form (1NF), Second Normal Form (2NF) and Third Normal Form (3NF).

1. First Normal Form (1NF):

First Normal Form (1NF) is the foundational step in the normalization process. Its primary goal is to ensure that each column in a table contains atomic, indivisible values. Additionally, there should be no repeating groups of columns.

Understanding 1NF with an Example:

Consider a table representing students and their courses:

In this example, the Courses column violates 1NF because it contains multiple values. To bring it into 1NF, we split the column into separate rows for each course:

Now, each cell contains an atomic value, and there are no repeating groups.

2. Second Normal Form (2NF):

Second Normal Form (2NF) builds on 1NF and aims to eliminate partial dependencies. In 2NF, all non-key attributes must be fully functionally dependent on the entire primary key.

Functional Dependency

A functional dependency exists when the value of one attribute uniquely determines the value of another attribute in the same table. In other words, if knowing the value of attribute A uniquely determines the value of attribute B, we say that B is functionally dependent on A, denoted as A → B.

Candidate Keys

In the context of normalization, a candidate key is a set of one or more columns that uniquely identifies each record in a table. These are potential choices for the primary key of a table. It's essential to identify candidate keys as they play a crucial role in determining functional dependencies.

Understanding candidate keys helps in establishing proper relationships and dependencies within the data.

Primary Key

A primary key is a unique identifier for a record in a table. It serves as a means of uniquely identifying each row or record in the table. The primary key must have two main properties:

1. Uniqueness: Each value in the primary key column must be unique across all rows in the table. No two rows can have the same primary key value.
2. Non-nullability: The primary key column cannot contain null (empty) values. Every record must have a valid and non-null primary key.

Commonly, primary keys are implemented using a single column, but they can also be composite keys, which involve multiple columns to ensure uniqueness. Primary keys are critical for establishing relationships between tables, facilitating data retrieval, and maintaining data integrity.

Foreign Key

A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a link or relationship between two tables, enabling the creation of meaningful associations between records in different tables. The foreign key in one table typically corresponds to the primary key in another table.

Understanding 2NF with an Example:

Applying 2NF from the previous example output will result in Student and Student_Course tables. The logical split is by functional dependency, student specific data are in student table while their associated courses will be in student_course table.

Table: Student

• Primary Key: {Student_ID}

The Student_ID was added to have primary key. This will make the function of the table obvious.

The introduction of Student_ID column is not necessary if there can be another candidate key that is unique enough to become a primary key. In this particular example, Full_Name is the candidate key that has the potential to be a primary key. But can it guarantee that no two people will going the have the same name. Hence the introduction of Student_ID makes sense in this context.

The functional dependency is as follows:

{Student_ID} → {Full_Name, Gender}: The Student_ID uniquely determines the Full_Name and Gender in the first table. For example, for Student_ID 1, the combination of Full_Name and Gender is uniquely determined as {Juan Dela Cruz, Male}.

This a functional dependency because knowing the values on the left side of the arrow uniquely determines the values on the right side.

Table: Student_Course

• Primary Key: {Student_ID, Course}
• Foreign Key: {Student_ID} reference the Primary Key in Student table.

Now, each table represents a single function (i.e. one for student, and another for course data), and all non-key attributes are fully dependent on the primary key.

3. Third Normal Form (3NF):

Third Normal Form (3NF) is a crucial stage in the normalization process, building on the principles of 1NF and 2NF. The primary goal of 3NF is to eliminate transitive dependencies, ensuring that non-prime attributes do not depend on other non-prime attributes.

Transitive Dependency

• Transitive dependency is a specific type of functional dependency that occurs when the value of one attribute determines the value of another attribute through a third attribute.
• If A determines B (A → B) and B determines C (B → C), then A indirectly determines C through the transitive dependency (A → B → C).
• In database normalization, transitive dependencies are generally undesirable, and the goal is to eliminate them to achieve higher normal forms.

Non-Prime Attributes

In the context of normalization, non-prime attributes are attributes that are not part of any candidate key. In other words, they are attributes that are not used to uniquely identify records in a table. Prime attributes, on the other hand, are part of a candidate key.

It's crucial to identify and handle dependencies involving non-prime attributes to achieve a well-organized and normalized database.

Understanding 3NF with an Example:

Expanding the Student_Course table from the previous example and introducing the Department column:

Candidate Keys:

• {Student_ID, Course}
• {Student_ID}

In this case, the data appears to have a transitive dependency, as the Department is functionally dependent on the candidate key {Student_ID, Course}.

Identifying Transitive Dependency

In the given example, the transitive dependency is represented as:

• {Course} → Department

This dependency indicates that a non-prime attribute Department depends on the attribute Course.

Applying 3NF:

To bring this table into 3NF, we need to separate the transitive dependency into a new table (i.e. Course_Department). We create two tables: one for student-course relationships, and one for course-department relationships.

Table: Student_Course

This is still the same output from 2NF after removing the transitive dependency. It indicates that the introduction of the department attribute earlier introduces a transitive dependency.

Table: Course_Department

• Primary Key: {Course}

Now, the tables are in 3NF. The transitive dependency has been eliminated by decomposing the original table into two tables. Each table represents a separate entity with clear functional dependencies. The relationships are maintained through primary and foreign keys.

Normalization helps in maintaining data integrity, reducing redundancy, and making the database more adaptable to changes. However, it's essential to strike a balance and not over-normalize, as it could lead to complex queries and performance issues in certain scenarios.