DATA MODELING - Fundamentals of Database Management Systems - page 35

2.3 M ODELING Y OUR W ORLD— P ART 3

YOUR

TURN

N ow it's time to put

the university

Q UESTION :
Create one comprehensive E-R diagram for your university
environment that you developed in Your Turn Parts 1
and 2.

environment all together.

BOOK and AUTHOR that is realized in the associative entity WROTE, which has
no intersection data. The company wants to keep track of which authors wrote
which books, but there are no attributes that further describe that many-to-many
relationship. The associative entity SALE indicates that there is a many-to-many
relationship between books and customers. A book can be involved in many sales
and so can a customer. But a particular sale involves just one book and one customer.
Date, Price, and Quantity are intersection data in the many-to-many relationship
between the BOOK and CUSTOMER entities.
Does this make sense? Might a customer have bought several copies of the
same book on the same date? After all, that's what the presence of the Quantity
attribute implies. And might she have then bought more copies of the same book on
a later date? Yes to both questions! A grandmother bought a copy of a book for each
of three of her grandchildren one day and they liked it so much that she returned and
bought five more copies of the same book for her other five grandchildren several
days later. By the way, notice that the modality 0 going from book to sale says
that a book may not have been involved in any sales (maybe it just came out). The
modality of 1 going from customer to book says that for a person to be considered
a customer, he must have participated in at least one sale, which is reasonable.

EXAMPLE: WORLD MUSIC ASSOCIATION

The World Music Association (WMA) is an organization that maintains information
about its member orchestras and the recordings they have made. The WMA
E-R diagram in Figure 2.11 shows the information about the orchestras and their
musicians across the top and the information about the recordings in the rest of
the diagram. Each orchestra has at least one and possibly many musicians. (In this
case, the modality expressing ''at least one'' is a technicality. Certainly an orchestra
must have many musicians.) A musician might not work for any orchestra (perhaps
she is currently unemployed but WMA wants to keep track of her anyway) or may
work for just one orchestra. A musician may not be a college graduate or may have
several college degrees. A degree belongs to just one musician (for the moment we
ignore the possibility that more than one musician earned the same degree from
the same university in the same year). Since the DEGREE entity is dependent on
the MUSICIAN entity, the unique identifier for DEGREE is the combination of the
Musician Number and Degree (e.g. B.A.) attributes.
Looking downward from the ORCHESTRA entity box, an orchestra may
have made no recordings of a particular composition or may have made many. In
the reverse direction, a composition may not have been recorded by any orchestra