this book, I will be using industry-standard diagrams to illustrate
designs. A critical part of relational data architecture is
understanding a special kind of diagram called an entity relationship
diagram, or ERD. An ERD graphically
captures the entities in your problem domain and illustrates the
relationships among them. Figure 2-2 is the ERD of
the music library database.
Figure 2-2. The ERD for the music library
There are in fact several forms of ERDs. In the style I use in this
book, each entity is indicated by a box with the name of the entity
at the top. A line separates the name of the entity from its
attributes inside the box.
attributes have "PK" after them,
and foreign key attributes have
"FK" after them.
The lines between entities indicate a relationship. At each end of
the relationship are symbols that indicate
what type of relationship it is and whether it is optional or
mandatory. Table 2-4 describes these symbols.
Table 2-4. Symbols for an ERD
The many side of a mandatory
one-to-many or many-to-many relationship
one side of a mandatory one-to-one or one-to-many relationship
The many side of an optional one-to-many or many-to-many relationship
The one side of an optional one-to-one or one-to-many relationship
Our ERD therefore says the following things:
One compact disc contains one or more songs.
One song appears on exactly one compact disc.
One compact disc features one or more artists.
One artist is featured on one or more compact discs.
An artist can optionally be part of one or more artists (bands).
is a logical representation of the music
library. The entities in a logical model are not tables. First of
all, you probably noticed there is no composite entity handling the
relationship between an artist and a compact disc—I have drawn
the relation directly as a many-to-many relationship. Furthermore,
all of the entity names and attributes are in plain English. Finally,
no foreign keys are shown.
BEST PRACTICE: Develop an ERD to model your problem before you create the database.
physical data model transforms the logical data model into the tables
that will be created in the working database. A data architect works
with the logical data model while DBAs (database administrators) and
developers work with the physical data model. You translate the
logical data model into a physical one by adding join tables, turning
domains into database-specific data types, and using table and column
names appropriate to your DBMS. Figure 2-3 shows
the physical data model for the music library as it would be created
Figure 2-3. The physical data model for the music library