Relational Database Design

Active learning works for relational database design because students need to physically build, critique, and revise schemas to grasp how entities, attributes, and relationships form a coherent structure. When students manipulate tables, keys, and joins instead of just listening, they develop an intuitive sense of data integrity and normalization that lectures alone cannot provide.

• 10th Grade
• Computer Science
• 4 activities
• 25 min–50 min

Learning Objectives

1. Design a relational database schema for a library catalog system, identifying entities, attributes, and relationships.
2. Explain the function of primary and foreign keys in ensuring data integrity and preventing anomalies within a database.
3. Compare and contrast one-to-one and one-to-many relationships, demonstrating how each is modeled in a relational schema.
4. Analyze a given real-world scenario, such as an online retail inventory, and identify the necessary tables and their connections for a database.

Case Study Analysis

⏱ 50 min·Small Groups

Collaborative Design: School Database Schema

Small groups receive a written description of a school's data needs (students, teachers, classes, grades, rooms) and must design an entity-relationship diagram on a shared whiteboard. Each group presents their schema, and the class identifies which designs would break if a student changed their name or transferred courses.

Prepare & details

Design a relational database schema for a given real-world scenario.

Facilitation Tip: During Collaborative Design, circulate and ask groups to explain how their foreign keys maintain data consistency between tables.

Setup: Groups at tables with case materials

Materials: Case study packet (3-5 pages), Analysis framework worksheet, Presentation template

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

Think-Pair-Share

⏱ 25 min·Pairs

Think-Pair-Share: Primary Key Decisions

Students individually decide which column should serve as the primary key for five different tables (orders, patients, books, flights, employees) and explain why. They compare choices with a partner, then the class discusses cases where natural keys (like ISBN) are reliable versus surrogate keys (auto-incremented IDs) are safer.

Prepare & details

Explain the importance of primary and foreign keys in database integrity.

Facilitation Tip: In Think-Pair-Share, listen for students to justify their primary key choices with stability and uniqueness rather than just uniqueness.

Setup: Standard classroom seating; students turn to a neighbor

Materials: Discussion prompt (projected or printed), Optional: recording sheet for pairs

• Understand
• Apply
• Analyze
• Self-Awareness
• Relationship Skills

Gallery Walk

⏱ 35 min·Small Groups

Gallery Walk: Spot the Design Flaw

Post five partially-designed schemas around the room, each with a deliberate flaw (missing primary key, wrong relationship cardinality, attributes stored in the wrong table). Student groups rotate and annotate each schema with sticky notes identifying the problem and a proposed fix.

Prepare & details

Analyze how different table relationships (one-to-one, one-to-many) are modeled.

Facilitation Tip: For Gallery Walk, require students to write one specific improvement for each flawed schema they examine.

Setup: Wall space or tables arranged around room perimeter

Materials: Large paper/poster boards, Markers, Sticky notes for feedback

• Understand
• Apply
• Analyze
• Create
• Relationship Skills
• Social Awareness

Case Study Analysis

⏱ 40 min·Pairs

Design Challenge: Social Media Data Model

Pairs design a minimal relational schema for a fictional social media app supporting users, posts, and likes. They must define all tables, primary keys, foreign keys, and at least one many-to-many relationship, then swap schemas with another pair for a structured critique.

Prepare & details

Design a relational database schema for a given real-world scenario.

Facilitation Tip: During Design Challenge, challenge students to explain how their model would handle edge cases like deleted users or merged accounts.

Setup: Groups at tables with case materials

Materials: Case study packet (3-5 pages), Analysis framework worksheet, Presentation template

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

Teaching This Topic

Teachers should model the design process by thinking aloud while creating a schema, making visible the decisions behind entity selection, attribute assignment, and key assignment. Avoid starting with abstract normalization rules; instead, let students discover anomalies through real scenarios so the rules emerge naturally from their work. Research suggests that students grasp relationships better when they trace data flows across tables, so emphasize join operations and their real-world consequences.

What to Expect

Successful learning looks like students producing accurate, normalized schemas with clear entities, attributes, and relationships, and being able to explain why their design choices avoid redundancy and ensure data integrity. They should confidently discuss how primary and foreign keys connect tables and prevent anomalies.

Watch Out for These Misconceptions

Common Misconception

During Collaborative Design, watch for students who treat the database like a single spreadsheet by combining all data into one large table.

What to Teach Instead

In Collaborative Design, have groups present their first draft and ask the class to identify which entities and attributes would cause redundancy or update anomalies. Then require them to split the table and design proper relationships before proceeding.

Common Misconception

During Think-Pair-Share, students may assume any column with numbers or unique text can serve as a primary key.

What to Teach Instead

In Think-Pair-Share, provide real examples like email addresses or phone numbers and ask students to role-play scenarios where these values change or are shared, demonstrating why stable keys like auto-increment IDs are safer.

Assessment Ideas

Quick Check

After Collaborative Design, collect each group’s final schema and ask them to identify one entity, its primary key, and one foreign key relationship they created.

Discussion Prompt

During Gallery Walk, ask students to discuss which design flaw they found most convincing and how they would fix it, focusing on redundant data or improper relationships.

Peer Assessment

After Design Challenge, have students exchange schemas and complete a feedback form that asks them to check for clear entities, appropriate keys, and logical relationships, then provide one written suggestion for improvement.

Extensions & Scaffolding

• Challenge early finishers to design a schema that supports both a school’s course catalog and student enrollment history, including historical records of dropped classes.
• For students who struggle, provide a partially completed schema with missing attributes or relationships and ask them to fill in two more tables and their connecting keys.
• Deeper exploration: Have students research how database triggers or stored procedures could enforce business rules in their social media model, then add one example to their schema.

Key Vocabulary

Entity	A real-world object or concept about which data is stored, such as a customer, product, or order. In a relational database, an entity typically becomes a table.
Attribute	A property or characteristic of an entity, such as a customer's name, a product's price, or an order's date. Attributes become columns in a database table.
Relationship	An association between two or more entities, indicating how they are connected. Examples include a customer placing an order or a student enrolling in a course.
Primary Key	A column or set of columns in a table that uniquely identifies each row. It ensures that each record is distinct and can be precisely referenced.
Foreign Key	A column or set of columns in one table that refers to the primary key in another table. It establishes and enforces a link between the two tables.