Object-Relational and Nested-Relational Databases By Dr. Akhtar Ali

1. Extensions to Relational Databases Object-Relational and Nested-Relational Databases By Dr. Akhtar Ali

2. Lecture Theme & References • Theme • The need for extensions in Relational Data Model (RDM) • Classification of database systems • Extensions to RDM, namely • Object-Relational • Nested-Relational • References • R. Elmasri and S. B. Navate, Fundamental of Database Systems – 5th Edition, 2007, Chapter 22 • Thomas Connolly and Carolyn Begg, Database Systems- 4th Edition, 2004, Chapter 28 • Michael Stonebraker, Paul Brown and Dorothy Moore, Object-Relational DBMSs: The Next Great Wave and Object-Relational DBMSs: Tracking the Next Great Wave (1996, 1998) • Prof. Norman Paton, Object Relational Databases, University of Manchester.

3. Why Extend Relational Data Model • To counter the following weaknesses of RDM: • Poor representation of ‘real world’ conceptual model • Usually the relational schema does not correspond to real world entities • Semantic overloading • The same relation is used to represent entities (classes) as well as relationships (associations, aggregation) • Poor support for integrity and business rules • Fixed number of attributes & all attribute values must be atomic • Limited operations on the data • Difficult to handle recursive queries • Impedance mismatch (when SQL is embedded in 3GLs) • Type System mismatch, Evaluation Strategy mismatch • Poor navigational access

4. Michael Stonebraker’s Classification • Michael Stonebraker presents this four-quadrant matrix in the book entitled “Object-Relational DBMSs: The Next Great Wave” • This is a classification of both database applications and systems.

5. Lower-Left Quadrant • Those application that process simple data and require no query capability e.g. text processors (word, emacs) • Information has little internal structure. • Document updates are relatively infrequent. • Documents are of modest size. • Queries are simple string or pattern searches.

6. Upper-Left Quadrant • Those application that process simple data and require complex query capability e.g. a typical business application require RDBMS. • Information has straightforward and fixed structure. • Information collection may be large. • Information storage must be reliable. • Queries are relatively complex. • Updates are frequent and Security is vital.

7. Lower-Right Quadrant • Those application that process complex data and require no query capability e.g. a CAD application requires OODBMS. • Information has complex structure. • Analysis is complex. • Information is moderate in quantity. • Queries are not central. • Updates are periodic.

8. Upper-Right Quadrant • Those application that process complex data and require complex query capability e.g. an Image Data Archive requires ORDBMS. • Information has complex structure. • Information may include special data types. • Images, Spatial information • Information is large in quantity. • Queries are important. • Updates are periodic.

9. Object-Relational Databases • Object-Relational databases (ORDBSs) seek to: • Retain the relational model as a subset. • Retain the strengths of the relational model and all the technologies that come with it. • Support complex data types (BLOBS, ADTs, Spatial, and Multimedia, …). • Support object-oriented design. • Reduce impedance mismatch (type system).

10. Advantages of ORDBMSs • Resolves many of known weaknesses of RDBMS. • Reuse and sharing: • reuse comes from ability to extend server to perform standard functionality centrally; • gives rise to increased productivity both for developer and end-user. • Preserves significant body of knowledge and experience gone into developing relational applications.

11. Disadvantages of ORDBMSs • Complexity. • Increased costs. • Supporters of relational approach believe simplicity and purity of RDM are lost. • Some believe RDBMS is being extended for what will be a minority of applications. • OO purists not attracted by extensions either. • SQL now extremely complex.

12. Classification Problems ! • Most of OODBMSs claim to be in Upper-Right quadrant not just ORDBSs.

13. Myth: OODBs do not support queries

14. Characteristics of ORDBs • According to Stonebraker, ORDBs have the following characteristics. 1. Base type extensions 2. Complex objects 3. Inheritance 4. Active Rules • Oracle 8i supports 1,2, and 4. Item 4 is independent of object extensions. Oracle 9i has most of the features (SQL3 compliant). We assume using features of Oracle 9i throughout, although our server is running 10g.

15. From Relations to Object-Relations • Structured types for attributes (beside atomic) • Composite attributes (e.g. structs or records) • Arrays • Nested relation i.e. the value of an attribute can be an entire relation • This removes the restriction of first normal form (1NF) • Methods • Special operations can be defined for, and applied to instances of a user-defined type (UDT) • Identifiers for tuples • Tuples play the role of objects. • Each tuple has a unique ID that is generated by the system • References • An attribute can have a value that is a pointer to an object. This feature allows objects sharing.

16. Nested-Relations • Relations that violate the 1NF rule of relational model are called Nested-Relations. • Nested-Relational Model (NRM) are sometimes referred to as the Non-1NF or Non-First Normal Form (NFNF) or NF2 relational model. • Nested-Relational Model offers very limited features • Composite attributes (e.g. structs or records) • Nested relation i.e. the value of an attribute can be an entire relation • NRM does not support Object-Sharing • NRM based system supports new algebraic operations for manipulating nested-relations • Nest (to convert 1NF into NF2) • Unnest (to convert NF2 into 1NF)

17. Nesting and Unnesting • Family data in First Normal Form: • Nesting on the basis of Father and Mother grouping returns: • Unnesting of the Children collection returns:

18. Relational Design for Movie Database • Sample Relational Database • A Relational Query Select s.name, sa.street, sa.city, m.title, m.year, m.length From Stars s, Star_Adress sa, Movies m, Movie_Star ms Where s.name = sa.name And s.name = ms.name And ms.title = m.title

19. Nested-Relational Design • Nested-Relational Table • A Nested-Relational Query Select s.name, s.address, s.movies From Stars s • Watch out for duplication of data!

20. Object-Relational Design • Object-Relational Database • An Object-Relational Query Select s.name, s.address, s.movies From Stars s • Watch out for Object Sharing!