FIT9132: CREATE TABLE and CONSTRAINT definitions which are missing from the supplied partial schema script: Introduction to databases Assignment, MUM, Malaysia

INSTRUCTIONS

World Cruises books passengers on ships that host cruises. Each ship is operated by a particular company known as the operator. Each operator is assigned an operator id as an identifier and has the company’s name and Chief Executive Officer’s name recorded.

A given operator may operate one or more ships. For each ship, World Cruises records a ship code to identify the ship, the ship’s name, the date the ship was commissioned, the ship’s tonnage, its maximum guest capacity, and the name of the country in which the ship is registered.

The cabins on a given ship are identified by a cabin number (such numbers may be reused across ships eg. many ships may have a cabin number 211). World Cruises records for a given ship, the capacity of a particular cabin and the class of the cabin (this class classifies the quality of the experience and services available). The cabin class is one of the following: interior, ocean view, balcony, or suite.

A cruise makes use of a particular ship (a cruise only uses one ship) and departs on a particular date and time. Each such cruise is identified by a cruise id. World Cruises records the name of the cruise, a brief description, and the duration (in days) of the cruise.

Passengers register with World Cruises when they make their first cruise booking. Each passenger is assigned a unique id. The passenger’s name is recorded as first name and last name. World Cruise also records the passenger’s gender and date of birth. If the passenger is a minor (ie. under 18 years of age), a booking can only be accepted if another passenger on the same cruise can act as a guardian. The guardian must be identified by the system.

World Cruises record for each passenger their contact phone number, for a minor no contact number will be recorded, the contact for their guardian will be used.

World Cruises maintains a manifest for all cruises they manage. This manifest records for each cruise, the cabin which has been allocated for each passenger (this allocation is carried out when the passenger is booked on the cruise). All cruises board passengers only at the cruise’s originating port on the scheduled departure date and time. For each passenger taking part in a cruise, WC also records the date and time when they first boarded the ship. Passengers may board up to 24 hours earlier than the scheduled departure.

A model to represent this system has been developed:

The schema/insert file for creating this model (wc_schema_insert.sql) is available in the archive ass2_student.zip – this file partially creates the World Cruise tables and populates several of the tables (those shown in purple on the supplied model) – you should read this schema carefully and be sure you understand the various data requirements. You must not alter the schema file in any manner, it must be used as supplied.

TASK 1: DDL

For this task you are required to add to T1-wc-schema.sql, the CREATE TABLE and CONSTRAINT definitions which are missing from the supplied partial schema script in the positions indicated by the comments in the script.

The table below provides details of the meaning of the attributes in the missing two tables. You MUST use identical relation and attribute names as shown in the data model above to name the tables and attributes which you add. The attributes must be in the same order as shown in the model. You must use delete RESTRICT/NO ACTION for all FK constraints. These new DDL commands must be hand-coded, not generated in any manner (generated code will not be marked).

To test your code you will need to first run the provided script wc_schema_insert.sql to create the other required tables. wc_schema_insert.sql, at the head of the file, contains the drop commands for all tables in this model. If you have problems with Task 1 and/or Task 2 simply rerun wc_schema_insert.sql which will cause all tables to be dropped and correct the issues in your script. Do not add DROP TABLE statements to either of your Task 1 or Task 2 scripts.

TASK 2: Populate Sample Data

Before proceeding with Task 2, you must ensure you have run the file wc_schema_insert.sql (which must not be edited in any way) followed by the extra definitions that you added in Task 1 above (T1-wc-schema.sql).

Load the PASSENGER and MANIFEST tables with your own test data using the supplied T2-wc-insert.sql script file, and SQL commands which will insert as a minimum, the following sample data:

• 15 PASSENGER entries
  • Included at least 5 passengers who are under 18 years of age
• 30 MANIFEST entries
  • Included at least 10 passengers
    • out of these 10 passengers, at least 4 of them are under 18 years of age
  • Included at least 5 cruises which uses at last 3 different ships
  • Have at least 2 passengers who completed more than 1 cruise
  • Have at least 2 passengers who did not show up
  • Have at least 2 passengers who book future cruise

In adding this data, you must ensure that the test data thoroughly tests the model as supplied, to ensure your schema is correct.

Your inserted data must conform to the following rules:

• You may treat all the data that you add as a single transaction since you are setting up the initial test state for the database.
• The primary key values for this data should be hardcoded values (ie. NOT make use of sequences) and must consist of values below 100.
• The data added must be sensible (eg. boarding date time should be within 24 hours before the cruise scheduled departure).

For this task ONLY, Task 2, you may look up and include values for the loaded tables/data directly where required. However, if you wish, you can still use SQL to get any non-key values.

TASK 3: DML

Your answers for this task (Task 3) must be placed in the supplied SQL Script T3-wc-dm.sql

For this task you are required to complete the following sub-tasks in the same order they are listed. Where you have been supplied with a string contained in quotes, such as ‘New Zealand Delight’ you may search in the database using the string as listed. Where a particular case (upper case, lower case, etc.) for a word is provided you must only use that case. When a name is supplied you may break the name into first name and last name, for example ‘Dominik Davies’ can be split into ‘Dominik’ and ‘Davies’, again note that the case must be maintained as it was supplied.

• Oracle sequences are going to be implemented in the database for the subsequent insertion of records into the database for the PASSENGER and MANIFEST

Provide the CREATE SEQUENCE statement to create two sequences which could be used to provide primary key values for the PASSENGER and MANIFEST tables. Both sequences should start at 100 and increment by 1. Immediately prior to the create sequence commands, place appropriate DROP SEQUENCE commands so they will cause the sequences to be dropped before being created if they exist. Please note that there can only be these two sequences introduced and used in Task 3.

Question 3b, 3c and 3d are related questions. You can use the information given in 3b to answer 3c and 3d

• A family, consisting of a father named ‘Dominik Davies’ and his two daughters (‘Henrietta Davies’ and ’Poppy Davies’) booked a ‘Melbourne to Auckland’ cruise which will depart on 23 October 2022 at 10:00AM.

This is the first time the family has booked a cruise run by World Cruise. They booked a balcony cabin number 210. Dominik’s contact number is ‘0493336312’. Both children are under 18 years old and do not have their own contact numbers. Their father is their guardian.

Make these changes to the data in the database. You may assume that there is only one cruise named ‘Melbourne to Auckland’ departing on 23 October 2022 at 10:00AM in the system and you may make up sensible data for the rest of attributes. This entire cruise booking should be treated as a single transaction.

• A week later, Poppy was unwell and required a complete Her father called World Cruise to change their booking for the ‘Melbourne to Auckland’ departing on 23 October 2022 at 10:00AM cruise.

Dominik informed World Cruise that he wanted to cancel the booking for ‘Poppy Davies’. He also wanted to downsize the cabin to an ocean view cabin with 2 people capacity. He and Henrietta were then assigned cabin number 113.

Make these changes to the data in the database. You may assume that Dominik Davies is only listed as a guardian for his two kids in the system.

• A few days after the booking alteration, Dominik Davies decided to cancel the booking altogether.

Make these changes to the data in the database.

TASK 4: DATABASE MODIFICATIONS (20 marks)

Your answers for these tasks (Task 4) must be placed in the supplied SQL script T4-wc-alter.sql

The required changes must be made to the “live” database (the database after you have completed tasks 1, 2 and 3) not by editing and executing your schema file again. Before carrying out the work below, please ensure that you have completed tasks 1, 2 and 3 above.

If in answering these questions you need to create a table, please place a drop table statement prior to your create table statement.

• WC wants to track the number of passengers booked on each cruise. Add a new attribute in the CRUISE table to record this and initialise the attribute with the correct number of passengers based on the data which is currently stored in the system. If there is no passenger booking for a particular cruise, then the value of this new attribute must be set to 0.

Modify the database structure to meet this new requirement.

• The ships used for cruises require regular WC wish to store the ship maintenance records from this point forward. For each ship, WC wants to store the maintenance start date, maintenance expected end date, and maintenance type. There are three maintenance types used by WC: Preventive or Scheduled Maintenance, Corrective or Breakdown Maintenance, and Condition Maintenance. These types will not be expanded in the near future.

Modify the database structure to meet this new requirement.

• WC have realised that their database model has a serious weakness; a minor may travel with different guardians on different cruises. In the current model, if the guardian is changed (guardian_id in the passenger table), the result will be incorrect historical data – the previous cruise records may not refer to the correct passenger who acted as a guardian during the cruise. To address this weakness, WC wishes to record the guardian of a minor for each cruise that the minor is booked on (i.e. record the guardian history).

Modify the database structure to meet this new requirement. Note that you must not lose current guardian details for previous cruises which are stored in the database. You may assume that the nominated guardian in the passenger table was on board for all currently recorded cruises.

Answer

Request Answer of this Assignment