IY014 Repeat Information System Analysis & Design Assignment Task Description:
Sunderland Cruise Ships organises cruises in Europe, North America and the Far East. During the cruise, the ship docks 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 booked.
Task 1 Normalise Data for the Cruise Ship Excursion Booking System
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.
Describe what normalisation is and what are its advantages and disadvantages.
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.
Task 2 Class Diagram
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.
Task 3 Create the Database
Using SQL create the tables you design in third normal forms. This will require you to identify the appropriate data types and their sizes, the primary and foreign keys. You should input an appropriate amount of data into these tables. You should show the command in the SQL environment and the outcomes from creating the tables and storing the data.
Task 4 Use SQL Command to Adapt Data and Show Information
In this task you will create the appropriate commands to change the data in the tables and to display information related to the storage of information. You should show the actual commands run in the SQL environment and the output you create.
i) Update the cost for excursions where the port is ‘Copenhagen’ so they
are increased by a percentage equivalent to the 4 th and 5 th characters in
your T-Number. For example, if your T-Number is T003456 increase the
excursion prices by 34%.
ii) Create a list that shows the orderID, passenger number, and passenger
name and passenger cabin. The headings for the columns should be
“Order ID”, “Passenger ID”, “Name of Passenger” and “Cabin”.
iii) Display the passenger details for all passengers going on an excursion
in St Petersburg or Oslo.
iv) Display the order number, passenger name and who has bought more
than 4 tickets on the C001 excursion.
v) If the last character of your T-Number is greater than 6, create
the SQL to count the number of passengers by excursion leader. Include
the excursion leader’s name.
If the last character of your T-number is less than 6, produce
the SQL to get how many of each excursion are booked. Include the
excursion name. If the last character in the T-Number is equal to 6, produce the SQL to determine how many excursions are booked by cruise name.
vi) Display the passenger details, excursion details for passengers whose
name starts with the first two letters of your first name but do not include
the last 2 letters of your last name.
vii) Display all excursions where the price in pounds is between the 4 th and 5 th characters of your T-number and the 6 th , 7 th and 8 th characters of your T- number. For example, if your T-number is T0086299, you should return all excursions whose price is between £86 and £299.
viii) Get all the passengers who have booked one or more of the
excursions that Weber is on.
ORDER This IY014 Repeat Information System Analysis & Design Assignment NOW And Get Instant Discount
Read More :-