MySQL database design for employee data management

¶ … 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)

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

IT, U

21-juin-16

22-juin-16

2

2.3

vendors and issue RFI

IT

25-juin-16

26-juin-16

2

3

Review RFI responses

29-juin-16

06-juil-16

3.1

RFI responses

IT, U

29-juin-16

03-juil-16

3

3.2

Compare against the requirements

IT, U

02-juil-164

04-juil-16

3

3.3

Short list 3 or 4 vendors

IT, U

05-juil-14

05-juil-16

1

3.4

Inform the vendors

IT

06-juil-16

06-juil-16

1

4

Systems demonstrations

27-juin-14

17-juil-14

4.1

Short listed vendors

IT

06-juil-16

06-juil-16

1

4.2

Creating the demo questions / scripts

U, IT

27-juin-16

28-juin-14

2

4.3

Attend demonstrations

U, IT

11-juil-16

13-juil-16

3

5

Refining new system design

09-juil-16

17-juil-16

5.1

Additional designing the thoughts

U, IT

09-juil-16

10-juil-16

2

5.2

systems demonstrated

U, IT

12-juil-16

13-juil-16

2

5.3

Updating system design

U, IT

17-juil-16

17-juil-16

1

6

RFP (Request for proposal)

16-juil-16

31-juil-16

6.1

Prepare RFP

IT, U

16-juil-16

19-juil-16

4

6.2

Issue RP to the shortlisted vendors

IT

23-juil-16

23-juil-16

1

6.3

Analyzing, score responses

IT, U

26-juil-16

31-juil-16

4

7

Additional investigations

1-Aug-16

6-Aug-16

7.1

Software & due diligence checks

IT, U

1-Aug-16

6-Aug-16

4

7.2

Hardware, network, AND database

IT

3-Aug-16

6-Aug-16

2

8

Resolve outstanding issues

7-Aug-16

24-Aug-16

8.1

Queries from the RFP responses

IT, U

7-Aug-16

10-Aug-16

4

8.2

Software demonstrations

U, IT

9-Aug-16

14-Aug-16

4

8.3

Prototyping

IT, U

15-Aug-16

21-Aug-16

5

8.4

Test system

IT, U

22-Aug-16

24-Aug-16

3

9

Visit reference sites

15-Aug-16

28-Aug-16

9.1

Identify who visit

U, IT

15-Aug-16

15-Aug-16

1

9.2

Determine agenda

U, IT

16-Aug-14

16-Aug-16

1

10

The decision

29-Aug-16

07-sept-16

10.1

Review findings & consider options

PM, M, IT, U

29-Aug-16

31-Aug-16

3

10.2

Prepare the proposal paper

PM, PS

31-Aug-16

31-Aug-16

1

10.3

Board & steering group meeting

B, SG, PS

06-sept-16

06-sept-16

1

10.4

Decision made

B, SG, PS

06-sept-16

06-sept-16

1

10.5

Contract SLA negotiations

P, PM

07-sept-16

07-sept-16

1

11

Project management

04-juin-16

07-sept-16

11.1

Project plan

PM

04-juin-16

04-juin-16

1

11.2

Project kick-off meeting

PM, ALL

05-juin-16

05-juin-16

1

11.3

Weekly progress meetings

PM, U, IT

04-juin-16

07-sept-16

14

h. Project close out

There will be a project close out after the project is completed and accepted.

Reference

Patrick, O. & Felix, O. (2013). Assessing Manual and Online Course Registration in Nigeria Tertiary Institutions. World Journal of Education.3(6):8-14.