Object-Oriented and Distributed Databases

Syllabus tag: Paper-II, Section-B, Topics IV.12–IV.13 — Object-Oriented Databases · Distributed Databases

This page covers two separate syllabus subtopics; each has its own heading below so nothing is merged away.

A. Object-Oriented Databases (OODB)

Definition

An Object-Oriented Database stores data as objects — bundling both state (attributes) and behavior (methods) together, mirroring object-oriented programming languages, in contrast to the purely tabular relational model.

Key OODB Concepts

• Object identity (OID): each object has a unique, immutable identity independent of its attribute values (unlike a relational primary key, which is value-based and can theoretically change)
• Encapsulation: an object's internal state is accessed only through its defined methods/interface
• Inheritance: classes can derive from other classes, reusing and extending attributes/methods
• Complex objects: attributes can themselves be objects, sets, or nested structures — not limited to atomic values (useful for CAD data, multimedia, engineering designs)
• Persistence: objects can be made to outlive the program run that created them, stored transparently by the OODB

OODB vs RDBMS

• Object-Relational DBMS (ORDBMS): a hybrid — a relational engine extended with object features (user-defined types, inheritance, object references) — e.g., PostgreSQL. This is the practical middle ground actually deployed today, combining SQL maturity with some OO flexibility.
• ODMG standard: an (now largely historical) attempt to standardize OODB data model, ODL (Object Definition Language), and OQL, analogous to what SQL standards did for relational databases.

B. Distributed Databases

Definition

A Distributed Database is a single logical database whose data is physically stored across multiple, possibly geographically separate, sites — while appearing to users/applications as one integrated database.

Data Fragmentation

• Horizontal fragmentation: splits a relation by rows (e.g., customers in Lahore stored at the Lahore site, Karachi customers at the Karachi site)
• Vertical fragmentation: splits a relation by columns (e.g., frequently-accessed columns at one site, rarely-accessed ones at another), with the primary key replicated in every fragment to allow reconstruction
• Mixed (hybrid) fragmentation: a combination of horizontal and vertical fragmentation

Replication

• Storing copies of the same data at multiple sites — improves availability and read performance, but complicates keeping copies consistent on writes (synchronous vs asynchronous replication).

Task Partitioning & Data Allocation

Fragmentation decides how a relation is cut up; allocation decides where each fragment is placed. Together with task partitioning (splitting the work, not just the data) this is the core design decision in a distributed DBMS.

The Allocation Problem

• Choose a placement of fragments across sites that minimises total cost — storage + local query processing + inter-site communication — subject to response-time and availability constraints.
• Inputs to the decision: access frequency of each fragment at each site, read:write ratio, network bandwidth/latency cost, and storage capacity per site.
• It is an NP-hard optimisation problem, so real systems use heuristics rather than exact optimisation.
• Guiding principle — locality of reference: place a fragment at the site that issues most of the queries against it, so most access is local and the network is used rarely.

Four Allocation Strategies

• Rule of thumb: replication helps reads and hurts writes. The more write-heavy a fragment, the fewer copies it should have.

Task Partitioning (splitting the work)

• A global query is decomposed into subqueries, each executed at the site holding the relevant fragment; partial results are shipped and combined by the coordinating site — so the optimiser is minimising communication cost, not just disk I/O as in a centralized DBMS.
• Semi-join is the classic technique: ship only the joining column's values to the remote site instead of the whole relation, then ship back only the matching tuples — drastically cutting network transfer.
• In parallel databases the same idea partitions a table across disks/nodes for throughput:
  • Round-robin: rows dealt out cyclically — perfectly even load, but every range or point query must touch all nodes
  • Hash partitioning: node = hash(key) — excellent for exact-match lookups and hash joins, poor for range queries
  • Range partitioning: contiguous key ranges per node — excellent for range queries, but risks data skew and hot spots if values are unevenly distributed
• Speedup vs scaleup: speedup = same workload finishes faster with more nodes; scaleup = a proportionally larger workload finishes in the same time with proportionally more nodes.

Transparency in Distributed Databases

• Fragmentation transparency: users need not know a relation is fragmented
• Replication transparency: users need not know how many copies of data exist or where
• Location transparency: users need not know the physical site storing the data — they query as if the database were local

Design Approaches (FPSC 2019, Q8(c))

• Top-down approach: start from global conceptual requirements, design the global schema first, then decide fragmentation/allocation across sites — typical for a database being newly designed from scratch.
• Bottom-up approach: start from existing, already-operational local databases at each site and integrate them into a unified global schema — typical when merging pre-existing independent systems (e.g., after a company merger).

Distributed Transactions

• Two-Phase Commit (2PC) protocol: ensures all sites participating in a distributed transaction either all commit or all abort.
  1. Voting/Prepare phase: coordinator asks every participant site "can you commit?"; each replies yes/no.
  2. Commit phase: if all voted yes, coordinator tells everyone to commit; if any voted no, coordinator tells everyone to abort.
• CAP Theorem: in a distributed system, you can guarantee at most two of Consistency (all nodes see the same data at the same time), Availability (every request gets a response), and Partition tolerance (the system keeps working despite network partitions) — a foundational trade-off in distributed system/NoSQL design.

Centralized vs Distributed Database (FPSC 2017)

MCQ Trap

• Vertical fragmentation must replicate the primary key in every fragment, or the original relation cannot be reconstructed via join.
• 2PC guarantees atomicity across sites but can still block if the coordinator fails between phases — a known limitation tested conceptually.

CSS Exam Angle

• Distributed databases are a recurring topic (2017, 2019, 2024) — expect either a straight comparison question (centralized vs distributed) or a design-approach question (top-down vs bottom-up).
• FPSC 2024 explicitly asked for "advantages and challenges of managing data in a distributed environment" — prepare a balanced two-column answer (benefits: scalability, availability, local autonomy; challenges: complexity, consistency, network dependency).

Likely Questions

1. Differentiate Object-Oriented and Relational databases; when is OODB preferable?
2. What is an Object-Relational DBMS, and why is it the more commonly deployed hybrid in practice?
3. Differentiate horizontal, vertical, and mixed fragmentation with examples.
4. Explain fragmentation, replication, and location transparency in a distributed database.
5. Differentiate top-down and bottom-up approaches to distributed database design.
6. Explain the Two-Phase Commit protocol and why distributed transactions need it.
7. Discuss the advantages and challenges of managing data in a distributed environment.

---

📘 Enrichment — Worked Examples

A+. OODB Example — ODL Class Definition and OQL Query

class Person (extent Persons key cnic) {
    attribute string cnic;
    attribute string name;
    attribute struct Address { string street, string city } address;   // complex attribute
};
class Student extends Person (extent Students) {
    attribute float cgpa;
    relationship set<Course> enrolledIn inverse Course::students;   // object references, no FK
    float computeScholarship();                                      // behaviour stored with data
};
class Course (extent Courses key code) {
    attribute string code;
    attribute string title;
    relationship set<Student> students inverse Student::enrolledIn;
};

-- OQL: names of students from Lahore taking 'DBMS' (path expressions replace joins)
SELECT s.name
FROM Students s, s.enrolledIn c
WHERE s.address.city = 'Lahore' AND c.title = 'DBMS';

• Note what the relational version would need: 3 tables (Student, Course, Enrollment), 2 joins, and a separate Address table or flattened columns. The OODB navigates object references (path expressions) directly.
• Impedance mismatch: the gap between objects in programming languages and tables in RDBMS — OODBs remove it; in practice most teams bridge it with ORMs (Hibernate, Entity Framework, Prisma).

A+. Object-Relational Features in PostgreSQL (the practical hybrid)

CREATE TYPE address AS (street TEXT, city TEXT);          -- user-defined composite type
CREATE TABLE person  (cnic CHAR(13) PRIMARY KEY, name TEXT, addr address, phones TEXT[]);  -- array attribute
CREATE TABLE student (cgpa NUMERIC(3,2)) INHERITS (person);   -- table inheritance
SELECT name FROM person WHERE (addr).city = 'Lahore';         -- also returns students

B+. Fragmentation Worked on Data

Global relation Customer(CustID, Name, City, Balance, CreditLimit) of a bank with branches in Lahore and Karachi.

• Horizontal: Cust_LHE = σ City='Lahore' (Customer) at the Lahore site; Cust_KHI = σ City='Karachi' (Customer) at the Karachi site. Reconstruction: Customer = Cust_LHE ∪ Cust_KHI.
• Vertical: Cust_Info = π CustID, Name, City (Customer) at the head office; Cust_Fin = π CustID, Balance, CreditLimit (Customer) at the finance data centre. Reconstruction: Customer = Cust_Info ⋈ Cust_Fin (works only because CustID is in both).
• Mixed: first split vertically, then split Cust_Fin horizontally by city.

Three correctness rules of fragmentation (always write these):

1. Completeness — every tuple/attribute of the global relation appears in some fragment.
2. Reconstruction — the global relation can be rebuilt (∪ for horizontal, ⋈ for vertical).
3. Disjointness — a tuple appears in only one horizontal fragment; for vertical fragments only the key is repeated.

B+. Numerical — Semi-Join Saves Network Cost

Site 1: Employee — 10,000 tuples × 100 bytes = 1,000,000 bytes. Site 2: Department — 100 tuples; 10 of them are in Lahore. Query issued at Site 2: employees of Lahore departments. DeptId = 4 bytes; about 1,000 employees work in Lahore departments.

• Plan A (ship the whole relation): send Employee to Site 2 → 1,000,000 bytes.
• Plan B (semi-join): send π DeptId(σ Lahore(Department)) = 10 × 4 = 40 bytes to Site 1; Site 1 computes Employee ⋉ those DeptIds = 1,000 tuples; send back 1,000 × 100 = 100,000 bytes. Total ≈ 100,040 bytes → about 90% less network traffic.

B+. Two-Phase Commit — Message Flow

sequenceDiagram
    participant C as Coordinator
    participant L as Lahore site
    participant K as Karachi site
    C->>L: PREPARE
    C->>K: PREPARE
    L-->>C: VOTE-COMMIT (after forcing log)
    K-->>C: VOTE-COMMIT
    Note over C: All yes → write commit record
    C->>L: GLOBAL-COMMIT
    C->>K: GLOBAL-COMMIT
    L-->>C: ACK
    K-->>C: ACK

• If any site votes ABORT or times out → coordinator sends GLOBAL-ABORT to all.
• Blocking problem: a participant that voted COMMIT and then loses the coordinator must wait (it cannot decide alone) → Three-Phase Commit (3PC) adds a pre-commit phase to avoid blocking (at extra message cost).

B+. ACID vs BASE and the CAP Choices

• BASE = Basically Available, Soft state, Eventual consistency — the NoSQL counterpart to ACID.
• Without a partition, a system can offer both C and A; the CAP trade-off bites only when the network splits (PACELC refinement: else choose between latency and consistency).

B+. Types of Distributed DBMS

• Homogeneous: same DBMS software at every site (e.g., Oracle everywhere) — easier to manage.
• Heterogeneous / federated / multidatabase: different DBMSs (Oracle at one site, MySQL at another) integrated through a middleware layer — typical of bottom-up design (merging existing systems).
• Architectures: client–server, peer-to-peer, and modern shared-nothing clusters (each node has its own CPU, memory, disk).

Pakistani Illustration (use in answers)

• A bank with branches in Lahore, Karachi and Peshawar keeps each branch's customer rows locally (horizontal fragmentation, locality of reference), replicates the read-heavy branch/ATM directory everywhere, and uses 2PC for inter-city fund transfers.
• NADRA-style national systems combine central master data with regional data centres and replication for disaster recovery.

---

✍️ Model Answers — Likely Questions

Q1. Differentiate object-oriented and relational databases; when is OODB preferable?

Use the OODB vs RDBMS comparison table, then add the ODL/OQL example versus the equivalent 3-table relational design.

OODB preferable for complex, deeply nested, highly interrelated data navigated by reference: CAD/CAM, GIS, multimedia, telecom network models, scientific simulations — and where application objects should persist without impedance mismatch.

RDBMS preferable for business transactions, ad-hoc reporting, standardised SQL skills and tools.

Conclusion: pure OODBs remain niche; their ideas survive in ORDBMS and document databases.

Q2. What is an ORDBMS and why is it the commonly deployed hybrid?

An ORDBMS extends the relational model with UDTs, composite and array attributes, table inheritance, methods and large objects (e.g., PostgreSQL, Oracle, IBM Db2 — standardised in SQL:1999).

Why it won: keeps mature SQL, ACID, optimizers and tooling while adding enough object features for complex data — organisations get flexibility without migrating to a new ecosystem. Illustrate with the PostgreSQL address/phones/INHERITS example.

Q3. Differentiate horizontal, vertical and mixed fragmentation.

Define each, show the Customer example with σ/π expressions and reconstruction operators (∪ vs ⋈), then state the three correctness rules. Add when to use: horizontal for geographic locality; vertical when different sites use different column groups; mixed for large enterprises.

Q4. Explain fragmentation, replication and location transparency.

Transparency = hiding distribution from users (levels from highest to lowest):

• Fragmentation transparency: user queries Customer, unaware it is split into Cust_LHE and Cust_KHI.
• Replication transparency: user unaware that the branch directory has three copies; the DDBMS keeps them consistent.
• Location transparency: user names the fragment but not its site.
• Local mapping transparency: user names both fragment and site but not local file names.

Also mention transaction transparency (2PC keeps distributed transactions atomic) and failure transparency.

Example query written at each level shows how much the user must know.

Q5. Differentiate top-down and bottom-up distributed database design.

• Top-down: requirements → global conceptual schema → fragmentation design → allocation design → local schemas. For new systems; usually homogeneous DDBMS.
• Bottom-up: existing local schemas → schema translation to a common model → schema integration (resolving naming/structural conflicts) → global schema. For merging existing systems (bank mergers, integrating provincial databases); usually heterogeneous/federated.

Draw two arrows (downward vs upward) with the steps.

Q6. Explain the Two-Phase Commit protocol.

Need: a distributed transaction (inter-city fund transfer) touches several sites; atomicity requires all sites to commit or all to abort despite independent failures.

Phase 1 (voting): coordinator sends PREPARE; each participant force-writes its log and votes COMMIT or ABORT.

Phase 2 (decision): all COMMIT → GLOBAL-COMMIT; any ABORT/timeout → GLOBAL-ABORT; participants acknowledge.

Include the sequence diagram. Limitation: blocking when the coordinator fails after votes → 3PC or consensus protocols (Paxos/Raft) in modern systems like Spanner/CockroachDB.

Q7. Discuss advantages and challenges of distributed data management.

Conclude with the balanced view FPSC 2024 wanted: modern NewSQL/cloud systems (Spanner, CockroachDB, Aurora) automate much of this complexity, which is why distribution is now the default for large systems.

---

⚡ 60-Second Revision

• OODB: OID, encapsulation, inheritance, complex objects, persistence; query language OQL.
• ORDBMS = relational + object features (PostgreSQL).
• Fragmentation rules: completeness, reconstruction, disjointness. Horizontal → ∪, vertical → ⋈.
• 2PC: prepare/vote then commit/abort; blocking weakness → 3PC.
• CAP: pick 2 of C, A, P under partition; BASE vs ACID.

📜 Past-Paper Questions & MCQs (CSS 2016–2026)

2017 · Paper-II · Q6(c) · 6 marks

Q. Differentiate between a centralised database and a distributed database.

Ans.

2019 · Paper-II · Q8(c) · 6 marks

Q. Explain the difference between top-down and bottom-up approaches in distributed database design using a suitable example.

Ans.

• Top-down: start from the global requirements, design the global conceptual schema, then fragment it and allocate the fragments to sites, and finally design the local schemas. Used when building a new distributed system. Example: a new bank designs one global schema (Account, Customer), splits the Account table by branch and places each fragment at that branch.
• Bottom-up: start from existing local databases and integrate them into one global schema (schema integration, conflict resolution). Used when merging existing systems. Example: two companies merge, and their separate employee databases are integrated into a unified global view (a federated or multi-database system).
• Difference in short: top-down designs for distribution (global → local), and bottom-up integrates what already exists (local → global).

2024 · Paper-II · Q6(c) · 6 marks

Q. What are distributed databases, and why are they used? Discuss the advantages and challenges of managing data in a distributed environment.

Ans.

• A distributed database stores logically related data on several computers (sites) linked by a network, and it appears to users as a single database.
• Why used: organisations are geographically spread, and they want local control with global access, higher availability and scalability.
• Advantages: local autonomy and faster local access, improved reliability and availability (replication), scalability (add sites), parallel query processing, and lower communication cost when data is near its users.
• Challenges: complex design (fragmentation, allocation, replication), distributed query processing and optimisation, distributed transactions (two-phase commit), keeping replicas consistent (CAP trade-offs), network failures, security across sites and higher cost and administration effort.