Computer Science · 10th Grade

Active learning ideas

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.

• Common Core State Standards
• CSTA: 3A-DA-09

• 25–50 min
• Pairs → Whole Class
• 4 activities

Activity 01

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

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.

What to look for

Provide students with a list of entities and attributes for a simple scenario, like a pet adoption agency. Ask them to identify which items are entities and which are attributes, and to propose a primary key for each entity.

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

Activity 02

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

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.

What to look for

Present students with two proposed database schemas for the same scenario, one with redundant data and one without. Ask them to discuss: Which schema is more efficient and why? What problems could arise from the redundant schema? How do primary and foreign keys help avoid these problems?

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

Activity 03

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

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.

What to look for

Students individually design a simple relational schema for a school club's membership list. They then exchange their designs with a partner. Each student reviews their partner's design, checking for clear entities, appropriate attributes, and logical relationships, providing written feedback on one area for improvement.

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

Activity 04

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

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.

What to look for

Provide students with a list of entities and attributes for a simple scenario, like a pet adoption agency. Ask them to identify which items are entities and which are attributes, and to propose a primary key for each entity.

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

A few notes on teaching this unit

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.

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

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

  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.

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

  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.

---

Methods used in this brief

• Case Study Analysis
• Think-Pair-Share
• Gallery Walk