Database design step by step (4) -- Advanced ER model component

Introduction: Database Design
Step by step (3) -- the basic entity-link model component and its semantics are discussed in the basic ER model component. These concepts are very important and are the basis of today's lecture. Before starting this article, we suggest you review the previous article. Today, we will discuss the components of the advanced object link model, which, together with the previous article, covers most of the content of the ER Model diagram. The ternary relationship is the difficulty of today's speech. You can focus on it.

 

Generalization: Super type and subtype

The original ER model can already describe basic data and relationships, but the introduction of the general concept facilitates the integration of multiple conceptual data models.

A generalized relationship is used to extract the common attributes of multiple entities as superclasses. Low-level entities in generalized hierarchies-subtypes: Inherit and add attributes in super-class entities, and subtypes are specialized in supertypes.

The generalization in ER model is similar to the inheritance concept in object-oriented programming, but its markup method (composition method) is somewhat different.

General Relationship between employees and managers, engineers, technicians, and secretaries. Employee is a super-Class Object and contains common attributes. Manager, engineer, technician, and Secretary are all sub-class objects of employee. They can contain their own unique attributes.

Figure 1
Generalized relationship between employee and manager, engineer, technician, and Secretary

Generalization can express two important constraints of child types,Disjointness)AndCompleteness).

Overlapping ConstraintsIndicates whether the child types are exclusive. If exclusive, use the letter "D"; otherwise, use the "O" logo (o-> overlap ). In Figure 1, each subclass object is exclusive in concept.

Generalize the employees and customer entities and abstract super entity individuals to obtain the following relationship diagram. Because some of the employees may also be customers, the concepts of the sub-class Object "employee" and "customer" overlap.

Figure 2
General Relationship between individual, employee, and customer

Completeness ConstraintsIndicates whether all child types can completely overwrite the super type in the current system. If full coverage is possible, double lines are marked between the super type and the circle (two lines can be understood as equal signs ). In Figure 2, the sub-class Object "employee" and "customer" can completely overwrite the super-class "individual" entity.

Aggregation)

Aggregation is another super-type and sub-type abstraction different from generalized abstraction.

Generalization indicates the semantics of "is-a", and aggregation indicates the semantics of "part-. There is no inheritance relationship between the aggregated neutron type and the supertype.

The markup of an aggregate relationship is represented by the letter "A" in the circle.

Indicates that a software product consists of a program and a user manual.

Figure 3
Aggregation relationship between software-product, program, and user's guide

Ternary relationships)

When the relationship between three entities cannot be accurately described through the binary relationship, we need to use the ternary relationship.

How to determine the connected number in a ternary relationship:

A) Take one entity in a ternary relationship as the center. Assume that the other two entities have only one instance.

B) if only one instance of the central entity can be associated with one instance of the other two entities, the connection number of the central entity is "1"

C) If more than one instance of the central entity can be associated with the other two entity instances, the number of connections of the central entity is "multiple"

Note: When to use an instance with a ternary relationship, see "degree" in step by step (3) of database design.
Of a relationship) "section. For the concept of "connected number" of a link, see "connected number (connectivity) in database design step by step (3 ).
Of a relationship) "section.

Let's take a look at several three-element relationship instances.Degree of LinkAnd understand the semantics.

Figure 4
Relationship between technicians using manuals in projects

The Semantics in Figure 4 is:

A) A technician uses a manual for each project

B) Each manual belongs to a technician for each project

C) A technician may be working on multiple projects and maintain different manuals for different projects.

Use the function dependency in mathematics to represent the relationship in Figure 4:

A) EMP-ID, project-name-> notebook-No

B) EMP-ID, notebook-no-> Project-name

C) Project-name, notebook-no-> emp-ID

Figure 5
Relationship between projects assigned to employees in different locations

The Semantics in Figure 5 is:

A) each employee can be assigned only one project in one location, but different projects can be performed in different locations.

B) in a specific location, an employee can only work on one project.

C) in a specific place, a project can be implemented by multiple employees.

Use the function dependency in mathematics to represent the relationship in Figure 5:

A) EMP-ID, Loc-name-> Project-name

B) EMP-ID, project-name-> loc-name

Figure 6
Relationship between managers in managing projects and engineers

The Semantics in Figure 6 is:

A) one engineer under a manager may be involved in multiple projects.

B) a project managed by a manager may have multiple engineers.

C) an engineer working on a project only has one manager.

Use the function dependency in mathematics to represent the relationship in Figure 6:

A) project-name, EMP-ID-> Mgr-ID

Figure 7
Relationship between employees using skills in projects

The Semantics in Figure 7 is:

A) An employee can use multiple skills in a project.

B) one employee's skill can be used in multiple projects

C) A skill can be used by multiple employees in a project.

Figure 7 no function dependency between entities

In the preceding four forms of ternary relationships, the number of entities with a connection number of "1" is consistent with the number of functional dependency semantics reflected by the ternary relationship.

The ternary relationship can also have attributes. The attribute value is uniquely determined by the combination of the keys of the three entities.

 

General n-ary relationships)

The ternary relationship can be extended to the N meta relationship to describe the relationship between N entities.

Generally, the keys of each object with a connection number of "1" in the n-element relationship appear on the right of a function dependency expression.

It is relatively difficult to use language to express the constraints of the N-element relationship. We recommend that you use the mathematical form, that is, function dependency (FD.

The number of function dependent entries in the n-element relationship is the same as the number of "one" objects in the graph (0 ~ N ).

The function dependency expression of the N-element relationship contains n elements, n-1 elements appear on the left side of the expression, and 1 element appears on the right side.

Figure 8
N-element relationship

 

Exclusion Constraint)

Generally (by default), multiple relationships are compatible with "or" relationships, that is, any or all entities are allowed to participate in these relationships.

In some cases, a multi-link is not a "or" link. That is, the entity that participates in a link can only select one of these relations, but cannot select multiple links at the same time.

It indicates that a job is either classified as an external project or an internal project, and cannot belong to both an external project and an internal project.

Figure 9
Exclusive Constraint

 

We will make a general review of the previous database design step by step (3) and the key content in this article.

1. We have discussed the basic concepts of the ER Model and diagram.

2. An entity can be a person, place, thing, or event

3. the attribute is the description of the object.

4. attributes can be unique identifiers or non-unique descriptions.

5. Links describe the links between objects, such as "one-to-one", "one-to-many", and "many-to-many ".

6. The degree of the relationship reflects the number of entities involved in the relationship, such as binary, ternary, and n-element.

7. A Role (name) defines the functions of an object in a link.

8. The concept of link existence indicates whether an object is mandatory or optional in a link.

9. generalization allows the entity to be abstracted into superclasses and subclasses.

10. You can use function dependencies to define a Trielement relationship.

Original article: http://www.cnblogs.com/DBFocus/archive/2011/05/07/2039674.html