Payroll Processing System (Oracle SQL
Project)
1. Introduction
This project converts a file-based Payroll Processing System (implemented
using Unix Shell Scripts) into a relational database system using Oracle SQL.
2. Objective
Replace file storage with database tables
Implement payroll calculations using SQL
Generate reports using queries
3. System Design
Tables Used:
1. EMPLOYEE (Master Data)
2. EMP_TRANSACTION (Monthly Data)
3. GRADE_MASTER (Salary Rules)
4. Table Creation
EMPLOYEE Table
CREATE TABLE employee (
emp_id NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
sex CHAR(1),
address VARCHAR2(100),
city VARCHAR2(50),
pincode NUMBER,
dept VARCHAR2(30),
grade VARCHAR2(10),
gpf_no NUMBER,
gis_no NUMBER,
esis_no NUMBER,
max_cl NUMBER,
max_pl NUMBER,
max_ml NUMBER,
basic_salary NUMBER(10,2),
cum_cl NUMBER DEFAULT 0,
cum_pl NUMBER DEFAULT 0,
cum_ml NUMBER DEFAULT 0,
cum_lwp NUMBER DEFAULT 0,
cum_att NUMBER DEFAULT 0
);
GRADE_MASTER Table
CREATE TABLE grade_master (
grade VARCHAR2(10) PRIMARY KEY,
da_per NUMBER,
hra_per NUMBER,
ca_per NUMBER,
cca_per NUMBER,
gpf_per NUMBER,
esis NUMBER,
gis NUMBER,
prof_tax NUMBER
);
EMP_TRANSACTION Table
CREATE TABLE emp_transaction (
emp_id NUMBER,
dept VARCHAR2(30),
cl NUMBER,
ml NUMBER,
pl NUMBER,
lwp NUMBER,
sp_pay1 NUMBER,
sp_pay2 NUMBER,
income_tax NUMBER,
rent_ded NUMBER,
lt_loan NUMBER,
st_loan NUMBER,
sp_ded1 NUMBER,
sp_ded2 NUMBER,
da NUMBER,
hra NUMBER,
ca NUMBER,
cca NUMBER,
gross_salary NUMBER,
gpf NUMBER,
esis NUMBER,
gis NUMBER,
prof_tax NUMBER,
total_ded NUMBER,
net_pay NUMBER,
CONSTRAINT fk_emp FOREIGN KEY (emp_id)
REFERENCES employee(emp_id)
);
5. Sample Data
INSERT INTO employee VALUES
(1,'AMIT','M','DELHI','DELHI',110001,'IT','SSK',123,456,789,10,10,10,5
0000,0,0,0,0,0);
INSERT INTO grade_master VALUES ('SSK',200,30,10,10,10,75,115,20);
6. Payroll Calculation
SELECT e.emp_id, [Link], e.basic_salary,
(e.basic_salary * g.da_per/100) AS da,
(e.basic_salary * g.hra_per/100) AS hra,
(e.basic_salary + e.basic_salary*g.da_per/100 +
e.basic_salary*g.hra_per/100) AS gross_salary,
(e.basic_salary + e.basic_salary*g.da_per/100 +
e.basic_salary*g.hra_per/100 - g.prof_tax) AS net_salary
FROM employee e
JOIN grade_master g ON [Link] = [Link];
7. Operations
Add Employee
INSERT INTO employee VALUES (...);
Update Employee
UPDATE employee SET name='RAHUL' WHERE emp_id=1;
Delete Employee
DELETE FROM employee WHERE emp_id=1;
Retrieve Employee
SELECT * FROM employee WHERE emp_id=1;
8. Reports
Payslip
SELECT e.emp_id, [Link], t.gross_salary, t.total_ded, t.net_pay
FROM employee e
JOIN emp_transaction t ON e.emp_id = t.emp_id;
Summary Payroll
SELECT dept, COUNT(*) total_emp,
SUM(gross_salary) total_earning,
SUM(total_ded) total_deduction,
SUM(net_pay) total_payment
FROM emp_transaction
GROUP BY dept;
Mailing Labels
SELECT name, address, city, pincode FROM employee;
9. Maintenance
Close Month
DELETE FROM emp_transaction;
Reset Leaves (Year End)
UPDATE employee
SET cum_cl=0, cum_ml=0, cum_pl=0;
10. Conclusion
This project demonstrates the transformation of a file-based payroll system
into a structured relational database system using Oracle SQL. It improves
efficiency, data integrity, and scalability.
11. Viva Questions
1. Difference between file system and DBMS?
2. What is normalization?
3. What are constraints?
4. What is a foreign key?
5. How is salary calculated?