Relation candidate keys

By definition the union of two relation contains no duplicates. So it contains at least one candidate key, the composition of all attributes. In your example (id, name) is a candidate key.

DBMS do not "resolve" the issue by assigning a primary key because in almost all DBMS (at least those that use SQL), the union of two tables is a derived table (or a view or a CTE but not a base table) and primary keys⁽¹⁾ can be defined only for base tables (not for views, not for derived tables and not for common table expressions).

Still, the DBMS optimizer is often aware that the derived table that results from a union has no duplicates and that the composition of all attributes uniquely defines a row and may use this fact in subsequent operations on the table (e.g. in a more complex query where the union is joined to or unioned with other tables).

^{(1) : and constraints in general. There is an generalized SQL constraint construction (CREATE ASSERTION) that allows constraints across several tables that could be used for creating a unique constraint on a union but assertions have not been implemented by any major DBMS - probably because it's a very complicated issue. Firebird claims to have them but not sure how well or what the restrictions are.}

To express that relation in algebraic notation you actually need to alias the attribute.

The Cartesian product, by design, does not recognize common attribute names, so much like a single operation doesn't work in a self relation scenario, it's necessary to perform a rename within the notation.

The self join example:

R( a, b, c );

≠ R ⋈ R ( a, b, c );

Using rename:

R( a, b, c );

S = ρ_{a/b, b/b2, c/c2} R;

∴ R ⋈ S ( a, b, c, b₂, c₂ );

For instance:

E( EmpID, ManagerID, EmpName );

M = ρ_{EmpID/ManagerID, ManagerID/ManagerEmpID, EmpName/ManagerName} E;

∴ E ⋈ M ( EmpID, ManagerID, EmpName, ManagerEmpID, ManagerName );

The Cartesian product can be expressed using relational algebra in the same way:

R1( a, b, c );

R2( a, e, f );

≠ R1 × R2 ( a, b, c, e, f );

So then with rename:

R1( a, b, c );

R2( a, e, f );

R3 = ρ_a/a2R2;

∴ R1 × R3 ( a, b, c, a₂, e, f );