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