Topic 1: Review of Basic Database Concepts & Introduction to Advanced Concepts

1. What is a Database?
A database is an organized collection of data that can be easily accessed, managed, and
updated.
👉 Simple words:
It’s like a digital storage system where data is stored in a structured way.
Example
 University system → stores student records
 Bank system → stores account details
 WhatsApp → stores messages, contacts, media

2. What is DBMS (Database Management System)?

A DBMS is software used to create, manage, and interact with databases.
👉 Think of it like a bridge between user and database.
Examples
 MySQL
 Oracle
 SQL Server

3. Basic Database Concepts

a) Table
A table is a collection of related data in rows and columns.
👉 Example:
Student_ID Name Age
101 Ali 20

b) Row (Tuple)
A single record in a table.
👉 Example:
101, Ali, 20

c) Column (Attribute)
A field representing a property of data.
👉 Example:
Name, Age, Student_ID
d) Primary Key
A unique identifier for each record.
👉 Example:
Student_ID (no duplicates allowed)

e) Foreign Key
A key used to link two tables.
👉 Example:
Student_ID in another table referencing main table

f) Schema
Structure/design of database.
👉 Example:
Defines tables, columns, relationships

4. Types of Databases (Basic Overview)

a) Relational Database
 Data stored in tables
 Uses SQL
👉 Example: MySQL

b) Non-Relational (NoSQL)
 Flexible structure
 Used for large-scale apps
👉 Example: MongoDB

5. Basic Operations (CRUD)

These are very important for exams:
Operation Meaning

Create Insert data

Read Retrieve data

Update Modify data

Delete Remove data

👉 Example:
SELECT * FROM Students;

6. Advantages of Databases
 Organized data storage
 Easy access & retrieval
 Data consistency
 Security features
 Supports multiple users

7. Disadvantages
 Costly setup
 Requires skilled management
 Can be complex
 System failure risk

Now: Introduction to Advanced Database Concepts

1. Why Advanced Concepts?
Basic databases work fine for small systems, but modern applications need:
 High performance
 Large data handling
 Security
 Scalability

2. Advanced Concepts Overview

a) Indexing
Improves speed of data retrieval.
👉 Example:
Like index in a book → faster search

b) Normalization
Process of organizing data to reduce redundancy.
👉 Example:
Separate student and course tables instead of repeating data
c) Transactions
A group of operations executed together.
👉 Example: Bank transfer:
 Deduct from A
 Add to B

d) ACID Properties (Very Important)

A good transaction must follow:
 Atomicity → All or nothing
 Consistency → Data remains valid
 Isolation → No interference
 Durability → Changes saved permanently

e) Concurrency Control
Allows multiple users to access database safely.

f) Backup & Recovery

Protects data from loss.

g) Data Security
Includes:
 Authentication
 Authorization
 Encryption

3. Real-World Usage of Advanced Databases

 Banking systems
 Social media apps
 E-commerce (Daraz, Amazon)
 Healthcare systems

4. Pros of Advanced Systems

 Fast performance
 Handles large data
  Better security
 Reliable

5. Cons
 More complex
 Requires expertise
 Higher cost

Exam-Focused Summary
 Database = organized data
 DBMS = software to manage database
 Table, Row, Column = basic structure
 Primary key = unique identifier
 CRUD = basic operations
 Indexing = speed
 Normalization = reduce redundancy
 Transactions + ACID = reliability

Topic 2: Data Models — Object-Relational & Object-Oriented + Advantages over

Relational Model

1. What is a Data Model?

A data model defines how data is structured, stored, and related inside a database.
👉 Simple idea:
It’s the blueprint of how data is organized.

2. Traditional Relational Model (Quick Review)

Before jumping ahead, you need this contrast.
 Data stored in tables (rows & columns)
 Relationships handled using keys (Primary & Foreign)
 Uses SQL
Example
Student_ID Name Course
101 Ali CS
Limitation (Important)
Relational model struggles with:
 Complex data (images, videos, objects)
 Real-world entities (like objects with behavior)
 Reusability
👉 That’s why advanced models were introduced.

3. Object-Oriented Data Model (OODM)

Definition
An Object-Oriented Data Model stores data as objects, similar to object-oriented
programming (OOP).

Key Concepts
a) Object
An object contains:
 Data (attributes)
 Functions (methods)
👉 Example:
Object: Student
Attributes: name, age
Methods: getDetails()

b) Class
A blueprint for creating objects.
👉 Example:
Class: Student

c) Encapsulation
Data + methods combined together.

d) Inheritance
One class can inherit properties from another.
👉 Example:
Person → Student
Example (Real World)
Instead of storing only:
Name = Ali
Age = 20
You store a Student object with:
 Data
 Functions like calculateGrade()

Usage
 Multimedia systems
 CAD systems
 AI applications

Advantages
 Represents real-world entities better
 Supports complex data types
 Code reusability (inheritance)
 Easier integration with programming languages

Disadvantages
 Complex design
 Not widely used commercially
 Performance can be slower

4. Object-Relational Data Model (ORDM)

Definition
An Object-Relational Model is a hybrid model that combines:
 Relational database (tables)
 Object-oriented features
👉 Think of it as:
Relational + OOP = Object-Relational

Key Features
a) User-Defined Types
You can create custom data types.
👉 Example:
Address type = street, city, zip

b) Objects inside Tables

Tables can store complex objects.

c) Inheritance Support
Tables can inherit from other tables.

d) Methods Support
Functions can be attached to data.

Example
Instead of simple table:
Name Address

You can store:

Address = Object (street, city, zip)

Usage
 PostgreSQL (supports OR features)
 Oracle Object-Relational features

Advantages
 Combines best of both worlds
 Handles complex data
 Maintains relational structure
 More flexible than pure relational

Disadvantages
 More complex than relational
 Requires deeper understanding
 Can increase system overhead
5. Comparison: Relational vs OOP vs Object-Relational
Feature Relational Object-Oriented Object-Relational
Structure Tables Objects Tables + Objects
Data Type Simple Complex Complex
Methods No Yes Yes
Inheritance No Yes Yes
Flexibility Low High Medium-High

6. Advantages over Traditional Relational Model (VERY IMPORTANT)

This is likely an exam question.

1. Better Real-World Representation

 Objects represent real entities (student, car, account)

2. Handles Complex Data

 Images, videos, documents, nested data

3. Code Reusability
 Inheritance allows reuse

4. Reduced Data Redundancy

 Better structuring using objects

5. Improved Maintainability
 Easier to update and manage complex systems

6. Integration with Programming Languages

 Works smoothly with OOP languages like C++, Java

7. Exam Tip (Important)

If question comes:
👉 “Why advanced models over relational?”
Just write:
  Relational model handles simple structured data only
 Cannot efficiently represent complex real-world entities
 Object-based models provide:
o Flexibility
o Reusability
o Better performance for complex applications

Quick Revision (Last Minute)

 Data model = structure of data
 OOP model = data as objects
 OR model = mix of relational + objects
 Key features = inheritance, methods, complex types
 Advantage = better real-world representation

Topic 3: File Structures in DBMS (Heap, Sequential, Hashing, B+)

1. What are File Structures in DBMS?

Definition
A file structure defines how records are physically stored on disk in a database.
👉 Simple idea:
It’s about how data is arranged behind the scenes, not just tables you see.

Why are File Structures Important?

Because they affect:
 Speed of searching
 Insertion time
 Deletion efficiency
 Overall performance
👉 Same data, different structure → huge difference in speed.

2. Heap Files (Unordered Files)

Definition
A heap file stores records in no particular order.
How it Works
 New data is simply added at the end
 No sorting, no arrangement

Example
Insert records:
Ali, 20
Ahmed, 22
Sara, 19
Stored exactly in insertion order (random).

Advantages
 Very fast insertion
 Simple to implement
 No overhead of sorting

Disadvantages
 Very slow searching (linear search)
 Not suitable for large data
 Deletion can be messy

Usage
 Small systems
 Temporary data storage

3. Sequential Files
Definition
Data is stored in a sorted order, usually based on a key.

How it Works
 Records are arranged in sequence
 Searching can use binary search

Example
Sorted by Student_ID:
101 → Ali
102 → Ahmed
103 → Sara

Advantages
 Faster searching than heap
 Good for range queries
 Efficient for batch processing

Disadvantages
 Insertion is slow (must maintain order)
 Deletion requires shifting
 Not flexible

Usage
 Banking systems
 Report generation

4. Hashing
Definition
Hashing uses a hash function to map data to a specific location.

How it Works
You apply a function:
Hash(Key) → Address
👉 Example:
ID = 105
Hash function → 105 % 10 = 5
Store at index 5

Advantages
 Very fast searching (O(1))
 Fast insertion and deletion
 Direct access
Disadvantages
 Collisions (two keys → same location)
 Not good for range queries
 Requires good hash function

Usage
 Indexing
 Password storage
 Caching systems

5. B+ Trees (Very Important)

Definition
A B+ Tree is a balanced tree structure used for efficient data storage and retrieval.

Key Idea
 Data is stored in sorted form
 Tree remains balanced
 All actual data is stored in leaf nodes

Structure (Simple View)

 Root node → starting point
 Internal nodes → guide search
 Leaf nodes → store actual data

How Search Works

👉 Example:
 You search key = 50
 Start at root → move down → reach correct leaf

Important Properties
 All leaves are at same level
 Leaf nodes are linked (like a chain)
 Supports fast traversal
Advantages
 Fast searching (O(log n))
 Efficient for large databases
 Supports range queries
 Keeps data sorted

Disadvantages
 More complex to implement
 Slight overhead for maintaining balance

Usage
 Database indexing
 File systems
 Search engines

6. Comparison of File Structures

Feature Heap Sequential Hashing B+ Tree
Order No Yes No Yes
Search Slow Medium Fast Very Fast
Insert Fast Slow Fast Moderate
Range Query No Yes No Yes
Complexity Low Medium Medium High

7. Key Differences (Exam Focus)

👉 Heap vs Sequential
 Heap = unordered, fast insert
 Sequential = ordered, fast search
👉 Hashing vs B+ Tree
 Hashing = fastest for exact match
 B+ Tree = best for range queries

8. Exam Tips (Very Important)

If question comes:
“Which structure is best?”
 Exact search → Hashing
 Range query → B+ Tree
 Fast insert → Heap
 Sorted data → Sequential / B+ Tree

Quick Revision
 File structure = physical storage
 Heap = unordered
 Sequential = sorted
 Hashing = direct access
 B+ Tree = balanced + best overall

Topic 4: Tree Indexing and Access Paths

1. What is Indexing in DBMS?

Definition
An index is a data structure that improves the speed of data retrieval in a database.
👉 Simple idea:
It’s like the index of a book — instead of reading the whole book, you jump directly to the
page.

Example
Without index:
 You scan entire table → slow
With index:
 You jump directly to the record → fast

2. What is Tree Indexing?

Definition
Tree indexing uses a tree data structure (usually B+ Tree) to organize and search data
efficiently.

Why Trees?
Because they:
  Keep data sorted
 Allow fast searching, insertion, deletion
 Work well for large databases

3. B+ Tree Indexing (Core Concept)

This is the most commonly used indexing method in DBMS.

Structure Recap
 Root node → starting point
 Internal nodes → guide search
 Leaf nodes → store actual data (or pointers)
👉 Important:
 Leaf nodes are linked together
 All leaves are at the same level

How Search Works

Let’s say you search Student_ID = 50:
1. Start at root
2. Follow the correct branch
3. Reach leaf node
4. Get data directly
👉 Time complexity: O(log n)

Why B+ Tree is Preferred

 Balanced structure
 Efficient disk access
 Supports range queries

Range Query Example

Find students with ID between 50 and 100:
👉 B+ Tree:
 Go to first value (50)
 Then follow linked leaf nodes
👉 Very efficient
4. Types of Tree Indexing
a) Primary Index
Definition
Index built on primary key (sorted data).
Features
 Data file is already sorted
 One index entry per block
👉 Example:
Student_ID is primary key

b) Secondary Index
Definition
Index built on non-key attribute.
👉 Example:
Search by Name instead of ID

Features
 Data not necessarily sorted
 Can have duplicates

c) Clustered Index
Definition
Data is physically stored in the same order as the index.
👉 Only one clustered index per table

d) Non-Clustered Index
Definition
Index is separate from actual data.
👉 Contains pointers to data

5. What are Access Paths?

This is the second part of your topic.

Definition
An access path is the method used by DBMS to retrieve data from storage.
👉 Simple idea:
“How database decides to get your data”

6. Types of Access Paths

a) Sequential Access
 Data accessed one by one
 Used in sequential files
👉 Example:
Reading full table

b) Direct (Random) Access

 Jump directly to location
 Used in hashing
👉 Very fast

c) Indexed Access
 Uses index (like B+ tree)
 Most common in modern DBMS

7. How DBMS Chooses Access Path

DBMS automatically decides based on:
 Query type
 Available indexes
 Data size

Example
Query:
SELECT * FROM Students WHERE ID = 50;
👉 DBMS may use:
 Index → fast
 Full scan → slow

8. Advantages of Tree Indexing

 Fast searching
  Efficient for large data
 Supports range queries
 Balanced performance

9. Disadvantages
 Extra storage required
 Index maintenance overhead
 Slightly slower insert/update

10. Exam-Focused Differences

👉 Index vs Access Path
 Index = data structure
 Access path = method of retrieving data

👉 Hashing vs Tree Index

 Hashing → best for exact match
 Tree → best for range queries

11. Quick Revision (Before Exam)

 Index = speed up search
 Tree indexing = B+ tree
 Primary index = on primary key
 Secondary index = on non-key
 Access path = way to retrieve data
 Types = sequential, direct, indexed

Topic 5: Transactions, ACID Properties, Transaction States

1. What is a Transaction?
Definition
A transaction is a sequence of operations performed as a single unit of work in a
database.
👉 Simple idea:
Either all operations happen, or none happen.
Real-Life Example (Very Important)
Bank Transfer
You transfer Rs. 1000 from Account A to B:
1. Deduct 1000 from A
2. Add 1000 to B
👉 If step 2 fails and step 1 already happened → problem ❌
👉 So both must succeed together ✔️

Key Point
A transaction ensures:
 Data stays correct
 No partial updates

2. Properties of Transaction (ACID)

This is the most important part of this topic.

ACID Properties

1. Atomicity (All or Nothing)

Definition
A transaction is either fully completed or fully canceled.

Example
 If money deducted but not added → rollback
 System restores original state

Keyword
👉 “All or Nothing”

2. Consistency
Definition
After transaction, database must remain in a valid state.

Example
  Total balance before = after
 No invalid data

Keyword
👉 “Data remains correct”

3. Isolation
Definition
Transactions run independently without affecting each other.

Example
 Two users updating same account
 One should not see incomplete changes of other

Keyword
👉 “No interference”

4. Durability
Definition
Once a transaction is committed, it is permanently saved.

Example
 Even if system crashes → data ‫محفوظ‬

Keyword
👉 “Permanent storage”

3. Summary Table (ACID)

Property Meaning
Atomicity All or nothing
Consistency Valid data
Isolation No interference
Durability Permanent
4. Transaction States
A transaction does not just happen instantly, it goes through different states.

1. Active State
 Transaction is executing
 Operations are running

2. Partially Committed
 Last operation executed
 But not yet saved permanently

3. Committed
 Transaction successfully completed
 Changes are saved permanently

4. Failed
 Error occurs
 Transaction cannot continue

5. Aborted
 Transaction is rolled back
 Database restored to previous state

6. Terminated
 Transaction ends completely
 Resources are released

5. Flow of Transaction States

👉 Easy way to remember:
Active → Partially Committed → Committed → Terminated
↓
Failed → Aborted → Terminated

Simple Explanation
 Start working → Active
  Finished operations → Partially committed
 Saved → Committed
 Error → Failed
 Undo → Aborted

6. Advantages of Transactions
 Ensures data accuracy
 Prevents data loss
 Maintains integrity
 Supports multi-user environment

7. Disadvantages
 Performance overhead
 Complex to manage
 Requires recovery mechanisms

8. Exam Tips (Very Important)

👉 If question: “Explain ACID properties”
Write:
 Definition
 4 properties
 Example (bank transaction)

👉 If question: “Explain transaction states”

 Draw flow diagram
 Explain each state briefly

Quick Revision (Last Minute)

 Transaction = group of operations
 ACID = Atomicity, Consistency, Isolation, Durability
 States = Active → Commit / Fail
 Bank example = best explanation

Topic 6: Problems in Concurrency

(Lost Update, Dirty Read, Deadlocks)

1. What is Concurrency?
Definition
Concurrency means multiple transactions executing at the same time in a database.
👉 Simple idea:
Many users accessing and modifying data simultaneously.

Example
 Ali is transferring money
 Ahmed is checking balance
👉 Both happening at the same time = concurrency

Why is Concurrency Important?

 Improves performance
 Supports multiple users
 Makes systems faster
👉 But… it creates problems if not handled properly ❌

2. Concurrency Problems

1. Lost Update Problem

Definition
Occurs when two transactions update the same data, and one update is overwritten
(lost).

Example
Initial balance = 1000
Transaction T1:
 Reads balance → 1000
 Adds 500 → 1500
Transaction T2:
 Reads balance → 1000
 Subtracts 200 → 800
👉 If T2 writes after T1:
Final value = 800 ❌
(But correct should be 1300)

Why it Happens
 No proper synchronization
 Both transactions read same old value

Solution (Idea)
 Use locking
 Use isolation levels

2. Dirty Read Problem

Definition
Occurs when a transaction reads uncommitted data from another transaction.

Example
Transaction T1:
 Updates balance → 2000
 Not committed yet
Transaction T2:
 Reads balance → 2000

👉 Now if T1 fails and rolls back:

Actual balance = 1000 ❌
But T2 already used wrong value

Why it Happens
 Reading data before it is finalized

Solution
 Do not allow reading uncommitted data
 Use higher isolation levels

3. Deadlock
Definition
A deadlock occurs when two or more transactions are waiting for each other forever.

Example
Transaction T1:
 Locks Account A
 Wants Account B
Transaction T2:
 Locks Account B
 Wants Account A

👉 Result:
 T1 waiting for T2
 T2 waiting for T1
 No one proceeds ❌

Simple Analogy
Two people:
 One holds pen
 One holds paper
 Each waiting for the other
👉 Stuck forever

Why it Happens
 Circular waiting
 Improper locking

Solutions
 Deadlock prevention
 Deadlock detection
 Timeout (cancel one transaction)

3. Summary Table
 Problem Cause Result

Lost Update Overwriting updates Wrong final value

Dirty Read Reading uncommitted data Incorrect data

Deadlock Circular waiting System stuck

4. Key Differences (Exam Focus)

👉 Lost Update vs Dirty Read
 Lost update → data overwritten
 Dirty read → reading uncommitted data

👉 Deadlock vs Others
 Deadlock → system stops
 Others → wrong data but system runs

5. Advantages of Concurrency (Why we still use it)

 Faster execution
 Better resource usage
 Multi-user support

6. Disadvantages
 Data inconsistency
 Complex control
 Risk of deadlocks

7. Exam Tips (Very Important)

👉 If question:
“Explain concurrency problems”
Do this:
1. Define concurrency
2. Explain each problem
3. Give example (VERY IMPORTANT)

👉 Keywords to remember:
 Lost Update → overwrite
  Dirty Read → uncommitted
 Deadlock → waiting forever

Quick Revision
 Concurrency = multiple transactions
 Lost update = overwrite problem
 Dirty read = uncommitted data
 Deadlock = stuck forever

Topic 7: Concurrency Control Methods (Locking, Timestamp Ordering)

1. What is Concurrency Control?

Definition
Concurrency control is the technique used to ensure that multiple transactions execute
safely without causing data inconsistency.
👉 Simple idea:
It manages who can access what data and when.

Why Do We Need It?

Because without it:
 Lost updates happen
 Dirty reads occur
 Deadlocks may happen
👉 So DBMS uses control mechanisms to keep data correct and consistent.

2. Method 1: Locking (Most Common)

Definition
Locking is a method where a transaction must lock a data item before accessing it.
👉 Like:
“Don’t touch this data, I’m using it.”

Types of Locks

1. Shared Lock (Read Lock)

  Used when transaction wants to read data
 Multiple transactions can read at the same time
👉 Example:
Many users checking account balance

2. Exclusive Lock (Write Lock)

 Used when transaction wants to modify data
 No other transaction can read or write
👉 Example:
Updating balance

Simple Rule
Operation Lock Type
Read Shared Lock
Write Exclusive Lock

Example
Transaction T1:
 Locks record (exclusive)
 Updates value
Transaction T2:
 Must wait until T1 finishes

Two-Phase Locking (2PL) (Important)

Definition
Transaction follows two phases:

1. Growing Phase
 Acquires locks
 Cannot release any lock

2. Shrinking Phase
 Releases locks
 Cannot acquire new locks
👉 Ensures consistency

Advantages of Locking
 Prevents lost updates
 Ensures data consistency
 Easy to understand

Disadvantages
 Can cause deadlocks
 Waiting time increases
 Performance overhead

3. Method 2: Timestamp Ordering

Definition
Each transaction is assigned a timestamp (unique time value), and execution is controlled
based on that order.
👉 Simple idea:
“Who came first gets priority.”

How it Works
 Every transaction gets a timestamp:
T1 = 1
T2 = 2
👉 T1 is older, so it has higher priority

Rules
 Older transaction executes first
 If conflict occurs:
o Newer transaction is delayed or rejected

Example
T1 (older) wants to write
T2 (newer) already wrote
👉 Conflict → T2 is rolled back
Key Idea
Maintains serial order based on time

Advantages
 No deadlocks ✔️
 Simple ordering mechanism
 Good for high concurrency

Disadvantages
 Transactions may be restarted frequently
 Can cause starvation (some never complete)
 Complex to manage timestamps

4. Comparison: Locking vs Timestamp

Feature Locking Timestamp
Concept Locks data Uses time order
Deadlocks Possible Not possible
Waiting Yes Less waiting
Restart Rare Frequent
Complexity Moderate Higher

5. Which One is Better?

👉 Depends on situation:
 Need strict control → Locking
 Want no deadlocks → Timestamp

6. Connection with Previous Topic

👉 Problems → Solutions:
Problem Solution

Lost Update Locking

Dirty Read Locking

Deadlock Timestamp (avoids it)

7. Exam Tips (Very Important)
👉 If question:
“Explain concurrency control methods”
Write:
1. Definition
2. Locking (types + 2PL)
3. Timestamp ordering
4. Comparison

👉 Keywords:
 Locking → shared, exclusive
 2PL → growing + shrinking
 Timestamp → priority by time

Quick Revision
 Concurrency control = manage multiple transactions
 Locking = control using locks
 Timestamp = control using time order
 Locking → deadlock possible
 Timestamp → no deadlock

Topic 8: Multiversion Concurrency Control (MVCC)

1. What is MVCC?
Definition
Multiversion Concurrency Control (MVCC) is a method where the database keeps
multiple versions of data to allow transactions to work without interfering with each
other.
👉 Simple idea:
Instead of locking data, DBMS creates different copies (versions) of the same data.

2. Why MVCC is Needed

From previous topic, problems were:
 Dirty reads
  Waiting due to locks
 Deadlocks
👉 MVCC solves these by:
 Allowing readers and writers to work simultaneously
 Avoiding unnecessary blocking

3. Core Idea of MVCC

👉 Instead of:
“Wait until data is free”
👉 It does:
“Use another version of data”

Example (Very Important)

Initial balance = 1000
Transaction T1:
 Updates balance → 2000
 Not committed yet
Transaction T2:
 Reads balance

👉 In locking:
T2 must wait ❌
👉 In MVCC:
T2 reads old version (1000) ✔️

Key Insight
 Readers don’t block writers
 Writers don’t block readers

4. How MVCC Works

1. Version Creation
Every time data is updated:
 A new version is created
 Old version is kept
2. Timestamp Assignment
Each transaction gets a timestamp.

3. Visibility Rule
A transaction can only see:
 Data versions that were committed before it started

Simple Flow
Old Version → New Version → Newer Version
Transactions read the correct version based on time.

5. Important Concepts

a) Read Consistency
 Each transaction sees a consistent snapshot

b) Snapshot
 A “view” of database at a specific time

c) Version Control
 Multiple versions stored temporarily

6. Advantages of MVCC

1. No Dirty Reads
 Only committed data is visible

2. No Locking Delays
 Reads don’t wait

3. High Concurrency
 Many users can work simultaneously
4. Better Performance
 Especially for read-heavy systems

7. Disadvantages

1. Extra Storage Required

 Multiple versions stored

2. Complexity
 Harder to implement

3. Garbage Collection Needed

 Old versions must be deleted

8. Comparison with Locking

Feature Locking MVCC
Blocking Yes No
Read Speed Slower Faster
Complexity Low High
Storage Less More

9. Real-World Usage
MVCC is used in:
 MySQL (InnoDB)
 PostgreSQL
 Oracle
👉 That’s why modern systems feel fast even with many users.

10. Exam Tips (Very Important)

👉 If question:
“Explain MVCC”
Write:
1. Definition
2. Working (versions + timestamps)
 3. Example (old vs new version)
4. Advantages

👉 Keywords:
 Multiple versions
 Snapshot
 No blocking
 High concurrency

Quick Revision
 MVCC = multiple copies of data
 Readers don’t wait
 Uses timestamps + versions
 Prevents dirty reads
 Improves performance

Topic 9: Need for Recovery, Log-Based Recovery, Deferred & Immediate

1. Need for Recovery in DBMS

Definition
Database recovery is the process of restoring the database to a correct state after a failure.

Why recovery is needed?

Because failures happen all the time:
 Power failure ⚡
 System crash 💻
 Disk failure 💾
 Transaction failure ❌
👉 Without recovery → data becomes inconsistent or lost

Simple Idea
Database should behave like:
“Nothing bad happened, everything is still correct”
Example
Bank transaction:
 Money deducted
 System crashes before adding to receiver
👉 Recovery ensures:
 Either both steps happen
 Or nothing happens

2. What is Log-Based Recovery?

Definition
A log-based recovery system keeps a record (log) of all database operations so it can redo
or undo changes after failure.

What is a Log?
A log file stores:
 Transaction start
 Read/write operations
 Commit or abort

Example Log
T1: START
T1: WRITE A = 100
T1: COMMIT

Why logs are important?

Because they allow DBMS to:
 REDO (reapply changes)
 UNDO (cancel changes)

3. Two Types of Log-Based Recovery

A) Deferred Update (Deferred Modification)

Definition
In deferred update, changes are not written to database immediately. They are stored
and applied only after transaction commits.

How it works
1. Transaction starts
2. Changes stored in log only
3. No database update yet
4. After commit → changes applied

Example
T1:
 Update balance = 500
 System crashes before commit
👉 Result:
 No change in database ✔️
 No undo needed

Key Idea
👉 “Wait until commit before writing”

Advantages
 No undo required
 Safe from partial updates
 Simple recovery

Disadvantages
 Slower performance
 Uses memory for temporary storage

B) Immediate Update

Definition
In immediate update, changes are written to the database as soon as they happen, even
before commit.
How it works
1. Transaction starts
2. Update database immediately
3. Log stores old value (for undo)
4. Commit confirms changes

Example
T1:
 Writes balance = 500
 System crashes before commit
👉 Recovery:
 Undo using log ✔️
 Restore old value

Key Idea
👉 “Write immediately, fix later if needed”

Advantages
 Faster execution
 Real-time updates

Disadvantages
 Requires undo mechanism
 More complex recovery
 Risk of inconsistency during crash

4. Difference: Deferred vs Immediate

Feature Deferred Update Immediate Update
Write time After commit Immediately
Recovery needed Redo only Undo + Redo
Speed Slower Faster
Risk Low Higher
Complexity Simple Complex
5. Log-Based Recovery Techniques

1. Undo Operation
 Reverses changes
 Used in immediate update

2. Redo Operation
 Reapplies changes
 Used in both systems

6. Important Concept: Write-Ahead Logging (WAL)

Definition
Before making changes to database, log must be written first.
👉 Rule:
Log first, data later

Why important?
Because if crash happens:
 Log is used to recover everything

7. Advantages of Recovery System

 Ensures data consistency
 Protects against crashes
 Maintains integrity
 Supports transactions

8. Disadvantages
 Extra storage (logs)
 Performance overhead
 Complex system design

9. Exam Tips (VERY IMPORTANT)

👉 If question:
“Explain log-based recovery”
Write:
 Definition
 Log structure
 Undo/Redo

👉 If question:
“Differentiate Deferred and Immediate”
Draw table (like above) + example

10. Quick Revision

 Recovery = restore database after failure
 Log = record of operations
 Deferred = update after commit
 Immediate = update before commit
 Undo = reverse
 Redo = reapply

Topic 10: Shadow Paging, Checkpoints, ARIES

1. Shadow Paging

Definition
Shadow paging is a recovery technique where the database keeps a copy (shadow) of
the page table before making changes.
👉 Simple idea:
You never overwrite original data directly. You work on a “shadow copy”.

How it Works
Database maintains:
 Current page table
 Shadow page table (backup)

Steps:
 1. Transaction starts
2. Copy page table → shadow table
3. Make changes in new pages
4. If commit → switch pointer to new table
5. If crash → discard changes, use shadow table

Example
Before update:
A = 100
Transaction updates A = 200:
 Shadow still has A = 100
 New page has A = 200
👉 If crash → system uses shadow (100)

Advantages
 No undo/redo needed
 Very simple recovery
 Crash-safe

Disadvantages
 Waste of storage
 Copying page tables is expensive
 Not good for large systems

Key Idea
👉 “Never overwrite, always copy first”

2. Checkpoints

Definition
A checkpoint is a point in time where the DBMS saves its current state to reduce
recovery time.

Why Checkpoints are needed?

Without checkpoints:
 DBMS has to scan entire log after crash
👉 very slow
With checkpoints:
 Only recent transactions are checked

How it Works
At checkpoint:
 All committed data is written to disk
 Log is saved
 Active transactions recorded

Simple Flow
Transaction log → → → CHECKPOINT → → → crash → recovery starts here

Types of Checkpoints
1. Simple Checkpoint
 Stop all transactions temporarily
 Save everything

2. Fuzzy Checkpoint
 Does NOT stop transactions
 Works in background (modern systems)

Advantages
 Faster recovery
 Reduces log scanning
 Improves efficiency

Disadvantages
 Overhead during checkpoint
 Complex in fuzzy mode

Key Idea
👉 “Save progress so recovery is faster”

3. ARIES (Very Important ⭐)

ARIES is a real-world recovery algorithm used in modern DBMS (like PostgreSQL,
IBM DB2).

Full Form (not always asked)

Algorithm for Recovery and Isolation Exploiting Semantics

Core Idea
ARIES uses:
 Logging
 Undo/Redo
 Checkpoints
👉 It is most advanced recovery method

4. Three Phases of ARIES

1. Analysis Phase
Purpose:
Find:
 What transactions were active
 What needs redo/undo
👉 DBMS scans log from last checkpoint

2. Redo Phase
Purpose:
Reapply all changes from log
👉 Ensures database reflects all committed work

3. Undo Phase
Purpose:
Remove effects of incomplete transactions
👉 Fixes uncommitted work
Simple Flow
Analysis → Redo → Undo

5. Key Features of ARIES

1. Write-Ahead Logging (WAL)

 Log written before data changes

2. Repeating History
 Redo everything (even if already done)

3. Fine-Grained Undo
 Undo only failed transactions

6. Advantages of ARIES
 Very reliable
 Used in real DBMS
 Handles crashes efficiently
 Supports high concurrency

7. Disadvantages
 Very complex
 Requires large logs
 Hard to implement

8. Comparison Table
Feature Shadow Paging Checkpoints ARIES

Complexity Low Medium High

Speed Fast recovery Faster recovery Very efficient

Storage High Medium Log-based

Used in real systems Rare Yes Yes (very common)

9. Exam Tips (VERY IMPORTANT)
👉 If question:
“Explain Shadow Paging”
 Copy page table
 Shadow vs current
 No undo/redo

👉 If question:
“Explain Checkpoints”
 Definition
 Purpose
 Types

👉 If question:
“Explain ARIES”
Write:
 3 phases
 WAL
 Redo + Undo concept

10. Quick Revision

 Shadow paging = copy system
 Checkpoint = save system state
 ARIES = modern recovery algorithm
 ARIES phases = Analysis → Redo → Undo

Topic 11: Integrity Constraints (Primary Key, Foreign Key, CHECK, Domain)

1. What are Integrity Constraints?

Definition
Integrity constraints are rules applied on a database to ensure that data is accurate,
valid, and consistent.
👉 Simple idea:
They are the “rules” that prevent wrong data from entering the database.

Why are they needed?

Without constraints:
 Duplicate data can appear
 Invalid values can be stored
 Relationships between tables break
👉 So constraints keep database clean and reliable.

2. Primary Key Constraint

Definition
A Primary Key is a column (or set of columns) that uniquely identifies each record in a table.

Rules
 Must be unique
 Cannot be NULL
 Only one primary key per table

Example
Student_ID Name Age

101 Ali 20

102 Ahmed 21

👉 Student_ID = Primary Key

Key Idea
👉 “One record = one unique identity”

Advantages
 Ensures uniqueness
 Helps fast searching (indexing)
 Prevents duplicate records

Disadvantages
 Cannot allow NULL values
 Must be carefully chosen
3. Foreign Key Constraint

Definition
A Foreign Key is a column that creates a relationship between two tables.
👉 It refers to the Primary Key of another table.

Example
Table 1: Students
Student_ID Name

101 Ali

Table 2: Courses
Course_ID Student_ID
C1 101

👉 Student_ID in Courses = Foreign Key

Key Idea
👉 “Foreign key connects tables”

Rules
 Must match primary key in parent table
 Can have duplicates
 Can be NULL (sometimes allowed)

Advantages
 Maintains relationships
 Ensures referential integrity
 Avoids orphan records

Disadvantages
 Slows insert/update
 Complex dependency management
4. CHECK Constraint

Definition
A CHECK constraint ensures that values in a column satisfy a specific condition.

Example
Age must be greater than 18:
Age > 18

Table Example
Name Age

Ali 20

Sara 17 ❌

👉 17 is not allowed

Key Idea
👉 “Value must satisfy condition”

Advantages
 Prevents invalid data
 Enforces business rules

Disadvantages
 Cannot handle complex logic easily
 May slow inserts slightly

5. Domain Constraint

Definition
A domain constraint defines the type of values a column can accept.
👉 It controls:
 Data type
 Range
  Format

Example
Age column:
 Only integers
 Range: 0–120
Age: INTEGER (0–120)

Name column:
 Only text
 Cannot accept numbers

Key Idea
👉 “Defines valid value set for a column”

Advantages
 Prevents wrong data types
 Maintains consistency
 Improves data quality

Disadvantages
 Less flexible
 Requires strict design

6. Summary Table
Constraint Purpose Example
Primary Key Unique identity Student_ID
Foreign Key Relationship Student_ID in another table
CHECK Condition rule Age > 18
Domain Data type rules Integer, String, Range

7. Key Differences (Exam Focus)

👉 Primary vs Foreign Key
 Primary = unique identifier
  Foreign = link between tables

👉 CHECK vs Domain
 CHECK = condition rule
 Domain = data type + range definition

8. Why Integrity Constraints Matter

They ensure:
 No duplicate data
 No invalid values
 Proper relationships
 Reliable database structure

9. Exam Tips (VERY IMPORTANT)

👉 If question:
“Explain integrity constraints”
Write:
 Definition
 Types
 Examples of each
 Importance

👉 Keywords to remember:
 Primary key → uniqueness
 Foreign key → relationship
 CHECK → condition
 Domain → data type rules

10. Quick Revision

 Constraints = rules for data
 Primary key = unique identity
 Foreign key = link tables
 CHECK = condition validation
 Domain = data type restriction
Topic 12: Constraints (Referential) & Security (Authentication, Authorization)

Part A: Constraints (Referential Integrity)

1. What are Constraints?
Definition
Constraints are rules applied on database tables to ensure valid, correct, and consistent
data.
👉 You already saw primary key, foreign key, etc.
Now we focus more on referential constraint, which is closely tied to foreign key.

2. Referential Integrity Constraint

Definition
Referential integrity ensures that relationships between tables remain consistent and
valid.
👉 Simple idea:
If one table refers to another, that reference must always be valid.

Example
Table 1: Students
Student_ID Name
101 Ali
102 Ahmed

Table 2: Enrollments
Course Student_ID
CS101 101
CS102 999 ❌
👉 Problem:
Student_ID = 999 does not exist in Students table

What Referential Integrity does

It prevents invalid references like this.

Rules
 Foreign key must match a primary key in parent table
  Cannot insert invalid foreign key
 Cannot delete parent record if child depends on it (unless cascade)

Actions on Delete/Update
1. CASCADE
 If parent deleted → child also deleted

2. SET NULL
 Child foreign key becomes NULL

3. RESTRICT / NO ACTION
 Prevent deletion if dependency exists

Advantages
 Maintains consistency between tables
 Prevents orphan records
 Keeps relationships valid

Disadvantages
 Slower updates/deletes
 Adds complexity

Key Idea
👉 “No broken links between tables”

Part B: Database Security

Now we move to security concepts.

3. What is Database Security?

Definition
Database security means protecting data from:
 Unauthorized access
 Modification
 Loss or theft
Why is it important?
 Protect sensitive data (bank, student records)
 Prevent hacking
 Maintain trust and integrity

4. Authentication

Definition
Authentication is the process of verifying the identity of a user.
👉 Simple idea:
“Who are you?”

Examples
 Username + password
 OTP verification
 Fingerprint / Face ID

How it works
User tries to log in:
1. System asks credentials
2. System checks database
3. If correct → access granted

Advantages
 Prevents unauthorized access
 First layer of security

Disadvantages
 Passwords can be stolen
 Weak passwords reduce security

Key Idea
👉 “Identity check”
5. Authorization

Definition
Authorization determines what an authenticated user is allowed to do.
👉 Simple idea:
“You are allowed to do THIS, but not THAT”

Example
After login:
 Admin → can delete data
 Student → can only view data

Types of Permissions
 Read
 Write
 Update
 Delete

Difference from Authentication

Feature Authentication Authorization
Purpose Verify identity Grant permissions
Question Who are you? What can you do?
Happens first Yes After authentication

Advantages
 Controls access levels
 Protects sensitive data
 Prevents misuse

Disadvantages
 Complex role management
 Misconfiguration risk

Key Idea
👉 “Access control after identity check”

6. Security vs Integrity (Important Exam Angle)

 Security → protects from unauthorized access
 Integrity → ensures data is correct and consistent

7. Summary Table
Concept Meaning
Referential Integrity Valid relationships between tables

Authentication Verify user identity

Authorization Control user permissions

8. Exam Tips (VERY IMPORTANT)

👉 If question:
“Explain referential integrity”
Write:
 Definition
 Example with foreign key error
 Actions (CASCADE, SET NULL)

👉 If question:
“Authentication vs Authorization”
Always draw table + short explanation

9. Quick Revision
 Referential integrity = valid table relationships
 Authentication = identity check
 Authorization = permission control
 Security = protect database

Topic 13: DBA Responsibilities, Role Management & Limitations of RDBMS

Part A: DBA Responsibilities

1. Who is a DBA?
Definition
A Database Administrator (DBA) is the person responsible for managing, maintaining,
and securing the database system.
👉 Simple idea:
DBA is the “caretaker” of the database.

2. Responsibilities of DBA

1. Database Design
 Designing tables, relationships, schema
 Choosing data structures

2. Security Management
 Controlling who can access data
 Setting authentication and authorization

3. Backup and Recovery

 Creating backups regularly
 Restoring data after failure

4. Performance Tuning
 Making queries faster
 Optimizing indexing
 Reducing system load

5. User Management
 Creating user accounts
 Assigning roles and permissions

6. Data Integrity Maintenance

 Ensuring constraints are applied correctly
 Preventing invalid data

7. Monitoring System
  Checking database health
 Fixing errors and issues

Key Idea
👉 “DBA ensures database runs smoothly, securely, and efficiently”

Advantages of DBA Role

 Better security
 High performance
 Data consistency
 Controlled access

Disadvantages
 Requires expert knowledge
 High responsibility
 Can be costly for organizations

Part B: Role Management

1. What is Role Management?

Definition
Role management is the process of assigning roles (permissions groups) to users instead
of giving individual permissions.
👉 Simple idea:
Instead of giving rights one by one → give a “role”.

2. What is a Role?
A role is a set of permissions grouped together.

Example
Role Permissions

Admin Full access

Student Read only

 Teacher Read + Update

3. How it works
1. Create role (Admin, User, etc.)
2. Assign permissions to role
3. Assign role to user

Advantages
 Easier management
 More secure
 Scalable for large systems

Disadvantages
 Role complexity increases in big systems
 Misconfiguration risk

Key Idea
👉 “Manage users through roles, not individual permissions”

Part C: Limitations of RDBMS

1. What is RDBMS? (Quick Reminder)

Relational Database Management System stores data in:
 Tables (rows & columns)
 Uses SQL

2. Limitations of RDBMS

1. Poor Handling of Complex Data

 Not good for images, videos, multimedia
 Only structured data works well

2. Scalability Issues
  Difficult to scale for very large distributed systems
 Big data handling is weak

3. Performance Overhead
 Joins between large tables are slow
 Complex queries reduce performance

4. Rigid Schema
 Fixed structure (columns must be defined beforehand)
 Hard to modify structure frequently

5. Not Suitable for Unstructured Data

 Cannot efficiently handle:
o JSON
o Multimedia
o Real-time streaming data

6. Cost
 Enterprise RDBMS systems are expensive

7. Complexity in Distributed Systems

 Hard to manage across multiple servers

Key Idea
👉 “RDBMS is powerful but not flexible for modern large-scale, unstructured data”

Advantages of RDBMS (for balance in exams)

 Strong consistency
 Data integrity
 Easy querying with SQL
 Well-structured data storage

3. Comparison Idea (Exam Gold)

Feature RDBMS Strength Limitation
 Structure Organized Rigid
Data Type Structured No unstructured support
Scalability Medium Limited
Performance Good for small-medium Slow for huge data

4. Exam Tips (VERY IMPORTANT)

👉 If question:
“Explain DBA responsibilities”
Write:
 Design
 Security
 Backup
 Performance
 User management

👉 If question:
“Limitations of RDBMS”
Always write:
 Structured only
 Scalability issues
 Performance overhead
 Not good for multimedia

👉 If question:
“Role management”
 Define role
 Example (Admin/User)
 Advantages

5. Quick Revision
 DBA = database manager
 Role = group of permissions
 Role management = assign roles instead of users
 RDBMS limitations = rigid, slow for big/unstructured data