Database Design: ER Diagrams

Active learning works especially well for ER diagrams because students must wrestle with abstraction by turning messy real-world situations into clear, logical structures. When learners sketch, critique, and revise diagrams together, they immediately see how design choices affect efficiency and integrity.

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

Learning Objectives

1. Design an ER diagram for a school management system, accurately representing entities, attributes, and relationships.
2. Analyze the impact of different relationship types (one-to-one, one-to-many, many-to-many) on database structure and query efficiency.
3. Justify the selection of specific attributes for entities based on data requirements and normalization principles.
4. Critique existing ER diagrams for clarity, completeness, and adherence to best practices in database design.

Collaborative Problem-Solving

⏱ 35 min·Pairs

Pairs: Library System ER Design

Pairs identify entities (Books, Borrowers, Loans), list attributes, and draw relationships with cardinality notation. They justify choices using a scenario sheet. Pairs swap diagrams for 5-minute peer feedback before finalizing.

Prepare & details

Design an ER diagram for a school management system.

Facilitation Tip: During the Library System ER Design, give each pair a printed scenario and colored markers so they visibly separate entities, attributes, and relationships on paper.

Setup: Groups at tables with problem materials

Materials: Problem packet, Role cards (facilitator, recorder, timekeeper, reporter), Problem-solving protocol sheet, Solution evaluation rubric

• Apply
• Analyze
• Evaluate
• Create
• Relationship Skills
• Decision-Making
• Self-Management

Collaborative Problem-Solving

⏱ 45 min·Small Groups

Small Groups: Relationship Classification Challenges

Groups receive 10 real-world scenarios and classify each into one-to-one, one-to-many, or many-to-many. They sketch sample ER snippets for three scenarios. Groups share one example with the class for discussion.

Prepare & details

Analyze how different relationship types (one-to-one, one-to-many) impact database design.

Facilitation Tip: In Relationship Classification Challenges, ask groups to categorize each relationship type by placing sticky notes on a classroom wall labeled One-to-One, One-to-Many, Many-to-Many.

Setup: Groups at tables with problem materials

Materials: Problem packet, Role cards (facilitator, recorder, timekeeper, reporter), Problem-solving protocol sheet, Solution evaluation rubric

• Apply
• Analyze
• Evaluate
• Create
• Relationship Skills
• Decision-Making
• Self-Management

Collaborative Problem-Solving

⏱ 50 min·Whole Class

Whole Class: School Management Build

Project a blank canvas; class nominates entities and attributes for a school system. Vote on relationships via hand signals, then draw iteratively on shared digital tool. Adjust based on class input.

Prepare & details

Justify the inclusion of specific attributes for an entity.

Facilitation Tip: For the School Management Build, circulate with a checklist of normalization rules so students can self-assess if their design meets third normal form before whole-class discussion.

Setup: Groups at tables with problem materials

Materials: Problem packet, Role cards (facilitator, recorder, timekeeper, reporter), Problem-solving protocol sheet, Solution evaluation rubric

• Apply
• Analyze
• Evaluate
• Create
• Relationship Skills
• Decision-Making
• Self-Management

Collaborative Problem-Solving

⏱ 20 min·Individual

Individual: Attribute Justification Task

Students receive a partial ER diagram and add attributes to entities, writing one sentence per attribute explaining its purpose and data type. Submit digitally for quick review.

Prepare & details

Design an ER diagram for a school management system.

Setup: Groups at tables with problem materials

Materials: Problem packet, Role cards (facilitator, recorder, timekeeper, reporter), Problem-solving protocol sheet, Solution evaluation rubric

• Apply
• Analyze
• Evaluate
• Create
• Relationship Skills
• Decision-Making
• Self-Management

Teaching This Topic

Teach ER diagrams by alternating between quick modeling tasks and reflective critiques. Start with a simple scenario, model your own thinking aloud, then have students iterate. Research shows that frequent, low-stakes sketching followed by peer review builds both conceptual clarity and technical precision faster than lectures alone.

What to Expect

Successful learning looks like students confidently translating scenarios into ER diagrams with correctly labeled entities, attributes, primary keys, and cardinal relationships. They justify choices aloud and adjust diagrams after peer feedback.

Watch Out for These Misconceptions

Common Misconception

During Relationship Classification Challenges, watch for students defaulting to one-to-many relationships in every scenario.

What to Teach Instead

Have pairs present their classification choices to the class and justify why a one-to-one or many-to-many fits better; circulate with example cards of Person–Passport (one-to-one) and Student–Course (many-to-many) to prompt discussion.

Common Misconception

During Library System ER Design, students may treat entities as vague containers without identifying unique keys.

What to Teach Instead

Before they sketch, ask each pair to list three attributes for each entity and circle the one they would choose as the primary key, then share with another pair for feedback.

Common Misconception

During School Management Build, students may view ER diagrams as decorative rather than logical blueprints.

What to Teach Instead

After the whole-class build, display two student diagrams side by side and facilitate a critique session where the class identifies which design supports faster queries for common tasks like finding a teacher’s classes or a student’s timetable.

Assessment Ideas

Quick Check

After the Library System ER Design, collect pairs’ diagrams and check that entities include primary keys, relationships show correct cardinality, and attributes are clearly defined.

Discussion Prompt

During Relationship Classification Challenges, present two student-generated diagrams for the same scenario and ask the class to compare efficiency and query speed based on relationship choices.

Peer Assessment

After the School Management Build, have students swap diagrams and use a feedback sheet to evaluate essential entities, attribute clarity, and logical cardinality before returning designs for revisions.

Extensions & Scaffolding

• Challenge students who finish early to add a superclass/subclass hierarchy to their diagram and explain how inheritance reduces redundancy.
• Scaffolding for struggling learners: provide pre-labeled entity cards they can arrange before drawing, focusing first on relationships rather than attributes.
• Deeper exploration: ask students to convert their ER diagram into SQL CREATE TABLE statements and test queries on a small dataset.

Key Vocabulary

Entity	A real-world object or concept that can be uniquely identified, such as a 'Student' or a 'Course'.
Attribute	A property or characteristic of an entity, like 'StudentID' or 'StudentName' for the 'Student' entity.
Relationship	An association between two or more entities, indicating how they are connected, such as a 'Student' enrolling in a 'Course'.
Cardinality	Specifies the number of instances of one entity that can be related to instances of another entity (e.g., one-to-one, one-to-many).