On Sun, Nov 24, 2002 at 11:05:03PM -0500, Russell Hires wrote:
<snip>
> > Drop the Class_Schedule table. It duplicates the Classes table. In fact,
> > scour all these tables for duplicative fields. The only time you should
> > have a duplicate field in two tables is if it's being used as a primary
> > or foreign key; that is, it's used to tie the data in two tables
> > together. Otherwise eliminate it. See "normalization".
> Yeah...this is where I'm having the most trouble. This is hard! :-)
Hmm. I don't know how to make it easy. It's intuitive to me, but I've
been messing with databases for years. Basically, you don't want any
duplicate fields anywhere except for keys. And any table has to be
indexed by a key that doesn't allow duplicate records. That's _way_
over-simplified and not totally true, but it's kind of a snapshot.
<snip>
> > Create a teachers_classes table with teacher_id and class_id in it. As
> > you have it, there's no real way to cross reference these two things,
> > even though they obviously go together. Primary key would be the
> > class_id, since potentially you could have more than one teacher per
> > class.
> Is this to be a separate table from the students_classes table, where I've
> got the three fields as student_id, class_id, teacher_id ?
I musta missed that.
> > It's not clear what the grades table is for. I assume it's for final
> > grades per semester?
> That's not the intention. It's supposed to be for a "running/current grade
> average" of all assignments up to this point. But maybe I don't need this to
> be kept in the database. That can be calculated on a dynamic basis, depending
> on the assignments that are in the DB already...I can have Zope deal with
> that for me...
Agreed.
> > The work_item table seems like the same thing as
> > the assignments table.
> Okay, so I took it out.
> >Grade_category table?
> Grade category is for tests vs daily assignments vs quizzes...each has a
> different value of points in the scale, and that information has to be kept
> somewhere
Hmm. This should probably be in the assignments table then. The
assumption is that every assignment by every teacher for every class has
a different unique ID associated with it. The "weight" and "type" of the
assignment can simply be appended to the assignments table, since that
weight/type is directly tied to the individual assignment. I'm assuming
that the assignments table is just a list of all the assignments all the
teachers have given or will give.
> >Performance table? Don't
> > see what these are for.
>
> > You also need a table to tie students, courses, assignments and grades
> > together. In other words, you want a way to show each student's grade on
> > each assignment. You may not need the course_id field in this, if the
> > assignments_id is unique throughout the whole of the school.
> Okay. Created this table. Called it "student location" as it tells where the
> kid is (in what class, who the kid is, his teacher's name, what the
> assignment is, what he got on the assignment)
Again, this is duplicative effort. If you know the assignment id, then
you know the teacher, since each assignment will have a different
teacher id right in the assignments table.
The point of this table isn't really to locate the student. The point is
that this is the table where each student's complete grades on all
assignments are for the year or semester. If you want to know the grade
Johnny got on the third quiz this semester, this is the table where you
find that. So here's what you need. First you need an assignment id,
since grades are assigned against assignments. Could be a test, a quiz,
homework, whatever. That stuff's is in the assignments table. But the
grade and the assignment go together in this table. And you need at
least one more thing to be able to tie the student to this assignment:
the student id. An assignment doesn't have a grade by itself. It only
has a grade in relation to a student. You'd say, "Student A made 86% on
assignment B." That tells you all you need to know about that grade. You
could actually call this the "grades" or "raw_grades" table.
You may run into another problem with this. Teachers fudge grades for
students they like; I know, I had teachers do this for me. The raw
grades and their weights may add up to one thing, but the teacher may
take into account the attitude of the student and their assistance
during the course. As a result, although the student actually scored an
89% for the semester, the teacher may up that to 91% arbitrarily. The
teacher may know that the student is trying to get into a good college,
and just slacked off this semester; but she know's he's capable of doing
the work. So if you have a table with final grades in it, it can't
necessarily be tied to the calculated grade for each student. You'll
have to give the teacher some leeway to "fudge".
You should probably update your website, since it looks like you've made
significant changes.
We should take this offlist. I don't want to test the patience of the
people on the list, most of whom probably couldn't care less about this.
Feel free to reply offlist, and I'll do what I can to help.
Paul
This archive was generated by hypermail 2.1.3 : Fri Aug 01 2014 - 20:10:29 EDT