Database design step by step (3) -- Basic ER model component

Source: Internet
Author: User

Introduction: Database Design
Step by step (2) -- after the database life cycle is published in the garden, I received some emails and a friend called me directly to ask why it does not contain the physical design of the database. Here I will explain that the physical design of databases is closely related to database products. The focus of this series is the general design concepts and methods of databases, this is also a piece of software that is easily overlooked in China. Today, we will learn the components and semantics of the entity relationship (ER) model, which is the basis of database logic design. The content may be boring, but very important and useful.

Due to the large amount of content, we will give two lectures to learn the component of the object link model.

Today, we will first learn about the basic object-link model.

The object relation (ER) model aims to capture the data requirements of the real world and express them in a simple and easy-to-understand manner. The ER model can be used to communicate with the project team or discuss system data requirements with users.

Basic Elements in ER Model

The basic ER model contains three elements: entity, relation, and attribute.

Figure 1
ER diagram of entities, relationships, and attributes

Entity: An entity is the primary data object. It is often used to represent a person, a place, a thing, or an event. A specific entity is called an entity instance (entity instance or entity occurrence ). The object is represented in a rectangular box, and the object name is marked in the box. Generally, the first letter of a name is capitalized.

Relationships: A link represents a link between one or more entities. The relationship depends on the entity, and generally does not exist physically. Links are commonly used to indicate the correspondence between objects, one-to-one, one-to-many, and many-to-many. The graph of a link is a diamond, and its name is generally a verb. The link endpoint is associated with the role ). Generally, the role name can be omitted, because the entity name and Link name can clearly reflect the concept of the role, but in some cases, we need to mark the role name to avoid ambiguity.

Attribute (attributes): the attribute provides a detailed description of the object. A Property of a specific object is called a property value. Attributes of the employee entity may include: EMP-ID, EMP-name, EMP-address, phone-No ....... Attributes are generally expressed in an elliptical shape and connected to the object described in the description. Attributes can be divided into two types: identifiers and descriptors ). Identifiers can uniquely identify an instance (key) of an object and can be composed of multiple attributes. The ER diagram is marked by an underscore in the property name. Multivalued attributes uses two lines to connect to an object. For example, the hobbies attribute (a person may have multiple hobrs, such as reading, movies ...). Composite attribute (Complex
Attributes) itself has other attributes.

Identify strong and weak entities: a strong entity has a unique identifier. The identifier of a weak entity comes from one or more other strong entities. Weak entities are represented in a two-line rectangular box and depend on strong entities.


Deep understanding of relationships

The relationship plays a very important role in the ER model. The ergraph can be used to describe the degree, number of connections, and existence information of relations between entities.

Let's explain these concepts one by one. First, let's look at the semantics of the link in the ergraph.

Figure 2
Degree, number of connections, and existence of a link

Degree of a relationship)

The number of entities associated with the link. The degree of the binary relationship and the ternary relationship is 2 and 3 respectively, and so on to N yuan. Binary relationships are the most common relationships.

The leader relationship between an employee and another employee is called a binary regression relationship. As shown in 2, the employee object is connected to itself through relationship manages. The role name (Manager and subordinate) is marked because the employee plays two roles in this relation ).

The three-element relationship is associated with three entities. When a binary relationship cannot accurately describe the semantics of an association, a ternary relationship is required. Let's take a look at the example below. (1) it can reflect what skill an employee uses in a project. (2) Only the skill of the employee and the project involved can be seen, but the specific skill used in a project cannot be known.

Figure 3
Semantics of ternary relationships

Note that in some cases, the ternary relationship is incorrectly defined. These three relations can be divided into two or three binary relationships to achieve the purity of simplification and semantics. We will discuss the ternary relationship in detail in future blog posts.

An object can be involved in any number of relationships. Each link can be connected to any number of elements (entities), and there can be any number of binary relationships between two entities.

Connectivity of a relationship)

The number of instances associated with the link.

The value of the connected number can be "one" or "multiple ". The end of "1" is represented by marking "1" between the object and the link in the ER diagram. "N" indicates multiple ends. 2. In the relational connectivity section, "1" to "1": department is managed by employee; "1" to "many": department has employees; "many" to "many ": employee may work on multiple projects and each project may have
Worker employees.

In some cases, the maximum number of connections is determined, and N can be replaced by a number. For example, the Team has 12 members.

Link Attributes

The link can also have attributes. As shown in figure 4, when an employee participates in a project's start date, the tasks assigned by an employee in a project are meaningful only when they are placed on the relational works-on.

Figure 4
Link Attributes

Note that the attributes of a link generally appear in the binary or ternary relationship between "many" and "many. Generally, "1" to "1" or "1" to "many" links do not include attributes (which may cause ambiguity ). These attributes can be moved to an object at one end. As shown in figure 5, if the relationship between a department and an employee (manager) is "one" to "one, in modeling, start-date may be used as the property of the link is managed by (indicating the time taken over). This property can be moved to the department or employee entity.

Figure 5
One-to-one management relationship between departments and managers

If there is a "many" to "many" relationship between the Department and the manager, that is, cross-management, what will happen?

Existence of an entity in a relationship)

The existence of entities in a link can be mandatory or optional. When one side of a link (either "one" or "multiple" Ends) must always exist, the object is mandatory. This object is optional.

Mark "0" on the connection line between the object and the link to indicate an optional existence. It indicates that the minimum number of connections is 0.

Forced existence indicates that the minimum number of connections is 1. When the existence is uncertain or unknown, the default minimum number of connections is 1.

In the ER diagram, the maximum connected data is displayed beside the object. As shown in figure 6, the meaning of the meaning is that a department has only one employee as a manager. An employee may be a department manager or not.

Figure 6
Existence of entities in a link

Other conceptual data model tagging Methods

The ER diagram method used previously was proposed by Peter Chen in 1976. In the field of modern database design, there are other ER Model marking methods.

Let's take a look at another method that uses a lot of marking methods, the "crow's-foot" (crow's feet) marking method, and make a simple comparison with the previously described marking method.

Learning each markup method is meaningless. Promote the application of a markup method in your organization to make it a common "language ".

Figure 7
Comparison between the Chen-based marking method and the crow's-foot marking method



1. The basic elements that constitute the ER model include entities, relations, and attributes.

2. Understand the semantics of a link in depth: the degree of the link, the number of connected links, and the existence of the link.

3. Understand the different marking methods of the ER model, master one of the marking methods, and promote and use them in your project.

Object link model reference

1. entity-relationship model (

2. entity-relationship modelling ( /~ Franconi/teaching/2000/ct481/er-modelling /)

Original article:

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.