Re: Hopelessly circular relationships

From: --CELKO-- <jcelko212_at_earthlink.net>
Date: 18 Jul 2004 12:17:13 -0700
Message-ID: <18c7b3c2.0407181117.14c4303e@posting.google.com>

>> * Courses have Activities.

• Activities have SubActivities.
• SubActivities have Scores
• Scores are assigned to Students.
• Students are in Courses. <<
  • *Courses have Activities.

CREATE TABLE Courses
(course_nbr INTEGER NOT NULL PRIMARY KEY,
 course_name CHAR(15) NOT NULL,
 credits INTEGER NOT NULL,
 ..);

CREATE TABLE Activities
(course_nbr INTEGER NOT NULL

            REFERENCES Courses (course_nbr)
            ON UPDATE CASCADE
            ON DELETE CASCADE,

 activity_nbr INTEGER NOT NULL,
 ..
 PRIMARY KEY (course_nbr, activity_nbr));

• *Activities have SubActivities.
• Note how the DRI actions are set up - important!

CREATE TABLE SubActivities
(course_nbr INTEGER NOT NULL

 activity_nbr INTEGER NOT NULL,
   FOREIGN KEY (course_nbr, activity_nbr)    REFERENCES Activities (course_nbr, activity_nbr)    ON UPDATE CASCADE
   ON DELETE CASCADE,
 sub_activity_nbr INTEGER NOT NULL,
 PRIMARY KEY (course_nbr, activity_nbr, sub_activity_nbr)  points INTEGER NOT NULL,
 .. );  

• *SubActivities have Scores
• *Scores are assigned to Students.
• No, SubActivities have points at best, and the scores belong to the student who did that sub_activity. Billy gets a 70, John gets 75, etc.

CREATE TABLE Students
(student_id CHAR(10) NOT NULL PRIMARY KEY,
 ..);

CREATE TABLE ReportCards
(course_nbr INTEGER NOT NULL

 activity_nbr INTEGER NOT NULL,
 sub_activity_nbr INTEGER NOT NULL,
   FOREIGN KEY (course_nbr, activity_nbr, sub_activity_nbr)    REFERENCES SubActivities (course_nbr, activity_nbr, subactivity_nbr)

   ON UPDATE CASCADE
   ON DELETE CASCADE,
 student_id CHAR(10) NOT NULL
   REFERENCES Students (student_id)
   ON UPDATE CASCADE
   ON DELETE CASCADE,
 PRIMARY KEY (course_nbr, activity_nbr, sub_activity_nbr, student_id),  score INTEGER NOT NULL, -- not greater than SubActivities points ..);

• *Students are in Courses.

There probably would be a class register table, but this schema will also provide that information.

I have set this up so that if an activity is dropped, its subs are dropped; if course is dropped all activities are dropped and it cascades to the subs; if a student is dropped, his report card is dropped. Received on Sun Jul 18 2004 - 14:17:13 CDT