Information Storage and Retrieval: Relational Databases and SQL Foundations

Lesson overview

Introduce the relational model, tables, keys, constraints and foundational SQL SELECT queries for structured information retrieval.

CourseInformation Storage and Retrieval

Topic strandRelational data

Assessment styleWorked scenario, applied task and digital exam practice

EvidenceDefinitions, representation, method, result and interpretation

Starter

Write down the user need, the data being stored or searched, and the decision the system has to support. Then predict which representation, index, query method or governance control will matter most in this lesson.

Learning objectives

Explain relation, tuple, attribute, primary key and foreign key.
Write simple SELECT queries with WHERE filters.
Explain how relational constraints support reliable retrieval.

Learning outcomes

Students can explain relation, tuple, attribute, primary key and foreign key.
Students can write simple SELECT queries with WHERE filters.
Students can explain how relational constraints support reliable retrieval.

Key vocabulary, acronyms and terminology

SQL: Structured Query Language: the standard language for querying and managing relational databases.
relation: A table-like set of tuples with named attributes.
tuple: A row in a relation.
primary key: An attribute or set of attributes that uniquely identifies a row.
foreign key: An attribute that references a primary key in another relation.
SELECT: SQL command for retrieving columns and rows.
WHERE: SQL clause for filtering rows.

Detailed teaching notes

Core concept

Relational databases store structured data in relations and use constraints to protect meaning. SQL gives a declarative language for retrieving and changing that data.

Representation choice

A relation such as Book(book_id, title, year) is not merely a spreadsheet. Its primary key, attribute domains and relationships with other tables define what counts as valid data.

Method and reasoning

Start SQL retrieval by identifying the relation, columns and condition. SELECT names output columns; FROM names tables; WHERE expresses row conditions.

Risk and limitation

A query can be syntactically valid yet semantically wrong if keys, relationships or null values are misunderstood.

Degree-level deep dive

The relational model is about constraints

Relations, attributes, tuples, domains and keys define what data is valid. This is why relational storage can support precise retrieval and protect meaning better than an uncontrolled spreadsheet.

SQL states the result, not the procedure

A SELECT query describes the columns, tables and conditions of the desired result. The database engine chooses an execution plan using indexes and statistics, but the query must express the intended relationship correctly.

What excellent work shows

A strong answer names primary and foreign keys, writes a valid SELECT-FROM-WHERE query, and explains how constraints prevent ambiguity. It should not join tables by uncontrolled names when a key relationship exists.

Concrete example to study

Book and publisher tables

Publisher(publisher_id, name)
Book(book_id, title, year, publisher_id)
SQL:
SELECT title
FROM Book
WHERE year >= 2020;

Reasoning

Publisher_id is the primary key in Publisher and a foreign key in Book.
The SELECT clause chooses what appears in the result.
The WHERE clause restricts rows before they are returned.

Reveal takeaway

The relational model supports reliable retrieval because table structure and constraints define valid links.

Worked example

Scenario

A library table Book(book_id, title, year) stores catalogue records.

Worked solution

Identify book_id as primary key.
Choose title and year as output columns.
Filter rows with WHERE year >= 2020.
Write SELECT title, year FROM Book WHERE year >= 2020;

Reveal model result

The SQL query describes the desired result. The database engine decides how to execute it using tables, indexes and constraints.

Applied retrieval task

Create a relational design for module records.

Deliverables

Define two tables with primary keys.
Add one foreign key relationship.
Write a SELECT query with a WHERE condition.

Success checks

Keys are meaningful and unique.
The query matches the schema.

Common misconception

A relational table is not just any grid of data. Its meaning comes from attributes, domains, keys and constraints.

Quick checks

1. In this lesson, why does SQL matter?

It replaces the need to understand user tasks or data representation.Structured Query Language: the standard language for querying and managing relational databases.It is only a presentational label and has no effect on retrieval quality.

2. Which answer best shows degree-level understanding of relational databases and sql foundations?

Name as many technologies as possible without explaining the scenario.Give a final recommendation without showing how the data or query was represented.Define relation, apply it to the scenario, then explain the trade-off or risk.

Digital exam practice

Example exam task

A library stores books and publishers in relational tables. Students must retrieve all book titles published after 2020 by a named publisher. In your answer, define the relevant objects or data structures, use course-specific vocabulary, show the method rather than only the result, and finish with a decision about the storage or retrieval system.

Notation and technical toolkit

Book(id,title,year)relation schema Use to show table structure.

PRIMARY KEY(id)unique row identifier Use to protect entity identity.

SELECT ... FROM ... WHERE ...basic SQL retrieval pattern Use for structured queries.

What a good answer is expected to show

A strong answer for Relational Databases and SQL Foundations the answer should identify relations, primary key and foreign key, then write a simple SELECT with WHERE and explain why constraints make retrieval reliable. It should connect the formal or technical representation to the user's information need instead of listing terms without using them.

How to solve it

State the relevant relation schemas and keys.
Identify the foreign key linking books to publishers.
Write the SELECT, FROM and WHERE parts.
Explain how keys prevent ambiguous joins and duplicate identities.

Model answer

Reveal model answer

A suitable schema is Publisher(publisher_id, name) and Book(book_id, title, year, publisher_id), with Book.publisher_id as a foreign key. The query can retrieve titles with SELECT title FROM Book JOIN Publisher ON Book.publisher_id = Publisher.publisher_id WHERE year > 2020 AND Publisher.name = 'Named Publisher';. The primary and foreign keys make the link between book and publisher explicit, which gives more reliable retrieval than matching publisher names as uncontrolled text.

Practise next

Write a SELECT query to retrieve all modules worth 20 credits.
Explain why a primary key is not just a visible row number.

Self-marking criteria

Defines the scenario objects, data forms or system components before solving.
Uses the lesson vocabulary accurately and in context.
Shows a clear method with enough working for a marker to follow.
Connects the result back to retrieval, storage, analytics or governance.
States a limitation, trade-off or quality risk rather than presenting the answer as absolute.

Extension

Add an index to a column in your example and explain which query it could speed up.

Study route

Save a short worked answer from this lesson using this pattern: define the need or data, choose the representation, show the method, state the result and interpret the implication for the system.

Next lesson: SQL Querying for Information Retrieval.