E - R Diagram

The E – R Diagram consists of the following major components:

• Rectangles: represent entity sets 
• Ellipses: represent attributes
• Diamonds: represent relationship sets
• Lines: links attributes to entity sets and vice versa
• Double ellipses: represents multivalued attributes
• Dashed ellipses: represents derived attributes
• Double lines: represents total participation of an entity in a relationship set
• Double rectangles: represents weak entity sets

Figure – 1: An illustration of E – R diagram corresponding to customers and loans

Figure – 2 depicts two entity sets, customer and loans, related through relationship set borrower. The attributes associated with customer are customer_id, customer_name, customer_munber, customer_address, customer_pincode, customer_city.  The attributes associated with loan are loan_number, loan_amount. The customer_id and loan_number are primary keys.

The relationship set borrower may have one-to-one, one-to-many, many-to-one, many-to-many. To represent this, either directed line (à) or an undirected line (—) is used.

A directed line from the relationship set borrower to the entity set loan specifies that borrower is either one-to-one or many-to-one relationship set, from customer to loan; borrower cannot be a many-to-many or one-to-many relationship set from customer to loan.

An undirected line from the relationship set borrower to the entity set loan specifies that borrower is either many-to-many or one-to-many relationship set from customer to loan.

(a)

(b)

(c)

Figure – 2: Relationships (a) one to many (b) many to one (c) one to one.

Figure – 3 depicts the representation of composite attributes in the E – R notation. The composite attribute name, with component attributes first_name, middle_name, last_name, is replaced from simple attribute customer_name of customer. Also, the composite attribute address, with component attributes street, city, state, and pincode, is replaced from simple attribute customer_address. The attribute street is composite attribute, with component attributes street_number, street_name, and house_number. The figure also shows the phone_number attribute as multivalued attribute, represented by double ellipse, and also a derived attribute age, represented by dashed ellipse.

Figure – 3: An illustration of E – R diagram with composite, multivalued and derived attributes.

The roles in E – R diagrams are indicated by labeling the lines that connects the diamonds with rectangles. Figure – 4 depicts the role of indicators manager and workers between the employee set and the works-for relationship set.

The roles in E – R diagrams are indicated by labeling the lines that connects the diamonds with rectangles. Figure – 4 depicts the role of indicators manager and workers between the employee set and the works-for relationship set.

Figure – 4: An illustration of E – R diagram with a ternary relationship

In E – R Diagram, double lines are used to indicate total participation of an entity set in a relationship set, i.e. each entity set must be in at least one relationship in that relationship set. The example is shown in Figure – 5, where a double line from loan to borrower indicates that each loan must have at least one customer associated.

Figure – 5: An illustration of total participation of an entity set in a relationship set.

In E – R Diagram, more complex constraints can be indicated by number of times each entity participates in relationships in a relationship set. An edge between an entity set and a relationship set can have a maximum and minimum cardinality, represented in the form l..h, where l is the minimum cardinality and h is the maximum cardinality. A minimum value of 1 indicates total participation of the entity set in a relationship set. A maximum value of 1 indicates that the entity participates in at most one relationship set. A maximum value * indicates no limit. A label 1..* is equivalent to a double line.

Consider an example shown in Figure – 6, where the edge between the loan and borrower has 1..1 cardinality, i.e. both the maximum and minimum cardinality are 1, which means each loan must have exactly one associated customer. The 0..* on the edge from customer to borrower indicates that the customer may have 0 or more loans. Thus, one to many borrower relationship exists from customer to loan, and there is a total participation of loan in borrower.

Figure – 6: An illustration of cardinality limits on relationship sets.