Describe the possible relationships between the various entities.

Q1:
Washington DC Tours offers guided sightseeing tours around the nation’s capital to groups of visitors. The company’s
operations are as follows.
a. Washington DC Tours offers many different tours. Each tour has a fixed price rate per visitor regardless of their
age. For each tour, the tour name, approximate length, and price per visitor are needed.
b. Every tour must be designed to visit several locations. For each location, a name, official description, and
admission requirements are kept. Some locations (such as the White House) are visited by more than one tour,
while others (such as Arlington Cemetery) are visited by a single tour. All locations are visited by at least one
tour. The order in which the tour visits each location should be tracked as well.
c. When a tour is given, that is referred to as an “outing.” A tour can have many scheduled outings, although newly
designed tours may not have any outings scheduled. Each outing is for a single tour and is scheduled for a
particular date and time. All the outings must be associated with a tour.
d. A visitor can make a reservation for a scheduled outing for one or more travellers. When a visitor makes a
reservation, the visitor’s name, address, telephone number, reservation date, and the number of travellers are
recorded. Visitors may sign up to join many different outings, and each outing can have many visitors.
Information is kept only on visitors who have signed up for at least one outing, although newly scheduled outings
may not have any visitors signed up yet.
e. All tours at Washington DC Tours are guided tours, so a driver tour guide (DTG) must be assigned to each outing.
The DTG act as both driver and tour guide. Each outing has one and only one DTG. DTGs are identified by an
employee ID, but the system should also record their name, home address, date of hire, and contact number.
f. Visitors can also make hotel pickup requests at the time of the reservation if needed. The company provides the
service based on the availability of DTGs. The company maintain a hotel pickup schedule and record pickup date
and time, and the location.
g. DTGs pick up visitors from hotels when they are not guiding any tours. Each DTG may be assigned to several
scheduled hotel pickups based on their availability.
h. The visitor can make partial or full payment at the time of the reservation. The company also records the total
amount associated with the reservation and their current outstanding balance on the tour. If the visitor pays for a
tour in instalments, the outstanding balance is modified each time a payment is made.
1. Identify all the entities in this company. (5 points)
2. For each entity, list its attributes. (5 points)
3. Describe the possible relationships between the various entities. For example, a tour can have many scheduled
outings and each outing must be associated with one and only one tour. (5 points)
4. Now you are ready to create a Crow’s Foot notation E-R diagram for this case. For each entity, present the
attributes, the primary key, and foreign keys (if any). Name each relationship and diagram both the minimum and
maximum cardinalities. If you cannot infer the cardinalities from the case description, make your own
assumption. You can use MySQL workbench, Visio or any other drawing software to draw your ERD. (15 points)

Last Completed Projects