Group and Ungroup

Recall from Chapter 2 that relations with relation-valued attributes (RVAs for short) are legal. Figure 5-1 shows relations R1 and R4 from Figures 2-1and 2-2 in that chapter; R4 has an RVA and R1 doesn't, but the two relations clearly convey the same information.

Figure 5-1. Relations R1 and R4 from Figures 2-1 and 2-2 in Chapter 2

Of course, we need a way to map between relations without RVAs and relations with them, and that's the purpose of the GROUP and UNGROUP operators. I don't want to go into a lot of detail on those operators here; let me just say that, given relations R1 and R4 as shown in Figure 5-1, respectively, the expression:

   R1 GROUP ( { PNO } AS PNO_REL ) 

will produce R4, and the expression:

   R4 UNGROUP ( PNO_REL ) 

will produce R1. SQL has no direct counterpart to these operators.

By the way: if R4 includes exactly one tuple for supplier number S x, say, and if the PNO_REL value in that tuple is empty, then the result of the foregoing UNGROUP will contain no tuple at all for supplier number S x. For further details, I refer you to my book An Introduction to Database Systems, Eighth Edition (Addison-Wesley, 2004) or the book Databases, Types, and the Relational Model: The Third Manifesto, Third Edition (Addison-Wesley, 2006), by Hugh Darwen and myself.

And by the way again: you might be wondering what operations on relations with RVAs look like. Well, operations on any relation, when they refer to some attribute A of that relation of type T, say, typically involve what we might call "suboperations" on values of that attribute A that are, precisely, operations that have been defined in connection with that type T. So if T is a relation type, those "suboperations" are operations that have been defined in connection with relation types which is to say, they're essentially the relational operations (join and the rest) described in the present chapter! See Exercises 5-27 through 5-30 at the end of this chapter.