Databases

Before databases, data was stored in separate flat files, one per application, each with no awareness of the others. This caused problems serious enough that the relational database was invented specifically to solve them.

Try it: watch redundancy break in a file-based system

The customer's address is edited in the sales file below. Click update and watch what happens in the delivery file, which has no idea the sales file even exists.

These terms are used precisely and interchangeably with their everyday-spreadsheet equivalents in exam mark schemes, know both names for each.

These two linked tables are used as the running example for the rest of this chapter, including the interactive SQL playground later on, get familiar with their structure now.

Student

🔑 = Primary Key  |  🔗 = Foreign Key (references the Course table)

Course

Entity-relationship (ER) diagrams show entities and the relationships between them, including cardinality, how many of each entity can relate to the other. There are three cardinalities to know, and they are implemented in a relational database in different ways.

Try it: explore each relationship type

1NF rules

• Every cell contains a single, atomic (indivisible) value
• No repeating groups of columns
• All values in a column are the same data type
• Each row is unique, and has a primary key

Try it: normalise a table step by step

The unnormalised table below is a real exam-style starting point. Step through 1NF, then continue into 2NF and 3NF in the next section, watching the table split at each stage.

2NF rules

• Must already be in 1NF
• No partial dependencies: every non-key attribute must depend on the whole primary key, not just part of it
• Only relevant when the primary key is composite, a single-field primary key is automatically in 2NF

3NF rules

• Must already be in 2NF
• No transitive dependencies: non-key attributes must not depend on other non-key attributes

Continue the stepper from the previous section here, if you have not already reached 3NF.

A Database Management System (DBMS) is software that sits between the user (or an application) and the physical data files, handling the creation, maintenance, security and querying of a database so that no program ever has to touch the raw storage directly. This is the layer that turns a pile of files back into the single, consistent, controlled resource that Section 1 showed file-based systems could never be.

A transaction is a single logical unit of work made up of one or more database operations, for example "transfer $50 from Account A to Account B" is really two operations, a debit and a credit, that must both succeed or both fail together. A DBMS guarantees four properties for every transaction, remembered by the acronym ACID.

Try it: watch atomicity protect a bank transfer

Below is a $50 transfer from Account A to Account B, modelled as two steps: debit A, then credit B. Run it normally, or simulate a crash between the two steps, and compare what happens with a DBMS enforcing atomicity versus a naive file-based system that has no concept of a transaction at all.

SQL (Structured Query Language) is split into two families of commands: DDL, which defines and modifies the structure of a database (creating and altering databases and tables), and DML, which manipulates the data stored inside that structure (querying, inserting, updating, deleting rows). This section covers DDL.

Creating a database and a table

CREATE DATABASE SchoolSystem;

CREATE TABLE Student (
    StudentID CHARACTER(4) PRIMARY KEY,
    FirstName VARCHAR(20),
    LastName VARCHAR(20),
    DateOfBirth DATE,
    CourseID CHARACTER(3),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

SQL data types

Altering a table after creation

ALTER TABLE changes the structure of a table that already exists, adding or removing columns, or adding keys that were not defined at creation time.

ALTER TABLE Student ADD Email VARCHAR(40);

ALTER TABLE Student ADD PRIMARY KEY (StudentID);

ALTER TABLE Student
    ADD FOREIGN KEY (CourseID) REFERENCES Course(CourseID);

Try it: build a CREATE TABLE statement

Add columns, pick a data type for each, and mark one as the primary key, the SQL below updates live as you build the table.

Every query you write starts from SELECT. The three clauses below, in this order, cover the large majority of exam questions.

SELECT column1, column2
FROM TableName
WHERE condition
ORDER BY column1 ASC | DESC;

WHERE conditions

WHERE CourseID = 'C01'
WHERE DateOfBirth > '2006-12-31'
WHERE FirstName LIKE 'A%'
WHERE CourseID = 'C01' AND LastName = 'Smith'
WHERE CourseID = 'C01' OR CourseID = 'C02'

Aggregate functions perform a calculation across many rows and return a single summary value, instead of returning the rows themselves. They are one of the most commonly missed topics in a quick read of the syllabus, but come up regularly in exam questions.

SELECT COUNT(*) FROM Student;

SELECT CourseID, COUNT(*) AS NumStudents
FROM Student
GROUP BY CourseID;

Run GROUP BY and aggregate queries yourself in the SQL Query Playground in the next section.

The Student and Course tables have been kept separate throughout this chapter, on purpose, that is what normalisation demands. But most useful questions need data from both at once, that is what JOIN is for.

INNER JOIN

SELECT Student.FirstName, Student.LastName, Course.CourseName
FROM Student
INNER JOIN Course ON Student.CourseID = Course.CourseID;

INSERT, UPDATE, DELETE

INSERT INTO Student (StudentID, FirstName, LastName, DateOfBirth, CourseID)
VALUES ('S005', 'Wei', 'Chen', '2007-02-18', 'C01');

UPDATE Student
SET CourseID = 'C02'
WHERE StudentID = 'S005';

DELETE FROM Student
WHERE StudentID = 'S005';

Try it: SQL Query Playground

Pick a preset query below and run it against the live Student and Course tables. INSERT, UPDATE and DELETE actually change the displayed data, use Reset to restore the original tables.