Databases and SQL — CCEA A-Level Computer Science

Databases and SQL form the backbone of modern data management, enabling efficient storage, retrieval, and manipulation of structured data. In the CCEA A-Level Computer Science specification, this topic covers relational database theory, normalisation, and the practical use of Structured Query Language (SQL). You will learn how to design tables with appropriate primary and foreign keys to maintain data integrity, and how to write SQL queries to extract meaningful information. Understanding databases is crucial for any software developer, data analyst, or systems architect, as nearly every application relies on a database backend.

This topic builds on earlier concepts of data representation and file handling, but introduces a formal approach to data organisation. You will explore the principles of normalisation up to Third Normal Form (3NF) to eliminate redundancy and avoid update anomalies. SQL is the standard language for interacting with relational databases, and you will master commands such as SELECT, INSERT, UPDATE, DELETE, JOIN, and subqueries. The ability to design a normalised database and query it efficiently is a key skill assessed in both written exams and practical programming tasks.

Mastering databases and SQL not only prepares you for the CCEA A-Level exam but also equips you with industry-relevant skills. In the wider subject of Computer Science, databases intersect with topics like data structures, algorithms, and system design. A solid grasp of this area will help you understand how large-scale systems manage data, from social media platforms to banking systems. By the end of this topic, you should be able to design a database from a given scenario, normalise it to 3NF, and write complex SQL queries to answer business questions.

• →Relational database structure: tables, rows (records), columns (fields), primary keys, foreign keys, and relationships (one-to-one, one-to-many, many-to-many).
• →Normalisation: the process of organising data to reduce redundancy and dependency, typically up to Third Normal Form (1NF, 2NF, 3NF).
• →SQL commands: DDL (CREATE, ALTER, DROP) and DML (SELECT, INSERT, UPDATE, DELETE), including JOINs (INNER, LEFT, RIGHT) and subqueries.
• →Data integrity: entity integrity (primary key uniqueness), referential integrity (foreign key constraints), and domain integrity (data types and constraints).
• →Transaction management: ACID properties (Atomicity, Consistency, Isolation, Durability) and the use of COMMIT and ROLLBACK.

• Award credit for accurately defining a table as a relation composed of tuples and attributes, with clear distinction between the structural and data components.
• Demonstrate understanding of primary keys as unique identifiers for tuples and foreign keys as attributes that establish links between tables, enforced by referential integrity.
• Show ability to identify and eliminate data redundancy by normalizing to 3NF, including explicit explanation of how 1NF, 2NF, and 3NF conditions are met and any anomalies resolved.

• 💡When defining keys, always specify the scope: a primary key uniquely identifies tuples within a table, while a foreign key references a primary key in a related table; use clear examples.
• 💡For normalization questions, systematically decompose: first list all functional dependencies, then transform to 1NF by ensuring atomic values, then remove partial dependencies for 2NF, and finally eliminate transitive dependencies for 3NF, annotating each step.
• 💡To maximize marks, explicitly state how each normal form condition is satisfied and which anomalies (insertion, deletion, update) are prevented, linking theory to practical database design scenarios.
• 💡When writing SQL queries, always specify the columns you need instead of using SELECT * — this shows precision and can improve performance. Examiners look for efficient queries.
• 💡For normalisation questions, work step by step: identify the primary key, check for partial dependencies (2NF), then transitive dependencies (3NF). Show your working clearly to gain method marks.
• 💡Practice writing JOINs with aliases (e.g., FROM Students s JOIN Enrolments e ON s.studentID = e.studentID). This makes queries more readable and is a sign of good style.

• Confusing primary key and foreign key roles, e.g., assuming a foreign key must be unique or that a primary key can be null.
• Incorrectly applying normalization rules, such as failing to remove partial dependencies in 2NF or leaving transitive dependencies in 3NF, often due to misidentifying functional dependencies.
• Misconceiving that normalization is about splitting tables arbitrarily rather than methodically addressing specific dependency problems.
• Misconception: A primary key can be NULL. Correction: A primary key must be unique and NOT NULL; it uniquely identifies each record.
• Misconception: Normalisation always improves performance. Correction: While normalisation reduces redundancy, it can increase the number of JOINs, which may slow down queries. Denormalisation is sometimes used for performance reasons.
• Misconception: SQL is case-insensitive for everything. Correction: SQL keywords are case-insensitive, but string comparisons are case-sensitive in many databases (e.g., 'Smith' != 'smith' unless using a case-insensitive collation).