Question

Assignment Task Description:

Sunderland Cruise Ships organises cruises in Europe, North America and Far East. During the cruise, the ship dock at various ports. Although passengers are free to explore the ports themselves, the company offers excursions of interest in the port. Once the passengers have booked their cruise, they are in a position to book the excursions related to the cruise. The passengers can either book the excursions online, by phone or in person once the cruise has started. There are a restricted number of places on the excursions and so when they are full up the person goes on a waiting list to see if anyone pulls out of the excursion. Once a person has booked tickets for the excursion their details are stored and they are provided with further information on what will be involved. There will be recommendations on clothing and footwear to bring and whether food is provided. If a person does cancel a booking within 2 weeks of the excursion they will receive a refund minus a £20 admin charge per seat booted.

Task 1 Normalise Data for the Cruise Ship Excursion Booking System (30 marks)

Sunderland Cruise Ships have heard that they should use a relational data for maintaining the customer details and excursions that are booked. Explain to them what a relational database is. This description should include the advantages and disadvantages of this approach with regards to the system being developed and the data being stored.

Take the preliminary un-normalised table for the Excursion Booking System below and put it into third normal form.

Order ID

Passenger ID

Passenger Name

Cabin

Cruise No

Cruise Name

Port

Excursion No.

Excursion

Qty

Price per excursion

Excursion leader ID

Excursion Leader

Total Cost

O23

P001

Weber

2345

1012

Baltic Highlights

Copenhagen

C001

Little Mermaid

5

200

E0001

Wermter

1900

Oslo

O002

Museums

2

150

E0002

Smith

St Petersburg

P002

Palaces

1

300

E1008

Jones

St Petersburg

P003

Ballet

3

100

E1008

Jones

O32

P005

Elshaw

3777

2121

Fjords

Bergen

B001

Biking

4

50

E0070

Malone

1450

Bergen

B111

Hiking

10

75

E0070

Malone

Holden

H002

Puffins

5

100

E0101

Ham

O01

P003

Brown

8124

1012

Baltic Highlights

Oslo

O002

Museums

3

150

E0002

Smith

1050

St Petersburg

P002

Palaces

2

300

E1008

Jones

1) Describe what normalisation is and what are its advantages and disadvantages.

2) You should take the data in the un-normalised form and go through the steps to put the data in third normal form. Ensure you show the tables and keys produced in first, second and third normal form.

3) Create a class diagram based on the description of the system given above. Show the classes, attributes, methods and the correct relationships that exist between the classes.

Answer 1

1)

Normalisation: It is the process of organisation data i.e.., columns and tables in such a way that there is less duplication of data. Final goal is to attain less number of repeated instances or attributes of data.

Generally improper database design leads to update , deletion and insertion anamolies. By doing normalisation of tables we can eliminate those anamolies

Advantages:

• Overall size of database is comparitiely less when compared to un normalised tables
• lesser database size faster database access
• faster maintainance due to fewer indexes
• More data records per page because resultant tables have lesser colums

Disadvantages:

• More joins are required because data is spread in different tables
• requires good knowledge about normalisation
• performance decrease when query involves tables to joined and other operations to e done.