Principles of Database Systems
Project 1 Solution
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.
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.
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.
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.
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.
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.
AdditionalInfo. AdditionalInfo is similar to OptionalInfo except with a set of different attributes plus loginName.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.
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.3. OtherTrip
OtherTrip has attributes tripId, date, reason, departureTime, arrivalTime, maxMinutesEarlyDeparture, maxMinutesEarlyArrival, costCompensation, and notes. tripId is the primary key.
OtherTrip is related to User in two relationships, Request and Offer. Requst has attribute numPassengers. Offer has attribute maxNumPassengers.
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.
OtherTrip is related to Address for both from address and to address.
Each OtherTrip must have exactly one from Address and one to Address.