Database Normalization to 3NF

Overview

This lecture demonstrates how to normalize a credit card transaction database from 2nd to 3rd Normal Form by removing transitive dependencies using Microsoft Access.

Identifying Normalization Issues

• The credit card transactions table contains both vendor number and vendor name.
• Including both fields in one table violates 3rd Normal Form due to a transitive dependency.
• Vendor name is dependent on vendor number, not on the transaction ID (the primary key).

Steps to Normalize to 3rd Normal Form

• Move vendor name information to a separate vendor table.
• In the vendor table, use vendor number as the primary key and vendor name as an attribute.
• Remove duplicates from the vendor table to ensure vendor number is unique.
• Delete the vendor name column from the credit card transactions table.
• Make vendor number in credit card transactions a foreign key referencing the vendor table.

Setting Up Relationships in Access

• Create the vendor table in Access with vendor number (number) and vendor name (short text) fields.
• Match data types and field properties (e.g., 'double' or 'long integer') for the vendor number in both tables before linking.
• Set up a one-to-many relationship between credit card transactions (foreign key) and vendor (primary key) by enforcing referential integrity.

Result of Normalization

• The database is now in 3rd Normal Form, with no transitive dependencies.
• All vendor information is stored in a single dedicated table, reducing redundancy.

Key Terms & Definitions

• Normalization — The process of organizing a database to reduce redundancy and improve data integrity.
• 3rd Normal Form (3NF) — A database is in 3NF if it is in 2NF and all its attributes are only dependent on the primary key.
• Transitive Dependency — A situation where a non-key attribute depends on another non-key attribute rather than directly on the primary key.
• Foreign Key — A field in one table that links to the primary key in another table, creating a relationship between the tables.
• Referential Integrity — A property that ensures foreign keys correctly and consistently reference primary keys in related tables.

Action Items / Next Steps

• Watch the next video for detailed steps on future normalization exercises.
• Prepare to follow the step-by-step normalization process in upcoming class sessions.