normalisation l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Normalisation PowerPoint Presentation
Download Presentation
Normalisation

Loading in 2 Seconds...

play fullscreen
1 / 25

Normalisation - PowerPoint PPT Presentation


  • 134 Views
  • Uploaded on

Normalisation. The theory of Relational Database Design. Introduction. Normalisation is a theory for designing relational schema that “make sense” and work well. Well-normalised tables avoid redundancy and thereby reduce inconsistencies. Redundancy is unnecessary duplication.

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 'Normalisation' - Albert_Lan


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
normalisation

Normalisation

The theory of Relational Database Design

introduction
Introduction
  • Normalisation is a theory for designing relational schema that “make sense” and work well.
  • Well-normalised tables avoid redundancy and thereby reduce inconsistencies.
  • Redundancy is unnecessary duplication.
  • In well-normalised DBs semantic dependencies are maintained by primary key uniqueness.
goals of normalisation
Goals of Normalisation
  • Eliminate certain kinds of redundancy
  • avoid certain update anomalies
  • good reresentation of real world
  • simplify enforcement of DB integrity
update anomalies
Update anomalies
  • Undesirable side-effects that occur when performaing insertion, modification or deletion operations on badly designed relational DBs.

Dept Name

...

SSN

987

654

333

321

678

467

Name

J Smith

M Burke

A Dolan

K Doyle

O O’Neill

R McKay

Dept

1

2

1

1

3

2

DeptMgr

321

467

321

321

678

467

Representing Department info in the Employee table causes problems.

sample anomalies
Sample anomalies
  • Modification -
    • when the manager of a dept changes we have to change many values.
    • If we are not careful the DB will contain inconsistencies.
    • There is no easy way to get the DB to ensure that a department has only one manager and only one name.
anomalies continued
Anomalies continued
  • Deletion -
    • if O O’Neill leaves we delete his tuple and lose
      • the fact that there is a department 3
      • the name of dept 3
      • who is the manager of dept. 3
  • Insertion
    • how would we create a new department before any employees are assigned to it ?
better design
Better design
  • Separate entities are represented in separate tables.

Dept Name

...

SSN

987

654

333

321

678

467

Name

J Smith

M Burke

A Dolan

K Doyle

O O’Neill

R McKay

Dept

1

2

1

1

3

2

Dept

1

2

3

DeptMgr

321

467

678

Note that mapping from an ER model following the steps given will give a well-normalised DB.

boyce codd normal form
Boyce-Codd Normal Form
  • After a lot of other approaches Boyce and Codd noticed a simple rule for ensuring tables are well-normalised. Tables which obey the rule are in BCNF (Boyce Codd Normal Form).
  • BCNF rule:

Every determinant in a table must be a candidate key for that table.

determinants
Determinants
  • A is a determinant of B if each value of A has precisely one (possibly null) associated value of B.

Said another way -

  • A is a determinant of B if and only if whenever two tuples agree on their A value they agree on their B value.

A

B

determinants10
Determinants
  • Note that determinancy depends on semantics of data
    • cannot be decided from individual table occurences.
  • Alternative terminology
    • if A (functionally) determines B then
    • B is (functionally) dependent on A
example determinants
Example determinants
  • SSN determines employee name
  • SSN determines employee department
  • Dept. No. determines Dept. Name
  • Dept. Name determines Dept. No.
    • assuming Dept. names are also unique
  • Emp. Name does not determine Emp. Dept
    • two John Smiths could be in difft. Depts.
  • Emp. Name does not determine SSN.
determinancy diagram
Determinancy Diagram

Name

SSN

Department

Dept. Name

Dept. Mgr

In general key attributes of an entity determine all the single-valued attributes of the entity.

composite determinants
Composite Determinants
  • (SSN, Project#) together determine the hours that the employee works on the project.
  • Suppose packsize of a part depends on the supplier.

Name

SSN

hours

Project#

PName

S#

packsize

P#

PName

superfluous attrbiutes
Superfluous Attrbiutes
  • Superfluous attributes
    • If SSN determines name, so does (SSN, Dept) and (SSN, Dept, salary), etc.
    • Always remove superfluous attributes from determinants.
transitive dependencies
Transitive Dependencies
  • SSN actually determines DeptMgr
  • but only because
    • SSN determines DeptNo and
    • DeptNo determines DeptMgr.
  • Be careful to remove transitive dependencies.
    • They mess up normalisation.

DeptNo

SSN

Dept. Mgr

candidate keys
Candidate keys
  • candidate key = any attribute or set of attributes which will be unique for a table (set of attributes).
    • As well as the primary key there may be other candidate keys.
    • E.g. DNUMBER and DNAME are both candidate keys for the Department table.
  • Key = row identifier
  • Candidate key = candidate identifier
finding candidate keys
Finding candidate keys
  • Every key is by definition a determinant of all other attributes in a relation.
    • So in a diagram, any attribute (or composite) from which all other attributes are reachable is a candidate key.

Name

(SSN, Project#) is a (composite) candidate key for a table containing these five attributes.

SSN

hours

Project#

PName

what are the candidate keys
What are the candidate keys ?

G

H

J

student

B

teacher

E

E

subject

D

D

F

P

Q

R

F

G

H

V

X

S

K

M

W

Y

T

B

U

Z

L

A

N

C

problems occur when
Problems occur when ...
  • Redundancy and anomalies occur when there are determinants which are not candidate keys.

SSN

Name

  • SSN is the only key for a table containing these attributes
    • all attributes are reachable from SSN.
  • SSN, DeptNo and DeptName are determinants
    • they have arrows coming out of them.

Dept. Name

DeptNo

Dept. Mgr

bcnf rule
BCNF rule
  • In well-normalised relations (Boyce-Codd normal form)

every determinant is a candidate key.

SSN

Name

Dept. Name

DeptNo

DeptNo

Dept. Mgr

The employee/dept table decomposed to BCNF.

Note that both DeptNo and DeptName are candidate keys of the second table.

transformation to bcnf
Transformation to BCNF
  • Create new tables such that each non-key determinant is a candidate key in a new table.
  • The new table contains the attributes which are directly determined by the new candidate key.

V

X

W

Y

B

Z

A

C

V

V

BCNF tables :

(V, X)

(A, B, C)

(V, W, Z, A)

(V, W, Y)

X

W

V

B

A

Z

A

W

C

Y

other normal forms
Other Normal Forms
  • First NF - no multi-valued attributes
    • all relational DBs are 1NF
  • 2NF - every non-key attribute is fully dependent on the primary key
  • 3NF - eliminate functional dependencies between non-key attributes
    • all dependencies can then be enforced by uniqueness of keys.

G

H

J

Table is in 2NF but not 3NF

bcnf vs 3nf
BCNF vs. 3NF
  • BCNF goes further than 3NF, some say too far.
  • A 3NF table that has no overlapping composite keys is in BCNF.

A teacher teaches only one subject.

For a given subject a given student has only one teacher.

student

teacher

student

teacher

subject

teacher

subject

3NF, not BCNF

keys: (student, subject)

(student, teacher)

teacher is a determinant

BCNF

but tables are not independent

4nf multi valued dependencies
4NF : Multi-valued dependencies
  • If a course can have multiple teachers and multiple texts, blind mapping to 1NF will give

Subject

Physics

Physics

Physics

Physics

Maths

Maths

Maths

Teacher

Green

Brown

Green

Brown

Green

Green

Green

Text

Basic Mechanis

Basic Mechanics

Principles of Optics

Principles of Optics

Basic Mechanics

Vector Analysis

Trigonometry

which clearly has redundancy.

fully normalised
Fully-normalised
  • BCNF relations are well-normalised
  • Fully-normalised relations are those with no multi-valued dependencies (4NF) and no join dependencies (5NF).