Attached is a copy of the assignment
Actual Assignment: The Following is an example of how the assignment should be done: Question: Convert the following table to third normal form. Student (StudentNum (key), StudentName, NumCredits, AdvisorNum, AdvisorName, (CourseNum, Description, Grade) ) where (CourseNum, Description, Grade) is a repeating group and given the following functional dependencies: StudentNum determines StudentName, NumCredits, AdvisorNum, AdvisorName. AdvisorNum determines AdvisorName. CourseNum determines Description. The combination of StudentNum and CourseNum determines Grade. Answer: Step 1. The relation is not in 1NF because it contains repeating groups. Convert to 1NF by making the determinant of the repeating group a part of the composite key: Student (StudentNum(key), StudentName, NumCredits, AdvisorNum, AdvisorName, CourseNum(key), Description, Grade) This relation is now in 1NF because it has no repeatin groups. FD1: StudentNum -> StudentName, NumCredits, AdvisorNum, AdvisorName. FD2: AdvisorNum -> AdvisorName. FD3: CourseNum -> Description. FD4: StudentNum, CourseNum->Grade. It violates 2NF because it contains partial dependency : i.e. StudentName is dependent only on StudentNum, which is only a portion of the primary key. Step 2. Convert 1NF to 2 NF by creating a relation for each subset of the primary key. Composite key (superkey) maintains relationship to each of its components (subkeys) as foreign key . Place the non-key attributes with their determinants, and assign names to newly created relations: Student (StudentNum(key), StudentName, NumCredits, AdvisorNum, AdvisorName) Course (CourseNum(key), Description) StudentCourse (StudentNum(key)(fk), CourseNum(key)(fk), Grade) Course and StudentCourse are in 2NF, and also in 3NF. The Student relation violates 3NF, because it contains a transitive dependency: a determinant AdvisorNum is not a candidate key. Step 3. Convert Student from 2NF to 3NF by splitting the relation that causes the problem: copy the determinant AdvisorNum and moving the attributes that depends on the determinant AdvisorNum and placing both in a separate table: (StudentNum (key), StudentName, NumCredits, AdvisorNum(fk)) (AdvisorNum(key), AdvisorName) Step 4. Put the entire schema together, giving the following: Student (StudentNum(key), StudentName, NumCredits, AdvisorNum(fk)) Advisor (AdvisorNum(key), AdvisorName) Course (CourseNum(key), Description) StudentCourse (StudentNum(key)(fk), CourseNum(key)(fk), Grade)