ER Model Fundamentals: Entities, Attributes, and Relationships
The ER model defines the conceptual view of a database. It works around real-world entities and the associations among them. At the view level, the ER model is considered a good option for designing databases.
Entity Concepts in the ER Model
An entity can be a real-world object, either animate or inanimate, that can be easily identifiable. For example, in a school database, students, teachers, classes, and courses offered can be considered entities. All these entities have attributes or properties that define their identity.
Entity Sets
An entity set is a collection of similar types of entities. An entity set may contain entities whose attributes share similar values. For example, a Students set may contain all the students of a school; likewise, a Teachers set may contain all the teachers of a school from all faculties. Entity sets need not be disjoint.
Attributes: Properties of Entities
Entities are represented by means of their properties, called attributes. All attributes have values. For example, a student entity may have name, class, and age as attributes.
There exists a domain or range of values that can be assigned to attributes. For example, a student’s name cannot be a numeric value; it must be alphabetic. A student’s age cannot be negative, etc.
Types of Attributes
- Simple Attribute: Simple attributes are atomic values, which cannot be divided further. For example, a student’s phone number is an atomic value of 10 digits.
- Composite Attribute: Composite attributes are made of more than one simple attribute. For example, a student’s complete name may have first_name and last_name.
- Derived Attribute: Derived attributes are attributes that do not exist in the physical database, but their values are derived from other attributes present in the database. For example, average_salary in a department should not be saved directly in the database; instead, it can be derived. For another example, age can be derived from the date of birth.
- Single-Value Attribute: Single-value attributes contain a single value. For example: Social Security Number.
- Multi-Value Attribute: Multi-value attributes may contain more than one value. For example, a person can have more than one phone number, email address, etc.
These attribute types can be combined:
- Simple single-valued attributes
- Simple multi-valued attributes
- Composite single-valued attributes
- Composite multi-valued attributes
Entity Sets and Keys
A Key is an attribute or collection of attributes that uniquely identifies an entity within an entity set.
For example, the roll number of a student makes him or her identifiable among all students.
- Super Key: A set of attributes (one or more) that collectively identifies an entity in an entity set.
- Candidate Key: A minimal super key is called a candidate key. An entity set may have more than one candidate key.
- Primary Key: A primary key is one of the candidate keys chosen by the database designer to uniquely identify the entity set.
Relationships and Associations
The association among entities is called a relationship. For example, an employee works_at a department, or a student enrolls in a course. Here, Works_at and Enrolls are examples of relationships.
Relationship Sets and Descriptive Attributes
A set of relationships of a similar type is called a relationship set. Like entities, a relationship can also have attributes. These attributes are called descriptive attributes.
Degree of Relationship
The number of participating entities in a relationship defines the degree of the relationship.
- Binary = degree 2 (involving two entity sets)
- Ternary = degree 3 (involving three entity sets)
- N-ary = degree N (involving N entity sets)
Mapping Cardinalities
Cardinality defines the number of entities in one entity set that can be associated with the number of entities of another set via a relationship set.
One-to-One (1:1)
One entity from entity set A can be associated with at most one entity of entity set B, and vice versa.
One-to-Many (1:N)
One entity from entity set A can be associated with more than one entity of entity set B. However, an entity from entity set B can be associated with at most one entity from entity set A.
Many-to-One (N:1)
More than one entity from entity set A can be associated with at most one entity of entity set B. However, an entity from entity set B can be associated with more than one entity from entity set A.
Many-to-Many (M:N)
One entity from entity set A can be associated with more than one entity from entity set B, and vice versa (meaning entities in B can also be associated with multiple entities in A).
