Microsoft Office Tutorials and References
In Depth Information
Introduction to Database Concepts
In the sample database shown in Figure 1-2, each record in the Customer table has a
fi eld named CustomerID, which is also a fi eld in the Contract table. For example, Owen
Hawes is the fi rst customer in the Customer table and has a CustomerID fi eld value
of 11005. This same CustomerID fi eld value, 11005, appears in three records in the
Contract table. Therefore, Owen Hawes is the customer with these three contracts.
Figure 1-2
Database relationship between tables for customers and contracts
Customer table
CustomerID
FirstName
LastName
Phone
Owen
Hawes
616-392-0622
11005
Caputo
11008
Melissa
269-985-1122
11014
Amol
Mehta
616-396-1972
11015
John
Weiss
616-637-7783
Karen
O’Brien
11027
517-483-9244
primary keys
Hwan
Tang
11053
616-396-8401
comm on field
foreign key
three co ntracts
for Owe n Hawes
Contract table
ContractNum
CustomerID
ContractAmt
SigningDate
3012
3015
3027
3032
3033
3050
3060
3062
11027
11005
11008
11014
11005
11015
11005
11053
$300
$1,500
$1,250
$6,500
$2,250
$1,000
$4,000
$20,000
2/18/2013
3/1/2013
4/7/2013
6/22/2013
7/8/2013
8/27/2013
11/30/2013
1/11/2014
Each CustomerID value in the Customer table must be unique so that you can
distinguish one customer from another. These unique CustomerID values also identify each
customer’s specifi c contracts in the Contract table. The CustomerID fi eld is referred to as
the primary key of the Customer table. A primary key is a fi eld, or a collection of fi elds,
whose values uniquely identify each record in a table. No two records can contain the
same value for the primary key fi eld. In the Contract table, the ContractNum fi eld is
the primary key because Belmont Landscapes assigns each contract a unique contract
number.
When you include the primary key from one table as a fi eld in a second table to form
a relationship between the two tables, it is called a foreign key in the second table, as
shown in Figure 1-2. For example, CustomerID is the primary key in the Customer table
and a foreign key in the Contract table. Although the primary key CustomerID contains
unique values in the Customer table, the same fi eld as a foreign key in the Contract table
does not necessarily contain unique values. The CustomerID value 11005, for example,
appears three times in the Contract table because Owen Hawes has three contracts. Each
foreign key value, however, must match one of the fi eld values for the primary key in the
other table. In the example shown in Figure 1-2, each CustomerID value in the Contract
table must match a CustomerID value in the Customer table. The two tables are related,
enabling users to connect the facts about customers with the facts about their contracts.
Search JabSto ::




Custom Search