Notes on ER Diagrams

Here are the ER diagram to English notes we gave out in class ..
Our next exercise is to convert our ER diagrams into English to close
the loop in the requirements/specification phases ..the notes:

To relieve any ambiguity in the statement of the relationship, we will take
the English statement from the relationship as we have illustrated and
define eight pattern possibilities for expressing a side of a relationship.
 The grammatical expressions are classified as "short" (meaning colloquial)
and "long" (purporting to be clearer):

Pattern 1 - 1:1 from the 1 side, full participation
Short: All x are related to one y
which actually means:
Long: All x, which are recorded in the database, are related to one and
only one y.  No x is related to more than one y.

Example: All students are advised by one advisor, which means:

All students, which are recorded in the database, are advised by one and
only one advisor.  No student is advised by more than one advisor.

The phrase, "which are recorded in the database," has proven to be helpful
because database designers tend to generalize beyond the problem at hand. 
For example, one could reasonably argue that there might be a case where
thus-and-so are not true, but the point is, "will that case ever be
encountered in this particular database?"  The negative statement is
helpful to solidify the meaning of the relationship.

Pattern 2 - 1:1 from the 1 side, partial participation
Short: Some x are related to one y
which actually means:
Long: Some x, but not necessarily all x, (which are recorded in the
database) are related to one and only one y.  Some x's are not related to a
y. X's may not be related to more than one y.

Example:  Some students join a fraternity, which becomes:

Some students, but not necessarily all students (which are recorded in the
database) join a fraternity.  Some students do not join a fraternity. 
Students may not join more than one fraternity.

Pattern 3 - 1:M from the 1 side, full participation
Short: All x are related to many y
which actually means:
Long: All x, which are recorded in the database, are related to many (one
or more) y's.  No x is related to a non y (or) Non x are not related to a
y.
(The negative will depend on the sense of the statement).

Example: All automobiles are driven by (registered to) many students, which
means:

All automobiles, which are recorded in our database, are driven by
(registered to) many (one or more) students.  No automobile is registered
to  a non-student.

There are several ideas implied in these statements.  First, we are only
talking about cars which are registered at this school.  Second, in this
database, only student cars are registered.  Third, if an auto from this
database is driven, it has to be registered and driven by a student. 
Fourth, there is a strong temptation to say something about the y, the M
side, but this should be avoided as this is covered elsewhere in another
pattern and because we discourage inferring other relationships from the
one covered.  For example, one might try to say here that all students
drive cars or all students are related to a car .. neither statement is
true.

Pattern 4 - 1:M from the 1 side, partial participation
Short: Some x are related to many y
which actually means:
Long: Some x, but not necessarily all x, (which are recorded in the
database) are related to many (one or more) y's. Some x are not related to
a y.

Example: Some courses require (use) many books, which restated becomes:

Some courses, but not necessarily all courses, (which are recorded in the
database) use many (one or more) textbooks.  Some courses do not require
textbooks.

Pattern 5- M:1 from the M side, full participation
Short: All x are related to one y
which actually means:
Long: All x, which are recorded in the database, are related to one and
only one y.  No x are related to more than one y.

Example: All students live in one dorm room.  Here, x = student, y = dorm
room and the relation is "live in."  The relationship from the student
viewpoint means:

All students, which are recorded in our database, live in one and only one
dorm room.  No student lives in more than one dorm-room.  (All students
live on campus in this example).

Pattern 6 - M:1 from the M side, partial participation
Short: Some x are related to one y
which actually means:
Long: Some x, but not necessarily all x, (which are recorded in the
database) are related to one and only one y.  Some x are not related to y. 
No x is related to more than one y.

Example: Some students register automobiles, which resolves into:

Some students, but not necessarily all students (which are recorded in the
database) register one and only one automobile.  Some students do not
register automobiles.  No student registers more than one automobile.

Pattern 7 - M:N from the M side, full participation
Short: All x are related to many y.
which actually means:
Long: All x, which are recorded in the database, are related to many (one
or more) y.  No x is related to a non y (or) Non x are not related to a y.
(The negative will depend on the sense of the statement).

Example: All students take courses, which resolves into:

All students, which are recorded in the database, take many (one or more)
courses.  Non-students do not take courses.

Pattern 8 - M:N from the M side, partial participation
Short: Some x are related to many y.
which actually means:
Long: Some x, but not necessarily all x, (which are recorded in the
database) are related to many (one or more) y.  Some x may not be related
to y.

Example: Some students play many sports, which means:

Some students, but not necessarily all students, (which are recorded in the
database) play one or more sports.  Some students may not play sports.

If a relationship is modeled with the patterns we have used and then the
English sounds incorrect, it is likely that the wrong model has been
chosen.  Generally, the grammatical expression will be most useful in (a)
restating the designed database to a naive user, and (b) checking the
meaning on the designed database among the designers.  The complete version
of the long form will eventually prove tiresome to a database designer, but
one should never lose track of the fact that a statement like "All x are
related to one y" can be interpreted in several ways unless it is "nailed
down" with constraints stated in an unambiguous way.  Furthermore, the
negation  statement is often useful to elicit requirements definition.


Dr. E's home page