Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about the relationship between relational algebra and database, which may not be well understood by many people. in order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

Happen to see a sentence, relational algebra understand, there is no SQL that can not be written. Let me rekindle my interest in the "knowledge" that I have learned but do not know who to hand it to. Let's cut in with a question.

The above formula is based on what the result of the following graph is, what the left outer, right outer, and all outer connected graphs of R S look like.

At first glance, it doesn't seem to have anything to do with the database, but these are actually the basis for SQL to write complex logical relationships. It is estimated that some people are already there now, Turn your nose up, make a bet that they may change their mind after reading it.

The first thing we need to make sure is that SQL is a language with the dual characteristics of relational algebra and relational calculus. Relational algebra, meta-ancestor relational calculus and domain relational calculus are equivalent in expressive ability. It will bring out three integrals, two of which are necessary, entity integrity and referential integrity, which can be understood as the "elegant name" of tables with primary keys and tables with foreign keys in our usual primary foreign key relationship.

I guess some people are getting impatient at this point. Go to the operator right away.

1 according to R S in the above figure, we can analyze why R (X.Y) S (YMagneZ) is different from X and Z because the primary keys of R and S are of the same kind and different.

This can be expressed by our query statement similar to the following

Select A.X,A.Y,B.Y,B.Z

From A

Inner join B on A.Y=B.Y

OK, let's do a magic trick first.

The picture above is equal to

Select R.A3

From R

Inner join S on R.A1A2 = S.A1A2 and S.A1

1 the following figure means that only filtering the data of the S set 1 < 3 means that the value of the first column of the S table is less than 3, then we first perform a WHERE conditional operation on S to select the data.

({1pr 9pm 1}, {2je 1pm 8}) and the field of projected S is A1A2

2 the rest of the division, in relational algebra, means to find the different columns in the set before the division sign and in the set after the division sign. Here is RA3, so the select R.A3 above is the final result we want to output. And the S field projected in the preceding parentheses is required to be A1A2.

Select R.A3

From R

Inner join S on R.A1A2 = S.A1A2 and S.A1 < 3

This is the SQL statement converted from the above formula, which is also called relational algebra.

In addition, in databases, not all databases support all connection methods.

Internal connection inner join external connection left join right join or left outer join right outer join is basically supported, while the full outer join approach is not supported by some databases.

What about the database that does not support FULL OUTER JOIN?

SELECT * FROM R

LEFT JOIN S ON R.A1A2 = S.A1A2

UNION

SELECT * FROM R

RIGHT JOIN S ON R.A1A2 = S.A1A2

You can achieve the effect in the picture above.

After reading the above, do you have any further understanding of the relationship between relational algebra and database? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.