Microsoft Office Tutorials and References
In Depth Information
Session 2.1
number, naturally serves the function of a primary key. For other tables, two or more fields
might be needed to function as the primary key. In these cases, the primary key is referred to
as a composite key . For example, a school grade table would use a combination of student
number and course code to serve as the primary key. For a third category of tables, no single
field or combination of fields can uniquely identify a record in a table. In these cases, you
need to add a field whose sole purpose is to serve as the table’s primary key. For Elsa’s tables,
EmployerID is the primary key for the Employer table, NAICSCode is the primary key for the
NAICS table, and PositionID will be the primary key for the Position table.
Include a common field in related tables. You use the common field to connect one table
logically with another table. For example, Elsa’s Employer and Position tables will include
the EmployerID field as a common field. Recall that when you include the primary key from
one table as a field in a second table to form a relationship, the field is called a foreign key in
the second table; therefore, the EmployerID field will be a foreign key in the Position table.
With this common field, Elsa can find all positions available at a particular employer; she
can use the EmployerID value for an employer and search the Position table for all records
with that EmployerID value. Likewise, she can determine which employer has a particular
position available by searching the Employer table to find the one record with the same
EmployerID value as the corresponding value in the Position table.
Avoid data redundancy. When you store the same data in more than one place, data
redundancy occurs. With the exception of common fields to connect tables, you should
avoid redundancy because it wastes storage space and can cause inconsistencies, if, for
instance, you type a field value one way in one table and a different way in the same
table or in a second table. Figure 2-3, which contains portions of potential data to be
stored in the Employer and Position tables, shows an example of incorrect database
design that has data redundancy in the Position table; the EmployerName field is redun-
dant, and one value was entered incorrectly, in three different ways.
Incorrect database design with data redundancy
Figure 2-3
Employer table
Employer ID
Employer Name
Address
Phone
10122
BeanTown Tours
105 State Street, Boston, MA 02109
617-451-1970
Boston Harbor Excursions
BaySide Inn & Country Club
The Briar Rose Inn
Windsor Alpine Tours
Trudel Spa & Resort
75 Atlantic Avenue, Boston, MA 02110
617-235-1800
10125
10126
10190
10191
10198
354 Oceanside Drive, Brewster, MA 02631
508-283-5775
105 Queen Street, Charlottetown PE C1A 8R4
902-626-1595
14 Longmeadow Road, Laconia, NH 03246
603-266-9233
40 Rue Rivard, North Hatley QC J0B 2C0
819-842-7783
data redundancy
Position table
Position ID
Employer ID
Employer Name
Position Title
Hours/Week
2040
DaySide Inn & Country Club
Waiter/Waitress
32
24
24
32
24
30
35
40
32
10126
2045
2053
2066
10122
BeanTown Tours
Tour Guide
Host/Hostess
Lifeguard
Pro Shop Clerk
Ski Patrol
Day Care
Reservationist
Kitchen Help
10190
The Briar Rose Inn
Trudel Spa & Resort
Baside Inn & Country Club
Windsor Alpine Tours
Windsor Alpine Tours
Boston Harbor Excursions
BaySide Inn Club
10198
2073
2078
2079
2082
2111
10126
10191
10191
10125
10126
inconsistent data
 
Search JabSto ::




Custom Search