Task 4 — Database Normalization 22 marks

Start from one flat, unnormalized HR report table and take it to 3NF step by step: completeness 6, correctness 6, viva 10.

The unnormalized table (UNF)

A typical HR spreadsheet: one row per employee, with the whole salary history crammed into a single cell and the department stored as “code, name” together. It is unnormalized because cells are not atomic and a repeating group (the salary history) lives inside a row.

Emp_ID	Emp_Name	Department	Designation	Basic_Scale	Salary_History (Effective_Date : Net_Salary)
E01	Mohd. Rahman	D1, CSE	Professor	85000	2019-01-01 : 84000 2023-01-01 : 95000
E03	Rahim Uddin	D2, EEE	Asso. Professor	70000	2023-01-01 : 78000
E07	Kamrul Hasan	D4, Registrar Office	Registrar	72000	2021-01-01 : 74000 2023-01-01 : 80000

Anomalies in this table

Anomaly	Problem
Insertion	A new department with no employee yet cannot be recorded — there is no row to put it in.
Update	Renaming “CSE” means editing every salary row of every CSE employee; miss one and the data is inconsistent.
Deletion	Deleting the last salary row of the only Registrar loses the fact that the Registrar grade exists at all.
Redundancy	Emp_Name, Dept_Name and Basic_Scale are repeated once per salary revision.

A — First Normal Form (1NF)

Rule: every attribute value is atomic — no repeating groups, no multi-valued or composite cells, and each row is unique.

Split Department into Dept_ID + Dept_Name, and flatten the salary history so each (employee, effective date) pair becomes its own row. The primary key must now be the composite {Emp_ID, Effective_Date}, because Emp_ID alone repeats.

Emp_ID PK	Effective_Date PK	Net_Salary	Emp_Name	Dept_ID	Dept_Name	Designation	Basic_Scale
E01	2019-01-01	84000	Mohd. Rahman	D1	CSE	Professor	85000
E01	2023-01-01	95000	Mohd. Rahman	D1	CSE	Professor	85000
E03	2023-01-01	78000	Rahim Uddin	D2	EEE	Asso. Professor	70000
E07	2021-01-01	74000	Kamrul Hasan	D4	Registrar Office	Registrar	72000
E07	2023-01-01	80000	Kamrul Hasan	D4	Registrar Office	Registrar	72000

Functional dependencies

FD1 {Emp_ID, Effective_Date} → Net_Salary (full dependency on the whole key)
FD2 Emp_ID → Emp_Name, Dept_ID, Designation (partial — only half the key)
FD3 Dept_ID → Dept_Name (transitive through a non-key attribute)
FD4 Designation → Basic_Scale (transitive through a non-key attribute)

The table is in 1NF but not 2NF: FD2 is a partial dependency on part of the composite key.

B — Second Normal Form (2NF)

Rule: in 1NF and every non-key attribute is fully functionally dependent on the whole primary key — no partial dependencies. (Only a composite key can violate 2NF.)

Emp_Name, Dept_ID, Dept_Name, Designation and Basic_Scale depend on Emp_ID alone, not on Effective_Date. Move them out into their own relation.

EMP_SALARY — PK {Emp_ID, Effective_Date}

Emp_ID PK	Effective_Date PK	Net_Salary
E01	2019-01-01	84000
E01	2023-01-01	95000
E03	2023-01-01	78000
E07	2021-01-01	74000
E07	2023-01-01	80000

EMPLOYEE_2NF — PK Emp_ID

Emp_ID PK	Emp_Name	Dept_ID	Dept_Name	Designation	Basic_Scale
E01	Mohd. Rahman	D1	CSE	Professor	85000
E03	Rahim Uddin	D2	EEE	Asso. Professor	70000
E07	Kamrul Hasan	D4	Registrar Office	Registrar	72000

Both tables are in 2NF, but EMPLOYEE_2NF still has FD3 and FD4 — transitive dependencies.

C — Third Normal Form (3NF)

Rule: in 2NF and no non-key attribute depends on another non-key attribute — no transitive dependencies. Every non-key attribute depends on the key, the whole key, and nothing but the key.

Emp_ID → Dept_ID → Dept_Name and Emp_ID → Designation → Basic_Scale are both transitive. Extract DEPARTMENT and DESIGNATION, leaving foreign keys behind.

EMPLOYEE_3NF — PK Emp_ID

Emp_ID PK	Emp_Name	Dept_ID FK	Designation_ID FK
E01	Mohd. Rahman	D1	G1
E03	Rahim Uddin	D2	G2
E07	Kamrul Hasan	D4	G5

DEPARTMENT — PK Dept_ID

Dept_ID PK	Dept_Name
D1	CSE
D2	EEE
D4	Registrar Office

DESIGNATION — PK Designation_ID

Designation_ID PK	Designation_Title	Basic_Scale
G1	Professor	85000
G2	Asso. Professor	70000
G5	Registrar	72000

EMP_SALARY — PK {Emp_ID, Effective_Date}

Carried over unchanged from 2NF; it already satisfies 3NF.

Result: 4 relations, all in 3NF. Every non-key attribute now depends on the whole key and nothing but the key — no partial, no transitive dependencies. Each determinant (Emp_ID, Dept_ID, Designation_ID, {Emp_ID, Effective_Date}) is a candidate key of its relation, so these tables are in BCNF as well. This is exactly the structure implemented by the MySQL schema in Task 2.

What normalization bought us

Before (UNF)	After (3NF)
Department name repeated in every salary row	Stored once in DEPARTMENT; rename is a one-row UPDATE
A new department needs an employee to exist	Insert into DEPARTMENT independently
Deleting a salary row can erase a designation	DESIGNATION survives on its own
Salary history hidden inside a cell — unqueryable	One row per revision — `ORDER BY effective_date` works

Trade-off to state in the viva: more tables ⇒ more joins at query time. Normalize for OLTP integrity (this HR system); denormalize deliberately only for read-heavy reporting/warehouse workloads.