Database Normalization and Entity-Relationship (ER) Model
The entity-relationship diagram (ERD) is a popular data modeling approach. Data modeling is a necessary part of database design and development. It enables the analysis of business needs in order to standardize corporate terminology, enforce business standards, and assure acceptable data quality. Because data has become such an important business resource, excellent data models may contribute significantly to an organization’s future success.
Data modeling is done in the early stages of the database building process. The first two phases of this procedure are focused on the database’s information content, while the latter two are concerned with the database’s implementation on some commercial DBMS.
Data needs are stated using an ERD during the conceptual data modeling step. In general, the conceptual data modeling step is not dependent on a database management system. The logical design step converts the conceptual data model into a format that the DBMS can understand. This phase may additionally improve or update the preceding phase’s data model (ERD) to ensure effective database use. Because the majority of commercial DBMS is based on relational models, the outcome of this step is relational model design.
The technique of normalization is one method by which an ERD is improved during the logical design phase. Normalization is a fundamental principle in relational model design. It is the technique of reducing duplication in a table so that it may be modified more easily. It generally entails splitting one entity table into two or more tables and creating relationships between them. The goal is to isolate data such that changes to an attribute may be made in only one table and then propagated through the rest of the database via the established relationships.
Normalization achieves its goal by utilizing correlation among characteristics inside an entity database. Because an ERD uses association among characteristics to determine entity type structure, normalization rules may be used in the conceptual data modeling phase. Normalization can help to enhance the conceptual model and accelerate its implementation during ERD development. This study describes how normalization techniques are applied to ERD development throughout the conceptual modeling phase. There are different ERD standards. The Crow’s Foot notation is used in this work.
The ERD includes several expansions and modifications, which are collectively known as the Enhanced Entity-Relationship (EER) model. In recent years, the Unified Modeling Language (UML) has evolved as the industry standard for many areas of object modeling. Except for weak entity types, all constructions of EER schemas are included in the EER model to reflect the static aspect of object modeling. ERD, on the other hand, remains popular for conceptual data modeling.
ERD Normalization Application
Sequential data modeling is a method. In most cases, a preliminary data model is created, which is subsequently improved several times. There are several criteria (rules) for improving an ERD. The following are some of these rules:
- Convert attributes to entity types. This transformation adds an entity type as well as a 1-M (one-to-many) relationship.
- Dividing complicated qualities into smaller ones. A compound attribute comprises many types of data.
- Increase the number of entity types to two and add a relationship. This transformation might be beneficial for capturing more specific information about an object.
- Converting a weak entity type to a strong entity type This transformation is especially beneficial when dealing with associative entity types.
- You can add historical information to a data model. Historical information may be required for legal and strategic reporting purposes. This transformation is applicable to both quality and relationships.
The use of normalization concepts in the production of ERDs improves these standards. To comprehend this application, first, describe the representation of dependence notions in an ERD, then
(ii) the representation of normal forms toward the establishment of entity-type structures. The article avoids guidelines for identifying various dependencies in order to focus on their application. The first four normal forms and the Boyce-Codd normal forms are taken into account.