Object-Relational Features in Oracle Database1C. ShahabiCyrus ShahabiComputer Science DepartmentUniversity of Southern [email protected] of the Database World2C. ShahabiOracle Database History of Oracle database In 1979, Oracle Version 2 introduced  An early commercial relational database system.… In 1997, Oracle version 8 released  Support for object-oriented development and multimedia applications. Object-Relational DBMS3C. Shahabi In 1999, Oracle 8i released Tuned with the needs of the Internet/Web In 2001, Oracle 9i released  Query-intensive data warehouses, and demanding Internet applications (XML, Text ) In 2003, Oracle 10g released Support for Grid Computing In 2007, Oracle 11g released Automatic memory disk and memory management Extended features (e.g., 3D capabilities)Object-Relational Elements in Oracle 10g Object-Oriented Concepts Objects Methods Object TablesType Inheritance 4C. ShahabiType Inheritance  Collections Object Types and ReferencesObject-Oriented Concepts Abstraction and Encapsulation (Provided by Abstract Data Types (ADT)) Abstraction is the process of identifying the essential aspects of an entity and ignoring the unimportant properties. Focus on what an object is and what it does, rather than how it should be implemented. Encapsulation (or information hiding) provides data independence by separating the external aspects of an object from its internal details, which is hidden from the outside world. 5C. Shahabiis hidden from the outside world.  Classes Classes: A class is a blueprint or prototype from which objects are created. A group of objects with the same attributes and methods. Hence, the attributes and the associated methods are defined once for the class rather than separately for each object. Attributes (or instance variables) describe the current state of an object (the notation for attribute: object-name.attribute-name).Object-Oriented Concepts Methods: define the behavior of the object. They can be used to change the object’s state by modifying its attribute values, or to query the value of the selected attributes. A method consists of a name and a body that performs the behavior associated with the method name (notation: object-name.method-name).Attributes6C. Shahabi The instances of a class are those objects belonging to a class.AttributesAdvantages of ORDBMS Enables reuse and sharing.u Ability to extend the DBMS server to perform standard functionality centrally, rather than have it coded in each application. Example: Embedded Functions, it saves having to define it in each application that needs it.Ability and support for complex objects and rich data types, 7C. ShahabiAbility and support for complex objects and rich data types, termed abstract data types (ADTs)u Complex applications such as Oracle Spatial  Support for Inheritanceu Inherent attributes and behavior of the pre-existing classes, hence ease of definition and programmingOracle Object Types User-Defined data types (classes) Consist of 2 parts: attributes + methodsCREATE TYPE person_type AS OBJECT (name VARCHAR2(30),phone VARCHAR2(20), -- attributes declared.8C. ShahabiMEMBER FUNCTION get_areacode RETURN VARCHAR2 ); -- method/ -- This slash needed to get Oracle process this statement. --Defining an object type does not allocate any storage.--The body of method is defined in a separate CREATE --TYPE BODY statement, written in PL/SQL or any other languages.DROP TYPE person_type;--First drop all tables and other types using person_type.Oracle Objects Definitionu Actual instance of the defined object type, u Storages are allocated to an object and values are assigned to the attributes of an objectCREATE TABLE contacts (9C. ShahabiCREATE TABLE contacts (contact person_type,c_date DATE );-- object type can be used like any other built-in data types.INSERT INTO contacts VALUES (person_type(‘Tommy Trojan’, '‘213-740-1114’), -- instance‘24 Jan 2004’ );-- person_type is instantiated and values are assigned to -- the attributes of the object instance.Oracle Methods Definitionu Functions/procedures declared in the object type definition to implement behavior of the object of that type.u Written in PL/SQL or virtually any other languages (Java, C…)Method types10C. ShahabiMethod typesu Member method Defined on object instance’s data.u Static method Invoked on the object type, not its instances. Can be used to the operations that are global to the type (e.g. initialization)u Constructor method Built-in constructor function, like in C++.Member Method Member methods are used to access an object instance’s values.CREATE OR REPLACE TYPE BODY person_type ASMEMBER FUNCTION get_areacode RETURN VARCHAR2 ISBEGINRETURN SUBSTR(phone, 1, 3);END get_areacode;END; /--Define the body of the method using CREATE OR REPLACE TYPE BODY.11C. Shahabi--Define the body of the method using CREATE OR REPLACE TYPE BODY.SELECT c.contact.get_areacode()FROM contacts c;-- Invoke a member methodC.CONTACT.GET_AREACODE()----------------------------------------------------------------------213Constructor Method Every object type has a constructor method implicitly defined by system. Returns a new instance of the user-defined object type and sets up the values of its attributes. The name of constructor method is the same as the name of the object type.12C. Shahabip = person_type(‘Scott Tiger’, ‘321-123-1234’);--Built-in constructor method, person_type(att1, att2) is invoked --to create a new object instance of person_type, specify values --for its attributes(name, phone), and set the object into a --variable p.INSERT INTO contacts VALUES (person_type(‘Scott Tiger’, ‘321-123-1234’), ’10 Feb 2004’));--Same thing occurs here.Oracle Object Tables Object Table: special type of table, each row represents an objectCREATE TYPE person_type AS OBJECT (name VARCHAR2(30),phone VARCHAR2(20) );/CREATE TABLE person_table OF person_type;INSERT INTO person_tableVALUES (person_type (‘Scott Tiger’, ‘321-123-1234’));13C. ShahabiSELECT VALUE(p) FROM person_table p WHERE p.name = ‘Scott Tiger’;-- Single-column table: each row is a person_type object-- Perform object-oriented operations Comparing to a relational tableCREATE TABLE person_table (name VARCHAR2(30),phone VARCHAR2(20) );INSERT INTO person_tableVALUES (‘Tommy