FIT2094 Databases Assignment 2
Creating, Populating and Manipulating Databases Paris Arrow Transit PAT
FACULTY OF INFORMATION TECHNOLOGY
Students will be asked to implement, via SQL, a small database in the Oracle RDBMS from a provided logical model case study, followed by the insert of appropriate data to the created tables. Once populated the database will be used to carry out specified DML commands and make specified changes to the database structure via SQL. Students will then use SQL and NoSQL to write queries to produce specified output. This task covers learning outcomes:
1. Apply the theories of the relational database model.
3. Implement a relational database based on a sound database design.
4. Manage data that meets user requirements, including queries and transactions. 5. Contrast the differences between nonrelational database models and the
relational database model.
This is an open book, individual task. The final output for this task will be a set of tables and data implemented in the Oracle RDBMS. In addition students will create a set of relational Oracle and non relational MongoDB queries which meet the user requirements.
40 of your total marks for the unit
Wednesday, 30th October 2024, 4:30 PM
note: staff support is unavailable after business hours
Submission
Via Moodle Assignment Submission
FIT GitLab check ins will be used to assess history of development
Assessment Criteria
Application of relational database principles.
Handling of transactions and the setting of appropriate transaction boundaries.
Application of SQL statements and constructs to create and alter tables
including the required constraints and column comments, populate tables, modify existing data in tables, and modify the live database structure to meet the expressed requirements including appropriate use of constraints.
Application of SQL select statements to produce outputs that meet user requirements.
Mapping of relational database data into non relational database data structure.
Application of MongoDB operations to produce outputs that meet user
requirements.
Late Penalties
10 deduction per calendar day or part thereof for up to one week
Submissions more than 7 calendar days after the due date will receive a mark
of zero 0 and no assessment feedback will be provided.
Page 1 of 19
FACULTY OF INFORMATION TECHNOLOGY
Support Resources
See Moodle Assessment page
Feedback will be provided on student work via:
general cohort performance
specific student feedback ten working days post submission
a sample solution
Page 2 of 19
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 PATs needs, which are detailed below.
PAT owns a fleet of vehicles. Each vehicle is identified by its 17character 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 Committees NOC will compete at the Paris Games each represents a particular country or region of the world. For each countryregion, an identifying IOC code is recorded as well as the name of the countryregion and its number of athletes. 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 officials 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. A trip only involves a single vehicle. An official may use PATs 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 indicatedrecorded. The intended pickup date and time and the projected dropoff 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 companys drivers. Each driver is assigned a unique driver ID. The drivers name given and family, licence number 12 characters in length, date of birth and the level of security clearance granted to the driver are recorded. Due to issuesproblems 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 ISO6391 twocharacter language codes, for example, EN as English and ZH as Chinese PAT records the ISO6391 code and the name of the language.
Based on these requirements, a data model has been created for PAT:
FACULTY OF INFORMATION TECHNOLOGY
Page 3 of 19
The schemainsert file for creating this model patinitialSchemaInsert.sql is available in the archive ass2student.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 ass2student.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.
FACULTY OF INFORMATION TECHNOLOGY
Page 4 of 19
FACULTY OF INFORMATION TECHNOLOGY
4. When handling dates with SQL, the default date format must not be assumed; you must use the TODATE and TOCHAR 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 ass2student.zip from Moodle.
2. Extract the zip archive and place the contained files in your local repository in the folder: AssignmentsAss2
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 FITGitLab server.
5. Run patinitialSchemaInsert.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 T1patschema.sql and so on.
7. Save, add, commit and push the files 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 TABLEDROP SEQUENCE statements. Any tasks script that runs with an error will receive a maximum grade of half of the tasks available marks 1. For example, if your task 1 script runs with an error, regardless of the code contained, your maximum grade will be 152 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.
Page 5 of 19
FACULTY OF INFORMATION TECHNOLOGY
In arriving at your solutions for Assignment 2 you are ONLY permitted to use the SQLNoSQL 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 NonRelational Database
As detailed above, SQLNoSQL syntax and commands outside of the covered work will NOT be acceptedmarked.
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 plagiarismcollusion 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 GitLab 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 GitLab servers web interface; you must not simply assume they are working. Before submission via Moodle, you must log in to the GitLab servers web interface and ensure your submission files are present and their names are unchanged.
Page 6 of 19
Assignment Tasks
TASK 1: DDL 15 mks
ENSURE your ID and name are shown at the top of any file you submit.
For this task, you must add to T1patschema.sql the CREATE TABLE and CONSTRAINT definitions, which are missing from the supplied partial schema script, in the positions indicated by the scripts comments. The table below details the attributes meaning in the missing three tables. You MUST use exactly the same relation and attribute names shown in the data model above to name the tables and attributes you add. The attributes must be in the same order as shown in the model. These new DDL commands must be handcoded, not generated in any manner generated code will not be marked.
FACULTY OF INFORMATION TECHNOLOGY
Table name
Attribute name
Identifier for an official
Given name for the official
Family name for the official
IOC country code for the official
Identifier for Chef De Mission for the official
Identifier for vehicle
Registration plate of vehicle
Year of manufacture of vehicle
vehcurrodo
Current odometer reading of vehicle
vehnopassenngers
Number of passengers vehicle can seat
Identifier for vehiclemodel
Identifier for a trip
tripnopassengers
Number of passengers for the trip
tripintpickupdt
Intended pickup date and time for the trip
tripactpickupdt
Actual pickup date and time for the trip
tripintdropoffdt
Intended dropoff date and time for the trip
tripactdropoffdt
Actual dropoff date and time for the trip
Identifier for a vehicle
Identifier for a driver
pickuplocnid
Identifier for the pickup location
dropofflocnid
Identifier for the dropoff location
langisocode
ISO6391 twocharacter language codes
Identifier for an official
Page 7 of 19
FACULTY OF INFORMATION TECHNOLOGY
To test your code, you must first run the provided script patinitialSchemaInsert.sql to create the other required tables. patinitialSchemaInsert.sql contains the drop commands for all tables in this model at the head of the file. If you have problems with task 1 simply rerun patinitialSchemaInsert.sql, which will cause all tables to be dropped and correct the issues in your script. DO NOT add drop table statements to T1patschema.sql
TASK 2: INSERT 20 mks
Before proceeding with Task 2, you must ensure you have run the file patinitialSchemaInsert.sql which must not be edited in any way followed by the extra definitions that you added in Task 1 above T1patschema.sql.
Load the VEHICLE, OFFICIAL and TRIP tables with your own test data using the supplied T2patinsert.sql script file. Write SQL commands that will insert as a minimum i.e. you may and should insert more the following sample data:
i 10 VEHICLE entries
Include at least three vehicle models
ii 10 OFFICIAL entries
Include at least four IOC CountriesRegions
iii 20 TRIP entries
Include at least five vehicles used in more than one trip
Include at least five officials booked more than one trip
Include at least five drivers
Include at least five languages
Include at least two parallel trips i.e. trips that have the same intended
pickupdropoff date times and the same pickupdropoff locations
In adding this data, you must ensure that the test data thoroughly tests the model as supplied to ensure your schema is correct you are not required to submit or code fail tests; all insert statements must execute correctly.
Your inserted data must conform to the following rules:
1. Treat all the data you add as a single transaction since you are setting up the initial test state
for the database.
2. The primary key values for this data should be hardcoded values i.e. NOT make use of
sequences. If the primary key attributes datatype is number, it must consist of values below
3. Trip dates used must be chosen between the 20th July 2024 and 15th August 2024.
4. The data added must be sensible e.g., the dropoff date should be after the pickup date, the
vehicle must accommodate the requested number of passengers, etc..
For this task ONLY, Task 2, you may manually look up and include values for the loaded tablesdata directly where required. However, you can still use SQL to get any nonkey values if you wish. In carrying out Task 2, you must not modify any data or add any further data to the tables populated by the patinitialSchemaInsert.sql script. Design your test data to get output for the SQL scriptsqueries specified below this may require you to add further data as you complete the required tasks.
Page 8 of 19
TASK 3: DML 20 mks
Your answers for this task must be placed in the SQL file T3patdml.sql For this and all subsequent Tasks, you are NOT permitted to:
manuallylookupavalueinthedatabase,obtainitsprimarykey,ormanuallyobtainthe highestlowest value in a column,
manuallycalculatevaluesexternaltothedatabase,e.g.onacalculatorandthenusesuch values in your answers. Any necessary calculations must be carried out as part of your SQL code or
assumeanyparticularcontentsinthedatabaserowsinatablearepotentiallyinaconstant 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 subtasks 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.
FACULTY OF INFORMATION TECHNOLOGY
Page 9 of 19
FACULTY OF INFORMATION TECHNOLOGY
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 officials 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.
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.
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.
Page 10 of 19
TASK 4: DATABASE MODIFICATIONS 13 marks:
Your answers for these tasks Task 4 must be placed in the supplied SQL script T4patmods.sql
The required changes must be made to the live database the database after you have completed tasks 1, 2 and 3, in which other users must be assumed to be active. You MUST not edit and execute your schema file again. Before completing the work below, please ensure 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 immediately before your create table statement.
a PAT would like to store each officials role. The list of roles, which includes General, Administrator, Head Coach, Coach, and Physician, will remain unchanged. The default role is General. The Chef de Mission of each country must be assigned an Administrator role.
As part of your solution, provide appropriate select and desc statements to show the changes you have made. Select to show any data changes that have occurred, and desc tablename, e.g., desc customer, to show any table structural changes.
b PAT would like to allow complaints about the drivers behaviour on a particular trip. The official who requested the trip will submit these complaints. Multiple complaints may be lodged for a particular trip. An official cannot make two complaints for a particular trip at the same time.
PAT provides some categories for the complaints. The current categories are late arrival, rude behaviour, poor driving, and failing to assist. Each category has a specific demerit point: a late arrival or failure to assist incurs one demerit point, and rude behaviour or poor driving incurs two demerit points. PAT wants to add more categories in the future.
The system stores each complaints date, time, category, and detailed comment. It also stores the trip for which the complaint was made.
The PAT staff member follows up on the complaint and investigates whether it is valid. If it is, it will be flagged in the system as a valid complaint.
Change the database structure to support these new business rules. You are not required to add the complaint data you must add the category data as listed in this task. You only need to provide the structure so PAT can store complaints.
As part of your solution provide appropriate select and desc statements to show the changes you have made. Select to show any data changes that have occurred and desc tablename e.g. desc customer to show any table structural changes.
FACULTY OF INFORMATION TECHNOLOGY
Page 11 of 19
TASK 5: SQL Queries 15 mks
Your answers for this task Task 5 must be placed in the supplied SQL script T5patselect.sql.
If you need to add further data for this task, you must return and add it as part of task 2 and then rerun patinitialSchemaInsert.sql and all tasks tasks 1, 2, 3 and 4 before running task 5.
You can only code a single select statement for each question below. Note that an SQL select statement begins with the SELECT keyword and ends with a semicolon ; within this statement, the SELECT keyword can be used multiple times.
Where you have been supplied with a string in italics, you must search the database using the provided string precisely as supplied. Where you need to show a full name, you must not have any extra spaces e.g., leading space or extra space in the middle of the name.
Please remember VIEWS or PLSQL including anonymous blocks BEGIN…END must not be used. Your SQL code used must be restricted to the syntax covered in your unit.
Sample partial output showing the form of what you must produce is provided. Note that this is the form of the output ONLY, i.e., the appearance only; the data you return will differ.
a Paris Arrow Transport wishes to know how often all locations have been used as pickup or dropoff locations trips that have not been completed must be excluded from this count. The report should show the location ID, name, address, type of location, and the number of times the location has been used for pickup or dropoff. If a particular location has been used once for pickup and twice for dropoff, the pickupdropoff count will be three.
Sample partial output is shown below your output must have the same format and column headings:
The output must be ordered to show the location with the most pickupsdropoffs first. If several locations have the same number of pickupsdropoffs, then order the output by the location ID.
FACULTY OF INFORMATION TECHNOLOGY
Page 12 of 19
FACULTY OF INFORMATION TECHNOLOGY
b Paris Arrow Transport would like a report for its weekly pay run covering the period from August 1st to August 7th, 2024, inclusive. Drivers are paid 45.42 per hour for the time they drive a trip. All trips during this period have been completed, and any cancellations have been removed from the system.
If a driver has not completed any trips during this period, show their total payment as No Trips. The total payment must be displayed in the following format: 1234.56. Your output must show the driver ID, the drivers full name, and the total payment. Order the output in ascending order of driverid.
Sample partial output is shown below your output must have the same format and column headings:
Page 13 of 19
TASK 6: MongoDB 12 mks
Your answers for this task Task 6 must be placed in the supplied sql file T6patjson.sql and the supplied MongoDB script file T6patmongo.mongodb.js.
You must not add any further comments to the supplied MongoDB script file nor removerename any comments indicated by
a Write an SQL statement in T6patjson.sql to generate a collection of JSON documents using the following structureformat. Each document in the collection represents a driver, their details, and a summary of their completed trip details. Note that id in this structure is the drivers ID, and nooftrips is the number of trips that the driver has completed. You only need to include drivers who already have at least one completed trip.
name: Mansour, licencenum: 33022B678901, nooftrips: 2, suspended: N, tripsinfo:
tripid: 21,
vehvin: 1D4PU4GK7BW972165, pickup:
locationid: 102,
locationname: Bercy Arena, intendeddatetime: 09082024 17:35, actualdatetime: 09082024 17:38
locationid: 114,
locationname: Champions Park, intendeddatetime: 09082024 18:00, actualdatetime: 09082024 18:05
tripid: 9,
vehvin: 1C4SDHCT9FC613386, pickup:
locationid: 112,
locationname: Roland Garros Stadium, intendeddatetime: 01082024 14:00, actualdatetime: 01082024 14:01
locationid: 116,
locationname: Louvre Museum, intendeddatetime: 01082024 14:25, actualdatetime: 01082024 14:28
FACULTY OF INFORMATION TECHNOLOGY
Page 14 of 19
FACULTY OF INFORMATION TECHNOLOGY
Write the MongoDB commands for the following questions, 6b 6d, in the supplied MongoDB script file named T6patmongo.mongodb.js.
b Create a new collection and insert all documents generated in 6a above into MongoDB. Provide a drop collection statement right above the create collection statement. You may pick any collection name. After the documents have been inserted, use an appropriate db.find command to list all the documents you added.
c List the name and licence number of all drivers who have completed trips to Champions Park or Porte de La Chapelle Arena.
d It has been discovered that driver Antoine Lefevre id:2004 made a trip from Tuileries Garden location id: 117 to SainteChapelle location id: 118 on 10082024 and forgot to record it. Because of this mistake, they have now been suspended for failing to follow policy.
Use an appropriate db.find command before making the change so that you illustrate which documents will be changed.
Write the necessary MongoDB commands to add this trip to Antoines record, and change their status to suspended. You may make up other required information when adding the trip.
Use an appropriate db.find command after making the change so that you illustrateconfirm the change which was made.
Page 15 of 19
Submission Requirements
Due Date: Wednesday, 30th October 2024, 4:30 PM
Please note that if you need to resubmit, you cannot depend on your staffs availability; therefore, please be VERY CAREFUL with your