Suppose you are given the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club’s members, to plan the meals, to keep track of who attends the dinners, and so on.

Each dinner serves many members, and each member may attend many

A member receives many invitations, and each invitation is mailed to many members.

A dinner is based on a single entree, but an entree may be used as the basis for many For example, a dinner may be composed of a fish entree, rice, and corn. Or the dinner may be composed of a fish entree, a baked potato, and string beans.

Because the manager is not a database expert, the first attempt at creating the database uses the structure shown in Table P6.7:

Table P6.7 Sample RESERVATION Records

a. Given the table structure illustrated in Table P6.7, write its relational schema and draw its dependency diagram. Label all transitive and/or partial dependencies. (Hint: This structure uses a composite primary key.)

The relational schema may be written as follows:

MEMBER(MEMBER_NUM, MEMBER_NAME, MEMBER_ADDRESS, MEMBER_CITY, MEMBER_ZIP_CODE, INVITE_NUM, INVITE_DATE, ACCEPT_DATE, DINNER_DATE, DINNER_ATTENDED, DINNER_CODE, ENTRÉE_CODE, ENTRÉE_DESCRIPTION, DESSERT_CODE, DESSERT_DESCRIPTION)

The dependency diagram is shown in Figure P6.7a. Note that DIN_CODE in Figure P6.7a does not determine DIN_ATTEND; just because a dinner is offered does not mean that it is attended. Note also that we have shortened the prefixes – for example, MEMBER_ADDRESS has been shortened to MEM_ADDRESS -- to provide sufficient space to include all the attributes.

b. Break up the dependency diagram you drew in Problem 7a to produce dependency diagrams that are in 3NF and write the relational schema. (Hint: You might have to create a few new attributes. Also, make sure that the new dependency diagrams contain attributes that meet proper design criteria; that is, make sure that there are no multivalued attributes, that the naming conventions are met, and so on.)

Actually, there is no way to prevent the existence of multi-valued attributes by merely following normalization rules. Instead, knowledge of E-R modeling concepts will help define the environment in which the multi-valued attributes are dealt with. Although we keep repeating the message, it is worth repeating: normalization and modeling fit within the same design spectrum and they take place concurrently as the definition of entities and their attributes take place.

The design process can be described thus:

• Define entities, attributes, and relationships and model them.
• Normalize.
• Redesign based on the normalization outcomes and the evaluation of the design's ability to meet transaction and information requirements.
• Normalize the results and evaluate the normal forms until the process has yielded a stable design, implementation, and applications development environment.

Such a process will yield the dependency diagrams shown in Figure P6.7b. In this case, it hardly seems practical to eliminate the 2NF condition displayed by MEMBER. After all, zip codes tend to be thought of as part of the address. Worse, the elimination of the MEMBER's 2NF condition would require the creation of a ZIPCODE table, with ZIP_CODE as the foreign key in the MEMBER table. Such a solution would merely add complexity without adding functionality.

Figure P6.7b The Dependency Diagram for Problem 7b

As you examine Figure P6.7b, note how easy it is to see the functionality of the decomposition. For example, the (composite) INVITATION and DINNER entities make it possible to track who was sent an invitation on what date (INVITE_DATE) to a dinner to be held at some specified date (DIN_DATE), what dinner (DIN_CODE) would be served on that date, who (MEM_NUM) accepted the invitation (INVITE_ACCEPT), and who actually attended (INVITE_ATTEND. The INVITE_ACCEPT attribute would be a simple Y/N, as would be the INVITE_ATTEND. To avoid nulls, the default values for INVITE_ACCEPT and INVITE_ATTEND could be set to N. Getting the number of acceptances for a given dinner by a given date would be simple, thus enabling the catering service to plan the dinner better.

The relational schemas follow:

MEMBER(MEM_NUM, MEM_NAME, MEM_ADDRESS, MEM_CITY, MEM_STATE, MEM_ZIP)

INVITATION(INVITE_NUM, INVITE_DATE, DIN_CODE, MEM_NUM, INVITE_ACCEPT, INVITE_ATTEND)

ENTRÉE(ENT_CODE, ENT_DESCRIPTION)

DINNER(DIN_CODE, DIN_DATE, DIN_DESCRIPTION, ENT_CODE, DES_CODE)

DESSERT(DES_CODE, DES_DESCRIPTION)

Naturally, to tracks costs and revenues, the manager would ask you to add appropriate attributes in DESSERT and ENTRÉE. For example, the DESSERT table might include DES_COST and DES_PRICE to enable the manager to track net returns on each dessert served. One would also expect that the manager would want to track YTD expenditures of the members and, of course, there would have to be an invoicing module for billing purposes. And what about keeping track of member balances as the members charge meals and make payments on account?

c. Using the results of Problem 7b, draw the Crow’s Foot ERD.

 The Crow’s Foot ERD is shown in Figure P6.7c.

Figure P6.7c The Crow’s Foot ERD for Problem 7c