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 the principles of relational database design, including tables, relationships, keys, and normalisation. You'll learn how to construct and execute SQL queries to extract meaningful information, ensuring data integrity and avoiding redundancy. Understanding databases is essential for any software system that handles persistent data, from simple apps to large-scale enterprise solutions.

Why does this matter? In an era of big data, the ability to design robust databases and query them effectively is a highly sought-after skill. This topic directly links to real-world applications such as e-commerce platforms, social media networks, and banking systems. Moreover, it builds on foundational concepts from earlier study, such as data types and file handling, and prepares you for more advanced topics like transaction processing and database security. Mastery of databases and SQL is not just about passing exams—it's about understanding how the digital world organises and uses information.

Within the wider A-Level curriculum, databases intersect with other key areas: they provide the persistent storage layer for programming projects, rely on logical reasoning for query optimisation, and raise ethical considerations around data privacy. By the end of this topic, you should be able to design a normalised database from a given scenario, write complex SQL queries involving joins and subqueries, and appreciate the trade-offs involved in database design decisions.

• →Relational model: tables (relations), rows (tuples), columns (attributes), primary keys, foreign keys, and referential integrity.
• →Normalisation: 1NF, 2NF, 3NF—eliminating redundancy and ensuring data dependencies make sense.
• →SQL DML: SELECT, INSERT, UPDATE, DELETE with WHERE, ORDER BY, GROUP BY, HAVING, and aggregate functions (COUNT, SUM, AVG, MIN, MAX).
• →SQL DDL: CREATE TABLE, ALTER TABLE, DROP TABLE, and constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE).
• →Joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN—combining data from multiple tables based on related columns.

• Award credit for correctly implementing CREATE TABLE with appropriate data types, primary keys, and foreign key constraints to enforce referential integrity.
• Award credit for accurately constructing SELECT statements that use multiple conditions, aggregate functions, or sorting and grouping (though not specified in objectives, it's typical) and specifically for correctly applying INNER, LEFT, and RIGHT JOINs to retrieve related data from multiple tables.
• Award credit for demonstrating proper use of subqueries, including correlated subqueries, and for selecting the correct operator (e.g., IN, EXISTS, =) depending on whether the subquery returns single or multiple rows.

• 💡Always check the row count and sample results after executing DML statements, especially when using joins, to ensure that the correct records are being combined and no unintended duplicates appear.
• 💡Use explicit JOIN syntax (e.g., INNER JOIN, LEFT JOIN) rather than implicit comma-separated table lists, as it makes the query logic clearer and helps avoid accidental cross joins.
• 💡When writing DDL, double-check the order of table creation to respect foreign key dependencies, and consider using ALTER to add constraints after creating tables if needed to avoid ‘table does not exist’ errors.
• 💡Always check your SQL syntax carefully—missing commas or incorrect quotation marks lose easy marks. Practice writing queries by hand to simulate exam conditions.
• 💡When normalising, start by identifying all attributes and functional dependencies. Then apply the normal forms step-by-step; examiners reward clear working even if the final design is slightly off.
• 💡For join questions, draw a quick diagram of the tables and their relationships. This helps you visualise which columns to link and avoids missing rows due to incorrect join types.

• Confusing the ON clause with the WHERE clause when specifying join conditions, leading to Cartesian products or incorrect filtering.
• Forgetting to include a WHERE condition in UPDATE or DELETE statements, which results in unintentional modification or deletion of all rows in the table.
• Using single-row comparison operators (e.g., =, >) with subqueries that return multiple rows, causing runtime errors; students should use IN, ANY, or ALL where appropriate.
• Misconception: A primary key must always be a single column. Correction: A primary key can be composite (multiple columns) as long as the combination is unique and non-null.
• Misconception: Normalisation always improves performance. Correction: While normalisation reduces redundancy, it can increase the number of joins, potentially slowing queries. Denormalisation is sometimes used for performance reasons.
• Misconception: NULL means zero or empty string. Correction: NULL represents missing or unknown data; it is not equal to any value, including itself. Use IS NULL or IS NOT NULL in SQL, not = NULL.