Database normalisation, SQL and transactions

Keys, relationships and indexes

Keys describe how rows are identified or related. An index is a separate access structure: it can speed suitable searches, but consumes space and adds work when data changes.

Students and courses are many-to-many. Enrolment links them: each student and each course has many enrolments, while each enrolment refers to one of each. These invented tables are unrelated to the site's application database.

• Candidate key: uniquely identifies a row; choose one as the primary key.
• Composite key: combines several attributes.
• Foreign key: references another relation's unique key.
• Secondary key: supports searching, for example by surname; it need not be unique.

Read entity-relationship diagrams

OCR Appendix 5f uses rectangles for entities. An unbranched relationship end means one; a fork means many. Our text sketches use brackets for rectangles and angle marks for forks.

Read cardinality from the business rule, not from a small sample. One current enrolment does not establish a one-to-one policy. These examples describe maximum cardinality without asserting optional participation.

Capture and exchange useful data

Capture can use a form, barcode reader or import. Validate types and ranges; verification compares captured data with its source. Queries select needed records. Constraints, permissions, backups and retention rules manage stored data.

Exchange requires agreed field names, types, encoding and dates. CSV suits rectangular extracts but needs quoting/missing-value conventions; structured formats can represent nesting. Stage imports, check references and duplicates, then commit accepted records. A readable file can still contain incorrect values.

What each normal form removes

Normalisation follows dependencies. Splitting tables without first stating those dependencies can lose information or invent relationships.

Our model assumes one enrolment per student/course pair, one current tutor per course and one name per student or tutor ID. These are business rules. If tutors vary by class session, revise the model.

Read SQL by the rows it selects

Read a SELECT by asking which rows it finds and which columns it outputs. Parenthesise mixed AND/OR conditions. A nested SELECT can supply values to an outer condition.

LIKE 'A%' matches text beginning with A under the database's matching rules; % matches a sequence of characters. SELECT * requests every column.

INSERT adds rows. DELETE ... WHERE removes selected rows; omitting WHERE removes every row. DROP TABLE removes the table itself.

CREATE TABLE, UPDATE, ORDER BY, GROUP BY, COUNT, AVG and HAVING are explained practical extensions here. They are not additional named OCR appendix requirements. WHERE filters individual rows before grouping; HAVING filters finished groups, including aggregate conditions.

Protect relationships and concurrent changes

Referential integrity prevents an enrolment referencing a nonexistent student or course. Define a deletion policy: for example, refuse to delete a course until its enrolments are removed.

Two clerks can both read the same last place and book it twice. Protect the capacity check and change with a suitable lock or atomic conditional update inside the booking transaction.

Row locks can make conflicting writers wait without universally blocking readers. Consistent lock ordering helps avoid deadlocks.

Redundant copies improve availability and recovery, but cost storage and synchronisation. Replication can copy an accidental deletion too; retain recoverable backups.

Original A-Level practice

8 original questions total 32 marks. Attempt each before opening the independently written indicative marking guidance.