The Subject Learning Outcomes demonstrated by successful completion of the task below include: b)Design solutions applying relational data base techniques to complex problems and communicate these solutions to all stakeholders. 12WeekCycle:Dueby11:55pmAEST/AEDT Sundayendof Module 4.2 (week 8) 6WeekCycle:Dueby11:55pmAEST/AEDTSundayendof Module 4.2 (week 4)ASSESSMENT 2 BRIEF
Subject Code and Title MIS602 Data Modelling and Database Design Assessment Database Implementation Individual/Group Individual Length N/A LearningOutcomes Submission Weighting 35% Total Marks 100 Marks
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.
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
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:
In this task, you will identify the department with the highest average hours worked per employee in 2024.
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.
Write aquery to identify the most recent salary increment given in 2024. Your query should include the following details:
Which projects have the highest employee participation?
In this task, you will identify the projects with the highest number of employees assigned.
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:
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.
Analyse the impact of work hours on performance warnings across different departments. Tasks:
In this task, you will redesign the schema to track employees' historical department transfers while maintaining normalization.
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).
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
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
You are required to submit two files:
Word document:A Word document containing answers to all the Questions, except Question 8.
Guideline to prepare the word document:
Recorded Presentation:A7-minute Power Point presentation answering Question8, recorded in MP4 format.
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
Submit Assessment 2 via the Assessment link in the main navigation menu in MIS602 Database Modelling and Database Design.
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.
Get original papers written according to your instructions and save time for what matters most.