Case Background Hiraeth Cruises has been in the cruise business for the last 30 years. They...

Transcribed Image Text:

Case Background Hiraeth Cruises has been in the cruise business for the last 30 years. They started storing data in a file-based system and then transitioned into using spreadsheets. As years progressed, their business has grown exponentially leading to an increase in the number of cruises and volume of data. You have been recently employed a database model to replace the current spreadsheets. You have been provided with the following business rules about Hiraeth Cruises. This is only a section of their business rules. Vessels: Every vessel is uniquely identified using a primary identifier. Other details of a vessel include the name, and the year it was purchased. Every vessel is of a particular model. Every model is identified with a unique identifier. The name of the model and the passenger capacity for the model are recorded. The vessels are serviced in service docks. Every service dock is identified using a primary identifier. The name of the dock is also recorded. A vessel could get serviced in multiple docks. Every time the vessel is serviced, the service date, and multiple comments about the service are stored. There are three types of vessels: small, medium, and large vessels. Cruises: Every cruise is uniquely identified using a primary identifier. Other details of a cruise include the name, and the number of days the cruise goes for. There are two types of cruises: short and long cruises. A vessel gets booked by the cruise for a few months which are also recorded. The short cruises use small vessels, whereas the long cruises use either a medium or a large vessel. The cruises are created along a particular route. Every route is identified using an identifier. The description of the route is also stored. A route will have a source location, a destination location and multiple stopover locations. Each location is identified by a location code. The name of the location is also stored. Tours: Every cruise offers a unique set of tours for their customers. A tour code is used to identify a tour within every cruise. Other details of the tour such as the name, cost, and type are stored. A tour could be made up of other tours (a package). A tour could be a part of multiple packages. A tour will belong to a particular location. A location could have multiple tours. Staffing: Every Hiraeth staff member is provided with a unique staff number. The company also needs to keep track of other details about their staff members like their name, position, and their salary. There are two types of staff that need to be tracked in the system: crew staff and tour staff. For crew staff, their qualifications need to be recorded. For tour staff, their tour preferences need to be recorded. There are three types of tour staff - drivers, our guides, and assistants. The license number is recorded for the driver and the tour certification number is recorded for the tour guide. In certain instances, the drivers will need to be tour guides as well. Tour staff work for a particular location. Scheduling: A schedule gets created when the cruise is ready to handle bookings. The start date and the max capacity that can be booked are recorded. Every schedule has a detailed roster of the staff involved in the cruise including the crew and the tour staff. The start and end time for every staff will be stored in the roster. Task Description Task 1- EER Diagram (60 marks) Based on the business rules, you are expected to construct an Enhanced-ER (EER) diagram. The EER diagram should include entities, attributes, and identifiers. You are also expected to show the relationships among entities using cardinality and constraints. You may choose to add attributes on the relationships (if there are any) or create an associative entity, when necessary. Your diagram should also specify the complete (total) and disjoint (mutually exclusive) constraints on the EER. Task 2-Logical Transformation (40 marks) Based on your EER, perform a logical transformation. Please use 8a for your step 8 to keep the process simple. Please note, if there are errors in the EER diagram, this will impact your marks in the transformation. However, the correctness of the process will be taken into account. Case Background Hiraeth Cruises has been in the cruise business for the last 30 years. They started storing data in a file-based system and then transitioned into using spreadsheets. As years progressed, their business has grown exponentially leading to an increase in the number of cruises and volume of data. You have been recently employed a database model to replace the current spreadsheets. You have been provided with the following business rules about Hiraeth Cruises. This is only a section of their business rules. Vessels: Every vessel is uniquely identified using a primary identifier. Other details of a vessel include the name, and the year it was purchased. Every vessel is of a particular model. Every model is identified with a unique identifier. The name of the model and the passenger capacity for the model are recorded. The vessels are serviced in service docks. Every service dock is identified using a primary identifier. The name of the dock is also recorded. A vessel could get serviced in multiple docks. Every time the vessel is serviced, the service date, and multiple comments about the service are stored. There are three types of vessels: small, medium, and large vessels. Cruises: Every cruise is uniquely identified using a primary identifier. Other details of a cruise include the name, and the number of days the cruise goes for. There are two types of cruises: short and long cruises. A vessel gets booked by the cruise for a few months which are also recorded. The short cruises use small vessels, whereas the long cruises use either a medium or a large vessel. The cruises are created along a particular route. Every route is identified using an identifier. The description of the route is also stored. A route will have a source location, a destination location and multiple stopover locations. Each location is identified by a location code. The name of the location is also stored. Tours: Every cruise offers a unique set of tours for their customers. A tour code is used to identify a tour within every cruise. Other details of the tour such as the name, cost, and type are stored. A tour could be made up of other tours (a package). A tour could be a part of multiple packages. A tour will belong to a particular location. A location could have multiple tours. Staffing: Every Hiraeth staff member is provided with a unique staff number. The company also needs to keep track of other details about their staff members like their name, position, and their salary. There are two types of staff that need to be tracked in the system: crew staff and tour staff. For crew staff, their qualifications need to be recorded. For tour staff, their tour preferences need to be recorded. There are three types of tour staff - drivers, our guides, and assistants. The license number is recorded for the driver and the tour certification number is recorded for the tour guide. In certain instances, the drivers will need to be tour guides as well. Tour staff work for a particular location. Scheduling: A schedule gets created when the cruise is ready to handle bookings. The start date and the max capacity that can be booked are recorded. Every schedule has a detailed roster of the staff involved in the cruise including the crew and the tour staff. The start and end time for every staff will be stored in the roster. Task Description Task 1- EER Diagram (60 marks) Based on the business rules, you are expected to construct an Enhanced-ER (EER) diagram. The EER diagram should include entities, attributes, and identifiers. You are also expected to show the relationships among entities using cardinality and constraints. You may choose to add attributes on the relationships (if there are any) or create an associative entity, when necessary. Your diagram should also specify the complete (total) and disjoint (mutually exclusive) constraints on the EER. Task 2-Logical Transformation (40 marks) Based on your EER, perform a logical transformation. Please use 8a for your step 8 to keep the process simple. Please note, if there are errors in the EER diagram, this will impact your marks in the transformation. However, the correctness of the process will be taken into account.

Answer rating: 100% (QA)

1Your E... View the full answer