TECH1400 Demonstration and analysis – Part II Assignment Help

09 Feb 2024

Share via Whatsapp
blog-post-image

Assessment 4 Information

 

 

Subject Code:

TECH1400

Subject Name:

Database Design and Management

Assessment Title:

Demonstration and analysis – Part II

Assessment Type:

Individual

Word Count:

2000 Words

(+/-10%)

Weighting:

40 %

Total Marks:

100

Submission:

MyKBS

Due Date:

Week 13

 

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 questions 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)

 

 

 

 

 

Chris Taverly, the member of the leadership team at UoA was impressed with your previous work and has approached you for a second time, asking you more questions. Chris has asked you for to retrieve more data from the database and has also asked you to examine the integrity of the database. 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: Add a new attribute called "EmployeeInitials" to the Employees table (2.5 marks)

 Question 2: Display the first names of all employees who work in the department located in Sydney (10 marks)

 Question 3: Display employee first and last names and work out the original salary for all employees and their new salary after their (10%) bonus is applied (10 marks)

Question 4: Display how many employees will not receive a performance bonus (12.5 marks)

Question 5: Write a query that returns the employees with initials that are a palindrome and also return employees without palindrome initials, but output 'not a palindrome!' (15 marks)

Question 6: Write a query that populates EmployeeInitials, based off the existing stored names (15 marks)

Question 7: Which joins did you use in your queries and why? Would other joins have worked? (15 marks)

Question 8: Write 250 words explaining the integrity of this database. Is it up to standards? If yes, explain why and how. If no, what is wrong with the database's integrity? (20 marks)

  • Delivery in day(s):
    1
Price : $500 Dollars
Get instant help from 5000+ experts for

Your Email *

Subject/Course Code *

Your PhoneNumber *

Pages *

Deadline *

Attachment *

Description *

;