Exam Board:
OCR A-Level
3.2 - Databases
Specification:
Computer Science H446
Database Key Terms
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.
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.
ER Diagrams
Relationships between entities are shown using an Entity–Relationship (ER) diagram, which illustrates connections between tables in a visual format. There are three types:
many-to-many
(e.g. Students to Teachers)
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.
Flat File & Relational Databases
one-to-one
(e.g. Students to Lockers)
one-to-many
(e.g. Tutor Group to Students)
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
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.
​​
Referential integrity is important as it:
-
Prevents orphan records (e.g. doctors linked to a non-existent patient).
-
Keeps relationships between tables valid.
-
Ensures queries and reports remain accurate.
ACID Rules
​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 by following the same rules.
-
Isolation: Transactions are independent, so one cannot interfere with another.
-
Durability: Once completed, a transaction’s changes are permanent, even if the system crashes.
Watch on YouTube:
Database key terms
Flat File & Relational databases
Entity relationship (ER) diagrams
Capturing & managing data
Database normalisation
SQL
Referential integrity
Transaction processing (ACID)
Questo's Key Terms
Database Keys & Relationships: field, record, table, primary key, composite key, secondary key, indexing, foreign key, entity relationship (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, *, %
​
Normalisation: first normal form (1NF), second normal form (2NF), third normal form ( 3NF), partial dependency, non-key dependency
​
Referential Integrity & ACID: referential integrity, transaction processing, ACID (atomicity, consistency, isolation, durability), record locking, redundancy
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.
