Topics in this domain
- IT Service Management
- Hardware Components and Architecture
- Operating Systems
- OSI Model
- TCP/IP and Common Protocols
- Network Devices: Switches, Routers, Gateways
- LAN/WAN Topologies
- Database Models and DBMS
- Database Controls and Normalization
- Asset Management
- Configuration Management
- Job Scheduling and Batch Processing
- Incident Management
- Problem Management
- Change Management
- Release and Patch Management
- Service Level Management
- Capacity and Performance Monitoring
- End-User Computing
- BCP Lifecycle
- Business Impact Analysis
- RTO, RPO, MTD, SDO
- Recovery Strategies
- Alternate Sites: Hot, Warm, Cold
- Backup Types: Full, Incremental, Differential
- Backup Storage and Rotation
- DRP Contents
- Testing BCP/DRP
- High Availability, RAID, Insurance
- Auditing BCP
Database Controls and Normalization
Store each fact once
If your address is written in five different address books and you move house, you have to update all five. Miss one, and your post goes to the old address.
Normalisation designs a relational database so that each fact is stored in one place only. Change it once, and it’s right everywhere. That prevents update anomalies, where changing data in one place leaves an out-of-date copy somewhere else.
| Normal form | Rule |
|---|---|
| First (1NF) | One value per field; no repeating groups; every record has a key |
| Second (2NF) | In 1NF, and every non-key field depends on the whole key, not just part of a combined key |
| Third (3NF) | In 2NF, and no non-key field depends on another non-key field |
For the exam, the purpose matters more than the rules: less redundancy, fewer anomalies, better integrity.
Denormalisation
Sometimes data is copied on purpose to make queries and reports faster, especially in data warehouses. That’s denormalisation. It’s a legitimate design choice, but now the copies can drift apart, so compensating controls are needed: controlled update routines, or regular reconciliation between the copies.
Example. A reporting team copies customer addresses into a sales table for speed. Address changes are only made in the customer table. Six months later, invoices go to old addresses because the billing job reads the sales table. A controlled update routine, or reconciliation between the two tables, was missing.
Access controls
- Users reach data through applications, not directly with database tools. Direct database access bypasses application controls.
- Roles and views limit what each user can see and change.
- Privileged accounts (DBA, schema owners) are named, few and monitored.
- Application service accounts have only the rights the application needs.
- Production data is masked or anonymised before it’s used in test environments.
Encryption protects against stolen disks and backups. It doesn’t protect against an authorised user who simply has too much access.
Integrity controls
Primary keys, foreign keys (referential integrity), check constraints, triggers and stored procedures enforce rules inside the database, so they apply however the data arrives.
Backup and recovery
| Technique | Role |
|---|---|
| Full and incremental backups | Restore the database to a point in time |
| Transaction logs (journals) | Record every change since the last backup |
| Before images | Used to roll back incomplete or failed transactions |
| After images | Used to roll forward: reapply committed transactions after restoring a backup |
| Checkpoints | Saved points that limit how much work has to be redone after a failure |
Restores should be tested regularly. A backup you’ve never restored is a hope, not a control.
Monitoring
Database activity monitoring (DAM) tools log and alert on suspicious activity: bulk extracts, direct changes to sensitive tables, privilege escalation, and access outside working hours.
How the exam asks about it
| If the question asks… | The answer is usually… |
|---|---|
| Purpose of normalisation | Reduce redundancy and update anomalies |
| Risk of denormalisation | Data inconsistency / integrity problems |
| Restoring committed transactions after a backup restore | Roll forward using after images / logs |
| Undoing incomplete transactions | Rollback using before images |
| Best control over DBA activity | Logging with independent review |
| Production data used in testing | Mask or anonymise it |
In short
- Normalisation organises data to reduce redundancy and update anomalies, improving integrity.
- First normal form removes repeating groups; second removes partial dependencies on a composite key; third removes transitive dependencies.
- Denormalisation improves performance but increases redundancy and the risk of inconsistent data.
- Database controls include access through roles and views, DBA activity logging, integrity constraints, encryption, backup and recovery.
- Recovery uses backups plus transaction logs: rollback removes incomplete transactions, roll forward reapplies committed ones.
- Checkpoints limit how much processing must be redone after a failure.
Exam traps
- Normalisation reduces redundancy; denormalisation reintroduces it for speed, so compensating integrity controls are needed.
- Rollback undoes incomplete transactions; roll forward reapplies committed transactions after a restore.
- Database-level access for end users bypasses application controls.
- Encrypting the database doesn't protect against an authorised user with excessive access.
Slides
Click the slide and use the arrow keys. F toggles full screen.
Store each fact once
If your address is written in five different address books and you move house, you have to update all five. Miss one, and post goes to the old address.
Normalisation designs a database so each fact is stored in one place only. Change it once, and it's right everywhere.
Recovery