Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4
This is the forum to discuss the Wrox book Beginning Database Design Solutions by Rod Stephens; ISBN: 9780470385494
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Beginning Database Design Solutions ISBN: 978-0-470-38549-4 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old April 2nd, 2014, 10:58 PM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Default Students,Courses, and Grades

Hello Rod,
I am trying to implement one of the models that you listed in Appendix B. I am using vb6 frontend and Access backend

For the moment, I have three questions based on figure B-16 page 478.

Q1 : Why is Instructors.InstructorID not connected to CourseOfferings.InstructorID. Isn't it a foreign key relationship

Q2 : Why don't you create a look up table for GradeItem

Q3 : StudentCourses has Grade field. StudentGrades has Score field. What is the difference between StudentCourses and StudentGrades

Thank you
Reply With Quote
  #2 (permalink)  
Old April 3rd, 2014, 01:02 AM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

Quote:
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.

Quote:
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. )

Quote:
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

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
The Following User Says Thank You to Rod Stephens For This Useful Post:
frdata (May 8th, 2014)
  #3 (permalink)  
Old April 3rd, 2014, 10:16 AM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Thank you for the reply. Your explanation of the purpose of StudentGrades is very helpful. It gave me new insights about this problem space.

Yes, I agree with you that this design could be improved to make accessing the relationships easier.

I would welcome any suggestions from other viewers to improve on this aspect.

Thank you.
Reply With Quote
  #4 (permalink)  
Old May 9th, 2014, 03:40 AM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hello,

I have searched a lot of places about Student-grades. However I found that your design is more extensive because it includes the StudentGrades capability.

And your CourseOfferings table conforms to 3NF because you moved the CourseOfferingDays in a separate table. Other models I saw were not careful in this aspect and mixed it together. Nice job !

One thing that made me wonder is why does some model name the CourseOfferings as Sections. It is a synonym?

Lastly, would you offer help on the following :

Lets say we already calculate the total marks for a student based on all the quizzes and exams for a CourseOffering.

We would then determine its grade based on a certain criteria.

Let say the criteria is as follows :

90 to 100 A
70 to 89 B
50 to 69 C
40 to 49 D
0 to 39 F

How do we store the grading. Do we store it in a table linked to other tables or store it separately.

Some courses would have different gradings. How would we link it to the present database.

I use vb6 as the front-end and access2007 as the back-end.
Reply With Quote
  #5 (permalink)  
Old May 9th, 2014, 01:07 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

Thanks! I'm glad you're finding the models useful.

Quote:
One thing that made me wonder is why does some model name the CourseOfferings as Sections. It is a synonym?
Can you tell me which model you're looking at? I don't see which one this is.

My guess is one of two things. First, it could just be a typo and I changed the name from Section to CourseOffering but missed it somewhere. In that case, you should treat them as synonyms.

Alternatively where I went to school a single class often had multiple sections. You might go to 1 or 2 lectures per week in a big room with the whole class and then 2 or 3 more smaller sections with teaching assistants. A big class might have 2 lectures with 200 students, and then 10 sections each meeting twice a week with 10-20 students each.

If that's what the model is doing, then there should be a many-to-one relationship between sections and course offerings.

Quote:
We would then determine its grade based on a certain criteria.
...
How do we store the grading. Do we store it in a table linked to other tables or store it separately.

Some courses would have different gradings. How would we link it to the present database.
Good question. The grade software that I've used when I taught VB let you define assignments and give each assignment a category. Then each category gets a weight. For example, quizzes might be worth 25% of your grade, the midterm might be worth 25%, and the final might be worth 50%.

With that approach, you would need:

  • A Categories table giving the category names and weights.
  • An Assignments table giving each assignment's name and category.
  • A StudentAssignments table giving each a student's assignment scores.

Based on that information, you could calculate a student's grade. If you don't create the Assignments entries until they're due (or you give them dates), you can get a grade at any point during the course by looking only at what has been recorded.

In practice, I think most schools don't save all of that information for very long. You might keep a student's full information for a year after the course. After that you might want to move the records into history tables. There you might only store the student's grade A, B, etc. and not all the data about the student's score on the third quiz.

This is data warehousing: You store only what you will need in the future to save space.

I hope that helps. If you tell me which design you're looking at with the CourseOffering and Section entries, I can probably give a more definite answer to that question.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
  #6 (permalink)  
Old May 9th, 2014, 01:56 PM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Hello,

I'm referring to figure B-16 on page 478. CourseOfferings is listed as one of the tables.

I'm sorry that I was not specific enough. Actually this is the continuation of my previous question at the beginning of this thread.

And when I said "I have searched a lot of places about Student-grades" what I meant is I searched for it from the internet.

The Section table is mentioned in one of the models I found in the internet. But it also includes the following fields : Building, Room, MeetingDay, MeetingTime.

Your model is superior compared to the other models I found in the internet.

Your answer is great. But could you please relate the solution you are giving with respect to figure B-16.

Because that was what my question was referring to.

For instance, where does the tables you have given fits in figure B-16.

Quote : "Good question. The grade software that I've used when I taught VB let you define assignments and give each assignment a category. Then each category gets a weight. For example, quizzes might be worth 25% of your grade, the midterm might be worth 25%, and the final might be worth 50%."

Actually I assume all of this is already processed in the figure B-16 (from the Weight field in CourseGradeItems).

My main question is about how to fit in the grading that I listed above in figure B-16 with the assumptions given.


Thanks in advance

Last edited by frdata; May 9th, 2014 at 02:42 PM. Reason: clarify a few things
Reply With Quote
  #7 (permalink)  
Old May 9th, 2014, 02:41 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

Quote:
I'm referring to figure B-16 on page 478. CourseOfferings is listed as one of the tables.
Thanks. I was looking in Appendix A.

I still don't see the reference to sections. (But I have a really bad cold so it's probably staring me in the face and I can't see it.)

Quote:
Your answer is great. But could you please relate the solution you are giving with respect to figure B-16.

For instance, where does the tables you have given fits in.
My previous post is a bit more elaborate than B-16. In B-16, the CourseGradeItems table holds the definitions of the grades. For example, the midterm is worth 25% and quiz 1 is worth 5%.

That figure doesn't use the idea of grade categories like the ones I described before. In this figure you would need to give each item its final weight. For example, if you want 5 quizzes with a total weight of 25%, then you would need to give each 5% weight. (In the other solution, you would say the category has weight 25% and the program would divide that among the quizzes for you.)

So CourseGradeItems defines the weights for the items in a CourseId. The StudentGrades table holds the grades for a particular student. CourseOfferingId gets you to CourseId. Then CourseId plus GradeItem gets you to the CourseGradeItems table so you can see how much that item is worth.

You can still compute partial grades. For example, halfway through the course, the students won't have all of their grades entered. A program could find those that are present and calculate a weighted average. For example, if you have 3 quizzes at 5% each and a midterm worth 25%, then you would calculate:

Code:
(quiz1 * 5 + quiz2 * 5 + quiz3 * 5 + midterm * 25) / (5 + 5 + 5 + 25)
Note also that the model in B-16 is a bit more consistent and less flexible than the one that the grade software I used is. That software defined the grade items for each offering not once for each course. For example, each time you teach VB 101, you could define different quiz, midterm, and other grade items. (That was probably just as well because there were often different instructors, and different instructors give different assignments.)
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
  #8 (permalink)  
Old May 9th, 2014, 02:57 PM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Default

I'm sorry you are not feeling well.

And I rarely find someone with such enthutiasm that you have.

Anyways, I think that you are giving a reply before my last edit.

Would you pls have a look at it again.

I can wait a couple of days until you are better.

Thanks so much for the attention
Reply With Quote
  #9 (permalink)  
Old May 9th, 2014, 09:41 PM
Rod Stephens's Avatar
Wrox Author
Points: 3,141, Level: 23
Points: 3,141, Level: 23 Points: 3,141, Level: 23 Points: 3,141, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jan 2006
Location: , , .
Posts: 640
Thanks: 2
Thanked 96 Times in 95 Posts
Default

Quote:
The Section table is mentioned in one of the models I found in the internet. But it also includes the following fields : Building, Room, MeetingDay, MeetingTime.
Ah. that sounds like the situation I was describing where the students in a single large course also have smaller sections at different days, times, and rooms.

You could add another table to store the section information. It would link to the CourseOfferings table by CourseOfferingId. It would also either (1) have a SectionId, or (2) use CourseOfferingId/room/day/time as the primary key. The StudentCourses table would probably then link to it, assuming each student is enrolled in a specific section for each course.

(The courses I've taught most recently weren't that big so we didn't have sections. We had lecture twice a week with lab time immediately following. With a maximum of 20 or 25 students, we had a lab room big enough for everyone.)

Quote:
My main question is about how to fit in the grading that I listed above in figure B-16 with the assumptions given.
I'm not sure if I already answered that with the talk about assignment weights. Let me know if I missed it.

To do the more elaborate thing with assignment types, you would need to add a new table to describe them.
__________________
Rod

Rod Stephens, Microsoft MVP

Essential Algorithms: A Practical Approach to Computer Algorithms

(Please post reviews at Amazon or wherever you shop!)
Reply With Quote
  #10 (permalink)  
Old May 10th, 2014, 02:00 AM
Authorized User
Points: 80, Level: 1
Points: 80, Level: 1 Points: 80, Level: 1 Points: 80, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Nov 2011
Posts: 20
Thanks: 7
Thanked 0 Times in 0 Posts
Default

Thank you for still offering to help although you have allocated a lot of time answering this thread.

OK Sir. Let me be clearer on the question.

I already know how to calculate total score based on the weightages. The model makes it easy to do so because everything is kept neatly in the model.

Lets say I calculated the final score for John Doe in Maths 101 for Semester 1 in 2014 as 78. I would then use the grading scale below to determine the final gred in StudentCourses table for John Doe. It would be recorded as a B.

Here is the grading scale :

90 to 100 A
70 to 89 B
50 to 69 C
40 to 49 D
0 to 39 F

a) Could we create a table from the grading scale, lets say GradingScales

b) What are the fields we can put in it

c) What would be the primary key and unique keys

d) How is this table linked to the present model in figure B-16. (To which table would it link)

How about Situation B :

e) If Comp 101 have a different grading scale, Lets say

90 to 100 EXCELLENT
70 to 89 GOOD
40 to 69 SATISFACTORY
0 to 39 WEAK

could it fit in the model?

Situation C :

f) Maths101 for Summer Semester in 2014 have a different scaling than Semester 1 in 2014 :

80 to 100 A
60 to 79 B
50 to 59 C
40 to 49 D
0 to 39 F

could it fit in also?

I hope you have the idea what I am trying to achieve.

Thank you
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do i generate random numbers with mysql for students 2 register on a school site www.freegist.com Beginning PHP 0 April 16th, 2012 06:02 AM
Tutor-led ASP.NET courses kefi2927 BOOK: ASP.NET Website Programming Problem-Design-Solution 4 January 15th, 2005 10:44 PM



All times are GMT -4. The time now is 12:22 PM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.