Computer Science · 11th Grade · Data Structures and Management · Weeks 1-9

Introduction to Relational Databases

Designing schemas and querying data using structured language to find meaningful patterns.

• Common Core State Standards
• CSTA: 3B-DA-05

About This Topic

Relational databases are the backbone of most business software, web applications, and data systems students will encounter in professional life. This topic addresses CSTA standard 3B-DA-05 and introduces 11th-grade students to the core model: data organized into tables with rows and columns, where relationships between tables are expressed through shared key values. The relational model, developed in the 1970s, remains dominant because it provides a principled, flexible way to organize structured data while minimizing duplication.

In the US K-12 context, students often have intuitive experience with spreadsheets but have not thought carefully about the limitations of flat data storage. A spreadsheet works for simple data, but relational databases become necessary when data has complex relationships, needs to be queried in flexible ways, or must remain consistent across updates. Connecting the relational model to familiar problems like managing school enrollment data or a music library grounds the abstract concepts.

Active learning is valuable here because schema design requires genuine decision-making. When students defend their table structure choices to peers or discover inconsistencies in each other's designs, they develop deeper understanding of why normalization and key relationships matter.

Key Questions

1. Explain the core concepts of a relational database, including tables, rows, and columns.
2. Analyze the benefits of organizing data into a relational model.
3. Design a simple database schema for a given real-world scenario.

Learning Objectives

• Explain the fundamental components of a relational database, including tables, rows, columns, and keys.
• Compare the advantages of the relational data model over flat-file storage for complex datasets.
• Design a normalized database schema for a given real-world scenario, justifying table structures and relationships.
• Write basic SQL queries to retrieve specific data from a relational database.
• Evaluate the effectiveness of a given database schema in meeting specific data management requirements.

Before You Start

Spreadsheet Fundamentals

Why: Familiarity with rows, columns, and basic data organization in spreadsheets provides a foundation for understanding tables and data structures.

Data Types

Why: Understanding different types of data (text, numbers, dates) is essential for defining column properties in a database schema.

Key Vocabulary

Table	A collection of related data entries organized in rows and columns, representing a specific entity or concept.
Row (Record)	A single entry within a table, containing data for all columns for one specific instance of the entity.
Column (Field)	A single attribute or characteristic of the entity represented by the table, with a specific data type.
Primary Key	A column or set of columns that uniquely identifies each row in a table, ensuring no two rows are identical.
Foreign Key	A column in one table that refers to the primary key in another table, establishing a link or relationship between them.

Watch Out for These Misconceptions

Common Misconception

A database is just a spreadsheet.

What to Teach Instead

Spreadsheets store flat, denormalized data in a single grid. Relational databases store structured data across multiple linked tables, enforce data types and constraints, support complex multi-table queries, and handle concurrent access safely. The difference becomes clear when students try to model data with multiple relationships using a spreadsheet.

Common Misconception

Every table needs an auto-generated ID column as its primary key.

What to Teach Instead

While auto-generated integer IDs are common, any column or combination of columns that uniquely identifies a row can serve as a primary key. A composite key like (student_id, course_id) is a perfectly valid primary key for an enrollment table. Overreliance on surrogate keys can obscure the natural structure of the data.

Common Misconception

More tables always means better design.

What to Teach Instead

Splitting data into too many tables creates unnecessary complexity and can make queries harder to write and maintain. Good schema design balances normalization against query simplicity. Students benefit from evaluating schemas on both dimensions rather than treating more tables as inherently better.

Active Learning Ideas

Design Challenge: School Database Schema

Groups receive a description of a school's data needs (students, teachers, classes, grades, rooms) and must design a database schema on paper, choosing tables, columns, and primary/foreign keys. Groups present to each other and critique: Where does a design cause redundancy? What breaks when a teacher changes classrooms?

35 min·Small Groups

Think-Pair-Share: Flat vs. Relational

Show students a spreadsheet with repeated data (student name duplicated in every grade record). Students individually identify problems with this design, compare observations with a partner, and the class catalogs all the issues found. This motivates the relational model as a solution rather than an abstract requirement.

20 min·Pairs

Card Sort: Table Components

Groups receive a set of cards labeled with terms (primary key, foreign key, row, column, table, relationship, NULL, constraint) and a partially completed relational schema. Groups must correctly place cards on the schema diagram and explain each placement. Comparing across groups surfaces confusion about key relationships.

25 min·Small Groups

Case Study Analysis: Database Design Failures

Groups analyze a simplified scenario where poor database design caused a real problem (such as a hospital's records becoming inconsistent after a department restructuring). Each group identifies the design flaw and proposes a relational schema fix, then compares solutions with another group to evaluate the alternatives.

30 min·Small Groups

Real-World Connections

• E-commerce platforms like Amazon use relational databases to manage product catalogs, customer orders, and shipping information, allowing for complex queries to recommend products or track inventory.
• Libraries utilize relational databases to store information about books, patrons, and borrowing history, enabling efficient searches for available titles and management of due dates.
• Social media sites like Facebook or Instagram employ relational databases to store user profiles, posts, and connections, facilitating features like friend suggestions and news feed generation.

Assessment Ideas

Quick Check

Present students with a small dataset (e.g., a list of students and their favorite colors). Ask them to identify how this data could be organized into at least two tables, specifying the columns for each and identifying a potential primary key for each table.

Discussion Prompt

Pose the scenario: 'Imagine you are designing a database for a small music festival. What kinds of information would you need to store (e.g., bands, attendees, stages, schedules)? How would you organize this into tables, and what relationships would exist between them?' Facilitate a class discussion where students share and critique their proposed schemas.

Exit Ticket

Provide students with a simple database schema diagram. Ask them to write one SQL query to retrieve all records from one table, and another query to join two tables based on their foreign key relationship to find specific related information.

Frequently Asked Questions

What is a relational database?

A relational database organizes data into tables made of rows and columns. Tables relate to each other through shared key values, allowing complex data to be stored without duplication and retrieved through flexible queries. The relational model was developed by Edgar Codd in the 1970s and remains the dominant approach for storing structured data in most applications.

What is the difference between a primary key and a foreign key?

A primary key uniquely identifies each row within a table. A foreign key in one table references the primary key in another table, creating a link between them. For example, an orders table might have a customer_id foreign key that references the id column in a customers table, connecting each order to the customer who placed it.

Why use a relational database instead of a spreadsheet?

Spreadsheets work for simple, flat data but struggle when data has multiple relationships, when multiple users need simultaneous access, or when data consistency across updates is critical. Relational databases enforce constraints, handle concurrent users safely, support complex queries across multiple datasets, and scale to millions of records without the manual maintenance a spreadsheet would require.

How does active learning help students learn relational database concepts?

Schema design activities that require students to make and defend real structural decisions are far more effective than studying pre-built examples. When student groups critique each other's schemas and find concrete problems, they internalize the principles behind normalization rather than memorizing rules in the abstract. The social pressure of defending a design also sharpens analytical thinking.