{"id":297,"date":"2020-06-16T10:04:35","date_gmt":"2020-06-16T10:04:35","guid":{"rendered":"http:\/\/blog.ijse.lk\/web\/blog\/?p=297"},"modified":"2020-06-16T10:04:37","modified_gmt":"2020-06-16T10:04:37","slug":"entity-relationship-diagram-erd-database-management-systems-dbms","status":"publish","type":"post","link":"https:\/\/blog.ijse.lk\/web\/blog\/2020\/06\/16\/entity-relationship-diagram-erd-database-management-systems-dbms\/","title":{"rendered":"Entity Relationship Diagram (ERD) \u2013 Database Management Systems (DBMS)"},"content":{"rendered":"\n

Entity Relationship Diagram is a type of diagram that illustrates the blueprint of the database using Entities in a business such as a person, an object, a place, an event, or a concept and their relationships between each other. In a sense, the ER diagram explains the logical structure of databases. It was proposed by Peter Chen in 1971 to use a uniform convention for Relational Database Modelling.<\/p>\n\n\n\n

Uses of Entity Relationship Diagram (ERD)<\/strong><\/h2>\n\n\n\n
  1. You can illustrate and understand the fields required to create the database of the desired system.<\/li>
  2. Understand and illustrate the data structure of the system that you are making.<\/li>
  3. Understand and illustrate the connection between the tables of the database and the fields required which will help you a great deal when applying integrity constraints. <\/li>
  4. It can give a clear understanding when designing the database and with a clear illustration of the whole blueprint, the designing process becomes a lot easier. <\/li><\/ol>\n\n\n\n

    Components of Entity Relationship Diagram (ERD)<\/strong><\/h3>\n\n\n\n
    1. Entity<\/li>
    2. Attribute <\/li>
    3. Relationship<\/li><\/ol>\n\n\n\n

      1. Entity <\/strong><\/h4>\n\n\n\n

      Usually, a business is made from\npeople and data. And these data are usually a noun. For example, in an\neducational institution, there may be students, and staff, courses, fees, and\nmuch more. As such, it is fair to say the collection of these things or nouns\nmakes the business or the enterprise whole. Being that as it may, among the\ncollection of things, if we single out one type of noun, for example, students\ncan be considered as an entity. In other words, either a physical thing or a\nfact or rather a concept in a business or an enterprise can be defined as an\nentity.<\/p>\n\n\n\n

      An entity can be a person, an\nobject, a place, an event, or a concept in a business or an enterprise. Usually,\nwhen naming an entity, we use the singular form of the noun. <\/p>\n\n\n\n

      Examples:<\/p>\n\n\n\n

      1. Person \u2013 Student, Employee, Lecturer, Doctor<\/li>
      2. Object \u2013 warehouse, merchandise, vehicles,\nproduct<\/li>
      3. Place – Store, Branch office, Building<\/li>
      4. Event \u2013 Registration, Selling, Deposit, Withdraw<\/li>
      5. Concept \u2013 Course, Payments<\/li><\/ol>\n\n\n\n

        In ER diagrams, entities are shown as rectangles.<\/p>\n\n\n\n

        \"entity\"<\/figure>\n\n\n\n

        2. <\/strong>Attributes<\/strong><\/h4>\n\n\n\n

        Attributes describe the properties of an entity or rather in a sense an entity can be considered a table and attributes fields of the table. There are few types of attributes that you need to know and in an ER diagram, attributes are represented in oval shapes.<\/p>\n\n\n\n

        \"attribute\"<\/figure>\n\n\n\n

        Other types of attributes,<\/p>\n\n\n\n

        a. Multi-valued attributes <\/h5>\n\n\n\n

        A multi-valued attribute is when\nan attribute has multiple values for an instance. Let\u2019s consider a student, if\nyou are asked to store his\/her skills in a table, you might have to add a few\nskills under the same entry.<\/p>\n\n\n\n

        For example, Tom can sing and he is good at martial arts as well. In this case, the attribute \u201cSkill\u201d has multiple values within it.  <\/p>\n\n\n\n

        \"multi-valued-attribute\"<\/figure>\n\n\n\n
        b. Key attribute <\/h5>\n\n\n\n

        The key attribute is the unique attribute that helps to identify the designated entry in the entity table from the other records. For example, from giving a unique id for each student in a student set, you can identify any student separately from their id. <\/p>\n\n\n\n

        \"key-attribute\"<\/figure>\n\n\n\n
        c. Composite attribute <\/h5>\n\n\n\n

        An attribute that is made from a collection of other attributes is called a composite attribute. For Example, consider a situation where you are storing the addresses of the students.<\/p>\n\n\n\n

        \"composite-attribute\"<\/figure>\n\n\n\n
        d. Derived attribute <\/h5>\n\n\n\n

        A derived attribute is an attribute that is calculated, generated, or derived with the help of another attribute. For example, a student\u2019s age can be derived from the birthday of the student. <\/p>\n\n\n\n

        \"derived-attribute\"<\/figure>\n\n\n\n

        3. Relationships <\/strong><\/h4>\n\n\n\n

        A relationship describes the\nassociation between two or more entities. There are four main types of\nrelationships between entities.<\/p>\n\n\n\n

        1. Unary Relationships.<\/li>
        2. Binary Relationships.<\/li>
        3. Turnery Relationships.<\/li>
        4. N-ary relationships.<\/li><\/ol>\n\n\n\n

          Relationships are represented in ER Diagrams using a Diamond shape. The relationship is written using a verb or a verb phrase inside the diamond shape.<\/p>\n\n\n\n

          \"relationship\"<\/figure>\n\n\n\n

          Before diving into more details, let\u2019s consider these relationships one by one.<\/p>\n\n\n\n

          a. Unary Relationship<\/strong><\/h5>\n\n\n\n

          The unary relationship is also\ncalled as a recursive relationship. This relationship occurs between the\ninstances of the same entity set. In this particular case, since it is the same\nentity set, the primary and foreign keys stay the same. However, it represents\ntwo separate roles within the same entity. <\/p>\n\n\n\n

          For example, consider a situation where a supervisor of a company has to supervise his subordinates. In this scenario, both the subordinates and the supervisor are employees under one company. Additionally, in this case, the supervisor is entitled to supervise one or more subordinates. <\/p>\n\n\n\n

          \"unary-example\"<\/figure>\n\n\n\n

          Relational Schema: <\/em><\/p>\n\n\n\n

          Employee(Employye_ID<\/u>, Employee_name, Employee_Address, Employee_Salary, Supervisor_ID)<\/em><\/p>\n\n\n\n

          If we were to include these data in a table, it would be as follows. <\/p>\n\n\n\n

          \"unary-table\"<\/figure>\n\n\n\n
          b. Binary Relationships<\/strong>      <\/h5>\n\n\n\n

          Binary relationships describe the\nrelationship between two entities. Binary relationships can be represented in\nthree types.<\/p>\n\n\n\n

          1. One-to-one Relationships.<\/li>
          2. One-to-many Relationships.<\/li>
          3. Many-to-Many Relationships.<\/li><\/ol>\n\n\n\n

            As such, we will be discussing these three types of relationships in detail.<\/p>\n\n\n\n

            i. One-to-one Relationships.<\/h6>\n\n\n\n

            In\nOne-to-one relationships, one entity from entity set Entity-1 is associated\nwith one entity from Entity-2. In the simplest terms, when the tables are\ndrawn, a single record from Entity-1 has a single corresponding record in the\nEntity-2.<\/p>\n\n\n\n

            (As such, please note that you can draw both tables as one table if it is\nrequired. Since there is only one corresponding record per subsequent record,\nit will not alter the data structure by compacting both entities into one)<\/p>\n\n\n\n

            Please note that when choosing where to place your foreign key, in one to one relationships, place the foreign key on the child entity or the dependent entity. In this case, the student is the parent entity while the birth certificate is the child entity.<\/p>\n\n\n\n

            For example, let’s consider a situation where the birth certificate details of a student have to be recorded.<\/p>\n\n\n\n

            \"one-to-one-er\"<\/figure>\n\n\n\n
            \"one-to-one-relational-schema\"<\/figure>\n\n\n\n

            Table: <\/p>\n\n\n\n

            \"one-to-one-example-table\"<\/figure>\n\n\n\n
            ii. One-to-many<\/strong><\/h6>\n\n\n\n

            One-to-many\ndescribes a relationship where one entry from Entity-1 has more than one\ncorresponding entries in the Entity-2.<\/p>\n\n\n\n

            Please\nnote that when you are placing the foreign key, you have to place in in the\nentity where many entries are available.<\/p>\n\n\n\n

            For example, let\u2019s consider a situation where a customer places an order in a grocery shop. In this situation, one customer can make multiple orders as he\/she may deem fit. <\/p>\n\n\n\n

            \"one-to-many-example-er\"<\/figure>\n\n\n\n
            \"one-to-many-example-relational-schema\"<\/figure>\n\n\n\n

            Table : <\/p>\n\n\n\n

            \"one-to-many-example-table\"<\/figure>\n\n\n\n
            iii. Many-to-many relationship<\/h6>\n\n\n\n

            In many to many relationships, we\ndescribe a relationship where many entries from Entity-1 has more than one\ncorresponding entries in the Entity-2 to a ratio of M: N.<\/p>\n\n\n\n

            Please note that in this\ncase, when we are placing the foreign key, we cannot directly use the exact\nrelational model. We have to use an additional table to maintain data\nconsistency. <\/p>\n\n\n\n

            For example, consider an instance wherein a grocery store one order has multiple items, and since the item batch is stored in their warehouses the same item can be ordered more than once in different quantities.<\/p>\n\n\n\n

            \"many-to-many\"<\/figure>\n\n\n\n
            \"many-to-many-relational-schema\"<\/figure>\n\n\n\n
            \"many-to-many-table\"<\/figure>\n\n\n\n

            However, in this particular case, what we have already drawn\nis rather impractical since the given scenario indicates that there might be\nmultiple quantities of the same item could be included in a single order.<\/p>\n\n\n\n

            That being the case, we can utilize the ORDER_ITEM table and\nadd a column \u201cquantity_bought\u201d so that the contained data would be more\nmeaningful. Additionally, we can calculate the price per item bundle of the\nsame kind.<\/p>\n\n\n\n

            When we add additional attributes to the relationship, it becomes an Associate entity<\/strong>.<\/p>\n\n\n\n

            \"many-to-many-associate-entity\"<\/figure>\n\n\n\n
            \"many-to-many-associate-entity-relational-schema\"<\/figure>\n\n\n\n

            Table :<\/p>\n\n\n\n

            \"many-to-many-associate-entity-table\"<\/figure>\n\n\n\n

            Note: Unary and Turnery relationships will be discussed in separate articles in the future.<\/em><\/p>\n\n\n\n

            Since these relationships between entities are clearly to be represented, we must represent one-to-one, many to many relationships. Thus, we mark both Cardinality and Ordinality. We will be learning crowfoot representation for cardinality. <\/p>\n\n\n\n

            1. Cardinality<\/h5>\n\n\n\n

            Cardinality\nis the maximum number of records that Entity-2 can have corresponding to a\nsingle record of the Entity-1. We always mark the cardinality of the\ncorresponding entities related to the selected entity relatively. <\/p>\n\n\n\n

            Let’s\nconsider the earlier examples that we discussed under Binary Relationships.<\/p>\n\n\n\n

            Example 1: one to one.<\/p>\n\n\n\n

            In this case, the maximum number of birth certificates a student can have is one.<\/p>\n\n\n\n

            \"one-to-one-cardinality\"<\/figure>\n\n\n\n

            The maximum number of students that a birth certificate can have is also one. <\/p>\n\n\n\n

            \"one-to-one-cardinality\"<\/figure>\n\n\n\n

            Example 2: One-to-many<\/p>\n\n\n\n

            A customer can place as much as orders he\/she wants.<\/p>\n\n\n\n

            <\/p>\n\n\n\n

            \"\"<\/figure>\n\n\n\n

            The maximum number of customers that a single grocery order can have is one.<\/p>\n\n\n\n

            \"\"<\/figure>\n\n\n\n

            Example 3: Many-to-many<\/p>\n\n\n\n

            In this case, an order can include many items.<\/p>\n\n\n\n

            \"\"<\/figure>\n\n\n\n

            As the items recorded consists of a large quantity of the same type of item, a type of item can be ordered more than once.<\/p>\n\n\n\n

            2. Ordinality.<\/h5>\n\n\n\n

            Ordinality is the minimum number of corresponding entries of\nEntity-2 that exists in the Entity-1. This is also marked in the same relative\nmethod we used while marking cardinality. We mark ordinality in front of\ncardinality.<\/p>\n\n\n\n

            Example 1: One-to-many<\/p>\n\n\n\n

            In this case, to add a customer, he must buy something from the store. So to be a customer having at least one order is mandatory.<\/p>\n\n\n\n

            \"\"<\/figure>\n\n\n\n

            It is the same for orders too. An order cannot exist without a customer. So, for an order, a customer is mandatory. <\/p>\n\n\n\n

            \"\"<\/figure>\n\n\n\n

            Example 2: many to many.<\/p>\n\n\n\n

            In this case, an order should have at least one item, so an entry in order entity must have at least one item.<\/p>\n\n\n\n

            \"\"<\/figure>\n\n\n\n

            However, it is not mandatory for an item to have an order, because there may be items that might not sell. So, the not mandatory indicator is placed in front of the cardinality as a small circle. <\/p>\n\n\n\n

            \"\"<\/figure>\n\n\n\n

            After cardinality, you must have a basic understanding of \u201cTotal Participation<\/strong>\u201d and \u201cPartial participation<\/strong>\u201d<\/p>\n\n\n\n

            Partial participation.<\/h5>\n\n\n\n

            In partial\nparticipation, the entities in the entity set may or may not participate in the\nrelationship set, which is why it is called optional participation. <\/p>\n\n\n\n

            It is\nrepresented using a single line between the entity and relationship sets.<\/p>\n\n\n\n

            Let’s consider an example where a student enrolls in a course program.<\/p>\n\n\n\n

            \"\"<\/figure>\n\n\n\n

            In this case, students don’t need to enroll in all the course programs, which in the sense, there might be course programs that students did not sign up for.<\/p>\n\n\n\n

            Total participation. <\/h5>\n\n\n\n

            In total\nparticipation, it states that the entities in the entity set must participate\nin the relationship set, which is why it can also be said mandatory\nparticipation.<\/strong><\/p>\n\n\n\n

            It is\nrepresented using double lines between the entity and the relationship.<\/p>\n\n\n\n

            Let’s consider the above example again. <\/p>\n\n\n\n

            \"\"<\/figure>\n\n\n\n

            In this case, the student must\nenroll in at least one course<\/p>\n\n\n\n

            In a sense, Ordinality <\/strong>can\nindicate whether the participation is partial or total.<\/strong><\/p>\n\n\n\n

            Note: The next article will be tips for drawing ER Diagrams.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"

            Entity Relationship Diagram is a type of diagram that illustrates the blueprint of the database using Entities in a business such as a person, an object, a place, an event, or a concept and their relationships between each other. In a sense, the ER diagram explains the logical structure of databases. It was proposed by […]<\/p>\n","protected":false},"author":5,"featured_media":298,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[16],"tags":[49,51,21,17,54,48,43,42,55,52,53,46,45,44,50,47,5,56,30,25],"_links":{"self":[{"href":"https:\/\/blog.ijse.lk\/web\/blog\/wp-json\/wp\/v2\/posts\/297"}],"collection":[{"href":"https:\/\/blog.ijse.lk\/web\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ijse.lk\/web\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ijse.lk\/web\/blog\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ijse.lk\/web\/blog\/wp-json\/wp\/v2\/comments?post=297"}],"version-history":[{"count":26,"href":"https:\/\/blog.ijse.lk\/web\/blog\/wp-json\/wp\/v2\/posts\/297\/revisions"}],"predecessor-version":[{"id":360,"href":"https:\/\/blog.ijse.lk\/web\/blog\/wp-json\/wp\/v2\/posts\/297\/revisions\/360"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ijse.lk\/web\/blog\/wp-json\/wp\/v2\/media\/298"}],"wp:attachment":[{"href":"https:\/\/blog.ijse.lk\/web\/blog\/wp-json\/wp\/v2\/media?parent=297"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ijse.lk\/web\/blog\/wp-json\/wp\/v2\/categories?post=297"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ijse.lk\/web\/blog\/wp-json\/wp\/v2\/tags?post=297"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}