ER Model
Entity-Relationship (ER) Model
The ER model is widely used for database design as it visually represents how data is structured and related. It consists of a collection of entities (real-world objects) and the relationships between them.
Each entity in the model is connected to others through conditions and dependencies, meaning one entity may depend on another. The ER model helps in designing a clear and logical structure before implementing the database.
Basic Concepts of ER Modeling
The Entity-Relationship (ER) Model is built on three fundamental concepts:
-
Entity or Entity Type
Attributes
Relationship
These basic concepts help in creating an ER diagram which visually represents the structure and relationships within a database, aiding in database design and understanding.
Entity or Entity Type
An Entity represents a real-world object or concept that is easily identifiable. It could be a physical object or an abstract concept.
For example, in a company’s database, Employee, HR, and Manager are considered entities. Each entity will have its own set of attributes.
In an ER diagram, an entity is typically represented by a rectangular box.
Types of Entity
- Strong Entity
- Weak Entity
- Entity Instance
Strong Entity
A Strong Entity is an entity that does not depend on any other entity in the database or schema for its identification. It has a primary key, which is a unique identifier that no other entity shares.
For example, in a Student entity, the Roll Number can be the primary key since it uniquely identifies each student. This makes the Student a strong entity, as the Roll Number will always be unique and independent from other entities.
In an ER diagram, a strong entity is represented by a single rectangle.
Weak Entity
A Weak Entity is an entity that depends on another entity for its identification and does not have its own primary key, unlike a Strong Entity. It relies on the primary key of another entity (often called the owner entity) to form a unique identifier.
In an ER diagram, a weak entity is represented by a double rectangle.
For example, in a Marks entity, there may be no unique ID, and its existence depends on another entity, such as the Student entity. The Marks entity cannot be uniquely identified without referencing the Student entity.
Entity Instance
An Entity Instance refers to a specific example of an entity. It represents a single occurrence of an entity type.
For example, if Animal is an entity, its instances could be Dog, Cat, Cow, etc. Each of these instances belongs to the Animal entity category.
In a database, entity instances are the actual data values stored for an entity.
Attributes
An Attribute is a piece of information that describes or defines an entity. It provides details about an entity by quantifying, qualifying, classifying, or specifying its characteristics. Attributes can hold single values, which may be numbers, characters, or strings.
Types of Attributes
- Key Attribute
- Simple Attribute
- Composite Attribute
- Single-Valued Attribute
- Multi-Valued Attribute
Key Attribute
A Key Attribute is an attribute that uniquely identifies an entity within a database. It ensures that each instance of an entity is distinct from all others.
For example, in a Student entity, Roll Number is a key attribute because it uniquely identifies each student.
Simple Attribute
A Simple Attribute is an attribute that cannot be divided into smaller parts. It holds a single, indivisible value for an entity.
For example, in an Employee entity, Name is a simple attribute because it is stored as a single value without further division.
Composite Attribute
A Composite Attribute is an attribute that can be divided into smaller sub-attributes while still retaining its meaning.
For example, in an Employee entity, the Name attribute can be broken down into First Name and Last Name. Here, Name is a composite attribute because it consists of multiple meaningful sub-parts.
Single-Valued Attribute
A Single-Valued Attribute holds only one value for each entity instance and cannot have multiple values.
For example, in a Person entity, Age is a single-valued attribute because a person can have only one age at a given time.
Multi-Valued Attribute
A Multi-Valued Attribute is an attribute that can hold multiple values for a single entity instance.
For example, in a Person entity, Degree is a multi-valued attribute because a person can have multiple degrees (e.g., B.Sc., M.Sc., Ph.D.).
Relationship Types in ER Model
In the ER Model, relationships define how two entities are connected. There are three main types of relationships:
-
One-to-One (1:1) Relationship
- A single instance of Entity A is associated with at most one instance of Entity B, and vice versa.
- Example: A person and their passport (Each person has one passport, and each passport belongs to one person).
-
One-to-Many (1:N) Relationship
- A single instance of Entity A can be related to multiple instances of Entity B, but each instance of Entity B is associated with at most one instance of Entity A.
- Example: A teacher and students (One teacher can teach multiple students, but each student has only one assigned teacher).
-
Many-to-Many (M:N) Relationship
- Multiple instances of Entity A can be related to multiple instances of Entity B.
- Example: Students and courses (A student can enroll in multiple courses, and each course can have multiple students).
Practical Usage
- One-to-One relationships are rare in real-world database design.
- One-to-Many and Many-to-Many relationships are commonly used.
- In relational databases, Many-to-Many relationships are usually converted into One-to-Many relationships using a junction table (or bridge table) for proper database normalization.
Relationship Instance
A Relationship Instance is a specific occurrence of a relationship between two entities in an RDBMS. It represents a finite set of tuples (rows) in a relational table, ensuring that there are no duplicates.
For example, if "Works-For" is the relationship between the Employee entity and the Department entity, then:
- Ram works for the Computer Science department
- Shyam works for the Electrical department
These are relationship instances of the "Works-For" relationship.
Degree of a Relationship
The Degree of a Relationship refers to the number of entity types involved in a relationship. It can be classified as:
-
Unary Relationship (Degree = 1)
- A relationship where an entity is related to itself.
- Example: Manager-of → An employee manages other employees within the same entity set.
-
Binary Relationship (Degree = 2)
- A relationship between two entities.
- Example: Works-For → An employee works for a department.
-
Ternary Relationship (Degree = 3)
- A relationship involving three entities.
- Example: Purchases → A customer buys an item from a shopkeeper.
In real-world database design, binary relationships are the most common, while ternary and higher-degree relationships are often broken down into multiple binary relationships for better normalization.
Cardinality
Cardinality defines the number of items (or entities) that must be involved in a relationship between two sets of entities. It represents how many instances of one entity are related to instances of another entity in a relationship.
The three common classifications of cardinality are:
-
One-to-One (1:1)
- Each instance of Entity A is associated with exactly one instance of Entity B, and vice versa.
-
One-to-Many (1:N)
- One instance of Entity A can be associated with multiple instances of Entity B, but each instance of Entity B is associated with only one instance of Entity A.
-
Many-to-Many (M:N)
- Multiple instances of Entity A can be related to multiple instances of Entity B, and vice versa.
Cardinality helps define the rules for how entities interact with each other, ensuring referential integrity and appropriate relationships in a database schema.
Labels: DBMS
