Database administration
The database administrator (DBA) looks after the database every day.
- Users and permissions: create accounts and give each one only the rights it needs (read, add, change, delete). This is called access control.
- Security: strong passwords, encryption, and logs of who did what.
- Performance: watch speed and free space, and add indexes so searches stay fast.
- Backup and recovery plan: decide how often to copy data and test that restoring works.
- Keeping data correct: rules so wrong values cannot be entered.
Backup and recovery
A backup is a copy of the data kept in a separate place. If the main data is lost or damaged, recovery (restore) copies it back.
- Full backup: copy everything.
- Incremental backup: copy only what changed since the last backup. Faster and smaller.
- Keep copies in another place (another building or cloud), because a fire or flood can hit both.
- Test a restore sometimes. An untested backup may not work.
Database maintenance
Maintenance keeps the database healthy over the years.
- Clean up: remove old or duplicate records, archive data that is rarely used.
- Re-organise: rebuild indexes and free space so it stays fast.
- Update: apply security patches and new versions of the software.
- Monitor: watch errors, disk space and slow searches, and fix small problems early.
- Change management: test a change on a copy before doing it on the real data.
Key formulas and definitions
- Admin jobs: Permissions, Backup, Recovery, Tune-up
- Full backup = everything; incremental = only changes
- Data lost on failure = time since the last backup
- Least privilege = only the rights needed
Worked examples
1. A nightly backup runs at 1 a.m. The disk fails at 5 p.m. How many hours of data may be lost?
From 1 a.m. to 5 p.m. is 16 hours. Up to 16 hours of changes are lost unless there is a more frequent backup.
2. A clerk should add orders but never delete them. Which permissions?
Read and add (insert), but not delete. This is least privilege.
3. A full backup is 100 GB. A daily incremental holds 5 GB. Backup storage for 1 full and 6 incrementals?
100 + 6 x 5 = 130 GB.
Common mistakes
- Making backups but never testing a restore.
- Keeping the backup on the same disk as the data. If the disk dies, both are gone.
- Giving everyone full rights because it is easier.
- Skipping maintenance until the database becomes slow.