Building the CML Data Model

Many of the CML requirements can effectively be viewed as statements that identify the entities and describe how the different entities relate to one another. The CML requirements produce the following collection of statements that represent the primary business processes of the CML. The nouns and verbs form the basic data model. The nouns in each statement are emphasized.

A BORROWER searches library TITLES.

A TITLE has one or more AUTHORS.

An AUTHOR writes one or more TITLES.

A TITLE has one or more SUBJECTS.

A SUBJECT is in one or more TITLES.

A BORROWER submits checkout REQUESTS.

A TITLE can have one or more ITEMS.

An ITEM has a STATUS.

An ADMINISTRATOR fills REQUESTS.

An ADMINISTRATOR adds and changes TITLES.

An ADMINISTRATOR adds and deletes ITEMS

An ADMINISTRATOR adds and deletes AUTHORS.

An ADMINISTRATOR adds and deletes SUBJECTS.

An ADMINISTRATOR assigns authors to TITLES.

An ADMINISTRATOR assigns subjects to TITLES.

From these statements we derive a list of tables (entities) and their relationships (one-to-many or many-to-many). Reviewing the BORROWER and the ADMINISTRATOR tables, we realize that an administrator can also be a borrower and vice versa. The two entities are merged and an attribute is added to the BORROWER table to identify borrowers with administrative privileges. The entities (tables) in the CML database are:

The sets of statements that reflect mirrored relationships — for example, a TITLE has one or more AUTHORS and an AUTHOR writes one or more TITLES — describe a many-to-many relationship. Additional tables are added to the database to resolve all many-to-many relationships. The following CML tables are added:

In addition, many requirements of the CML application are satisfied as attributes are defined for each table. For example, the title, notes, and ISBN attributes in the title table provide the foundation for a comprehensive full-text search of the Fitch & Mather library titles.