Question

Create an ER model for the scenario. Make sure that you read the description carefully. Your diagram should reflect all entities, attributes, and relationships in the description. You should make sure each entity has a primary key (a unique identifier). Use the relationship types we used in class (one-to-one, one-to-many, or many-to-many). Don't forget, attributes can describe both entities and relationships.

Scenario 2: Tracking Trips for the SchUber Taxi Service

A new Philadephia startup called SchUber is a matching service between freelance taxi drivers and passengers. The database to support the service tracks trips, passengers, and ratings that drivers and passengers can give each other.

The basic transaction for SchUber is the trip. A trip is described by the date and time the trip occurs and the fare. A trip involves one driver and at least one passenger. A driver has a first name, a last name, a social security number, and an email address. Drivers’ information is collected before they start offering any ride (hence it is possible for new drivers to have no trip yet). A passenger has a first name, a last name, and an email address. Note that only passengers who paid the fare are recorded. If there are multiple passengers on a trip, they can split the fare, and each of their paid amounts should be recorded in the database.

The database keeps track of addresses. An address is described by the street address, city, state, and zip code. A trip is associated with exactly two addresses, an origin address and destination address. An address can be included in the database even if it is not associated with any trip.

Passengers have the option to associate one address with their account (their home address) – this is useful for billing and for making a request for a home pick-up. Some addresses may not be associated with any passenger, and some addresses are associated with multiple passengers.

The rating system is one of the key features of SchUber. Both drivers and passengers can have ratings, although some of them may not have any ratings, especially if they just started with the service. When a driver or a passenger submits a rating regarding a trip, they give a numeric score (1 to 5), a time of rating, and the type of rating (i.e., for driver or for passenger) is also recorded. Therefore, each rating is associated with a driver, a passenger, and a trip. A trip may have no rating at all, or could have multiple ratings (for both the driver and the passenger, or if there were multiple passengers on the trip). A driver or a passenger may have no rating at all, or could have many ratings over time.

Answer 1