Database Modeling and Normalization

Assignment 4: Database Modeling and Normalization

CIS 515

Database Modeling and Normalization

Introduction:

In this paper, I will introduce the iterative steps in the development of an effective Entity Relationship Model (ERM). Then I will analyze what happen if any of the developmental steps of creating an ERM Diagram is not performed. After that, I will prefer the entities that would be required for the development of the data repositories with the explaining of what is the time-variant data and what is the determined components for this data type. Also, I will draw the diagram of the possible solution for the many variant data by the Visio application. In the last I will explain the steps of the normalization process to ensure the 3NF level of normalization with the drawing of the all these steps, by the Visio App.

The iterative steps in the development of an effective Entity Relationship Model (ERM) Diagram, factors, consideration of the HR core functions, and the responsibilities of the client:

The Entity Relationship Model (ERD) is the model to represent the relationships among the entities. It has many components: Entity: That is the person, location, or anything by it can store the data. Attribute: It is the characteristic of the entity. Relationship: It is the association among the entities. Cardinality: It is the Constraint in the ERM, among the entities. The development steps of the ERM are the following:

1. Identify the Entities: We suppose that we have in this Government agency that is the Hiring Agency, and this has in its database the entities: Management, Employee, Department, and Customer. Then we can implement these entities by the ERM as the following: Entity by the Rectangle figure.

Fig(1): The Entities

2. Determine the relationships: That are identified in the first by the matrix:

Here There are many relationships among the entities:

1. MANAGEMENT manages EMPLOYEE.

2. MANAGEMENT responsible of DEPARTMENT.

3. MANAGEMENT hires CUSTOMER.

4. EMPLOYEE works by MANAGEMENT.

5. EMPLOYEE part of DEPARTMENT.

6. EMPLOYEE manipulates with CUSTOMER.

After determining the relationships, it must draw them with all the lines between the entities and the relationships, by the ERM: (Relationship by the Diamond figure).

Fig (2): Entities with the relationships

3. Determine the relationship type (1:1), (1: M), or (M: N). Every relationship type has a specific symbol to implement it.

Fig (3): Symbols and their meanings

For Example One manager manages many employees: (1: M): We can implement this relationship as the following: One and only one manager can manage one or many employees.

Fig(4): Entities with relationships types

4. In the end, we must identify the entity attributes and then check the result. (infocom.cqu.edu.au, 2006). The end figure as the following:

Fig (5): The End Figure (Entities, relationships types, and the attributes)

The risks that can occur if any of the developmental or iterative steps of creating an ERM Diagram are not performed:

Any organization has the following components: Entities, Attributes, Relationships, and Constraints. The Org. Entities are the main things by it the database model is built. The attributes are the features of the Entities, and the relationships are the describing of the set of correlation among the entities. While the constraint is the limitation of the relationship, and it is very important to ensure the database integrity. (Carlos Coronel, et al., 2013).

There is no standard modeling that is suitable for all the system types, but every model must align the organization requirements. Every organization has its specific components; therefore, it must have the specific model for its system. These system components are the collection of to implement the system model; therefore, all of it must be founded. If any one of the system components is missed, then the modeling design didn’t implement the system requirements, many risks must be faced. Also, the database model may fail. (McDonald, Caroline, 2004).

Entities that would be required for the development of the data repositories:

There are various effective entities in the government companies that is required for the development of the data repositories. The entities are implemented by the departments in the companies, the tasks that are performed in this company, and other. The very important entities are the management, department (such as planning, training, security, hiring, etc.), employee, salary, and the customer entity. (Caroline Gilbert, 2011).

The components that would be required to hold time-variant data for policy enforcement and training management:

The time-variant data is the data that has the multi-value, the previous value, and the current value. Some attributes may have the changed value, such as the email address, phone number, employee address, and other. While there are another attributes must have only one value, such as the birthday date. The time-variant data has the two value, the fist value is the current entity with all the new-value attributes, and the second value has the entity with its primary key, previous-value data, and the data history. (Inmon, Bill, 2004), Such as the following example:

The Diagram for a possible 1: M solution that will hold salary history data, job history, and training history for each employee:

The step of the normalization process to ensure the 3NF level of normalization using the selected five (5) entities of the personnel database solution:

The First Normal Form 1NF is the process of collection all the related columns in one table. Then, it must ensure that no repeated group in the tables. Also, it must implement the primary keys in their locations. Every column has unique value, and must not have the null value.

2NF: “The Second Normal Form 2NF: is the second step of the normalization, and it is built on the First Normal Form 1NF. A 1NF table is in 2NF form if and only if all of its non-prime attributes are functionally dependent on the whole of every candidate key”. (Janssen, 2014).

3NF: “Third normal form (3NF) is the third step in normalizing a database and it builds on the first and second normal forms, 1NF and 2NF. 3NF states that all column reference in referenced data that are not dependent on the primary key should be removed. Another way of putting this is that only foreign key columns should be used to reference another table, and no other columns from the parent table should exist in the referenced table”. (Janssen, 2014).

The diagram at least five (5) possible entities that will be required to sustain a personal solution. The diagram should include the Dependency diagram, and the Multi–valued dependencies:

Second Normal Form:

Third Normal Form: there are no transitive dependencies:

Conclusion:

The ERM is the diagram which used to understand the system database by the few steps, The ERM is the diagram used to understand the system database by the few steps with high efficient way, also, it is used to align the company requirements. There are many iterative steps to draw the Entity Relationship Model ERM for its components: Entities, attributes, relationships, and constraints. If any one of these steps is missed, then the modeling design will not implement the system requirements, the many risks must to be faced, also, the database model may be fail. There are various effective entities in the government companies, that is required for the development of the data repositories such as: management, department, employee, salary, and the customer entity. There is another type of data has multi-value because of its changed value such as the email address, phone number, etc. this data is called the time-variant data; therefore, it must store the history of the data by the date stamp, to retrieve it when we need that. In all these types of data may be there are the redundancy; therefore we need the process to eliminate this redundancy. This process is called the Normalization: Which has the various forms: 1NF, 2NF, and 3NF, and every form has its steps. In my paper, I explained all these steps with details.

References:

infocom.cqu.edu.au, (2006), Developing Entity Relationship Diagrams (ERDs) (pdf), Retrieved from http://www.javaguicodexample.com/erddatabasemodelnotes.pdf

McDonald, Caroline, (2004), Article of (ERM Success Checklist), National Underwriter / P&C (Journal), Retrieved from http://eds.a.ebscohost.com/eds/pdfviewer/pdfviewer?sid=95780d02-cfdd-45e0-8a48-63c08dfa7d87%40sessionmgr4004&vid=4&hid=4111

Carlos Coronel, et al., (2013), Article of (Data Model Basic Building Blocks), Database Systems: Design, Implementation, and Management, 10e, 10th Edition.

Caroline Gilbert, (2011), Article of (The influence of line managers and HR department on employees’ affective commitment), The International Journal of Human Resource Management, Vol. 22, No. 8, April 2011, 1618–1637, Retrieved from http://eds.b.ebscohost.com/eds/pdfviewer/pdfviewer?sid=61ce43fe-bd58-4d0d-8620-9f85f105aa7f%40sessionmgr110&vid=7&hid=103

Inmon, Bill, (2004), Article of (Measuring Time in the data Warehouse),  DM Review. Oct2004, Vol. 14 Issue 10, p64-65. 2p. (Journal), Retrieved from http://eds.b.ebscohost.com/eds/pdfviewer/pdfviewer?sid=2e12fcdc-9e57-4be4-b50f-fa0120477ccc%40sessionmgr115&vid=7&hid=103

Janssen, C. (2014), Definition – What does First Normal Form (1NF) mean?, Retrieved from http://www.tutorialspoint.com/sql/first-normal-form.htm

Janssen, C. (2014), Definition – What does Second Normal Form (2NF) mean?, Retrieved from http://www.techopedia.com/definition/21980/second-normal-form-2nf

Janssen, C. (2014), Definition – What does Third Normal Form (3NF) mean?, Retrieved from

http://www.techopedia.com/definition/22561/third-normal-form-3nf

Professional Visio 2013.