Ban 453: Data management & Sql | fall 2022 | bostonProject 2: database implementation & sql queryingThis is a two-part assignment, which will be worth 50% of your final grade. The first part will...

1 answer below »
Instructions are attached in the file


Ban 453: Data management & Sql | fall 2022 | boston Project 2: database implementation & sql querying This is a two-part assignment, which will be worth 50% of your final grade. The first part will involve translating the database you designed in Project 1 into a physical database using MySQL. The second part will involve querying a different dataset based on business questions that will be provided to you. Details of each part are broken down below. PART 1: PHYSICAL DATABASE CREATION · Step 1: Create database in MySQL for the database you designed in Project 1. · Step 2: Create tables with their respective columns. Columns should be formatted for an appropriate datatype and should have appropriate constraints (e.g., PRIMARY KEY, NOT NULL, UNIQUE). · Step 3: Insert three rows of data into your database. (If you add five rows of data, you may earn up to 5 bonus points). Note that if you lost points for database design (problematic relationships between tables, issues with normalization, etc.) in Project 1 you will not be penalized for those issues again in Project 2 (i.e., you do not need to fix them for this project). However, you will be expected to fix any spelling or formatting issues from Project 1 in this project (i.e., if you misspelled table or column names, correct those mistakes for this project). While you need to enter all foreign key columns into their respective tables, you do not need to do anything special to designate them as “foreign keys” in MySQL. Simply add them as columns with the appropriate data types (and constraints for NOT NULL or UNIQUE if and when appropriate). PART 2: DATABASE QUERYING You have been provided a MySQL script file that, when run, will create a database called “human_resources”. Once you have created this database, create queries within MySQL that that will allow you to answer each of the following business questions. You will answer these questions in a Word Document but will also submit your MySQL query code as a MySQL file type. (Refer to the “File Submissions/Formatting Requirements” section of this document for detailed instructions on submission requirements.) “Appendix 1” at the end of this document contains an ER diagram illustrating the logical design of the database: Business Questions to be Answered: 1. List all employees who work in the Engineering department within the Austin office. Report their employee ID, first name, last name, department, and office city. 2. List all employees who have a role title of “Vice President”. Report their employee ID, first name, last name, and role title. 3. List all female employees with the job title of “Director” in the Engineering department. Report their first name, last name, role title, department name, and office city. 4. List all employees who work in either the Beijing or Miami offices and also work in the Accounting department. Report their first name, last name, office city, and department name. 5. Report all employees at the company who have the letter “c” as the third letter of their last name. Report their first name and last name. 6. Report all role titles that have a bonus of at least 10%. Report role title, salary range minimum, salary range maximum, and bonus percentage. (Note that you do not need to reformat any of these values.) 7. List all employees who work in the Finance department in the Phoenix office, along with their monthly base salary. Report their first name, last name, department name, office city, annual base salary, and monthly base salary (“monthly_base_sal”). 8. Report the number of employees who work within each department. Provide the name of each department as well as the number of employees for each given department (“employee_count”). Departments should be ordered by highest number of employees to lowest number of employees. 9. List each department and their average base salaries. Order your results by highest to lowest average base salary. Report average base salary with a dollar sign ($) and round to the nearest dollar. 10. List each employee who makes a base salary of $100,000 or more per year. Report their first name, last name, and annual base salary. 11. List each role’s average annual base salary. Restrict your results to those roles that have an average base salary of $90,000 or higher. Report role title, average annual base salary (“avg_annual_base_sal”) 12. Create a list of each employee (emp_id, first_name, last_name) as well as a column called “bonus” that indicates whether that employee receives an annual bonus (with values of “Y” for yes and “N” for no). 13. Create a copy of your “employees” table and call it “employees_2”. In this table, include all columns and records from “employees”, but add a column called “age”. Populate this column with each employee’s age (in years). 14. Create a table called “employee_salary”. In this table, include each employee’s employee ID, first name, last name, annual base salary, and bonus percentage.,. Also include the following additional columns: · “bonus_amt” (employee’s bonus amount in dollars) · “total_annual_comp” (employee’s total annual compensation including base salary and bonus) 15. List all roles that have a salary range minimum that is less than $50,000. Report role_title, salary_range_min, and salary_range_max. Order your results by salary_range_min (lowest to highest). 16. List all roles that have a salary range maximum that is at least $50,000 greater than the salary range minimum. Report role_title, salary_range_min, and salary_range_max. Order your results by salary_range_max (highest to lowest). 17. List the salary ranges for each role title. List the role title, salary range minimum, salary range maximum, and salary range. Order your results by largest salary range to smallest salary range. Round all dollar amounts to the nearest dollar. 18. Create a list of each employee who works in the Austin office (include first name and last name columns), along with their full office mailing address in the following format: First Name Last Name, Address 1, Address 2, City, State, Country Postal Code. (Example: John Smith, 14 Oak Drive, Suite 202, Rochester, New York, USA 10026) (Note that some fields have commas between them, and some do not.) 19. Pull a list of all employees with their respective role title and department in the following format: [FIRST INITIAL]. [ LAST NAME], [ROLE_TITLE], [DEPARTMENT] E.g.: J. SMITH, VICE PRESIDENT, SALES (Note all caps) 20. Create a table called “current_employees” that includes all data from the “employees” table but is restricted to employees who currently work at the company. (Hint: It is possible that all employees in the database currently work at the company.) 21. BONUS (+5 POINTS): Report each office’s city along with the percentage of its employees who are female, male, and unspecified (perc_female, perc_male, perc_unspec). Exclude NULL values from your denominator. (HINT: you are allowed to write multiple queries/create multiple tables to achieve the required results.) 22. Next year, the company will be providing 3.5% raises to each employee’s base salary. Calculate each employee’s new base salary in a column called “base_salary_2023”. In your results, report each employee’s first name, last name, current base salary (as “base_salary_2022”) and “base_salary_2023”. 23. List all employees who have a first name that starts with the letter “J” and a last name that starts with the letter “R”. Include their emp_id, first_name, and last_name in your results. 24. Calculate the number of years that each employee has worked at the company (“employment_years”) as of the day that you conduct the query. Report the employee’s emp_id, first_name, last_name and employment_years. 25. Report the number of employees within each combination of office and department. Report the office_city, dept_name, and employee_count. 26. Report all employees who were hired between 1/1/19 and 6/30/19. Report emp_id, first_name, last_name, and hire_date. 27. Report all employees who were hired in the month of January of any year. Report emp_id, first_name, last_name, and hire_date. 28. You have been asked to determine if the average annual base salary is greater for Computer Systems Managers or System Administrators. Calculate the average base salary for each role. Report the role_title and “avg_base_sal”. 29. Create a copy of the “offices” table and call it “offices_2”. In this table, include all columns and records from “offices”, but add an additional column called “continent”. Populate this column with the continent that the given office is in. File Submissions/Formatting Requirements: Please submit the following six files (three files for Part 1 and three files for Part 2) in the format described below. Part 1 Files: 1. Word Document or PDF of your logical model from Project 1. This file should have the following naming convention: “Project 2_Part 1_ Logical Model_LastName_FirstName” 2. MySQL Script file with the queries you wrote to create your database. This file should be well formatted and should include comments to serve as headers to organize the document (you may reference the HR SQL script from Part 2 as an example of this). This file should have the following naming convention: “Project 2_Part 1_Database Creation_LastName_FirstName.sql” 3. Another Word Document with your MySQL script syntax from #2 above pasted in the document. (You can simply copy and paste all the contents of your MySQL Script file from #2. This will serve as a reference for any second grader who may not have access to MySQL and are therefore unable to view your MySQL file.) This file should have the following naming convention: “Project 2_Part 1_ Database Creation_LastName_FirstName.docx” (any other doc file extension is acceptable). Part 2 Files: 1. Word Document with each question and a screenshot of your results directly below the given question. If your results include more than 10 rows, you may cut your screenshot off after 10 rows and add a note below that says “10 records shown in image. XXX total rows returned” (where XXX represents the total number of rows returned in your results). This file should have the following naming convention: “Project 2_Part 2_ Questions and Results_LastName_FirstName”. 2. MySQL Script file with all of your queries that you wrote to answer the given business questions. This file should be well formatted and should include each question above (along with question number) commented above the question’s respective queries (as illustrated below): This file should have the following naming convention: “Project 2_Part 2_ Queries_LastName_FirstName.sql” 3. Another Word Document with your MySQL script syntax pasted. (You can simply copy and paste all the contents of your MySQL Script file from #2. This will serve as a reference for any second grader who may not have access to MySQL and are therefore unable to view your MySQL file.) This file should have the following naming convention: “Project 2_Part 2_ Queries_LastName_FirstName.docx” (any other doc file extension is acceptable). Reminders: 1. All requirements must be met by typing SQL syntax directly into your MySQL query editor (credit will not be given for utilizing the point-and-click features within the Software). 2. Points will be deducted for not following syntax formatting best practices (e.g., if you submit a long query within a single line of code, you will lose points). 3. Points will be deducted for not following Syntax best practices, even if you get the correct results (e.g.., if you put quotes around numeric values within a query, you will lose points). Appendix 1: Figure 1: Logical Model of “human_resources” database
Answered 3 days AfterDec 11, 2022

Answer To: Ban 453: Data management & Sql | fall 2022 | bostonProject 2: database implementation & sql...

Salony answered on Dec 14 2022
27 Votes
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here