- By
**Anita** - Follow User

- 215 Views
- Uploaded on

Download Presentation
## PowerPoint Slideshow about 'Schedule' - Anita

**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

Schedule

- Today:
- Normal Forms.
- Section 3.6.
- Next
- Relational Algebra. Read chapter 5 to page 199
- After that
- SQL Queries.
- Read Sections 6.1-6.2.

J. Holliday - coen 178

Normalization

Goal = BCNF = Boyce-Codd Normal Form =

all FD’s follow from the fact “key everything.”

- Formally, R is in BCNF if for every nontrivial FD for R, if XA, then X is a superkey.
- “Nontrivial” = right-side attribute not in left side.

Why?

1. Guarantees no redundancy due to FD’s.

2. Guarantees no update anomalies = one occurrence of a fact is updated, not all.

3. Guarantees no deletion anomalies = valid fact is lost when tuple is deleted.

J. Holliday - coen 178

Example of Problems

Drinkers(name, addr, beerLiked, manf, favoriteBeer)

FD’s:

1. name addr

2. name favoriteBeer

3. beerLiked manf

- ???’s are redundant, since we can figure them out from the FD’s.
- Update anomalies: If Bill transfers to UC Berkeley, will we remember to change addr in each of his tuples?
- Deletion anomalies: If nobody likes Bud, we lose track of Bud’s manufacturer.

J. Holliday - coen 178

Each of the 3 given FD’s is a BCNF violation:

- Key = {name, beerLiked}
- Each of the given FD’s has a left side that is a proper subset of the key.

Another Example

Beers(name, manf, manfAddr).

- FD’s: name manf, manf manfAddr
- Only key is name.
- ManfmanfAddr violates BCNF with a left side not a super key.

J. Holliday - coen 178

Decomposition to Reach BCNF

Given: relation R, and FD’s F.

If there is a non-trivial FD in F, XB, and X is not a superkey, then R is not in BCNF.

Suppose relation R has BCNF violation XB. We can decompose R into two or more relations so that each of the relations will be in BCNF.

J. Holliday - coen 178

1. Compute X+.

- Cannot be all attributes – why?

2. Decompose R into X+ and (R–X+) X.

3. Find the FD’s for the decomposed relations.

- Project the FD’s from F = calculate all consequents of F that involve only attributes from X+ or only from (RX+) X.

R

X+

X

J. Holliday - coen 178

Example 1

R= (A,B,C,D) F = {A BC, C D}

The key is A (why?)

The functional dependency C D violates BCNF (why?)

Decomposition:

1. Compute X+. C+ = CD

2. Decompose R: R1 = X+ and R2 = (R–X+) X.

R1 = CD R2 = ABC

3. Find the FD’s for the decomposed relations. (why?)

J. Holliday - coen 178

Example 2

R = Drinkers(name, addr, beerLiked,manf,favoriteBeer)

F =

1. name addr

2. name favoriteBeer

3. beerLiked manf

Pick BCNF violation name addr

- Close left side: name+ = name addr favoriteBeer.
- Decomposed relations:

Drinkers1(name, addr, favoriteBeer)

Drinkers2(name, beerLiked, manf)

- Projected FD’s (skipping a lot of work):
- For Drinkers1: name addr and name favoriteBeer.
- For Drinkers2: beerLiked manf.

J. Holliday - coen 178

(Repeating)

- Decomposed relations:

Drinkers1(name, addr, favoriteBeer)

Drinkers2(name, beerLiked, manf)

- Projected FD’s:
- For Drinkers1: name addr and name favoriteBeer.
- For Drinkers2: beerLiked manf.
- BCNF violations?
- For Drinkers1, name is key and all left sides of FD’s are superkeys.
- For Drinkers2, {name, beerLiked} is the key, and beerLiked manf violates BCNF.

J. Holliday - coen 178

Decompose Drinkers2

- First set of decomposed relations:

Drinkers1(name, addr, favoriteBeer)

Drinkers2(name, beerLiked, manf)

- Close beerLiked+ = beerLiked manf
- Decompose Drinkers2 into:

Drinkers3(beersLiked, manf)

Drinkers4(name, beersLiked)

- Resulting relations are all in BCNF:

Drinkers1(name, addr, favoriteBeer)

Drinkers3(beerLiked, manf)

Drinkers4(name, beerLiked)

J. Holliday - coen 178

Why Decompose This Way?

- Eliminate unnecessary redundancy (update and delete anomalies)
- Loss-less join decomposition (recover original information with join on equality)
- Dependency preserving (efficient checking of constraints)

J. Holliday - coen 178

Lossless Join Decomposition

If decomposition of a schema to avoid redundant info is not done carefully, we can lose information and generate extra tuples when we try to reconstruct the information from the original table. Consider the decomposition of

emp-dept (ename, ssn, bdate, address, dnumber, dname, dmgrssn)

into

emp-mgr (ename, ssn, bdate, address, dmgrssn)

dept (dnumber, dname, dmgrssn)

This decomposition solves the redundant info problem. However, there can be problems joining the tables emp-mgr and dept.

J. Holliday - coen 178

John

222-33-4444

2/12/78

123 4th Street

5

CS

432-32-1234

Sue

432-32-1234

3/22/71

500 5th Street

1

EX

119-99-7883

Mike

111-22-3333

5/25/75

23 A Street

3

TS

432-32-1234

Bob

119-99-7883

9/21/62

568 Main Street

1

EX

119-99-7883

John

222-33-4444

2/12/78

123 4th Street

432-32-1234

Sue

432-32-1234

3/22/71

500 5th Street

119-99-7883

Mike

111-22-3333

5/25/75

23 A Street

432-32-1234

Bob

119-99-7883

9/21/62

568 Main Street

119-99-7883

1

EX

119-99-7883

3

TS

432-32-1234

5

CS

432-32-1234

Lossless Join Decompositionemp-mgr

dept

J. Holliday - coen 178

222-33-4444

2/12/78

123 4th Street

432-32-1234

3

TS

John

222-33-4444

2/12/78

123 4th Street

432-32-1234

5

CS

Sue

432-32-1234

3/22/71

500 5th Street

119-99-7883

1

EX

Mike

111-22-3333

5/25/75

23 A Street

432-32-1234

3

TS

Mike

111-22-3333

5/25/75

23 A Street

432-32-1234

5

CS

Bob

119-99-7883

9/21/62

568 Main Street

119-99-7883

1

EX

If we try to recover the original info by doing a join of emp-mgr and dept, we get:

emp-mgr join dept

There are some extra rows here!! Another way of looking at it is that we lost some information when we decomposed emp-dept into emp-mgr and dept. Why did this happen? We have the functional dependency(dnumber dname dmgrssn), but not (dmgrssn dnumber) and we joined on dmgrssn.

J. Holliday - coen 178

Lossless Join Decomposition

In a lossless join decomposition into R1 and R2, at least one of the following dependencies is in F+

R1 R2 R1 or R1 R2 R2

Example: R = (A, B, C) F = { A B, B C}

Decomposition is: R1 = (A, B) R2 = (B, C)

This is a lossless join decomposition because R1 R2 = {B} and B BC, so R1 R2 R2

What about the decomposition R1 = (A, C) R2 = (B, C) ?

J. Holliday - coen 178

Dependency Preserving Decomposition

This property ensures that checking updates for violation of FD’s is efficient.

Dependency preservation: Let Fi be the set of dependencies in F+ that includes only attributes in Ri. The decomposition is dependency preserving if

( Fi )+ = F+

that is, for a 2 relation decomposition (F1 F2)+ = F+

Example: R = (A, B, C) F = { A B, B C}

R1 = (A, B) R2 = (B, C) This is dependency preserving.

What about the decomposition R1 = (A, C) R2 = (B, C) ?

J. Holliday - coen 178

3NF

One FD structure causes problems:

- If you decompose, you can’t check all the FD’s only in the decomposed relations.
- If you don’t decompose, you violate BCNF.

Structure: ABC and CB.

- Example 1: title city theatre and theatre city.
- Example 2: street city zip, zip city.

Keys: {A, B} and {A, C}, but CB has a left side that is not a superkey.

- Decompose into BC and AC.
- But you can’t check the FD ABC in only these relations.

J. Holliday - coen 178

“Elegant” Workaround

Define the problem away.

- A relation R is in 3NF iff (if and only if)for every nontrivial FD XA, either:

1. X is a superkey, or

2. A is prime = member of at least one key.

- Thus, the canonical problem goes away: you don’t have to decompose because all attributes are prime.

J. Holliday - coen 178

What 3NF Gives You

There are two important properties of a decomposition:

- We should be able to recover from the decomposed relations the data of the original.
- Recovery involves projection and join.
- We should be able to check that the FD’s for the original relation are satisfied by checking the projections of those FD’s in the decomposed relations.
- You can always decompose into BCNF and satisfy (1).
- We can decompose into 3NF and satisfy both (1) and (2).
- But it is not always possible to decompose into BNCF and get both (1) and (2).
- Street-city-zip is an example of this point.

J. Holliday - coen 178

BCNF and 3NF

- BCNF: Whenever a non-trivial functional dependency XA holds in R, then X is a superkey of R.
- 3NF: Whenever a non-trivial functional dependency XA holds in R, then X is a superkey of R OR

each attribute of A is a member of a candidate key (prime).

J. Holliday - coen 178

Exercise

Consider the schema and 2 sets of FD’s F and E:

emp-dept (ename, ssn, bdate, address, dnumber, dname, dmgrssn)

F ={ ssn ename bdate address dnumber,

dnumber dname dmgrssn

}

E = { ssn ename address dnumber,

ssn dname bdate, dnumber dname dmgrssn

}

Are F and E equivalent?

J. Holliday - coen 178

3NF Decomposition

Find the canonical form for F

A canonical cover of a set of dependencies, F, has the following properties:

- · No functional dependency contains an extraneous attribute. That is, an attribute that can be removed from the dependency without changing the closure of F.
- · Each left side of a functional dependency in F is unique.

J. Holliday - coen 178

3NF Decomposition Algorithm

1.Calculate the canonical cover of F

2.set j = 0

3.for each FD A B in Fc, do

if none of current schemas contain AB then

j = j+1

Rj = (A B)

4. if none of the schemas in the result contains a candidate key for the original R, then: j = j + 1 and Rj = (any candidate key)

J. Holliday - coen 178

3NF Example

Example: R = (A, B, C, D, E)

F = {A BC, C DE, DE A)

Soln: Candidate keys are: A , C, and DE

F is in canonical form.

R1 = (A, B, C), R2 = (C, D, E), R3 = (A, D, E)

No natural join produces spurious tuples, so the decomposition is lossless. Dependencies are preserved. All relations are in 3NF.

J. Holliday - coen 178

Example - BCNF

R = (b-name, b-city, assets, c-name, loan#, amount)

F = {b-name b-city assets, loan# amount b-name}

Primary key = {loan#, c-name}

** R is not in BCNF

"b-name b-city assets" is a non-trivial FD that holds on R and "b-name R" is not in F+ (that is, b-name is not a super key).

Split R into R1 and R2

R1 = (b-name, b-city, assets)

R2 = (b-name, c-name, loan#, amount)

J. Holliday - coen 178

Continued

R1 = (b-name, b-city, assets)

R2 = (b-name, c-name, loan#, amount)

**Now R1 is in BCNF, but R2 is not (why?) So, we split R2 into R3 and R4

R3 = (b-name, loan#, amount)

R4 = (c-name, loan#)

The final decomposition is R1, R3, R4

J. Holliday - coen 178

Exercise

- Find the keys.
- How should this be decomposed?

R = (A, B, C, D, E)

F = {A BC, C DE, DE A)

J. Holliday - coen 178

Answers

R = (A, B, C, D, E)

F = {A BC, C DE, DE A)

Keys are: A , C, and DE

R is already in BCNF.

J. Holliday - coen 178

Download Presentation

Connecting to Server..