Welcome to Databases (A2 1: Information Systems)
Welcome to one of the most practical and scoring topics in your CCEA A2 Digital Technology course: Databases. Whether you are browsing social media, streaming your favourite show, or checking your exam timetable, a database is working silently behind the scenes. In this unit, we will explore how databases store, organise, and protect data efficiently. Don't worry if concepts like normalisation or entity-relationship modelling sound intimidating at first — we will break everything down into clear, bite-sized steps with relatable everyday examples!
1. Flat Files vs Relational Databases
Before relational databases were invented, organisations stored their data in flat files (such as simple text files or single spreadsheets). Let's look at why modern information systems moved away from flat files to relational databases.
What is a Flat File?
A flat file database stores all data in a single table or text file. Think of a giant spreadsheet where every single detail about students, teachers, classes, and grades is written on one long row.
Problems with Flat Files:
1. Data Redundancy: Unnecessary duplication of data. For example, if a student takes four subjects, their name, home address, and date of birth are typed out four separate times.
2. Data Inconsistency (Anomalies): Because data is duplicated, updating an address in one row might be forgotten in another row. This means the system holds conflicting versions of the truth.
3. Lack of Data Independence: The structure of the data file is tightly tied to the application program. If you change the file layout, you must rewrite the software that reads it.
4. Poor Data Security: It is difficult to give users partial access. Either a user can see the whole spreadsheet file, or they cannot see anything at all.
The Solution: Relational Databases
A relational database splits data into separate, specialised tables (entities) that are linked together using common fields called keys. For example, student details live in a Student table, and course details live in a Course table.
Advantages of Relational Databases:
• Reduced Data Redundancy: Data is recorded in one place only.
• High Data Integrity & Consistency: Changes made in one table automatically reflect across the system.
• Program-Data Independence: The data storage structure can be altered without breaking user applications.
• Granular Security: Permissions can be assigned per table, record, or field.
Key Takeaway: Flat files duplicate data and lead to errors; relational databases use linked tables to keep data accurate, secure, and compact.
2. Database Terminology & Keys
To master this topic, you need to speak the language of database designers. Here is your quick-reference glossary:
• Entity: A real-world object, person, or concept about which data is stored (e.g., Student, Product, Doctor). An entity becomes a table.
• Attribute: A specific characteristic or property of an entity (e.g., FirstName, DateOfBirth, Price). An attribute becomes a field/column.
• Tuple / Record: A single row in a table containing all the attributes for one specific instance of an entity.
• Domain: The permissible set of values an attribute can hold (e.g., a grade domain might be limited to A*, A, B, C, D, E, U).
Understanding Database Keys
Keys are the secret sauce that make relational databases work. Here are the types you need to know for your exam:
1. Primary Key: A unique identifier for every record in a table. It cannot contain null (blank) values. Example: StudentID or NationalInsuranceNumber.
2. Candidate Key: Any attribute (or combination of attributes) that could uniquely identify a record. The designer selects one candidate key to become the Primary Key.
3. Composite Key (Compound Key): A primary key made up of two or more attributes combined together when no single attribute is unique on its own. Example: Combining StudentID + ModuleCode to identify an enrolment.
4. Foreign Key: An attribute in one table that is the Primary Key in another table. It forms the link (relationship) between the two tables.
5. Secondary / Alternative Key: An index or candidate key not chosen as the primary key, but used to search and sort records quickly (e.g., searching by Surname or Postcode).
Memory Trick: Think of a Foreign Key as an ambassador visiting from another country (table) — it represents its home table inside a new one!
3. Entity-Relationship (ER) Modelling
An Entity-Relationship Diagram (ERD) visually shows how different entities connect with one another. Relationships describe the business rules of the system.
The Three Types of Relationships:
1. One-to-One (1:1): One record in Table A connects to exactly one record in Table B.
Example: Each Country has one Capital City; each Capital City belongs to one Country.
2. One-to-Many (1:M): One record in Table A connects to multiple records in Table B, but each record in Table B connects back to only one in Table A.
Example: One School Class has many Students; each Student belongs to only one School Class.
3. Many-to-Many (M:N): Multiple records in Table A connect to multiple records in Table B.
Example: A Student studies many Subjects; a Subject is studied by many Students.
Resolving Many-to-Many (M:N) Relationships
Crucial Exam Rule: Relational databases cannot directly implement Many-to-Many relationships because doing so creates massive data repetition and linking errors.
To resolve an M:N relationship, we create a middle table called a Junction Table (or Link Entity).
• The original M:N relationship splits into two separate 1:M relationships pointing towards the new junction table.
• The junction table typically takes the Primary Keys from both original tables and combines them into a Composite Primary Key.
Example: Student (1) ——< (M) Enrolment (M) >—— (1) Subject
Key Takeaway: Whenever you see a Many-to-Many relationship on an exam paper, resolve it immediately by placing a link table in the middle!
4. Normalisation
Normalisation is a formal, step-by-step mathematical technique used to organise data into tables. Its goal is to eliminate data redundancy and prevent insert, update, and delete anomalies.
The Three Normal Forms (UNF to 3NF)
Step 0: Unnormalised Form (UNF)
Data exists in a raw state containing repeating groups (multiple values inside a single cell or repeating columns for the same data).
Step 1: First Normal Form (1NF)
A table is in 1NF if:
• There are no repeating groups or repeating attributes.
• All data values are atomic (indivisible — e.g., "John Smith" is split into "John" and "Smith").
• A unique Primary Key (or Composite Key) has been identified.
How to achieve 1NF: Separate repeating groups into their own rows or new records so each cell contains exactly one single piece of data.
Step 2: Second Normal Form (2NF)
A table is in 2NF if:
• It is already in 1NF.
• It contains no partial key dependencies.
What is a Partial Dependency? In a table with a Composite Primary Key (e.g., StudentID + CourseID), every non-key attribute must depend on the whole key, not just part of it. If CourseName depends only on CourseID and not on StudentID, it is a partial dependency and violates 2NF!
How to achieve 2NF: Move the partially dependent attributes into a new table along with the part of the primary key they depend on.
Step 3: Third Normal Form (3NF)
A table is in 3NF if:
• It is already in 2NF.
• It contains no non-key (transitive) dependencies.
What is a Transitive Dependency? A non-key attribute depends on another non-key attribute instead of the Primary Key. For example, if a table has StudentID (Primary Key), DoctorID, and DoctorPhoneNumber: the phone number depends on the doctor, not directly on the student!
How to achieve 3NF: Remove transitive attributes into a separate entity where the determinant attribute becomes the primary key.
The Famous Normalisation Mnemonic:
"Every non-key attribute must depend on the key (1NF), the whole key (2NF), and nothing but the key (3NF), so help me Codd!" (Named after Edgar F. Codd, the inventor of relational databases).
Key Takeaway: 1NF removes repeating groups; 2NF removes partial key dependencies; 3NF removes transitive (non-key) dependencies.
5. Database Management Systems (DBMS)
A Database Management System (DBMS) is the systems software that sits between the database files and the users/applications. Examples include Microsoft Access, MySQL, Oracle, and Microsoft SQL Server.
Core Features & Tools of a DBMS:
1. Data Dictionary: An internal catalog (metadata — "data about data") storing definitions of tables, field names, data types, field lengths, validation rules, primary/foreign keys, and access rights.
2. Query Processor: Interprets user search requests (often written in SQL) and retrieves matching records efficiently.
3. Form and Report Generators: Creates user-friendly input screens (forms) and structured printable outputs (reports).
4. Security & Access Control: Manages user logins, passwords, encryption, and permission levels (read, write, delete).
5. Backup and Recovery Tools: Automates regular copies of data and provides rollback mechanisms in case of system crashes.
Structured Query Language (SQL)
SQL is the standard programming language used to communicate with relational databases. It is split into two main subsets:
A. Data Definition Language (DDL): Commands that define and modify the structure/schema of the database.
• CREATE TABLE — creates a new table.
• ALTER TABLE — adds, deletes, or modifies columns in an existing table.
• DROP TABLE — permanently deletes an entire table.
B. Data Manipulation Language (DML): Commands used to retrieve, insert, and modify actual data rows.
• SELECT — extracts data from a database.
• INSERT INTO — adds new records.
• UPDATE — modifies existing data values.
• DELETE — removes specific records.
Basic SQL Syntax Example:
To find the first names and surnames of all students older than 16, sorted alphabetically by surname:
SELECT FirstName, Surname
FROM tblStudent
WHERE Age > 16
ORDER BY Surname ASC;
Key Takeaway: DDL builds the structural frame of the house; DML arranges and moves the furniture inside.
6. Database Integrity, Security & Concurrency
When multiple users access a database at the same time (e.g., booking concert tickets online), the DBMS must ensure the data remains consistent and secure.
1. Data Integrity
Data integrity refers to the accuracy, completeness, and consistency of stored data.
• Entity Integrity: No primary key attribute can be null, ensuring every record is distinctly identifiable.
• Referential Integrity: Ensures that foreign key values always match an existing primary key in the parent table. You cannot have an order assigned to a CustomerID that does not exist!
2. Transaction Processing & ACID Properties
A transaction is a single logical unit of work (e.g., transferring £50 from Account A to Account B requires subtracting from A and adding to B). To maintain integrity, all transactions must obey the ACID rules:
• Atomicity: The transaction is "all or nothing". If any part fails, the entire transaction is rolled back.
• Consistency: The transaction takes the database from one valid state to another, never violating any validation rules or constraints.
• Isolation: Concurrent transactions execute without interfering with one another as if they occurred sequentially.
• Durability: Once a transaction commits successfully, its changes are permanently recorded and will survive any future system crashes.
3. Concurrent Access & Record Locking
When two users attempt to update the exact same record simultaneously, a clash occurs (the lost update problem).
To prevent this, the DBMS uses Record Locking:
• While User 1 is editing a record, the system locks it.
• User 2 is given "read-only" access or made to wait until User 1 finishes and commits the changes.
• Deadlock Warning: If User 1 locks Record A and waits for Record B, while User 2 locks Record B and waits for Record A, a deadlock occurs. The DBMS must detect this and abort one of the transactions.
4. Database Views & Security
A View is a virtual table generated from a query. It allows the database administrator to present only the relevant fields to specific users while hiding sensitive data (e.g., a receptionist can view patient names and appointments, but medical records and financial data remain hidden).
Key Takeaway: ACID properties and record locking prevent data corruption during multi-user operations, while views and referential integrity maintain security and logical accuracy.
Quick Exam Revision Checklist
Before sitting your exam, make sure you can confidently:
✓ State the differences and benefits of relational databases over flat files.
✓ Define primary, candidate, composite, secondary, and foreign keys.
✓ Draw and interpret ER diagrams, including resolving M:N relationships with junction tables.
✓ Step through normalisation from UNF up to 3NF and explain why each step is taken.
✓ Identify DDL vs DML commands and write basic SQL queries.
✓ Explain the role of the Data Dictionary, ACID transaction rules, and Record Locking.