- Learn to use Oracle.
- Design a database application from first principles.
- Apply theoretical concepts done in class.
- Raise problems and understand limitations of current technologies.
- Bibliography Database.
- Grading Database.
- 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,
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
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
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
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
(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
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
Netscape Communicator - of type web browser - on grendel, in directory
/local/Netscape/"; "On Jan 10, 1998,
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.