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