Advanced Database - Old Questions

2.What is data fragmentation? Discuss horizontal and vertical fragmentation in detail.

Data Fragmentation: A process of splitting a relation into logically related and correct parts. Fragmentation consists of breaking a relation into smaller relations or fragments, and storing the fragments (instead of the relation itself), possibly at different sites. In horizontal fragmentation, each fragment consists of a subset of rows of the original relation. In vertical fragmentation, each fragment consists of a subset of columns of the original relation. A relation can be fragmented in three ways:

1. Horizontal Fragmentation
2. Vertical Fragmentation
3. Mixed Fragmentation

Horizontal Fragmentation:

A horizontal fragment of a relation is a subset of the tuples in that relation. The tuples that belong to the horizontal fragment are specified by a condition on one or more attributes of the relation. Horizontal fragmentation divides a relation “horizontally” by grouping rows to create subsets of tuples, where each subset has a certain logical meaning. These fragments can then be assigned to different sites in the distributed system. Derived horizontal fragmentation applies the partitioning of a primary relation to other secondary relations which are related to the primary via a foreign key.

It is a horizontal subset of a relation which contains those of tuples which satisfy selection conditions specified in the SELECT operation of the relational algebra on single or multiple attributes. Consider the Customer relation with selection condition (sex= male). All tuples satisfy this condition will create a subset which will be a horizontal fragment of Customer relation.

Customer

Horizontal Fragmentation are subsets of tuples (rows)

σsex= male(customer)

Fragment 1

Fragment 2

σsex=female(customer)

Vertical Fragmentation:

Vertical fragmentation divides a relation “vertically” by columns. A vertical fragment of a relation keeps only certain attributes of the relation. It is a subset of a relation which is created by a subset of columns. Thus a vertical fragment of a relation will contain values of selected columns. There is no selection condition used in vertical fragmentation. All vertical fragments of a relation are connected by using PROJECT operation of the relational algebra.

Example

Vertical fragmentation is subset of attributes

Fragment 1

Fragment 2

To combine all the vertically fragmented tables we need to perform join operation on the fragments.

SELECT customer_id, Name, Area, Sex, Payment_type

FROM Fragment 1 NATURAL JOIN Fragment 2;

Mixed (Hybrid) Fragmentation

We can intermix the two types of fragmentation, yielding a mixed fragmentation. The original relation can be reconstructed by applying UNION and OUTER UNION (or OUTER JOIN) operations in the appropriate order. In general a fragment of a relation can be specified by SELECT-PROJECT combination of operations.