DataModellingandDatabaseDesign MIS602

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 utilizeessential queryskills 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.Furthermore,criticallyanalysingdatabasedesignflawsandimprovingdatabasestructurefor integrity and maintainability is another important skill for IS professionals.

Task Instructions

Please read and examine carefully the attached MIS602_Assessment 2_Case study and then answerthequestions.Pleasenote,extramarkswillbeawardedforpresentationandreadability 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
• Where SQL queries must be provided, each question should be answered with a single SQL statement unless specified otherwise.
• Outputs should bepresentedclearly,with attention to theorder ofcolumns and thenaming of calculated columns.
• The LIMIT clause should be used diligently, ensuring it does not obscure important competing values.
• IfanyAItoolshavebeenusedinanyquestions,includeallreferencesfollowingtheuniversity'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. Writeaqueryusingajointocalculatetheaveragehoursworkedperemployeeforeach
2. Writeaqueryusing an uncorrelatedsubquery toachievethe same
3. Writeaqueryusing acorrelatedsubquery to achievethe same
4. Explainwhichapproach thebestis substantiatingyourargumentswithrelevant

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.

QUESTION2 [5MARKS]

Writeaquerytoidentifythemostrecentsalaryincrementgivenin2024. Your query should include the following details:

• Theemployee's nameand
• Thedepartment
• Themanager'sname andIDwhoapprovedthe
• Theincrement
• Thedate ofthe

QUESTION3 [5MARKS]

Which projects have the highest employee participation?

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

1. Writean SQLquery to findthe numberofemployees assignedto each
2. Identifytheproject(s)with thehighest employee

QUESTION4 [5MARKS]

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

Tasks:

1. WriteSQLqueriestodetectthese
2. Provideawrittenexplanation ofhowyouidentified andaddressedthese

QUESTION 5 [10MARKS]

Identify the employees with the most diverse project assignments in terms 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.

1. Writean SQLquery to findthe numberofuniqueprojectseach employeeis assigned to
2. Identifythetop5employeeswiththemostdiverse andleastdiverseprojectassignmentsin terms of skills.
3. Writeashortreport,notmorethan200words,discussingyourfindings includingthe matrices used to reach conclusions.

QUESTION 6 [20MARKS]

Analysetheimpactofworkhoursonperformancewarningsacrossdifferentdepartments. Tasks:

1. Write an SQL query to compare the number of warnings received by employees working morethan40hoursperweektothoseworking40hoursorless,acrossdifferentdepartments (each department).
2. WriteanSQLquerytoidentifythetop5employeeswiththehighestaverageworkhoursper 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. WriteanSQLquerytofindthetotalnumberofwarningsandaverageworkhoursforall employees, broken down by department. Compare the results to identify any trends or
4. Basedontheresultsoftheabovequeries,writeashortreport(notmorethan300words) discussing:

• Doemployeeswithmoreworkhoursreceivemorewarningscomparedtothosewith fewer hours?
• Arethereanyoutliers,suchasemployeeswithhighwarningsbutlowhours,orvice versa?
• Arecertaindepartments moreaffectedbythesetrends?
• Analysethedatatodetermineifthereisaconsistentpatternacrossdifferentgroupsand
• ProviderecommendationstoHRbasedonyourcomparative

QUESTION 7 [20 MARKS]

Inthistask,youwillredesigntheschematotrackemployees'historicaldepartmenttransferswhile maintaining normalization.

1. Describe how you would redesign the schema (logical model) to support employees'historicaldepartmentUsevisuals(e.g., logicalERdiagram)tosupportyouranswer, including sample data.
2. 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 [25MARKS]

Carefullyreviewtheprovidedcasestudyandtheexistingdatabasestructure,includingthetablesand their relationships.

Task1:IdentifyAreasof Improvement

• Identify at least three areas where the database structure can be improved, focusing on normalization,incorporationofadditionaltables,andoptimizingexisting

Task 2:ProposeImprovements

• Foreachidentified area,proposespecific
• Explainwhyeachimprovementisnecessary with
• Describehoweachimprovementwillbenefitthedatabase'sperformance,integrity,and maintainability substantiated with evidence.

Task 3:Prepare a 7-minute presentation with no more than 6 slides. Your presentation should incorporatetheabovetasks,clearlyillustratingyourpoints,andshouldbesubmittedasarecordingin MP4 format.

PresentationGuide

• Anoverviewofthecurrentdatabase
• Identifiedissuesandareasof
• Detailedexplanationoftheproposed
• Expectedbenefitsofthe proposed
• Proposeddatabase
• Conclusion

SubmissionInstructions

Youarerequired tosubmittwo files:

1. Worddocument:AWorddocumentcontaininganswerstoalltheQuestions,except Question 8.

Guidelinetopreparetheworddocument:

• Copyandpaste alltheSQLquestionsinto theWord
• Undereachquestion,provide:
• Thecorresponding SQL query or
• An image of the result set. If the query returns more than 10 records, capture onlythefirst10lines;otherwise,captureallthe DoNOTuseLIMIT 10in your Query to force 10 records as it might overshadow the total number of records returned by the Query.
• An image of the output as evidence of running the query. Capture the output asanimagewhichshouldalsoshowhowmanyrecordsreturnedbythe
• Anyother tasks associatedwith the
• Allreferences ifAItoolshavebeen usedin anyof the

2. RecordedPresentation:A7-minutePowerPointpresentationansweringQuestion8, recorded in MP4 format.

Referencing

ItisessentialthatyouuseappropriateAPAstyleforcitingandreferencingresearch.Pleaseseemore information on referencing herehttp://library.laureate.net.au/research_skills/referencing

SubmissionInstructions

SubmitAssessment2viatheAssessmentlink in the main navigation menu in MIS602 Database Modelling and Database Design.

AcademicIntegrityDeclaration

I declare that except where I have referenced, the work I am submitting for this assessment task is my own work. I have read and am aware of Torrens University Australia Academic Integrity Policy and Procedure viewable online athttp://www.torrens.edu.au/policies-and-forms

Iamawarethat Ineedto keepacopyofallsubmittedmaterialandtheirdrafts,and Iwilldoso accordingly.

AssessmentRubric

CaseStudy:EnhancingWorkforceEfficiencythroughDatabaseManagement

Innovative Solutions Ltd. is a medium-sized enterprise employing over 50 individuals across various departments such as Human Resources, IT, Finance, Marketing, and Operations. To streamline operations and enhance decision-making, the company implemented a robust database system. This system manages essential aspects of workforce operations, including employee details, project assignments, work schedules, and performance tracking.

The database comprises several interrelated entities. The Employees table stores personal details, employment status, and department affiliations. The Departments table organizes the workforce into functional areas, ensuring streamlined management. Projects are logged in a dedicated table, capturing departmental initiatives and objectives.

WorkAssignmentslinkemployeestospecificprojects,trackingtheirhoursandcontributions. Shifts record employee schedules, highlighting overlapping or weekend work that may need attention. The Work Hours table logs daily efforts, providing insights into individual and departmental productivity. Performance Warnings track employees who require intervention, guiding tailored training or corrective measures. Salary Increments log adjustments, documenting the rationale for raises and the approving managers.

This comprehensive system enables the company to address key challenges such as project allocation, workload balance, and performance evaluation. By leveraging the database, Innovative Solutions Ltd. ensures fair recognition, compliance with policies, and informed managerial decisions, fostering a productive and efficient workplace environment.