Re: Hopelessly circular relationships

Thank you very much for your reply. Let me attempt to clarify my problem and answer some of your comments.

Students partaking of a subactivity are assigned scores, as you said.

> No; students take courses, but the courses exist independently of the
> students that take it.

Correct! A course exists independently of the students that take it. The fact that, in real life, the school would cancel any course that didn't have students taking it is irrelevent to my problem.

Nevertheless, the same students that are associated with the SubActivity/Score combination are also associated with a course. The course is also eventually associated with the SubActivity via the Activity.

Full Circle!

> I don't think you've thought this through accurately yet. And once
> you have thought it through, you will find that the current
> circularity is irrelevant. You may have some hopelessly circular
> thinking, but not hopelessly circular relationships.

I'm trying! Thanks for your help. I'll keep trying to clear the spider webs out of my brain. The fact that I realize my head is spinning means there yet may be hope.

> For example - does a course get given once, or can the same course be
> given by the same instructor several times, with different students
> each time. (And, potentially, different activities and subactivities.)

You got it exactly right. Same course several times in the same semester over several years. Other courses too. "Database Theory 101" at 7:00 am to a group of students and 10:30 am to another group of students and "Database Theory 201" at 1:00 pm to yet another group of students, for example--each with its own set of activities and subactivities.

Sometimes a student will try the same course again, and his/her studentID will never change. The same student may take another course with that same instructor. Sometimes the activities and subactivities change some over the years, but each time the courseID the activityID and subactivityID will be different.

> Do all activities have subactivities? Can an activity have a score in
> its own right, or only as a sum of the scores for its subactivities.

Well I suppose an activity could have a score in its own right, but in my case, an activity is actually a grouping, such as "Exams" is a grouping of subactivitiesincluding such as "midterm" and "final."

> etc.

> For instance, how is a SubActivityID related to anything else -
> course, activity, subactivity. We can guess - but we can't be sure
> and making us guess is worse than useless.

SubActivityID and StudentID come together as a combined key in a new table called "StudentScores" to provide a place to assign a score for each student for each subactivity. Many students will take the same final exam and each will earn a different score (unless they all copied each other).

The activities have a weight. Exams, for example, may be worth 25% of the entire course. The activity "Exams" would be associated with subactivites such as "Midterm" and "Final."

Subactivities are also weighted. The subactivity "final exam," for example, may be worth 75% of the activity "Exams."

> The primary area that you need to understand better is the application
> area. Your description is not clear enough.

Sorry. I tried to be clear. Just because I think I know what I mean to say doesn't mean that I adequately communicated that thought. I do appreciate your attempt to help me. I tried to be concise, but I never intended to by cryptic. :-)

> The 'institution' runs courses - each course is different from every
> other, is given by a single lecturer, and has a different start and
> end date, scheduled lecture times, ... Even if the lecturer considers
> that he is teaching 'Database Modelling 101' each time, each
> occurrence is different.

Correct.

> Each course consists of one or more activities. Each activity is
> associated with exactly one course. Each activity has a name, and an
> indication of when it occurs during the course. ...

Well, actually, in my case, there is no indication of when it occurs during the course, but you appear to have that right. One course to many activities.

> Each activity necessarily involves one or more subactivities. Each
> subactivity is associated with exactly one activity. It has a name,
> and a score and ...

Yep. One activity (with its weight) to many subactivities (with their weights within the activity.)

I may have just uncovered a possible problem: One student may enrolled in many courses, and one course may have many students enrolled in it. It appears to be a many-to-many relationship, though I don't really think it is.

I'll attempt to diagram it here, though it is difficult to do with text only.

"courses" --> "students-in-courses" <-- "students" (courseID and studentID as combined keys).

"students" --> "student-scores" <-- "subactivities" (studentID and subactivityID as combined keys).

"courses" --> "activities" --> "subactivities".

By the way, the table "students_in_courses" could also, I suppose, just as correctly be called "rosters," but I didn't call it that.

Thanks, Jonathan. I hope I clarified my situation to you. However, I am still as confused as I was before. I still need help. Received on Sun Jul 18 2004 - 08:42:52 CEST