Page 216 -

Q5-6 How Is a Data Model Transformed into a Database Design?

Department Adviser Student 215
DeptName AdviserName StudentNumber
Admin Phone StudentName
Phone CampusAddress HW1
Figure 5-21 Email EmailAddress HW2
Sample Relationships Version 2 MidTerm

is not written N:N because that notation would imply that there are the same number of entities on
each side of the relationship, which is not necessarily true. N:M means that more than one entity is
allowed on each side of the relationship and that the number of entities on each side can be different.
Figure 5-21 shows the same entities with different assumptions. Here, advisers may advise in
more than one department, but a student may have only one adviser, representing a policy that
students may not have multiple majors.
Which, if either, of these versions is correct? Only the users know. These alternatives illustrate
the kinds of questions you will need to answer when a database designer asks you to check a data
model for correctness.
Figures 5-20 and 5-21 are typical examples of an entity-relationship diagram. Unfortu-
nately, there are several different styles of entity-relationship diagrams. This one is called, not
surprisingly, a crow’s-foot diagram version. You may learn other versions if you take a database
management class.
The crow’s-foot notation shows the maximum number of entities that can be involved in a
relationship. Accordingly, they are called the relationship’s maximum cardinality. Common
examples of maximum cardinality are 1:N, N:M, and 1:1 (not shown).
Another important question is “What is the minimum number of entities required in the
relationship?” Must an adviser have a student to advise, and must a student have an adviser?
Constraints on minimum requirements are called minimum cardinalities.
Figure 5-22 presents a third version of this E-R diagram that shows both maximum and mini-
mum cardinalities. The second vertical bar on the lines means that at least one entity of that type is
required. The small oval means that the entity is optional; the relationship need not have an entity
of that type. Using this notation, if there are two vertical bars, both the minimum and maximum
cardinality are one. If there is a vertical bar with a crow’s foot, then the minimum cardinality is
one and the maximum is many.
Thus, in Figure 5-22 a department is not required to have a relationship to any adviser, but an
adviser is required to belong to a department. Similarly, an adviser is not required to have a rela-
tionship to a student, but a student is required to have a relationship to an adviser. Note, also, that
the maximum cardinalities in Figure 5-22 have been changed so that both are 1:N.
Is the model in Figure 5-22 a good one? It depends on the policy of the university. Again, only
the users know for sure.

Q5-6 How Is a Data Model Transformed

into a Database Design?

Database design is the process of converting a data model into tables, relationships, and data con-
straints. The database design team transforms entities into tables and expresses relationships by
defining foreign keys. Database design is a complicated subject; as with data modeling, it occupies

Department Adviser Student
DeptName AdviserName StudentNumber
Admin Phone StudentName
Figure 5-22 Phone CampusAddress HW1
Sample Relationships Showing Email EmailAddress HW2
Both Maximum and Minimum MidTerm
Cardinalities