Erwin Basics. Building a logical data model. Model design in ERWin Fig.1. Communication power notation in IE notation

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

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

Rice. 5.3.

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

Rice. 5.4.

And the third is the area directly allocated for creating an object model, in which all model objects are created and edited. Bookmarks appear at the bottom with the names of stored 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, in which the data is presented as it looks in the real world, for example, “Customer”, “Workshop” or “Employee’s name”. Model objects represented at the logical level are called entities and attributes. The logical data model can be built on the basis of another logical model, such as process models. The logical data model is universal and is in no way related to a specific DBMS implementation.

Physical model data, on the contrary, depends on the specific DBMS, in fact being a reflection of the system catalog. IN 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. Consequently, several different physical models can correspond to the same logical model. If in a logical model it does not matter what specific data type an attribute has, then in a 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 keys 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 context menu You should select Display Level and then the desired 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 Entity Display/Entity Icon in the context menu. A small icon will be shown to the left of the entity name at all levels of the model display.

Set color and font. There are several ways to set the font and color of objects in ERwin. Firstly, to set the color and font of an object, use the Font and Color Toolbar, which is located under the main panel. To edit the font and color of a specific object, click right click mouse on an entity or connection and selecting Object Font & Color... from the pop-up menu, call the Font/Color Editor dialog, in which the name, description and comments of the entity are defined. 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, only for entities) and line color (Outline Color property, only for entities).

When creating real data models, the number of entities and attributes can be in the hundreds. For more comfortable work with larger models ERwin provides model subsets (Subject Areas), into which thematically common entities can be included. 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 indicate the name of the subset and the entities included in it. All changes made in any Subject Area are automatically reflected on general model. The same entity can be included in several Subject Areas.

Stored Display- a representation of a subset of the model, displaying a specific aspect of the data structure. One Subject Area can include several stored views. The stored display includes the same entities and relationships as the Subject Area, but they may be positioned differently on the screen, at different levels, at different scales, and at different object or background colors.

To create a stored display, use the Stored Displays dialog (menu Format/Stored Display Settings...). To switch between stored views, 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 copy is individual and must be different from all other copies. An attribute expresses a specific 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 corresponds to a row in the table, and an attribute corresponds to a table column.

Creation of a logical data model for the “Custom-made furniture” subject area. The created logical model repeats the structure of the designed IC. In order to create an entity in the area for creating object models, you must (after making sure that you are at the logical model level: 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 open 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 using a text description in the Definition tab. The Note, Note 2, Note 3, UDP (User Defined Properties) bookmarks are used to add additional comments to the entity. The next step is to create entity attributes. As stated 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 the entity and select Attributes... from the menu that appears. The Attributes dialog appears. If you click on 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 attribute name is the name of the supplier). The attribute domain will be used when defining the column type at the physical model level. For primary key attributes, in the General tab of the Attributes dialog, you must make a mark in the Primary Key selection window.

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 shown above the rectangle depicting the entity, the list of entity attributes is shown inside the rectangle. The list is divided by a horizontal line, 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 us to partially solve the problem of data normalization already at the stage of determining attributes. For example, creating the Supplier Phones attribute in the Supplier entity contradicts the normalization requirements because the attribute must be atomic, that is, not contain multiple values. According to IDEF1X syntax, the attribute name must be unique within the model (and not just within the entity!). Each instance of an entity 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 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 IS DESIGNED.

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

To create a new connection:

• place the cursor on the desired button in the tool palette (identifying or non-identifying connection) and click left button mice;
• 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 migrate as foreign keys to the child entity. By default, the connection name is not shown in the diagram. To display the name, in 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 “Furniture to order” subject area is shown in Fig. 5.7.

Rice. 5.7.

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

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

In Fig. Figure 5.10 presents the data model at the definition level.

Rice. 5.8.

Rice. 5.E. Data Model Entity Layer

A connection is a functional dependence between two entities (in particular, an entity can 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 depicted additional character 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.

1. Study questions

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

2. Lesson plan

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

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

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

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

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

4. Technological process for completing tasks

4.1. Technological process for creating data models

4.1.1. Methodology for creating models (IDEF1X methodology)

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

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

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

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

The process of creating a model involves the following steps:

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

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

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

4.1.2. Technological process for creating a logical data model

During the model creation process, entities can be introduced by importing from an entity dictionary developed in BPwin, or by creating using the button Entity on the toolbar.

Importing entities into ERwin

Notes

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

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

Establishing relationships between entities

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

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

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

4.1.3. Technological process of creating a physical data model

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

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

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

1. In the window Computer Associates ERwin – )
   Type