OCR A Level Computer Your notes

Science
3.2 Databases
Contents
Relational Databases
Data Normalisation
Entity Relationship Diagrams
Capturing, Selecting, Managing & Exchanging Data
SQL
Referential Integrity
Transaction Processing

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 1
 Relational Databases
Your notes

Relational Databases
What is a database?
A database is an organised collection of data
It allows easy storage, retrieval, and management of information
Electronic databases offer a number of key benefits:
Easier to add, delete, modify and update data
Data can be backed up and copied easier
Multiple users, from multiple locations, can access the same database at the same
time

Database terminology
Term Definition

Field A single piece of data in a record

Record A group of related fields, representing one data entry

Table A collection of records with a similar structure

Primary key A unique identifier for each record in a table. Usually an ID

number

Compound primary A combination of (2 or more) fields that is unique for all records
key
(sometimes called
composite)

Foreign key A field in a table that refers to the primary key in another table.
Used to link tables and create relationships

Secondary key A field or fields that are indexed for faster searching

Database Software used to manage databases. Examples include

Management System MySQL, Oracle, Microsoft SQL Server, PostgreSQL
(DBMS)

Primary, Foreign & Secondary Keys

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 2
Visualising a database
Your notes

This database has 2 tables : orders and customers

Fields:
In the orders table are order_id, product, total and customer_id
In the customers table are customer_id, first_name, last_name, phone and country
A record would be 1 complete row in either table
order_id is the primary key for the orders table
customer_id is the primary key for the customers table
In the orders table customer_id is a foreign key - it links an order back to the customer
that made the order in the customer table

Indexing (secondary keys)

Indexing is a technique used to speed up data retrieval in a database
It works in a similar way to the index in a book
If a student had a maths book and wanted to find the section on factorising, they could
start at the first page and look at each page in turn until they found the section they
wanted

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 3
 But this would be slow, so it is better to look in the index to find where the factorising
section is and just go directly to it
Your notes
Likewise in a database, certain columns can be indexed so that the DBMS does not have
to look at every single record during a search and can just go to the relevant records
directly
This can greatly speed up searches
Fields that are indexed are known as secondary keys

Flat File vs Relational Database

A flat file database is one that stores all data in a single table
It is simple and easy to understand but causes data redundancy, inefficient storage and
is harder to maintain
A relational database is one that organises data into multiple tables
It uses keys to connect related data which reduces data redundancy, makes efficient
use of storage and is easier to maintain
Consider this example flat file table of students

This table has redundant data - the tutor and form room information repeats
This is inefficient
If a tutor changed their name we would need to find all instances of that name and
change them all
Missing any would mean the table had inconsistent data
A relational database would solve this issue:

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 4
 A new table could be created to store the tutor information and the tutor
information in the student table could be moved to the new table. Then a foreign
key in the student table (TutorID) could link a student to their tutor Your notes

Now the name of each tutor and their form room is stored only once
This means if they change only one piece of data, the data is updated in the entire
database and Inconsistency is avoided

Worked Example
Customers’ details are stored in the flat file database table Customer. An extract of
the table is shown below

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 5
Describe one problem that would arise with the flat file database structure if a
customer wanted to insure more than one car at the same time
Your notes
[2]
Answer:
One mark per pair:
Only one customer entry allowed (because of key field)...
so would not be able to add second entry [1]
Customer data already present/would be repeated...
resulting in redundant data/wasted space/inconsistencies should changes be made
[1]

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 6
 Data Normalisation
Your notes
What is normalisation?
In A Level Computer Science, normalisation is the process of organising a database to
reduce data duplication and improve data accuracy and consistency
Achieved by applying a set of guidelines (forms), each with specific rules and
requirements
Enhances database efficiency and maintainability
Provides consistency within the database

First Normal Form (1NF)

What is first normal form?
For a table to be in first normal form it must:
Contain atomic values
Each column in a table must contain single, indivisible values
Have no repeating groups
Columns must not contain arrays or lists of values
Have unique column names
Each column must have a unique name within the table
Have a unique identifier (primary key)
Each row must have a unique identifier to distinguish it from other rows
This customers table below has no primary key and the name is stored in one field so is
not atomic
This table is not in first normal form

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 7
 This customers table below has a primary key and the name is stored in two fields so it is
atomic
Your notes
This table is in first normal form

Second Normal Form (2NF)

What is second normal form?
For a table to be in second normal form it must:
Fulfil all 1NF requirements
Only apply to tables with a compound primary key
Have full functional dependency
All non-prime attributes (attributes not part of the primary key) must be fully
dependent on the primary key
Have no partial dependencies
Non-prime attributes must not depend on only part of the primary key (in case
of a composite primary key)
Separate tables should be created for partially dependent attributes
In this table below, Course Title only depends on part of the compound primary key (the
course code) and not the Date so this table is not in second normal form

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 8
Third Normal Form (3NF)
Your notes
What is third normal form?
For a table to be in third normal form it must:
Fulfil all 2NF requirements
Have no transitive dependencies
Non-prime attributes must not depend on other non-prime attributes
Have each non-prime attribute dependent solely on the primary key, not on other
non-prime attributes
Have separate tables for attributes with transitive dependencies, and the tables
should be linked using a foreign key
In this table below, the certificate depends on the title - this a transitive dependency and
so this table is not in third normal form

Examiner Tips and Tricks

For a table to be in second normal form it has to be in first normal form.
For a table to be in third normal form it has to be in second normal form.
So if asked for the rules of either second or third normal form make sure you say this.

Worked Example
An airport holds details of flights in a database using the table Flight. An extract of
the table is shown below.

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 9
 Your notes

The airline wishes to ensure the database is normalised.

i) Describe why the database can be considered to be in First Normal Form
[2]
ii) Describe why the database can be considered to be in Second Normal Form
[2]
iii) Describe why the database can not be considered to be in Third Normal form
[2]
Answer:
No Repeating fields/data. Data is atomic. Has a primary Key [2]
Is in First Normal Form. Every field is dependent on the primary key [2]
Has a transitive relationship. A non-key field depends on another non-key field.
DestinationName depends on DestinationCode [2]

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 10
 Entity Relationship Diagrams
Your notes

Entity Relationship Types

What is an entity?
An entity in something worthy of capturing and storing data about e.g. students, orders,
products, courses, customers
Entities become tables in a relational database
Relational databases store different entities in separate tables. Linking tables depends
on the relationships between entities.
There are 3 types of (sometimes called degrees of) relationships:
One to one
One to many
Many to many
Imagine a company has
A table of products
A table of customers
A table of the orders the customers have made
What is the relationship between a customer and an order?
One customer can make multiple (many) orders
But each order relates to a specific (one) customer
So the relationship between customer and order is one to many
Now consider the relationship between a product and an order
An order could have more than one (many) products on it
A product could be on more than one (many) order
So the relationship between order and product is many to many
One to one relationships also exist but are not very common in databases

Drawing Entity Relationship Diagrams

What is an entity relationship diagram?
In A Level Computer Science, an entity relationship diagram (ERDs) is a diagram that
represents the entities (tables) that will be in a database and the relationships between
these entities

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 11
The entities are drawn as boxes with the entity name in
The relationships are drawn in as what is known as ‘crow’s feet notation’ Your notes
This is how to draw the relationships in the exam:

The names of the entities would go inside the boxes

Examiner Tips and Tricks

These diagrams are simple but tell us some important things about the database:
The names of all the tables
Which tables will have a foreign key - when an entity has a ‘many’ relationship
against it that means it will have a foreign key in it that links to the primary key of
the connected entity

Worked Example
An insurance company's offices have a large number of black and white printers
The company's technicians keep accurate records of the printers in the building,
and the quantity of toner cartridges in stock, in a flat file database. An extract of
the database is shown:

Printer Location Notes Cartridge Quantity Re-order URL

Model Code in stock

LasPrint office 3 LP-7XB 12 [Link].c

LP753 / LP-7XB

LasPrint office 6 drum LP-7XB 12 [Link].c

LP710 replaced toner / LP-7XB

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 12
 Zodiac reception Zod17 4 [Link]
ZN217 shop / Z17
Your notes
Zodiac conference had to Zod17 4 [Link].c
ZN217 Room 2 add RAM toner / LP-7XB

LasPrint office 8 LP-7XB 12 [Link].c

LP753 toner / LP-7XB

A relational database is created with three tables:

PrinterModel: this stores all the data about each model of printer
PrinterInstance: this stores the data about each individual printer in the
building
Cartridge: this stores information about the toner cartridges
Draw an entity-relationship diagram to show the relationships between the three
tables
[4]
Answer:

Entities and relationships drawn using standard notation [1]

Cartridge linked to PrinterModel, PrinterModel linked to PrinterInstance with no other
links [1]
1:M relationship from Cartridge to Printer Model [1]
1:M relationship from PrinterModel to PrinterInstance [1]

Resolving Many to Many Relationships

Many to many relationships cannot be implemented into an actual database and need
to be ‘resolved’
This involves creating a new table known as a ‘link’ table that goes between the entities
This new table links the entities that have the many to many relationships together

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 13
 Your notes

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 14
 Capturing, Selecting, Managing & Exchanging Data
Your notes

Capturing Data
How can data be captured?
Data can be captured by a computer in many forms, these include:
Forms
OMR (Optical Mark Recognition)
OCR (Optical Character Recognition)
Sensors
Barcodes
Data Mining

Forms
Collect user input
Organise data in structured format
Common in web applications

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 15
 Your notes

A data capture form

OMR (Optical Mark Recognition)
Detects marked areas on paper by using a special machine to read the marks
Used for exams, surveys, lottery tickets
Automates data collection and entry

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 16
 Your notes

An OMR form being filled in

OCR (Optical Character Recognition)
Converts printed or handwritten text into digital format
Useful for digitising documents
Assists in searching and editing text

Sensors
Devices that detect and respond to changes in environment
Convert physical signals into digital data
Facilitates automated data collection and real-time monitoring
Used in various applications:
Temperature sensors
Pressure sensors
Proximity sensors
Light sensors
Motion sensors
Humidity sensors
Gas sensors
Force sensors
Acoustic sensors
Magnetic sensors

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 17
 Your notes

Different Types of Sensor

Barcodes
Machine-readable representation of data using parallel lines of varying widths and
spacings
Commonly used for tracking items, inventory management, and point-of-sale systems
Two main types:
1D (One-dimensional) barcodes: represent data using parallel lines (e.g. on
products in shops)
2D (Two-dimensional) barcodes: use geometric patterns like squares, dots, or
hexagons to store data (e.g. QR code)
Advantages of Barcodes
Fast and accurate data entry
Reduces human error

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 18
 Your notes

A Typical Barcode Scanner

Data Mining
Process of discovering hidden patterns, correlations, and insights from large datasets
Involves techniques and algorithms from fields like machine learning, statistics, and
artificial intelligence
Supports decision-making by transforming raw data into valuable information
Applications of data mining
Marketing: Customer segmentation, market basket analysis, and targeted
advertising
Finance: Fraud detection, credit scoring, and portfolio management
Healthcare: Disease prediction, patient clustering, and drug discovery
Manufacturing: Quality control, predictive maintenance, and supply chain
optimisation
Telecommunications: Network monitoring, customer analysis, and service
improvement

Selecting Data
Query By Example (QBE)

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 19
 User-friendly method for constructing database queries using a visual interface
Allows users to search for data by providing an example of the desired output Your notes
Key Features
Visual representation: QBE uses a grid or form-based interface, where users can
enter criteria in columns representing database fields
Intuitive: Users don't need to learn complex query syntax, making it accessible for
non-technical users
Flexible: Allows for simple to complex queries, including filtering, sorting, and joining
multiple tables
How QBE Works
Users enter criteria in the appropriate columns or fields in the QBE grid or form
The QBE system translates the user's input into an equivalent SQL query or other query
language
The query is executed against the database, and the results are displayed to the user
Common Query Operations
Filtering: Users can specify conditions or criteria in the QBE grid to retrieve specific
records (e.g., all customers from a particular city)
Sorting: Users can indicate the desired sorting order for the results (e.g., ascending or
descending by last name)
Joining: Users can combine data from multiple related tables by specifying relationships
between the tables in the QBE grid
Aggregation: Users can perform calculations or summaries on the data (e.g., counting
the number of records, calculating averages, or summing values)
Benefits and Drawbacks of Query By Example (QBE)

Drawbacks
Benefits

Easy to learn and use, even for non-technical Less powerful and flexible than SQL for
users complex queries and data manipulation

Visual interface makes it simple to May not support advanced database

understand and modify queries features, such as stored procedures or
triggers

Provides a more accessible way to perform Can be slower than SQL queries for large
database searches compared to writing SQL datasets or complex operations
queries

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 20
Managing & Exchanging Data
Your notes
Managing data
After a database has been created, there must be easy ways of being able to manage
the data, this includes:
Add new data
Edit/modify existing data
Delete data
This can be achieved by:
A database manipulation language (DML) such as SQL
Built in facilities of a Database Management System (DBMS)

Exchanging data
CSV (Comma Separated Values)
Simple data exchange format
Stores tabular data in plain text
Uses commas to separate values
Widely supported and easy to parse

JSON (JavaScript Object Notation)

Lightweight data interchange format
Human-readable and easy to analyse
Uses Key-value pairs
Common in web applications

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 21
EDI (Electronic Data Interchange)
Standardised electronic communication method Your notes
Transfers documents and data between businesses
Reduces paper usage and manual processes
Streamlines transactions and increases efficiency

APIs (Application Programming Interfaces)

A set of protocols that allow different software applications to communicate with each
other
Enables developers to integrate different services and functionalities into their
applications
Facilitates data exchange between applications and platforms
Can be RESTful (Representational State Transfer), SOAP (Simple Object Access
Protocol), or other types
Advantages of APIs
Encourages code reuse and modular programming
Simplifies application development by providing pre-built functionalities
Facilitates seamless integration of different services
Promotes innovation by enabling developers to build upon existing technologies

Memory sticks
Portable storage devices, also known as USB flash drives or thumb drives
Use flash memory to store and transfer data between computers and other devices
Connect to devices via USB (Universal Serial Bus) port
Benefits and Drawbacks of Memory Sticks

Benefits Drawbacks

Easy to use: Plug-and-play functionality with no Limited storage capacity compared

need for additional software to external hard drives

Portable: Compact size allows for easy transport Data loss risk due to physical damage,
and storage loss, or theft

Durable: No moving parts, making them resistant Slower transfer speeds compared to
to physical damage other storage solutions

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 22
Compatible: Widely supported across different
operating systems and devices
Your notes

Email
Electronic communication system used for exchanging messages and files between
users
Requires internet access and an email account with an email service provider
Benefits and Drawbacks of Email

Benefits Drawbacks

Fast: Allows for near-instant Limited file size: Most email service providers
communication across the world impose restrictions on attachment sizes

Convenient: Accessible on various Security risks: Vulnerable to phishing attacks,

devices, including computers, spam, and data breaches
smartphones, and tablets

Organised: Stores and organises Privacy concerns: Email messages and

messages in folders, such as Inbox, Sent, attachments can be intercepted, read, or
and Drafts modified by unauthorised parties

Versatile: Supports the attachment of Reliability: Delivery issues can occur due to
various file types and sizes server problems, incorrect email addresses, or
spam filters

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 23
 SQL
Your notes

Selecting Data (Single Table)

What is SQL?
In A Level Computer Science, SQL (Structured Query Language) is a programming
language used to interact with a DBMS.
The use of SQL allows a user to:
Select data (single table)
Select data (multiple tables)
Insert data
Delete records
Delete tables

Selecting data (single table) commands

Example
Command Description

SELECT Retrieves data from a database table SELECT * FROM users;

(retrieves all data from the
'users' table)
SELECT name, age
FROM users
(retrieves names and ages from
the 'users' table)

FROM Specifies the tables to retrieve data SELECT name, age FROM users;
from (retrieves names and ages from
the 'users' table)

WHERE Filters the data based on a specified SELECT * FROM users

condition WHERE age > 30;
(Retrieves users older than 30)

LIKE Filters the data based on a specific SELECT * FROM users

pattern WHERE name LIKE 'J%';
(retrieves users whose names
start with 'J')

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 24
 AND Combines multiple conditions in a SELECT * FROM users
WHERE clause WHERE age > 18 AND city =
Your notes
'New York';
(retrieves users older than 18
and from New York)

OR Retrieves data when at least one of the SELECT * FROM users

conditions is true WHERE age < 18 OR city = 'New
York';
(retrieves users younger than 18
or from New York)

WILDCARDS '*' and '%' symbols are used for SELECT * FROM users;
searching and matching data (retrieves all columns for the
'*' used to select all columns in a table 'users' table)
'%' used as a wildcard character in the
LIKE operator SELECT * FROM users WHERE
name LIKE 'J%';
(retrieves users whose names
start with 'J')

Examples
Select all the fields from the Customers table
Command:

Output:

ID Name Age City Country

1 John Doe 30 New York USA

2 Jane Doe 25 London UK

3 Peter Lee 40 Paris France

Select the ID, name & age of customers who are older than 25
Command:

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 25
Output:

ID Name Age Your notes

1 John Doe 30

3 Peter Lee 40

Select the name and country of customers who are from a country that begins with 'U'
Command:

Output:

Name Country

John Doe USA

Jane Doe UK

Select all fields of customers who are from 'London' or 'Paris'

Command:

Output:

ID Name Age City Country

2 Jane Doe 25 London UK

3 Peter Lee 40 Paris France

Worked Example

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 26
 Customers’ details are stored in the flat file database table Customer. An extract of
the table is shown below
Your notes

Write the SQL statement that would show only the CustomerID and Surname fields
for customers with the Title “Miss” or “Mrs”
[4]
Answer:
SELECT CustomerID, Surname [1]
FROM Customer [1]
WHERE Title="Miss" [1]
OR Title = "Mrs" [1]

Selecting Data (Multiple Tables)

Example
Command Description

Nested A select within another select SELECT * FROM users WHERE age
SELECT statement (nested). A mini select > (SELECT AVG(age) FROM users);
within the main one
(retrieves users with an age greater
than the average age)

JOIN Combines data from two or more SELECT [Link],

(INNER tables based on a related column orders.order_id FROM users
JOIN) JOIN orders
ON users.user_id = orders.user_id;
(retrieves user names and their
corresponding order IDs)

Examples
Table: Employees
ID Name Salary Department City

1 Fynn Roberts 45000 HR London

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 27
 2 Zarmeen Azra 52000 Sales Manchester
Your notes
3 Ella Stanley 39500 Marketing Birmingham

Select all fields for employees whose salary is bigger than the average salary of all
employees
Command:

Output:

ID Name Salary Department City

2 Zarmeen Azra 52000 Sales Manchester

Inserting Data
Example
Command Description

INSERT Adds new data to a database INSERT INTO users (name, age)
table VALUES ('John Doe',25);
(inserts a new user with the name 'John Doe'
and age 25)

Example
Table: Employees

ID Name Salary Department City

1 Fynn Roberts 45000 HR London

2 Zarmeen Azra 52000 Sales Manchester

3 Ella Stanley 39500 Marketing Birmingham

Insert a new employee into the Employees table with the 'Name', 'Salary', 'Department'
and 'City' fields

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 28
Command:

Your notes

Output:

ID Name Salary Department City

1 Fynn Roberts 45000 HR London

2 Zarmeen Azra 52000 Sales Manchester

3 Ella Stanley 39500 Marketing Birmingham

4 George Rope 47250 Sales Leeds

Deleting Records
Example
Command Description

DELETE Removes data from a DELETE FROM users

database table WHERE age < 18;
(deletes all users younger than 18 from the
'users' table)
DELETE FROM users
WHERE name="John";
(deletes a record where the name is John)

Example
Table: Employees

ID Name Salary Department City

1 Fynn Roberts 45000 HR London

2 Zarmeen Azra 52000 Sales Manchester

3 Ella Stanley 39500 Marketing Birmingham

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 29
 4 George Rope 47250 Sales Leeds
Your notes
Delete all records from the Employees table whose department is 'Marketing'
Command:

Output:

ID Name Salary Department City

1 Fynn Roberts 45000 HR London

2 Zarmeen Azra 52000 Sales Manchester

3 George Rope 47250 Sales Leeds

Worked Example
A database stores information about songs on a music streaming service. One of
the tables called Song has the fields Title, Artist, Genre and Length

A band called RandomBits removes their permission for their songs to be

streamed. The company removes all the songs belonging to RandomBits from their
service.

Write an SQL statement that will remove all songs by RandomBits from the table
Song
[2]
Answer:
DELETE FROM Song [1]
WHERE Artist = “RandomBits” [1]

Deleting Tables
Example
Command Description

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 30
 DROP Deletes a table in a database DROP TABLE users;
(deletes the 'users' table)
Your notes

Example
Table: Employees

ID Name Salary Department City

1 Fynn Roberts 45000 HR London

2 Zarmeen Azra 52000 Sales Manchester

3 Ella Stanley 39500 Marketing Birmingham

4 George Rope 47250 Sales Leeds

Delete the Employees table

Command:

Output

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 31
 Referential Integrity
Your notes

Referential Integrity
What is referential integrity?
In A Level Computer Science, referential integrity ensures consistency between related
tables in a relational database
Maintains valid relationships between primary and foreign keys
There should not be foreign keys for which a matching primary key in the linked table
does not exist
Foreign key constraints
Value in a foreign key field must either:
Match a primary key value in the related table, or
Be null (if allowed)
Enforce referential integrity
Rules:
Cascade actions
CASCADE: automatically makes changes to related records
SET NULL: sets foreign key value to null in related records
SET DEFAULT: sets foreign key value to its default in related records
NO ACTION/RESTRICT: prevents changes if related records exist
Update or delete actions will take effect everywhere in the database automatically
Types:
Benefits and drawbacks of referential integrity

Drawbacks
Benefits

Ensures data consistency and Can impact performance due to additional

accuracy checks

Prevents orphaned records May require additional planning and design

Maintaining referential integrity

Use database management systems (DBMS) with built-in support

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 32
 Implement triggers to enforce custom referential integrity rules
Regularly validate and clean up data to ensure consistency Your notes

Worked Example
A hotel uses a computer system to keep track of room bookings. The hotel staff are
able to query a database to discover which rooms are booked or which rooms are
free
The hotel booking database enforces referential integrity.
Explain what is meant by the term ‘referential integrity’ and how this could
potentially be broken
[2]

Answer:
Database/relationships are consistent and each foreign key links to an existing/valid
primary key [1]
If a primary key is deleted, foreign keys that link to it are no longer valid so they should
also be deleted - this is known as a cascaded delete [1]

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 33
 Transaction Processing
Your notes

Transaction Processing
What is a transaction?
A transaction is a sequence of database operations treated as a single unit of work
Ensures data consistency and integrity during simultaneous access
Example: money transfer between bank accounts. The transaction here would be a
withdrawal from one account and a deposit into another. Both operations must happen
together or neither should happen
Problems that arise from transaction processing
Concurrency: When multiple transactions are executed simultaneously, they might
try to access or modify the same data, leading to inconsistencies
Deadlock: This occurs when two or more transactions are waiting for each other to
release resources, causing them to wait indefinitely
Data Integrity: Transactions might leave the database in an inconsistent state if they
fail in the middle of execution
Isolation: In a multi-user environment, one user's transaction might affect another
user's transaction, leading to unpredictable outcomes
Durability: If a system fails after a transaction has been confirmed, it may result in
loss of data
Solutions to overcome problems that arise in transaction processing
Locking: Implementing a locking mechanism ensures that no two transactions can
access the same data simultaneously. There are two types of locks: shared locks
and exclusive locks
Deadlock Prevention or Deadlock Detection: Deadlocks can be prevented by
ordering the way in which resources are requested or by having a timeout
mechanism
Logging and Recovery: By keeping a log of all changes, the system can recover to a
consistent state after a crash
Commit and Rollback: The commit operation saves all changes made in the
transaction as permanent. The rollback operation reverts the changes made in the
transaction
Two-Phase Commit Protocol: In distributed systems, this protocol ensures that a
transaction is committed in all participating nodes, or none at all, to maintain
consistency
Concurrency control
Manages simultaneous access to data in a multi-user environment

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 34
 Techniques:
Locking: prevents multiple transactions from accessing the same data Your notes
simultaneously
Timestamping: assigns a unique timestamp to each transaction
Transaction management
Use database management systems (DBMS) with built-in transaction support
Implement custom transaction logic using SQL or other query languages
Test and monitor transactions to ensure ACID compliance and performance

ACID (Atomicity, Consistency,

Isolation, Durability)
What is ACID?
In A Level Computer Science, ACID is a set of rules that all Database Management
Systems (DBMS) must use to ensure data integrity
Guarantee reliable processing of transactions
Atomicity
All operations in a transaction succeed or fail as a whole
If any operation fails, the transaction is rolled back
Ensures partial transactions do not occur
Consistency
Ensures data remains in a consistent state after transactions
Transactions must follow database rules and constraints
Starts with a consistent state and ends with a consistent state
Isolation
Transactions are isolated from each other
Intermediate states are not visible to other transactions
Prevents conflicts and data inconsistencies
Durability
Committed transactions persist even in case of system failures
Ensures data is not lost once a transaction is complete

Worked Example

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 35
 RestaurantReview is a website that allows users to leave reviews and ratings for
different restaurants
Your notes
The website uses a database with the following structure

The database management system ensures referential integrity is maintained

Whenever a review is added to the system, the restaurant’s average rating is
updated
Describe what is meant by the term ‘Atomic’ in the context of ACID transactions.
You should refer to the example of a review being added
[2]
Answer:
A transaction / review can only fully complete or not complete / cannot partially
complete [1]
In this case, it should not be possible for the review to be added without the (average)
rating being updated [1]

Record Locking
Record locking is a technique used in database management systems (DBMS) to
prevent conflicting access to data by multiple transactions or processes
It ensures data consistency and integrity when multiple users or processes try to read,
modify, or delete records simultaneously
Key Concepts
Lock: A mechanism that prevents access to a database record by other
transactions while a specific transaction is using the record
Lock granularity: Refers to the size of the locked data, ranging from a single row to
an entire table
Types of Locks
Shared lock (Read lock): Allows multiple transactions to read a record
simultaneously, but prevents modifications or deletions until the lock is released
Exclusive lock (Write lock): Allows only one transaction to access and modify a
record, blocking other transactions from reading or writing to the locked record
until the lock is released
Benefits of Record Locking
Maintains data consistency and integrity by preventing conflicting modifications

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 36
 Allows concurrent read access to records without compromising data consistency
Improves database performance by enabling multiple users to access data Your notes
simultaneously, while managing access to prevent conflicts

Redundancy
Redundancy can occur when the same piece of data is stored in more than one table in a
database. This can either be by accident or by design
It can lead to inconsistencies in the data and/or wasted storage space

© 2025 Save My Exams, Ltd. Get more and ace your exams at [Link] 37