Quick Contact

    Conversion of ER-Model into Relational Model

    An Entity-Relationship Model can be converted to relational model, in which each entity set and each relationship set is converted to a relational model.

    Example shows the conversion of ER-Diagram into a set of relational model.
    Conversion of ER-Diagram into Table

    A separate relation represents each entity set and each relationship set. The attributes of the entities in the entity set become the attributes of the association, which represents the entity set. The entity identifier becomes the key to the relation, and a tuple represents each entity in the association. Similarly, the attributes of the relationships in each relationship set become the attributes of the relation, which represents the relationship set. The relationship identifiers become the keys to the association, and a tuple represents each relationship in that relation.

    The entity-relationship model is converted into the following three relations, as shown in figure (b).

    PERSONS (PER-ID, DESIGN, LAST-NAME, DOB)
    From entity set PERSONS
    PROJECTS (PROJ-ID, START-DATE, END-DATE)
    From entity set PROJECTS
    WORKS-ON (PROJ-ID, PER-ID, HRS-SPENT, STATUS)
    From relationship set WORKS-ON
    Conversion of ER-Model into SQL Constructs

    The Entity-Relationship Model is transformed into SQL constructs using transformation rules. The following three types of tables are produced during the transformation of ER-Model into SQL constructs.

    1. An entity table with the same information content as the original entity: This transformation rule always occurs with the following relationships:
      • Entities with recursive relationships that are many-to-many (M: N).
      • Entities with binary relationships that are many-to-many (M: N), one-to-many (1: N) on the ‘1’ (parent) side, and one-to-one (1:1) on one side.
      • Ternary or Higher-degree relationships.
    2. An entity table with the embedded foreign key of the parent entity: This is one of the most common ways CASE tools handle relationships. It prompts the user-to-define a foreign key in the ‘child’ table that matches a primary key in the ‘parent’ table. This transformation rule always occurs with the following relationships:
      • Each recursive entity relationship is one-to-one (1:1) or one-to-many (1: N).
      • Binary relationships that are one-to-many (1: N) for the entity on the ‘N’ (child) side, and one-to-one (1:1) relationships for one of the entities.
    3. A relationship table with the foreign keys of all the entities in the relationship: This is the other most common way CASE tools handle relationships in the entity-relationship model. In this case, many-to-many (M: N) relationship can only be defined in terms of a table that contains foreign keys that match the primary keys of the two associated entities. This new table may also contain attributes of the original relationship. This transformation rule always occurs with the following relationships:
      • Recursive and Many-to-Many (M: N)
      • Binary and Many-to-Many (M: N)
      • Ternary or Higher-degree.

    In the above transformations, the following rules apply to handle SQL null values:

    • Nulls are allowed in an entity table for foreign keys of associated (referenced) optional entities.
    • Nulls are not permitted in an entity table for foreign keys of associated (referenced) mandatory entities.
    • Nulls are not allowed for any key in a relationship table because only complete row entities are meaningful in the entries.

    The other sub-heading shows standard SQL statements needed to define each type of the ER-Model construct.

    Conversion of Recursive Relationships into SQL Constructs

    The Entity-Relationship model can be converted into SQL constructs. The figure shows the conversion of recursive relationships into SQL constructs.

    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of Binary Relationships into SQL Constructs

    Figure illustrates the conversion of binary relationships into SQL constructs.

    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of Ternary Relationships into SQL Constructs

    Figure illustrates the conversion of ternary relationships into SQL constructs.

    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Conversion of ER-Diagram into Table
    Enroll Yourself in Live Training: DBMS Training

    Copyright 1999- Ducat Creative, All rights reserved.