lecture 8- OOD Overloading.pptx

CSC-468 Advance Database

Lecture 08

Object Oriented Database

Overloading

Aniqa Naeem

TEXT BOOKS FOR DATA WAREHOUSING

2. “Database Systems: Models, Languages , Design and Application Programming” By Ramez Elmasri and Shamkant B. Navathe – 6th Edition”

3. “Advance Data Management” By Lena Wiese

REFERENCE BOOKS FOR DATA WAREHOUSING

4. “Data Warehousing (Design, Development and Best Practices)” By Soumendra Mohanty

5. “Mastering Data Warehouse Design” By Claudia Imhoff, Nicolas Galemmo, Jonathan G. Geiger

Books In Library

•

“Fundamentals of Database

Systems", 7th Ed, by Ramez Elmasri

and Shamkant B. Navathe. 2017

•

“Database System: A Practical

Approach to design,

Implementation and

Management” , 4

Ed, by Thomas

Last lecture

• Object databases store objects rather than data such as integers, strings or real numbers.

• Objects basically consist of the following:

 Class: collection of similar objects with shared attributes and methods

 Attributes: properties of objects

 Method: class procedures representing actions: finding, changing, printing

• In PL/SQL, methods can defined as subprograms or packages.

• Subprogram are of two types: 1. Procedures. (perform action)

2. Function. (computes value)

• _{Packages are collection of different subprograms.} Package created in two parts:

Outline

•

What is overloading?

•

Forward Declaration

•

Implementation of ODBMS using PL/SQ

•

Instantiating an Object

– _{Member Methods} – _{Comparison Methods}

– _{Implementing MAP Method} – _{Implementing Order Method}

Chapter 22– Chapter 14 in 2016 book

Book: Database System: A Practical Approach to design, Implementation and

Management” , 4th _{Ed, by Thomas Connolly}

and Carolyn Begg

(package specification)

(package body)

10

Overloading Example

•

If you call ADD_DEPT with an explicitly provided

department ID, PL/SQL uses the first version of the

procedure.

•

If you call

ADD_DEPT with no department

ID, PL/SQL uses the second version.

12

FORWARD DECLARATIONS

•

PL/SQL does not allow forward references.

•

You

must

declare an identifier before using

it.

Therefore, a subprogram must be declared before

calling it.

•

In the example in the slide, the procedure

SOLUTIONS

• _{You can solve the illegal reference problem by reversing the order of}

the two procedures.

• However, this easy solution does not always work. Suppose the procedures call each other or you absolutely want to define them in alphabetical order.

Solution-2

• Declare the procedure or function in the package body before it can be referenced by any other procedure or function in the package.

• The formal parameter list must appear in both the forward declaration and the subprogram body.

• _{The subprogram body can appear anywhere after the forward}

Implementation of ODBMS using PL/SQL

•

PL/SQL allows defining an

object type

, which helps in

designing

object-oriented database

in Oracle.

•

An

object type

allows you to create

composite types

.

•

Using objects allow you to implement real world objects

with specific structure of

Data

and

Methods

for operating it.

•

Objects have

Attributes

and

Methods.

•

Attributes

are properties of an object and are used for

storing

an object's

state; and

Methods

are used for

modelling its

Type Object

• Most object-oriented programming languages refer to an object’s definition as its Class.

• SQL (and accordingly PL/SQL) refers to an object’s definition as its TYPE.

• ATYPE has attributes and methods,which are analogous to columns in a table and functions or procedures in a package.

• _{TYPEs are defined using CREATE TYPE type_name…AS}

OBJECT.

• TYPEs are implemented using CREATE TYPE BODY type_name AS…, that is, if they have methods (functions or procedures).

• A TYPE is sometimes referred to as an Abstract Data Type (ADT) or

Objects are created using the

CREATE [OR REPLACE]

TYPE

statement. Following are examples to create

objects

ADDRESS

and

CUSTOMER

consisting of few

attributes:

Example-1:

CREATE OR REPLACE

TYPE

address

AS OBJECT

(house_no varchar2(10), street varchar2(30),

19

Example-2:

CREATE OR REPLACE TYPE customer AS OBJECT

(code number(5),

name varchar2(30),

contact_no varchar2(12),

addr address,

MEMBER PROCEDURE display);

where

ADDR is an attribute of object type ADDRESS defined in Example-1. DISPLAY is a method for the object

20

Example-3:

Step-1:

create an object type ADDRESS

create or replace type address

as object

(home_nonumber,

street_no number,

area varchar2(25),

city varchar2(10));

Step-2

: create a table using object type ADDRESS

create table employee

(empno number, ename

21

Step-3: insert a record into the table EMPLOYEE

insert into employee

values(101,'A',address(100,35,'Gulshan','Karachi'));

Step-4: Selection of records from the EMPLOYEE table

select * from employee;

select addr from employee;

declare

residence address; -- instance of an object Begin

Declare

a

employee%rowtype;

begin

Select * into

a

from

employee;

dbms_output.put_line(a.addr.h

ome_no);

end;

Instantiating an Object

• Defining an object type provides a blueprint for the object. To use this object, you need to create instances of this object.

• You can access the attributes and methods of the object using the instance name and the access operator (.) as follows :

Example: DECLARE

residence address; BEGIN

residence := address('103A', ‘M.A.Jinnah', ‘Khi', ‘Sindh',‘76900'); dbms_output.put_line('House No: '|| residence.house_no);

dbms_output.put_line('Street: '|| residence.street); dbms_output.put_line('City: '|| residence.city);

dbms_output.put_line('State: '|| residence.state); End;

Member Methods

• _{Member methods are used for manipulating the attributes of}

the object.

• _{You provide the declaration of a member method while}

declaring the object type.

• The object body defines the code for the member methods.

• The object body is created using the CREATE TYPE BODY

statement.

• _{Constructors are functions that return a new object as its value.} • _{Every object has a system defined constructor method.}

• _{The name of the constructor is same as the object type.}

Example-1

DECLARE

BEGIN

residence :=

address('103A',

'M.G.Road', 'Jaipur',

'Rajasthan','201301')

;

END;

25

Example-2

Step-1:

Create an object type ADDRESS with a member

procedure

create or replace type ADDRESS as

object (

home_no number, street_no number, area varchar2(25), city varchar2(10),

member procedure INSERTION );

Step-2:

Create a table EMPLOYEE using object

type ADDRESS

.

create table EMPLOYEE

(empno number,

ename

varchar2(10),

addr ADDRESS);

Step-3: Define a member procedure

create or replace type body ADDRESS as

Step-4: create an instance B of object type ADDRESS and calling its member procedure INSERTION

DECLARE

b address ; BEGIN

b:= address(103,45,'saddar’,'khi'); b.insertion;

END;

Step-5: verify the record insertion into the

EMPLOYEE table.

Select * from EMPLOYEE;

•

The

comparison methods

are used for

comparing objects.

•

There are two ways to compare objects :

1. MAP Method

2. Order Method

COMPARISON METHODS

1. Map Method

• The MAP member functions are used for performing

comparisons between a single attribute of an object instance to a single attribute of another object instance.

• The MAP functions do not accept any formal parameters and

must return a scalar data type, either CHAR, VARCHAR2, NUMBER or DATE, which can be easily compared by the system.

• The MAP member functions are used for validating object

instances with a single attribute.

• The Map method is a function implemented in such a way that

For example,

• for a CUSTOMER object, if the customer code is same for two customers, both customers could be the same. So the relationship between these two objects would depend upon the value of code.

2. Order Method

• Unlike the MAP member function, the ORDER member function

allows us to compare every attribute from an object instance with an another object instance.

• An ORDER function has a single actual parameter with the object’s instance as its data type and compares the input instance with the current instance of the object attribute by attribute. The ORDER method must return a Number data type and the values are restricted between -1, 0, 1 and Null.

• The Order method implements some internal logic for

comparing two objects.

• _{For example, for a RECTANGLE object, a rectangle is bigger than}

Implementing MAP Method

34

 First create TYPE.

 _{Then create TYPE BODY.}

Example:

CREATE OR REPLACE TYPE BODY rectangle AS

MEMBER FUNCTION enlarge(inc number) RETURN rectangle IS BEGIN

return rectangle(self.length + inc, self.width + inc); // constructor

END enlarge;

MEMBER PROCEDURE display IS BEGIN

dbms_output.put_line('Length: '|| length);

dbms_output.put_line('Width: '|| width); END display;

MAP MEMBER FUNCTION measure RETURN number IS BEGIN

return (sqrt(length*length + width*width)); END measure;

END;

Now using the rectangle object and its member functions −

DECLARE

r1 rectangle; r2 rectangle; r3 rectangle;

inc_factor number := 5; BEGIN r1 := rectangle(3, 4);

r2 := rectangle(5, 7);

r3 := r1.enlarge(inc_factor); r3.display; -- calling measure function

IF (r1 > r2) THEN r1.display; ELSE r2.display

When the above code is executed at the SQL prompt, it produces the following result –

Length: 8 Width: 9

Length: 5 Width: 7

Now, the same effect could be achieved using an order method. Let us recreate the rectangle object using an Order Method −

CREATE OR REPLACE TYPE rectangle AS OBJECT (length number, width number, member procedure display, order member function measure(r rectangle) return number ); /

Implementing Order Method

Creating the type body −

CREATE OR REPLACE TYPE BODY rectangle AS

MEMBER PROCEDURE display IS BEGIN dbms _outp ut.put _line ('Len gth: '|| lengt h);

dbms_output.put_line('Width: '|| width); END display;

ORDER MEMBER FUNCTION measure(r rectangle) return number IS

BEGIN

IF(sqrt(self.length*self.length + self.width*self.width) > sqrt(r.length*r.length + r.width*r.width)) then

return(1); ELSE

return(-1); END IF;

END measure;

END;

Using the rectangle object and its member functions − DECLARE

r1 rectangle; r2 rectangle;

BEGIN

r1 := rectangle(23, 44); r2 := rectangle(15, 17); r1.display;

r2.display;

IF (r1 > r2) THEN -- calling measure function r1.display; ELSE

r2.display; END IF; END;

When the above code is executed at the SQL prompt, it produces the following result −

Length: 23 Width: 44

Length: 15 Width: 17

Length: 23 Width: 44