Fundamentals/ICY: Databases 2013/14 Week 11 – Monday – relations, ended.

1. Fundamentals/ICY: Databases2013/14Week 11 – Monday – relations, ended. John Barnden Professor of Artificial Intelligence School of Computer Science University of Birmingham, UK

2. Reminder

3. 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.

4. New

5. ((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 (e.g., between staff ids and names in the staff table) will almost certainly NOT be a one-to-one correspondence between whole value domains!

6. 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.

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

8. 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.

9. 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.

10. 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.

11. ((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.

12. Points about Connectivity • If a DB 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 DB 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) [= functional both ways]

13. Connectivity, contd • If a DB relationship from an entity type to another is “1:1” • then at any moment the mathematical relationisone-to-one (1-1). • If a DB relationship is “1:M” • then the math’l relationat any momentmay beone-to-many (but may by chance be one-to-one). • If a DB relationship is “M:N” • then the math’l relationat any momentmay bemany-to-many (but may by chance be one-to-many, many-to-one or one-to-one).

14. Optionality/Mandatoriness • If a DB 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 (set of current “entity occurrences”) to the F current entity set is total. • If a DB 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).

15. Optionality/Mandatoriness, contd • If a relationship from an entity type E to another type F is both mandatory and unique then • the math’l relation at any moment from the E current entity set to the F set is a function (because it’s both functional and total).