Question 1

In distributed database classifications, what is an autonomous homogeneous environment?

Accepted Answer

Introduction / Context: Distributed databases are often categorized along two axes: homogeneity versus heterogeneity (same versus different DBMS products) and autonomy versus central control (local independence versus global coordination). This classification guides design choices and middleware needs. Given Data / Assumptions: Homogeneous: identical DBMS engines at all nodes. Autonomous: nodes operate independently without a central controller. Networked environment interconnects the nodes. Concept / Approach: An autonomous homogeneous environment means each site uses the same DBMS software yet retains local control over its data and transactions. Global queries may require federation, but there is no single central DBMS dictating access. Step-by-Step Solution: Check “homogeneous” → same DBMS everywhere.Check “autonomous” → no central coordinator; independent operation.Pick the statement that matches both characteristics. Verification / Alternative check: Taxonomies in distributed DB literature contrast this with “tightly coupled” (central coordination) or “heterogeneous” (different products) environments. Why Other Options Are Wrong: B: same DBMS but not autonomous (central coordination). C/D: heterogeneous, not homogeneous. Common Pitfalls: Assuming homogeneity implies central control; they are independent axes. Final Answer: The same DBMS is at each node and each DBMS works independently.

Question 2

Within a DBMS, what are the core responsibilities of a Transaction Manager (consider logging, recovery, and concurrency)?

Accepted Answer

Introduction / Context: The transaction manager enforces ACID properties in database systems. It coordinates logging, recovery, and isolation so that committed work is durable and concurrent activities do not corrupt data. Given Data / Assumptions: Transactions need atomicity and durability (logging, recovery). Isolation requires concurrency control (locks, MVCC). System failures must be recoverable using logs and images. Concept / Approach: A transaction manager typically maintains a transaction log, stores before/after images (or logical redo/undo information), and enforces concurrency control via locking or MVCC to achieve isolation levels. Step-by-Step Solution: Map ACID properties to mechanisms: A/D → logging & images; I → concurrency control.Recognize the transaction manager as the component integrating these functions.Select the option that includes all duties. Verification / Alternative check: DBMS architecture references consistently list logging/recovery and locking/MVCC under the transaction subsystem. Why Other Options Are Wrong: A/B/C alone are partial; D captures the complete set. Common Pitfalls: Believing logging alone guarantees isolation; without concurrency control, anomalies can occur. Final Answer: All of the above.

Question 3

Which statement best defines a distributed database in terms of logical unity and physical distribution across networked locations?

Accepted Answer

Introduction / Context: Distributed databases present a unified logical view of data even when the data is physically partitioned or replicated across multiple sites. This definition distinguishes them from simple file sharing or isolated databases. Given Data / Assumptions: Multiple sites connected via a network. Transparency goals (location, fragmentation, replication) typical in distributed DBMS. Unified schema or global catalog provides the single logical view. Concept / Approach: A distributed database is one logical database that spans sites. Users query it as if it were centralized; the DBMS handles routing, fragmentation, and replication behind the scenes. Step-by-Step Solution: Check for “single logical” and “multiple locations.”Reject definitions that imply mere files or single-site scope.Choose the option combining logical unity with physical distribution. Verification / Alternative check: Classic distributed DB definitions (for example, Ceri & Pelagatti) emphasize logical integration with physical distribution. Why Other Options Are Wrong: B and D: describe loose file collections, not DBMS-managed databases. C: describes a centralized database. Common Pitfalls: Equating “federated” file access or ETL copies with a distributed DBMS; the latter supplies a global schema and query processing. Final Answer: A single logical database that is spread to multiple locations and is interconnected by a network

Question 4

When is database replication most appropriate (consider network constraints, heterogeneity, and tolerance for staleness)?

Accepted Answer

Introduction / Context: Replication copies data across sites to improve availability, performance, and local autonomy. Deciding when to replicate requires weighing network bandwidth, heterogeneity, and read/write patterns—including tolerance for slightly stale data. Given Data / Assumptions: Large tables are costly to refresh frequently across slow networks. Sites may be heterogeneous, complicating tightly coupled distributed transactions. Use cases may allow eventual consistency where slight staleness is acceptable. Concept / Approach: Replication is well-suited when bandwidth is limited (reduce round-trips), environments are heterogeneous (decouple updates), and the application can accept eventual consistency rather than strict real-time synchronization. Step-by-Step Solution: Evaluate each condition independently as a reason to replicate.Recognize that each favors local reads and asynchronous propagation.Select the option that consolidates all valid reasons. Verification / Alternative check: CAP-inspired designs and classic replication literature cite bandwidth constraints and staleness tolerance as drivers for replication strategies. Why Other Options Are Wrong: Each single reason is valid but incomplete; D captures the full set. Common Pitfalls: Assuming replication always guarantees strict consistency; asynchronous models trade freshness for availability/performance. Final Answer: All of the above.

Question 5

Which option best describes a disadvantage (or cost) associated with database replication across sites?

Accepted Answer

Introduction / Context: Replication provides benefits—availability, local read performance, and fault tolerance—but also introduces costs such as additional storage, administrative overhead, and potential consistency challenges. Given Data / Assumptions: Replicas consume storage at multiple sites. Network coordination can be reduced for reads but still required for writes depending on model. The question frames one option as a disadvantage related to capacity. Concept / Approach: While strict equality of storage capacity is not a hard requirement in all systems, the downside captured by Option C reflects a practical cost: maintaining replicated copies increases storage demands across sites. In many exam contexts, this is presented as “every site must provision substantial storage for replicated data,” which is a disadvantage relative to a single copy. Step-by-Step Solution: Identify advantages: reduced network latency for reads; failover; local execution.Contrast with a storage-related burden across sites.Select the option highlighting the storage disadvantage. Verification / Alternative check: Operational experience shows storage and administrative overheads scale with the number of replicas; capacity planning becomes more complex. Why Other Options Are Wrong: A: reduced network traffic for read-heavy workloads is an advantage, not a disadvantage. B: having a copy at another site is high availability (advantage). D: local autonomy (fewer coordinated transactions) is considered an advantage for performance and availability. Common Pitfalls: Assuming replication is “free” aside from setup; it impacts storage, write coordination, and conflict resolution. Final Answer: Each site must have the same storage capacity.

Question 6

Distributed database fundamentals In a homogeneous distributed database system, which description is most accurate about the DBMS software at each site and how the data are laid out across participating nodes?

Accepted Answer

Introduction / Context: A distributed database stores logically related data across multiple networked sites. A key classification is whether the participating sites run the same database engine (homogeneous) or different engines (heterogeneous). Understanding this distinction helps you reason about transparency, query processing, and administration in distributed systems. Given Data / Assumptions: The term “homogeneous” refers to the DBMS software stack at each site. We are discussing a distributed database, so data are logically one database but physically spread. Sites cooperate through a distributed DBMS layer that coordinates queries and transactions. Concept / Approach: In a homogeneous distributed DBMS, every node runs the same DBMS product and version (or compatible variants). This uniformity simplifies metadata sharing, query optimization, and two-phase commit because catalogs, SQL dialects, and transaction semantics align. Since it is distributed, data are fragmented and/or replicated across nodes; the layer provides transparency for location, fragmentation, and sometimes replication. Step-by-Step Solution: 1) Identify “homogeneous” → same DBMS at all sites.2) Recognize “distributed database” → data are spread (fragmented and/or replicated) across nodes.3) Eliminate options that imply different DBMS engines (heterogeneous).4) Eliminate options that deny distribution (not consistent with a distributed DB).5) Choose the option stating same DBMS + distributed data. Verification / Alternative check: Standard references define homogeneous DDBMS as uniform DBMS software across nodes with distribution of data; heterogeneity introduces differing engines, catalogs, and gateways to reconcile dialects and semantics. Why Other Options Are Wrong: Same DBMS but not distributed: contradicts the “distributed database” premise. Different DBMS (c or d): describes heterogeneous, not homogeneous. Shared filesystem only: lacks DBMS-level distribution semantics. Common Pitfalls: Confusing “homogeneous” with “centralized.” Homogeneous still distributes data. Assuming identical hardware is required; homogeneity is about DBMS software, not servers. Final Answer: The same DBMS is used at every site, and data are distributed across the participating nodes.

Question 7

Data distribution patterns When some of the columns (attributes) of a relation are placed at different sites while rows may remain aligned by primary key, this is called:

Accepted Answer

Introduction / Context: Distributed databases often split tables to improve locality, security, and performance. Two primary strategies are horizontal partitioning (by rows) and vertical partitioning (by columns). Knowing which method is used helps predict query routing, join costs, and storage design. Given Data / Assumptions: Some columns of a relation are stored at different sites. A common key (typically the primary key) exists for reconstructing the full row via joins. Goal: identify the correct partitioning terminology. Concept / Approach: Vertical partitioning divides a table by columns. Each fragment contains the key plus a subset of attributes to enable reconstruction (lossless decomposition). In contrast, horizontal partitioning divides by rows (tuples), typically using predicates or hashing. Replication duplicates data across sites rather than splitting it. A hybrid approach uses both row and column fragmentation simultaneously. Step-by-Step Solution: 1) “Some columns are at different sites” → column-based split.2) Attach the primary key to each fragment to preserve reconstructability.3) Identify the term for column-based splits → vertical partitioning.4) Reject horizontal partitioning (row-based) and replication (duplicates). Verification / Alternative check: Textbooks define vertical partitioning as attribute-wise distribution with the key repeated; horizontal partitioning distributes tuples based on predicates or hashing across sites. Why Other Options Are Wrong: Replication: copies the same data at multiple sites. Horizontal partitioning: distributes rows, not columns. Hybrid: both are used; the stem mentions only columns. Shuffling: vague, not a standard term for schema design. Common Pitfalls: Omitting the key in vertical fragments, making lossless joins impossible. Confusing columnar storage formats with vertical partitioning (they are related but distinct concepts). Final Answer: Vertical partitioning.

Question 8

Transparency goals in distributed databases “Location transparency” in a distributed DBMS allows which stakeholders to behave as if all data were in one place?

Accepted Answer

Introduction / Context: Distributed DBMSs strive for several transparencies: location, fragmentation, and replication. Location transparency means users and applications do not need to know the physical site of data. Instead, the system resolves where a fragment lives and routes operations automatically, improving usability and portability. Given Data / Assumptions: The organization has data spread across multiple sites. Stakeholders include users, developers, and managers using tools and applications. The DBMS provides a single logical schema or global view. Concept / Approach: If location transparency holds, SQL queries, BI tools, and end-user applications operate against a unified catalog. The optimizer and data shipping layer select sites, plan sub-queries, and combine results without exposing site details to stakeholders. Thus, the property benefits all roles—not only DBAs—by hiding distribution complexity. Step-by-Step Solution: 1) Define location transparency → physical location is hidden.2) Consider each stakeholder: users, programmers, managers.3) All benefit from not hard-coding locations.4) Therefore, “All of the above” is correct. Verification / Alternative check: Canonical definitions list location transparency as a core goal alongside replication and fragmentation transparency; examples include global schemas and location-independent naming. Why Other Options Are Wrong: Restricting transparency to one role defeats the point of system-wide abstraction. Common Pitfalls: Embedding site qualifiers in code, which erodes transparency and portability. Final Answer: All of the above.

Question 9

Centralized vs distributed databases Which of the following is a core advantage a distributed database offers over a purely centralized database?

Accepted Answer

Introduction / Context: Organizations distribute data to improve availability, locality, and scalability. While distribution introduces complexity (coordination, catalog management, distributed transactions), it enables incremental growth and better proximity to users and services. This question asks you to identify a true advantage rather than a myth. Given Data / Assumptions: Workloads can be geographically or functionally partitioned. Capacity needs grow over time. Network and coordination overhead exist but are manageable. Concept / Approach: Distributed databases support modular growth: you can add nodes, place fragments where needed, and replicate for read scaling or availability. Centralized systems can scale up, but distribution enables scale-out and locality benefits. Costs and complexity usually increase, so options promising blanket cost reduction or simplicity are suspect. Likewise, slower response is not an advantage, and concurrency control remains essential. Step-by-Step Solution: 1) Evaluate each option against known trade-offs.2) A and B: costs and complexity typically go up, not down.3) C: slower response is a disadvantage, not an advantage.4) E: concurrency control is still required for correctness.5) D: modular growth is the genuine advantage. Verification / Alternative check: Design literature emphasizes scale-out and data locality as drivers for distribution (e.g., global applications, fault domains, and read replicas). Why Other Options Are Wrong: Licensing and complexity seldom decrease. Slower response is clearly not beneficial. Correctness requires concurrency control regardless of topology. Common Pitfalls: Over-replicating without considering write amplification and consistency. Final Answer: Modular growth (scale by adding sites with data placed near workloads).

Question 10

Defining global transactions In a distributed DBMS, a “global transaction” is best described as one where:

Accepted Answer

Introduction / Context: Transactions define units of atomic work. In distributed databases, a transaction may touch data at multiple sites. Such a transaction must be coordinated to ensure ACID properties across nodes, typically through a transaction manager and protocols like two-phase commit (2PC). Given Data / Assumptions: The database is distributed across networked sites. The transaction's read/write set may include fragments at remote sites. The DDBMS provides routing and commit coordination. Concept / Approach: A global (or distributed) transaction accesses at least one nonlocal site. The DDBMS decomposes the query/updates into subtransactions, executes them at the right sites, and coordinates commit/rollback. If everything is local, it is not “global.” If the DDBMS refuses to route, it cannot satisfy cross-site data access, contradicting the definition. Step-by-Step Solution: 1) Determine whether nonlocal sites are involved → yes for global transactions.2) Ensure the DDBMS routes and coordinates requests across sites.3) Recognize the need for distributed commit to maintain atomicity.4) Choose the option that states nonlocal access with DDBMS routing. Verification / Alternative check: Most DDBMS textbooks classify transactions as local (single site) or global/distributed (multiple sites), with 2PC ensuring atomic outcomes across participants. Why Other Options Are Wrong: Options a/c: describe local-only transactions. Option d: contradicts routing beyond local; then it isn't truly distributed. Option e: cross-site commit typically requires coordination (e.g., 2PC). Common Pitfalls: Ignoring partial failures and network partitions in design of global transactions. Final Answer: Some required data are at one or more nonlocal sites and the DDBMS routes requests across sites as needed.

Question 11

Reducing data transfer with semijoins In distributed query processing, a semijoin strategy does which of the following to minimize communication cost?

Accepted Answer

Introduction / Context: Network I/O is a major cost in distributed joins. A classic optimization is the semijoin, which uses projections on join attributes to filter remote relations before transferring full tuples, thereby cutting down data shipped across the network. Given Data / Assumptions: Two relations R and S reside at different sites. We need R ⋈ S on some join attributes (e.g., R.k = S.k). Goal: minimize data transferred while preserving correctness. Concept / Approach: In a semijoin, the initiating site sends only the distinct join attribute values (π_k(R)) to the remote site holding S. The remote site filters S to S’ = σ_{k ∈ π_k(R)}(S) and returns only the matching rows (or sometimes just their keys). This preselection step avoids shipping irrelevant tuples from S that would not contribute to the final join. Step-by-Step Solution: 1) Project join attributes from the first relation.2) Ship this small set of key values to the remote site.3) Filter the remote relation by those keys to obtain only relevant rows.4) Return the reduced set (or keys) to complete the join with the local relation. Verification / Alternative check: Semijoin-based query plans are widely cited in distributed optimization, especially when selectivity is high and join attribute domains are much smaller than full tuples. Why Other Options Are Wrong: Sending all attributes (b/d) increases traffic unnecessarily. Sending join attributes but returning all rows (a) defeats the purpose. Broadcasting all data (e) is worst-case and rarely optimal. Common Pitfalls: Using semijoins when selectivity is poor, yielding little reduction. Forgetting to deduplicate projected keys before shipping. Final Answer: Sends only the join attributes to a remote site and then returns only the required matching rows.

Question 12

Heterogeneous distributed databases Which description best characterizes a heterogeneous distributed database system?

Accepted Answer

Introduction / Context: Heterogeneous distributed databases integrate multiple DBMS products—often with different SQL dialects, data types, and transaction semantics—into a single logical database. Middleware, gateways, or federation layers reconcile differences to provide location and, at times, heterogeneity transparency to applications. Given Data / Assumptions: Multiple DBMS engines (e.g., Oracle, PostgreSQL, SQL Server) participate. Data are physically distributed across sites. A coordination layer provides a global schema and routing. Concept / Approach: The defining trait is differing DBMSs across nodes. Distribution still applies; otherwise, it would not be a distributed database. Integrating unlike systems requires translation (SQL dialects, type mappings), metadata federation, and distributed transaction coordination where supported. Step-by-Step Solution: 1) Identify “heterogeneous” → different DBMS products.2) Confirm data are distributed, not centralized.3) Integration relies on gateways or federation middleware.4) Select the option that matches all three aspects. Verification / Alternative check: Federated database systems and heterogeneous DDBMS designs are documented to use wrappers/gateways (e.g., Oracle Database Links, SQL Server Linked Servers, foreign data wrappers) to bridge engines. Why Other Options Are Wrong: Options a/b: homogeneous, not heterogeneous. Option c: not distributed (contradicts the premise). Option e: lacks DBMS-level distribution semantics. Common Pitfalls: Assuming full ACID across heterogeneous engines is automatic; capabilities vary. Final Answer: Different DBMS products are used at sites, and data are distributed across nodes with gateways/middleware to integrate them.

Question 13

Redundancy for availability and locality Keeping a separate full copy of the database at multiple locations is known as:

Accepted Answer

Introduction / Context: Replication stores duplicate copies of data across sites or servers for availability, read scaling, and locality. It differs from partitioning, which splits the data. Replication policies (synchronous vs asynchronous) and topologies (primary–secondary, multi-primary) influence consistency and latency guarantees. Given Data / Assumptions: Multiple sites each maintain a copy of the same database (full or partial). Replication mechanisms keep copies in sync. Goal: identify the correct term for “separate copy at multiple locations.” Concept / Approach: Replication == duplication; partitioning == division. Horizontal partitioning divides rows among sites; vertical partitioning divides columns. Hybrid uses both. Replication may be layered atop partitioning (e.g., each shard is replicated) to achieve both scale and availability. Step-by-Step Solution: 1) Recognize “separate copy” at multiple sites → duplication.2) Map duplication to the term “replication.”3) Exclude partitioning options that split rather than duplicate.4) Exclude specialized coding schemes that are not simple full-copy replication. Verification / Alternative check: DBMS manuals describe replication for HA/DR and geo-distribution; partitioning is used for scale-out and data placement. Why Other Options Are Wrong: Horizontal/vertical/hybrid: these distribute parts of the data, not full copies. Erasure coding: parity-based redundancy, not verbatim full copies. Common Pitfalls: Assuming replication alone ensures consistency; mode matters (sync/async). Final Answer: Data replication.

Question 14

Capability milestones in distributed DBMS evolution Which of the following is NOT commonly listed as a stage in the historical evolution of distributed DBMS capability?

Accepted Answer

Introduction / Context: Distributed DBMS capability evolved from simple remote access to fully distributed queries and transactions. Terminology varies slightly across sources, but typical stages include remote request, remote unit of work, distributed unit of work, and distributed request. Recognizing the odd one out requires recalling these standard milestones. Given Data / Assumptions: We consider progression in terms of how much of a statement/transaction can span sites. “Remote” implies operations at one nonlocal site at a time. “Distributed” implies multiple sites within one unit of work. Concept / Approach: Common taxonomy: (1) Remote request (single SQL statement sent to a remote site), (2) Remote unit of work (multiple statements, but at one remote site per unit of work), (3) Distributed unit of work (a transaction spans multiple sites, one site per statement), (4) Distributed request (a single SQL statement can access data at multiple sites). “Unit of work” by itself, without the “remote” qualifier, refers to a local capability that predates distributed features and is not part of the distributed milestone list. Step-by-Step Solution: 1) List canonical stages: remote request, remote unit of work, distributed unit of work, distributed request.2) Compare options and spot which one is not part of that distributed list.3) “Unit of work” (local only) is not a distributed capability stage.4) Choose it as the answer. Verification / Alternative check: Reference texts and vendor docs (e.g., early IBM/ISO terminology) follow the above naming. While terms can vary, the unqualified “unit of work” is generally not counted among distributed milestones. Why Other Options Are Wrong: Remote unit of work: recognized stage. Distributed unit of work: recognized stage. Distributed request: recognized stage. Remote request: recognized stage. Common Pitfalls: Confusing “unit of work” (transaction scope) with distributed capability categories. Final Answer: Unit of work (without remote capability).

Question 15

Strategy variants in distributed databases Which strategies can a distributed database use to organize and place data across sites?

Accepted Answer

Introduction / Context: Distributed databases offer flexible deployment patterns. Even a “distributed” system may centralize certain critical datasets while providing remote access, replicate hot objects for availability and read scaling, and partition large tables for locality and parallelism. Understanding these options helps architects balance performance, cost, and consistency. Given Data / Assumptions: Diverse workload requirements exist (OLTP, analytics, regional traffic). Network latency and bandwidth vary by site. The DDBMS or data platform supports multiple placement strategies. Concept / Approach: Valid strategies include: centralized storage with distributed access (simplifies management but may add latency), partial/total replication (improves availability and proximity but increases write cost), and partitioning (splits data by rows/columns to scale and localize access). A practical design often combines all three, depending on data domains and SLAs. Step-by-Step Solution: 1) Evaluate each option as a known strategy.2) A: central store with remote access—valid for some systems.3) B: replication—widely used for HA and read scaling.4) C: partitioning—core to sharding and locality.5) Therefore select “All of the above.” Verification / Alternative check: Architectural guides describe hybrid deployments combining central stores, replicas, and shards to meet diverse requirements. Why Other Options Are Wrong: “None of the above” is false; many systems use each approach. “Only full replication” is overly restrictive and inefficient for many workloads. Common Pitfalls: Over-replicating write-heavy datasets, causing latency and conflicts. Ignoring data governance and compliance when placing shards globally. Final Answer: All of the above.

Question 16

Replication styles: With pull replication, is synchronization typically less disruptive because each subscriber pulls updates when needed rather than receiving pushes on the publisher’s schedule?

Accepted Answer

Introduction / Context: Database replication can be configured as push (publisher initiates) or pull (subscriber initiates). This question evaluates whether pull replication tends to be less disruptive by letting each site choose when to fetch changes. Given Data / Assumptions: Subscribers have local workload patterns and maintenance windows. Network conditions and SLAs vary by site. Consistency goals permit scheduled latency. Concept / Approach: In pull replication, the subscriber controls when to synchronize, reducing unexpected contention and allowing alignment with local off-peak hours. This contrasts with push replication, where the publisher pushes changes on its timetable, potentially interrupting subscribers during busy periods. While details depend on platform, the general operational effect is that pull is more subscriber-friendly and can be less disruptive. Step-by-Step Solution: Assess workload windows at each site.Configure subscribers to pull during low-impact periods.Tune batch sizes and retry policies to balance freshness and load.Monitor latency and adjust schedule as needed. Verification / Alternative check: Observe system metrics before and after switching from push to pull on busy subscribers; reduced locking, CPU spikes, or I/O contention during business hours confirms the benefit. Why Other Options Are Wrong: “Incorrect because pull causes more locks” is not generally true; locks are tied to apply operations, not direction of initiation. Restricting correctness to snapshots or LANs is unnecessary; scheduling benefits apply broadly across replication types and networks. Common Pitfalls: Allowing excessive latency that violates freshness requirements, or misconfiguring agent credentials/tokens so pulls fail silently. Implement monitoring and alerts. Final Answer: Correct

Question 17

Definition check: Are databases stored at multiple locations without any interconnecting network still considered “distributed databases”, or does distribution imply networked coordination among sites?

Accepted Answer

Introduction / Context: Distributed databases coordinate a single logical database across multiple networked sites. This question tests whether merely having databases in many places—without networking—meets the definition of “distributed.” Given Data / Assumptions: Multiple computers store related data sets. No network connectivity or distributed DBMS services exist between them. Goal of distributed systems: transparency (location, fragmentation, replication) via coordinated software. Concept / Approach: Distribution implies coordination. A distributed DBMS provides a unified interface, concurrency control, and recovery across nodes. Physically separated but unconnected databases are simply independent databases, not parts of a distributed database. Tape/drive sneaker-net copies do not create distribution; they create replicas or backups without online coordination. Step-by-Step Solution: Check for a single logical schema spanned across sites.Verify network connectivity enabling distributed query, concurrency, and recovery.If absent, recognize the systems as separate, not distributed.Add a distributed layer (e.g., sharding, replication) to achieve true distribution. Verification / Alternative check: Run a cross-site transaction. If there is no facility for two-phase commit, distributed queries, or global catalogs, the environment is not a distributed database. Why Other Options Are Wrong: Marking “Correct” confuses geographic dispersion with distributed database architecture. Building location or portable media do not satisfy the defining characteristics. Replication may exist without exposing a single logical database, but true distributed DBMSs coordinate more than copies. Common Pitfalls: Assuming offsite backups form a distributed DB; they do not provide distributed concurrency or transparency. Final Answer: Incorrect

Question 18

Distributed unit of work (DUOW): Does it allow the SQL statements inside a single unit of work to reference multiple remote DBMS locations under one transactional context?

Accepted Answer

Introduction / Context: Distributed transactions extend ACID guarantees across multiple databases or servers. A “distributed unit of work” addresses whether multiple remote locations can be involved within one logical transaction boundary. Given Data / Assumptions: SQL statements may target more than one server or catalog. A transaction manager or coordinator is available (for example, two-phase commit). ACID properties must hold across all participants. Concept / Approach: A distributed unit of work groups statements across nodes so they either all commit or all roll back together. Coordinators manage prepare/commit phases to guarantee atomicity and durability. This enables cross-database updates like “debit here, credit there” without inconsistent outcomes. Step-by-Step Solution: Begin a transaction (explicit or implicit depending on DBMS).Execute statements referencing multiple remote locations.Coordinator invokes prepare on all participants, ensuring readiness.Commit if all agree; otherwise roll back everywhere. Verification / Alternative check: Simulate failure of one participant during the prepare phase; the coordinator should force a global rollback, demonstrating atomic behavior across sites. Why Other Options Are Wrong: “Incorrect” contradicts the definition of DUOW. Limiting to read-only or tying to autocommit/bandwidth misunderstands that the key is a transaction coordinator and protocol, not read/write status or network speed. Common Pitfalls: Ignoring failure modes and idempotency; ensure participants support recovery logs and in-doubt resolution to avoid blocked transactions. Final Answer: Correct

Question 19

Concurrency control methods: Is the primary purpose of timestamp ordering to avoid the use of locks by ordering transactions based on timestamps rather than acquiring traditional data locks?

Accepted Answer

Introduction / Context: Concurrency control can be pessimistic (locking) or optimistic (validation/timestamps). Timestamp ordering is an optimistic strategy that schedules conflicting operations based on transaction timestamps rather than locking data items preemptively. Given Data / Assumptions: Each transaction receives a unique timestamp at start. Read/write rules compare operation times against item timestamps (read_TS, write_TS). Goal is serializability without holding locks for long durations. Concept / Approach: Instead of locking, timestamp ordering validates whether an operation would violate the serial order implied by timestamps. If a conflict arises (for example, a late write following a newer read), the offending transaction aborts and restarts with a new timestamp. This avoids deadlocks common in locking systems and can improve throughput in low-contention workloads. Step-by-Step Solution: Assign a timestamp to each transaction when it begins.On each read/write, compare with the item’s recorded read/write timestamps.If ordering would be violated, abort and retry the transaction.If valid, update the relevant item timestamps and proceed. Verification / Alternative check: Analyze deadlock graphs: timestamp protocols show no cyclical waiting because they do not rely on blocking locks. Conflicts lead to aborts, not waits. Why Other Options Are Wrong: “Incorrect” conflicts with the core goal of timestamp ordering. Limiting to a specific isolation level or platform misses the conceptual nature of the method. Claiming timestamps increase locking is the opposite of their purpose. Common Pitfalls: High abort rates under heavy contention; choosing between locking and timestamp approaches requires workload analysis. Hybrid schemes and MVCC can balance trade-offs. Final Answer: Correct

Question 20

Distributed databases: Is concurrency control generally more complex in a distributed database environment than in a centralized database?

Accepted Answer

Introduction / Context: Concurrency control ensures correctness under concurrent access. Distributed databases add network partitions, message delays, and cross-site failures to the equation. This question evaluates whether distribution inherently complicates concurrency management. Given Data / Assumptions: Multiple nodes participate in transactions and queries. Links can fail or degrade; clocks may not be perfectly synchronized. Consistency requirements (from eventual to strong) vary by system and workload. Concept / Approach: Compared to a single-node database, a distributed system must coordinate locks or validations across sites, manage deadlock detection across networks, and ensure atomic commit (often via two-phase commit). Latency and partial failures complicate both pessimistic (locking) and optimistic (timestamp/MVCC) approaches. Replication (synchronous or asynchronous) introduces further ordering and conflict resolution challenges. Step-by-Step Solution: Identify the concurrency model (locking, MVCC, OCC, or hybrid).Define scope: single-partition transactions vs. cross-partition (distributed) transactions.Implement a coordinator for atomic commit and deadlock handling.Tune timeouts, retries, and backoff strategies for WAN conditions. Verification / Alternative check: Measure abort rates and lock wait graphs under cross-site load; expect higher complexity and overhead than on a centralized node due to coordination costs and failures. Why Other Options Are Wrong: “Incorrect because each node controls its own locks” ignores global conflicts in cross-site transactions. Restricting complexity to synchronous replication or read-write only is too narrow; distributed reads can also face staleness and ordering concerns. Common Pitfalls: Underestimating network impact, neglecting idempotency, or failing to design for partitions and retries. Choose consistency levels and transaction scope intentionally. Final Answer: Correct

Distributed Databases Questions