Terms in this set (15)

Step 1: Mapping of Regular Entity Types. For each regular (strong) entity type
E in the ER schema, create a relation R that includes all the simple attributes of E.
Include only the simple component attributes of a composite attribute. Choose one
of the key attributes of E as the primary key for R. If the chosen key of E is a composite,
then the set of simple attributes that form it will together form the primary
key of R.
If multiple keys were identified for E during the conceptual design, the information
describing the attributes that form each additional key is kept in order to specify
secondary (unique) keys of relation R. Knowledge about keys is also kept for indexing
purposes and other types of analyses.
In our example, we create the relations EMPLOYEE, DEPARTMENT, and PROJECT in
Figure 9.2 to correspond to the regular entity types EMPLOYEE, DEPARTMENT, and
PROJECT in Figure 9.1. The foreign key and relationship attributes, if any, are
not included yet; they will be added during subsequent steps. These include theattributes Super_ssn and Dno of EMPLOYEE, Mgr_ssn and Mgr_start_date of
DEPARTMENT, and Dnum of PROJECT. In our example, we choose Ssn, Dnumber,
and Pnumber as primary keys for the relations EMPLOYEE, DEPARTMENT, and
PROJECT, respectively. Knowledge that Dname of DEPARTMENT and Pname of
PROJECT are secondary keys is kept for possible use later in the design.
The relations that are created from the mapping of entity types are sometimes called
entity relations because each tuple represents an entity instance. The result after
this mapping step is shown in Figure 9.3
Step 2: Mapping of Weak Entity Types. For each weak entity type W in the ER
schema with owner entity type E, create a relation R and include all simple attributes
(or simple components of composite attributes) of W as attributes of R. In
addition, include as foreign key attributes of R, the primary key attribute(s) of the
relation(s) that correspond to the owner entity type(s); this takes care of mapping
the identifying relationship type of W. The primary key of R is the combination of
the primary key(s) of the owner(s) and the partial key of the weak entity type W, if
any.
If there is a weak entity type E2 whose owner is also a weak entity type E1, then E1
should be mapped before E2 to determine its primary key first.
In our example, we create the relation DEPENDENT in this step to correspond to the
weak entity type DEPENDENT (see Figure 9.3(b)).We include the primary key Ssn
of the EMPLOYEE relation—which corresponds to the owner entity type—as a foreign
key attribute of DEPENDENT; we rename it Essn, although this is not necessary.
The primary key of the DEPENDENT relation is the combination {Essn,
Dependent_name}, because Dependent_name (also renamed from Name in Figure 9.1)
is the partial key of DEPENDENT.
It is common to choose the propagate (CASCADE) option for the referential triggered
action (see Section 4.2) on the foreign key in the relation corresponding to the
weak entity type, since a weak entity has an existence dependency on its owner
entity. This can be used for both ON UPDATE and ON DELETE.
Step 3: Mapping of Binary 1:1 Relationship Types. For each binary 1:1 relationship
type R in the ER schema, identify the relations S and T that correspond to
the entity types participating in R. There are three possible approaches: (1) the foreign
key approach, (2) the merged relationship approach, and (3) the crossreference
or relationship relation approach. The first approach is the most useful
and should be followed unless special conditions exist, as we discuss below.
1. Foreign key approach: Choose one of the relations—S, say—and include as
a foreign key in S the primary key of T. It is better to choose an entity type
with total participation in R in the role of S. Include all the simple attributes
(or simple components of composite attributes) of the 1:1 relationship type
R as attributes of S.
In our example, we map the 1:1 relationship type MANAGES from Figure
9.1 by choosing the participating entity type DEPARTMENT to serve in the
role of S because its participation in the MANAGES relationship type is total
(every department has a manager). We include the primary key of the
EMPLOYEE relation as foreign key in the DEPARTMENT relation and rename
it Mgr_ssn.We also include the simple attribute Start_date of the MANAGES
relationship type in the DEPARTMENT relation and rename it Mgr_start_date
(see Figure 9.2).
Note that it is possible to include the primary key of S as a foreign key in T
instead. In our example, this amounts to having a foreign key attribute, say
Department_managed in the EMPLOYEE relation, but it will have a NULL value
for employee tuples who do not manage a department. If only 2 percent of
employees manage a department, then 98 percent of the foreign keys would
be NULL in this case. Another possibility is to have foreign keys in both relations
S and T redundantly, but this creates redundancy and incurs a penalty
for consistency maintenance.
2. Merged relation approach: An
Step 4: Mapping of Binary 1:N Relationship Types. For each regular binary
1:N relationship type R, identify the relation S that represents the participating entity
type at the N-side of the relationship type. Include as foreign key in S the primary key
of the relation T that represents the other entity type participating in R; we do this
because each entity instance on the N-side is related to at most one entity instance on
the 1-side of the relationship type. Include any simple attributes (or simple components
of composite attributes) of the 1:N relationship type as attributes of S.
In our example, we now map the 1:N relationship types WORKS_FOR, CONTROLS,
and SUPERVISION from Figure 9.1. For WORKS_FOR we include the primary key
Dnumber of the DEPARTMENT relation as foreign key in the EMPLOYEE relation and
call it Dno. For SUPERVISION we include the primary key of the EMPLOYEE relation
as foreign key in the EMPLOYEE relation itself—because the relationship is recursive—
and call it Super_ssn. The CONTROLS relationship is mapped to the foreign
key attribute Dnum of PROJECT, which references the primary key Dnumber of the
DEPARTMENT relation. These foreign keys are shown in Figure 9.2.
An alternative approach is to use the relationship relation (cross-reference) option
as in the third option for binary 1:1 relationships. We create a separate relation R
whose attributes are the primary keys of S and T, which will also be foreign keys to
S and T. The primary key of R is the same as the primary key of S. This option can
be used if few tuples in S participate in the relationship to avoid excessive NULL values
in the foreign key.
Step 8: Options for Mapping Specialization or Generalization. Convert each
specialization with m subclasses {S1, S2, ..., Sm} and (generalized) superclass C,
where the attributes of C are {k, a1, ...an} and k is the (primary) key, into relation
schemas using one of the following options:
■ Option 8A: Multiple relations—superclass and subclasses. Create a relation
L for C with attributes Attrs(L) = {k, a1, ..., an} and PK(L) = k. Create a
relation Li for each subclass Si, 1 ≤ i ≤ m, with the attributes Attrs(Li) = {k} ∪
{attributes of Si} and PK(Li) = k. This option works for any specialization
(total or partial, disjoint or overlapping).
■ Option 8B: Multiple relations—subclass relations only. Create a relation
Li for each subclass Si, 1 ≤ i ≤ m, with the attributes Attrs(Li) = {attributes of
Si} ∪ {k, a1, ..., an} and PK(Li) = k. This option only works for a specialization
whose subclasses are total (every entity in the superclass must belong to (at
least) one of the subclasses). Additionally, it is only recommended if the specialization
has the disjointedness constraint (see Section 8.3.1).If the specialization
is overlapping, the same entity may be duplicated in several relations.
■ Option 8C: Single relation with one type attribute. Create a single relation
L with attributes Attrs(L) = {k, a1, ..., an} ∪ {attributes of S1} ∪ ... ∪ {attributes
of SSm} ∪ {t} and PK(L) = k. The attribute t is called a type (ordiscriminating) attribute whose value indicates the subclass to which each
tuple belongs, if any. This option works only for a specialization whose subclasses
are disjoint, and has the potential for generating many NULL values if
many specific attributes exist in the subclasses
;