HP 3000 Manuals HP 3000 Manuals
Identifying Relationships Between Entities
After subdividing the data by entities, the next step of the design is to
identify meaningful relationships between the entities described so far.
For each relationship you identify, an attribute or group of attributes
must support the relationship. This may mean adding one or more
attributes to support the relationship.
What are the relationships among the entities in the sample data? For
the Albums and Selections entities, there is a relationship of content;
that is, each album contains a specific group of selections. For the
Composers and Selections entities, the relationship is one of authorship;
each composer has created one or more selections.
For each relationship defined, linking attributes are added to related
entities. In the case of Selections, the link is Selection Name. In the
case of Selections and Composers, the link is Composer Name. Where the
attribute is missing, it must be added to define the relationship.
After distributing the attributes, the next step is to identify those
attributes which will serve as keys to any unique occurrence of an
entity. It may be necessary to create attributes to clarify certain
ambiguities. For example, two albums could have the same name. This
situation is clarified by creating an AlbumCode attribute that will serve
as the key to that entity. You can now see a set of relational tables
emerging, as follows (* indicates a key column):
Columns and Keys for Four Tables
-------------------------------------------------------------------------------------------------
| | | | |
| Albums Table | Selections Table | Composers Table | Station Log Table |
| | | | |
-------------------------------------------------------------------------------------------------
| | | | |
| *AlbumCode | *AlbumCode | *ComposerName | *AlbumCode |
| | | | |
| AlbumTitle | *SelectionName | Birth | *SelectionName |
| | | | |
| Medium | *ComposerName | Death | StartTime |
| | | | |
| AlbumCost | Duration | Birthplace | EndTime |
| | | | |
| RecordingCo | Performers | Comment | Announcer |
| | | | |
| DateRecorded | | | |
| | | | |
| MfgCode | | | |
| | | | |
| Comment | | | |
| | | | |
-------------------------------------------------------------------------------------------------
Some entities contain a unique occurrence for the key values, other
entities will have multiple occurrences for a key value. For example,
the composer Beethoven will exist once in the Composers entity. There
can be, however, many selections by this composer. Also, a selection,
Beethoven's Fifth Symphony, could be on several albums performed by
different orchestras.
From a relationship point of view, this is called referential integrity,
where an occurrence of information in one entity is required to support
the existence of information in another entity. To demonstrate
referential integrity, suppose the station receives a new album
containing Beethoven's greatest symphonies as performed by the San
Francisco Symphony Orchestra. This album is entered into the Albums
entity. Each selection from the album is added to the Selections entity,
which includes the attribute Composer Name. A relationship exists
between Selections and Composers based upon this attribute. For the data
to be complete, an entry for Beethoven must be added in the Composers
entity. Once this is done, referential integrity has been established.
Without it, the referential model is incomplete.