1 / 10

Decompositions

Decompositions. Given a relation schema R that is not in 3NF (or perhaps BCNF) decomposition can be used to help fix this problem Decomposition replaces R with R1,...,RN where 1) attributes of Ri are a subset of the attributes of R 2) each attribute of R is in at least one Ri

hanzila
Download Presentation

Decompositions

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Decompositions • Given a relation schema R that is not in 3NF (or perhaps BCNF) decomposition can be used to help fix this problem • Decomposition replaces R with R1,...,RN where • 1) attributes of Ri are a subset of the attributes of R • 2) each attribute of R is in at least one Ri • Binary decomposition: R  R1,R2

  2. Example Decomposition • Hourly_Emps relation with attributes • (Ssn, name, rating, hourly wage, hours worked) • FD: rating  hourly wage • Hourly_Emps is not in 3NF (why?) • Decompose Hourly_Emps into • R1: (ssn, name, rating) • R2: (rating, hourly wage) • Key question: can we recover any legal row in Hourly_Emps from rows in R1 and R2? Projections of Hourly_Emps

  3. Desirable Properties of Decompositions • Lossless-Join • A decomposition R  R1,R2 has the lossless join property if R can be exactly reconstructed from NATURAL_JOIN(R1,R2) • Dependency Preserving • A decomposition R  R1,R2 is dependency preserving if we can enforce all FDs on R by examining either only R1 or R2 whenever a row is inserted or modified • LJ property is essential, DP is nice • 3NF normalization w/ LJ & DP always possible • DP BCNF normalization may not be possible

  4. Example 1 of Lossy Decomposition

  5. Example 2 of Lossy Decomposition • Hourly_Emps relation with attributes • (Ssn, name, rating, hourly wage, hours worked) • FD: rating  hourly wage • Decompose Hourly_Emps into • R1: (ssn, rating) • R2: (rating, name, hourly wage) • Why?

  6. A test for lossless decomposition • The binary decomposition R with functional dependencies F into R1, R2 is lossless if and only if F contains either: • R1 ∩ R2  R1 or • R1 ∩ R2  R2 • That is, attrs common to R1 and R2 must be key of either R1 or R2. • Consequence 1: If FD X  Y holds over R and X ∩ Y is empty then decomposition of R into (R-Y) and XY is lossless. • Consequence 2: If R  R1, R2 AND R1  R1a, R1b are both lossless then R R1a,R1b,R2 is lossless.

  7. Normalization by Decomposition into BCNF • If R is not in BCNF, it is possible to obtain a lossless join decomposition into a collection of BCNF relation schmas • However, there may not by any dependency preserving decompositions into BCNF relations

  8. Normalization by Decomposition into BCNF • Suppose that R is not in BCNF and XA be a FD that violates BCNF • Decompose R into R-A and XA • If either R-A or XA is not in BCNF, decompose further by recursive application • In general there may be alternate ways to normalize to BCNF. The theory does not help discriminate among these.

  9. What about normalizing to 3NF • An dependency preserving algorithm for normalizing to 3NF exists • Extension of BCNF normalization approach • See section 11.2.3

  10. Summary of Database Design Theory • Constructing relation schemas is called DB design • Poor design can lead to insert, update and delete anomalies because of redundancy • Good design reduces redundancy by normalizing all relations to 3NF or BCNF • The theory of functional dependencies plays a major role in DB design

More Related