Entity relationship modeling and normalization

Entity-Relationship (ER) Models — CSCI Database Systems 1 documentation

entity relationship modeling and normalization

Logical Design: Mapping to a Relational Model Each entity in the ER Diagram becomes a relation. A properly normalized ER diagram will. Abstract. Entity relationship diagram (ERD) is one of the most widely used technique for data modeling. An ERD developed during the. It helps give the high-level view of the whole database, while normalization is more ER Data models design a whole database using entities and relationships.

entity relationship modeling and normalization

It provides a means to analyze business requirements so as to standardize organizational vocabulary, enforce business rules, and ensure adequate data quality.

Since data has become a vital corporate resource Adelman et al. During this process, the top two phases are concerned with the information content of the database, while the last two phases are concerned with the implementation of the database on some commercial DBMS.

Figure 1 During the conceptual data modeling phase, data requirements are expressed through an ERD. The conceptual data modeling phase in general is independent of a DBMS. The logical design phase transforms the conceptual data model into a format understandable to DBMS. This phase may also enhance or refine the data model ERD of the previous phase to ensure efficient utilization of the database. Since most of the commercial DBMS are based on the relational model, the end product of this phase is relational model design.

One of the ways an ERD is enhanced during the logical design phase is through the process of normalization. Normalization is one of the key tenets in relational model design.

It usually involves dividing an entity table into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of an attribute can be made in just one table and then propagated through the rest of the database via the defined relationships. Normalization utilizes association among attributes within an entity table to accomplish its objective.

Since an ERD also utilizes association among attributes as a basis to identify entity type structure, it is possible to apply normalization principles during the conceptual data modeling phase. Performing normalization during ERD development can improve the conceptual model, and speed its implementation. This paper outlines the application of normalization principles to ERD development during the conceptual modeling phase. There are various standards for ERD.

However, ERD continues to be popular for conceptual data modeling. Generally a preliminary data model is constructed which is then refined many times. There are many guidelines rules for refining an ERD. Some of these rules are as follows Mannino, Transform attributes into entity types. This transformation involves the addition of an entity type and a 1-M one-to-many relationship. Split compound attributes into smaller attributes. A compound attribute contains multiple kinds of data.

Expand entity types into two entity types and a relationship.

Normalizing with Entity Relationship Diagramming | fabula-fantasia.info

This transformation can be useful to record a finer level of detail about an entity. Transform a weak entity type into a strong entity type. This transformation is most useful for associative entity types. Add historical details to a data model. Historical details may be necessary for legal as well as strategic reporting requirements. This transformation can be applied to attributes and relationships. Add generalization hierarchies by transforming entity types into generalization hierarchy.

Application of normalization principles toward ERD development enhances these guidelines.

entity relationship modeling and normalization

To understand this application i representation of dependency concepts in an ERD is outlined, followed by ii representation of normal forms toward the development of entity type structure. Guidelines for identification of various dependencies is avoided in the paper so as to focus more on their application.

Only the first four normal forms and the Boyce-Codd normal forms are considered. Each entity instance represents a set of values taken by the non entity identifier attributes for each primary key entity identifier value. So, in a way an entity instance structure also reflects an application of the functional dependency concept.

entity relationship modeling and normalization

Name, Street, City, Zip. Figure 2 Each entity instance will now represent the functional dependency among the entity attributes as shown in Figure 3.

Figure 3 During requirement analysis, some entity types may be identified through functional dependencies, while others may be determined through database relationships.

entity relationship modeling and normalization

Another important consideration is to distinguish when one attribute alone is the entity identifier versus a composite entity identifier. A composite entity identifier is an entity identifier with more than one attribute.

Normalizing with Entity Relationship Diagramming

A functional dependency in which the determinant contains more than one attribute usually represents a many-to-many relationship, which is more addressed through higher normal forms.

The notion of having a composite entity identifier is not very common, and often times is a matter of expediency, rather than good entity structure or design. Transitive dependency in an entity type occurs if non entity identifier attributes have dependency among themselves.

entity relationship modeling and normalization

For example, consider the modified Student entity type as shown in Figure 4. Figure 4 In this entity type, suppose there is a functional dependency BuildingName?

Fee dependency implies that the value assigned to the Fee attribute is fixed for distinct BuildingName attribute values. In other words, the Fee attribute values are not specific to the SID value of a student, but rather the BuildingName value. The entity instance of transitive dependency is shown in Figure 5. Figure 5 Multi-valued dependency equivalency in ERD occurs when attributes within an entity instance have more than one value. This is a situation when some attributes within an entity instance have maximum cardinality of N more than 1.

When an attribute has multiple values in an entity instance, it can be setup either as a composite key identifier of the entity type, or split into a weak entity type.

For example, consider the following entity type Student Details as shown in Figure 6. The composition of entity identifier is due to the fact that a student has multiple MajorMinor values along with being involved in multiple activities. The multi-valued dependency affects the key structure. This means that a SID value is associated with multiple values of MajorMinor and Activity attributes, and together they determine other attributes.

The entity instance of Student Details entity type is shown Figure 7. Each normal form rule and its application is outlined.

First Normal Form 1NF The first normal form rule is that there should be no nesting or repeating groups in a table. Now an entity type that contains only one value for an attribute in an entity instance ensures the application of first normal form for the entity type. A student can have multiple phones. Phones and emails are unrelated as in a multi-valued dependencyhence belong in different weak entities.

Unlike type inheritance in programming languages, inheritance in entities is about the attributes the relation has. The key and all other attributes are inherited from the parent. Student and Staff both inherit the attributes and key of Person.

A staff entity is a person, attributes related to a person are stored under person. Attributes related to a staff are stored under staff. Ask the following questions? Can a person be student and staff at the same time? Are the subentities covering? Do the set of all students and staff make up all the people we will store in the database? Are the people who are not student or staff? The key defines the meaning of the entity.

If for a class, the key is the code of the class i. CSCIthen we are talking about a catalog class. If for a class, the key is the CRN, then we are talking about a specific section of a class that is offered at a specific semester and year.

If no natural key exists, then it is reasonable to make up an arbitrary one. All relationships must be marked with referential integrity and cardinality constraints which are crucial in converting them to the relational model.

The model must satisfy all the requirements of the given problem, no more no less. Faitfulness The model must not contain unnecessary information: Simpler models are better: An entity that contains only key attributes may be converted to an attribute. Attributes that do not depend solely on the key of the entity must be put in a separate entity. Unless there are other relationships specifically connecting to the email entity. Suppose the following functional dependency holds: We can create a new entity with attributes: