Lecture 5 & 6 — DBA: Types of DBMS & Distributed

Databases

1. Types of DBMS

There are 4 (+ 1 extra) main types of DBMS:

1. Hierarchical DBMS
• Relationships among data are established so that one data element is
subordinate to another
• Data elements have parent-child relationships
• Modelled using tree data structure
• Very fast and simple
2. Network DBMS
• Relationships among data are of type many-to-many (in the form of a
network)
• Structure is generally complicated due to many-to-many relationships
• Modelled using graph data structure
3. Relational DBMS (RDBMS)
• Database is represented in the form of relations (tables)
• Each relation models an entity
• A row = tuple (single record)
• A column = field / attribute (characteristic property of entity)
• Most popular type of DBMS
• Example: Student Relation table
4. Object-Oriented DBMS (OODBMS)
• Derived from object-oriented programming paradigm
• Helpful in representing both stored data and data in executing programs
• Uses small, reusable elements called objects
• Each object contains:
o A data part
o A set of operations that work on the data
• Objects and attributes are accessed through pointers (not relational tables)
• Example: Bank Account object-oriented database
5. Distributed DBMS (DDBMS)
• A set of interconnected databases distributed over a computer network or
internet
• DDBMS manages the distributed database and makes it transparent to users
• Data is intentionally distributed among multiple nodes for optimal use of
computing resources

2. Distributed Database System (DDBS)

• A collection of multiple databases spread across different physical locations,

connected via a network
• Data is stored across multiple sites but appears as a single database to users
• Enables local access to data — improves response time and performance
• Supports parallel processing — multiple operations at the same time
3. Types of Distributed Database Systems
1. Homogeneous Database
• All different sites store data identically
• Same operating system, DBMS, and data structures at all sites
• Features:
o Unified query language and interface
o Low integration complexity
o Efficient synchronization
• Example: A bank with branches in different cities uses Oracle DB at every
location — same structure, synchronized regularly
2. Heterogeneous Database
• Different sites may use different DBMSs, schemas, or data models
• Query processing and transactions are difficult
• Some sites may not be aware of others — translation mechanisms needed
• Features:
o Supports interoperability between diverse systems
o Complex query optimization and transaction management
o Useful in mergers or collaborations between organizations
• Example: A logistics company uses MySQL for inventory, MongoDB for
vehicle tracking, PostgreSQL for billing — middleware allows unified querying
3. Client-Server Distributed Database
• Server stores and manages the database
• Clients send queries over the network
• Centralized control with distributed access
• Features:
o Simplifies resource management
o Central server optimized for performance
o Easily scalable with more clients
• Example: E-commerce website where frontend (client) interacts with a central
PostgreSQL server to manage orders, users, and inventory
4. Peer-to-Peer Distributed Database
• All nodes are equal — no fixed client or server roles
• Each node can store data and process queries
• Decentralized control
• Features:
o No single point of failure
o Useful in decentralized and distributed apps
o High availability and data redundancy
• Example: Blockchain networks like Ethereum — each node maintains part of
the distributed ledger
5. Cloud-Based Distributed Database
• Data stored and managed across multiple cloud servers in different
geographic locations
• Uses cloud computing infrastructure
• Users can access the database from anywhere via the internet
• Features:
o Automatic scaling and replication
o Pay-as-you-use pricing
o Global availability and disaster recovery
• Examples:
o Google Cloud Spanner — globally distributed relational database
o Amazon DynamoDB — NoSQL, high performance, key-value and document
DB
o Microsoft Azure Cosmos DB — multi-model, globally distributed DBMS
 o Firebase Realtime Database — real-time data synchronization between
clients and servers

4. Homogeneous Distributed Databases — Sub-Types

Autonomous
• Each database is independent and functions on its own
• Integrated by a controlling application using message passing for data
updates
• Each site can function independently without central control
• Examples:
o Banking: State Bank of Pakistan and HBL operate their own databases
independently
o Airline Reservation: PIA and Emirates maintain separate systems
o Universities: NUST and University of Punjab operate independently
o E-Commerce: Amazon and Alibaba maintain independent databases
Non-Autonomous
• Multiple databases integrated under a single central control
• Individual databases do not operate independently
• All sites follow the same global schema and DBMS rules
• Central system manages data distribution and queries
• Examples:
o Banking: State Bank of Pakistan regulating centralized branch databases
o University: Multiple campuses using same DBMS controlled by central IT
department
o Airline: PIA reservation system controlled centrally across booking offices
o Hospital: PIMS — multiple departments (radiology, pharmacy, emergency)
under central administration

5. Heterogeneous Distributed Databases — Sub-Types

Federated
• Heterogeneous database systems are independent but integrated to function
as a single database system
• Each database remains autonomous while participating in a federation for
data sharing
• May use different DBMSs, data models, schemas, and platforms
• Examples:
o Healthcare: PIMS and Shifa International Hospital maintain independent
systems but exchange patient data when needed
o E-Government: NADRA and FBR maintain separate databases but share
data through federation
o University Research: Multiple universities share research data across
different database systems
Un-Federated
• Multiple autonomous databases exist independently
• No formal integration or federation layer
• No unified global schema or coordinated query processing
• Each system operates independently with no data sharing
• Examples:
o Hospitals: PIMS and Shifa maintain completely separate systems with no
integration
 o Universities: NUST and University of Punjab operate independent databases
o Companies: Amazon and Alibaba manage independent systems
o Government: NADRA and FBR maintain own records without formal
integration

6. DDBMS Architectures

DDBMS architectures are developed based on 3 parameters:

• Distribution — physical distribution of data across sites
• Autonomy — how independently each constituent DBMS can operate
• Heterogeneity — uniformity or dissimilarity of data models and components
Client-Server Architecture
• Two-level architecture dividing functionality into servers and clients
• Server functions: data management, query processing, optimization,
transaction management
• Client functions: user interface, some consistency checking and transaction
management
• Two types:
o Single Server Multiple Client
o Multiple Server Multiple Client
Peer-to-Peer Architecture
• Each peer acts both as a client and server
• Peers share resources and coordinate activities
• Has 4 levels of schemas:
o Global Conceptual Schema — global logical view of data
o Local Conceptual Schema — logical data organization at each site
o Local Internal Schema — physical data organization at each site
o External Schema — user view of data
Multi-DBMS Architecture
• Integrated database system formed by two or more autonomous database
systems
• Has 6 levels of schemas:
o Multi-database View Level — multiple user views of subsets of integrated
database
o Multi-database Conceptual Level — global logical multi-database structure
definitions
o Multi-database Internal Level — data distribution and multi-database to local
data mapping
o Local database View Level — public view of local data
o Local database Conceptual Level — local data organization at each site
o Local database Internal Level — physical data organization at each site
• Two design alternatives:
o Model with multi-database conceptual level
o Model without multi-database conceptual level

7. Design Alternatives (Distribution Design)

Non-replicated & Non-fragmented
• Different tables placed at different sites
• Data placed close to the site where it is used most
• Suitable when queries joining tables at different sites are rare
• Helps reduce communication cost during data processing
Fully Replicated
• Each site has one full copy of all database tables
• Queries are very fast — negligible communication cost
• Update operations are costly due to massive data redundancy
• Suitable for systems with many queries but few updates
Partially Replicated
• Copies of tables or portions of tables stored at different sites
• Distribution done based on frequency of access
• Number of copies depends on how frequently access queries execute
Fragmented
• Table is divided into two or more pieces called fragments or partitions
• Each fragment stored at different sites
• Increases parallelism and provides better disaster recovery
• Only one copy of each fragment — no redundant data
• 3 fragmentation techniques:
o Vertical fragmentation — splitting by columns
o Horizontal fragmentation — splitting by rows
o Hybrid fragmentation — combination of both
Mixed Distribution
• Combination of fragmentation and partial replication
• Tables are first fragmented (horizontal or vertical)
• Then fragments are partially replicated across different sites based on access
frequency

8. Key Components and Challenges of Distributed Database

1. Replication
• Copies of the same data stored at two or more sites
• If every site has the full database = full replication
• Benefits:
o Improves data availability
o Allows faster, parallel query processing
• Drawbacks:
o Updates must be made at all sites — or data becomes inconsistent
o Adds overhead and makes concurrency control complex
2. Fragmentation
• Relations are divided into smaller parts (fragments) stored at different sites
• Fragments must be able to reconstruct the original relation (no data loss)
• Advantage: no data copies, so consistency is not a problem
• Types:
o Horizontal fragmentation — splitting by rows; each tuple assigned to at least
one fragment
o Vertical fragmentation — splitting by columns; each fragment must contain a
common candidate key for lossless join
3. Concurrency Control
• Ensures data remains accurate when multiple transactions run at the same
time
• Without it: lost updates, dirty reads, and inconsistencies can occur
• Goal: make parallel transactions behave as if run one by one
• Common methods: locking, timestamps, optimistic concurrency
4. Semantic Heterogeneity
• Happens when different databases use the same data labels but with different
meanings, formats, or units
• Example: one system stores salary in dollars, another in rupees
 • Causes confusion during data integration
• Must be resolved for accurate results

9. Functions of Distributed Database

• Used in Corporate Management Information Systems

• Used in multimedia applications
• Used in Military control systems, Hotel chains, etc.
• Used in manufacturing control systems

10. Advantages of Distributed Database

• Fast data processing — several sites participate in request processing

• High reliability and availability
• Reduced operating cost
• Easier to expand the system by adding more sites
• Improved sharing ability and local autonomy

11. Disadvantages of Distributed Database

• Complex to manage and control

• Security issues must be carefully managed
• Requires deadlock handling during transaction processing
• Without deadlock handling — entire system may become inconsistent
• Need for standardization for processing of distributed database systems

End of Lecture 5 & 6 Notes — DBA (Types of DBMS & Distributed Databases)