Databases

What the examiner is really testing

A database task in Paper 2 is a chain. You import a file, choose data types, set keys, join tables, build a form, run a query, and print a report. Each link depends on the one before it.

Chain broken at the start — a postcode stored as a number, a primary key that is not unique, two tables that were never related — and the query returns nothing and the report is empty. Most lost marks in this section are structural, not cosmetic.

That is why this chapter starts with structure and only reaches reports at the end.

The three questions every database answers

18.1 Create a database structure — what shape should the data be in, and how does it get in there?

18.2 Manipulate data — how do I calculate, sort and search it?

18.3 Present data — how do I get the answer out on paper, looking right?

Type 1 Flat file database

A database made of one single table. Every piece of data about every transaction is stored in that one table, and information is repeated as often as it is needed.

Characteristics: one table only · no relationships · data is repeated (redundant) · usually stored as a CSV or text file · quick to create, slow to maintain.

Uses: a simple mailing list, a single list of contacts, a small stock list, a spreadsheet used as a database, a one-off list exported from another system.

Advantages: very simple to set up and understand · no design work needed · fine for small amounts of data that rarely change · easy to export and email.

Disadvantages: data redundancy — the same facts are stored again and again · update anomalies — change one fact and you must change it in every row · insertion and deletion anomalies · wastes storage · larger chance of inconsistency · slow and unwieldy once it grows.

Type 2 Relational database

A database made of two or more linked tables. Each table stores one subject only, and the tables are connected by primary and foreign keys.

Characteristics: many tables · each table holds one entity · tables are linked by keys · each fact is stored once · relationships are one-to-one, one-to-many or many-to-many.

Uses: any system where the same customer, product or student appears many times — order processing, school records, libraries, hospitals, booking systems, stock control.

Advantages: no redundancy — each fact is stored once · no update anomalies — change a fact in one place and it is changed everywhere · data integrity is easier to enforce · less storage · faster searching · many users can work at once · one table can be changed without touching the others.

Disadvantages: takes time and skill to design properly · more complex to set up · queries must join tables together, which is slower to write and needs more knowledge · overkill for a tiny list.

Normalisation — how you get from one to the other

Normalisation is the process of taking a flat file and splitting it into linked tables so that each fact is stored once. There are formal stages (first, second and third normal form) but the exam only expects the idea:

1. Identify each entity — a real thing you store data about: customers, books, orders.

2. Give each entity its own table, with the fields that describe only it.

3. Give each table a primary key.

4. Put a copy of that primary key into any table that needs to refer to it — that copy is the foreign key.

5. Create the relationship between them.

How the question is usually phrased

The three words you must not mix up

Entity — a real thing you store data about: a customer, a book, an order.

Table — where the data about one entity is stored. One entity, one table.

Field — one column. One piece of information about the entity: Town, Price, OrderDate.

Record — one row. All the fields for one single customer, book or order.

"Field" and "column" mean the same thing; "record" and "row" mean the same thing. Use the exam's word: field and record.

Naming fields well

Field names must be meaningful — someone reading the report should understand them.

The task will often specify the field names to use. Use them exactly: if it says DateOfBirth, do not type DOB.

Avoid spaces in field names — use OrderDate or Order_Date instead. Spaces force you to wrap the name in square brackets in every formula.

Do not put the unit in the field name (PriceInTaka) — put it in the report label instead.

The two file types you will be given

.csv — comma separated values. Plain text, one record per line, with a comma between each field. The first line often holds the field names.

.txt — plain text. The values may be separated by commas, tabs, or be at fixed widths.

Both are flat files: a single list of records with no relationships. Your job in the exam is often to import one and then split it into linked tables.

The four import traps

1 · Wrong delimiter. Choose tab when the file uses commas and every record imports as one giant field. Look at the preview pane before clicking Finish.

2 · "First row contains field names" left off. The heading line imports as a record of data, so OrderID ends up sitting in the table as if it were an order.

3 · Numbers and dates landing as text. The wizard guesses; check the preview and set the type yourself. This is why the next section matters so much.

4 · No primary key set. Either let the wizard add one, or choose the field that is unique. Without it you cannot build relationships later.

Dates: the one that catches everyone out

A date in a text file can be 2024-03-02, 02/03/2024 or 3/2/24. The database reads it using your computer's regional settings.

If your machine is set to US format (mm/dd/yyyy), then 05/03/2024 means 5 March to you but 3 May to the database. Anything after the 12th of the month cannot be a month, so those dates import correctly — which is exactly why the error hides until it is too late.

Key 1 Primary key

The field (or set of fields) that uniquely identifies each record in a table. No two records may share the same primary key value.

Its characteristics — learn these four:

1 · Unique. No two records can have the same value. This is the whole point.

2 · Never empty. Every record must have a value — a blank primary key would identify nothing.

3 · Does not change. A primary key must be stable for the life of the record. If it changes, every foreign key that refers to it breaks.

4 · Minimal. Use the smallest set of fields that does the job. One field is better than two.

Composite key — sometimes no single field is unique, so two fields together form the key. In tblBooks, Title alone is not unique (the same title can be reprinted), and neither is Author. Title + Author together usually works.

AutoNumber — most packages can generate the key for you (1, 2, 3…). This guarantees uniqueness and means nobody has to invent a code.

Key 2 Foreign key

A field in one table that holds the primary key of another table. It is the link between the two tables.

Its characteristics — learn these four:

1 · It is not unique. Unlike a primary key, the same foreign key value appears many times. Customer C01 appears in three orders — that is exactly what makes it a one-to-many relationship.

2 · It matches the primary key's data type. A text CustomerID cannot link to a numeric one.

3 · Every value must exist in the parent table. This rule is called referential integrity — you cannot have an order for a customer who does not exist.

4 · It may be left empty if the relationship is optional — an order that has not yet been assigned to a customer.

Why not use names as keys? Because a name is not guaranteed to be unique (two Amins), and names change. A customer ID does neither.

One-to-many (1 : ∞)

By far the most common. One record in the first table relates to many records in the second — and every record in the second relates to only one in the first.

Example: one customer places many orders; each order belongs to one customer.

How it is built: the primary key of the "one" table is placed into the "many" table as a foreign key. There is no other way to build it.

One-to-one (1 : 1)

One record in the first table relates to exactly one record in the second.

Example: each employee has one payroll record; each student has one medical record.

Why bother? To split a very wide table for security or speed — the sensitive fields go in a separate table that fewer people can open. It is used rarely.

Many-to-many (∞ : ∞)

Many records in the first table relate to many in the second.

Example: many students take many courses; many books are written by many authors.

A relational database cannot store this directly. You must break it into two one-to-many relationships using a link table (also called a junction table) in the middle, holding the two foreign keys.

Why use a form instead of typing into the table?

One record at a time. Far less chance of typing a value into the wrong row.

Controls restrict what can be entered. A drop-down cannot be misspelled; a checkbox can only be true or false. This is the main reason forms exist — they prevent invalid data.

Validation can be added — a rule that rejects an impossible date or an empty required field.

It can show data from several tables at once, including a subform listing that customer's orders.

Labels and layout make the data understandable to someone who does not know the table design — which is who will be using it.

The four controls the syllabus names

The layout settings the syllabus lists

Specified fields — include every field the task names, and no others. A missing field loses a mark even if the form looks beautiful.

Font styles and sizes — choose one font and one size and use them consistently. Headings may be larger or bold; body fields must match each other.

Spacing between fields — enough vertical space that the eye can follow from one field to the next without them merging into a block.

Character spacing of individual fields — the space between the letters inside a field. Widening it on a short code field (a customer ID, a postcode) makes the value much easier to read and to check.

Use of white space — empty space around and between groups of fields. It is not wasted space; it is what makes the form scannable.

The three regions of a form

Form header — appears once at the top. Holds the title of the form, and sometimes a logo or instructions.

Detail — the middle. Holds the labels and controls, and repeats once per record.

Form footer — appears once at the bottom. Holds totals, buttons, or the date the form was printed.

Do not confuse these with report headers and footers — a report has four zones (report/page header and footer) because it prints many records over several pages. A form shows one record, so it has three.

Content

All required fields are present, with no unnecessary ones. A form that cannot capture the data is useless however pretty it is.

Every field has a clear label. Not Txt3 — "First name". The label must say plainly what goes in the box.

Labels are meaningful to the user, not to the designer. "Date joined", not JoinDate.

The correct control is used for each field — checkbox for yes/no, radio for a short fixed list, drop-down for a long one, text box only for genuinely free text.

Choices are restricted wherever possible, so invalid data cannot be typed in at all.

Validation is applied — required fields cannot be left empty, dates must be real dates, numbers must be numbers.

Layout

A clear title in the form header, so the user knows what they are filling in.

Logical order and grouping — fields follow the order the user thinks in, and related fields sit together (all the name fields, then all the address fields).

Consistent font style and size throughout the detail area.

Labels aligned with each other and controls aligned with each other, usually left-aligned labels with the boxes starting at the same x-position down the form.

Adequate spacing between fields, so each field reads as a separate item.

Generous white space — the form must not look cramped. Empty space is a design tool, not wasted paper.

A sensible tab order, so pressing Tab moves down the form in the order the user expects.

Idea 1 Calculated field

A field whose value is worked out from other fields rather than typed in. It is defined by an expression and it produces one value per record.

Example: tblOrders stores Quantity and the book's Price. A calculated field named Total with the expression

Total: [Quantity] * [Price]

gives each order its own line total. A calculated field is a column — one value on every row.

Never store what you can calculate. If you stored the total as well, it would go out of date the moment somebody changed the quantity.

Idea 2 Calculated control

A box on a form or report that displays the result of a calculation. It is not a field in a table — it lives on the form or report itself.

Example: a box in the report footer containing

=Sum([Total])

shows the grand total once, at the end of the report. A calculated control usually produces one value for the whole set, because it uses an aggregate function.

Where they go: totals and counts go in the report footer or form footer, not in the detail area — otherwise they repeat on every single row.

Ascending and descending

Ascending (ASC) — the smallest or earliest first: A→Z, 0→9, oldest date → newest date.

Descending (DESC) — the largest or latest first: Z→A, 9→0, newest date → oldest date.

Descending is how you find the biggest seller, the highest mark or the most recent order — sort descending and the answer is the first record.

Each field sorts by its own rules: text sorts alphabetically character by character, numbers sort by value, dates sort chronologically. This is exactly why setting the right data type matters — text "sorts" 10 before 9.

Single criterion sorting

One field, one direction. "Sort the data into ascending order of Town."

Sorting does not change the stored order of the records in the table — it only changes the order in which they are displayed by the query, form or report. The underlying data is untouched.

Because of that, the same table can be shown in several different orders at once, by several different queries.

What a query actually is

A query is saved criteria, not saved data. Run it and the database looks at the current table and returns the matching rows. Add a new order tomorrow and the same query will include it.

A criterion is the test a record must pass: Town = "Dhaka". Records that pass are selected; the rest are hidden.

Multiple criteria means two or more tests joined by AND or OR.

Queries are built in the query design grid (query by example, or QBE): you add the fields you want to see, then type the criteria underneath.

The nine operators

NOT is the tenth: it reverses a test, so NOT "Dhaka" means everything except Dhaka — the same result as <> "Dhaka".

Wildcards

A wildcard is a character that stands in for other characters, used with LIKE when you do not know the exact value.

* — any number of characters, including none at all.
LIKE "D*" finds Dhaka and Dinajpur.
LIKE "*pur*" finds anything containing "pur" anywhere.

? — exactly one character.
LIKE "B0?" finds B01 and B05, but not B011.

# — exactly one digit (in some packages).
LIKE "B0#" finds B01 but not B0A.

The QBE grid

The query design grid has one row per setting and one column per field:

Field — the fields to include in the result.
Table — which table each field comes from.
Sort — ascending or descending.
Show — ticked if the field appears in the output.
Criteria — the first test.
or — a second test joined with OR.

Two criteria on the same row = AND. A criterion on the or row = OR. That single rule is how you build every multi-criteria query.

Report header and report footer — once

Report header prints once, at the very beginning of the report. It holds the report title, the business name, a logo, and the date the report was produced.

Report footer prints once, at the very end. It holds the grand totals and counts — the =Sum([Total]) calculated controls from §10.

The test: if it should appear on page 1 only, or on the last page only, it belongs in a report header or footer.

Page header and page footer — every page

Page header prints at the top of every page. It holds the column headings — without it, pages 2, 3 and 4 are just columns of numbers with no idea what they mean.

Page footer prints at the bottom of every page. It holds the page number ("Page 2 of 5"), the date, and often the candidate's centre and candidate number — which Paper 2 requires on every printed page.

The test: if a reader holding only page 3 needs it, it belongs in a page header or footer.

Tabular layout

One row per record, one column per field, with the column headings across the top — it looks like a table.

Use it when you have many records with few fields each, and the reader wants to compare or scan down a column. This is the layout for an order list, a stock list or a mark sheet.

It fits far more records on a page, which saves paper.

Columnar layout

Each record is a block, with the labels down the left and the values beside them — like a filled-in form.

Use it when each record has many fields, or fields with long values (an address, a description) that would not fit in a narrow column. This is the layout for a customer record, a patient record or an invoice.

It is far easier to read one complete record, but it uses much more paper.

Setting Number of decimal places

How many digits are shown after the decimal point. Display only — the stored value keeps full precision, so totals still add up exactly.

0 dp — counts, quantities, whole units: 450.

2 dp — money in nearly every currency: 450.00. Money always shows exactly two decimal places, even when they are both zero.

1 or 3 dp — measurements and scientific data: 12.5 kg, 3.142 m.

Consistency is the rule. Every money figure in the report must use the same number of decimal places. A column showing 450.00, 320 and 780.5 looks careless and loses a mark.

Setting Currency symbol and percentage

Currency adds the symbol and usually a thousands separator: Tk 12,463.00, $1,250.00. Set it once on the field or control, not by typing the symbol into the label.

Why thousands separators matter: 1246300 takes a moment to read; 1,246,300 does not.

Percentage shows the value as a fraction of 100, with the % sign: 12.5%. Be clear which you have stored — most packages store 0.125 and display 12.5%; some expect you to type 12.5. Check, or every figure in the report will be 100 times too small.

Alignment Right-align numeric data

Text is left-aligned. The eye reads from the left edge, so a ragged right edge is fine for words.

Numbers are right-aligned. The units column must line up, so that the size of a number can be judged at a glance by where its last digit sits. Left-align a column of money and 320.00, 1250.00 and 78.50 all start in the same place — the reader has to count digits to compare them.

Column headings are aligned with the data beneath them: a right-aligned column of numbers gets a right-aligned heading.

Alignment Decimal alignment

Decimal alignment lines the column up on the decimal point itself — the whole numbers sit to the left of it and the decimal places to the right.

It is the same idea as the decimal tab stop in a word processor, and it is the correct alignment whenever values have different numbers of digits before the point.

With a fixed number of decimal places, right alignment and decimal alignment give the same result — which is why setting 2 decimal places and right-aligning is the standard combination for money.

Never centre a column of numbers. Centring destroys the units alignment entirely.

The command words

"Compare a flat file with a relational database" is a Compare question. Half the marks are for the flat file, half for the relational — an answer that only describes one of them cannot score more than half, however good it is.

Model answer — flat file vs relational

Model answer — primary and foreign keys

Model answer — good form design

Task A Import and set up

"Import the data in orders.csv into a new table called tblOrders using the field names given."

1. Choose Import, browse to the file, and pick delimited.

2. Set the delimiter to comma — check the preview shows separate columns.

3. Tick "first row contains field names".

4. Set each field's data type in the wizard: ID → text or integer, date → date/time, money → currency.

5. Choose a primary key — let the wizard add one, or pick the unique field.

6. Name the table exactly as asked: tblOrders.

The classic error: clicking Finish before checking the preview, so the whole file lands in one column.

Task B Link the tables

"Create a one-to-many relationship between tblCustomers and tblOrders."

1. Open the Relationships window and add both tables.

2. Check that CustomerID is the primary key of tblCustomers and that tblOrders contains a CustomerID field of the same data type.

3. Drag CustomerID from tblCustomers onto CustomerID in tblOrders.

4. In the dialog, tick Enforce Referential Integrity.

5. Confirm the relationship type is One-To-Many, then Create.

The classic error: forgetting referential integrity, or dragging the wrong way round — always drag from the one side to the many side.

Task C Build the query

"Select all orders from Dhaka or Sylhet where the quantity is more than 2."

1. Read the question carefully: two towns = OR; more than 2 = > 2 (not >=).

2. Add the fields you need to the grid: OrderID, Town, Quantity, Total.

3. Under Town, type "Dhaka" on the criteria row and "Sylhet" on the or row — the or row is what makes it OR.

4. Under Quantity, type >2 on the criteria row — the same row as the first town, so it is joined with AND.

5. Run it and count the records to check the answer looks sensible.

The classic error: putting both towns on the criteria row — which asks for an order that is from Dhaka AND Sylhet, and returns nothing.

Task D Produce the report

"Produce a report showing all the selected orders with a title, column headings, page numbers and a grand total."

1. Base the report on the query from Task C, not on the raw table.

2. Report header — add the title.

3. Page header — move or retype the column headings here so they repeat on every page.

4. Page footer — add ="Page " & [Page] & " of " & [Pages] and your centre and candidate number.

5. Report footer — add a calculated control =Sum([Total]).

6. Right-align the money column, set it to 2 decimal places with the currency symbol, and widen every control until nothing is cut off.

The classic error: leaving the column headings in the report header, so they print once and pages 2+ have none.

Structure — I can…

Forms — I can…

Manipulate data — I can…

Present data — I can…

Where §18 fits

§18 shares Paper 2 with §17 and §19, and it leans on earlier sections:

§4–8 Data and files — data types, validation and file formats appear in both theory and practical work.

§14 Styles — the report formatting settings are the same ideas applied in a different application.

§16 Graphs and charts — a chart can be embedded in a database report, and the same rules about axes, labels and legends apply.

§17 Document production — headers, footers, page numbering and alignment in a report work exactly as they do in a word-processed document. Learn them once.

§20 Spreadsheets — SUM, AVERAGE, MAX, MIN and COUNT are the same five functions in both applications, with the same syntax.

The one-paragraph summary

Decide first whether the data is flat (one table, repeated data, update anomalies) or relational (linked tables, each fact stored once, needs designing). Import the source .csv or .txt file, watching the delimiter and the field-name row. Give every field the right data type — text for codes, numeric for anything you add up, date/time for dates, Boolean for yes/no — and set its display format. Give each table a unique, never-empty primary key, copy it into the child table as a foreign key, and create the relationship with referential integrity on. Build a form with the right controls so bad data cannot be typed in. Then ask questions: calculate with a calculated field, sort on one or many fields, and query with operators and wildcards, remembering that AND narrows and OR widens. Finally print a report: title in the report header, column headings in the page header, page numbers in the page footer, grand total in the report footer, numbers right-aligned with two decimal places — and nothing cut off.