Resources

Online Shop

Info

Designer
for Microsoft Access
Create complex MS Access databases without being an expert in relational
database design! Designer for Microsoft Access asks you plain-language
questions about what you want to manage with your database, and creates
the tables and relationships automatically. Free trial available

Third Normal Form (3NF) - Normalising Your Database:

Concepts >> A table is in second normal form (2NF) and
there are no transitive dependencies.

A transitive dependency is a type of functional
dependency in which the value in a non-key field is determined by
the value in another non-key field and that field is not a candidate
key.

A Practical Approach

Again, look for repeated values in a non-key field as in the following
example.

A table with a single field primary key and repeating values in non-key
fields.

*ProjectNum

ProjectTitle

ProjectMgr

Phone

30-452-T3

STAR manual

Garrison

2756

30-457-T3

ISO procedures

Jacanda

2954

30-482-TC

Web site

Friedman

2846

31-124-T3

Employee handbook

Jones

3102

31-238-TC

STAR prototype

Garrison

2756

31-241-TC

New catalog

Jones

3102

35-152-TC

STAR pricing

Vance

3022

36-272-TC

Order system

Jacanda

2954

The phone number is repeated each time a manager name is repeated.
This is because the phone number is only a second cousin to the project
number. It's dependent on the manager, which is dependent on the project
number (a transitive dependency).

The ProjectMgr field is not a candidate key because the same person
manages more than one project. Again, the solution is to remove the
field with repeating data to a separate table.

Complying with third normal form

As you've probably come to expect by now, you'll take the above table
and create new tables to fix the problem.

Think about which fields belong together and create new
tables to hold them.

Enter the sample data and check for unnecessarily (not
part of primary key) repeated values.

Identify the primary key for each table and, if necessary,
add foreign keys.

PROJECTS

*ProjectNum

ProjectTitle

ProjectMgr

30-452-T3

STAR manual

Garrison

30-457-T3

ISO procedures

Jacanda

30-482-TC

Web site

Friedman

31-124-T3

Employee handbook

Jones

31-238-TC

STAR prototype

Garrison

31-241-TC

New catalog

Jones

35-152-TC

STAR pricing

Vance

36-272-TC

Order system

Jacanda

MANAGERS

*ProjectMgr

Phone

Friedman

2846

Garrison

2756

Jacanda

2954

Jones

3102

Vance

3022

Re-examine your tables to make sure there are no unnecessarily repeating
values in non-key fields and that the value in each non-key field
is determined by the value(s) in the key field(s).

That wraps up this topic on normalising to Third Normal Form. In
most cases 3NF should be sufficient to ensure that your database is
properly normalised, however higher
normal forms can be achieved.