This discussion is archived
2 Replies Latest reply: Feb 28, 2010 1:19 PM by Zlatko Sirotic RSS

Implementing ORDBMS

725856 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points