Databases — OCR A-Level Computer Science
Databases explained
Databases covers 6 specification points, including (a) Relational database, flat file, primary… and (b) Methods of capturing, selecting, managing….
Read the full explanation
Open a point below for its explanation and revision guidance.
(a) Relational database, flat file, primary key, foreign key, secondary key, entity relationship modelling, normalisation and indexing. See appendix 5f.
This clause covers eight linked database terms. A flat file stores all data in one table, repeating values and risking inconsistent updates. A relational database splits data into related tables. A primary key uniquely identifies each record in its table; a foreign key holds a primary key from another table to create a link. A secondary key is a non-unique field used to search. Entity relationship modelling plans entities, attributes and relationships before tables are built. Normalisation removes redundancy by splitting tables. Indexing builds a structure that speeds up searches. Appendix 5f holds supporting detail not supplied here, so rely on these definitions.
(b) Methods of capturing, selecting, managing and exchanging data.
This clause covers four data-handling methods: capturing, selecting, managing and exchanging. Capturing collects data into a system, for example through online forms, sensors or barcode scanners. Selecting retrieves only the records that meet set conditions, such as a query returning orders placed in March. Managing covers storing, updating, deleting and protecting data, including validation and backup. Exchanging moves data between systems or users, for example through file transfer or an API. For each method, explain its purpose, a suitable example and one risk, such as inaccurate input.
(c) Normalisation to 3NF.
Normalisation to third normal form (3NF) is the final stage of removing redundancy and update anomalies from a relational database design. A table is in 1NF when every attribute is atomic and there are no repeating groups. It is in 2NF when it is in 1NF and no non-key attribute depends on only part of a composite primary key. It is in 3NF when it is in 2NF and no non-key attribute depends on another non-key attribute, a transitive dependency. For example, an Order table holding OrderID, CustomerID and CustomerPostcode has the postcode depending on CustomerID, so it moves to a Customer table keyed by CustomerID. The original table keeps CustomerID as a foreign key.
(d) SQL – Interpret and modify. See appendix 5d.
SQL (Structured Query Language) is used to define, query and change data in a relational database. For this criterion, you must be able to interpret an existing SQL statement by tracing which table, columns and conditions it uses, and to modify it so that it meets a new requirement. For example, SELECT Name FROM Student WHERE Year = 13; returns the Name values of students in Year 13. Changing the condition to Year = 12 changes which rows are returned. The source refers to appendix 5d for further detail. That appendix is not included in the supplied material, so consult it in the official specification and do not rely on this note for its content. Use only the table and column names given in the question.
(e) Referential integrity.
Referential integrity keeps relationships between tables consistent. A foreign key in a child table must either be null, where the design allows it, or match an existing primary key value in the parent table. For example, an Order table with a CustomerID foreign key must not refer to a customer missing from the Customer table. The DBMS enforces this, so an insert or update that would create an orphan record is rejected. Deleting a parent row that still has child rows is controlled by the design: the designer chooses to block the delete, cascade it to the child rows, or set the foreign key to null. The rule protects the meaning of links between tables rather than the validity of values in a single column.
(f) Transaction processing, ACID (Atomicity, Consistency, Isolation, Durability), record locking and redundancy.
A transaction is a group of database operations treated as one unit of work. ACID describes reliability properties: Atomicity means all operations complete or none do. Consistency means the database moves from one valid state to another. Isolation means concurrent transactions do not see each other's intermediate changes. Durability means committed changes survive a later failure. Record locking supports isolation by stopping a second transaction changing a record that the first is using. Redundancy, such as backup copies, supports durability and recovery, although good table design still limits unnecessary duplicated data.
Your focus
- Define relational database, flat file, primary key, foreign key and secondary key in one sentence each.
- Explain how normalisation and indexing affect redundancy and retrieval speed respectively.
- Identify the purpose of entity relationship modelling in planning a database before implementation.
Show all 18 objectives
- Define capturing, selecting, managing and exchanging data in one sentence each.
- Give a realistic example of each method from an everyday system.
- Identify one risk and one control for each method.
- Identify the primary key and non-key attributes in a given unnormalised or 2NF table.
- Locate a transitive dependency between two non-key attributes in a given table design.
- Decompose a table into 3NF tables, showing primary and foreign keys for each new table.
- Trace which table, columns and rows a given SELECT statement uses to produce its output.
- Modify a given SQL condition so that the query returns a specified set of rows.
- Identify the effect of an UPDATE or DELETE statement that is missing a WHERE clause.
- Explain the purpose of referential integrity in a relational database.
- Identify a foreign key and the primary key it must match in a given pair of tables.
- Give an example of an orphan record that referential integrity prevents.
- Define a transaction and explain why it must complete or be rolled back as a whole.
- Explain each ACID property with a database example.
- Describe how record locking and redundancy support isolation and durability.
Databases exam tips
Marking Points
- Defines a relational database as data held in several related tables linked by keys, rather than in one large table.
- Identifies a primary key as a field whose value is unique for each record in its own table.
- Explains that a foreign key is a field in one table that matches the primary key of another table, creating a relationship.
- Distinguishes a secondary key as a field used to search for records, which need not be unique.
- Describes entity relationship modelling as planning entities, attributes and relationships before the database is implemented.
- Explains normalisation as organising tables to reduce redundancy and update anomalies.
- Explains indexing as a structure that speeds up data retrieval at the cost of extra storage and slower writes.
- Contrasts a flat file, which holds all data in one table, with a relational database.
- Defines capturing as collecting data into a system from a named source, such as a form or sensor.
- Explains selecting as retrieving only the records that meet stated conditions, typically through a query.
- Describes managing as storing, updating, deleting and protecting data, including validation and backup.
- Describes exchanging as transferring data between systems or users, for example by file transfer or an API.
- Links each method to a suitable example and a relevant risk, such as inaccurate capture or unauthorised access during exchange.
- Checks that every attribute holds a single atomic value before testing for 3NF.
- Confirms that the table is already in 2NF, so no partial dependency on a composite primary key remains.
- Identifies a non-key attribute that depends on another non-key attribute as a transitive dependency.
- Splits the table so that the determinant and its dependent attributes sit in a new table with the determinant as its primary key.
- Keeps the determinant in the original table as a foreign key so the tables can be joined again.
- Justifies the change by naming the update anomaly the split removes, such as changing a postcode in several order rows.
- Identifies the table named in the FROM clause as the source of the rows being read.
- Traces each condition in the WHERE clause to determine which rows are returned.
- Predicts the output of a SELECT statement by listing the columns named after SELECT for the rows that meet the condition.
- Modifies a condition, such as changing a comparison value, so that the query returns the rows the new requirement describes.
- Recognises that an UPDATE or DELETE statement without a WHERE clause affects every row in the table.
- Writes a modified statement that uses only the table and column names given in the question.
- Defines referential integrity as a rule that links a foreign key in one table to a primary key in another table.
- States that a child-table foreign key must be null where allowed, or must match an existing parent-table primary key value.
- Gives a concrete example, such as an orders table referencing a customers table, and identifies which key is the foreign key.
- Explains that the DBMS rejects an insertion or update that would create an orphan record.
- Describes how a delete of a parent row is handled when child rows exist, such as blocking, cascading or setting the foreign key to null, and states that the design determines which applies.
- Defines a transaction as a sequence of database operations that succeeds or fails as one unit.
- Explains each ACID property separately, giving Atomicity, Consistency, Isolation and Durability their own meanings.
- Describes how record locking stops conflicting concurrent updates to the same record and links this to isolation.
- Explains redundancy as duplicated data or backup copies that support recovery and durability.
- Uses a banking or booking example to show why a partial update would leave data inconsistent.
Examiner Tips
- 💡Write a one-line definition for each of the eight terms before answering, then check each option against your definitions.
- 💡When an option names a key, ask which table holds that key and whether its values must be unique.
- 💡Eliminate options that describe a flat file as having several linked tables, because that describes a relational database.
- 💡Name each of the four methods and give one example before adding further detail.
- 💡Attach a specific example to every point, such as a sensor for capturing, rather than writing general statements.
- 💡Name a risk for each method and a control that reduces it, such as validation for capturing.
- 💡Write the primary key first, then test each non-key attribute against it.
- 💡Name the transitive dependency in your answer, for example CustomerID determines CustomerPostcode, so the examiner can see your reasoning.
- 💡Show the resulting tables with their primary keys and foreign keys underlined or labelled clearly.
- 💡Read the FROM clause first to confirm the table, then trace the WHERE condition row by row.
- 💡When modifying a statement, change only the part the new requirement affects and keep the rest of the statement unchanged.
- 💡Check that every column name you write appears in the question's table definition before you finish.
- 💡Name the parent table, the child table, the primary key and the foreign key in your answer so the relationship is unambiguous.
- 💡Use an orphan-record example rather than a general statement, because a concrete example shows exactly what the rule prevents.
- 💡Keep the three possible actions for deleting a parent row distinct, and say which one a given design would choose.
- 💡Name each ACID property and give its meaning in one sentence, so that no property is merged with another.
- 💡Use a concrete transaction example, such as a bank transfer, to show why the all-or-nothing rule matters.
- 💡When explaining record locking, state which transaction must wait and why, so your answer shows how isolation is preserved.
Common Mistakes
- Confuses a primary key with a foreign key; correct this by stating that a primary key is unique within its own table, while a foreign key references another table's primary key.
- Treats a secondary key as always unique; correct this by recognising that secondary keys support searching and need not be unique.
- Describes normalisation as a way to add more data to tables; correct this by stating that it removes redundancy and anomalies by splitting tables.
- Assumes indexing changes which records exist; correct this by stating that an index only speeds up retrieval and does not change the stored data.
- Treats selecting as the same as capturing; correct this by stating that selecting retrieves existing data while capturing adds new data to a system.
- Gives a method without an example; correct this by adding a concrete context, such as a barcode scanner at a supermarket checkout.
- Equates exchanging with local storage; correct this by stating that exchanging moves data between separate systems or users.
- Treats any repeated data as a 3NF violation without checking for a non-key determinant; test each non-key attribute against the primary key first.
- Removes the determinant from the new table, which loses the key needed to join the tables; keep it as the primary key of the new table.
- Skips the 1NF and 2NF checks and jumps straight to 3NF; check each normal form in order, because 3NF assumes 2NF has already been met.
- Assumes a dependency on the primary key is transitive; only a dependency between two non-key attributes breaks 3NF.
- Assumes a query without a WHERE clause returns only a filtered subset; read every row of the table unless a condition is given.
- Writes table or column names that are not in the supplied schema; use only the names given in the question.
- Confuses the purpose of INSERT, UPDATE and DELETE, applying one where another is needed; match each statement to the change the question requires.
- Changes the SELECT list when the requirement concerns which rows are returned; alter the WHERE condition instead.
- Confuses referential integrity with entity integrity, describing only the uniqueness of primary keys; correct by focusing on foreign keys matching parent-table primary keys.
- Describes referential integrity as checking that a value has the correct data type; correct by explaining that it concerns the links between tables.
- Assumes a foreign key may hold any value; correct by requiring a matching parent primary key, or null only where the design permits it.
- Treats Consistency as meaning the data is accurate in the real world; correct by defining it as the database moving between states that satisfy its integrity rules.
- Confuses Atomicity with Isolation, describing concurrent interference; correct by reserving Atomicity for all-or-nothing completion of a transaction.
- Claims redundancy always improves database design; correct by noting that normalisation reduces unnecessary duplication, while backups add controlled redundancy for recovery.