Database System Concept 9-additional relational operations

In addition to the basic relational operations, there are some additional (Additional) operations, because if you use only basic operations, some expressions are cumbersome, and additional operations are simplified for some basic expressions.

A) intersection (Set-intersection operation)
The intersection operator is ∩, which is used to find data that is common to several collections, such as classes that start in Fall 2009 and Spring 2010.
(semester = "Fall" ∧year=2009 (section)
∩
(semester = "Spring" ∧year=2010 (section))
The equivalent expression of the intersection is: R∩s = R-(r-s)

b) Natural-join operation
On the basis of Cartesian product, the ganso of common fields are selected, and the repetitive ganso is removed. fields in the result set are returned in the following order: field of the right relationship with the field of the left relationship
The ID expression that queries the names of all teachers and their teaching information is:
Πname, course_id (instructor?teaches)
The equivalent expression for the connection is:
R?s = r∪s (r.a1 = s.a1∧r. A2 = S.a2∧ ... ∧r. an = S.an (RxS))
If R and S do not have the same Value property, then R?s = RxS.
If you want to query all the physics faculty names and the names of the courses taught are:
Πname,title (dept_name = "Comp. Sci.") (Instructor?teaches?course))
Whether it's (instructor?teaches), course or instructor? (teaches?course), the result of the operation is not affected by the order of the connections

c) Assignment Operation assignment operation
The symbol is ←, like the assignment of other programming languages, ← You can assign the value of an expression to a temporary variable to avoid nesting too much to understand. For example, the equivalent expression of R?s can also be written as:
Temp1←rxs
Temps←σr. A1 = S.a1∧r. A2 = S.a2∧ ... ∧r. an = s.an (TEMP1)
Result=πr∪s (TEMP2)
← Complex logic can be simplified into procedural code, and ← must be assigned to a temporary variable.

d) OUTER JOIN outer JOIN
An outer join is an extension of the connection operation that can be used to handle missing information, both of which are missing from the natural connection, where the left outer join, the right outer join, and the full outer join? Leave the left, right, and all unmatched rows separately.
The equivalent expression for the left outer join is:
(R?s) ∪ (R-πr (R?s)) x{(null,... NULL)}
where {(null,... null)} is a schema after s-r

e) Extended operation
Generalized projection (generalized Projection)
Generalized projections allow the operation of arithmetic and string processing at the same time as projection operations, such as:
ΠID,NAME,SALARY*13 (instructor)

Aggregation (aggregation)
Aggregation includes operations such as Min, Max, average, count, and the input of the aggregation operation is a set of values, and the output is a single value. The average teacher's salary can be expressed as:
Gsum (Salary) (instructor). The correct g should be the calligraphic font.
Statistics on the number of teachers in the spring of 2010 need to be weighed:
Gcount-distinct (ID) (σsemester= "Spring" ∧year=2010 (teaches))
When you count the average salary for each department, you need to group by department to write:
Dept_name G Averge (Salary) (instructor)


Learning materials: Database System concepts, by Abraham Silberschatz, Henry F.korth, S.sudarshan

Database System Concept 9-additional relational operations