This paper outlines the iterative process for developing Entity Relationship Model (ERM) diagrams within governmental and business contexts. It details initial stakeholder meetings, departmental interviews, and user validation as core design phases. The paper emphasizes three principles of data communication through time-variant audits, data protection protocols, and redundancy checks performed at varying intervals. It addresses risks of non-iterative approaches, including configuration drift and security vulnerabilities, then provides a practical case study demonstrating database normalization to Third Normal Form (3NF) using employee, job history, and training data repositories.
Developing an effective Entity Relationship Model (ERM) diagram requires iterative steps that identify business rules and requirements within a given set of operations and procedures. An ERM diagram provides an initial visual flow that identifies individual attributes of each entity and the necessary primary keys. Once the ERM diagram has undergone review, it can be revised to further define the final steps. Properly following these iterative steps allows a designer to develop a more effective ERM for any business.
A critical part of this process involves initial meetings with key people within the organizational structure, such as a governmental agency. This includes meeting with decision makers and stakeholders to allow for discovery of necessary operational activities. Next, interviewing management from different operational departments aids significantly in the identification of key entities, possible attributes, and relationships between entities and attributes. Once the initial ERM has been designed, conducting meetings with users from the necessary departments further defines specific data elements that include entities, relationships, and constraints.
The iterative approach to ERM development is supported by three significant principles of data communication, each requiring different audit frequencies and control mechanisms. Employing a time-variant data audit involves checks and balances related to accounting and any necessary attributes requiring a data type consisting of date or time. Protection of data can be accomplished by performing weekly full backups, given the sensitive nature of organizational data. Periodic redundancy audits will include checks against current operational procedures that validate attributes currently associated with each entity, helping to ensure the database continues to produce meaningful data for the organization as a whole.
As noted by scholars in the field, "A very sophisticated control is required to avoid redundancy and loss of integrity, which an audit can help mitigate" (Doon & Rivero, 2002). The redundancy audit would also include an internal integrity check that ensures there are no factors negatively affecting performance, performed and analyzed on a quarterly basis. Additionally, an SQL View audit should also be performed quarterly to determine whether users from different departments continue to have access only to their specific information. This represents a security best practice because it ensures personnel changes within the organization are correctly reflected in the user presentation view. Finally, a semi-annual database user security audit should be performed to maintain database access integrity.
Several risks exist with a non-iterative approach to database design. Any design implementation today must be able to evolve as users and systems evolve. For example, hardware and software used today has increasingly become more modular, portable, and capable of multiple communication methods over the past decade. The multitude of users in the world has created a scenario where the choice between many devices and many operating systems has introduced a multitude of vulnerabilities.
As Coronel, Morris, and Rob (2012) observe, "Unintentional events seem more often easily resolved, but intentional events are of a more severe nature and normally indicate that the company data are at serious risk." Sidestepping an iterative, security-based model increases the risk that an ever-growing number of vulnerabilities can be exploited over the system's lifecycle. Another risk involves growing configuration drift, meaning every end user begins using an outdated view of their data. If redundancy checks and operational drift audits that check procedures, processes, and business rules are not implemented, integrity can be compromised as changes and new software are introduced to the system over time.
Time-variant data audits provide the necessary checks and balances against data and time-oriented attributes to ensure compliance for data processed in both internal and external databases. This mitigates potential risks associated with hosting incorrect or inconsistent data. Each risk can be addressed through system administration based on the iterative steps described for the development of the ERM diagram.
To illustrate the practical application of ERM principles, consider a personnel database system with the following five key data repositories ranked by priority:
"Applying 1NF, 2NF, and 3NF normalization rules"
"Time-variant data requirements for employee and training systems"
You’re 62% through this paper. Sign up to read the remaining 2 sections.
Sign Up Now — Instant Access Already a member? Log inAlways verify citation format against your institution’s current style guide requirements.