lossless decomposition 2 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Lossless Decomposition (2) PowerPoint Presentation
Download Presentation
Lossless Decomposition (2)

Loading in 2 Seconds...

play fullscreen
1 / 63

Lossless Decomposition (2) - PowerPoint PPT Presentation


  • 92 Views
  • Uploaded on

Lecture 17. CS157A. Lossless Decomposition (2). Prof. Sin-Min Lee Department of Computer Science San Jose State University. Normal form and normalization • A normal form is a property of a relational database. • When a relation is non-normalized (that is, does not satisfy a normal

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Lossless Decomposition (2)' - nathan


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
lossless decomposition 2

Lecture 17

CS157A

Lossless Decomposition (2)

Prof. Sin-Min Lee

Department of Computer Science

San Jose State University

slide2

Normal form and normalization

• A normal form is a property of a relational database.

• When a relation is non-normalized (that is, does not satisfy a normal

form), then it presents redundancies and produces undesirable

behavior during update operations.

• This principle can be used to carry out quality analysis and constitutes

a useful tool for database design.

• Normalization is a procedure that allows the non-normalized

schemas to be transformed into new schemas for which the

satisfaction of a normal form is guaranteed.

slide3

Example of a relation with anomalies

Employee Salary Project Budget Function

Brown 20 Mars 2 technician

Green 35 Jupiter 15 designer

Green 35 Venus 15 designer

Hoskins 55 Venus 15 manager

Hoskins 55 Jupiter 15 consultant

Hoskins 55 Mars 2 consultant

Moore 48 Mars 2 manager

Moore 48 Venus 15 designer

Kemp 48 Venus 15 designer

Kemp 48 Jupiter 15 manager

The key is made up of the attributes Employee and Project

normal forms
Normal Forms
  • Each normal form is a set of conditions on a schema that guarantees certain properties (relating to redundancy and update anomalies)
  • First normal form (1NF) is the same as the definition of relational model (relations = sets of tuples; each tuple = sequence of atomic values)
  • Second normal form (2NF) – a research lab accident; has no practical or theoretical value – won’t discuss
  • The two commonly used normal forms are third normal form (3NF) and Boyce-Codd normal form (BCNF)
slide5

Anomalies in the example relation

• The value of the salary of each employee is repeated in all the tuples

relating to it: therefore there is a redundancy.

• If the salary of an employee changes, we have to modify the value in

all the corresponding tuples. This problem is known as the update

anomaly.

• If an employee stops working on all the projects but does not leave

the company, all the corresponding tuples are deleted and so, even

the basic information, name and salary is lost. This problem is known

as the deletion anomaly.

• If we have information on a new employee, we cannot insert it until

the employee is assigned to a project. This is known as the insertion

anomaly.

why these undesirable phenomena
Why these undesirable phenomena?

• Intuitive explanation: we have used a single relation to represent

items of information of different types.

• In particular, the following independent real-world concepts are

represented in the relation:

– employees with their salaries,

– projects with their budgets,

– participation of the employees in the projects with their functions.

• To systematically study the principles introduced informally, it is

necessary to use a specific notion: the functional dependency.

functional dependencies

Functional dependencies

• Given a relation r on a schema R(X) and two non-empty subsets Y

and Z of the attributes X, we say that there is a functional dependency

on r between Y and Z, if, for each pair of tuples t 1 and t 2 of r having

the same values on the attributes Y, t 1 and t 2 also have the same

values of the Z attributes.

• A functional dependency between the attributes Y and Z is indicated

by the notation Y -> Z

functional dependencies in the example schema

Functional dependencies in the example schema

• Employee -> Salary

the salary of each employee is unique and thus each time a certain

employee appears in a tuple, the value of his or her salary always

remains the same.

• Project -> Budget

the budget of each project is unique and thus each time a certain

project appears in a tuple, the value of its budget always remains

the same.

non trivial functional dependencies

Non-trivial functional dependencies

• We then say that a functional dependency Y-> Z is non-trivial if no

attribute in Z appears among the attributes of Y.

– Employee -> Salary is a non-trivial functional dependency

– Employee Project -> Project is a trivial functional dependency

anomalies and functional dependencies

Anomalies and functional dependencies

• In our example, the two properties causing anomalies correspond

exactly to attributes involved in functional dependencies:

– the property ‘ the salary of each employee is unique and depends

only on the employee’ corresponds to the functional dependency

Employee -> Salary;

– the property ‘ the budget of each project is unique and depends

only on the project’ corresponds to the functional dependency

Project -> Budget.

• Moreover, the following property can be formalized by means of a

functional dependency:

– the property ‘ in each project, each of the employees involved can

carry out only one function’ corresponds to the functional

dependency Employee Project -> Function.

dependencies generating anomalies

Dependencies generating anomalies

• The first two dependencies generate undesirable redundancies and

anomalies.

• The third dependency however never generates redundancies

because, having Employee and Project as a key, the relation cannot

contain two tuples with the same values of these attributes.

• The difference is that Employee Project is a key of the relation.

slide13
BCNF
  • Definition: A relation schema R is in BCNF if for every FD X Y associated with R either
    • Y  X(i.e., the FD is trivial) or
    • X is a superkey of R
  • Example: Person1(SSN, Name, Address)
    • The only FD is SSN Name, Address
    • Since SSN is a key, Person1 is in BCNF
non bcnf examples
(non) BCNF Examples
  • Person (SSN, Name, Address, Hobby)
    • The FD SSN  Name, Address does not satisfy requirements of BCNF
      • since the key is (SSN, Hobby)
  • HasAccount (AcctNum, ClientId, OfficeId)
    • The FD AcctNum OfficeId does not satisfy BCNF requirements
      • since keys are (ClientId, OfficeId) and (AcctNum, ClientId); not AcctNum.
redundancy
Redundancy
  • Suppose R has a FD A  B, and A is not a superkey. If an instance has 2 rows with same value in A, they must also have same value in B (=> redundancy, if the A-value repeats twice)
  • If A is a superkey, there cannot be two rows with same value of A
    • Hence, BCNF eliminates redundancy

SSN  Name, Address

SSN Name Address Hobby

1111 Joe 123 Main stamps

1111 Joe 123 Main coins

redundancy

third normal form
Third Normal Form
  • A relational schema R is in 3NF if for every FD X Y associated with R either:
    • Y  X(i.e., the FD is trivial); or
    • X is a superkey of R; or
    • Every A Y is part of some key of R
  • 3NF is weaker than BCNF (every schema that is in BCNF is also in 3NF)

BCNF conditions

3nf example
3NF Example
  • HasAccount (AcctNum, ClientId, OfficeId)
    • ClientId, OfficeId AcctNum
      • OK since LHS contains a key
    • AcctNum OfficeId
      • OK since RHS is part of a key
  • HasAccount is in 3NF but it might still contain redundant information due to AcctNum  OfficeId (which is not allowed by BCNF)
3nf non example
3NF (Non) Example
  • Person (SSN, Name, Address, Hobby)
    • (SSN, Hobby) is the only key.
    • SSN Name violates 3NF conditions since Name is not part of a key and SSN is not a superkey
decompositions
Decompositions
  • Goal: Eliminate redundancy by decomposing a relation into several relations in a higher normal form
  • Decomposition must be lossless: it must be possible to reconstruct the original relation from the relations in the decomposition
      • We will see why
decomposition
Decomposition
  • Schema R = (R, F)
    • R is set a of attributes
    • F is a set of functional dependencies over R
      • Each key is described by a FD
  • The decompositionof schemaR is a collection of schemas Ri = (Ri, Fi) where
    • R = i Rifor all i (no new attributes)
    • Fi is a set of functional dependences involving only attributes of Ri
    • Fentails Fi for all i (no new FDs)
  • The decomposition of an instance, r, of R is a set of relations ri = Ri(r) for all i
example decomposition
Example Decomposition

Schema (R, F) where

R = {SSN, Name, Address, Hobby}

F = {SSN Name, Address}

can be decomposed into

R1 = {SSN, Name, Address}

F1 = {SSN  Name, Address}

and

R2 = {SSN, Hobby}

F2 = { }

lossless schema decomposition
Lossless Schema Decomposition
  • A decomposition should not lose information
  • A decomposition (R1,…,Rn) of a schema, R, is losslessif every valid instance, r, of R can be reconstructed from its components:
  • where each ri = Ri(r)

r2

r = r1

……

rn

lossy decomposition
Lossy Decomposition

The following is always the case (Think why?):

rr1

r2

rn

...

But the following is not always true:

rr1

r2

rn

...

Example:

r

r1

r2

SSN Name AddressSSN NameName Address

1111 Joe 1 Pine 1111 Joe Joe 1 Pine

2222 Alice 2 Oak 2222 Alice Alice 2 Oak

3333 Alice 3 Pine 3333 Alice Alice 3 Pine

The tuples(2222, Alice, 3 Pine) and (3333, Alice, 2 Oak)are in the join,

but not in the original

lossy decompositions what is actually lost
Lossy Decompositions: What is Actually Lost?
  • In the previous example, the tuples(2222, Alice, 3 Pine) and (3333, Alice, 2 Oak) were gained, not lost!
    • Why do we say that the decomposition was lossy?
  • What was lost is information:
    • That 2222 lives at 2 Oak: In the decomposition, 2222 can live at either 2 Oak or 3 Pine
    • That 3333 lives at 3 Pine: In the decomposition, 3333 can live at either 2 Oak or 3 Pine
example
Example

Schema (R, F) where

R = {SSN, Name, Address, Hobby}

F = {SSN  Name, Address}

can be decomposed into

R1 = {SSN, Name, Address}

F1 = {SSN  Name, Address}

and

R2 = {SSN, Hobby}

F2 = { }

Since R1 R2 = SSN and SSN  R1 the

decomposition is lossless

intuition behind the test for losslessness
Intuition Behind the Test for Losslessness
  • Suppose R1 R2  R2 . Then a row of r1 can combine with exactly one row of r2 in the natural join (since in r2 a particular set of values for the attributes in R1 R2 defines a unique row)

R1  R2 R1  R2

…………. a a ………...

………… a b ………….

………… b c ………….

………… c

r1r2

proof of lossless condition
Proof of Lossless Condition
  • rr1

r2 – this is true for any decomposition

r2

  • rr1

If R1 R2 R2 then

card (r1

r2) = card (r1)

(since each row of r1 joins with exactly one row of r2)

But card (r)  card (r1) (sincer1 is a projection of r)

and therefore card(r)  card (r1 r2)

r2

Hence r = r1

dependency preservation
Dependency Preservation
  • Consider a decomposition of R = (R, F) into R1 = (R1, F1) and R2 = (R2, F2)
    • An FD X Y of F+is in Fi iff X  Y  Ri
    • An FD, f F+may be in neither F1, nor F2, nor even (F1F2)+
      • Checking that f is true in r1 or r2 is (relatively) easy
      • Checking f in r1 r2is harder – requires a join
      • Ideally: want to check FDs locally, in r1 and r2, and have a guarantee that every f F holds in r1 r2
  • The decomposition is dependency preserving iff the sets F and F1F2 are equivalent: F+ = (F1F2)+
    • Then checking all FDs in F, as r1 and r2are updated, can be done by checking F1 in r1 and F2 in r2
dependency preservation1
Dependency Preservation
  • If f is an FD in F, but f is not in F1F2, there are two possibilities:
    • f  (F1F2)+
      • If the constraints in F1 and F2 are maintained, f will be maintained automatically.
    • f(F1F2)+
      • f can be checked only by first taking the join of r1 and r2. This is costly.
example1
Example

Schema (R, F) where

R = {SSN, Name, Address, Hobby}

F = {SSN  Name, Address}

can be decomposed into

R1 = {SSN, Name, Address}

F1 = {SSN  Name, Address}

and

R2 = {SSN, Hobby}

F2 = { }

Since F = F1F2the decomposition is

dependency preserving

example2
Example
  • Schema: (ABC; F) , F = {A  B, BC, CB}
  • Decomposition:
    • (AC, F1), F1 = {AC}
      • Note: AC  F, but in F+
    • (BC,F2), F2 = {BC, CB}
  • A  B (F1  F2), but A  B (F1  F2)+.
    • So F+ = (F1  F2)+ and thusthe decompositions is still dependency preserving
bcnf decomposition algorithm
BCNF Decomposition Algorithm

Input: R = (R; F)

Decomp := R

while there is S = (S; F’)  Decomp and S not in BCNFdo

FindX Y  F’thatviolatesBCNF // X isn’t a superkey in S

Replace S in Decomp with S1= (XY; F1), S2 = (S - (Y - X); F2)

//F1= all FDs of F’ involving only attributes of XY

// F2 = all FDs of F’ involving only attributes of S - (Y - X)

end

return Decomp

simple example
Simple Example
  • HasAccount :

(ClientId, OfficeId, AcctNum)

ClientId,OfficeId AcctNum

AcctNum  OfficeId

  • Decompose using AcctNum  OfficeId :

(OfficeId, AcctNum)

(ClientId , AcctNum)

BCNF (only trivial FDs)

BCNF: AcctNum is key

FD: AcctNum OfficeId

a larger example
A Larger Example

Given: R = (R; F) where R = ABCDEGHK and

F = {ABH C, A DE, BGH K, K ADH, BH GE}

step 1: Find a FD that violates BCNF

Not ABH  C since (ABH)+ includes all attributes

(BH is a key)

A  DE violates BCNF since A is not a superkey (A+ =ADE)

step 2: Split R into:

R1 = (ADE, F1={A DE })

R2 = (ABCGHK; F1={ABHC, BGHK, KAH, BHG})

Note 1: R1 is in BCNF

Note 2: Decomposition is lossless since A is a key of R1.

Note 3: FDs K  D and BH  E are not in F1 or F2. But

both can be derived from F1F2

(E.g., KA and A D implies K D)

Hence, decomposition isdependency preserving.

example con t
Example (con’t)

Given: R2 = (ABCGHK; {ABHC, BGHK, KAH, BHG})

step 1: Find a FD that violates BCNF.

Not ABH  C or BGH  K, since BH is a key of R2

K AH violates BCNF since K is not a superkey (K+ =AH)

step 2: Split R2 into:

R21 = (KAH, F21={K  AH})

R22 = (BCGK; F22={})

Note 1: Both R21 and R22are in BCNF.

Note 2: The decomposition is lossless (since K is a key of R21)

Note 3: FDs ABH C, BGH K, BH G are not in F21

or F22 , andthey can’t be derived from F1 F21F22 .

Hence the decomposition is not dependency-preserving

properties of bcnf decomposition algorithm
Properties of BCNF Decomposition Algorithm

Let X  Y violate BCNF inR = (R,F)and R1 = (R1,F1),

R2 = (R2,F2) is the resulting decomposition. Then:

  • There are fewer violations of BCNF in R1and R2 than there were in R
    • X  Y implies X is a key of R1
    • Hence X  Y  F1does not violate BCNF in R1 and, since X  Y F2, does not violate BCNF in R2 either
    • Suppose f is X’ Y’ and f Fdoesn’t violate BCNF in R. If f F1 or F2it does not violate BCNF in R1or R2 either since X’is a superkey of R and hence also of R1and R2 .
properties of bcnf decomposition algorithm1
Properties of BCNF Decomposition Algorithm
  • A BCNF decomposition is not necessarily dependency preserving
  • But always lossless:

since R1 R2 = X, X  Y, and R1 = XY

  • BCNF+lossless+dependency preserving is sometimes unachievable (recall HasAccount)
boyce codd normal form bcnf

Boyce–Codd Normal Form (BCNF)

• A relation r is in Boyce–Codd normal form if for every (non-trivial)

functional dependency X -> Y defined on it, X contains a key K of r.

That is, X is a superkey for r.

• Anomalies and redundancies, as discussed above, do not appear in

databases with relations in Boyce–Codd normal form, because the

independent pieces of information are separate, one per relation.

decomposition into boyce codd normal form

Decomposition into Boyce–Codd normal form

• Given a relation that does not satisfy Boyce–Codd normal form, we

can often replace it with one or more normalized relations using a

process called normalization.

• We can eliminate redundancies and anomalies for the example

relation if we replace it with the three relations, obtained by

projections on the sets of attributes corresponding to the three

functional dependencies.

• The keys of the relations we obtain are the left hand side of a

functional dependency: the satisfaction of the Boyce–Codd normal

form is therefore guaranteed.