MySQL: Entity-Relationship Model

Entity-Relationship Model

Entity-Relationship model or E R model is used to create a relationship between different attributes or entities. It describes the structure of the database with the help of the ER Diagram or Entity Relationship Diagram. ER model creates a simple design view of the data. It helps in creating the conceptual diagram of the database that makes the data easier to understand.

For example:Here, we have a database COMPANY, and in this database, EMPLOYEE is the entity (table). The employee entity contains several attributes like EMP_ID, EMP_NAME, EMP_ADDRESS, EMP_DATE_OF_BIRTH, EMP_AGE, and EMP_CONTACT. In these attributes, EMP_NAME can be work as another entity having attributes like FIRST_NAME, MIDDLE_NAME, and LAST_NAME. Again, another attribute EMP_ADDRESS in the table EMPLOYEE can work as another entity with attributes like EMP_CITY, EMP_HOUSE_NO, EMP_PIN, etc.

ER model contains the following essential component:

• Entity
• Entity Set
• Attributes
• Relation

Entity:

The entity in DBMS can be a real-world object having conceptual reality and existence.

For example: Above, we have a database COMPANY, and the entity type is EMPLOYEE. Here employees are the natural world person that has some existence.

An entity can be further divided into several types:

• Strong entity
• Weak entity
• Tangible entity
• Intangible entity

Strong entity:

Strong entities are those entities which do not depend on other entity. The strong entity mainly contains the primary key.A single rectangular box represents a strong entity.

For example:In the previous example, we have entity EMPLOYEE having EMP_ID as the primary key. Therefore, it is termed as a strong entity.

Weak entity:

Weak entities do not contain the primary key in the table. They mainly depend on the parent entity for the data. A double rectangular box represents it.

For example:Suppose we have an entity EMPLOYEE_ADDRESS having attributes EMP_CITY, EMP_HOUSE_NO, and EMP_PIN. Here we see that the primary key is unavailable, and EMPLOYEE_ADDRESS (entity) is dependent on the EMPLOYEE (entity) for the data.

Tangible-entity:

Tangible entities are those entities that have some physical existence. These entities exist in the real world.

For example, Person, Table, Chair, etc.

Intangible-entity:

Intangible entities are those entities that do not have physical existence. There is no existence of an intangible entity in the real world.

For example, Bank Account.

Entity Set

An entity set is a collection of more than one entity of a similar entity type. The entity type can be the data or values given to the data.

For example, suppose we have a table STUDENT(entity type) with attributes ID, NAME, and AGE.Then, the data given to this table can be the entity set. Let’s have a table name as STUDENT.

Here, STUDENT is entity type, and (01, Sumit, 23), (02, Ajay 26), and (03, Amit, 32) are the entity set.

Attributes

Attributes indicate the property and characteristics of an entity. There can be several attributes of an entity, depend on its possibility. One of the attributes is considered to be the primary key, candidate key, etc.

Attributes are represented by elliptical shape.

Example:

These attributes can be divided into several types, such as:

• Simple attribute
• Composite attribute
• Single valued attribute
• Multi-valued attribute
• Derived attribute

Simple attribute:

The simple attribute cannot be further divided into its subtype. In other words, it cannot be further divided into one or more attributes.It also contains null values.

For example, Attributes like ID and ROLL_NO in table STUDENT (entity type) are examples of simple attributes as these attributes cannot be further divided into subtypes.

Composite Attribute

Composite attributes are those attributes that can be further divided into meaningful subparts. In other words, these attributes can be further divided into more than one sub-attributes.

For example, in the table STUDENT, attributes like NAME and ADDRESS can be further divided into sub-attributes. Like name can be divided into FIRST_NAME, MIDDLE_NAME, and LAST_NAME. ADDRESS can also be divided into CITY, HOUSE_NO, and PIN.

Single valued attribute

A single value attribute can have only a single attribute. Single value attributes are not necessarily simple attributes.

For example:AGE in entity STUDENT can be a single-valued attribute because age for each people will have a single value. It is not possible to have two ages for a single person. Each person will have only one age.

Multi-valued attribute

Multi-valued attributes are those attributes that contain more than one value at a time. It can be a composite attribute.

For example, ADDRESS attribute in the table STUDENT. An address can hold attributes like city, pin code, etc. NAME attributes in table STUDENT can also be multi-valued attributes.

Derived attribute

The derived attribute is those attributes that do not exist in the physical database. Its value gets derived from other attributes, or we can say that it depends on other database attributes for the data.

For example:Suppose we have a table SCHOOL. In this table, we can say that the AGE attribute can be derived attribute as it can be derived from the DATE OF BIRTH attribute of the table.

Relation

The relation shows the relationship among different entities. Its shows how two or more entities are related to each other.It is represented by a diamond shape in the ER diagram.

There are four types of relationship:

• One to one
• One to many
• Many to one
• Many to many

One to one

Here, one entity is related to another single entity known as one relationship.

For example, a person can have only one voter ID card, and so the relation among them will be one to one only.

One to many

When an entity is related to more than one entity, then this relation is known as one to many relationships.

For example, a relation between customer and order. Here a customer can place many orders, but many customers cannot place an order.

Many to one

When more than one entity is related to a single entity, this is called a many to one relationship.

For example, a relation between student and school, many students can study in a single school, but vice versa is not possible.

Many to many

When more than one entity is related to more than one entity, this is called many to many relationships.

For example, a relation among students and subjects will be the best example for this as many students read many subjects and vice versa.