Skip to content

GP3: Build and Load

Due: Monday, Oct 26th

In GP2 you designed your tables. In GP3 you will build them and fill them. This is the same work you did in HW4, on your own schema, with two differences:

  1. The core tables arrive full: real courses, terms, and the department's employees, and about 300 generated students, loaded by planner.core.load.
  2. Your data is not thrown away after grading. Every team's queries in GP4 and endpoints in GP5 run on what your load.py produces, so make it data worth querying.

What to commit

In your team's directory, src/planner/<area>/:

  • models.py – one SQLModel class per table in your schema.dbml
  • load.py – a load() function that adds your rows to the database
  • data/ – if you use real data, the files, the script that prepared them, and a README.md saying where they came from
  • schema.dbml and schema.png – updated wherever the tables changed, as GP2 requires from now on
  • README.md – a new "Data" section, described below

Divide the work as you did in GP2: each person owns two or three tables, and writes both the classes and the loading code for them. Commit your own work yourself, and say in the pull request who owns which.

Before you start

Your <area>/gp3 branch starts from main after the GP2 merge, and main has changed in three ways since GP2.

The core changed. faculty is now employee, and holds everyone who works for the department. If your schema.dbml has a faculty stub or a reference to faculty.person_id, change both to employee. The Core Schema Design page lists the other changes.

The checks changed. The third check now runs python scripts/load.py --scratch, which builds every table and runs every team's load.py. A load.py that fails is a failed check, for your pull request and for everyone else's.

The dependencies changed. Run the install again, which adds two libraries for reading web pages:

pip install -e ".[dev]"

Then check that everything works before you add anything:

python scripts/load.py --drop

That builds and loads a local planner.db, which you can open in DB Browser for SQLite.

The model classes

Write one class per table, with the table and column names from your schema.dbml. Use the SQLModel mini lab and HW4 as references, and the core's own models.py as an example.

Keys. A composite primary key is primary_key=True on each of its columns. A [unique] combination from your indexes block goes in __table_args__, as UniqueConstraint(...). A foreign key names the table and column: Field(foreign_key="employee.person_id").

Enums. For a column with a fixed set of values, use a Python StrEnum, and give its type the name from your DBML, which starts with your area:

class RecommendationStatus(StrEnum):
    pending = "pending"
    accepted = "accepted"
    declined = "declined"


class Recommendation(SQLModel, table=True):
    ...
    status: RecommendationStatus = Field(
        sa_type=Enum(RecommendationStatus, name="advising_recommendation_status")
    )

PostgreSQL stores each enum as a named type, shared by the whole database, and scripts/build.py fails if two enums share a name but not their values. HW4 used a CheckConstraint for this; an enum is the better tool, so save CheckConstraint for rules that involve more than one value, such as an end date after a start date.

Times. A moment in time is datetime, stored with its time zone: Field(sa_type=TIMESTAMPTZ), imported from planner.core. A calendar date is date.

Imports. Import your classes in your package's __init__.py, so that scripts/build.py finds them. Your classes may reference your own tables and the core, and nothing else.

The load script

scripts/load.py loads the core first, then calls each team's load():

def load(session: Session) -> None:
    """Add this team's rows to the session; scripts/load.py commits them."""

Find core rows by what they mean, not by id. An id depends on the order rows were loaded, so never type one in. Look rows up by their natural keys instead, once, at the start:

courses = {(c.subject_code, c.number): c for c in session.exec(select(Course))}
cs374 = courses["CS", "374"]
advisors = session.exec(select(Employee).where(Employee.is_advisor)).all()

Load each table before the tables that point at it. The session sends rows in an order it chooses, which follows your Relationships but not every foreign key. Call session.flush() after adding a table's rows, and before adding rows that refer to them. SQLite checks foreign keys in this project, as PostgreSQL does, so a row loaded in the wrong order fails the check.

Make every run the same. Seed every random generator you use, as HW4 did, so that every run loads the same data and a teammate can reproduce what you see.

Never download anything while loading. Real data goes in your data/ folder, prepared ahead of time, and load() reads it from there. Keep a file under about a megabyte; the repository rejects anything larger.

Respect the core's data. Your rows must agree with the core's: a student's plan starts no earlier than the student's admit_term, an advisor has is_advisor true, a course you refer to exists. The database cannot check every one of these, which is what makes them yours to get right, as the vet rule was in HW4.

Real data and generated data

Everything about the institution is real, and everything about a student is generated, as the System Description says. Teaching preferences are personnel information, so they are generated too, even though the faculty are real.

The core's src/planner/core/data/ holds real data for every team to start from, each directory with a README that describes its files:

  • catalog/ – four editions of the catalog: programs and general education for 2023 through 2026, and courses for 2025 and 2026, as CSV and Markdown files
  • scheduling/ – the CS and IT sections offered since Fall 2023, with their meeting times, instructors, and enrollment; the rooms in King Hall and EnGeo; and the standard meeting times
  • employee.csv – the department's employees, with their kinds and roles, which are already in the core tables
Area Start from Generate
catalog each edition's courses, cross-listings, and prerequisite text; the gen-ed tags in the program files; past sections for offering patterns anything the catalog does not print
requirements each edition's program_rows.csv, degree_requirements.csv, and program pages which programs each student is in; substitutions and waivers
plans the sample plans in sample_plans.csv, and the courses plans, grades, transfer credit, and repeated courses
advising the employees who advise assignments, recommendations, flags, meetings, and histories
preferences the instructional faculty, who taught what in past sections, and the standard meeting times every preference
scheduling rooms, past sections and their meetings, and the standard meeting times features, drafts, and demand estimates

Real data is never in exactly the form your tables need. A prerequisite is a sentence, a credit range is text, and a requirement table mixes headers and choices with courses. Turning that into rows is the point of the assignment, and you may decide how far to go: parse the cases that matter to your use cases, and say in your README what you left out.

The catalog has courses for 2025 and 2026 only. For 2023 and 2024, treat the 2025 course data as the closest available, and say so in your README.

Generate enough data that your GP4 queries will have something to find: every generated student, every advisor, or every past term, wherever your tables apply to them, and hundreds or thousands of rows rather than a handful. Generated data should be believable to someone who knows the department.

Loading into PostgreSQL

The check uses SQLite, but the class database is PostgreSQL, and a few mistakes pass in one and fail in the other: a string longer than its max_length, a number with more digits than its column allows, a value of the wrong type. So before you open your pull request, load everything into your own schema too. Start ssh stu, as in HW4, and run:

DATABASE_URL=postgresql+psycopg://username@localhost/sec1 python scripts/load.py --drop

Then open your tables in pgAdmin, next to the core's.

Watch for tables with the same names

--drop drops every table in your schema that has the same name as a planner table, including tables from your homework. If you ran HW4 on PostgreSQL, your schema has an employee table from the vet clinic, which --drop will remove.

Updating the README

Add a section named "Data" to your team's README.md, after "Tables". For each table, say in a sentence or two whether its rows are real or generated, where real rows came from, how generated rows are made, and about how many there are. Then say what you left out, such as prerequisites you did not parse, and any rule your data follows that the database does not enforce.

Grading criteria

I will read your code and load your data looking for the following, roughly in order of weight:

  • The classes match the schema. Every table, column, key, and constraint in schema.dbml is in models.py, and the other way around.
  • The data is right. It satisfies every constraint, including the rules the database cannot check, and it agrees with the core's data.
  • The data is believable and sufficient. Enough rows, in realistic proportions, to make the questions in your README worth asking.
  • Real data is used and documented. Wherever the core provides real data for your tables, you use it, and your README says where it came from and what you did with it.
  • The load script follows the rules. Natural keys rather than ids, seeded randomness, nothing downloaded, a passing check, and a load into PostgreSQL that works too.
  • Boundaries respected. References only into your own tables and the core.

I am also looking at the commit history for evidence that all four people wrote classes and loading code, and at how your team responds to the review.

Submission and review

Switch to your <area>/gp3 branch, commit as you go, and open a pull request by the deadline. See the GitHub Workflow for how branches, pull requests, and reviews work, and Code Reviews for what the reviewers are asked to look at.