University Degree Requirement Checklist - Converting to Database - Relationships

July 24, 200619 yr

I'm doing a side project for an ex-co-worker. He's an undergrad advisor and wants to replace a bunch of paper-based degree-requirement checklists with a database.

There are eight different checklists (for various combinations of major, minor, joint-major, 2nd degree). They're most interested in getting just one (the standard major checklist) automated, but I'd like to make this as extensible as possible. I don't have a PDF of the checklist (I'll scan it at work tomorrow if anyone wants to see it), but it looks something like this and should be pretty familiar for anyone who's been in a college or university:

Lower Division:

------------------------------------------------------------------------------------

Group (Criminology) |   Group B                     |   General

------------------------------------------------------------------------------------

CRIM 101 ____       |   SA 150 ____                 |   Elective ____

CRIM 103 ____       |   POL 100 OR 151 ____         |   Elective ____

CRIM 104 ____       |   PSYC 100 ____               |   Elective ____

CRIM 131 ____       |   PSYC 102 ____               |   Elective ____

CRIM 135 ____       |   ONE OF:                     |   Elective ____

CRIM 220 ____       |     PHIL 001/100 OR           |

CRIM 230 ____       |          110/120 OR           |

1 ELECTIVE ____     |          220/25/280   ____    |

                    |     ONE OF:                   |

                    |          STAT 100/101/203 OR  |

                    |          PSYC 210 ____        |       Total LD Credits: ____

                    |   1 ELECTIVE ____             |               (Min. 60)

------------------------------------------------------------------------------------



Upper Division:

------------------------------------------------------------------------------------

Minimum 36 Credit Hours             |       Plus 12 credit hours

Criminology, including 300,         |       Criminology and/or Group B

320, 321 & 330                      |       (See calendar for Group B list)

------------------------------------------------------------------------------------

____    ____    ____                |       ____

____    ____    ____                |       ____

____    ____    ____                |       ____

____    ____    ____                |       ____

____    ____    ____                |                   Total UD Credits: ____

____    ____    ____                |                           (Min. 48)

------------------------------------------------------------------------------------

The blanks are for filling in the number of credit hours (nearly all of them are three credits).

So far I've got a student table (id, nameFirst, nameLast, studentNumber), courses table (id, dept, courseNumber, credits, description) and a courses_completed join table (stuID, courseID). I can see what courses a student has completed, and the total number of credits, but I haven't figured out how to track the different requirements, especially since there's several places where there are options for required courses (i.e. POL 100 or 151). I'm still plugging away trying different stuff to see what sticks, but I'd appreciate any insight anyone has.

Edited July 24, 200619 yr by Guest