Database Modelling and Normalization

Database Modelling and Normalization

Determine the steps in the development of an effective entity relationship model (ERM) diagram and determine the possible iterative steps and factors that one must consider in this process, with consideration of the HR core functions and responsibilities of the client.

To best develop an effective Entity Relationship Diagram (ERD), an organization needs to use a model that involves iterative steps that identify business rules and requirements to explain its procedures and operations. An ERD is initially developed to make it easy for those involved to identify the individual attributes of each entity and their suitable primary keys. After these entities are completely identified, a review of the diagram can be performed to further define the model. By iteratively reviewing such a diagram, the developer will help a designer develop an effective model for any business.

To develop an effective ERD, the first step would be to meet up with stakeholders in the entire process, including those who directly use the system, the executives, and others directly involved. This way, it will be possible to know what they expect from the system and how to design it in a manner that will work perfectly (Date, 2012). There should be meetings and interviews with the system’s critical players to deduce what they perceive the entities, possible attributes, and their relations to be. These meetings should also be repeated once the ERDs are defined and designed. After doing this, the next step would be identifying the entities and the relationships that they share.

A rough ERD is the next logical thing to fill in the cardinality to clarify how the different entities and attributes interact with each other. The primary keys are then defined, and an ERD that respects these keys on the entities is drawn. The process of designing the best ERD should also be properly iterated so that these relationships and the entities and attributes involved will serve the system appropriately. The attributes should then be identified and mapped so that a fully attributed ERD will be drawn appropriately. These processes should be repeated at least once to ensure that the right thing has been done. However, aspects such as collecting information and identifying the different aspects should be done as many times as possible so that accuracy and relevance will be guaranteed.

Risks from Ignoring Iteration Analyze the risks that can occur if any of the developmental or iterative steps of creating an ERM diagram are not performed.

Various risks are involved when these iterative steps are skipped from the process. For one, the system might not work as desired. If, for instance, the wrong answers are provided by the system users because of reasons such as misunderstanding the exercise, then it means that the designers will put unnecessary functions in the system (Date, 2012). The requirements-gathering process is usually confusing in the first instance and might only be fully understood by the parties involved after being revisited several times. It is also possible for the attributes and their relationships to not be properly identified.

When there are many unnecessary attributes or a few of them, their relationships will be either too little or redundant to some extent. Such a system will also not be able to meet the requirements that are simply outlined because it will not correct the right information that is needed for the job. Another risk is that the system will fail to define the data repository. Because of the inability to produce the right entities, attributes, and relationships, it will be impossible to have a repository that works as it should since not all relevant information will be collected.

Select and rank at least five entities that would be required for the development of the data repositories.

Five entities that would be necessary in developing the HR data depositories include Employee info, role, benefits, training, and promotions, in that order. The employee info as an entity would provide information about the individual, including their name, employee ID, address, and gender, amongst others related to him. The role table will define what he does in the organization and how important that role is amongst others. The benefits entity would show what the individual receives as part of delivering his role, including healthcare insurance, loans, and bonuses, amongst others. The training table will also show what the individual is a professional in and what they are eligible to study. The promotion entity could show if the individual has been promoted, what position, and what it will take to get the next promotion.

Specify the components that would be required to hold time-variant data for policy enforcement and training management.

When it comes to storing and retrieving information during different times, it would be important for the system to have data warehouses. For information to be considered time-variant, it means that it changes over time, and the historically stored information will change over time. Data warehouses would, thus, best handle such data (Narang, 2011.). Data warehouses are central repositories that are core to such systems and help manage both historical and current data that is up to date. Considering it is the government, information stored there will be millions of people and will be stored even up to 15 years. Having such a warehouse will ensure that time-variant data will have to be collected and stored. This is information that will be important to decision-makers for a long time.

Diagram a possible 1:M solution that will hold salary history data, job history, and training history for each employee through the use of graphical tools. Note: The graphically depicted solution is not included in the required page length.

Plan each step of the normalization process to ensure the 3NF level of normalization using the selected five entities of the personnel database solution.

The system will have five different entities, which will all require review and normalization into the 3NF. For the entities to be normalized into the third normal form, they must first be normalized to the first and second normal forms. To normalize the entities into 1NF, it has to see whether it has any repeating group that has to be eliminated; it has to have its primary key identified and ensure that all of its attributes depend on the primary key. Converting it to the 2NF will only happen if the entities can all be identified using a single attribute’s primary key. For instance, if the employee table has entities such as employee name, ID, and contacts, it is in its 2NF because they depend on the entity’s primary key.

The 2NF helps such entities to relate with others in the same system effectively. By the time it gets to the 3NF, most anomalies are usually eliminated (Narang, 2011.). Normalizing the entity into the 3rd normal form requires the removal of transitive dependencies. A relation is normalized to its third form if its non-key attributes are fully dependent on the primary key. These attribute dependencies should also be identified and their dependencies properly defined so their content and function can be understood. All dependent attributes from transitive relationships should then be removed from the entity. However, the entities should come from 2NF without any transitive dependencies.

Diagram at least five possible entities that will be required to sustain a personnel solution. (Note: The graphically depicted solution is not included in the required page length.)

The five possible entities that are very likely to be involved in the system include the Employee info, role, benefits, training, and promotions. The entities will involve the following information;

References

Date, C. J. (2012). Database design and relational theory: Normals forms and all that jazz. Sebastopol, Calif: O’Reilly Media.

Narang, R. (2011.). Database management systems. New Delhi: PHI Learning.

ORDER A PLAGIARISM-FREE PAPER HERE

We’ll write everything from scratch

---

Database Modelling and Normalization

Imagine that you work for a consulting firm that offers information technology and database services. Part of its core services is to optimize and offer streamlined solutions for efficiency. In this scenario, your firm has been awarded a contract to implement a new personnel system for a government agency.

This government agency has requested an optimized data repository for its system, which will enable the management staff to perform essential human resources (HR) duties along with the capability to produce ad hoc reporting features for various departments. They look forward to holding data that will allow them to perform HR core functions such as hiring, promotions, policy enforcement, benefits management, and training.

Instructions

Using this scenario, write a 3–4 page paper in which you:

1. Determine the steps in the development of an effective entity relationship model (ERM) diagram and determine the possible iterative steps and factors that one must consider in this process, with consideration of the HR core functions and responsibilities of the client.
2. Analyze the risks that can occur if any of the developmental or iterative steps of creating an ERM diagram are not performed.
3. Select and rank at least five entities that would be required for the development of the data repositories.
4. Specify the components that would be required to hold time-variant data for policy enforcement and training management.
5. Diagram a possible 1:M solution that will hold salary history data, job history, and training history for each employee through the use of graphical tools. Note: The graphically depicted solution is not included in the required page length.
6. Plan each step of the normalization process to ensure the 3NF level of normalization using the selected five entities of the personnel database solution.
   1. Document each step of the process and justify your assumptions in the process.
7. Diagram at least five possible entities that will be required to sustain a personnel solution. (Note: The graphically depicted solution is not included in the required page length.)

1. • The diagram should include the following:
     1. Dependency diagrams.
     2. Multivalued dependencies.

Note: The cover page and the reference page are not included in the required assignment page length. Include charts or diagrams created in any chart or drawing tool with which you are familiar. The completed diagrams or charts must be imported into the Word document before the paper is submitted.

Your assignment must follow these formatting requirements:

• Be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides; citations and references must follow APA or school-specific format. Check with your professor for any additional instructions.
• Include a cover page containing the title of the assignment, the student’s name, the professor’s name, the course title, and the date. The cover page and the reference page are not included in the required assignment page length.
• Include charts or diagrams created in a drawing tool with which you are familiar. The completed diagrams / charts must be imported into the Word document before the paper is submitted.

This course requires the use of Strayer Writing Standards. For assistance and information, please refer to the Strayer Writing Standards link in the left-hand menu of your course. Check with your professor for any additional instructions.

The specific course learning outcome associated with this assignment is:

• Create a database using normalization techniques and logical/physical design best practices.