View by Category

Loading...

PPT – Normalization 1NF, 2NF, 3NF, BCNF PowerPoint presentation | free to view

The Adobe Flash plugin is needed to view this content

About This Presentation

Write a Comment

User Comments (0)

Transcript and Presenter's Notes

Normalization 1NF, 2NF, 3NF, BCNF

Outline

- Introduction
- Nonloss Decomposition and Functional Dependencies
- First, Second, and Third Normal Forms
- Dependency Preservation
- Boyce/Codd Normal Form
- A Note on Relation-Valued Attributes

Normalization

- Normalized and 1 NF are the same thing typically

normalized refers incorrectly to 3NF - Normalization helps control redundancy
- Normalization is reversible i.e. nonloss, or

information preserving - Six normal forms are discussed 1 through 5, and

Boyce-Codd Normal Form (BCNF), which is an

improvement on 3NF

Levels of Normalization

Nonloss Decomposition and Functional Dependencies

Overview

- Nonloss decomposition the normalization

procedure involves decomposing a given relvar

into other relvars, and moreover that the

decomposition is required to be reversible, so

that no information is lost in the process - Example
- Case a is an nonloss decomposition
- If you join SST and SC back together again, you

get back to S - Case b is a lossy decomposition
- Some additional spurious tuples will appear

Sample value for relvar S and two corresponding

decompositions

Nonloss Decomposition and Functional Dependencies

- Normalization uses a process of projection to

decompose relvars - Re-composition is a process of joins
- Heaths theorem Let RA, B, C be a relvar,

where A, B, and C are sets of attributes. If R

satisfies the FD A?B, then R is equal to the join

of its projection on A, B and A, C - The decomposition of relvar R into projections

R1Rn is nonloss if R the join of R1Rn

First, Second, and Third Normal Forms

- Throughout this section, we assume for simplicity

that each relvar has exactly one candidate key,

which we further assume is the primary key

Third Normal Form

- A relvar is in 3NF if and only if the nonkey

attributes are both mutually independent and

irreducibly dependent on the primary key - Two or more attributes are mutually independent

if none of them is FD on any combination of the

others. Such independence implies that each

attribute can be updated independently of the

rest - Example the parts relvar P is in 3NF
- A relvar is in 3NF if and only if, for all time,

each tuple consists of a primary key value that

identifies some entity, together with a set of

zero or more mutually independent attribute

values that describe that entity in some way

First Normal Form

- A relvar is in 1NF if and only if in every legal

value of that relvar, every tuple contains

exactly one value for each attribute - In this way, relvars are always in 1NF
- A relvar in 1NF may display functional

dependencies other than those emanating from the

primary key - Such non-primary-key dependencies promote a

miasma of update anomalies difficulties with

the update operations INSERT, DELETE, and UPDATE

Example

- FIRST S, STATUS, CITY, P, QTY PRIMARY KEY

S, P - One more constraint CITY ? STATUS
- A suppliers status is determined by the location

of that supplier (e.g. all London suppliers must

have a status of 20) - Non3NF diagram has arrows out of candidate keys

together with certain additional arrows

Sample Value for relvar FIRST

Note relvar FIRST violates both conditions a and

b in the preliminary 3NF definition the nonkey

attributes are not all mutually independent, and

they are not all irreducible dependent on the

primary key

The Suppliers-and-Parts Database (Sample Values)

S (suppliers) and P (parts) are entities, and SP

(shipment) is a relationship between S and P (a

relationship is a special case of an entity) In

RDB, entities and relationships are also

represented in the same uniform way

Update Anomalies For FIRST

- Focus on the FD S ? CITY
- INSERT we cannot insert the fact that a

particular supplier is located in a particular

city until that supplier supplies at least one

part (e.g. supplier S5 is located in Athens) - DELETE if we delete the sole FIRST tuple for a

particular supplier, we delete not only the

shipment concerning that supplier to a particular

part but also the information that the supplier

is located in a particular city (e.g. the S3

tuple) - UPDATE the city value for a given supplier

appears in FIRST many times, in general

Solution for FIRST

- The real problem is that relvar FIRST contains

too much information (shipment, and supplier) all

bundled together - If we delete a tuple, we delete too much
- The solution to this problem is to un-bundle
- Replace relvar FIRST by the two relvars
- SECOND S, STATUS, CITY
- SP S, P, QTY
- The decomposition of FIRST into SECOND and SP

eliminates the dependencies that were not

irreducible - The attribute CITY in FIRST did not describe the

entity described by the primary key instead, it

described the supplier involved in that shipment

Relvars SECOND and SP

Step Summarization

- The first step in the normalization procedure is

to take projections to eliminate

non-irreducible FD - Before normalization
- R A, B, C, D PRIMARY KEY A, B /

assume A ? D holds / - After normalization
- R1 A, D PRIMARY KEY A
- R2 A, B, C PRIMARY KEY A, B

FOREIGN KEY A REFERENCES R1

Second Normal Form

- A relvar is in 2NF if and only if it is in 1NF

and every nonkey attribute is irreducibly

dependent on the primary key - Assumes only one candidate key
- A relvar in 2NF is less susceptible to update

anomalies, but may still exhibit transitive

dependencies - Both attributes in a transitive dependency are

irreducibly implied by the primary key, and each

implies the other

Update Anomalies For SECOND

- Focus on the FD CITY ? STATUS
- S ? CITY CITY ? STATUS, then S ? STATUS
- INSERT we cannot insert the fact that a

particular city has a particular status until we

have some suppliers actually located in that city

(e.g. city ROME has a status of 50) - DELETE if we delete the sole SECOND tuple for a

particular city, we delete not only the supplier

concerned but also the information that that

city has that particular status (e.g. the S5

tuple) - UPDATE the status value for a given city appears

in SECOND many times, in general

Solution for SECOND

- The real problem is that relvar SECOND contains

too much information (supplier, and city) all

bundled together - If we delete a tuple, we delete too much
- The solution to this problem is to un-bundle
- Replace relvar SECOND by the two relvars
- SC S, CITY
- CS CITY, STATUS
- The decomposition of SECOND into SC and CS

eliminates the transitive dependencies - The attribute STATUS in SECOND did not describe

the entity described by the primary key instead,

it described the city involved in which that

supplier happened to be located

Relvars SC and CS

Step Summarization

- The second step in the normalization procedure is

to take projections to eliminate transitive FD - Before normalization
- R A, B, C PRIMARY KEY A / assume

B ? C holds / - After normalization
- R1 B, C PRIMARY KEY B
- R2 A, B PRIMARY KEY A FOREIGN KEY

B REFERENCES R1

Third Normal Form

- A relvar is in 3NF if and only if it is in 2NF

and every nonkey attribute is nontransitively

dependent on the primary key - Assumes only one candidate key

Dependency Preservation

- It is frequently the case that a given relvar can

be nonloss-decomposed in a variety of different

ways - Example
- SC S, CITY CS CITY, STATUS
- SC S, CITY SS S, STATUS
- Dependency preservation refers to a specific case

of nonloss decomposition, such that the

normalized relvars are independent of each other - Updates can be made to either one without regard

for others

Which decomposition is better ?

Dependency Preservation (Cont.)

- Projections R1 and R2 of a relvar R are

independent in the foregoing sense if and only if

both of the following are true - Every FD in R is a logical consequence of those

in R1 and R2 - The common attributes of R1 and R2 form a

candidate key for at least one of the pair - More on dependency preservation pp. 366-367

Boyce/Codd Normal Form

Overview

- Codds original definition of 3NF did not

adequately deal with the case of a relvar that - Had two or more candidate keys, such that
- The candidate keys were composite, and
- The are overlapped (i.e. had at least one

attribute in common) - Definition of Boyce/Codd normal form
- A relvar is in BCNF if and only if every

nontrivial, left-irreducible FD has a candidate

key as its determinant - A relvar is in BCNF if and only if every

determinant is a candidate key (informal

definition) - The only arrows in the FD diagram are arrows out

of candidate keys

Overview (Cont.)

- Examples
- FIRST and SECOND, which were not in 3NF, are not

in BCNF either - SP, SC, and CS, which were in 3NF, are also in

BCNF - Another example involving two disjoint

candidate keys - Suppose S and SNAME are both candidate keys, and

STATUS and CITY are mutually independent ? BCNF

BCNF Example I

- SSP S, SNAME, P, QTY
- Candidate key S, P , SNAME, P
- Non-BCNF determinants S and SNAME are not

candidate keys - Suffer the same kind of problems as FIRST and

SECOND did - SSP is in 3NF by the old definition (not the

simplified version) - That definition did not require an attribute to

be irreducibly dependent on each candidate key if

it was itself a component of some candidate key

of the relvar - The fact that SNAME is notirreducibly dependent

on S, P was ignored

Solution for SSP

- Break SSP into two projections
- SS S, SNAME
- SP S, P, QTY
- OR
- SS S, SNAME
- SP SNAME, P, QTY

S

P

QTY

SNAME

P

BCNF Example II

- SJT (student, subject, teacher)
- The meaning of an SJT tuple (s, j, t) is that a

student s is taught subject j by teacher t - Constraints
- For each subject, each student of that subject is

taught by only one teacher - Each teacher teaches only one subject (but one

subject is taught by several teachers)

BCNF Example II (Cont.)

- SJT (Cont.)
- Two overlapping candidate keys, S, J and S,

T - SJT is in 3NF and not BCNF
- Attribute T is a determinant but not a candidate

key - Solution for SJT break SJT into
- ST S, T and TJ T, J

BCNF Example II (Cont.)

- However, the two projections ST and TJ are not

independent - FD S, J ? T cannot be deduced from the FD T?J
- Example an attempt to insert a tuple for Smith

and Prof. Brown into ST must be rejected Why ??? - The twin objectives of (a) decomposing a relvar

into BCNF components, and (b) decomposing it into

independent components, can occasionally be in

conflict

BCNF Example III

- EXAM
- The meaning of an EXAM tuple (s, j, p) is that a

student s was examined in subject j and achieved

position p - Constraint no two students obtained the same

position in the same subject - Two overlapping candidate keys, S, J , and

J, P - They are the only determinants
- EXAM is in BCNF

Relation-Valued Attributes

- A relation may include attributes whose values

are relations - Traditionally this would be seen to violate 1NF,

which was held to prohibit repeating groups - Now they are theoretically sound, but in practice

you should avoid them because they have

complicated predicates

GET S for suppliers who supply part P1 ( SPQ

WHERE TUPLE P P (P1) ? PQ P ) S

GET P for parts supplied by supplier S1 ( SPQ

WHERE S S (S1) ) UNGROUP PQ ) P

Matters are worse for update operations

Conclusions

- The whole point of normalization theory is to try

to identify commonsense principles and formalize

them - In this way, we can mechanize those principles by

an algorithm - BCNF and indeed 5NF is always achievable

About PowerShow.com

PowerShow.com is a leading presentation/slideshow sharing website. Whether your application is business, how-to, education, medicine, school, church, sales, marketing, online training or just for fun, PowerShow.com is a great resource. And, best of all, most of its cool features are free and easy to use.

You can use PowerShow.com to find and download example online PowerPoint ppt presentations on just about any topic you can imagine so you can learn how to improve your own slides and presentations for free. Or use it to find and download high-quality how-to PowerPoint ppt presentations with illustrated or animated slides that will teach you how to do something new, also for free. Or use it to upload your own PowerPoint slides so you can share them with your teachers, class, students, bosses, employees, customers, potential investors or the world. Or use it to create really cool photo slideshows - with 2D and 3D transitions, animation, and your choice of music - that you can share with your Facebook friends or Google+ circles. That's all free as well!

For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!

You can use PowerShow.com to find and download example online PowerPoint ppt presentations on just about any topic you can imagine so you can learn how to improve your own slides and presentations for free. Or use it to find and download high-quality how-to PowerPoint ppt presentations with illustrated or animated slides that will teach you how to do something new, also for free. Or use it to upload your own PowerPoint slides so you can share them with your teachers, class, students, bosses, employees, customers, potential investors or the world. Or use it to create really cool photo slideshows - with 2D and 3D transitions, animation, and your choice of music - that you can share with your Facebook friends or Google+ circles. That's all free as well!

For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!

presentations for free. Or use it to find and download high-quality how-to PowerPoint ppt presentations with illustrated or animated slides that will teach you how to do something new, also for free. Or use it to upload your own PowerPoint slides so you can share them with your teachers, class, students, bosses, employees, customers, potential investors or the world. Or use it to create really cool photo slideshows - with 2D and 3D transitions, animation, and your choice of music - that you can share with your Facebook friends or Google+ circles. That's all free as well!

For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!

For a small fee you can get the industry's best online privacy or publicly promote your presentations and slide shows with top rankings. But aside from that it's free. We'll even convert your presentations and slide shows into the universal Flash format with all their original multimedia glory, including animation, 2D and 3D transition effects, embedded music or other audio, or even video embedded in slides. All for free. Most of the presentations and slideshows on PowerShow.com are free to view, many are even free to download. (You can choose whether to allow people to download your original PowerPoint presentations and photo slideshows for a fee or free or not at all.) Check out PowerShow.com today - for FREE. There is truly something for everyone!

Recommended

«

/ »

Page of

«

/ »

Promoted Presentations

Related Presentations

Page of

Page of

CrystalGraphics Sales Tel: (800) 394-0700 x 1 or Send an email

Home About Us Terms and Conditions Privacy Policy Contact Us Send Us Feedback

Copyright 2015 CrystalGraphics, Inc. — All rights Reserved. PowerShow.com is a trademark of CrystalGraphics, Inc.

Copyright 2015 CrystalGraphics, Inc. — All rights Reserved. PowerShow.com is a trademark of CrystalGraphics, Inc.

The PowerPoint PPT presentation: "Normalization 1NF, 2NF, 3NF, BCNF" is the property of its rightful owner.

Do you have PowerPoint slides to share? If so, share your PPT presentation slides online with PowerShow.com. It's FREE!

Committed to assisting Nctu University and other schools with their online training by sharing educational presentations for free