Database design step by step (7) -- Conceptual Data Modeling

Introduction: The first two blog posts (Database Design
Step by step (5)-understand user requirements and database design
In step by step (6) -- extract business rules), we analyze database requirements, focusing on two topics: 1. understanding user requirements; 2. Extracting business rules. After the requirement analysis is complete, we will enter the conceptual data modeling stage. This article uses the "Basic Object link model component" and "Advanced object link model component" introduced earlier as the basic elements of modeling. You can review the database design.
Step by step (3) -- Basic ER model component and database design
Step by step (4): model component and semantics of the advanced ER model component.

The logical database design has multiple implementation methods, including top-to-bottom, bottom-to-top, and hybrid. Traditional database design is a process from bottom to top. It combines multiple data elements from a single data element in the analysis requirement into tables in the database. This method is more difficult to deal with complex large-scale database design, which requires a combination of top-to-bottom design methods.

Using the ER Model for conceptual data modeling facilitates communication and communication between the project team and end users. The efficiency of ER modeling is also reflected in the design method from top to bottom. The number of entities in a database is much less than the number of data elements, because most data elements represent attributes. Identifying entities and focusing on relationships between entities can greatly reduce the number of objects to be analyzed.

Conceptual Data Modeling connects both ends, with one end being requirement analysis. It can help capture entities in the requirement and their relationships to facilitate communication. The other end is a relational database, and the model can easily be converted to a paradigm-oriented or quasi-paradigm-oriented SQL table.

Conceptual Data Modeling steps

Let's take a closer look at the basic data elements and relationships that should be defined in the demand analysis and conceptual design phases. Generally, demand analysis and conceptual design are completed simultaneously.

The steps for conceptual design using the ER model include:

1. Entity and attribute identification
2. Recognition of generalized hierarchies
3. Define Link

We will discuss these three steps one by one.

Entity and attribute identification

Concepts and ER diagrams of entities and attributes are very simple, but it is not easy to distinguish objects and attributes in requirements. For example, there is a sentence in the Requirement Description: "The project address is located in a city ". Is the city an entity or an attribute in this sentence? Another example: each employee has a resume. Is the resume an entity or an attribute?

To identify entities and attributes, see the following guidelines:

1. The entity should contain descriptive information
2. Multi-value attributes should be processed as objects
3. Attributes should be attached to the entities directly described by them.

These guidelines can guide developers to get a rational relational database design.

How can we understand the three principles mentioned above?

Entity content: the entity should contain description information. If a data element contains descriptive information, the data element should be recognized as an entity. If a data element has only one Identification name, it should be recognized as an attribute. The preceding "city" is used as an example. If the "city" contains descriptive information such as the country and population, the "city" should be recognized as an entity. If the "city" in the requirement only represents a city name, the "City" is used as the property affiliated with other entities, such as the affiliated Project entity. The exception to this criterion is that the value mark is a finite set that can be enumerated and should be processed as an entity. For example, define a valid national set in the system as an entity. In the real world, data elements viewed as entities include: employee, task, project, department, and customer.

Multi-value attribute: the multi-value attribute is used as an object. If a descriptor of an instance contains multiple corresponding values, the descriptor should be modeled as an entity even if it does not have its own description information. For example, a person has many hobbies, such as watching movies, playing games, and playing basketball. A hobby is a multi-value attribute for a person, and the hobby should be viewed as an entity.

Attribute attachment: attaches an attribute to the entity most directly described. For example, "office-building-name" is better than "employee" as a "department" attribute. It is a loop iteration process to recognize objects and attributes and attach them to objects.

Recognition generalization level

If there is a general hierarchical relationship between entities, put the identifiers and common Descriptors (attributes) in the super-class entities, put the same identifiers and unique descriptors in the sub-class entities. For example, the ER model has five entities: employee, Manager, engineer, technician, and secretary. Here, employee can be used as a super entity of manager, engineer, technician, and secretary. We can put the identifier empno, the common descriptor empname, address, and date-of-birth in the superclass. Put empno In the subclass Object Manager and the special descriptor jobtitle. Put empno, special descriptor jobtitle, and highest-degree in the engineer object.

Define Link

After recognizing entities and attributes, we can process the data elements that represent the links between entities, that is, the relationships. In the Requirement Description, links are generally some verbs, such as works-in, works-for, purchases, and drives. These verbs relate to different entities.

For any relationship, the following aspects need to be clarified.

1. Degree of Link (binary, ternary, etc );
2. The number of links (one-to-one, one-to-many, etc );
3. Whether the link is mandatory or optional;
4. What are the attributes of a link.

Note: For more information about these relational concepts, see database design step by step (3.

Redundancy

Analyze the redundancy relationship carefully. Two or more relationships describing the same concept are considered redundant. When the ER model is transformed into a table in a relational database, the redundant relationship may cause a non-paradigm table. Note that two or more relationships are allowed between two entities, as long as these relationships have different meanings. In this case, these relationships are not redundant.

For example, in 1, the city in which the employee lives can be different from the city of the professional-association to which the employee belongs (two meanings), so the lives-in relationship is not redundant.

(Figure 1
Non-redundant relationship)

For example, if the city where the employee in employee 2 works is the same as the city of the project where the employee participates (in the same meaning), the relationship works-in redundancy.

(Figure 2
Transmission redundancy)

Ternary relationship

We are very careful to define the ternary relationship. Only when multiple binary relationships cannot fully describe the semantics of multiple entities can we define the ternary relationship. Take technician, project, and notebook as examples.

Example 1: If a technician only works for one project and a project has only one technician, each project is recorded in a notebook independently.

(Figure 3
Example 1 binary relationship diagram)

Example 2: If a technician can have multiple projects at the same time, one project can have multiple technician participating at the same time, each project has a notebook (multiple technician working in the same project shares a notebook ).

(Figure 4
Example 2 binary relationship diagram)

Example 3: If a technician can have multiple projects at the same time, a project can have multiple technician participating at the same time, and a technician uses an independent notebook in a project.

(Figure 5
Example 3: ternary relationship diagram)

Note: For Semantic Analysis of ternary relationships, see step by step (4) in database design.

 

Suppose we want to design a database for an engineering project company to track all full-time employees, including assigned projects, skills, departments, and business units, it belongs to a professional association and is assigned a computer.

Er modeling for a single view

Through the requirement collection and analysis process, we obtain three views of the database.

The first view is the human resource management view. Each employee belongs to a department. Business Department is the basic unit of the company. Each business department contains multiple departments. Every department and business department has a manager. We need to track every manager. The ER Model 6 of this view is shown.

(Figure 6
HR relationship view)

The second view defines the titles of each employee, such as engineers, technicians, secretaries, and managers. An engineer generally belongs to a professional association and may be assigned a workstation. Secretaries and managers are assigned desktops. The company reserves some desktop computers and workstations to allocate to new employees or lend them out when their computers are being repaired. Employees may have a relationship between husband and wife, which also needs to be tracked in the system to prevent direct leadership between husband and wife employees. The ER Model 7 in this view is shown.

(Figure 7
Employee title and computer assignment view)

View 3 8 contains information about the project assigned by employees (engineers and technicians. Employees can participate in multiple projects at the same time. Each project can have headquarters in different places (cities. However, an employee can only create one local project at a specified location. Employees can select different skills in different projects.

(Figure 8
Project allocation and skill usage view)

Global ergraph

Simple Integration of the three views can produce a global ermap, as shown in Figure 9. It is the basis for constructing a paradigm-based table. Each link in the global ergraph is a verifiable asserted based on the actual data in the enterprise. These assertions are analyzed to export the conversion from the ergraph to the relational database table.

(Figure 9
Global ergraph)

From the global ER diagram, we can see the relations between binary, ternary, and binary regression. optional and mandatory relations exist. Generalized decomposition constraints. In Figure 9, the ternary relationship "skill-used" and "assigned-to" are required because the same semantics cannot be described using the binary relationship.

Optional use of the existence, where the relationship between employee and division or department is based on common sense: most employees are not managers of division or department. Another example of an optional existence is the assignment of a desktop or workstation. Each desktop or Workstation may not be assigned to a person. All in all, before converting the ER model into an SQL table, all the relationships, optional constraints, and generalization layers must be confirmed with the end users of the system.

 

In summary, applying the ER Model in relational database design brings the following benefits:

1. Using the ER model can help project members focus on discussing important relationships between entities without interference from other details.

2. The ER Model converts a large number of complex language descriptions into simplified and easy-to-understand graphical descriptions.

3. Extension of the original ER model, such as the optional and forced relations of existence and generalization relationships, enhances the ER model's ability to describe realistic semantics.

4. Converting from an ER model to an SQL table has complete rules and is easy to use.

References to the object Link (ER) Model

1. Basic entity relationship model component-entity, relation, attribute, degree of relation, connected number, property of relation, existence of entity in relation (http://www.cnblogs.com/DBFocus/archive/2011/04/24/2026142.html)

2. Components of advanced object Relationship Model-generalization, aggregation, ternary relationship (http://www.cnblogs.com/DBFocus/archive/2011/05/07/2039674.html)

Original article: http://www.cnblogs.com/DBFocus/archive/2011/06/26/2090567.html