In developing your final logical data model, composite attributes present on your conceptual model must be expanded into their component simple attributes, unless otherwise directed. If the supplementary material presented in this document does not guide you in deciding the components you may make any reasonable decision on their simple component attributes.

Further discussions with World Cruises have revealed the points listed below:

1. World Cruises record for each passenger their contact phone number, for a minor no contact number will be recoded, the contact for their guardian will be used (in modelling this point keep phone number as a simple attribute of passenger, do not create a further PHONE relation)
2. at ports, where a cruise stops, local entrepreneurs offer various tours to the passengers which they can participate in while the ship is in port. The tours are not specific to a particular cruise, any ship which is in port can have passengers partake in an advertised tour. Some tours provide audio commentary to help explain what passengers are experiencing. Where possible the tours are encouraged to supply the commentary in a range of languages so as to support the needs of as many passengers as possible. The commentary languages available for a tour must be stored as part of your model.

Tours are offered at regular intervals for example every weekday, every Saturday, on Monday only, etc (this range of offerings may be changed regularly, including adding new ranges). A particular tour only runs once on any given date.

Each offering of a particular tour uses the same tour number. Tour numbers are reused for each port ie. each port has a tour number 1. Each tour has a minimum number of participants, a particular tour offering is not run unless this minimum number of participants have booked for it.

3. cabins across the various ships are assigned a cabin class as one of the following
   • interior

ocean view

balcony, or

suite

These classes are fixed and will not be modified.

4. country codes stored in the system make use of the ISO 3166-1 Alpha-2 codes eg. Australia is AU
5. languages stored in the system are stored as ISO 639-1 Alpha-2 codes eg. English is

EN

6. latitudes and longitudes stored in the system are stored in decimal form, for example the latitude and longitude of Hobart, Tasmania, Australia is:
   • latitude: -42.8825088

longitude: 147.3281233

World Cruises have supplied the following forms which are used within their business:

1. Tours which are available in a given port

Port Tours Available

Port code AUHOB

Port Name Hobart

Country Code AU

Country Name Australia

Port Temperature

Month	Average High	Average Low
Jan	22	13
Feb	22	13
Mar	20	12
Apr	18	10
May	15	8
Jun	12	6
July	12	6
Aug	14	6
Sep	16	7
Oct	17	8
Nov	19	10
Dec	21	11

Tours Available

Tour Number	Tour Name	Tour Description	Hours Required	Tour Cost per Person	Wheel Chair Access	Tour Availability	Start Time
1	Horbat Shopping	mmmnnnnnnn	2.5	15	yes	Weekdays	11:00
2	Port Arthur		8	270	No	Dairy	09:00
3	MONA		4	70	yes	Dairy	13:00
4	Salamanca Market		3	15	yes	Saturday	11:00

ii) Participants for a particular tour instance:

Port - Tour Participant Report

Port Code AUHOB Generated: 24th March 2022

Tour number 3

Tour Name MONA

Tour Date 25th March 2022

Tour Start Time 13:00

Passenger ID	Passenger Name	Principal Spoken Language	Cruise ID	Cruise Name	Payment Received

Task to complete:

1. Perform normalisation to 3NF for the data depicted in the sample documents.

The approach you are required to use is the same approach as shown in the normalisation applied class solution. The normalisation must be carried out form by form, beginning by representing the document you are working on as a single UNF relation and then moving through 1NF, 2NF and 3NF.

During normalisation, you must:

• Not add surrogate keys.

Include all attributes (you must not remove any attribute as derivable) Clearly show UNF, 1NF, 2NF and 3NF.

Clearly identify the Primary Key in all relations.

Clearly identify all dependencies at the various normalisation stages (Partial at 1NF, Transitive at 2NF and Full at 3NF). You should use the same notation as depicted in the normalisation sample solutions, for example: attr1 -> attr2, attr3

If none exist you must note this by stating:

No partial dependencies present and/or

No transitive dependencies present If required, carry out attribute synthesis.

The attribute names used throughout your normalisation and those on your subsequent logical model must be the same.