Question

Answer the following questions:

Question 1: Using your knowledge of the college environment, determine the functional dependencies that exist in the following table. After determining the functional dependencies convert this table to an equivalent collection of tables that are in third normal form.

Student (StudentNum, StudentName, NumCredits, AdvisorNum,AdvisorName,

DeptNum, DeptName, AdvisorNum, AdvisorName, DeptNum, DeptName, (CourseNum, Description, Term, Grade))

: Draw an ERD for the case given in Question 1.

Answer 1

The functional dependency exists in the Student table:

StudentNum ->StudentName, NumCredits, AdvisorNum

AdvisorNum ->AdvisorName, DeptNum, DeptName

CourseNum -> Description

StudentNum, CourseNum ->Term, Grade

To convert table to 3NF, the below condition applies:

1.Convert the table to 1NF, by identifying primary key and removing grouped data.

Student (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName, DeptNum,DeptName, CourseNum, Description, Term, Grade)

2. Convert 1NF to 2NF by removing partial dependencies and create a table for set of primary keys:

Student (StudentNum, StudentName, NumCredits, AdvisorNum, AdvisorName, DeptNum,DeptName)

Course (CourseNum, Description)

StudentGrade (StudentNum, CourseNum, Grade)

3. Covert 2NF to 3NF by removing transitive dependency.

Table Student is not in 3NF, because it has a determinant(Advisornum) that is not a candidate key.So we need to remove column dependent on Advisornum.

Student(StudentNum, StudentName, NumCredits, AdvisorNum)

Advisor(AdvisorNum, AdvisorName, DeptNum,DeptName)

So the Final Answer is:

Student (StudentNum, StudentName, NumCredits, AdvisorNum)

Advisor (AdvisorNum, AdvisorName, DeptNum, DeptName)

Course (CourseNum, Description)

StudentGrade (StudentNum, CourseNum, Description, Term, Grade)

ER Diagram: