Relational Database Design: Guidelines for Good Relation Schemas, Slides of Computer Science

Download Relational Database Design: Guidelines for Good Relation Schemas and more Slides Computer Science in PDF only on Docsity!

NORMALIZATION

Database

design

methodologies

•^

Bottom

‐up

design

methodology

•^

Top

‐down

design

methodology

Top

‐down

design

methodology

•^

Also

called

design

by

analysis

•^

It^ starts

with

a^

number

of

groupings

of

attributes

into

relations

that

exist

together

naturally.

•^

It^

involves

designing

the

relations

based

on

external

knowledge

derived

from

an

existing

implementation

of^

files

or

forms

or

reports.

•^

The

relations

are

then

analyzed

individually

and

collectively,

leading

to

further

decomposition

until

all

desirable

properties

are

met.

Goodness

of

relation

schemas

•^

Logical

or

conceptual

level

•^

Implementation

or

storage

level

Implementation

or

storage

level

•^

How

the

tuples

in

a^

relation

are

stored

and

updated.

•^

This

level

applies

only

to

schemas

of

base

relations,

which

will

be

physically

stored

as

files.

Informal

design

guidelines

for

relation

schemas

•^

Informal

measures

of

quality

for

relation

schema

design

are:

-^ Semantics

of

the

attributes

-^ Reducing

the

redundant

values

in

tuples

-^ Reducing

the

null

values

in

tuples

-^ Disallowing

the

possibility

of

generating

spurious

tuples

Relation

schemas

violating

Guideline

ENAME

EMPID

DOB

ADDRESS

DNUMBER

DNAME

DMGRID

a)

EMP_DEPT

b) EMP_PROJ EMPID

PNUMBER

HOURS

ENAME

PNAME

PLOCATION

Reducing

the

redundant

values

in

tuples

•^

Mixing

attributes

of

multiple

entities

may

result

in

redundancy

of

data

in

the

database.

•^

Redundant

information

in^

tuples

causes

two

problems:^ – Wastage

of

storage

space

-^ Problem

of

Update

anomalies

Wastage

of

storage

space…………….

PNUMBER

PNAME

PLOCATION

DNUM

ENMAE

EMPID

DOB

ADDRESS

DNUMBER

EMP_PROJ EMPID^ EMPLOYEEPROJECT

PNUMBER

HOURS

ENAME

PNAME

PLOCATION

WORKS_ON

EMPID

PNUMBER

HOURS

REDUNDANCY

REDUNDANCY

Problem

of

Update

Anomalies

•^

Insertion

anomalies

•^

Deletion

anomalies

•^

Modification

anomalies

Insertion

anomalies………………………

ENAME

EMPID

DOB

ADDRESS

DNUMBER

DNAME

DMGRID

EMP_DEPT

•^

An

insertion

anomaly

.^ Until

the

new

faculty

member,

Dr.

Newsome,

is

assigned

to

teach

at

least

one

course,

his

details

cannot

be

recorded.