ITEC200: Data and Information Management | My Assignment Tutor

ASSESSMENT GUIDEITEC200: Data and Information Management, Semester 1, 2021Assessment 3 – IndividualAssessment Artefact: Database (SQL Code)Weighting [45%]Why this assessment?What are the types of employability skills that I will acquire upon completion of thisassessment?Assessment Overview:Purpose, as written in the EUO Due date:Thursday 10 June 2021 – 5 pm (exam week)Weighting:45%Length and/or format:SQL Code + Video recordingLearning outcomes assessedLO3, LO4Graduate attributes assessedGA5, GA8, GA10How to submit:Via LEOReturn of assignment:Final semester resultsAssessment criteria:Rubric: see end of document • To enable students to develop professional skills through the application of theoretical knowledge and skillsin database design and applying SQL for data definition and manipulation. Skill TypeDeveloped critical and analytical thinking☒Developed ability to solve complex problems☒Developed knowledge in the field study☒Developed work-related knowledge and skills☒ 2ContextThe Human Resources (HR) database is where transactions relating to payroll processing, position management,time and attendance, recruitment, benefits, and other human resources data are recorded, stored and retrieved ina variety of ways. In this assessment, you will apply SQL on a HR database to generate insightful information fromdata. The following diagram provides an overview of the structure of the HR database.The HR database has seven tables:1. The employees table stores the data of employees.2. The jobs table stores the job data including job title and salary range.3. The departments table stores department data.4. The dependents table stores the employee’s dependents.5. The locations table stores the location of the departments of the company.6. The countries table stores the data of countries where the company is doing business.7. The regions table stores the data of regions such as Asia, Europe, America, and the Middle East and Africa.3The countries are grouped into regions.Instructions1. Create a new Schema in mySQL Workbench. The name of schema must be your student ID.2. Set the schema you just created as default schema.3. Download the “Create HR Database script” From LEO and execute it in MySQL. This will create thetables.4. Download the “Load HR Data script” From LEO and execute it in MySQL. This will load data intothe tables.5. Queries5.1. Write a query to add an index to the phone_number filed.5.2. Write a query to add a column to the department table. Use your student ID as column name.This column will be used for entering up to 50 characters data.5.3. Write a query to create a table according to the following specifications:Table Name: Employee_TrainingAttributes: employee_ID INT, Training_Description VARCHAR (100), date_from DATE, date_toDATE, Training hours int must be between 1 and 100. Date_to must be grater than or equealdate_from.employee_ID must be defined as a foreign key.5.4. Add a constraints to the salary field in the employee table to limit the salary to numbers between1000 and 100,000.5.5. Write a query to replace the name, last name and email address of employee number 101 withyour name, last name and email address.5.6. Create a query that shows the employee_id, first_name, last name, first_name of employee’smanager, last name of employees’ manager.5.7. Write a query that lists employees who have the letter w in their last name.5.8. Write a query that lists employees who have a salary greater than 3000 and work in the US.5.9. Write a query that shows all employees and the number of dependants for each employee. Note:ALL employees must be in the list even if the employee does not have any dependent. 5.10.5.11.Write a query to show the number of employees in each city. (City – number of employee)Write a query to show the number of employees in each country. (country – number ofemployee)5.12.Write a query to show the number of employees in each region. (region – number ofemployee)5.13.5.14.Write a query that shows the average salary for employees who work in the IT department.Create a Stored Procedure the receives department_ID as input and returns the numberof employees how work in that department, their maximum salary, minimum salary, and averagesalary5.15.Create a Stored Function that calculates the salary tax based on the following rule: if salaryis les than 4000, tax would be zero. If salary is grater than or equal to 4000, tax would be %15of salary.5.16.Create a view that shows the list of employees and their salary tax (Use the tax function you created in the previous step in the view).4 5.17.Create a stored procedure that increases the salary of employees by 10%. Use transactionmanagement features of MySQL (COMMIT, ROLLBACK). When there is any error occurs duringthe query execution the changes to data (update) must be undone (rollbacked).5.18.Write a SQL script to create a user called ACU_u ser, set the password to 20###21, grantthe privileges SELECT and UPDATE on columns min_salary and max_salary.5.19.Write a SQL script to remove the UPDATE privilege on columns min_salary and max_salary.How do I submit?Submit the SQL Script (.sql) and Video presentation (mp4) files via the submission link provided on LEO underAssessment section.1. Create A MYSQL dump file containing your database schema and data records, using Server → DataExport menu in MySQL Workbench. You should export your database as one self-containing file inone transaction, include create schema (This link has the steps for database export: MySQL ::MySQL Workbench Manual :: 6.5.2 SQL Data Export and Import Wizard) .2. Recorded presentation:2.1. Run MySQL workbench2.2. Run Zoom, turn on your webcam (to include your face in the presentation) and share your screen(to show MySQL Workbench)2.3. Use Zoom to record a short video presentation (3-4 minutes). In your presentation run queries you’vewritten for questions 5.9, 5.12,5.13 , 5.14 to 5.19 (explain the commands/functions used in each queryand how the query works).5Important Notice: The purpose of this recording is to safeguard academic integrity. The assignment will receive aFAIL grade if you do not submit the recorded presentation, or your presentation does not provide adequate evidencethat the submitted materials are original and the result of your own work.Some Helpful Websites and ResourcesMySQL :: MySQL Workbench Manual :: 6.5.2 SQL Data Export and Import Wizard)Who can help me?Lecturer, Dr Mehdi RajaeianI’m having problemsYou may be eligible to apply for one of the following extensions:Application for Extension (EX) of Time for submission of an Assessment TaskThe EX form should be completed by ACU students applying for an extension of time for submission of anassessment task. The completed and signed form must be submitted to the relevant National Lecturer-in-Chargeprior to the due date of the assessment task. It must be accompanied by supporting documentary evidence such asEIP, doctor’s certificate or equivalent, death certificate, or a statutory declaration.Application for Special Consideration (SC)The SC form is used by students to apply for Special Consideration for assessable work in studies at AustralianCatholic University. It must be accompanied by supporting documentary evidence as outlined in the SC form.Approval of such applications will only be granted to students who are legitimately disadvantaged in theirassessment due to exceptional and unforeseen circumstances beyond their control.ReferencingN/A.CriteriaThe full criteria are compiled in a rubric, which can be found on the following page/s.6Rubric for Assignment 3 [45%] Relevant LO/GAsCriterion (related to asingle GA from therelated LO – one GAper criterionDoes not meetexpectationsMeets expectationsExceeds expectationsNNPACRDIHDGA8L3W: 10 marksTL: 3LS: D, AData definition (Task5.1-5.4)Queries are notimplemented orreturn error.At least half ofqueries areimplementedcorrectly,demonstratingabove elementarylevel skills in usingSQL DDL.At least two third ofqueries areimplementedcorrectly,demonstratingintermediate levelskills in using SQLDDL.Most of queries areimplementedcorrectly,demonstratingabove intermediatelevel skills in usingSQL DDL.All Queries areimplementedcorrectly,demonstratingadvanced levelskills in using SQLDDL.GA5LO4W: 23 marksTL: 3LS: DData manipulation(Tasks 5.5-5.15)Queries are notimplemented orreturn error.At least half ofqueries areimplementedcorrectly,demonstratingabove elementarylevel skills in usingSQL DML.At least two third ofqueries areimplementedcorrectly,demonstratingintermediate levelskills in using SQLDML.Most of queries areimplementedcorrectly,demonstratingabove intermediatelevel skills in usingSQL DML.All Queries areimplementedcorrectly,demonstratingadvanced levelskills in using SQLDML.GA10LO4W: 12 marksTL: 3-6LS: DDatabaseadministration (DBA)(Task 5.16-5.19)Queries are notimplemented orreturn error.At least half ofqueries areimplementedcorrectly, ,demonstratingabove elementarylevel skills in DBA.At least two third ofqueries areimplementedcorrectly,demonstratingintermediate levelskills in using SQLDBA.Most of queries areimplementedcorrectly,demonstratingabove intermediatelevel skills in usingSQL DBA.All Queries areimplementedcorrectly,demonstratingadvanced levelskills in using SQLDBA. 7

QUALITY: 100% ORIGINAL PAPER – NO PLAGIARISM – CUSTOM PAPER

Leave a Reply

Your email address will not be published. Required fields are marked *