7 - Data Modeling

Answers to exercises

Short Answers

What is data modeling?

Data modeling is both a technique for modeling data and its relationships and a graphical representation of a database. It is a method for determining what data and relationships should be stored in the database. The data model is not concerned with how the data should be stored.

When do you label relationships?
Typically, relationship arcs should not be labeled. Only when there are two relationships between two entities or if the relationship is not easily inferred should you label the arc.
How do you handle exceptions when data modeling?

Exceptions should be handled by the data model. Actually, it is recommended to go "hunting for exceptions" to avoid a low-fidelity data model. One should ask the client questions such as:

Why might a data model grow?

A data model might grow as you expand the boundaries of the application. Some 1:m relationships will become m:m relationships and you will add entities, attributes, and relationships to faithfully describe the real world.

How do you indicate ordering of instances in a data model?

To handle ordering of the instances in a database, create a new attribute which contains the order of the entity. This way one does not have to store the rows in order and it is also possible to create two or more orders for each entity. The SQL clause ORDER BY can be used to print out the rows in order.

Create a data model from the following narratives, which are sometimes intentionally incomplete. You will have to make some assumptions. Make certain you state these alongside your data model. Define the identifier(s) and attributes of each entity.

The president of a book wholesaler has told you that she wants information about publishers, authors, and books.
Kids'n'Vans retails minivans for a number of manufacturers. Each manufacturer offers several models of its minivan (e.g., SE, LE, GT). Each model comes with a standard set of equipment (e.g., the Acme SE comes with wheels, seats, and an engine). Minivans can have a variety of additional equipment or accessories (radio, air conditioning, automatic transmission, airbag, etc.), but not all accessories are available for all minivans (e.g., not all manufacturers offer a driver's side airbag). Some sets of accessories are sold as packages (e.g., the luxury package might include stereo, six speakers, cocktail bar, and twin overhead foxtails).

A university gymnastics team can have as many as 10 gymnasts. The team competes many times during the season. A meet can have one or more opponents and consists of four events: vault, uneven bars, beam, and floor routine. A gymnast can participate in all or some of these events though the team is limited to five participants in any event.
A medical center employs several physicians. A physician can see many patients, and a patient can be seen by many physicians though not always on the one visit. On any particular visit, a patient may be diagnosed to have one or more illnesses.
Global Trading (GT), Inc. is a conglomerate. It buys and sells businesses frequently and has difficulty keeping track of what strategic business units (SBUs) it owns, in what nations it operates, and what markets it serves. For example, the CEO was recently surprised to find that GT owns 25 percent of Dundee's Wild Adventures, headquartered in Zaire, that has subsidiaries operating tours of Australia, Zaire, and New York. You have been commissioned to design a database to keep track of GT's businesses. The CEO has provided you with the following information: SBUs are headquartered in one country, not necessarily the United States. Each SBU has subsidiaries or foreign agents, depending on local legal requirements, in a number of countries. Each subsidiary or foreign agent operates in only one country but can operate in more than one market. GT uses the standard industrial code (SIC) to identify a market (e.g., newspaper publishing). The SIC is a unique four-digit code. While foreign agents operate as separate legal entities, GT needs to know in what countries and markets they operate. On the other hand, subsidiaries are fully or partly-owned by GT, and it is important for GT to know who are the other owners of any subsidiary and what percentage of the subsidiary they own. It is not unusual for a corporation to have shares in several of GT's subsidiary companies and for several corporations to own a portion of a subsidiary. Multiple ownership can also occur at the SBU level.
Draw a data model for the following table taken from a magazine that evaluates consumer goods. The reports follow a standard fashion of listing a brand and model, price, overall score, and then an evaluation of a series of attributes, which can vary with the product. For example, the sample table evaluates stereo systems. A table for evaluating microwave ovens would have a similar layout, but different features would be reported (e.g., cooking quality).
Brand and model Price Overall
of use
Phillips SC-AK103 140 620 Very good Good Very good Excellent Fair
Panasonic MC-50 215 55 Good Good Very good Very good Good
Rio G300 165 38 Good Good Fair Very good Poor

This page is part of the promotional and support material for Data Management (sixth edition) by Richard T. Watson
For questions and comments please contact the author

Date revised: 19-Oct-2016