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:

• login name
• password
• last name
• first name
• status (student resident / student commuter / faculty / staff)
• if student commuter or faculty or staff: residence address (street, town, state, zip code, nearest major intersection)
• if student resident: on campus address (street, room no)
• for student: major and status (freshman / sophomore / junior / senior / graduate)
• for faculty or staff: department and position
• contact phone number
• contract email address

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:

• age
• sex
• cars owned (manufacturer, model, year)
• smoker / non smoker
• preference (driver only / share driving / passenger only)
• range of cost compensation requested / offered
• if student commuter or faculty or staff: current method of commuting (drive alone / bus / train / car pool / other).

• start and end times
• flexibility arrival and departure times (yes / no) and by how many minutes

• date of trip
• reason of trip (e.g., football game)
• from and to addresses
• departure and / or arrival times
• flexibility arrival and / or departure times (yes / no) and by how many minutes
• ride offered / requested
• if ride offered: car and maximum number of passengers
• if ride requested: number of passengers
• range of cost compensation requested / offered
• notes

• how did you find this system / website (friend / employer / internet site / newspaper)
• if previous is internet site: name the website
• comments

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.

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.