SCHOOL OF COMPUTING
COMP1350 2023 – ASSIGNMENT TWO
Introduction to Database Design and Management
Database Implementation Assignment
(Worth 20%: 5% Submission; 15% Viva Assessment)
5% Submission due: 11:55 pm, Sunday 15th October 2023 (WEEK 10 SUNDAY) 15% Viva Assessment due in YOUR Week 11 SGTA/Workshop
Table of Contents
CASE BACKGROUND ....................................................................................................... 2
UNDERSTANDING HOW THE ASSIGNMENT WORKS .................................................... 6
CREATING TABLES, INSERTING DATA INTO TABLES: ............................................................................. 6
EXECUTING QUERIES: .................................................................................................................. 6
HOW TO SUBMIT: ...................................................................................................................... 6
HOW THE LATE SUBMISSION PENALTY WOULD WORK: ........................................................................ 6
TASK DESCRIPTIONS ...................................................................................................... 7 LEVEL-ONE ............................................................................................................................. 7 LEVEL-TWO ............................................................................................................................ 9 LEVEL-THREE ....................................................................................................................... 10
HOW WILL THE VIVA ASSESSMENT WORK? .............................................................. 10
COMP1350 2023 Assignment 2 Specifications
2
Case Background With your assistance, the South African Conservation Society has moved to relational databases. However, members of their IT department did not pay attention during COMP1350 classes and are now having trouble with creating tables and writing queries. You have been employed to construct and implement the database and write queries for some common searches on the database. Table Num:1 Table Name: Reserve
Column Name Comments/Description ReserveID (PK) The unique identifier for the reserve. ReserveName A short name for the reserve. ReserveLocation The name of the reserve location ReserveSize Size of the land in hectares.
Data for Reserve
Table Num:2 Table Name: Vehicle
Column Name Comments/Description VehicleID (PK) The unique identifier for the vehicle. ReserveID (PK, FK) The unique identifier for the reserve
References ReserveID in the Reserve table. VehicleType A short name/description for the type of vehicle VehicleCapacity Maximum number of passengers. VehicleRegoNum Registration of the vehicle; 6 alphanumeric values.
Data for Vehicle
COMP1350 2023 Assignment 2 Specifications
3
Table Num:3 Table Name: Tour
Column Name Comments/Description TourID (PK) The unique identifier for the tour. TourName A short name/description for the tour. TourCost Cost of the tour. TourDuration Duration of tour in hours.
Data for Tour
Table Num:4 Table Name: Staff
Column Name Comments/Description StaffID (PK) The unique identifier for the staff member StaffName The name of the staff member StaffPosition A short name/description for the position of the staff member StaffSalary Staff member’s annual salary.
Data for Staff
Table Num:5 Table Name: VisitorGroup
Column Name Comments/Description VisGroupID (PK) Unique identifier for the Visitor Group. VisGroupName The name of the person who registered the group. VisGroupCountry Name of the country the group will be travelling from. VisGroupNumPeople Number of members in the group.
Data for VisitorGroup
COMP1350 2023 Assignment 2 Specifications
4
Table Num:6 Table Name: Booking
Column Name Comments/Description BookingID (PK) Unique identifier for the Booking. TourID (FK) Unique identifier for the Tour.
References TourID from the Tour table. VehicleID (FK) Unique identifier for the Vehicle at a Reserve.
References VehicleID, ReserveID from the Vehicle table. ReserveID (FK) StaffID (FK) Unique identifier for the member of Staff.
References StaffID from the Staff table. VisGroupID(FK) Unique identifier for the Visitor Group.
References VisGroupID from the VisitorGroup Table BookingDate The tour date booking has been made for BookingTime The tour time booking has been made for
Data for Booking
Table Num:7 Table Name: Organisation
Column Name Comments/Description OrganisationID (PK) Unique identifier for the Organisation. OrganisationName Name of the Organisation. OrganisationManager First name and surname of the Organisation’s manager. OrgContactNumber Organisation’s contact number including area code.
Data for Organisation
COMP1350 2023 Assignment 2 Specifications
5
Table Num:8 Table Name: Partnership
Column Name Comments/Description ReserveID (PK, FK) Unique identifier for the Reserve. References ReserveID from the Reserve table. OrganisationID (PK, FK) Unique identifier for the Organisation. References OrganisationID from the Organisation table. StartDate Date the partnership commences. EndDate Date the partnership ends. Amount Amount funded by the Organisation.
Data for Partnership
Table Num:9 Table Name: TourPackage
Column Name Comments/Description PackageTourID (PK, FK) Unique identifier for the Package Tour.
References TourID from the Tour table. ComponentTourID (PK, FK) Unique identifier for the Component Tour.
References TourID from the Tour table.
Data for TourPackage
COMP1350 2023 Assignment 2 Specifications
6
Understanding how the assignment works
• Please read these instructions carefully to understand how the assignment works.
• A sample schema (as a pdf file) is provided in the Assignment-2 folder. It should give you an idea of which tables are connected to which other tables.
• The assignment is broken down into 3 levels:
1. Level One: 5 Questions (25 marks)
2. Level Two: 3 Questions (15 marks)
3. Level Three: 2 Questions (10 marks)
• You will have to score full marks in Level One to qualify for Level Two, and full marks in Level Two to qualify for Level Three. This means even if you lose one mark in the previous level, your next level will not be marked.
• There are 10 (ten) questions and every question is worth 5 (five) marks. Creating tables, Inserting data into tables:
• Comments are given for you to understand the column and do not have to be added to the database.
• A snapshot of the data your tables should contain is provided beneath the table description. This is the exact data that you must insert into the tables. No extra tables should be added.
• The creation of tables has no marks. This means you are expected to create tables and insert data for your queries to work. Executing queries:
• The expected output of each query has been provided for you to cross-check the understanding of your question. Execution of every query must match the output provided in the same order
• If there are computed fields/fields with some calculation, the column names must have a proper alias
• Remove duplicate results, wherever applicable. You will see the expected output does just that.
• “Natural Join/Union/Intersect” phrases shall not be used within the realm of the assignment.
• If the question doesn’t specify whether to use joins or subqueries, you are allowed to either use join and/or subqueries to answer the question. How to Submit:
• Use the template provided in the folder to write your code in MySQL under the comments provided. This will be executed on MySQLWorkbench during marking.
• Rename the file to your StudentID_StudentName (If the file is in any format other than .sql, the submission will receive zero).
• Upload the file in the link provided on iLearn before the submission date. How the late submission penalty would work:
• The late submission penalty of 5% per day will apply to the entire assignment and not to the individual components.
• The viva assessment for students who submit after the one-hour grace period will be rescheduled in Week-13 and will incur the same late penalty that the submission receives. Late submissions are only accepted up until 7 days past the due date as provided on the unit guide.
COMP1350 2023 Assignment 2 Specifications
7
Task Descriptions Level-One This level has five questions. To be able to answer the questions, you will have to create and populate tables 1-6: Reserve, Vehicle, Tour, Staff, VisitorGroup and Booking.
Task 1 (5 marks): Write a query to print all details of the tours. The cost of the tour should be prefixed with ‘$’ and the duration should end with ‘hours’. Sort the records from the highest to lowest based on the cost of the tour.
Expected output:
Task 2 (5 marks): Write a query to print out the details of the booking: the booking ID along with its start and end times. Rename the columns as ‘Tour Start Time’ and ‘Tour End Time’.
Expected output:
COMP1350 2023 Assignment 2 Specifications
8
Task 3 (5 marks): Write a query to retrieve the details of the booking: the booking ID along with the booking date and the reserve name. Display only the records where the booking has been made at a reserve larger than 300 hectares, with a vehicle capable of accommodating at least seven passengers, and the booking is scheduled for more than six months from the current date.
Hint: You have to use the current date when writing the query, and consider 1 month as
equivalent to 30 days.
Expected output:
Task 4 (5 marks): Using a SUBQUERY, print the name and size of each reserve that has a vehicle with the letter ‘b’ appearing in the Rego Number (not case-sensitive). All sizes should be displayed with ‘ hectares’ and the result should be sorted from the largest to the smallest reserve.
Expected output:
Task 5 (5 marks): Write a query to display the total number of bookings for each reserve (Include just the IDs of the Reserve) that were made before 10:00 AM. There should be no formulas as column names. Order the results by ReserveID in ascending order.
Expected output:
COMP1350 2023 Assignment 2 Specifications
9
Level-Two This level has three questions. To be able to answer the questions, you will have to create and populate tables 7,8, and 9: Partnership, Organisation, TourPackage.
Task 6 (5 marks): Write a query to print the name of staff members along with the dates of the booking, if the bookings were made in October of any year, and only if the staff members are either reserve managers or senior tour guides earning at least $70,000. Order the results by Staff Name in ascending order Additionally, include staff members who do not have any bookings, and represent any null values as 'No Booking'.
Expected Output:
Task 7 (5 marks): Write a query to display the country of the visitor group and the name of the staff member assigned to the group for the booking, where the tour duration is over two hours, the assigned staff member has two or more bookings, and their salary is less than or equal to the average staff salary.
Expected Output:
Task 8 (5 marks): Write a query to display the name of package tours along with their component tours. Include only packages that consist of more than two component tours. The column names should indicate whether each entry is a package or a component.
Expected Output:
COMP1350 2023 Assignment 2 Specifications
10
Level-Three This level has two questions.
Task 9 (5 marks): Write a query to display the ID, name, and cost of all package tours. Include a column that shows the total cost of all the component tours within each package and another column that calculates the savings provided by the package tour. Ensure that all prices are prefixed with '$’. Note: The query should only include records for package tours.
Expected Output:
Task 10 (5 marks): Write a query to display the name of the reserve and its partnering organization, the duration of the partnership in years, and the amount funded. Include only records where the first digit in the organization contact number is '9' (excluding the area code), the amount funded exceeds the average funding across all organizations, and the reserve has no package tours booked. The funded amount should be prefixed with “$”.
Expected Output:
How will the Viva Assessment work?
• To assess your understanding of your submitted work, we will be conducting a viva assessment in Week 11 in your enrolled SGTAs/Workshops.
• You will be given a total score of zero for Assignment 2 if you do not attend your viva assessment. The students with late submissions will only be graded in Week-13 workshop.
• You will need to know how to open MySQLWorkbench & create a connection to
AshServer in the lab computer, run your codes to produce an output, and create a
new SQLFile from scratch to check the execution/comment as needed.
• You will be asked to download your Assignment submission and you can use this as a reference during the exam to answer the questions. All the questions will be based on the tables created in Level One.
• You will be provided with a set of questions - 5 questions and an expected output; You can write your codes, run them, check the output and submit your answers. You will have 15 minutes for this and your SGTA teaching staff might ask you questions and grade you.
• You will be under examination conditions and this means communicating with any other student in any method or form will be a breach of academic integrity.