Princeton University
COS 333: Advanced Programming Techniques

Assignment 2: A Registrar Application Using Database Programming

Purpose

The purpose of this assignment is to help you learn or review database programming in Java or Python. If done properly, the assignment also will give you practice at creating modular code.

Rules

Make sure you study the COS 333 "Policies" Web page before doing this assignment or any of the course's assignments.

You must work with one partner on this assignment. If the count of students working on the assignment is an odd number, then exactly one team may consist of three students; we will ask that team to implement some features in addition to those that are described in this document.

Only one of the partners should submit files. Your readme file and your source code files should contain your name and your partner's name.

Your Task

Any university registrar's office must maintain data about classes and courses offered during the upcoming semester. Those data typically are kept in a database. The registrar's office must provide an interface that allows students and other interested parties to query the database.

Assume that you are working for Princeton's Registrar's Office. You are given a database containing data about classes and courses offered during an upcoming Princeton semester. (Actually the data are for the Spring 2010 semester; that's the most recent data that I had last summer when preparing this assignment.) Your task is to create programs, written in Java or Python (but not both), that allow Princeton students and other interested parties to query the database.

For this assignment your programs should have simple textual interfaces. The next assignment will ask you to enhance your programs in a few ways, perhaps most notably such that they have graphical user interfaces. So it will be to your advantage to modularize your programs so you easily can replace their textual interfaces with graphical ones.

The Database

The database resides in the MySQL database management system that is administered by the CS Department. These settings allow you to access it:

The cos333 database consists of these five tables:

  1. classes
    Fields: classid, courseid, days, starttime, endtime, bldg, roomnum
  2. courses
    Fields: courseid, area, title, descrip, prereqs
    The courseid field of the courses table is related to the courseid field of the classes table.
  3. crosslistings
    Fields: courseid, dept, coursenum
    The courseid field of the crosslistings table is related to the courseid field of the courses table.
  4. coursesprofs
    Fields: courseid, profid
    The courseid field of the coursesprofs table is related to the courseid field of the courses table.
  5. profs
    Fields: profid, profname
    The profid field of the profs table is related to the profid field of the coursesprofs table.

Your first step should be to familiarize yourself with those tables.

All COS 333 students will use the one-and-only-one cos333 database. The cos333 database is not read-only. So, for the sake of other students, please be careful not to change the database.

The reg Program

You should write two programs. The first should be named reg. The reg program should have the behavior illustrated by these examples:

As those examples show, the reg program should accept command-line arguments. Each command-line argument should specify a search criterion. Each criterion should be of the form "key=value", where the valid keys are dept, coursenum, area, and title. The program should display the classid, dept, coursenum, area, and title of each class that matches the given criteria.

Normally the output should consist of lines having exactly this format:

classidtdepttcoursenumtareattitlen

where 't' is a tab character and 'n' is a newline character. For example, your program might write these lines:

8308tCOSt217tQRtIntroduction to Programming Systemsn
8321tCOSt333ttAdvanced Programming Techniquesn

The lines should be sorted; the primary sort should be by dept, the secondary sort should be by coursenum, and tertiary sort should be by classid. Please make sure that your output has the specified format. If it doesn't, then your program will fail our automated checks.

However, if the user specifies "-h" as the first command-line argument, then the program should print its data in a more human-readable format of your choosing. For example, you might consider printing column titles, aligning the columns, making sure no rows are overly long, etc.

Your reg program should be robust. It should print a descriptive error message to stderr and terminate if the user supplies bad command-line arguments. Some examples:

Your reg program also should protect the database against SQL injection attacks. That is, it should use SQL "prepared statements".

Our expectation is that your reg program actually will be a Bash script, and that the Bash script will execute a Java program in a file named Reg.class, or a Python program in a file named reg.py. Feel free to use either of these Bash scripts:

#!/bin/bash
java Reg "$@"
#!/bin/bash
reg.py "$@"

The regdetails Program

Your second program should be named regdetails. The regdetails program should accept a single command-line argument, which should be a classid. It should display the courseid, days, starttime, endtime, bldg, roomnum, dept(s), coursenum(s), area, title, descrip, prereqs, and profname(s) for the class with the given classid.

Normally the output should consist of lines having exactly this format:

courseidn
daysn
starttimen
endtimen
bldgn
roomnumn
dept1tcoursenum1t...tdeptNtcoursenumNn
arean
titlen
descripn
prereqsn
profname1t...tprofnameNn

where 't' is a tab character and 'n' is a newline character. The data in the "depts and coursenums" line should be ordered primarily by dept and secondarily by coursenum. The data in the "procnames" line should be ordered by profname. For example, your program might write these lines:

3672n
TThn
11:00 AMn
12:20 PMn
FRIENn
006n
COSt333n
n
Advanced Programming Techniquesn
This is a course about the practice of programming ... group projects.n
COS 217 and COS 226.n
Brian W. Kernighann

(Your program should write the complete program description; the description shown above is abbreviated just for clarity.) As with the reg program, please make sure that your output has the specified format. If it doesn't, then your program will fail our automated checks.

However, if the user specifies "-h" as the first command-line argument, then the program should print its data in a more human-readable format of your choosing. For example, you might consider printing titles, printing blank lines, making sure no rows are overly long, etc.

Your regdetails program should be robust. It should print a descriptive error message to stderr and terminate if the user supplies no command-line arguments, more than one command-line argument, or a non-numeric command-line argument. It also should print an error message to stderr if no class with the specified classid exists, and should protect the database against SQL injection attacks.

Our expectation is that your regdetails program actually will be a Bash script, and that the Bash script will execute a Java program in a file named RegDetails.class, or a Python program in a file named regdetails.py. Feel free to use either of these Bash scripts:

#!/bin/bash
java RegDetails "$@"
#!/bin/bash
regdetails.py "$@"

Advice

Note that the command-line format is incomplete in the logical sense. Most notably the format doesn't allow the user to express OR relationships in queries. For example, the format doesn't allow the user to express queries of the form "Display data for all classes whose area is qr OR st", or "Display data for all classes whose dept is cos OR whose area is qr." Don't be concerned about that. Remember that the purpose of the assignment is not to develop a great registrar's office application per se. Instead its purpose is to give you experience with database programming -- experience that, we hope, will help you to develop your project.

Encapsulate your user interface code and database handling code as much as you can. Good encapsulation will help you to reuse your code from this assignment in the next assignment.

Use CVS or Subversion to manage and share your source code. The course "Topics" Web page contains links to documents describing how to use CVS or Subversion on the penguins cluster.

Submission

Submit your source code files on penguins using these commands:

/u/cos333/bin/i686/submit 2 reg anyFilesUsedByReg
/u/cos333/bin/i686/submit 2 regdetails anyFilesUsedByRegDetails

If you use Java, then submit both your .java files and the corresponding .class files. Your grader should be able to type only reg or regdetails to execute your programs; your grader should not need to build your programs.

As noted above in the "Rules" section, either you or your partner, but not both, should submit files. You can submit multiple times; we'll grade the latest files that you submit.

Your readme file should contain:

Your readme file should be a plain text file. Don't create your readme file using Microsoft Word or any other word processor.

Please follow the rules on what to submit. It will be a big help to us and to your grade if you get the filenames right and submit exactly what's asked for. Thanks.

Grading

To grade your programs we will run them to execute multiple queries. Your grade will be based upon the correctness of their normal output, and the correctness and readability of their "human-readable" output.

It won't be possible for us to inspect your code manually. Instead we will grade your code based upon its behavior only. The good news: the code that you submit need not be well-styled -- although we strongly suggest that you use good style for your sake. The bad news: if your code fails the tests on some particular functionality, then we won't be able to inspect your code manually to try to assign partial credit for that functionality. So please make sure your code behaves properly.

This assignment was written by Robert M. Dondero, Ph.D.