Relational Extensions

• The Golden Rules, or core features, which were divided in two categories: those coming from the object-oriented data model and those from the DBMS field,

• Optional features, which advocated for advisable characteristics and

• Open issues, which embraced open problems not solved back then.

• Golden Rules: These rules define an OODBS. A system that integrates the traditional database management with the object-oriented data model.

  • OO Data Model: Data must be internally organized according to the object-oriented data model; i.e., objects as means to represent data and universally identified by an object identifier (OID). Encapsulation, in terms of functions, is mandatory as well as traditional object-oriented features such as inheritance and extensibility, overriding / overloading operators and late binding. All in all, providing computational completeness (i.e., any algorithm can be implemented following this paradigm).

  • DBMS: A database management system should provide data persistence and therefore, it must deal with issues such as secondary storage management, concurrency, recovery and ad hoc query facilities.

• Optional features: These features are considered to be appealing, but the manifesto acknowledges the difficulty to provide them so they are categorized as optional. It includes multiple inheritance, type checking and type inferring, data distribution (in the sense of distributed database management systems), transactions and versioning (any modification and not only the last version must be stored).

• Finally, open features refer to those features still representing a challenge for the community, mainly because of the lack of standards. For example, several object-oriented languages co-existed or were developed back then. The manifesto claimed that it was not possible to deploy a different OODBS for each existing high-level language. Thus, some agreement should be reached to provide uniformity. In the manifesto, they identify three main issues on which to agree; a programming paradigm, an internal representation system and a type system. These open issues were never solved, however, as discussed in section 1.1.4.

• Traditional DBMS services and support for richer object structures and rules (i.e., integrity constraints),

• Must subsume second generation DBMSs and

• Must be open to other subsystems.

• Object and rule management: These features extend the relational model to deal with objects and rules (i.e., more expressive integrity constraints). Basically, they combine relational and object-oriented features. For example, they mainly talk about rich types, although objects are also supported and consequently, encapsulation. Note, however, that multiple inheritance is mandatory and, interestingly, object identifiers (OIDs) are allowed as an alternative to primary keys. Rule management was dealt as a first-class citizen in order to deal with richer semantics. Accordingly, additional means (to those already provided by the relational model) to enforce constraints are identified as mandatory (this point was the seed for triggers and procedures, which were not available for relational databases at that time).

• DBMS Function: This set of features extends those principles upon which relational database systems were built. From the database point of view, a non-procedural high-level language should be available to access the database (i.e., SQL) instead of a procedural one (as claimed in the OODBSs manifesto). For better or worse, they say, a non-procedural high-level access language is needed, and cannot be replaced by object-oriented programming languages. Their ultimate objective behind this statement was preserving data independence. However, back then, SQL was heavily criticized for not being computationally complete. In this sense, the ORDBS manifesto proposed additional features to complement SQL; namely: updatable views (in their own words, dynamic views) and collections (to deal with procedural arrays).

• Towards an open system: By an open system they meant that any application (i.e., high-level languages) should be able to access the database, but always through programming interfaces (this was the seed for what nowadays is known as high-level language connectors; e.g., ODBC or JDBC). SQL remains as the only access language and, again, they recall us that high-level languages should not directly access the physical structures of the database (to preserve data independence). Instead, they should query the database and receive an answer by means of SQL.

What we have presented in previous sections are the essentials behind the object-oriented data model (OODM) and the alternative object-relational data model (ORDM). In this section, we discuss the main differences and agreements between both models, which can be summarized with the following claims (A stands for agreement, for disagreement and for a partial agreement):

• Complex Objects, Type Classes, Extensibility etc. vs. Rich Type System: Concepts from the ORDM parallel those in OODBs, with the notions of user-defined data types, object tables formed from user-defined types, hierarchies of user-defined types and object tables, rows of object tables with internal object identifiers, and relationships between object tables that use object identifiers as references. However, in a object-relational database all these concepts are finally mapped to the relational structure. Thus, they are introduced as a relational extension.

• Inheritance vs. Multiple Inheritance: Inheritance is essential for the OODM, but the ORDM goes even further by asking for multiple inheritance, whereas supporting multiple inheritance is not mandatory for OODBS (see section 1.1.1).

• Encapsulation: Both models fully agree on the need for encapsulation, which refers to the fact that class methods must also be stored in the database, altogether with the object itself.

• Object Identity (OID) vs. OIDs and PKs: Whereas the OODM claims for the necessity of OIDs and refer to them by means of object pointers, the ORDM claims for the co-existence of OIDs and object references with primary keys and foreign keys. It must be up to the database designer to decide which mechanism better suits their necessities.

• DBMS main features (persistence, concurrency, recovery etc.): There is no argument about what a database management system means, and both models advocate for the same principles.

• HLLs Computational Complexity vs. SQL, Constraints enforcement, updatable views and HLLs Interfaces: High-level languages (HLLs in short) are computationally complete (i.e., you can implement any algorithm), whereas SQL by itself was not. The ORDM proposes to complement SQL with constraints enforcement (basically, triggers and procedures), updatable views (to support dynamic views over data) and also HLLs interfaces to access data (but always through a query-answer manner). Furthermore, people behind the third generation database system manifesto proposed this solution because a non-procedural high-level language untied to physical structures (such as SQL) was mandatory to preserve data independence.

• Open Systems: By this claim, both models argue that databases must be able to communicate with other systems or applications.

• OODBSs vs. ORDBSs: All in all, the OODM is supported by OODBSs, whereas the ORDM is supported by ORDBSs, being incompatible with each other.

• They lacked a strong theoretical framework (like the set theory behind the relational model) behind the OODM,

• And because OODBSs bloomed with an incredibly strong experimental activity, with many systems and proposals being around but with no de facto standard (instead, relational systems were supported by major relational vendors).

• LOB (Binary Large Object) type: the LOB type was conceived as a “relational” feature to support object-oriented databases. Internally, LOB serializes objects as byte streams.

• Row type (ROW): The row type allows a column to contain several attributes and, thus, violates the first normal form. These attributes are accessed by means of the dot notation (e.g., person.age). Relevantly, ROWs cannot be references not reused externally, which makes them useless.

• User defined types (UDTs): UDTs were introduced in two different ways, as distinct types and structured types. Importantly, both are types, not objects. Distinct types attach semantics to already built-in datatypes (e.g., CREATE DISTINCT TYPE age AS Integer) but they do neither provide inheritance nor methods. Structured types (most commonly known as user defined types in databases) were thought to support objects, as discussed later in section 1.2.

• Typed tables: Tables where each row is an object (i.e., an instance of a UDT).

• Inheritance: Inheritance is allowed at UDT level. However, multiple inheritance (as claimed in the manifesto) is not supported.

• REF Type and object pointers, as an alternative to primary and foreign keys.

• Collection type (ARRAY): This collection type implements an array that could contain multiple values.

• An extra column to store the mandatory OID and

• Extra space to store NULL pointers (see section 1.2.2.).

   INSERT INTO my_songs VALUES (
   release (person ('Adele', fullname ('Adele','Atkins','L.B.') ),'Chasing Pavements'), 10);

		SELECT s.song.artist.realname.get_id(), s.rating  
		FROM my_songs s 
		WHERE s.rating > 8
		ORDER BY s.rating; 

• The OID of the referenced object (16 bytes long) and

• The OID of the table or view containing that object (16 bytes long).

   CREATE TYPE artist UNDER person (
      country VARCHAR2(10),
      genre VARCHAR(20)
   );
   CREATE TABLE artists OF artist;
   
   CREATE TABLE single (
      artist_ref REF artist SCOPE IS artists, 
      title VARCHAR2(20),
      album VARCHAR2(20)
   );
   
   INSERT INTO single SELECT REF(a),'Chasing Pavements','19'
   FROM artists a
   WHERE a.name = 'Adele';

   DECLARE artist_ref REF artist; 
   BEGIN 
      SELECT REF(a) INTO artist_ref
      FROM artists a 
      WHERE a.name = 'Adele';
   END;

   SELECT DEREF(s.artist_ref), s.title 
   FROM single s; 

   SELECT s.artist_ref.name, s.title
   FROM singles s 
   WHERE s.album = '19';

• The referred object has been deleted from the database (this is possible because REFs only guarantee that the referenced table does exist) or

• By revoking privileges.

• It contains only properly encoded legal Unicode characters.

• None of the special syntax characters such as '<' and '&' appear except when performing their mark-up delineation roles.

  When you need any of these special characters but you do not want them to be interpreted as part of the XML syntax, as part of the content you put between tags or in attributes, use the corresponding entities as shown in Code 3. They are replaced with their corresponding character after the XML syntax has been processed.

  Code 3. XML entities for XML special characters

      Character      Entity
        &      		    &
        <      		    &lt;
        >      	   	 &gt;
        '     	      &apos;
        "      		    &quot;
  

  For instance, if you want to include a piece of XML inside the content of an element but it should not be interpreted as XML, you can replace all special characters with the corresponding entities, like in Code 4.

  Code 4. Encoding XML syntax to include it in element content

     <example>
        &lt;artist
           type=&quot;Person&quot;&gt;
           Adele 
        &lt;/artist&gt;
     </example>
  

  Another alternative is to use the <![CDATA[…]]> construct to mark a set of characters that should not be interpreted as XML markup. An alternative to Code 4 based on this option is shown in Code 5.

  Code 5. Encoding XML syntax to include it in element content using CDATA region

     <example>
        <![CDATA[
           <artist 
           type="Person">
           Adele
        </artist>]>
     </example>
  

• The beginning, end and empty-element tags that mark the elements are correctly nested, with none missing and none overlapping, i.e. not as shown in Code 6.

  Code 6. Examples of missing name closing element and overlapping artist and name elements

     <artist><name>Adele</artist>
     <artist><name>Adele</artist></name>
  

• The element tags are case-sensitive; the beginning and end tags must match exactly.

• Tag names cannot contain any of the characters " # $ % & ' ( ) * + , / ; < = > ? @ [ \ ] ^ ` { | } ~, nor a space character, and cannot start with - . or a numeral.

• There is a single root element that contains all the other elements, which might be nested. Altogether, the XML syntax encodes an underlying tree data structure, as shown in Figure 2 for the XML syntax in Code 7.

  Codi 7. A piece of XML syntax that corresponds to the tree structure in Figure 4

     <metadata>
        <artist type="Person">
           <name>Adele</name>
           <full-name>
              Adele<initials>L.B.</initials>Adkins
           </full-name>
        </artist>
        <release>
           <title>Rolling in the Deep</title>
        </release>
     </metadata>
  

Figure 4. Tree structure for the XML example in Code 7

   <?xml version="1.0" encoding="UTF-8"?>
   <metadata xmlns="https://musicbrainz.org/ns/mmd-2.0#"
       xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="https://musicbrainz.org/ns/mmd-2.0# 
       musicbrainz_mmd-2.0.xsd">
       <release id="79ef6f41-51a3-3ee5-8b2f-7347de023e30">
           <title>Rolling in the Deep</title>
           <status>Official</status>
           <quality>normal</quality>
           <text-representation>
               <language>eng</language>
               <script>Latn</script>
           </text-representation>
           <artist-credit>
               <name-credit>
                   <artist id="cC2c9c3c-b7bc-4b8b-84d8-4fbd8779e493">
                       <name>Adele</name>
                       <sort-name>Adele</sort-name>
                       <disambiguation>UK Soul/Jazz singer</disambiguation>
                   </artist>
               </name-credit>
           </artist-credit>
           <date>2011-01-17</date>
           <country>GB</country>
           <barcode>634904152123</barcode>
           <asin>B004DK49WI</asin>
           <medium-list count="1">
               <medium>
                   <position>1</position>
                       <track-list count="2" offset="0">
                           <track>
                               <position>1</position>
                               <length>229706</length>
                               <recording 
                                   id="1a13c710-4b7e-4701-8968-cd61f2e58110">
                                   <title>Rolling in the Deep</title>
                                   <length>229000</length>
                               </recording>
                           </track>
                           <track>
                               <position>2</position>
                               <title>If It Hadn't Been For Love</title>
                               <length>186933</length>
                               <recording 
                                       id="addd7af6-36c5-4626-a623-aC23b8bc3d2e">
                                   <title>If It Hadn't Been for Love</title>
                                   <length>188000</length>
                               </recording>
                           </track>
                       </track-list>
                   </medium>
           </medium-list>
       </release>
   </metadata>

• Unstructured storage provides highest throughput when inserting and retrieving entire XML documents. It also provides the greatest degree of flexibility in terms of the structure of the XML that can be stored. These throughput and flexibility benefits come at the expense of less performance when working with documents at a finer granularity level. There is little the database can do to optimize queries or updates on XML stored using a Character Large Object (CLOB), Binary Large Object (BLOB), Binary File (BFILE), or VARCHAR column.

• Structured storage is based on the XMLType, a new datatype that makes the database aware that XML is being stored. It has a number of advantages, including optimized memory management, reduced storage requirements, B-tree indexing that optimize XPath queries and in-place updates. These advantages are at a cost of a greater processing overhead during ingestion and retrieval and reduced flexibility in terms of the structure of the XML.

• Sequence: the subelements for the complex type structure defined inside of a sequence should appear in the appearing order in instance XML documents.

• All: the collection of subelements must appear, but the order is not relevant, they might appear in the instance XML documents in a order different from the order they appear in the XML Schema complex type.

• Choice: from the collection of subelements in the choice just one will be chosen in the instance XML documents.

• Range: this restriction can be applied to simple types derived from numeric or temporal types. The lower limit of the range is defined using the minInclusive or minExclusive subelement and the value attribute, whose value is included in the range if inclusive or not if exclusive. For the upper limit the subelements are maxInclusive and maxExclusive.

  For instance, to define a new simple type for the range of integers between 10 and 99, both included in the range, the new simple type is derived from integer and the minInclusive facet is set to 10 while the maxInclusive one is set to 99:

     <xsd:simpleType name="myInteger">
         <xsd:restriction base="xsd:integer">
            <xsd:minInclusive value="10"/>
            <xsd:maxInclusive value="99"/>
         </xsd:restriction>
     </xsd:simpleType>
  

• Pattern: this restriction allows defining a regular expression that constraints the desired combinations of characters from the simple type string. The constraint is set using a facet called pattern in conjunction with the regular expression [A-Z]{2} that is read "from the range of upper-case letters between A and Z, take two of them".

     <!-- A two-letter country code like 'DE', 'UK',... so 
      it does not include 'SPA' or 'USA' -->
     <xsd:simpleType name="def_iso-3166">
        <xsd:restriction base="xsd:string">
           <xsd:pattern value="[A-Z]{2}"/>
        </xsd:restriction>
     </xsd:simpleType>
  

  Another example of pattern restriction from the MusicBrainz schema is about defining a simple type for ISWC (the international standard for identifying music creations):

     <!-- An ISWC code: 
        C           - single-letter prefix character
        NNN.NNN.NNN - 9-digits separated by "." grouped 3x3
        C           - check digit
        -->
     <xsd:simpleType name="def_iswc">
        <xsd:restriction base="xsd:string">
          <xsd:pattern
            value="[A-Z]-[0-9]{3}\.[0-9]{3}\.[0-9]{3}-[0-9]"/>
        </xsd:restriction>
     </xsd:simpleType>
  

• Enumeration: it can be used to constrain the values of almost every simple type, except the boolean type. The enumeration facet limits the new type to a set of distinct values. For example, the quality levels, derived from NMTOKEN (a normalized string without spaces), whose value must be low, normal or high:

     <xsd:simpleType name="def_quality">
        <xsd:restriction base="xsd:NMTOKEN">
            <xsd:enumeration value="low"/>
            <xsd:enumeration value="normal"/>
            <xsd:enumeration value="high"/>
        </xsd:restriction>
     </xsd:simpleType>
  

• Length: another way to limit the set of possible values for a simple type is by restricting their length to a particular quantity or to define a minimum or maximum length. This is done using the length, minLength and maxLength restriction facet elements.

     <xsd:element name="password">
        <xsd:simpleType>
            <xsd:restriction base="xsd:string">
           <xsd:minLength value="5"/>
           <xsd:maxLength value="8"/>
            </xs:restriction>
        </xsd:simpleType>
     </xsd:element>
  

• NMTOKENS

• IDREFS

• ENTITIES

   <xsd:element name="listOfLengths">
   <xsd:simpleType>
      <xsd:list itemType="myInteger"/>
   </xsd:simpleType>
   </xsd:element>

   <listOfLengths>189 187 191</listOfMyInt>  

   <!-- Two-letter country code like 'DE', 'UK', 'FR' etc. -->
   <xsd:simpleType name="def_iso-3166">
      <xsd:restriction base="xsd:string">
          <xsd:pattern value="[A-Z]{2}"/>
      </xsd:restriction>
   </xsd:simpleType>
   <!-- Two-letter country code followed by a 3 letter subdivision -->
   <xsd:simpleType name="def_iso-3166-2">
       <xsd:restriction base="xsd:string">
         <xsd:pattern value="[A-Z]{2}\-[A-Z]{2}"/>
       </xsd:restriction>
   </xsd:simpleType>
   <!-- The union of the previous simple types -->
   <xsd:simpleType name="countryCodes">
      <xsd:union memberTypes="def_iso-3166 def_iso-3166-2"/> 
   </xsd:simpleType>

   BEGIN
     DBMS_XMLSCHEMA.registerSchema(
       SCHEMAURL => 'https://musicbrainz.org/ns/mmd-2.0#',
       SCHEMADOC => bfilename('examplesDir',
       'musicbrainz_mmd-2.0.xsd') );
   END;
   /

• The database will ensure that only XML documents that validate against the XML schema can be stored in the column or table.

• Since the contents of the table or column conform to a known XML structure, Oracle XML DB can use the information contained in the XML schema to provide more intelligent query and update processing of the XML.

• Constraining the XMLType to an XML schema provides the option of storing the content of the document using structured-storage techniques. Structured storage decomposes the content of the XML document and stores it as a set of SQL objects rather than simply storing the document in an unstructured way, such as text in a CLOB.

• A syntax for defining the parts of an XML document.

• A set of expressions that select parts of an XML document.

• A set of standard functions for manipulating strings, numbers, dates etc.

• A sequence of nodes

• A boolean value, true or false

• A number

• A string of characters

• for: it links one or more variables to expressions written in XPath, creating a flow of tuples (rows) where each tuple contains the values for all the variables defined by the for.

• let: it binds a variable to the result of an expression, which might involve variables defined by a for. There might be more than one let in an XQuery. The variable values are added to the tuples generated by a for clause or, if there is no for clause, creating a unique tuple containing these links.

• where: it filters the tuples by removing all those that do not meet the conditions.

• order by: it sorts the tuple stream according to the given criterion.

• return: it is evaluated for each tuple in previously filtered and reordered stream. It builds the result XML of the query by concatenating the results of all return evaluations. It can combine XML code with XPath expressions, the latter are surrounded with '{' and '}' if mixed with XML.