Relational Decomposition
- When a relation in the relational model is not in appropriate normal form then the decomposition of a relation is required.
- In a database, it breaks the table into multiple tables.
- If the relation has no proper decomposition, then it may lead to problems like loss of information.
- Decomposition is used to eliminate some of the problems of bad design like anomalies, inconsistencies, and redundancy.
Types of Decomposition
Lossless Decomposition
- If the information is not lost from the relation that is decomposed, then the decomposition will be lossless.
- The lossless decomposition guarantees that the join of relations will result in the same relation as it was decomposed.
- The relation is said to be lossless decomposition if natural joins of all the decomposition give the original relation.
Example:
EMPLOYEE_DEPARTMENT table:
EMP_ID |
EMP_NAME |
EMP_AGE |
EMP_CITY |
DEPT_ID |
DEPT_NAME |
22 |
Denim |
28 |
Mumbai |
827 |
Sales |
33 |
Alina |
25 |
Delhi |
438 |
Marketing |
46 |
Stephan |
30 |
Bangalore |
869 |
Finance |
52 |
Katherine |
36 |
Mumbai |
575 |
Production |
60 |
Jack |
40 |
Noida |
678 |
Testing |
The above relation is decomposed into two relations EMPLOYEE and DEPARTMENT
EMPLOYEE table:
EMP_ID |
EMP_NAME |
EMP_AGE |
EMP_CITY |
22 |
Denim |
28 |
Mumbai |
33 |
Alina |
25 |
Delhi |
46 |
Stephan |
30 |
Bangalore |
52 |
Katherine |
36 |
Mumbai |
60 |
Jack |
40 |
Noida |
DEPARTMENT table
DEPT_ID |
EMP_ID |
DEPT_NAME |
827 |
22 |
Sales |
438 |
33 |
Marketing |
869 |
46 |
Finance |
575 |
52 |
Production |
678 |
60 |
Testing |
Now, when these two relations are joined on the common column "EMP_ID", then the resultant relation will look like:
Employee ⋈ Department
EMP_ID |
EMP_NAME |
EMP_AGE |
EMP_CITY |
DEPT_ID |
DEPT_NAME |
22 |
Denim |
28 |
Mumbai |
827 |
Sales |
33 |
Alina |
25 |
Delhi |
438 |
Marketing |
46 |
Stephan |
30 |
Bangalore |
869 |
Finance |
52 |
Katherine |
36 |
Mumbai |
575 |
Production |
60 |
Jack |
40 |
Noida |
678 |
Testing |
Hence, the decomposition is Lossless join decomposition.
Dependency Preserving
- It is an important constraint of the database.
- In the dependency preservation, at least one decomposed table must satisfy every dependency.
- If a relation R is decomposed into relation R1 and R2, then the dependencies of R either must be a part of R1 or R2 or must be derivable from the combination of functional dependencies of R1 and R2.
- For example, suppose there is a relation R (A, B, C, D) with functional dependency set (A->BC). The relational R is decomposed into R1(ABC) and R2(AD) which is dependency preserving because FD A->BC is a part of relation R1(ABC).
|