Object-relational databases

1. Object-relational databases And object-oriented databases

2. Note Some information has been taken from http://www.amazon.com/Database-Systems-Application-Oriented-Approach/dp/0321268458/ref=sr_1_2?ie=UTF8&qid=1361919837&sr=8-2&keywords=kifer+bernstein+lewis

3. Problems with pre-object-era relational database systems • Extreme impedance mismatch • Atomic values are only common data type • Complex objects demand many-way joins and relational dbs are not optimized for this • Difficult to create similar but different table structures, like cars and red-cars • You must program with two very different languages • Difficult to represent objects that are collections

4. An overriding problem: only value-based semantics • No notion of object identity • We make assumptions about object identity based on key values • We manipulate tuples that represent properties of objects, not the objects themselves • We can look cardinality information about object sets by doing selections • No notion of versioning • We must simulate versions or time-based variations by using multiple tuples and temporal/version fields

5. What is a relational DB? • Collections of tables • A set of tuples, with atomic domains • Keys, FKs, null limits, FDs and MVDs, and triggers • Manipulated with SQL • We extract semantics at runtime

6. What is (or would be) an object database? • Collections of classes • Defined by types • Including behavioral encapsulation • Subtypes allowed • Components of an object can be sets, tuples, other objects • Manipulated with an object-oriented language extended with an SQL derivative that includes path expressions

7. Object identity • The key questions: • are these the same object or are they two objects that look the same? • What is all the relevant information about this particular object? • Object IDs are immutable: different ID, different object • A relational PK only tells us that this description of some object out there is unique

8. An object is a… • Pair (OID, value) • The oid is unseen • The value can have structure, such as… • A flat tuple • A tuple with a collection attribute • A tuple with an oid value attribute

9. More precisely, A value is… • Primitive – like an integer or char string • A reference value, i.e., an oid • A tuple (at1: value1, at2:value2, …, atn: valuen) • A set {}

10. A class is… • A group of objects structured by a type • Three parts • Type • Method signatures • An extent – the objects in the class • Classes are organized in a class hierarchy

11. ODMG • Group of object db vendors • Developed a standard • ODL – object definition language • OQL – object query language • A transaction protocol • Language bindings for c++, smalltalk, java

12. The big idea behind object databases • Objects in the host language are identical in nature to the ones in the DB • Objects can become persistent “selectively” • Manipulating an object or a group of objects is the same in the host language and the db language – because it is a single language • This is a “persistent programming language” • No more JDBC connectivity needed • No more embedding SQL in the host language • No more impedance mismatche

13. A note on multiple language bindings • C++, Java, Smalltalk, C# are similar, but not identical • So we need a “reference data model” to map these languages to

14. Why did they die? • Demands a new application stack • New database • New query language • Not optimized for set selection • The perceived need apparently wasn’t there • We are used to forms and tables • Potential user domains needed so much more, like engineering constraints, solid modeling, animation, complex version hierarchies

15. Objects in the small:the SQL object extension • First, all legacy relational databases are valid • A relation is • A set of tuples OR… • A set of objects • An object is an oid and a tuple-value • A tuple value is (at1: value1, at2:value2, …, atn: valuen)

16. A value is • Primitive • Reference (an oid) • Another tuple • A collection • Multiset • Fixed sized array

17. Note • A tuple can have complex internal structure without having to be an object • Thus, we call this “objects in the small”

18. Example table CREATE TABLE students( NameCHAR(30), Address row(NumberINTEGER, Street CHAR(20), ZIP CHAR(5)) )

19. Example queries SELECT students.name FROM student s s WHERE s.address.zip = ‘80309’ INSERT INTO students(Name, Address) VALUES (‘John Doe’, row “123 Elm, ‘80309’))

20. User defined types(UDTs) • These encapsulate structural and behavioral content • A UDT can serve as the domain of an attribute

21. Example type CREATE TYPEPersonType AS ( Name CHAR(20), AddressROW(Number INTEGER, Street CHAR(20), ZIP CHAR(5)) ); CREATE TYPE StudentTypeUNDERPersonType AS ( Id INTEGER, Status CHAR(2) ) METHODaward_degree() RETURNS BOOLEAN; CREATE METHODaward_degree() FOR StudentType LANGUAGE C EXTERNAL NAME ‘file:/home/admin/award_degree’;

22. Example table creation • As an attribute type: CREATE TABLE TRANSCRIPT ( StudentStudentType, CrsCode CHAR(6), Semester CHAR(6), Grade CHAR(1) ) • As a table type: CREATE TABLE STUDENTOFStudentType; Such a table is called typed table.

23. Important distinction • Only typed tables contain objects (ie, tuples with oids) • Compare: CREATE TABLE STUDENTOFStudentType; and CREATE TABLE STUDENT1 ( Name CHAR(20), Address ROW(Number INTEGER, Street CHAR(20), ZIP CHAR(5)), Id INTEGER, Status CHAR(2) ) • Both contain tuples of exactly the same structure • Only the tuples in STUDENT – not STUDENT1 – have oids

24. Example collection type • Set (multiset) data type was added in SQL:2003. CREATE TYPE StudentType UNDER PersonType AS ( Id INTEGER, Status CHAR(2), Enrolled REF(CourseType) MULTISET )