DATABASE MODELING AND NORMALIZATION 1 Database Modeling and Normalization Name: Prof : CIS 515: Strategic Planning for Database Systems Date: Explain the process of developing an effective Entity Relationship Model (ERM) for a personnel database, including key components such as entities, relationships, attributes, and constraints. Discuss the iterative steps required for its development, the potential risks associated with not following these steps, and the importance of time - variant data for policy enforcement and training management. Additionally, explain the normalization process to ensure the database is in 3NF, and illustrate the five entities necessary for sustaining the personnel database solution. Your response should be approximately 1200 - 1500 words. DATABASE MODELING AND NORMALIZATION 2 S teps in the development of an effective Entity Relationship Model (ERM) Diagram and the possible iterative steps / factors An entity relationship model or ERM is a type of gr aphical diagram where relationships between entities in a database are being shown. Below here is an example diagram of ERM: As ERM is an iterative process it needs replication of processes and procedures. There are three main components in ERM and they are: Entity – In ERM database designing entity can be many things. It can be somebody, can be some place, an object or event, or even can be a concept where data is collected or saved. As an example, if a person accepts some data information for a business, then entities would include c onsum ers and also their address, and orders . In database entity is labelled with a singular noun and characterized by a rectangle . Relationship – Relationship symbolizes link between entities and/or entity types. Simply we can say it is the interaction among the entities. For an example , a c onsu mer places an order, then the word "places" highlights the relationship between th e instance of a customer and the order that the person place d . Usually relationships are being represented by a diamond shape or just by a simple line connecting the entities. DATABASE MODELING AND NORMALIZATION 3 Attribute (s) – An attribute is a p roperty of an entity that is of interest to the business . For an example, “a person” entity can be described by name, d ate of birth, SSN, address, gender etc. These description works as attributes for the entity and each attributes have a specific value. • A ttributes are classified by following dimensions: • Simple/atomic vs. composite • Single - valued vs. multi - valued (or set - valued) • Stored vs. derived Constraints (Cardinality) – Constraint of ERM are mainly a representation of a relation presenting number of instances of one entity related to number of instances of another entity. Simply it is the bond between the entities in terms of numbers. F or example, a sales person may have zero customers or may have one or more cu stomers. Another example could be one order should contain at least one product. Among different type of c ardinality notation , c row's foot notation is a common one. In this notation, a single bar indicates one, a double bar indicates one and only one, a circle indicates zero, and a crow's foot indicates many. Draw Rough ERD – In this step entities are written in rectangles and relationships are on diamond or line segments and connect the entities .