Shulou(Shulou.com)05/31 Report--

This article shares with you the content of an example analysis of relational algebra in PostgreSQL. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

I. basic concepts

Relational algebra is a procedural query language. It includes a set of operations that take one or two relationships as input and produce a new relationship as a result. Relational algebra is the mathematical basis of SQL query language.

The operation object "relation" of relational algebra is a two-dimensional table, which is composed of rows and columns.

Here are the relationships used in this article:

Unit information T_DWXX

DWMCDWBHDWDZX Co., Ltd. 1001 Guangzhou Liwan District Y Co., Ltd. 1002 Beijing Haidian District Z Co., Ltd. 1003 Wuxiang District, Nanning City, Guangxi

Personal Information T_GRXX

DWBHGRBHNL100190123100290233100290343 II. Basic operation

The basic operations of relational algebra are:

Name symbol pronunciation selection (select) σ sigma projection (project) π pi and (union) ∪

Difference (set-difference)-

Cartesian product (cartesian-product) ×

Rename (rename) ρ rou

Select σ

Definition: σ φ (R)

Select the Tuple in relation R that satisfies the given predicate φ, and eliminate those tuples that do not satisfy the predicate to form a new relation.

Such as:

σ DWBH='1001' (T_DWXX), select the unit information of the unit number DWBH=1001 in the unit information

σ NL > 40 (T_GRXX), select personal information with age NL > 40 in personal information.

Projection Pi

Definition: π a1m... dint an (R)

The projection relationship is used to filter out the desired attributes, and the projection relationship returns a relationship that contains only these attributes. Note that duplicate property values are filtered because the collection is returned.

Such as:

π DWMC (T_DWXX), which returns the unit name column in the unit information

And ∪

Definition: π A1 ∪.

Merge the contents of two relationships, or a relationship merges the results through different queries. The two relationships handled by the parallel operation must have the same attributes.

Such as:

π DWBH (T_DWXX) ∪ π DWBH (T_GRXX)

Poor-

Definition: π a 1m. A (R)-π a 1.

The relation R difference operation relation S, there is a relation composed of tuples in R but not in S. It is necessary to ensure that operations are carried out between compatible relationships.

Such as:

π DWBH (T_DWXX)-π DWBH (T_GRXX)

Cartesian product ×

Definition: r × S

The attributes of relations are different. In this case, intersection, union and difference operation can not be used, but we hope to connect two unrelated relations. By Cartesian product, we can combine each tuple in the first relation R with all the tuples in the second relation S to form a new relationship.

Such as:

T_DWXX × T_GRXX, get the Cartesian product of T_DWXX and T_GRXX, a total of 9 rows of data

Rename ρ

Definition: ρ newname (R)

If you want to change the name of the result, you can rename a relationship with a new name by ρ.

Such as:

ρ DW1001 σ DWBH='1001' (T_DWXX), renaming the unit information of DWBH='1001' to DW1001

III. Other operations

Other common operations include:

Name symbol pronunciation cross ∩

Assign ←

Natural connection ⋈

θ-connected ⋈ thet semi-connected ⋉ / ⋊

External connection ⟕ / ⟖ / ⟗

Aggregation operation G

Hand in ∩

Definition: r ∩ S

A new relationship of tuples that exists in both R and S relations. The tuple attributes in the R and S relationships are required to be the same.

Such as:

π DWBH (T_DWXX) ∩ π DWBH (T_GRXX)

Assign ←

Definition: r ← S

Use the name on the left of the arrow as a representation of the relationship on the right.

Such as:

DW ← π DWBH (T_DWXX)

Natural connection ⋈

Definition: r ⋈ S

Natural join splices tuples with the same common attribute values in two tables as a new tuple, and discards all the remaining parts that cannot be spliced to get a new relationship.

Such as:

T_DWXX ⋈ T_GRXX

Theta connection

Definition: r ⋈ θ S

The combination comes from the tuples of two relations R and S, and the combination condition is not a simple equality on the common attribute, but requires a general form of join operator, which is the θ connection. Unlike natural connections, the same attribute appears only once

Such as:

T_DWXX ⋈ DWBH > GRBH T_GRXX, the tuple combination of unit information and personal information whose unit number DWBH is greater than personal number GRBH

Semi-connected ⋉ / ⋊

Definition: r ⋉ S / R ⋊ S

Tuples in all RPX S that have a common attribute equivalent to Spicer R

Such as:

T_DWXX ⋉ T_GRXX, the same unit information as DWBH in personal information

External connection ⟕ / ⟖ / ⟗

Definition: r ⟕ S / R ⟖ S / R ⟗ S

The result of the left outer join contains all tuples in R. for each tuple, if the tuples with equal values on all attributes in S are connected normally, otherwise the tuples in R are preserved, and the other corresponding columns in S are set to NULL. The right outer connection / full external connection is similar.

Such as:

T_DWXX ⟕ T_GRXX, all unit information. In units where personal information does not exist, the value of personal information column is set to NULL.

Aggregation operation G

Definition: Exp1,Exp2...Gfunc1,func2,... (R)

Maximum / minimum / average / summary / count, etc. Where the expression Exp1... Optional, G=MAX/MIN, etc.

Such as:

DWMCGmax (DWBH) (T_DWXX), the name of the unit with the highest unit number

IV. Relational Algebra and SQL

The operation expression of the SQL statement can be expressed using relational algebra operations:

Example one

-- SQL

SELECT c1,c2,...

FROM r1,r2,...

WHERE P

-Relational algebra

π C1 and C2. (σ P (R1 × R2 ×.))

Example two

-- SQL

SELECT C1, c2, max (C1)

FROM r1,r2,...

WHERE P

GROUP BY c1,c2,...

-Relational algebra

C1, C2, GMAX (C1) (π, C1, () (σ P (R1 × R2 ×.))

Thank you for reading! This is the end of the article on "sample Analysis of Relational Algebra in PostgreSQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.