ELEMENTS USED IN LOGICAL DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 40

length of tenure for a dean at a given school may vary, so this attribute
could be very useful.
Many-to-many relationships are widely used, but you should approach
them with caution and carefully document them to ensure that there is no
confusion as you move forward with the physical implementation.
Relationship Options
Now that you know about the various types of relationships, we need to
cover some options that can vary from relationship to relationship within
each type. These options will help you further refine the behavior of each
relationship.
Identifying versus Non-Identifying Relationships
When the primary key of a child entity requires that the primary key of its
parent entity be included, then the relationship between the entities is said
to be identifying. This is because the child entity's unique attribute relies
on the parent entity's unique attribute to correctly identify the correspon-
ding instance. If this requirement is not in place, the relationship is defined
as non-identifying.
In an identifying relationship, the primary key from the parent entity
is literally one of the attributes in the child entity's primary key. Therefore,
the foreign key in the child entity is actually also a part of, or the entirety
of, its primary key. In a non-identifying relationship, the primary key from
the parent entity is simply a non-key attribute in the child entity.
Few relationships are identifying relationships, because most child en-
tities can be referenced independently of the parent entity. Many-to-many
relationships often use identifying relationships, because the additional en-
tity ties together the primary key values of the parent and child entities. For
example, as shown earlier in Figure 2.6, the Deans_Schools entity shows
SchoolsObjectID and DeansObjectID as the attributes in its primary key.
Note that this is always the case with many-to-many relationships; the
join table's primary key is made up of the other tables' primary keys.
Because the primary key attributes from the parent and child primary keys
are present, you can tell visually that these are identifying relationships.
Non-identifying relationships, being far more prevalent, can be recog-
nized when the primary key attribute of the parent entity is a non-key at-
tribute in the child entity. Unless you have a specific requirement for an
identifying relationship, most of your relationships will be non-identifying.