A yacht charter company requires a web-based system to manage aspects of its holiday charter business.

There are several customers, each of whom may book several holiday charters, with each charter booked by an individual customer. A holiday charter may involve one yacht only, but each yacht may be involved in many holiday charters. A holiday charter may visit several ports and each port will be visited by many holiday charters. Most ports have several yachts based in them (although a few smaller ports have no yachts based in them) with each yacht based in just a single home port.

 

Information to be held and manipulated include:

 

• the name, nationality, email address, phone number, and id number of each customer.
• the name, type, model, home port, number of berths, and cost of hire per day of each yacht.
• the name, phone number, email address, and a number of docking places at each port.
• the charter id, start date, and duration of each charter, and the visit id, expected date of arrival, and duration of stay at each port visited.

Business processes to be supported include the ability to:

 

• add details of a new customer together with the start date and duration of the charter they have booked, but without specifying the yacht to be used or the ports to be visited.
• list the total length of stay, between two given dates of yachts in the fleet in each port.
• get a list of yachts (by name) visiting their home port between two given dates, together with the date of arrival and the length of the stay.
• list the ports visited by a given customer together with the date of arrival and length of stay, ordered by date.
• remove a yacht temporarily for some time (e.g., for servicing) and get a list of yachts that are not available.

Task one: Noting any simplifying assumptions you make, design a relational database using either Chen's notation or Crow Foot notation, capable of supporting the given business scenario and storing the data provided in the sample data file.  Your design should include Relationships and any participation constraints

 

Task two: Implement your database using MySQL and populate it with the data provided.  Write SQL code to implement your database design. You should document your code and use constraints, default values, ON DELETE clauses, etc as appropriate for the business scenario.  The use of wizards is prohibited

 

Task three: Develop SQL code to test that your database supports each of the business processes given above

 

Try to answer these questions along:

Explain how you design your database

How many tables were decided to create and why?

How many attributes are there in each table and why?

what type of relationships do the entities have?

Explain the code and results of each step

mention what the code