Database Reference
In-Depth Information
constraints must then be implemented using triggers. A
trigger
is a named
event-condition-action rule that is automatically activated when a relation is
updated. In this topic, we shall see several examples of integrity constraints
implemented using triggers.
Notice that triggers can also be used to compute derived attributes.
Figure
2.1
shows a derived attribute
NumberOrders
in the entity type
Products
. If we want to implement this derived attribute in the table of the
same name in Fig.
2.4
, a trigger will update the value of the attribute each
time there is an insert, update, or delete in table
OrderDetails
.
The translation of a conceptual schema (written in the ER or any other
conceptual model) to an equivalent relational schema is called a
mapping
.
This is a well-known process, implemented in most database design tools.
These tools use conceptual schemas to facilitate database design and then
automatically translate the conceptual schemas to logical ones, mainly into
the relational model. This process includes the definition of the tables in
various RDBMSs.
We now outline seven rules that are used to map an ER schema into a
relational one.
Rule 1:
A strong entity type
E
is mapped to a table
T
containing the simple
monovalued attributes and the simple components of the monovalued
complex attributes of
E
. The identifier of
E
defines the primary key of
T
.
T
also defines non-null constraints for the mandatory attributes. Note
that additional attributes will be added to this table by subsequent rules.
For example, the strong entity type
Products
in Fig.
2.1
is mapped to
the table
Products
in Fig.
2.4
,withkey
ProductID
.
Rule 2:
Let us consider a weak entity type
W
, with owner (strong) entity
type
O
. Assume
W
id
is the partial identifier of
W
,and
O
id
is the identifier
of
O
.
W
is mapped in the same way as a strong entity type, that is, to
atable
T
.Inthiscase,
T
must also include
O
id
as an attribute, with a
referential integrity constraint to attribute
O.O
id
. Moreover, the identifier
of
T
is the union of
W
id
and
O
id
.
As an example, the weak entity type
OrderDetails
in Fig.
2.2
is mapped
to the table of the same name in Fig.
2.5
. The key of the latter is composed
of the attributes
OrderID
and
LineNo
, where the former is a foreign key
referencing table
Orders
.
Rule 3:
A regular binary one-to-one relationship type
R
between two entity
types
E
1
and
E
2
, which are mapped, respectively, to tables
T
1
and
T
2
is mapped embedding the identifier of
T
1
in
T
2
as a foreign key. In
addition, the simple monovalued attributes and the simple components of
the monovalued complex attributes of
R
are also included in
T
2
.Thistable
also defines non-null constraints for the mandatory attributes.
Search WWH ::
Custom Search