ERwin tool/steps to create sample data model with ERwin tool

Introduction

ERwin is a popular data modeling tool used by a number of major companies in Omaha and throughout the world.  The product is currently owned, developed, and marketed by Computer Associates, a leading software developer.  The product supports a variety of aspects of database design, including data modeling, forward engineering (the creation of a database schema and physical database on the basis of a data model), and reverse engineering (the creation of a data model on the basis of an existing database) for a wide variety of relational DBMS, including Microsoft Access, Oracle, DB2, Sybase, and others.

This brief tutorial steps you through the process of creating a data model using ERwin r8.0.   It will not explain all aspects of ERwin, but will show you the minimum necessary to create and use data models for this class.  It consists of three major segments, which correspond to the project-related assignments in your class: 

Creation of a basic data model (Conceptual data model)

1. Creation of a database schema
2. Creation of the database

This tutorial is a static one, suitable for printing. A tutorial using screen captures and narration is also available (currently, for ERwin 7.0).

Section 1. Creation of a basic data model

You will be creating a data model similar to that created for the Heartland Properties case study used in class.  The Entities involved in this model include:  Employee, Office, Property, and Inspection.  

Heartland Properties Case Study

Heartland Properties, Inc. (HPI) is a property management company that manages rental properties on behalf of owners.   The company offers a comprehensive collection of services to these owners, including advertising the property, interviewing potential renters, negotiating leases, inspecting and maintaining the property, and collecting rent from the renters.  The following is a brief description of the kinds of data Heartland Properties is concerned with.

Offices

HPI has offices in several cities throughout the Heartland.   Each office is identified by an Office Number.   Each office has an address (Street, City, State, Zipcode), and telephone and fax numbers.  Each office also has a year in which it was opened.

Employees

Employees of HPI are assigned to work at branch offices.  Each employee has an employee identifier, first and last names, a social-security number, as well as a home address and telephone number.  For each employee, HPI keeps track of when that employee was hired, the employee's gender, and job title.  For each employee, HPI keeps track of a single next-of-kin who may be notified in the event of an emergency.  

Employees are divided into three categories:  Managers, Associates, and Administration Assistants.  Managers are salaries employees and a given a monthly allowance for maintaining a company car.   Associates and administrative assistances are both hourly employees, and HPI tracks their hourly rate.  

Each office has one senior manager, and several regular managers.  Each manager manages up to 10 associates and administrative assistants.

An example of the form used to record these details is shown below.

Owners

Owners own the properties HPI manages.  Owners may be either business or private owners.  In either case, an owner is given a unique owner number, and HPI records the owner's name, address, and telephone number.  For owners that are businesses, HPI also must record the name of a contact person.

Properties

Each property has a unique property number, as well as an address.  Moreover, the property is managed by a particularly HPI office.  Each property has a type (house, apartment), a particularly number of rooms, and a particular number of baths.

Renters

In order for the business to work effectively, HPI must be able to identify quickly the properties that are of potential interest to a client.  Consequently, when a client approaches HPI, the company records the client's maximum monthly rent, the type of property (apartment, house), and desired number of rooms. The basic client data include a unique client number, the client's name, address, and phone number.   HPI also records the employee who initially interviews the renter.

The Rental Process

When a client, a potential renter, approaches HPI, a process begins which continues until HPI's association with that renter has ended.  Following the preliminary interview, the following stages may be followed:

1. Viewing properties. A client may wish to see one or more properties before deciding to lease.  HPI must keep track of which property was viewed, the date it was viewed, the HPI employee who accompanied the client, and any comments the client made about the property.
2. Drawing up of a lease.   If a client agrees to lease a particularly property, HPI draws up a lease.  The lease has a unique lease identifier, and includes details of the lease, including the property to be rented, the monthly rental, the rental deposit, the start and end dates of the lease, and the manager who signs off on the lease.
3. Collection of rent.  When a client rents a property, he or she is responsible for monthly payments.  Each payment is recorded by noting the day on which payment was received, the amount of the payment, the property for which payment was made, the renter making the payment, and the number on the check used to make the payment.

Property Management

One of the services HPI provides to owners is the regular inspection and maintenance of the properties.  Each property is inspected no less frequently than every six months.  Whenever a lease ends, the property is also inspected.  For each inspection, HPI keeps track of the date, property, and HPI employee making the inspection.  The employee's comments following the inspection are also recorded.

Management Reports

Rental listings

Each day, each HPI office prints out a list of all of the properties currently available for rent.  An example of such a report is found below.

Property Inspection Report

Periodically, management wishes to see a record of the inspections performed on a particular property.  One such listing appears as follows:

First, invoke ERwin from Start->Programs->CA->Erwin-> ERwin Data Modeler r8->  ERwin

You will first encounter a dialog box entitled "Erwin Data Modeler Tips".  Simply click on Close.

From the File Menu choose to create a new model: File->New

Figure 1: Create a new model

The next dialog box, shown in Figure 2, will ask you to choose the template to be used to create the new model.  At this point, you may associate the model with a target DBMS product, such as Oracle, DB2, etc.  You will have the opportunity to change this choice later, but for now choose Oracle.  Also, choose Logical/Physical as the new model type.  This choice will allow us to switch back and forth easily between a logical model (ER Diagram) and a physical model (database schema).

Figure 2: Selecting a model type

ERwin will now display the main window from which most of your ER diagram development will be done, as shown in Figure 3.

Figure 3: The ERwin Workplace

The ERwin workplace consists of two main parts.  On the left is the Model Navigator, which displays a hierarchy of items of importance, such as entities, domains, and subject areas.   On the right is the Display Window, which will show the ER diagram itself.  As you create objects, they will appear in the display window (if they are visual in nature, like entities), and appear in the hierarchy within the Model Navigator.  

Setting Preferences

A tool like ERwin can accomodate a number of data modeling notations and conventions.   In my class please make the following changes to the preferences before you begin to create your model: Right-Click on the blue background of the Display Window and select Properties

1. From the Entity Tab make sure that Primary Key Designator is checked
2. From the Entity Tab make sure that Foreign Key Designator (FK) is unchecked
3. From the Entity Tab make sure that Show Migrated Attributes is unchecked
4. From the Display Tab make sure that Display Child-To-Parent Verb Phrase is checked.
5. From the Display Tab make sure that Display Parent-To-Child Verb Phrase is checked.
6. From the Layout Tab make sure that Show Background is unchecked.

In addition, you may choose between two different E-R diagramming notations.  In ERwin, click on Model->Model Properties to see the window shown in Figure 3b:

Figure 3b:   Choice of notation

This tutorial is based on the Information Engineering ("Crows Foot") notation. .

Creating an Entity

To create a new entity, click on the entity icon () on the toolbar, or right-click on the word Entity in the Model Navigator.  If you click on the entity icon, you then should click on the Display Window where you would like the entity to appear, as shown in Figure 4.

Figure 4: A new entity

Notice that the default name for the entity is E/x, where x is some number (2 in this case).  Click on the Tab key several times and notice what happens.  Pressing the tab key cause the focus to cycle between the three main parts of the Entity:   the name of the entity, the primary key attribute(s), and the non-primary key attribute(s).   In general, to modify one of these three parts of the entity, you will press the Tab key to cycle to the appropriate part of the entity, then type to add or modify that part of the entity.  

Right now, press the Tab key until the entity name is highlighted.  Then type EMPLOYEE, as shown in Figure 5.

Figure 5: Changing the name of the entity

At this point, you may wish to save and name your diagram to avoid loss should the system or application crash.

Adding primary key columns

Once you have changed the name to EMPLOYEE, press the Tab key again to move the focus to the next part of the Entity, adding a primary key attribute.  Then type the name of the primary key attribute, Emp_Num, as shown in Figure 6.

Figure 6: Adding a primary key attribute

Notice that because of the preferences you set earlier, the primary key attribute has a key icon next to it.

Press the Tab key one more time to bring the focus below the horizontal line in the Entity, where you will add in a number of non-primary key attributes.   Type Emp_Fname, as shown in Figure 7.   When you have typed Emp_Name, press the Enter key (not Tab).   Notice what happens.  The cursor is now positioned for you to add another attribute in this same portion of the Entity, the non-primary key attribute portion.  

Figure 7: Adding non-primary key attributes

Continue adding the following non-primary key attributes:

Emp_SSN
Emp_street
Emp_city
Emp_state
Emp_zipcode
Emp_phone
Emp_fax

Your diagram should now look like Figure 8.

Figure 8: The Employee entity

Repeat the entity creation process for the entities and attributes listed in Table 1.

Entity	Attributes	Entity	Attributes
OFFICE	Office_Num (pk) Office_Street Office_City Office_State Office_Zipcode Office_Name Office_Phone Office_Fax	INSPECTION	Insp_Date (pk) Insp_Comments
PROPERTY	Prop_ID (pk) Prop_Street Prop_City Prop_State Prop_Type Prop_Baths Prop_Rooms Prop_MonthlyRent	MANAGER	Man_Salary Man_Car_Allowance
		ASSOCIATE	Assoc_Hourly_Rate
Table 1: Entities and Attributes

Notice that some of the entities have no primary key attributes (Manager, Associate), and one entity has an attribute which is part of the primary key, but by itself does not constitute a primary key (Inspection).  As we add relationships to the diagram, the nature of the keys for these entities will become clear.  For the time being, be sure that there are no primary key attributes for Manager and Associate, and only one primary key attribute for Inspection, as shown in Figure 9.

Figure 9: Heartland Properties Entities

Creating Relationships

ERwin supports the creation of relationships with three basic kinds of connectivity:   one-to-one, one-to-many, and many-to-many.  Within the one-to-many category, ERwin allows us to distinguish between identifying and non-identifying one-to-many relationships.    

One-to-many Relationships

We'll begin with two relationships, between Inspection and Employee and between Inspection and Property.  Inspection is related to both Property and Employee in one to many relationships.   Each Inspection is undertaken at one Property; each Property may undergo many inspections.  Each Inspection is carried out by one Employee; each Employee carries out many Inspections.  Are these relationships identifying relationships or non-identifying relationships?   The issue hinges on the nature of the primary key of Inspection, the entity on the "many" side of the relationship.  The primary key of Inspection in this example is a composite primary key consisting of two attributes: {Insp_Date, Prop_ID}  The business rule in effect here is that each property has at most one inspection per day.  One might argue whether or not that is a sound assumption, but lets suppose for the sake of illustration that it is.  Notice that the primary key of Inspection includes the primary key of Property, the entity with which it has a relationship.  For this reason, we say that the relationship between Inspection and Property is an identifying one-to-many relationship.

An identifying relationship is created by clicking first on the identifying relationship icon ().  Notice that it has the crow's foot notation (indicating "many") and a solid line (indicating identifying).   To create an identifying relationship, click first on this icon, then click on the parent entity (on the one side of the relationship) and then click on the child entity (on the many side of the relationship).   In this case, you will click first on the identifying relationship icon, then on Property, then on Inspection.  The results are shown in Figure 10.

Figure 10: An identifying relationship

Notice that ERwin has not provided a label for the relationship, which is not very helpful.  Double-click on the relationship itself to bring up a dialog box in which we can further refine the relationship definition.   Fill out this dialog box as shown in Figure 11.  

Figure 11: Relationships definition

 In the Relationship: text box the relationship is presented in the form parent entity to child entity.  The Verb Phrase portion of this box allows us to define the label to place on the relationship.   Since the parent entity is Property and the Child entity is Inspection, the Parent-to-Child verb phrase will read "Property Undergoes Inspection."  The Child-to-Parent verb phrase will read "Inspection Undertaken at Property."  

In the Relationship Cardinality portion of this window, we can determine how many child entity occurences may be associated with each parent entity occurence.  More specifically, "One Property Undergoes Zero, One or More Inspections."  Notice that at this point we are also able to define participation.   If each property had to have undergone at least one inspection in order to be stored in the database, then we could have forced mandatory participation by choosing the One or More option.  

Click OK to complete the relationship definition, as shown in Figure 12.

Figure 12: Finished relationship

Notice that when an entity participates as the child in an identifying relationship it is modeled with rounded corners.  This notation is used for what in other data modeling tools is called a "Weak Entity".  

The relationship between Inspection and Employee is somewhat different.  It is a one-to-many relationship, but it is non-identifying, because the primary key of Employee is not part of the primary key of Inspection.  For such a relationship, we must click on the non-identifying relationship icon (), which uses a dashed line instead of a solid line.

As before, we click on the icon, then the parent entity, then the child entity.   Click on the non-identifying relationship icon, then the Employee entity, then the Inspection entity.   Double-click on the relationship to bring up the relationship definition window and fill it in as shown in Figure 13.

Figure 13: Non-identifying relationship definition.

As before, we provide more meaningful verb phrases for the relationship.  As before, since each employee may be associated with zero, one or more inspections, we choose the corresponding cardinality.  Unlike before, when we chose the identifying relationship type, we have now chosen the non-identifying relationship type.  We have the option of indicating whether nulls are permitted or not.   This decision regards the participation of the parent entity in a relationship with the child entity.  In other words, does each child entity occurence (Inspection) have to be associated with a parent entity occurence (Employee)?   In this case, the answer yes.  Each inspection must be carried out by an employee, or it is not considered a proper inspection.  The participation of Employee in the relationship is mandatory.  

Click OK to complete the relationship definition, as shown in Figure 14.

Figure 14: Non-identifying relationship

Employee and Office also participation in a relationship with each other.  Each Employee is assigned to one and only one Office, and each Office has one or more Employees. In this one-to-many relationship, Office is the parent and Employee is the child.  Since the primary key of the child (Employee) does not include the primary key of the parent (Office), this is a non-identifying relationship.  Click on the non-identifying relationship icon, Office, and Employee to create this relationship.  Double-click on the relationship and fill in the relationship definition window as shown in Figure 15.  

Figure 15: A non-identifying relationship with mandatory participation

In Figure 15, see an example of a relationship in which participation is mandatory for both entities.  The Cardinality indicates that each Office is associated with One or More (but not Zero) Employees.  The Relationship Type indicates that this is a non-identifying relationship, and that Nulls are not permitted.  In other words, each child (Employee) must be associated with a single parent (Office).

Complete the relationships by creating a relationship between Office and Property in which each Office manages zero, one, or more properties, and each property is managed by one and only one Office.  Your diagram should now appear as shown in Figure 16.

Figure 16: Relationships

Subtypes and Supertypes

There are two kinds of employees we wish to distinguish between:  Managers and Associates.  Each of these two kinds of employees has all of the properties of Employee, but have in addition a small number of specific attributes.  Only managers have a salary and a car allowance.  Only associates has an hourly rate.  We can specify that Manager and Associate are subtypes of Employee.  To do this, we will use the subtype icon ().   To indicate that Manager is a subtype of Employee, click first on the subtype icon, then on the supertype (Employee), then on the subtype (Manager).  The result appears in Figure 17.

Figure 17: Subtype relationship

To include Associate as a subtype, click again on the subtype icon.  Now, instead of clicking on the supertype again, click on the subtype icon in the diagram between Employee and Manager.  Then, click on Associate.  The result appears in Figure 18.

Figure 18: The Final ER Diagram

Finally, save your work.   You may now either exit ERwin, or proceed to the next section, entitled Creating a Relational Schema.

Section 2: Creation of a Database Schema

In this section you will learn how to create a database schema based on the E-R diagram you created in Section 1.  As you proceed through this section, you may either use the E-R diagram you created in Section 1, or you may download my section 1 E-R diagram.
The section has two main parts.  First, we ensure that all fundamental (strong) entities have a primary key.  Second, we define the domain for each attribute.  Third, we let ERwin do the work of converting the E-R diagram into a relational schema.

Assigning primary keys

Fundamental entities are those that do not depend on any other entity for their primary key.  In particular, fundamental entities are not:

• subtypes
• associative (composite) entities
• weak (attributive) entities.  
• entities at the 'many' end of an identifying 1-to-many relationship.  Actually, associative and weak entities are examples of this kind of entity.

In our current example, our E-R diagram appears as in figure 2.1

Figure 2.1 E-R diagram
Notice that MANAGER and ASSOCIATE do not have primary keys (they are subtypes).   The primary key of INSPECTION includes Insp_Date, but will also include Prop_ID, because the relationship INSPECTION undertaken at PROPERTY is an identifying relationship.  Our fundamental entities are:  EMPLOYEE, OFFICE, PROPERTY.  Each of these three has a fully defined primary key.

Defining Domains

The domain of an attribute is the set of values that attribute is permitted to have.  For example, the domain of an attribute Day_of_Week would consist of seven values:   {Monday, Tuesday, ..., Sunday}.  To define a domain we need to define:

1. a datatype (mandatory).   Data types can be used to specify character string data, numbers (several flavors), date and time data, etc.  Every attribute must at least have a data type.   For example, Assoc_Hourly_Rate must be a decimal number with two decimal places.  Prop_Rooms must be an integer.   Emp_Fname must be a variable-length character string of up no more than 50 characters.
2. constraints (optional).  For some attributes, the set of permitted values may be narrower than the set defined by the data type alone.  The attribute Day_of_Week is one example.  In our E-R diagram, several attributes have more narrowly defined domains.  For example, the value of Prop_Type must be in {Home, Apartment, Condo, Commercial}.  The value of Prop_MonthlyRent must be greater than $0.  

One of the attractive features of domains, aside from the enhanced data integrity they provide, is that domains can be shared by more than one attribute.   For example, the domains Emp_State and Prop_State are identical:  the list of 50 possible state codes.  In an E-R diagram, we can define the domain once, and apply it to multiple attributes.  The benefit of this is consistence across attributes and ease of maintenance.   If a new state were added to the Union, for example, we could modify Emp_State and Prop_State to accept the new state code ('PR' for Puerto Rico?) simply by making a single change to the domain definition itself.
We will define all the domains we'll need in our E-R diagam first, then apply them to our attributes.

Creating domains

From the Model Menu, choose Domain.  You will see a window that looks like Figure 2.2

Figure 2.2 Domain Dictionary
We'll first create a new domain that can be applied to numeric identifiers we'll use, such as Emp_Num, Prop_ID, and Office_Num.   Click on New... to create a new domain.  Type in Identifier as the name of the domain in the Logical Name column as shown in Figure 2.3.

Figure 2.3 New Domain
Click on the Logical Data Type tab, and choose the NUMBER datatype for this domain.  

Figure 2.4 Assigning a Data Type to a New Domain
We'll now create another domain, State, to be used for Emp_State, Prop_State, and Office_State.   Click on New... again.  As shown in Figure 2.5, name this new domainState

Figure 2.5 The State Domain
Click OK.  Choose the CHAR() datatype from the Data Type tab, and type in the number 2 inside the parentheses, as shown in Figure 2.6.   This indicates that the data type is a fixed two-character string.

Figure 2.6 State Datatype
We now need to specify the state codes that constitute valid values for this domain.  Click on the Constraint tab You will see a window in which you can create a new constraint. Click on New ... to create a new constraint, as shown in Figure 2.7.

Figure 2.7 Validation Rules
We will create a new validation rule for the State domain.   Name the validation rule State Codes as shown in Figure 2.8.

Figure 2.8 State Codes
Choose valid values as the type of the validation rule. Begin typing in valid state codes in the Valid Value column, as shown in Figure 2.9.

Figure 2.9 Entering Valid Domain Values
You will notice that ERwin builds a SQL IN clause listing the values you have specified.  When you have completed, click OK.
Use a similar set of steps to create the domains with the data types and validation rules indicated in Table 2.1.  Be sure that the validation rule for the domain is either empty, or is the validation rule you want.  Sometimes ERwin will allow a validation rule created for one domain to be applied to the definition of a second domain without the user's involvement.

Domain name	Datatype	Validation rule
Name	VARCHAR(50)
Street	VARCHAR(100)
City	VARCHAR(50)
State	VARCHAR(2)
Zipcode	VARCHAR(10)
Phone	VARCHAR(15)
Currency	NUMBER(9,2)	Create a new validation rule called Currency with a Minimum value = 0
SSN	VARCHAR(11)
Comment	VARCHAR(255)
Property Type	VARCHAR(25)	Create a new validation rule called Property Type with a list of valid values consisting of:  {Home, Apartment, Condo, Commercial}
Date	DATE
Non negative Integer	NUMBER	Create a new validate rule called non negative integer with a Minimum value = 0
Table 2.1 Domain creation

When you are finished, the Domain Dictionary should show the domains you created, as shown in Figure 2.10

Figure 2.10 Domain List
Click OK when you have completed.

Applying Domains to Attributes

The final step of working with domains is to associate a domain with each attribute. &nb` sp;To do this, right-click on one of the entities in your E-R diagram, such as EMPLOYEE and choose Attribute Properties.  You will see a list of attributes and you can assign the domains by changing the parent domain to the new domain. For example, Figure 2.11 shows that the Emp_Num attribute has been associated with the Identifier domain.

Figure 2.11 Associated a Domain with an Attribute
Associate each of the attributes in your E-R diagram with a domain, as instructed in Table 2.2.  Notice that the same domain may be used for more than one attribute.

Attribute	Domain
Emp_Num	Identifier
Emp_Fname	Name
Emp_Lname	Name
Emp_Street	Street
Emp_City	City
Emp_State	State
Emp_Zipcode	Zipcode
Emp_SSN	SSN
Emp_Phone	Phone
Emp_Fax	Phone
Office_Num	Identifier
Office_Street	Street
Office_Zipcode	Zipcode
Office_Name	Name
Office_Phone	Phone
Office_Fax	Fax
Insp_Date	Date
Insp_Comments	Comment
Prop_ID	Identifier
Prop_Street	Street
Prop_City	City
Prop_State	State
Prop_Zipcode	Zipcode
Prop_Type	Property Type
Prop_Baths	Non negative integer
Prop_Rooms	Non negative integer
Prop_MonthlyRent	Currency
Man_Salary	Currency
Man_Car_Allowance	Currency
Assoc_Hourly_Rate	Currency
Table 2.2 Attribute - Domain Association

Converting an E-R Diagram into a Relational Schema

The first stop in converting an E-R Diagram into a relational schema is to transform each entity and each relationship into its counterpart in a relational schema consisting of relational tables.  We have covered in class the rules for such conversions.  ERwin is easily able to manage this transformation based on those rules.
Double click on the white Canvas (Display Window) and click on the Entity Tab. Make sure that the following three options are checked (and choose them if they are not):

1. Primary Key Designator (should already be checked)
2. Foreign Key Designator (FK)
3. Show Migrated Attributes

When these three are checked, the E-R Diagram should appear as in Figure 2.12

Figure 2.12 E-R Diagram Showing Migrated Attributes and Foreign Keys
Notice that ERwin has automatically done the following:

1. Foreign keys are used to reflect the one-to-many relationships.   Because ERwin automatically provides foreign keys when such relationships exist, any foreign keys created by the data modeler would not be redundant and would have to be deleted.
2. Foreign keys that result from identifying relationships automatically become part of the primary key (see INSPECTION).
3. Subtypes inherit the primary key of the supertype, and those primary keys also serve in a foreign key capacity.

Finally, to transform the logical E-R diagram into a physical Relational schema, simply choose Physical instead of Logical in the combo box ()at the top of the window shown in Figure 2.12.   Double click again on the white background and from the Table tab check Display Column Data Type. The result is shown in Figure 2.13.

Figure 2.13 The Physical Relational Schema Model
Notice that the datatypes, as defined in the domain definitions, are all clearly represented.
In our final section, Section 3, we will examine how we can use ERwin to create a database in Microsoft Access (or almost any other DBMS) directly, based on our design.

Section 3: Creation of the Database

In this section, we will see how we can generate the SQL statements based on the relational schema developed in Section 2.  You may either use the schema you created, or download my Section 2 relational schema.

Checking the Relational Schema

Before you go further, take a look at your relational schema to make sure that all of the data types and column names are as they should be. Figure 3.1 shows the Relational Schema from section 2.

Figure 3.1 The Physical Relational Schema Model
The datatypes in this figure all appear to be appropriate.  

Choose Forward engineer/Schema from the Actions menu.  

At this point, to keep things simple, indicate that ERwin should generate only the tables, views, and the primary keys. The simpler the schema, the more easily it will be created within Oracle.

1. Database: Uncheck everything
2. Schema: Uncheck everything
3. Storage: Uncheck everything
4. Table: Check Create Table, Check Drop Table
5. Materialized View Log: Uncheck everything
6. Column: Keep unchanged
7. Materialized View: Uncheck everything
8. View: Uncheck everything
9. Index: Uncheck everything
10. Referential Integrity: Check Primary Key (Create), Check Foreign Key (Create), Uncheck unique
11. Trigger: Uncheck everything
12. Other Options: Keep unchanged

Figure 3.13 Oracle Schema Generation Options
If you click on the Preview... button, you can see the SQL that ERwin will generate to create your tables in Oracle.