TECH1400 SQL Demonstration and analysis Assignment Help

Subject Code:	TECH1400
Subject Name:	Database Design and Management
Assessment Title:	SQL Demonstration and analysis
Assessment Type:	Coding task and Video presentation
Assessment Length:	10	Minutes (+/-10%)
Weighting:	40%
Total Marks:	40
Submission:	Via MyKBS
Due Date:	Week 13

Your 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 an 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.

This assessment aims to achieve the following subject learning outcomes:

LO1:	Investigate the use of relational database management systems in organizations.
LO2:	Construct database models.
LO4:	Create queries to manage data using a database query language.

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 questions (1–10) only, and responses to the SQL queries should be restricted to only the clauses used in workshop materials.

Question 1: Display the full names and department names of all employees who work in

departments located in Sydney, sorted by last name, then first name..

Question 2: Show the employees who have worked at the University the shortest time (include all

ties). Include their department names and calculated tenure.

Question 3: Display the full names, ages (based on 2025), and locations for employees whose

Ages are palindromes, restricted to Melbourne or Sydney.

Question 4: Create a stored procedure that lists each department name with its employee count.

The output must be sorted by the highest employee count first.

Question 5: Create a stored function that returns the average base salary for a given department

name, then write a query that selects all department names with their average salaries using the

function.

Question 6: Find the names, department names, salary IDs, and salaries of employees who are

older than 40 (as of 2025) and have a performance bonus.

Question 7: Show employee details and department names for employees with the title ‘Lecturer.’

who were hired before 2015 and whose department is in Brisbane or Adelaide.

Question 8: Display employees’ first and last names, department name, base salary, new salary

after a 10% bonus and the increase amount for employees eligible for a performance bonus.

Round currency fields to two decimals.

Question 9: Add a new attribute called “EmployeeInitials” to the Employees table.

Question 10: Write a query that populates “EmployeeInitials,” based on the existing stored names. Question 11: Write 250 words explaining the integrity of this database. Is it up to standard? If yes, explain why and how. If not, what is wrong with the database’s integrity? Include your SQL queries in a single .sql file and submit it via MyKBS.

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.

− Show your SQL queries and the database schema while you explain.

− Show the output (result set) for each question clearly on screen. Ensure column headers are

visible and legible.

− Ensure your video is clear and audible.

− Keep your explanation concise and focused within a 9-minute to 11-minute timeframe.

Recording and Submission

• Zoom is our teaching and learning tool, and you can choose to record on your computer so that

You can get the recording file in MP4 format to upload for submission in Kaltura. Click the link.

Below to learn how to upload a recorded video to Kaltura:

• Turn on your webcam so we can see you to assess your presentation skills. • You MUST host your video recording file in Kaltura only using the MyKBS portal (using any other video hosting platforms will NOT be considered for marking).

Important note: For this assessment, there will be two submission links: one on Moodle for the SQL file and another on Kaltura for your video.

Important Study Information

Academic Integrity and Conduct Policy

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

Please read the policy to learn the answers to these questions:

• What is academic integrity and misconduct?

• What are the penalties for academic misconduct?

• How can I appeal my grade?

Late submission of assignments (within the Assessment Policy)

Length Limits for Assessments

Penalties may be applied for assessment submissions that exceed prescribed limits.

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. Further details can be accessed at .

Submission Requirements

All assessments, including draft copies must be written and submitted in English. The use of translation tools is not permitted.

Generative AI Traffic Lights

Please see the level of generative AI that this assessment, which is Level 2, has been designed to accept:

Traffic Light	Amount of Generative Artificial Intelligence (GenerativeAI) usage	Evidence Required	This assessment ✓
Level 1	Prohibited: No AI allowed This assessment showcases your individual knowledge, skills, and/or personal experiences in the absence of generative AI support.	The use of generative AI is prohibited for this assessment and may potentially result in penalties for academic misconduct, including but not limited to a mark of zero for the assessment.
Level 2	Optional: You may use GenerativeAI for research and content generation that is appropriately referenced. See assessment instructions for details This assessment allows you to engage with Generative AI as a means of expanding your understanding, creativity, and idea generation in the research phase of your assessment and to produce content that enhances your assessment. I.e., images. You do not have to use it.	The use of GenAI is optional for this assessment. Your collaboration with GenerativeAI must be clearly referenced just as you would reference any other resource type used. Click on the link below to learn how to reference GenerativeAI. https://library.kaplan.edu.au/referencing other-sources/referencing-other-sources generative-ai In addition, you must include an appendix that documents your GenerativeAI collaboration including all prompts and responses used for the assessment. Unapproved use of generative AI as per assessment details during the content generation parts of your assessment may potentially result in penalties for academic misconduct, including but not limited to a mark of zero for the assessment. Ensure you follow the specific assessment instructions in the section above.	✓
Level 3	Compulsory: You must use GenerativeAI to complete your assessment See assessment instruction for details This assessment fully integrates Generative AI, allowing you to harness the technology’s full potential in collaboration with your own expertise. Always check your assessment instructions carefully as there may still be limitations on what constitutes acceptable use, and these may be specific to each assessment.	You will be taught how to use generative AI and assessed on its use. Your collaboration with GenerativeAI must be clearly referenced just as you would reference any other resource type used. Click on the link below to learn how to reference GenerativeAI. https://library.kaplan.edu.au/referencing other-sources/referencing-other-sources generative-ai In addition, you must include an appendix that documents your GenerativeAI collaboration including all prompts and responses used for the assessment. Unapproved use of generative AI as per assessment details during the content generation parts of your assessment may potentially result in penalties for academic misconduct, including but not limited to a mark of zero for the assessment. Ensure you follow the specific assessment instructions in the section above.

Assessment Marking Guide: Part A SQL Querying

Marking Criteria

F (Fail)

P (Pass)

C (Credit)

D (Distinction)

HD (High Distinction)

_____| 20 marks

0 – 49%

50 – 64%

65 – 74%

75 – 84%

85 – 100%

SQL Query

responses and

Accuracy

_____| 10 marks

Inaccurate

responses or no attempt has been made.

A good response to at least 3 queries with no errors and inconsistencies.

A good response to at least 5 queries with no errors and

inconsistencies.

A good response to at least 7 queries with no errors and

inconsistencies.

A complete response to all the queries

without errors.

Use of clauses in SQL Query

_____| 5 marks

Inaccurate

responses or no attempt has been made.

At least 3 queries were written based on the clauses

covered in the

workshop.

At least 5 queries were written based on the clauses

covered in the

workshop.

At least 7 queries

were written based on the clauses covered in the workshop.

All 10 queries were written based on the clauses covered in the workshop.

Database Integrity Analysis

_____| 5 marks

Provides a poor analysis of the database

integrity.

Provides basic

analysis of

database integrity.

Provides good

analysis of the

database integrity.

Provides very good and accurate analysis of database integrity.

Provides excellent and accurate analysis of the database integrity.

Feedback and grades will be released via MyKBS

Assessment Marking Guide: Part B Video Explanation

Marking Criteria

F (Fail)

P (Pass)

C (Credit)

D (Distinction)

HD (High Distinction)

_____| 20 marks

0 – 49%

50 – 64%

65 – 74%

75 – 84%

85 – 100%

Video Explanation _____| 12 marks

Poor or unclear justification of

each query.

Inadequate

explanation of

syntax and

output.

Basic justification of each query.

Adequate

explanation of

syntax and output.

Clear justification of some queries only. Good explanation of syntax and output.

Clear justification of each query. Good explanation of syntax and output.

Clear, detailed

justification of each query. Excellent

explanation of syntax and output.

Presentation

Skills

_____| 6 marks

No appropriate eye contact,

posture, or

speech volume.

Attempts

appropriate eye

contact, posture, and speech

volume.

Good eye contact, posture and speech volume reflect some interest in the topic.

Appropriate facial

expression, body

language, posture, eye contact and

speech volume reflect an interest in the

topic.

Appropriate facial

expression, body

language, posture, eye contact, and speech volume reflect strong interest and

enthusiasm in the

topic.

Time management of the

presentation

_____| 2 marks

The video is

over/under the time limit (more than 4 minutes

over or under the provided time

limit).

The video is

over/under the time limit (3 minutes

over or under the provided time limit).

The video is

over/under the time limit (2 minutes over or under the provided time limit).

The video is slightly over/under the time limit (1 minute over or under the provided time limit).

The video is within the provided time limit (9 minutes to 11 minutes time limit).

Feedback and grades will be released via MyKBS