Banner

 

8 - Normalization and Other Data Modeling Methods

Answers to exercises

Short Answers

1.
What is normalization and what is its goal?

Normalization provides a theoretical base for defining the desirable properties of relations. It is the process for checking the quality of a database design.

2.
How does DK/NF differ from earlier normal forms?

A relation is in domain key/normal form if and only if every constraint on the relation is a logical consequence of the domain constraints and the key constraints that apply to the relation. DK/NF differs from earlier normal forms in that it does not involve the idea of dependency and so far no algorithm has been developed for converting relations to DK/NF.

3.
How do E-R and IDEF1X differ from the data modeling method of this text?

The E-R modeling approach differs from the data modeling approach used in this text in that m:m relationships are not shown as intersection entities. There are some graphical differences: attributes are listed in ovals attached to the entity and relationships are symbolized by a diamond. Otherwise, the modeling technique is basically the same.

The IDEF1X modeling technique is also only slightly different from the modeling technique described in the text: relationships are always labeled and 1:m relationships are symbolized with a dot instead of a crow's foot. Table names are written on top of the entity box, primary keys are separated from the rest of the attributes with a line, and alternate keys and inversion entries are indicated in the model.

Data Modeling

Using normalization, E-R, or IDEF1X, create data models 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.
1.
The president of a book wholesaler has told you that she wants information about publishers, authors, and books.
For the data model according to the book's data modeling approach, see Chapter 7 modeling exercise 1.

E-R diagram:

IDEF1X:

2.
A university has many subject areas (e.g., MIS, Romance languages). Professors teach in only one subject area, but the same subject area can have many professors. Professors can teach many different courses in their subject area. An offering of a course (e.g., Data Management 457, French 101) is taught by only one professor at a particular time.

For the data model according to the book's data modeling approach, see Chapter 7 modeling exercise 2.

E-R diagram:

IDEF1X:

3.
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).

For the data model according to the book's data modeling approach, see Chapter 7 modeling exercise 3.

E-R diagram:

IDEF1X:

4.
Steve operates a cinema chain and has given you the following information: “I have many cinemas. Each cinema can have multiple theaters. Movies are shown throughout the day starting at 11 a.m. and finishing at 1 a.m. Each movie is given a two-hour time slot. We never show a movie in more than one theater at a time, but we do shift movies among theaters because seating capacity varies. I am interested in knowing how many people, classified by adults and children, attended each showing of a movie. I vary ticket prices by movie and time slot. For instance, Lassie Get Lost at 11 a.m. is 50 cents for everyone but is 75 cents at 11 p.m.”
For the data model according to the book's data modeling approach, see Chapter 7 modeling exercise 4.

E-R diagram:

IDEF1X:

5.
A telephone company offers a 10 percent discount to any customer who phones another person who is also a customer of the company. To be eligible for the discount, the pairing of the two phone numbers must be registered with the telephone company. Furthermore, for billing purposes the company records both phone numbers, start time, end time, and date of call.

For the data model according to the book's data modeling approach, see Chapter 7 modeling exercise 8.

E-R diagram:

IDEF1X:

 

This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson
For questions and comments please contact the author
Date revised: 10-Dec-2021