CS 330



  1. Bibliography Database.
  2. Grading Database.
  3. Computer System Administrator Database


Rather that assigning contrived examples of pedagogical examples, I have chosen three "real" projects - databases that would be useful to us/me, if properly implemented. The specifications are relatively open-ended, and beyond the minimal functionality, you are supposed to think globally about useful features that might be implemented, discuss them with your partner and with me, and - if feasible - implement them. In the spirit of this class (a 300-level course, where a lot of independent thinking and independent study is expected from you), I do not provide fully specified projects. Working on the project will have to be done as a continuous process where interaction with your partner(s) and me - and intermediate class presentations for feedback from your colleagues - is to occur.

You should choose to implement one of the projects below, but at least one team should work on the first project, which also carries a higher reward if properly implemented. This is to say that I consider projects 2 and 3 slightly easier, and in order to get full credit and a grade of A for them, you might have to do a better job with details, user interface, theoretical analysis and final presentation.

It is acceptable for everybody to work on Project 1.

You should work as a team with another one (or at most two) partners. I strongly recommend that you work with a partner, even if you now think you can do the project on your own.

Project 1: The Geometry Bibliography Database

This project is about turning an existing database, currently implemented with ad-hoc methods, into a fully functional and useful Oracle database with easy access for users with valid accounts.
This is a bibliography database that is used by a couple hundred researchers all over the world in a manner which might be substantially improved using database technology. If this is not strong enough motivation for you, and if you want examples of other situations where a similar approach might be very useful, think about your lists of web bookmarks that you would like to categorize and put into a simple database for easy reference and access. In fact, such an application is likely to be designed by someone, somewhere - if not done already.

Currently, the GeomBib database is implemented as a huge Unix file with additional scrips for extracting the information from it. A description of the status of the database is available from here. (Thanks to Sam Schiffman who ftp-ed the descriptive files and transformed them into a series of easily accessible HTML documents.)
The task is to create a similar database with added functionality, especially with respect to administration and maintenance. There should be a database administrator who can create user accounts on this database. All users (generally speaking, these are researchers in computational geometry and assumed not to be sophisticated database programmers) will have permissions to make queries (from a predefined list of queries, that should at least match those currently available). Some users (authors of papers in the database) will have permissions to insert/update/modify entries in the database. A new feature that I would like you to think of (and, even if we decide that it cannot be implemented, at least I want to see a valid argument of why not) is the ability to create and use cross-references within the bibliography. A cross-reference is like a link from one article A1 to another article A2, whenever article A1 refers in its own bibliography to article A2.

Project 2: Grading Database

This project is to to expand on the Literacy Database in Access which I have shown you at the beginning of the semester, and turn it into an Oracle database, as well as extend its functionality to make it suitable for general grading databases for a variety of courses.
The main tables and types of forms/reports are to follow the already existing Access database application, which I will provide to you as a starting example.
Your task is to turn the Access database into Oracle and expand it with security features. The information in the database should be about students (with various information maintained about them, such as computer account and database access code), homeworks (including date submitted, various fields for feedback to students, and grade per homework), as well as various types of users. Users include: students, who should only read their own grades and some global statistics made available by the instructors; TAs, who should not be able to enter grades, but enter information in the "feedback to students" fields as they look on students homeworks; professor, who should enter formulas for computing grades based on performence, for each homework; get reports and statistics; adjust grades; and administrator, who is a mixture of professor/head TA/lab assistant, who determines fields in the homework tables for feedback to students and grading criteria, and runs some scripts to create the appropriate forms for the use of the TAs who enter the data.

The main task is to determine the security structure: user types and their roles and permissions, views. A second task is to implement a system for easy and secure access to the database by the various types of users, including easy modification of the database structure by the "administrator". A third task is to design a set of pre-defined reports based on frequent or periodic queries to the database that would be suitable for each type of user to ask.

Project 3: Computer System Administrator Database

This project is the least well defined one. There is only a global description of what I would like it to do: all the rest is your work! Students choosing to work on this projects will have to design the whole application. Figure out what kind of information should be maintained in the database, the queries and reports, design the table structure and deal with administration issues such as types of users, security and permissions. They should also have to enter some test data in the database and prove, at the final presentation, that it is functioning according to the specifications.
Design a Computer System Administrator Database, which keeps track of the various types of hardware, software, users and maintenance events that a typical system administrator has to deal with.

For example, take our department facilities. Our system administrator has to add new computers, install new software on them, add new users and schedule maintenance events (such as backups, adding and removing accounts, temporary halting systems for maintenance, etc.). I would like you to design a database that would keep track of these events, and answer queries such as: "What compilers are available on grendel?"; "When was a web browser last time installed on skor, and what is it?"; "What users on skor have permission to install software?"; "What is the date of the last backup?". Also, for each event, it should allow the update of the database records: "On Oct 22 1997, Eric installed Netscape Communicator - of type web browser - on grendel, in directory /local/Netscape/"; "On Jan 10, 1998, Alice installed a vrowser named Hot Bun on skor in directory C:\HotBun" - where, say, "vrowser" is a new category of software that just came out this year, and we'd like to add it in our database of software categories to keep an eye of future versions. The system should allow removal of software, discarding of old computers, etc. The system should allow only users with permissions to install software to update the tables that refer to the installed software; users with permissions to create accounts to update tables keeping track of accounts, etc.

I expect the database tables and the number of forms/reports to be sufficiently small so that, for this project, you can actually implement a real system that can be used by someone.
Ileana Streinu