For your Employee Performance Management and Rating System project, I
will lay out the necessary tables, PL/SQL procedures, functions, a
trigger, and a package. This project will demonstrate your ability to
handle multiple types of database objects, implement complex business
logic, and integrate data across different tables.
Employee Performance Management and Rating System
1. Database Tables
We will have 6 tables to store information related to employees, their
performance, leave, and reviews.
1. EMPLOYEES – Stores employee details.
2. PERFORMANCE_METRICS – Stores performance metrics for employees.
3. PERFORMANCE_REVIEWS – Stores performance reviews for each employee.
4. PEER_REVIEWS – Stores peer reviews for each employee.
5. RATING_CRITERIA – Stores criteria weights used to calculate employee
performance scores.
6. LEAVE_BALANCES – Stores leave balances for employees (an additional
feature that ties into performance).
-- 1. Employees Table
CREATE TABLE EMPLOYEES (
EMP_ID NUMBER PRIMARY KEY,
NAME VARCHAR2(100),
DEPARTMENT VARCHAR2(50),
POSITION VARCHAR2(50),
JOIN_DATE DATE
);
-- 2. Performance Metrics Table
CREATE TABLE PERFORMANCE_METRICS (
METRIC_ID NUMBER PRIMARY KEY,
EMP_ID NUMBER REFERENCES EMPLOYEES(EMP_ID),
KPI_NAME VARCHAR2(100),
KPI_WEIGHT NUMBER
);
-- 3. Performance Reviews Table
CREATE TABLE PERFORMANCE_REVIEWS (
REVIEW_ID NUMBER PRIMARY KEY,
EMP_ID NUMBER REFERENCES EMPLOYEES(EMP_ID),
REVIEW_DATE DATE,
SCORE NUMBER,
REVIEW_TEXT VARCHAR2(500)
);
-- 4. Peer Reviews Table
CREATE TABLE PEER_REVIEWS (
REVIEW_ID NUMBER PRIMARY KEY,
EMP_ID NUMBER REFERENCES EMPLOYEES(EMP_ID),
REVIEWER_ID NUMBER REFERENCES EMPLOYEES(EMP_ID),
SCORE NUMBER,
COMMENTS VARCHAR2(500)
);
-- 5. Rating Criteria Table
CREATE TABLE RATING_CRITERIA (
CRITERIA_ID NUMBER PRIMARY KEY,
CRITERIA_NAME VARCHAR2(100),
WEIGHT NUMBER
);
-- 6. Leave Balances Table
CREATE TABLE LEAVE_BALANCES (
EMP_ID NUMBER PRIMARY KEY REFERENCES EMPLOYEES(EMP_ID),
ANNUAL_LEAVE NUMBER,
SICK_LEAVE NUMBER
);
---
2. PL/SQL Procedures
1. Add New Employee – Adds a new employee to the system.
2. Add Performance Metric – Adds performance metrics to the system.
3. Generate Review for Employee – Generates a performance review for an
employee based on metrics and peer reviews.
-- 1. Procedure to Add New Employee
CREATE OR REPLACE PROCEDURE ADD_NEW_EMPLOYEE (
p_name IN VARCHAR2,
p_department IN VARCHAR2,
p_position IN VARCHAR2
) AS
BEGIN
INSERT INTO EMPLOYEES (NAME, DEPARTMENT, POSITION, JOIN_DATE)
VALUES (p_name, p_department, p_position, SYSDATE);
END;
/
-- 2. Procedure to Add Performance Metric for Employee
CREATE OR REPLACE PROCEDURE ADD_PERFORMANCE_METRIC (
p_emp_id IN NUMBER,
p_kpi_name IN VARCHAR2,
p_kpi_weight IN NUMBER
) AS
BEGIN
INSERT INTO PERFORMANCE_METRICS (EMP_ID, KPI_NAME, KPI_WEIGHT)
VALUES (p_emp_id, p_kpi_name, p_kpi_weight);
END;
/
-- 3. Procedure to Generate Performance Review for Employee
CREATE OR REPLACE PROCEDURE GENERATE_PERFORMANCE_REVIEW (
p_emp_id IN NUMBER
) AS
v_score NUMBER;
v_review_text VARCHAR2(500);
BEGIN
-- Calculate the performance score (could use a complex function)
v_score := CALCULATE_PERFORMANCE_SCORE(p_emp_id);
-- Generate review text based on score
IF v_score >= 90 THEN
v_review_text := 'Excellent performance';
ELSIF v_score >= 75 THEN
v_review_text := 'Good performance, but room for improvement';
ELSIF v_score >= 50 THEN
v_review_text := 'Average performance, improvement needed';
ELSE
v_review_text := 'Poor performance, action required';
END IF;
-- Insert the review into the performance reviews table
INSERT INTO PERFORMANCE_REVIEWS (EMP_ID, REVIEW_DATE, SCORE,
REVIEW_TEXT)
VALUES (p_emp_id, SYSDATE, v_score, v_review_text);
END;
/
---
3. PL/SQL Functions
1. Calculate Performance Score – Calculates the employee’s overall
performance score based on the weighted average of KPIs.
2. Get Peer Review Score – Retrieves the average score from peer reviews
for an employee.
3. Get Leave Balance – Retrieves the available leave balance for an
employee.
-- 1. Function to Calculate Performance Score for Employee
CREATE OR REPLACE FUNCTION CALCULATE_PERFORMANCE_SCORE (
p_emp_id IN NUMBER
) RETURN NUMBER IS
v_score NUMBER := 0;
v_weighted_score NUMBER;
v_total_weight NUMBER := 0;
BEGIN
FOR metric IN (SELECT KPI_WEIGHT, SCORE FROM PERFORMANCE_METRICS
WHERE EMP_ID = p_emp_id) LOOP
v_weighted_score := metric.KPI_WEIGHT * [Link];
v_score := v_score + v_weighted_score;
v_total_weight := v_total_weight + metric.KPI_WEIGHT;
END LOOP;
IF v_total_weight > 0 THEN
RETURN v_score / v_total_weight; -- Normalized score
ELSE
RETURN 0;
END IF;
END;
/
-- 2. Function to Get Peer Review Score for Employee
CREATE OR REPLACE FUNCTION GET_PEER_REVIEW_SCORE (
p_emp_id IN NUMBER
) RETURN NUMBER IS
v_avg_score NUMBER;
BEGIN
SELECT AVG(SCORE)
INTO v_avg_score
FROM PEER_REVIEWS
WHERE EMP_ID = p_emp_id;
RETURN v_avg_score;
END;
/
-- 3. Function to Get Leave Balance for Employee
CREATE OR REPLACE FUNCTION GET_LEAVE_BALANCE (
p_emp_id IN NUMBER
) RETURN NUMBER IS
v_balance NUMBER;
BEGIN
SELECT ANNUAL_LEAVE + SICK_LEAVE
INTO v_balance
FROM LEAVE_BALANCES
WHERE EMP_ID = p_emp_id;
RETURN v_balance;
END;
/
---
4. PL/SQL Trigger
A trigger that automatically updates an employee's leave balance after a
leave application is processed.
-- Trigger to Update Leave Balance After Leave Application
CREATE OR REPLACE TRIGGER TRG_UPDATE_LEAVE_BALANCE
AFTER INSERT ON LEAVE_APPLICATIONS
FOR EACH ROW
BEGIN
-- Update leave balance based on leave type (annual or sick)
IF :NEW.LEAVE_TYPE = 'Annual' THEN
UPDATE LEAVE_BALANCES
SET ANNUAL_LEAVE = ANNUAL_LEAVE - :NEW.LEAVE_DAYS
WHERE EMP_ID = :NEW.EMP_ID;
ELSIF :NEW.LEAVE_TYPE = 'Sick' THEN
UPDATE LEAVE_BALANCES
SET SICK_LEAVE = SICK_LEAVE - :NEW.LEAVE_DAYS
WHERE EMP_ID = :NEW.EMP_ID;
END IF;
END;
/
---
5. PL/SQL Package
A package to encapsulate the logic related to employee performance and
provide a clean interface to interact with it.
-- Package Specification: Employee Performance Management
CREATE OR REPLACE PACKAGE EMPLOYEE_PERFORMANCE_PKG AS
PROCEDURE ADD_EMPLOYEE(p_name IN VARCHAR2, p_department IN VARCHAR2,
p_position IN VARCHAR2);
PROCEDURE ADD_METRIC(p_emp_id IN NUMBER, p_kpi_name IN VARCHAR2,
p_kpi_weight IN NUMBER);
PROCEDURE GENERATE_REVIEW(p_emp_id IN NUMBER);
FUNCTION CALCULATE_SCORE(p_emp_id IN NUMBER) RETURN NUMBER;
FUNCTION GET_PEER_SCORE(p_emp_id IN NUMBER) RETURN NUMBER;
FUNCTION GET_LEAVE_BALANCE(p_emp_id IN NUMBER) RETURN NUMBER;
END EMPLOYEE_PERFORMANCE_PKG;
/
-- Package Body: Employee Performance Management
CREATE OR REPLACE PACKAGE BODY EMPLOYEE_PERFORMANCE_PKG AS
PROCEDURE ADD_EMPLOYEE(p_name IN VARCHAR2, p_department IN VARCHAR2,
p_position IN VARCHAR2) IS
BEGIN
INSERT INTO EMPLOYEES (NAME, DEPARTMENT, POSITION, JOIN_DATE)
VALUES (p_name, p_department, p_position, SYSDATE);
END;
PROCEDURE ADD_METRIC(p_emp_id IN NUMBER, p_kpi_name IN VARCHAR2,
p_kpi_weight IN NUMBER) IS
BEGIN
INSERT INTO PERFORMANCE_METRICS (EMP_ID, KPI_NAME, KPI_WEIGHT)
VALUES (p_emp_id, p_kpi_name, p_kpi_weight);
END;
PROCEDURE GENERATE_REVIEW(p_emp_id IN NUMBER) IS
BEGIN
INSERT INTO PERFORMANCE_REVIEWS (EMP_ID, REVIEW_DATE, SCORE,
REVIEW_TEXT)
VALUES (p_emp_id, SYSDATE, CALCULATE_SCORE(p_emp_id), 'Generated
review for performance');
END;
FUNCTION CALCULATE_SCORE(p_emp_id IN NUMBER) RETURN NUMBER IS
v_score NUMBER := 0;
BEGIN
SELECT SUM(KPI_WEIGHT * SCORE)
INTO v_score
FROM PERFORMANCE_METRICS
WHERE EMP_ID = p_emp_id;
RETURN v_score;
END;
FUNCTION GET_PEER_SCORE(p_emp_id IN NUMBER) RETURN NUMBER IS
v_peer_score NUMBER;
BEGIN
SELECT AVG(SCORE)
INTO v_peer_score
FROM PEER_REVIEWS
WHERE EMP_ID = p_emp_id;
RETURN v_peer_score;
END;
FUNCTION GET_LEAVE_BALANCE(p_emp_id IN NUMBER) RETURN NUMBER IS
v_balance NUMBER;
BEGIN
SELECT ANNUAL_LEAVE + SICK_LEAVE
INTO v_balance
FROM LEAVE_BALANCES
WHERE EMP_ID = p_emp_id;
RETURN v_balance;
END;
END EMPLOYEE_PERFORMANCE_PKG;
/
---
Summary of Features:
6 Tables to store employee, performance, peer review, rating criteria,
and leave data.
3 Procedures to manage employees, metrics, and performance reviews.
3 Functions for calculating scores, retrieving peer reviews, and checking
leave balances.
1 Trigger to automatically update leave balances when an employee takes
leave.
1 Package to encapsulate performance-related logic, providing a modular
interface.