Computing · Secondary 4 · Data Management and Database Systems · Semester 1

Primary and Foreign Keys

Understanding primary keys, foreign keys, and their role in establishing relationships between tables.

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

About This Topic

Primary keys uniquely identify each record in a database table, ensuring no duplicates and enabling fast searches. Every table needs one primary key, often an auto-incrementing ID or a natural unique field like a student number. Foreign keys appear in one table and reference a primary key in another table. They create links between tables, enforce referential integrity, and prevent invalid data like assigning grades to nonexistent students.

In the MOE Secondary 4 Computing curriculum for Data Management and Database Systems, this topic builds skills to explain primary key importance, analyze foreign key roles in relationships, and design schemas that connect data accurately. Students apply these concepts to scenarios like library systems or school records, preparing for SQL queries and normalization in later units. This knowledge fosters logical thinking and problem-solving essential for database design.

Active learning suits this topic well. Students gain deeper understanding when they build schemas collaboratively, insert test data, and debug constraint violations in tools like SQLite. Hands-on trials reveal why unique identifiers matter and how foreign keys maintain consistency, making abstract rules concrete and memorable.

Key Questions

1. Explain the importance of primary keys in uniquely identifying records.
2. Analyze how foreign keys establish relationships and maintain data integrity across tables.
3. Design a database schema that correctly uses primary and foreign keys to link related information.

Learning Objectives

• Design a simple database schema for a school's student information system, correctly identifying and implementing primary and foreign keys.
• Analyze a given database schema and identify potential data integrity issues arising from incorrect primary or foreign key usage.
• Explain the role of a primary key in ensuring unique record identification and the function of a foreign key in establishing referential integrity.
• Compare and contrast the characteristics and purposes of primary keys versus foreign keys in relational database design.

Before You Start

Introduction to Databases and Tables

Why: Students need a foundational understanding of what a database is and how data is organized into tables with rows and columns.

Data Types

Why: Understanding different data types (e.g., integer, text, date) is necessary for choosing appropriate fields for primary and foreign keys.

Key Vocabulary

Primary Key	A column or set of columns that uniquely identifies each row in a database table. It ensures no two rows have the same identifier.
Foreign Key	A column or set of columns in one table that refers to the primary key in another table. It links the two tables and enforces relationships.
Referential Integrity	A database concept that ensures relationships between tables remain consistent. It prevents actions that would destroy links, such as deleting a record that is referenced by a foreign key.
Data Integrity	The overall accuracy, completeness, and consistency of data. Primary and foreign keys are crucial for maintaining data integrity in relational databases.

Watch Out for These Misconceptions

Common Misconception

Primary keys can have duplicate or null values.

What to Teach Instead

Primary keys must be unique and non-null by definition. Active schema-building activities let students try invalid inserts, see error messages, and adjust their designs, reinforcing these rules through direct experience.

Common Misconception

Foreign keys must also be unique across their table.

What to Teach Instead

Foreign keys can repeat to show multiple links to the same record, like many grades for one student. Group testing of relationships helps students insert duplicates successfully and observe how integrity holds without uniqueness.

Common Misconception

Any field can serve as a primary key without planning.

What to Teach Instead

Primary keys require uniqueness and stability. Collaborative debugging sessions expose issues like changing names as keys, guiding students to prefer stable IDs via trial and error.

Active Learning Ideas

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.

30 min·Pairs

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.

45 min·Small Groups

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.

35 min·Whole Class

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.

20 min·Individual

Real-World Connections

• E-commerce platforms like Amazon use primary keys to identify unique products and customers, and foreign keys to link orders to specific customers and products, ensuring accurate order fulfillment and inventory management.
• Library management systems, such as those used by the National Library Board in Singapore, employ primary keys for unique book IDs and patron numbers, and foreign keys to connect book loans to specific patrons, preventing errors in borrowing and returns.

Assessment Ideas

Quick Check

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.

Exit Ticket

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.

Discussion Prompt

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.'

Frequently Asked Questions

What is the role of primary keys in databases?

Primary keys uniquely identify records in a table, preventing duplicates and speeding up queries. In MOE curriculum contexts like school systems, they ensure each student or book has a distinct ID. Students design with them to avoid data errors, a skill tested in schema tasks.

How do foreign keys establish table relationships?

Foreign keys reference primary keys in other tables, linking data like enrollments to students. This maintains integrity by blocking invalid references. Practice in SQLite reveals how joins pull related info, vital for Secondary 4 projects.

How to choose a good primary key for a table?

Select a field that is unique, stable, and simple, like an auto-increment ID over names which change. Avoid composites unless needed. Hands-on schema design teaches evaluation through testing uniqueness in real databases.

How can active learning help teach primary and foreign keys?

Active approaches like pair schema sketching and group SQLite builds make constraints tangible. Students insert data, trigger errors, and fix violations, grasping uniqueness and referential integrity intuitively. Class shares highlight design choices, boosting retention over lectures alone.