Databases Reference
In-Depth Information
identifier. With relational diagrams, the name of the unique identifier is
underlined.
In the example shown in Exhibit 6, there are two key constraints —
Employee Identifier and Social Security Number. That means there are two
English sentences: one for employee and one for social security number. In
the entity-relationship diagrams, multiple key constraints are illustrated by
underlining each key attribute by a different type of line. Relational DBMSs
require exactly one primary key. The remaining unique identifiers are
called candidate keys. Each candidate key is declared with the UNIQUE
clause in SQL. With the relational diagrams, each unique identifier is under-
lined with a different type of line, so they are not confused with other
unique identifiers or the primary key.
One-to-Many Relationships
Exhibit 8 illustrates a one-to-many relationship between Department
and Employee. A one-to-many relationship between entity sets A and B
implies that zero or one A entity can be related to zero, one, or more B enti-
ties. In Exhibit 8, a single Department can be related to one or more
Employee entities. Each entity participates in, at most, one relationship.
One-to-many relationships are important because they describe how enti-
ties may relate to other entities. For example, a database containing
Department and Employee entities alone fails to capture which employee
works for which department. One-to-many relationships define these
important types of associations.
With Exhibit 8, the English design language shows that one-to-many rela-
tionships are expressed with sentences, such as “Every employee may
work in zero or one department. Several employees may work in the same
department.” With entity-relationship diagrams, a one-to-many relation-
ship is denoted as a diamond connecting two entity sets, with the “1” and
“m” indicating the “one” and “many” entities in the relationship, respec-
tively. The relationship name is shown inside of a diamond. In SQL nota-
tion, a one-to-many relationship is called a foreign key constraint. The DBA
specifies the primary key of the “one” (or A) entity as a column in the table
of the “many” (or B) entity. The value of this extra column in the Employee
table identifies the Department entity for which the Employee works. A
special clause, called a foreign key, describes a business rule that implies
the only values of the DeptName column in the Employee table may be val-
ues in the DeptName column of the Department table. The relational dia-
gram shows that the one-to-many relationship is represented by a dotted
line from the primary key of the table representing the entity set A to the
foreign key in the table representing entity set B. Thus, in this example, a
dotted line goes from the DeptName column of the Department table to the
DeptName column of the Employee table.
Search WWH ::
Custom Search