ARTICLE: ER Model- Basic Concepts and ER Diagram

The data model which is used for giving a conceptual view of the database is the ER model. The model of the data works around the real-world entities and the relations associated with them. For the designing of the databases, the ER model is considered as the best option for the view level.

Entity

The real-world objects which are identified easily and are either animated and inanimate. For example, the students, classes, the teachers and the courses are considered as the entity of the school database. The entities get the identity by some of the involved properties and the attributes.

The set in which there is the constitution of the similar types of entities is called as the entity set. The set contains the entities and the attributes which share the same value. There is no need for the entity set to be disjoint. The example of the fact is that the elements of the student’s set are the students only, whereas, in the teachers set of the school database contains the teachers only.

Attributes

The values and the domain range are the ones which the attributes contain. The name of the students cannot have a numeric value. The value has to be alphabetic. It is because the age of anyone cannot be negative.

TYPES OF ATTRIBUTES

  • Simple Attribute- The values in the simple attribute are atomic. They are the ones who cannot be divided further. For instance, the simple attribute of the data contains the ten digit of the students’ phone numbers.
  • Composite attribute- In the composite form of the attribute, there is more than one simple attribute. In this, the best example in the school database would be of the students’ complete name. It can be the first and the last name.
  • Derived Attribute- In this, there is no existence of the attributes in the database, but they derive their values from the attributes in the database. For instance, the average salary of the teachers in the school database does not need to be included separately in the database. It is because we can reach the level of the average value with derivation. Also, we can derive the age of the students from the available date of birth.
  • Single-value attribute- As the name specifies it, it is the attribute which contains the single value. For instance, the roll number of the students is a single value.
    Multi-value attribute- The multi-value attribute of the data has more than one value in it. For instance, the students in the school database can have more than an email address and multiple phone numbers.

ENTITY-SET AND THE KEYS

Key

It is a collection of an attribute or a single attribute which are used for identification of the entity among the entity set.

For instance, the students in the school database can use the roll number to make the students identifiable in the classroom.

  1. Super Key- The key which is used for the identification of the entity in the entity set is called as the super key.
  2. Candidate Key- The candidate key is the one which is named as the minimal super key. The set of entities can have any number of candidate keys.
  3. Primary Key- The key which the database designer uses are called the primary key. These are used for the unique identification of the entity set.

Relationship Set

This is the set which contains the set of relationships and that too of the similar type. As the entities, the set of relationship can have any number of attributes. Such attributes are called the descriptive attributes.

Degree of Relationship

The degree of relationship defines the number of participating entities.

Every form of data has a different degree of relationship. Binary has a degree of 2 whereas, in the ternary format of data, we have the degree of the relationship as 3. N-ary of the data have a degree n.

MAPPING CARDINALITIES

The mapping cardinalities define the number of the entities in a particular set. These are the entities which can be associated with the number of entities present in the other set. The help of the relationship set can do it.

  1. One to One- In this relation set, one entity from set 1 can be linked or associated with the only one entity of the other set 2. This can happen vice and versa.
  2. One to Many- In this format, one entity from a set can be linked with multiple entities of the different set. But, it cannot be done in the vice and versa case. The entity value from the set 2 can be linked to only one entity only.
  3. Many to one- Multiple entities from the set 1 can be associated with only one entity and that too for the utmost. Whereas, in the second set, an entity can be associated with more than one entity.
  4. Many to many- Here everything goes multiple. Every entity from each set can be linked to the other set. And, yes, it functions in the vice versa format.

ER DIAGRAM REPRESENTATION

In the ER diagram, we have the relationships sets, entities and attributes of the relationship sets.

Representation of the entity

The rectangles are used for the representation of the entities in the ER model. The name of each rectangle is given with the name of the set of an entity that they signify.

REPRESENTATION OF ATTRIBUTES

The properties of the entities are the attributes. The ellipses are used for their representation. One ellipse represents one attribute. And then, they are connected to the rectangular representation of the data, that is the entity directly.

The attributes are further divided into the structure of a tree only if they are composite. The attribute gets the direct connection to every node. It is because every composite attribute is connected with an ellipse and is represented by an ellipse as well.

REPRESENTATION OF THE MULTIVALUED AND DERIVED ATTRIBUTES

They are depicted using the double ellipse. The dashed ellipses are used for depicting the derived attributes.

RELATION REPRESENTATION

Diamond-shaped boxes are used for the representation of the relationships. The name is written in the diamond-box. The relation is the central point of the system which connects all the entities present in it. A line connects them.

BINARY RELATIONSHIP AND ITS CARDINALITY

The form of relationship in which the participating entities are two in number is called the binary relationship. The number of the entity instances in the relationship for the association with the relation is known as the cardinality.

  1. One to one- You can also depict the type of relationship in the form of 1:1. Here, in this, only one instance is used for the association of the relationship.
  2. One to many- The other notation of this form is 1: N. Here is one too many relationships, multiple entities are used for the relationship to associate. Only one instance of the entity is linked in the other set.
  3. Many to one- In this form of the relationship, more than one instance of an entity is linked to the association for the relationship of all the data.
  4. Many to many- In the many to many relationship forms, several instances can be associated with the relationship.

PARTICIPATION CONSTRAINTS

  1. Total Participation- In the total form of the constraint, as the name signifies, each entity is included in the relationship. The double lines represent them.
  2. Partial Participation- It works in the opposite manner of the total participation. Here, not all of the entities are included in the relationship. Single lines are used for the representation of the partial participation.

To read the Database Management: Architecture and Data Models.