complete paraphrasing

515 Wee4.doc

Assignment 4: Database Modeling and Normalization

Introduction

Organizations today especially business ones store large quantities of data records as computer based files in databases. A database can be described as a structured collection of records that is stored in a computer system. Databases are critical assets of any business organization since they can be leveraged as a competitive edge through data mining and data warehousing concepts (Vesely, 2010).

My organization is in the business of offering information technology and database services including optimization and streamlining solutions for efficiency. We recently won a contract to implement a new system for a government agency; the agency needs an optimized data repository for its systems such that the management team will be able to perform various human resource duties and as well be capable of producing ad hoc reporting features for other departments. The new system should also be able to enable the agency to perform other human resources functions including policy enforcement, hiring, promotions, and training.

Steps involved in Development of an Effective Entity Relationship Model (ERM) Diagram

An Entity Relationship Model (ERM) diagram is utilized to illustrate the flow of information as it is reviewed by the end user. The data represented in an ERD diagram include business entities, their attributes, as well as the relationships among those entities. ERM diagrams are represented by fours symbols which include rectangles to represent entities, diamonds to represent relationships, and ovals to represent attributes of the entities (Lightstone, 2011). The iterative steps that will be involved in the creation of ERD diagrams for the government agency is as described below:

· The first step involves establishing the entities of the ERM diagram which includes Project, Employee, Manager, Trainee, and so on.

· The second step involves establishing the attributes of the entities; an example is EmployeeID which is an attribute of the Employee entity.

· The third and the last step involve establishing the relationships between the entities, examples of relationships between entities include a one-to-many relationship, a many-to-many relationship and so on. A good example is the one-to-many relationship between a manager and an employee; this is so since one manager can manage many employees where else an employee can only report to one manager.

Risks involved if any of the Steps of Developing an ERM Diagram are not performed

In the event that any of the above outlined steps in the development of an ERM diagram are not performed, there is a risk of inconsistency of the database system that is being designed. This implies that there would be redundancy and inconsistency in the database being developed. There is also the risk of having a database with unstructured data and getting this into the organizational information systems. The other risk involves cardinality notations in defining of relationships between entities such that there would be inconsistency in cardinality notations.

Entities Required in Development of the Data Repository

In order to develop the data repository for the government agency, the below entities are required and their attributes are major components:

· Manager- Attributes include ManagerID, ManagerName, ManagerPhone;

· Trainee- Attributes include TraineeID, TraineeName, TraineeStatus;

· Employee- Attributes include EmployeeID, EmployeeName, EmployeePhone;

· Project- Attributes include ProjectID, ProjectName, ProjectStatus;

· Customer- Attributes include CustomerID, CustomerName, CustomerAddress;

Components required to hold Time-Variant Data for Policy Enforcement and Training Management

There are two components that are required in holding time-variant data for the goals of policy enforcement and training management, these include the following:

Data Warehouse - The data warehouse will provide the information needed from a historical point of view. The structure of the data warehouse contains an element of time both implicitly and explicitly.

Operational Database - these include policy enforcement and training management databases which can be organized around functional areas and business processes. Operational databases hold current value data, the operational database may or may not contain time variant.

ERD for an Employee Showing One-to-Many Relationship

1 M 1 M

Normalization Process

Normalizing a database is the process of organizing data in a database so as to eliminate redundancy and inconsistency of the data; the process involves creation of database tables and establishing the relationships between those tables according to rules designed both to protect the data and to make sure that the database is more flexible through eliminating inconsistency dependency and redundancy of data (McGwin, 2013).The process of normalizing the personnel database involves the following entities/tables:

Diagram of the Entities/Tables to be Normalized

M

M

M M

M

M

1

Un-Normalized Form (UNF)

If a database table contains non-atomic values at each row, then it is said to be in the un-normalized form (UNF).An atomic value is a value that cannot be further decomposed, a non-atomic value on the other hand is a value that can be further decomposed or simplified. A database that is not normalized has many instances of the same data in more than one table also referred to as repeating groups; this can be bad for several reasons such as security, speed of executed queries, disk space usage, efficiency of database updates, and most importantly data integrity. A database before normalization is one that has not been broken down logically into smaller and more manageable tables (McGwin, 2013).

First Normal Form (1NF)

The First Normal Form (INF) is aimed at removing the repeating groups of attributes to form a new entity and as well add to it the original key. A database table is said to be First Normal Form (INF) if it contains no non-atomic values such that each row can provide a unique combination of values and no repeating group of attributes. The main goal of the INF is to ensure no repeating groups in the database tables and as well assign a primary key to most or all the tables in that database.

Second Normal Form (2NF)

A database is said to be in the Second Normal Form (2NF) in the event that it is already in the 1NF and that each and every attribute fully depends on the primary key of the relation. This implies that if a table has some attributes that are not dependant on the primary key of that table, then the table is not in 2NF. The major goal of the 2NF is to take data that is only partly dependent on the primary key and enter it into another table.

Third Normal Form (3NF)

A table is said to be in 3NF in if it is the 2NF and there exists no transitive dependency in that relation. This implies that, if the table contains transitive dependency, then it is not in 3NF. Therefore, the table must be splint to bring it into 3NF. The major goal of the 3NF is to remove the data in a table that is not dependent on the primary key (McGwin, 2013).

Conclusion

Database systems are without doubts one of the most critical assets of any business organization; organizations utilize database systems to manage employees data, customers data, business information, and so on. Other concepts of database systems such as data mining and data warehousing are critical to the strategic decision making as well as competitive advantage of an organization. Through the new data repository, the government agency will be able offer efficient and effective information technology and database solutions that are optimized and streamlined for efficiency. Having a database management system for the government agency will improve efficiency in data sharing, data access, and data integration. Example databases systems in the market today include Oracle, Microsoft SQL Server, Microsoft Access, and so on.

References

Vesely, E. G. (2010). The practitioner's blueprint for logical and physical database design. Englewood Cliffs, NJ: Prentice-Hall.

Lightstone, S., Teorey, T. J., & Nadeau, T. (2011). Physical database design: The database professional's guide to exploiting indexes, views, storage, and more. Amsterdam: Morgan Kaufmann/Elsevier.

McGwin, K.,& Bethesda Lutheran Home. (2013). Normalization. Watertown, WI: Bethesda Lutheran Home.

HISTORY

JobHistory

SalaryHistory

TrainingHistory

EMPLOYEE

Employee ID

EmployeeName

EmployeePosition

CustomerPhone

PROJECT

Project_ ID

Project_ Name

Project_ Status

TRAINEE

Trainee_ ID

Trainee_ Name

Trainee_ Position

Trainee_ Phone

MANAGER

Manager_ ID

Manager_ Name

Customer_ Phone

CUSTOMER

Customer_ ID

Customer_ Name

Customer_ Address

Customer_ Phone

EMPLOYEE

Employee_ ID

Employee_ Name

Employee_ Position

Customer_ Phone