What is fragmentation?
- The process of dividing the database into a smaller multiple parts is called as fragmentation.
- These fragments may be stored at different locations.
- The data fragmentation process should be carrried out in such a way that the reconstruction of original database from the fragments is possible.
Types of data Fragmentation
There are three types of data fragmentation:1. Horizontal data fragmentation
Horizontal fragmentation divides a relation(table) horizontally into the group of rows to create subsets of tables.
Example:
Account (Acc_No, Balance, Branch_Name, Type).
In this example if values are inserted in table Branch_Name as Pune, Baroda, Delhi.
The query can be written as:
SELECT*FROM ACCOUNT WHERE Branch_Name= “Baroda”
Types of horizontal data fragmentation are as follows:
1) Primary horizontal fragmentation
Primary horizontal fragmentation is the process of fragmenting a single table, row wise using a set of conditions.
Example:
| Acc_No | Balance | Branch_Name |
|---|
| A_101 | 5000 | Pune |
| A_102 | 10,000 | Baroda |
| A_103 | 25,000 | Delhi |
For the above table we can define any simple condition like, Branch_Name= 'Pune', Branch_Name= 'Delhi', Balance < 50,000
Fragmentation1:
SELECT * FROM Account WHERE Branch_Name= 'Pune' AND Balance < 50,000
Fragmentation2:
SELECT * FROM Account WHERE Branch_Name= 'Delhi' AND Balance < 50,000
2) Derived horizontal fragmentation
Fragmentation derived from the primary relation is called as derived horizontal fragmentation.
Example: Refer the example of primary fragmentation given above.
The following fragmentation are derived from primary fragmentation.
Fragmentation1:
SELECT * FROM Account WHERE Branch_Name= 'Baroda' AND Balance < 50,000
Fragmentation2:
SELECT * FROM Account WHERE Branch_Name= 'Delhi' AND Balance < 50,000
3) Complete horizontal fragmentation- The complete horizontal fragmentation generates a set of horizontal fragmentation, which includes every table of original relation.
- Completeness is required for reconstruction of relation so that every table belongs to at least one of the partitions.
4) Disjoint horizontal fragmentation
The disjoint horizontal fragmentation generates a set of horizontal fragmentation in which no two fragments have common tables. That means every table of relation belongs to only one fragment.
5) Reconstruction of horizontal fragmentation
Reconstruction of horizontal fragmentation can be performed using UNION operation on fragments.
2. Vertical Fragmentation
Vertical fragmentation divides a relation(table) vertically into groups of columns to create subsets of tables.
Example:
| Acc_No | Balance | Branch_Name |
|---|
| A_101 | 5000 | Pune |
| A_102 | 10,000 | Baroda |
| A_103 | 25,000 | Delhi |
Fragmentation1:
SELECT * FROM Acc_NO
Fragmentation2:
SELECT * FROM Balance
Complete vertical fragmentation- The complete vertical fragmentation generates a set of vertical fragments, which can include all the attributes of original relation.
- Reconstruction of vertical fragmentation is performed by using Full Outer Join operation on fragments.
3) Hybrid Fragmentation
- Hybrid fragmentation can be achieved by performing horizontal and vertical partition together.
- Mixed fragmentation is group of rows and columns in relation.
Example: Consider the following table which consists of employee information.
| Emp_ID | Emp_Name | Emp_Address | Emp_Age | Emp_Salary |
|---|
| 101 | Surendra | Baroda | 25 | 15000 |
| 102 | Jaya | Pune | 37 | 12000 |
| 103 | Jayesh | Pune | 47 | 10000 |
Fragmentation1:
SELECT * FROM Emp_Name WHERE Emp_Age < 40
Fragmentation2:
SELECT * FROM Emp_Id WHERE Emp_Address= 'Pune' AND Salary < 14000
Reconstruction of Hybrid Fragmentation
The original relation in hybrid fragmentation is reconstructed by performing UNION and FULL OUTER JOIN.
