The Object Relational Approach

1. The Object Relational Approach Date., C.. (2003). Object/Relational Databases. In An Introduction to Database Systems (859). US: Pearson Education. Chp 25 OODB, and Chp26 ORDB - In library

2. Topics • Brief Historical Perspective • The first blunder • An (semi-working) example • The second blunder • Implementation issues • Rapprochement • SQL support

3. History • Introduced in the late 1990s • They were called Universal Servers • Virtuoso Open Link, perhaps the most popular open source example • However all the popular vendors provide Object/Relational support now • SQL:99 standard provided OR support

4. The basic idea • Vendors aimed to provide support for both object and relational capabilities. • In other words something of an attempt at rapprochement between the two technologies • Date believes any rapprochement ought to be firmly based on relational technology.

5. Evolution rather than revolution • Date argues such systems should evolve to incorporate the best features, and we should not discard the relational approach. • The general belief in the late 80s early 90s was that OO would eventually supersede relational

6. Date’s views not shared by all • “Computer science has seen many generations of data management, starting with indexed files, later, network and hierarchical DBMSs, and more recently relational DBMS. Now we are on the verge of another generation of database systems that provide object management supporting much more complex kinds of data”

7. Incorrect assumptions?! • Proponents of Object Oriented systems believe that OO databases will supplant relational databases and render them obsolete • However relational is different in part because it is not ad hoc. (based on a formal model) • What about OODBs – are they ad hoc?

8. Ad hoc? • Means, a solution built for a specific problem, non-generalisable, not able to be adapted for other purposes. • This from the Object-Oriented Database System Manifesto (easily found with google) “Thus, with respect to the specification of the system, we are taking a Darwinian approach: we hope that, out of the set of experimental prototypes being built, a fit model will emerge. We also hope that viable implementation technology for that model will evolve simultaneously.”

9. In other words... • Lets go out there, write some code and build some systems without any predefined model, and see what happens... • Hmm, sounds suspiciously ad hoc to me.

10. OO – the good points • Proper data type support • Inheritance • Proper data type support is already supported by the relational model (although implemented a little haphazardly by vendors) in the form of Domains (Create Type...in Oracle) • Inheritance, we’ll discuss later

11. Rounding up the usual suspects • OIDs – pointers, unnecessary and undesirable in data management. • All “instance variables” (ie. data) must be manipulated by “methods” • Declarative integrity constraints – typically not supported in OO systems • Catalog/Data dictionary – typically not supported • Views – typically not supported

12. Latest generation RDBMS • Support all manner of complex data types • Spatial data (GIS) • Financial, Multimedia • Time series, biological, text based etc. • Date argues a true relational DBMS is an object relational DBMS anyway. • DBMS vendors should be encouraged to extend their systems to include full data type support (domains, Date argues)

13. Object/Relational • They looked attractive Date argues because suddenly there was complex data type support – which should have been there in the first place. • ...but this isn’t an argument for abandoning the relational model.

14. So, it should be simple • Let the users define their own types problem solved! • But, Date argues, the vendors (most) have made two big mistakes in implementing Object/Relational • He calls them TheTwo Great Blunders • After Einstein, I suspect, who called one of his (ad hoc) errors a Great Blunder

15. The first great blunder • Before we address the issue of rapprochement between object and relations we have to consider this question. • What concept is it in the relational world that is the counterpart to the object class in the object world?

16. Two possibilities • domain = object class • table (relvar) = object class • First is obviously right because domains and object classes are both just types. • Second is wrong – but why? • Note, Date calls tables relvars – which means relation variables

17. The second possibility • Is wrong because variables and types are not the same things. • A table is essentially a variable (albeit a complex one). • Some people and vendors have embraced the second possibility – Date calls this the first great blunder. • How could we make such a mistake?

18. To see why, consider this example Create object class EMP ( EMP# CHAR(5), ENAME CHAR(20), SAL NUMBER, HOBBY CHAR(20), WORKS_FOR CHAR(20) )… ; EMP#, ENAME and so on are public instance variables – defined simple, for simplicity Create table EMP ( EMP# CHAR(5) not null, ENAME CHAR(20) not null, SAL NUMBER not null, HOBBY CHAR(20) not null, WORKS_FOR CHAR(20) not null )… ;

19. Tempting to equate the two • However there are some extensions required to make the SQL table more like an object • First Extension •   Permit composite attributes

20. Composite (or tuple-valued) attributes Create table EMP ( EMP# CHAR(5), ENAME CHAR(20), SAL NUMBER, HOBBY ACTIVITY, WORKS_FOR COMPANY )… ; Create table ACTIVITY ( NAME CHAR(20), TEAM INTEGER ) ; Create table COMPANY ( NAME CHAR(20), LOCATION CITY ) ; Create table CITY ( CITY CHAR(20), REGION CHAR(2) ) ;

21. Explanation • HOBBY is declared of type ACTIVITY which in turn is a table type containing two attributes – NAME and TEAM • HOBBY is thus a pair of values • This is roughly analogous to the concept of containment hierarchy in OO • Already a violation of domain = class, because the domain for HOBBY is defined as the table ACTIVITY ** • Also, it isn’t really attributes containing rows, it is attributes containing pointers to rows in the ACTIVITY table. (logical disconnect!)

22. Second Extension • Relation-valued attributes • That is, attribute values allowed to be sets of rows (tuples) • For eg. Employee might have a number of different HOBBIES instead of just one allowed by a composite attribute Create table EMP ( EMP# CHAR(5), ENAME CHAR(20), SAL NUMBER, HOBBIES SET OF (ACTIVITY), WORKS_FOR COMPANY )… ;

23. Explanation • HOBBIES value in EMP is now a set of zero or more (NAME, TEAM) pairs, that is tuples (or rows) from the table ACTIVITY • This extension is roughly analogous to collection objects – a more complex version of containment hierarchy.

24. Third Extension • Methods (i.e., operators) Create table EMP ( EMP# CHAR(5), ENAME CHAR(20), SAL NUMBER, HOBBY set of (ACTIVITY), WORKS_FOR COMPANY ) METHOD RETIREMENT_BENEFITS():NUMBER;

25. Explanation • RETIREMENT_BENEFITS is a method that takes an EMP row as it’s argument and returns a NUMERIC value.

26. Final (structural) extension • Subclasses (inheritance) Create table PERSON (IRD# CHAR(9), DOB DATE, ADDRESS CHAR(50) ); Create table EMP AS SUBCLASS OF PERSON( EMP# CHAR(5), ENAME CHAR(20), SAL NUMBER, HOBBY set of (ACTIVITY), WORKS_FOR COMPANY ) METHOD RETIREMENT_BENEFITS ( ):NUMBER;

27. Explanation • EMP now has 3 additional attributes inherited from PERSON. • Each EMP “is a” PERSON • Note in relational terminology we’d call this supertable / subtable

28. Manipulative extensions • Obviously we need some way to query the data • Path expressions – this could work • EMP.WORKS_FOR.LOCATION.STATE • Row and relation literals (‘E001’, ‘Smith’, $60,000, (( ‘Rugby’, 15), (‘Cricket’, 11)), (‘Oracle’, (‘Wellington’, ‘Te Aro’)))

29. Cont. • Relational Comparison Operators – SUBSET for example (Oracle’s is SUBMULTISET) • Operators for traversing class hierarchy, (to retrieve EMP and PERSON info together) – but would this result in the return of a table (like normal SQL) (concept of closure) • Ability to invoke methods within SELECT and WHERE clauses • Ability to access individual components within attribute values that are rows or nested tables

30. Ok, so what is so wrong with all this? • Table = class implies row = object, and attribute = (public) instance variable. But a true encapsulated object class has methods and no public instance variables. • A table on the other hand does have public instance variables (the data), optionally has methods and is definitely not encapsulated • So the two notions are quite different.

31. More... • Attribute definitions SAL NUMERIC and WORKS_FOR COMPANY are majorly different. • NUMERIC is a true data type – it places a time-independent constraint on the values in SAL • COMPANY is not a true data type. Values are time-dependant, depending on the current values in COMPANY • So the table vs domain distinction (collection vs class distinction) is muddied.

32. Pointer chasing... • Row objects can contain other objects (eg. EMP can contain COMPANY objects) • But do they? Not really. • They contain pointers to those contained objects. (be very clear about this) Oracle is quite explicit “A REF is a logical pointer to a row object that is constructed from the object identifier (OID) of the referenced object and is an Oracle built-in datatype. REFs and collections of REFs model associations among objects, particularly many-to-one relationships, thus reducing the need for foreign keys. REFs provide an easy mechanism for navigating between objects. You can use the dot notation to follow the pointers”

33. This creates (major) issues • Can we insert an EMP row and specify a COMPANY value that does not exist in the COMPANY table. • If yes, then no constraint exists. • If no, then the INSERT operation becomes unnecessarily complex – dot notation and the entire COMPANY row. (rather than a simple FK reference)

34. DELETE anomaly • If we want an on delete restrict rule for companies. ie. delete a company will fail if it has any associated employees. • We have to write procedural code to check all EMP rows for contained COMPANY that you want to delete. • Unnecessarily complex • Then, the code has to be invoked in any delete operation. • By removing the FK constraint in favour of object containment we’ve created additional complexity. But for what?

35. Deleting EMPs • This won’t cascade (unless we create more procedural code) to delete linked COMPANY rows – despite the pretense EMP contains COMPANY • From Oracle (orphan pointers) “if you specify a scoped REF to a row object and then delete the row object, the scoped REF becomes a dangling REF because the referenced object no longer exists.”

36. Views • What if I define a view V based on a select query on EMP on HOBBIES • If table = class then, • V a class? What class is it? What methods apply to it?

37. So... • We could go on, but... • It doesn’t seem we are talking about the relational model anymore. • Date asks, have we not undermined the conceptual integrity of the relational model?

38. Origins of the first great blunder • Date - lack of consensus about the term “object” • In programming, loosely object seems to mean value or variable • In semantic modelling (UML) seems not to mean variable but entity. • Seems like a level of abstraction mistake. • OO Class modelling is essentially ER modelling by a different name (we all know this right.)

39. The second great blunder • A logical consequence of the first, but significant in it’s own right • The mixing of pointers and relations (tables)

40. Tuple or row and relation valued attributes • No probs, but • a. That the attribute must have some specific values that appear in some other base table and • b. The idea that they are not actually rows or relations per se but instead are pointers to rows or relations • Pointers pointing to row or table values makes no sense (to Date)

41. Associating operators (methods) with tables • No probs either – but, • Essentially we are just talking about triggers or stored procedures. • Why have to write triggers to enforce integrity constraints when they were there already?

42. Subclasses and superclasses • Date thinks the implementation of this is extreme dodgyness in action. • He wants proper type inheritance – discussed somewhere else.

43. Path expressions • These by, for example, oracles own admission are just shorthand for following pointer chains. • How is this easier or better than PK=FK equivalence in joined views?

44. There is more... • See CJ Date, Chapter 26. • Crux of the argument here is this. • Pointers point to variables (not values) everyone agree? • Relational model deals with relation values which are sets of rows with values, which are in turn sets of scalar values. • There is more to this, but this will do for the moment.

45. Pointers • Date argues, mixing pointers and relations is a MAJOR departure from the relational model. • Most people understand the act of comparing values. But relatively few people understand the complexity of pointers. • The manipulation of pointers is way more susceptible to buggyness than comparing values

46. The point... • Of the relational model was meant to be one of conceptual simplicity. • How did the second blunder arise? • Hard to say – all object systems and languages include pointers (Object ID) • Oracle’s is the REF pointer. • Date argues – could be political and, • A failure to distinguish between the model and it’s implementation.

47. A true object relational system (rapprochement) • Don’t commit the two great blunders would be a good start • Ad hoc query facility, view definition and declarative integrity constraints • Methods that span classes (common shareable methods for general operations) • Dynamically defined classes (for ad hoc query results)

48. More.. • FK rules • OID (REF) and pointer chasing hidden from users • Difficult object questions (what happens if I try to join two objects) go away • Encapsulation apply to scalar values • Conceptually clean...

49. SQL • SQL:1999 included the OR features • User defined types • DISTINCT types and structured types • Basically scalar types and support for type inheritance (structured types) • REF type generator • Supertype subtype table support • To be brutally honest - Implementation of these features is more complex than traditional SQL, and lacks conceptual clarity, with no discernable gain in performance or productivity