RELATIONAL
DECOMPOSITI
ON
• Relational decomposition refers to the process of
breaking down a relation (table) in a database
into smaller, more manageable sub-relations (or
sub-tables).
• This process is typically done to normalize the
database,which helps in
Minimizing Redundancy
Ensuring Data Integrity.
Key Properties Of Relational
Decomposition
• Attribute Preservation
• Dependency Preservation
• Non Additive Join Property:
• No redundancy
• Lossless Join
Attribute Preservation
• Using functional dependencies the algorithms
decompose the universal relation schema R in a set of
relation schemas
• D = { R1, R2, ….. Rn } relational database schema,
• where ‘D’ is called the Decomposition of R.
• The attributes in R will appear in at least one relation
schema Ri in the decomposition, i.e., no attribute is lost.
• This is called the Attribute Preservation condition of
decomposition.
Dependency Preservation
• If each functional dependency X->Y
• specified in F appears directly in one of the relation
schemas Ri in the decomposition D
• It could be inferred from the dependencies that appear
in some Ri. This is the Dependency Preservation.
• If a decomposition is not dependency preserving some
dependency is lost in decomposition. To check this
condition, take the JOIN of 2 or more relations in the
decomposition.
Non Additive Join Property:
• Another property of decomposition is that D
should possess is the Non Additive Join Property,
which ensures that no spurious tuples are
generated when a NATURAL JOIN operation is
applied to the relations resulting from the
decomposition.
Lossless Join:
• A decomposition is lossless if it allows the
original relation to be perfectly reconstructed
from the decomposed relations through a natural
join operation.
• Lossless decomposition is critical to ensure no
data is lost during the decomposition.
• For a decomposition to be lossless, the
intersection of the attributes between the
decomposed relations must functionally
determine at least one of the decomposed
relations.
Examples of Relational Decompostion
• Example Table: Employee
Functional Dependencies (FDs):
• Emp_ID → Emp_Name, Dept_No (An employee ID
determines the name and department number.)
• Dept_No → Dept_Name, Dept_Location (A department
number determines the name and location of the
department.)
• Problem:
• This table contains redundancy. For example, the
information about the department is repeated for
employees in the same department (e.g., Alice and
Charlie both belong to the "Sales" department located
in "New York"). This can lead to anomalies.
Step 1: Decomposition
• Let’s decompose the table to remove redundancy by normalizing it
into smaller tables while maintaining data integrity.
• Decompose into Two Tables:
1.Employee_Info(Emp_ID, Emp_Name, Dept_No)
2.Department_Info(Dept_No, Dept_Name, Dept_Location)
Properties of Relational Decomposition
• 1. Lossless Join Decomposition:
After decomposing, we need to verify if we can reconstruct the
original table
by performing a natural join on the two new tables
(Employee_Info and Department_Info)
on the common attribute, Dept_No.
Performing the natural join between Employee_Info and
Department_Info:
Performing the natural join between Employee_Info and Department_Info:
• This result matches the original
table, so the decomposition is
lossless. No data was lost during
the decomposition.
Dependency Preservation
• Original Functional Dependency:
• Emp_ID → Emp_Name, Dept_No is preserved in the Employee_Info
table, as both Emp_Name and Dept_No can still be determined by
Emp_ID.
• Original Functional Dependency:
• Dept_No → Dept_Name, Dept_Location is preserved in the
Department_Info table, as Dept_No can still determine both
Dept_Name and Dept_Location.
Lossy Decomposition
• Definition: Lossy decomposition refers to a situation where the
original table cannot be perfectly reconstructed by joining the
decomposed tables.
• Problem: If the decomposition is not lossless, some data may be lost
when trying to reassemble the original relation. This results in
incorrect or incomplete data.
• Cause: Lossy decompositions often occur when the common
attributes between the decomposed tables do not form a superkey in
either of the tables.
Example of Lossy Decomposition: