Reference no: EM132781579

A tours operator company wants to get its database developed. They have provided the following requirements.

The system needs to keep track of people. For each person, it records his/her address. Each address consists of country, province/state, city, street, street number, zip code/post code, and a list (possible empty) of phone numbers and a list of email address. 

Each person in the database can be an old customer (have taken a tour of the company), a current customer (is booked to take a tour or is on a tour right now), a tour guide, an employee (works for the tour company), or any mixture of these (for instance an employee can take a tour and so can be a customer as well). The sex and age of each person must also be recorded.

The system also keeps track of all tours, past and future. Each tour has a unique number, itinerary, guide (at least one, but may be more than one), its status (completed, in-progress, in-the-future), and the list of participants. The amount paid by each person for the tour is also recorded. Each person is provided with an itinerary that consists of list of the dates the tour covers and for each date it includes the place of breakfast, the place of lunch, the place of dinner, and the accommodation and room. The type of room whether shared or private should also be mentioned on the itinerary. Each day in the itinerary also includes a simple English description of the activities during that day.

Tasks to be completed:

Create a complete ERD with entities and relationships using given scenario.

Convert the ERD to a set of relational tables in at least 3NF and draw a dependency diagram

Create a database using MySQL- with primary keys, foreign keys, and other attributes mentioned for each entity using proper constraints.

Input some significant data in each table showing your understanding of the scenario.

Create FOUR queries. (One SELECT, one Numeric function and two JOIN)