The following databases are currently stored in a single table. Normalize these structures to meet the 3NF requirements to the greatest practical extent. Using the resulting tables, draw an ER diagram.

1. Using the following INVOICE table structure, draw its dependency diagram and identify all dependencies (including all partial and transitive dependencies). You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table uses a composite primary key.)

Attribute name	Sample value
INV_NUM	211347
PROD_NUM	AA_E3422QW
SALE_DATE	06/25/1999
PROD_DESCRIPTION	B&D Rotary sander, 6 in. disk
VEND_CODE	211
VEND_NAME	NeverFail, Inc.
NUMBER_SOLD	2
PROD_PRICE	$49.95

2. Using the following STUDENT table structure, draw its dependency diagram and identify all dependencies (including all transitive dependencies).

Attribute name	Sample value
STU_NUM	211343
STU_LNAME	Stephanos
STU_MAJOR	Plant Sciences
DEPT_CODE	GEN
DEPT_NAME	Genetics
DEPT_PHONE	4356
FACULTY_NAME	Medicine
ADV_LASTNAME	Campbell
ADV_OFFICE	HKB201
ADV_BUILDING	Staging Bldg.
ADV_PHONE	2115
STU_GPA	2.87
STU_HOURS	96
STU_CLASS	Junior

3. The following table structure contains many unsatisfactory components and characteristics. (For example, there are several multi-valued attributes, some naming conventions are violated, some attributes are not atomic, and so on.)

Attribute name	Sample value
EMP_CODE	1003
LAST_NAME	Willaker
EDUCATION	HS, BBA, MBA
DEPT_CODE	MKTG
DEPARTMENT	Marketing
DEPT_MANAGER	Jill H. Martin
JOB_CLASS	23
TITLE	Sales agent
DEPENDENTS	Gerald (spouse), Mary (daughter)
BIRTH_DATE	12/23/65
HIRE_DATE	10/14/94
TRAINING	Level 1, level 2
BASE_SALARY	$32,255

4. Suppose you are given the following business rules that will 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, tossed salad, and mixed vegetables. Or the dinner may be composed of a fish entree, baked potato, vegetable soup, corn, 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 the "database" uses the following structure:

Attribute name	Sample value
MEMBER_NUM	214
MEMBER_NAME	Alice B. VanderVoort
MEMBER_ADDRESS	325 Meadow Park
MEMBER_CITY	Murkywater
MEMBER_ZIPCODE	12345
INVITE_NUM	8
INVITE_DATE	8/1/1999
ACCEPT_DATE	8/9/1999
DINNER_DATE	8/23/1999
DINNER_ATTEND	Y
DINNER_CODE	5
DINNER_DESCRIPTION	Sea Delight
ENTREE_CODE	3
ENTREE_DESCRIPTION	Stuffed crab
DESSERT_CODE	8
DESSERT_DESCRIPTION	Chocolate mousse with raspberry sauce