2 Replies Latest reply: Feb 28, 2010 3:19 PM by Zlatko Sirotic RSS

    Implementing ORDBMS

    725856
      hi,

      i'm plaaning to develop a cms in .net. i'v heard alot about ordbms and have studied bit on internet. I want to knw what exectly is difference between rdbms and ordbms. plus what will be the advantages and disadvantages in having Ordbms inplace of rdbms.

      thanks
        • 2. Re: Implementing ORDBMS
          Zlatko Sirotic
          It seems that web page "OR(DBMS) or R(DBMS), That is the Question" is removed.
          The original text (October 2007):



          In the late 1990s, several vendors (including Oracle) released object-relational DBMS products (ORDBMS).

          But, C.J.Date in "Database in Depth" (2005, page 32) says:
          "After all, the whole point of an "object/relational" system is precisely that we can have attribute values in relations that are of arbitrary complexity.
          Perhaps a better way to say it is this: a proper object/relational system is just a relational system with proper type support
          - which just means it's a proper relational system, no more and no less."

          In "An introduction to Database Systems" (2004, page 885), Date says:
          "... object/relational systems ... are, or should be, basically just relational systems that support the relational domain concept (i.e., types) properly
          - in other words, true relational systems, meaning in particular systems that allow users to define their own types."


          Here is an example of how to use "object-relational" features "in the proper way" (in Date's sense) - to use object type as domain.

          First create object type address_t:
          CREATE OR REPLACE TYPE address_t AS OBJECT (
            state       VARCHAR2(30),
            city        VARCHAR2(30),
            postal_code VARCHAR2(16)
          ) NOT FINAL
          /
          Next, create a relational table that has one column of object type address_t:
          CREATE TABLE users_tab (
            user_id    NUMBER(30) PRIMARY KEY,
            first_name VARCHAR2(30),
            last_name  VARCHAR2(30),
            address    address_t
          )
          /

          INSERT INTO users_tab
            VALUES (1, 'FN1', 'LN1', address_t ('S1', 'C1', 'PC1'));
          This SELECT shows the physical structure of table users_tab:
          SELECT segcol#, name, segcollength, type#
            FROM sys.col$
          WHERE obj# =
                 (SELECT object_id
                    FROM user_objects
                   WHERE object_name = 'USERS_TAB'
                 )
          ORDER BY segcol#;

             SEGCOL# NAME                           SEGCOLLENGTH      TYPE#
                   1 USER_ID                                  22          2
                   2 FIRST_NAME                               30          1
                   3 LAST_NAME                                30          1
                   4 ADDRESS                                   1        121
                   5 SYS_NC00005$                             16         23
                   6 SYS_NC00006$                             30          1
                   7 SYS_NC00007$                             30          1
                   8 SYS_NC00008$                             16          1
          Hidden columns SYS_NC00006$, SYS_NC00007$, SYS_NC00008$ corresponds to attributes state, city, postal_code.


          Now, create a subtype address_sub_t under (supertype) address_t:
          CREATE OR REPLACE TYPE address_sub_t UNDER address_t (
            street VARCHAR2(40)
          )
          /

          INSERT INTO users_tab
             VALUES (2, 'FN2', 'LN2', address_sub_t ('S2', 'C2', 'PC2', 'STREET 1'));
          SELECT shows changed physical structure of table users_tab:
          SELECT segcol#, name, segcollength, type#
            FROM sys.col$
          WHERE obj# =
                 (SELECT object_id
                    FROM user_objects
                   WHERE object_name = 'USERS_TAB'
                 )
          ORDER BY segcol#;

             SEGCOL# NAME                           SEGCOLLENGTH      TYPE#
                   1 USER_ID                                  22          2
                   2 FIRST_NAME                               30          1
                   3 LAST_NAME                                30          1
                   4 ADDRESS                                   1        121
                   5 SYS_NC00005$                             16         23
                   6 SYS_NC00006$                             30          1
                   7 SYS_NC00007$                             30          1
                   8 SYS_NC00008$                             16          1
                   9 SYS_NC00009$                             40          1
          Oracle automatically adds a new hidden column, SYS_NC00009, that corresponds to attribute street!


          Various SELECT statements:
          SELECT user_id, first_name, last_name,
                 address
            FROM users_tab
          ORDER BY user_id;

             USER_ID FIRST_NAME                     LAST_NAME   ADDRESS(STATE, CITY, POSTAL_CODE)
                   1 FN1                            LN1         ADDRESS_T('S1', 'C1', 'PC1')
                   2 FN2                            LN2         ADDRESS_SUB_T('S2', 'C2', 'PC2', 'STREET 1')
          SELECT user_id, first_name, last_name,
                 u.address.state
            FROM users_tab u
          ORDER BY user_id;

             USER_ID FIRST_NAME                     LAST_NAME                      ADDRESS.STATE
                   1 FN1                            LN1                            S1
                   2 FN2                            LN2                            S2
          We can't (directly) select hidden columns that corresponds to attributes of subtype (address_sub_t):
          SELECT user_id, first_name, last_name,
                 u.address.street
            FROM users_tab u
          ORDER BY user_id;

                 u.address.street
                 *
          ERROR at line 2:
          ORA-00904: "U"."ADDRESS"."STREET": invalid identifier
          But, we can use TREAT ("object_column" AS "subtype"):
          SELECT user_id, first_name, last_name,
                 TREAT (address AS address_sub_t).street address_street
            FROM users_tab
          ORDER BY user_id;

             USER_ID FIRST_NAME                     LAST_NAME                      ADDRESS_STREET
                   1 FN1                            LN1
                   2 FN2                            LN2                            STREET 1
          Show rows that belongs to a selected subtype:
          SELECT user_id, first_name, last_name,
                 TREAT (address AS address_sub_t).street address_street
            FROM users_tab
          WHERE address IS OF (address_sub_t)
          ORDER BY user_id;

             USER_ID FIRST_NAME                     LAST_NAME                      ADDRESS_STREET
                   2 FN2                            LN2                            STREET 1
          Show rows that belongs to a selected (super)type and all its subtypes:
          SELECT user_id, first_name, last_name,
                 TREAT (address AS address_sub_t).street address_street
            FROM users_tab
          WHERE address IS OF (address_t)
          ORDER BY user_id;

             USER_ID FIRST_NAME                     LAST_NAME                      ADDRESS_STREET
                   1 FN1                            LN1
                   2 FN2                            LN2                            STREET 1
          Show rows that belongs to selected (super)type only:
          SELECT user_id, first_name, last_name,
                 TREAT (address AS address_sub_t).street address_street
            FROM users_tab
          WHERE address IS OF (ONLY address_t)
          ORDER BY user_id;

             USER_ID FIRST_NAME                     LAST_NAME                      ADDRESS_STREET
                   1 FN1                            LN1
          And now UPDATE.
          We can update the whole object column:
          UPDATE users_tab
             SET address = address_sub_t ('S2', 'C2', 'PC2', 'STREET 2')
          WHERE user_id = 2;
          We can update hidden columns that corresponds to attributes of the root type (address_t):
          UPDATE users_tab u
             SET u.address.state = 'STATE 2'
          WHERE user_id = 2;
          But, we can't (directly) update hidden columns that corresponds to attributes of a subtype (address_sub_t):
          UPDATE users_tab u
             SET u.address.street = 'STREET 2'
          WHERE user_id = 2;

             SET u.address.street = 'STREET 2'
                 *
          ERROR at line 2:
          ORA-00904: "U"."ADDRESS"."STREET": invalid identifier
          or
          UPDATE users_tab
             SET TREAT (addres AS address_sub_t).street = 'STREET 2'
          WHERE user_id = 2;

             SET TREAT (addres AS address_sub_t).street = 'STREET 2'
                       *
          ERROR at line 2:
          ORA-00927: missing equal sign
          This works - UPDATE inline wiew:
          UPDATE (SELECT TREAT (address AS address_sub_t).street address_street
                    FROM users_tab
                   WHERE user_id = 2
                 )
             SET address_street = 'STREET 2';
          or
          UPDATE (SELECT user_id,
                         TREAT (address AS address_sub_t).street address_street
                    FROM users_tab
                 )
             SET address_street = 'STREET 2'
          WHERE user_id = 2;