Distributed DBMS - Design Strategies

Distributed DBMS - Design Strategies

We introduced various design alternatives in the last chapter. We will study the strategies that help in the adopting of the designs in this chapter. It is possible to split the strategies broadly into replication and fragmentation. A combination of the two is, however, used in most cases.

Data Replication

The method of storing separate copies of the database at two or more sites is data replication. It is a popular distributed database fault tolerance technique.

Advantages of Data Replication

• Reliability-The database system continues to run in case of failure on any site since a copy is available on another site(s).
• Network Load Reduction-Since local data copies are available, query processing can be performed with reduced network use, particularly during prime hours. Updating data at non-prime hours can be done.
• Quicker Response- The availability of local data copies ensures fast processing of queries and, therefore, fast response time.
• Simpler Transactions-Transactions mean minimal tables located at various sites and minimal network-wide coordination. Therefore, they are simpler in nature.

Disadvantages of Data Replication

• Increased Storage Requirements-The maintenance of multiple data copies is associated with increased storage costs. Multiples of the storage required for a centralized system provide the storage space needed.
• Increased Data Update Cost and Complexity -Each time a data item is modified, the update must be replicated in all copies of the data at the various sites. This includes complicated techniques and protocols for synchronization.
• Undesirable Application-Database coupling-If complex update methods are not used, complex coordination at the application level is needed to eliminate data inconsistency. This results in undesirable software-coupling of databases.

Such widely used methods of replication are –

• Snapshot replication
• Near-real-time replication
• Pull replication

Fragmentation

The process of dividing a table into a number of smaller tables is fragmentation. The table's subsets are called fragments. There can be three kinds of fragmentation: horizontal, vertical, and hybrid (horizontal and vertical combination). It is further possible to divide horizontal fragmentation into two techniques: primary horizontal fragmentation and derived horizontal fragmentation.

In order for the original table to be reconstructed from the fragments, fragmentation should be done in a way. This is required so that the original table can be reconstructed whenever necessary from the fragments. This requirement is referred to as "reconstructiveness."

Advantages of Fragmentation

• As data is stored near the site of use, the efficiency of the database system is enhanced.
• For most queries, local query optimization techniques are sufficient because data is accessible locally.
• Since irrelevant data is not accessible on the sites, it is possible to maintain the security and privacy of the database system.

Disadvantages of Fragmentation

• The access speeds can be very high when data from various fragments is required.
• In the case of recursive fragmentation, expensive techniques would be needed for reconstruction work.
• In the event of a server failure, the lack of back-up copies of data on various sites will make the database ineffective.

Vertical Fragmentation

The fields or columns of a table are grouped into fragments during vertical fragmentation. Each fragment should contain the primary key field(s) of the table to preserve reconstructiveness. To implement information privacy, vertical fragmentation can be used.

For example , let us consider that in a student table with the following schema, a university database keeps records of all registered students.

STUDENT

Now, the fees details are maintained in the accounts section. In this case, the designer will fragment the database as follows −

CREATE TABLE STD_FEES AS 
   SELECT Regd_No, Fees 
   FROM STUDENT;

Horizontal Fragmentation

Horizontal fragmentation classifies the table's tuples according to the values of one or more fields. The reconstructiveness rule should also be verified by horizontal fragmentation. Each horizontal fragment must contain all of the original base table columns.

In the student scheme, for example, if the records of all computer science students need to be retained at the School of Computer Science, the designer will fragment the database horizontally as follows.

CREATE COMP_STD AS 
   SELECT * FROM STUDENT  
   WHERE COURSE = "Computer Science";

Hybrid Fragmentation

A combination of horizontal and vertical fragmentation methods are used in hybrid fragmentation. This is the most flexible technique of fragmentation because fragments with minimal extraneous information are generated. Reconstruction of the initial table, however, is always a costly task.

It is possible to do hybrid fragmentation in two alternative ways:

• Generate a sequence of horizontal fragments first, and then generate vertical fragments from one or more horizontal fragments.
• Generate a sequence of vertical fragments first, then generate horizontal fragments from one or more of the vertical fragments.