ER Model & Diagrams

Before writing a single line of SQL, database engineers design the database logically using the Entity-Relationship (ER) Model. It is a high-level conceptual data model developed by Peter Chen in 1976 to facilitate database design by allowing specification of an enterprise schema that represents the overall logical structure of a database.

The ER model is typically visualized using an ER Diagram (ERD).

1. Core Components

The ER Model is built upon three basic concepts: Entities, Attributes, and Relationships.

1. Entities

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

• Example: A specific person, a company, an event, a plant.
• An Entity Set is a collection of entities of the same type that share the same properties (e.g., all Students in a university).
• In an ER diagram, Entity Sets are represented by Rectangles.

2. Attributes

Entities are described by Attributes.

• Example: A Student entity may have attributes ID, Name, Age, and Address.
• In an ER diagram, Attributes are represented by Ovals connected to their entity.

Types of Attributes:

• Simple Attributes: Cannot be divided (e.g., Age).
• Composite Attributes: Can be divided into smaller subparts (e.g., Name can be divided into FirstName and LastName).
• Multi-valued Attributes: Can have a set of values for a specific entity (e.g., Phone_Numbers - a person can have multiple). Represented by Double Ovals.
• Derived Attributes: The value can be derived from other attributes (e.g., Age can be derived from Date_of_Birth). Represented by Dashed Ovals.
• Key Attributes: An attribute that uniquely identifies an entity within an entity set. Represented by an oval with the text underlined.

3. Relationships

A Relationship is an association among several entities.

• Example: A Student enrolls in a Course.
• A Relationship Set is a mathematical relation on $n \ge 2$ entity sets.
• In an ER diagram, Relationship Sets are represented by Diamonds.

2. Mapping Cardinalities

Mapping cardinalities (or cardinality ratios) express the number of entities to which another entity can be associated via a relationship set. This is crucial for determining how tables will be created in SQL.

1. One-to-One (1:1): An entity in $A$ is associated with at most one entity in $B$, and an entity in $B$ is associated with at most one entity in $A$.
   • Example: Citizen has Passport.
2. One-to-Many (1:N): An entity in $A$ is associated with any number (zero or more) of entities in $B$. An entity in $B$, however, can be associated with at most one entity in $A$.
   • Example: Department employs Instructors. (A department has many instructors, but an instructor works for exactly one department).
3. Many-to-One (N:1): The reverse of 1:N.
4. Many-to-Many (M:N): An entity in $A$ is associated with any number of entities in $B$, and vice versa.
   • Example: Student takes Course. (A student takes many courses, and a course has many students).

3. Weak Entities

An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a Weak Entity Set.

• A weak entity set must be associated with another entity set, called the Identifying (or Owner) Entity Set.
• The relationship associating the weak entity set with the identifying entity set is called the Identifying Relationship.
• In an ER diagram, weak entities are represented by Double Rectangles, and identifying relationships by Double Diamonds.