E-R Model

E-R Model:

The term "E-R" is abbreviated to Entity-Relationship. As the name implies, this model designs the database as a collection of various entities and the relationship among those entities. It is widely used in database.

An Entity is an object that exists and is distinguishable from other objects. Such as real world entities are distinguishable from other objects. Some of the examples are: specific person, company, event, plant and so on.

An Entity set is a set of entities of the same type that share the same properties. All entities in an entity set have the same set of attributes. Each entity set has a key. Each attribute has a domain. Example: set of all Departments, Professors, Students, Administrators.

Relationship can be understood as the connections between two or more entity sets. A relationship is an association among several entities. Examples:

• John works in an IT department.
• Students and professors are under a certain department
• The manager manages and controls the departments.

Relationship set:

A relationship set is a mathematical relation among n>=entities, each taken from entity sets {(e₁,e₂,...,e_n) | e₁∈ E₁, e₂∈ E₂,..., e_n∈ E_n} where {e_1,e₂,......,e_n} is a relationship.An attribute can also be property of a relationship set. A relationship may also have attributes called descriptive attributes. For instance, the advisor relationship set between entity sets instructor and student may have the attribute date which tracks when the student started being associated with the advisor.

Degree of a Relationship set:

The degree of a relationship type refers to the number of participating entity. The relationships are:

• Binary Relationship:
  

  This type of relationship involves two entity sets or it is also commonly known as second degree relationship. Most of the relationships set in a database system are binary.

• Ternary Relationship:
  

  This type of relationship involves three entities.

• N-array Relationship:
  

  This type of relationship involves n entities. Example: If a student works on certain project under the supervision of an engineer then this relationship is a binary relationship. Relationship supply is a ternary relationship between supplier, part and object.

Kinds of Constraints

E-R Model can be defined by two kinds of constraints and they are:

• Cardinality Constraints
• Participation Constraints

These two constraints together are called "Structural Constraints"

1. Cardinality Constraints:

   These constraints are also called mapping cardinality constraints. These constraints express the number of entities to which another entity can be associated through a relationship set. Cardinality constraints are mostly useful in describing binary relationship sets. In order to describe a binary relationship set, the mapping cardinality must be one of the following types:
   
   One to one: An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. For example: One department can have only one manager.
   
   One to many:An entity in A is associated with any number of entity in B and by any number means it can be either zero or more. An entity in B, however, can be associated with at most one entity in A. Example: One department can have many employees.
   
   Many to one:An entity in A is associated with at most one entity in B. An entity in B, however, can be associated with any number and by any number means it can be either zero or more of entity in A. For example: Many employees works in one department.
   
   Many to many:An entity in A is associated with any number of entities in B and an entity in B is associated with any number of entities in A. For example: Many employees works in many department.
   
   
   

   
   
   One to One
   
   

   
   
   One to Many

   
   
   
   
   Many to One

   
   
   Many to Many
   
   
   

   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
2. Participation Constraints:Participation can be of two types:

• Total Participation (indicated by double line):In this type of participation, every entity in the entity set participates in at least one relationship in the relationship set. For example: participation of section in sec_course is total and every section must have an associates course.
• Partial Participation:In partial participation, some entities may not participate in any relationship in the relationship set. For example: participation of instructor in advisor is partial.

Attributes:

The properties of the entities in the set are called attributes. Descriptive properties possessed by all members of an entity set. The set of permitted values for each attribute is called domain.

For example: Instructor= (ID, name, street, city, salary)

course= (course_id, title, credits)

Types of Attributes:

1. Simple and Composite attributes: A Simple attribute is one that cannot be broken down into smaller components while Compositeattributes can be divided into smaller parts which represent simple attributes with independent meaning. Composite attributes can be divided into sub parts.
   
   
   
   

   

   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
2. Single-valued and Multi-valued attributes:
   A Multi-valuedattribute is one that may have more than one value for a given instance. For example: a person may have more than one mobile number, employee can have more than one skill.
   
   
3. Derived attributes:
   A Derivedattributes are those whose values can be calculated from related attribute values. These attributes can be computed from other attributes. For example: age, given date of birth.
   
   
4. Descriptive attributes:
   A relationship may also have attributes called descriptive attributes.

Keys:

Keys can be defined as a group of one or more attributes that uniquely identify an entity in the entity set. There are three types of keys and they are:

1. Super Key:
   A super key is a set of attributes that allows to identify an entity uniquely in the entity set.
2. Candidate Key:
   A candidate key is a minimal super key that uniquely identifies either an entity or a relationship.
3. Primary Key:
   A primary key is a candidate key that is chosen by the database designer to identify the entities of an entity set.

Strong and Weak Entity Sets

Strong entity set:

Strong entity set refers to those type of set that can exist independently from other entity types. These type of sets always have a unique characteristics or identifier. An identifier is an attribute or combination of attributes that uniquely distinguish each occurrence of that identity.

Weak entity set:

Weak entiity set refers to those type of set that does not have a primary key. The existence of a weak entity set depends upon the existence of an identifying identity set. The weak entity set may relate to the identifying entity set through a total, one-to-many relationship set from the identifying to the weak entity set. The set of attributes that distinguishes among all the entities of a weak entity set is known as the discriminator or partial key. The primary key of a weak entity set is formed by the primary key of the strong entity set on which the existence of the weak entity set is depended.

References:

1. H.F.Korth and A. Silberschatz,"Database system concepts",McGraw Hill,2010
2. A.K.Majumdar and p, Bhattacharaya,"Database Management Systems",Tata McGraw Hill,India,2004
3. F.Korth, Henry. Database System Concepts. 6th edition.