ADBMS Detail Notes

Mr. Shakeel Waris

Centralized Database System

• A centralized database is a database stored, located, and maintained in a single location
(like one central computer/server).
• All users or client computers connect to this central server for accessing and updating data.
• Example: A university keeps all student records in one central server in its main IT
department.
Advantages:
• Easy to manage, control, and secure because everything is in one place.
• Less data redundancy (no duplicate copies at multiple locations).
• Backup and recovery are simple.
Disadvantages:
• Single point of failure → if the central server crashes, the whole system becomes
unavailable.
• Performance issues when many users access the system at the same time.
• Scalability is limited.
Distributed Database System
• A distributed database is stored across multiple locations (sites/servers/computers)
connected via a network.
• Each site can manage its portion of the database independently but also work together as
one system.
• Example: A multinational company stores customer data in different country servers, but
all servers can still communicate.
Types:
• Homogeneous: All sites use the same DBMS.
• Heterogeneous: Different sites may use different DBMSs.
Benefits of Distributed Systems
1. Reliability & Availability:
o If one site fails, others can still function.
o No single point of failure like centralized systems.
2. Scalability:
o Easy to add more sites/servers as the organization grows.
3. Faster Access (Performance):
o Data stored closer to users reduces network delays.
4. Modularity:
o New locations or databases can be added without redesigning the entire system.
5. Fault Tolerance:
o Data replication ensures recovery in case of site failure.
 Challenges of Distributed Systems
1. Complexity in Management:
o Synchronizing and maintaining multiple sites is difficult.
2. Data Consistency:
o Ensuring all sites have the latest and correct version of data is challenging.
3. High Setup & Maintenance Cost:
o Requires advanced hardware, software, and skilled staff.
4. Security Issues:
o More vulnerable to cyber-attacks since data travels over networks.
5. Communication Overhead:
o Extra time and resources are needed for coordination between sites.
1. Transactional Processing (TP)
• Transactional Processing means performing database operations in the form of
transactions.
• A transaction is a logical unit of work that may consist of one or more database operations
(like INSERT, UPDATE, DELETE).
Example:
When you transfer money from one bank account to another, the transaction includes:
1. Deducting money from Account A.
2. Adding money to Account B.
Both steps must succeed together. If one fails, the whole transaction must be rolled back.
2. ACID Properties
ACID stands for Atomicity, Consistency, Isolation, and Durability. These are the key properties
that guarantee reliable transactions in databases.
A. Atomicity
• “All or nothing” rule.
• A transaction is either fully completed or not executed at all.
• If one part fails, the whole transaction is rolled back.
• Example: If money is deducted from Account A but not added to Account B, the whole
transaction is canceled.
B. Consistency
• The database must remain in a valid state before and after the transaction.
• Ensures that integrity rules (constraints, relationships) are not violated.
• Example: A bank transaction cannot create or destroy money → total balance in the system
must remain consistent.
C. Isolation
• Multiple transactions can run at the same time, but they must appear as if they were
executed one after the other (sequentially).
• Prevents problems like:
o Dirty Read: One transaction reading uncommitted changes of another.
 o Lost Update: Two transactions overwriting each other’s changes.
• Example: Two people booking the last seat in a flight at the same time → isolation ensures
only one succeeds.
D. Durability
• Once a transaction is committed, its results are permanently saved, even if there is a
system crash.
• Example: After confirming a money transfer, the record stays safe in the database, even if
the bank server crashes immediately afterward.
3. Why ACID Properties Are Important?
• Reliability: Ensures transactions are accurate and trustworthy.
• Data Integrity: Prevents corruption, inconsistency, and errors.
• Concurrency Control: Supports multiple users accessing the database simultaneously
without conflicts.
• Crash Recovery: Guarantees that committed changes survive failures.

Concurrency Control

• Concurrency control ensures that multiple transactions running at the same time do not
interfere with each other and the ACID properties are maintained.
• Without concurrency control, problems may occur:
o Dirty Read – one transaction reads uncommitted data of another.
o Lost Update – two transactions overwrite each other’s changes.
o Inconsistency – final results depend on transaction order.
Concurrency Control Techniques
There are many techniques, but the two most common are:
A. Lock-Based Protocols
• Transactions use locks to control access to data items.
• Types of locks:
o Shared Lock (S): Allows read only (many can read at the same time).
o Exclusive Lock (X): Allows read + write (only one transaction at a time).
• Two-Phase Locking (2PL):
o Growing phase: Transaction can acquire locks but not release.
o Shrinking phase: Transaction releases locks but cannot acquire new ones.
• Ensures serializability but may cause deadlock (two transactions waiting forever for each
other’s locks).
B. Timestamp-Based Protocols
• Each transaction is given a timestamp when it starts.
• Transactions are ordered based on their timestamps.
• Rules:
 o If a transaction tries to read/write a value that violates the timestamp order → it is
rolled back.
• Ensures serializability without using locks (so no deadlock).
• But may cause starvation (a long transaction keeps getting restarted because of conflicts
with newer ones).
Comparison: Lock-Based vs Timestamp-Based Protocols
Feature Lock-Based Protocols Timestamp-Based Protocols
Control Uses locks (Shared & Exclusive) Uses transaction timestamps
Mechanism
Concurrency Achieved via 2-Phase Locking (2PL) Achieved via ordering by
timestamps
Problems Deadlock & waiting Starvation & rollbacks
Overhead Lock management overhead Timestamp management
overhead
Performance Good for low to medium contention Good for high contention
Fairness Depends on lock scheduling Strict order (old transactions
first)
Example Use Banking systems (where consistency Real-time systems (where order
Case is critical) matters)

Relational Database Model (Traditional Model)

• In the relational model, data is stored in tables (relations) with rows (tuples) and columns
(attributes).
• Each table has a primary key to uniquely identify rows.
• Relationships are maintained using foreign keys.
• Supported by SQL (Structured Query Language).
• Example:
Student Table
----------------------------
StudentID | Name | Age
1 | Ali | 22
2 | Sara | 21
Simple and structured, but limited when handling complex data types (images, audio, video,
spatial data, etc.).
Object-Relational Database Model (ORDBMS)
• An Object-Relational Database (ORDBMS) extends the relational model by adding
object-oriented features.
• It allows databases to handle complex data types and user-defined types (UDTs).
• Supported by extended SQL, often called SQL:1999 / SQL:2003 (Object-SQL).
Key Features of ORDBMS:
1. User-Defined Data Types (UDTs):
o You can define your own data structures (e.g., address, image).
2. Objects with Methods:
o Data can have methods (functions) attached, just like in OOP.
3. Inheritance:
o A new type can inherit attributes and methods from another type.
4. Complex Data Storage:
o Supports multimedia, spatial data, XML, JSON, etc.
5. Extensibility:
o Users can define new data types and operations that integrate with SQL.
Example
Relational Model Example:
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
Object-Relational 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 Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT,
HomeAddress Address
);
Differences: Relational vs Object-Relational
Feature Relational DBMS (RDBMS) Object-Relational DBMS (ORDBMS)
Data Types Simple (int, char, varchar, date) Complex + User-Defined Types (UDTs)
Storage Tables (rows & columns) Tables + Objects
Methods/Functions Only predefined SQL functions Allows methods tied to objects
Inheritance Not supported Supported
Complex Data Not efficient Handles multimedia, spatial, JSON
Flexibility Less flexible, simple structure More flexible, extensible

Query Optimization in Advanced Databases

1. Introduction
• Query Optimization is the process of choosing the most efficient way to execute a
database query.
• A query can often be executed in multiple ways (different query plans).
• The Query Optimizer in DBMS decides the best execution plan based on cost factors
like CPU time, I/O, memory, and network usage.
Example:
SELECT *
FROM Students
WHERE Age > 20 AND Department = 'CS';
• The optimizer may:
o Scan the whole table (Full Table Scan)
o Or use an Index on Age or Department (faster).
2. Types of Query Optimization
A. Heuristic (Rule-Based) Optimization
• Uses predefined rules to transform queries into an efficient form.
• Examples of rules:
o Apply selections early (reduce dataset size).
o Replace Cartesian product + selection with join.
o Use projection to eliminate unnecessary attributes.
Example
Instead of:
SELECT Name
FROM Students, Departments
WHERE [Link] = [Link] AND Department = 'CS';
• Optimizer applies join first, filter later → more efficient.
B. Cost-Based Optimization
• The optimizer evaluates multiple query execution plans and chooses the one with the
lowest estimated cost.
• Cost is based on:
o Disk I/O
o CPU usage
o Memory usage
o Network delay (for distributed DBs)
• Relies on statistics (table size, index distribution, number of distinct values).
Example:
• If Department column has an index, the optimizer prefers index scan over full table scan.
C. Semantic Optimization
• Uses constraints, keys, and relationships to simplify queries.
• Removes redundant joins or conditions.
Example:
If DeptID in Students is a foreign key referencing Departments, then:
SELECT Name
FROM Students, Departments
WHERE [Link] = [Link];
• The join may be unnecessary if only student data is required.
3. Query Optimization Techniques
1. Indexing
• Creating indexes on frequently used attributes speeds up query execution.
• Types:
o B+ Tree Index (range queries)
o Hash Index (exact match queries)
o Bitmap Index (data warehouses).
2. Query Rewriting (Heuristics)
• Apply transformation rules to rewrite queries:
o Perform selection before join.
o Eliminate redundant predicates.
o Use equivalent relational algebra expressions.
3. Join Optimization
• Joins are costly, so optimizers:
o Choose best join order (left-deep, right-deep, bushy trees).
o Select best join algorithm:
▪ Nested-Loop Join (small tables).
▪ Sort-Merge Join (when both relations sorted).
▪ Hash Join (large tables, equality join).
4. Materialized Views
• Store results of complex queries in advance.
• Saves time for repeated queries in data warehouses.
5. Partitioning & Parallelization
• Partitioning large tables (horizontal/vertical) improves performance.
• Parallel query execution distributes work across multiple processors/servers.
6. Caching
• Frequently executed queries or sub-results are cached to reduce computation time.
7. Selectivity Estimation
• Optimizer uses statistics (histograms, cardinality, distinct counts) to estimate which query
plan will return fewer rows → cheaper execution.
4. Query Optimization in Advanced Databases
Advanced DBs (e.g., distributed, object-relational, NoSQL) introduce new challenges:
1. Distributed Query Optimization
o Optimize queries across multiple sites.
o Minimize network cost.
o Decide whether to bring data to one site or perform local joins.
2. Object-Relational Optimization
o Handle complex types (arrays, multimedia).
o Optimize method execution inside queries.
3. Big Data & NoSQL Optimization
o Query optimization in MapReduce, Spark SQL, MongoDB.
o Focuses on parallel execution and data locality.

Database Recovery:

• Database recovery is the process of restoring a database to a consistent and correct state
after a failure.
• Failures may cause loss of data, corruption, or incomplete transactions, so recovery
ensures ACID properties (especially Atomicity & Durability) are preserved.
Types of Failures in Databases
1. Transaction Failures – Transaction fails due to logical error (e.g., division by zero) or
system crash during execution.
2. System Failures – Hardware/software crashes cause loss of volatile memory (RAM), but
disk data remains safe.
3. Media Failures – Disk crashes or storage corruption lead to permanent data loss.
4. Application/Operator Errors – Mistaken deletion or update by users/programs.
5. Catastrophic Failures – Natural disasters or power failures affecting entire system.
Database Recovery Techniques
To handle failures, DBMS uses recovery techniques based on logs, backups, and checkpoints.
A. Backup-Based Recovery
• Backups are periodic full copies of the database.
• If database fails → restore from last backup, then apply logs for recent transactions.
• Two types:
o Full Backup – Entire database.
o Incremental Backup – Only changes since last backup.
B. Log-Based Recovery
• DBMS maintains a log file (Write-Ahead Log) recording all changes.
• Log contains:
o Transaction ID
o Data Item
o Old Value (before change)
o New Value (after change)
Two operations for recovery:
1. Undo (Rollback): Reverse uncommitted transactions using old values.
2. Redo (Rollforward): Reapply committed transactions using new values.
C. Shadow Paging
• Instead of updating the database directly, DBMS uses two copies (pages):
o Current Page Table (active)
o Shadow Page Table (backup, unchanged)
• If transaction succeeds → shadow is replaced with current.
• If failure occurs → shadow is used to restore database.
• Advantage: No log required.
• Limitation: High overhead for copying pages.
D. Checkpointing
• A checkpoint is a snapshot of the database and log at a certain point in time.
• During recovery:
o DBMS does not need to redo/undo transactions before the last checkpoint.
o Recovery time is reduced.
Example:
• Checkpoint at 9:00 AM.
• System crashes at 9:10 AM.
• Only transactions after 9:00 AM need to be checked in logs.
E. Recovery in Distributed Databases
• More complex due to multiple sites.
• Uses Two-Phase Commit (2PC):
1. Prepare Phase – Coordinator asks all sites if they are ready to commit.
2. Commit Phase – If all agree, commit; else rollback.
Database Administration (DBA)

• A Database Administrator (DBA) is the person/team responsible for managing,

controlling, and maintaining the database system.
• The DBA ensures the database is secure, available, consistent, and performs well.
• Acts as a bridge between end users, developers, and the database system.
Key Responsibilities of a DBA
A. Database Design & Implementation
• Work with developers to design schemas, tables, indexes, and relationships.
• Ensure proper normalization and data integrity.
B. Installation & Configuration
• Install DBMS software (e.g., Oracle, MySQL, SQL Server).
• Configure database settings for optimal performance.
C. Performance Monitoring & Tuning
• Monitor database queries and response times.
• Use query optimization, indexing, caching, and partitioning.
• Tune memory, CPU, and storage usage.
D. Backup & Recovery
• Create regular backups (full & incremental).
• Develop disaster recovery plans using logs, shadow paging, checkpoints.
E. Security Management
• Define user roles, privileges, and access control.
• Encrypt sensitive data.
• Monitor unauthorized access attempts.
F. Data Integrity & Consistency
• Enforce constraints (primary key, foreign key, unique, not null).
• Ensure transactions follow ACID properties.
G. Database Upgrades & Migration
• Upgrade DBMS software versions.
• Migrate data from old systems to new platforms.
H. User Support
• Assist developers in writing optimized SQL queries.
• Provide training to users for effective database usage.
Security Concerns in Database Administration
A. Unauthorized Access
• Hackers or insiders may try to access sensitive data.
• Solution: Authentication, role-based access, encryption.
B. SQL Injection Attacks
• Malicious SQL code injected into queries to steal or corrupt data.
• Solution: Prepared statements, input validation, firewalls.
C. Data Leakage
• Sensitive data (financial, personal, medical) may leak.
• Solution: Data masking, encryption, access auditing.
D. Insider Threats
• Employees with high privileges may misuse access.
• Solution: Least privilege principle, regular audits, activity monitoring.
E. Backup Security
• Stolen or unprotected backup files can leak data.
• Solution: Encrypt backups and secure storage.
F. Ransomware & Malware Attacks
• Attackers may encrypt or corrupt database files.
• Solution: Regular patches, intrusion detection, secure recovery plan.

NoSQL Databases

• NoSQL (Not Only SQL) databases are modern database systems designed to handle large-
scale, unstructured, and semi-structured data.
• Unlike traditional relational databases (RDBMS) that use tables and SQL, NoSQL
databases use flexible data models like key-value, document, column, and graph.
• Widely used in big data, real-time applications, cloud computing, and IoT.
Examples:
• Key-Value Stores: Redis, Amazon DynamoDB
• Document Stores: MongoDB, CouchDB
• Column Stores: Apache Cassandra, HBase
• Graph Databases: Neo4j

Characteristics of NoSQL Databases

1. Schema-Free / Flexible Schema – No fixed structure like RDBMS.
2. Horizontal Scalability – Easily add more servers (sharding/replication).
3. High Performance – Optimized for fast reads/writes.
4. Distributed by Design – Data spread across multiple nodes for reliability.
5. Handles Big Data – Can manage petabytes of structured, semi-structured, and
unstructured data.
Types of NoSQL Databases
1. Key-Value Stores – Simple key-value pairs. (e.g., Redis, DynamoDB)
2. Document Stores – JSON-like documents with flexible structure. (e.g., MongoDB)
3. Column-Oriented Stores – Data stored by columns instead of rows. (e.g., Cassandra,
HBase)
4. Graph Databases – Store entities and relationships as nodes and edges. (e.g., Neo4j)

Comparison: NoSQL vs Relational Databases

Relational Databases
Feature NoSQL Databases
(RDBMS)
Data Model Tables (rows & columns) Key-Value, Document, Column, Graph
Schema Fixed schema (predefined) Schema-less / flexible
Vertical (add more CPU/RAM
Scalability Horizontal (add more servers)
to one server)
Query
SQL Varies (MongoDB Query, GraphQL, APIs)
Language
Many use BASE (Basically Available, Soft
Transactions Strong ACID compliance
state, Eventually consistent)
Structured data, strong
Best For Big data, real-time apps, unstructured data
consistency
Oracle, MySQL, PostgreSQL,
Examples MongoDB, Cassandra, Redis, Neo4j
SQL Server
Performance Slower for very large datasets Optimized for speed & distributed processing
Use Cases Banking, ERP, CRM Social media, IoT, e-commerce, analytics

Normalization
• Normalization is the process of organizing data in a database to remove redundancy and
improve data integrity.
• It divides large, complex tables into smaller, related ones using keys and ensures
dependencies are logical.
Goals of Normalization:
1. Eliminate data redundancy (duplicate data).
2. Ensure data integrity and consistency.
3. Make database more efficient and flexible.
Normal Forms
1st Normal Form (1NF)
• Rule:
o Each column should contain atomic (indivisible) values.
o No repeating groups or arrays.
Example (Unnormalized Table):
Student
-------------------------------------------------
StudentID | Name | Subjects
1 | Ali | Math, Physics
2 | Sara | English, Biology
Here, Subjects contains multiple values → not atomic.
1NF Table:
Student
----------------------------
StudentID | Name | Subject
1 | Ali | Math
1 | Ali | Physics
2 | Sara | English
2 | Sara | Biology
Now, each cell has atomic values.
2nd Normal Form (2NF)
• Rule:
o Must be in 1NF.
o No partial dependency → Non-key attributes should depend on the whole
primary key, not part of it.
Example (Before 2NF):
Enrollment
----------------------------------------
StudentID | CourseID | StudentName | CourseName
1 | C1 | Ali | DBMS
1 | C2 | Ali | OS
2 | C1 | Sara | DBMS
Problem:
• StudentName depends only on StudentID.
• CourseName depends only on CourseID.
• Both are partial dependencies.
2NF Tables:
Student
-----------------------
StudentID | StudentName
1 | Ali
2 | Sara

Course
-----------------------
CourseID | CourseName
C1 | DBMS
C2 | OS

Enrollment
-----------------------
StudentID | CourseID
1 | C1
1 | C2
2 | C1
Now, no partial dependency.
3rd Normal Form (3NF)
• Rule:
o Must be in 2NF.
o No transitive dependency → Non-key attributes must not depend on other non-
key attributes.
Example (Before 3NF):
Student
-------------------------------------------
StudentID | StudentName | DeptID | DeptName
1 | Ali | D1 | Computer Science
2 | Sara | D2 | Mathematics
Problem:
• DeptName depends on DeptID, not directly on StudentID → transitive dependency.
3NF Tables:
Student
----------------------------
StudentID | StudentName | DeptID
1 | Ali | D1
2 | Sara | D2

Department
----------------------------
DeptID | DeptName
D1 | Computer Science
D2 | Mathematics
Now, no transitive dependency.
4 NF: Boyce-Codd Normal Form (BCNF)
• Rule:
o A stronger version of 3NF.
o For every functional dependency X → Y, X should be a candidate key.

Key in DBMS:
• A key is an attribute (or set of attributes) that is used to uniquely identify a record (row)
in a table.
• Keys also establish relationships between tables and enforce integrity constraints.
Example Table: Student
StudentID | Name | Email | DeptID
1 | Ali | ali@[Link] | D1
2 | Sara | sara@[Link] | D2
3 | Ahmed | ahmed@[Link] | D1

Types of Keys
A. Super Key
• A set of one or more attributes that can uniquely identify a row.
• Can have extra unnecessary attributes.
• Example:
o {StudentID}, {Email}, {StudentID, Name}, {StudentID, Email} are all Super
Keys.
B. Candidate Key
• A minimal super key (no extra attributes).
• Always unique and minimal.
• Example:
o {StudentID} and {Email} are Candidate Keys.
o But {StudentID, Name} is not minimal → so not a candidate key.
C. Primary Key
• One candidate key is chosen as the main key to identify records.
• Cannot contain NULL values.
• Example:
o If we choose StudentID as Primary Key, every student is uniquely identified.
D. Alternate Key
• Candidate keys not chosen as primary key.
• Example:
o If StudentID is primary key, then Email is an Alternate Key.
E. Foreign Key
• An attribute in one table that refers to the Primary Key of another table.
• Ensures referential integrity.
• Example:
Department Table
DeptID | DeptName
D1 | Computer Science
D2 | Mathematics
Student Table
StudentID | Name | DeptID
1 | Ali | D1
2 Sara | D2
Here, DeptID in Student is a Foreign Key referencing DeptID in Department.
F. Composite Key (Compound Key)
• A key that consists of two or more attributes to uniquely identify a row.
• Example:
Enrollment Table
StudentID | CourseID | Grade
1 | C1 |A
1 | C2 |B
2 | C1 |A
• Here, {StudentID, CourseID} together form a Composite Key (since neither alone is
unique).
1. Query in DBMS (Advanced Definition)
• A query is not just a command to fetch data — it is a formal request expressed in a query
language (mostly SQL, but also XQuery, SPARQL, GraphQL, etc.) to manipulate or
retrieve data.
• Modern DBMS optimizes queries internally using query optimization techniques (e.g.,
cost-based optimization, execution plan, indexing).

2. Types of Queries (Advanced View)

A. Data Definition Language (DDL) Queries
• Define, alter, and control database structures.
• Advanced features include constraints, triggers, indexes, and partitions.
-- Create table with constraints
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Salary DECIMAL(10,2) CHECK (Salary > 20000),
DeptID INT,
FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

-- Partition table for performance

CREATE TABLE Sales (
SaleID INT,
SaleDate DATE,
Amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(SaleDate)) (
PARTITION p2019 VALUES LESS THAN (2020),
PARTITION p2020 VALUES LESS THAN (2021)
);
Use case: DDL queries are critical for schema design, performance tuning, and large-scale
partitioning in advanced systems.
B. Data Manipulation Language (DML) Queries
• Insert, update, delete, and merge data.
• Advanced databases use bulk operations, MERGE statements, CTEs (Common Table
Expressions).
-- Merge (Upsert) to avoid duplicate inserts
MERGE INTO Employee e
USING NewData n
ON ([Link] = [Link])
WHEN MATCHED THEN
UPDATE SET [Link] = [Link]
WHEN NOT MATCHED THEN
INSERT (EmpID, Name, Salary, DeptID)
VALUES ([Link], [Link], [Link], [Link]);

-- Recursive CTE to fetch hierarchy (managers & subordinates)

WITH RECURSIVE EmpHierarchy AS (
SELECT EmpID, Name, ManagerID
FROM Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT [Link], [Link], [Link]
FROM Employee e
INNER JOIN EmpHierarchy h ON [Link] = [Link]
)
SELECT * FROM EmpHierarchy;
Use case: Complex business logic, data migration, hierarchical queries, and real-time updates.

C. Data Control Language (DCL) Queries

• Manage security, access control, and privileges.
• Advanced DBMS supports role-based access control (RBAC) and fine-grained security.
-- Create role and grant permissions
CREATE ROLE hr_manager;
GRANT SELECT, INSERT, UPDATE ON Employee TO hr_manager;

-- Assign role to user

GRANT hr_manager TO user1;

-- Fine-grained access (Row-Level Security in PostgreSQL)

CREATE POLICY salary_policy
ON Employee
FOR SELECT
USING (DeptID = current_setting('my.dept_id')::INT);
Use case: Banking, healthcare, and government systems where security & compliance (GDPR,
HIPAA) are critical.

D. Transaction Control Language (TCL) Queries

• Handle transactions with ACID properties.
• Advanced systems use SAVEPOINTS, distributed transactions, and two-phase commit
(2PC).
BEGIN;

UPDATE Account SET Balance = Balance - 500 WHERE AccID = 101;

UPDATE Account SET Balance = Balance + 500 WHERE AccID = 202;

SAVEPOINT transfer_check;

-- If something goes wrong

ROLLBACK TO transfer_check;

-- If all good
COMMIT;
Use case: Financial transactions, multi-step operations where atomicity and consistency are
critical.

E. Data Query Language (DQL)

• Mainly SELECT, but advanced queries involve:
o Window functions
o Joins and subqueries
o Analytical queries
o Optimization with indexes
-- Window function: Rank employees by salary
SELECT Name, Salary,
RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM Employee;

-- Subquery with aggregation

SELECT DeptID, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY DeptID
HAVING AVG(Salary) > (SELECT AVG(Salary) FROM Employee);
Use case: Data analytics, BI (Business Intelligence), machine learning preprocessing.

3. Advanced Query Types by Purpose

1. Join Queries – Combine multiple tables.
2. Nested Queries – Query inside another query.
3. Aggregate Queries – SUM, AVG, COUNT with GROUP BY.
4. Analytical Queries – Window functions, ranking, lead/lag.
5. Recursive Queries – Hierarchical data (e.g., employee-manager tree).
6. Distributed Queries – Run across multiple databases/servers.
7. Parameterized Queries – Prevent SQL injection, used in apps.
8. Materialized Queries (Views) – Store results for faster access.

4. Queries in Advanced Databases Beyond SQL

• NoSQL Queries: MongoDB uses JSON-like queries.
[Link]({ dept: "CS" }, { name: 1, _id: 0 })
• Graph Queries: Neo4j (Cypher language).
MATCH (p:Person)-[:FRIENDS_WITH]->(f)
WHERE [Link] = "Ali"
RETURN [Link];
• Big Data Queries: Apache Hive (SQL-like on Hadoop).
SELECT department, COUNT(*) FROM employees GROUP BY department;

Database Modeling
• Database modeling is the process of creating a structured representation of data and
its relationships before implementing it in a DBMS.
• It ensures that the database is:
o Consistent (no redundancy or anomalies)
o Efficient (optimized queries and storage)
o Scalable (able to grow with data and users)
o Aligned with real-world needs
Main Levels of Data Models
A. Conceptual Data Model
• High-level, abstract design of the data.
• Focuses on entities, attributes, and relationships.
• Tool: ERD (Entity-Relationship Diagram).
Example: A University Database
• Entities: Student, Course, Instructor
• Relationships: Student-Enrolls-Course, Instructor-Teaches-Course
STUDENT (StudentID, Name, Email)
COURSE (CourseID, Title, Credits)
INSTRUCTOR (InstructorID, Name, Dept)
RELATIONSHIPS:
- Student → Enrolls → Course
- Instructor → Teaches → Course

B. Logical Data Model

• Converts the conceptual model into a form supported by a specific DBMS (e.g., relational,
NoSQL, object-oriented).
• Defines tables, columns, primary keys, foreign keys.
Example: From above, in Relational Model (RDBMS)
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Email VARCHAR(100) UNIQUE
);

CREATE TABLE Course (

CourseID INT PRIMARY KEY,
Title VARCHAR(100),
Credits INT
);

CREATE TABLE Enrollment (

StudentID INT,
CourseID INT,
Grade CHAR(2),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

C. Physical Data Model

• Actual implementation details: storage, indexing, partitioning, performance tuning.
• Includes data types, storage engines, indexing strategy, sharding in distributed DBs.
Example: PostgreSQL Physical Model
• StudentID → SERIAL (auto-increment)
• Indexing → CREATE INDEX idx_student_email ON Student(Email);
• Partitioning → Large Course table partitioned by year
Types of Database Models
1. Hierarchical Model
• Organizes data into a tree-like structure (parent-child relationships).
• Used in early DBMS (e.g., IBM IMS).
Example:
Company
├── Department
│ ├── Employee
│ └── Project
• Efficient for one-to-many relationships, but rigid.

2. Network Model
• Data represented as records connected by links (pointers).
• More flexible than hierarchical.
• Used in CODASYL DBMS.
Example:
• Employee works on multiple projects.
• Projects may have multiple employees.

3. Relational Model (RDBMS)

• Data stored in tables (relations) with rows (tuples) and columns (attributes).
• Uses SQL for queries.
• Ensures ACID properties.
Example:
• Student (StudentID, Name)
• Course (CourseID, Title)
• Enrollment (StudentID, CourseID, Grade)
Most popular (MySQL, PostgreSQL, Oracle).

4. Object-Oriented Model
• Integrates object-oriented programming concepts with databases.
• Stores data as objects (attributes + methods).
Example:
• Class: Student { Name, Email, enroll() }
• Class: Course { Title, Credits, addStudent() }
5. Entity-Relationship Model
Graphical approach with entities, attributes, and relationships.
• Mainly used for conceptual database design.
• Foundation for ERD diagrams.
6. Document Model (NoSQL)
• Data stored in JSON-like documents.
• Flexible schema.
• Best for semi-structured/unstructured data.
Example in MongoDB:
{
"StudentID": 101,
"Name": "Ali",
"Courses": [
{"CourseID": "CS101", "Grade": "A"},
{"CourseID": "DB202", "Grade": "B"}
]
}

7. Key-Value Model (NoSQL)

• Simplest: key = value.
• High performance, used in caching (Redis, DynamoDB).
Example:
"User:101" → {"Name": "Ali", "Email": "ali@[Link]"}

8. Column-Family Model (NoSQL)

• Stores data in columns instead of rows.
• Optimized for big data queries.
• Used in Cassandra, HBase.
Example:
RowKey: 101
Name: Ali | Email: ali@[Link] | Dept: CS

9. Graph Model
• Focuses on nodes (entities) and edges (relationships).
• Powerful for social networks, recommendation engines.
• Query Language: Cypher (Neo4j).
Example:
(Ali) -[:FRIENDS_WITH]-> (Sara)