Database-relational algebra and relational operations

Database-relational algebra and relational operations

Overview
Traditional set operations (sum, difference, intersection, Cartesian Product)
Dedicated relational operations

Union)

R and S have the same object n (that is, there are n attributes in both relations). The corresponding attributes are obtained from the same domain R ∪ S and are still n object relations, r s = {t | t? R branch t? S}

Difference)

R and S have the same object n. The corresponding properties are obtained from the same domain R-S and still have the n object relationship, r-S = {t | t? R branch t? S}

Intersection)

R and S have the same object n. The corresponding attributes are obtained from the same domain R ∩ S and still have the n object relationship, consisting of tuples that belong to both R and s r s = {t | t? R branch t? S} R ∩ S = R-(R-S)

Cartesian Product)

R: n-grams relation, k1 tuples S: m-grams relation, k2 tuples R × S column :( n + m) the first n columns of the Set tuples of the column tuples are a tuples of the relationship r, and the latter m column is a row of the tuples of the relationship s: k1 × k2 tuples R × S = {tr ts | tr? R transaction ts? S}

Dedicated relational operations

First introduce several marks

(1) R, t? R, t [Ai] sets the relational mode to R (A1, A2 ,..., An) is one of its relationships set to R t? R indicates that t is a tuples of R. t [Ai] indicates a component of attribute Ai In the tuples.

(2) A, t [A], if A = {Ai1, Ai2 ,..., Aik}, where Ai1, Ai2 ,..., Aik is A1, A2 ,..., A is called An attribute column or An attribute group. T [A] = (t [Ai1], t [Ai2],…, T [Aik]) indicates the set of components of the tuples t on Attribute column. A Indicates {A1, A2 ,..., Remove {Ai1, Ai2 ,..., The remaining attribute groups after Aik.

(3) tr ts R is the n object relation, and S is the m object relation. Tr? R, ts? S, tr ts is called the connection of tuples. Tr ts is a tuples of n + m columns. The first n components are one of n tuples in R, and the last m components are one of m tuples in S.

(4) The image set Zx is given a relational R (X, Z), and X and Z are attribute groups. When t [X] = x, x's image Set in R is: Zx = {t [Z] | t? R, t [X] = x} indicates the set of components of the tuples with the value of X in the Property Group x in R.

Connection

1) join, also known as θ join 2) Meaning of Join Operation select the tuples that meet certain conditions between attributes from the Cartesian points of two relations r s = {| tr? R transaction ts? S branch tr [A] θ ts [B]} A and B: respectively, they are equivalent and comparable property groups θ: the comparison operator concatenation operation selects (R Relation) values and (S relation) on the attribute group from the generalized Cartesian Product R × S of R and S) tuples with values in the B Attribute Group meeting the comparison relation θ

3) equijoin) what is A join operation with the equijoin θ "=" called an equijoin equivalent connection? The meaning of the equijoin equivalent connection selects the tuples whose values of A and B are equal from the generalized Descartes OF THE RELATIONSHIP R and S, that is, the equivalent connection is: r s = {| tr? R transaction ts? S transaction tr [A] = ts [B]}

Natural join is a special equivalent join. The components for comparison must be the same attribute group (same name and same domain: must have the same attribute name, and from the same domain set) Remove duplicate attribute columns in the result. The meaning of the natural connection R and S have the same attribute group B R S = {| tr? R transaction ts? S branch tr [B] = ts [B]} generally, the connection operation is performed from the row perspective. The duplicate columns need to be removed during the natural connection. Therefore, operations are performed from both the row and column perspectives.

When the external connection performs a natural connection, if the discarded tuples are also saved in the result link, and other attributes are left blank (Null ), this connection is called an outer join ). When a left outer join is used for a natural connection, if only the tuples to be discarded in the LEFT relation R are retained, it is called the left outer join or left join. When the right outer join is used for a natural connection, if only the tuples to be discarded in the RIGHT link S are retained, they are called right outer join or right join ).

Division (Division)

Given the relationship R (X, Y) and S (Y, Z), where X, Y, Z is the attribute group. Y in R and Y in S can have different attribute names, but must come from the same domain set. The division operation between R and S generates a new relationship P (X). P is the projection of tuples in R that meet the following conditions on the X attribute column: the image set Yx of X, which is the value of x on X, contains the set of S projected on Y. It is recorded as: R? S = {tr [X] | tr? R? π Y (S )? Yx} Yx: image set of x in R, x = tr [X]

In the relationship R, A can take four values: {a1, a2, a3, a4} a1. The image set is {(b1, c2), (b2, c3), (b2, c1)} a2: {(b3, c7), (b2, c3)} a3: {(b4, c6)} a4: {(b6, c6)} S projection on (B, C) is {(b1, c2), (b2, c1), (b2, c3 )} only the image set of a1 contains the projection of S on the (B, C) Attribute Group, so R ÷ S = {a1}