Modeling courses and pre-requisites in the database
Basic scenario: I have students, courses, and course-requirements that need to be stored in the database.
Anyone has any insight into the best way to store these and traverse course prerequisites?
1) Simple example:
- C: Math 100, prereq: none
- C: Math 150, prereq: none
- C: Math 200, prereq: Math 101, Math 150
Student A completed Math 100. How does one determine if he is eligible to take CS200 via database querying?
2) More complicated example:
- C: Chem 100, pre开发者_如何学运维req: 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 completed Chem 100, Chem 200, Chem 203. How do you check that he is eligible to take Chem 300?
How to model the pre-requisites hierarchy in the database? Any advice, links, references would be most welcome.
There are are few things you have not thought about, as in Entities that need to be defined, that are implicit in your question. Permit me to include a few, but do not get distracted by them, I am focusing on your stated question. You can safely ignore Grade, Teacher
, etc for now and contemplate them for the future.
Typical Data Model for a College
IDEF1X Notation, for those who need explanation of the symbols.
There is no need to add Surrogate keys unless they are required, due the the natural key becoming too large to carry into the children; I have not blindly stamped them as PK on every table. In any case, the consideration is at the physical, not logical level.
I modelled a RDb for a local university once. I think in addition to pre-requisites such as "any two of ...", you may need "and at least one of ...", so I have provided for any combination of those.
Requisite
contains the full list of possible requisitesIsMandatory
identifies that the Requisite is Madatory required to fulfil the "at least one of ..."; the remainder are not, and fall into the "any two of ..."Grade
allows a minimum Grade to be specified as requisite .
Course.NumRequisite
identifies the number, of the possibleRequisites
, that are required, as in "any two of ..."
If you need SQL code to navigate the structure, please ask.
I think the following would do most of what you want:
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'))
Given the above the query to check to see if a student has successfully completed all coursework to take a given class would actually be easier if the query was written to return the pre-requisite courses the student HAD NOT taken - something like
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
Note that this hasn't been tested in any way and may contain some hideous logic errors (I'm a little leery of the late join of the COURSE table, for instance) but it should be useful as something to get started with. Or, to quote the philosopher Pratchett
Not tested on animals - you'll be the first!
Share and enjoy.
精彩评论