There are steps that are involved in developing an Entity Relationship Model. The first step is to identify the entities that will be used in the model. The entities are the objects that will be defined and have the relationships in the data model developed. The next step would be to define the relationships that exist between the entities that have been defined. The relationships will define the direction in which the relationship between the entities will take. They will guide in the creation of the database. The third step would be entering the attributes. Attributes are the characteristics that describe entities. After this, the domains of the attributes will be undertaken. The domain is the value range that an attribute can have in a database. The next step would be to identify unique identifiers. These are the primary and secondary keys of a given entity. The primary keys are the columns which are unique in a given entity. No two records can have the same value in a primary key. The last step would be to undertake quality checks. This is the process where the whole database is checked to certify the quality issues. The formalization process is the common task that is undertaken in this step.
There are steps that can be considered to be iterative. This is the step of relationship creation. From time to time, there will be an analysis of the relationships between the entities that have been listed in the database. The analyst will check to determine if there is a relationship that exists with other entities in the database. The creation of one more relationship between the entities is a common step. The last iterative factor to be considered is the exploration of cardinality between the attributes and the entities.
There are risks that will be encountered if there are some developmental or iterative steps which have not been followed. If the relationship between the entities is not explored, there is a high likelihood that some entities will not be clearly defined. It will be hard to normalize the database if this is the case. Another developmental step that would pose a great risk is the lack of definition of unique identifiers. Lack of this will pose the risk of relationship definition in the database. The entities will fail to have proper relationships between them.
The top entities that will be required for the government agency data repository would include employee_details, payment, administrator, skills, and department. The employee_details entity will hold the biodata of the employees. These are the common details that the employees will have in the company. In any human resource management system, there will be a need to have employee biodata which include date of birth, their names, addresses, and contact details among others. The payment entity will hold the rate of the payment, the type of employment, the hours that the staff member has worked, and the amount that they are supposed to be paid. The administrator entity will show the system administrators and their role. They have administrative rights to the system. They will access the backend of the system and make changes as they see fit. The skills entity is also important because it holds the data regarding the skills that the employees should be holding. The last entity that will be required for the government agency data repository is the department. This is important as it holds the department data of the employees. The department will help determine the rates of pay, and the number of staff members that a department has a given time.
There are components that will be used to hold time-variant data that will be useful during policy development and training. One of the components is skills of the employees. This is an important component because from time to time, there will be different skills required for the staff in the company. There might be changes in skills of different human resource functions. Another component would be the payment component. This is an important component because the employees will be paid according to the policies that have been set. There might be changes that might take place while they at work. These changes will affect the payments. There might be promotions and demotions which might change the pay that the staff members get. Training will also change the rate of pay of the employees. The training will change the pay if one decides to increase their level of education. Another component that might hold time-variant data is the department that one is working. The data will vary because the departments will hold varying rates of pay. This will change regarding the policies that affect this department. Different departments may also have their own policies according to the mandate given by the government. Although this is a rare thing for most government agencies, this might be changed if the department does most of the things with little considerations from the government.
Normalization process
1NF
2NF
The definition of this is that all non-key attributes should be able to depend on the key. One of the tables is Employee_details.
This makes it in 2NF.
3NF
In 3NF, there should be no entry which has transitional dependencies. This is illustrated with the fact that in the first table, if Wamoshe Peter Kamau is deleted from the database, it would mean that ICT department would be lost. There would be no indication that there is an ICT department in the government agency. This requires that the department comes out as an entity. One requirement of 3NF is that there should be no deletion anomalies.
BCF and 4NF
The five entities have been shown in the diagram which includes Administrator, Skills, Department, Payments, and Employee_details.
References
Embley, D. W., & Thalheim, B. (Eds.). (2011). Handbook of conceptual modeling: theory, practice, and research challenges. Springer.
Halpin, T., & Morgan, T. (2010). Information modeling and relational databases. Morgan Kaufmann.
Teorey, T. J., Lightstone, S. S., Nadeau, T., & Jagadish, H. V. (2011).Database modeling and design: Logical design. Elsevier.
