Normalization - Information Technology - Lecture Slides, Slides of Information Technology

Download Normalization - Information Technology - Lecture Slides and more Slides Information Technology in PDF only on Docsity!

NORMALIZATION

Contents

-^ The

purpose

of^ normailization

-^ Data

redundancy

and

Update

Anomalies

-^ Why

Normalization

-^ Functional

Dependencies

-^ The

Process

of^ Normalization

-^ First

Normal

Form

(1NF)

-^ Second

Normal

Form

(2NF)

-^ Third

Normal

Form

(3NF)

Normalization

-^ In^

relational

database

theory

-^ Normalization

is^ the

process

of^ restructuring

the^ logical

data

model

of^ a

database

to

eliminate

redundancy,

organize

data

efficiently

and^

reduce

repeating

data

and

to^ reduce

the

potential

for^ anomalies

during

data

operations.

Normalization

-^ Data

normalization

also

may

improve

data

consistency

and

simplify

future

extension

of

the^ logical

data

model.

-^ The

formal

classifications

used

for^ describing

a^ relational

database's

level

of^ normalization

are^ called

normal

forms

-^ Normal

Forms

is^ abbreviated

as^ NF

Why

Normalization….

-^ A^ non

‐normalized

database

can^

suffer

from

data

anomalies:

-^ A^ non

‐normalized

database

may

store

data

representing

a^ particular

referent

in^ multiple

locations.

An^ update

to^ such

data

in^ some

but

not^ all

of^ those

locations

results

in^ an

update

anomaly

,^ yielding

inconsistent

data.

A

normalized

database

prevents

such

an

anomaly

by^ storing

such

data

(i.e.

data

other

than

PRIMARY

KEYS)

in^ only

one

location.

Why

Normalization….

-^ A^ non

‐normalized

database

may^

have^

inappropriate

dependencies,

i.e.^ relationships

between

data^

with^ no

functional

dependencies.

Adding

data^

to^ such

a^ database

may^ require

first^ adding

the^ unrelated

dependency.

A

normalized

database

prevents

such^

insertion

anomalies

by

ensuring

that^ database

relations

mirror

functional

dependencies. • Similarly,

such^

dependencies

in^ non

‐normalized

databases

can^ hinder

deletion.

That^

is,^ deleting

data^

from^

such

databases

may^

require

deleting

data^

from^

the

inappropriate

dependency.

A^ normalized

database

prevents

such^ deletion

anomalies

by^ ensuring

that^ all

records

are

uniquely

identifiable

and^ contain

no^ extraneous

information.

Example

of^ Update

Anomalies

To^ insert

a^ new

staff^ with^ branchNo

B

into^ the

StaffBranch

relation;To^ delete

a^ tuple

that^ represents

the^ last

member

of^ staff

located

at^ a^ branch

B007;

To^ change

the^ address

of^ branch

B003.

staffNo

sName

position

salary

branchNo

bAddress

SL^

John White

Manager

30000

B

22 Deer Rd, London

SG^

Ann Beech

Assistant

12000

B

163 Main St,Glasgow

SG^

David Ford

Supervisor

18000

B

163 Main St,Glasgow

SA^

Mary Howe

Assistant

9000

B

16 Argyll St, Aberdeen

SG^

Susan Brand

Manager

24000

B

163 Main St,Glasgow

SL^

Julie Lee

Assistant

9000

B

22 Deer Rd, London

StaffBranch Figure 1 StraffBranch relation

Example

of^ Update

Anomalies

(2)

staffNo

sName

position

salary

branceNo

SL^

John White

Manager

30000

B

SG^

Ann Beech

Assistant

12000

B

SG^

David Ford

Supervisor

18000

B

SA^

Mary Howe

Assistant

9000

B

SG^

Susan Brand

Manager

24000

B

SL^

Julie Lee

Assistant

9000

B

branceNo

bAddress B^

22 Deer Rd, London B^

16 Argyll St, Aberdeen B^

163 Main St,Glasgow Staff Branch^ Figure 2 Straff and Branch relations

Normalized

Databases

-^ Redundancy

can^

be^ solved

by^ decomposing

the^ tables.

However

certain

new

problems

are

caused

by^ decomposition.

-^ Normalization

helps

us^ to

make

a^ conscious

decision

to^ avoid

redundancy

keeping

the^

pros

and^

cons

in^ mind.

-^ One

can^

only

describe

a^ database

as^ having

a

normal

form

if^ the

relationships

between

quantities

have

been

rigorously

defined.

Normalized

Databases

-^ It^ is

possible

to^ use

set^ theory

to^ express

this

knowledge

once

a^ problem

domain

has^

been

fully

understood,

but^

most

database

designers

model

the^

relationships

in^ terms

of^ an

"idealized

schema".

(The

mathematical

support

came

back

into

play

in^ proofs

regarding

the^

process

of^ transforming

from

one^

form

to^ another.)

-^ The

transformation

of^ conceptual

model

to

computer

representation

format

is^ known

as

Normalization.

Trival functional

dependency

means

that^ the

right‐

hand

side^ is

a^ subset

(^ not^

necessarily

a^ proper

subset)

of^ the

left‐

hand^

side.

Functional

Dependencies

For^ example:

(See^ Figure

staffNo,

sName

^ sName staffNo,

sName

^ staffNo

They^ do

not^ provide

any^ additional

information

about^

possible

integrity

constraints

on^ the

values

held^ by

these^ attributes.

We^ are

normally

more^ interested

in^ nontrivial

dependencies

because

they

represent

integrity

constraints

for^ the

relation

.

Functional

Dependencies

Main^

characteristics

of^ functional

dependencies

in^ normalization

-^ Have

a^ one

‐to‐one

relationship

between

attribute(s)

on^ the

left‐

and^ right

‐^ hand

side^ of

a^ dependency;

-^ hold

for^ all

time;

-^ are

nontrivial.

Functional

Dependencies

Inference Rules A^ set^ of^ all^ functional

dependencies

that^ are

implied

by^ a

givenset^ of

functional

dependencies

X^ is^ called

closure

of^ X,^

written

+^ X.^ A^ set

of^ inference

rule^ is

needed

to^ compute

+^ X from

X.

Armstrong’s

axioms

1.^ Relfexivity:

If^ B^ is^

a^ subset

of^ A,^

them^

A^ ^ B

2.^ Augmentation:

If^ A

^ B,^

then^ A,

C^ ^ B

3.^ Transitivity:

If^ A^ 

B^ and

B^ ^ C,

then^

A^ C

4.^ Self

‐determination:

A^ ^ A

5.^ Decomposition:

If^ A

^ B,C

then

A^ ^

B^ and

A^ C

6.^ Union:

If^ A^ 

B^ and

A^ ^ C,

then^

A^ B,C

7.^ Composition:

If

A^ ^

B^ and

C^ ^ D,

then^

A,C

B,

Functional

Dependencies

Minial

Sets

of^ Functional

Dependencies

A^ set^ of^ functional

dependencies

X^ is^ minimal

if^ it^ satisfies

the^ following

condition:

-^ Every

dependency

in^ X^ has

a^ single

attribute

on^ its

right‐hand

side

-^ We

cannot

replace

any^ dependency

A^ ^ B

in^ X^ with

dependency

C^ B,

where

C^ is^ a

proper

subset

of^ A,^

and

still^ have

a^ set^

of^ dependencies

that^ is

equivalent

to^ X.

-^ We

cannot

remove

any^ dependency

from^

X^ and

still

have^ a

set^ of

dependencies

that^ is

equivalent

to^ X.