Computer Science · Grade 11 · Data Structures and Management · Term 3

Relational Databases: Introduction to SQL

Introduction to structured data storage, entity-relationship diagrams, and basic query logic using SQL.

• Ontario Curriculum Expectations
• CS.HS.D.5
• CS.HS.S.1

About This Topic

Relational databases organize data into tables linked by keys to maintain consistency and reduce redundancy. Students explore entity-relationship diagrams to model real-world connections, such as students, courses, and teachers in a school system. They learn basic SQL commands like SELECT, WHERE, JOIN to query data across tables, directly addressing Ontario curriculum standards on data structures and management.

This topic connects to ethical considerations in centralized data storage, including privacy risks and the need for secure schemas. Primary and foreign keys enforce referential integrity, while normalization principles guide schema design to eliminate duplicates. These skills prepare students for advanced topics in data management and real-world applications like business analytics.

Active learning shines here because students can use free tools like SQLite to build and query their own databases. Collaborative schema design and error-troubleshooting sessions turn abstract concepts into practical experiences, fostering problem-solving and deeper retention.

Key Questions

1. How does a relational database ensure data consistency across multiple tables?
2. What are the ethical concerns regarding the centralized storage of personal data?
3. How do we design a database schema that minimizes redundancy?

Learning Objectives

• Design an entity-relationship diagram (ERD) to model a given real-world scenario, identifying entities, attributes, and relationships.
• Write basic SQL queries using SELECT, FROM, and WHERE clauses to retrieve specific data from a single table.
• Compare and contrast the use of primary and foreign keys in maintaining referential integrity across related tables.
• Analyze a given database schema for redundancy and propose normalization strategies to minimize it.
• Evaluate the ethical implications of storing personal data in a centralized relational database, considering privacy and security.

Before You Start

Introduction to Data Types and Variables

Why: Students need to understand basic data types (text, numbers, dates) to comprehend how data is represented in database columns.

Basic Programming Concepts (e.g., loops, conditionals)

Why: Familiarity with logical structures helps students understand query logic and data manipulation within databases.

Spreadsheet Software Basics

Why: Experience with organizing data in rows and columns in spreadsheets provides a foundational understanding of tabular data structures.

Key Vocabulary

Entity	A real-world object or concept about which data is stored, represented as a table in a relational database.
Attribute	A property or characteristic of an entity, represented as a column in a database table.
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 between the two tables.
SQL	Structured Query Language, a standard programming language used to manage and manipulate relational databases.
Referential Integrity	A database concept that ensures relationships between tables remain consistent, preventing actions that would delete or change related data.

Watch Out for These Misconceptions

Common Misconception

Relational databases work like spreadsheets with no links between sheets.

What to Teach Instead

Tables connect via primary and foreign keys for data consistency. Hands-on schema building in pairs helps students visualize relationships and spot inconsistencies during group reviews.

Common Misconception

SQL queries always return all data from a table.

What to Teach Instead

WHERE and JOIN clauses filter specific data. Relay activities let students practice iteratively, correcting overbroad queries through peer testing and live results.

Common Misconception

Redundancy is completely eliminated in any database.

What to Teach Instead

Normalization minimizes it, but some controlled duplication aids performance. Class challenges reveal trade-offs, as students debate and test normalized vs. denormalized schemas collaboratively.

Active Learning Ideas

Pairs: ER Diagram Sketching

Pairs draw entity-relationship diagrams for a library system with books, authors, and borrowers. Identify entities, attributes, and relationships, then add primary keys. Share and refine with class feedback.

30 min·Pairs

Small Groups: SQL Query Relay

Groups receive a multi-table database on school events. Each member writes one SQL query (SELECT with JOIN, WHERE clause) to answer a prompt, then passes to the next. Test queries live and discuss results.

45 min·Small Groups

Whole Class: Schema Normalization Challenge

Project a sample unnormalized dataset on screen. Class votes on normalization steps to 3NF, then volunteers update a shared digital schema. Compare before-and-after redundancy.

35 min·Whole Class

Individual: Personal Data Query

Students create a simple SQLite database of their hobbies and friends. Write 3-5 SQL queries to filter and join data. Submit queries with screenshots of results.

25 min·Individual

Real-World Connections

• Online retailers like Amazon use relational databases to manage product catalogs, customer orders, and inventory. Database administrators design schemas to efficiently track millions of items and customer interactions, ensuring order accuracy and stock availability.
• Hospitals employ relational databases to store patient records, appointment schedules, and billing information. Database designers must carefully structure these systems to protect sensitive health information while allowing authorized access for medical staff, adhering to privacy regulations like PIPEDA.
• Libraries utilize relational databases to manage book collections, patron information, and borrowing history. Librarians can query the database to find available books, track overdue items, and generate reports on popular genres.

Assessment Ideas

Quick Check

Present students with a simple scenario, such as a small bookstore with books and authors. Ask them to identify the entities, list key attributes for each, and draw a basic ERD showing the relationship between books and authors. Review their diagrams for correct identification of entities and relationships.

Exit Ticket

Provide students with a table of student data (e.g., student ID, name, course ID, grade). Ask them to write a SQL query to select the names of all students who achieved a grade above 80%. Collect their queries to check for correct SELECT, FROM, and WHERE clause syntax.

Discussion Prompt

Facilitate a class discussion using the prompt: 'Imagine a social media platform stores all user data in one large database. What are the potential privacy risks? How could a well-designed database schema, with proper access controls and encryption, help mitigate these risks?' Guide students to consider data breaches, unauthorized access, and the importance of data minimization.

Frequently Asked Questions

How do relational databases ensure data consistency?

Primary keys uniquely identify records, while foreign keys link tables and enforce referential integrity. This prevents orphaned data or inconsistencies during updates. Students grasp this best by simulating inserts and deletes in shared databases, observing errors firsthand.

What are entity-relationship diagrams used for?

ER diagrams visually map entities, attributes, and relationships to design schemas that minimize redundancy. They guide normalization and SQL implementation. Sketching activities help students iterate designs collaboratively before coding.

How can active learning help teach relational databases?

Tools like SQLite enable students to build schemas, run queries, and debug errors in real time. Group relays and pair diagramming promote discussion of failures, turning abstract logic into tangible skills. This boosts engagement and retention over lectures alone.

What ethical issues arise with relational databases?

Centralized storage raises privacy concerns, like data breaches exposing personal info. Design for access controls and encryption from the start. Class debates on real cases, tied to schema choices, connect tech to societal impacts effectively.