CSE/ISE 305
Spring 2002
Stony Brook 		Principles of Database Systems
Annie Liu
Project 1 Solution 		Handout P1S
Mar. 2, 2002

A sample E-R diagram is as given in Lecture 9; the most important alternatives and trade-offs are as discussed in class. Below is a summary of some of them.

The three most basic entity types are User, DailyCommute, and OtherTrip.

1. User

User has attributes loginName, password, lastName, firstName, phone, and email. loginName is the primary key.

User is related to several other entity types: Student, FacultyStaff, Address, Car, OptionalInfo, and AdditionalInfo.

You could put some of the information into attributes of User, as explained below. They are generally not as good models since they mix concepts and lack modularity, and may lead to inefficient implementation (waste of space for storing data, etc) and make it difficult to evolve the database.

Student, FacultyStaff. Both Student and FacultyStaff IsA User and have loginName as the primary key.

Student has also attributes major and status. FacultyStaff has also attributes dept and position.

You could have an additional attribute in User for studentOrNot and have attributes major/dept and status/position, if we assume/require that major and dept have the same domain, and similarly for status and position.

Address. Address is used to combine two entity types CampusAddr and ResidenceAddr and has only an attribute addrId, which is the primary key.

Both CampusAddr and ResidenceAddr IsA Address and has addrId as the primary key. Their attributes are exactly as specified in Handout P1.

We add the constraint that each User lives at at most one Address.

You could use two entity types Commuter and NonCommuter that either IsA User or IsA Student, and put attributes for CampusAddr with NonCommuter, and attributes for ResidenceAddr with Commuter. Both lacks the unifying concept of address; using IsA User is better since the other one fragments the address information even more. This is only partially correct for our system, since a uniform Address is needed for the OtherTrip described below.

One could also require that other attributes in CampusAddr and ResidenceAddr be unique, similar to as for Car below.

Car. Car has attributes carId, manufacture, model, and year. carId is the primary key.

We assume that a user does not have more than one car of the same manufacture, model, and year, and impose the constraint that manufacture, model, and year are together unique.

We also add the constraint that each Car must be owned by at least one User.

The constraint is based on the fact there are a relatively small number of cars that can be differentiated based the three attributes. It saves space and allows queries that compare cars most efficient.

You could use some physical car identity, such car registration number, as the primary key, but that would have as many copies of the car information as there are physical cars. You can not use loginName as the primary key, since a User can have multiple cars.

OptionalInfo. OptionalInfo has loginName as the primary key and other attributes age, sex, smokerOrNot, perferDriveOrRide, commuteCostCompensation, and currentCommuteMethod.

We add the constraints that each User has at most one OptionalInfo entity, and that each OptionalInfo entity corresponds to exactly one User.

You could also use an attribute optionalInfoId as the primary key and require the rest to be unique, similar to Car. This is not very good here, because commuteCostCompensation is an attributes which may have a big range of values.

AdditionalInfo. AdditionalInfo is similar to OptionalInfo except with a set of different attributes plus loginName.

This does not add new concept, except that perhaps the need of keeping the history of comments. We plan to remove this information for the rest of the project. 2. DailyCommute

DailyCommute has attributes loginName, startTime, endTime, maxMinutesEarlyArrival, maxMinutesLateDeparture. loginName is the primary key.

We have the constraints that each User can post at most one DailyCommute entity, and that each DailyCommute must correspond to exactly one User.

Again, loginName is used as the primary key because every User is more likely to have completely different schedules and flexibilities.

If you were more careful, you could have startTime, endTime, maxMinutesEarlyArrival, and maxMinutesLateDeparture for each of the weekdays.

Semester. DailyCommute is related to an entity type Semester, which has primary key semesterId and other attributes year and season.

Each DailyCommute must correspond to exactly one Semester.

You could also put semester information as attributes of the DailyCommute. 3. OtherTrip

OtherTrip has attributes tripId, date, reason, departureTime, arrivalTime, maxMinutesEarlyDeparture, maxMinutesEarlyArrival, costCompensation, and notes. tripId is the primary key.

If you were more careful, you may have maxCostCompensation and minCostCompensation.

OtherTrip is related to User in two relationships, Request and Offer. Requst has attribute numPassengers. Offer has attribute maxNumPassengers.

You could also use an attribute of OtherTrip to indicate whether this is a request or offer or both, and put the attributes numPassengers and maxNumPassengers as attributes of OtherTrip. In fact, you could omit the attribute for request/offer, and use two number attributes as an indication for that.

We also have the constraint that each OtherTrip must correspond to exactly one user either through request or offer or both. This can be expressed partially in the E-R diagram: each OtherTrip must correspond to at most one user through request and at most one user through offer; we still need to express that if each OtherTrip must be related to at least one User either through request or offer or both, and that if an OtherTrip is related to User through both request and offer, then these two must be referring to the same User.

The alternative discussed above makes it much simpler to express the desired constraint: each OtherTrip corresponds to exactly one User.

OtherTrip is related to Address for both from address and to address.

Each OtherTrip must have exactly one from Address and one to Address.