Database ER diagram PowerDesigner

I. Overview of the conceptual data model
The data model is the abstraction of data features in the real world. The data model should meet three requirements:
1) able to realistically simulate the real world
2) Easy to understand
3) Convenient for computer implementation

The conceptual data model is also called the information model, which is based on the theory of entity-relation (entity-relationship, abbreviation e-r), and expands the theory. It models the information from the user's point of view and is mainly used in the conceptual design of the database.

Often people first abstract the real world into a conceptual world and then turn the conceptual world into a machine world. In other words, the objective object of the real world is abstracted into entity and relation (relationship), it does not depend on the specific computer system or a DBMS, this model is what we call CDM; The CDM is then converted to a data model supported by a DBMS on the computer, which is the physical data model, or PDM.

CDM is a set of strictly defined model elements that accurately describe the system's static, dynamic and integrity constraints, including 1) The data structure is expressed as entities and attributes;
2) The data operation is expressed as inserting, deleting, modifying and querying the records in the entity;
3) Integrity constraints are expressed as referential integrity constraints (such as contact, inheritance, etc.) between the data's own integrity constraints (such as data types, checks, rules, etc.) and data;


Two, the definition of entities, attributes, and identifiers
Entities (entity), also known as instances, correspond to "events" or "things" that can be distinguished from other objects in the real world. For example, every student in the school, every surgery in the hospital.
Each entity has a set of properties that describe an entity's characteristics, called an attribute, and an entity is described by several attributes. If the student entity can be composed by the number, name, gender, date of birth, department, year of entry and other attributes. The

Entity set is a collection of exactly the same types and entities of the same nature. For example, a collection of all students in a school can be defined as a "student" entity set, and each entity in the "student" entity has a character such as number, name, gender, date of birth, department, year of entry, etc. The

entity type is a collection of the common properties of each entity in the entity set, for example, the patient entity type: Patient {Outpatient number, name, gender, age, social Security number ... ｝。 An entity is an instance of an entity type, and, in the case of a clear meaning, the entity, entity type is usually used interchangeably. Each entity in the

entity type contains one or a set of properties that uniquely identify it, which is known as an identifier for the entity type (Identifier), such as an identifier for the student entity type, name, date of birth, and address together to form the identifier for the citizen entity type.

Some entity types can have several sets of properties that act as identifiers, one of which is selected as the primary identifier of the entity type, and the other as the secondary identifier.

Iii. expression of entities, attributes, and identifiers

Describes the PowerDesigner conceptual data Model and entity, attribute creation.

A new concept data model
1) Select File-->new, Pop Up the dialog box, select the CDM model (i.e. conceptual data model) to build the model.


2) Complete the creation of the conceptual data model. To demonstrate, a brief introduction to the current workspace. (More details later)


3) Select the new CDM model, right-click, select "Properties" in the popup menu and pop up the dialog box. The "General" tab allows you to enter the name, code, description, creator, version, and default chart of the model you have built. In the "Notes" tab, you can enter the relevant description and description information. Of course there are more tags, you can click on the "more>>" button, here is no longer explained in detail.


Ii. creation of new entities
1) in the CDM's graphics window, click the Entity tool on the Tools tab, click the Space in the graphics window, and a solid symbol appears where you clicked. Click the Pointer tool or right-click the mouse to release the Entitiy tool.

2) Double-click the entity symbol you just created, open the following icon window, where you can enter the name, code, description, etc. of the entity in the "General" tab of this window.


Third, add entity properties
1) on the "Attribute" tab of the above window, you can add properties as shown in.


Attention:
The features "Add attribute" and "reuse existing data items" in data items are related to the unique code and allow reuse options for data item in the model.
The P column indicates whether the property is the primary identifier, and the D column indicates whether the property is displayed in the graphics window; The M column indicates whether the property is mandatory, that is, whether the column is a null value.

If an entity property is mandatory, then this property must be assigned in each record and cannot be empty.

2) in the window shown, click the Insert Properties button, the Pop-up Properties dialog box, as shown in.

Note: This involves the concept of a domain, a standard data structure that can be applied to the properties of a data item or entity

One, the standard check constraint that defines a property
The standard check constraint is a set of expressions that ensure that a property is valid. In the Properties window of the Entity property, open the Check tab.


In this tab you can define a standard check constraint for a property, the meaning of the parameter for each item in the window, as follows
parameter description The minimum number that the minimum property can accept maximum the maximum number of values that the default property can accept is not assigned. The system provides default values for unit units, such as kilometer, Ton, and meta format properties for data display formats lowercase the assignment of the property to all lowercase letters uppercase the assignment of the property to all uppercase letters cannot modify the property once assigned can no longer modify the list Ofvalues property Assignment list, except for the value in the list, no other value label for the Label property list Value
Two, additional checks for defining attributes
when standardchecks or rules do not meet the requirements of the inspection, You can define standard and rule in SQL statements using the%minmax%,%listval%,%rules%,%upper%,%lower% variables on the server sub-page of the Additionalchecks tab,


%minmax%,%listval%,%upper%,%lower%
Minimum and maximum defined in standard check, list values, Uppervalues, Lowervalues

%rules%
Validation rule expression defined in the Expression tab of the RULES Properties window

The

One, identifier
Identifier is a collection of one or more properties in an entity that uniquely identifies an instance of an entity. It is emphasized that the identifiers in the CDM are equivalent to the primary key or candidate key in PDM.
Each entity must have at least one identifier. If the entity has only one identifier, it is the primary identifier of the entity. If an entity has more than one identifier, one is specified as the primary identifier, and the remaining identifier is the secondary identifier.

Second, if you define the primary, secondary identifier
1) Select an entity double-click the pop-up Entity's Properties dialog box. You can define the entity identifiers on the Identifiers tab. As shown in



2) Select the first row "main identifier", click the Properties button or double-click the first row "main identifier", Pop-up Properties dialog box,

3) Select the "Attributes" tab, and then click the "Add Attributes" tool, Pop-up window, select an attribute as the identifier on the line.

The

One, data item
Data item is the smallest unit of information storage that can be attached to an entity as an attribute of an entity.
Note: Data items that are not attached to any entity are allowed in the model.

Second, new data item
1) Use the "Model"---> Data Items menu to display a list of existing data items in an open window, click the "Add a Row" button to create a new data item,


2) Of course you can continue to set the code, DataType, length, and so on for specific data items. This is not explained in detail here.

Three, unique code options and reuse options for data items
Use tools--->model options->model Settings. In the Data item group box, define the unique code option for the item and the Reuse option (allow reuse).
Note:
If you select the Unique Code check box, each data item has unique codes in the same namespace, and select Allow reuse, a data item can act as a property of more than one entity.


Four, add data item to entity
1) Double-click an entity symbol to open the Properties window for that entity.
2) Click the Attributes tab to open the window as shown


Note:
Add a DataItem differs from reuse a DataItem in the case of
Add a DataItem, Select an existing data item and the system automatically copies the selected data item. If you set the Uniquecode option, the code of the new data item will automatically generate a unique number during the copy process, otherwise the data item selected is exactly the same.


Reuse A DataItem case, references are not new, they refer to data items that already exist as data items for the new entity

First, contact
Contact (relationship) refers to a connection between an entity set or an internal instance of an entity set.

Entities can be interconnected by contact. In correspondence with entity and entity sets, contact can also be divided into contacts and contact sets, which are the links between entity sets, and relations between entities, and linkages are directional. Contact and contact sets can be referred to as connections in the case of a clear meaning.

Depending on the number of instances in the entity type, you can usually divide the contact into 4 categories, one to the other, a-to-many contact, a multiple-to-a (many to a) contact, and many-to-many contacts (many to many).  

Ii. Establishment of linkages
In the CDM tool palette, in addition to the common tools, other object generation tools are included as shown.

After you create two entities in the Graphics window, click the Create contact between Entities tool, click an entity, drag the cursor to another entity while holding down the left mouse button, and release the left mouse button, creating a connection between two entities, right-clicking the graphics window, and releasing the relationship tool. As shown


three or four basic connections
That is, single-to-one contact, a-to-many contact, multi-pair (many to a) contact, and many-to-many contacts (many to many).

Iv. Other types of special links

In addition to the 4 basic links, there is a calibration link (Identify relationship) between the entity set and the entity set, a non-calibrated link (non-identify relationship), and a recursive connection (Recursive relationship).

Calibration Contact:
Each entity type has its own identifier, and if a contact occurs between two entity sets, where an identifier for one of the entity types enters another entity type and forms its identifier together with an identifier in that entity type, this contact is called a calibration contact, also called a dependency relationship. The converse is called non-calibration contact, also called non-dependent connection.
Attention:
In a non-calibrated connection, a partial instance of an entity set relies on an instance in another instance set, in which each entity must have at least one identifier. In a calibration connection, all instances of an entity set depend entirely on an instance in another entity set, in which an entity must have at least one identifier and the other entity may not have its own identifier. An entity that does not have an identifier uses the identifier of the entity it relies on as its own identifier.

In other words, in a calibration connection, an entity (elective course) relies on an entity (student), then the (student) entity must have at least one identifier, and the (elective) entity may not have its own identifier, and an entity without a marked identifier can use the identifier of the entity (student) as its identifier.



Recursive Contact:
A recursive association is a relationship between instances within an entity set, which is often visually referred to as reflexive relationships. Links between different entity sets in the same entity type are also referred to as recursive connections.

For example, there are a lot of workers in the "staff" entity, and there must be a leadership relationship between these workers. And as the entity in the "Student" entity letter contains the "monitor" child entity set and the "normal student" child entity set, the association between the two child entity sets is a recursive association. When you create a recursive contact, you only need to click the Establish contact between entities tool to drag from a part of the entity to the other part of the entity.


v. Defining the characteristics of a relationship

Once you have established a connection between two entities, double-click the contact line to open the Contact Properties window.


Vi. defining the role name of the contact
Each of the two directions in the contact contains a group box, where the parameters only work in this direction, role name is a character, describe the role of the direction of the link, generally with a verb or a dynamic table.
For example, the "Students to subject" group box should be filled in with "owning", and "belong" in the "Lessons to student" group box. (Here is just an example, maybe some words are not very reasonable).
Vii. Mandatory definition of linkages
Mandatory the mandatory relationship between the form and this direction. Select this check box to create a vertical line of contact lines on the contact line. Not selecting this check box indicates that the contact is optional in this direction and a small circle is generated on the contact line.

Viii. base of relevant links
The linkage has directionality, and there is a cardinality in each direction.

Example
The link between "department" and "student" two entities is a one-to-many connection, in other words the link between "student" and "department" is a multi-pair connection. And a student must belong to a department, and can only belong to a department, can not belong to 0 departments, so from the "student" entity to "department" the base of the entity is "a", from the other side of the contact to consider, a department can have more than one student, or no student, that is, 0 students, so the direction of the link , n ",

CDM is the first model that most developers create when using PD, and is the abstraction at the highest level of the entire database design. CDM is based on the traditional ER diagram model theory, the ER diagram has three major elements: entity type, attribute and relation. Where the entity type corresponds to entity in the CDM, the attribute corresponds to the attribute of each entity in the CDM, which is basically one by one corresponding to the concept. But in the connection, the CDM has a relatively large expansion, in addition to preserving the ER diagram of the original relationship concept, but also increased the association,inheritance two kinds of entity relationships, Let's take a look at the usage of these relationships and the difference between them (the toolbar buttons that are labeled red are used to add these relationships to the entity).

In addition, before introducing all of these CDM elements, I first give a very simple CDM diagram, is a model of our most familiar school scene, all the concepts mentioned below are reflected in the diagram, we can look at the following time to see:


I. Relationship (CONTACT)
First, the definition of contact in the PD handbook is given: "A relationship is a link between entities. For Example,in a CDM that manages human resources, the relationship Member links theentities Employee and Team, because EM Ployees can is members of teams. This relationship expresses, all employee works in a team and Thateach team have employees. " See, perhaps the concept of connection is really too simple, so it is not so good to express, so the document in PD is also an example to illustrate what happened we think the two entities are connected.
When we mention the relationship between entities, the first thing I'm afraid of is the one to One,one to many and many tomany the three types of contact, these contact types are most familiar to you. I am not proficient in the original concept of ER diagram, but in CDM, there are three other properties that can be set: mandatory (mandatory contact), dependent (dependency contact/demarcation Association), and dominant (controlled connection). These properties have a large impact on the subsequent PDM generation and need to be understood. They are all set in the Properties Control Panel of the contact, see:

1.mandatory
Whether the connection is mandatory or not, it means that the relationship between entities is not certain; in other words, when we talk about an application scenario of a connection, the number of entity instances that correspond to the two entity types cannot be zero. Perhaps this explanation is still a bit abstract, let us give two examples of the connection, one for both sides of the entity is mandatory, and the other is not.
(1) Teacher-student Contact
This connection is first and foremost a many-to-many connection, because each teacher can teach multiple students, each student also has a number of teachers to take charge of their studies. At the same time, this connection is mandatory for teachers and students, that is, there is no teacher, he is not responsible for any student's teaching; there is no student, he does not have any teacher.
(2) Students--club contact
This link is also a many-to-many relationship, but it is not mandatory for students of this entity type (Optional, optional). Each club has at least one student participating, but not every student is going to the club. There could be some students who didn't attend any of the clubs.
The above example distinguishes between mandatory and optional from a conceptual point of view. In fact, if the model corresponds to our last generated table, if the A-B connection to a is mandatory, then if inside a, if it contains a foreign key, the foreign key can not be null, and vice-versa may be a null value. When we talk about PDM and the actual database, you will see this.
2.dependent
Each entity type has its own identifier, and if there is an association between the two entity types, When one of the entity type identifier enters another entity type and composes its identifier together with the identifier in the entity type, this association is called the Calibration Association, also called the Dependency association ( dependentrelationship). When an entity type identifier enters another entity type and acts as its non-identifier, this association is called a Non-calibration association, also called a non-dependency association.
The definition of the concept is still a bit of a mouthful, plainly speaking is actually the main-from the table relationship, from the table to rely on the main table. For example, in our system to record teacher leave, there is an entity-type holiday, whose attributes include the start time and the number of days of vacation, every time there is a teacher leave, you have to leave a record in this table. As can be seen from our scenario description, Entity holidays must be attached to a physical teacher, that is, for each vacation instance, it must point to a teacher instance.
For a dependent connection, it must be noted that it cannot be a many-to-many connection, in which there must be an entity type as the subject. A dependent contact from the entity can have no own identifier.
3.dominant
This contact attribute is the simplest, it is used only for one-to-one contact, and indicates the principal-subordinate table relationship in this connection. In the connection of a, B, two entity type, if A-->B is specified as dominant, then A is the primary table for this one-to-one connection, and the second is from the table, and a reference is generated in the later generation of PDM (two references are generated if the dominant attribute is not specified). such as the teacher and the relationship between the class, because each class has a teacher to do class, each teacher also can only do a class head teacher, so is a one-on relationship. At the same time, we can use the teacher as the main table, with the teacher's work number to determine the only one teacher contact.

Two. Association (association)
Let's take a look at the definition of PD for association: "An association is a connection between entities. In Themerise modeling methodology An association are used to connect severalentities so each represents clearly defined O Bjects, but is linked Byan event, which May is not being so clearly represented by another entity. "
In the relationship mentioned in the previous paragraph, in many cases (especially in many-to-many relationships), we will make contact specifically, as an entity type in the middle of two entities that need to be associated (in PD, select any one of the links, and in the right-click Pop-Up menu, select "Change Toentity "command to complete the operation of the contact to entity. Sometimes, however, it may not be appropriate to abstract a relationship between several entities into a single entity, and at this point you can choose to create a association for these entities, and then, when you generate PDM, All of these related entity-type identifier are added to the association corresponding to the resulting table model. So, frankly speaking, association is actually a special case of the entity type, which is used to more accurately express the association information between entities when modeling. In the PD document cited a tape, a customer, a store three entities were associated on the scene of the rental of tapes, and then the lease was defined as an example of association between the above three entity types, very exact. In our school model, I define home visits as a association between the teacher and the student entity, and in the next generation of PDM you may see the effect of this definition.

Three. Inheritance (inheritance)
This kind of relationship is the most easy to understand at the conceptual level, this article does not repeat.

The main content of the relationship between entities in the CDM is described in the previous section, so let's take a look at what the PDM generated under this CDM looks like:

All of the red parts are the ones we should be most concerned about because they are all due to our definition of the relationship between entities, and here are some simple explanations.
1. The two tables "teacher-student relationship" and "Student Club" are due to our many-to-many relationships.
2. The "Work number" field in the "Holidays" table is due to the fact that we have specified the teacher-holiday relationship as dependent.
3. The "Work number" field of the "Class" table is due to the fact that we have established the teacher-class relationship as a dominant.
4. The "Work number" and "study number" fields in the "home visits" table are the result of the association of the teacher and student entity type.
Also, remember that when we mentioned the dominant attribute, we said that a one-way connection without a dominant direction would produce two references, and we would make a minor change to the teacher-class relationship in the original CDM, Remove the dominant definition of this relationship, then the teacher and class tables in the resulting PDM will contain each other's primary key (because our class table does not have its own primary key, so we can only see the columns in the Class table), as follows:

Forward: http://www.360doc.com/content/11/0624/15/2617151_129276457.shtml#

Database ER diagram PowerDesigner