Reference no: EM132303641

Need an ER diagram

Caroline and Larry run a company that organizes trekking trips around the world for small groups of customers. These usually involve anywhere between 4 to 10 people per group. They asked you to help them design a database that will keep track of their bookings. You have conducted interviews with both and wrote down the information relevant to the database design. Use the below requirements to construct an ER diagram.

The database should keep track of customers and trips. For every customer we would like to keep track of their contact information (first & last name, address, phone, email). For every trip offered, we will keep track of its location (e.g., country), name, description, duration (beginning and ending dates), cost (per person), expected daily activity time (hours per day), average distance to walk every day, the terrain type (e.g., flat, hills, etc.), elevation change, and altitude at which the trip will take place. Both customers and trips will also need an internally assigned unique identifier (e.g., CustomerID and TripID)

Customers should be able to book many trips over time. Think of booking as a form of customer placing an order for a product (i.e., a trip). Customers should be able to create many bookings over time, but each single booking should be associated with one customer only. Each booking may include many trips (for instance, a customer may wish to combine a trip to Peru with a trip to Galapagos Islands), but a minimum of one trip is required. Each trip may be part of many bookings, but it is possible that we may have a trip in our offer that will never get booked. Each booking should have its own identifier, the date when it was created, and the total amount for which the customer was charged.

When a customer books a trip, we would like to know the personal details of the people for whom the customer booked a trip. For instance, we may have a customer book a trip for himself/herself and their family. We can collectively refer to them as participants. Any given booking might have only one participant, but it may have several as well. For each participant, we would like to keep track of their full name, contact information (phone, email), date of birth, height, weight, and the overall fitness level.

Caroline and Larry work with travel agents in each trip location. For each travel agent, the database should keep track of their personal and contact information. Each trip will only have one travel agent associated with it, but the same travel agent might be responsible for several trips. In addition, each trip may require one or more guides, with the same guides being able to work with many trips. For each guide, we would like to keep track of the personal and contact information.

Some trips may require specific hotel arrangements, while other may (for example, there may be not hotel available in high mountain areas). It is possible that several different hotels will have to be booked for a single trip. For each hotel we will want to know its name, and exact address.