week 10

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?