Task Summary

In this assessment, you are required to demonstrate your ability to understand the requirements for various data information requests from an existing database and develop appropriate SQL statements to satisfy those requirements. Additionally, you will evaluate and improve database design flaws, suggesting improvements to enhance query optimization, maintainability, and integrity.

Context

Being able to query a database is a fundamental skill for all Information Systems (IS) professionals working with relational databases. In this assessment, you will utilize essential query skills to extract information for analysis, reporting, and data cleansing in a data management setting. Timely provisioning of key business information promotes effective communication and enhances solution delivery.Further more,critically analyzing data base design flaws and improving data base structure for integrity and maintain ability is another important skill for IS professionals

Task Instructions

Please read and examine carefully the attached MIS602_Assessment 2_Case study and then answer the questions. Please note, extra marks will be awarded for presentation and readability of SQL queries, including the ordering of columns. Please pay attention to the following:

• All SQL queries must be written and tested using a MySQL server, either through MySQL Workbench or the MySQL Command Line Client, using the provided database as part of this assessment.
• Where SQL queries must be provided, each question should be answered with a single SQL statement unless specified otherwise.
• Outputs should be presented clearly, with attention to the order of columns and the naming of calculated columns.
• The ‘LIMIT’ clause should be used diligently, ensuring it does not obscure important competing values.
• If any AI tools have been used in any questions, include all references following the university's referencing guidelines. Failure to disclose may lead to Academic Integrity violation consequences as per university policies.

QUESTION 1 [10 MARKS]

In this task, you will identify the department with the highest average hours worked per employee in 2024.

1. Write a query using a join to calculate the average hours worked per employee for each department.
2. Write a query using an uncorrelated subquery  to achieve the same result.
3. Write a query using a correlated subquery to achieve the same result.
4. Explain which approach the best is substantiating your arguments with relevant examples.

Note : A correlated subquery is a query that uses values from the outer query.It runs repeatedly for every row selected by the outer query, making it dependent on the outer query.

QUESTION 2 [5 MARKS]

Write aquery to identify the most recent salary increment given in 2024. Your query should include the following details:

• The employee's name and ID.
• The department name.
• The manager's name and ID who approved the increment.
• The increment amount.
• The date of the increment.

QUESTION3 [5 MARKS]

Which projects have the highest employee participation?

In this task, you  will identify the projects with the highest number of employees assigned.

1. Write an SQLquery to find the number of employees assigned to each project.

• Identify the project(s)with the highest employee participation

  QUESTION 4 [5 MARKS]

  Identify anomalies(if exists)in the database, such as employees assigned to non-existent projects or duplicate records in the Work Hours table.

  Tasks:

• Write SQL queries to detect these anomalies.
• Provide a written explanation of how you identified and addressed these issues.

QUESTION 5 [10 MARKS]

Identify the employees with the most diverse project assignments interms of skills.

In this task, you will explore which employees are assigned to  the most diverse range of projects, considering the diversity of skills required for each project.

• Write an SQL query to find the number of unique projects each employee is assigned to
• Identify the top5 employees with the most diverse and least diverse project assignments in terms of skills.
• Write a short report, not more than 200 words,discussing your findings including the matrices used to reach conclusions.

QUESTION 6 [20 MARKS]

Analyse the impact of work hours on performance warnings across different departments. Tasks:

1. Write an SQL query to compare the number of warnings received by employees working more than 40 hours per week to those working 40 hours or less, across different departments (each department).
2. Write an SQL query to identify the top 5 employees with the highest average work hours per shift who have received at least one performance warning. Provide their employee ID, first name, last name, average work hours per shift, number of warnings received, and their department. Compare these employees to those with fewer warnings across departments.
3. Write an SQL query to find the total number of warnings and average work hours for all employees, broken down by department. Compare the results to identify any trends or patterns.

• Based on the results of the above queries,write a short report(not more than 300words) discussing:
• Do employees with more work hours receive more warnings compared to those with fewer hours?
• Are there any outliers,such as employees with high warnings but low hours,or vice versa? 
• Are certain departments more affected by these trends?
• Analyse the data to determine if there is a consistent pattern across different groups and departments.
• Provide recommendations to HR based on your comparative analysis.

QUESTION 7 [20 MARKS]

In this task, you will redesign the schema to track employees' historical department transfers while maintaining normalization.

1. Describe how you would redesign the schema (logical model) to support employees' historical department transfers. Use visuals(e.g., logical ER diagram)to support your answer, including sample data.

• Based on part (a), write a sample SQL query to retrieve the history of department transfers foraspecificemployee,includingtheemployee'sID,name,departmentname,andtransfer date if any transfers occurred (You are not required to create the table and insert data as testing of the SQL is not required).

  QUESTION 8 [25 MARKS]

  Carefully review the provided case study and the existing database structure, including the tables and their relationships.

  Task1: Identify Areas of  Improvement

• Identify at least three areas where the database structure can be improved, focusing on normalization ,incorporation of additional tables ,and optimizing existing Relationships.

  Task 2: Propose Improvements

• For each identified area ,propose specific improvements.
• Explain why each improvement is necessary with evidence.
• Describe how each improvement will benefit the database's performance,integrity,and maintainability substantiated with evidence.

       Task 3: Prepare a 7-minute presentation with no more than 6 slides. Your presentation should incorporate the above tasks, clearly illustrating your points ,and should be submitted as a recording in MP4 format.

Presentation Guide

• An overview of the current database structure.
• Identified issues and areas of improvement.
• Detailed explanation of the proposed improvements.
• Expected benefits of the proposed changes.
• Proposed database structure.
• Conclusion

You are required to submit two files:

1. Word document:A Word document containing answers to all the Questions, except Question 8.

   Guideline to prepare the word document:

   1. Copy and paste all the SQL questions into the Word document.
   2. Under each question, provide:
      1. The corresponding SQL query or queries.
      2. An image of the result set. If the query returns more than 10 records, capture onlythefirst10lines;otherwise,capture all the records. Do NOT use LIMIT 10 in your Query to force 10 records as it might overshadow the total number of records returned by the Query.
      3. An image of the output as evidence of running the query. Capture the output as an image which should also show how many records returned by the query.
      4. Any other tasks associated with the question.
      5. All references if AItools have been used in any of the questions.

2. Recorded Presentation:A7-minute Power Point presentation answering Question8, recorded in MP4 format.

   Referencing

   It is essential that you use appropriate APA style for citing and referencing research.Please see more information on referencing here http://library.laureate.net.au/research_skills/referencing

   Submission Instructions

   Submit Assessment 2 via the Assessment link in the main navigation menu in MIS602 Database Modelling and Database Design.

   Academic Integrity Declaration

   I declare that except where I have referenced, the work I am submitting for this assessment task is myownwork.IhavereadandamawareofTorrensUniversityAustraliaAcademicIntegrityPolicy and Procedure viewable online athttp://www.torrens.edu.au/policies-and-forms

   I am aware that I need to keep a copy of all submitted material and their drafts, and  I will do so accordingly.