Skip to topic
    ← Back to course topics

    Databases — OCR A-Level Computer Science

    Test yourself on Databases with OCR A-Level practice questions.

    Start free

    7 days Premium · Then free forever · No card, no charge

    Databases explained

    This topic covers the fundamental principles of relational databases, including entity-relationship modelling, normalisation to 3NF, and the use of primary, foreign, and secondary keys.

    Read the full explanation

    It also explores methods for data management, transaction processing (ACID properties), and the interpretation and modification of SQL queries.

    What to demonstrate

    1. Definition and purpose of relational databases, flat files, and keys (primary, foreign, secondary).
    2. Ability to construct and interpret entity-relationship diagrams.
    3. Application of normalisation rules up to 3rd Normal Form (3NF).
    Show all 6 objectives
    1. Understanding of ACID properties (Atomicity, Consistency, Isolation, Durability) in transaction processing.
    2. Interpretation and modification of SQL statements (SELECT, FROM, WHERE, LIKE, AND, OR, DELETE, INSERT, DROP, JOIN).
    3. Understanding of record locking and data redundancy.

    Databases exam tips

    Topic Overview

    Databases are fundamental to modern computing, enabling efficient storage, retrieval, and management of large volumes of structured data. In the OCR A-Level Computer Science specification, this topic covers the principles of database design, the relational model, SQL, and the implications of using databases in real-world applications. You'll learn how to design normalised databases to eliminate redundancy and maintain data integrity, and how to query them using Structured Query Language (SQL). Understanding databases is crucial for developing data-driven applications and is a core component of the 'Data Structures and Algorithms' and 'Software Development' modules.

    The relational database model organises data into tables (relations) with rows (tuples) and columns (attributes). Keys such as primary keys and foreign keys establish relationships between tables, ensuring referential integrity. Normalisation, typically up to Third Normal Form (3NF), is a systematic process to reduce data redundancy and avoid update anomalies. You'll also explore transaction management, including ACID properties (Atomicity, Consistency, Isolation, Durability), and the role of database management systems (DBMS) in concurrency control and recovery.

    Databases are everywhere—from banking systems to social media platforms. Mastering this topic not only prepares you for exams but also equips you with skills for further study or careers in software engineering, data science, and IT. The OCR exam often includes questions on designing a database from a scenario, writing SQL queries, and explaining normalisation steps. A solid grasp of databases will also help you in the non-exam assessment (NEA) where you may implement a database-backed application.

    Key Concepts
    • →Relational model: tables, tuples, attributes, primary keys, foreign keys, and relationships (one-to-one, one-to-many, many-to-many).
    • →Normalisation: 1NF (atomic values, no repeating groups), 2NF (no partial dependencies), 3NF (no transitive dependencies).
    • →SQL: Data Definition Language (CREATE, ALTER, DROP) and Data Manipulation Language (SELECT, INSERT, UPDATE, DELETE) with JOINs, GROUP BY, and subqueries.
    • →ACID properties: Atomicity (transactions are all-or-nothing), Consistency (database remains valid), Isolation (concurrent transactions don't interfere), Durability (committed changes persist).
    • →Entity-Relationship (ER) modelling: entities, attributes, relationships, and cardinality constraints.
    Marking Points
    • Definition and purpose of relational databases, flat files, and keys (primary, foreign, secondary).
    • Ability to construct and interpret entity-relationship diagrams.
    • Application of normalisation rules up to 3rd Normal Form (3NF).
    • Understanding of ACID properties (Atomicity, Consistency, Isolation, Durability) in transaction processing.
    • Interpretation and modification of SQL statements (SELECT, FROM, WHERE, LIKE, AND, OR, DELETE, INSERT, DROP, JOIN).
    • Understanding of record locking and data redundancy.
    Examiner Tips
    • 💡Practice drawing entity-relationship diagrams using standard notation.
    • 💡Ensure you can explain the 'why' behind normalisation (e.g., reducing redundancy).
    • 💡Be prepared to write or correct SQL queries based on provided table schemas.
    • 💡Memorise the ACID acronym and be able to explain each component in the context of a database transaction.
    • 💡Use the provided SQL syntax guide in the specification to ensure your query structure is accurate.
    • 💡When designing a database from a scenario, always identify entities first, then attributes, then relationships. Use ER diagrams to visualise before creating tables. Examiners look for clear primary and foreign key definitions.
    • 💡In SQL questions, write queries step-by-step: start with SELECT and FROM, then add WHERE, JOINs, GROUP BY, and HAVING. Use aliases for readability. Test your query mentally with sample data.
    • 💡For normalisation questions, state the normal form at each step and justify why the table is in that form (e.g., 'No repeating groups, so 1NF'). Show the decomposition process clearly.
    Common Mistakes
    • Confusing primary keys with foreign keys or secondary keys.
    • Failing to correctly identify the functional dependencies required for 3NF.
    • Misinterpreting the scope of SQL JOIN operations.
    • Incorrectly applying ACID properties to non-transactional database scenarios.
    • Overlooking the impact of record locking on system performance.
    • Misconception: A primary key must always be a single attribute. Correction: A primary key can be composite (multiple attributes) if no single attribute uniquely identifies a row.
    • Misconception: Normalisation always improves performance. Correction: Normalisation reduces redundancy but can increase the number of joins, potentially slowing queries. Denormalisation may be used for performance in read-heavy systems.
    • Misconception: SQL is case-insensitive for everything. Correction: While SQL keywords are case-insensitive, string comparisons are case-sensitive in many DBMS (e.g., MySQL depends on collation).
    Frequently Asked Questions
    What is the difference between a primary key and a foreign key?
    A primary key uniquely identifies each record in a table; it must contain unique values and cannot be NULL. A foreign key is a field in one table that refers to the primary key of another table, establishing a link between the two tables. Foreign keys enforce referential integrity, ensuring that values in the foreign key column correspond to existing primary key values in the referenced table.
    How do I normalise a database to 3NF?
    Start with unnormalised data. To achieve 1NF, ensure each cell contains a single atomic value and eliminate repeating groups (e.g., create separate rows). For 2NF, remove partial dependencies: if a table has a composite primary key, any non-key attribute must depend on the whole key, not just part of it. For 3NF, remove transitive dependencies: non-key attributes should depend only on the primary key, not on other non-key attributes. Each step involves decomposing tables to eliminate redundancy.
    What is an SQL JOIN and how do I use it?
    A JOIN combines rows from two or more tables based on a related column. The most common is INNER JOIN, which returns only rows with matching values in both tables. For example: SELECT * FROM Students INNER JOIN Enrolments ON Students.StudentID = Enrolments.StudentID;. Other types include LEFT JOIN (all rows from left table, matching from right) and RIGHT JOIN. Use JOINs to retrieve data spread across multiple tables in a normalised database.
    What are ACID properties in databases?
    ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity ensures a transaction is fully completed or fully rolled back. Consistency guarantees that a transaction brings the database from one valid state to another, preserving integrity constraints. Isolation means concurrent transactions do not interfere with each other, appearing as if executed sequentially. Durability ensures that once a transaction is committed, its changes persist even after a system failure.
    Why is normalisation important?
    Normalisation reduces data redundancy and prevents update anomalies (insertion, deletion, modification anomalies). For example, without normalisation, updating a customer's address in one row but not others leads to inconsistency. Normalisation also improves data integrity and makes the database more flexible for queries. However, it can increase the number of tables and joins, which may affect performance, so a balance is sometimes needed.
    What is an entity-relationship diagram (ERD)?
    An ERD is a visual representation of entities (tables), their attributes, and the relationships between them. Entities are shown as rectangles, attributes as ovals, and relationships as diamonds. Cardinality (e.g., one-to-many) is indicated by lines and crow's foot notation. ERDs help in designing a database schema before implementation, ensuring all data requirements are captured and relationships are correctly defined.

    More OCR A-Level Computer Science topics

    See every OCR A-Level Computer Science topic

    Start free

    Cookies on MasteryMind

    Essential cookies keep you signed in.

    Analytics and advertising cookies stay off unless you accept. Read our Privacy Policy.