Welcome to Database Concepts!
Databases are the beating heart of modern software systems, from your favourite music streaming apps to school management portals and online banking. In your CCEA A2 1 examination, mastering database design, normalisation, and SQL will earn you a massive chunk of marks. Don't worry if these ideas seem intimidating at first—we will break down every single concept step-by-step so you can tackle both the pre-release case study and unseen exam questions with total confidence!
---1. Flat-File vs. Relational Databases
Before diving into complex systems, let's understand why modern computing moved away from simple flat-file storage to relational databases.
What is a Flat-File Database?
A flat-file database stores all data in a single table or text file (like a massive spreadsheet). While simple to set up for a single user, flat files create severe problems in multi-user systems:
• Data Redundancy: Information is duplicated across records (e.g., typing a customer's address every time they place an order).
• Update Anomalies: If an address changes, you must update every single record where it appears. Missing just one creates inconsistent data.
• Insertion Anomalies: You might not be able to record certain data without creating a blank or dummy record (e.g., you cannot add a new course until a student enrols).
• Deletion Anomalies: Deleting one piece of data might unintentionally destroy other vital information (e.g., deleting the only student on a course deletes the record of the course existing).
• Lack of Concurrency: Multiple users cannot safely read and write to the same file at the exact same time without file locking issues or corrupted data.
The Relational Model
Proposed by Edgar F. Codd, the relational model organises data into separate, linked tables. Instead of repeating information, we split data logically across tables and connect them using shared attributes.
Let's learn the formal relational terminology expected in your CCEA exams:
• Relation: A table made up of rows and columns.
• Tuple: A single row or record in a relation representing one instance of an entity.
• Attribute: A column or field representing a single property or piece of information.
• Cardinality: The number of tuples (rows) in a relation.
• Degree: The number of attributes (columns) in a relation.
Key Terms & Database Integrity
To keep our data orderly and ensure every record can be located, we use specialised keys:
• Primary Key (PK): A unique identifier for each tuple in a relation. A primary key must never be empty (it cannot contain a NULL value).
• Composite / Compound Key: A primary key made up of two or more attributes combined together to guarantee uniqueness when no single field can do so.
• Foreign Key (FK): An attribute in one table that references the Primary Key of another table, creating a relational link between them.
• Candidate / Secondary Key: An alternative attribute (or set of attributes) that uniquely identifies a record and could have been chosen as the primary key.
• Referential Integrity: A vital relational rule stating that a foreign key value in a child table must match an existing primary key value in the parent table (or be NULL where allowed). This prevents "orphan records" (e.g., an order linked to a customer ID that does not exist).
• Data Dictionary / Metadata: A centralised file containing data about data—such as table definitions, data types, validation rules, field lengths, and access permissions.
Section Takeaway: Relational databases eliminate redundancy and anomalies by splitting data into relations (tables) containing tuples (rows) and attributes (columns), linked securely using primary and foreign keys to uphold referential integrity.
---2. Data Modelling & Entity-Relationship (ER) Diagrams
An Entity is any real-world object, person, place, or concept about which data is stored (e.g., PATIENT, DOCTOR, APPOINTMENT). In exam diagrams, entities are written as singular nouns inside rectangles.
Relationship Cardinalities
Relationships show how entities interact. There are three cardinalities you need to know:
• One-to-One (\(1:1\)): One instance of Entity A relates to exactly one instance of Entity B (e.g., a COUNTRY has one CAPITAL_CITY).
• One-to-Many (\(1:M\)): One instance of Entity A relates to many instances of Entity B, but each B relates to only one A (e.g., one DEPARTMENT employs many EMPLOYEES). The foreign key is always placed in the table on the "Many" side.
• Many-to-Many (\(M:N\)): Many instances of Entity A relate to many instances of Entity B (e.g., a STUDENT studies many COURSES, and a COURSE contains many STUDENTS).
Resolving Many-to-Many (\(M:N\)) Relationships
Crucial Rule: A Many-to-Many (\(M:N\)) relationship cannot be implemented directly in a relational database because it causes severe data duplication.
To resolve an \(M:N\) relationship, we break it down into two One-to-Many (\(1:M\)) relationships by introducing an intermediate junction/linking entity:
1. Create the new linking entity (e.g., ENROLMENT).
2. Position the linking entity between the two original entities.
3. Point the "Many" ends towards the linking entity: STUDENT (\(1\)) --- (\(M\)) ENROLMENT (\(M\)) --- (\(1\)) COURSE.
4. The linking entity's primary key is typically a composite primary key composed of the foreign keys from both parent entities (e.g., StudentID + CourseID).
Section Takeaway: Always resolve \(M:N\) relationships into two \(1:M\) relationships using a junction table containing foreign keys from both parent tables.
---3. Data Normalisation (UNF to 3NF)
Normalisation is a formal, step-by-step mathematical process used to organise data into relations that minimise data redundancy and avoid anomalies. Let's walk through each stage carefully.
Memory Trick: Remember the courtroom oath for 3NF: "Every attribute must depend on the key, the whole key, and nothing but the key (so help me Codd)!"
Unnormalised Form (UNF)
A table is in UNF if it contains repeating groups, duplicate entries, or non-atomic values (e.g., a single cell containing multiple phone numbers or multiple items purchased on a single invoice).
First Normal Form (1NF)
To move from UNF to 1NF:
• Remove all repeating groups and ensure all data values are atomic (indivisible—one single value per cell).
• Identify a suitable primary key (often a composite key) to uniquely identify each tuple.
• Ensure there is a unique column name for each attribute and that all entries in any given column are of the same data type.
Second Normal Form (2NF)
To move from 1NF to 2NF:
• The relation must already be in 1NF.
• Remove all partial functional dependencies.
• What is a partial dependency? It occurs when a non-key attribute depends on only part of a composite primary key rather than the entire key.
• How to fix it: Split the table. Move the partially dependent attributes and the part of the primary key they depend on into a new separate relation.
Examiner Note: If a table is in 1NF and has a single-attribute primary key (not a composite key), it is automatically in 2NF because partial dependency is mathematically impossible without a composite key!
Third Normal Form (3NF)
To move from 2NF to 3NF:
• The relation must already be in 2NF.
• Remove all transitive dependencies.
• What is a transitive dependency? It occurs when a non-key attribute depends on another non-key attribute rather than directly on the primary key (in formal logic: \(X \to Y\) and \(Y \to Z\), therefore \(X \to Z\)). For example, if Postcode determines City, City is transitively dependent on the CustomerID primary key.
• How to fix it: Move the non-key determinant and its dependent attributes into a new relation. The determinant becomes the primary key in the new table and remains as a foreign key in the original table.
Quick Normalisation Summary
• 1NF: Atomic data, no repeating groups, primary key identified.
• 2NF: In 1NF + no partial functional dependencies (all non-key attributes fully depend on the entire PK).
• 3NF: In 2NF + no transitive dependencies (non-key attributes depend purely on the PK).
4. Structured Query Language (SQL)
SQL is divided into two main categories: Data Definition Language (DDL) for creating/modifying database structures, and Data Manipulation Language (DML) for querying and modifying the data inside those structures.
Data Definition Language (DDL)
Use DDL commands to define the relational schema and enforce constraints:
1. CREATE TABLE:
CREATE TABLE Student (
StudentID VARCHAR(10) NOT NULL,
FirstName VARCHAR(30) NOT NULL,
LastName VARCHAR(30) NOT NULL,
DateOfBirth DATE,
TutorID INT,
PRIMARY KEY (StudentID),
FOREIGN KEY (TutorID) REFERENCES Tutor(TutorID)
);
2. ALTER TABLE: (Modifies an existing table structure)
ALTER TABLE Student ADD Email VARCHAR(100);
ALTER TABLE Student DROP COLUMN DateOfBirth;
ALTER TABLE Student MODIFY FirstName VARCHAR(50);
3. DROP TABLE: (Deletes the table structure and all its data permanently)
DROP TABLE Student;
Data Manipulation Language (DML)
Use DML commands to view, add, change, and remove data records:
1. INSERT INTO: (Adds new tuples)
INSERT INTO Student (StudentID, FirstName, LastName, TutorID)
VALUES ('S101', 'Sarah', 'Connor', 4);
2. UPDATE: (Modifies existing records)
UPDATE Student
SET TutorID = 5
WHERE StudentID = 'S101';
Warning: Forgetting the WHERE clause updates every single row in the table!
3. DELETE FROM: (Removes existing records)
DELETE FROM Student
WHERE StudentID = 'S101';
4. SELECT Queries: (Retrieving data)
The general structure of a full SQL query must follow this exact order:
SELECT [DISTINCT] column1, AggregateFunction(column2)
FROM TableA
INNER JOIN TableB ON TableA.PK = TableB.FK
WHERE condition
GROUP BY column1
HAVING aggregate_condition
ORDER BY column1 [ASC | DESC];
• Aggregate Functions: COUNT(), SUM(), AVG(), MIN(), MAX().
• WHERE vs. HAVING: Use WHERE to filter individual records before grouping. Use HAVING to filter summary groups after the GROUP BY calculation (e.g., HAVING COUNT(StudentID) > 5).
5. Transaction Processing & ACID Properties
A Transaction is a single logical unit of work made up of one or more database operations (e.g., transferring £50 from Account A to Account B requires deducting £50 from A and adding £50 to B). Both steps must succeed together, or the database must revert to its original state.
The ACID Properties
To guarantee reliability and integrity, every transaction must satisfy the four ACID rules:
• Atomicity: The "all-or-nothing" rule. A transaction cannot be partially completed. If any individual operation fails, the entire transaction is aborted and rolled back to the starting point.
• Consistency: A transaction must transition the database from one valid state to another valid state, preserving all schema rules, constraints, and referential integrity.
• Isolation: Ensures that concurrent transactions execute independently without interfering with one another. Intermediate results of an ongoing transaction are hidden from other operations until committed.
• Durability: Once a transaction has been successfully committed, its changes are permanently recorded and will survive any subsequent system crash or power failure.
Concurrency Control & Deadlocks
When multiple users access a database simultaneously, we must prevent race conditions (where two transactions overwrite each other's data):
• Shared Lock (Read Lock): Allows multiple users to read the data, but no one can modify it while the lock is active.
• Exclusive Lock (Write Lock): Gives a single transaction complete control over the record or table. No other transaction can read or write until the lock is released.
• Deadlock: A critical state where Transaction 1 holds a lock on Resource A and waits for Resource B, while Transaction 2 holds a lock on Resource B and waits for Resource A. Neither transaction can proceed.
• Resolving Deadlocks: The Database Management System (DBMS) resolves deadlocks using timeout mechanisms, deadlock detection algorithms, and forcibly aborting/rolling back one of the transactions.
Section Takeaway: ACID guarantees transaction safety. Concurrency locks prevent data corruption, while deadlock handling prevents system freezes during multi-user operations.
---6. Top Exam Tips & Common Pitfalls to Avoid
Avoid these frequent mistakes identified in CCEA examiner reports:
• Vague Normalisation Definitions: Never say "2NF removes duplicates" or "3NF cleans the data". Use the exact technical definitions: 2NF removes partial functional dependencies on composite keys; 3NF removes transitive dependencies.
• Single vs. Composite Keys in 2NF: Always check the primary key in 1NF. If it consists of just one column, state clearly that it is already in 2NF because partial dependencies cannot exist without a composite key.
• WHERE vs. HAVING Mistakes: Never put aggregate functions inside a WHERE clause (e.g., WHERE COUNT(*) > 2 is invalid). Always use HAVING when filtering aggregated groups.
• Resolving \(M:N\) Relationships: In ER diagram questions, remember to remove the original direct link and add the intermediate linking entity with the correct foreign keys.
• Apply to Case Study Rules: Always read the pre-release case study scenario carefully. Ensure your table attributes and data types reflect the specific business requirements given in the exam paper.