Microsoft Office Tutorials and References
In Depth Information
What is the primary key of the Donation table? No single field is unique, and neither is
any combination of fields. For example, on 10/10/2011 two anonymous donors (DonorID
value of 4) donated $50 each. You need to add an artificial key, DonationID for example,
to the Donation table. The addition of the artificial key makes every record in the
Donation table unique, as shown in Figure A-19.
Donation table after adding DonationID, an artificial key
Figure A-19
Donation
artificial key
DonationID
DonorID
DonationDate
DonationAmt
1
1
10/12/2010
$50.00
2
1
09/30/2011
$50.00
3
2
10/03/2011
$75.00
4
4
10/10/2011
$50.00
5
4
10/10/2011
$50.00
6
4
10/11/2011
$25.00
7
5
10/13/2011
$50.00
The descriptions of the Donor and Donation tables now are:
Donor ( DonorID , DonorFirstName, DonorLastName)
Donation ( DonationID , DonorID, DonationDate, DonationAmt)
Foreign key: DonorID to Donor table
For another common situation, consider the 3NF tables you reviewed in the previous
section (see Figure A-17) that have the following descriptions:
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
Recall that a primary key must be unique, must be minimal, and must not change in
value. In theory, primary keys don’t change in value. However, in practice, you might have
to change EmployeeNum field values that you incorrectly entered in the Employment table.
Further, if you need to change an EmployeeNum field value in the Employee table, the
change must cascade to the EmployeeNum field values in the Employment table. Also,
changes to a PositionID field value in the Position table must cascade to the Employment
table. For these and other reasons, many experts add surrogate keys to their tables. A
surrogate key (also called a synthetic key ) is a system-generated primary key that is hidden
from users. Usually you can use an automatic numbering data type, such as the Access
AutoNumber data type, for a surrogate key. Figure A-20 shows the four tables with surro-
gate keys added to three of the four tables.
 
Search JabSto ::




Custom Search