Understanding E-R Model

Entity-Relationship Model is an essential part of database design process. In this article, we focus on understanding E-R model.

Database design is a step-by-step process used to build database that accurately represents real-world requirements. It ensures a consistent and efficient database system that scales when necessary.

The step involved in database design are:

1. Requirement analysis
2. E-R Model (Entity-Relationship Model)
3. Translation to Relational Model
4. Physical Storage

We will explore each of these steps in briefly, however, our goal is understanding the E-R model completely.

Requirement Analysis

This is the starting phase where purpose of the database is understood. We try to understand what data the system must store and what operations users expect from it. In other words, “What problems we are solving and what data is required to solve it”.

1. We start by identifying users who will use the system. This is all the stakeholders of the database system.
2. Then we gather their needs and requirements. There are gathered by interviewing, or distributing questionnaires to users.
3. We define data requirements. Even though the users tells us their requirements, it must be translated into well structured model. Also, users speak in terms of real-world scenarios, not in database terms.
4. We must identify constraints and rules. These are real world rules , a broader set of constraints and behavior rules for entities, attributes, relationships, and business logic.
5. We document all the requirements. The document contains all entities, attributes for entities, relationships, and business logic. This information is input to the E-R Model.

In the next section, we will start to explore the E-R model.

Entity-Relationship Model

The E-R Model is a graphical conceptual model used in database design to represent:

1. Entities
2. Attributes for Entities
3. Relationships between Entities

Elements of Entity-Relationship Model

The E-R Model users simple shapes to represent data collected during the requirement analysis stage.

1. Entity – A distinct real-time object is called an Entity and it is shown as a rectangular box in E-R diagram.
2. Types of Entities
   • Person ( Student, Employee)
   • Places (Delhi, Mumbai)
   • Things (Table, Ball, Car)
   • Events (Sales, Purchase)
   • Concepts (Account, Course)
3. Attributes – Attributes are properties that describe the entities. Ellipses are used to describe attributes. For example, Name, Address.
4. Relationships – A diamond shape in E-R diagram represents relationship between two entities. This relationship is connected through links. In real database, tables are connected using primary key and foreign key.

E-R diagram is not just graphical model, it has to parts.

1. Conceptual Structure
2. Graphical Structure

The conceptual structure defines the type of data used, how data is related, and high level business logic.

On the other hand, graphical structure use symbols to represent the same idea visually.

Strong vs Weak Entity in E-R Model

In E-R model, entities are classified into two types:

1. Strong Entity
2. Weak Entity

Strong Entity

You can identify a strong entity with following features:

1. Strong entity has a primary key.
2. It is unique.
3. Does not depend on another entity for its existence.

A strong entity has enough attributes to identify it uniquely.

Weak Entity

The features of weak entity is

1. Not enough attributes to identify the entity uniquely.
2. No primary key
3. Depend on strong entity for its existence.

The weak entity has a partial key (discriminator). It is not sufficient to identity the weak entity. The partial key is then associated with the primary key of the strong entity , that can uniquely identity all the other attributes of the weak entity.

For example,

Suppose Employee relation is uniquely identified with EmployeeID (primary key)and attributes like Name, Salary. There is another relation called Dependent with its own attributes such as Name, age.

Employee Relation
------------------
Employee ID (Primary key)
Employee Name
Salary

Dependent Relation
-------------------
Dependent Name 
Age 

The following table is an instance of the relation.

The employee ID uniquely define the toubles.

Now look at the dependents of Employee A102 shown in the following table.

The dependents children of employee A102 cannot be identified uniquely, there could be other dependents with same name.

The attribute Dependent Name is a Partial Key. It is associated with the Employee ID to uniquely identify the Dependents.

The key is now a composite key (Employee ID, Dependent Name) to uniquely identify the rows of Dependent table.

(A102, Ram) is a unique key to identify the first row.

How to represent weak and strong entities ?

The strong entity is shown as rectangular box made of single line stroke, where as weak entity is depicted as a rectangular box with double line stroke.

The diagram above shows the nature of relationship between strong entity and the weak entity. The relationship is shows as double diamond shape.

The next diagram shows a simple E-R diagram involving strong and weak entity.

The diagram shows that primary key (empID) is underlined and partial key is also underlined with dashed lines. The relationship is double diamond with doubled rectangle for weak entity.

The entities are described by attributes which is the topic of next section.

Types of Attributes

There are several attributes types that depend on value, key, and nature of the attributes. The attributes are important because they describe the properties of entity. Later these attributes become the basis for table columns.

Here is a list of attributes.

1. Simple or Atomic attributes
2. Composite attributes
3. Single valued attributes
4. Multi-valued attributes
5. Derived attributes
6. Key attributes
7. Stored attributes
8. Null attributes
9. Complex attributes

In the E-R diagram, each of these attributes are shown using different shapes, lines, or diagrams.

Simple or Atomic Attributes

A simple attribute cannot be divided anymore because there is no meaningful parts. It is stored as a single indivisible unit in database.

Example: Age, Address

Notation:

Composite Attributes

A composite attribute can be broken into many meaningful sub-parts.

Example:

Address –> City, Street, State

Notation:

In the figure above, the composite attribute Address is divided into State, City, and Street.

Single Valued Attributes

A single valued attribute is the attribute that stores one value for each entity. The attribute has a single value in the database.

Example: Salary, Age

Notation:

The attribute Salary can only store single numerical value for the entity Employee.

Multi-valued Attributes

An attribute that has multiple values for a single entity is called Multi-valued attribute.

Examples: Phone numbers, E-mail ids

Notation:

Multi-valued attributes are shows as double ovals.

Derived Attributes

A derived attribute is one whose value is derived from another attribute.

Example:

Age is calculated from Date_of_Birth.

Total calculated from Quantity x Price.

Notation:

A derived attribute is shown as a dashed oval.

Stored Attributes

A stored attribute is the one whose value is stored in the database, and used to calculate the value of derived attribute.

Examples:

Date_of_Birth, Salary, Quantity, Price

Notation:

The notation for stored attribute is same as single attribute. A single oval.

Key Attributes

The key attribute uniquely identify the entiry.

Example:

StudentID identifies Student,

EmployeeID uniquely identify Employee.

Notation:

The key attribute is underlined.

Partial Key Attributes

An attribute that partially identifies a weak entity is called partial key attribute. The weak entity has no primary key. It has a partial key that combines with primary key of strong entity and uniquely identify the weak entity.

Example:

EnrollmentID is a partial key for Enrollment. It is because same student can enroll in different courses with different EnrollmentID.

Notation:

The partial key attribute has dashed underline for the attribute name.

Complex Attributes

Complex attributes are possible in E-R Diagram. When an attribute is both composite and multi-valued, it is a complex attribute.

Example:

Address attribute is composite with more than one address.

Each of those addresses have state, city, and street.

Relationships

A relationship represents how entities are logically connected in an E-R model. Without relationships, the database is just an isolated collection of tables with no meaningful connections.

Here are few important points to remember to understand the importance of relationships.

• They determine the database design by determining how tables are linked, the nature of relationship such as one-to-one, or one-to-many, and so on.
• They express the real world business rules. The cardinality of the relationship such as one-to-many is expressed business rules like one student can enroll into multiple courses.
• Relationships reduce data redundancy.

Example:

Students Enrolls in a Course. The Enrolls is a relationship.

Student (A103) Enrolls in Course ( Mathematics).

Relationship Set

• The relationship set is a set of similar relationship instances between the same entity sets.
• An entity set is a collection of similar entities that share the common attributes.

Student entity is one instance. However, Students entity set has many instances.

Similarly, Course entity set has many instances.

• Enrolls is a relationship set.

Enrolls = {
 (Student 101, Course DBMS),
 (Student 102, Course OS),
 (Student 103, Course DBMS) 
 }

There is more to entities, relationships and attributes.

Summary

ER diagram is made of three primary elements – entities, attributes for entities, and relationships. There are different types of entities – strong, weak entities.

The attributes are also of many types – single, composite, key, multi-valued, and so on.

The relationship is also defined using degrees, and cardinality. There is topic of aggregation.

All of these deserve separate article and discussion. In our next article, we will explore more on these topics.