Database Analysis and Design for Employees

¶ … Database

In the United States, manual method had been the primary method to store employee data for several decades before the advent of information technology. However, analysis of manual method reveals that it is both time consuming and prone to errors. Moreover, manual method of keeping employees' data make organizations to incur enormous amount of financial resources. Manual method also requires large office space to store the paper. However, a rapid development of Information Technology has made increasing number of organizations globally to escalate from using manual data storage to database system because computer has advantages of maintaining consistence and accurate data. (Patrick, & Felix, 2013). Moreover, computer has the advantages of processing data rapidly with lowest costs and time. Different benefits database offers to organizations and employees. First, the database system is cost and time effective. Organizations can store and retrieve employee data at a fast rate. Moreover, the database is free from errors that are associated to manual data processing.

b. Design of the database (Tables, Queries, Forms, Reports)

The project suggests using MySQL to design and manage database. The Entity Relation Diagram will be used to design the database. The Entity relation diagram is a graphical relationships showing the relationships between entities. An entity refers to an object where data are stored. All the entities to store data are as follows:

employees

departments

dept_manager

dept_emp titles salaries.

The identification of the entities requires creating table for each entity to track employee data, and other data of departments, dept_manager, dept_emp, titles, and salaries.

TABLE employees emp_no

birth_date

first_name

last_name

gender hire_date

PRIMARY KEY (emp_no)

TABLE departments dept_no

dept_name

PRIMARY KEY (dept_no),

TABLE dept_

emp_no

dept_no

from_date

to_date

PRIMARY KEY (dept_no)

TABLE dept_emp

emp_no

dept_no

from_date

to_date

PRIMARY KEY (,dept_no)

TABLE titles emp_no

title from_date

to_date

PRIMARY KEY (emp_no)

TABLE salaries emp_no

salary from_date

to_date

PRIMARY KEY (emp_no)

Forms

The form will be integrated in the database, which allow the users to enter, modify and view records. Forms enhances data entry and assists in presenting data in an organized manner.

Queries

Queries assist in searching and compiling data from tables.

Reports

Reports present data in print, and the MySQL assists in creating report from table.

Prototype

The fig 1 reveals prototype of the ER (entity-relationship) model. The prototype also reveals the integration of primary key for each table.

Fig 1: Prototype of the Database

Coding

The following step converts tables into SQL statements, and the SQL statements are revealed below:

CREATE TABLE employees (

emp_ID INT NOT NULL,

date_of_birth DATE NOT NULL,

first_name VARCHAR (25) NOT NULL,

last name VARCHAR (25) NOT NULL,

gender ENUM ('F','M') NOT NULL,

employment_date DATE NOT NULL,

PRIMARY KEY (emp_ID)

CREATE TABLE departments (

dept_id CHAR (8) NOT NULL,

dept_name VARCHAR (50) NOT NULL,

PRIMARY KEY (dept_no),

UNIQUE KEY (dept_name)

CREATE TABLE dept_manager (

emp_id INT NOT NULL,

dept_id CHAR (8) NOT NULL,

from_date DATE NOT NULL,

to_date DATE NOT NULL,

FOREIGN KEY (emp_id)

CASCADE,

FOREIGN KEY (dept_no)

CASCADE,

PRIMARY KEY (dept_id)

CREATE TABLE dept_emp (

emp_id INT NOT NULL,

dept_id CHAR (8) NOT NULL,

from_date DATE NOT NULL,

to_date DATE NOT NULL,

FOREIGN KEY (emp_id)

CASCADE,

FOREIGN KEY (dept_id)

CASCADE,

PRIMARY KEY (dept_id)

CREATE TABLE titles (

emp_id INT NOT NULL,

title VARCHAR (40) NOT NULL,

from_date DATE NOT NULL,

to_date DATE,

FOREIGN KEY (emp_id)

CASCADE,

PRIMARY KEY (emp_id)

CREATE TABLE salaries (

emp_id INT NOT NULL,

salary INT NOT NULL,

from_date DATE NOT NULL,

to_date DATE NOT NULL,

FOREIGN KEY (emp_id)

CASCADE,

PRIMARY KEY (emp_id)

Testing

The paper will use series of queries and commands to test the database. For example, we can use SELECT command to test the database revealing as follows:

mysql> SELECT gender, emp_no, last_name, first_name,

-> FROM employees

-> LIMIT 12;

The output from the database will be as follows:

emp_no -- first_name -- last_name -- gender --

-- 10001 -- Facello -- Georgi -- M --

-- 10002 -- Bezalel -- Parto -- F --

-- 10003 -- Simmel -- Bamford -- M --

-- 10004 -- Chirstian -- Kyoichi -- M --

-- 10005 -- Koblick -- Maliniak -- M --

-- 10006 -- Tzvetan -- Preusig -- F --

-- 10007 -- Anneke -- Piveteau -- F --

-- 10008 -- Saniya -- Kalloufi -- M --

-- 10009 -- Peac -- Sumant -- F --

-- 10010 -- Zielinski -- Duangkaew -- F --

-- 10011 -- Bezalel -- Facello -- M --

-- 10012 -- Simmel -- Georgi -- F --

Additional test is as follows:

mysql> SELECT gender, emp_no, last_name, first_name,

-> FROM employees

ORDER BY first_name ASC

-> LIMIT 10;

The output from the database will be as follows:

-- emp_id -- first_name -- last_name -- gender --

-- 258641 -- Abelkader -- Anneke -- M --

-- 258005 -- Ademar -- Anneke -- F --

-- 455773 -- Aenilian -- Anneke -- M --

-- 436560 -- Alaga -- Anneke -- F --

-- 266651 -- Alepsander -- Anneke -- F --

-- 487598 -- Alexius -- Anneke -- M --

-- 216963 -- Alois -- Anneke -- M --

-- 15427 -- Aluyio -- Anneke -- M --

-- 100860 -- Amebile -- Anneke -- F --

-- 107070 -- Anastis -- Anneke -- M --

Additional test is as follows:

mysql> SELECT first_name, COUNT (emp_id) AS num_emp

-> FROM employees

-> GROUP BY first_name

-> ORDER BY nume_emp DESC

-> LIMIT 10;

The output from the database will be as follows:

-- first_name -- num_emp --

-- Gelosh -- 226 --

-- Baba -- 223 --

-- Sudbeck -- 223 --

-- Coorg -- 222 --

-- Adachi -- 222 --

-- Farris -- 221 --

-- Masada -- 220 --

-- Osgood -- 218 --

-- Neiman -- 218 --

-- Mandell -- 218 --

User Acceptance

The database will be designed to meet the project objective. After the test, the developer will ensure that the project satisfies the following conditions:

Meet the Business Requirements

Application Code fully developed

Integration Testing, Unit Testing and System Testing are completed.

No errors are identified

Testing completed with no defects

All defects fixed and tested

UAT Environment are ready

Sign off communication or mail from System Testing Team revealing that the system is available and ready for UAT execution. The Appendix 1 provides the project plan.

Training

There will a month training for the employee of the IT department, and the training will compose of the strategy to implement the database. The user manual and training CD will be available to guide employees on the use of database after training.

Implementation Plan

The project will be implemented within four months between June 2016 and September 2016. A costs of $100,000 will be available for the project plan, which include the costs of project design, implementation and training costs.

Implementation Plan

Tasks

Responsibilities

Start Date

End Date

1

Specify requirements

06-juin-14

22-juin-14

1.1

User interviews Checklist

U, IT

06-juin-16

13-juin-16

6

1.2

Consolidate requirements

U, IT

13-juin-16

15-juin-16

3

1.3

Drafting of the project requirement

U

15-juin-16

15-juin-16

1

2

Search for vendors

19-juin-14

26-juin-16

2.1

possible software, vendors

IT, U

19-juin-16

20-juin-16

2

2.2

Identification of Vendors requirements