Relational Algebra, Relational Operation Pooling - Databases: design

Relational Algebra

The relational algebra was first formulated by E. Codd in 1972, where the main operand for operations is defined by relations. They (relations) are the main components of all operations in relational algebra.

A relational algebra is usually understood as a set of operations that use relationships as the primary operands and the result to be returned.

It is important to understand that relational algebra is formulated on the basis of mathematical operations with sets, but since all data are formed into relations that are a particular case of the set, the relational algebra used in the construction of databases considers that, as sets, over which will be performed relational operations, will be considered the relationship. The representation of relational algebra for working with relationships includes eight operations, which are divided into two groups with four operations each:

• standard operations on relations - union, intersection, difference, Cartesian product;

• specialized operations on relations - reduction (selection), projection, connection, division.

Although the author of a relational algebra with relations defines these eight operations, they do not limit the possibilities of processing relations. In fact, many other operations on relationships can be defined. The main thing is that all important defined operations correspond to the frames of relational algebra and be used as operands and return result of the relationship.

To show the relationship of operations on relations with operations on sets and for better understanding of them, we will consider operations of relational algebra from two positions: operations on sets with graphical illustration and operations on relations.

Relational Operation Merge

Mathematics in operations on sets determines that the union of two sets leads to the appearance of a set containing all elements belonging to either one of the sets or to both sets (Figure 1.16).

Fig. 1.16. The operation of combining (and) over sets

The

The result of this operation will be a relation consisting of all tuples of one of the relations or both initial relations. However, it is worth remembering that operations with sets in mathematics and operations with relations in databases are close, but not identical operations. It is due to the fact that in relational algebra, implemented in modern databases, the unification operation can be performed only on the same type of relationship, since it is necessary to preserve the relational property of being closed.

The property of relational closure defines that any operation on a relation must form a new relation with all properties inherent in the relation. Therefore, considering the support of this property, the join operation, like any other operation of relational algebra, must form a relation. This is possible only if the original relationship is the same.

For example, in Fig. 1.17 shows that the two relations Employee and Job Title can not be combined, because they consist of a different set of attributes, i.e. are of different types. Such an operation on these relations will be impossible, but if there are two relations with the same set of attributes, i.e. of the same type, then it will be possible to perform the unification operation.

Fig. 1.17. Unification operation (and) over different types of relationships

The

Thus, the unification operation assumes that both initial relations should be of the same type, i.e. have an identical structure for the data types of each attribute and the same sequence of attributes, forming the result of the operation in the same relationship structure as the original relationship (Figure 1.18). In this case, the tuples of both relations become tuples of the resulting relation.

Fig. 1.18. The operation of merging (and) over the same type of relationship

The

The merge operation is often used in databases, when it is necessary to obtain information about the same object of the domain, but to different conditions, combining the information obtained into a single set of values. An example of such a need may be such an option (see Figure 1.19): to generate lists of people working at the university, and people studying in the first and second years with an indication of the type of person ("Employee", "Student"). Such a request requires the sampling of employees and a separate sample of students, since to perform such a request it is necessary to address various relationships that are not related to each other.

The

 Fig. 1.19. Initial relations for employee and student

The


Fig. 1.20. An example of the performed association with the representation of the resulting relation

As you can see, both relationships are not related to each other, but have identical attributes: Personnel number and Student ticket are attributes described by a character type, and they can be treated as identical, and the name is the same attribute in meaning and type (character) of the stored data. As a result of the unification operation, a relationship will be obtained containing two attributes: the human identifier (here the original attribute is renamed to ensure the merge operation, since it is exactly the same attribute names, not just the same data types, that allow it to be executed) and the name (Figure 1.20 ).

The

The

The resulting relationship will consist of all tuples of the Employee relationship, representing the attribute Personnel number as an attribute of the "human IDF", and from all the tuples of the "Student" relationship, representing the attribute "Student ticket" as an attribute of the human IDF.

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)