18.10417 · Section 18: Databases

Create a database structure

Key learnings

Data type, sub-type and display format
Imported .csv/.txt data uses the specified field names to build a table; each field then needs up to three separate settings. Data type: text, numeric (integer, decimal, currency), date/time, or Boolean/logical. Sub-type (numeric only): percentage, or a set number of decimal places. Display format: a Boolean shows as yes/no, true/false or a checkbox; a date/time shows as e.g. dd/mm/yyyy. The correct data type does NOT set the sub-type or display format for you.
Primary key, foreign key, relationship
Primary key — the field (or fields) whose value is unique for every record and never blank, so it identifies each record without ambiguity (e.g. CustomerID). Foreign key — a field holding a value copied from another table's primary key, to link the two tables (e.g. CustomerID placed in an Orders table). Relationship — links a foreign key to the matching primary key; usually one-to-many, where one record at the primary-key end links to many at the foreign-key end.
Flat-file vs relational database
Flat-file database — all data in a single table (which can be a spreadsheet), so the same fact (e.g. an address) repeats across records: simple, but it wastes storage and risks inconsistent updates. Relational database — data split across multiple linked tables joined by primary and foreign keys, so each fact is stored once, cutting duplication but adding a more complex structure. A flat file suits a small single list; relational suits repeating linked groups.

Common mistakes

Drawn from real examiner reports.

Primary key must be truly unique

The primary key must hold a different value for every single record. Two mistakes recur: picking a field because it comes first in the imported .csv, and picking a field such as Surname or Product name that can easily repeat. A duplicate value destroys the key's purpose — identifying one record unambiguously. Check the data for a unique field before setting it.

Flagged w23 P02 Q16, s23 P21 — candidates picked the first or a duplicate-prone field as the primary key

Don't add a redundant ID or leave no key

If an imported field (e.g. CustomerID) is already unique, inventing a brand-new ID field is unnecessary work. The opposite error is setting no primary key at all: nothing then guards against duplicate or blank values, and no foreign key in another table can reliably link to its records. Set exactly one genuinely unique field as the key — no more, no fewer.

Flagged w23 P02 Q16, s23 P21 — candidates added an unnecessary ID field or set no primary key

Data type right, sub-type forgotten

Setting the data type is not enough — the sub-type must match too. A money field must be currency with the decimal-places sub-type set as specified (commonly 2), not a generic numeric field. A sales field may need a different count (e.g. 3 dp); decimal places are set per field, not copied. A sub-type left at its default loses marks even though the field looks numeric.

Flagged w23 P02 Q16 — import with correct data types (sales to 3 dp, currency to 2 dp)

Which table holds the foreign key?

Candidates confuse which table holds the primary key and which holds the foreign key. The foreign key lives in the "many" table and holds a copy of the OTHER table's primary key — e.g. an Orders table holds a CustomerID foreign key matching the CustomerID primary key in Customers. When asked for the foreign key, name the field in the correct table.

One-to-many drawn the wrong way

A one-to-many relationship is evidenced with a 1 at the primary-key ("one") end of the link and an $\infty$ (infinity) symbol at the foreign-key ("many") end. Labelling the ends the wrong way round, or drawing the link as one-to-one, loses the mark even when the two tables are otherwise linked correctly.

Flagged w23 P02 Q18 — evidence the one-to-many relationship with the 1 and infinity symbols

Flat file = all data in one table

Definition precision matters: a flat-file database is "all data held in one single table" (which can be a spreadsheet). Describing it vaguely as "a database with no data" or "a database with only one field" scores nothing. The defining feature is the single table with its repeated data, not an absence of tables or of data.

Don't overwrite the first record

When testing a data-entry form, add the new data as a genuinely new/blank record. Opening the form on the first existing record and editing it overwrites real data the mark scheme still expects to be present in the table afterwards, losing marks even though the form itself works. Move to a new record before entering any test data.

Flagged w23 P02, s23 P21 — candidates lost marks overwriting the first record with a new entry

Exam tips

Build the structure in a fixed order
Follow one order every time: import with the exact field names; set each data type from the sample values; then any sub-type/display format; then the unique field as the primary key; then the matching foreign key and relationship; and finally the data-entry form, on a new record.
Name DISTINCT form features
When a form question asks for a set number of formatting features, name that many genuinely DIFFERENT ones — font style/size, field spacing, character spacing, white space, radio buttons, check boxes, drop-down menus. Restating one idea twice counts only once.
Advantage needs a comparative + because
For an advantage of relational vs flat-file, give a comparative point that scores — "each fact is stored once, reducing duplication" — not a vague benefit like "easier to use". If asked to explain or describe, add the because: it stops the same fact being updated inconsistently.

Revision notes