Fundamentals/ICY: Databases 2012/13 WEEK 11 (relations, contd.)

1. Fundamentals/ICY: Databases2012/13WEEK 11 (relations, contd.) John Barnden Professor of Artificial Intelligence School of Computer Science University of Birmingham, UK

2. Remember:((Items in double round brackets are optional material))

3. New for Week 11

4. ((“Relations don’t remember where they came from”)) • Consider a relation R on A, B, C, D, E, … • i.e., R  A  B  C  D  E …. • Suppose A  AA, B  BB, C  CC, etc. • Then: a tuple formed from sets A, B, … is also automatically a tuple formed from AA, BB, … • That is, R  AA  BB  CC  DD  EE …. • So R is also a relation on AA, BB, CC, DD, EE, …. • So a relation has no very close connection to the original sets it might have been defined from, unlike the case of tables, where the attribute domains are part of the nature of the table.

5. ((“Arity” of Relations)) • A relation on two sets is binary, on three sets is ternary, … • … even whennot all the sets are different. • So a relation on A and A is still binary and NOT “unary.” • The members of the relation are two-element tuples. • A relation on, say, A, B and A is ternary and not binary. • The members of the relation are three-element tuples.

6. ((“Arity” of Relations, contd.)) • A “unary relation” on A is a set of singleton tuples formed from A elements. • Unusual (though not inconceivable) to want a single-attribute table in a finalized ER model. • But one-attribute tables often arise dynamically from table operations, as you know.

7. Relations from Somewhere to Somewhere • A relation R “from” set A “to” set B is the same thing as a relation “on” A “and” B — just different terminology. • Similarly, a relation from A, B, C to D, E is the same thing as a relation on A, B, C, D, E.

8. Changing the Sets in a Relation Around • A relation R on A, B, C, D, E, say, obviously “induces” (i.e., gives rise to, in a natural way) a relation on any reordering of the sets, such as D, A, B, E, C, just byreordering each tuple in the same way. • Thus, R induces a relation from, say, D, A to B, E, C. • When there are just two sets A and B, the (only possible) reordering of the sets gives the inverse of R.

9. Removing some of the Sets in a Relation(Projection) • And we can remove some of the sets and the corresponding items from each tuple. • Given the relation on D,A,B,E,C, we can get a relation on, say, D,B,C, just by removing the second and fourth item from each tuple. • This is the mathematical operation underlying the PROJECT relational operator on tables (what I would prefer call Select-Columns or Select-Attributes).

10. Functional Relations(Partial Functions) • A relation R from A to B is functional if, for any a in A, there is AT MOST one (but perhaps no)b in B such that a, b> is in R. • So several things in A can be related to the same thing in B. • But you can’t have several things in B related to the same thing in A. • A functional relation from A to B is also called a partial functionfrom A to B.

11. Functional Relations, contd. • Can generalize: • a relation R from A1, A2, A3 … to B1, B2, B3, …is functional if, • for each combination of things a1, a2, a3, … in A1, A2, A3, … respectively, • there is at most oneb1, b2, b3, … in B1, B2, B3, … respectively such that a1, a2, a3, …, b1, b2, b3, …> is in R.

12. Functional Relations arising from Functional Dependencies • Suppose attribute X is functionally dependent on (= determined by) attributes A, B, … in a table. • Then, at any moment, the induced relation from A, B to X is a partial function from the A, B, … value domains to the X value domain. • Special case: • Consider any superkey (e.g., the primary key) of a table. • Then the relation in the table at a given moment is a partial function from the superkey’s domains to the remaining attribute domains.

13. Caution • The word “partial” in the phrase “partial function” has nothing to do with the word “partial” in “partial dependency” as discussed under Normalization. • Any dependency relationship in a table gives us a partial function, irrespective of whether the dependency is also “partial” in the special sense of involving only a part of the PK.

14. ((Restriction of a Relation)) • Consider a relation R from A to B, • and a subset AA of A. • Then the restriction of R to AA is the relation derived from R by restricting attention to AA, • i.e., including only tuples whose first element is in AA. • The new relation is notated R|AA

15. ((Restriction More Generally)) • Consider a relation R from A, B, …C to D, E, …, F • and subsets AA of A, BB of B, …, CC of C. • Then the restriction of R to AA, BB, …, CC is the relation derived from R by restricting attention to AA, BB, …, CC • i.e., including only tuples whose first few elements are in AA, BB, …, CC respectively. • The new relation is notated R|AA, BB, …, CC

16. Totality of Relations • A relation R from A to B is total (on A)if it relates everythinginA to at least one thinginB. • I.e., for every member a of A, there is at least one b in Bsuch that a, b>is in R.

17. ((Totality, contd.)) • Can generalize: • A relation R from A, B, C, … to D, E, … is total (on A, B, C, …) • if for every member a of A, b of B, c of C, etc. • there is at least oned in D, e in E, etc. such that • a, b, c, …, d, e, …>is in R.

18. ((Partiality of Table Relations)) • The relation in a table (at a given moment), considered as a relation from any of its attribute value domains to the remaining value domains, will almost always NOT be total. • This is simply because it’s highly unlikely that all possible combinations of values from the former collection of value domains will appear in the table!!

19. Functions • A totalfunctional relation from A to B is called a function from A to B. • Eachthing in A is related to exactly one thing in B. (But two different things in A can be related to the same thing in B, and not everything in B needs to be related to anything in A. So the inverse relation is not necessarily either functional or total.) • Caution: every function is also a partial function.

20. ((From Partiality to Totality by Restriction)) • We can always turn a merely-partial R from A to B into a total one by slimming A down enough! Just remove the members of A that aren’t related to anything by R, to get a new set AA. We don’t remove any tuples from R. • R (as a relation from AA to B) is total on AA. • And note that R|AA = R. • AA is called the domain of R, notated dom(R). • Not to be confused with “value domains” of DB entity attributes. • Can generalize the above to non-binary relations.

21. ((Totality contd. and “Onto”)) • A relation R from A to B is ontoif foreverythingin B there is at least one thing inA that is related by R to it. I.e.: • For every member bof B, • there is at least one a in Asuch that a, b> is in R. • Onto-ness is just totality in the other direction. • You can also say that R is total on B, or that the inverse of R is total.

22. Other Categories of Relation • A relation R from A to B is one-to-one (1-1) if, for anya in A, there is at most oneb in B such that a, b> is in R, AND for anyb in B, there is at most onea in A such that a, b> is in R. • That is, both the relation and its inverse from B to A are functional. (But they don’t need to be total.) • To put it another way: it is functional and different members of A map to (= are related to) different members of B. • Or again: Different members of A map to different members of B and different members of B map to different members of A.

23. ((Other Categories of Relation, contd.)) • A relation R from A to B is many-to-one if it is functional but not one-to-one: i.e., there are different members of A that map to the same member of B, in at least one case. • A relation R from A to B is one-to-many if it is not functional but its inverse from B to A is functional. That is, there’s a member of A that maps to more than one member of B; but each member of B maps to at most one member of A. • A relation R from A to B is many-to-many if neither it nor its inverse is functional: i.e., there’s a case of a member of A mapping to more than one member of B, and a case of a member of B mapping to more than one member of A.

24. Relations from Entity Relationships • Between-entity-type relationships also correspond to mathematical relations, distinct from the ones within individual tables.

25. Intuitively ... • Recall that for each entity type there is the set of current entities of that type (the current entity set). • A “relationship” between two (or more) entity types is a description of the fact that at any given moment the database stores a particular mathematical relation on the current entity sets. • E.g., the EMPLOYED-BY relationship from the People entity type to the Organizations entity type says that the database (at any moment) stores a mathematical relation on the People entity set and Organizations entity set.

26. Example Continued • So at any given moment the relation might be • {Person1, Org1>, Person2, Org1>, Person3, Org1>, • Person4, Org2>, Person3, Org2>} • Each Person… and Org… is an entity represented as a row of the corresponding table …... therefore itself mathematically represented as a tuple of attribute values: • So Person1, Org1> could be, in more detail, •  E156, ‘Sam’, ‘Finks’, I678>, I678, ‘IBM’, ‘USA’> > • Note the nested tuples.

27. ((Bridging Entity Types)) • Recall that bridging entity types are brought in to represent M:N relationships (and similarly M:N:P relationships, etc.) • People/Organizations again: the relation within the bridging table would look like • { E156, I678>,  E257, I996>, E714, I678>, … }. • This relation can also be said to correspond to the original People-Organization relationship, but is abstracted from the above relation by replacing tuples representing entities, such as E156, ‘Sam’, ‘Finks’, I678>, bythe PK values in them, such as E156.

28. ((Bridging Entity Types (contd.) )) • But now … what about the relationship between the People and Organization entity types and the bridging entity type!! (Exercise) • And note: We could have chosen to use the bridging-entity-style relation to begin with as our mathematical formulation of the People/Organization relationship. • A mathematical formulation is not objectively given by the world … it is chosen by us, on the basis of convenience for whatever purposes we have.

29. Points about Connectivity • If a relationship between entity types E and F is 1:M, with uniqueness going from F to E, then at any moment the mathematical relation from the F current entity set to the E set is functional. • If a relationship from an entity type to another is 1:1 • then the mathematical relation on current entity sets (either way round) isone-to-one (1-1) • [recall that 1-1 = functional both ways round]

30. Optionality/Mandatoriness • If a relationship from an entity type E to another type F is mandatory then • the math’l relation at any moment from the E current entity set to the F set is total. • If a relationship from an entity type E to another type F is optionalthen • the math’l relation at any moment is not required to be total (but may happen to be).

31. ((Another Caution)) • A one-to-onecorrespondence between a set A and B is a SPECIAL one-to-one relation from A to B (or B to A): it is not onlyone-to-one but also TOTAL (on A) and ONTO (B). (Or we can say: total on both A and B.) • But any 1-1 relation from A to B is a 1-1 correspondence between the subsets of A, B consisting of those members that do happen to feature in the relation! • A 1-1 relation induced by a single table will almost certainly NOT be a one-to-one correspondence between whole attribute domains!