Step by Step Guide on Normalizing a Table with Dependencies (ERD)

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 dinners.
• 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 dinners. 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.
• A member may attend many dinners, and each dinner may be attended by many members.

Because the manager is not a database expert, the first attempt at creating the database uses the structure shown in the following table

Attribute Name	Sample Value	Sample Value	Sample Value
MEMBER_NUM	214	235	214
MEMBER_NAME	Alice B. VanderVoort	Gerald M. Gallega	Alice B. VanderVoort
MEMBER_ADDRESS	325 Meadow Park	123 Rose Court	325 Meadow Park
MEMBER_CITY	Murkywater	Highlight	Murkywater
MEMBER_ZIPCODE	12345	12349	12345
INVITE_NUM	8	9	10
INVITE_DATE	23-Feb-2006	12-Mar-2006	23-Feb-2006
ACCEPT_DATE	27-Feb-2006	15-Mar-2006	27-Feb-2006
DINNER_DATE	15-Mar-2006	17-Mar-3006	15-Mar-2006
DINNER_ATTENDED	Yes	Yes	No
DINNER_CODE	DI5	DI5	DI2
DINNER_DESCRIPTION	Glowing Sea Delight	Glowing Sea Delight	Ranch Superb
ENTREE_CODE	EN3	EN3	EN5
ENTREE_DESCRIPTION	Stuffed crab	Stuffed crab	Marinated steak
DESERT_CODE	DE8	DE5	DE2
DESERT_DESCRIPTION	Chocolate mousse with raspberry sauce	Cherries Jubilee	Apple pie with honey crust

Given that structure, 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 for above table

Note:

DIN_CODE in the above Figure 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.

  Break up the dependency diagram you drew in Problem 15 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.)

The Dependency Diagram for above figure as follows

As you examine Figure P5.16, 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?

 Using the results of above diagram, draw the Crow’s Foot ERD.

The Crow’s Foot ERD is shown below