Abstract: Modeling in ERwin. Model design in ERWin Logical data model erwin

To establish relationships between entities and create foreign keys, ERWin provides the ability to divide relationship types into several options:

• identifying relationship - a relationship that defines a one-to-one correspondence between an instance of one entity and a single instance of a related entity and, as a rule, describes a 1:1 relationship, but when implementing a chained primary key, it can implement a one-to-many relationship (1:JV);
• non-identifying relationship - a relationship that implements a one-to-many type of relationship (1 :N), by representing a foreign key in a related entity as a simple attribute that can be subject to certain additional restrictions compared to ordinary information attributes;
• multiple relationship - a relationship that implements the many-to-many (L G:M) type of relationship, is represented only at the level of the logical model, illustrating the connection between entities, but without creating foreign keys in related entities;
• categorization - a relationship that ensures the linking of an entity-community with entity-categories using a one-to-one (1:1) type of relationship and at the same time creating a foreign primary key in the entity-categories associated with the primary key of the entity-community.

When creating a relationship between two entities, simply select the relationship type icon, and then sequentially point to the parent and related entities. This will create a relationship and, if allowed by the relationship type, create the necessary foreign keys in the related entity. In the case when the developer has not defined the primary key of the parent entity and has established a relationship, for example, a non-identifying one, then the creation of a foreign key will not occur, but as soon as the primary key is specified in the parent entity, it will immediately be reflected in the related entity by a foreign key in accordance with the connection between these entities existing in the model.

The ERWin tool, when establishing relationships between entities, defines two types of entities:

• parent - is a base entity whose primary key can migrate to a related entity;
• child - defined by an entity that, when a connection is established, receives a foreign key formed from the migrating primary key of the parent entity.

This division looks quite logical, since, based on the peculiarities of building connections and the logic of the subject area, the information described by the parent entity is aggregated in relation to the data described by the child entity. For example, considering the relationship between the entities "Customer" and "Order", a specific customer, represented by an instance of the entity "Customer", combines (aggregates) the set of orders that he created in the electronic store.

zine. As a result, the “Order” entity in relation to the “Customer” entity can be considered as a child, and the “Client” entity as a parent.

The description of relationships contains, compared to entities and attributes, a smaller number of properties that need to be described, but they are also, and sometimes more important, because they allow you to describe and configure referential integrity rules, ensuring, when implemented in the database, the correctness of data storage . One of the characteristics is the name of the connection, which is used in the database model and determines the basic meaning of the connection being established between entities (Fig. 3.15).

Rice. 3.15. Basic description of communication in ERWin

The "Name" column in the connection description provides the developer with the opportunity to specify a name that reflects the semantic meaning of the connection. This is an important component of the model, allowing you to unambiguously understand the essence and meaning of the established connection, which, ultimately, should help to correctly normalize the database model, correctly redistributing individual attributes between entities.

It is important to understand that the names of relationships should be, if possible, unique within the entire database model, and not just at the level of an individual diagram. The presence of identical names of links can lead to the inability to correctly identify the corresponding link and ultimately build an effective model. Other characteristics describing the relationship between entities are located in the dialog box below the list of model relationships and contain rules for cardinality (cardinality), renaming the generated foreign key (role) and ensuring referential integrity.

The three most important basic characteristics of a connection (Fig. 3.16), which form the main essence of the connection, are described in the first tab “General” and represent the type of connection, the name of the connection, cardinality (power). These communication parameters must always be defined and described correctly. In addition to the name of the connection, the remaining characteristics will be transferred during the appropriate transformation to the physical model, and then to the database.

The first characteristic of a connection determines its type (Figure 3.17): identifying or non-identifying. At the same time, by choosing the appropriate type of relationship, the developer has the opportunity (for a non-identifying relationship) to specify the absence of an instance of the parent entity, thereby allowing the foreign key to specify the empty value “NULL”.

Rice. 3.16. Main characteristics of communication in RZh Win

Typically, when establishing a non-identifying relationship, the Null Option is set to Nulls Not Allowed. This is determined by the peculiarities of working with data, according to which a child data instance must be associated with a parent instance. But sometimes there are cases when this is not observed. As a rule, this situation arises when the objects of the subject area united by this relationship are equivalent and it is impossible to unambiguously determine the priority of the appearance of an instance of a particular entity. Then the value is set to “Null Allowed” (NULL is allowed), as shown in the example (see Fig. 3.17).

Since one-to-one and one-to-many connections are related and the differences in them are only in power and some more stringent requirements, switching between these types of connections can be done within the connection settings dialog box, switching the connection from the “Not” type identifying" to type "Identifying". In this case, the Null Option will not be available

for settings. This is explained by the fact that when an identifying relationship is established, the foreign key obtained in a child entity is also the primary key, and according to the database construction rules, the primary key cannot store an empty value. Therefore, the resulting foreign key is set to Null Not Allowed.

Another characteristic that makes it possible to move from a one-to-one relationship to a one-to-many relationship and vice versa is cardinality. Establishing the cardinality (power) of a connection within the “Cardinality” and “Cardinality Value” properties sets the rules for filling instances of a child entity (Fig. 3.18). There are four cardinality options determined by ERWin:

• Zero, One or More (zero, one or many) - for a child entity, any number of instances associated with one instance of the parent entity is possible, including the option of no instances;
• (P) One or More - the number of instances of a child entity associated with one instance of a parent entity can be any, but when creating an instance in a parent entity, instances must already exist in the child entity, which requires setting the "Null Option" parameter " to the value "Nulls Allowed", allowing the null value "NULL" to be stored in the foreign key obtained upon establishment of the relationship;
• (Z) Zero or One - a one-to-one relationship is defined, allowing the existence of no more than one instance of data in a child entity;
• Cardinality Value - specifies the exact number of related instances in the child entity, which can only be achieved by setting the "Null Option" to "Nulls Allowed", first creating instances in the child entity, and then linking them to an instance in the parent entity .

As a result of indicating the cardinality (power) of the connection on the model, its alphanumeric designation will be displayed in the diagram. If the cardinality (power) option is selected in the “One or many” option, then the letter “P” will be displayed, in the case of cardinality “Zero or one” - the letter “Z”, if an exact numerical value is specified - the specified value, in other options no markings will be displayed on the model.

Another characteristic of a connection is described as the main one - the semantic content of the connection (Fig. 3.19), denoted by the verb form.

This description, as in all database models at any level of presentation, shows the peculiarity of the interaction of entity instances in accordance with the characteristics of the subject area. The description must be a phrase denoting the relationship of a parent entity instance to a child entity instance, or vice versa, representing an expression containing an action verb.

When one-to-one and one-to-many relationships are established, as discussed above, the primary key of the parent entity migrates to the child entity, creating a corresponding foreign key (Figure 3.20). Often, especially when using a naming convention for model elements, identical primary keys in different entities may have the same names, which causes some difficulties when establishing relationships, resulting in the need to reflect foreign keys with names that are already present in the entity. Another option when it is necessary to use the naming mechanism of a key migration rule may be that the attribute that describes the corresponding foreign key needs to be more precisely worded.

The solution to these problems is implemented through the "Role Name" mechanism, where the developer specifies the attribute name for the foreign key, as it should be represented in the database model and, as a result, transformation in the database. The "Role Name Info" area contains two columns:

• Migrated Attribute - shows an attribute of the parent entity that is represented by a foreign key in the associated child entity (cannot be changed);
• Role Name—Indicates the new foreign key attribute name value that should be used in place of the migrating attribute name.

Specifying the desired attribute name in the "Role Name" column will lead to the renaming of the foreign key attribute and subsequent use of the new attribute name in all elements of the database model where it is necessary.

Defining referential integrity rules (Figure 3.21) is a step in the physical modeling of the database. This is due to the fact that individual rules for some DBMSs may not be available. However, ERWin provides the ability to specify referential integrity rules for the relationships being formed at the logical modeling stage. At this stage, the developer is offered a maximum set of rules:

• None (absent) - a rule that assumes any user actions without affecting other elements of the database;
• No Action (no action) - a rule that assumes developer-defined actions;
• Restrict (prohibit) a rule that prohibits performing an operation on data if the test condition is true;
• Cascade - a rule that performs sequential actions on related data in accordance with the action performed on the data to which this rule is defined;
• Set Null - a rule that sets the foreign key value to NULL for related instances;
• Set Default - A rule that sets the default value defined for the foreign key of the associated instance.

Referential integrity rules are aimed at ensuring the correctness of operations with data when modifying it. Thus, these rules must be followed if operations are implemented in the database, but adding, changing and deleting data. ERWin implements referential integrity constraint operations to the maximum extent possible, considering the execution of the relevant operations not only for the main cases that affect changes in the database, but also for operations that should not have a significant change in the database. As a result, the developer is asked to specify referential integrity rules when performing actions on data when modifying data instances in both parent and child entities. Subsequently, all these actions, if they are not provided for in the DBMS, will be converted into automatic execution program modules (triggers) and associated with actions performed on the data. If the DBMS contains the specified referential integrity actions, they will be declared by the corresponding rules when describing data tables.

When forming a many-to-many connection, the developer has the opportunity to specify a minimum set of connection characteristics, including defining the semantic load. No other rules and characteristics are established for this type of connection, since when moving to a physical model, such a connection must be normalized and represented by one-to-many connections (Fig. 3.22).

• when specifying a connection for the first time, select the connection icon and sequentially select a community entity and one of the category entities;
• Using the same categorization link icon, connect the remaining category entities by sequentially selecting a graphic element and the next category entity.

As a result of performing these actions, the database model will have a representation of the relationship similar to the example above (see Figure 3.22).

There are two types of categorization relationships, one of which must be determined when establishing this type of relationship (Fig. 3.23). To indicate differences in the type of categorization connection, the designation of a graphic element will be presented with two lines or one line (Table 3.1).

For the categorization link itself, no other characteristics are defined, and the developer is only given the opportunity to view the structure of the categorization link (Figure 3.24). This description makes it possible to see which category entities are defined by subtypes, and which entity-community is represented by a supertype.

The connections themselves from the graphical categorization element to the entities are one-to-one connections, and, as a rule, fixed characteristics are defined for them. The developer is given the opportunity to specify only the semantic content of the relationships, the name of the foreign key attribute and the referential integrity rules.

Rice. 3.24. Description of the categorization relationship in the EHRI

• A detailed discussion of referential integrity rules is discussed in Section 3.2.

Laboratory work No. 4. Defining relationships between entities in ERwin

As noted in Exercise 3, to build a logical data model, you first need to define a set of entities and define the relationships between them. In Exercise 3, a diagram was created containing four entities (Figure 4.1). We will consider the method of determining the connection between them in the current exercise.

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

Figure 4.2 - Non-identifying relationship

Step 3. Go to the attribute level and notice that the “Classroom” entity has added a primary key attribute from the “Class” entity and is marked with the letters “FK”. An 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 - Attribute migration

Step 4. Assign a verb phrase to the connection. To do this, select the relationship by clicking on it with the mouse pointer, then right-click and select “Relationship Properties...” 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 when analyzing the subject area we found out that the educational place cannot exist separately from the class, set this ne - switch to the “No Nulls” position, thereby imposing the condition that an existing instance of a workbench must always have a reference to the class to which it belongs.

Tab " Definition"(definition).

This page introduces the definition of the relationship. The text of the connection definition, as in the case of an entity, depends on the standards adopted at the enterprise and should facilitate the perception of the model.

“Rolename” Tab

The role name (rolename) is an additional characteristic that can be used

assigned to the 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

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

tiable database (Figure 4.7).

Referential integrity statements are logical constructs that express business rules for using data. They define what actions the DBMS should perform when a table row (an entity instance) is deleted, inserted, or modified. Actions defined in this way can be used subsequently to automatically generate triggers that maintain data integrity

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

1 RESTRICT - prohibition of deleting, inserting or changing an entity instance

2 CASCADE - when deleting an instance of a parent entity, deleting all instances of the child entity that reference the deleted parent instance.

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

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

5 NONE - no action is taken.

Figure 4.7 – Tab “RI Actions” (Referential integrity settings)

These rules apply to insert, delete, and modify instances of both parent and child entities. Thus, each connection must have a set of six rules that are entered into fields united by the common heading “RI Actions”. When adding a relationship 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 then sub-

“Model Properties” commands (Figure 4.9).

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

Figure 4.9 – Procedure for calling the “Model Properties” dialog box

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

del, if you simply exit the dialog without reassigning, then the changed settings will only affect new models.

Each type of communication 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 various types of connections

actions	Relationship Type
Identification	Unidentified non-identifying, nulls	Unidentified identifying (Non- Identifying, No	connection
(deleting a 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 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 - Place connection prohibit inserting and changing an instance of a child entity, as well as deleting and changing a parent entity. This means that deletion is not allowed

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

“UDP” tab (User-settable parameters)

Bookmark - UDP, like 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

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

Figure 4.10 – The “equipment type code” attribute has migrated to the non-key attributes of the “Training place” entity

Let us now consider the connection between the entities “Teaching place” and “Unit of equipment”. 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 the warehouse, be faulty and await write-off, etc., that is, exist separately from the training place. Thus, the entities “Training place” and “Unit of equipment” do not depend on each other, and must be associated with a non-identifying relationship.

Step 5. Select the non-identifying relationship from the Tools palette and bring it into the diagram by selecting Study Place as the parent entity and

“Unit of equipment” - subsidiary. In the Link Editor, change the verb phrase "Parent-to-Child" to "consists of." There are two types of non-identifying relationships - those that allow NULL values ​​(Nulls Allowed) and those that do not allow (No Nulls). By default, the “Nulls Allowed” variety is selected, leave this unchanged. This setting means that for an instance of the “Equipment Unit” entity, the foreign key fields can have a null value, that is

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

After establishing connections, the diagram at the entity level will look like this:

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

Figure 4.12 - Illustration of connections in IDEF1X notation

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

density - to do this, select the item in the context menu of the diagram

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

Alphabet" href="/text/category/alfavit/" rel="bookmark">alphabetic groups separated by the colon symbol “:”. 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, prohibiting deletion is denoted by D:R, and setting NULL on modification is denoted by U:SN. Designations are placed at the parent or child end of the relationship, depending on which entity they relate to. With referential integrity settings enabled, the diagram is

looks as shown in Figure 4.14.

Figure 4.14 - ER diagram with referential integrity settings enabled

The diagram now identifies one of the object areas, of which we have identified four - material support for the learning process. We'll look at other object areas in later exercises.

Step 7 On the “Entity Level” tab, save the model, for example, under the name

it Lab_4_Petrov. er1.

Step 8. Complete an individual task to identify connections between entities.

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

1. Results of completing steps 1 – 7 of the lesson program.

2. Results of completing an individual task.

Control questions

1. How do you differentiate between dependent and independent entities in an ERwin diagram?

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

3. What is a physical and logical data model?

4. What relationship between entities is called identifying?

5. Explain the meaning of the statement that some attribute has “migrated”?

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

7. What features does the link editor have?

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

9. How to indicate referential integrity in a diagram

10.What connections between entities were used when performing indi-

visual task?

6. Simulation in ERwin

ERwin's Place in Information Modeling
The process of building an information model consists of the following steps:

• entity definition;
• defining dependencies between entities;
• setting primary and alternative keys;
• defining entity attributes;
• bringing the model to the required level of normal form;
• transition to the physical description of the model: assignment of correspondences entity name - table name, entity attribute - table attribute; setting triggers, procedures and restrictions;
• database generation.

ERwin creates a visual representation (data model) for the problem being solved. This view can be used for detailed analysis, refinement, and dissemination as part of the documentation required in the development cycle. However, ERwin is far from just a drawing tool. ERwin automatically creates the database (tables, indexes, stored procedures, referential integrity triggers, and other objects needed to manage the data).

Mapping the logical and physical layer of the data model in ERwin

There are two levels of representation and modeling in ERwin - logical and physical. The logical level means a direct representation of real-life facts. For example, people, tables, departments, dogs and computers are real objects. They are named in natural language, with any word separators (spaces, commas, etc.). The logical level does not consider the use of a specific DBMS, does not define data types (for example, integer or real), and does not define indexes for tables.
The target DBMS, object names and data types, indexes make up the second (physical) level of the ERwin model.
ERwin provides the ability to create and manage these two different levels of presentation of a single diagram (model), as well as having many display options at each level.

ERwin Diagram Components and Basic Types of Diagram Views

An ERwin diagram is built from three main blocks - entities, attributes and relationships. If we consider a diagram as a graphical representation of the rules of a subject area, then entities are nouns and relationships are verbs.
The choice between the logical and physical display levels is made through the toolbar or menu. Within each of these levels there are the following display modes:

• "Entity" mode - the name of the entity (for the logical model) or the name of the table (for the physical representation of the model) is displayed inside the rectangles; serves for the convenience of reviewing a large diagram or placing rectangles of entities on the diagram.
• The "entity definition" mode is used to present the diagram to other people.
• "Attributes" mode. When moving from a domain to a model, you need to enter information about what constitutes an entity. This information is entered by specifying attributes (at the physical level - table columns). In this mode, the rectangle-entity is divided by a line into two parts - the attributes (columns) that make up the primary key are displayed in the upper part, and the remaining attributes are displayed in the lower part. This mode is the main one when designing at the logical and physical levels.
• "Primary keys" mode - inside the rectangles - entities - only the attributes/columns that make up the primary key are shown.
• Icon mode. For presentation purposes, each table can be assigned an icon (bitmap).
• "Verb phrase display" mode. Link arcs show verb phrases that link entities (for the logical layer) or foreign key names (for the physical layer).

A chart can span more than one screen and more than one sheet when printed. To review the model, in addition to scrolling the screen, there are modes for reducing/enlarging the image, displaying the entire model, and displaying a selected part of the model.

Tools for creating a model in ERwin

Basic model creation tools are available both from the menu and through the tools window. With their help, independent and dependent entities are created, identifying and non-identifying relationships, complete and incomplete categories, non-specific relationships and text elements.
By clicking the mouse over an entity, you enter one of the many ERwin editors:

• editors associated with the entity as a whole (entity definition, additional information, triggers, indexes, table characteristics, stored procedures associated with the table);
• attribute editors (attribute definitions, table columns in the physical model view, 4GL tool repository, for example, extended attributes in PowerBuilder).

Entity identification. Entities in ERwin

In a diagram, an entity is represented by a rectangle. Depending on the diagram's presentation mode, the rectangle may contain the name of the entity, its description, a list of its attributes, and other information.
The horizontal line of the rectangle divides the entity's attributes into two sets - the attributes that make up the primary key in the upper part and the others (not included in the primary key) in the lower part.
An entity is a set of real or abstract objects, such as people, places, events, facts, that have common characteristics. Essence is a logical concept. The entity corresponds to a table in a real DBMS. In ERwin, an entity visually represents three main types of information:

• the attributes that make up the primary key;
• non-key attributes;
• entity type (independent/dependent).

A primary key is an attribute or set of attributes that uniquely identifies an instance of an entity. If several sets of attributes can uniquely identify an entity, then the choice of one of them is made by the developer based on an analysis of the subject area.
For each primary key, ERwin creates a unique index when generating the database structure.
Instances of an independent entity can be uniquely identified without defining its relationships to other entities; a dependent entity, on the other hand, cannot be uniquely identified without identifying its relationships with other entities. A dependent entity is displayed in ERwin as a rounded rectangle.

Relationships in ERwin

A connection is a functional dependence between two entities (in particular, it is possible for an entity to connect with itself). For example, it is important to know the employee's last name, and it is equally important to know in which department he works. Thus, between the entities “department” and “employee” there is a relationship “consists of” (the department consists of employees). A relationship is a logical level concept to which a foreign key corresponds at the physical level. In ERwin, relationships are represented by five main pieces of information:

• type of connection (identifying, non-identifying, complete/incomplete category, non-specific connection);
• parent entity;
• child (dependent) entity;
• communication power (cardinality);
• Acceptability of empty (null) values.

A relationship is said to be identifying if an instance of a child entity is identified through its relationship to a parent entity. The attributes that make up the primary key of the parent entity are also included in the primary key of the child entity. A child entity in an identifying relationship is always dependent.
A relationship is said to be non-identifying if an instance of a child entity is identified other than through the relationship to the parent entity. The attributes that make up the primary key of the parent entity are also included in the non-key attributes of the child entity.
To define ERwin relationships, you select the relationship type, then use the mouse to select the parent and child entities. The identifying link is depicted as a solid line; non-identifying - dotted line. The lines end with a dot on the child entity's side.
When defining a relationship, the primary key attributes of the parent entity are migrated to the corresponding attribute scope of the child entity. Therefore, such attributes are not entered manually.
The primary key attributes of a parent entity migrate with their own names by default. ERwin allows you to enter roles for them, i.e. new names under which the migrating attributes will be represented in the child entity. If an attribute is migrated multiple times, such renaming is necessary. For example, the entity “intermediary transaction” has the attribute “seller enterprise code” and “buyer enterprise code”. In this case, the primary key of the entity "enterprise" ("enterprise code") has two roles in the child entity.
At the physical level, the role name is the name of the foreign key column in the child table.
The strength of a relationship is the ratio of the number of instances of a parent entity to the corresponding number of instances of a child entity. For any relationship other than a non-specific one, this relationship is written as 1:n.
ERwin, in accordance with the IDEF1X methodology, provides 4 options for n, which are represented by an additional symbol in the child entity: zero, one or more (default); zero or one; exactly N, where N is a specific number.
ERwin depicts the acceptability of empty (NULL) values ​​in non-identifying relationships as an empty diamond on the parent entity side of the relationship arc.
Power designations, respectively, zero, one or more, one or more, zero or one in IE notation are shown in Fig. 1.

Fig.1. Communication power notation in IE notation

The name of a relationship at the logical level is a “verb” that links entities. The physical name of a link (which may be different from the logical name) for ERwin is the name of a constraint or index.

Graphical model editing

Laboratory work No. 5

Goal of the work:

Exercise:

Work sequence

Getting to Know the User Interface

· Download Erwin software.

· In the dialog box that appears, select the radio button Create a New Model. A dialogue will appear on the screen Create Model – Select Template, where you need to select the modeling level.

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

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

Set the switch Logical/Physical to create a model with logical and physical levels.

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

· The main program window will appear on the screen.

At the top of the window there is a title line, which indicates 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 work area in which the ER diagram is created.

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

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

Table 1.1.

Buttons located on the Erwin program toolbar

Button	Purpose
	Create, open, save and print a model
	Calling the Report Browser dialog to generate reports
	Changing the model view level: entity level, attribute level, definition level
	Changing the model viewing scale
	Generating a database schema, aligning the schema with the model and selecting a server (available only at the physical model level)
	Switching between model areas Subject Area

To directly 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

Adding entities to the model

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

· Select the button on the toolbar (ERwin Toolbox) 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 the new entity, with the automatically generated name “E/1”.

· Enter the entity name from the keyboard " Buyer" and press Enter.

· In exactly 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 accessed from the main menu: Model | Entities.

Rice. 1.6. Entity Editor

At the top of the editor window is a list of all entities present in the diagram. With its help, you can select an entity whose properties you want to view or change. By default, the selected entity is the one highlighted in the diagram that was clicked on. Next there is the Name field, in which the name of the entity is displayed. The name can be edited.

Below in the editor window there are a number of bookmarks:

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

Note, Note2, Note3(note) – used to enter free text associated with an entity, such as sample data and queries.

UDP– user-defined properties.

Icon(icon) – for clarity, each entity can be assigned an icon, which is displayed next to its name.

· For each entity, enter a definition Definition.

Key groups

· Call the key group editor Key Groups, by right clicking on the entity Buyer and selecting the item from the context menu Key Groups. The key group editor can also be accessed from the main menu: Model | Key Groups.

The key group editor contains the following controls:

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

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

In addition, the Key Group Editor dialog box contains the following tabs:

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

ü General (general settings). Switches that allow you to specify the type of key group. These groups are not available for primary and foreign keys.

ü Definition. Free text information related to the selected key group.

ü Note. Note for the selected group.

ü UDP (custom properties).

· Click the button New.

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

Switch Key Group Type specifies the type of key to be created. This can be an Alternate Key or an Inversion Entry. Select Alternate Key and press OK. The newly entered alternative 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(key group members) empty. Select an attribute from the left list 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 key groups for the inverse inputs shown in table. 1.3.

Laboratory work No. 6

Setting Declarative Referential Integrity Rules

· While on logical at the data model level, select the “concludes” relationship between the Buyer and Contract entities by clicking on it with the mouse pointer. Then right-click and select from the context menu Relationship Properties(link editor).

· In the link editor window Relationship go to the tab RI Actions. Review the default referential integrity rules for the “Buyer – Contract” relationship. These settings prevent inserting or modifying a child entity instance, or deleting or modifying a parent entity. This means that deleting or changing a buyer is not allowed if there are contracts concluded with him in the database, as well as entering an agreement without specifying the buyer or with reference to a non-existent buyer. Thus, we have fulfilled the condition according to which the contract can exist only for a specific buyer.

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

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

Data Normalization

It is clear from the model that in the Buyer entity there is a multiple attribute TEL. 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 a Phone entity containing the following attributes: TEL_CODE (primary key, type – number) and TEL (type – string).

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

Server selection

· Execute the 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 the NULL condition for newly created columns. Some options in this dialog box depend on the selected server type.

· After selecting the server, click the button OK.

Denormalization of data

The model has two many-to-many relationships: Product – Contract and Product – Invoice, which must be resolved at the physical level. The result of resolving these connections is presented in table. 2.1.

Table 2.1.

Result of resolving many-to-many relationships

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 “Product – Contract” connection by clicking on it with the mouse pointer. Then right-click and select from the context menu Create Association Table(create an association 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 Delivery_Plan table has appeared on the model, connected by an identifying link to the Product and Contract tables.

· The new table must 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 right-click and select from the context menu Columns ( column editor) . Working with this editor is similar to working with the attribute editor.

· Enter three new columns yourself in accordance with the table. 2.1.

· Using the method discussed above (using the wizard), transform the “Product – Invoice” relationship and supplement the resulting associative table Shipping with two columns according to table. 2.1.

Setting validation rules

Specifying a list of acceptable values

In accordance with the subject area under consideration, for the VAT_RATE field of the Product table, we will set a list of acceptable 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 rule name – Checking the VAT rate. Click the button OK.

· Go to the bookmark General. In Group Type set option Valid Value List.

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

· Check that at the top of the editor window Validation Rules the 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 - “VAT rate check”.

Setting Default Values

Let's create a rule according to which the value of the current date will be inserted by default into the DATE_DOG field of the Contract table.

· Call the context menu of the Contract table and select Columns.

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

· On the bookmark 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 rule name – The current date. Click the button OK.

· On the bookmark Access in field Server Value – Access Default enter Date()(function that gets 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 REPORT_DATE field of the Invoice table. To do this, in the column editor window Column select the DATE_OTGRATION field and on the Access tab in the field Default from the drop-down list, select a rule The current date.

Setting rules for validating input values

Let's create a rule for checking the entered 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 Columns.

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

· On the bookmark 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 rule name – Price check. Click the button OK.

· Go to the bookmark General. In Group Type set option Min/Max.

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

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

· Click the button OK.

Laboratory work No. 7

Database size calculation

Goal of the work:

Master the database size calculation technique implemented in Erwin.

Laboratory work No. 8

Generating reports in Erwin

Goal of the work:

· studying the types of reports;

· mastering the procedure for creating reports

Laboratory work No. 5

Erwin Basics. Building a logical data model

Goal of the work:

· 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.