DATA BASE NOTES :
WEEK 11 :
📘 Topic 1: Database Backup & Recovery
Techniques
🔹 1. What is Database Backup?
A backup is a saved copy of the database that can be used to recover
data in case of a problem like system crash, virus attack, hardware
failure, or accidental deletion.
🔹 2. Why is Backup Important?
To prevent data loss
To restore the system after a crash
To protect data from corruption or hacking
To maintain business continuity
🔹 3. Types of Backup:
✅ a. Full Backup
A complete copy of the entire database.
Takes more time and storage space.
Commonly done weekly.
Example:
A university takes a full backup of its student records every Sunday
night.
✅ b. Incremental Backup
Saves only the changes made since the last backup.
Very fast and uses less space.
Needs the last full backup and all incremental backups to restore
fully.
Example:
If full backup was on Sunday, and new student data was added on
Monday, only Monday’s data is saved in incremental backup.
✅ c. Differential Backup
Saves all changes since the last full backup.
Bigger than incremental, smaller than full.
Faster restore than incremental.
Example:
If Sunday is full backup and it’s Wednesday today, differential backup
will store all changes from Sunday to Wednesday.
🔹 4. What is Recovery?
Recovery means bringing back lost, damaged, or corrupted data
using backups or logs.
🔹 5. Database Recovery Techniques:
✅ a. Rollback
Used to undo a failed transaction.
Ensures database returns to its previous consistent state.
Example:
If money is deducted from one bank account but not added to another,
rollback will cancel the transaction.
✅ b. Rollforward
Applies changes recorded in logs after the last backup to update
the database to its latest state.
Example:
After restoring a backup, rollforward applies changes from logs to bring
data up to current time.
✅ c. Log-Based Recovery
All transactions are saved in a log file.
In case of crash, the system replays the log to recover data.
Example:
If the system crashes, the database uses the log to re-execute completed
transactions and discard incomplete ones.
✅ d. Checkpoints
Automatic save points in the database system.
After a failure, the system goes back to the last checkpoint and
restarts from there.
Example:
Checkpoint is saved every 30 minutes. If a crash happens at 10:45, the
system recovers from the 10:30 checkpoint.
📘 Topic 2: Database Security & Authorization
🔹 1. What is Database Security?
Database security means protecting the database from:
Unauthorized users
Hackers
Viruses
Accidental data loss or corruption
🔹 2. Common Database Security Techniques:
✅ a. User Authentication
Only authorized users can access the database.
Uses username & password to verify identity.
Example:
Only admin can access student records by logging in with their
credentials.
✅ b. Data Encryption
Converts data into coded form (unreadable text).
Only users with a key can read it.
Example:
Student fee details are encrypted so that even if stolen, they can’t be read
without a decryption key.
✅ c. Firewall Protection
Acts like a security gate between the database and outside world.
Prevents external attacks from hackers or malicious programs.
Example:
University's server uses a firewall to block unauthorized access from
unknown IP addresses.
✅ d. Database Activity Monitoring
Tracks user actions like logins, queries, changes, etc.
Helps detect unusual or suspicious activity.
Example:
If someone tries to delete all student data, the system alerts the admin
immediately.
🔹 3. What is Authorization?
Authorization means defining what a user is allowed to do in the
database.
🔹 4. Types of Authorization:
Authorization Type Description SQL Command
Read View data only SELECT
Authorization Type Description SQL Command
Write Add or modify data INSERT, UPDATE
Delete Remove data DELETE
All Privileges Full access (admin) SELECT, INSERT, UPDATE, DELETE
🔹 5. SQL Examples for Authorization:
sql
CopyEdit
-- Give read and insert permissions to Khadija
GRANT SELECT, INSERT ON Students TO khadija;
-- Remove delete permission from Khadija
REVOKE DELETE ON Students FROM khadija;
-- Give all permissions to Admin
GRANT ALL PRIVILEGES ON Fees TO admin;
✅ Summary Chart
Concept Purpose
Backup Save a copy of database
Full Backup Complete database copy
Incremental Backup Only changed data since last backup
Differential Backup All changes since full backup
Recovery Restore lost or damaged data
Rollback Undo failed transaction
Rollforward Apply logs to reach latest state
Authentication User login verification
Encryption Secure data in coded form
Authorization Give specific permissions to users
WEEK 12 :
