Want to make sure I understand a question about normalization. I have read about and seem to understand it in my mind but a bit confused on question.
Choose a key and write the dependencies for the following relation:
GRADES (StudentID, Course#, Semester#,Grade)
In what normal form is this relation.
From what I understand above: I would need to have StudentID,Course#,Semsester ==>Grade
The first 3 have to be the composite key or the table would have duplicate data.
I know a student can retake a course (my own experience) and get a grade of "W" then have to take the course again but another semester.
example:
1234, Course345, SemsesterFall2003, W
1234, Course345, SemsesterSpring2004, A
What is stupid about this excercise to me is:
1) THere is not student name here
2) no Course description
3) no Professor that teaches the course
Due to above I would assume GRADES is 1 relation of many wihtin larger database.
Thus GRADES with PRIMARY KEY: StudentID,Course#,Semsester
FUNCTIONAL DEPENDENCY OF GRADES ON THOSE = 3rd normal form.
Does this make sense Or am I really not getting it?
Upon reading your original question again, it seems to me that you are getting it. The answers you propose are correct... the GRADES table is in 3rd normal form (no unnecessary redundancy in the table), it will have a composite primary key of StudentId, Course#, Semester#, and every row must have a unique trio of values.
If the GRADES table included the following:
StudentId, FirstName, LastName, Course#, CourseName, Semester#, Grade
It would not be fully normalized. The fields FirstName, LastName, and CourseName should not appear in this table, because they are identical for each unique occurrence of <StudentId> and <Course#>, respectively. Instead, the student's name should appear in the Student table, only once, with the <StudentId> linking all of the student's data back to the Student table.
You are concerned that these details are not in the GRADES table, since this means that the GRADES table is insufficent for painting a complete and meaningful picture of the student, course, and his grades. However, when pulling data, a simple JOIN to the Student table and Course table will bring all relevant details together. Example:
select s.SSN, s.FirstName, s.LastName, g.Semester#, g.Course#, c.CourseName, g.Grade
from GRADES g
join Student s
on g.StudentId = s.StudentId
join Course c
on g.Course# = c.Course#
order by s.SSN, g.Semester#, g.Course#
This query would present a full listing of all students, their courses, and grades, sorted by SSN, Semester, and Course. Notice that we can leave the Student# out of the final output, only using it as a foreign key to pull the data together.
I hope this helps.
-- Brian