Databases

A regular Paper 2 question: data types, primary keys, and reading or completing an SQL query on a table.

1. Tables, records and fields

A single-table database stores data in one table. Each record (row) holds the data about one item; each field (column) holds one piece of data about every item.

Table: STUDENTS (8 records, 6 fields)

StudentID	FirstName	Year	House	Score	Paid
S001	Ayla	10	Red	72	TRUE
S002	Ben	11	Blue	85	TRUE
S003	Cem	10	Green	64	FALSE
S004	Deniz	11	Red	91	TRUE
S005	Elif	10	Blue	58	TRUE
S006	Farid	11	Green	77	FALSE
S007	Grace	10	Red	83	TRUE
S008	Hasan	11	Blue	69	TRUE

2. Data types

Data type	Stores	Example
Text / alphanumeric	Letters, numbers and symbols	FirstName: “Ayla”; StudentID: “S001”
Character	A single character	Grade: “A”
Boolean	One of two values: TRUE or FALSE	Paid: TRUE
Integer	Whole numbers	Year: 10
Real	Numbers with a decimal part	Price: 4.99
Date/time	A date and/or time	DateOfBirth: 14/05/2010

Phone numbers and IDs with letters are stored as text, not integer: they can start with 0 and are never used in calculations.

3. Validation

Validation checks that data entered is sensible and reasonable before it is stored.

Check	Example
Range check	Year must be between 7 and 13
Length check	StudentID must be exactly 4 characters
Type check	Score must be an integer
Presence check	FirstName must not be left blank
Format check	StudentID must be one letter followed by three digits
Check digit	The last digit of an ISBN or barcode

4. Primary keys

A primary key is a field that holds a unique value for every record, so each record can be identified. In STUDENTS, StudentID is the primary key. FirstName, Year or House would not work, because two students could share the same value.

5. SQL

Structured query language (SQL) is used to search a database.

Keyword	Purpose	Example
SELECT	Which fields to show (* = all fields)	SELECT FirstName, Score
FROM	Which table to search	FROM STUDENTS
WHERE	Which records to include	WHERE Year = 11
AND / OR	Combine conditions	WHERE House = ‘Red’ AND Paid = TRUE
ORDER BY … ASC / DESC	Sort the results in ascending or descending order	ORDER BY Score DESC
SUM	Add up a number field	SELECT SUM(Score)
COUNT	Count the records found	SELECT COUNT(*)

Example: SELECT FirstName, Score FROM STUDENTS WHERE Year = 11 ORDER BY Score DESC; returns Deniz 91, Ben 85, Farid 77, Hasan 69.

6. Examiner tips

1. Use the field names exactly as they appear in the table.
2. Text values go in quotes in SQL; numbers and TRUE/FALSE do not.
3. Phone numbers are text, not integer, because of leading zeros and spaces.
4. Give a reason for a primary key: “StudentID is unique for every student”.
5. Count records and fields carefully; don’t count the header row as a record.
6. Check AND vs OR: AND needs both conditions to be true; OR needs at least one.
7. Copy the output exactly in the order the ORDER BY gives.

7. Practice questions

8. Topic test: predict your grade