Object relational databases salman azhar
This presentation is the property of its rightful owner.
Sponsored Links
1 / 40

Object-Relational Databases Salman Azhar PowerPoint PPT Presentation


  • 61 Views
  • Uploaded on
  • Presentation posted in: General

Object-Relational Databases Salman Azhar. User-Defined Types Object IDs Nested Tables. These slides use some figures, definitions, and explanations from Elmasri-Navathe’s Fundamentals of Database Systems and Molina-Ullman-Widom’s Database Systems. Merging Relational and Object Models.

Download Presentation

Object-Relational Databases Salman Azhar

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Object relational databases salman azhar

Object-Relational DatabasesSalman Azhar

User-Defined Types

Object IDs

Nested Tables

These slides use some figures, definitions, and explanations from Elmasri-Navathe’s Fundamentals of Database Systemsand Molina-Ullman-Widom’s Database Systems

Database Systems: Salman Azhar


Merging relational and object models

Merging Relational and Object Models

  • Object-oriented models support

    • interesting data types --- not just flat files.

      • E.g., Maps, multimedia, etc.

  • The relational model supports

    • very-high-level queries

  • Object-relational databases are an attempt to get the best of both.

Database Systems: Salman Azhar


Evolution of dbmss

Evolution of DBMSs

  • Object-oriented DBMSs fell short

    • because they did not offer the efficiencies of a relational DBMS.

  • Object-relational extensions to relational DBMSs

    • capture much of the advantages of OO

    • yet retain the relation as the fundamental abstraction.

Database Systems: Salman Azhar


Sql 99 and dbms features

SQL-99 and DBMS Features

  • SQL-99 includes many of the object-relational features to be described.

  • However, being so new, different DBMSs use different approaches.

    • We’ll sometimes use features and syntax from Oracle and SQL Server.

Database Systems: Salman Azhar


User defined types

User Defined Types

  • A user-defined type, or UDT, is essentially a class definition, with a structure and methods.

  • Two uses:

    • As a row-type, that is, the type of a relation.

    • As an column-type, that is, the type if attribute in a relation.

Database Systems: Salman Azhar


Udt definition

UDT Definition

CREATE TYPE <typename> AS (

<list of elements, as in CREATE TABLE>

);

  • Oracle syntax:

    • Add “OBJECT” as in

      CREATE TYPE <name> AS OBJECT.

    • Follow with / to have the type stored.

Database Systems: Salman Azhar


Example udt definition

Example: UDT Definition

CREATE TYPE DealerType AS (

name CHAR(20),

addrCHAR(20)

);

CREATE TYPE CarType AS (

nameCHAR(20),

manfCHAR(20)

);

Database Systems: Salman Azhar


References

References

  • If T is a type, then REF T is the type of a reference to T

    • this means a pointer to an object of type T.

    • often called an “object ID” in OO systems.

  • Unlike object ID’s, a REF is visible,

    • although it is usually gibberish.

Database Systems: Salman Azhar


Example ref

Example: REF

CREATE TYPE SellsType AS (

dealerREF DealerType,

carREF CarType,

priceFLOAT

);

  • SellsType objects look like:

    REF DealerType REF CarType FLOAT

30000

Reference to a

DealerType object

Reference to a

CarType object

Database Systems: Salman Azhar


Udts as row types

UDTs as Row Types

  • A table may be defined to have a schema that is a row type, rather than by listing its elements.

  • Syntax:

    • CREATE TABLE <table name> OF <type name>;

  • Creates a table where each row is of <type name>

Database Systems: Salman Azhar


Example creating tables

Example: Creating Tables

CREATE TABLE Dealer OF DealerType;

CREATE TABLE Car OF CarType;

CREATE TABLE Sells OF SellsType;

Database Systems: Salman Azhar


Values of relations with a row type

Values of Relations with a Row-type

  • Technically, a relation declared to have a rowtype DealerType (such as Dealer),

    • is not a set of pairs

    • it is a unary relation

      • whose tuples are objects with two components:

        • name and addr.

Database Systems: Salman Azhar


Type constructors

Type Constructors

  • Each UDT has a type constructor of the same name that wraps objects of that type.

Database Systems: Salman Azhar


Example type constructor

Example: Type Constructor

  • The query

    SELECT * FROM Dealer;

  • Produces “tuples” such as:

    DealerType(‘AutoNation’, ‘Maple St.’)

Database Systems: Salman Azhar


Aliasing accessing values from a rowtype

Aliasing:Accessing Values From a Rowtype

  • In Oracle and MS SQL Server, the dot works as expected

    • but it may not work in all DBMS

    • so it is a good idea, to use an alias for every relation, when O-R features are used.

  • Example:

    SELECT dd.name, dd.addr

    FROM Dealer dd;

Database Systems: Salman Azhar


Accessing values sql 99 approach

Accessing Values: SQL-99 Approach

  • In SQL-99, each attribute of a UDT has:

    • Get method

      • Called Generator methods (get the value) and

    • Set method

      • Called Mutator methods (change the value)

  • These methods have the same name as the attribute.

    • The generator for A takes no argument, as A( ).

    • The mutator for A takes a new value as argument, as A(v).

Database Systems: Salman Azhar


Example sql 99 value access

Example: SQL-99 Value Access

  • Consider

    SELECT dd.name, dd.addr

    FROM Dealer dd;

  • The same query in SQL-99 is

    SELECT dd.name( ), dd.addr( )FROM Dealer dd;

Explicitly access generators

Database Systems: Salman Azhar


Inserting row type values oracle style

Inserting Row Type Values(Oracle Style)

  • We can use a standard INSERT statement,

    • remembering that a relation with a row type is really unary and needs that type constructor.

  • Example:

    INSERT INTO Dealer VALUES(

    DealerType(‘AutoNation’, ‘Maple St.’)

    );

  • Note: DealerType is a constructor

Database Systems: Salman Azhar


Inserting values sql 99 style

Inserting Values: SQL-99 Style

  • Create a variable X of the suitable type,

    using the constructor method for that type.

  • Use the mutator methods for the attributes

    to set the values of the fields of X.

  • Insert X into the relation.

Database Systems: Salman Azhar


Example sql 99 insert

Example: SQL-99 Insert

  • The following must be part of a procedure, so we have a variable newDealer.

    SET newDealer = DealerType( );

    newDealer.name(‘AutoNation’);

    newDealer.addr(‘Maple St.’);

    INSERT INTO Dealer VALUES(newDealer);

Construct a

new dealer

Mutator

methods

change

newDealer’s

name and addr

components.

Insert new

values

Database Systems: Salman Azhar


Udts as column types

UDTs as Column Types

  • A UDT can be the type of a column (an attribute)

    • in either another UDT definition

    • or in a CREATE TABLE statement

  • Use the name of the UDT as the type of the attribute

Database Systems: Salman Azhar


Example column type

Values of addr and

favCar components

are objects with 3 and

2 fields, respectively.

Example: Column Type

CREATE TYPE AddrType AS (

streetCHAR(30),

cityCHAR(20),

zipINT

);

CREATE TABLE Buyers (

nameCHAR(30),

addrAddrType,

favCarCarType

);

Database Systems: Salman Azhar


Oracle problem with field access

Oracle Problem With Field Access

  • You can access a field F of an object that is the value of an attribute A by A.F .

  • However, you must use an alias, say tt, for the table T with attribute A, as tt.A.F .

Database Systems: Salman Azhar


Example field access in oracle

Example: Field Access in Oracle

  • Wrong (can’t have nothing):

    SELECT favCar.name

    FROM Buyers;

  • Wrong (can’t have the table name):

    SELECT Buyers.favCar.name

    FROM Buyers;

  • Right (must have alias name):

    SELECT b.favCar.name

    FROM Buyers b;

Database Systems: Salman Azhar


Following references refs

Following References (REFs)

  • AB denotes the value of the B component of the object pointed to by A

  • AB makes sense if:

    • A is of type REF T

    • B is an attribute (component) of objects of type T

Database Systems: Salman Azhar


Following ref s oracle style

Following REF’s: Oracle Style

  • REF-following is implicit in the dot

  • Just follow a REF by a dot and a field of the object referred to

  • Example:

    SELECT ss.car.name

    FROM Sells ss

    WHERE ss.dealer.name = ‘AutoNation’;

Field name inobject car intable aliases as ss

Database Systems: Salman Azhar


Oracle s deref operator motivation

Oracle’s DEREF Operator -- Motivation

  • If we want the set of car objects for the cars sold by AutoNAtion, we might try:

    SELECT ss.car

    FROM Sells ss

    WHERE ss.dealer.name = ‘AutoNation’;

  • Legal, but ss.car is a REF, hence gibberish!

    • Need to add DEREF

object car (ref) intable aliases as ss

Database Systems: Salman Azhar


Using deref

Using DEREF

  • To see the CarType objects, use:

    SELECT DEREF(ss.car)

    FROM Sells ss

    WHERE ss.dealer.name = ‘AutoNation’;

  • Produces values like:

    CarType(‘MiniCooper’, ‘BMW’)

Database Systems: Salman Azhar


Methods

Methods

  • Classes are more than structures; they may have methods.

    • Declare in CREATE TYPE

    • Define methods in a CREATE TYPE BODY statement

    • Use T-SQL syntax for methods.

      • (T-SQL: MS SQL Server = PL/SQL: Oracle)

    • Variable SELF refers to the object to which the method is applied (this)

Database Systems: Salman Azhar


Method definition

Method Definition

  • Form of create-body statement:

    CREATE TYPE BODY <type name> AS

    PROCEDURE

    methodName(arg <ARGTYPE>) RETURN <RETURNTYPE>

    BEGIN

    END;

    END;

Database Systems: Salman Azhar


Method definition oracle style

Method Definition – Oracle Style

Method definitions are called PL/SQL procedure definitions in Oracle

Oracle uses “MEMBER FUNCTION”

in place of “PROCEDURE”

Database Systems: Salman Azhar


Example method definition

Example: Method Definition

(parenthesis only if an argument is passed)

CREATE TYPE BODY SellsType AS

MEMBER FUNCTION

priceConvert(rate FLOAT) RETURN FLOAT IS

BEGIN

RETURN rate * SELF.price;

END;

END;

Argument & argument type

Sorta like “this” keyword in C++/Java

Database Systems: Salman Azhar


Method use

Method Use

  • objectName.methodName(arguments if any)

  • Example:

    SELECT ss.car.name, ss.priceConvert(1.33)

    FROM Sells ss

    WHERE ss.dealer.name = ‘AutoNation’;

Database Systems: Salman Azhar


Example nested table type

Example: Nested Table Type

CREATE TYPE CarType AS OBJECT (

nameCHAR(20),

editionCHAR(20)

);

GO

CREATE TYPE CarTableType AS

TABLE OF CarType;

GO

Database Systems: Salman Azhar


Example continued

Example --- Continued

  • Use CarTableType in a Manfs relation that stores the set of cars by each manufacturer in one tuple for that manufacturer.

    CREATE TABLE Manfs (

    nameCHAR(30),

    addrCHAR(50),

    carcarTableType

    );

Database Systems: Salman Azhar


Nested tables

Nested Tables

  • Allows values of row components to be whole relations

  • If T is a UDT,

    • we can create a type S whose values are relations with rowtype T, by:

      CREATE TYPE S AS TABLE OF T ;

Database Systems: Salman Azhar


Querying a nested table

Querying a Nested Table

  • We can print the value of a nested table like any other value

  • But these values have two type constructors:

    • For the table

    • For the type of tuples in the table

Database Systems: Salman Azhar


Example query a nested table

Example: Query a Nested Table

  • Find the cars by Toyota:

    SELECT car FROM Manfs

    WHERE name = ‘Toyota’;

  • Produces one value:

    CarTableType(

    CarType(‘Camry’, ‘LE’)

    CarType(‘Corolla’, ‘L’)

    ….

    )

Database Systems: Salman Azhar


Querying within a nested table

Querying Within a Nested Table

  • A nested table can be converted to an ordinary relation by applying THE(…)

  • This relation can be used in FROM clauses like any other relation

Database Systems: Salman Azhar


Example use of the

Example: Use of THE

  • Find the cars made by Toyota:

    SELECT dd.edition

    FROM THE(

    SELECT car FROM Manfs

    WHERE name = ‘Toyota’;

    ) dd

    WHERE dd.name = ‘Camry’;

Database Systems: Salman Azhar


  • Login