Group, Image

We can group parts of relations. Since the group operation returns a relation, we can then apply further processing on it, such as adding totals. The image operator lets us embed matching rows from another relation or relation expression. These can be used for summaries.

Group

We can group some attributes together as nested relations (or RVAs - relation-valued-attributes). For example, if we make a group in the parts table from all the attributes except COLOR:

group(P, {PNO, PNAME, WEIGHT, CITY}, gpart)

COLOR gpart

Blue PNO PNAME WEIGHT CITY

P3 Screw 17 Rome
P5 Cam 12 Paris
Green PNO PNAME WEIGHT CITY

P2 Bolt 17 Paris
Red PNO PNAME WEIGHT CITY

P1 Nut 12 London
P4 Screw 14 London
P6 Cog 19 London

This can perhaps be expressed more simply by making a group from all the columns except COLOR:

r := group(P, ~{COLOR}, gpart)
r

COLOR gpart

Blue PNO PNAME WEIGHT CITY

P3 Screw 17 Rome
P5 Cam 12 Paris
Green PNO PNAME WEIGHT CITY

P2 Bolt 17 Paris
Red PNO PNAME WEIGHT CITY

P1 Nut 12 London
P4 Screw 14 London
P6 Cog 19 London

This version has the advantage that it still gives us a tuple per COLOR even if the attributes of P change later (providing COLOR remains). It is also closer to the SQL GROUP BY construct.

Ungroup

We can unnest it using ungroup.

ungroup(r, gpart)

COLOR PNO PNAME WEIGHT CITY

Blue P3 Screw 17 Rome
Blue P5 Cam 12 Paris
Green P2 Bolt 17 Paris
Red P1 Nut 12 London
Red P4 Screw 14 London
Red P6 Cog 19 London

COLOR	PNO	PNAME	WEIGHT	CITY
Blue	P3	Screw	17	Rome
Blue	P5	Cam	12	Paris
Green	P2	Bolt	17	Paris
Red	P1	Nut	12	London
Red	P4	Screw	14	London
Red	P6	Cog	19	London

Image

We can also create RVAs using the image operator !!.

This includes tuples from the imaged relation that match each tuple in the outer relation.

To find non-London suppliers and their shipments:

S[CITY != "London"]{*, ship:=!!SP}

ship SNO SNAME STATUS CITY

PNO QTY

P1 300
P2 400 S2 Jones 10 Paris
PNO QTY

P2 200 S3 Blake 30 Paris

S5 Adams 30 Athens

Notice how suppliers with no shipments still appear. We don't need an outer join.

Aggregate Operators

We can use aggregate operators to add totals. For example, to total the shipments above, by supplier, we can use the sum operator on the image relation.

S[CITY != "London"]{*, tot_qty:=sum(!!SP, QTY)}

tot_qty SNO SNAME STATUS CITY

0 S5 Adams 30 Athens
200 S3 Blake 30 Paris
700 S2 Jones 10 Paris

tot_qty	SNO	SNAME	STATUS	CITY
0	S5	Adams	30	Athens
200	S3	Blake	30	Paris
700	S2	Jones	10	Paris

Notice how we get a 0 for suppliers with no shipments.

There are other aggregate operators available:

S[CITY != "London" and count(!!SP) > 0]{*, 
    min_qty:=min(!!SP, QTY), 
    max_qty:=max(!!SP, QTY)
}

min_qty max_qty SNO SNAME STATUS CITY

200 200 S3 Blake 30 Paris
300 400 S2 Jones 10 Paris

min_qty	max_qty	SNO	SNAME	STATUS	CITY
200	200	S3	Blake	30	Paris
300	400	S2	Jones	10	Paris

Aggregate operators needing an attribute, when used on single attribute relations, can omit the attribute name:

count(S{CITY})