Advanced Normalization

List the primary key and functional dependencies in the following table, subject to the specified conditions. Convert this table to an equivalent collection of tables that are in third normal form (3NF).

INVOICE

INVOICE NUMBER

CUSTOMER_NUMBER

LAST_NAME

FIRST_NAME

STREET

CITY

STATE

ZIP_CODE

INVOICE_DATE

PARTNUMBER

PARTDESCRIPTION

UNIT_PRICE

NUMBER_SHIPPED

This table concerns invoice information. For a given invoice (identified by the invoice number) there will be a single customer. The customer's number, name, and address appear on the invoice as well as the invoice date. Also, there may be several different parts appearing on the invoice. For each part that appears, the Part Number, Part Description, Unit Price, and Number Shipped will be displayed. The price is from the current master price list.

Using your knowledge of a college environment, determine the primary key and functional dependencies that exist in the following table. After these have been determined, convert this table to an equivalent collection of tables that are in 3NF.

STUDENT

STUDENT_NUMBER

STUDENT_NAME

NUMBER_CREDITS

ADVISOR_NUMBER

ADVISOR_NAME

DEPT_NUMB

DEPT_NAME

COURSE_NUMBER

COURSE_DESCRIPTION

COURSE_TERM

GRADE

For both questions, you will need to submit:

1. The attribute or, in the case of composite keys the attributes, that would most likely be used as the primary key.
2. A listing of all the functional dependencies among the attributes listed in the initial table.
3. A three-stage set depicting the normalization process, showing the tables after first, second, and third normal form.