CSE2/4DBF Dealership Database System Individual Assignment 2 Semester 01 2025

Aims and Objectives : 

✓to perform queries on a relational database system using SQL. 

This is an individual Assignment. You are not permitted to work as a group when writing this assignment. You are also not permitted to use AI to generate code for your tasks. 

Copying, Plagiarism: Plagiarism is the submission of somebody else's work in a manner that gives the impression that the work is your own. The Department of Computer Science and Information Technology treats plagiarism very seriously. When it is detected, penalties are strictly imposed. Students are referred to the Department of Computer Science and Information Technology's Handbook and policy documents about plagiarism and assignment return, and also to the section on 'Academic Integrity the subject learning guide. 

No extensions will be given: Penalties are applied to late assignments (5% of the total assignment mark given is deducted per day, accepted up to 5 days after the due date only). If there are circumstances that prevent the assignment from being submitted on time, an application for special consideration may be made. See Student Handbook for details. Note that delays caused by computer downtime cannot be accepted as a valid reason for a late submission without penalty. Students must plan their work to allow for both scheduled and unscheduled downtime.

Task 1 [100 marks] 

Using the tables provided above, provide SQL select statements for the following queries. 

• Display the name and contact details of all professionals who have a 'Cert III' level qualification in any field. [10 marks]
• List the contact details (name, email, and phone) for all members of the hub with ID 'HUB795' and the corresponding number of children. [10 marks]
• List the details of all events that were held in 2024. Order the output by date in ascending order. [10 marks] 
• List the ID(s) of all hubs that have at least one event that is currently ongoing. The query output may vary based on the date of the execution. [10 marks]
• List the hub(s) details that host the highest number of services. [10 marks].
• List the details of all employees who are managers of a hub. [10 marks] 
• List all employee details and the corresponding number of events each employee has assisted in. Your query must display zero for the number of events if the employee did not assist in any events. [10 marks]
• List the ID and address for the Hub that has the highest total seating capacity. To calculate the total seating capacity of a hub, you must include all activity space into consideration that facilitates seating. [10 marks]
• List the name, email, and phone number of all personnel who either assisted or supervised the dance event with ID 'EVT002'. [10 marks] 
• List the event ID, description, start date (in Month-YYYY format), end date (in Month-YYYY format), supervising professional's name for all events, along with their corresponding total number of assisting staff. [10 marks]