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
select * from ap_employee where substr(last_name,1,1)='A'and JOB_ID='SA_REP' order by last_name; select upper(first_name), upper(last_name), department_id, salary from ap_employee a where a.salary>(select avg(salary) from ap_employee b where b.department_id=department_id); For each of the above query do following a) Suggest which column(s) are suitable for indexes and what type of index should be created. b) Create index(es) as suggested in step a c) Create the query execution plan. Submit i) Suggested column(s) for index(es) and type of the index(es) ii) DDL code of the index(es) created. iii) Screenshot of execution plan iv) Explanation about which index(es) are used and which are not, and reason for it a) i) Indexes should be created on the following columns last_name, job_id This should be a composite index containing both columns and a functional index on last_name. Student may have suggested an additional index on last_name, since it is in order by clause . ii) CREATE INDEX idx_last_name_jobid ON ap_employee (SUBSTR(last_name, 1, 1), job_id); iii)
iv) We will use a composite and functional index, because the where clause filters based on 2 columns, both of which should be included in the index for optimal performance. Also, it also contains a function (substr on last_name), hence it should also be a functional index. We cannot use a bitmap index as it requires columns with low cardinality which is not the case for last_name. b) i) We will create index on the following columns salary - B- tree index department_id - bitmap index ii) CREATE INDEX idx_salary ON ap_employee (salary); CREATE BITMAP INDEX idx_dept_id on ap_employee(department_id); iii)
iv) We will create a B- tree index on salary to improve query performance. We cannot use a bitmap index on salary column as it does not have a low cardinality. However, we can create a bitmap index on department_id column which has low cardinality (ie, the number of distinct values of department_id would be much less than the total number of records in the employee table).
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

Related Documents

LAB Topic 2 team F23.docx
Lab Topic 12 individual.docx
Lab Topic 4 individual F23.docx
Lab Topic 6 individual F23.docx
LabAssignment_13b.docx
PS3_3_FA23_CSGY6083B.pdf
Midterm_FA23_CSGY6083B.docx
FE Workshop.pdf
Midterm previous qsns.docx
a2_handout.pdf
prep3.py
prep5.py
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