Database Design and Management Assessment-3 (TECH1400) Assignment Help

Your Task

This demonstration assessment is to be completed individually.

– You will be given a database schema, data, and a scenario.

– You are to carefully analyse the database, ensuring you understand the stored data. – You must write an SQL query for each question below.

– You must also answer the additional question at the end of the assessment.

– Once completed, you must submit your .sql file to Turnitin.

Assessment Description

University of Australia (UoA) is a university with 24 departments located all across Australia. These  departments act as the work-space for each student and employee, with a total of 20,000 employees working at UoA.

You have been hired by UoA as a Database Administrator to do some data retrieval on their database. You  have been given the database schema and data, as seen below.

Employees(EmployeeID, DepartmentID, SalaryID, Title,  EmployeeFirstName , EmployeeLastName, Gender, yearHired,  birthyear, PerformanceBonus)

Departments(DepartmentID, DepartmentName, Location)

SalaryClass(SalaryID, Salary)

DepartmentEmployee(EmployeeID, DepartmentID)

A member of the leadership team at UoA, Chris Taverly, is interested in the data stored in this database  and has asked you a variety of questions. As there are thousands of rows of data in this database, it is not  possible to find answers to these questions by eye. You are asked to use MySQL to find the answers to the questions below.

Data Files

Once found on MyKBS, you must download the following files:

Assessment_Database.sql

Assessment_Employee_Data.sql

Assessment_Data.sql

Assessment Instructions

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.

Question 1: Show the first five thousand employees in the employees table (10 marks) Question 2: Display all employee first names in alphabetical order (10 marks)

Question 3: Show employees who have worked at the University the shortest amount of time (20 marks) Question 4: Display the names of all employees whose age is a palindrome (20 marks)

Question 5: Write a query to display all male and female employees who were not born in 1993 and who  will not receive a performance bonus (20 marks)

Question 6: Is there anything in this database that you would improve? If no, explain why. If yes, what  would you change and why? (200 words) You must include this in your .sql file as a comment) (20 marks)

Important Study Information

Academic Integrity Policy

KBS values academic integrity. All students must understand the meaning and consequences of  cheating, plagiarism and other academic offences under the Academic Integrity and Conduct Policy.

What is academic integrity and misconduct?

What are the penalties for academic misconduct?

What are the late penalties?

How can I appeal my grade?

Word Limits for Written Assessments

Submissions that exceed the word limit by more than 10% will cease to be marked from the point at  which that limit is exceeded.

Study Assistance

Students may seek study assistance from their local Academic Learning Advisor or refer to the  resources on the MyKBS Academic Success Centre page. Click here for this information

Assessment Marking Guide 

Question 1

Question 2 

Question 3

Question 4 

Question 5 

Question 6