DISTRIBUTED DATABASES AND DDBMS Learning Objectives Describe various

DISTRIBUTED DATABASES AND DDBMS

Learning Objectives Describe various DDBMS implementations Explain how database design affects the DDBMS environment Apply DDBMS principles to solve problems

Definitions Distributed Database: A single logical database that is spread physically across computers in multiple locations that are connected by a data communications link Decentralized Database: A collection of independent databases on non-networked computers They are not the same thing!

Distributed database environments (adapted from Bell and Grimson, 1992)

Homogeneous Database Identical DBMSs Source: adapted from Bell and Grimson, 1992.

Typical Heterogeneous Environment Non-identical DBMSs Source: adapted from Bell and Grimson, 1992.

Example of Replication CRM Db User’s View of Db • Customers • Sales Staff • Orders Actual Implementation N. America Europe • All Customers • All Sales Staff • All Orders Master Replica

Example of Horizontal Partitioning User’s View of Db CRM Db • Customers • Sales Staff • Orders Actual Implementation N. America Europe • NA Customers • NA Sales Staff • NA Orders • E Customers • E Sales Staff • E Orders

Example of Vertical Partitioning ERP System User’s View of Db • Financials • Customer Service • Prod. Support • Human Resources Actual Implementation N. America • Financials • Human Resources Europe • Customer Service • Prod Support

Five Distributed Database Organizations § Centralized database, distributed access § Replication with periodic snapshot update § Replication with near real-time synchronization of updates § Partitioned, one logical database § Partitioned, independent, nonintegrated segments

Distributed Design Strategies

Distributed DBMS: Transparency Location Transparency Replication Transparency User/application does not need to know where data resides User/application does not need to know about duplication Failure Transparency Either all or none of the actions of a transaction are committed

Applying the concepts through examples: Client server: Mono, 2 -tier, 3 -tier, n-tier? Single Site / Multi Site data? Y/N If multi-site: Partitioned H/V or Replicated? Location Transparency: Y/N? Replication Transparency: Y/N? Failure Transparency: Y/N?

Distributed DBMS architecture

Local Transaction Steps 1. 2. 3. 4. 5. Application makes request to distributed DBMS Distributed DBMS checks distributed data repository for location of data. Finds that it is local Distributed DBMS sends request to local DBMS Local DBMS processes request Local DBMS sends results to application

Distributed DBMS Architecture - cont. Showing local transaction steps 2 1 3 5 4 Local transaction – all data stored locally

Global Transaction Steps 1. 2. 3. 4. 5. 6. 7. 8. Application makes request to distributed DBMS Distributed DBMS checks distributed data repository for location of data. Finds that it is remote Distributed DBMS routes request to remote site Distributed DBMS at remote site translates request for its local DBMS if necessary, and sends request to local DBMS Local DBMS at remote site processes request Local DBMS at remote site sends results to distributed DBMS at remote site Remote distributed DBMS sends results back to originating site Distributed DBMS at originating site sends results to application

Distributed DBMS architecture – cont Showing global transaction steps 2 3 1 7 8 6 4 5 Global transaction – some data is at remote site(s)

DISTRIBUTED DATABASE AND DDBMS Questions?