ADBMS

Topic: Distributed Database Management System

Instructor: Ms. Sadia Zulfiqar
Review Centralized Database Management
Till now, we have been studying centralized databases, where all the data is stored on a single server. One system, one location, and
all users depend on that one server.

Example:

● One main database server

● Other servers are:
○ backup servers
○ mirror servers
○ failover servers
 Situation Type

One server, one DB Centralized

Multiple servers but one main control Still Centralized

Multiple servers, each with part of data, Distributed

autonomous
Review Centralized Database Management
Data may exist on multiple machines. But system is still controlled centrally

Now think about this:

● What if that server fails?

● What if users are sitting in different cities or countries?
● What if millions of users access the system at the same time?

Can one server handle all of this?”

So to solve these problems, we move from
one server → to multiple servers.
Distributed Database
Database:- Logical interrelated collection of shared data, along with description of
data, physically distributed over a computer network.
Example:
Example: Example: (meta data)
● Students table
● Courses table ● Bank account data accessed by: ● Table name
● Enrollment table ○ ATM ● Column names
○ Mobile app ● Data types
These are linked using: ○ Bank branch ● Relationships

● student_id So → data is shared across users So system knows:

● course_id
● What data exists
So → data is logically connected
● Where it is stored
● How it is structured
What is Distributed Database?
● A distributed database (DDB) is a collection of multiple, logically interrelated
databases distributed over a computer network.
● A distributed database management system (DDBMS) is the software that
manages the DDB and provides an access mechanism that makes this
distribution transparent to the users
This is a Distributed Database System DB (Cylinder icons)
● Multiple sites (Site 1, Site 2, Site 3, Site 4) ● These are local databases
● Each site = a server (computer) ● Each site may have:
● Some sites have their own database (DB) ○ full data (replication)
● All sites are connected through a computer ○ partial data (fragmentation)
network

Computer Network (center)

● Connects all sites

Component-wise Explanation
● Allows:
○ communication
(a) Sites (Site 1, 2, 3, 4)
○ data transfer
● Each site is a node/server ○ query sharing
● Can:
○ store data
○ process queries

Some nodes may act as:

● processing nodes
● coordinators
● clients
How system works (step-by-step)
1. User sends query (any site)
2. System checks:
○ where data exists
3. Data may come from:
○ one site OR multiple sites
4. Results are combined
5. Final answer sent to user
A DDBMS mainly classified into two types:
● Homogeneous Distributed database management systems

● Heterogeneous Distributed database management systems

Homogeneous Distributed database management systems
All sites use:

● Same DBMS software

● Same data model
● Same schema (structure)

Example

● All sites using MySQL

● Same tables:
○ student(id, name)
○ course(id, title)

Characteristics

● Easy to design
● Easy communication
● High compatibility
● Less complexity
Heterogeneous Distributed database management systems
Definition

Different sites use:

● Different DBMS
● Different data models
● Different schemas

Example

● Site 1 → MySQL
● Site 2 → Oracle
● Site 3 → MongoDB

Characteristics

● Complex system
● Requires data conversion
● Integration is difficult
● Communication issues possible
Characteristics
● All sites are interconnected.
○ All database sites (servers) are connected through a network.
○ Example
○ Lahore, Karachi, Islamabad servers connected via internet/VPN
○ Why it matters
■ Sites can communicate
■ Queries can access remote data
■ Without network → no distributed system
Characteristics
● Fragments can be replicated.
● Data can be:
○ Fragmented (split)
○ and those fragments can also be copied (replicated) at other sites
○ Example
■ Customer data split by region
■ Important customers copied to multiple sites
○ Why it matters
■ Faster access (local copy)
■ Backup in case of failure
● Improves performance + reliability
Characteristics
● Logically related shared data can be collected.
○ Even if data is in different places, it is still:
■ logically connected
■ can be used together
○ Example
■ Orders (Site 1)
■ Customers (Site 2)
■ Query:
■ SELECT * FROM Orders JOIN Customers;
■ System combines them
● Why it matters
○ User gets complete result, not partial
Characteristics
● Data at each and every site is controlled by the DBMS.
○ Each site has its own local DBMS to manage its data

What it controls

● Storage
● Security
● Access
● Transactions

Example

● Each branch server runs its own DBMS

Why it matters

● Local control = faster operations

● Independence at each site
Characteristics
● Each Distributed Database Management System takes part in at least one global
application or all sites work together for a common system/application

Example

● Banking system E-commerce website:

● Airline reservation
● E-commerce system ● Server 1 → products
● Server 2 → orders
All sites contribute to one system ● Server 3 → users
Why it matters

● System behaves as one unified database

● Not isolated databases
Functionality
● Security
● Keeping track of data
● Replicated data management
● System catalog management
● Distributed transaction management
● Distributed database recovery
Homogeneous DDBMS
● In a homogeneous distributed database all sites have identical software and
are aware of each other and agree to cooperate in processing user requests.
● The homogeneous system is much easier to design and manage
● The operating system used, at each location must be same or compatible.
● The database application (or DBMS) used at each location must be same or
compatible.
Heterogeneous DDBMS
● In a heterogeneous distributed database different sites may use different
schema and software.
● In heterogeneous systems, different nodes may have different hardware &
software and data structures at various nodes or locations are also
incompatible.
● Different computers and operating systems, database applications or data
models may be used at each of the locations.
● On heterogeneous system, translations are required to allow communication
between different sites (or DBMS).
Heterogeneous DDBMS
● The heterogeneous system is often not technically or economically feasible.
In this system, a user at one location may be able to read but not update the
data at another location.(security restriction)
Advantages
● Less danger of a single-point failure.
● When one of the computers fails, the workload is picked up by other
workstations.
● Data are also distributed at multiple sites.
● The end user is able to access any available copy of the data, and an end
user's request is processed by any processor at the data location.
Advantages
● Improved communications. Because local sites are smaller and located closer
to customers.
● Reduced operating costs. It is more costeffective to add workstations to a
network than to update a mainframe system.
● Faster data access, faster data processing.
● A distributed database system spreads out the systems workload by
processing data at several sites.
Disadvantages
● Complexity of management and control.
● Applications must recognize data location, and they must be able to stitch
together data from various sites.
● Security.
● Increased storage and infrastructure requirements.
● Multiple copies of data has to be at different sites, thus an additional disk
storage space will be required.
● The probability of security lapses increases when data are located at multiple
sites.
What is Parallel database...??
● A parallel database system is to improve performance through parallelization
of various operations, such as loading data, building indexes and evaluating
queries.
● The distribution is solely done on the bases of performance.
● Parallel databases improve processing and input/output speeds by using
multiple CPUs and disks in parallel.
● Many operations are performed simultaneously Data may be stored in a
distributed fashion.
Difference b/w Distributed Database and Parallel Database

Characteristics Parallel Database Distributed database

Definition It is a software system where It is a software system that

multiple processors or machines manages multiple logically
are used to execute and run interrelated databases
queries in parallel. distributed over a computer
network

Geographical The nodes are located at The nodes are usually

Location geographically same location. located at geographically
different locations.

Execution Speed Quicker Slower

Overhead Less More

Difference b/w Distributed Database and Parallel Database

Characteristics Parallel Database Distributed database

Node types Compulsorily Homogeneous Need not be homogeneous

Performance Lower reliability & availability Higher reliability &

availability.

Scope of Expansion Difficult to expand Easier to expand

Backup Backup at one site only Backup at multiple sites

Consistency Maintaining consistency is easier Maintaining consistency is

difficult.
Data fragmentation
● Fragmentation is a process of division or the mapping of the tables based on
the columns and rows of data into the smallest unit of data.
● Data that has broken down is still possible to be combined again with the
intention to complete the data collection using fragmentation.
● Fragmentation is a database server feature that allows you to control where
data is stored at the table level.
● Fragmentation enables you to define groups of rows or index keys within a
table.
Replication
● Replication is that we store several copies of a relation or relation fragment.
● An entire relation can be replicated at one or more sites.
● Similarly, one or more fragments of a relation can be replicated at other sites.
● For example, if a relation R is fragmented into R1,R2, and R3, there might be
just one copy of R1, whereas R2 is replicated at two other sites and R3 is
replicated at all sites.
Two Fold Replication
The motivation for replication is two fold:

1. Increased Availability of Data: If a site that contains a replica goes down, we

can find the same data at other sites. Similarly, if local copies of remote
relations are available, we are less vulnerable to failure of communication
links.
2. Faster Query Evaluation: Queries can execute faster by using a local copy of
a relation instead of going to a remote site.
Distributed Transaction
● In a distributed DBMS, a given transaction is submitted at some one site, but
it can access data at other sites as well.
● When a transaction is submitted at some site, the transaction manager at that
site breaks it up into a collection of one or more sub-transactions that execute
at different sites, submits them to transaction managers at the other sites, and
coordinates their activity.
Distributed Transaction
● Distributed Concurrency Control: How can locks for objects stored across
several sites be managed?
● Distributed Recovery: Transaction atomicity must be ensured when a
transaction commits, all its actions, across all the sites at which it executes,
must persist. Similarly, when a transaction aborts, none of its actions must be
allowed to persist.
Distributed Concurrency Control
The choice of technique determines which objects are to be locked. When locks
are obtained and released is determined by the concurrency control protocol. We
now consider how lock and unlock requests are implemented in a distributed
environment. Lock management can be distributed across sites in many ways:
Lock management-Centralized
A single site is in charge of handling lock and unlock requests for all objects.

How it works
● Whenever a user or data request comes it must first get permission from the central lock
manager

Example
● Users from Lahore, Karachi, and Islamabad, all send their lock requests to one main server
Pros
● Simple
Easy to manage
Cons
● Single point of failure
● Slow (every request goes to the central server)
Lock management-Primary Copy
● One copy of each object is designated the primary copy.
● All requests to lock or unlock a copy of this object are handled by the lock
manager at the site where the primary copy is stored, regardless of where the
copy itself is stored.
How it works
● Even if copies of the data exist at multiple locations ,the lock request is
always sent to the primary site.
Object refers to Row (record), table or file)
Lock management-Primary Copy
Example
● Account data is stored in 3 cities but the primary copy is in Lahore
● Lock request: Even if it comes from Karachi → Lahore will decide
Pros
● Better than centralized
● Reduced bottleneck
Cons
● Still depends on one site per object
Lock management-Fully Distributed
● Requests to lock or unlock a copy of an object stored at a site are handled by
the lock manager at the site where the copy is stored.

How it works

● For any data stored at a site

the lock manager at that same site handles the lock
Lock management-Fully Distributed
Example
● Lahore data → controlled by Lahore
● Karachi data → controlled by Karachi
Pros
● No single point of failure
● Fast (local control)
Cons
● Complex
● Coordination issues may occur
DISTRIBUTED RECOVERY
● Recovery in a distributed DBMS is more complicated than in a centralized
DBMS for the following reasons:
○ New kinds of failure can arise : Failure of communication links and failure of a remote site at
which a sub-transaction is executing.
○ Either all sub-transactions of a given transaction must commit or none must commit, and this
property must be guaranteed despite any combination of site and link failures. This guarantee
is achieved using a commit protocol.