Describe files, fields and records, relational databases, normalisation, basic SQL, and validation and verification.

What this dot point is asking

WJEC wants you to describe how data is held in files and in relational databases, to explain primary and foreign keys, to normalise a simple database to remove redundancy and anomalies, to write basic SQL, and to distinguish validation from verification. Databases are a substantial slice of Unit 1 and link forward to the deeper database content in Unit 4. Expect a normalisation question and a keys question, both of which reward precise vocabulary.

The answer

Files, fields and records

The move from a single flat file to a structured database is the central theme of this dot point, because separating data into tables is what makes it consistent and maintainable.

Relational databases and keys

Designing the tables and their relationships, with one fact stored in one place, is what gives a database integrity and avoids the contradictions a flat file invites.

Normalisation

Normalisation is the process of organising tables to remove redundancy (repeated data) and the anomalies redundancy causes: the insert anomaly (cannot add data without unrelated data), the update anomaly (a change must be made in many places) and the delete anomaly (removing one fact loses another). It splits repeating or dependent data into separate, linked tables.

SQL, validation and verification

Validation checks that entered data is reasonable, using checks such as range, type, presence, format and length. Verification checks that data has been entered or transferred correctly, for example by double entry or a visual check. Validation cannot guarantee correctness, only plausibility.

Examples in context

Example 1. A library catalogue

A library separates Books, Members and Loans into three tables. The Loans table holds a foreign key to Books and a foreign key to Members, so each loan links a member to a book without copying the book title or member name. This is normalisation in action: each fact is stored once, in one place.

Example 2. The cost of a flat-file address

A shop keeps customer addresses in its Orders flat file, repeating the address on every order. When a customer moves, staff must edit dozens of rows, and one missed row leaves the database self-contradicting. Splitting customers into their own table fixes this update anomaly, showing concretely why normalisation matters.

Example 3. Validation versus verification at sign-up

A web form rejects an email without an at sign (validation: a format check), then asks the user to type the email twice and compares the two (verification: a double-entry check). Both run, because a correctly formatted email can still be the wrong one, which is exactly the distinction examiners test.

Try this

Q1. State the SQL keyword used to filter the rows returned by a query. [1 mark]

• Cue. WHERE.

Q2. Give one example of a validation check and state what it tests. [2 marks]

• Cue. For example, a range check tests that a value falls between a stated minimum and maximum.