Skip to main content

Systems Engineering Practical Lab: Schema Normalization and Logic Simulation

🎓 SS2 | 📅 Term 1 • Week 6

| ⏱ About 45 minutes

Learning goals: Decompose unnormalized flat datasets into First, Second, and Third Normal Forms (1NF, 2NF, 3NF). • Eliminate partial dependencies and transitive dependencies to prevent data redundancy anomalies. • Integrate combinational logic circuit equations into software decision engines. • Conduct an end-to-end diagnostic audit of hardware memory addressing and database query safety.

Lesson menu On this page

Welcome to the SS 2 Mid-Term Systems Engineering Practical Lab!

Over the first five weeks, you mastered advanced computing foundations: 1. Memory Hierarchy & Caching (Latency, hit ratios, spatial and temporal locality). 2. Data Representation (Two's Complement, overflow, UTF-8 byte streams). 3. Combinational Logic (De Morgan's laws, Universal gates, Ripple-Carry Adders). 4. Data Structures (Array memory base addressing, Linear vs Binary Search). 5. Relational Database Architecture (Entities, Keys, ERDs, referential integrity).

In this mid-term lab, you will synthesize these skills into practical systems engineering: you will normalize a real-world messy dataset from Unnormalized Form to 3NF, and verify logic decision engines.

---

Learning Outcomes

By the end of this lab, you will be able to:

1. Perform Database Normalization through 1NF, 2NF, and 3NF step by step.
2. Identify and eliminate Update, Deletion, and Insertion Anomalies.
3. Execute software verification of multi-table database integrity.
4. Audit systems architecture tradeoffs between redundancy and query performance.

---

The Normalization Process (1NF to 3NF)

Normalization is the systematic process of decomposing relational tables to minimize data redundancy and prevent data modification anomalies.

Case Study: An Unnormalized School Clinic Record Table

Consider this messy clinic record:

Table: ClinicVisits_Unnormalized
┌────────────┬─────────────┬───────────┬───────────────────┬──────────────┬───────────────┐
│ StudentID  │ StudentName │ ClassName │ HealthIssues      │ DoctorID     │ DoctorClinic  │
├────────────┼─────────────┼───────────┼───────────────────┼──────────────┼───────────────┤
│ 201        │ Ngozi Obi   │ SS 2A     │ Headache, Malaria │ D-01         │ Central Ward  │
│ 202        │ Musa Danladi│ SS 2B     │ Asthma            │ D-02         │ East Annex    │
│ 203        │ Segun Adele │ SS 2A     │ Fever             │ D-01         │ Central Ward  │
└────────────┴─────────────┴───────────┴───────────────────┴──────────────┴───────────────┘

What is wrong with this table? - Not Atomic: HealthIssues has two values in one cell (Headache, Malaria). - Update Anomaly: If Doctor D-01 moves from Central Ward to North Wing, we must update multiple rows. If we miss one row, our clinic data is corrupted! - Deletion Anomaly: If Student 202 is deleted, we lose the fact that Doctor D-02 works at East Annex!

---

Step 1: First Normal Form (1NF) - Atomicity

Rule: Each column must hold exactly one indivisible atomic value, and each row must have a unique primary key.

Correction: Separate multiple health issues into individual rows:

Table: ClinicVisits_1NF (Composite PK: StudentID + VisitDate + Issue)
(201, 'Ngozi Obi', 'SS 2A', 'Headache', 'D-01', 'Central Ward')
(201, 'Ngozi Obi', 'SS 2A', 'Malaria',  'D-01', 'Central Ward')
(202, 'Musa Danladi', 'SS 2B', 'Asthma', 'D-02', 'East Annex')
(203, 'Segun Adele', 'SS 2A', 'Fever',   'D-01', 'Central Ward')

---

Step 2: Second Normal Form (2NF) - No Partial Dependencies

Rule: The table must be in 1NF, and every non-key column must depend on the entire composite primary key, not just part of it.

Analysis: StudentName and ClassName depend only on StudentID, not on the medical visit! Correction: Split into two tables: Students and ClinicVisits.

Table: Students (PK: StudentID)
(201, 'Ngozi Obi', 'SS 2A')
(202, 'Musa Danladi', 'SS 2B')
(203, 'Segun Adele', 'SS 2A')

Table: ClinicVisits_2NF (PK: VisitID, FK: StudentID)
(1, 201, 'Headache', 'D-01', 'Central Ward')
(2, 201, 'Malaria',  'D-01', 'Central Ward')
(3, 202, 'Asthma',   'D-02', 'East Annex')
(4, 203, 'Fever',    'D-01', 'Central Ward')

---

Step 3: Third Normal Form (3NF) - No Transitive Dependencies

Rule: The table must be in 2NF, and no non-key attribute can depend on another non-key attribute ("no transitive dependency").

Analysis: In ClinicVisits_2NF, DoctorClinic depends on DoctorID, which is not a primary key! Correction: Split into Doctors and ClinicVisits:

Table: Doctors (PK: DoctorID)
('D-01', 'Dr. Alabi', 'Central Ward')
('D-02', 'Dr. Fatima', 'East Annex')

Table: ClinicVisits_3NF (PK: VisitID, FK: StudentID, FK: DoctorID)
(1, 201, 'Headache', 'D-01')
(2, 201, 'Malaria',  'D-01')
(3, 202, 'Asthma',   'D-02')
(4, 203, 'Fever',    'D-01')

Now the database is in 3NF: zero redundant strings, zero update anomalies, and clean referential integrity!

---

Practical Lab Activity: Complete 3NF Implementation in Python SQLite

Let us write a complete script that builds our normalized 3NF database, populates the relations, and performs cross-table SQL verification.

Step 1: Create clinic_3nf_lab.py

import sqlite3

conn = sqlite3.connect(":memory:")
cursor = conn.cursor()
cursor.execute("PRAGMA foreign_keys = ON;")

# 1. Create Students Table
cursor.execute("""
CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    full_name TEXT NOT NULL,
    class_arm TEXT NOT NULL
);
""")

# 2. Create Doctors Table
cursor.execute("""
CREATE TABLE doctors (
    doctor_id TEXT PRIMARY KEY,
    doctor_name TEXT NOT NULL,
    clinic_ward TEXT NOT NULL
);
""")

# 3. Create Clinic Visits (Associative/Transaction Table)
cursor.execute("""
CREATE TABLE clinic_visits (
    visit_id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    doctor_id TEXT NOT NULL,
    diagnosis TEXT NOT NULL,
    visit_date TEXT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id)
);
""")

# Insert Doctors
doctors = [
    ('D-01', 'Dr. Alabi', 'Central Ward'),
    ('D-02', 'Dr. Fatima', 'East Annex')
]
cursor.executemany("INSERT INTO doctors VALUES (?, ?, ?);", doctors)

# Insert Students
students = [
    (201, 'Ngozi Obi', 'SS 2A'),
    (202, 'Musa Danladi', 'SS 2B'),
    (203, 'Segun Adele', 'SS 2A')
]
cursor.executemany("INSERT INTO students VALUES (?, ?, ?);", students)

# Insert Visits
visits = [
    (201, 'D-01', 'Headache', '2026-11-01'),
    (201, 'D-01', 'Malaria Test', '2026-11-01'),
    (202, 'D-02', 'Asthma Check', '2026-11-02'),
    (203, 'D-01', 'Mild Fever', '2026-11-03')
]
cursor.executemany("INSERT INTO clinic_visits (student_id, doctor_id, diagnosis, visit_date) VALUES (?, ?, ?, ?);", visits)
conn.commit()

# Execute a Relational Join Query across all 3 normalized tables
query = """
SELECT 
    v.visit_id,
    s.full_name,
    s.class_arm,
    v.diagnosis,
    d.doctor_name,
    d.clinic_ward
FROM clinic_visits v
JOIN students s ON v.student_id = s.student_id
JOIN doctors d ON v.doctor_id = d.doctor_id
ORDER BY v.visit_id;
"""

cursor.execute(query)
records = cursor.fetchall()

print(f"{'VisitID':<8}{'Student Name':<16}{'Class':<8}{'Diagnosis':<16}{'Doctor':<14}{'Ward'}")
print("-" * 75)
for r in records:
    print(f"{r[0]:<8}{r[1]:<16}{r[2]:<8}{r[3]:<16}{r[4]:<14}{r[5]}")

conn.close()

Step 2: Run the Lab Script

python clinic_3nf_lab.py

---

Expected Results and Model Output

When you run clinic_3nf_lab.py, your terminal will display:

VisitID Student Name    Class   Diagnosis       Doctor        Ward
---------------------------------------------------------------------------
1       Ngozi Obi       SS 2A   Headache        Dr. Alabi     Central Ward
2       Ngozi Obi       SS 2A   Malaria Test    Dr. Alabi     Central Ward
3       Musa Danladi    SS 2B   Asthma Check    Dr. Fatima    East Annex
4       Segun Adele     SS 2A   Mild Fever      Dr. Alabi     Central Ward

Analysis: Data is stored cleanly without redundancy. If Dr. Alabi changes wards, only one row in the doctors table needs modification, while the reporting query instantly reflects the change for all visits!

---

Common Mistakes to Avoid

1. Over-Normalizing Beyond Practical Limits (BCNF/4NF) Without Need: While 3NF is the golden standard for transaction databases (OLTP), over-normalizing into dozens of tiny tables can degrade read performance due to too many joins.
2. Confusing Atomicity with Text Length: A long paragraph of clinical notes is atomic if treated as one text unit. A comma-separated list of three distinct phone numbers in one cell is NOT atomic.
3. Omitting Foreign Key Constraints: Without FOREIGN KEY constraints enabled, SQLite will allow invalid IDs to be entered silently, destroying database consistency.

---

Notebook Summary

• Normalization eliminates update, insertion, and deletion anomalies by restructuring relational schemas.
• 1NF: Requires atomic attribute values and a defined primary key.
• 2NF: 1NF + elimination of partial functional dependencies on composite keys.
• 3NF: 2NF + elimination of transitive dependencies ("Every non-key attribute must depend on the key, the whole key, and nothing but the key").
• Relational JOIN queries reconstruct full business records at query time from normalized tables.

---

Optional Extension Challenge

A school bookstore has the following fields in one table: (ISBN, Title, AuthorName, AuthorCountry, PublisherName, Price). Outline how you would decompose this schema into three normalized 3NF tables. Identify the Primary and Foreign Keys.

Check your understanding

What is the primary requirement for a database table to satisfy First Normal Form (1NF)?

• All attribute values must be atomic (no repeating groups, comma-separated lists, or arrays in a single column), and each row must have a unique primary key
• The table must have at least 1,000 rows
• All column names must be written in capital letters
• The database must run on a Linux server

What condition must be removed from a 1NF table with a composite primary key to achieve Second Normal Form (2NF)?

• Partial Functional Dependencies (where a non-key column depends on only part of the composite primary key)
• Foreign Keys
• Numeric data columns
• Null values in non-key fields

In Third Normal Form (3NF), what type of dependency must be eliminated from all non-key attributes?

• Transitive Dependencies (where a non-key attribute depends on another non-key attribute)
• Binary Dependencies
• Loop Dependencies
• Atomic Dependencies

Which anomaly occurs when updating a customer's address in one row leaves contradictory old addresses in other rows of the same table?

• An Update Anomaly
• A Silicon Latch Anomaly
• A Bus Arbitration Error
• A Parity Inversion

Check answer Try again Feedback appears after you check your answer.