Normalization Practice -
Solution Approaches & Hints
TABLE 1: Employee Project
Assignment - SOLUTION APPROACH
Issues in Original Table:
1NF Violation: ProjectCode, ProjectName, Duration, TaskAssigned are repeating groups (one employee can
work on multiple projects)
Transitive Dependency: Department is dependent on EmployeeName (non-key attribute)
Step 1: Convert to 1NF
Remove repeating groups by creating separate rows:
EMPLOYEE_PROJECT Table: | EmpID | EmployeeName | Department | ProjectCode | ProjectName | Duration |
TaskAssigned | |-------|--------------|------------|-------------|-------------|----------|--------------| | E01 | Ahmed Hassan | HR | P001 |
Recruitment Drive | 3 | Interviewing | | E01 | Ahmed Hassan | HR | P002 | Training Program | 6 | Scheduling | | E02 |
Fatima Ali | IT | P003 | System Upgrade | 4 | Development | | ... | ... | ... | ... | ... | ... | ... |
Step 2: Convert to 2NF
Remove partial dependencies:
EMPLOYEE Table (PK: EmpID): | EmpID | EmployeeName | Department |
PROJECT Table (PK: ProjectCode): | ProjectCode | ProjectName | Duration |
ASSIGNMENT Table (PK: EmpID, ProjectCode; FK: EmpID, ProjectCode): | EmpID | ProjectCode | TaskAssigned |
Step 3: Convert to 3NF
Check for transitive dependencies. No additional issues found in this case.
Final Schema:
EMPLOYEE (EmpID, EmployeeName, Department)
PROJECT (ProjectCode, ProjectName, Duration)
ASSIGNMENT (EmpID, ProjectCode, TaskAssigned) - with FKs to EMPLOYEE and PROJECT
Total Tables: 3
TABLE 2: Hospital Patient Record
System - SOLUTION APPROACH
Issues in Original Table:
1NF Violation: DiseaseCode, DiseaseName, Medicine, Dosage are repeating groups
Transitive Dependency: DoctorContactNo depends on Doctor (non-key attribute)
Step 1: Convert to 1NF
Flatten repeating groups:
PATIENT_DISEASE Table: | PatientID | PatientName | Doctor | DoctorContactNo | DiseaseCode | DiseaseName |
Medicine | Dosage | (One row per disease-medicine combination)
Step 2: Convert to 2NF
Create separate tables for multi-valued attributes:
PATIENT Table (PK: PatientID): | PatientID | PatientName | Doctor |
DOCTOR Table (PK: Doctor): | Doctor | DoctorContactNo |
DISEASE Table (PK: DiseaseCode): | DiseaseCode | DiseaseName |
MEDICATION Table (PK: MedicineCode): | MedicineCode | MedicineName |
PATIENT_DIAGNOSIS Table (PK: PatientID, DiseaseCode; FK: PatientID, DiseaseCode, MedicineCode): | PatientID
| DiseaseCode | MedicineCode | Dosage |
Step 3: Convert to 3NF
Separate Doctor information:
Update PATIENT Table: | PatientID | PatientName | DoctorID |
Final Schema:
PATIENT (PatientID, PatientName, DoctorID)
DOCTOR (DoctorID, DoctorName, DoctorContactNo)
DISEASE (DiseaseCode, DiseaseName)
MEDICATION (MedicineCode, MedicineName)
PATIENT_DIAGNOSIS (PatientID, DiseaseCode, MedicineCode, Dosage)
Total Tables: 5
TABLE 3: Library Book Catalog System
- SOLUTION APPROACH
Issues in Original Table:
1NF Violation: AuthorName, AuthorEmail are repeating groups (multiple authors per book)
Transitive Dependency: PublisherCity depends on PublisherName (non-key attribute)
Step 1: Convert to 1NF
Remove repeating author groups:
Create separate rows for each author of a book.
Step 2: Convert to 2NF
Separate book information from author information:
BOOK Table (PK: BookID): | BookID | Title | ISBN | Genre | PublisherID | CopiesAvailable | AvailableDate |
AUTHOR Table (PK: AuthorID): | AuthorID | AuthorName | AuthorEmail |
PUBLISHER Table (PK: PublisherID): | PublisherID | PublisherName | PublisherCity |
BOOK_AUTHOR Table (PK: BookID, AuthorID; FK: BookID, AuthorID): | BookID | AuthorID |
Step 3: Convert to 3NF
No additional transitive dependencies after separating Publisher.
Final Schema:
BOOK (BookID, Title, ISBN, Genre, PublisherID, CopiesAvailable, AvailableDate)
AUTHOR (AuthorID, AuthorName, AuthorEmail)
PUBLISHER (PublisherID, PublisherName, PublisherCity)
BOOK_AUTHOR (BookID, AuthorID)
Total Tables: 4
TABLE 4: Online Store Order System -
SOLUTION APPROACH
Issues in Original Table:
1NF Violation: ProductCode, ProductName, UnitPrice, Quantity are repeating groups (multiple products per
order)
Transitive Dependency: SupplierCity depends on SupplierName; SupplierName may depend on ProductCode
Step 1: Convert to 1NF
Flatten repeating products:
One row per OrderID-ProductCode combination.
Step 2: Convert to 2NF
Separate order details from product details:
CUSTOMER Table (PK: CustomerID): | CustomerID | CustomerName | CustomerEmail |
PRODUCT Table (PK: ProductCode): | ProductCode | ProductName | UnitPrice | SupplierID |
SUPPLIER Table (PK: SupplierID): | SupplierID | SupplierName | SupplierCity |
ORDER Table (PK: OrderID; FK: CustomerID): | OrderID | CustomerID | OrderDate |
ORDER_DETAIL Table (PK: OrderID, ProductCode; FK: OrderID, ProductCode): | OrderID | ProductCode | Quantity |
Step 3: Convert to 3NF
Separate supplier information from product:
(Already done in Step 2)
Final Schema:
CUSTOMER (CustomerID, CustomerName, CustomerEmail)
ORDER (OrderID, CustomerID, OrderDate)
PRODUCT (ProductCode, ProductName, UnitPrice, SupplierID)
SUPPLIER (SupplierID, SupplierName, SupplierCity)
ORDER_DETAIL (OrderID, ProductCode, Quantity)
Total Tables: 5
TABLE 5: University Course
Registration - SOLUTION APPROACH
Issues in Original Table:
1NF Violation: CourseCode, CourseName, Instructor, InstructorOffice, Credits, ClassTime are repeating groups
Transitive Dependency: InstructorOffice depends on Instructor; CourseName depends on CourseCode
Step 1: Convert to 1NF
Flatten repeating courses:
One row per StudentID-CourseCode combination.
Step 2: Convert to 2NF
Separate student information from course information:
STUDENT Table (PK: StudentID): | StudentID | StudentName | Semester |
COURSE Table (PK: CourseCode): | CourseCode | CourseName | Credits |
INSTRUCTOR Table (PK: InstructorID): | InstructorID | InstructorName | InstructorOffice |
COURSE_SCHEDULE Table (PK: CourseCode, InstructorID; FK: CourseCode, InstructorID): | CourseCode |
InstructorID | ClassTime |
REGISTRATION Table (PK: StudentID, CourseCode; FK: StudentID, CourseCode): | StudentID | CourseCode |
Semester |
Step 3: Convert to 3NF
Separate instructor information:
(Already done in Step 2)
Final Schema:
STUDENT (StudentID, StudentName)
COURSE (CourseCode, CourseName, Credits)
INSTRUCTOR (InstructorID, InstructorName, InstructorOffice)
COURSE_SCHEDULE (CourseCode, InstructorID, ClassTime, Semester)
REGISTRATION (StudentID, CourseCode, Semester)
Total Tables: 5
Quick Reference: Normalization
Checklist
1NF (First Normal Form) - Atomic Values Only
✓ Remove repeating groups
✓ Ensure each attribute contains only atomic (single) values
✓ No multi-valued attributes
2NF (Second Normal Form) - Remove Partial
Dependencies
✓ Must be in 1NF first
✓ Remove attributes that depend on only part of a composite key
✓ For tables with single-key PKs, usually satisfied if in 1NF
3NF (Third Normal Form) - Remove Transitive
Dependencies
✓ Must be in 2NF first
✓ Remove non-key attributes that depend on other non-key attributes
✓ All attributes should depend only on the primary key
Common Patterns to Look For
Issue Pattern Solution
Split into separate rows/tables
Repeating Groups Multiple values in one column
(1NF)
Non-key depends on part of Create new table with that key
Partial Dependency
composite key part
Transitive Move transitive attribute to new
Non-key1 → Non-key2 → Key
Dependency table
Multi-valued
One attribute with multiple values Create junction/linking table
Attribute
Exam Tips
1. Always define Primary Keys (PK) and Foreign Keys (FK)
2. Show your working - examiners want to see your thought process
3. Use clear table names - descriptive and following naming conventions
4. Draw ER diagrams if space allows - shows relationships clearly
5. Count your final tables - provide the total number explicitly