Scribd
Upload
3 views23 pages

Understanding Decomposition in Databases

Decomposition is used in databases to eliminate redundancy and avoid anomalies, achieving higher normal forms for consistency and efficiency. Lossless decomposition allows for the original relation to be perfectly reconstructed without losing information, while lossy decomposition results in either lost information or spurious tuples. Examples illustrate the differences between lossless and lossy decompositions using functional dependencies.

Uploaded by

kumbharvsneha
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
3 views23 pages

Understanding Decomposition in Databases

Decomposition is used in databases to eliminate redundancy and avoid anomalies, achieving higher normal forms for consistency and efficiency. Lossless decomposition allows for the original relation to be perfectly reconstructed without losing information, while lossy decomposition results in either lost information or spurious tuples. Examples illustrate the differences between lossless and lossy decompositions using functional dependencies.

Uploaded by

kumbharvsneha
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd

Decomposition

• Why Decomposition?
• To remove data redundancy.
• To avoid insertion, deletion, and update
anomalies.
• To achieve higher normal forms (1NF → 2NF
→ 3NF → BCNF, etc.).
• To make the database consistent and
efficient.
Type of Decomposition
Lossless decomposition
A decomposition is lossless if after decomposing a
relation into two or more relations, we can reconstruct
the original relation exactly by joining them back.
• No information is lost.
• No spurious (extra) tuples are generated.
Condition for Lossless Join
• For relation R decomposed into R1 and R2:
• If (R1∩R2)→R1(R1 ∩ R2) → R1(R1∩R2)→R1 OR
(R1∩R2)→R2(R1 ∩ R2) → R2(R1∩R2)→R2, then the
decomposition is lossless.
Example (Lossless Decomposition)
Relation: R(StudentID, StudentName, CourseID, CourseName)
FDs:
StudentID → StudentName
CourseID → CourseName
Decompose into:
• R1(StudentID, StudentName)
• R2(StudentID, CourseID, CourseName)
• Common attribute = StudentID
Since StudentID → StudentName, StudentID is a key in R1.
Hence, lossless join.
Original Table (R):
• StudentIDStudentNameCourseIDCourseName1RahulC1DBMS2Priya
C2OS
R1:StudentIDStudentName1Rahul2Priya
R2:StudentIDCourseIDCourseName1C1DBMS2C2OS
• Join(R1, R2) = Original R
Lossy Decomposition
• A decomposition is lossy if after joining the
decomposed relations, we either:
• Lose some information, OR
• Generate spurious tuples (extra unwanted
rows).
Example (Lossy Decomposition)
Relation: R(StudentID, StudentName, CourseID)
FD:
• StudentID → StudentName
• Decompose into:
R1(StudentID, StudentName)
R2(StudentName, CourseID)
Common attribute = StudentName
But StudentName is not a key in either R1 or R2. Hence, lossy join.
Original Table (R):
StudentIDStudentNameCourseID1RahulC11RahulC2
R1:StudentIDStudentName1Rahul
R2:StudentNameCourseIDRahulC1RahulC2
Join(R1, R2):
• StudentIDStudentNameCourseID1RahulC11RahulC21RahulC11Rahul
C2

You might also like