Creating and Editing Entity Relationships

An entity relationship represents the association between two tables that exist within a database. For example, a table containing employee data is linked to one containing sales data in the sense that each sale is made by an employee. Each relationship is denoted by a foreign key, which is an attribute that refers to the primary key of the parent entity. In a data model, these relationships are visualized through links connecting related entities.

In this article, we will discuss how you can create and edit relationships between the entities present in a data model in Astera Data Warehouse Builder.

Video

Editing an Existing Relationship

Here, we have a couple of entities that have been reverse engineered from an existing database. The entity named Employee is the parent entity and the one named Sale is the child entity.

To edit a relationship, right-click on the relationship link and select Edit from the context menu.

The Edit Relationship configuration window will appear.

Using this window, you can modify the properties of the relationship that exists between these two entities.

Relationship Type

From the Relationship Type dropdown menu, you can choose a relationship type from two options: Identifying and Non-Identifying.

Non-identifying: A relationship where the primary key of the parent entity is included in the child entity but not as part of the primary key.

Identifying: A relationship where the primary key of the parent entity is included in the primary key of the child entity.

The relationship between Sale and Employee will remain non-identifying. You can also change the relationship type by right-clicking on the relationship link, hovering over the Relationship Type option, and selecting the appropriate choice.

Parent Alias, Child Alias, Constraint Name, and Verbs

Using the Parent Alias, Child Alias, Constraint Name, Parent to Child Verb and Child to Parent Verb text boxes, you can define certain attributes of the relationship.

Parent Alias: A user-defined alternative name for the parent entity.

Child Alias: A user-defined alternative name for the child entity.

Constraint Name: A user-defined name that represents the rules describing this relationship.

Parent to Child Verb: A user-defined phrase that describes the dynamics of the parent to child relationship.

Child to Parent Verb: A user-defined phrase that describes the dynamics of the child to parent relationship.

Mandatory

By checking or unchecking the Mandatory checkbox, you can define whether the relationship is mandatory or optional, respectively.

A mandatory relationship is one that requires the parent entity to participate in the relationship. On the other hand, an optional one is where participation is not compulsory. Once the mandatory or optional nature of a relationship has been defined, it is further indicated by the Allows Null column in the Layout Builder of the child entity. If the relationship is mandatory, the Allows Null checkbox for the foreign key that represents the relationship between the entities is automatically unchecked.

You can also define the mandatory or optional nature of a relationship by right-clicking on the relationship link and checking or unchecking the Mandatory option.

Parent Field and Foreign Key(s)

The table at the bottom of the Edit Relationship screen contains a non-editable column representing the Parent Field and a corresponding dropdown menu for the Foreign Key(s).

The Parent Field is the primary key of the parent entity and the Foreign Key(s) is a corresponding key that represents the relationship in the child entity.

Creating a New Relationship

Non-Identifying

This is how you can create a non-identifying relationship between two entities:

1. Click on the Link entities to create non-identifying relationships icon in the data model menu bar.

2. Drag a relationship link from the parent entity and drop it onto the child entity.

Identifying

This is how you can create an identifying relationship between two entities:

1. Click on the Link entities to create identifying relationships icon in the data model menu bar.

2. Drag a relationship link from the parent entity and drop it onto the child entity.

Self-Reference

A self-reference relationship denotes that a foreign key exists within the same entity as the parent field. This is how you can create one:

1. Click on the entity that fulfils the requirements of a self-reference relationship. This will enable the Add Self Reference icon in the menu bar.

1. Click on the Add Self Reference icon.

You’ll notice that a self-relationship link will have been created to and from the entity in question.

This concludes our discussion on creating and editing relationships. In the next article, you’ll learn how to verify a data model in Astera Data Warehouse Builder.