Database Reference
In-Depth Information
5.5.3 Determining the Designative Entities
This third step involves identifying and properly structuring the designative entities.
As mentioned earlier, a designation is a 1:M or 1:1 relationship between two entities. In
the example, designations are
ProjMgr
,
DeptEmp
, and
EmpDep
. However,
EmpDep
is a
characteristic (that has already been identified above).
From the theory established in chapter 3 (section 3.5.4), a designation is implemented
by the introduction of a foreign key in the relation for the designating entity. Following
this principle, we would introduce foreign key,
EmpDept#
in relation
Employee
(where
EmpDept#
references
Department.Dept#
), and foreign key,
ProjManagerEmp#
in
relation
Project
(where
ProjManagerEmp#
references
Employee.Emp#
).
Integrity constraints for designations would typically be:
•
Null FKs allowed in the designating entity if the participation
is partial
•
Null FKs not allowed in the designating entity if the participation
is full
•
Deletion of referenced records restricted
•
Update of referenced records restricted (although for some
practical purposes, update could be cascaded)
Typically, the foreign key in a designative entity is a non-key attribute. Consequently,
there are normally no keying issues. However, there could be exceptions to this
observation (for instance in the case where an intersecting relation is introduced to
implement a M:M relationship).
5.5.4 Determining the Associations
Step 4 involves identification and implementation of all associations. As mentioned
earlier, associations are the implementation of M:M relationships. They translate to
base relations. In the example, associations are
ProjWork
,
SuppItems
,
ItemStruct
,
and
SuppSched
. Again relying on the theory established in chapter 3 (section 3.5.4)
on the implementation of M:M relationships, we would introduce four base relations
for the four associations —
ProjWork
,
SuppItems
,
ItemStruct
, and
SuppSched
.
However, as established in chapter 4 (section 4.10),
SuppSched
should be replaced
with three relations, namely
SuppItems
{Suppl#, Item#},
ItemProj
{Item#, Proj#}, and
ProjSupp
{Proj#, Suppl#}. Additionally, and in keeping with the principle of having
unique attribute names for the entire database, we will change the foreign key attribute
names to names that are unique but easily traceable to the attributes they reference.
Integrity constraints for associations would typically be:
•
Null FKs not allowed
•
Deletion of referenced records restricted
•
Update of referenced records restricted
