Database Modeling and Normalization

Assignment 4: Database Modeling and Normalization

CIS 515

Running Head: Database Modeling and Normalization

1. The possible iterative steps/factors that one must consider in the process of development of an effective ERM diagram with consideration of the HR core functions and responsibilities of the client are as follows:

i) Identifying entities and attributes: The first step in creating an ERM diagram is to identify entities and attributes based on the business needs and requirements of the government agency. Core HR functions such as hiring, promotion, benefits, and training can be established as the entities. Attributes such as Emp ID, training ID, hire date, training date, etc are the attributes (details) that fill out the entities.

ii) Detecting relationships: Relationships are the logical associations between related entities. Based on the business needs and organizational reality of the agency, natural or logical relations can be established. In this step, connections are made between entities.

iii) Drawing preliminary ERD: Construction of rough ERD takes place by drawing rectangles for entities and connecting them with lines and diamonds if they share relationship. Attributes are placed in ovals which are joined to the related entity by a line.

iv) Determining cardinality: Cardinality expresses “the minimum and maximum number of entity occurrences associated with one occurrence of the related entity” (Coronel, 2017). With the help of cardinality, relationships can be built among entities. For example, cardinality expresses relationships like an employee may have many jobs and a job is held by one employee. Cardinality is an essential element of proper database design.

Running Head: Database Modeling and Normalization

v) Defining primary keys: A primary key uniquely identifies records in a table (Coronel, 2017). Key attributes like Employee ID, Training ID, Job ID, and Salary ID of the HR functions can be made primary keys. The primary key must be a unique value for every record and cannot be null.

vi) Drawing fully attributed ERD: After determining cardinality and defining primary keys, these details need to be included in the revised ERD.

2. The risks that can occur if any of the developmental or iterative steps of creating an ERM diagram are not performed are as follows:

i) Ineffective data: If entities, attributes and relationships are not properly established, then the information provided by the database can lead to ineffective decision-making. A database is only as good as the business rules that define it.

ii) Data redundancy: When iterative steps are not performed, the database will be defective. Data redundancy is a side-effect of poor database design. This situation compromises data integrity and consistency.

iii) Slow performance: When the size of the database is large due to data redundancy, performance is sacrificed at the expense of size. More storage space is required for the data.

iv) Updating data becomes much more difficult as it has to be changed in multiple tables.

Running Head: Database Modeling and Normalization

3. The five entities that would be required for the development of the data repositories are as follows:

i) Employee ii) Job iii) Training iv) Department v) Organization

4. Time-variant data contains historical value and the current value. Attributes such as salary, commission rate, contact number change over time. In other words, they are time-variant. Attributes such as date of birth and SSN remain unchanged. Time-variant data stores entity with previous value and history while maintaining entity with current attribute values. Therefore, they have multi-value. To store time-variant data, a new entity containing the new values will have to be established in a 1:M relation with the current entity.

The components required to hold time-variant data for policy enforcement and training management are data warehouse and operational database. Data warehouse is used to store and retrieve historical data whereas operational database contains current data which allows users to update data in real-time.

5. The possible 1:M solution that will hold salary history data, job history, and training history for each employee through the use of graphical tools is given below:

Running Head: Database Modeling and Normalization

Fig. 1: 1:M solution

6. The normalization steps to ensure the 3NF level of normalization using the five entities of personnel database solution are given below:

Running Head: Database Modeling and Normalization

Fig 2: First Normal Form

In 1NF, the repeating groups are eliminated and the primary key for each entity have been identified as Emp_ID, Job_ID, and Training_ID. Partial and transitive dependencies have been identified.

`

Running Head: Database Modeling and Normalization

Fig 3: Second Normal Form

Database is in 1NF. A new table Organization is created to eliminate partial dependency and its dependents in Employee table.

Running Head: Database Modeling and Normalization

Fig. 4: Third Normal Form

In conversion to 3NF, a new table for department has been created which removes the transitive dependency of on Employee table. A separate table Department showing partial dependency is created.

Running Head: Database Modeling and Normalization

7. The diagram that includes dependency diagram and multivalued dependencies with five possible entities that will be required to sustain a personnel solution is provided below:

Fig 5: Dependency and Multi-valued diagram

Running Head: Database Modeling and Normalization

References

Coronel, C., Morris, S., and Rob, P. (2015, 2017). Database Systems (12th ed.). Boston, MA: Cengage.