Luxury-Oriented Scenic Tours (LOST) provides guided tours to groups of visitors to Rotorua City. In recent years, LOST has grown quickly and is having difficulty keeping up with all of the various information needs of the company. The companys operations are as follows

LOST offers many tours. For each tour, the tour name, approximate length (in hours), and fee charged is required. Guides are identified by an employee ID, but the system should also record a guides name, home address, and date of hire. Each guide must be trained by one and only one other employee referred to as their mentor. An employee may not be a mentor for any other employee, or they may train many employees.

Guides take a test to be qualified to lead specific tours. It is important to know which guides are qualified to lead which tours and the date that they completed the qualification test for each tour. The tour number and the qualification date are required to track the qualification (it is only stored if the employee passes the test). A guide may be qualified to lead many different tours. A tour can have many different qualified guides. New guides must be qualified to lead at least one tour, and a new tour must have at least one qualified guide.

When a tour is actually given, that is referred to as an outing. All tours at LOST are guided tours, so a guide must be assigned to each outing. Each outing has one and only one guide. Only officially qualified guides for that tour may lead the outing. Newly hired guides may not have ever been scheduled to lead any outings.

Every tour must be designed to visit at least four locations. For each location, a name, type, and official description are kept. Some locations (e.g. the Te Puia) are visited by more than one tour, while others (e.g. Rotorua Lake) 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 too.

LOST schedules outings well in advance so they can be advertised and so employees can understand their upcoming work schedules. 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 outings must be associated with a tour.

Tourists, called clients by LOST, pay to join a scheduled outing. For each client, the name, country of origin, and telephone number are recorded. Clients may sign up to join many different outings, and each outing can have many clients. Information is kept on all potential and current clients. Note that newly scheduled outings may not have any clients signed up yet.

Based on the provided information, draw an Entity-Relationship (ER) Diagram using the Crows foot model symbols and include all attributes. (a) Identify all possible entities and relationships. (b) Identify (i) strong and weak entities and (ii) identifying and non-identifying relationships. (c) Identify the main attributes in each entity including all primary and foreign keys (d) Identify the connectivity for each relationship. (e) Identify the mandatory/optional dependencies for the relationships. (f) Resolve all M:N relationships.