Chapter 12: Distributed Database Management Systems (DDBMSs)

Comprehensive review flashcards covering Chapter 12: Distributed Database Management Systems (DDBMSs), including architecture types, levels of distribution, transparency features, distributed concurrency control (2PC), data fragmentation, replication styles, and data allocation strategies.

What is the difference between a centralized database and a centralized DBMS?

A centralized database is a database where all data is stored and managed in a single physical location or on a single server. A centralized DBMS is the software system that manages that centralized database.

Why is a centralized database architecture unsuitable for large-scale platforms like Uber, Netflix, Amazon, and TikTok?

It suffers from performance bottlenecks (a single server struggles with massive simultaneous requests), limited scalability (adding users/data overloads the server), a single point of failure (if the server crashes, the entire system goes offline), latency issues for distant users, and regulatory/compliance challenges (such as GDPR or POPIA requiring local data storage).

What is a distributed database, and what is a Distributed Database Management System (DDBMS)?

A distributed database stores a logically related database over two or more physically independent sites connected via a network, divided into fragments. A DDBMS is the software system that manages a distributed database and treats it as a single logical database.

In the UKZN student records fictional example, how is the single logical database physically fragmented across campus data centers?

Westville Campus stores student biographical and financial information; Howard College Campus manages academic records (modules, marks, transcripts); and Pietermaritzburg Campus stores residential and accommodation records.

What is the distinction between distributed processing and a distributed database?

Distributed processing shares database processing tasks across two or more independent computers over a network (while data may reside on a single database server). A distributed database physically divides and stores the data itself across two or more independent sites.

What are the two primary software components of a DDBMS and their alternative names?

The Transaction Processor (TP), also known as the Application Processor (AP) or Transaction Manager (TM), runs on workstations/computers accessing data; and the Data Processor (DP), also known as the Data Manager (DM), is the software that directly stores and retrieves data on site.

What are three key advantages and three key disadvantages of a DDBMS?

Advantages: High availability/reliability (no single point of failure), improved performance/faster data access (data placed closer to users), and scalability/growth. Disadvantages: System complexity, high initial and operational costs (storage, training, duplication), and security/administrative challenges.

What are the characteristics of Single-site Processing, Single-site Data (SPSD)?

All processing is performed on a single host computer, and all data is stored on that host computer's local disk system.

What are the characteristics of Multiple-site Processing, Single-site Data (MPSD)?

Multiple processes run on different computers across a network, but all of them share and access a single centralized data repository.

What are the characteristics of Multiple-site Processing, Multiple-site Data (MPMD)?

A fully distributed DBMS where both processing and data are distributed, supporting multiple data processors (DPs) and multiple transaction processors (TPs) across multiple sites.

How is a Homogeneous DDBMS defined?

A system where all sites use the same DBMS software product (e.g., all Oracle 19c) and the same data model (e.g., relational), allowing straightforward integration and communication.

How does a Heterogeneous DDBMS differ from a Fully Heterogeneous DDBMS?

A Heterogeneous DDBMS uses different DBMS vendors across sites (e.g., Oracle and SQL Server) but maintains the same data model (relational). A Fully Heterogeneous DDBMS integrates different DBMS vendors, different data models (relational, NoSQL, network), and potentially different query languages across different platforms and operating systems.

What are the five minimum desirable transparency features in a DDBMS?

Distribution transparency, Transaction transparency, Failure transparency, Performance transparency, and Heterogeneity transparency.

What are the three levels of distribution transparency?

1. Fragmentation transparency (highest): The user/programmer does not need to know the database is partitioned. 2. Location transparency (medium): The user must specify fragment names but not their physical locations. 3. Local mapping transparency (low): The user must specify both fragment names and physical site locations.

What level of distribution transparency is shown by the query: 'SELECT * FROM CUSTOMER WHERE Gender = \'Female\';'?

Fragmentation transparency (high level), because the user queries the logical table name without needing to specify fragment names or physical locations.

What level of distribution transparency is shown by a query that explicitly combines fragments: 'SELECT * FROM F1 WHERE Gender = \'Female\' UNION SELECT * FROM F2 WHERE Gender = \'Female\';'?

Location transparency (medium level), because the query requires the specific fragment names ($F_1$, $F_2$) but does not require specifying the physical server locations where those fragments reside.

What level of distribution transparency is shown by: 'SELECT * FROM F1 Durban WHERE DOB < \'20 January 1995\' UNION SELECT * FROM F2 Johannesburg WHERE …'?

Local mapping transparency (low level), because the query requires the user to specify both the exact fragment names ($F_1$, $F_2$) and their physical site locations (Durban, Johannesburg).

What is Transaction Transparency, and what is its core requirement?

Transaction transparency ensures that a transaction can access or update data at multiple network sites while maintaining atomicity: either the entire distributed transaction commits successfully, or all changes across all sites are rolled back.

How does a Remote Request differ from a Remote Transaction?

A Remote Request is a single SQL statement that accesses data processed by a single remote Data Processor (DP). A Remote Transaction consists of several SQL statements (requests) sent to a single remote DP as part of a single transaction.

How does a Distributed Transaction differ from a Distributed Request?

A Distributed Transaction contains several requests that access data located at multiple local or remote DP sites. A Distributed Request allows a single SQL statement to reference data located across multiple local or remote DP sites simultaneously.

What is Failure Transparency in a DDBMS?

The ability of the distributed database system to continue operating when a network node or link fails, by detecting failures and redirecting transactions to alternative available nodes or replicas.

What is Performance Transparency in a DDBMS?

The system's ability to achieve performance comparable to a centralized DBMS by choosing optimal query evaluation paths, minimizing network communication, and processing queries as close as practical to where data resides.

What is Heterogeneity Transparency in a DDBMS?

The ability of a DDBMS to integrate several different types of DBMS technologies, data models, and database products under a unified global schema, hiding technical differences from end users.

How does the Two-Phase Commit Protocol (2PC) maintain distributed transaction atomicity?

In the Prepare Phase, the coordinator sends a PREPARE message to all participating sites, which reply YES (Ready) or NO (Abort). In the Commit Phase, if all sites reply YES, the coordinator sends a global COMMIT; if any site replies NO or fails, a global ROLLBACK is issued to abort all changes at all sites.

What are the three strategies for data fragmentation?

1. Horizontal Fragmentation: Splitting a table into row subsets. 2. Vertical Fragmentation: Splitting a table into column subsets. 3. Mixed Fragmentation: Combining horizontal and vertical fragmentation.

Which relational algebra operations are used to reconstruct horizontally fragmented and vertically fragmented tables?

Horizontal fragments are reconstructed using the UNION operation. Vertical fragments are reconstructed using the JOIN operation (linked by the common primary key).

What are the two common methods of horizontal partitioning?

1. Round-robin partitioning: Assigns rows sequentially to fragments ($F_1, F_2, \dots, F_n$) to evenly balance data distribution. 2. Range partitioning: Partitions rows based on the value of a specific attribute known as the partition key.

What is a mandatory design rule when performing vertical fragmentation on a database table?

Every vertical fragment must include the primary key column(s) of the original table so that the complete relation can be reconstructed without loss of data.

What does the mutual consistency rule state in data replication?

All copies of identical data fragments stored across multiple sites must be identical and kept synchronized.

How do Push Replication and Pull Replication differ in terms of update propagation and primary focus?

In Push Replication, the originating node immediately pushes updates to all replica nodes after a write; its primary focus is data consistency (decreasing availability/increasing latency). In Pull Replication, the originating node notifies replicas, which decide when to request and apply updates; its primary focus is data availability (allowing temporary data inconsistencies).

What are the three data replication scenarios supported in distributed database design?

1. Fully Replicated Database (all fragments duplicated at all sites; high overhead). 2. Partially Replicated Database (some fragments duplicated at select sites; most practical and common). 3. Unreplicated Database (each fragment stored at only one site; no duplicates).

What are the three primary data allocation strategies in a DDBMS?

1. Centralized Data Allocation (entire database stored at a single site). 2. Partitioned Data Allocation (database divided into disjoint fragments stored at different sites). 3. Replicated Data Allocation (copies of fragments stored across multiple sites).

In the Mzansi Retail Chain scenario, why did a sales administrator in Cape Town ($S_2$) see an outdated shipping address for several minutes after it was updated in Johannesburg ($S_3$)?

The fragment ($Orders\_A2$) used pull replication, meaning the originating node only sent an update notification and the Cape Town site waited before pulling and applying the change, creating temporary data inconsistency.