Two students called Amy Khan: how does a database tell them apart?

Tables, records and fields

• A database is an organised, persistent store of data.
• A table holds data about one kind of thing, such as students.
• A record is one row: all the data about one student.
• A field is one column: one item of data stored for every record, such as Surname.
• A relational database stores data in several tables that are linked together.

The primary key

• A primary key is a field that holds a different value for every record, so it uniquely identifies each record.
• Names, forms and even birthdays can repeat, so they cannot be keys.
• Schools, shops and banks add an ID field, such as StudentID, to act as the key.
• The database refuses a new record whose key value is already used.

Choosing data types

• Each field has a data type that fits the data it stores.
• Integer for whole numbers, real for decimals, Boolean for True/False, character for one character, string for text.
• Phone numbers and IDs are stored as strings: they are not used in calculations, can start with 0 and can contain letters or spaces.

Linking tables with a foreign key

• A foreign key is a field in one table that is the primary key of another table. It links the two tables.
• FormID is the primary key of Form, and a foreign key in Student.
• Each tutor's name is stored once, in Form, not repeated in every student record. This removes data redundancy (needless repeats).
• If a tutor changes, only one record is edited, so copies can't disagree: no data inconsistency.

How can one line of SQL find every overdue book in a library?

SELECT and FROM

• SQL (Structured Query Language) is used to search for data in a database.
• SELECT lists the fields you want to see, separated by commas.
• FROM names the table to search.
• This query shows the Title and DaysLate of every record in Loan:

SELECT Title, DaysLate
FROM Loan

WHERE picks the records

• WHERE gives a condition. Only records that meet it are returned.
• Compare with = < > <= >= and <> (not equal).
• Put text values in quotes: Genre = 'Fantasy'. Numbers need no quotes.

SELECT Title, DaysLate
FROM Loan
WHERE Genre = 'Fantasy'

Result: Coraline 0, Skellig 7, Matilda 5.

Writing a query

• Read the question for the fields to show (SELECT), the table (FROM) and the rule (WHERE).
• Fields in WHERE do not have to appear in SELECT.
• ORDER BY sorts the results by a field: ASC (ascending, A to Z, smallest first) or DESC (descending, largest first).
• To use two tables, list both after FROM and match the linking key in WHERE, writing Table.Field.

SELECT Title, DaysLate
FROM Loan
WHERE Genre = 'Fantasy'
ORDER BY DaysLate DESC

SELECT Member.Name, Loan.Title
FROM Member, Loan
WHERE Member.MemberID = Loan.MemberID

The first returns Skellig 7, Matilda 5, Coraline 0. The second gives each loan's title with the borrower's name from a Member(MemberID, Name, Form) table.

Adding, changing and deleting data

• INSERT INTO adds a new record: list the fields, then the VALUES in the same order.
• UPDATE changes values: SET gives the new values, WHERE picks the records.
• DELETE FROM removes the records that match WHERE.
• Leave out WHERE and UPDATE or DELETE affects every record.

INSERT INTO Loan (LoanID, Title, Genre, DaysLate, MemberID)
VALUES ('L07', 'Holes', 'Adventure', 0, 'M03')

UPDATE Loan
SET DaysLate = 0
WHERE LoanID = 'L02'

DELETE FROM Loan
WHERE LoanID = 'L07'

Big ideas

• A table stores records (rows); each record has the same fields (columns), each with a suitable data type.
• A primary key uniquely identifies each record.
• A foreign key links tables in a relational database, cutting data redundancy and inconsistency.
• SELECT fields FROM table WHERE condition: the fields to show, the table to search and the records to keep.
• ORDER BY … ASC or DESC sorts the results.
• INSERT INTO adds records, UPDATE … SET changes them and DELETE FROM removes them; WHERE decides which.