Databases

Cambridge tests these terms repeatedly — get them right.

Database. A structured, organised collection of related data.

DBMS (Database Management System). The software that creates, manages and queries databases. Examples: MySQL, PostgreSQL, SQL Server, SQLite, MongoDB.

Table. A 2D structure: rows and columns. Each table holds related data — a Students table holds rows of student data; a Books table holds rows of book data.

Record. A ROW in a table — all the fields about ONE entity. One record = one student.

Field. A COLUMN in a table — one specific attribute. The Name field has every student's name; the Year field has every student's year.

Primary key (PK). A field (or combination) that UNIQUELY identifies each record. No duplicates; no nulls. Examples: StudentID, ProductCode, ISBN. Every relational table has one.

Foreign key (FK). A field in one table that REFERENCES another table's primary key, linking the two. Marks.StudentID is a foreign key to Students.StudentID.

Term	Memorable shape
Table	Grid
Record	ROW (entity)
Field	COLUMN (attribute)
Primary key	Unique row identifier
Foreign key	Cross-table link

Cambridge tip. Mark scheme expects students to know all five terms. Confusing record/field is the most common error.

Flat-file database. A single table holding ALL the data.

Imagine a Sales flat-file:

• Each ROW = one transaction.
• COLUMNS: TransactionID, CustomerName, CustomerAddress, ProductCode, ProductName, Price, Quantity, Date.

Problems.

• Duplication. A regular customer's name and address appear on EVERY order they place. Wasted space.
• Update anomalies. If the customer moves house, every row needs updating. Miss one and the database becomes inconsistent.
• Insertion anomalies. Hard to add a new customer who hasn't placed an order yet — there's no place for them.
• Deletion anomalies. Deleting a customer's last order also loses the customer's contact details.

Relational database. Multiple tables linked by keys.

Same data, three tables:

• Customers: CustomerID (PK), Name, Address.
• Products: ProductCode (PK), Name, Price.
• Sales: TransactionID (PK), CustomerID (FK), ProductCode (FK), Quantity, Date.

Customer details stored ONCE; orders REFER to them. Product details stored ONCE; orders REFER to them.

Advantages.

• Less duplication. Each fact stored in ONE PLACE.
• Easier updates. Change a customer's address ONCE; every order automatically reflects it.
• Better integrity. Foreign-key constraints prevent orders from referencing non-existent customers.
• Flexibility. Easy to add new types of relationships without restructuring.
• Better querying. SQL JOINs let you combine tables efficiently for reports.

Cambridge tip. Mark scheme rewards naming the SPECIFIC advantage and explaining WHY it matters. 'Less duplication' alone gets half marks; 'less duplication so updates only happen in one place' gets full.

Primary key (PK). Each table has ONE primary key — a field (or combination of fields) that uniquely identifies each record.

Rules:

• Unique — no two records can share the same PK.
• Not null — every record must have a PK value.
• Stable — shouldn't change once assigned.

Common choices: surrogate IDs (StudentID = 1, 2, 3 ...), natural identifiers (ISBN, NHS number), composite keys.

Foreign key (FK). A field in one table that REFERENCES the primary key of another. Creates a relationship.

Example. Students(StudentID, Name, Year). Marks(MarkID, StudentID, Subject, Score). Marks.StudentID is a FK pointing to Students.StudentID. Each mark is associated with a specific student.

Why foreign keys matter.

• Allow normalisation (split tables sensibly without losing relationships).
• Enable JOINs in queries to combine data.
• Enforce REFERENTIAL INTEGRITY — the DBMS prevents orphan references (you can't have a Mark referring to a non-existent Student).

Cambridge tip. When asked to identify a foreign key in a schema, look for a field whose value matches a PRIMARY KEY in ANOTHER table.