Engineering Notes | Unified Knowledge Base

Entity-Relationship Model

A high-level data model based on a perception of a real world that consists of a collection of basic objects, called entities, and of relationships among these objects.

1Introduction

Data Model

Collection of conceptual tools for describing data, data relationships, data semantics, and consistency constraints.

ER Model

- It is a high level dataa odel based on a perception of a real world that consists of a collection of basic objects, called entities, and of relationships among these objects.

- Graphical representation of ER Model is ER diagram, which acts as a blueprint of DB.

2Entity

An Entity is a "thing" or "object" in the real world that is distinguishable from all other objects.

It has physical existence.
Each student in a college is an entity.
Entity can be uniquely identified. (By a primary attribute, aka Primary Key)

Strong Entity

Can be uniquely identified by its own attributes.

Weak Entity

Can't be uniquely identified by its own attributes.
Depends on some other strong entity.
Example: Loan (Strong) → Payment (Weak). Payment depends on Loan.

3Entity Set

It is a set of entities of the same type that share the same properties, or attributes.

E.g., Student is an entity set. Customer of a bank.

4Attributes

An entity is represented by a set of attributes.
Each entity has a value for each of its attributes.
For each attribute, there is a set of permitted values, called the domain, or value set, of that attribute.
E.g., Student Entity has following attributes:
- Student_ID
- Name
- Standard
- Course
- Batch
- Contact number
- Address

Types of Attributes

Simple

Attributes which can't be divided further.

Ex: Customer account number, Roll number

Composite

Can be divided into subparts (e.g., Name → First, Middle, Last).

Ex: Address (Street, City, State, PIN)

Single-valued

Only one value attribute.

Ex: Student ID, Loan Number

Multi-valued

Attribute having more than one value.

Ex: Phone Number, Dependent Name

Derived

Value can be derived from other related attributes.

Ex: Age (derived from Date of Birth)

Null Value

Attribute does not have a value (Unknown, Not exists, Missing).

Ex: Middle name (might not exist)

5Relationships

Association among two or more entities.
E.g., Person has vehicle, Parent has Child, Customer borrow loan etc.
Strong Relationship: Relationship between two independent entities.
Weak Relationship: Relationship between a weak entity and its owner/strong entity.
Ex: Loan <instalment-payments> Payment

Degree of Relationship

Unary: Only one entity participates. (e.g., Employee manages Employee)
Binary: Two entities participate. (e.g., Student takes Course)
Ternary: Three entities participate. (e.g.,Employee works-on branch, employee works-on project)

Relationship Constraints

1. Mapping Cardinality / Cardinality Ratio

Number of entities to which another entity can be associated via a relationship.

One to one: Entity in A associates with at most one entity in B.
E.g., Citizen has Aadhar Card. (A & B are entity sets)
One to many: Entity in A associated with N entities in B.
E.g., Citizen has Vehicle.
Many to one: Entity in A associated with at most one entity in B.
E.g., Course taken by Professor.
Many to many: Entity in A associated with N entities in B.
E.g., Customer buys product, Student attend course.

2. Participation Constraints

Also known as Minimum cardinality constraint.

Partial Participation: Not all entities are involved in the relationship instance.
E.g., Customer borrow loan (loan has total participation as it can't exist without customer entity, but customer has partial).
Total Participation: Each entity must be involved in at least one relationship instance.
E.g., Loan has total participation constraint.
Note: Weak entity has total participation constraint, but strong may not have total.

Symbols used in ER Diagram

Entity

Weak Entity

Relationship

Weak Relationship

Attribute

Multi-valued Attribute

Primary Key Attribute

Weak Key Attribute

Derived Attribute

Total Participation

Extended ER Features

Basic ER features are sufficient for most DBs, but complex schemas require extended features.

1. SpecialisationTop-Down Approach

Subgrouping an entity set into other entity sets that are distinct in some way. Splitting up an entity set into further sub entity sets based on functionalities.

"Is-a" relationship between superclass and subclass.
Depicted by Triangle component.
Used to pinpoint distinctive features of sub entities.

Example

Person (Superclass) divided into:

Customer
Student
Employee

Not all attributes apply to everyone. e.g., Salary applies only to Employee.

2. GeneralisationBottom-Up Approach

Reverse of Specialisation. Identifying common properties of two or more entities to create a new generalised entity set (Super class).

Avoids data repetition for common attributes.
Makes DB more refined and simpler.

Example

Car, Jeep, Bus have common attributes (Engine, Wheels).
Generalized into new entity set: Vehicle.

3. Attribute Inheritance

Attributes of higher level entity sets are inherited by lower level entity sets.

Ex: Customer & Employee inherit attributes of Person (Name, Age).

4. Participation Inheritance

If a parent entity set participates in a relationship, then its child entity sets will also participate in that relationship.

5. Aggregation

How to show relationships among relationships? Aggregation is the technique.

Abstraction applied to treat relationships as higher-level entities.
Called "Abstract Entity".
Avoids redundancy by aggregating relationship as an entity set itself.

Example

An Project is managed by a Manager. This relationship itself can be aggregated into a single entity to relate with Work Reports. (e.g., A "Management" relationship interacts with "Reports").