How Does Union Work in Relational Algebra?

TL;DR

Relational algebra union combines tuples from two compatible input relations and removes duplicate rows from the result. It is a binary operation modeled on set union, so the output contains every tuple found in either relation or in both. Projection can first isolate matching attributes, such as customer names, before union is applied.

Transcript

foreign we will see the next relational algebra fundamental operation the relational algebra Union operation we know basically there are six fundamental relational algebra operations and we have already seen about the first two operations the select and project operations in the previous lectures now we are going to focus on the third fundamental o... Read More

Key Insights

• Union is one of the six fundamental relational algebra operations, and it is introduced after the select and project operations in the lecture sequence.
• A relation is closely associated with the mathematical concept of a set, so relational algebra union follows the same basic idea as union in set theory.
• Union is a binary operator because it requires two input relations or tables. Its result contains tuples belonging to the first input, the second input, or both inputs.
• Duplicate rows are eliminated from a union result. If the same value occurs in both input relations, it appears only once in the resulting relation.
• The union symbol denotes the relational algebra union operation. An expression such as R union S represents the combination of the tuples contained in relations R and S.
• Union cannot always be applied directly to arbitrary relations. The lecture identifies necessary conditions, including that the relations being combined must have the same number of attributes.
• Projection can prepare relations for union by selecting corresponding columns from each input. Each projection produces a temporary relation that can then participate in the union operation.
• The banking example uses union to list customers who are depositors, borrowers, or both. Customer names are projected from each relation, combined, and deduplicated in the output.

Questions & Answers

Q: What is the union operation in relational algebra?

The union operation combines tuples from two input relations and returns a relation containing every tuple that belongs to the first relation, the second relation, or both. It follows the basic union concept from set theory because relations closely correspond to sets. When an identical tuple occurs in both inputs, the resulting relation includes it only once.

Q: Why is union called a binary operator in relational algebra?

Union is called a binary operator because it requires two input relations or tables to perform its operation. For example, relations A and B can be combined so that the output contains members from A, members from B, and members found in both. A single input is insufficient because union is specifically defined as combining two relations.

Q: Does relational algebra union keep duplicate rows?

Relational algebra union does not keep duplicate rows in its result. If a value or tuple appears in both input relations, it is included only once in the output. In the fruit example, apple occurs in both sets, but the union contains one apple. The same duplicate-elimination behavior is also noted for the project operation.

Q: When can union be performed on two relations?

Union cannot be performed directly on every possible pair of relations because necessary conditions must first be satisfied. One stated condition is that both relations must have the same number of attributes. If relation A has five attributes and relation B has four, their direct union cannot be performed. The lecture also notes that another condition must be checked.

Q: How can projection be used before a union operation?

Projection can extract a single attribute or a group of attributes from each source relation before union is performed. Each projection produces a temporary relation. For example, customer names can be projected from the depositor relation and separately from the borrower relation. These single-attribute results can then be combined through union instead of combining the full source relations.

Q: How does union find all bank customers who are depositors or borrowers?

Project the customer-name attribute from the depositor relation, then project the customer-name attribute from the borrower relation. Apply union to the two projected relations. The resulting single-attribute relation lists customers associated with the bank as account holders, loan borrowers, or both. Any customer appearing in both source relations is included only once.

Q: What is the difference between unioning full relations and projected attributes?

Unioning full relations attempts to combine every attribute and tuple from both relations, so the required conditions must be checked across those complete relations. Unioning projected attributes first creates smaller temporary relations containing only the selected columns. In the banking example, only customer-name columns are combined, even though the depositor and borrower relations also contain account and loan numbers.

Q: How is an R union S result constructed from two single-attribute relations?

Begin by placing the members of R into the output relation. Then inspect the members of S. Values already present in the output are ignored because duplicates are eliminated, while values found only in S are added. In the example, shared letters appear once, while the dollar sign, the value one, and the hash symbol are added from S.

Summary & Key Takeaways

• Union is presented as the third of six fundamental relational algebra operations, following select and project. Because a relation closely corresponds to a mathematical set, relational union behaves like set union. It combines members from two input relations and produces a relation containing tuples found in either input or in both inputs.

• Union removes duplicate tuples from its resulting relation. In the example, relation R contains a, b, c, e, and f, while relation S also contains a, b, e, and f alongside additional symbols. Shared values appear once, while values unique to either relation are added to the combined output relation.

• A banking example finds every customer associated with the bank as either an account holder or a loan borrower. Customer names are projected separately from the depositor and borrower relations, producing two single-attribute temporary relations. Union then combines those projected names, ensuring customers appearing in both relations are listed only once.