Operators for a Relational Data Model

1. Operators for a Relational Data Model Matt Dube Doctoral Student, Spatial Information Science and Engineering

2. Monday’s Class • Mathematical definitions that underlie the relational data model: • Domain: set of inputs for a particular attribute • Type: structure of the members of that set • Cartesian Product: combination of members of one set with every member of another set…and another…and another • Relation: subset of the Cartesian product of the attribute domains • Key: unique identifying attributes for a relation

3. Discussion of Assignment • Think of an example in your particular discipline where a relational data model might be helpful. • What are the attributes? • What are their domains? • What would you key the database with? • Why?

4. Stepping Up from Relations • Having only useful data was a motivating concern for us • How do we go about that? • Operators are the critical component that allows us to transform a relational database of large size to one which is more manageable • There are six of these operators to be concerned with

5. Projection • Projection is the first of the operators • Mathematical example: • Projection is the “shadow” of a vector of any sort onto a lower dimensional surface. • Think of a right triangle: the lower leg is always shorter than the hypotenuse (why?) • Projection thus represents only considering certain attributes of interest

6. Projection Example • What is the projection here? • Dimensional reduction (Z coordinate removed)

7. Notation • π< pertinent attributes > (R) • New relation is thus a subset of a different space • That different space is a component of the original domain

8. Table Projection

9. Problem with Projection • What dictates the usefulness of a projection? • Is the key involved? • What if a key isn’t involved? • If the key isn’t involved, duplicates are removed to preserve relation status = missing data! • Order not important…attributes can be listed in any order in the projection function (analogous to rotation)

10. Selection • Selection is the second operator, and is the converse of projection • Mathematical example: • Intersections in a Venn Diagram • Selection takes a list of specific properties and find things which satisfy that list

11. Selection Example • What is the selection here? • What do these foxy ladies want to wear today?

12. Notation • σ< selection criteria > (R) • New relation is thus a subset of the original relation

13. Table Selection

14. Properties of Selection • Selection is in the same application space as the original relation • Key structure is thus the same • Selection is associative • Associativity: • Being able to interchange the groupings • Addition and multiplication are associative operators you are familiar with already

15. Renaming • Renaming is the third operator • Mathematical example: • Equivalent terms • Compact = Closed and Bounded • Renaming is used when combining relations • Why would that be potentially necessary? • Notation for this is ρattribute / attribute (R)

16. Cartesian Product • We went over this a bit mathematically, but now we are going to apply it to relations themselves • Mathematical Example: • The X,Y Plane (or the X,Y,Z space, or any other similar type of space) • Take all possible combinations of relation records between 2 or more relations

17. Cartesian Product Example • What is this a Cartesian Product of? • Truth values for P, Q, and R

18. Cartesian Product

19. Properties of a Cartesian Product • How big will a Cartesian Product be? • Treat this generally: • Relation R has x rows and y columns • Relation S has z rows and w columns • R x S has x * z rows and y + w columns • Why? • The key of a Cartesian product needs to involve at least one attribute from both R and S. • Why?

20. Union • Union is the fifth operator • Mathematical Example: • Addition of positive integers is a natural union • Addition of sets • Union thus takes relations and binds them together

21. Union Example • What is this a union of? • The 50 States and Puerto Rico and the Virgin Islands

22. What does a Union have? • Unioned attributes always have identical domains • Why? • Do unioned attributes have to have identical names? • No • Think of unioning two sets together. Did those sets have the same names? • Unions are commutative • Commutativity: • Changing the order is irrelevant • Addition and multiplication are commutative operators • Difference between this and associativity?

23. Commutative vs. Associative

24. Difference • Difference is the sixth and final operator • Mathematical example: • Subsets • Difference produces a subset not in common

25. Difference Example • What is the difference here?

26. Unary and N-ary Operators • Unary operators only have one operand (in this case they only involve one relation) • Projection, Selection, Renaming • N-ary operators involve N operands (in this case they involve N relations) • Cartesian Product, Union, Difference • Can you classify the six?

27. Friday • Deriving relations and operators in our first order languages • Combinations of relations • Joins • Algebras