We can effectively re-shape relations by removing tuples. Also by adding or removing attributes.
Restrict
We can restrict a relation to only include certain tuples using [ ].
S[STATUS=30]| SNO | SNAME | STATUS | CITY |
|---|---|---|---|
| S3 | Blake | 30 | Paris |
| S5 | Adams | 30 | Athens |
Note: this does not modify the original S relvar. It is an expression returning a new, in this case throwaway, value. We could assign the expression back to S, and that would modify it, but we will discuss such updates in a later section.
We can build comparisons using = != < <= > >= + - * / ( ) and combine them using and, or, xor, not.
S[STATUS<30 and STATUS>=10]| SNO | SNAME | STATUS | CITY |
|---|---|---|---|
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S4 | Clark | 20 | London |
Project
We can project (keep) certain attributes of a relation, using { }.
S{SNO, SNAME, STATUS}| SNO | SNAME | STATUS |
|---|---|---|
| S1 | Smith | 20 |
| S2 | Jones | 10 |
| S3 | Blake | 30 |
| S4 | Clark | 20 |
| S5 | Adams | 30 |
We can also project away certain attributes of a relation using ~{ }, i.e. project all but the ones given.
S~{SNO, STATUS}| SNAME | CITY |
|---|---|
| Adams | Athens |
| Blake | Paris |
| Clark | London |
| Jones | Paris |
| Smith | London |
If we remove the unique attributes, no duplicate entries will appear: a relation is always a set of tuples and a set has no duplicates. In the relational model, a tuple is a statement about the world and saying it twice adds nothing new.
S| SNO | SNAME | STATUS | CITY |
|---|---|---|---|
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
S{CITY}| CITY |
|---|
| Athens |
| London |
| Paris |
Notice how there are only three tuples in this projection.
Rename
We can rename attributes using := inside { }.
S{SN:=SNO}| SN |
|---|
| S1 |
| S2 |
| S3 |
| S4 |
| S5 |
Adding * means keep all existing attributes.
S{*, SCITY:=CITY}| SNO | SNAME | STATUS | SCITY |
|---|---|---|---|
| S1 | Smith | 20 | London |
| S2 | Jones | 10 | Paris |
| S3 | Blake | 30 | Paris |
| S4 | Clark | 20 | London |
| S5 | Adams | 30 | Athens |
We can use two renames to swap attributes too.
S{SNAME:=CITY, CITY:=SNAME}| SNAME | CITY |
|---|---|
| Athens | Adams |
| London | Clark |
| London | Smith |
| Paris | Blake |
| Paris | Jones |
Extend
We can also add attributes (extend) using :=.
S{*, NEW_STATUS:=STATUS/10}| NEW_STATUS | SNO | SNAME | STATUS | CITY |
|---|---|---|---|---|
| 1 | S2 | Jones | 10 | Paris |
| 2 | S1 | Smith | 20 | London |
| 2 | S4 | Clark | 20 | London |
| 3 | S3 | Blake | 30 | Paris |
| 3 | S5 | Adams | 30 | Athens |
All together
And we can restrict and project and rename and extend in a single expression.
S[STATUS=20]{SNO, STATUS, SCITY:=CITY, NEW_STATUS:=STATUS/10}| NEW_STATUS | SNO | STATUS | SCITY |
|---|---|---|---|
| 2 | S1 | 20 | London |
| 2 | S4 | 20 | London |
Each of these operations (restrict, project/rename/extend) returns a relation.
Note: again, none of these operations modify the original S relvar. They simply return relations derived from S.