Smith Consulting Database Environment
Student Name
DBM/380
Date:
Instructor name:
Smith Consulting Database Environment
Smith Consulting’s current employee management system is inefficient involving a one table database, which produces duplication of many attributes throughout the system. The first iteration of database design provided a new version consisting of various one-to-many relationships to link its various modules. There are currently four modules that consist of an employees, jobs, skills, and projects tables. Smith Consulting has many employees filling many jobs descriptions. Furthermore, each job position may encompass many skills. Finally, many employees may be working on many projects. This first iteration of database design provided a database model that is inadequate and requires a return to the database development life cycle (DDLC) to incorporate the needed many-to-many relationships among tables for efficient allocation of human resources in order to better meet company customer needs. An analysis of the database environment has surfaced the various problems and constraints with the current system. This in turn has determined the scope and boundaries for the new system version. An overall objective and supporting objectives provided the needed data specifications, which is depicted in a logical model table and database schema.
Database Environment Analysis
The current database environment involves a one table database. This current system has various issues. For example, duplicate fields throughout the system lead to data abnormalities and data corruption. The design phase of the DDLC broke down this one table database into smaller tables of various entities and attributes encompassing one-to-many relationships. This database model too is also flawed and another iteration of database design is required to incorporate many-to-many relationships among appropriate relational tables for development of a successful relational database management system (RDBMS). However, one-to-many relational tables is the primary focus throughout further reading. The one-to-many RDBMS entities translate to tables that include employees, jobs, skills, and projects. Various inconsistencies were found, such as improper table linkage, data type usage, and missing attributes. For instance, the employee’s entity or table is missing a foreign key attribute called JOB_ID. This foreign key is needed in order to link the employee table to the job table. Without it there is no relationship between the two tables and no way to show what employees fill what job positions at Smith Consulting. Furthermore, various unique attributes defined by primary and foreign keys consist of one-to-many relationships. However, many of these unique attributes should consist of a many-to-many relationships. For example, the current system displays that a particular skill may be required in many jobs and many jobs may have only one skill. This is a one-to-many relationship. However a many-to-many relationship is needed because a particular job requires many skills. In this case multiple instances on both sides of the relationship are needed (Guzel, 2013). Acknowledging these problems provide efficient solutions for database design.
Problems and Constraints
The issues rely within the database tables and consist of attribute redundancies, data types, reference integrity, and relationships. Attribute redundancies occur when a field is repeated in two or more tables. For example, the Smith Consulting’s current database system stored an employee’s first and last name in the employee table, job table, and project table. This violates the rules of data integrity by causing an employee’s name to appear in three separate locations within the database system. Plew (2003), “Confusion is always a threat when, for instance, an address for an employee in one table does not match the address of the same employee in another table” (para. 8) Problems with data types were found, such as using a number type for a unique attribute in one table and a text or alphanumeric type in another. For example, the EMP_ID field or attribute uses a number data type in the employees table and a text data type in the jobs table. The EMP_ID attribute is the primary key in the employees table and a foreign key in the jobs table. This could cause the database to fail to link records with text values. Further problems pertain to reference integrity. Constraints help to normalize a database system. For instance using keys create a relationship among parent and child tables and eliminates data redundancies. Further constraints depend upon data types. These data types are specific and must be consistent when across relational tables. For instance, the primary key EMP_ID in the employees table must be consistent with the EMP_ID foreign key in the jobs table in order to create a working relationship among these two tables in order to link the two table’s records. Data type consistency means using the same data types, specifying a variable length, and utilizing a specific data size for both the primary and foreign keys.
Database Environment Objective
The overall objective for the new database environment consists of designing a new relational version of the current DBMS that supports efficient use of human resources to better meet customer needs. The company’s human resources may consist of database analysts, database designers, programmers, network administrators, and software engineers to name a few. Smith consulting specializes in information technology (IT) with concentrations in database, network, and application development, design, implementation, and maintenance. To meet customer needs and demands Smith Consulting must implement a database system that facilitates proper allocation of its human resources. The company has many projects in progress and each project has many employees working together to complete a particular project. The new (RDBMS) must encompass three business objectives pertaining to adequate tracking of consulting staff, details each staff member’s skill set for effective placement of employees to projects, and show current employees assigned to projects to reduce over-allocation of human resources or employee burn-out. Tracking consulting staff, their skills, and the projects they are working on helps to determine the required entities, attributes, and entity relationships needed, which is further defined within the scope and its boundaries.
Scope and Boundaries
The database analysis, business objectives, and requirements determined the scope and its boundaries. The current system consists of a single entity called employees or EMPLOYEES_DATABASE_TABLE. This single table contains many duplicate attributes or fields. Plew (2003), “A database that is not normalized may include data that is contained in one or more different tables for no apparent reason” (para. 5). Notice the repetitiveness of employees name attributes under EMP_ID, JOB_ID, SKILL_ID, and PROJ_ID in the raw database depicted below. This leads to many issues involving performance, security, and data integrity issues. This means when end users access the database they must wait until the entire database is loaded, which adversely affects system performance. Furthermore, all company end-users have access to the entire database because all entities, such as employees, jobs, skills, and projects are contained within a single table. This makes it difficult for system administrators to assign different permission levels to different users, which allows any employee to read, write, or modify data. Finally, the reoccurrence of attributes within the table compromises data integrity by causing redundant information, such an employee’s name to appear many times within a record. Plew (2003), “The objective of the first normal form is to divide the base data into logical units called tables” (para. 12). The scope of the new RDBMS involves breaking the employee table down into four relational tables each with its own attributes. The boundaries involve remaining within the scope of these four tables. For example, linking these tables to existing external tables, such as a financial module is outside the design of the new RDBMS. Furthermore, boundaries consist of various attribute constraints. For example, the RDBMS will not contain duplicate attributes also called fields and unique identifiers will link parent tables containing a primary key to child tables containing a foreign key. The new RDBMS consists of employees, jobs, skills, and projects tables that are further defined in the data specifications. A graphic view of the current DBMS or raw database is depicted below in figure 1.1 and a high-level logical view of the new RDBMS is depicted in the data specifications section for comparison in figure 1.2.
Depiction of Smith Consulting’s current raw database system figure 1.1
EMPLOYEE_DATABASE_TABLE
EMP_ID JOB_ID SKILL_ID PROJ_ID
L_NAME JOB_DESC SKILL_DESC PROJ_DESC
F_NAME EMP_LNAME EMP_LNAME PROJ_SRT_DATE
INT EMP_FNAME EMP_FNAME PROJ_FIN_DATE
EMP_INT EMP_INT EMP_LNAME
EMP_FNAME
EMP_INT

EMPLOYEE_DATABASE_TABLE
EMP_ID JOB_ID SKILL_ID PROJ_ID
L_NAME JOB_DESC SKILL_DESC PROJ_DESC
F_NAME EMP_LNAME EMP_LNAME PROJ_SRT_DATE
INT EMP_FNAME EMP_FNAME PROJ_FIN_DATE
EMP_INT EMP_INT EMP_LNAME
EMP_FNAME
EMP_INT

Data Specifications
The data specifications consist of employees, jobs, skills, and projects entities. Each entity is related through a unique identifier and each entity contains various attributes.
From a database model perspective each entity becomes a table and each attribute becomes a field. Each attribute or field is categorized in columns. The raw data in each column fields makes up a row also referred as a record. For example, the entity employee’s contains attributes, such as EMP_ID, LNAME, FNAME, INT, JOB_ID, and PROJ_ID. The entity employee’s becomes a table and EMP_ID, LNAME, and so forth become fields organized in columns. The graphical depiction labeled 2.1 below shows the transformation of the employees table into the data model view. The example of the data model view contains two records each with a unique EMP_ID number or primary key. The PROJ_ID column in the database model is the foreign key that links to a primary key in the projects table. The entity relationship diagram (ERD) in figure 2.1 depicts this relation as well. The ERD show that one project may have many employees. Relational tables reduce redundancies by eliminating same data duplication and provide easy navigation of the database system. For instance, the database model displays a record with a unique EMP_ID number of 1 for a particular employee. This employee name is Jeremy D. Melix and a foreign key in the PROJ_ID column provides a unique PROJ_ID number of 4. This linked projects table would generate another record for Jeremy Melix consisting of the individual’s working project description, such as the development of a graphical user interface (GUI) for Smith Consulting RDBMS. This projects record would also display the start date and target date for project completion for this individual.

Entity Relationship Diagram (ERD) View figure 2.1 EMPLOYEES | PK EMP_ID | ATTRIBUTES
ATTRIBUTES
LNAME | FNAME | INT | FK JOB_ID | FK PROJ_ID | JOBS | PK JOBS_ID | JOBS_DESC | FK SKILL_ID | FK EMP_ID | PROJECTS | PK PROJ_ID | PROJ_DESC | PROJ_SRT_DATE | PROJ_FIN_DATE |

Database Model View of Employees Table EMP_ID | LNAME | FNAME | INT | JOB_ID | PROJ_ID | PK 1 | Jeremy | Melix | D | 2 | 4 | PK 2 | Jack | Daniels | F | 10 | 3 |
DATA FIELDS
DATA FIELDS

Jobs Table figure 2.2 JOBS | PK JOBS_ID | JOBS_DESC | FK SKILL_ID | FK EMP_ID |

Database Model View of Jobs Table JOBS_ID | JOBS_DESC | SKILL_ID | EMP_ID | PK 1 | Programmer | 6 | 4 | PK 2 | System Administrator | 10 | 5 |

Projects Table figure 2.3 PROJECTS | PK PROJ_ID | PROJ_DESC | PROJ_SRT_DATE | PROJ_FIN_DATE |

Database Model View of Projects Table PROJ_ID | PROJ_DESC | PROJ_SRT_DATE | PROJ_FIN_DATE | PK 1 | Develop a RDBMS | 5/15/2014 | 5/15/2015 | PK 2 | Develop a GUI for Smith Consulting | 7/15/2014 | 5/15/2015 |

Other data specifications pertain to data type constraints. “A constraint is an object used to place rules on data” (Stephens & Plew, 2001, p. 51). Improper use or inconsistencies with data types cause problems with record linkage. These data types may involve a number, text, date/time, currency, Boolean, such as yes or no and much more. Examples of data types are depicted in the logical model of the RDBMS depicted below.

Smith Consulting Logical Model Table of RDBMS figure 1.2 SMITH CONSULTING EMPLOYEE DATABASE MANAGEMENT SYSTEM | | | | | | | | | | TABLE NAME | ATTRIBUTE NAME | DATA TYPE | DATA SIZE | DATA RANGES | REQUIRED | PRIMARY KEY | FOREIGN KEY | FOREIGN KEY REFERENCE TABLE | EMPLOYEES | EMP_ID | AUTO_ID | 4 BYTES | 1-1000 (4 DIGITS MAX) | YES | YES | | | | LNAME | SHORT TEXT | 255 BYTES | 0-255 (VARIABLE LENGTH) | YES | | | | | FNAME | SHORT TEXT | 255 BYTES | 0-255 (VARIABLE LENGTH) | YES | | | | | INT | SHORT TEXT | 255 BYTES | 0-255 (VARIABLE LENGTH) | YES | | | | | JOB_ID | NUMBER | 3 BYTES | 1-100 (3 DIGITS MAX) | YES | | YES | JOB_ID | | PROJECT_ID | NUMBER | 2 BYTES | 1-50 (2 DIGITS MAX) | YES | | YES | PROJECT_ID | | | | | | | | | | TABLE NAME | ATTRIBUTE NAME | DATA TYPE | DATA SIZE | DATA RANGES | REQUIRED | PRIMARY KEY | FOREIGN KEY | FOREIGN KEY REFERENCE TABLE | JOBS | JOB_ID | AUTO_ID | 3 BYTES | 1-100 (3 DIGITS) | YES | YES | | | | JOB_DESC | SHORT TEXT | 255 BYTES | 0-255 (VARIABLE LENGTH) | YES | | | | | SKILL_ID | NUMBER | 4 BYTES | 1-1000 (4 DIGITS MAX) | YES | | YES | SKILL_ID | | EMP_ID | NUMBER | 4 BYTES | 1-1000 (4 DIGITS MAX) | | | YES | EMP_ID | | | | | | | | | | TABLE NAME | ATTRIBUTE NAME | DATA TYPE | DATA SIZE | DATA RANGES | REQUIRED | PRIMARY KEY | FOREIGN KEY | FOREIGN KEY REFERENCE TABLE | SKILLS | SKILL_ID | AUTO_ID | 4 BYTES | 1-1000 (4 DIGITS MAX) | YES | YES | | | | SKILL_DESC | LONG TEXT | | UNLIMITED | YES | | | | | EMP_ID | NUMBER | 4 BYTES | 1-1000 (4 DIGITS MAX) | YES | | YES | EMP_ID | | | | | | | | | | TABLE NAME | ATTRIBUTE NAME | DATA TYPE | DATA SIZE | DATA RANGES | REQUIRED | PRIMARY KEY | FOREIGN KEY | FOREIGN KEY REFERENCE TABLE | PROJECTS | PROJ_ID | AUTO_ID | 2 BYTES | 1-50 (2 DIGITS MAX) | YES | YES | | | | PROJ_DESC | LONG TEXT | | UNLIMITED | YES | | | | | PROJ_SRT_DATE | DATE/TIME | 10 BYTES | 10 CHARACTERS (FIXED LENGTH) | YES | | | | | PROJ_FIN_DATE | DATE/TIME | 10 BYTES | 10 CHARACTERS (FIXED LENGTH) | YES | | | |

Conclusion
Smith Consulting needs to roll its current DBMS over to a RDBMS. The current system is anything but relational consisting of one database table that houses all its attributes. This cause many issues pertaining to data redundancies and data abnormalities, which results in data corruption. The overall performance of the current system is slow and it opens its doors to various security threats. Creating a successful RDBMS for Smith Consulting involves a firm understanding of the company’s objectives, its scope and boundaries, accurate database analysis, efficient identification of problems and constraints, and a list of only required data specifications. The new database system must track employees, their skill sets, and projects they are working on to facilitate a system that properly allocates employee resources to better meet the company’s customer needs. The first iteration of the database design phase broke down the current system into four separate relational tables facilitating many one to many relationships. This model is depicted throughout the reading and graphical representations, such as an ERD, database model, and logical model. However, these graphical models helped the system users and database design team to see the need for more many-to-many relationships within the RDBMS. A return to the database development life cycle is required for the development and implementation of a successful RDBMS.

References
Guzel, B. (2013). SQL for Beginners: Part 3 - Database Relationships. Retrieved from http://code.tutsplus.com/articles/sql-for-beginners-part-3-database-relationships--net-8561 Plew, R. (2003). The Database Normalization Process. Retrieved from http://www.informit.com/articles/article.aspx?p=30646 Stephens, R.K., & Plew, R. (2001). Database Design. Retrieved from
http://www.dtic.co.cu/FTP/libros/database_desing.pdf.