🎓 Database Management Systems — Assignment Student Management System — Normalization
(UNF → 1NF → 2NF → 3NF)
DATABASE MANAGEMENT SYSTEMS
Assignment
🎓 Student Management System
Database Normalization: UNF → 1NF → 2NF → 3NF
Submitted to:
Department of Computer Science
⚠️UNF 1 1NF
1️⃣ 2️⃣2NF 3️⃣3NF
Raw Data Atomic Values No Partial Dep. No Transitive Dep.
Section 1 — Unnormalized Form (UNF)
What is Unnormalized Form (UNF)?
✔ Raw data with NO normalization applied
✔ Multiple values stuffed in a single cell — called Repeating Groups
✔ Lots of duplicate / redundant data throughout the table
✔ Causes Insert, Update, and Delete Anomalies
Below is our original raw table before any normalization. Notice how Courses, Grades, and Instructor
columns hold multiple values in one cell — this is the core problem.
STUDENT_INFO — Unnormalized Form (UNF)
Courses Grades ⚠ Instructors
Std_ID Std_Name DOB Dept HOD
⚠ multi multi ⚠ multi
S01 Ali Hassan 2002-04-12 CS Dr. Usman DBMS, A, B, A Dr.
OOP, OS Kamran,
Ms. Sana,
Mr. Raza
S02 Sara Khan 2003-07-25 SE Dr. Nadia SE, HCI B, A Dr. Imran,
🔑 PK = Primary Key | 🔗 FK = Foreign Key | Page 1 of 7
🎓 Database Management Systems — Assignment Student Management System — Normalization
(UNF → 1NF → 2NF → 3NF)
Courses Grades ⚠ Instructors
Std_ID Std_Name DOB Dept HOD
⚠ multi multi ⚠ multi
Ms. Hira
S03 Usman 2001-11-05 CS Dr. Usman DBMS, AI A, A Dr.
Raza Kamran,
Dr. Zara
S04 Fatima Ali 2002-09-18 IT Prof. Tariq NW, Web C, B Mr. Babar,
Ms. Noor
🔴 Problems: Repeating groups in Courses, Grades, Instructors — NOT acceptable in a relational
database
Anomalies Present:
• Insert Anomaly: Cannot add a new course without a student enrolled
• Delete Anomaly: Deleting a student loses all course information
• Update Anomaly: Changing HOD requires updating every single student row
Section 2 — First Normal Form (1NF)
Rules of First Normal Form (1NF)
✔ Each column must contain ATOMIC (single, indivisible) values
✔ No repeating groups or arrays in any cell
✔ Each row must be unique — identified by a Primary Key
✔ All values in a column must be of the same data type
🔧 Changes Made: Split every course into its own row (one row per student–course pair). Each cell
now holds exactly ONE value. Composite Primary Key = (Std_ID + Course_ID)
STUDENT_INFO — After 1NF
🔑
🔑 Std_ID
Course_I Std_Name DOB Dept HOD Course_Name Grade Instructor
(PK)
D (PK)
S01 C101 Ali Hassan 2002-04- CS Dr. DBMS A Dr.
12 Usman Kamran
S01 C102 Ali Hassan 2002-04- CS Dr. OOP B Ms. Sana
12 Usman
S01 C103 Ali Hassan 2002-04- CS Dr. OS A Mr. Raza
12 Usman
🔑 PK = Primary Key | 🔗 FK = Foreign Key | Page 2 of 7
🎓 Database Management Systems — Assignment Student Management System — Normalization
(UNF → 1NF → 2NF → 3NF)
🔑
🔑 Std_ID
Course_I Std_Name DOB Dept HOD Course_Name Grade Instructor
(PK)
D (PK)
S02 C201 Sara Khan 2003-07- SE Dr. Nadia SE B Dr. Imran
25
S02 C202 Sara Khan 2003-07- SE Dr. Nadia HCI A Ms. Hira
25
S03 C101 Usman 2001-11- CS Dr. DBMS A Dr.
Raza 05 Usman Kamran
S03 C104 Usman 2001-11- CS Dr. AI A Dr. Zara
Raza 05 Usman
S04 C301 Fatima Ali 2002-09- IT Prof. NW C Mr. Babar
18 Tariq
S04 C302 Fatima Ali 2002-09- IT Prof. Web B Ms. Noor
18 Tariq
✅ 1NF Achieved — All values are atomic. Composite PK = (Std_ID + Course_ID)
Problem Still Remaining — Partial Dependency:
• Std_Name, DOB, Dept, HOD → depend only on Std_ID (part of PK) — PARTIAL dependency!
• Course_Name, Instructor → depend only on Course_ID (part of PK) — PARTIAL dependency!
• Grade → depends on BOTH Std_ID + Course_ID — this is correct ✅
Section 3 — Second Normal Form (2NF)
Rules of Second Normal Form (2NF)
✔ Table must already be in 1NF
✔ Every non-key attribute must be FULLY FUNCTIONALLY DEPENDENT on the ENTIRE
Primary Key
✔ No PARTIAL DEPENDENCY allowed — a non-key column must not depend on only part
of the composite PK
✔ Fix: Decompose the table — move partially-dependent columns to separate tables
Functional Dependencies Identified:
▶ Std_ID only → Std_Name, DOB, Dept, HOD → PARTIAL ❌
▶ Course_ID only → Course_Name, Instructor → PARTIAL ❌
▶ Std_ID + Course_ID → Grade → FULL dependency ✅
After decomposing — 3 Tables Created:
🔑 PK = Primary Key | 🔗 FK = Foreign Key | Page 3 of 7
🎓 Database Management Systems — Assignment Student Management System — Normalization
(UNF → 1NF → 2NF → 3NF)
Table 1: STUDENT (Std_ID → Std_Name, DOB, Dept, HOD)
🔑 Std_ID (PK) Std_Name DOB Dept HOD
S01 Ali Hassan 2002-04-12 CS Dr. Usman
S02 Sara Khan 2003-07-25 SE Dr. Nadia
S03 Usman Raza 2001-11-05 CS Dr. Usman
S04 Fatima Ali 2002-09-18 IT Prof. Tariq
✅ Std_Name, DOB, Dept, HOD fully depend on Std_ID alone — no partial dependency
Table 2: COURSE (Course_ID → Course_Name, Instructor)
🔑 Course_ID (PK) Course_Name Instructor
C101 DBMS Dr. Kamran
C102 OOP Ms. Sana
C103 OS Mr. Raza
C201 Software Eng. Dr. Imran
C202 HCI Ms. Hira
C104 AI Dr. Zara
C301 Networks Mr. Babar
C302 Web Dev Ms. Noor
✅ Course_Name and Instructor fully depend on Course_ID alone — no partial dependency
Table 3: ENROLLMENT (Std_ID + Course_ID → Grade)
🔑 Std_ID (PK/FK) 🔑 Course_ID (PK/FK) Grade
S01 C101 A
S01 C102 B
S01 C103 A
S02 C201 B
S02 C202 A
S03 C101 A
S03 C104 A
🔑 PK = Primary Key | 🔗 FK = Foreign Key | Page 4 of 7
🎓 Database Management Systems — Assignment Student Management System — Normalization
(UNF → 1NF → 2NF → 3NF)
🔑 Std_ID (PK/FK) 🔑 Course_ID (PK/FK) Grade
S04 C301 C
S04 C302 B
✅ Grade fully depends on composite PK (Std_ID + Course_ID) — fully correct
Problem Still Remaining — Transitive Dependency:
• In STUDENT table: Std_ID → Dept → HOD
• HOD does NOT directly depend on Std_ID — it depends on Dept first (Transitive!)
• This violates 3NF — we need to fix it in the next step
Section 4 — Third Normal Form (3NF)
Rules of Third Normal Form (3NF)
✔ Table must already be in 2NF
✔ No TRANSITIVE DEPENDENCY allowed
✔ A non-key attribute must NOT depend on another non-key attribute
✔ Rule: If A → B and B → C, then A → C is transitive — remove it!
✔ Fix: Move the transitively-dependent attribute into its own separate table
Transitive Dependency Found in STUDENT table:
▶ Std_ID → Dept (direct — OK ✅)
▶ Dept → HOD — TRANSITIVE ❌ (HOD depends on Dept, not Std_ID)
🔧 Solution: Remove HOD from STUDENT table. Create a new DEPARTMENT table where HOD
directly depends on Dept_ID.
After removing transitive dependency — 4 Final Tables:
Table 1: STUDENT — Final (3NF)
🔑 Std_ID (PK) Std_Name DOB Email 🔗 Dept_ID (FK)
S01 Ali Hassan 2002-04-12 ali@[Link] D01
S02 Sara Khan 2003-07-25 sara@[Link] D02
🔑 PK = Primary Key | 🔗 FK = Foreign Key | Page 5 of 7
🎓 Database Management Systems — Assignment Student Management System — Normalization
(UNF → 1NF → 2NF → 3NF)
🔑 Std_ID (PK) Std_Name DOB Email 🔗 Dept_ID (FK)
S03 Usman Raza 2001-11-05 usman@[Link] D01
S04 Fatima Ali 2002-09-18 fatima@[Link] D03
✅ HOD removed — replaced with Dept_ID (FK). No transitive dependency.
Table 2: DEPARTMENT — New Table — Transitive Dependency Resolved
🔑 Dept_ID (PK) Dept_Name HOD Location
D01 Computer Science Dr. Usman Block A
D02 Software Eng. Dr. Nadia Block B
D03 Information Tech. Prof. Tariq Block C
✅ HOD now directly depends on Dept_ID — fully correct. Transitive dependency resolved.
Table 3: COURSE — Final (3NF)
🔑 Course_ID (PK) Course_Name Credit_Hrs Instructor
C101 Database Mgmt Systems 3 Dr. Kamran
C102 Object Oriented Prog. 3 Ms. Sana
C103 Operating Systems 3 Mr. Raza
C201 Software Engineering 3 Dr. Imran
C202 Human Comp. Interaction 3 Ms. Hira
C104 Artificial Intelligence 3 Dr. Zara
C301 Computer Networks 3 Mr. Babar
C302 Web Development 3 Ms. Noor
✅ No partial or transitive dependencies in COURSE table.
Table 4: ENROLLMENT — Final (3NF)
🔑 Enroll_ID (PK) 🔗 Std_ID (FK) 🔗 Course_ID (FK) Enroll_Date Grade
E001 S01 C101 2024-01-15 A
E002 S01 C102 2024-01-15 B
🔑 PK = Primary Key | 🔗 FK = Foreign Key | Page 6 of 7
🎓 Database Management Systems — Assignment Student Management System — Normalization
(UNF → 1NF → 2NF → 3NF)
🔑 Enroll_ID (PK) 🔗 Std_ID (FK) 🔗 Course_ID (FK) Enroll_Date Grade
E003 S01 C103 2024-01-15 A
E004 S02 C201 2024-01-15 B
E005 S02 C202 2024-01-15 A
E006 S03 C101 2024-01-16 A
E007 S03 C104 2024-01-16 A
E008 S04 C301 2024-01-16 C
E009 S04 C302 2024-01-16 B
✅ All attributes fully depend on Enroll_ID. Std_ID and Course_ID are Foreign Keys.
Section 5 — Normalization Summary
Step Tables Created What Was Fixed Status
⚠️UNF 1 (raw) Nothing — raw data with ❌ Not Normalized
repeating groups
11️⃣1NF 1 table Repeating groups ⚠️Partial Dep. Exists
removed — atomic
values
2️⃣2NF 3 tables Partial dependency ⚠️Transitive Dep.
removed — tables split Exists
3️⃣3NF 4 tables Transitive dependency ✅ Fully Normalized
removed — DEPT
added
🎉 3NF Achieved — Database is Fully Normalized!
No Repeating Groups ✅ No Partial Dependencies ✅ No Transitive Dependencies ✅
🔑 PK = Primary Key | 🔗 FK = Foreign Key | Page 7 of 7