Defining the Data Sets

Once you have added the necessary key attributes that support the relationships, you have completed the majority of the work in designing the TurboIMAGE/XL database. Before the database can be completed, paths, a distinction of TurboIMAGE/XL, must be defined.

Paths are fixed relationships between entities that provide fast access to data and define referential integrity. Relationships are defined by designating entities as Master Data Sets and Detail Data Sets, and then linking them with paths.

Note that the Composers and Albums entities each have only one key item, whereas the other entities have more than one. A Master set, by definition, has one key item, whereas a Detail set may have up to sixteen. A Detail set contains one or more entries linked to a Master entry where the key values agree.

For example, Album Code 0019 is assigned to the Beethoven's hits album mentioned before. This information is in the Albums entity. There are two selections on the album:

These entries are in the Selections entity.

If a query is made for Album Code 0019 against Albums, one entry is returned. There should only ever be one entry returned. If the same Album Code is requested from Selections, two entries are returned, equal to the number of selections on the album.

Albums, then, is a candidate for being a Master data set and Selections will be a Detail data set. Selections also has a second key, Composer Name, a condition that forces it to be a Detail set.

One other distinction within TurboIMAGE/XL before continuing with our design: Master sets are either Manual Masters or Automatic Masters. Each has a single key, but Automatic Masters can contain no other attributes. Manual Masters can have other attributes per entry.

Also, Automatic Master key values are added and deleted automatically; their function is to provide fast access paths to data. Manual Master key values and their associated attributes must be added and deleted explicitly, thereby supporting the referential integrity concept discussed earlier and providing fast access to data.

The TurboIMAGE/XL database designed so far is shown in Figure 4-1 “Database Design”.

Figure 4-1 Database Design

Composers and Albums are Manual Master data sets, Selections and Log are Detail data sets. Because Selection Name is a key value in both Selections and Log, a supporting Automatic Master was created to supply the keyed access to these two entities.

Before you see the schema to create this database, a final design consideration must be addressed: security.