System Description

What we are building

This semester we will build plan.cs.jmu.edu, a web application for planning courses. Students use the application to build a plan of study: the courses they have already taken, the courses they intend to take, and the term in which each one falls. Advisors use it to review those plans, catch problems, and send suggestions. Faculty use it to request courses they would like to teach. Program directors use it to decide how many sections of each course to offer, and who teaches them.

Today the student half of this problem lives in spreadsheets. There are templates for various starting semesters and amounts of time remaining, the formulas that check the plan are duplicated in each copy, and the copies drift. Ownership is the worse problem. Nobody agrees on who owns a given sheet, students lose track of the link, and when a student changes advisors the sheet often does not follow. The department cannot answer a simple question like "how many students intend to take CS 445 next fall" without opening several dozen spreadsheets by hand.

The department half of the problem is different. Building a schedule is already partly automated, and scripts handle several of the mechanical steps. What is missing is the data those steps run on. Teaching preferences arrive by email in whatever form each person sends them, and projected demand for a course is estimated rather than drawn from what students actually intend to take.

The application replaces the spreadsheets with one database that the department owns.

Where this project leads

A system that stores student records and teaching assignments cannot simply be put on the web. Hosting an application that handles FERPA-protected data and personnel data requires approval, and approval requires something concrete to review: what the system stores, who can see each piece of it, and what it actually does. That is what you produce this semester. A working prototype is the step that makes the request possible, so build it as though someone will read your schema and decide. The requirements here are real, and the questions the database has to answer are the questions advisors actually ask.

Who uses the system

A student maintains one or more plans and keeps one of them marked active. A second plan is useful for comparing options, such as adding a minor the student has not yet declared. Every plan distinguishes the past from the future: completed courses carry grades, while future courses are intentions that change often. All of this data is self-reported, so a student can correct a grade or a term that was entered wrong. Because completed courses carry grades, the system can compute a term GPA, a cumulative GPA, and a GPA over major courses only.

An advisor sees the plans of assigned advisees and flags the ones that are incomplete or incorrect. An advisor cannot edit a student's data. Instead, an advisor leaves recommendations that the student sees at the next login and either accepts or declines. An advisor also needs a view across all advisees at once, rather than opening plans one at a time.

A faculty member submits teaching preferences each cycle: which courses, which kind of classrooms, which days and times, and what to avoid. Discussion about a proposed schedule happens over email rather than in the application, which collects the preferences and produces a starting point.

A program director sees aggregate demand drawn from student plans, builds a draft schedule from the submitted preferences, and looks for conflicts. Program directors also assign students to advisors.

A department admin sees everything, across all students, advisors, faculty, and courses, but does not edit any of it.

What the system does not do

The application does not register anyone for classes and does not talk to MyMadison. It does not solve the scheduling problem automatically; it records a draft schedule and reports the conflicts in it. It does not integrate with JMU single sign-on this semester, so people log in with an email address and a password. It does not track anything about a course beyond what a plan or a schedule needs, so no rosters, no assignments, and no grades other than the final grade on a completed course.

Ground rules

The core schema is provided and is not yours to change. It holds the tables that every team needs: people, students, faculty, terms, catalog years, subjects, and courses. Your team's tables may declare foreign keys into the core schema. Your team's tables may not declare foreign keys into another team's tables.

From GP4 onward your queries may read another team's tables, and your endpoints may call another team's endpoints. But your team may never write to another team's tables. Because another team may be behind, seed enough data of your own that you can demonstrate your feature without waiting on anyone.

No real people in the database

Everything the system knows about an institution is real: faculty, courses, prerequisites, catalog requirements, gen-ed clusters, buildings, rooms, and historical enrollment counts. Everything the system knows about a student is generated. No real student, no real grade, no real GPA, and no real advisor assignment goes into this database, in any branch, at any point in the semester. Real student records are protected by FERPA, and teaching preferences are personnel information. Generating believable people is part of GP3 and is a skill worth having.

How the work is divided

You will work in six teams of 3–4 students, and each team owns one feature area.

1. The catalog team owns what a course is and what it requires.
2. The requirements team owns what a degree demands.
3. The plans team owns what a student intends and has completed.
4. The advising team owns data about advisors and advisees.
5. The preferences team owns what faculty would like to teach.
6. The scheduling team owns the sections the department offers.

Each team designs 8–10 tables of its own, loads them, queries them, and serves them over an API. The outlines below describe what each team is responsible for and where its boundaries are. They deliberately do not list the questions your data must answer, and they do not list the tables to build. Working out the questions is most of GP1, and the tables are GP2.

Team 1: Course catalog

You own what a course is, what it requires, and how both change from one catalog year to the next.

The core schema holds the identity of a course: a subject, a number, and a course_id that never changes. Everything else about a course belongs to you, and almost all of it varies by catalog year. A title is rewritten, credits change from three to four, a description is revised. A plan written under the 2024 catalog has to display the course as it was described then, not as it reads today.

Prerequisites are the hard part. A prerequisite is rarely a single course. CS 374 might require CS 240 and either MATH 245 or CS 227, each passed with a C minus or better, and a corequisite is a different kind of constraint than a prerequisite even though it is written the same way. Whatever you design has to express and-of, or-of, and nesting, and it has to record the minimum grade that each part demands.

Courses also carry attributes. A course may be writing intensive, may carry a general education cluster tag, may be approved as a major elective. You own the tagging. The requirements team owns what a degree does with a tag once it exists.

Two courses may be the same course. CS 445 and IT 445 may be cross listed, meaning one course with two names. CS 139 may have become CS 149 in a later catalog, meaning two courses where the later one satisfies anything the earlier one did, starting from a particular term. These are different relationships and they are easy to conflate.

Finally, you record when a course is normally offered, which is an intention rather than a fact: fall only, spring only, every term, alternating years. The scheduling team records the sections that actually exist.

You do not own the identity of a course, which is in the core schema and is shared by every team. You do not own what a degree requires, which belongs to the requirements team; you own what a course requires. You do not own whether a particular student has met a prerequisite, because that depends on a plan.

Team 2: Degree requirements

You own what a degree demands, for every program the department advises and for every catalog year in which someone is still enrolled.

A program is a major, a minor, or a certificate. General education is not a program. It is a set of requirements that every student satisfies regardless of major, and it should be described that way even if you end up storing it with the same structure you use for programs.

Requirements change from year to year, and a student is bound to the catalog year recorded in the core schema. That binding is uniform: when a student moves to a later catalog year, the move applies to the major, every minor, and general education together, so you never have to reconcile one student against two different catalogs.

Requirements nest, and the nesting is the design problem. A major requires all of a set of core courses, and three of five electives, and nine credits at the 300 level or above, and each of those clauses may itself contain clauses. General education has the same shape with different labels, where a cluster is a top-level requirement with alternatives beneath it.

A requirement is satisfied in more than one way. Some name a specific course. Some name an attribute, so any course the catalog team has tagged will do. Some ask only for a number of credits within a range of course numbers.

One course may satisfy several requirements at once, and students rely on this constantly. MATH 318 may count toward both the CS major and the math minor. Double counting is normal here, not an edge case to design around.

Requirements are not absolute. A course may be approved to count in place of what a requirement names, and a requirement may be waived entirely for a particular student. Program directors make those decisions, and the conversation that leads to them happens outside the application. Your tables need to record only that an approved exception exists, for which student and which requirement.

You do not own courses or their attributes, which belong to the catalog team. You do not own a student's plan, which belongs to the plans team; you own the rules that a plan is checked against. You do not own the flags an advisor sees, which belong to the advising team, though those flags are produced by running your check.

Scope

Build a structure that records which requirements exist and how each one may be satisfied. Do not build a general-purpose rule language. If your design starts to look like a small programming language stored in tables, simplify it.

Team 3: Plans of study

You own the center of the application. Everything the catalog and requirements teams describe exists so that a plan can be checked against it, and everything the advising and scheduling teams do reads what you store.

A plan belongs to a student and lays out courses by term. A student may keep several plans, exactly one of which is active at a time. Each entry in a plan names a term and either a specific course or a placeholder, because a student who knows they need a general education course in a cluster may not yet know which one. Each entry has a status: completed, in progress, or planned. A completed entry carries a final grade.

Grades are the reason this area is harder than it looks. A letter grade maps to quality points, and some grades do not count toward a GPA at all. A course may also be taken more than once, and the GPA in the major counts only the best attempt rather than every attempt. The application computes a term GPA, a cumulative GPA, and a GPA restricted to courses in the major, and the three calculations differ in which entries they include.

Transfer credit is also yours. A course taken elsewhere may satisfy a JMU course, may satisfy a requirement without matching any specific course, or may carry credit that counts toward the total and nothing else.

Plans change constantly, and an advisor needs to see what changed since the last conversation.

You do not own the definition of a course, which belongs to the catalog team. You do not own whether a plan satisfies a degree, which belongs to the requirements team; you own the plan that the check runs against. You do not own advisor recommendations on a plan, which belong to the advising team.

Team 4: Advising

You own the relationship between an advisor and a student.

Every student has an advisor, and program directors decide who. An assignment has a beginning and usually an end, because advisors change and a plan reviewed in 2025 was reviewed by whoever held the assignment then. A student may have a second advisor for a minor or a certificate, so an assignment is not always exclusive.

An advisor cannot change a student's data, so the only way to influence a plan is to recommend something. A recommendation comes from an advisor, points at a plan or at one entry within it, and says what the advisor thinks should change. The student sees it at the next login and either accepts it or declines it, and until then the recommendation is outstanding. A recommendation that has sat unanswered for a month is a different thing from one that was declined, and an advisor needs to tell them apart.

Flags are the other half of your work. A flag is a problem detected in a plan rather than an opinion about it: a prerequisite that is not met, a term with too many credits, a spring-only course placed in a fall term, a requirement that nothing satisfies. The requirements team's check produces some of these and the catalog team's data produces others, but the record of what fired, when, and whether it has since cleared belongs to you.

Advising conversations also leave a record. You store that a meeting happened, when, with whom, and what was discussed, which is what lets an advisor see who has not been in this year.

Finally, you own the history of two facts that the core schema keeps only in current form. The core schema records a student's status and catalog year as they stand today. You record how they got there: when a student went inactive or graduated and why, and when a student moved from one catalog year to another.

You do not own the plan itself, which belongs to the plans team; you point at it and comment on it. You do not own the rules that decide whether a plan is valid, which belong to the requirements and catalog teams; you own the record of what those checks found. You do not own whether a person is permitted to advise, which is a flag in the core schema.

Team 5: Teaching preferences

You own what faculty want to teach, and everything the department needs to know before it can build a schedule.

Preferences are collected in cycles. A cycle covers one term, opens on a date, closes on a date, and produces one submission per faculty member. Cycles repeat, so the same person's answers for Fall 2027 and Spring 2028 both have to be there, and comparing them is useful.

A course preference is more than a wish. It combines what someone wants to teach with what someone is prepared to teach, and those are different: a person may be qualified for a course they would rather not teach again, and eager for a course they have never taught and would need a semester to prepare. Ranking matters too, because a preference for four courses is not four equal preferences.

Time preferences have three levels, not two. Preferred, willing to accept, and genuinely unavailable are different answers, and collapsing them loses the only information a director actually needs. Preferences attach to day patterns and to windows within a day.

Classroom needs are a separate question from time. A course taught as a lecture, a course taught with students working in groups at tables, and a course that needs a computer lab impose different constraints, and the person teaching it is the one who knows which applies. You and the scheduling team will need the same vocabulary for kinds of classrooms, but each of you keeps your own copy of it, since neither team may point a foreign key at the other.

Not everyone teaches a full load. A sabbatical, an administrative appointment, or a course bought out by a grant reduces what a person owes in a given term, and a schedule built without that information is wrong before it starts.

Preferences are not public

What a person asked for is visible to that person and to program directors, and not to their colleagues. Design as though that rule will be enforced, because in a deployed system it would be.

You do not own the courses themselves, which belong to the catalog team and the core schema. You do not own rooms, which belong to the scheduling team; you own the kind of room a course needs. You do not own the schedule that results, and a preference is never a promise.

Team 6: Schedule building

You own the sections the department offers, and the rooms and times they occupy.

Start with the physical facts. A building holds rooms, and a room has a capacity, a kind, and features that make it suitable or unsuitable for a given course. Times are equally concrete: a meeting pattern is a named combination of days and clock times, and the department reuses a small set of them rather than inventing new ones each term.

A schedule is a draft before it is a schedule. A term may have several drafts, each a revision of the last, and a director needs to compare them and to know which one is current. A section within a draft names a course, a section number, a seat count, an instructor, a room, and a meeting pattern. Some sections meet in more than one block, such as a lecture plus a lab in a different room on a different day, so a section is not always one time and one place.

Demand is what makes the draft defensible. The number of sections to offer comes from what students actually plan to take, so you record an estimate for a course in a term, where the estimate came from, and when it was computed. An estimate computed in March and an estimate computed in October are different numbers, and keeping only the latest one throws away the ability to say how the projection moved.

Conflicts are the payoff: one instructor assigned to two sections at the same hour, two sections assigned to the same room at the same hour, a section with more seats than its room holds, or a required course offered only at a time that collides with another required course.

Scope

You record a schedule and report what is wrong with it. You do not build something that generates an optimal schedule. Assignment is a human decision made over email, and your job is to give that decision good information.

You do not own courses, which belong to the catalog team and the core schema. You do not own what faculty asked for, which belongs to the preferences team; you own what was actually assigned. You do not own student plans, which belong to the plans team; you read them to estimate demand.