Entity-Relationship Model

Introduction to Entity Sets

The Entity-Relationship (ER) model is a high-level conceptual data model that helps in designing the schema of a database. It allows us to describe the data involved in a real-world enterprise in terms of objects and their relationships.

Entity: An entity is an object that exists and is distinguishable from other objects. For example, a specific student named “Alice” is an entity.
Entity Set: An entity set is a collection of entities of the same type that share the same properties or attributes. For example, the set of all students in a university forms an entity set.

An entity type describes the common structure (such as Student), while an entity instance is one particular member (such as the student with roll number 26f10). In a database design, attributes describe entities and relationships; their domain is the set of values they may take.

Keys and Relationships

Superkey: An attribute set that uniquely identifies an entity. It may contain extra attributes that are not needed for uniqueness.
Candidate key: A minimal superkey; removing any attribute would make it stop being unique.
Primary key: The candidate key selected as the main identifier. Other candidate keys are alternate keys.
Composite key: A key made from more than one attribute, such as (course_id, sec_id, semester, year) for a course section.
Relationship set: A set of associations among entity sets. A relationship instance connects particular entity instances, such as one student taking one section.
Relationship degree: The number of participating entity sets. A recursive (unary) relationship connects an entity set to itself; a binary relationship connects two sets; a ternary relationship connects three.

An entity may take part in the same relationship more than once. In a recursive relationship such as prereq, role names distinguish the roles: one course is the course being taken, and the other is its prerequisite.

Strong Entity Set

A strong entity set is an entity set that contains sufficient attributes to uniquely identify all its entities.
A strong entity set always has a Primary Key.

Weak Entity Set

A weak entity set is an entity set that does not contain sufficient attributes to uniquely identify its entities on its own.
A primary key does not exist for a weak entity set.
Instead, it contains a partial key called a Discriminator. The discriminator is represented in an ER diagram by underlining the attribute with a dashed line.

Characteristics of Weak Entity Sets

A weak entity set cannot exist independently since it doesn’t have a primary key. It depends on a strong entity set.
It features in the model in relationship with a strong entity set. This dependency is called an identifying relationship.
The primary key of a weak entity set is formed by combining its discriminator with the primary key of the strong entity set it depends on.
Primary key of weak entity set = Discriminator + Primary key of Strong entity set.
A weak entity set must have total participation in its identifying relationship.

Attributes

An attribute is a property or characteristic associated with an entity set. For example, the Student entity set might have attributes like Name, Age, and Roll Number.

Types of Attributes

Simple attribute: An attribute that cannot be divided into smaller sub-parts (e.g., Age).
Composite attribute: An attribute that can be divided into smaller sub-parts. For example, a Name attribute can be divided into First Name, Middle Name, and Last Name.
Single-valued attribute: An attribute that holds a single value for a specific entity (e.g., Roll Number).
Multivalued attribute: An attribute that can hold multiple values for a specific entity. For example, a student might have multiple phone numbers ({phone_numbers}).
Derived attribute: An attribute whose value is calculated (derived) from the value of another attribute. For example, Age() can be derived from the Date of Birth attribute.

A relationship attribute describes an association rather than an entity. For example, grade describes a student’s takes relationship with a particular section; storing it only on Student would lose which course earned that grade.

Entity-Relationship (ER) Diagrams

An ER diagram is a visual representation of the ER model. The standard notation involves:

Rectangles: Represent entity sets.
Ellipses: Attributes are listed inside ellipses and connected to the entity set.
Solid Underline: Indicates a primary key attribute.
Diamonds: Represent relationship sets.
Double Lines: Indicate total participation of an entity in a relationship.
Double Rectangles: Represent weak entity sets.
Double Diamonds: Represent identifying relationship sets for weak entity sets.

See the university schema example for a worked diagram.

Ternary Relationships

A ternary relationship connects three entity sets in one fact. For example, Assigns can connect an instructor, a project, and a student to record which instructor assigned which project to which student.

Ternary relationship connecting three entity sets

Use one ternary relationship when the fact depends on all three participants together. Three separate binary relationships may lose which participants formed the original association. See Advanced ER Concepts for more detail.

Mapping Cardinalities (Relationship Types)

Mapping cardinality describes the maximum number of relationship instances in which an entity may participate with entities on the other side. It does not by itself say whether participation is required; that is a separate constraint described below.

One-to-One (1:1) Relationship

Each entity in \(A\) is associated with at most one entity in \(B\).
Each entity in \(B\) is associated with at most one entity in \(A\).
Example: Each instructor has at most one student, and each student has at most one instructor.

One-to-Many (1:N) Relationship

One entity in \(A\) may be associated with many entities in \(B\).
Each entity in \(B\) is associated with at most one entity in \(A\).
Example: An instructor can advise multiple students, but each student has only one instructor.

Many-to-One (N:1) Relationship

Each entity in \(A\) is associated with at most one entity in \(B\).
One entity in \(B\) may be associated with many entities in \(A\).
Example: Many students can be advised by the same instructor, but each student has at most one instructor.

Many-to-Many (M:N) Relationship

One entity in \(A\) may be associated with many entities in \(B\).
One entity in \(B\) may be associated with many entities in \(A\).
Example: An instructor can advise multiple students, and a student can have multiple instructors.

Total and Partial Participation

Participation constraints dictate whether the existence of an entity depends on its being related to another entity via the relationship type.

Total Participation: Every entity in the entity set must participate in at least one relationship in the relationship set. Indicated by a double line.
Partial Participation: Some entities may not participate in any relationship in the relationship set. Indicated by a single line.

Example: If every student must have an instructor, the Student entity set has total participation. If an instructor may or may not have a student, the Instructor entity set has partial participation.

Do not confuse the two constraints: 1:N says that a student can have at most one advisor; it does not say that every student has an advisor. Total participation adds the minimum of one. In min-max notation, a student who must have exactly one advisor has (1,1), while an instructor who may advise any number of students has (0,N). Together, min-max constraints express both optionality and maximum cardinality.

Expressing Weak Entity Sets in ER Diagrams

As discussed earlier, a weak entity set does not have a primary key and depends on a strong entity set. In an ER diagram, we denote the weak entity set with a double rectangle, its identifying relationship with a double diamond, and its discriminator attribute with a dashed underline.