DBMS Normalization

Normalization is a process in database design aimed at ensuring data consistency and integrity. This process focuses on reducing data redundancy, making the database structure more efficient, and preventing anomalies that may arise during data updates.

The levels of normalization are referred to as normal forms, each based on a specific set of rules. These rules determine the organization of data in a database table. Normalization levels are typically classified into five main forms:

1. First Normal Form (1NF)

Definition: For a table to be in 1NF, all columns must contain atomic (indivisible) values. That is, each cell must contain only one value. Goal: Eliminate multi-valued fields or repeating groups. Example: If a student table contains multiple addresses for a student, those addresses should be placed in separate rows.

1NF Example

A table is in 1NF if each column contains only atomic (indivisible) values. That is, there should be no multiple values in a single cell, and each record must be unique.

Fixing a Table Not in 1NF

-- Example: Not in 1NF
CREATE TABLE Students (
    student_id INT,
    student_name VARCHAR(50),
    courses VARCHAR(255)  -- A student can have multiple courses.
);

INSERT INTO Students VALUES
(1, 'Ali', 'Math, Science'),
(2, 'Ayşe', 'English, History');

This table has a courses column that holds multiple courses for each student, which violates 1NF. To fix this, each course should be in a separate row.

-- Table in 1NF:
CREATE TABLE Students (
    student_id INT,
    student_name VARCHAR(50),
    course VARCHAR(50)  -- Each course will be in a separate row.
);

INSERT INTO Students VALUES
(1, 'Ali', 'Math'),
(1, 'Ali', 'Science'),
(2, 'Ayşe', 'English'),
(2, 'Ayşe', 'History');

2. Second Normal Form (2NF)

Definition: To achieve 2NF, the table must first be in 1NF. Additionally, every non-prime (non-key) attribute must be fully functionally dependent on the entire primary key. That is, each attribute must be fully dependent on the whole key. Goal: Eliminate partial dependencies. Example: If student information and course information are stored together, and the course name depends only on the course code, the course name should be moved to a separate table.

2NF Example

To be in 2NF, a table must be in 1NF, and every non-prime attribute (attribute that is not part of a candidate key) must be fully functionally dependent on the entire primary key. In other words, partial dependencies must be removed.

Fixing a Table Not in 2NF

-- Table in 1NF:
CREATE TABLE Enrollments (
    student_id INT,
    course_id INT,
    course_name VARCHAR(50),
    teacher_name VARCHAR(50)
);

INSERT INTO Enrollments VALUES
(1, 101, 'Math', 'Mr. A'),
(1, 102, 'Science', 'Ms. B'),
(2, 103, 'English', 'Dr. C');

In this table, course_name and teacher_name depend only on course_id, not on the entire primary key (student_id, course_id). To reach 2NF, we need to separate the data into two tables.

-- Table in 2NF:
-- 1. Enrollments table: Student and course relation.
CREATE TABLE Enrollments (
    student_id INT,
    course_id INT
);

INSERT INTO Enrollments VALUES
(1, 101),
(1, 102),
(2, 103);

-- 2. Courses table: Course information.
CREATE TABLE Courses (
    course_id INT,
    course_name VARCHAR(50),
    teacher_name VARCHAR(50)
);

INSERT INTO Courses VALUES
(101, 'Math', 'Mr. A'),
(102, 'Science', 'Ms. B'),
(103, 'English', 'Dr. C');

3. Third Normal Form (3NF)

Definition: To achieve 3NF, the table must first be in 2NF. Also, every non-prime attribute must not be dependent on another non-prime attribute. In other words, transitive dependencies must be removed. Goal: Eliminate transitive dependencies. Example: If student and school information are stored together, and the school’s address depends on the student, the school details should be moved to a separate table.

3NF Example

To be in 3NF, a table must be in 2NF, and all non-prime attributes must be non-transitively dependent on the primary key. This means that no non-prime attribute should depend on another non-prime attribute.

Fixing a Table Not in 3NF

-- Table in 2NF:
CREATE TABLE Students (
    student_id INT,
    student_name VARCHAR(50),
    student_address VARCHAR(255),
    city_name VARCHAR(50)
);

INSERT INTO Students VALUES
(1, 'Ali', '123 Street', 'Istanbul'),
(2, 'Ayşe', '456 Avenue', 'Ankara');

Here, city_name depends on student_address, which is a non-prime attribute. To fix this and reach 3NF, we should remove the transitive dependency.

-- Table in 3NF:
-- 1. Students table: Student information.
CREATE TABLE Students (
    student_id INT,
    student_name VARCHAR(50),
    student_address VARCHAR(255),
    city_id INT
);

INSERT INTO Students VALUES
(1, 'Ali', '123 Street', 1),
(2, 'Ayşe', '456 Avenue', 2);

-- 2. Cities table: City information.
CREATE TABLE Cities (
    city_id INT,
    city_name VARCHAR(50)
);

INSERT INTO Cities VALUES
(1, 'Istanbul'),
(2, 'Ankara');

BCNF Boyce-Codd Normal Form

BCNF is a higher level of normalization compared to the Third Normal Form (3NF), addressing anomalies that 3NF does not resolve. A table is in BCNF if it is in 3NF and, for every functional dependency, the left-hand side (determinant) must be a superkey. This means no non-prime (non-key) attribute should depend on anything other than a candidate key.

Definition: A table is in BCNF if, for every functional dependency X -> Y, X is a superkey.

Example of BCNF Violation

Consider a table where student_id, course_id, and professor_name are attributes:

CREATE TABLE CourseAssignments (
    student_id INT,
    course_id INT,
    professor_name VARCHAR(50),
    PRIMARY KEY (student_id, course_id)
);

Now, assume we have the following functional dependencies:

1. student_id, course_id → professor_name (The professor assigned to a student in a course is determined by the combination of student and course).
2. course_id → professor_name (Each course is assigned only one professor, no matter which student is enrolled).

Here, the second dependency course_id → professor_name violates BCNF because course_id is not a superkey, yet it determines professor_name. To fix this and bring it into BCNF, we can decompose the table into two:

-- 1. Course table: Stores the course information.
CREATE TABLE Courses (
    course_id INT PRIMARY KEY,
    professor_name VARCHAR(50)
);

-- 2. CourseAssignments table: Stores the student and course relation.
CREATE TABLE CourseAssignments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

Now, both tables are in BCNF because in each case, the left-hand side of the functional dependencies is a superkey.

4. Fourth Normal Form (4NF)

Definition: To achieve 4NF, the table must first be in 3NF. Additionally, there should be no multi-valued dependencies in the table. Goal: Eliminate multi-valued dependencies. Example: If a table stores student information and a student can have multiple hobbies, these multi-valued dependencies should be stored in separate tables.

4NF Example

To be in 4NF, a table must be in 3NF, and no multi-valued dependencies should exist. A multi-valued dependency occurs when one attribute determines multiple values for other attributes.

Fixing a Table Not in 4NF

-- Table in 3NF:
CREATE TABLE Students (
    student_id INT,
    student_name VARCHAR(50),
    hobby VARCHAR(50),
    language VARCHAR(50)
);

INSERT INTO Students VALUES
(1, 'Ali', 'Reading', 'English'),
(1, 'Ali', 'Cycling', 'French'),
(2, 'Ayşe', 'Dancing', 'German');

In this table, both hobby and language are independent multi-valued dependencies. To bring this table into 4NF, we separate these into different tables.

-- Table in 4NF:
-- 1. Students table: Student information.
CREATE TABLE Students (
    student_id INT,
    student_name VARCHAR(50)
);

INSERT INTO Students VALUES
(1, 'Ali'),
(2, 'Ayşe');

-- 2. Hobbies table: Student hobbies.
CREATE TABLE Hobbies (
    student_id INT,
    hobby VARCHAR(50)
);

INSERT INTO Hobbies VALUES
(1, 'Reading'),
(1, 'Cycling'),
(2, 'Dancing');

-- 3. Languages table: Student languages.
CREATE TABLE Languages (
    student_id INT,
    language VARCHAR(50)
);

INSERT INTO Languages VALUES
(1, 'English'),
(1, 'French'),
(2, 'German');

Key Differences Between BCNF and 4NF

Type of Dependency:

• BCNF: Deals with functional dependencies (one attribute depends on another).
• 4NF: Deals with multivalued dependencies (one attribute determines multiple independent values).

Eliminating Redundancy:

• BCNF: Prevents non-prime attributes from depending on non-superkeys.
• 4NF: Prevents independent multivalued attributes from being stored in the same table.

Application Order:

• 4NF is applied after BCNF because BCNF resolves functional dependencies, while 4NF addresses multivalued dependencies that BCNF does not handle.

5. Fifth Normal Form (5NF)

Definition: To achieve 5NF, the table must first be in 4NF. Additionally, any piece of information in the table should be decomposable in only one way. This normal form is necessary to ensure that every piece of information can be derived from a single table. Goal: Eliminate complex join dependencies. Example: If a table combines product, supplier, and order information, these details should be separated to form a more logical structure.

5NF Example

To be in 5NF, a table must be in 4NF, and no join dependency should exist that can be removed by decomposition into smaller tables. In other words, every fact must be represented by a combination of the primary keys.

Fixing a Table Not in 5NF

-- Table in 4NF:
CREATE TABLE StudentCourses (
    student_id INT,
    course_id INT,
    teacher_id INT
);

INSERT INTO StudentCourses VALUES
(1, 101, 1),
(1, 102, 2),
(2, 103, 1);

In this table, student_id, course_id, and teacher_id have a join dependency that can be decomposed into smaller tables. To bring this table into 5NF, we should decompose it further.

-- Table in 5NF:
-- 1. StudentCourses table: Student and course relation.
CREATE TABLE StudentCourses (
    student_id INT,
    course_id INT
);

INSERT INTO StudentCourses VALUES
(1, 101),
(1, 102),
(2, 103);

-- 2. CourseTeachers table: Course and teacher relation.
CREATE TABLE CourseTeachers (
    course_id INT,
    teacher_id INT
);

INSERT INTO CourseTeachers VALUES
(101, 1),
(102, 2),
(103, 1);

-- 3. Students table: Student information.
CREATE TABLE Students (
    student_id INT,
    student_name VARCHAR(50)
);

INSERT INTO Students VALUES
(1, 'Ali'),
(2, 'Ayşe');

-- 4. Teachers table: Teacher information.
CREATE TABLE Teachers (
    teacher_id INT,
    teacher_name VARCHAR(50)
);

INSERT INTO Teachers VALUES
(1, 'Mr. A'),
(2, 'Ms. B');

As a result, each normalization level ensures that the database structure becomes more consistent and efficient by eliminating redundancy and preserving data integrity. These examples demonstrate how the data is organized and how each normalization level contributes to a more optimized and logically structured database.