How to add identifying, non-identifying, self-recursive relationship in ER Studio Data Architect?

Now, we are going to make changes to the previously created data model. So create another file LDM_creating_entity_version2. Open the previous data model “LDM_creating_entity_version1” and click menu file/save as “LDM_creating_entity_version2”.

How to add entity and attributes in ER Studio Data Architect:

Add the following attributes to “Employee” Entity. Place the cursor in attribute section of Employee Entity, right click edit entity. Click attributes tab and add the following attributes.

  • “Employee Name” with data type as Varchar(50); NOT NULL;
  • “Incentive Indicator” with datatype as Char(10);  NOT NULL;
  • “Phone Number” with datatype as Char(12); NULL;

You can use UP or DOWN arrow keys to align the attributes.

Create another entity Address with Address Identifier as attribute, datatype as Integer and Address identifier as primary key.

Address ID should be a sequence number.

How to create a sequence number in ERStudio Data Architect:

When you add an attribute, you can see a section “Identity Property”.  Click the check box in Identity column.

Type 1 for seed and 1 for increment. Seed means the starting number of the sequence and increment means how it has to be incremented.

Create another entity Employee-Address with no attributes.  We will explain how to add attributes below.

How to add non-identifying relationship in ER studio data architect?

Department entity to Employee entity:

Many employees can work in one department. One-to-many relationship. Hence we can create this relationship by Non-Identifying Relationship.

Click menu Insert/Relationship/Non-Identifying mandatory:

Click the cursor on Department Entity and then on Employee Entity.

Now you can see the non-identifying relationship between department entity and employee identity.

A new attribute Department Number is added to employee entity.

How to add identifying relationship in ER studio data architect?

Employee entity and Address entity: One employee can stay in many addresses.

One address can contain many employees.

Many to many relationship: Identifying relationship

Create address entity with address id as the primary key.

Click menu Insert/Relationship/Identifying

Click the cursor on employee entity and employee address entity. Again click on address entity and then on employee-address entity.

Now you can see the identifying relationship in employee address entity.

Two new attributes employee number, and address identifier are added as primary keys to employee-address entity. Since two keys are there, it is called as composite primary keys.

How to add self-recursive relationship in ER studio data architect?

To connect relationship between an employee and a manager: There is no attribute which references the employee number in the employee entity.  So we will create a new attribute Manager Number by using self-recursive relationship. When we want to create an attribute in an entity that references the same primary key attribute, we have to create role name, because, two attributes cannot have the same name in the same table.

In this example, the parent entity and child entity are same (employee entity).

Click menu Insert/Relationship/Non-Identifying mandatory

Click the cursor on employee entity twice. You can see a new screen. Type role name as manager number.

You can see a new attribute manager number in employee entity.

If you want to know more about the relationship, click each relationship line and you can see different options.

02_identifying_and_non_identifying relationship

 

How To Create A Logical Data Model In ER Studio Data Architect?

The following example has been created from the Embarcadero’s product ER/Studio Data Architect, Version 10.0. You can find different products in the URL http://www.embarcadero.com/products. For more information, please visit http://www.embarcadero.com.

ER/Studio Data Architect is a powerful Data Modeling tool with several applications. You can create either relational data model or dimensional data model from Embarcadero’s ER/Studio Data Architect. Based on the usage, you can create conceptual, enterprise and sub models etc. After you finish your creation of logical relational data model, you can create physical relational data model.

How you will create a logical relational data model in ER/Studio Data Architect?

  • Open ER Studio Data Architect.
  • Click File New.
  • From the new screen that appears click “Draw a new data model” and select “Relational” from the drop down box. A new screen appears. By default, you will create a logical data model.
  • Either you can use menu options or icons present in the screen and we will follow menu approach.

How to create an entity (Table), add attribute (column), datatype, primary key in ER/Studio Data Architect?

  • Click Menu Insert/Entity. Drop that entity in working area by clicking your cursor in the working area.
  • Do the same thing to create another entity.  By default the first entity you created will be Entity1 and the second entity will be Entity2 respectively.
  • Change the entity name “Entity1” to Department.
  • Right click “Entity1”. Click edit entity. A new screen “Entity Editor” is opened.
  • Type the entity name as “Department”.
  • There are different tabs present in that “Entity Editor” screen.
  • Click tab “Attributes”. On the bottom of the screen, you can see add button and Click that add button.
  • Type Attribute name as “Department Number”.
  • Select the datatype as Integer.
  • Click the check box “Add to Primary Key”.
  • Click add and a new attribute “Department Number” is created.
  • Create another attribute with “Department Name”,and datatype as Varchar(50). Select datatype as Varchar and width as 50.
  • A new entity “Department” with a primary key “Department Number” and a standalone attribute “Department Name” is created.

Similarly, create another entity “Employee” with attribute names:

  • “Employee Number” with data type as Integer and constraint as primary key.
  • “Salary” with data type as Numeric (10, 2), NOT NULL constraint as a standalone attributes. Type width as 10 and scale as 2.
  • “Date of Joining” with datatype as Date, NOT NULL constraint as a standalone attribute:
  • Click menu file/save. Save it as “LDM_creating_entity_version1”

01_logical_data_model_diagram

Since we have not created domain names, defaults, role names, naming standards, we have not selected those options. We will explain it later.

We will explain in the next section how to create physical data model  of data model  LDM_creating_entity_version2. Based on that you can create physical data model of LDM_creating_entity_version1.

01_physical_data_model_diagram

1 2