A step-wise distributed database design methodology

Following is a step-wise methodology for distributed database design.

(1) Examine the nature of distribution. Find out whether an organisation requires having a database at each branch office, or in every city, or possibly at a regional level. It has direct implication from the viewpoint of  fragmentation.   For example, in case database is needed at each branch office, the relations may fragment on the basis of branch number.

(2) Create a detailed global E-R diagram, if so needed and classify relations from entities.

(3) Analyse the most significant transactions in the system and classify where horizontal or vertical fragmentation may be desirable and helpful.

(4) Classify the relations that are not to be fragmented. Such relations will be replicated everywhere. From the global ER diagram, eliminate the relations that are not going to be fragmented.

(5) Study the relations that are on one-side of a relationship and choose a suitable fragmentation schema for these relations. Relations on the many-side of a relationship might be the candidates for derived fragmentation.

(6)  During the earlier step, check for situations where either vertical or mixed fragmentation would be required, that is, where the transactions need access to a subset of the attributes of a relation.