Entity-Relationship (ER) Model Basics: A Foundation for Database Design

Explore the fundamental concepts of the Entity-Relationship (ER) model, which provides a conceptual framework for designing databases. Learn about entities, attributes, relationships, and cardinality constraints, and how they are used to represent real-world scenarios in a database model.

DBMS - ER Model Basic Concepts

The Entity-Relationship (ER) model defines the conceptual view of a database. It is based on real-world entities and the associations between them, making it an ideal option for designing databases at the view level.

Entity

An entity is a real-world object, either animate or inanimate, that can be easily identified. For instance, in a school database, students, teachers, classes, and courses are considered entities. Each entity has certain attributes that provide it with identity.

An entity set is a collection of similar types of entities that share attributes. For example, a "Students" entity set may include all the students of a school, while a "Teachers" entity set may include all the teachers from different departments. Entity sets do not need to be disjoint.

Attributes

Entities are represented by their attributes, which are properties that have specific values. For example, a "Student" entity may have "Name," "Class," and "Age" as its attributes.

Each attribute has a domain or range of values that it can accept. For instance, a student's name must be alphabetic, while age cannot be a negative number.

Types of Attributes

  • Simple attribute − These are atomic values that cannot be further divided. For example, a phone number.
  • Composite attribute − Composed of more than one simple attribute. For instance, a full name might include "First Name" and "Last Name."
  • Derived attribute − Attributes that don’t exist in the database physically but can be derived from other attributes, such as "Age" derived from "Date of Birth."
  • Single-value attribute − Attributes that contain only one value, like "Social Security Number."
  • Multi-value attribute − Attributes that may contain multiple values, such as "Phone Numbers" or "Email Addresses."

Attributes can be combined in various ways, including:

  • Simple single-valued attributes
  • Simple multi-valued attributes
  • Composite single-valued attributes
  • Composite multi-valued attributes

Entity-Set and Keys

A key is an attribute or combination of attributes that uniquely identifies an entity within an entity set. For example, a student's "Roll Number" can uniquely identify them among other students.

  • Super Key − A set of one or more attributes that, collectively, identify an entity in an entity set.
  • Candidate Key − A minimal super key; an entity set can have multiple candidate keys.
  • Primary Key − A candidate key chosen by the database designer to uniquely identify the entity set.

Relationship

A relationship is an association between entities. For instance, an "Employee" works at a "Department," and a "Student" enrolls in a "Course." In these cases, "Works_at" and "Enrolls" represent relationships.

Relationship Set

A relationship set is a collection of relationships of a similar type. Like entities, relationships can have attributes known as descriptive attributes.

Degree of Relationship

The degree of a relationship is determined by the number of participating entities:

  • Binary = degree 2
  • Ternary = degree 3
  • n-ary = degree n

Mapping Cardinalities

Cardinality defines the number of entities in one entity set that can be associated with entities in another set through a relationship. The types include:

  • One-to-One − Each entity from one set is associated with at most one entity from the other set, and vice versa.
  • One-to-Many − An entity from one set can be associated with multiple entities from the other set, but each entity from the latter set is associated with at most one entity from the first set.
  • Many-to-One − Multiple entities from one set can associate with at most one entity from the other set, but an entity from the latter can associate with multiple entities from the first set.
  • Many-to-Many − Entities from both sets can associate with multiple entities from the other set.