Database Design and

Development

Topic 10:
Distributed Databases

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.2

Scope and Coverage

This topic will cover:
• The need for distributed databases
• Components of distributed databases
• Advantages and disadvantages of distributed
databases
• Homogenous and Heterogeneous distribution
• Distributed Database Design

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.3

Learning Outcomes
By the end of this topic students will be able to:
• Recognise the need for distributed data
• Define the main features of a distributed database
• Define the different types of distributed databases

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.4

Site 1

Network

Site 4 Site 2

Site 3 Note: no database

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.5

Distributed Database Definition

• A database system that is split over more than one
site

• This might involve fragmentation

• This might involve replication

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.6

Why are Distributed Databases

Needed?
• Increase in size of firms generally

• Growth of multi-nationals and globalisation

• Benefits of making data available across an

organisation

• Diversification

• Performance issues

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.7

Fragmentation and Transparency

London
Customers Table Customers Table

Birmingham
Customers Table

Manchester
Customers Table

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.8

Site One
Replication London
Customers
Table

Customers Table Birmingham Site Two

Customers London
Table Customers
Table
Manchester
Customers
Table Birmingham
Customers
Table

Manchester
Customers
Table

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.9

Transparency
• Location transparency

• Fragmentation transparency

• Replication transparency

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.10

Foundation Rule
• A distributed database system should look exactly
like a centralised database system.

• Local Autonomy
• No reliance on a central site

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.11

Vertical Fragmentation

Create fragment with a

PROJECT with primary keys

Reconstruct original table

with a JOIN

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.12

CustomerID Name Area PaymentType Sex

6 Smith London Cash M
5 Patel London Card F
8 Singh Manchester Card F
9 Kodogo Birmingham Card F
2 Rice Manchester Cash M

CustomerID Name Sex

Vertical
6 Smith M
Fragmentation
5 Patel F
8 Singh F
CustomerID, Name,
9 Kodogo F
Sex
2 Rice M

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.13

Horizontal Fragmentation

Create fragment with

RESTRICT

Reconstruct original table

with UNION

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.14

CustomerID Name Area PaymentType Sex

6 Smith London Cash M
5 Patel London Card F
8 Singh Manchester Card F
9 Kodogo Birmingham Card F
2 Rice Manchester Cash M

CustomerID Name Area PaymentType Sex

8 Singh Manchester Card F
2 Rice Manchester Cash M

Horizontal Fragmentation
Customers in Manchester

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.15

Vertical and Horizontal Fragment

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.16

CustomerID Name Area PaymentType Sex

6 Smith London Cash M
5 Patel London Card F
8 Singh Manchester Card F
9 Kodogo Birmingham Card F
2 Rice Manchester Cash M

CustomerID Name PaymentType

8 Singh Card
2 Rice Cash

Vertical and Horizontal Fragmentation

Customers in Manchester and their
Payment Type

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.17

Advantages of Distributed Data

• Emulating organisational structure
• Greater Control
• Improved availability
• Greater reliability
• Better performance
• Easier growth

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.18

Disadvantages of Distributed
Databases
• Complexity
• Cost
• Security
• Integrity control more difficult
• Lack of standards
• Lack of experience
• Database design more complex

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.19

Types of Distributed Database

• Homogeneous

• Heterogeneous

• Federated (multi-database)

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.20

Site 1
Windows NT
ORACLE

Network

Site 4 Site 2
Windows NT Windows NT
ORACLE ORACLE

Site 3 Homogeneous
Windows NT Distributed Database
ORACLE System

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.21

Site 1
Windows NT
ORACLE

Network

Site 4 Site 2
Unix Windows NT
Informix MS SQL Server

Site 3 Heterogeneous
Unix Distributed Database
ORACLE System

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.22

Site 1
Windows NT
ORACLE

Network
No permanent
integration of
data
Site 4 Site 2
Unix Windows NT
Informix MS SQL Server
Site 3
Unix Federated
ORACLE Database System

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.23

Distributed DBMS
• More complex system catalogue

• Concurrency control needs to be more

sophisticated

• Query optimiser needs to take account of

distributed nature of data

• The DBMS itself must be distributed to ensure

stability of systems

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.24

Physical Design and Distributed

Database Systems
• Follow normal process of physical design in
designing base tables

• Investigate which data is used at which site

• Decide on fragmentation needed

• Consider topology of network and traffic on system

to minimise traffic

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.25

Learning Outcomes
By the end of this unit students will be able to:
• Recognise the need for distributed data
• Define the main features of a distributed database
• Define the different types of distributed databases

Have we met them?

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.26

References
• Database Systems Benyon-Davies, Paul. Palgrave
Third Edition 2004 Chapter 37

• Connolly, Thomas M., and Begg, Carolyn E.,

Database Systems: A Practical Approach to Design
and Implementation Addision-Wesley, Fourth
Edition 2005 Chapter 22

V1.0 © NCC Education Limited

 Distributed Databases Topic 10 - 10.27

Topic 10 – Distributed Databases

Any Questions?

V1.0 © NCC Education Limited