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):

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.