Table of Content
Problem Description
The use of paper work in managing this system can be challenging. Human errors and the lack of integrity can occur. Not to mention the large amount of time consumed. Designing this system could alleviate the inefficiency that occasionally arose when using manual method. It provides uncomplicated methods in handling the employees that still maintains its privacy. The purpose of this database is to manage and see the details of employees in a company. This database will contain employee details such as department, which branch do they work in, etc. Hence this database will make it easier to keep track of employees and makes managing employees more efficient.
Contributions
Brenda Spears
In this project, I was assigned to create and design the user interface using Java Swing. I also contributed in designing and making further changes in the ERD and the normalization table. Adding to that, I also helped in creating the database.
Kotrakona Harinatha Sreeya Reddy
My contribution to this project was to design and make the functions that will be used in the database. For example, addAdmin, checkadmin, deleteDepartment and updateEmployee are few of those functions. I also worked to optimise these functions to suit the database. I also helped contribute to making the queries that are used in the database.
Vicky Vanessa
My contribution in this project are to design the initial ERD (Entity Relationship Diagram) for the project and then help with further changes in the ERD as discussed as well as making the database. Moreover, I provided the query that are used in the functions of the codes. I also helped with and provide further improvement for some of the code used for the function and user interfaces. Also, help with the initial design of the user interface and help to code it.
Database Design
Entity Relationship Diagram
Relations
admin(username, password)
branch(branch_id, branch_address, branch_city)
- PRIMARY KEY (branch_id)
department(department_id, department_name, branch_id)
- PRIMARY KEY (department_id)
- FOREIGN KEY (branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE
job(job_id, job_name, min_salary, department_id)
- PRIMARY KEY (job_id)
- FOREIGN KEY (department_id) REFERENCES department(department_id) ON DELETE CASCADE
history(employee_id, hire_date, end_date, job_id)
- PRIMARY KEY (employee_id)
- FOREIGN KEY (job_id) REFERENCES job(job_id)
employee(employee_id, first_name, last_name, email, phone_number, job_id, salary)
- PRIMARY KEY (employee_id)
- FOREIGN KEY (employee_id) REFERENCES history(employee_id)
- FOREIGN KEY (job_id) REFERENCES job(job_id) ON DELETE CASCADE
Normalization
Sample Queries
Show all Employees
SELECT employee.employee_id, employee.first_name, employee.last_name, employee.email, employee.phone_number, job.job_name, employee.salary, department.department_name, branch.branch_address, branch.branch_city FROM employee INNER JOIN department ON department.department_id = employee.department_id INNER JOIN branch ON department.branch_id = branch.branch_id INNER JOIN job ON job.job_id = employee.job_id INNER JOIN history ON employee.employee_id = history.employee_id WHERE history.end_date is null ORDER BY employee.employee_id DESC
Delete Branch
DELETE FROM branch WHERE branch_id = ?
Insert into Department
INSERT INTO department(department_name, branch_id) VALUES(?,?)
Update employee who want to quit(stop working)
UPDATE history set end_date = CURDATE() WHERE employee_id = ?
Add Department
INSERT INTO department(department_name, branch_id) VALUES(?,?)
User Interface
