Question

Consider an Engineering database that maintains three tables:

EMP (ENO, NAME, TITLE, SAL)

PROJ (PNO, PNAME, BUDGET, LOC)

ASG (PNO, ENO, RESP, DUR)

EMP stores employee information, including the name, title and salary of the employee. PROJ stores project information, including the name, budget and location of the project. ASG keeps information about the assignment between projects and employees. RESP is the responsibility of the employee, while DUR is the duration that the employee works on the project. ENO is the employee id. PNO is the project id. The keys are underlined.

The following predicates appear in the majority of queries.

LOC = “New York”, LOC = “Montreal”, LOC = “Paris”

SAL > 5000, SAL ≤ 5000

a) Write down the fragments if we perform primary fragmentation on EMP and PROJ, given the predicates above.

PLEASE EXPLAIN EACH STEP IN DETAIL.

Answer 1

Fragmentation:
   Fragmentation means dividing the database into multiple parts.
Primary Fragmentation:
   Primary Fragmentation or Primary Horizontal Fragmentation means fragmenting a table row wise
   (horizontally) using a set of simple predicates(conditions).

Simple Predicate means consider a Table with Attributes A1,A2,...An
   -> Then the Predicate can be expressed as P = Ai CONDITION value.
   -> CONDITION can be operator like <,>,=,<>,etc.,
   -> Ai is the i_th Attribute in Table
   -> value is anything on which we want to fragment the Table data.
  

Now the Predicates in your question are:

   -> Predicate 1 based on LOC Attribute
       LOC = "New York"
       LOC = "Montreal"
       LOC = "Paris"
   -> Predicate 2 based on SAL Attribute
       SAL > 5000
       SAL ≤ 5000

Let us consider fragmenting the table on two conditions of Attributes LOC, SAL

Fragmentation 1 for LOC = "New York" and SAL > 5000:
   SELECT * FROM EMP e,PROJ p WHERE p.LOC = "New York" AND e.SAL > 5000

Fragmentation 2 for LOC = "Montreal" and SAL > 5000:
   SELECT * FROM EMP e,PROJ p WHERE p.LOC = "Montreal" AND e.SAL > 5000

Fragmentation 3 for LOC = "Paris" and SAL > 5000:
   SELECT * FROM EMP e,PROJ p WHERE p.LOC = "Paris" AND e.SAL > 5000

Fragmentation 4 for LOC = "New York" and SAL <= 5000:
   SELECT * FROM EMP e,PROJ p WHERE p.LOC = "New York" AND e.SAL <= 5000

Fragmentation 5 for LOC = "Montreal" and SAL <= 5000:
   SELECT * FROM EMP e,PROJ p WHERE p.LOC = "Montreal" AND e.SAL <= 5000

Fragmentation 6 for LOC = "Paris" and SAL <= 5000:
   SELECT * FROM EMP e,PROJ p WHERE p.LOC = "Paris" AND e.SAL <= 5000

After fragmenting the Tables EMP and PROJ with Above Fragmentation Queries
Result returns will be in such a way that the TWO conditions on attributes are satisfied.

Comment if you have any doubts and please like the answer.