Reference no: EM133926285 , Length: 7 minutes PPT

Database Design and Management

Assessment - SQL Demonstration and analysis

Assessment Type: Coding task and Video presentation

Task

This assessment evaluates your SQL querying proficiency and your ability to justify and explain query logic. You will receive a database schema, data, and a scenario. You can use Gen AI tools for specific tasks only. The assessment consists of a SQL query task (Part A) and a detailed video explanation (Part B).

Assessment Description

In this assessment, you will use a case study provided as a supplementary document. Your task is to retrieve data on the given database using MySQL to find answers to the questions in the Assessment Instructions. The database contains thousands of rows of data, making it impractical to find answers manually. Get expert-level assistance in any subject with our assignment help services.

Learning outcome 1: Explain the use of Relational Database Management Systems in organisations.

Learning outcome 2: Describe a database model that demonstrates the association of entities and their relationships.

Learning outcome 3: Use a database query language to interpret queries related to data management.

Assessment Instructions

Part A: SQL Querying
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. In addition, you can use Gen AI tools to get an idea about how to answer the questions of this part only, and responses to the SQL queries should be restricted to only the clauses used in workshop materials.

Question 1: Display the first names of all employees who work in the department located in Sydney in alphabetical order.

Question 2: Show employees who have worked at the University the shortest amount of time. Question 3: Display the names of all employees whose age is a palindrome.

Question 4: Create a procedure to list the departments along with the number of employees in each department.

Question 5: Create a function that finds the average salary of employees in each department. Question 6: Add a new attribute called "EmployeeInitials" to the Employees table.

Question 7: Write a query that populates "EmployeeInitials", based off the existing stored names.

Part B: Video Explanation

You should record a video discussing the following:
Justify each SQL query in detail, including the syntax, the purpose of the query, and the appropriate output.
Ensure your video is clear and audible.
Show your SQL queries and the database schema while you explain.
Keep your explanation concise and focused within a 6-minute to 8-minute timeframe.