Exam Board:
OCR A-Level
3.2 - Databases
Specification:
Computer Science H446
Watch on YouTube:
Database Keys & Relationships
Flat File & Relational Databases
Capturing & Managing Data
SQL
Normalisation
Referential Integrity & ACID
Database Keys & Relationships
A database is an organised collection of data stored in tables, where each table represents an entity such as a customer or product.
Each table is made up of records (rows), which store information about individual instances of that entity, and fields (columns), which hold specific attributes like names or prices.
A primary key uniquely identifies each record, while a foreign key links records between tables to create relationships. Secondary keys use indexing to speed up searches and data retrieval.
Relationships between entities are shown using an Entity–Relationship (ER) diagram, which illustrates one-to-one, one-to-many and many-to-many connections between tables.
Flat File & Relational Databases
A flat file database stores all data in a single table or file, often in a simple format such as a spreadsheet or text file. It is easy to create and manage, making it suitable for small-scale applications. However, it can lead to data duplication and inconsistency because the same data may be repeated in multiple records.
A relational database stores data in multiple related tables, linked together using primary and foreign keys. This design reduces data redundancy and improves data integrity by storing each piece of information only once. The main disadvantage is that relational databases are more complex to design and maintain, requiring more processing power and knowledge to manage effectively.
Capturing & Managing Data
Data can be captured from sources such as forms, Optical Character Recognition (OCR), Optical Mark Recognition (OMR) and sensors. These methods allow information to be collected automatically or manually and entered into a computer system for processing.
Data can be selected and filtered using tools like Query By Example (QBE) or SQL commands. This allows users to retrieve only the specific records that meet certain criteria from a database.
Data management involves manipulating stored information, such as performing arithmetic functions or adding, editing and deleting records. This ensures that the data remains accurate, up to date and useful for decision-making.
Data can be exchanged (shared) between systems using common file formats such as CSV and JSON. These formats make it easier to transfer data between different software applications or platforms while maintaining structure and meaning.
Database Normalisation
Database normalisation is the process of organising data in a database to reduce redundancy and improve data integrity. It ensures that each piece of data is stored only once, making updates and maintenance more efficient and reducing the risk of inconsistent data. Normalisation is measured in 'forms':
​
-
First Normal Form (1NF): Ensures that all fields contain atomic (indivisible) values and that there are no repeating groups in a table. Each table must have a primary key.
-
Second Normal Form (2NF): Builds on 1NF by ensuring that all non-key attributes depend on the entire primary key, removing partial dependencies.
-
Third Normal Form (3NF): Builds on 2NF by removing transitive dependencies, meaning non-key attributes depend only on the primary key and not on other non-key attributes.
Structured Query Language (SQL)
SQL (Structured Query Language) is used to create, manage and manipulate data in databases.
It allows users to retrieve specific data using commands such as SELECT, FROM and WHERE, often combined with logical operators like AND and OR to filter results. The LIKE command and wildcards such as * and % are used to search for patterns within text data.
SQL also supports data modification through commands like INSERT (to add data), DELETE (to remove data) and DROP (to delete entire tables).
More advanced queries can use nested SELECT statements or JOIN commands to combine data from multiple tables for more complex analysis.
Referential Integrity & ACID
Referential integrity ensures that relationships between tables in a relational database remain consistent. It means that a foreign key in one table must always refer to a valid primary key in another table.
​
Transaction processing refers to the handling of a sequence of database operations (a transaction) that must be completed fully or not at all. It ensures that databases remain accurate and consistent even if errors or system failures occur during the process.
​
The ACID rules define the key properties of reliable transactions:
-
Atomicity: The transaction is all or nothing - it either completes fully or not at all.
-
Consistency: The database must remain valid before and after the transaction.
-
Isolation: Transactions are independent, so one cannot interfere with another.
-
Durability: Once completed, a transaction’s changes are permanent, even if the system crashes.
Questo's Key Terms
Database Keys & Relationships: field, record, table, primary key, secondary key, indexing, foreign key, entity relation (ER) diagram, one-to-one, one-to-many, many-to-many, flat file database, relational database
​​​​​​
Capturing & Managing Data: form, OCR, OMR, sensor, query by example, SQL, CSV, JSON, electronic and non-electronic data transfer
​
SQL: SELECT, nested SELECT, FROM, WHERE, LIKE, AND, OR, DELETE, INSERT, DROP, JOIN, wildcards (* and %)
​
Normalisation: first normal form (1NF), second normal form (2NF), third normal form ( 3NF), partial dependency, non-key dependency
​
Referential Integrity & ACID: referential integrity, ransaction processing, ACID (atomicity, consistency, isolation, durability), record locking, redunancy
Did You Know?
In 2013 the web portal / search engine company Yahoo! was hacked, with all 3 billion user accounts in their database compromised - the largest hack in history. Personal details such as names, email addresses and hashed passwords were taken, causing major reputational and financial damage to the company.
