[Assignment Spec]  [SQL Schema]  [Examples]

Aims

This assignment gives you practice in

• building a small REPL that talks to PostgreSQL via psycopg2
• writing parameterised SQL that updates and queries a realistic schema
• reasoning about timetabling constraints and deterministic output

The goal is to construct unswsh, an interactive shell for a cleaned subset of the mymyunsw database containing Faculty of Engineering students and courses.

Summary

cse$ give cs3311 ass2 ass2.py

Shell behaviour & general errors

• Your shell must echo every input line back to stdout, prefixed with >  (a greater-than sign followed by a space), before processing it. Lines without the prefix are shell output. For example:

  echo -e 'show\n/c\nshow\nterm 5024\nshow\nterm 5158\nterm 5000' | python3 ass2.py
  > show
  Term { unset }
  > /c
  > show
  Term { unset }
  > term 5024
  > show
  Term { unswid: 5024, year: 2002, term: S1, starting: 2002-03-04, ending: 2002-06-14 }
  > term 5158
  Term not set up yet, you cannot set this term.
  > term 5000
  Invalid term.

• Commands needing a configured term: student, enrol, timetable, plan, offer. If unset, they print Term not configured. Use /c then term <...>.
• show with no term prints Term { unset }.
• Unknown commands print Unknown command.
• ZID parsing: accepts z1234567 or 1234567; anything else counts as not found.
• Any place that needs a valid student but cannot find one prints Student not found.
• admin with wrong/missing argument prints Usage: admin on|off.
• Command names and admin arguments are case-insensitive; subject codes are normalised to uppercase. ZID prefix z is case-insensitive.
• Outputs that include building codes use ? if the building code is missing in the data (timetable/plan/offer).

How to do this assignment

• Read this specification carefully and completely.
• Create a working directory and copy the supplied template files (see Setting Up).
• Load the supplied mymyunsw dump and explore the schema.
• Implement each question in ass2.py, building on your previous work.
• Use parameterised SQL only; avoid string concatenation for queries.
• Test locally with the provided scripts in tests/ before submitting.

Background

The supplied database is a slimmed version of mymyunsw containing Engineering students, programs, courses and timetabled classes. Your shell will drive enrolment and planning workflows, always producing deterministic output.

Setting Up

Create a working directory and obtain the starter files:

cse$ mkdir ~/cs3311/ass2
cse$ cd ~/cs3311/ass2
cse$ cp /home/cs3311/web/26T1/assignments/ass2/ass2.py ass2.py
cse$ ln -s /home/cs3311/web/26T1/assignments/ass2/mymyunsw.dump mymyunsw.dump

Then initialise the database:

cse$ dropdb mymyunsw
cse$ createdb mymyunsw
cse$ psql mymyunsw -f mymyunsw.dump   -- loads schema + data
cse$ psql mymyunsw                    -- explore the database

If you are working on your own machine, you can download the starter files from: ass2.py, mymyunsw.dump, run_tests.py, output.json.

Environment

• Language: Python 3
• Database: PostgreSQL
• Library: psycopg2
• Database name for testing: mymyunsw
• Relevant tables: semesters, orgunit_types, orgunits, orgunit_groups, people, students, programs, program_enrolments, subjects, courses, class_types, buildings, rooms, classes, course_enrolments

Provided files

• ass2.py — skeleton to complete
• mymyunsw.dump — database schema + data
• tests/run_tests.py — local test runner
• tests/output.json — golden expected outputs

Marks Breakdown

Your Tasks

Complete each task by extending ass2.py. All commands read from standard input; outputs must match the required format exactly.

Q1 (2 marks)

Implement configure mode and term selection.

• Commands: /c or /d enter configure mode; exit leaves configure mode; term <SEM_UNSWID> sets the current term; show prints current term.
• Validation: if SEM_UNSWID exceeds MAX(semesters.unswid), print Term not set up yet, you cannot set this term.; if it is not in semesters, print Invalid term.
• Output format: Term { unswid: 5064, year: 2014, term: S2, starting: 2014-07-28, ending: 2014-11-02 }
• show always works; with no current term it prints Term { unset }.

Example:

echo -e '/c\nterm 5024\nshow' | python3 ass2.py
> /c
> term 5024
> show
Term { unswid: 5024, year: 2002, term: S1, starting: 2002-03-04, ending: 2002-06-14 }

Hints:

• Store the current term in self.current_term (already defined in skeleton).
• Use conn.commit() after successful term selection; conn.rollback() on validation failure.
• Check MAX first, then check existence—order matters for correct error messages.

Q2 (3 marks)

Add student <ZID> to pretty-print a student's profile for the configured term.

• Accept z1234567 or 1234567; the numeric part matches people.unswid.
• Show identity from people and students; ensure the person is a student.
• Program as-of current term: latest program_enrolment with start date <= current term start; otherwise program: Unknown.
• Faculty: walk orgunit_groups from programs.offeredby up to type Faculty; otherwise faculty: Unknown.
• Errors: no configured term => Term not configured. Use /c then term <...>.; missing student => Student not found.
• Malformed ZIDs or ZIDs that are not students also print Student not found.
• Faculty lookup stops when a Faculty orgunit is found; if none are found (e.g. deep or missing links), output remains Unknown.

Example:

echo -e '/c\nterm 5024\nstudent 2281773' | python3 ass2.py
> /c
> term 5024
> student 2281773
Student {
  zid: z2281773
  id:  1013041
  name: Solailakshmi Wright
  email: z2281773@student.unsw.edu.au
  stype: local
  program: Unknown
  faculty: Unknown
}

echo -e '/c\nterm 5054\nstudent 2281773' | python3 ass2.py
> /c
> term 5054
> student 2281773
Student {
  zid: z2281773
  id:  1013041
  name: Solailakshmi Wright
  email: z2281773@student.unsw.edu.au
  stype: local
  program: 1010 Chemical Engineering
  faculty: Faculty of Engineering
}

Q3 (3 marks)

Add enrol <ZID> <SUBJ1> [SUBJ2] [SUBJ3] to create course_enrolments in the configured term and then print all class options.

• Resolve subject code via subjects.code; find offering in current term via courses.
• If multiple offerings in term, choose the smallest courses.id.
• Single transaction: any failure rolls back all insertions. Existing enrolment prints Already enrolled in <CODE>. and continues.
• Class options output sorted by (day, start, end, room) using day names Mon..Sun and times as HH-HH.
• Errors: missing subject => Subject <CODE> not found.; not offered this term => <CODE> not offered in this term.
• Accepts up to three subject codes; extra codes are ignored.
• Any failing subject aborts the whole batch and rolls back prior inserts in that command; the first failure message prints once.
• ZID parsing/lookup failures print Student not found.
• After processing (even if some were already enrolled) the timetable is printed. If no classes exist, it still prints Timetable { then } on separate lines.
• Timetable ordering: day, start time, subject code, class type, building code, room, class id.
• The timetable <ZID> command can also be run directly; it follows the same term/student requirements and prints the same format and ordering.

Example:

echo -e '/c\nterm 5024\nenrol 2281773 COMP1001' | python3 ass2.py
> /c
> term 5024
> enrol 2281773 COMP1001
Timetable {
  Tue 09-11 COMP1001 LAB EE EE-LG7
  Wed 15-16 COMP1001 LEC EE EE-LG1
  ...
}

Hints:

• Use INSERT ... ON CONFLICT (student, course) DO NOTHING RETURNING 1 to detect duplicates—if fetchone() returns None, the student was already enrolled.
• Wrap all enrolments in a try/except block; on any error, rollback() and print the error message, then return early.
• For timetable: COALESCE(b.unswid, '?') handles missing building codes.
• Order by: ORDER BY cl.dayofwk, cl.starttime, sub.code, ct.unswid, building, room, cl.id.

Q4 (4 marks)

Add plan <ZID> <SUBJ1> [SUBJ2] [SUBJ3] to search for a clash-free weekly plan without writing to the database.

• Select exactly one LEC (if any) and one TUT (if any) per course.
• Use backtracking; order candidates by (dayofwk, starttime, endtime, room.unswid, classes.id); choose the first feasible solution.
• Output: Plan { ... } listing chosen classes; if none exists, print No feasible plan.
• Accepts up to three subject codes; extra codes are ignored.
• Validation errors mirror enrol: Subject <CODE> not found. or <CODE> not offered in this term. abort the command; invalid/unknown ZID prints Student not found.
• Student need not be enrolled, but must exist as a student record.
• If a course has no LEC or TUT classes, the plan still succeeds and simply omits that course from the listing.
• Plan output order: subject code, then LEC before TUT, then day/time/room (deterministic, not search order).
• Course offering resolution matches enrol: if multiple offerings for a code in the term, choose the smallest courses.id.

Example:

echo -e '/c\nterm 5024\nplan 2281773 COMP1091 ELEC1011' | python3 ass2.py
> /c
> term 5024
> plan 2281773 COMP1091 ELEC1011
Plan {
  COMP1091 LEC Tue 12-13 OMB OMB-112
  COMP1091 TUT Thu 12-13 MAT MAT-929
  ELEC1011 LEC Wed 14-15 CLB CLB-6
  ELEC1011 TUT Tue 16-17 MECH ME-503
}

Hints:

• Build a list of requirements: for each subject, for each class type (LEC/TUT) that exists, fetch all candidate classes sorted by the specified order.
• Implement a recursive backtracking function: def dfs(index) -> bool that tries each candidate for requirement[index], checks for clashes with already-chosen classes, and recurses to index+1.
• Clash detection: two classes clash if they're on the same day and their time ranges overlap (start1 < end2 and start2 < end1).
• After finding a solution, sort the chosen classes by (subject_code, type_order, day, start, end) before printing—this ensures deterministic output regardless of search order.

Q5 (3 marks)

Add admin mode (admin on|off) and offer <SUBJECT_CODE> <EXPECTED> <NLEC> to create a next-term offering and auto-schedule classes.

• Target term: the term immediately after the configured term; fail if none.
• Insert a new courses row, then schedule NLEC two-hour lectures and NTUT = ceil(EXPECTED / 25) one-hour tutorials.
• Lecture slot order: Mon–Fri 09-11, 11-13, 13-15, 15-17. Tutorial slot order: Mon–Fri 09-10, 10-11, ..., 17-18.
• Room choice: smallest capacity meeting requirement, then smallest building code, then smallest room code; slot usable only if no overlap in that room/term.
• Output: listing of created offering and classes. Failures: not admin => Admin mode required.; no next term => No next term in database.; no schedule => No feasible timetable.
• admin toggling errors: wrong/missing arg prints Usage: admin on|off; without admin on, offer prints Admin mode required.
• Subject validation: unknown subject prints Subject <CODE> not found.; if LEC class type is missing, print LEC class type missing.
• If no next term exists, print No next term in database. and do not create anything.
• Scheduling failures (no room/slot) print No feasible timetable. and roll back. Tutorials are created only if a TUT class type exists; otherwise only lectures are created.
• Expected <= 0 leads to zero tutorials; lectures still schedule per NLEC.
• If multiple subjects share the same code, offer uses the subject with the smallest subject ID (deterministic).
• Tutorial capacity per class is based on group size ceil(EXPECTED / NTUT); rooms must meet that capacity.

Example:

echo -e '/c\nterm 5024\nadmin on\noffer COMP1091 30 1' | python3 ass2.py
> /c
> term 5024
> admin on
> offer COMP1091 30 1
Offering created: COMP1091 in term 5025 (course_id=74126)
  LEC Mon 09-11 K17 K17-B01
  TUT Mon 09-10 ASB CHEM-611
  TUT Mon 09-10 ASB CHEM-614

Hints:

• Use import math for math.ceil() to calculate tutorial count and group sizes.
• Pre-fetch all rooms sorted by ORDER BY capacity, building, room_code, id once, then reuse this list for each class allocation.
• Query existing classes in the next term once: SELECT room, dayofwk, starttime, endtime FROM classes JOIN courses ..., then check conflicts in Python.
• Build slot lists: lec_slots = [(day, 9, 11), (day, 11, 13), ...] for day in range(5); similar for tutorials.
• For each class to schedule, iterate through slots, then through rooms; for each (slot, room) pair, check if room capacity is sufficient and no conflict exists with existing/placed classes. If found, insert the class and track it in a placed list; if not found after trying all combinations, rollback and print "No feasible timetable."

Submission and Testing

We will test your submission by loading mymyunsw, installing your ass2.py, and running automated scripts. Ensure your program is deterministic and uses parameterised queries.

• Each test case has a timeout of 60 seconds. If your program does not finish within this time, the test will be marked as failed.
• We will test against the same mymyunsw dataset provided in the dump. No larger or different datasets will be used.

You can test on the CSE server using the autotest command:

cse$ 3311 autotest ass2

You can also test locally with the provided scripts:

cse$ python3 tests/run_tests.py
cse$ python3 tests/run_tests.py --only=q3

If you pass all tests, your outputs match the golden samples in tests/output.json.

Good luck, and code safely!