One-to-many relationship erwin. Basics of working at Erwin. Building a logical data model. Setting validation rules

02.05.2022

Laboratory work No. 3. Database Modeling with Erwin

Objective- Acquisition by students of practical skills in creating logical and physical data models using CASE - information systems development tools.

Basic information

ERwin supports forward and reverse database modeling. In direct modeling, the database schema is described directly using an entity-relationship diagram. Entities in the diagram are represented by rectangles. Each rectangle can have different visual attributes. Each entity must be given a unique name. Entity names must be singular. This is determined by the fact that the system always operates on separate entity instances. In this case, individual instances of an entity are considered as objects, and entities are considered as a class of objects. If the entities were described during modeling in BPwin, then they can simply be imported into ERwin. An example of a diagram with created entities is shown in the figure.

Figure 4 - An example of a diagram with created entities

Building Models in ERwin

There are two points of view on the information model and, accordingly, two levels of the model. The first - the logical level (user's point of view) means a direct display of facts from real life. For example, people, tables, departments, dogs, and computers are real objects. They are named in natural language, with any word separators (spaces, commas, etc.). At the physical level of the model, the use of a specific DBMS is considered, data types (for example, integer or real number), indexes for tables are determined.

ERwin provides the ability to create and manage these two different presentation levels of a single diagram (model), as well as having many display options at each level. The term "logical level" in ERwin corresponds to the conceptual model.

Stages of building an information model.

definition of entities;
determination of dependencies between entities;
setting primary and alternate keys;
definition of 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 constraints;
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 of the documentation needed in the development cycle. However, ERwin is far from being just a drawing tool. ERwin automatically creates the database (tables, indexes, stored procedures, referential integrity triggers, and other objects needed to manage data).

Creation of an entity.

To add an entity to the model, click on the entity button on the toolbar (Erwin Toolbox), then click on the place on the diagram where you want to place the new entity. Right-clicking on an entity and selecting Entity Editor from the pop-up menu brings up the Entity Editor dialog, which defines the entity's name, description, and comments.

Each entity must be fully defined with a textual description on the Definition tab. These definitions are useful both at the logical level, since they allow you to understand what kind of object it is, and at the physical level, since they can be exported as part of a schema and used in a real database ( CREATE COMMENT on entity_name). The Note, Note2, Note3, UDP (User Defined Properties) tabs are used to add additional comments and definitions to an entity.

In the Icon tab of each entity, you can match the image that will be displayed in the model view at the icon level and the image that will be displayed at all other levels.

The UDP tab of the Entity Editor dialog is used to define user-defined properties (User - Defined Properties). When you click on the button of this tab, the User - Defined Property Editor dialog is called (also called from the Edit/UDPs menu). In it, you must specify the type of object for which UDP is started (diagram as a whole, entity, attribute, etc.) and data type. To add a new property, click the button in the table and enter the name, data type, default value, and definition.

Creating attributes.

The next step in creating a model is to set attributes for each entity. When specifying an attribute type, it is possible to use domains. A domain is an abstract user-defined type that is assigned to any physical data type. However, each domain can have its own default values and input validation rules. ERwin provides the ability to document all steps for creating your own data types. Using the concept of a domain, the database is portable to different hardware platforms.

Figure 5 - Creating a new domain	Figure 6 - Specifying the properties of the new domain

Figure 7 - Default value for the new domain

Figure 8 - Using a domain to specify the data type of an attribute.

To describe attributes, right-click on an entity and select the Attribute Editor item from the menu that appears. The Attribute Editor dialog will appear.

If you click on the New button, then in the New Attribute dialog that appears, you can specify the name of the attribute, the name of the column corresponding to it in the physical model, and the domain. The attribute domain will be used when defining the column type at the physical model level.

Primary key attributes on the General tab of the Attribute Editor dialog must be checked in the Primary Key selection box.
The Definition, Note and UDP tabs carry the same functions as in defining an entity, but at the attribute level.

For greater clarity of the diagram, each attribute can be associated with an icon. This can be done using the Icon selection list on the General tab.

It is very important to give the attribute the correct name. Attributes must be named in the singular and have a clear semantic meaning.

According to the IDEF1X syntax, the attribute name must be unique within the model (not just within the entity!). By default, when you try to enter an already existing attribute name, ERwin renames it. For example, if the attribute Comment already exists in the model, another attribute (in a different entity) would be named Comment/2, then Comment/3, and so on.
When transferring attributes within and between entities, you can use the drag&drop technique by selecting a button in the tool palette.

To create a new relationship, select an identifying or non-identifying relationship in the tool palette (ERwin Toolbox), click first on the parent and then on the child entity.
On the tool palette, a button corresponds to an identifying relationship, a many-to-many relationship button, and a button 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 power, name and type of connection.

Communication power (Cardinality)- serves 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:

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 when one instance of the parent entity corresponds to a predetermined number of instances of the child entity.

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

Link type (identifying/non-identifying).

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 relationship to a dependent relationship. A dependent entity is represented by a rectangle with rounded corners.

An instance of a dependent entity is only defined through a relation to the parent entity. 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 keys - (FK).

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

An identifying relationship is shown in the diagram as a solid line with a bold dot at the child end of the relationship, a non-identifying relationship is shown as a dotted line.

For a non-identifying relationship, you can specify whether it is mandatory (Nulls on the General tab of the Relationship Editor dialog). 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

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 a many-to-many relationship, both Parent-to-Child and Child-to-Parent names must be specified. To display the name, use the context menu that appears when you right-click anywhere in the diagram that is not occupied by model objects, select the Display Options/Relationship item, and then enable the Verb Phrase option.

Role name or functional name (Rolename) is a synonym for a foreign key attribute that indicates what role the attribute plays in the child entity. You can set the role name in the Rolename/RI Actions tab of the Relationship Editor dialog.

Creation of keys.

Each entity instance must be unique and distinct from other attributes.

Primary key is an attribute or group of attributes that uniquely identifies an instance of an entity. Primary key attributes in the diagram don't require special designation - they are those attributes that are in the list of attributes above the horizontal line. When adding a new attribute in the Attribute Editor dialog, in order to make it the primary key attribute, the Primary Key checkbox at the bottom of the General tab must be enabled. In the diagram, the key attribute can be added to the primary key using the attribute transfer mode (button in the tool palette).

One entity may have several attributes or sets of attributes that claim to be the primary key. Such applicants are called candidate keys.

Keys can be complex, i.e. containing multiple attributes. Complex primary keys don't need special notation - it's a list of attributes above the horizontal line. When choosing a primary key, preference should be given to simpler keys, i.e. keys containing fewer attributes.

Many entities have only one candidate key. This key becomes the primary key. Some entities may have more than one possible key. Then one of them becomes the primary key and the rest become alternate keys.

Alternative Key is a candidate key that has not become a primary key.

Each key corresponds to an index, the name of which is also assigned automatically. The key and index names can be changed manually if desired.

In the diagram, the attributes of alternative keys are denoted as (Akn.m.), where n is the ordinal number of the key, m is the ordinal number of the attribute in the key. When an alternate key contains multiple attributes, (Akn.m.) is placed after each.

Foreign Keys are created automatically when a relationship connects entities: the relationships form a link to the primary key attributes in the child entity and these attributes form a foreign key in the child entity (key migration). Foreign key attributes are denoted by a (FK) character after their name.

A dependent entity can have the same key from multiple parent entities. An entity can also obtain the same foreign key multiple times from the same parent through multiple different relationships. When ERwin encounters one of these events, it recognizes that the two attributes are the same and places the foreign key attributes on the dependent entity only once. This combination or union of identical attributes is called unification.

There are cases when unification is undesirable. For example, when two attributes have the same name, but in fact they differ in meaning, and it is necessary that this difference be reflected in the diagram. In this case, you must use the foreign key role names.

Relationships in the diagram are represented by lines going from one entity (table) to another. Each link is assigned a unique name. Related tables are divided into parent and child tables. Parent tables are displayed as rectangular rectangles, child tables are rounded.

After specifying all the attributes of the data format, it is necessary to convert the created logical model into a physical one. For this it is necessary in Tools choose Derive New Model, where for Target Databases select ODBC/Generic(for use in the MySQL DBMS) see Figure 9. Our model (see Figure 4) will be converted to the form see Figure 11.

Figure 9 - Converting a logical model to a physical one

Figure 10 - Physical model indicating the data format.

Figure 11 - SQL code generation

Exercise

1. Build a diagram with given entities (direct modeling) for a given subject area.

2. Set attributes for each defined entity. When setting attributes, use domains.

3. Enter relationships between entities. Give links unique names.

4. Using the MYSQL DBMS, solve the direct generation of the database for the projected information.

5. The report must contain a conceptual model and a physical database in the MYSQL DBMS.

test questions

1. What is the difference between the logical and physical levels of representing data models using ERwin?

2. What is the difference between data models presented in the form of an entity-relationship diagram, based on keys and in the form of a full attribute model?

3. What are the main components of the data models presented according to the IDEF1X methodology?

The list of data types supported by the DBMS must be checked with the manufacturer

6. Modeling in ERwin

ERwin's place in information modeling
The process of building an information model consists of the following steps:

definition of entities;
determination of dependencies between entities;
setting primary and alternate keys;
definition of 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 constraints;
database generation.

ERwin creates a visual representation (data model) for the problem being solved. This representation can be used for detailed analysis, refinement and dissemination as part of the documentation needed in the development cycle. However, ERwin is far from being just a drawing tool. ERwin automatically creates the database (tables, indexes, stored procedures, referential integrity triggers, and other objects needed to manage 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 display of facts from real life. For example, people, tables, departments, dogs, and computers are real objects. They are named in natural language, with any word separators (spaces, commas, etc.). At the logical level, the use of a particular DBMS is not considered, data types (for example, integer or real number) are not defined, and indexes for tables are not defined.
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 presentation levels of a single diagram (model), as well as having many display options at each level.

ERwin Chart Components and Basic Chart Views

The ERwin diagram is built from three main blocks - entities, attributes and relationships. If we consider the diagram as a graphical representation of the rules of the subject area, then entities are nouns, and relationships are verbs.
The choice between the logical and physical level of display is carried out 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; is used to conveniently view a large diagram or place entity rectangles on the diagram.
The entity definition mode is used to present the diagram to other people.
Attributes mode. When moving from the subject area to the model, it is required to enter information about what constitutes the entity. This information is entered by setting attributes (at the physical level - columns of tables). In this mode, the entity-rectangle 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 rest of the attributes are displayed in the lower part. This mode is the main one when designing at the logical and physical levels.
Mode "primary keys" - inside the rectangles - only the attributes/columns that make up the primary key are shown.
Icon mode. For presentational purposes, each table can be assigned an icon (bitmap).
Verb phrase display mode. Link arcs show verb phrases linking entities (for the logical level) or foreign key names (for the physical level).

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

Tools for creating a model in ERwin

The main tools for creating a model are available both from the menu and through the tool 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.
Clicking the mouse over an entity enters one of the many ERwin editors:

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

Entity identification. Entities in ERwin

In the 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 attributes of an entity into two sets - attributes that make up the primary key in the upper part and 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 share 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:

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 carried out by the developer based on the 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 determining its relationships to other entities; a dependent entity, on the other hand, cannot be uniquely identified without determining its relationships to other entities. A dependent entity is displayed in ERwin as a rounded rectangle.

Relationships in ERwin

A relationship is a functional dependency between two entities (in particular, an entity may be associated with itself). For example, it is important to know the name of an employee, 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" (a department consists of employees). Relationship is a logical level concept that corresponds to a foreign key at the physical level. In ERwin, relationships are represented by five main pieces of information:

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

A relationship is called identifying if an instance of a child entity is identified through its relationship to the parent entity. The attributes that make up the primary key of the parent entity are included in the primary key of the child entity. A child entity in an identifying relationship is always a dependent entity.
A relationship is said to be non-identifying if an instance of a child entity is identified other than through a relationship with the parent entity. The attributes that make up the primary key of the parent entity are included in the non-key attributes of the child entity.
To define ERwin relationships, select the relationship type, then click on the parent and child entities. An identifying relationship is shown as a solid line; non-identifying - dotted line. The lines end with a dot on the side of the child entity.
When a relationship is defined, the primary key attributes of the parent entity are migrated to the corresponding attribute area of the child entity. Therefore, such attributes are not entered manually.
Primary key attributes of the parent entity are migrated with their 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. In the case of multiple migrations of an attribute, such a renaming is necessary. For example, the entity "intermediary transaction" has the attribute "code of the company-seller" and "code of the company-buyer". 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 cardinality of a link is the ratio of the number of instances of the parent entity to the corresponding number of instances of the child entity. For any relationship other than non-specific, 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 character in the child entity: zero, one or more (by default); zero or one; exactly N, where N is a specific number.
The admissibility of NULL values in non-identifying relationships ERwin depicts an empty diamond on the arc of the relationship from the side of the parent entity.
Power designations, respectively, zero, one or more, one or more, zero or one in the IE notation are shown in Fig. one.

Fig.1. Link power notation in IE notation

The link name at the logical level is a "verb" linking entities. The physical name of the link (which may differ from the logical one) for ERwin means the name of the constraint (constraint) or index.

Graphical Model Editing

All objects of the ERwin model can be edited by means accepted in Windows - grouping, copying, deleting, moving, using the system buffer. Colors and fonts are set in convenient dialogs.
Model components represented by text (names of entities, attributes, text elements) can be edited directly on the screen.

Alternative Keys

An alternate key is an attribute (or group of attributes) that is different from the primary key and uniquely identifies an instance of an entity. For example, for the entity employee (employee ID, last name, first name, patronymic), the group of attributes "last name", "first name", "patronymic" can be an alternative key (assuming that full namesakes do not work at the enterprise).
For an alternative key, as well as for a primary one, ERwin automatically creates indexes when generating a database.

Inverted indices

The attributes that make up the alternate key unambiguously (uniquely) identify entity instances. In ERwin, you can also compose attribute groups that do not uniquely identify entity instances, but are often used to access data. For each such attribute group, ERwin creates non-unique indexes.
The same entity attributes can be included in several different key groups.

Attribute Unification

A dependent entity can inherit the same foreign key from more than one parent entity, or from the same parent entity through multiple relationships. Unless distinct roles are introduced for such multiple inheritance, ERwin considers the foreign key attributes to appear only once in the dependent entity.
Unification is the union of two or more foreign key attribute groups into one foreign key (attribute group), on the assumption that the values of the same-named attributes in the child entity are always the same.
Let's consider an example: the entity "employee" has the primary key "employee code" and is linked by an identifying relationship with the entities "spouse" and "children". In this case, the primary key is migrated to dependent entities. In turn, the "spouse" entity has a non-identifying relationship with the "children" entity. There are two key migration paths, however, in the children entity, the employee ID attribute appears once as an element of the primary key.
There are cases when the unification of attributes gives an incorrect result from the point of view of the subject area. To unify attributes, role names are entered.

Some entities define an entire category of objects of the same type. In ERwin, in this case, an entity is created to define the category and for each element of the category, and then a categorization relationship is entered for them. The parent entity of a category is called a supertype, and the children are called a subtype.
For example, the entity "employee" can contain data about both full-time employees and temporary employees. The first and second have different, partially overlapping sets of attributes (the minimum intersection of subtypes is the primary key). The common part of these attributes, including the primary key, is placed in the employee supertype entity.
The different part (for example, hourly pay data for temporary workers and salary and vacation data for full-time workers) are placed in subtype entities.
In the entity-supertype, a discriminator attribute is introduced, which makes it possible to distinguish between specific instances of the entity-subtype.
Depending on whether all possible subtype entities are included in the model, the categorical relationship is complete or incomplete. Continuing the example, if a supertype can contain data about laid-off employees, then this relationship is an incomplete categorization, since there is no entry for it in entities - subtypes.
In ERwin, a full category is represented by a circle with two underlines, and an incomplete category by a circle with one underline.

Implementing Referential Integrity with ERwin

Referential integrity is the enforcement of the requirement that the foreign key values of a child entity instance match the primary key values in the parent entity. Referential integrity can be controlled for all operations that change data (INSERT/UPDATE/DELETE). Referential integrity controls in ERwin include automatic generation of triggers and the use of declarative referential integrity mechanisms (for those DBMS that support these mechanisms).
For each connection at the logical level, requirements can be set for processing INSERT / UPDATE / DELETE operations for the parent and child entities. ERwin provides the following options for handling these events:

lack of verification;
validation;
prohibition of the operation;
cascading operation execution (DELETE/UPDATE);
setting an empty (NULL value) or specified default value.

According to the selected option, ERwin automatically creates the necessary triggers in the SQL dialect of the target DBMS. At the same time, ERwin uses a library of trigger templates that can be modified.
When generating a database structure, referential integrity triggers can be overridden at three levels:

Triggers can be overridden to provide rules for the entire model.
The triggers specified for a particular relationship can be overridden.
Triggers specified for a particular table can be overridden.

The override type is specified by the developer when generating the database schema (Fig. 6 - respectively RI Type Override, Relationship Override, Entity Override).

Storing Information in an ERwin Model

Typically, ERwin models are saved to disk as a file. It is possible to store the model in the target DBMS. To do this, using ERwin itself, an ERwin metabase is created in the target DBMS. Model information is stored in this database. In a particular case, the database can also be dBase files that ERwin works with via ODBC.

An example of model development in ERwin

Consider the development cycle using the example provided in Codd's article.
Let us briefly recall the content side of the problem. Employee records are maintained. For each employee, information is stored about the children and the list of positions held by this employee. For positions, information on established official salaries is stored.
First, let's create the logical level of the model. To do this, set the display mode for entities (Display/Entity Level). Using the toolbar, we will create the entities "employee", "children", "work history", "salary history". We will name entities in Russian.
After selecting each entity, we will set a detailed description for it in Russian in the "Entity Definition" editor. This description will appear in ERwin reports and can be displayed in a diagram.
Let's specify connections between entities. For example, "employee" has an identifying relationship "is a parent" to the entity "children". The description of the relationship is entered in the "Editor/Relationship" editor.
The result of the work is displayed on the ERwin diagram (Fig. 2).

Rice. 2. Entity level diagram

Now let's switch to the attribute setting mode (Display/Atribute Level). In the "Entity/Attribute" editor, let's set the names of key and non-key attributes in Russian. Note that for the child entity "children", the key attribute "employee number" is not specified manually. ERwin provides its migration from the parent entity. The same happens with other child entities.
For the "name" attribute of the "employee" entity, we indicate that it is an alternative key (we will assume that all employees have unique first / last names). To do this, after the attribute name, we place the pointer AK1 in brackets.
The result of the work is displayed on the ERwin diagram (Fig. 3) in IDEF1X notation.

Rice. 3. Attribute level diagram in IDEF1X notation

The view of the same diagram in IE (Information Engineering) notation is shown in Fig.4.

Rice. 4. Attribute Level Diagram in IE Notation

Since the names of attributes and entities were set by us in Russian, to go to the physical level of the model, they should be associated with identifiers of tables, columns, and constraints that satisfy the rules of the target DBMS (usually this means using Latin letters, numbers, and some special characters).
In the "Database Schema" editor, specify the corresponding table name for each entity. Then, in the "Attribute Definition" editor, we set the names of the columns of the tables corresponding to the attributes of the entities. ERwin also provides for the migration of column names to subordinate tables.
At this stage, you can also use the "Extended Attributes" editor to define PowerBuilder's extended attributes (display format, edit mask, control rule, alignment, headings, and comments).
The "Relationship Definitions" editor specifies the physical name of the relationship, which corresponds to the name of the constraint created by ERwin in the database.
Now everything is ready to create a database and you need to select the target DBMS (if this has not been done before). Let's choose, for example, Sybase System 10.
In the SYBASE Database Schema editor, we set the data types for the table columns.
The dialog in which the data type is selected is shown in Fig.5.

Rice. 5. Definition of the physical model

Now we can move on to creating the database. To do this, the "Sybase schema generation" command is executed. ERwin will build a database generation SQL package. Figure 6 shows the dialog for selecting the parameters for generating a package for generating a database. The figure shows that a filter can be set (generation of not all tables), a package of SQL statements can be viewed (preview), printed, saved to a file (report), and generated (generate).

Rice. 6. Selecting database generation options

7. Advanced features of ERwin

Reverse engineering

Reverse engineering, that is, restoring the information model from an existing database, is used when choosing the optimal platform (rightsizing) for an existing desktop database or a database on the mainframe, as well as when expanding (or modifying) an existing structure that was built without necessary supporting documentation. After the model recovery process is completed, ERwin automatically "spreads" the tables on the diagram. Now you can perform modifications already using the logical scheme - add entities, attributes, comments, links, etc. Upon completion of the changes, one command - synchronize the model with the database - updates all the changes made.
Model building can be performed either on the basis of the database catalog data or on the basis of the SQL statement package with which the database was created.

Database Synchronization

In the process of developing an information system, a situation may arise when the database structure and the information model do not correspond to each other. ERwin provides an opportunity to bring them into line.
For this, a synchronization function with the database is provided. After connecting to the DBMS, a list of inconsistencies between the existing data structure and the model is offered. For example, if a new table is created in the database, then ERwin will offer to include it in the model. If a new table is added to the model, ERwin will offer to create it in the real database. Similarly, when adding columns to a database or model, ERwin offers to perform the appropriate synchronization operations. The procedure for selecting synchronized tables is shown in Figure 7.

Rice. 7. Selecting Synchronized Tables

ERwin "knows" about such features of data storage in individual DBMS as segments (in Sybase) and table space (in Oracle). Physical placement information can be included in the model and used in forward and reverse engineering.

Interfaces to DBMS

ERwin supports a direct interface with the main DBMS: DB2 versions 2 and 3, Informix versions 5.1, 6.0, 7.1, Ingres, NetWare SQL, ORACLE versions 6 and 7, Progress, Rdb versions 4 and 6, SQL/400 versions 2 and 3, SQLBase versions 5 and 6, SQL Server versions 4 and 6, Sybase version 4.2, Sybase System 10 and 11, Watcom SQL. Note that both the most modern and previous versions of the main DBMS are supported (Fig. 8).

Rice. 8. Choosing a DBMS for creating a model

ERwin also supports desktop (desktop) DBMS: Microsoft Access, FoxPro, Clipper, dBASE III, dBASE IV and Paradox.
The design at the physical layer is done in terms of the database that is supposed to be used in the system. It is important that ERwin "knows" the correspondences between the capabilities of DBMSs from different vendors, so it is possible to convert a physical schema designed for one DBMS to another.
To create the physical structure of the database, the generation of a DDL script (data definition language) can be requested. This uses the SQL dialect for the selected server type and version. Although the generated code does not need to be modified, it is possible to save it to a file or print it.

Support for 4GL tools

ERwin is available in several different editions targeting the most common 4GL development tools. Supported tools include PowerBuidler by Powersoft, SQL Windows by Gupta, Visual Basic by Microsoft, Oracle*CASE by Oracle.
ERwin's bi-directional database interactions provide both back-end and client-side information management. For example, for PowerBuilder, you can view/edit extended attributes in ERwin editors.
ERwin's focus on 4GL tools allows you to set for future applications most of the parameters directly related to the database, already at the design stage of the information model.
Let's show the principles of organizing such interaction on the example of PowerBuilder.
PowerBuilder creates several internal tables in the database to store its repository (extended attributes for datawindow). The use of extended attributes ensures that the display style of the same database columns is maintained for all applications created by a team. Extended attributes set parameters such as display format, editing style, validation expression, initial value, alignment, display element width and height, edit form label, table display title.
The same synchronization operations are allowed for extended attributes as for the entire model, i.e. descriptions can be loaded into the database and, conversely, extended attribute definitions created from the PowerBuilder environment can be loaded from the database into ERwin for modification.
An example of defining extended attributes is shown in Figure 9.

Rice. 9. Setting PowerBuilder Extended Attributes

The ERwin function for generating DataWindow allows you to generate prototypes of data windows of a future application already at the stage of creating an information model. To create a Data Windows, a Wizard is offered, with the help of which the window style and the selected columns of the tables are specified.

Rice. 2. Entity level diagram

Rice. 3. Attribute level diagram in IDEF1X notation

The view of the same diagram in IE (Information Engineering) notation is shown in Fig.4.

Rice. 4. Attribute Level Diagram in IE Notation

Rice. 5. Definition of the physical model

Rice. 6. Selecting database generation options

Advanced features of 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 " Buyer" 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 Buyer 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 a 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 a 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.

Description of the ERwin interface. The CASE interface of the ERwin tool consists of three main parts. The first is the main menu and toolbars.

The buttons on the toolbars repeat some of the basic commands on the main menu. Save, open, create a new file, a panel with buttons to zoom in or out model display, a switch between physical and logical model, a switch between stored displays, a panel for editing the style, size and color of fonts, a panel with tools for constructing geometric shapes and several auxiliary toolbars (Fig. 5.3).

Rice. 5.3.

The second is the Model Explorer. It contains three tabs: Model, Subject Areas and Domains. The most commonly used in the Model Explorer is the Domains or Model tab (which contains all objects and models). In Domains, respectively, domains are displayed, in Subject Areas - displayed areas (Fig. 5.4).

Rice. 5.4.

And the third is directly the area reserved for creating an object model, in which all objects of the model are created and edited. Tabs appear at the bottom with the names of the stored displays (Stored Displays) (Fig. 5.5).

Rice. 5.5.

ERwin has two levels of model data representation: logical and physical. logic level- this is an abstract view of the data, on it the data is presented as it looks in the real world, for example, "Customer", "Workshop" or "Employee's last name". Model objects that are represented at the logical level are called entities and attributes. The logical data model may be built on top of another logical model, such as a process model. The logical data model is universal and is in no way associated with a specific DBMS implementation.

Physical model data, on the contrary, depends on the specific DBMS, in fact, being a display of the system catalog. The physical model contains information about all database objects. Since there are no standards for database objects (for example, there is no standard for data types), the physical model depends on the specific implementation of the DBMS. Therefore, the same logical model may correspond to several different physical models. If in the logical model it does not matter what specific data type an attribute has, then in the physical model it is important to describe all the information about specific physical objects - tables, columns, indexes, procedures, etc. Dividing the model into logical and physical allows you to solve many important tasks.

ERwin has several levels of diagram display: entity level, attribute level, definition level, primary key level and icon level. You can switch between the first three levels using the toolbar buttons. You can switch to other display levels using the context menu that appears if you “click” on any place in the diagram that is not occupied by model objects. In the context menu, select the Display Level item and then the required display level. ERwin allows you to associate large and small icons with an entity. When switching to the icon level, a large icon is shown. To display a small icon, select the Entity Display/Entity Icon item from the context menu. A small icon will be shown to the left of the entity name at all model display levels.

Set color and font. There are several ways to set the font and color of objects in ERwin. First, to set the color and font of the object, use the Font and Color Toolbar, which is located under the main panel. To edit the font and color of a particular object, by right-clicking on an entity or relationship and selecting the Object Font & Color... item from the pop-up menu, call up the Font/Color Editor dialog, which defines the name, description, and comments of the entity. In the Font/Color Editor dialog, you can select a font and set its size, style and color, set the fill color (Fill Color property, for entities only) and line color (Outline Color property, for entities only).

When creating real data models, the number of entities and attributes can be in the hundreds. For more convenient work with large models, ERwin provides model subsets (Subject Areas), in which you can include thematically common entities. A model subset can include an arbitrary set of entities, relationships, and text comments. To create, delete or edit model subsets, you need to call the Subject Areas dialog (Model/Subject Areas... menu), in which you specify the name of the subset and the entities included in it. All changes made in any Subject Area are automatically reflected on the general model. The same entity can be included in several Subject Areas.

Stored Display- representation of a subset of the model, displaying a specific aspect of the data structure. One Subject Area may include several stored mappings. The stored display includes the same entities and relationships as in the Subject Area, but they can be located differently on the screen, have different levels, different scales and colors of objects or backgrounds.

To create a stored display, use the Stored Displays dialog (Format/Stored Display Settings... menu). To switch between stored displays, use the tabs at the bottom of the diagram.

The main components of an ERwin diagram are entities, attributes, and relationships. Each entity is a set of similar individual objects, called instances. Each instance is individual and must be different from all other instances. An attribute expresses a particular property of an object. From the point of view of the database (physical model), an entity corresponds to a table, an instance of an entity - a row in a table, and an attribute - a column of a table.

Creation of a logical data model for the subject area "Furniture to order". The created logical model repeats the structure of the designed IS. In order to create an entity in the area for creating object models, it is necessary (after making sure that you are at the level of the logical model: the switch between the logical and physical model is the drop-down list on the right side of the toolbar) “click” on the entity button on the toolbar ( ERwin Toolbox) Q , then click on the place on the diagram where you want to place the new entity. By right-clicking on an entity and selecting Entity Properties... from the pop-up menu, you can call up the Entities dialog, which defines the name, description, and comments of the entity (for example, entity name - supplier, description - supplier data). Each entity is defined with a textual description on the Definition tab. The Note, Note 2, Note 3, UDP (User Defined Properties) tabs are used to add additional comments to the entity. The next step is to create entity attributes. As mentioned above, each attribute stores information about a specific property of an entity, and each instance of an entity must be unique. An attribute or group of attributes that identifies an entity is called a primary key. To create attributes, right-click on an entity and select the Attributes... item from the menu that appears. The Attributes dialog appears. If you click the New... button, then in the New Attribute dialog that appears, specify the name of the attribute, the name of the column corresponding to it in the physical model, and the domain (for example, the name of the attribute is the name of the supplier). The attribute domain will be used when defining the column type at the physical model level. Primary key attributes on the General tab of the Attributes dialog must be checked in the Primary Key selection box.

To display an attribute icon, select the Entity Display item in the context menu and enable the Attribute Icon option in the cascade menu. A small icon will be shown to the left of the attribute name at the model display attribute level. The name of the entity is displayed above the rectangle depicting the entity, the list of entity attributes is shown inside the rectangle. The list is separated by a horizontal bar, above which are the primary key attributes, below which are the non-key attributes. Attributes must be named in the singular and have a clear semantic meaning. Compliance with this rule allows you to partially solve the problem of data normalization already at the stage of defining attributes. For example, creating the Supplier Phones attribute in the Vendor entity is against normalization requirements, since the attribute must be atomic, i.e., not contain multiple values. According to the IDEF1X syntax, the attribute name must be unique within the model (not just within the entity!). Each entity instance must be unique and distinct from other attributes. The next step in creating a model is to establish relationships between entities. Each relationship should be called a verb or a verb phrase (Relationship Verb Phrases Fig. 5.6). The relationship name expresses some constraint or business rule and makes the diagram easier to read, for example:

Each CUSTOMER ORDERS;

Each ORDER DESIGN.

Rice. 5.B. Relationship Name - Relationship Verb Phrases

To create a new connection:

place the cursor on the required button in the tool palette (identifying or non-identifying relationship) and press the left mouse button;
click first on the parent and then on the child entity. When relationships are established between entities, the primary key attributes of the parent entity are migrated as foreign keys to the child entity. 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 Relationship Display item and enable the Verb Phrase option in the context menu.

The logical data model of the subject area "Furniture to order" is shown in fig. 5.7.

Rice. 5.7.

The complete attribute model represents data in the third normal form and includes all entities, attributes and relationships, and is shown in Fig. 5.8.

At the entity level, the model is shown in Fig. 5.9.

On fig. Figure 5.10 presents the data model at the definition level.