December 16, 2008 at 1:28 am
Hi, I'm building a web application that uses sql server for my database. This database holds student information, course information, student grades, etc. Currently I have my student table, which stores the student_id(PK), first name, last name, and middle name. The course table holds course_id(PK), module number, teacher_id, time, semester, etc...Since these two have a many to many relationship, I then have another table called enrollment which stores student_id and course_id. The problem is this: this database must hold quiz grades for every student in a given course, as well as homework grades, exam grade, worksheets grades, and bonus points. Now a teacher in a course may assign only one quiz, another could assign 4 quizzes, another only 2, and the same goes for the rest of the assignments. All these assignments will be queried by my application in order to look up how a student did in quiz 1 of a certain course, or homework 1 grade...how should I approach the design of all these assignment tables? Currently I have something which I'm sure is pretty inefficient. I have a quiz table, homeworks table, exam table...etc. My quiz table for example, has columns student_id, course_id, quiz1grade, quiz2grade, quiz3grade, quiz4grade, and quiz5grade..since I know for a fact a teacher won't give out more than 5 quizes. But if a teacher only gives out 2 quizes, then there will be a lot of space wasted in my database. I'm also storing the student_id and course_id in all these assignment tables, so there's a bit of redundancy there but I couldn't think of another way to store these assignments and be able to retrieve them for every student in a given course. I would greatly appreciate any advice on how to construct these tables. Thanks.
December 16, 2008 at 1:44 am
Is this a homework assignment?
Can you post the schema as it is now?
The quiz table as you describe it is in violation of 1st normal form. It has repeating groups. You should have a table with studentID, QuizNo and score so that 5 quizzes are on 5 rows, not 1.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 16, 2008 at 2:17 am
No it isn't a homework assignment, I'm actually doing this for my schools math department for this new program they have for incoming remedial math students.
This is the schema:
(student_id is int, course_id is varchar(50), all grades are stored as float)
student_info(student_id(PK), first_name, last_name, middle_name)
course_info(course_id(PK), module, modular_period, semester, year, ref_course_number, ref_teacher_id, time)
course_info(course_number(PK), course_name, dept_name)
teacher(teacher_id(PK), first_name, last_name, middle_name)
enrollment(ref_student_id, ref_course_id)
quiz(student_id, course_id, quiz1grade, quiz2grade, quiz3grade, quiz4grade, quiz5grade)
homework(student_id, course_id, hw1grade, hw2grade, hw3grade, hw4grade, hw5grade)
worksheet(student_id, course_id, wk1grade, wk2grade, wk3grade)
exam(student_id, course_id, exam_grade)
bonus(student_id, course_id, bonus_points)
attendance(student_id, course_id, absence_date)
summary(student_id, course_id, attendance_avg, hw_avg, wk_avg, quiz_avg, final_module_grade)
I know this is a crappy design...please help. Thanks.
December 16, 2008 at 5:46 am
stratergib_77 (12/16/2008)
Hi, I'm building a web application that uses sql server for my database. This database holds student information, course information, student grades, etc. Currently I have my student table, which stores the student_id(PK), first name, last name, and middle name. The course table holds course_id(PK), module number, teacher_id, time, semester, etc...Since these two have a many to many relationship, I then have another table called enrollment which stores student_id and course_id. The problem is this: this database must hold quiz grades for every student in a given course, as well as homework grades, exam grade, worksheets grades, and bonus points. Now a teacher in a course may assign only one quiz, another could assign 4 quizzes, another only 2, and the same goes for the rest of the assignments. All these assignments will be queried by my application in order to look up how a student did in quiz 1 of a certain course, or homework 1 grade...how should I approach the design of all these assignment tables? Currently I have something which I'm sure is pretty inefficient. I have a quiz table, homeworks table, exam table...etc. My quiz table for example, has columns student_id, course_id, quiz1grade, quiz2grade, quiz3grade, quiz4grade, and quiz5grade..since I know for a fact a teacher won't give out more than 5 quizes. But if a teacher only gives out 2 quizes, then there will be a lot of space wasted in my database. I'm also storing the student_id and course_id in all these assignment tables, so there's a bit of redundancy there but I couldn't think of another way to store these assignments and be able to retrieve them for every student in a given course. I would greatly appreciate any advice on how to construct these tables. Thanks.
Instead of breaking down the quiz and homework and other objects and events that add up to a grade, instead, define something, for want of a better term at the moment, let's call it a GradeableObject (name sucks, sorry). You can create this as a table that is a child of the course table, so that a given course has X number of gradeable objects. You can also define an object type, quizz, test, homework, bonus, and put that into a lookup table so that others can be added as needed (book report..., etc.). Then have another many to many table, this time joining, I'd say from the StudentCourse table to another interum table that joins the gradeable object to the student course table. In that able you would establish the grade or points associated with the given gradeable object for the given student within the course.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2008 at 6:56 am
I was just going to type out what Grant said and I'd recommend normalizing the course_info table a bit more by putting semester and year in a different table(s).
I think you can also handle bonus points a little differently. Perhaps on the "GradeableObject" instead of having a single grade, perhaps putting total marks and the student's marks (i.e. the quiz is worth 15 marks and the student scored 12). Then the grade would be a calculation, but if there were bonus marks, another student could score 17/15. Then everything would be weighted correctly when you do a query to figure out the total grades for each student at the end of the year.
My old database management systems textbook from university used such a schema for much of the instructions. I re-read it about 7 months ago.
December 17, 2008 at 7:14 am
Yeah, Ian, that sounds like a good approach. I was thinking more or less the same thing, but didn't put in as clear as manner as you just did.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 17, 2008 at 1:33 pm
yea thanks for the advice, what i did is for example the quiz table now looks like this:
quiz(student_id(PK), course_id(PK), quiz_number(PK), quiz_grade)
the homeworks, worksheets, and quizzes table is the same thing.
the thing is with this project is that they only give out these types of assingments, and a module(or course_id) only lasts three weeks.
now about adding how much a quiz is worth in order to calculate the final grade..the quizzes for example are worth 20% of the final grade, so I was just thinking just do an AVG function:
SELECT AVG(quiz_grade) AS quiz_avg
FROM quiz
WHERE student_id = @student_id and course_id = @course_id
then with this i would get the avg for all quizzes of a certain student..then in my application i would multipy the avg by .20 and then add to the rest of the assignments to get the final grade. is this a good idea? should i store the final grade in the database? thanks for all the help.
December 17, 2008 at 1:50 pm
Your plan seems workable. If you're more comfortable doing the weighting in the application code then it'll work. I wouldn't put the final grade in the database since it seems to just be a derived value. However if you're going to run through the grades in each table to perform this calculation, retrieval would be quicker if you save the grade during the calculation process. I would make sure you have a process to recalculate it if any of the grades that make it up are modified though.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply