Please answer assignment task 2.

This lab assignment is intended to analyze the issues surrounding the database tables provided, to identify functional dependencies in those tables and to normalize those tables into 3NF relations.

Tables in the Assignment Database

The database includes three tables as shown below:

The STUDENT table

The STUDENT table stores data about each student including name, major, academic department offering the major and academic advisor of the student. You may assume that a student has only one major and one advisor. An advisor may advise multiple students. Note that the class standing of a student is determined by the credit hours. For example, a student is considered a junior if the student has completed between 61 and 90 hours. You may assume the school has the business rule for the classification.

The INVOICE Table

The INVOICE table stores sales data of products and vendors supplying the products. You may assume that any given product is supplied by a single vendor but a vendor can supply many products.

The INVITATION table

The INVITATION table is used by the manager of a company dinner club to mail invitations to the clubs members, to plan the meals, to keep track of who attends the dinners, and so on.

You may assume the following business rules:

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.

Assignment Tasks

You are asked to analyze the design of the given tables in the assignment database and normalize those tables into 3NF tables. You should perform the following tasks for each of the given tables:

1. Determining Candidate Keys and Primary Keys

Determine all candidate keys in the table and decide which one is the primary key. (If there is only one candidate key, it is the primary key by default.)

2. Analyzing Data Redundancy and Modification Anomalies

Discover all redundant data in the table and explain why the data is redundant (e.g., attribute names that need to be repeated).

Identify one insertion anomaly, one update anomaly, and one deletion anomaly in the table. If you cannot identify any data anomalies for a specific operation, you need to provide an explanation. Otherwise, the analysis is considered incomplete.

3. Determining Functional Dependencies

Identify all functional dependencies (FDs) in the table using the textual X -> Y notation.

For each FD you identified, you must state your assumption/business rule in a sentence to support the FD. You may make your own assumptions if the business rules in the previous section are unclear or absent.

4. Normalization

Determine if the table is in 2NF or 3NF and explain your conclusion. (The table is in 1NF by default.)

If the table is already in 3NF, skip the rest of this task.

If the table is not in 3NF, normalize it into 3NF relations based on the FDs you identified. (You will need to normalize into 2NF relations first and then into 3NF. If the last step is wrong, you can still receive partial credit for 2NF.)

For each new table resulting from the normalization, determine the candidate keys and the primary key of the table.

Student Field Name Data Type Description TU NUM STU LNAME STU MAJOR DEPT CODE DEPT NAME DEPT PHONE COLLEGE NAME ADVISOR LNAME ADVISOR_OFFICE ADVISOR_BLDG ADVISOR_PHONE STU GPA STU HOURS STU CLASS Number Text Text Text Text Text Text Text Text Text Text Number Number Text Student number Student name Student major Department code Department name Department phone extension College name Advisor last name Advisor office location Advisor office building Advisor phone extension Student GPA Student credit hours accumulated Student class standing based on credit hours Student Field Name Data Type Description TU NUM STU LNAME STU MAJOR DEPT CODE DEPT NAME DEPT PHONE COLLEGE NAME ADVISOR LNAME ADVISOR_OFFICE ADVISOR_BLDG ADVISOR_PHONE STU GPA STU HOURS STU CLASS Number Text Text Text Text Text Text Text Text Text Text Number Number Text Student number Student name Student major Department code Department name Department phone extension College name Advisor last name Advisor office location Advisor office building Advisor phone extension Student GPA Student credit hours accumulated Student class standing based on credit hours