PS_GHW4_CSGY6083B_F23

.pdf

School

New York University *

*We aren’t endorsed by this school

Course

6083

Subject

Computer Science

Date

Jan 9, 2024

Type

pdf

Pages

6

Uploaded by LieutenantElectron12238

Report
CS-GY 6083 - B, FALL 2023 Principles of Database Systems Assignment: 4 [100 points] Please submit your assignment on NYU Brightspace course site with a single PDF document attachment. Please mention Student ID, Name, Course, Section Number, and date of submission on first page of your submission. Each table in your submission of SQLs and their results should have your initial as prefix, e.g., AP_EMPLOYEE etc. You can use either Oracle or MySQL for this assignment. Q1) To write a database procedure (Oracle or MySQL) [60 points] The HR department intend to give salary increment to employees of specific department when requested by their department director. Different directors have different criteria about salary increment. For an example, some directors ask for base increment as 5% of average salary of their department, and some may ask for base increment as 7% or 10% of average salary. So, the base increment percent of avg. salary is determined by the department director. However, following criteria remains same for all departments. The new salary is calculated by the formula, New Salary = S + N% of A+ S*Y% S= original salary N%= base increment percent of department’s avg. salary (e.g 5, 7, 10 etc.) A= average salary of the department Y=Square root of number of years employee’s working as of Dec. 31 st , 2022. Write a database procedure that takes two input variables department number and base N percentage of avg salary. Apply salary increment criteria as detailed above. Your procedure name should have your initial as prefix, e.g. AP_RAISE_SAL. Use the table and its data attached to the assignment. Submit: a) Procedure code (Oracle or MySQL) CREATE OR REPLACE PROCEDURE AP_raise_sal ( p_deptno IN NUMBER,
p_base_increment_percentage IN NUMBER ) AS v_avg_salary NUMBER; ref_date DATE := TO_DATE('2022-12-31', 'YYYY-MM-DD'); BEGIN SELECT AVG(salary) INTO v_avg_salary FROM AP_EMPLOYEE WHERE DEPARTMENT_ID = p_deptno; UPDATE AP_EMPLOYEE b SET salary = ROUND(salary + (v_avg_salary * p_base_increment_percentage / 100) + (salary * SQRT(EXTRACT(YEAR FROM ref_date) - EXTRACT(YEAR FROM hire_date)) / 100)) WHERE department_id = p_deptno; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM); END; / b) If you are using Oracle, provide result of following, SELECT employee_id, first_name,last_name,hire_date,department_id, salary FROM ap_employee WHERE department_id=90; SELECT employee_id, first_name,last_name,hire_date,department_id, salary FROM ap_employee WHERE department_id=90; execute ap_raise_sal (90, 5); -- 90 is the department_id and 5 is base increment of avg. salary
SELECT employee_id, first_name,last_name,hire_date,department_id, salary FROM ap_employee WHERE department_id=60; SELECT employee_id, first_name,last_name,hire_date,department_id, salary FROM ap_employee WHERE department_id=60; execute ap_raise_sal (60, 5); -- 60 is the department_id and 5 is base increment of avg. salary Q2) Indexes [40 points] Consider following queries to the same employee table that used in Q1.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
  • Access to all documents
  • Unlimited textbook solutions
  • 24/7 expert homework help
Recommended textbooks for you
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
SEE MORE TEXTBOOKS
Recommended textbooks for you
Text book image
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781285196145
Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel
Publisher:Cengage Learning
Text book image
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Text book image
Np Ms Office 365/Excel 2016 I Ntermed
Computer Science
ISBN:9781337508841
Author:Carey
Publisher:Cengage
Text book image
Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
SEE MORE TEXTBOOKS