Re: Hopelessly circular relationships

From: Dan <guntermannxxx_at_verizon.com>
Date: Sun, 18 Jul 2004 09:12:08 GMT
Message-ID: <I9rKc.8791$gt1.1551_at_nwrddc02.gnilink.net>

"Richard Hollenbeck" <richard.hollenbeck_at_verizon.net> wrote in message news:JbpKc.7841$lz2.7233_at_nwrddc03.gnilink.net...
> > Courses have Activities which have Subactivities which have Scores.
> > Courses have Students which have Scores.

I don't presume to know all the semantics and functional dependencies that might be relevant for your model and I'm therefore guessing, but would something like this get you close?

Students(Name #sname#, .. other attributes, CK Name);

Courses(Course_ID #cid#, sdate #date#, CK Course_ID);

Activities(AID #aid#, Name #aname#, CK AID, CK Name );

CourseRegistration(CID #cid#, SName #sname#, other attributes

                             CK (CID, SName),
                             FK (CID references Courses(Course_ID),
                             FK (SName references Students(Name) );

CourseActivities(Course_ID #cid#, AID #aid#, other attributes...,
                          CK (Course_ID, AID),
                           FK (Course_ID) references Courses (Course_ID),
                           FK (AID) references Activities(AID)) ;

SubActivities(Course_ID #cid#, AID #aid#, SubActivityName #suba_name#,
                      ....other attributes.....,
                     CK (Course_ID, AID, SubActivityName,
                      FK (Course_ID, AID) references

CourseActivities(Course_ID, AID));

StudentSubActivities(course #cid#, student #sname#, activity #aid#, sub_activity #suba_name#, score #natural#,

                                other attributes..
                                CK (course, student, activity,
sub_activity),
                                FK (course, student) REFERENCES
course_enrollement(cid, sname),
                                FK (course, activity, sub_activity)

REFERENCES subactivities(course_id, aid, subactivityname));

In this case I am assuming the following as functional dependencies:

1.  The student name functionally determines information about students.
2.  The course id functionally determines the start date of the course.
3.  The activity id functionally determines the activity name; and the

activity name functionally determines the activity id. 4. The course id and student name functionally determine information about the students registration to the class.
5. The course id and the activity id functionally determine information about the activities planned or executed for a particular course. 6. The course id, the activiiy id, and the subactivity name functionally determine the information about subactivities that comprise an activity for a specific course.
7. The course id, student name, activity id, and sub-activity name together functionally determine the score of a student sub-activity (for a course, student, activity, and sub-activity).

Some of these tables can go away if no attributes other than the determinants exist.

 > Seems right but actually, neither students nor subactivities have scores.
> It is the combination of students and subactivities which actually gets a
> score. That was my fault for being unclear in the original post. An
> activity cannot directly get a score because it's a score for many
different
> students. The student cannot directly receive a score, because he or she
> will be receiving many scores (one for each subactivity.) To try to solve
> this problem I created a new table called "StudentScores" for want of a
> better name. This table only has three fields: a combined key of
"studentID
> and "subactivityID" and a field called "score"

I came up with something similiar, except that my key was the combination of course, student, activity, and subactivity. But if sub-activities are defined or described independently of activity and course, I agree that this would be right.
>
> So students are indirectly associated with the courses by their scores.

If I gave you a student name and a score, could you guarantee that you could answer for which course, activity, or sub_activity it was for? I don't see the circular relationship, and I've really looked. Could you give it another go at explaining where it is circular?

Regards,

Dan Received on Sun Jul 18 2004 - 11:12:08 CEST