Spring 2002
Stony Brook
Principles of Database Systems
Annie Liu
Project 1
Handout P1
Feb. 6, 2002
Due Feb. 19

Our project for this course is

Stony Brook Car Pooling Management System

Requirements specification

We will design and implement a simple interactive system based on a relational database that will support the Stony Brook community by providing car pooling information management functionalities. The project will be split in four parts. The first part will be done individually (so that everyone will have to think over the design of the system and have a good understanding of the problems involved). The next three parts will be done in groups of two students (so that everyone can have someone else in class to share the happy and unhappy experiences during the implementation).

This system will manage information for both daily commutes and non-daily trips. It will have a registration process that collects required information about a user and provides services afterward for updating required information, adding/deleting/updating requests and other information, querying information, matching requests, etc.

The registration process needs to be completed by all users prior to use of any of the provided services. During this registration, the following required information will be collected:

Once registered, a user can change the required information and can add, change, or delete the following optional information of him or herself at any time. Personal information:

For daily commutes: a valid time frame (semester), and the following information daily or for some days of the week: For non-daily trips, the following information: Additional information (assuming we will put the system on the web):

When new commutes or trips information is added, the system will automatically query existing information to report any matches. The system will allow the users to search by address, weekday / date, time, driver or passenger, a combination of them, etc. Information obsolete will be automatically deleted by the system.

The system will have a number of integrity constraints. For example, each user can have at most one daily commute schedule; only car owners may offer rides; the end time must be after start time; etc.

Acknowledgment: The idea of a database management system for car pooling at Stony Brook was from Francesco Gallarotti, a student in our own class.

What to do? Your task for the first project assignment is to create an Entity-Relationship (E-R) diagram for the data required to support this application. This diagram (model) should support all the aspects mentioned in the requirements specification.

In particular, please identify a set of integrity constraints that you think must be satisfied, as complete as possible. Specify them, and describe how, if possible, each of them is reflected in your E-R diagram.

If there are alternative models that are worth mentioning, please do so and explain the trade offs.

What to deliver? This should be a clearly drawn document (printed or handwritten) using the modeling figures of the E-R framework, plus your description of possible alternatives and integrity constraints (printed or typed).

Reminder: You must work individually and the work you turn in must be your own.