Database Reference
In-Depth Information
combine the lname and fname columns, the combination becomes a determinant for the rest of
the columns. hat is
(fname, lname)
→
phone, address, city, zip
he notation indicates that the columns phone, address, city, and zip are functionally depen-
dent on the combination (fname, lname).
In the above functional dependency, phone depends on the combination (fname, lname), but
fname alone does not determine phone. he discussion leads to another concept,
fully functional
dependency
, in the data modeling theory.
If the attribute Y is functionally dependent on the combination X which consists of multiple
attributes but not on any subset of the attributes of X, then, Y is fully functionally dependent on X.
In this section, the concepts of functional dependency have been introduced. Later in this
chapter, functional dependency will be used to establish the relationships among tables.
3.2.2 Keys
In Chapter 2, you learned that an attribute or a group of attributes that can uniquely identify
entity instances in an entity is called a
key
. In the previous section, the column LocationID
uniquely determines the rest of the columns. herefore, LocationID is a
key
for the table. his
means that a key is a determinant; however, in general, a determinant may not necessarily be a key.
For example, in Figure 3.1, CostRate determines Availability. Any attribute that determines the
values of another attribute is a determinant. Although CostRate is a determinant, it is not a key
column because the values in it are not unique.
Since a key is a determinant and functional dependency speciies the relationship between two
tables, the concept of keys plays an important role in the process of converting a data model to a
relational database. Tables in a relational database will be connected with key columns.
To get ready for the converting process, let us do a quick review about keys. First, to be unique,
each value in a key column can only occur once. For a table to be a relation, the table must include
a
key
column since no relation allows duplicated rows. he concept of keys is summarized in the
following:
Candidate key
: A nonredundant column or a set of columns that can uniquely identify each
row in a table is called candidate key. For the table in Figure 3.1, both LocationID and Name can
serve as a key to determine each row in the table. herefore, the attributes LocationID and Name
are both qualiied as the candidate key attributes:
LocationID
→
Name, CostRate, Availability, ModiiedDate
Name
→
LocationID, CostRate, Availability, ModiiedDate
Primary key
: In a relation, a
primary key
is a
candidate key
selected by the user to uniquely
identify other columns in the relation.
Foreign key
: In a relation, a foreign key is a column or a set of columns whose values match
the values of the
primary key
in another relation.
Combination key
: A combination key consists of more than one column that can be used as
a key.
Now that you have learned the concept of keys, the next step is to use keys to represent
relationships.
