INSTRUCTIONS

Your task for this assignment is to design a model for Paris Arrow Transit (PAT). Paris Arrow Transit is a private company subcontracted by the Olympic Federation to transport officials during the Olympic competition. The company realises that it needs a completely new computerised system to more efficiently manage and record its services during the Games. You have been asked to develop a database system that can meet PAT's needs, which are detailed below.

PAT owns a fleet of vehicles. Each vehicle is identified by its 17-character vehicle identification number (VIN), the company also records the registration plate (7 characters such as AB126FD), the make, such as Peugeot, the current odometer reading and the number of passengers which the vehicle can transport.

206 National Olympic Committee's (NOC) will compete at the Paris Games - each represents a particular country or region of the world. For each country/region, an identifying IOC code is recorded as well as the name of the country/region and its population. Each NOC will enter a team into the competition. The PAT system must track all officials who are part of these teams since they are the ones who will need to book official vehicles. One member of each NOC team is designated as the Chef De Mission (the team manager). PAT will record the official's Olympic ID and name for each official. The system records the manager (Chef De Mission) for each official. The Chef De Missions is listed as an official but has no manager since they are the team manager.

An official will book a trip with PAT to transport members of their team between various locations (these to/from locations will be further examined in Assignment 1 Logical - they should be omitted for now). A trip only involves a single vehicle. An official may use PAT's services multiple times (even during a single day); the only limiting factor is whether a suitable vehicle and driver is available at the date and time they wish to book travel. The vehicle is booked in the name of the official making the booking; the details of the actual passengers do not need to be recorded. The official booking the trip will also indicate the preferred language to be spoken during the trip so that the travellers can be understood by the driver and vice versa. Only a single preferred language for the trip will be indicated/recorded. The intended pick-up date and time and the projected drop-off date and time are recorded for each booked trip. In addition, PAT records the number of passengers needing transport for each booked trip.

PAT vehicles are driven by the company's drivers. Each driver is assigned a unique driver ID. The driver's name (given and family), licence number (18 characters in length), date of birth and the level of security clearance granted to the driver are recorded. Due to issues/problems that may arise, a driver may need to be suspended while a particular matter is investigated; while suspended a driver cannot drive any PAT vehicle - the system must be able to flag this. Driver security clearances are set at either F to represent Full or R to represent Restricted; the default should be R. Only a single driver  drives for a particular booked trip.

PAT records the languages that a driver speaks – some drivers speak several languages. To record languages, PAT will use ISO639-1 two-character language codes, for example, EN as English and ZH as Chinese – PAT records the ISO639-1 code and the name of the language.

Based on these requirements, a data model has been created for PAT:

The schema/insert file for creating this model (pat_initialSchemaInsert.sql) is available in the archive  ass2_student.zip. This file partially creates the Pets First tables and populates several of them (those shown in purple on the supplied model). Please read this schema carefully and be sure you understand the various data requirements.

IMPORTANT points for you to observe when completing this assignment are:

1.   The ass2-student.zip archive also contains seven script files to code your answers in. You

MUST ensure these files are regularly pushed to the GitLab server so that a clear

development history is available for the marker to verify your work (a minimum of

fourteen pushes are required - 2 pushes per file). In each file, you must fill in the header

details with your name and student ID before beginning work. Your SQL script files must not include any SPOOL or ECHO commands. Although you might include such commands when testing your work, they must be removed before submission (a -10 mark grade penalty will    be applied if your documents contain spool or echo commands).

2.  You are free to make assumptions if needed. However, your assumptions must align with the details here and in the Ed Assignment 2 forum and must be clearly documented (see the required submission files).

3.  Views must not be used to arrive at any solutions for the tasks you must complete as part of this assessment.

4.  When handling dates with SQL, the default date format must not be assumed; you must use the TO_DATE and TO_CHAR functions where appropriate.

5. ANSI joins must be used where the joining of tables is required.

6.   In completing the following tasks, you must design your test data so that you always get output for the queries specified below - this may require you to add further data as you move through completing the required tasks. Such extra data MUST be added as part of Task 2 (i.e. as part of your test data load). Queries that are correct but do not produce any output (“no rows selected” message) using your test data will lose 50% of the marks allocated. So, you should carefully check your test data and ensure it thoroughly validates  your SQL queries.

Steps for working on Assignment 2

1. Download the Assignment 2 Required Files zip archive (ass2-student.zip) from Moodle.

2. Extract the zip archive and place the contained files in your local repository in the folder: /Assignments/Ass2

Do not add the zip archive to your local repo.

3. Examine the extracted files, i.e. read carefully through them and ensure you understand their content.

4.  In each supplied script, fill in the header details with your name and student ID. Then, add, commit and push them to the FITGit Lab server.

5. Run pat_initialSchemaInsert.sql from the supplied zip archive to set up the initial state of the database.

6. Write your answer for each task in its respective file (e.g. write your answer for task 1 in T1-pat-schema.sql and so on).

7. Save, add, commit and push the file/s regularly while working on the assignment.

8. Finally, when you have completed all tasks, separately run each SQL or MongoDB as a script (not as individual statements) and ensure there are no errors. Upload all required files from your local repository to Moodle. Check that the files you have uploaded are the correct files (download them from Moodle into a temporary folder and check they are correct). After you are sure they are correct, submit your assignment.

************************************************************************************************************

For all assignment tasks, your final script must run as a script without errors except for SQL errors generated by the DROP TABLE/DROP SEQUENCE statements. Any task's script that runs with an error will receive a maximum grade of half of the task's available marks -1. For example, if your task 1 script runs with an error, regardless of the code contained, your maximum   grade will be 15/2 => 7.5 - 1 = 6.5 marks. This will be applied even if the error is simply a forgotten semicolon. Thus, please carefully check that your final scripts for all tasks run without error.

In arriving at your solutions for Assignment 2 you are ONLY permitted to use the SQL/NoSQL structures and syntax which have been covered within this unit:

● Topic 6 Workshop and Applied 7 - Creating & Populating the Database

● Topic 7 Workshop and Applied 8 - Insert, Update, Delete (DML) and Transaction Management

● Topic 8 Workshop and Applied 9 - SQL Part I - Basic

● Topic 9 Workshop and Applied 10 - SQL Part II- Intermediate

● Topic 10 Workshop and Applied 11 - SQL Part III - Advanced

● Topic 11 Workshop and Applied 12 - Non-Relational Database

As detailed above, SQL/NoSQL syntax and commands outside of the covered work will NOT be accepted/marked.

Views must not be used in completing these tasks.

You must also keep up to date with the Ed Assignment 2 forum where further clarifications may be posted. Please ensure you do not publicly post anything that includes your reasoning, logic, or any part of your work to this forum; doing so violates Monash plagiarism/collusion rules and has significant academic penalties. Attend a consultation session or use a private Ed post to raise such questions.

************************************************************************************************************

GIT STORAGE

Your work for these tasks MUST be saved in your individual local working directory (repo) in the Assignment 2 folder and regularly pushed to the FIT GitLab server to build a clear history of the development of your approach. A minimum of fourteen pushes to the FIT Git Lab server is required (2 pushes per solution script). Please note that fourteen pushes are a minimum; we expect significantly more in practice. All commits must include a meaningful commit message that clearly    describes what the particular commit is about and must be correctly assigned to your valid GitLab author.

You must regularly check that your pushes have been successful by logging in to the FIT Git Lab server's web interface; you must not simply assume they are working. Before submission via Moodle, you must log in to the Git Lab server's web interface and ensure your submission files are present and their names are unchanged.

************************************************************************************************************

TASK 3: DML [20 mks]

Your answers for this task must be placed in the SQL file T3-pat-dml.sql For this and all subsequent Tasks, you are NOT permitted to:

●  manually lookup a value in the database, obtain its primary key, or manually obtain the highest/lowest value in a column,

●  manually calculate values external to the database, e.g. on a calculator and then use such    values in your answers. Any necessary calculations must be carried out as part of your SQL code or

●  assume any particular contents in the database - rows in a table are potentially in a constant state of change

Your answers must recognise that you are dealing with only a very small sample snapshot of a multiuser database; as such, you must operate on the basis that there will be more data in all of the database tables than you currently have access to. Thus, data will be in a constant state of change. Your answers must work regardless of the extra quantity of this extra "real" data and the fact that multiple users will operate in the tables simultaneously. You must consider this aspect when writing SQL statements.

For any following SQL tasks, your SQL must correctly manage transactions and use sequences to generate new primary keys for numeric primary key values (a new primary key value cannot   be hardcoded as a number or value).

You must ONLY use the data provided in the questions' text.

For Task 3, you must complete the following sub-tasks in the same order they are listed. Where you have been supplied with a string in italics, such as La Beaujoire Stadium, you may search in the database using the string as listed. Where a particular case for a word is provided, you must only use that case (same spacing, case, etc) in your SQL code. When a name is supplied, you may break the name into first name and last name. For example, James SMITH can be split into James and

SMITH; again, note that the case must be maintained as it was supplied. Failure to adhere to these requirements, such as changing the case of a provided string, will result in a grade penalty.

(a) Oracle sequences will be implemented in the database to insert records for the OFFICIAL and TRIP tables.

Provide the CREATE SEQUENCE statements to create sequences that could be used to provide primary key values for the OFFICIAL and TRIP tables. These sequences must start at 100 and increment by 10. Immediately before the create sequence commands, place appropriate DROP SEQUENCE commands so that the sequences will be dropped before being created if they exist. Please note that these are the ONLY sequences that can be    introduced and used in Task 3. [1 mark]

Questions 3b, 3c, and 3d are related questions. You can use the information below in any part of Task 3.

(b) An official and a vehicle needed to be stored in the database.

The official’s name is Franklin Gateau from St Vincent and the Grenadines. Franklin is the only official from this country and the Chef De Mission for this country.

The vehicle is an ALPHARD manufactured by TOYOTA. You may assume that TOYOTA only produced one model called ALPHARD. The vehicle identification number (VIN) is 1C4SDHCT9FC614231, which seats up to 6 passengers.

Take the necessary steps in the database to record the required entries for this vehicle and official. When inserting this data, you may make up (invent) any other required information. [5 marks]

(c)  Franklin booked two trips.

The first trip was booked for 30 July 2024 at 12:30 PM from Olympic and Paralympic Village to Porte de la Chapelle Arena, and it was scheduled to arrive 1 hour and 30 minutes later.

The second trip was booked for 30 July 2024 at 8:00 PM from Porte de la Chapelle Arena to Olympic and Paralympic Village, and it was scheduled to arrive 1 hour and 15 minutes later.

For both trips, the vehicle assigned was the Toyota Alphard with VIN 1C4SDHCT9FC614231; the allocated driver was Claire Robert (licence number: 55052a543210), and the preferred

language was English.

Take the necessary steps in the database to record the required trip entries. Both trip bookings must be treated as a single transaction. When inserting this data, you may make up (invent) any other required information. [8 marks]

(d) As scheduled, on 30 July 2024 at 12:30 PM, Claire Robert picked up the St Vincent and the Grenadines team from the Olympic and Paralympic Village and dropped them at the Porte de la Chapelle Arena. The actual trip took 1 hour and 45 minutes.

Then, at 5 PM on the same day, Claire got into an accident. Due to the short notice and the unavailability of other drivers, all booked (incomplete) trips allocated to Claire for the rest of the day must be cancelled (i.e. removed from the system). PAT informed the officials who    booked the trips and reimbursed them with taxi vouchers.

Make these required changes to the data in the database. [6 marks]