Relational databases and SQL

A database is an organised collection of data that can be searched, updated and managed efficiently. Relational databases organise data into related tables, and SQL (Structured Query Language) is the standard language for working with them.

Why relational databases?

Before databases, data was stored in flat files — one large table with everything in it. This caused:

• Data redundancy — same data repeated in many rows (e.g. teacher name in every lesson row)
• Update anomalies — changing a teacher's name requires updating many rows
• Inconsistency — some rows may be missed during updates

Relational databases solve this by splitting data into multiple related tables.

Key concepts (CS7.1)

• Table — a collection of rows (records) and columns (fields) about one type of entity
• Record — one row in a table; represents one instance of the entity
• Field — one column; stores one attribute of the entity
• Primary key — a field (or combination) that uniquely identifies each record; cannot be NULL or duplicate
• Foreign key — a field in one table that refers to the primary key of another table; creates a relationship
• Entity-relationship (ER) diagram — shows tables and the relationships between them (one-to-many, many-to-many)

Example schema:

Students(StudentID [PK], Name, TutorGroup)
Subjects(SubjectID [PK], Name)
Enrolments(EnrolmentID [PK], StudentID [FK→Students], SubjectID [FK→Subjects], Grade)

SELECT queries (CS7.2)

SELECT Name, TutorGroup
FROM Students
WHERE TutorGroup = '10A'
ORDER BY Name ASC;

• SELECT — which columns to return
• FROM — which table
• WHERE — filter rows by condition (AND / OR for compound conditions)
• ORDER BY — sort results; ASC (ascending) or DESC (descending)

Join example:

SELECT Students.Name, Subjects.Name
FROM Students, Enrolments, Subjects
WHERE Students.StudentID = Enrolments.StudentID
AND Enrolments.SubjectID = Subjects.SubjectID;

INSERT, UPDATE, DELETE (CS7.3)

-- Add a new student
INSERT INTO Students (StudentID, Name, TutorGroup)
VALUES (1005, 'Priya Sharma', '10B');

-- Update a grade
UPDATE Enrolments
SET Grade = 'A'
WHERE EnrolmentID = 42;

-- Remove a record
DELETE FROM Students
WHERE StudentID = 1005;

Critical: always use WHERE with UPDATE and DELETE — omitting it affects every row.

Referential integrity

The database enforces that foreign keys always point to valid primary keys. A DELETE may be refused (RESTRICT) or cascade to dependent rows (CASCADE).

Why SQL matters

SQL is one of the most-used technologies in software development. Understanding it lets you:

• Build web applications that store and retrieve data
• Analyse datasets in business intelligence tools
• Understand how almost every backend system manages its data