Computer Science - ICT Grade 9 Databases Normalisation and ER Diagrams

Grade 9 Computer Science – Databases: Normalisation & ER Diagrams

1. The Driving Question

"If you’ve ever tried to organize a shared spreadsheet—like a class project tracker or a family grocery list—and ended up with duplicate entries, missing info, or weird errors when you try to update it, you’ve hit the limits of a flat table. How do real databases avoid this mess? And how do you design one so that adding a new student to a school system doesn’t accidentally delete their emergency contact or mix up their homeroom?"

2. The Core Idea – Built, Not Listed

Imagine your school’s library catalog. Right now, it’s a single spreadsheet with columns for Book Title, Author, Genre, Student Borrower, Borrow Date, and Due Date. But what happens when: - A student borrows two books? Their name and contact info get repeated in multiple rows.
- A book has two authors? You either cram both names into one cell or create duplicate rows for the same book.
- A student returns a book late? You have to update every row where their name appears.

This is a denormalized database—it’s messy, wastes space, and risks update anomalies (e.g., forgetting to change a student’s address in one row but not others). Normalization is the process of splitting this single table into smaller, linked tables to eliminate redundancy and ensure data stays consistent. An Entity-Relationship (ER) Diagram is the blueprint for this design, showing how tables (entities) like Students, Books, and Loans connect.

Key Vocabulary:
- Entity
Definition: A real-world object or concept represented as a table in a database (e.g., a Student, a Book).
Example: In a hospital database, Doctors and Patients are entities, but so is Appointments—even though it’s not a "thing" you can touch.
College Note: In advanced database theory, entities can model abstract concepts (e.g., Permissions in a security system) or even events (e.g., Transactions in banking).

• Attribute
  Definition: A single piece of data describing an entity (a column in a table).
  Example: For the Student entity, StudentID (e.g., "S1001") is an attribute, but so is Allergies—even if it’s a list (which might need its own table later).
  College Note: Attributes can be composite (e.g., Address = Street + City + Zip) or derived (e.g., Age calculated from Birthdate).

• Relationship
  Definition: How entities interact (e.g., a Student borrows a Book).
  Example: In a music app, Users create Playlists, and Playlists contain Songs—a three-way relationship.
  College Note: Relationships can have attributes too (e.g., BorrowDate in the Loans relationship between Students and Books).

• Normal Form (1NF, 2NF, 3NF)
  Definition: Rules to reduce redundancy in tables. 1NF = no repeating groups (e.g., no "Author1, Author2" in one cell); 2NF = no partial dependencies (e.g., BookTitle shouldn’t depend on only part of a composite key); 3NF = no transitive dependencies (e.g., DueDate shouldn’t depend on BorrowDate if BorrowDate depends on StudentID).
  Example: A Movie table with columns Title, Director, and Director’s Birthdate violates 3NF because Director’s Birthdate depends on Director, not Title.
  College Note: Higher normal forms (BCNF, 4NF, 5NF) handle edge cases like multi-valued dependencies, but 3NF is the "good enough" standard for most real-world databases.

3. Assessment Translation

How This Appears on Assessments:
- Classroom: Short-answer questions (e.g., "Explain why this table violates 2NF"), ER diagram sketching (e.g., "Draw an ER diagram for a gym membership system"), or SQL queries based on a normalized schema.
- Standardized Tests (e.g., AP CSP, state ICT exams): Multiple-choice questions testing normalization rules (e.g., "Which normal form is violated if a table has a non-key attribute depending on another non-key attribute?") or interpreting ER diagrams (e.g., "How many tables would this diagram require?").
- SAT/ACT: Unlikely to appear directly, but logical reasoning about data organization (e.g., "Which table structure would minimize redundancy?") aligns with the Problem Solving and Data Analysis section.

Distractor Patterns in Multiple Choice:
- Misidentifying normal forms: Confusing 2NF (partial dependencies) with 3NF (transitive dependencies).
- Over-normalizing: Suggesting a table should be split when it’s already in 3NF (e.g., splitting Student into StudentName and StudentID tables).
- Ignoring relationships: Forgetting to include a junction table for many-to-many relationships (e.g., Students and Courses).

Proficient vs. Developing Responses:
| Proficient | Developing | |----------------|----------------| | Question: "Normalize this table to 3NF. Original table: Orders(OrderID, CustomerName, CustomerAddress, ProductID, ProductName, Quantity)." | | | Response: "Split into three tables: Orders(OrderID, CustomerID, ProductID, Quantity), Customers(CustomerID, CustomerName, CustomerAddress), Products(ProductID, ProductName). This removes redundancy (e.g., CustomerAddress isn’t repeated for each order) and ensures no transitive dependencies (e.g., ProductName depends only on ProductID)." | Response: "Make two tables: Orders(OrderID, CustomerName, ProductID, Quantity) and Products(ProductID, ProductName). CustomerAddress is still repeated, so this only reaches 1NF." | | Why it’s proficient: Identifies all dependencies, splits tables correctly, and justifies the design. | Why it’s developing: Misses the Customers table, leaving redundancy. |

Model Student Response (Proficient):
Prompt: "Draw an ER diagram for a system tracking video game tournaments. Include Players, Teams, Tournaments, and Matches. Specify cardinalities (e.g., one-to-many)."

Response:

[Players] ——(1:N)—— [Teams]
   |                     |
   | (N:M)               | (1:N)
   |                     |
[Matches] ——(N:1)—— [Tournaments]

Explanation: - A Player can be on one Team (1:N), but a Team has many Players.
- A Match involves many Players (N:M, so we’d need a junction table like MatchPlayers in the actual database).
- A Tournament has many Matches (1:N), but a Match belongs to one Tournament.

4. Mistake Taxonomy

Mistake 1: Over-Simplifying Relationships
Question: "Design an ER diagram for a library where Books can have multiple Authors and Authors can write multiple Books. What’s the cardinality?" Common Wrong Response: "Books (1:N) Authors" (draws a one-to-many arrow from Books to Authors).
Why It Loses Credit: Misrepresents the relationship as hierarchical. A book can’t "own" an author, and an author doesn’t belong to a single book.
Correct Approach: - This is a many-to-many (N:M) relationship. Draw a line between Books and Authors with "N" and "M" at the ends.
- In the actual database, create a junction table (e.g., BookAuthors) with BookID and AuthorID as foreign keys.

Mistake 2: Ignoring Partial Dependencies in 2NF
Question: "This table violates 2NF. Explain why and fix it.
Enrollments(StudentID, CourseID, StudentName, CourseName, Instructor, Grade)" Common Wrong Response: "Split into Students(StudentID, StudentName) and Courses(CourseID, CourseName, Instructor, Grade)." (Leaves Grade in the wrong table.) Why It Loses Credit: Grade depends on both StudentID and CourseID (the composite key), but the response doesn’t preserve this dependency.
Correct Approach: - Split into three tables: - Students(StudentID, StudentName) - Courses(CourseID, CourseName, Instructor) - Enrollments(StudentID, CourseID, Grade) - Grade now depends on the full composite key (StudentID + CourseID).

Mistake 3: Misapplying 3NF to Derived Attributes
Question: "Does this table violate 3NF? Employees(EmployeeID, Name, Department, DepartmentHead)" Common Wrong Response: "No, because DepartmentHead depends on Department, which is part of the key." (Assumes Department is a key.) Why It Loses Credit: Department is not a key (it’s not unique to each employee), so DepartmentHead is a transitive dependency (EmployeeID → Department → DepartmentHead).
Correct Approach: - Split into two tables: - Employees(EmployeeID, Name, Department) - Departments(Department, DepartmentHead) - Now DepartmentHead depends only on Department, not EmployeeID.

5. Connection Layer

1. Within Computer Science:
   Normalization → SQL Joins — Normalized tables require joins to reconstruct data (e.g., combining Orders and Customers tables to see a customer’s order history). Understanding normalization helps you write efficient queries.

2. Across Subjects:
   ER Diagrams → Biology (Food Webs) — Both model relationships between entities. In a food web, arrows show "eats" relationships (e.g., Grass → Rabbit → Fox); in an ER diagram, arrows show "has" or "belongs to" relationships (e.g., Team → Player). The cardinalities (1:N, N:M) work the same way.

3. Outside School:
   Database Design → Fantasy Sports Leagues — When you draft players for your fantasy team, the app uses a database with tables for Players, Teams, Leagues, and Matchups. A poorly designed database would let you accidentally draft the same player twice or lose track of which league a team belongs to—just like a denormalized spreadsheet.

6. The Stretch Question

"A school database has a Students table with StudentID, Name, HomeroomTeacher, and HomeroomRoomNumber. The principal argues this is already in 3NF because HomeroomRoomNumber depends on HomeroomTeacher, which depends on StudentID. But the IT team says it violates 3NF. Who’s right—and why does it matter in practice?"

Pointer Toward the Answer: The IT team is correct. HomeroomRoomNumber depends on HomeroomTeacher, not directly on StudentID, creating a transitive dependency. In practice, this means: - If a teacher changes rooms, you’d have to update every student in that homeroom (redundancy).
- If a teacher has no students, their room number disappears from the database (anomaly).
The fix? Split into Students(StudentID, Name, HomeroomTeacher) and Homerooms(HomeroomTeacher, HomeroomRoomNumber). Now room numbers are stored once, and updates are easy.