Lesson 2: System Architecture & Relational Database Design
Developing a complex digital outcome to solve a real-world problem for NCEA Level 3 Digital Technologies.
🎯 Ngā Whāinga Akoranga | Learning Intentions
Relational database normalization (1NF, 2NF, 3NF), entity-relationship diagrams (ERD), primary/foreign keys, referential integrity, and data types (Supabase / PostgreSQL).
Design a normalized 3NF Entity-Relationship Diagram, define SQL schemas with foreign key constraints, and model application data flow.
🎥 Media Anchor & Pedagogical Scaffold
Relational Database Design & 3NF Normalization
Video Clip: “Entity Relationship Diagram (ERD) Tutorial - Part 1” — 6 min 10 s.
🧠 1. Before Viewing (Activate & Predict)
Why is storing duplicate data in a flat table a recipe for database anomalies and data corruption?
👁️ 2. During Viewing — watch the full 6m10s clip, capturing as you go
- Capture: Analyse 1NF, 2NF, and 3NF normalization rules removing repeating groups and transitive dependencies.
- Capture: Map one-to-many and many-to-many relationships using junction tables and foreign keys.
- Capture: Define PostgreSQL schema constraints (NOT NULL, UNIQUE, CHECK, RLS policies).
🗣️ 3. After Viewing & Kaiako Move (Process & Apply)
Kaiako Move: Demonstrate visual ERD modelling tools (dbdiagram.io or Draw.io) to blueprint relational database tables.
Immediate Task: Complete Section 2 of your Digital Tech Portfolio: 3NF Relational Database ERD & SQL Schema Specification.
⚡ Whakaoho | Do Now: Find the Fact You Have to Update Twice
Take any spreadsheet you have used and find one fact stored in more than one place.
Two minutes: what happens when that fact changes and someone updates only one copy? That is precisely the problem normalisation exists to solve, and most people have already been bitten by it without having a name for it.
📖 Activity 1: Draw the ERD, Then Normalise It
Model it (13 min). Lay out entities, attributes and relationships. Mark primary keys first, then foreign keys, then cardinalities. Every many-to-many relationship gets its join table drawn now — leaving one implicit is where schemas quietly go wrong.
Walk it to 3NF (12 min). Take your schema through 1NF, 2NF and 3NF, writing down what changed at each step. Then justify any deliberate exception: denormalising for read performance is a legitimate engineering decision, but only when you can state what it costs you.
📝 Activity 2: Level 3 Digital Technology Portfolio Task & Merit/Excellence Scaffolding (20 mins)
Portfolio Section 2. Submit: (1) your ERD with all keys and cardinalities marked; (2) a SQL schema with foreign key constraints and deliberate data types — not text for every column; (3) your 1NF to 3NF walkthrough naming what changed at each stage, plus any denormalisation with its cost stated.
🏫 Kaiako Planning & Pedagogy Notes
NCEA Level 3 Alignment: Direct preparation for Level 3 Digital Technology (Develop a complex digital outcome to solve a specified problem). Emphasise systematic testing, stakeholder feedback loops, and technical code refinement for Merit/Excellence grades.