View Single Post
  #2 (permalink)  
Old April 3rd, 2014, 01:02 AM
Rod Stephens's Avatar
Rod Stephens Rod Stephens is offline
Wrox Author
Points: 3,187, Level: 23
Points: 3,187, Level: 23 Points: 3,187, Level: 23 Points: 3,187, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jan 2006
Location: , , .
Posts: 646
Thanks: 2
Thanked 96 Times in 95 Posts

Q1 : Why is Instructors.InstructorID not connected to CourseOfferings.InstructorID. Isn't it a foreign key relationship
That's just an oversight. You're right, InstructorID should connect those tables.

Q2 : Why don't you create a look up table for GradeItem
Do you mean have a table with values like "Quiz1" and "Midterm" in it so you can validate the GradeItem values in StudentGrades? You could do that, but it would restrict the instructors so they couldn't create any kind of grade item that they wanted. Like a pop quiz or a new paper that wasn't previously listed.

But there does need to be a relationship between CourseGradeItems.GradeItem and StudentGrades.GradeItem to ensure that a student's GradeItems are allowed for a particular course. And there a simple lookup table probably wouldn't quite do the job because different courses will have different allowed grade items.

That's why I thought this should probably be implemented in a business rule. The model just can't do a good job representing the chain:
  • StudentGrades.GradeItem -->
  • StudentGrades.CourseOfferingId -->
  • CourseOfferings.CourseOfferingId -->
  • CourseOfferings.CourseId -->
  • Courses.CourseId -->
  • CourseGradeItems.GradeItem

But I'm certainly open to suggestions. There are often multiple ways to design for a particular application. (And I can't even promise that every design in the book is perfect. Your first question is proof of that. )

Q3 : StudentCourses has Grade field. StudentGrades has Score field. What is the difference between StudentCourses and StudentGrades
If I remember correctly (and it's been a while), I think StudentCourses is supposed to represent the student taking a course, like Math 210. So the grade in StudentCourses would be the overall grade for the course, like an A or B-.

StudentGrades is supposed to represent a single score on a particular grade item such as a paper or quiz. So there would usually be many StudentGrades records corresponding to a single StudentCourses record. For example, you might have 10 quizzes and a final in Math 210 (StudentGrades records) but only one final grade for the class (StudentCourses record).

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
The Following User Says Thank You to Rod Stephens For This Useful Post:
frdata (May 8th, 2014)