RSS

C.S. Projects

Hello

Database Final Project Report

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. 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

Primary Key **

Field –

Foreign key #

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

Normalizations

Sample Queries

Query for showing all the 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

Query for deleting branch

DELETE FROM branch WHERE branch_id = ?

Query for inserting into department

INSERT INTO department(department_name, branch_id)

VALUES(?,?)

Query for updating when an employee want to quit