Normalisation

1
Normalisation

• HIIB62
• Databases and Data Modelling
• Contributions by Arthur Adamopoulos, Vince Bruno,
  Hossein S. Zadeh, Ian Searle and Ian Storey

2
Normalization

• Normalization none,1st, 2nd,3rd normal forms
  (rarely need 4th and 5th) 1NF, 2NF, 3NF
• Functional Dependence
• Partial/Transitive dependencies
• Primary Keys
• Referential Integrity

3
Normalization

• This is the process of converting complex data
  structures into a simple, stable structure.

4
Normalization - Steps

• Each stage is a normal form
• Normal forms relate by applying simple rules
  about dependencies.

5
Normalization - steps
remove repeated groups
remove partial dependencies
remove transitive dependencies
6
Functional Dependence

• A relationship between two attributes
• One field is dependent on another, the first
  field value would not come into existence unless
  the second field value does.
• IF A depends on B there is only 1 value for A for
  each value of B
• A only exists when B exists first.
• B ? A

7
Keys

• Primary Key
• Unique identifier (field or fields) of a table
• properties of a primary key are
• Uniqueness
• Availability
• Stability
• Minimality
• Candidate Key
• A field or fields that could be a primary key
• Composite Key
• a primary key containing more than one field.

8
Partial Dependency

• A field that depends on part of the primary or
  candidate key.
• If tables do NOT have multi-part keys, partial
  dependency CANNOT exist.

9
Transitive Dependency

• A field that depends on a NON-Key field(s).

10
Constraints - Domain

• Constraints on field entries
• input checking
• type
• length
• formats
• allowable values
• min/max ranges
• Optional/mandatory

11
Referential Integrity

• Related to foreign keys only.
• Values in foreign key must exist in primary key
  of related file.

12
RI - Other Issues

• Insert/Update
• value inserted/change in foreign key must already
  exist in primary key of other file.
• Delete - three options
• not allow
• null out the corresponding foreign key(s)
• cascade delete - remove entire record and any
  related foreign keys.

13
RI - Cascade Delete

• A delete is issued to delete an employee from the
  EMPLOYEE file.

EMPLOYEE
CHILD
POLICY
emp.no surname given joined
cascade delete
cascade delete
any policy records with a related child deleted
are also deleted.
any child records with same emp.no are deleted.
14
Zero Normal Formal
STUDENT

• student.no
• surname
• given
• address
• DOB
• dept.no
• progm.code
• progm.name
• library.card

• course.name1
• course.name2
• course.name3
• syllabus1
• syllabus2
• syllabus3
• year.start1
• year.start2
• year.start3

• term.start1
• term.start2
• term.start3
• result1
• result2
• result3

15
1st Normal Form
PD
16
2nd Normal Form
ENROL
COURSE
course.code student.no year.start
term.start result
course.code course.name syllabus
TD
TD
17
3rd Normal Form
ENROL
COURSE
Enrolment.No course.code student.no year.start ter
m.start result
course.code course.name syllabus
18
Other Normal Forms

• Fourth Normal Form
• remove anomalies that result from a multi-valued
  dependencies
• Fifth Normal Form
• designed to cope with dependency known as join
  dependency.
• Boyce-Codd Normal Form
• remove remaining anomalies resulting from
  functional dependencies