Lesson 4: Database Normalization

The Normalization Process

An optimal relational database is typically developed using entity-relationship modeling and normalization. These two processes or methodologies aid in defining the logical data model.

Entity-Relationship Modeling

Entity-relationship modeling defines the structure of a relational database. It identifies data entities or objects and the relationships between these entities.

Figure 6.16 The entity-relationship modeling process

The entity-relationship modeling process usually progresses in the following manner:

• Entities or objects are identified.
• Identifiers or primary keys for each entity are identified.
• Data elements or attributes associated with each entity are identified. Data types should use the smallest amount of storage space necessary for the required data. This allows more data rows to fit on a single data page, which can reduce the number of I/Os.
• Relationships between entities are identified, which allows you to identify primary key and foreign key relationships.

For each relationship, the SQL Join statement is used to join tables with similar data. For example, the Customers table would join to the Orders table on the CustomerID field.

After this process has been completed, the resulting entity-relationship model represents a logical view of the data entities and associated relationships upon which the physical database will be based. Normalization commonly occurs during this modeling process.