Computing · Secondary 4

Active learning ideas

Primary and Foreign Keys

Students learn primary and foreign keys best when they build and test real database tables, not just listen to definitions. Active schema creation lets them experience why uniqueness matters and how relationships work, turning abstract rules into memorable insights.

• MOE Syllabus Outcomes
• MOE: Data Management - S4
• MOE: Database Systems - S4

• 20–45 min
• Pairs → Whole Class
• 4 activities

Activity 01

• Case Study Analysis
• 30 min · Pairs

Pair Design: School Database Schema

Pairs sketch tables for students, classes, and enrollments on paper. Identify primary keys for each table, then add foreign keys to link them. Discuss choices and refine based on peer feedback.

Explain the importance of primary keys in uniquely identifying records.

Facilitation Tip

During Pair Design, circulate to ask each pair how they decided which field should be the primary key and how they plan to link tables.

What to look for

Present students with two simple tables, one for 'Authors' (AuthorID, Name) and one for 'Books' (BookID, Title, AuthorID). Ask them to identify the primary key in each table and the foreign key linking them, explaining their reasoning for each choice.

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Activity 02

• Case Study Analysis
• 45 min · Small Groups

Small Groups: SQLite Table Creation

Groups create three tables in SQLite with primary and foreign keys. Insert sample data, test inserts that violate integrity, and query joined results. Share one successful join query with the class.

Analyze how foreign keys establish relationships and maintain data integrity across tables.

Facilitation Tip

When groups create SQLite tables, insist they test their schema with invalid inserts to experience error messages firsthand.

What to look for

On a slip of paper, ask students to write: 1) One reason why a primary key is essential. 2) One scenario where a foreign key would be used to connect two tables. 3) A potential problem if referential integrity is not maintained.

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Activity 03

• Case Study Analysis
• 35 min · Whole Class

Whole Class: Error Hunt Challenge

Project flawed schemas on the board with missing or incorrect keys. Class votes on fixes, then tests in a shared database file. Tally correct identifications to review rules.

Design a database schema that correctly uses primary and foreign keys to link related information.

Facilitation Tip

For the Error Hunt Challenge, provide intentionally flawed schemas so students practice identifying missing keys or broken relationships.

What to look for

Facilitate a class discussion using the prompt: 'Imagine you are designing a database for a school. What tables might you need, and how would you use primary and foreign keys to link information about students, courses, and teachers? Discuss potential data integrity issues if keys are not implemented correctly.'

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

Activity 04

• Case Study Analysis
• 20 min · Individual

Individual: Key Constraint Quiz

Students receive table descriptions and add primary/foreign keys in a worksheet. Validate by simulating data entry scenarios. Peer review follows to confirm designs.

Explain the importance of primary keys in uniquely identifying records.

Facilitation Tip

After Key Constraint Quiz, review responses as a class to clarify any lingering misconceptions before moving on.

What to look for

Present students with two simple tables, one for 'Authors' (AuthorID, Name) and one for 'Books' (BookID, Title, AuthorID). Ask them to identify the primary key in each table and the foreign key linking them, explaining their reasoning for each choice.

• Analyze
• Evaluate
• Create
• Decision-Making
• Self-Management

A few notes on teaching this unit

Teach primary and foreign keys through iterative design rather than lecture. Start with simple examples like student grades, then gradually add complexity to reveal edge cases. Avoid front-loading too much theory, as students grasp these concepts through trial, error, and correction. Research shows hands-on schema building strengthens retention compared to passive note-taking.

By the end of these activities, students will confidently design tables with correct primary and foreign keys, explain referential integrity, and debug common schema errors without prompting. They will also articulate why keys are essential for data consistency.

---

Watch Out for These Misconceptions

• During Pair Design, watch for students who plan to use a name or email as a primary key without considering duplicates.

  Ask pairs to insert two records with the same name or email and observe the error. Then guide them to revise their schema to use an auto-incrementing ID instead.

• During Small Groups: SQLite Table Creation, watch for students who assume foreign keys must be unique within their table.

  Have groups insert multiple records with the same foreign key value and watch how the database handles it. Ask them to explain why uniqueness isn’t required for foreign keys in this context.

• During Whole Class: Error Hunt Challenge, watch for students who treat any unique field as a suitable primary key without considering stability.

  Present a schema where a field like 'student_name' is used as a primary key, then demonstrate how changing a name would break the relationship. Guide students to identify stable alternatives like IDs.

---

Methods used in this brief

• Case Study Analysis