Topic 10

Topic Overview

  • Exploration of database implementation concepts

  • Focus on distributed database architectures and embedded SQL programming

Learning Outcomes

After completing this topic, you should be able to:

  • Explain drivers for the development of Distributed Database Management Systems (DDBMS)

  • Distinguish between distributed processing and distributed databases

  • Describe functions and architecture of DDBMS

  • Explain design factors in distributed databases: partitioning, allocation, and replication

  • Describe transparency goals of DDBMS

  • Explain distribution effects on concurrency and the two-phase commit protocol

  • Describe components of replication services and replication models

Distributed Database Concepts

Definition

  • Distributed Database: A logically interrelated collection of shared data distributed across a network.

  • Distributed DBMS (DDBMS): Software that manages distributed databases, ensuring distribution transparency.

Architecture

  • Nodes act as both clients and servers (client-server model)

  • Network for communication among sites

  • Each site managed by its local DBMS, supporting local applications

Reasons for Distribution

  • Organizations commonly have distributed structures (divisions, departments, geographic locations)

  • Enables:

    • Data sharing

    • Improved data availability

    • Storage of data close to its usage point

Types of DDBMS

  • Homogeneous DDBMS: All sites use the same DBMS.

  • Heterogeneous DDBMS: Sites may use different DBMS products, needing translations.

Advantages and Challenges

Advantages

  • Mirrors organizational distribution

  • Enhanced shareability and local autonomy in accessing data

  • Improved availability, reliability, and performance

  • Cost-effective due to distributing workloads across nodes

Challenges

  • Increased complexity in architecture

  • Difficult integrity control due to fragmented data

  • Security risks with replicated data

  • Higher maintenance and communication costs

Functions of DDBMS

  • Must have full DBMS functionality plus:

    • Extended communication and security services

    • Distributed query processing

    • Extended recovery services

Database Design Principles

Key Issues

  1. Partitioning (Fragmentation): Data divided into sub-relations, distributed logically.

  2. Allocation: Optimal distribution of data fragments across sites.

  3. Replication: Copies of fragments maintained for reliability.

Types of Partitioning

  • Horizontal: Subset of tuples of a relation.

  • Vertical: Subset of attributes of a relation.

  • Mixed: Combination of horizontal and vertical.

  • Derived: Fragmentation on derived relations.

Transparency Goals of DDBMS

  • Allows users to interact as though the system is centralized.

Types of Transparency

  • Distribution Transparency: Users unaware of physical distribution.

  • Transaction Transparency: Ensures integrity across distributed transactions.

  • Performance Transparency: The system should perform equally to a centralized DBMS.

  • Database Transparency: Integrates various DBMSs under a global schema.

Embedding SQL in Program Code

Procedural Programming vs. SQL

  • Mismatch exists between SQL's table-based results and procedural languages.

SQL/Persistent Stored Modules (SQL/PSM)

  • Module Types:

    • User-defined functions

    • Triggers

    • Stored procedures

User-defined Functions

  • Stored SQL statements callable by other SQL statements.

  • Can have input parameters and output values.

Triggers

  • Executed automatically on data modification events (insert, update, delete).

  • Types: BEFORE, AFTER, INSTEAD OF.

Stored Procedures

  • Compiled modules within the database, called at runtime.

  • Can take parameters, return results.

Replication in DDBMS

  • Provision of data copies at multiple sites to lower complexity.

Types of Replication

  • Snapshot Replication: Copies data at a certain time.

  • Transactional Replication: Maintains ongoing sync after initial snapshot.

  • Merge Replication: Allows autonomous changes at subscribers and synchronizes later.

Subscription Types

  • Push Subscription: Publisher sends updates automatically.

  • Pull Subscription: Subscriber requests data updates.