1
|
- Database Design 1:
- Normalization
|
2
|
- Discuss functional dependence
- Define first normal form, second normal form, and third normal form
|
3
|
- 2 Main concepts underlie the idea of normalisation:
- Functional dependence
- Keys
|
4
|
- 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
|
- 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
|
- Candidate Keys
- Column(s) on which all other columns in table are functionally
dependent
- Alternate Keys
- Candidate keys not chosen as primary keys
|
9
|
- 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
|
- Unnormalized table
- Contains a repeating group
|
11
|
- 1NFContains no repeating groups
- Removal of repeating groups is starting point in quest for problem-free
tables
|
12
|
|
13
|
- 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
|
|
16
|
|
17
|
- 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
|
- 3NF Table
- In 2NF
- +
- the only determinants contained are candidate keys
|
19
|
|
20
|
|
21
|
|
22
|
- Normalisation two key concepts:
- Functional dependency
- Keys
- Un-normalised form
- First normal form
- Second normal form
- Third normal form
|
23
|
|