Relationship types in erwin. General principles of work in erwin. Building Models in ERwin

Lab #5

Objective:

Exercise:

Work sequence

Getting to know the user interface

· Download the Erwin program.

In the dialog box that appears, select the radio button Create a New Model. A dialog will appear on the screen. Create Model - Select Template, where you want to select the simulation level.

Erwin has two levels of simulation: logical and physical. On the logical level, the data is presented as it would appear in the real world. Logical level objects are entities and attributes.

On the physical level, the model depends on the particular implementation of the database chosen by the user. When the model moves to the physical layer, entities are transformed into tables, and attributes into fields, so all names and descriptions of the physical model must comply with the conventions adopted for the selected DBMS.

Install switch Logical/Physical to create a model with logical and physical layers.

In the fields DataBase and version specifies the type and version of the server for which the model is being created. Select Access, 2000 from the list. Click OK.

· The main program window will appear on the screen.

At the top of the window is the title line, which contains the name of the program, the name of the model, the name of the subset (Subject Area) and the stored display (Stored Display). The main part of the program space is occupied by the workspace in which the ER diagram is created.

To switch between the logical and physical levels, there is a list on the toolbar (Fig. 1.1).

In addition to this list, there are buttons on the toolbar (see Table 1.1).

Table 1.1.

Buttons located on the toolbar of the Erwin program

Button	Purpose
	Create, open, save and print a model
	Calling the Report Browser Dialog to Generate Reports
	Model View Level Change: Entity Level, Attribute Level, Definition Level
	Change the scale of the model view
	DB schema generation, schema alignment with the model and server selection (only available at the physical model level)
	Switching between areas of the Subject Area model

For direct work with model elements, the program has a tool palette (Erwin Toolbox), which is a “floating window” (Fig. 1.2). If necessary, the tool palette can be removed from the screen and called up by pressing the CTRL-T key combination.

Rice. 1.2. Tool palette at the logical level

Introducing Entities into the Model

At this stage, it is necessary to introduce the following entities into the model, identified as a result of the analysis of the subject area (delivery of goods in accordance with contracts): buyer, contract, invoice, goods, warehouse.

Select on the toolbar (ERwin Toolbox) the button Essence by clicking on it with the mouse pointer. Then click on the place on the diagram where you want to place the new entity. A rectangle will appear on the diagram field, depicting a new entity, with an automatically generated name "E/1".

Enter the name of the entity " Customer" and press Enter.

· In the same way, insert four more entities into the diagram: contract, invoice, product, warehouse.

By right-clicking on the entity and selecting the item from the context menu Entity Properties, you can call the entity editor Entities(Fig. 1.6), which allows you to change the properties of the selected entity. The entity editor can also be called from the main menu: Model | entities.

Rice. 1.6. Entity editor

In the upper part of the editor window there is a list of all entities present in the diagram. With it, you can select the entity whose properties you want to view or change. By default, the selected entity in the diagram is the entity that is clicked on. Next, there is the Name field, which displays the name of the entity. The name can be edited.

Below in the editor window are a number of tabs:

Definition(definition) - on this page you enter the definition of the entity.

Note, Note2, Note3(note) - used to enter arbitrary text associated with the entity, such as sample data and queries.

UDP– user-defined properties.

icon(icon) - for clarity, each entity can be assigned an icon that is displayed next to its name.

For each entity, enter a definition definition.

Key groups

・Invoke keygroup editor Key Groups, by right clicking on an entity Customer and selecting from the context menu Key Groups. The keygroup editor can also be called from the main menu: Model | key groups.

Keygroup editor contains controls:

entity– a field with a drop-down list in which you should select an entity to edit.

Window with a list of key groups. Each group is represented by a separate line, including the name (Key Group), type (Type) and definition (Definition).

In addition, the keygroup editor dialog contains the following tabs:

ü Members (members). Members of key groups and their order in the group are specified.

ü General (general settings). Switches that allow you to set the type of key group. For primary and foreign keys, these groups are not available.

ü Definition (definition). Arbitrary textual information related to the selected key group.

ü Note (note). Note to the selected group.

ü UDP (custom properties).

・Click button New.

· In the window New Key Group in field Key Group enter key group name − TIN. In field Index the Erwin-generated index name is displayed. Leave it unchanged.

switch Key Group Type specifies the type of the generated key. This can be an Alternate Key or an Inversion Entry. Select Alternate Key and press OK. The newly entered alternate key will appear in the list of keys.

Go to bookmark Members. The new key does not yet contain any attributes, so the right list Key Group Members(members of the key group) is empty. Select an attribute from the list on the left TIN and move it to the right list using the arrow button (see Fig. 1.8).

Rice. 1.8. Key Group Editor

· In the same way, create the key groups for the inverse inputs shown in Table. 1.3.

Lab #6

Specifying Declarative Referential Integrity Rules

· Being on a logical level of the data model, select the relationship “concludes” between the entities Buyer and Contract by clicking on it with the mouse pointer. Then press the right mouse button and select the item in the context menu Relationship Properties(link editor).

In the link editor window relationship go to tab R.I. Actions. Familiarize yourself with the referential integrity rules for the relationship "Buyer - Contract", assigned by default. The setting data prevents insertion and modification of a child entity instance, as well as deletion and modification of the parent entity. This means that it is not allowed to delete or change a buyer if the database contains contracts concluded with him, as well as entering a contract without specifying a buyer or with reference to a non-existent buyer. Thus, we fulfilled the condition according to which the contract can exist only for a specific buyer.

· Review the established referential integrity rules for all other links.

The default rules assigned to a link can be changed by selecting the desired value from the drop-down list.

Data normalization

It can be seen from the model that there is a multiple attribute TEL in the Buyer entity. A customer can have multiple phone numbers, which is a violation of first normal form, which requires all attribute values ​​to be atomic. Therefore, it is necessary to separate the TEL attribute into a separate entity.

· Create the Phone entity containing the following attributes: TEL_CODE (primary key, type - number) and TEL (type - string).

· Associate the entities Customer and Phone with an identifying relationship. Set link power - One or More (P) and enter the connection name - It has.

Server selection

Execute a command database | Choose Database.

· In the dialog box Erwin/ERX - Target Server you need to set the server type - Access and its version 2000 . It also specifies the default data type and NULL condition for newly created columns. Some of the options in this dialog box depend on the selected server type.

· After selecting the server, press the button OK.

Data denormalization

There are two many-to-many relationships in the model: Item - Contract and Item - Invoice, which must be resolved at the physical level. The result of resolving these links is presented in Table. 2.1.

Table 2.1.

Result of resolving many-to-many relationships

The resolution of many-to-many relationships is carried out automatically when moving to the physical layer, or using a special wizard Many Relationship Transform Wizard.

· To call this wizard, select the link “Goods – Contract” by clicking on it with the mouse pointer. Then press the right mouse button and select the item in the context menu Create Association Table(create an associative table). The first dialog of the wizard will appear on the screen, containing text about its purpose.

Enter in the field table name(table name) - Delivery_Plan. In field Table Comment(comments to the table) enter the text: Information about the supply of goods under the contract.

· A new table Delivery_Plan appeared on the model, connected by an identifying relationship with the Goods and Contract tables.

· The new table should be supplemented with three columns (see Table 2.1). To do this, select the table Delivery_Plan by clicking on it with the mouse pointer. Then press the right mouse button and select the item in the context menu Columns ( column editor) . Working with this editor is similar to working with the attribute editor.

· Independently enter three new columns in accordance with the table. 2.1.

· Using the method described above (using the wizard), transform the relationship "Goods - Invoice" and supplement the resulting associative table Shipment with two columns according to Table. 2.1.

Setting validation rules

Specifying a List of Valid Values

In accordance with the subject area under consideration, for the RATE_VAT field of the Product table, let's set a list of valid values: 0, 10 and 18%.

Columns.

· In the editor window in the field Column- VAT RATE.

· Go to the tab of the selected DBMS – Access.

· Valid.

in dialogue Validation Rules click the button New.

in dialogue New Validation Rule in field Logical enter the name of the rule − Checking the VAT rate. Click the button OK.

・Go to bookmark General. In Group type set option Valid Value List.

· In field Valid Value on the first line, enter 0. On the second and third lines, enter the values: 10 and 18.

· Check that at the top of the editor window Validation Rules line appeared: Checking the VAT rate(Validation Name) IN (0, 10, 18)(Validation Rule).

・Click OK. In the editor window columns on the bookmark Access in field Valid the name of the created rule appeared - “Checking the VAT rate”.

Setting Default Values

Let's create a rule according to which the value of the current date will be substituted in the DATE_DOG field of the Agreement table by default.

Call the context menu of the Agreement table and select the item Columns.

· In the editor window in the field Column select the column for which the rule will be set – DATE_DOG.

・Bookmarked Access click on the button located to the right of the drop-down list Default.

· In the dialog box Default/Initial Values click the button New.

in dialogue New Default Value in field Logical enter the name of the rule − The current date. Click the button OK.

・Bookmarked Access in field Server Value - Access Default enter Date()(a function that receives the value of the current date).

・Click OK. In the editor window columns on the bookmark Access in field Default the name of the created rule appeared - “Current date”.

· Set the same rule for the DATE_SHIPMENT field of the Invoice table. To do this, in the column editor window Column select the field DATE_OTGR and on the Access tab in the field Default select a rule from the drop-down list The current date.

Specifying Input Validation Rules

Let's create a rule for checking input values ​​for the PRICE field of the Product table, according to which this field cannot have values ​​less than 0.

Call the context menu of the Product table and select the item Columns.

· In the editor window in the field Column select the column for which the rule will be set – PRICE.

・Bookmarked Access click on the button located to the right of the drop-down list Valid.

in dialogue Validation Rules click the button New.

in dialogue New Validation Rule in field Logical enter the name of the rule − Price check. Click the button OK.

・Go to bookmark General. In Group type set option min/max.

· In field Min enter 1. In addition to the lower limit of the value range, here you can also set the upper limit ( Max).

At the top of the editor window Validation Rules a newly created one was added to the list of validation rules: Price check >=1.

・Click button OK.

Lab #7

Database size calculation

Objective:

To master the methodology for calculating the size of the database, implemented in Erwin.

Lab #8

Creating reports in Erwin

Objective:

study of types of reports;

· Learn how to create reports

Lab #5

Basics of working at Erwin. Building a logical data model

Objective:

· mastering the skills of working in Erwin;

· construction of a logical model of a given subject area.

Exercise:

Build a logical information model for the supply of goods in accordance with contracts using Erwin tools.

Laboratory work №4. Defining relationships between entities in ERwin

As noted in Exercise 3, to build a logical data model, first of all, it is necessary to define a set of entities and define relationships between them. In Exercise 3, a diagram was created containing four entities (Figure 4.1). We will consider the methodology for determining the relationship between them in the current exercise.

https://pandia.ru/text/78/177/images/image002_182.gif" width="123" height="128 src=">the default generated verb phrase is “R/1” (Figure 4.2).

Figure 4.2 - Non-identifying relationship

Step 3 Go to the attribute level and notice that the “Learning place” entity has added a primary key attribute from the “Class” entity and is marked with the letters “FK”. The attribute is said to have "migrated", and FK (foreign key) means that the attribute is part of a foreign key (Figure 4.3). For an identity relationship, the foreign key is always included in the primary key of the child.

entity, for non-identifying is not included.

Figure 4.3 - Migration of attributes

Step 4 Assign links to a verb phrase. To do this, select the relationship by clicking on it with the mouse pointer, then press the right mouse button and select the "Relationship Properties ..." item in the context menu.

The general view of the link editor window is shown in Figure 4.4.

Databases" href="/text/category/bazi_dannih/" rel="bookmark">databases . In our example, since we found out during the analysis of the subject area that the study place cannot exist separately from the class, set this ne - the switch to the “No Nulls” position, which imposes the condition that an existing workplace instance must always have a reference to the class it belongs to.

Bookmark « Definition»(definition).

On this page, you enter a relationship definition. The text of the relationship definition, as in the case of an entity, depends on the standards adopted by the enterprise, and should facilitate the perception of the model.

Rolename tab

The role name (rolename) is an optional characteristic that can

be assigned to a migrating primary key attribute (Figure 4.5).

https://pandia.ru/text/78/177/images/image006_79.gif" width="358" height="221 src=">

Figure 4.6 - Diagram context menu for displaying migrating entity attributes

RI Actions Tab (Referential Integrity Settings)

The tab is intended for setting the parameters of the referential integrity of the project

the database being processed (figure 4.7).

Referential integrity assertions are logical constructs that express business rules for using data. They determine what actions the DBMS should perform when deleting, inserting or changing a table row (entity instance). Actions defined in this way can be used later in the automatic generation of triggers that maintain data integrity.

There are the following types of actions or rules defined in the logical model:

1 RESTRICT - prohibition of deletion, insertion or modification of an entity instance

2 CASCADE - when deleting an instance of the parent entity, deleting all instances of the child entity that refer to the parent entity being deleted.

3 SET NULL - when an instance of the parent entity is deleted, the foreign key attributes of all instances of the child entity are set to NULL.

4 SET DEFAULT - the same as in the previous case, but instead of the NULL value, the default value is assigned.

5 NONE - no action is taken.

Figure 4.7 - Tab "RI Actions" (Settings of referential integrity)

These rules are set for inserting, deleting, and modifying an instance of both parent and child entities. Thus, each connection must have a set of six rules that are entered in the fields, united by the general heading "RI Actions". When adding a link to a diagram, ERwin by default sets a set of rules for it, which can be edited in the "Model Properties" dialog on the "RI Defaults" tab (Figure 4.8), calling

by selecting the “Model” Server command from the main menu and, further, the sub-

"Model Properties" mands (figure 4.9).

https://pandia.ru/text/78/177/images/image009_57.gif" width="227" height="289 src=">

Figure 4.9 - The order of calling the dialog box "Model Properties"

The default rules assigned to a link can be changed by selecting the desired value from the drop-down list (see figure 4.8). When you click on the "Re-bind" button (reassign), the new default settings are transferred to the current mode.

Del, if you just exit the dialog without making a reassignment, then the changed settings will only affect new models.

Each type of connection has, depending on the type of action, its own set of valid rules, shown in Table 4.2.

Table 4.2 - Set of valid rules for different types of links

actions	Relationship Type
Identification	Unidentified ruyuschaya (Non-Identifying, Nulls	Unidentified non-identifying (Non-Identifying, No	naya connection
(deleting child object)			CASCADE, NONE SET NULL,	CASCADE, NONE SET DEFAULT
(insert child object)			CASCADE, NONE SET NULL,	CASCADE, NONE SET DEFAULT
(change child object)			CASCADE, NONE SET NULL, SET DE - FAULT	CASCADE, N6NE SET
(deleting the parent object)			CASCADE, NONE SET	CASCADE, NONE SET
(insert parent object)			CASCADE, NONE SET NULL,	CASCADE. NONE SET DEFAULT
(change parent object)			CASCADE, NONE SET	CASCADE, NONE SET

The default settings for the "Class - School Place" relationship prohibit the insertion and modification of a child entity instance, as well as the deletion and modification of the parent entity. This means that deletion is not allowed.

or changing the class if it has places, as well as entering a place without class or with a reference to a non-existent class. Thus, we have fulfilled the condition according to which a learning place can exist only as part of a class.

UDP tab (User-defined parameters)

The tab - UDP, like the previous diagram objects, allows you to assign your own set of custom properties to the connection.

So, we have created a non-identifying relationship between the entities "Class" and

"Training place" with the condition "No Nulls". Obviously, the relationship of the same type must exist between the entities "Type of equipment" and "Equipment item", since a piece of equipment must have a type. Enter this relationship in the diagram by following the same steps as in the previous case. Call up the link editor and change the verb phrase to "describes", leave the rest of the link settings unchanged. Please note that the “equipment type code” attribute has migrated to the composition of the non-key attributes of the “Learning Place” entity (Figure 4.10).

Figure 4.10 - The "equipment type code" attribute migrated to the composition of non-key attributes of the "Learning place" entity

Let us now consider the relationship between the entities "Training place" and "Equipment item". As we found out when examining the subject area, pieces of equipment form a certain fund of components, some of which are installed in training places. Another part of the components may be in stock, be faulty and wait for decommissioning, etc., that is, exist separately from the training place. Thus, the entities "Training place" and "Equipment" do not depend on each other, and must be associated with a non-identifying relationship.

Step 5 Select a non-identifying relationship from the tool palette and bring it into the diagram by selecting "Study Location" as the parent entity, and

"Unit of equipment" - a child. In the link editor, change the verb phrase "Parent-to-Child" to "consists of". A non-identifying relationship has two varieties - allowing NULL values ​​(Nulls Allowed) and not allowing (No Nulls). The Nulls Allowed variant is selected by default, leave this as it is. This setting means that for an instance of the entity "Equipment item" the foreign key fields can have a null value, i.e.

there is no indication of a copy of the "Study Place". Thus, a piece of equipment can exist "on its own".

After establishing the links, the diagram at the entity level will look like,

https://pandia.ru/text/78/177/images/image012_32.gif" width="556" height="327 src="> links shown in Figure 4.12.

Figure 4.12 - Image of links in IDEF1X notation

In addition to the type of connection, the diagram can also display the settings of the reference value.

flatness - to do this, select the item in the chart context menu

"Relationship Display" and sub-item "Referential Integrity" ( Step 6. ).

Alphabet" href="/text/category/alfavit/" rel="bookmark">alphabetic groups separated by a colon ":". The first character indicates the action to which the integrity rule applies: D - delete, I - insert (insert), U - change (update).

The second group denotes the rule: R - RESTRICT, C - CASCADE, SN - SET NULL, SD - SET DEFAULT. Thus, preventing deletion is denoted by D:R, and setting NULL on change is denoted by U:SN. Designations are put down at the parent or child end of the relationship, depending on which entity they refer to. With the referential integrity settings turned on, the diagram will

looks as shown in figure 4.14.

Figure 4.14 - ER diagram with referential integrity settings enabled

The diagram now defines one of the object areas, of which we have identified four - the material support of the learning process. Let's look at other object areas in later exercises.

Step 7 On the Entity Layer tab, save the model, for example, under the name

German Lab_4_Petrov. er1.

Step 8. Perform an individual task to determine the links between the

data in ERwin for the specified subject area (see Table 3.4).

1. The results of the steps 1 - 7 of the lesson program.

2. The results of the individual task.

Control questions

1. How are dependent and independent entities distinguished in an ERwin diagram?

2. What relationship between entities is called non-identifying?

3. What is the physical and logical data model?

4. What relationship between entities is called identifying?

5. Explain the meaning of the statement that some attribute "migrated"?

6. What does the "FK" symbol mean on an ERwin diagram?

7. What are the features of the link editor?

8. What is the representation of links in IDEF1X notation?

9. How is the designation of referential integrity in the diagram

10. What relationships between entities were used when performing indie

visual task?

The creation of modern information systems is a very difficult task, the solution of which requires the use of special techniques and tools. It is not surprising that recently among system analysts and developers there has been a significant increase in interest in CASE (Computer-Aided Software / System Engineering) - CASE technologies and tools that allow you to systematize and automate all stages of software development as much as possible.

The book offered to the reader is a practical guide to creating information systems using effective analysis, design and code generation tools from PLATINUM technology - BPwin and ERwin. It also contains a description of the methods of structural analysis and the design of data models to the extent necessary for practical work. The application of the methods is illustrated by examples.

The book is written on the basis of the author's personal experience gained while developing information systems, giving lectures and conducting practical classes on CASE-technologies and CASE-tools at the Training Center of "Interface Ltd." It is addressed to specialists in the field of information technology: system analysts, project managers, developers - and may also be useful for undergraduate and graduate students studying the basics of system analysis and information systems design.

Book:

A relationship is a logical relationship between entities. Each relationship should be called a verb or a verb phrase (Relationship Verb Phrases) (Fig. 2.20). The relationship name expresses some constraint or business rule and makes the diagram easier to read, for example:

Each CLIENT <размещает> ORDERS;

Every order <выполняется> EMPLOYEE.

Rice. 2.20. Relationship Name - Relationship Verb Phrases

The connection shows exactly which orders the customer has placed and which employee is fulfilling the order. By default, the link name is not shown on the diagram. To display the name, use the context menu that appears if you left-click on any place in the diagram that is not occupied by model objects, select the Display Options/Relationship item and then enable the Verb Phrase option.

At the logical level, you can set a one-to-many identifying relationship, a many-to-many relationship, and a one-to-many non-identifying relationship (respectively, these are the buttons from left to right in the tool palette).

IDEF1X distinguishes between dependent and independent entities. The type of an entity is determined by its relationship to other entities. An identifying relationship is established between the independent (parent end of the relationship) and dependent (child end of the relationship) entities. When an identifying relationship is drawn, ERwin automatically converts the child entity into a dependent entity. A dependent entity is represented by a rectangle with rounded corners (entity Order in fig. 2.21). An instance of a dependent entity is defined only through a relation to the parent entity, i.e., in the structure in Fig. 2.21 Order information cannot be entered and is meaningless without information about the customer who places it. When an identifying relationship is established, the attributes of the primary key of the parent entity are automatically transferred to the primary key of the child entity. This operation of adding attributes to a child entity when creating a relationship is called attribute migration. In the child entity, new attributes are marked as foreign key - (FK).

Rice. 2.21. Identifying relationship between independent and dependent table

In the future, when generating the database schema, the primary key attributes will be marked as NOT NULL, which means that it is impossible to make a record in the order table without information about the customer number.

When a non-identifying relationship is established (Figure 2.22), the child entity remains independent, and the primary key attributes of the parent entity migrate to the non-key components of the parent entity. A non-identifying relationship is used to link independent entities.

Rice. 2.22. Non-identifying relationship

Entity instance Employee can exist regardless of any entity instance The Department, i.e., an employee can work in an organization without being listed in any department.

An identifying relationship is shown in the diagram as a solid line with a bold dot at the child end of the relationship (see Figure 2.21), a non-identifying one is dashed (Figure 2.22).

To create a new connection:

place the cursor on the desired button in the tool palette (identifying or non-identifying relationship) and press the left mouse button (Fig. 2.2);

click first on the parent and then on the child entity.

You can change the shape of the link line. To do this, you need to grab the desired connection line with the mouse and move it from place to place until the line starts to look better.

Button on the tool palette

Corresponds to identifying link, button

Many-to-many relationships and a button

Correspond to a non-identifying relationship.

To edit the properties of a relationship, right-click on the relationship and select Relationship Editor from the context menu.

In the General tab of the dialog that appears, you can set the capacity, name and type of connection (Fig. 2.23).

Communication power (Cardinality) - is used to indicate the ratio of the number of instances of the parent entity to the number of instances of the child.

There are four types of power (Fig. 2.24):

the general case, when one instance of the parent entity corresponds to 0, 1, or many instances of the child entity, is not marked with any symbol;

the symbol P marks the case when one instance of the parent entity corresponds to 1 or many instances of the child entity (zero value is excluded);

the symbol Z marks the case when one instance of the parent entity corresponds to 0 or 1 instance of the child entity (multiple values ​​are excluded);

the number marks the case of exact match, when one instance of the parent entity corresponds to a predetermined number of instances of the child entity.

Rice. 2.23. Relationship Editor Dialog

By default, the link power symbol is not shown on the diagram. To display the name, use the context menu that appears if you left-click on any place in the diagram that is not occupied by model objects, select the Display Options / Relationship item and then enable the Cardinality option.

Relationship Name (Verb Phrase)- a phrase characterizing the relationship between parent and child entities. For a one-to-many identifying or non-identifying relationship, it is enough to specify a name that characterizes the relationship from the parent to the child entity (Parent-to-Child). For many-to-many relationships, both Parent-to-Child and Child-to-Parent names should be specified.

Rice. 2.24. Power designations

Link type (identifying/non-identifying). For a non-identifying relationship, you can specify mandatory (Nulls). In the case of a mandatory relationship (No Nulls), when generating the database schema, the foreign key attribute will receive the NOT NULL attribute, despite the fact that the foreign key will not be included in the primary key of the child entity. In the case of an optional relationship (Nulls Allowed), the foreign key can be NULL. An optional non-identifying relationship is marked with a transparent diamond on the side of the parent entity (see Figure 2.22).

Rice. 2.25. The Rolename/RI Actions tab of the Relationship Editor dialog

In the Definition tab, you can give a more complete definition of the connection in order to be able to refer to it in the future.

In the Rolename/RI Actions tab, you can set the role name and referential integrity rules.

Role name (functional name) - it is a synonym for the foreign key attribute, which indicates what role the attribute plays in the child entity.

Rice. 2.26. Foreign key role names

In the example shown in fig. 2.26, in essence Employee external key Department number has a functional name "Where Works", which indicates what role this attribute plays in the entity. By default, only the role name is shown in the attribute list. To display the full name of an attribute (both functional name and role name), use the context menu that appears when you left-click anywhere in the diagram that is not occupied by model objects, select the Display Options / Entities item and then enable the Rolename / option. Attribute (fig. 2.25). The full name is shown as the functional name and the base name, separated by a dot (see Figure 2.26).

It is mandatory to use role names when two or more attributes of the same entity are defined in the same scope, i.e. they have the same scope but different meanings. On fig. 2.27 entity Sale of currency contains information about the act of currency exchange, in which two currencies are involved - sold and bought. Information about currencies is contained in the entity Currency. Therefore, entities Sale of currency and Currency must be linked twice and primary key - Currency number should migrate twice to entity Currency as a foreign key. It is necessary to distinguish between these attributes, which contain information about the number of the sold and bought currency (have a different meaning), but refer to the same entity Currency (have a common range). In the example in fig. 2.27 attributes got role names sold and Bought.

Rice. 2.27. The Case of Mandatory Role Names

Another example of mandatory assignment of role names are recursive links(sometimes called "fish hook" - fish hook), when the same entity is both parent and child at the same time. When defining a recursive relationship, the attribute must migrate as a foreign key to non-key attributes of the same entity. An attribute cannot appear twice in the same entity under the same name, so it must be given a role name. On fig. 2.26 entity Employee contains a primary key attribute Personnel Number. Information about the manager of the employee is contained in the same entity, since the manager works in the same organization. To refer to the manager of an employee, you should create a recursive relationship (in Figure 2.26, the relationship leads / reports) and assign the name of the role ("Manager"). Note that a recursive relationship can only be non-identifying. Otherwise, the foreign key would have to be included in the primary key and receive the NOT NULL sign when generating the schema. This would make it impossible to build a hierarchy - the subordination tree must have a root - an employee who does not report to anyone within the organization.

Communication directs/obeys on fig. 2.26 allows you to store a tree-like hierarchy of subordination of employees. This type of recursive relationship is called hierarchical recursion and sets the relationship when the leader (an instance of the parent entity) can have many subordinates (instances of the child entity), but the subordinate has only one leader (Fig. 2.28).

Hierarchical recursion Network recursion

Rice. 2.28. Subordination of entity instances in hierarchical and network recursion

Another kind of recursion is network recursion (network recursion), when a leader can have many subordinates and, conversely, a subordinate can have many leaders. Network recursion defines a web of relationships between parent and child entity instances. This is the case when an entity is in a many-to-many relationship with itself. To resolve a many-to-many relationship, you need to create a new entity (many-to-many relationships will be discussed in detail below).

Rice. 2.29. An example implementation of network recursion

On fig. 2.29 considers an example of the implementation of network recursion. The structure models kinship relations between family members of any complexity. Attribute Relationship type can take on the meanings of "father-son", "mother-daughter", "grandfather-grandson", "mother-in-law-daughter-in-law", "father-in-law", etc. Since the relationship always connects two people, from the essence Relative to. entities kinship relationship two identifying relationships are established with the role names "Senior" and "Junior". Each family member can be related to any other family member, moreover, the same pair of relatives can be connected by different types of family relationships.

If an attribute migrates more than one level as a foreign key, then the full name of the foreign key (role name + attribute base name) is displayed at the first level, only the role name is displayed at the second or more. On fig. 2.30 shows a data structure that contains an entity Team, essence Player, which stores information about the players of each team, and the entity Goal, containing information and goals scored by each player. foreign key attribute Team number entities Player has the role name "Which team he is on".

Rice. 2.30. Role name migration

On the next level, basically Goal, only the role name of the corresponding foreign key attribute is displayed (What team does he play in).

Referential integrity (RI) rules are logical constructs that express business rules for using data and are rules for insertion, replacement, and deletion. When generating a database schema based on the logical model options specified in the Rolename/RI Actions tab, declarative referential integrity rules will be generated that must be prescribed for each relationship, and triggers that ensure referential integrity. Triggers are programs that are executed whenever an insert, replace, or delete command (INSERT, UPDATE, or DELETE) is executed. On fig. 2.30 there is an identifying relationship between entities Team and Player. What happens if you delete a team? Entity instance Player cannot exist without command (primary key attribute What team does he play on? Team number cannot take the value NULL), therefore, you must either prohibit deleting a team while at least one player is listed in it (to delete a team, you must first delete all players), or immediately delete all its players together with the team. Such deletion rules are called "restriction" and "cascade" (Parent RESTRICT and Parent CASCADE, see Figure 2.25). Note that the entities Player and Goal, in turn, are also connected by an identifying link and in case of removal by a cascade of a team, all the players of the team and all the goals they scored will be removed. Executing a command to delete a single row can actually delete thousands of rows in the database, so you should use the cascade delete rule with caution. In the event that a deletion restriction rule is set, when attempting to delete a team that has at least one player, the RDBMS server will return an error.

On fig. 2.26 an optional non-identifying relationship between entities is established The Department and Employee. Entity instance Employee can exist without reference to a department (foreign key attribute Where does he work. Department number may be NULL). In this case, it is possible to set the rule to set to zero - SET NULL. When deleting a department, the entity's foreign key attribute Employee - Where does he work? Department number will be NULL. This means that when deleting a department, the employee remains working in the organization without being assigned to any department, and information about him is saved.

Two more deletion rules can be set (if supported by the DBMS):

SET DEFAULT - when deleted, the foreign key attribute is assigned a default value. For example, when a team is deleted, players can be transferred to another team.

NONE - the value of the foreign key attribute does not change when deleted. The record about the player "hangs in the air", that is, it refers to a team that no longer exists. This situation is typical for "flat" tables. For example, if information about players and teams is stored in .dbf files, it is possible to remove the team entry, while the players file "knows nothing" that the corresponding team does not exist. Therefore, on desktop or file server systems, the functionality that enforces referential integrity rules is implemented in the client application.

Deletion rules control what happens in the database when a row is deleted. Similarly, the insert and update rules govern what happens to the database if rows are changed or added. For example, you can set a rule that allows you to add a new team only if at least one player is enrolled in it. The desired behavior can be achieved by the following actions:

Set the strength of communication between entities Team and Player, equal to "One or more" - 1 or more (type P). It is assumed that an identifying relationship has been established.

Assign the action of the RI trigger "Parent Insert-CASCADE" so that when creating a new row in the table Team at least one row was automatically created in the child table Player.

Assign the RI-trigger action "Parent Delete-CASCADE" to the relationship so that when a row is deleted from the table Team the corresponding row or rows from the table Player were also removed.

ERwin automatically assigns each relationship a default referential integrity value before adding it to the diagram. The default RI modes assigned by ERwin (shown in Table 2.4) can be changed in the Referential Integrity Default editor, which is invoked by clicking the RI Defaults button on the Target Server dialog (Server/Target Server menu).

Table 2.4. ERwin no default RI values ​​and possible modes for each link type

Identifying link	Non-identifying association (Nulls Allowed)	Non-identifying relationship (No Nulls)	Categorical relationship
Child Delete Possible modes	RESTRICT, CASCADE, NONE	RESTRICT, CASCADE, NONE, SET NULL, SET DEFAULT		RESTRICT, CASCADE,
NONE
Child Delete Default Modes	NONE	NONE	NONE	NONE
Child Insert Possible modes	RESTRICT, CASCADE,		RESTRICT, CASCADE, NONE, SET DEFAULT	RESTRICT, CASCADE,
NONE	NONE
Child Insert Default Modes	RESTRICT	SET NULL	RESTRICT	RESTRICT
Child Update Possible modes	RESTRICT, CASCADE, NONE	RESTRICT, CASCADE, NONE, SET NULL, SET DEFAULT	RESTRICT, CASCADE, NONE, SET DEFAULT	RESTRICT, CASCADE, NONE
Child Update Default Modes	RESTRICT	SET NULL	RESTRICT	RESTRICT
Parent Delete Possible Modes	RESTRICT, CASCADE, NONE	RESTRICT, CASCADE, NONE, SET NULL, SET DEFAULT	RESTRICT, CASCADE, NONE, SET DEFAULT	RESTRICT, CASCADE,
NONE
Parent Delete Default Modes	RESTRICT	SET NULL	RESTRICT	CASCADE
Parent Insert Possible modes	RESTRICT, CASCADE, NONE	RESTRICT, CASCADE, NONE, SET NULL, SET DEFAULT	RESTRICT, CASCADE, NONE, SET DEFAULT	RESTRICT, CASCADE, NONE
Parent Insert Default Modes	NONE	NONE	NONE	NONE
Parent Update Possible Modes	RESTRICT, CASCADE, NONE	RESTRICT, CASCADE, NONE, SET NULL, SET DEFAULT	RESTRICT, CASCADE, NONE, SET DEFAULT	RESTRICT, CASCADE, NONE
Parent Update Default modes	RESTRICT	SET NULL	RESTRICT	CASCADE

Many-to-many relationship only possible at the level of the logical data model. On fig. Figure 2.31 shows an example of a many-to-many relationship at the top. A doctor can see many patients, a patient can be treated by several doctors. Such a connection is indicated by a solid line with two dots at the ends.

The previous chapters have looked at the most common type of relationship - identifying relationships - and have shown how ER

winperforms key migration through these links.

In this chapter, you will learn how to:

Create and use non-identifying, recursive and subtype relationships. Perform automatic migration of key attributes from one entity to another. Prevent duplicate attribute names from appearing. Set attribute inheritance modes. Set the order in which null values ​​are handled in the child relationship entity. Eliminate many-to-many relationships.

Foreign key management

When creating a diagram, although you may want to add key attributes to an entity when you create it, ER

wincan do most of this work for you if you let him. When you create a relationship between two entities, ERwinautomatically migrates the key attributes of the parent entity to the child entity, where they become foreign keys. Because ERwinsupports automatic key migration, we recommend that you add primary keys to an independent entity as soon as it is created, but don't worry about keys for child entities. After you create a connection and ERwinwill automatically migrate the foreign keys, go back and add own keys to child entities of the chart.

To facilitate database design, ER

winallows you to set the Unique Key mode, which can be used to warn you about the creation of an attribute that can automatically migrate through the link, or to prevent you from creating such attributes. Also, if you delete a link, ERwinautomatically removes the corresponding foreign keys from child entities. See Ch.11, Documenting the rules sec. "Ensuring the uniqueness of entity and attribute names".

How to create foreign keys

1. Draw a relationship between two entities, or between an entity and itself (recursive relationship). The foreign key is automatically migrated. You don't have to do anything!

To see automatic foreign key migration,

need to set chart view mode to Attribute display. You must have already created the attributes of the primary key in the parent entity.

How to view foreign key attributes

1. Right click on the link trunk, you will enter the Relationship menu.

2. Give the "Relationship" command to enter the Relationship editor.

Specifying Attribute Inheritance Modes

winmigrates a primary key attribute, then by default a foreign key that appears in a child entity inherits the name but does not inherit the definition of the primary key attribute. If you want the primary key attribute definition to also migrate to the child entity, you need to change the inheritance modes - Attribute Inheritance Option - which ERwinsets by default. To change the inheritance mode with respect to migrating keys, issue the "Attribute Inheritance..." command from the Option menu. You will enter the Attribute Inheritance Option dialog box.

Three modes can be set in this dialog. The default mode is "No Inheritance" and you can change it to "Full Inheritance" or "Restricted Inheritance" by clicking the appropriate button and then "OK" to save the new mode.

How to set the inheritance mode of an attribute

1. Give the command "Attribute Inheritance..." from the Option menu.

2. Click one of the buttons in the "Inheritance of Primary Key Definitions" window to set the attribute inheritance mode you need:

"Full Inheritance" - Primary key attribute definitions are migrated to foreign key for all new and already created relationships in the current diagram.

"Restricted Inheritance" - primary key attribute definitions are migrated to the foreign key for all new relationships, but this does not apply to already created relationships in the current diagram.

"No Inheritance" - Migration of attribute definitions is disabled for all links in the current diagram. This is the default mode.

Setting non-identifying relationships

Identifying link

is called a relationship that adds identity to a child entity by migrating the parent entity's keys to the child's key attribute area and thus making the child entity dependent from the parent in terms of their identity. For example, when the attribute movie-numb ER migrates from the MOVIE entity to MOVIE-COPY in the MOVIES.ER1 diagram, then each instance of MOVIE-COPY also depends onmovie-numb ER, and from movie-copy-numbERs that uniquely define it (neither of these two attributes alone can uniquely identify a particular copy of a movie).

You can also set such a relationship that does not make the child entity dependent on the parent. This type of connection is called non-identifying link. In ER

winsuch a relationship is indicated by a dotted line with a thick dot at the end corresponding to a child relationship. With a non-identifying relationship, the attributes of the primary key of the parent entity migrate to the data area (non-key area), which is located under the line in the child entity. If the attributes that migrated to the non-key area of ​​the child entity are not needed in that entity, then the relationship is called optional non-identifying relationship, which implies that the child entity does not need the migrated attributes to identify it, and that it can exist without these attributes. In ERwinan optional non-identifying relationship is indicated by a dotted line with a bold dot at one end (child) and a diamond at the other (parent).

The figure below shows how they are depicted in ER

winidentifying, non-identifying, and optional non-identifying relationships, and summarizes the differences between these relationships.

If a relationship has already been created on the diagram, then you can change its type in the Relationship editor.

How to change the connection type

1. Select the link you want to change. Place the cursor on the link line and right-click to enter the Editor pop-up menu.

2. Give the "Relationship" command on the Editor menu to enter the Relationship editor.

3. Click the desired button in the Relationship Type window (Identifying - identifying, Non-Identifying - non-identifying).

4. Click "OK" to commit the changes. ER

winchanges the link line on the diagram according to the new type.

Recursive communication in

ER win is automatically assigned a type Non-Identifying. It cannot be changed.

Allow or not allow null values?

When you draw a non-identifying relationship, you need to decide whether the foreign key attributes inherited from the parent can be null or not. By default, a non-identifying relationship is set to "Nulls Allowed", which means that a child entity can exist without a parent, and the relationship is called optional. "No Nulls" means that the existence of a child entity depends on the parent, and the connection is called mandatory. In the case of a Nulls Allowed relationship at the parent end of a non-identifying ER relationship

winputs a sign - a rhombus.

One of the main differences between an identifying relationship and a non-identifying relationship is that only those foreign keys that migrate through a non-identifying relationship can be null.

See ER methods guide

win Methods Guide, Ch.3, "Language Review", sec. "Identifying and non-identifying relationships".

The default for a non-identifying association is set to

"Nulls Allowed" mode, i.e. NULL values ​​for foreign keys

1. Study questions

1. Development of a relational data model inERwin.
2. Normalization of the physical data model inERwin.

2. Lesson plan

1. Knowledge control by testing (test ISE005).
2. Importing entities into ERwin.
3. Development of logical and physical data models in ERwin using the methodology IDEF1X.
4. Normalization of the physical data model in erwin.

1. Import entities into ERwin, using file Data _IS_Name. bpx, and based on the obtained set of entities, develop a logical data model.

Comment: If the names of entities and attributes were created in Cyrillic (in Russian), they should be rewritten in Latin characters.

1. Create logical and physical data models using tools erwin.
2. 
   in your folder ISE.
3. Normalization of the physical model should be carried out by resolving MANY-TO-MANY relationships using the button Many to Many Transform toolbar ER winTransform Toolbar.
4. Save the results of the work in a file
   Data_model_IS_Name_IDEF1X.er1 in your folder ISE.

An EXAMPLE of a logical model, as well as a normalized physical data model, made in IDEF1X technology is given in.

4. Technological process of completing tasks

4.1. Technological process of creating data models

4.1.1. Methodology for creating models (IDEF1X methodology)

Methodology IDEF1X used by the CASE tool ERwin to build logical and physical data models of the information system.

ERwin has a simple and understandable user interface for building logical and physical data models processed by the system. V logical model, it is allowed to create MANY-TO-MANY relationships between entities, and the attribute name ( Attribute Name) will be the name of the attribute in logical model, and the column name ( column name), if given, will be the name of the attribute in physical models.

In any of these models, you can automatically convert a MANY-TO-MANY relationship to a ONE-TO-MANY relationship.

As a result, a subordinate table will be created that unbinds the MANY-TO-MANY relationship. This table will contain an embedded composite key (FK) with attributes embedded from the main tables and their corresponding data types. If you need to change the data type, you must do so manually.

The process of creating a model involves the following steps:

1. You can create a new model from the window Computer Associates ERwin or click the create model button. In both cases, a dialog box will be displayed. Create Model - Select Template (Fig. 5.1).

1. In the window Create Model - Select Template you should select an option that determines the ability to create data models of a certain type: Logical(can only be created logical model), Physical(can only be created physical model) or Logical/Physical(you can create both models in parallel: and logical , and Physical). To have more options, it is advisable to choose the last option − Logical/Physical .
2. V the Target Database group from the list provided in the field database, select a database management system (DBMS) – SQL Server, and in the field version desired version - 2000 .
3. In the window that appears < Main Subject Area >/Display] select the type of model to be created from the list: Logical or Physical(Fig. 5.2).

In the toolbar ERwin Toolbox contains buttons that allow you to add to the data model and edit its individual fragments:

	– Select(editing the selected model object),
	– entity(adding entity),
	– Many-to-many Relationship(many-to-many relationship),
	– Identifying Relationship (identifying link),
	– non-identifying relationship (non-identifying relationship).

4.1.2. Technological process of creating a logical data model

In the process of creating a model, entities can be introduced by importing from an entity dictionary developed in BP win, or by creating with a button entity on the toolbar.

Importing entities into ERwin

Remarks

• Entities can only be exported and imported once.
• After importing entities from BPwin flags Exchange with ERwin and buttons update and Delete in the dialog box Entity and Attribute Dictionary Editor become dim. This is because you cannot change entities and attributes that BPwin shares c erwin.

1. Creation of new entities.
   • Click the add entities button entity and click within the model window.
   • Fit entity name and press enter, then enter attribute name entities.
   • To select the desired font, perform p.p. 1.9–1.12.
2. Adding new attributes.
   • In the context menu of the entity, select the command Attributes … and in the window that appears (Fig. 5.4) press the button new.
   • In the window New Attributes(Fig. 5.6) enter the attribute name in the field Attribute Name .
   • Set the data type of each attribute for each entity: Text (String), Numeric (Number), Date/Time (Datetime), or Memo field (B inaryL argeOb .ject, Blob) (fig. 5.5 or fig. 5.6) .
   • Define key attributes by checking the box Primary Key in the window Attributes(Fig. 5.5) after selecting the desired attribute in the field attribute.

Establishing Relationships Between Entities

1. Establishing a MANY-TO-MANY relationship:
   • In the toolbar Erwin Toolbox press the button Many-to-Many Relationship .
   • Sequentially click the left mouse button on the names of the entities between which you want to create a relationship (Fig. 5.7).

1. Installation identifying ONE-TO-MANY connections:
   • In the toolbar Erwin Toolbox press the button identifying relationships.
   • key key attribute subordinate entity (FK) located on the MANY side (Fig. 5.8).
   • A composite key is formed in the subordinate entity.

1. Installation non-identifying ONE-TO-MANY connections:
   • In the toolbar Erwin Toolbox press the button non-identifying relationship .
   • Sequentially click the left mouse button on the names of the entities between which you want to create a relationship. The result of creating a connection will be the implementation key attribute of the main entity as non-key attribute subordinate entity (FK) located on the MUCH side (Fig. 5.9).

4.1.3. Technological process of creating a physical data model

The result of the command execution will be an automatically created physical model, in which tables will be presented instead of entities, and fields of tables will be presented instead of entity attributes.

1. In the physical model, check the data types and relationships established between the tables.

4.2. Technological process of normalization of the physical data model (IDEF1X methodology)

1. In the window Computer Associates ERwin - )