Computer Science · 10th Grade · Advanced Data Structures and Management · Weeks 10-18

Relational Database Design

Students learn the principles of relational database design, including entities, attributes, and relationships.

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

About This Topic

Relational database design gives students the skills to organize information so it can be stored, retrieved, and maintained reliably. At the core of this topic are three concepts: entities (the things being tracked, like students or products), attributes (the properties of those things, like name or price), and relationships (how entities connect, like which students are enrolled in which courses). Students learn to represent these as tables linked by primary and foreign keys, creating schemas that accurately reflect the real world. This aligns with CSTA standard 3A-DA-09.

A well-designed schema is both a technical and an analytical achievement. Students must think critically about what data belongs together, where duplication would cause problems, and what access patterns the design must support. These are the same decisions professional database administrators and backend engineers make regularly.

Schema design lends itself to active learning because it is inherently a design process with multiple valid approaches. Presenting students with the same real-world scenario and comparing their schemas reveals how design choices reflect different priorities and assumptions.

Key Questions

1. Design a relational database schema for a given real-world scenario.
2. Explain the importance of primary and foreign keys in database integrity.
3. Analyze how different table relationships (one-to-one, one-to-many) are modeled.

Learning Objectives

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

Before You Start

Introduction to Data Organization

Why: Students need a basic understanding of how to group and categorize information before learning to structure it in tables.

Basic Data Types and Structures

Why: Familiarity with different types of data (text, numbers, dates) and simple structures like lists is helpful for defining attributes.

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.

Watch Out for These Misconceptions

Common Misconception

A database is just a spreadsheet with more rows.

What to Teach Instead

Spreadsheets store flat data in a single table without enforced relationships. Relational databases distribute data across multiple linked tables with enforced integrity rules. Students who build multi-table schemas and trace how a foreign key join retrieves related data quickly see the structural difference.

Common Misconception

Any column that looks unique can be a primary key.

What to Teach Instead

A good primary key must be unique, non-null, and stable over time. Email addresses, for example, look unique but people change them -- making them poor primary keys. Design critiques where students examine real failure scenarios (two users sharing an email, a key value changing) build intuition about key selection.

Active Learning Ideas

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.

50 min·Small Groups

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.

25 min·Pairs

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.

35 min·Small Groups

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.

40 min·Pairs

Real-World Connections

• E-commerce platforms like Amazon use relational databases to manage vast inventories of products, customer orders, and user accounts. Database designers must carefully structure these tables to efficiently handle millions of transactions daily.
• Libraries worldwide employ relational databases to track books, patrons, and borrowing history. The design ensures that each book is uniquely identified and that borrowing records accurately link patrons to the items they have checked out.
• Social media sites such as Facebook or Instagram rely on complex relational databases to store user profiles, posts, connections between users, and media content. Efficient schema design is critical for fast data retrieval and scalability.

Assessment Ideas

Quick Check

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.

Discussion Prompt

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?

Peer Assessment

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.

Frequently Asked Questions

What is a primary key and why does every table need one?

A primary key is a column or set of columns that uniquely identifies each row in a table. Without one, the database cannot distinguish between two otherwise identical records, and other tables cannot reliably reference rows in this table. Most databases enforce the primary key constraint automatically to maintain data integrity.

What is a foreign key in a relational database?

A foreign key is a column in one table that references the primary key of another table, creating a link between the two. For example, an orders table might contain a customer_id foreign key that references the id column of the customers table. This enforces referential integrity -- you cannot create an order for a customer who does not exist.

What is the difference between a one-to-many and a many-to-many relationship?

In a one-to-many relationship, one record in the first table relates to multiple records in the second (one customer, many orders). In many-to-many relationships, multiple records on both sides relate to each other (students and classes). Many-to-many relationships require a junction table containing foreign keys from both sides.

How does active learning improve relational database design skills?

Database design requires applying abstract rules to open-ended scenarios, which is difficult without feedback. When students critique each other's schemas in gallery walks or debate key choices in pairs, they encounter errors and edge cases they would not have generated alone. This peer review mimics the code review process used in professional database development.