Topics in this domain
- Project Roles and Governance
- PMBOK Phases
- Gantt, PERT, Critical Path
- Function Point Analysis
- Earned Value and Project Risk
- Business Case and Feasibility
- Benefits Realization
- SDLC Phases Overview
- Requirements Definition
- System Design
- Waterfall vs. Iterative vs. Prototyping
- Agile
- DevOps and DevSecOps
- RAD, CASE, Low-Code
- Build, Coding Standards, Code Review
- Testing Levels: Unit, Integration, System, UAT
- Regression, Sociability, Parallel, Pilot Testing
- Defect Management
- Data Migration and Conversion
- Change-Over: Parallel, Phased, Abrupt
- Go-Live Readiness and Acceptance
- Post-Implementation Review
- Input Controls
- Processing Controls
- Output Controls
- Data Integrity and ACID
- EDI and E-Commerce Controls
Data Integrity and ACID
Why databases need rules
Moving 500 from savings to your current account is really two steps: take 500 out of one, put 500 into the other. What if the system crashes in between? What if your partner moves money from the same account at the same moment?
Databases follow four rules, known as ACID, so that the answer is always “your money is safe”.
ACID
| Property | Meaning | How it’s achieved |
|---|---|---|
| Atomicity | All or nothing. If any part fails, the whole transaction is undone. | Commit and rollback |
| Consistency | Each transaction moves the database from one valid state to another, respecting all integrity rules | Constraints, validation, triggers |
| Isolation | Transactions running at the same time don’t interfere or see each other’s unfinished changes | Locking, concurrency control |
| Durability | Once committed, a transaction survives a system failure | Transaction logs, write-ahead logging, recovery |
Example. A customer transfers 500 from savings to current. The system debits savings, then crashes before crediting current. Atomicity means the debit is rolled back on restart, so the money doesn’t vanish. Durability means that if the transfer had committed before the crash, it would still be there after recovery.
Durability isn’t just “we take backups”. It means a committed transaction survives a crash, usually thanks to transaction logs.
When two users collide
Without isolation, transactions running at the same time can cause:
- lost updates: two people update the same record and one silently overwrites the other
- dirty reads: reading data another transaction hasn’t committed yet (and might roll back)
- inconsistent reads: data changes between two reads in the same transaction
Locking and other concurrency controls prevent these. A deadlock (two transactions each waiting for the other to let go) is detected, and one of them is rolled back.
Integrity rules
- Entity integrity: every record has a unique primary key that’s never blank.
- Referential integrity: every foreign key points to a record that exists. This prevents orphans, such as an order for a customer who isn’t in the customer table.
- Domain integrity: values fit their defined type, format and range.
The database management system enforces these itself, so they work even when data arrives by some route other than the application.
Data integrity testing
Data integrity tests are substantive tests of whether data is accurate, complete, consistent and authorised. They’re often run with CAATs across the whole database.
- Relational integrity tests at field and record level: valid values, formats, required fields.
- Referential integrity tests across tables: no orphans, relationships intact.
- Reconciling totals between related tables and between systems.
How the exam asks about it
| If the question asks… | The answer is usually… |
|---|---|
| Property making a transaction all-or-nothing | Atomicity |
| Property preventing concurrent transactions from interfering | Isolation |
| Property making committed transactions survive a crash | Durability |
| Control preventing orphan records | Referential integrity |
| Each record has a unique identifier | Entity integrity |
| Type of test checking data relationships between tables | Referential integrity test (substantive) |
In short
- ACID: atomicity (all or nothing), consistency (valid state to valid state), isolation (transactions don't interfere), durability (committed changes survive failures).
- Atomicity is achieved with commit and rollback; durability with transaction logs and recovery.
- Isolation is achieved with concurrency controls such as locking, preventing problems like lost updates.
- Referential integrity makes sure foreign keys point to existing records; entity integrity makes sure every record has a unique, non-null primary key.
- Data integrity tests are substantive tests that check data accuracy, completeness, consistency and authorisation.
- Relational integrity tests check data at the field and record level; referential integrity tests check relationships between tables.
Exam traps
- Atomicity: a funds transfer either debits AND credits, or does neither.
- Isolation prevents concurrent transactions from seeing each other's partial results.
- Referential integrity prevents orphan records, such as an order for a customer who doesn't exist.
- Durability doesn't mean backups alone; it means committed transactions survive a crash, typically via logs.
Slides
Click the slide and use the arrow keys. F toggles full screen.
ACID, through one bank transfer
Integrity rules