Modeling courses and prerequisites in the database

Question

The main scenario: I have students, courses and course requirements that need to be saved in the database.

Does anyone have an idea of ​​the best way to store these and prerequisites?

1) A simple example:

• C: Math 100, prereq: none
• C: Math 150, prereq: none
• C: Math 200, prereq: Math 101, Math 150

Student. Completed Math 100. How to determine if it can accept CS200 through a database query?

2) A more complex example:

• C: Chem 100, prereq: none
• C: Chem 200, prereq: Chem 100
• C: Chem 201, prereq: Chem 200
• C: Chem 202, prereq: Chem 200
• C: Chem 300, prereq: any two of Chem 200, Chem 201, Chem 202

Student B graduated from Chem 100, Chem 200, Chem 203. How can you verify that he is eligible to accept Chem 300?

? , , .

Answer 1

, , Entities, , . , , . Grade, Teacher .., .

IDEF1X , .

• , , - , , ; . , .

• RDb . , , " ...", " ...", .

• Requisite

  • IsMandatory , Requisite " ..."; " ..."

  • Grade ,

• Course.NumRequisite Requisites, , " of..."

SQL , .

Answer 2

, , :

Table COURSE
  ID_COURSE    NUMBER         PRIMARY KEY
  DESCRIPTION  VARCHAR2(200)

Table PREREQUISITE_COURSE
  ID_COURSE         NUMBER    REFERENCES COURSE.ID_COURSE
  ID_PREREQ_COURSE  NUMBER    REFERENCES COURSE.ID_COURSE
  PRIMARY KEY (ID_COURSE, ID_PREREQ_COURSE)

Table STUDENT
  ID_STUDENT        NUMBER    PRIMARY KEY

Table STUDENT_COURSE
  ID_STUDENT        NUMBER    REFERENCES STUDENT.ID_STUDENT
  ID_COURSE         NUMBER    REFERENCES COURSE.ID_COURSE
  COMPLETED         CHAR(1)   CHECK(IN('Y', 'N'))
  PASSED            CHAR(1)   CHECK(IS NULL OR IN ('Y', 'N'))

, , , , , , , - -

SELECT c.*
  FROM STUDENT s
INNER JOIN COURSE STUDENT_COURSE sc
  ON (sc.ID_STUDENT = s.ID_STUDENT)
LEFT OUTER JOIN PREREQUISITE_COURSE pc
  ON (pc.ID_PREREQ_COURSE = sc.ID_COURSE)
INNER JOIN COURSE c
  ON (c.ID_COURSE = pc.ID_COURSE)
WHERE s.ID_STUDENT = <id of student of interest> AND
      c.ID_COURSE = <id of course of interest> AND
      sc.COMPLETED = 'Y' AND
      sc.PASSED = 'Y' AND
      pc.ID_PREREQ_COURSE IS NULL

, (, COURSE), - . ,

- !

.