Database normalization

#programming #productivity #development #mobile

Introduction:

Database normalization is the process of organizing data in a database to reduce duplication and improve consistency. It ensures each piece of information is stored only once and in the right place, making the database easier to manage and update without errors.

Step 1: Base Table

A base table is a table in a database that physically stores the data and is not derived from any other table or view.

Step 2: Identifying Anomalies

Insertion anomaly – You can’t add new data without also adding unnecessary or duplicate information.
Update anomaly – If data stored in multiple places changes, you must update all copies, or inconsistencies occur.
Deletion anomaly – Deleting one piece of data accidentally removes other important information.

Step 3: First Normal Form (1NF)

1NF (First Normal Form) means a table where all columns have atomic (indivisible) values and no repeating groups or multi-valued attributes.

CREATE TABLE Students_1NF (
StudentID INT,
Name VARCHAR(50),
Course VARCHAR(50),
Phone VARCHAR(20),
PRIMARY KEY (StudentID, Course, Phone)
);

Step 4: Second Normal Form (2NF)

No partial dependency. This usually applies when the primary key is composite (made of multiple columns).

CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(100)
);

CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(100),
Instructor VARCHAR2(100),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Enrollments (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Step 5: Third Normal Form (3NF)

No transitive dependencies exist → non-key attributes must depend only on the primary key, not on another non-key attribute.

DROP TABLE IF EXISTS Students;
DROP TABLE IF EXISTS Departments;

CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50) NOT NULL
);

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50) NOT NULL,
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Step 6: Isert Sample Data

-- Insert into Departments
INSERT INTO Departments (DepartmentID, DepartmentName) VALUES
(10, 'Computer Science'),
(20, 'Mathematics'),
(30, 'Physics');

-- Insert into Students
INSERT INTO Students (StudentID, StudentName, DepartmentID) VALUES
(1, 'Alice', 10),
(2, 'Bob', 20),
(3, 'Charlie', 10),
(4, 'Diana', 30),
(5, 'Ethan', 20);