Drewslair.com - Microsoft Access for Beginners: Data Normalization

Microsoft Access for Beginners - Additional Information

Database Normalization - The Slideshow

Microsoft Access for Beginners eBook now available! You can read the entire Microsoft Access for Beginners series, completely reviewed and updated for Access 2010, in the new Kindle edition now available from Amazon.com!

With an entirely new sample application created specifically for this eBook and additional chapters, this new Kindle edition is an important update to the series available only on Amazon.com.

While reviewing the series, I decided it would be helpful to have an actual demonstration of the database normalization concepts presented in Part I. So I've put together a PowerPoint slideshow that explains the first three Normal Forms while showing step-by-step how the sample Excel file used in this series is changed into a set of Access tables.

You can view the show online or download it for viewing later.

If you don't have Microsoft Powerpoint, you can download the free viewer from Microsoft or view the slides by clicking on the images below.

Introduction This presentation is intended as a companion to Part I of the series in which I write about organizing data in a database and how it differs from data storage in a flat file such as an Excel spreadsheet.

Raw Data Starting out with the raw information, the goal is to organize this data into an Access database.

Normal Forms The process of normalization is defined by a set of rules by which the data is progressively organized. These are referred to as Normal Forms.

The First Normal Form (sometimes abbreviated as 1NF) specifies that all table fields must contain only one value, not a list of values. These single values are referred to as scalar or atomic.

Splitting Tables To store information more efficiently, it's broken off into a separate table. A key is added to each table which is used to link the two.

Levels of Normalization Normal forms can be more strictly adhered to in certain cases depending on the needs of the system.

The Second Normal Form (2NF) specifies that the tables meet the requirements of the First Normal Form and that all non-key fields are dependent on the entire table key.

Designing for Security Another reason to split data between tables is to keep certain information private. If you have many people using the database, there may be some groups who do not need access to certain information.

The Third Normal Form (3NF) specifies that the tables meet the requirements of the first and second normal forms and that all non-key fields are mutually independent of each other.

Adding New Fields Normalization is important when adding new fields to the database as well as when you're first designing it.

Adding Tables As the design of the database grows and changes, making the changes according to the normal forms is important.

Refining the Design Another example of how the normal forms can be applied based on the needs of the database.

Variations A certain amount of flexibility is necessary when applying the normal forms but always be careful when deciding to ignore them (denormalization).

Recommended reading and references ...