数据库代写 | INFO90002 Semester 1 Assignment 2 – SQL

这个作业是完成力资源人员数据库的设计和一些查询语句
INFO90002 Semester 1
Assignment 2 – SQL
Due: 0700H AEST Monday 11th May 2020
Submission: Via LMS https://lms.unimelb.edu.au
Weighting: 10% of your total assessment. The assignment will be graded out of 20 marks and a
percentage out of 100 awarded to you.
The Human Resources Staff Database
The Human Resources (HR) staff database stores information about staff that work in a global technology
company. Departments are located in cities in countries that belong to one of four business regions.
Within the database, staff must have a current job and they may have held jobs in other departments and
locations as part of their job history. Staff may belong to a department and may manage one or more
departments. Staff may supervise other staff, but not all staff have a supervisor.
The HR database stores staff first and last names, email and phone number, the day they were hired for their
current job role as well as their current salary and a commission percentage for those staff that are eligible.
The Data Model
The following is a physical ER model of the database:
Figure 1: The HR ER Model (Updated)
2
INFO90002 A2 S1 2020 © The University of Melbourne
Assignment 2 SET UP
INFO90002 server
To set up the database on the INFO90002 MySQL server, download the file staff.sql from the Assignment
folder on LMS and run it in Workbench. This script creates the schema and database tables and populates
them with data.
PLEASE NOTE that there are some tables similarly named the staff department table is plural called
departments. The labs2018 department table is singular and called department. Make sure you use
departments for this assignment
On your own device
To set up the database on the INFO90002 MySQL server, download the file staff-byod.sql from the Assignment
folder on LMS and run it in Workbench. This script creates the schema and database tables and populates
them with data.
To use the staff data set enter
use staff;
3
INFO90002 A2 S1 2020 © The University of Melbourne
The SQL Tasks
In this section are listed 10 questions for you to answer. Write one (single) SQL statement per question.
Subqueries and nesting is allowed within a single SQL statement. DO NOT USE VIEWS to answer questions.
1. List the first name, last name, department name and current job title of all staff.
(1 mark)
2. List every region name, country name and the number of staff in each country. Order the result
by country name.
(1 mark)
3. Who has spent the shortest amount of time in a job? Print their name (first and last name),
how long the job lasted in days and the job title.
(1 mark)
4. For all supervisors who supervise seven or more staff, list their first name, last name, job title,
and the number of staff members they supervise.
(2 marks)
5. Print department names of departments that currently have neither a manager nor any staff.
(2 marks)
6. Which region has the most locations? Print the region name, as well as the total number of
locations in that region.
(2 marks)
7. Some staff members are eligible for a commission. Find the names of staff who will exceed the
maximum salary for their job title if they achieve their commission. The calculation of a staff
member’s total income if the commission is achieved is their salary multiplied by the
commission percent and added onto their original salary. List the staff member’s first name,
last name and the amount by which they will exceed the maximum salary for their current job
role. Order the results from the highest amount to lowest.
(3 marks)
8. List the cities, country names and region names for cities outside the United States of America
and Europe where no staff work.
(3 marks)
9. How many years has Troy Anthony been in his current job role.? Include his Full Name (e.g.
‘Troy Anthony’), his Current Job Title, and length in years (rounded to the nearest whole
number).
(2 marks)
10. Print the first name, last name and current salary for all staff who held more than one job
position prior to their current position, and whose current salary is below the average value of
maximum salaries for all positions they held in the past prior to their current position.
(3 marks)
4
INFO90002 A2 S1 2020 © The University of Melbourne
Submission Details:
Submit a single PDF showing your answers to all questions to the Assessment page on LMS by 0700H Monday
11th May 2020
Formatting requirements for your submission
For each question, present an answer in the following format:
• Show the question number and question in black text.
• Show your answer (the SQL statement) in blue text (DO NOT use a screen shot)
• Show a screenshot from Workbench showing output of 10 or fewer lines.
• Show how many rows were actually returned, in red text.
• Show each query on a separate page.
Example:
Qxx. List the first name, last name and salary of all staff. Order the result by the department id, salary and last
name.
SELECT first_name, last_name, salary
FROM staff
ORDER BY department_id, salary, last_name;
118 Rows returned
5
INFO90002 A2 S1 2020 © The University of Melbourne
Requesting a submission deadline extension:
If you need an extension due to a valid (medical) reason, you will need to provide evidence to support your
request. Medical certificates need to be at least 2 days in length.
To request an extension:
1. Email the Subject Coordinator (deccles@unimelb.edu.au) with your student id, your name and your
university email with the extension request and supporting evidence.
2. If your submission deadline extension is granted you will receive an email reply granting the new
submission date. Do not lose this email!
Reminder: INFO90002 Hurdle Requirements
To pass INFO90002 you must pass two hurdles:
• Hurdle 1: Obtain at least 50% (15/30) or higher for the modelling (20) and SQL assignments (10)
• Hurdle 2: Obtain a grade of 50% (35/70) or higher for the End of Semester Exam (70)
Therefore, it is our recommendation to students that you attempt every assignment and every question in the
exam.
GOOD LUCK!
ERRATA
An earlier version of this assignment (v3) had incorrect participation relationships in Figure 1 for the following
relationships:
Locations – Departments
Staff – Job_History
Jobs – Job_History
Departments – Job_History
This should not affect your approach to any of the SQL required to answer this assignment