Notes
Slide Show
Outline
1
Chapter 5
  • Database Design 1:
  • Normalization
2
Objectives
  • Discuss functional dependence


  • Define first normal form, second normal form, and third normal form


3
Foundation Concepts
  • 2 Main concepts underlie the idea of normalisation:


    • Functional dependence
    • Keys
4
Functional Dependence
  • Column B is functionally dependent on Column A if A’s value determines a single value for B at a given time


  • Given A, a single value for B can be determined
5
 
6
 
7
Keys
  • Column(s) C is primary key for table T if:
    • Property 1: All columns in T are functionally dependent on C
    • Property 2: No subcollection of columns in C (assuming C is a collection of columns and not just a single column) also has Property 1
8
Keys
  • Candidate Keys
    • Column(s) on which all other columns in table are functionally dependent
  • Alternate Keys
    • Candidate keys not chosen as primary keys
9
Normalisation
  • Basically normalisation is the process of organising data in order to reduce replication.


  • It is mostly achieved by separating data into tow or more related tables.


  • The advantage of normalisation:
    • Reduce storage space required
    • Better performance
    • Ease of use
    • Easier maintenance
10
Unormalised table Form
  • Unnormalized table
    • Contains a repeating group
11
First Normal Form (1NF)
  • 1NFContains no repeating groups
  • Removal of repeating groups is starting point in quest for problem-free tables
12
 
13
Second Normal Form (2NF)
  • 1NF Tables may contain problems (refer to p. 147 text for full example)
    • Redundancy
    • Update Anomalies
      • Update, inconsistent data, additions, deletions
      • Occur because a column is dependent on a portion of a multi-column primary key
  • 2NF Table
    • In 1NF +no non-key column is dependent on only a portion of the primary key


14
 
15
To determine 2NF
16
 
17
Third Normal Form (3NF)
  • 2NF Tables may still contain problems
    • Redundancy and wasted space
    • Update Anomalies
      • Update, inconsistent data, additions, deletions
      • Occur because a column is dependent on a portion of a multi-column primary key


      • --we won’t cover 4th & 5th normal forms since they are not often used.

18
Third Normal Form (3NF)
  • 3NF Table
    • In 2NF
    • +
    • the only determinants contained are candidate keys


19
Procedure 3NF
20
 
21
 
22
Summary
  • Normalisation two key concepts:
    • Functional dependency
    • Keys
  • Un-normalised form
  • First normal form
  • Second normal form
  • Third normal form
23
Tables in the database