Lecture No. 4. Relational algebra. Unary operations

Relational algebra, as you might guess, is a special type of algebra in which all operations are performed on relational data models, i.e., on relationships.

In tabular terms, a relationship includes rows, columns, and a row - the heading of the columns. Therefore, natural unary operations are operations of selecting certain rows or columns, as well as changing column headers - renaming attributes.

1. Unary selection operation

The first unary operation we will look at is fetch operation - the operation of selecting rows from a table representing a relation, according to some principle, i.e., selecting rows-tuples that satisfy a certain condition or conditions.

Fetch Operator denoted by σ , sampling condition - P , i.e., the operator σ is always taken with a certain condition on the tuples P, and the condition P itself is written depending on the scheme of the relation S. Taking into account all this, the fetch operation over the scheme of the relation S in relation to the relation r will look like this:

σ r(S) ≡ σ r = {t(S) |t ∈ r & P t} = {t(S) |t ∈ r & IfNull(P t, False};

The result of this operation will be a new relation with the same relation schema S, consisting of those tuples t(S) of the original relation-operand that satisfy the selection condition P t. It is clear that in order to apply some kind of condition to a tuple, it is necessary to substitute the values ​​of the tuple attributes instead of the attribute names.

To better understand how this operation works, let's look at an example. Let the following relation scheme be given:

S: Session (Gradebook No., Surname, Subject, Grade).

Let's take the selection condition as follows:

P = (Subject = ‘Computer Science’ and Assessment >

3).

We need to extract from the initial relation-operand those tuples that contain information about students who passed the subject "Computer Science" by at least three points.

Let also be given the following tuple from this relation:

t₀(S) ∈ r(S): {(Gradebook #: 100), (Surname: 'Ivanov'), (Subject: 'Databases'), (Score: 5)};

Applying our selection condition to the tuple t₀, we get:

P t₀ = ('Databases' = 'Informatics' and 5 >

3);

On this particular tuple, the selection condition is not met.

In general, the result of this particular sample

σ<Subject = 'Computer Science' and Grade > 3 > Session

there will be a "Session" table, in which rows are left that satisfy the selection condition.

2. Unary projection operation

Another standard unary operation that we will study is the projection operation.

Projection operation is the operation of selecting columns from a table representing a relation, according to some attribute. Namely, the machine chooses those attributes (that is, literally those columns) of the original operand relation that were specified in the projection.

projection operator denoted by [S'] or π . Here S' is a subschema of the original schema of relation S, i.e. some of its columns. What does this mean? This means that S' has fewer attributes than S, because only those attributes remained in S' for which the projection condition was satisfied. And in the table representing the relation r(S' ), there are as many rows as there are in the table r(S), and there are fewer columns, since only those corresponding to the remaining attributes remain. Thus, the projection operator π< S'> applied to the relation r(S) results in a new relation with a different relation scheme r(S' ), consisting of projections t(S) [S' ] of tuples of the original relation. How are these tuple projections defined? Projection of any tuple t(S) of the original relation r(S) to the subcircuit S' is determined by the following formula:

t(S) [S'] = {t(a)|a ∈ def(t) ∩ S'}, S' ⊆S.

It is important to note that duplicate tuples are excluded from the result, i.e. there will be no duplicate rows in the table representing the new one.

With all of the above in mind, a projection operation in terms of database management systems would look like this:

π r(S) ≡ π r ≡ r(S) [S'] ≡ r [S' ] = {t(S) [S'] | t ∈ r};

Let's look at an example illustrating how the fetch operation works.

Let the relation "Session" and the scheme of this relation be given:

S: Session (classbook number, Surname, Subject, Grade);

We will be interested in only two attributes from this scheme, namely the student's "Gradebook #" and "Last Name", so the S' subschema will look like this:

S': (Record book number, Surname).

We need to project the initial relation r(S) onto the subcircuit S'.

Next, let us be given a tuple t₀(S) from the original relation:

t₀(S) ∈ r(S): {(Gradebook #: 100), (Surname: 'Ivanov'), (Subject: 'Databases'), (Score: 5)};

Hence, the projection of this tuple onto the given subcircuit S' will look like this:

t₀(S) S': {(Account book number: 100), (Surname: 'Ivanov')};

If we talk about the projection operation in terms of tables, then the projection Session [gradebook number, Last name] of the original relation is the Session table, from which all columns are deleted, except for two: gradebook number and Last name. In addition, all duplicate lines have also been removed.

3. Unary renaming operation

And the last unary operation we'll look at is attribute renaming operation. If we talk about the relationship as a table, then the renaming operation is needed in order to change the names of all or some of the columns.

rename operator looks like this: ρ<φ>, here φ - rename function.

This function establishes a one-to-one correspondence between schema attribute names S and Ŝ, where respectively S is the schema of the original relation and Ŝ is the schema of the relation with renamed attributes. Thus, the operator ρ<φ> applied to the relation r(S) gives a new relation with the schema Ŝ, consisting of tuples of the original relation with only renamed attributes.

Let's write the operation of renaming attributes in terms of database management systems:

ρ<φ> r(S) ≡ ρ<φ>r = {ρ<φ> t(S)| t ∈ r};

Here is an example of using this operation:

Let's consider the relation Session already familiar to us, with the scheme:

S: Session (classbook number, Surname, Subject, Grade);

Let's introduce a new relationship schema Ŝ, with different attribute names that we would like to see instead of the existing ones:

Ŝ : (No. ZK, Surname, Subject, Score);

For example, a database customer wanted to see other names in your out-of-the-box relation. To implement this order, you need to design the following rename function:

φ : (number of record book, Surname, Subject, Grade) → (No. ZK, Surname, Subject, Score);

In fact, only two attributes need to be renamed, so it's legal to write the following rename function instead of the current one:

φ : (number of record book, Grade) → (No. ZK, Score);

Further, let the already familiar tuple belonging to the Session relation be also given:

t₀(S) ∈ r(S): {(Gradebook #: 100), (Surname: 'Ivanov'), (Subject: 'Databases'), (Score: 5)};

Apply the rename operator to this tuple:

ρ<φ>t₀(S): {(ZK #: 100), (Surname: 'Ivanov'), (Subject: 'Databases'), (Score: 5)};

So, this is one of the tuples of our relation, whose attributes have been renamed.

In tabular terms, the ratio

ρ < Gradebook number, Grade → "No. ZK, Score > Session -

this is a new table obtained from the "Session" relationship table by renaming the specified attributes.

4. Properties of unary operations

Unary operations, like any other, have certain properties. Let's consider the most important of them.

The first property of the unary selection, projection, and renaming operations is the property that characterizes the ratio of the cardinalities of the relations. (Recall that the cardinality is the number of tuples in one or another relation.) It is clear that here we are considering, respectively, the initial relation and the relation obtained as a result of applying one or another operation.

Note that all properties of unary operations follow directly from their definitions, so they can be easily explained and even, if desired, deduced independently.

So:

1) power ratio:

a) for the selection operation: | σ r |≤ |r|;

b) for the projection operation: | r[S'] | ≤ |r|;

c) for the renaming operation: | ρ<φ>r | = |r|;

In total, we see that for two operators, namely for the selection operator and the projection operator, the power of the original relations - operands is greater than the power of the relations obtained from the original ones by applying the corresponding operations. This is because the selection accompanying these two select and project operations excludes some rows or columns that do not satisfy the selection conditions. In the case when all rows or columns satisfy the conditions, there is no decrease in power (i.e., the number of tuples), so the inequality in the formulas is not strict.

In the case of the renaming operation, the power of the relation does not change, due to the fact that when changing names, no tuples are excluded from the relation;

2) idempotent property:

a) for the sampling operation: σ σ r = σ ;

b) for the projection operation: r [S'] [S'] = r [S'];

c) for the renaming operation, in the general case, the property of idempotency is not applicable.

This property means that applying the same operator twice in succession to any relation is equivalent to applying it once.

For the operation of renaming relation attributes, generally speaking, this property can be applied, but with special reservations and conditions.

The property of idempotency is very often used to simplify the form of an expression and bring it to a more economical, actual form.

And the last property we will consider is the property of monotonicity. It is interesting to note that under any conditions all three operators are monotonic;

3) monotonicity property:

a) for a fetch operation: r₁ ⊆ r₂ ⇒σ r₁ ⇒ σ r₂;

b) for the projection operation: r₁ ⊆ r₂ ⇒ r₁[S'] ⊆ r₂ [S'];

c) for the rename operation: r₁ ⊆ r₂ ⇒ ρ<φ>r₁ ⊆ ρ<φ>r₂;

The concept of monotonicity in relational algebra is similar to the same concept from ordinary, general algebra. Let us clarify: if initially the relations r₁ and r₂ were related to each other in such a way that r ⊆ r₂, then even after applying any of the three selection, projection, or renaming operators, this relation will be preserved.

<< Back: Relational Data Objects (Requirements for the tabular form of representing relationships. Domains and attributes. Relationship schemas. Named values ​​of tuples. Tuples. Types of tuples. Relations. Types of relations)

>> Forward: Relational algebra. Binary operations (Operations of union, intersection, difference. Operations of Cartesian product and natural join. Properties of binary operations. Variants of join operations. Derived operations. Relational algebra expressions)

We recommend interesting articles Section Lecture notes, cheat sheets:

▪ History of religion. Lecture notes

▪ Theory of learning. Lecture notes

▪ Civil law. Part II. Crib

See other articles Section Lecture notes, cheat sheets.

Read and write useful comments on this article.

<< Back

Latest news of science and technology, new electronics:

The existence of an entropy rule for quantum entanglement has been proven 09.05.2024

Quantum mechanics continues to amaze us with its mysterious phenomena and unexpected discoveries. Recently, Bartosz Regula from the RIKEN Center for Quantum Computing and Ludovico Lamy from the University of Amsterdam presented a new discovery that concerns quantum entanglement and its relation to entropy. Quantum entanglement plays an important role in modern quantum information science and technology. However, the complexity of its structure makes understanding and managing it challenging. Regulus and Lamy's discovery shows that quantum entanglement follows an entropy rule similar to that for classical systems. This discovery opens new perspectives in the field of quantum information science and technology, deepening our understanding of quantum entanglement and its connection to thermodynamics. The results of the study indicate the possibility of reversibility of entanglement transformations, which could greatly simplify their use in various quantum technologies. Opening a new rule ... >>

Mini air conditioner Sony Reon Pocket 5 09.05.2024

Summer is a time for relaxation and travel, but often the heat can turn this time into an unbearable torment. Meet a new product from Sony - the Reon Pocket 5 mini-air conditioner, which promises to make summer more comfortable for its users. Sony has introduced a unique device - the Reon Pocket 5 mini-conditioner, which provides body cooling on hot days. With it, users can enjoy coolness anytime, anywhere by simply wearing it around their neck. This mini air conditioner is equipped with automatic adjustment of operating modes, as well as temperature and humidity sensors. Thanks to innovative technologies, Reon Pocket 5 adjusts its operation depending on the user's activity and environmental conditions. Users can easily adjust the temperature using a dedicated mobile app connected via Bluetooth. Additionally, specially designed T-shirts and shorts are available for convenience, to which a mini air conditioner can be attached. The device can oh ... >>

Energy from space for Starship 08.05.2024

Producing solar energy in space is becoming more feasible with the advent of new technologies and the development of space programs. The head of the startup Virtus Solis shared his vision of using SpaceX's Starship to create orbital power plants capable of powering the Earth. Startup Virtus Solis has unveiled an ambitious project to create orbital power plants using SpaceX's Starship. This idea could significantly change the field of solar energy production, making it more accessible and cheaper. The core of the startup's plan is to reduce the cost of launching satellites into space using Starship. This technological breakthrough is expected to make solar energy production in space more competitive with traditional energy sources. Virtual Solis plans to build large photovoltaic panels in orbit, using Starship to deliver the necessary equipment. However, one of the key challenges ... >>

Random news from the Archive Type 2 diabetes raises risk of early dementia 27.07.2021 Scientists from the University of Paris found that type 2 diabetes significantly increases the risk of developing early dementia. Patients with this diagnosis between the ages of 60 and 64 require close monitoring by doctors. Experts studied data from more than 10 people collected as part of the Whitehall II study. This long-term experiment involved people aged 35 to 55 years. All of them were tested every 4 years and recorded in an electronic medical record. Thus, scientists received the most complete data over a long period, in particular, about the health status of people with type 2 diabetes. It turned out that the risk of developing dementia increases by 25% if the disease was diagnosed at 60-64 years old. If high blood sugar began to bother respondents aged 65 to 70, in this case they were 24% more likely than others to suffer from senile dementia. In cases where the disease developed in people older than 70 years, this did not have any effect on dementia. Researchers are sure that the whole thing is in the work of the cardiovascular system. Typically, people with type 2 diabetes have low blood pressure. Also, the body suffers from insulin-dependent therapy and is depleted. As a result, in old age, the risk of stroke increases, and already against its background, Alzheimer's disease or dementia develops.

Other interesting news:

▪ Carbon dioxide detected near planetary body

▪ XNUMXD materials change shape and store energy

▪ The smell of disease passes from the sick to the healthy

▪ New electronics will withstand radiation and heat

▪ Tamron 16-300mm F/3.5-6.3 Di II VC PZD Macro Lens (Model B016)

News feed of science and technology, new electronics

Interesting materials of the Free Technical Library:

▪ site section Lighting. Article selection

▪ Sharashkin's office article. Popular expression

▪ article Is there life on Mars? Detailed answer

▪ article Conducting classes in sports and outdoor games (football, volleyball, basketball, tennis, etc.). Standard instruction on labor protection

▪ article Watering machine for the garden. Encyclopedia of radio electronics and electrical engineering

▪ article Faithful sevens. Focus Secret

Leave your comment on this article:

All languages ​​of this page

Arabic	Hebrew	Polish
Bengali	Hindi	Portuguese
Chinese	Italian	Spanish
English	Japanese	Turkish
French	Korean	Ukrainian
German	Malay	Vietnamese

Home page | Library | Articles | Website map | Site Reviews

www.diagram.com.ua
2000-2024