Table of Content

• Problem Description
• Contributions
• Database Design
• Entity Relationship Diagram
• Relations
• Normalization
• Sample Queries
• User Interface
• Links

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

p {font-family: Courier; color: #D3D3D3}

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

Report:

https://docs.google.com/document/d/1-S_TlQn2kCfX9hhcduOBIC4WOlSPnsxTR1pSBHe86H8/edit?usp=sharing

Github:

https://github.com/vynsss/Final_Project_Database

Video:

https://youtu.be/qI07j6FnNEI

My contributions 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. And making the database. Moreover, I provide 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 functions and user interfaces. Also, help with the initial design for the user interface and help to code it.

Project Details:

Project name:

Employee Management System

Group member:

• Brenda Spears
• Sreeya Reddy
• Vicky Vanessa

Problem Statement:

This database is to manage employees in a company. A company have different kind of departments, branches, etc. So we are trying to make a database that will make it easier for them to keep track of their employees.
Using database is much more efficient than traditional file system. A big company might have thousands of employees, and to keep in a traditional file system will make it hard for employers to keep track of their employees. In addition, big companies might have a branch company that they also need to keep track of. Having a database system to help manage their employees will be much more efficient.
We plan to use MySQL for the database and java as the base language for the user interface.

Target User:

• Employers