Fundamentals of databases — AQA A-Level Computer Science

Databases are fundamental to modern computing, enabling efficient storage, retrieval, and management of large volumes of structured data. In the AQA A-Level Computer Science specification, this topic covers the principles of database design, the relational model, and the use of Structured Query Language (SQL) to manipulate data. You will learn how to design a database schema using entity-relationship (ER) diagrams, normalise data to reduce redundancy, and implement queries using SQL. Understanding databases is crucial for developing data-driven applications and is a core component of the course, often examined through both theoretical questions and practical SQL tasks.

The relational database model organises data into tables (relations) with rows (tuples) and columns (attributes). Each table represents an entity, and relationships between entities are established through foreign keys. You will study the three normal forms (1NF, 2NF, 3NF) to ensure data integrity and eliminate anomalies. Additionally, you will learn about transactions, ACID properties (Atomicity, Consistency, Isolation, Durability), and the role of database management systems (DBMS). This knowledge is not only examinable but also directly applicable to real-world scenarios, such as designing a school's student records system or an e-commerce platform.

Mastering databases is essential for any aspiring software developer or data analyst. The topic builds on earlier concepts of data structures and algorithms, and it connects to later topics like big data and data mining. In the A-Level exam, you may be asked to interpret a scenario, draw an ER diagram, normalise a set of attributes, write SQL queries, or discuss the advantages of using a DBMS. A strong grasp of databases will help you secure high marks in both Paper 1 (practical programming) and Paper 2 (theoretical concepts).

• →Relational model: data organised into tables with rows and columns; primary keys uniquely identify each row; foreign keys link tables.
• →Normalisation: process of organising data to reduce redundancy and dependency, typically up to Third Normal Form (3NF).
• →SQL: standard language for querying and manipulating databases; includes SELECT, INSERT, UPDATE, DELETE, and JOIN operations.
• →Entity-Relationship (ER) diagrams: graphical representation of entities, attributes, and relationships (one-to-one, one-to-many, many-to-many).
• →ACID properties: ensure reliable processing of database transactions (Atomicity, Consistency, Isolation, Durability).

• Award credit for accurate SQL syntax, including correct capitalization of keywords, use of quotes for strings, and appropriate punctuation.
• When using JOIN, award credit for specifying the correct ON condition linking primary and foreign keys; penalize Cartesian products.
• In GROUP BY queries, require all non-aggregated columns in SELECT to appear in the GROUP BY clause; award credit for appropriate use of aggregate functions (e.g., COUNT, SUM, AVG).
• For HAVING, award credit when it is correctly applied to filter groups (not individual rows), and distinguish from WHERE usage.
• In data manipulation commands, award credit for WHERE clauses that accurately target specific rows; penalize unqualified DELETE/UPDATE that could cause data loss.

• 💡Always test your SQL queries on sample data before finalizing; predict the expected output to verify logic.
• 💡Remember the standard SQL execution order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.
• 💡When working with GROUP BY, double-check that every column in the SELECT list is either in the GROUP BY clause or an argument to an aggregate function.
• 💡Use aliases for table names in multi-table queries to improve readability and avoid ambiguity.
• 💡When writing SQL queries, always specify the columns you need instead of using SELECT * to show you understand projection and to avoid unnecessary data retrieval.
• 💡In ER diagrams, clearly label relationship cardinalities (1:1, 1:M, M:N) and ensure entities have appropriate attributes. Many-to-many relationships must be resolved into two one-to-many relationships using a linking table.
• 💡For normalisation questions, start by identifying the primary key and functional dependencies. Check for partial dependencies (2NF) and transitive dependencies (3NF). Show your working step by step to gain method marks.

• Confusing WHERE and HAVING: applying HAVING for row-level filters rather than group-level filters.
• Omitting GROUP BY for non-aggregated columns when using aggregate functions, leading to logical errors.
• Incorrect JOIN conditions resulting in Cartesian products or mismatches; forgetting the ON clause.
• Using DELETE or UPDATE without a WHERE clause, accidentally modifying or removing all rows.
• Misunderstanding the order of SQL clause execution, leading to illogical queries (e.g., referencing aliases in WHERE before they are defined).
• 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: Normalisation reduces redundancy but can increase the number of joins, which may slow down queries. Denormalisation is sometimes used for performance.
• Misconception: SQL is case-insensitive for everything. Correction: SQL keywords are case-insensitive, but string comparisons are case-sensitive in many DBMS (e.g., MySQL depends on collation).