ADVANCED DATABASE

MANAGEMENT SYSTEMS
Course Code DC-220
Introduction to Advanced Data Models

Traditional Relational Data Models (RDBMS) are powerful but limited for handling
complex, real-world data like images, multimedia, or user-defined data types.

Advanced Data Models were developed to overcome these limitations, providing:

• Better handling of complex data

• Support for user-defined types
• Encapsulation of data and behavior

The two most common advanced models are:

1. Object-Relational Data Model (ORDBMS)

2. Object-Oriented Data Model (OODBMS)

Object-Relational Data Model (ORDBMS)

The Object-Relational Data Model combines the relational model with some features of
object-oriented programming.

Key Features

Feature Description
Extended Data
Supports user-defined data types, collections, and multimedia.
Types
Inheritance Enables table hierarchies.
Encapsulation Allows methods/functions tied to user-defined types.
Retains SQL as query language with extensions (SQL:1999,
Compatibility
SQL:2003).

Example

-- Define a custom type

CREATE TYPE Address AS OBJECT (
street VARCHAR(50),
city VARCHAR(30),
zip VARCHAR(10)
);

-- Use the type in a table

CREATE TABLE Customers (
id INT PRIMARY KEY,
name VARCHAR(50),
addr Address
);

-- Access object attributes

SELECT [Link]
FROM Customers c
WHERE [Link] = '54000';

Popular ORDBMS Systems

• PostgreSQL
• Oracle 12c+
• IBM DB2

Advantages

• Supports complex data types.

• Maintains backward compatibility with relational systems.
• Better suited for multimedia and GIS data.

Disadvantages

• More complex to design and manage.

• Performance overhead due to object features.

Object-Oriented Data Model (OODBMS)

The Object-Oriented Data Model integrates object-oriented programming concepts

directly into the database.

It treats everything as an object, just like in OOP languages such as Java or C++.

Key Features

Feature Description
Objects Data and methods are encapsulated together.
Classes Objects are grouped into classes with attributes and methods.
Inheritance Classes can inherit attributes and methods from parent classes.
Persistence Objects can be stored and retrieved from the database.
Complex Objects Supports storage of images, videos, arrays, and user-defined types.

Example (Pseudo Code)

// Define a class
class Customer {
int id;
 String name;
Address addr;

void display() {
[Link](name + " - " + [Link]);
}
}

// Store object in OODBMS

db. Store(new Customer(101, "Ali", new Address("Street 10", "Lahore", "54000")));

Popular OODBMS Systems

• db4o (Database for Objects)

• Object DB
• Versant ODBMS
• GemStone/S

Advantages

• Seamless integration with OOP languages.

• No mismatch between programming objects and database schema.
• Ideal for complex, hierarchical data.

Disadvantages

• Lack of standard query language like SQL.

• Limited adoption compared to relational systems.
• Migration and interoperability can be challenging.

4. Comparison of ORDBMS vs. OODBMS

Feature ORDBMS OODBMS

Data Structure Tables + Objects Pure Objects

Query
Extended SQL OQL or APIs
Language

Compatibility Compatible with relational databases Requires specialized systems

Complex relational data with some object Full object-oriented

Best Use
features applications

Use Cases
Model Common Applications
Multimedia databases, GIS, data warehousing,
ORDBMS
scientific databases
CAD/CAM, real-time systems, simulations,
OODBMS
complex engineering projects

File Organization Concepts

File organization refers to how data is stored in files on secondary storage (like hard drives).
The organization affects data access speed, efficiency, and ease of maintenance.
Types of File Organizations
1. Heap (Unordered) File Organization
o Records are stored randomly as they arrive.
o Use case: Temporary data storage.
o Pros: Simple, fast insertion.
o Cons: Slow search and updates.
2. Sequential (Ordered) File Organization
o Records are stored in sorted order (usually by key).
o Use case: Batch processing.
o Pros: Efficient for range queries.
o Cons: Insertion and deletion are slow.
3. Hashed File Organization
o Uses a hash function to calculate the location of a record.
o Use case: Fast access for exact match queries.
o Pros: Very fast lookup.
o Cons: Not good for range queries.
4. Clustered File Organization
o Related records from different tables are stored together.
o Use case: Queries that join tables frequently.

2. Transaction Processing
A transaction is a logical unit of work that must be either fully completed or fully rolled
back.
ACID Properties
• A – Atomicity: Entire transaction is done or not done.
• C – Consistency: Database moves from one consistent state to another.
• I – Isolation: Transactions do not interfere with each other.
• D – Durability: Once committed, changes persist even after system failure.
States of a Transaction
• Active → Partially Committed → Committed → Terminated
• If an error occurs: Active → Failed → Aborted → Rolled Back

3. Concurrency Control Techniques

Concurrency control ensures correctness when multiple transactions execute
simultaneously.
Techniques
1. Lock-Based Protocols
o Shared (S) Lock: For read.
o Exclusive (X) Lock: For write.
o Variants:
▪ Two-Phase Locking (2PL): Ensures serializability.
▪ Strict 2PL: Prevents cascading aborts.
2. Timestamp Ordering
o Each transaction gets a timestamp; operations are executed in timestamp order.
3. Optimistic Concurrency Control
o Transactions execute without locks and validate before commit.
4. Multiversion Concurrency Control (MVCC)
o Creates versions of data for consistent reads without blocking writers.

4. Recovery Techniques
Recovery ensures the database can be restored to a consistent state after a failure.
Types of Failures
• Transaction Failure: Logical errors, system crashes.
• System Failure: Power outage, OS crash.
 • Media Failure: Disk crash or corruption.
Techniques
1. Log-Based Recovery
o Write-Ahead Logging (WAL): All actions recorded in a log before execution.
o Undo/Redo operations during recovery.
2. Checkpointing
o Saves a snapshot of the database state periodically to speed up recovery.
3. Shadow Paging
o Maintains two versions of the database pages — current and shadow — to avoid
inconsistencies.
4. Backup & Restore
o Periodic backups are used to restore the database if a crash occurs.

Query Processing and Optimization

Query processing is the sequence of steps a DBMS follows to execute an SQL query
efficiently.
The goal is to minimize response time and resource usage.
Stages of Query Processing
1. Parsing and Translation
o SQL query is checked for syntax and semantics.
o Converted into an internal query tree or relational algebra expression.
2. Optimization
o The system selects the best execution plan based on cost estimation.
o Uses statistics such as table size, indexes, and selectivity.
3. Evaluation
o The query execution plan is executed to retrieve the results.
Types of Query Optimization
1. Heuristic Optimization (Rule-Based)
o Uses rules to simplify and rearrange the query.
o Example: Pushing selection closer to the base table.
2. Cost-Based Optimization (CBO)
 o Estimates cost of multiple execution plans.
o Chooses the plan with the lowest cost based on factors like:
▪ Disk I/O
▪ CPU usage
▪ Memory usage
Optimization Techniques
• Use of indexes for faster lookups.
• Query rewriting to simplify expressions.
• Join ordering and methods (nested loop, hash join, merge join).
• Materialized views for frequently used queries.

Database Programming
Database programming uses procedural extensions of SQL to build more powerful
applications.
A. PL/SQL (Procedural Language/SQL)
• Platform: Oracle Databases
• Features:
o Variables, loops, and conditional statements.
o Functions, procedures, and triggers.
o Exception handling for robust programming.
Example:
DECLARE
v_salary NUMBER;
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 101;
DBMS_OUTPUT.PUT_LINE('Salary is: ' || v_salary);
END;
B. T-SQL (Transact-SQL)
• Platform: Microsoft SQL Server
• Features:
o Supports procedural logic.
 o Transaction management.
o Error handling with TRY...CATCH blocks.
Example:
DECLARE @Salary DECIMAL(10,2);
SELECT @Salary = salary FROM Employees WHERE employee_id = 101;
PRINT 'Salary is: ' + CAST(@Salary AS VARCHAR);
Common Uses of Database Programming
• Stored Procedures: Precompiled SQL blocks for performance and security.
• Triggers: Automatic execution of code on events like INSERT or UPDATE.
• Functions: Reusable code that returns a value.
• Cursors: Row-by-row processing of query results.
• Error and Transaction Handling: Ensuring consistency with COMMIT and
ROLLBACK.
Integrity in Databases
Integrity ensures accuracy, consistency, and validity of data in the database.
Types of Integrity
1. Entity Integrity
o Each table must have a primary key.
o No primary key column can have NULL values.
2. Referential Integrity
o Foreign key values must match primary key values in related tables or be
NULL.
o Prevents orphaned records.
3. Domain Integrity
o Ensures values in a column are within a defined range, format, or type.
o Example: age column must be between 0 and 120.
4. User-Defined Integrity
o Business-specific rules.
o Example: Salary of an employee cannot exceed the manager’s salary.
Security in Databases
Database security protects data from unauthorized access, misuse, or corruption.
Security Mechanisms
• Authentication
o Verifies user identity (username, password, biometrics, or tokens).
• Authorization and Access Control
o Grants specific privileges using roles and permissions.
o Example: GRANT SELECT ON Employees TO analyst;
• Encryption
o Secures data at rest (disk) and in transit (network).
• Auditing and Monitoring
o Logs actions for detecting suspicious activity.
• Backup and Recovery Security
o Ensures secure storage of backups to avoid data leaks.

3. Database Administration (DBA)

A Database Administrator (DBA) manages and maintains the database for performance,
security, and availability.
Key Responsibilities
1. Role Management
o Creating roles for users and assigning privileges.
o Example (Oracle):
o CREATE ROLE data_analyst;
o GRANT SELECT, INSERT ON sales TO data_analyst;
2. Managing Database Access
o Controlling who can access the database and what they can do.
o Use GRANT and REVOKE commands in SQL.
o Example (SQL Server):
o CREATE LOGIN user1 WITH PASSWORD = 'pass123';
o GRANT SELECT, UPDATE ON Employees TO user1;
3. Views for Security
o Views provide limited, filtered access to data.
o Example:
 o CREATE VIEW EmployeePublic AS
o SELECT emp_id, emp_name, department
o FROM Employees;
o Users can query this view without accessing sensitive columns like salary or
SSN.
4. Performance Monitoring
o Optimizing queries and indexing for faster response times.
5. Backup and Recovery
o Scheduling regular backups and creating disaster recovery plans.

Physical Database Design and Tuning

Physical design focuses on how data is stored, accessed, and optimized in the DBMS to
improve performance.

Key Steps

• Choosing Storage Structures

o Tablespaces, data files, and partitions for better organization.
• Indexing
o Using B-tree indexes, hash indexes, or composite indexes to speed up queries.
• Denormalization
o Storing redundant data to reduce complex joins and improve performance.
• Partitioning
o Splitting large tables for faster access.
• Materialized Views
o Pre-computed query results to optimize reporting and analytics.

Database Tuning

• Query Tuning: Rewrite slow SQL queries.

• Index Tuning: Add/remove indexes based on usage.
• Memory and I/O Tuning: Adjust buffer sizes, cache, and parallelism.
• Monitoring Tools: Oracle AWR, SQL Profiler, PostgreSQL EXPLAIN.

2. Distributed Database Systems

A Distributed Database (DDB) is a collection of databases spread across multiple sites,

but appearing as a single logical database.

Features
 • Transparency: Users don’t need to know the data location.
• Replication: Copying data to multiple sites for fault tolerance.
• Fragmentation: Dividing data into logical pieces (horizontal/vertical).

Advantages

• Improved availability and reliability.

• Faster local access.
• Load balancing across multiple servers.

Challenges

• Maintaining consistency.
• Higher complexity in concurrency control and recovery.
• Network latency and synchronization issues.

3. Emerging Research Trends in Databases

Some modern research areas include:

• Cloud Databases: Scalable and elastic storage (e.g., Amazon RDS, Azure SQL).
• Big Data Integration: Combining traditional DBMS with platforms like Hadoop and
Spark.
• AI-Driven Query Optimization: Using machine learning for cost estimation and
tuning.
• Graph Databases: Neo4j, Amazon Neptune for social networks or recommendation
systems.
• Blockchain Databases: Immutable and secure storage for sensitive transactions.
• Time-Series Databases: Optimized for IoT and event-based data (e.g., InfluxDB).

4. MongoDB (NoSQL Database)

Key Features

• Document-Oriented: Stores data as JSON-like documents.

• Schema-less: No fixed table structure.
• Horizontal Scalability: Sharding allows scaling across multiple servers.
• High Availability: Built-in replication (Replica Sets).

Example

// Insert document
[Link]({ name: "Ali", semester: 4, gpa: 3.5 });
// Query document
[Link]({ semester: 4 });
NoSQL Databases

NoSQL databases are designed for unstructured or semi-structured data and provide high
scalability and performance.

Types of NoSQL
Type Examples Use Case
Document MongoDB, CouchDB Flexible data storage
Key-Value Redis, DynamoDB Caching, real-time apps
Column-Family Cassandra, HBase Big data analytics
Graph Neo4j, ArangoDB Social networks, fraud detection
Advantages
• Handles large-scale, complex, or rapidly changing data.
• Easier horizontal scaling.
• Better for real-time web or IoT applications.