Establishing relationships between entities - Databases: designing

Establishing relationships between entities

To establish relationships between entities and create foreign keys, ERWin provides the ability to separate connection types into several options:

• An identifying relationship is a relationship that determines the unique correspondence of an instance of one entity to a single instance of a bound entity and, as a rule, describes a 1: 1 relationship, but when implementing a concatenated primary key, it can realize a one-to-many relationship (1: JV);

• non-identifying relationship - a link that implements a one-to-many type of communication (1 : N), representing a foreign key in a related entity as a simple attribute, to which certain additional restrictions can be imposed compared with the usual information attributes;

• multiple connection - a link that implements the many-to-many-type (L: M) type of communication, is presented only at the level of the logical model, illustrating the connection between entities, but not creating external keys in the related entities;

• categorization is a connection that provides the connection of the entity-community with the entity-category connection type one-to-one (1: 1) and simultaneously creates an external primary key in entity-categories associated with the primary key of the entity-community.

When creating a connection between two entities, it's enough to select the link type icon, then sequentially specify the parent and related entities. As a result, a link will be created and, if it is provided by the communication type, the necessary foreign keys will be created in the associated entity. In the case when the developer did not determine the primary key of the parent entity and established a connection, for example, not identifying, then the foreign key will not be created, but as soon as the primary key in the parent entity is specified, it will immediately be reflected in the related entity with a foreign key in accordance with the existing connection in the model between these entities.

The ERWin tool, when establishing relationships between entities, defines two kinds of entities:

• Parent is the base entity whose primary key can migrate to the related entity;

• Child - is determined by the entity that, when establishing a connection, receives a foreign key formed from the migrating primary key of the parent entity.

Such a separation looks quite logical, since, based on the peculiarities of linking and the logic of the domain, the information described by the parent entity is aggregating with respect to the data described by the child entity. For example, considering the relationship between the entities Client and "Order", the particular client represented by the entity instance "Client" combines (aggregates) the set of orders that it created in the electronic mage -

zine. As a result, the entity Order in relation to the entity Client can be treated as a child, and the entity "Client" - parent.

The links description contains, in comparison with entities and attributes, fewer properties that need to be described, but they are also, and sometimes more, important, since they allow you to describe and configure the rules of referential integrity, providing, when implemented in a database, correctness of data saving. One of the characteristics is the name of the link used in the database model and defines the main meaning of the established connection between entities (Figure 3.15).

Fig. 3.15. Basic communication description in ERWin

The

Column Name in the description of the connection, the developer is given the opportunity to indicate a name that reflects the meaning of the communication. This is an important component of the model, which makes it possible to unambiguously understand the essence and meaning of the established connection, which, in the end, should help to correct the model of the database correctly, correctly redistributing individual attributes between entities.

It is important to understand that the names of links should be, if possible, unique within the entire database model, and not only at the level of a single chart. The presence of the same names of links can lead to the impossibility of correctly identifying the corresponding relationship and ultimately to build an effective model. Other characteristics describing the relationship between entities in the dialog box are located below the list of model links and contain rules for cardinality (power), renaming of the generated foreign key (role), and providing referential integrity.

The three most important basic characteristics of the connection (Figure 3.16), which form the basic essence of the relationship, are described in the first tab "General" and represent the type of connection, the name of the connection, cardinality (power). These communication parameters must always be determined and properly described. In addition to the name of the connection, the remaining characteristics will be transferred with the appropriate transformation into the physical model, and then into the database.

The first characteristic of the connection determines its type (Figure 3.17): identifying or not identifying. In this case, choosing the appropriate type of connection, the developer has the opportunity (for non-identifying link) to specify the absence of the instance of the parent entity, thus allowing the foreign key to indicate the empty value of "NULL".

Fig. 3.16. The main characteristics of communication in the WinZip Win

The

Normally, if you set a non-identifying relationship, the parameter "Null Option" is set to Nulls Not Allowed (NULL is invalid). This is determined by the peculiarities of working with data, according to which the child data instance must be associated with the parent instance. But sometimes there are cases when it is not respected. As a rule, such a situation arises when the objects of the domain, united by this link, are equivalent and it is impossible to uniquely determine the priority of the occurrence of an instance of this or that entity. Then the value of Null Allowed (NULL is allowed), as shown in the example (see Figure 3.17).

Fig. 3.17. Determining the type of communication in ERWin

The

Since one-to-one and one-to-many relationships are related, and the differences are only in power and some more stringent requirements, switching between these types of connections can be performed in the dialog box of communication settings, type Not identifying in the Identifying type. In this case, the Null Option will be unavailable

for customization. This is explained by the fact that when you install an identifying link, the foreign key that is obtained from the child entity is also the primary key, and the primary key can not store an empty value in the database construction rules. Therefore, for the foreign key obtained, the Null Not Allowed (NULL is invalid).

Another characteristic that makes it possible to move from one-to-one communication to one-to-many communication and vice versa is cardinality. Establishing cardinality (power) of communication within the properties of "Cardinality" and Cardinality Value specifies the rules for filling instances of the child entity (Figure 3.18). There are four options for cardinality, defined by means of ERWin:

• Zero, One or More (for a child entity, any number of instances associated with one instance of the parent entity, including the option of missing instances, is possible;

• (P) One or More (one or many) - the number of instances of a child entity associated with a single instance of the parent entity can be any, but when an instance is created in the parent entity in the child entity, instances must already exist, the Null Option in the value of "Nulls Allowed", allowing the storage of the null value "NULL in an external key obtained when establishing a connection;

• (Z) Zero or One (zero or one) - defines a one-to-one relationship, allowing the existence of at most one instance of data in the child entity;

• Cardinality Value - specifies the exact number of associated instances in the child entity, which can be implemented only with the option "Null Option" in the value of "Nulls Allowed", the primary creation of instances in the child entity and their subsequent association with the instance in the parent entity.

Fig. 3.18. Determination of cardinality (power) of communication in ERWin

The

As a result of indicating the cardinality (power) of communication on the model, the diagram will display its alphanumeric designation. If the cardinality option is selected in the One or many option, the letter P & quot ;, in the case of cardinality Zero or one - letter "Z", if the exact numeric value is specified, the specified value, in other cases, no designations will be displayed on the model.

Another characteristic of the connection is described as the main one - the meaningful filling of the connection (Figure 3.19), denoted by the verbal form.

This description, as in all models of the database at any level of the presentation, shows the feature of the interaction of entity instances in accordance with the features of the domain. The description should be a phrase indicating the relation of the instance of the parent entity to the instance of the child entity or vice versa, representing the expression containing the action verb.

Fig. 3.19. Description of meaningful filling of communication in ERWin

The

When establishing one-to-one and one-to-many relationships, as discussed above, the primary key of the parent entity migrates to the child entity, creating the corresponding foreign key (see Figure 3.20). Often, especially when using the agreement of names of model elements, in identical entities identical primary keys can have the same names, which causes some difficulties in establishing links, expressing the need to reflect foreign keys with names that are already present in essence. Alternatively, when you need to use the mechanism for naming the key migration rule, there may be a need for a more precise formulation of the attribute describing the corresponding foreign key.

Fig. 3.20. Description of key migration rules in ERWin

The

The solution to these problems is implemented through the "Role Name" mechanism, where the developer specifies the attribute name for the foreign key, how it should be represented in the database model and, as a result of the transformation, in the database. The Role Name Info contains two columns:

• Migrated Attribute - shows the attribute of the parent entity, which is represented by a foreign key in the associated child entity (it is not subject to change);

• Role Name - indicates the new value of the foreign key attribute name, which should be used instead of the name of the migrating attribute.

Specifying the desired attribute name in the Role Name will result in renaming the foreign key attribute and then using the new attribute name in all database model elements, where necessary.

Defining referential integrity rules (Figure 3.21) is the physical database modeling step. This is due to the fact that individual rules for some DBMS may not be available. Nevertheless, ERWin at the stage of logical modeling provides an opportunity to specify the rules of referential integrity for the formed links. At this stage, the developer is offered a maximum set of rules:

• None - A rule that assumes any user action without affecting other database elements;

• No Action (non-action) - a rule that presupposes actions determined by the developer;

• Restrict (prohibit) a rule that prohibits the operation of the data if the test condition is satisfied;

• Cascade (cascade) - a rule that performs sequential actions on related data in accordance with the action performed on the data to which this rule is defined;

• Set Null (set to NULL) - a rule that sets the NULL foreign key value to related instances;

• Set Default - the rule that sets the default value for the foreign key of the associated instance.

The rules of referential integrity are aimed at ensuring the correctness of operations with data when they are modified. Thus, these rules should be executed if operations are performed in the database, but addition, modification and deletion of data. ERWin implements referential integrity constraint operations in the maximum version, considering the execution of corresponding operations not only for the main variants that affect the changes in the database, but also for operations that should not have a significant change in the database. As a result, the developer is invited to specify the rules of referential integrity when performing actions on the data when modifying the data of instances in both the parent and child entities. Subsequently, all these actions, if they are not provided in the DBMS, will be converted into the program modules of automatic execution (triggers) and associated with the actions performed on the data. In the case of the presence of referential integrity in the DBMS, they will be declared by the appropriate rules when describing the data tables.

Fig. 3.21. Definition of the rules of referential integrity in the EKSHIP

The

In the formation of a link, many to many developers are given the opportunity to specify a minimum set of communication characteristics that include the definition of a meaningful load. No other rules and specifications for this type of connection are established, because when switching to a physical model, such a connection should be normalized and represented by one-to-many relationships (Figure 3.22).

Fig. 3.22. An example of the categorization categorization implementation in ERWin

The

The categorization relationship is organized in the form of a set of one-to-one relationships, united by a specialized graphic element. For its implementation in EILUT, it is necessary: ​​

• when you first specify a link, select the link icon and sequentially select the entity-community and one of the category entities;

• the rest of the category entities using the same categorization connection icon are joined by sequentially selecting the graphic element and the next category entity.

As a result of these actions in the database model, the relationship representation is similar to the example above (see Figure 3.22).

The categorization relationship can be of two types, one of which must be determined when establishing this type of connection (Figure 3.23). To denote differences in the type of categorization relationship, the designation of the graphic element will be represented with two features or one feature (Table 3.1).

Fig. 3.23. Determining the type of categorization connection in ERWin

The

Table 3.1

Tins of categorization and notation in ERWin

Designation

Description

About

Complete - all entities-categories will contain instances from the entity-community, indicating the need for a complete description (for all entity-categories) of each instance from the entity-community

Incomplete (an incomplete categorization relationship) - an instance from an entity-community can be defined in one or more entity-categories, including the possibility of no instances in all entity-categories

The

The

For the categorization connection itself, no other characteristics are defined, and the developer is given only the ability to view the categorization relationship structure (Figure 3.24). This description makes it possible to see which entity-categories are defined by subtypes, and which entity-community is represented by a supertype.

The links from the graphical element of categorization to entities are one-to-one relationships, and for them, as a rule, fixed characteristics are defined. The developer is given the opportunity to specify only the meaningful filling of the links, the name of the foreign key attribute and the rules of referential integrity.

Fig. 3.24. Description of the categorization relationship in EICH

thematic pictures

Also We Can Offer!

Other services that we offer

If you don’t see the necessary subject, paper type, or topic in our list of available services and examples, don’t worry! We have a number of other academic disciplines to suit the needs of anyone who visits this website looking for help.

How to ...

We made your life easier with putting together a big number of articles and guidelines on how to plan and write different types of assignments (Essay, Research Paper, Dissertation etc)