Derived Horizontal Fragmentation
Today, in this fresh new article, we will cover the following topics;- Derived Horizontal Fragmentation.
- Owner Table in Derived Horizontal Fragmentation.
- Member Table in Derived Horizontal Fragmentation
Owner Table in Derived Horizontal Fragmentation
Owner table is a parent table to which we apply the constraint.Member Table in Derived Horizontal Fragmentation
Member table is a child table that can be fragmented but by following the constraints of the parent table. If we fragment the tables separately, then for every insertion of records the table must verify the existence of one such value in the parent table. Hence, for this case, the Primary Horizontal Fragmentation would not work. Let’s consider an example, where an international university maintains the information about its STUDENTs. They store information about the STUDENT in STUDENT table and the STUDENT addresses in ADDRESS table as follows;STUDENT(RollNo, Name, Marks, Country)
ADDRESS(RollNo, Address)
RollNo | NAME | MARKS | COUNTRY |
01 | Fazal | 22 | IRAQ |
02 | Abdul | 66 | Italy |
03 | Sameed | 77 | UK |
04 | Shahzeb | 90 | China |
05 | Mumraiz | 66 | China |
Table 1: STUDENT table
RollNo | Address |
01 | City A, IRAQ |
01 | City A, UK |
02 | City D, Italy |
02 | City A, Pakistan |
03 | City D, IRAQ |
04 | City D, Iraq |
04 | City A, Pakistan |
05 | City B, China |
Table 2: Address Table
If the organization would go for fragmenting the relation STUDENT on the Country attribute, it needs to create 4 fragments using horizontal fragmentation as mentioned in table below;STUDENT1 | ROLLNO | NAME | MARKS | COUNTRY |
C001 | Fazal | 22 | IRAQ | |
STUDENT2 | ROLLNO | NAME | MARKS | COUNTRY |
C002 | Abdul | 66 | Italy | |
STUDENT3 | ROLLNO | NAME | MARKS | COUNTRY |
C010 | Mumraiz | 66 | China | |
C004 | Shahzeb | 90 | China | |
STUDENT4 | ROLLNO | NAME | MARKS | COUNTRY |
C003 | Sameed | 77 | UK |
Table 3: Horizontal fragments of Table 1 on Country attribute
Now, it is necessary to fragment the second relation ADDRESS based on the fragment created in STUDENT relation. The fragmentation of ADDRESS is done as follow as a set of semi-joins as follows.ADDRESS1 = ADDRESS ⋉ STUDENT1
ADDRESS2 = ADDRESS ⋉ STUDENT2
ADDRESS3 = ADDRESS ⋉ STUDENT3
ADDRESS4 = ADDRESS ⋉ STUDENT4
This will result in four fragments of ADDRESS where the STUDENT address of all STUDENTs of fragment STUDENT1 will go into ADDRESS1, and the STUDENT address of all STUDENTs of fragment STUDENT2 will go into ADDRESS2, and so on. The resultant fragment of ADDRESS will be the following;RollNo | Address |
01 | City A, IRAQ |
01 | City A, UK |
RollNo | Address |
02 | City D, Italy |
02 | City A, Pakistan |
RollNo | Address |
04 | City D, Iraq |
04 | City A, Pakistan |
05 | City B, China |
RollNo | Address |
03 | City D, IRAQ |