10.7 Backup Strategies

Backup Type

Description and Nigerian Bank Use

Full Backup

Complete copy of all database data. Done weekly for most Nigerian banks. Stored off-site (or in cloud — AWS/Azure).

Incremental Backup

Only data changed since the last backup (full or incremental). Done daily to reduce backup time and storage.

Differential Backup

Data changed since the last FULL backup. Faster to restore than incremental (only need full + latest differential).

Transaction Log Backup

Backup of the transaction log (for point-in-time recovery). Done hourly or continuously for critical systems.

Snapshot Backup

A consistent point-in-time copy of the database (using OS/storage-level snapshot). Near-instantaneous — used for 24/7 systems.

Course Summary

Week

Core Topics Covered

Week 1

Data vs. Information vs. Knowledge; IM Concepts; File-based vs. Database approach; DBMS overview

Week 2

DBMS components; DBMS functions; Three-schema architecture; Data independence; Database languages; Roles

Week 3

Conceptual modelling; ER model; Entities, attributes, relationships; Cardinality; ER diagram construction

Week 4

Relational data model; Keys (PK, FK, candidate, composite); Entity integrity; Referential integrity; ER→Relational mapping

Week 5

SQL DDL/DML/DQL; SELECT, WHERE, ORDER BY; Aggregate functions; GROUP BY/HAVING; JOINs; Subqueries

Week 6

Functional dependencies; Update anomalies; 1NF, 2NF, 3NF, BCNF; Normalisation impact; Denormalisation

Week 7

Semi-structured data; XML structure and syntax; JSON format; Relational vs. semi-structured; JSON in MySQL

Week 8

DB security threats; Authentication/Authorisation; GRANT/REVOKE; Views; SQL injection; Encryption; NDPR

Week 9

Query processing pipeline; Relational algebra; Query optimisation; Indexes (B-Tree, Hash, Composite); EXPLAIN

Week 10

Transactions; ACID properties; Concurrency problems; Lock-based/timestamp protocols; Isolation levels; Recovery; WAL; ARIES; Backups

 

Reading List / References

1.    Ramakrishnan, R. & Gehrke, J. (2003). Database Management Systems, Chapters 16-18: Concurrency & Recovery. McGraw-Hill.

2.    Silberschatz et al. (2020). Database System Concepts, Chapters 17-19: Transactions & Recovery. McGraw-Hill.

3.    Mohan, C. et al. (1992). ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging. ACM TODS, 17(1), 94-162.

 

Activities

Self-Assessment Quiz: 1. What does ACID stand for? Define each property with a banking example. 2. What is a dirty read? Give a specific scenario from a Nigerian fintech context. 3. Explain the difference between a shared lock and an exclusive lock. 4. What is Write-Ahead Logging (WAL)? Why is it essential for database recovery? 5. A Nigerian bank's database server crashes mid-transaction while processing salary payments. Describe step by step how the ARIES algorithm would recover the database.

 

Concurrency Scenario Exercise: Consider three concurrent transactions in a bank database: T1: Transfer ₦50,000 from Account A to Account B T2: Generate end-of-day balance report for Account A T3: Apply monthly interest to all Savings accounts including Account A  (a) What concurrency problems could arise without any concurrency control? (b) Using two-phase locking, describe what locks each transaction needs and in what order. (c) Could a deadlock occur? If yes, describe the scenario and how it would be resolved. (d) Which isolation level would you recommend for T1, T2, and T3 respectively, and why?