Relational Databases and SQL

Active learning builds relational schema thinking by letting students experience the friction of real data problems, where flat structures collapse under updates and queries break without proper keys. Hands-on activities make abstract concepts like normalization and JOINs visible through tangible outcomes, so students see why constraints exist rather than memorize them.

• Year 10
• Technologies
• 4 activities
• 30 min–50 min

Learning Objectives

1. Design a relational database schema for a complex scenario, such as a global social media network, including appropriate primary and foreign keys.
2. Analyze data redundancy and inconsistency issues within a given database design and propose normalization techniques (up to 3NF) to resolve them.
3. Write and execute SQL queries to perform CRUD operations (Create, Read, Update, Delete) on a relational database.
4. Evaluate the ethical implications of linking disparate datasets, considering privacy and potential misuse.
5. Compare and contrast different normalization forms (1NF, 2NF, 3NF) in terms of their impact on data integrity and query performance.

Concept Mapping

⏱ 45 min·Pairs

Pair Programming: Schema Design Challenge

Pairs sketch an ER diagram for a social media network with users, posts, and likes tables. They identify primary keys, foreign keys, and normalize to 3NF, then create the database in SQLite. Partners alternate explaining choices to each other.

Prepare & details

How does data normalization prevent redundancy and inconsistency?

Facilitation Tip: During the Pair Programming: Schema Design Challenge, circulate and ask teams to explain how their foreign keys prevent orphaned records when one table is deleted.

Setup: Tables with large paper, or wall space

Materials: Concept cards or sticky notes, Large paper, Markers, Example concept map

• Understand
• Analyze
• Create
• Self-Awareness
• Self-Management

Concept Mapping

⏱ 50 min·Small Groups

Small Groups: SQL Query Relay

Divide a sample customer orders database among groups. Each group writes and tests one SQL query type: SELECT with JOIN, INSERT, UPDATE, DELETE. Groups share queries class-wide for peer testing and discussion.

Prepare & details

What are the ethical implications of linking disparate datasets together?

Facilitation Tip: For the SQL Query Relay, seed the first incorrect query in each relay so students practice debugging syntax errors under time pressure.

Setup: Tables with large paper, or wall space

Materials: Concept cards or sticky notes, Large paper, Markers, Example concept map

• Understand
• Analyze
• Create
• Self-Awareness
• Self-Management

Concept Mapping

⏱ 30 min·Whole Class

Whole Class: Ethical Dataset Linking Debate

Present two datasets: health records and shopping history. Class votes on linking them, then queries the combined schema in SQL to reveal insights. Discuss ethics via think-pair-share.

Prepare & details

How would you design a schema to represent a global social media network?

Facilitation Tip: In the Ethical Dataset Linking Debate, provide three concrete design choices per scenario so students ground ethical arguments in technical constraints they have experienced.

Setup: Tables with large paper, or wall space

Materials: Concept cards or sticky notes, Large paper, Markers, Example concept map

• Understand
• Analyze
• Create
• Self-Awareness
• Self-Management

Concept Mapping

⏱ 35 min·Individual

Individual: Normalization Puzzle

Provide unnormalized data on school clubs. Students identify redundancies, create normalized tables, and write SQL to populate them. Submit queries for class database demo.

Prepare & details

How does data normalization prevent redundancy and inconsistency?

Setup: Tables with large paper, or wall space

Materials: Concept cards or sticky notes, Large paper, Markers, Example concept map

• Understand
• Analyze
• Create
• Self-Awareness
• Self-Management

Teaching This Topic

Start with concrete, messy data sets so students feel the pain of redundancy before learning normalization rules. Avoid introducing second normal form until students have manually cleaned unnormalized tables and timed how long updates take. Research shows students grasp joins better when they design the tables first, so teach schema design before query writing to build intuitive understanding.

What to Expect

Successful learning looks like students designing schemas that prevent redundancy, writing SQL that correctly retrieves related records, and debating trade-offs between normalization and query speed with evidence from their own prototypes. Expect students to articulate why foreign keys matter and how normalization reduces update anomalies.

Watch Out for These Misconceptions

Common Misconception

During the Pair Programming: Schema Design Challenge, watch for students treating tables like spreadsheets without primary keys.

What to Teach Instead

Ask teams to explain how they chose primary keys and what happens when two users share the same username if no key prevents duplication.

Common Misconception

During the Small Groups: SQL Query Relay, watch for students assuming LIKE clauses work the same as search engine keywords.

What to Teach Instead

Provide a broken query using wildcards and ask groups to fix it by testing with sample data before moving to the next station.

Common Misconception

During the Whole Class: Ethical Dataset Linking Debate, watch for students dismissing privacy concerns because data is anonymous.

Assessment Ideas

Quick Check

After the Pair Programming: Schema Design Challenge, present students with a single unnormalized table containing band gigs, members, and instruments. Ask them to identify two redundancies and explain the update anomaly that would occur if a member’s instrument changed.

Exit Ticket

After the Small Groups: SQL Query Relay, have each student submit one working query from their station and one error they overcame, explaining the fix in one sentence.

Discussion Prompt

During the Whole Class: Ethical Dataset Linking Debate, ask students to vote with thumbs up or down on whether a proposed health data linkage is ethical, then defend their stance using technical constraints from their schema designs.

Extensions & Scaffolding

• Challenge students to optimize a working schema by adding a junction table for many-to-many relationships and measuring query time differences.
• Scaffold struggling students with partially completed schemas where they only need to add keys and write basic queries.
• Deeper exploration: Have students design a schema for a library system, then write a single query that joins books, authors, and borrowers to show overdue items with patron contact details.

Key Vocabulary

Primary Key	A column or set of columns that uniquely identifies each row in a table. It ensures that each record is distinct.
Foreign Key	A column or set of columns in one table that refers to the primary key in another table. It establishes a link between tables.
Normalization	The process of organizing data in a database to reduce redundancy and improve data integrity, typically by dividing larger tables into smaller ones.
SQL (Structured Query Language)	A standard programming language used for managing and manipulating relational databases, including querying and updating data.
Data Integrity	The overall accuracy, completeness, and consistency of data. Relational databases use keys and constraints to maintain this.