#翻译 # Original from Database.System.Concepts (6th. edition.2010) 2.6Relational Operations, original author Abraham Silberschaz, Henry F. Korth, S. Sudarshan

2.6 Relationship Operations

All process relational query languages provide a set of actions that can be applied to a single relationship or to a pair of relationships. These operations have good and expected properties, and their results are always a single relationship. This property allows you to combine several of these operations in a modular way. Specifically, because the result of a relational query is itself a relationship, a relational operation can be applied to the result of a query and to a given set of relationships.

the specific relationship operations vary depending on the language, but are appropriate for the generic framework that we describe in this section. In Chapter 3 , we show The specifics of how SQL is used to represent operations .

  the most common operation is from a single relationship ( e.g. coach ) example $8.5 Inner structure Span style= "font-family:arial;" >) 2.1 The predicate wage is greater than $85000 we get 2 . 10

Table 2.10 Query Choose the results of the instructor, pay more than $85000

Another common operation is to select certain properties from a relationship ( column ) . The result is a new relationship with only those attributes selected. For example, suppose we want a list of teacher IDs and salaries without having to List names and dept from the instructor relationship in Figure 2.1 The name value, as shown in the result 2.11 , has two attribute IDs and a salary. Each tuple in the result comes from a tuple in the instructor relationship, but contains only the selected properties that are displayed.

The JOIN operation allows a combination of two relationships to be combined into a single tuple, combining one tuple in two relationships into one tuple. There are many different ways to connect relationships (as we'll see in Chapter 3 ). Figure 2.12 shows an example of adding a tuple from the teacher and department tables, and the new tuple shows information about each teacher and the department she is working on. This result is achieved by combining each tuple with a tuple in the instructor relationship .

In the form of a connection as shown in figure 2.12 , this is called a " Natural Connection ", A tuple from the instructor relationship matches a tuple in the departmental relationship if their dept Name property has the same value. All of these matching tuples appear in the connection results. In general, a natural join operation in two relationships matches the value of a tuple, and the values of those tuples are the same in all attribute names.

Table 2.11 The results of selecting attribute IDs and salaries from the teacher relationship

Table 2.12 The natural participation of Teachers and department relations

Descartes Product The operation combines tuples in two relationships, but unlike a join operation, its result contains all tuples in two relationships, regardless of whether their property values match.

because relationships are collections, we can perform normal collection operations on relationships. The union operation performs a collection of two similar structures of tables ( such as all graduate students ' tables and all undergraduates ' tables ). For example, a department can get a collection of all students. Other collection operations, such as intersections and setting differences, can also be performed .

As mentioned earlier, we can perform operations on query results. For example, if we want to find the ID and salary of a teacher whose annual salary exceeds 8.5 million , we will perform the first two actions in the example above. First, we select these tuples from the teacher relationship, where the pay value is greater than $8.5, and then, from this result, select two attribute IDs and salaries, and the results are shown in Figure 2.13 the relationship shown in, including ID and salary. In this case, we can perform the operation in any order, but in all possible cases, we can see that this is not the case .

Table 2.13 Select the attribute ID and salary of teachers with annual salary exceeding 8.5 million

Relational algebra

relational algebra defines a set of operations on relationships that are calculated on a number, as with usual algebraic operations, such as addition, subtraction, or multiplication. Just as the algebraic operation of a number takes one or more numbers as input and returns a number as the output, the relational algebra operation typically takes one or two relationships as input and returns a relationship as output.

The relationship algebra is described in detail in the sixth chapter, but we have outlined some of the following operations.

sometimes, the result of a query contains duplicate tuples. For example, if we choose the Dept name attribute from a teacher relationship, there will be some duplicate examples, including compositing. Sci. It appeared three times. Some relational languages strictly follow the mathematical definition of a collection and delete the copy. Also, take into account the large amount of processing required to remove a replica from a large result relationship, preserving the copy. In the latter case, the relationship is not purely mathematical in meaning.

Of course, the data in the database must change over time. You can update a relationship by inserting a new tuple, deleting an existing tuple, or modifying the values of some properties. You can delete the entire relationship and create a new relationship.

we will discuss relational queries and updates using the SQL language in Chapters 3 through 5 .

#翻译 # Original from Database.System.Concepts (6th. edition.2010) 2.6Relational Operations, original author Abraham Silberschaz, Henry F. Korth, S. Sudarshan