Entity - Relationship model

The E – R data model employs three notations: 

1. Entity sets, 
2. Relationship sets, and 
3. Attributes.

1. Entity sets

An entity is a thing or object in real world that is different from other objects. For example, each employee in a company is an entity.

An entity has a set of properties, and unique value for some properties. For example, the employee may have employee_id property whose value uniquely identifies the employee. Thus, 0201 value for employee_id would uniquely identify the employee in a company.

An entity set is a set of entities of the same type that may share same properties, or attributes. For example, the set of all persons who are employees at a company can be defined as the entity set employee.

Entity sets do not need to be disjoint. For example, a person may be an employee entity, a customer entity, both or neither.

An entity is represented by set of attributes. Attributes are properties possessed by each member of the entity set. Each entity may have its own unique value for each attribute. For example, attributes of employee entity are employee_id, employee_name, employee_address, employee_city, employee_pincode, employee_salary, employee_designation, employee_phone_no.

Each entity has a value for each attributes. The employee_id is used to uniquely identify the employees. For each attribute, there is set of values, called the domain, or value set. A database therefore includes a set of entity, each of which contain any number of entities with same type.

An attribute, in the E – R model, can be categorized by following attribute types:

Simple and composite attributes:

In our examples till now, the attributes have been simple, hence cannot be further divided into subpart.

Composite attributes can be further divided into subparts. For example, the attribute name can be further divided as first_name, middle_name and last_name.

Single – valued and multivalued attributes:

The attributes in our examples, have single value for particular entity. Such attributes are said to be single valued.

For example, an employee entity set with attribute phone_no. An employee may have zero, one, or several phone numbers. This type of attribute is said to be multivalued.

Derived attribute:

The attribute values are derived from the values of other attributes or entities. For example, the employee entity set has an attribute age to indicate the employee’s age. If date_of_birth attribute is present in employee entity, then age calculation can be done. Hence, age is a derived attribute. The value of derived attribute is not stored, but evaluated whenever required.

An attribute takes null value when no value is there for an entity. For example, if the employee does not have middle_name, the null value can be assigned.

1.     Relationship sets

Association among entities is called relationship. For example, we can define a relationship that associated employee shreya with account number A – 5009.

A relationship set is a set of relationships of same type. It is mathematical relation on n ≥ 2 (non-distinct) entity set. If E₁, E₂, ….., E_n are entity sets, then a relationship set R is a subset of

{(e₁, e₂, …., e_n) | e₁ ∈ E₁, e₂ ∈ E₂, …., e_n ∈ E_n}

Where, (e1, e2, …., en) is a relationship.

Consider (Figure – 1), the two entity sets customer and loan. Figure – 2, depicts the association between customer and loan using relationship set borrower.  

Figure – 1(a): Customer table

Figure – 1(b): Loan table

Figure – 2: An illustration of relationship set borrower

A relationship instance in an E – R schema represents an association between the entities that is being modeled. For example, the customer entity Rakesh, who has customer_id 230, and loan entity L – 17 participate in a relationship instance of borrower. This relationship instance represents, the person called Rakesh who holds customer_id 230 has taken the L – 17 numbered loan.