Travel Agency Data Model

The diagram below shows some key elements of this sample application. This particular diagram was made in USoft URequire Studio.

This particular diagram shows:

•Tables.

•Primary keys - indicated by a key icon .

•Other mandatory columns  - indicated by a red rectangle.

•Non-mandatory columns - indicated by a green rectangle.

•Relationships (Parent-to-child).

•Role names, e.g. GUIDES, FOR WHICH ARE MADE.

•Cardinality, e.g. 1-to-1 relationship or 1-to-many relationship.

•Supertypes and subtypes.

Among other things, the diagram does NOT show or identify.

•Which columns are foreign key in a relationship.

•Co-role names, e.g. IS GUIDED BY, MADE FOR.

•Domains, domain characteristics.

•Constraints.

The diagram in this help topic shows cardinality properties of Relationships by symbols at the line ends of the relationship symbols:

The diagram also shows the role that the PARENT plays (eg., Reservation IS DEALT WITH BY Staff), but not the co-role that the CHILD plays (eg., Staff DEALS WITH Reservation).

Relationships have other interesting properties in addition to cardinality properties, roles and co-roles:

•A Relationship may have Restricted, Cascading or Nullify behavior on DELETE. This is determined by its Delete Rule attribute.

•A Relationship may have Restricted, Cascading or Nullify behavior on UPDATE. This is determined by its Update Rule attribute.

There is a Table in the model (the DISCOUNT table) that does not have any standard Relationships with any other tables. The relationship between DISCOUNT data and data in other tables is such that it cannot be expressed by parent-child relationships. Instead, it is defined by Constraints.

In the Travel Agency example, GUIDE and STAFF are subtypes of PERSON:

The diagram in this help topic does not show all the interesting properties of these supertype-subtype relationships. For example, it does not show two aspects that may be specified in USoft Definer by defining Subtype Definitions:

•Whether it is possible for a person to be a Guide and a member of Staff at the same time.

•That it is possible for a person to be neither a Guide not a member of Staff. This can be guessed indirectly from other Relationships in the diagram: these show that people make Reservations and take part as Participants, and these activities are probably performed by other people than (just) Guides and Staff.

The diagram seems to imply that GUIDE, STAFF and PERSON are 3 physical database tables but alternatively, using the Create Separate Tables attribute in USoft Definer, you could decide to have all the supertype and subtype data in a single database table.

Both the GUIDE and the STAFF subtype have a BANK_ACCOUNT. If this is precisely the same type of bank account, then a better model is:

with BANK_ACCOUNT as a column of the interposed EMPLOYEE table.