Reference no: EM134043956

Database Design and Management

Assessment - SQL Application and Analysis

Task

This assessment evaluates your SQL querying proficiency and your ability to analyse and explain SQL logic. You will receive a database schema, data, and a scenario. The assessment consists of an in-class oral viva (Part A) and a SQL query task (Part B). Part A assesses your ability to identify and explain SQL errors without the use of external tools, while Part B assesses your skills in writing SQL queries.

Assessment Description

In this assessment, you will use a case study and a provided database to complete two components: an in-class oral viva and a SQL querying task. You will use MySQL to retrieve and analyse data, as the database contains large volumes of data that cannot be processed manually.

In the oral viva, you will analyse a SQL query containing errors and explain the issues and corrections verbally without using any devices, demonstrating your understanding of SQL logic and query structure. In the SQL querying task, you will write SQL queries to answer questions based on the case study using the provided database.

You are expected to download the database, load it into MySQL, and familiarise yourself with the database schema before Week 11 to support both parts of the assessment, and you must bring a valid photo ID (KBS student ID or another approved photo ID) for identity verification during the oral viva. Further preparation may include reviewing Database Management DBMS concepts relevant to the assessment.

learning outcomes:

LO1: Investigate the use of Relational Database Management Systems in organisations.

LO2: Construct database models.

LO4: Create queries to manage data using a database query language.

Assessment Instructions Part A: In-Class Oral Viva

As an individual, you must download the database and data files and load them into MySQL. Once loaded, you must prepare for this Part before week 11.

This is a secure, in-class individual assessment conducted face-to-face. Each student will have 10 minutes in total to complete the task. 5 minutes of reading and preparation, and 5 minutes of oral explanation.

Task:

You will receive one printed SQL query containing multiple errors. Each query will contain "four embedded errors". You must identify the errors and suggest how to correct them.

Conditions:

No laptops or mobile devices allowed
No internet access
No written answers required
Responses must be oral only

Question Allocation:

Questions are randomly assigned
A secure question bank is used
Questions are not accessible to students

Identity Verification:

Before the viva, your identity will be verified by the facilitator using:
KBS issued student ID OR
Alternative Photo ID (approved by facilitator)

Part B: SQL Querying

As an individual, you must download the database and data files and load them into MySQL. Once loaded, you must develop queries for the following questions:

Note: You should include a comment above each query, specifying which question you are answering. In addition, you can use Gen AI tools to get an idea about how to answer Questions, and responses to the SQL queries should be restricted to only the clauses used in workshop materials.

Question 1: Display the full names of employees, their job titles, department names, campus names, and campus cities. Sort the results by campus city, then department name, then employee last name.

Question 2: Show all employees who were hired after 1 January 2018. Include the employee's full name, hire date, job title, department name, and employment status.

Question 3: Display all departments with their campus name, city, and state. Sort the results by state and then campus name.

Question 4: Show the number of employees in each department. Include department name, campus name, and employee count. Sort the results from highest to lowest employee count.

Question 5: Display the full names of employees, their salary level, minimum salary, and maximum salary for all employees whose salary range has a maximum salary greater than 90,000.

Question 6: List all employees who have received a bonus. Include employee full name, bonus year, bonus amount, department name, and campus city. Sort the results by bonus amount in descending order.

Question 7: Show the average bonus amount for each department. Include only departments where the average bonus exceeds 5,000. Sort the results from highest to lowest average bonus.

Question 8: Find all employees whose job title is Lecturer or Senior Lecturer and who work in campuses located in Sydney or Melbourne. Include full name, job title, department name, campus city, and hire date.

Question 9: Display the youngest employee in each department. Include department name, employee's full name, and date of birth.

Question 10: Create a stored procedure that returns all employees for a given campus city. The output should include the employee's full name, department name, and job title. SQL programming resources may also be relevant when working with database query tasks such as these, including PL SQL.