Consider the following user view of a grade report at a university: Student Name
ID: 3907151 • Letter: C
Question
Consider the following user view of a grade report at a university:
Student Name: Student ID:
Semester: Year:
Major: Status:
Course
Number
Course
Title
Professor
Name
Professor Dept.
Grade
…..
……….
………….
………..
……….
Build the required relations and normalize them into 3NF specifying the primary key for each resulting relation. Make your own assumptions.
Student Name: Student ID:
Semester: Year:
Major: Status:
Course
Number
Course
Title
Professor
Name
Professor Dept.
Grade
…..
……….
………….
………..
……….
Explanation / Answer
Normalization of Student Record
To convert an unnormalized record to 1NF, separate the original record into two or more new records, which do not have repeating groups.
Student Record - *StudentID, StudentName, Semester, Year, Major
StudentID is the primary key
Grade Record - *StudentID, *CourseNumber, Course Title, Grade
CourseNumber and StudentID is the primary key of Grade Record
Professor Record- *ProfessorID, ProfessorName, ProfessorDept, *CourseID, CourseTitle
ProfessorID is the primary key
Student Record - is in 2NF
Grade Record - *StudentID, *CourseNumber, Grade
Course Record - *CourseNumber, Course Title
Professor Record- *ProfessorID, ProfessorName, ProfessorDept
To convert a 2NF record to 3NF, separate the 2NF record into new records, which do not have transitive dependence.
(In Student Record, Semester is transitively dependent on the key only via another nonkey item, Major.)
Grade Record - is in 3NF
Course Record - is in 3NF
Professor Record – is in 3NF,
Student Record - *StudentID, Student Name, Major
CourseType Record - *Major, Semester
Professor Record- *ProfessorID, ProfessorName, ProfessorDept
CourseType Record - *Major, Semester