Microsoft Office Tutorials and References
In Depth Information
A bubble diagram for the modified Employee table
You can read the bubble diagram in Figure A-12 as follows:
• The EmployeeNum field is a determinant for the LastName, HealthPlan, and
• The PositionID field is a determinant for the PositionDesc field.
• The StartDate field is functionally dependent on the EmployeeNum and PositionID
• The HealthPlan field is a determinant for the PlanDesc field.
Note that EmployeeNum and PositionID together is a determinant for the StartDate field
and for all fields that depend on the EmployeeNum field alone and the PositionID field
alone. Some experts include these additional fields and some don’t. The previous list of
determinants does not include these additional fields.
An alternative way to show determinants is to list the determinant, a right arrow, and
then the dependent fields, separated by commas. Using this alternative, the determinants
shown in Figure A-12 are:
LastName, HealthPlan, PlanDesc
Only the StartDate field is functionally dependent on the table’s full primary key, the
EmployeeNum and PositionID fields. The LastName, HealthPlan, and PlanDesc fields have
partial dependencies because they are functionally dependent on the EmployeeNum field,
which is part of the primary key. A partial dependency is a functional dependency on part of
the primary key, instead of the entire primary key. Does another partial dependency exist in the
Employee table? Yes, the PositionDesc field has a partial dependency on the PositionID field.
Because the EmployeeNum field is a determinant of both the HealthPlan and PlanDesc
fields, and the HealthPlan field is a determinant of the PlanDesc field, the HealthPlan and
PlanDesc fields have a transitive dependency. A transitive dependency is a functional
dependency between two nonkey fields, which are both dependent on a third field.
How do you know which functional dependencies exist among a collection of fields,
and how do you recognize partial and transitive dependencies? The answers lie with the
questions you ask as you gather the requirements for a database application. For each
field and entity, you must gain an accurate understanding of its meaning and relationships
in the context of the application. Semantic object modeling is an entire area of study
within the database field devoted to the meanings and relationships of data.