The Normalization Process

Creating an Entity Relationship

In this exercise, you will create a new, normalized database.

As the database administrator at a local college, you have been asked to design a database for recording students, faculty, and classes.

* To create a new database

1. Create a new database using Microsoft Access. Name the database College.mdb and save it in WA\Practice\Ch06
2. Create the appropriate tables and fields based on the following information:

   ---

   Category	Field
   Student Information	First Name
   	Last Name
   	Student ID (unique)
   	Telephone Number
   	Graduation Date
   Faculty Information	First Name
   	Last Name
   	Faculty ID (unique)
   	Telephone Number
   	Department
   	Date Hired
   	Tenure (Y/N)
   Class Information	Description
   	Course ID (unique)
   	Location (building number)
   	Time
   	Department
   	Faculty Instructor

   ---

3. Save the database.

* To create an entity relationship

1. From the Tools menu, click Relationships .
2. Show all three tables and click Close .
3. From the Faculty table, drag FacultyID to the Faculty field in the Class table.
4. By default, a one-to-many relationship will be created. A faculty member can potentially instruct more than one class.
5. Save the changes and exit Access.

Normalization

The goal of database normalization is to develop a well-designed, optimized, and logical database schema before construction of the database begins. This minimizes the need for database tuning during later stages of development. The end result should enhance performance by saving space and time.

Normalization Rules

According to the rules of database design (called normalization rules ), each table should describe one type of entity, person, place, event, or thing. There are three forms of database normalization. Each normal form defines the state of the data residing in the database.

Figure 6.17 Normalization forms

• First Normal Form: You cannot have repeating groups or multiple value columns .
• Second Normal Form: Every nonkey field must depend on the entire primary key and not on the constituent parts of a composite primary key.
• Third Normal Form: A nonkey field must not depend on another nonkey field.

A database designed according to normalization rules has a larger number of narrow tables, which reduces data redundancy and the amount of space needed to store the data.

Referential Integrity

Referential integrity is a system of rules that a database uses to ensure that relationships between records in related tables are valid, and that you don't accidentally delete or change related data. Referential integrity ensures that for each row in a foreign key table, a corresponding row exists in the primary key table. It also prevents a row in a primary key table from being deleted when a relationship exists to a foreign key table. A relationship between tables must be deleted before primary key columns can be deleted.

Periodic checks for referential integrity help ensure that vital data, such as the unique identifier, remains accurate and usable as the database evolves. Referential integrity also involves managing corresponding data values between tables when the foreign key of a table contains the same values as the primary key of another table.