Information Storage and Retrieval: Transactions, Integrity and Operational Storage

Lesson overview

Explain transaction processing, ACID properties, integrity constraints and isolation issues in systems that support reliable operational storage and retrieval.

CourseInformation Storage and Retrieval

Topic strandOperational systems

Assessment styleWorked scenario, applied task and digital exam practice

EvidenceDefinitions, representation, method, result and interpretation

Starter

Write down the user need, the data being stored or searched, and the decision the system has to support. Then predict which representation, index, query method or governance control will matter most in this lesson.

Learning objectives

Define transaction and ACID properties.
Explain integrity constraints such as primary key, foreign key and check constraints.
Describe isolation problems and why operational storage needs consistency.

Learning outcomes

Students can define transaction and ACID properties.
Students can explain integrity constraints such as primary key, foreign key and check constraints.
Students can describe isolation problems and why operational storage needs consistency.

Key vocabulary, acronyms and terminology

transaction: A unit of work that should complete fully or not at all.
ACID: Atomicity, Consistency, Isolation and Durability.
constraint: A database rule limiting allowed data states.
isolation: The degree to which concurrent transactions do not interfere.
commit: Making transaction changes permanent.
rollback: Undoing changes from a failed transaction.

Detailed teaching notes

Core concept

Operational systems do not merely store values; they protect business events. Transactions ensure a multi-step change is treated as one reliable unit.

Representation choice

A bank transfer might debit one account and credit another. Those two updates must be represented inside one transaction so the database never records only half the event.

Method and reasoning

Define transaction boundaries, enforce constraints and choose isolation levels appropriate to the risks of concurrent access. Use rollback when a constraint or later step fails.

Risk and limitation

Weak isolation can produce lost updates, dirty reads or inconsistent reports. Overly strict isolation can reduce throughput, so systems balance correctness and performance.

Degree-level deep dive

Transactions define all-or-nothing work

A transaction groups operations that must succeed or fail together. In booking, payment or enrolment systems, partial completion can create corrupt or misleading operational state.

ACID is operational reasoning

Atomicity, consistency, isolation and durability describe the guarantees needed when concurrent users and failures are possible. Each property should be tied to a concrete failure mode, not just listed.

What excellent work shows

A strong answer names the transaction boundary, constraints and concurrent risk. It should explain commit, rollback, isolation and durability using the scenario, such as preventing double booking or preserving a paid order.

Concrete example to study

Seat booking transaction

BEGIN
1. Check seat A12 for performance P7
2. Insert booking(customer_id, P7, A12)
3. Record payment authorisation
4. Mark seat A12 unavailable
COMMIT or ROLLBACK

Reasoning

Atomicity prevents a paid booking without a reserved seat.
Isolation prevents two users from booking A12 at the same time.
Durability means the committed booking survives a crash.

Reveal takeaway

ACID properties become meaningful when tied to the concrete failure they prevent, such as double booking, partial payment records or lost committed reservations.

Worked example

Scenario

A booking system reserves the last seat and records payment.

Worked solution

Start a transaction before checking and updating seat availability.
Check constraints: seat exists, not already reserved, payment reference valid.
Update reservation and payment records together.
Commit only if all steps succeed; otherwise rollback.

Reveal model result

The transaction prevents a paid booking without a seat or a reserved seat without valid payment.

Applied retrieval task

Design transaction boundaries for a library loan.

Deliverables

List the steps in issuing a loan.
State constraints that must hold.
Explain when to commit or rollback.

Success checks

All related changes are inside one transaction.
The answer identifies a concurrency risk.

Common misconception

A transaction is not just a SQL statement. It is a logical unit of work that may include several statements that must succeed together.

Quick checks

1. In this lesson, why does transaction matter?

It is only a presentational label and has no effect on retrieval quality.A unit of work that should complete fully or not at all.It replaces the need to understand user tasks or data representation.

2. Which answer best shows degree-level understanding of transactions, integrity and operational storage?

Define ACID, apply it to the scenario, then explain the trade-off or risk.Name as many technologies as possible without explaining the scenario.Give a final recommendation without showing how the data or query was represented.

Digital exam practice

Example exam task

An online ticket system sometimes sells the same seat twice when many users try to book at once. In your answer, define the relevant objects or data structures, use course-specific vocabulary, show the method rather than only the result, and finish with a decision about the storage or retrieval system.

Notation and technical toolkit

BEGIN; ... COMMIT;transaction boundary Use to show a complete unit of work.

ROLLBACKundo failed transaction Use when any required step fails.

ACIDtransaction reliability properties Use to structure explanations.

What a good answer is expected to show

A strong answer for Transactions, Integrity and Operational Storage the answer should explain transaction boundaries, ACID, isolation and integrity constraints, then propose a safer operational storage design. It should connect the formal or technical representation to the user's information need instead of listing terms without using them.

How to solve it

Describe the booking as one transaction.
State the relevant constraints on seat identity and availability.
Explain isolation or locking needed to prevent concurrent double booking.
Commit only after all required updates succeed.

Model answer

Reveal model answer

Seat booking should be one transaction: check availability, reserve the seat, create the order and record payment status. ACID matters because atomicity prevents half-completed bookings, consistency enforces constraints, isolation prevents another transaction from seeing or taking the same seat, and durability keeps the committed booking. A unique constraint on seat and performance, plus appropriate locking or isolation, prevents double selling when users act concurrently.

Practise next

Explain the difference between rollback and commit using an order example.
Give one integrity constraint for a student-enrolment database.

Self-marking criteria

Defines the scenario objects, data forms or system components before solving.
Uses the lesson vocabulary accurately and in context.
Shows a clear method with enough working for a marker to follow.
Connects the result back to retrieval, storage, analytics or governance.
States a limitation, trade-off or quality risk rather than presenting the answer as absolute.

Extension

Research one isolation level and describe one anomaly it prevents or permits.

Study route

Save a short worked answer from this lesson using this pattern: define the need or data, choose the representation, show the method, state the result and interpret the implication for the system.

Next lesson: Denormalisation, Warehouses and Business Intelligence.