When you create a calculation in an RDBMS, you use a code to inform the system which mathematical operation you want it to perform. Here is a list of the common symbols and the operations they represent.
Eight operations were originally defined for relational databases and they form the core of modern database operations. The following list describes those original operations:
- Selection: This selects some or all of the records in a table. For example, you might want to select .
Select drink from fridge where drink = "juice";
- Projection: This drops columns from a table (or selection).
- Union: This combines tables with similar columns and removes duplicates. For example, suppose you have another table named FormerCompetitors that contains data for people who participated in
previous years competitions. Some of these people are competing this year and some are not.
You could use the union operator to build a list of everyone in either table. (Note that the operation would remove duplicates, but for these tables you would still get the same person several times with different events.)
- Intersection: This finds the records that are the same in two tables. The intersection of the FormerCompetitors and Competitors tables would list those few who competed in previous years and who survived to compete again this year (the slow learners).
- Difference: This selects the records in one table that are not in a second table. For example, the difference between FormerCompetitors and Competitors would give you a list of those who competed in previous years but who are not competing this year (so you can email them and ask them what the problem is).
- Cartesian Product: This creates a new table containing every record in a first table combined with every record in a second table. For example, if one table contains values 1, 2, 3 and a second table contains values A, B, C,
then their Cartesian product contains the values 1/A, 1/B, 1/C, 2/A, 2/B, 2/C, 3/A, 3/B, and 3/C.
- Join: This is similar to a Cartesian product except records in one table are paired only with those in the second table if they meet some condition. For example, you might join the Competitors records with the NextOfKin records where a Competitors record's NextOfKin value matches the NextOfKin record's Name value.
In this example, that gives you a list of the competitors together with their corresponding next of kin data.
- Divide: This operation is the opposite of the Cartesian product. It uses one table to partition the records in another table. It finds all of the field values in one table that are associated with every value in another table. For example, if the first table contains the values 1/A, 1/B, 1/C, 2/A, 2/B,
2/C, 3/A, 3/B, and 3/C and a second table contains the values 1, 2, 3, then the first divided by the second gives A, B, C.