Lennyrogal 0 Newbie Poster

I do have this assignment I have been working on for a while. My lecturer told us that we can produce few different EER models for this assignment and I would like to share with you my point of view at this problem and get as many other ideas and critical answers on what am I doing wrong.

Here is the scenario:

A cruise company has an existing database system to deal with bookings, but in the light of security issues around some of the ports at which it calls, it needs to record and track more details of its passenger’s whereabouts during shore excursions.

From the brochure we can see that the company operates several different ships and several different cruises. At a port, passengers may join an excursion or may sight-see independently. Any particular cruise will include several shore excursions, some of which are included in the base price and some for which there is an extra charge. Different ships will, at different times, use the same excursions.

Passengers may book excursions in advance or, subject to space being available, they may book on board ship. There may be a maximum number of spaces available on some excursions. There will always be a minimum. There may be various attractions visited on excursions (eg. Theme Parks, Art Galleries, Museums, Historical Sites, etc). Passengers must buy all tickets required for entry to attractions, on board the ship before they disembark.

Once on board, passengers are issued with a Ship Card which they use to pay for any services or goods on board and also to check out of and into the ship. It is this system that we need to enhance.

When planning the excursions, the purser’s office must book sufficient buses to take all booked passengers, although there may be a preset maximum that can be accommodated. At the moment no record is kept of which passengers are allocated to each bus, so although they know which passenger has booked on which excursion, they do not know which bus any individual is on.

Every bus is also allocated 2 escorts. Crew members who are available for escort duty must have current first aid certificates, (within the last 2 years) and have received escort training in the last year. For use in emergencies, they will carry a mobile phone belonging to the ship.

There are guest lecturers on each cruise, who are classed as passengers, not as crew. Each may bring one guest. They or their guest may also act as escorts, provided that the other escort is a crew member.

On the day of the excursion, passengers collect a card with the number of one of the buses. They hand over the bus card and swipe their ship card at the hatch as they leave the ship.

Assignment Tasks:

Part 1
Your first task is to design an extension to the existing database to support an excursion booking and tracking facility for the Purser’s office. Passenger, Crew and other tables already exist, but you may need to specify new columns for extra data in these tables, - as well as creating additional tables needed by the extended system. You need to:

1. Produce an Extended Entity Relationship diagram for the required system based on the notation given in Connolly Edition 4 or 5. [10]
2. Use UML notation to model the required system, including at least a Use Case diagram, a Class diagram and an Object Interaction diagram. [10]
3. Describe how the subclass/superclass relationships and the type of specialisation involved in each were developed. [5]
4. Explain the features of your models which will require special consideration when attempting to implement these models using a Relational Database. [5]
5. Specify the tables which you would propose to implement and explain how you have overcome the problems identified above. [10]

Here is what I have done:

Any suggestions are more than welcome. I just started to learn how to create ER models so forgive me any sily mistakes and thank you!