Entity-Relationship Model (ER Model)

In the design process of relational databases, understanding and mastering the Entity-Relationship Model (shortly ER model) is a crucial first step. As the cornerstone of data modeling, the ER model graphically describes objects in the real world and their interconnections in an intuitive manner, providing a clear blueprint for subsequent transformation into rigorous relational table structures. This tutorial will deeply analyze the three core elements of the ER model: Entities, Attributes, and Relationships, while exploring their practical application value in development.

Definition and Identification of Entities

In the ER model, an "Entity" refers to distinguishable objects or concepts in the real world. These entities can be concrete physical things such as "Student," "Course," or "Order," but also abstract concepts like "Department" or "Employee." An entity is typically distinguished by a set of unique identifiers; this identifier is known as the Primary Key.

The identification of entities follows these principles:

  • Independence: An entity must be able to exist independently or be referenced separately. For example, a "Student" can exist as an independent entity without relying on specific courses.
  • Distinguishability: There must be exactly one unique identifier between two entities of the same type.
  • Instantiation: A single entity type may have multiple concrete instances (i.e., records) at any given point in time.

Classification and Role of Attributes

Attributes are the minimal data units that describe entity characteristics, carrying specific information about an entity. Based on their nature and value determination methods, attributes are generally classified into three categories:

  1. Simple Attribute
    This is the most common attribute type; its value is a single atomic value that cannot be further divided. For example, the "Name" of a "Student" entity is a typical simple attribute; it can only store one specific string and cannot simultaneously contain multiple unrelated values.

  2. Composite Attribute
    When characteristics to be described themselves form an integrated whole, they constitute composite attributes. For instance, "Address" is a composite attribute that can further decompose into sub-attributes such as "Province," "City," "District," and "Street." In database design, composite attributes are broken down into multiple simple attributes for separate storage; however, during the logical modeling phase, their integrity must be maintained.

  3. Multivalue Attribute
    If an entity can possess multiple values of the same type, that attribute is termed a multivalue attribute. For example, the "Phone" attribute of a "Student" may contain multiple numbers (such as home landline and mobile). In relational databases, multivalue attributes usually need to be implemented through auxiliary tables for normalized storage to avoid violating First Normal Form.

Types of Relationships and Cardinality Constraints

Entities are interconnected via "Relationships," which defines the logical structure between data. A relationship itself can also possess attributes, known as "relationship attributes." Based on the strength of connections and quantity constraints, relationships fall into several key types:

  • One-to-One Relationship (1:1)
    An entity instance is associated with at most one instance of another entity. For example, there is typically a one-to-one relationship between a "Principal" and a "School"; a school has only one principal, and a principal manages only one school. In database design, this can be achieved by setting the primary key of one table as a foreign key in the other.

  • One-to-Many Relationship (1:N)
    This is the most common relationship type. An entity instance can associate with multiple instances of another entity, but the latter can only associate with one instance of the former. A typical example is the relationship between "Student" and "Course": a student may enroll in many courses, while a course is typically pointed to by enrollment records from specific students (within a given time period).

  • Many-to-Many Relationship (M:N)
    Every element within two entity sets can be associated with multiple elements within the other set. For example, "Student" and "Course" often constitute a many-to-many relationship because one student may take many courses, while a course may also be taken by many students. In strict relational database normalization rules, many-to-many relationships must be converted into two one-to-many relationships for storage by introducing an intermediate association table (i.e., a "linking table").

Transformation from Conceptual Model to Logical Model

After mastering the ER model, the next critical task is converting the graphical conceptual model into specific relational schemas (i.e., database table structures). This process adheres to the following standard steps:

  1. Entity to Table: Convert each entity type into an independent table; the primary key of the entity serves directly as the unique identifier for that table.
  2. Attribute to Column: Convert all attributes of the entity into fields within the table and define corresponding data types.
  3. Relationship to Foreign Key: For one-to-many or many-to-many relationships, implement associations by adding a foreign key in the "one" side table pointing to the primary key of the "many" side table.
  4. Handling Multivalue Attributes: If multivalue attributes exist, create additional association tables to store these repeated values.

Practical Example: Building a Student Course Selection System

To better understand the above theory intuitively, let us attempt designing a simple "Student Course Selection System":

  • Entities and Attributes:
    • Student (Student): Contains ID (Primary Key), Name, Age.
    • Course (Course): Contains ID (Primary Key), Course Name, Credit Hours.
  • Relationship Definition:
    • There is a many-to-many relationship between Student and Course (one student selects multiple courses; one course is selected by multiple students).
  • ER Diagram Logical Deduction:
    • Create Student table and Course table.
    • Introduce an intermediate table Enrollment (Course Selection Record), containing three fields: Student_ID, Course_ID, and Grade.
    • In the Enrollment table, Student_ID acts as a foreign key referencing the Student table, while Course_ID acts as a foreign key referencing the Course table.

Through this structured design, we not only clearly express real-world business logic but also ensure data integrity and consistency at the database level. The ER model serves as a bridge connecting the real world with the digital realm; its rigorous construction logic is the starting point for any high-quality relational database design.