Microsoft Office Tutorials and References
In Depth Information
The alternative way to describe the 3NF relations is:
HealthBenefits ( HealthPlan , PlanDesc)
Employee ( EmployeeNum , FirstName, LastName, HealthPlan)
Foreign key: HealthPlan to HealthBenefits table
Position ( PositionID , PositionDesc, PayGrade)
Employment ( EmployeeNum , PositionID , StartDate)
Foreign key: EmployeeNum to Employee table
Foreign key: PositionID to Position table
The four tables have no anomalies because you have eliminated all the data redun-
dancy, partial dependencies, and transitive dependencies. Normalization provides the
framework for eliminating anomalies and delivering an optimal database design, which
you should always strive to achieve. You should be aware, however, that experts sometimes
denormalize tables to improve database performance—specifically, to decrease the time it
takes the database to respond to a user’s commands and requests. Typically, when you
denormalize tables, you combine separate tables into one table to reduce the need for the
DBMS to join the separate tables to process queries and other informational requests.
When you denormalize a table, you reintroduce redundancy to the table. At the same time,
you reintroduce anomalies. Thus, improving performance exposes a database to potential
integrity problems. Only database experts should denormalize tables, but even experts first
complete the normalization of their tables.
Natural, Artificial, and Surrogate Keys
When you complete the design of a database, your tables should be in third normal form,
free of anomalies and redundancy. Some tables, such as the State table (see Figure A-2),
have obvious third normal form designs with obvious primary keys. The State table’s
description is:
State ( StateAbbrev , StateName, EnteredUnionOrder, StateBird, StatePopulation)
Recall that the candidate keys for the State table are StateAbbrev, StateName, and
EnteredUnionOrder. Choosing the StateAbbrev field as the State table’s primary key makes
the StateName and EnteredUnionOrder fields alternate keys. Primary keys such as the
StateAbbrev field are sometimes called natural keys. A natural key (also called a logical
key or an intelligent key ) is a primary key that consists of a field, or a collection of fields,
that is an inherent characteristic of the entity described by the table and that is visible to
users. Other examples of natural keys are the ISBN (International Standard Book Number)
for a book, the SSN (Social Security number) for a U.S. individual, the UPC (Universal
Product Code) for a product, and the VIN (vehicle identification number) for a vehicle.
Search JabSto ::

Custom Search