Distributed Database: Definition, Benefits, and Key Features

1. Definition of Distributed Database

A distributed database is a collection of logically interrelated data that is physically stored and managed across multiple geographically dispersed locations (nodes/sites), connected through a computer network. Although the data resides at different physical locations, it appears to the user as a single unified database.:

"Portions of a database, known as database fragments, may reside in several physical locations."

The primary goal of a distributed database is to provide scalability, fault tolerance, and improved performance compared to a centralized database. It can handle larger data volumes and support higher levels of concurrent access.

2. Benefits of Distributed Databases Over Centralized Databases

The key advantages are as follows:

i. Reflects Organizational Structure

Organizations are naturally distributed over several locations (branches, departments, regions). A distributed database mirrors this structure by allowing each location to manage its own local data while remaining part of the whole system.

ii. Improved Availability

The system is designed to continue operating even if one node fails. Because data can be replicated across multiple nodes, the failure of a single node does not bring down the entire system. This is a major advantage over a centralized database where a single point of failure can make the entire database unavailable.

iii. Improved Performance

Data exists in more than one site, so each site handles only a part of the entire database. This reduces the load on any single node and improves overall query response time. Local queries are processed locally, reducing network traffic and latency.

iv. Modular Growth (Scalability)

New sites (nodes) can be added to the network without affecting the operations of other existing sites. This allows the system to grow incrementally as organizational needs expand, which is not easily achievable in a centralized database.

v. Integration

Distributed databases allow integration of software components from different vendors to meet specific requirements. This is especially useful in heterogeneous environments where different departments may use different DBMS software.

3. Key Features: Availability, Reliability, and Scalability

A. Availability

Availability refers to the ability of the distributed database system to remain accessible and operational at all times, even in the presence of partial failures.

• In a distributed database, data replication plays a central role in ensuring availability. Copies of data are stored on multiple nodes so that if one node fails, the data can still be accessed from another node.
• As noted in the reference: "To improve fault tolerance and availability, copies of data may be stored on multiple nodes, which is data replication. It ensures that if one node fails, the data can still be accessed from other nodes."
• Homogeneous distributed database systems specifically provide high availability and fault tolerance by replicating data and schema across multiple nodes.
• Unlike a centralized database where a server crash makes the entire database unavailable, a distributed database continues to serve users through its surviving nodes.

Example: If a bank's database node in Kathmandu fails, users can still access their account data from the replicated node in Pokhara.

B. Reliability

Reliability refers to the ability of the distributed database to consistently deliver correct results and recover from failures without data loss or corruption.

• Reliability is achieved through data replication and redundancy. Since multiple copies of data exist across different nodes, the system can recover from node failures without losing data.
• Fault tolerance is a core design principle: homogeneous distributed systems are specifically noted to provide fault tolerance.
• Distributed databases use mechanisms such as distributed transaction management and commit protocols (e.g., two-phase commit) to ensure that transactions are completed correctly across all nodes, maintaining data consistency and reliability.
• Even if a network partition or node failure occurs mid-transaction, the system can roll back or complete the transaction reliably.

Comparison with Centralized DB: In a centralized database, hardware failure can lead to complete data loss if backups are not maintained. In a distributed database, the inherent redundancy ensures reliability.

C. Scalability

Scalability refers to the ability of the distributed database to handle increasing amounts of data, users, and transactions by adding more nodes without disrupting existing operations.

• As stated in the reference: "New site can be added to hardware without affecting operations of other site." This is called modular growth.

• A distributed database supports horizontal scalability: instead of upgrading a single powerful server (as in centralized databases), new nodes are simply added to the network.

• Data distribution strategies such as:

  • Horizontal partitioning: Each node holds a portion of the rows of a table.
  • Vertical partitioning: Each node holds a subset of columns of a table.

  These strategies allow the workload to be distributed evenly, ensuring the system scales efficiently.

• The reference also notes: "A distributed database can handle larger data volumes and support higher levels of concurrent access."

Comparison with Centralized DB: A centralized database has a physical limit on how much it can scale (bounded by a single machine's capacity). A distributed database scales out horizontally with minimal disruption.

Summary Table

In conclusion, distributed databases offer significant advantages over centralized databases in terms of availability through replication, reliability through fault tolerance and redundancy, and scalability through modular growth and data partitioning, making them ideal for large-scale, geographically distributed organizations.

Importance of Indexing in Database and Multilevel Index

Why Indexing is Important (2.5 marks)

When executing SQL queries, it takes some amount of time to access data from the disk. An index is a data structure that helps to find and access data in a table of a database quickly. Indexing technique reduces the number of disk accesses required to process queries.

The key reasons why indexing is important are:

Without indexing, the database would perform a full table scan for every query, which is extremely slow for large databases.

Multilevel Index (2.5 marks)

Definition

A multilevel index is an indexing scheme where the index itself is further indexed at multiple levels, creating a hierarchy of indexes. It is used when a single-level index becomes too large to fit in memory or becomes inefficient to search.

Why Multilevel Index is Needed

• In a large database, it is not possible to search all the indexes at once to obtain the required data.
• A single-level index on a very large table can itself become very large, making the index search slow.
• Multilevel indexing solves this by creating an index over the index, reducing the search space at each level.

Structure

Level 3 (Top/Outer Index) --> very small, fits in memory
        |
Level 2 (Intermediate Index)
        |
Level 1 (Inner/Base Index)
        |
   Actual Data Blocks

• Level 1 (Primary Index): Index on the actual data file.
• Level 2 (Secondary Index): Index on the Level 1 index, further reducing search space.
• Level 3 and beyond: Further levels are added as needed until the top-level index fits in a single disk block or memory.

Key Points

• Each level reduces the number of entries to be searched.
• The top-level index is small enough to be loaded into main memory.
• Multilevel indexing is the foundation of B-Tree and B+ Tree index structures commonly used in DBMS.
• Secondary indexing contains another level of indexing to minimize the size of mapping, which is a basic form of multilevel indexing.

Example

If a table has 1,000,000 records and each index block holds 100 entries:

• Level 1 index: 10,000 blocks
• Level 2 index: 100 blocks
• Level 3 index: 1 block (fits in memory)

This reduces disk accesses from potentially 1,000,000 to just 3 levels, demonstrating the efficiency of multilevel indexing.

In summary, indexing is critical for fast and efficient data retrieval in databases, and multilevel indexing extends this concept to handle very large datasets by organizing indexes in a hierarchical manner, minimizing disk accesses at every level.