Collge_task

This repo contains the DBMS Normalization task for Foundation of computer science module.

Database Normalization

This project shows database normalization step by step.
It covers NNF, 1NF, 2NF, and 3NF.
All examples use simple student and course data.

---

NON NORMALIZED FORM (NNF)

Data has repeating columns.
Not good for database design.

CREATE TABLE student_all (
    student_id INT,
    student_name VARCHAR(50),
    course1 VARCHAR(50),
    course2 VARCHAR(50),
    teacher VARCHAR(50),
    dept VARCHAR(50)
);

INSERT INTO student_all VALUES
(1, 'Ram', 'Math', 'Science', 'Mr.A', 'Science'),
(2, 'Sita', 'Math', 'English', 'Ms.B', 'Arts');

SELECT * FROM student_all;
FIRST NORMAL FORM (1NF)
No repeating columns.
One course per row.
CREATE TABLE student_course_1nf (
    student_id INT,
    student_name VARCHAR(50),
    course VARCHAR(50),
    teacher VARCHAR(50),
    dept VARCHAR(50)
);

INSERT INTO student_course_1nf VALUES
(1, 'Ram', 'Math', 'Mr.A', 'Science'),
(1, 'Ram', 'Science', 'Mr.A', 'Science'),
(2, 'Sita', 'Math', 'Ms.B', 'Arts'),
(2, 'Sita', 'English', 'Ms.B', 'Arts');

SELECT * FROM student_course_1nf;
SECOND NORMAL FORM (2NF)
Remove partial dependency.
Split data into multiple tables.
CREATE TABLE student_2nf (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

CREATE TABLE course_2nf (
    course_name VARCHAR(50) PRIMARY KEY,
    teacher VARCHAR(50),
    dept VARCHAR(50)
);

CREATE TABLE enrollment_2nf (
    student_id INT,
    course_name VARCHAR(50),
    PRIMARY KEY (student_id, course_name)
);

INSERT INTO student_2nf VALUES
(1, 'Ram'),
(2, 'Sita');

INSERT INTO course_2nf VALUES
('Math', 'Mr.A', 'Science'),
('Science', 'Mr.A', 'Science'),
('English', 'Ms.B', 'Arts');

INSERT INTO enrollment_2nf VALUES
(1, 'Math'),
(1, 'Science'),
(2, 'Math'),
(2, 'English');

SELECT * FROM student_2nf;
SELECT * FROM course_2nf;
SELECT * FROM enrollment_2nf;
THIRD NORMAL FORM (3NF)
Remove transitive dependency.
Separate department data.
CREATE TABLE student_3nf (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50)
);

CREATE TABLE department_3nf (
    dept_id INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE course_3nf (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50),
    dept_id INT
);

CREATE TABLE enrollment_3nf (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

INSERT INTO student_3nf VALUES
(1, 'Ram'),
(2, 'Sita');

INSERT INTO department_3nf VALUES
(10, 'Science'),
(20, 'Arts');

INSERT INTO course_3nf VALUES
(101, 'Math', 10),
(102, 'Science', 10),
(103, 'English', 20);

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

SELECT * FROM student_3nf;
SELECT * FROM department_3nf;
SELECT * FROM course_3nf;
SELECT * FROM enrollment_3nf;