Skip to content

HW4: Models and Data

Due: Monday, Oct 19th

In HW3 you mapped the Vet Clinic EER model to a relational schema. In this assignment you will build part of that schema with SQLModel and fill it with realistic fake data. The model classes are the source of truth: create_all() builds the tables, and you write SQL only for what the classes cannot express. These are the same steps your team will take in GP3, on a schema of your own.

Objectives

  • Write SQLModel classes with composite keys, CHECK constraints, and UNIQUE constraints.
  • Write SQL for a view the classes cannot express, and drop and create it in the right order.
  • Generate fake data that satisfies every constraint, including a rule that no constraint can enforce.

Instructions

Step 1: Set up.

Download the hw4.py template and the hw4.dbml schema into a new folder outside of your planner clone. Activate the planner environment, as in the SQLModel mini lab.

Paste hw4.dbml into dbdiagram.io to see the diagram. It has six of the twelve tables from the HW3 solution, with constraints added. Read the notes at the top of the file and on each table: some constraints are written only in the notes, because DBML has no syntax for them.

The engine in the template uses SQLite, so you don't need the SSH tunnel for this assignment.

Step 2: Write the classes.

The template already has the Employee, PetOwner, and Animal classes. Read them first, and compare each one with its table in hw4.dbml. They show max_length for a varchar, a CheckConstraint in __table_args__, and a Relationship pair, the same pattern you used in the mini lab.

Write the other three classes, named Appointment, Service, and ServiceAtAppt. Use the column names from hw4.dbml exactly; the autograder checks them. The mini lab has a table of PostgreSQL types and their SQLModel equivalents. Three more rules apply here:

  • A UNIQUE on two columns goes in __table_args__, as UniqueConstraint("vet_id", "date_time"), next to the CheckConstraints.
  • A CHECK may mention several columns of the same row, joined with AND and OR.
  • A composite key is primary_key=True on each of its columns, as in HotelPhone in the hotel demo.

Then add one Relationship pair, each side with back_populates naming the other: Appointment.performed, a list of ServiceAtAppt objects, and ServiceAtAppt.appointment.

Run python hw4.py and compare the CREATE TABLE statements it prints with hw4.dbml. Every constraint in the DBML, including the ones in the notes, should appear in the output.

Step 3: Write the view.

A view is a named SELECT statement that you can query as if it were a table. For example, this view lists each animal with its owner's name:

CREATE VIEW pet_list AS
SELECT p.pet_id, p.name AS pet_name, p.species, o.name AS owner_name
FROM animal AS p
JOIN pet_owner AS o ON o.owner_id = p.owner_id;

After that, SELECT * FROM pet_list WHERE species = 'cat' works just like a query on a table. A view stores no rows of its own. Each time you query it, the database runs the SELECT again, so the results always match the current data in the tables. The column names of the view come from the SELECT list, which is why AS matters there. Views let you write a complicated query once and give it a simple name, which is useful for reports and for the API you will write later in the project. To remove a view, use DROP VIEW pet_list, which leaves the underlying tables alone.

The clinic wants a bill for each appointment. Write a view named appt_bill with these columns, in this order:

Column Contents
appt_id the appointment
date_time when it was
pet_name the animal's name
owner_name the owner's name
num_services how many services were performed
total_fee the sum of their fees

A model class describes a table, so create_all() cannot create a view. Run your own SQL in build() instead, with a connection from the engine:

    with engine.begin() as conn:
        conn.execute(text("CREATE VIEW appt_bill AS SELECT ..."))

engine.begin() commits when the with block ends. drop_all() knows nothing about the view either, so build() must drop it too (DROP VIEW IF EXISTS appt_bill). Run the program twice in a row to make sure build() works on a database that is already built.

Then answer Question 1 at the top of hw4.py. Comment out your DROP VIEW line and run the program twice to see what happens. (PostgreSQL is stricter than SQLite here: it refuses to drop a table that a view depends on, so the view must be dropped first.)

Step 4: Generate the owners and the menu.

The template's make_employees() is done. Implement make_services() and make_owners() as described in their docstrings. Each returns new objects that are not yet in the database.

  • The services are not fake. Write a list of at least eight real services that a small vet clinic offers, with fees you could believe, and include a few grooming services. Some tables hold fixed reference data like this, and some hold generated data.
  • Each owner has 1 to 3 animals, appended to owner.animals as in the hotel demo. Choose species from a list of at least three, with some species more common than others.

Step 5: Generate the appointments.

Implement make_appointments(). The appointments have the most rules, and your code must follow all of them for any count, not just 60:

  • Every appointment is on a weekday in 2026, in a half-hour slot from 8:00 through 16:30. The template's random_time() returns one of these.
  • A grooming appointment has a groomer and grooming services. Every other appointment has a vet and other services, and a reason that fits its type.
  • No employee has two appointments at the same time. The UNIQUE constraints reject that at commit time, so check for it before then.
  • Each appointment has 1 to 3 different services, appended to appt.performed.

The database cannot enforce one of these rules: that vet_id belongs to an employee whose emp_type is 'vet'. Answer Question 2 at the top of hw4.py.

An appointment needs the ids of an animal, an employee, and its services, so those objects must be committed before make_appointments() runs. The template's load() takes care of that. Read its comments: they are the attached vs. detached difference from Thursday's lab.

Step 6: Load the database.

The template's load() inserts 3 vets, 2 groomers, 2 other employees, the whole menu, 20 owners with their animals, and 60 appointments.

Run python hw4.py, then open hw4.db in DB Browser for SQLite, or run sqlite3 hw4.db, and look at appt_bill. Run the program again: because of the seeds at the top of the file, you should get exactly the same data.

Optional: Run it on PostgreSQL

Your team will load its tables into PostgreSQL in GP3, so this is good practice. Start ssh stu, and change only the engine, as in the last step of the mini lab:

engine = create_engine("postgresql+psycopg://username@localhost/sec1", echo=True)

Run the program, and refresh your tables in pgAdmin. Then move your DROP VIEW line after drop_all(), and run it again to see the difference from SQLite. Switch the engine back to SQLite before you submit.

Submission

Submit your hw4.py to the HW4 assignment on Gradescope.

The autograder imports your file and runs it against a new SQLite database. It checks your tables and columns, tries to insert rows that break each constraint, tests your view on a small example, and checks your generated data against the rules in Steps 4 through 6. It also asks make_appointments() for 400 appointments, so make sure the double-booking check works for large counts. Your file may import only sqlmodel, sqlalchemy, faker, and the standard library.

Your code will first be graded by Gradescope and then by the professor. The grade you receive from Gradescope is the maximum grade that you can receive on the assignment.

There is no limit on the number of submissions and no penalty for excessive submissions. Points will be allocated as follows:

Criterion Points Details
Autograder 80 pts Partial Credit Possible
Code Review 20 pts Partial Credit Possible

The code review will include:

  1. Your answers to the two questions at the top of hw4.py.
  2. How realistic your services, species, and reasons are.
  3. The readability of your code, including names and comments.