ADBMS-lec 7- Object oriented Database.pptx

(1)

CSC-468 Advance Database

Lecture 07

Object Oriented Database

Aniqa Naeem

(2)

TEXT BOOKS FOR DATA WAREHOUSING

1. “Building the Data Warehouse” By Inmon

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

(3)

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

th

Ed, by Thomas

(4)

Last lecture

•

Types of Transparencies:

Data Independence

or Data Transparency

(Logical Data Independence & Physical Data Independence),

Network Transparency

or Distribution Transparency,

Replication

Transparency

,

Fragmentation Transparency

.

•

A DDBMS may be classified as

Homogeneous

and

Heterogeneous

•

Fundamental

Factors for design

of DDBMS:

Fragmentation

,

Allocation

and

Replication.

•

Types of Fragmentation:

(5)

Outline

•

Define OO Model and OODB

•

Implementation of ODBMS using PL/SQL

•

Introduction to Subprogram

•

Development of Packages

(6)

Chapter 26- Chapter 12 in 2016 book

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

(7)

Object-Oriented Model (OO Model)

• Complex real-world problems needed a data model that more closely represents the real world.

• Both data and their relationships are contained in a single structure called object.

• _{Said to be a}_{semantic data model} _{because semantic indicates meaning.} • Based on following components:

 Object: one occurrence of an entity

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

 Attributes: properties of objects

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

 Class Hierarchy: upside-down tree, each class has one parent. Example: CUSTOMER and EMPLOYEE share a parent PERSON class.

 Inheritance: ability of object within class hierarchy to inherit

(8)

Example

• _{A real-life parallel to objects is a car engine. It is composed of several}

parts: the main cylinder block, the exhaust system, intake manifold and so on. Each of these is a standalone component; but when

machined and bolted into one object, they are now collectively referred to as an engine.

• _{Similarly, when programming one can define several components,}

such as a vertical line intersecting a perpendicular horizontal line

while both lines have a graded measurement. This object can then be collectively labeled a graph. When utilizing the ability to plot

components, there is no need to first define a graph; but rather the instance of the created graph can be called.

(9)

Object Oriented Databases:

•

Object oriented databases are also called

Object Database

Management Systems (ODBMS).

•

Object databases store

objects

rather than data such as

integers,

strings or real numbers.

•

Objects are used in object oriented languages such as C++,

Java, and others. Objects basically consist of the following:

–

Attributes

-

Attributes are data which defines the

characteristics

of an object. This data may be simple such as integers, strings,

and real numbers or it may be a reference to a complex object.

–

Methods

-

Methods define the

behavior

of an object and are

what was formally called

Procedures

or

Functions.

(10)

•

Classes

are used in object oriented programming to define the

data and methods

the object will contain.

•

The class is like a

template

to the object.

•

The class

does not itself contain data or methods

but defines

the data and methods contained in the object.

•

The class is used to

create (instantiate) the object

.

•

Classes may be used in object databases to

recreate par

ts of the

object that may

not actually be stored

in the database.

(11)

Implementation of ODBMS using PL/SQL

•

In PL/SQL, methods can defined as subprograms

or packages.

•

Subprogram are of two types:

1. Procedures.

2. Function.

•

Packages are collection of

different subprograms.

(12)

(13)

(14)

(15)

Subprograms

• Subprograms are named PL/SQL blocks that can accept parameters and be invoked from a calling environment. Types of Subprograms

•PL/SQL has two types of subprograms, procedures and functions.

Subprogram Specification

• The header is relevant for named blocks only and determines the way that the program unit is called or invoked.

• The header determines:

– The PL/SQL subprogram type, that is, either a procedure or a function

– The name of the subprogram

– The parameter list, if one exists

– The RETURN clause, which applies only to functions

(16)

Subprogram Body

The declaration section of the block between IS|AS and BEGIN.

• The keyword DECLARE that is used to indicate the start of the declaration section in anonymous blocks is not used here.

• The executable section between the BEGIN and END keywords is mandatory, enclosing the body of actions to be performed.

• _{There must be at least one statement existing in the executable}

section. There should be at least one NULL; statement, which is considered an executable statement.

(17)

//cursor base record

Example:

CREATE OR REPLACE PROCEDURE display IS

cursor employee is select ename,deptno,sal from emp

where deptno = 10;

emp_rec employee%rowtype;

BEGIN

open employee; LOOP

fetch employee into emp_rec; exit when employee%notfound; dbms_output.put_line(emp_rec.ename || ' '

(18)

(19)

(20)

Package Development

•

You create a package in two parts:

–

First the

package specification

–

Second the

package body

.

•

Public package constructs

are those that are declared in

the package specification and defined in the package

body.

(21)

SCOPE OF

THE

CONSTRUCT

DESCRIPTION

PLACEMENT WITHIN THE

PACKAGE

PUBLIC

Can be referenced from

any oracle server

environment.

Declared within the package

specification and may be

defined within the package

body.

PRIVATE

Can be referenced only

by other constructs

which are part of the

same package.

(22)

(23)

VISIBILITY OF

THE

CONSTRUCT

DESCRIPTION

LOCAL

A variable defined within a subprogram that is not

visible to external users.

Private (local to the package) variable: You can define

variables in a package body. These variables can be

accessed only by other objects in the same package.

They are not visible to any subprograms or objects

outside of the package.

GLOBAL

(24)

(25)

(26)

(27)

(28)

(29)

Creating the Package Body

•

The order in

which subprograms are defined within

the package body is important

.

•

You must declare a

variable before another variable

or subprogram

can refer to it.

•

You must

declare or define private subprograms

before calling

them from other subprograms.

•

It is quite common in the package body to see all

private variables and subprograms defined first and

the public subprograms defined last.

(30)

PARAMETER

DESCRIPTION

package_name

Is the name of the package

private type and

item declarations

Declares variables, constants, cursors,

exceptions, or types

subprogram bodies

Defines the PL/SQL subprograms, public

and private

(31)

Private procedure

(32)

Create a Package Body Example

•

In the slide on previous side:

–

1 is a public (global)

–

2 is a public procedure

–

3 is a private function

•

You can define a private procedure or

function to

modularize and clarify the code of

(33)

•

Note:

In the slide, the private function is shown

above the

public procedure

.

•

When you are coding the package body, the

definition of the

private function has to be above

the definition of the public procedure

.

•

Only subprograms and cursors declarations without

body in a package specification have an

underlying implementation in the package body

.

•

So if a specification declares only types, constants,

variables, exceptions, and call specifications, the

package body is unnecessary.

•

However, the body can still be used to initialize

(34)

(35)

(36)

(37)

(38)

Invoking Package Constructs:

•

When you invoke a package procedure or function from

outside the package, you must qualify its name with the

name of the package.

Example 2

•

Call the RESET_COMM procedure from iSQL*Plus,

making the prevailing commission 0.15 for the user session.

Example 3

•

Call the RESET_COMM procedure that is located in the

SCOTT schema from iSQL*Plus, making the prevailing

commission 0.15 for the user session.

Example 4

(39)

(40)

(41)

(42)

(43)

(44)

(45)

DETAILED EXAMPLE-1 OF PACAKGE

Step-1:

Create a package specification

CREATE OR REPLACE PACKAGE comm_package

IS

v_max_comm number;

/* function validate_comm(p_comm in number)

return boolean; */

END comm_package;

(46)

Step-2:

Create a package body

CREATE OR REPLACE PACKAGE BODY comm_package IS

FUNCTION validate_comm(p_comm in number) RETURN boolean IS

BEGIN

SELECT max(comm) INTO v_max_comm FROM emp; IF p_comm > v_max_comm

(47)

Step-3:

Create a program to call the package function

DECLARE

a boolean;

BEGIN

a:=comm_package.validate_comm(3000);

IF a = true

(48)

NOTE:

• When you execute the program in step-3, you will get the error component 'VALIDATE_COMM' must be declared.

REASON:

• _{Declaration of the}_{validate_comm( )}_{function in}_step-1_{had made} comment so it is not declared in the specification part of the package, hence the scope of the function is PRIVATE so it cannot be executed by any other program outside the package.

REMEDY:

(49)

DETAILED EXAMPLE-2 OF PACAKGE

PACKAGE SPECIFICATION

CREATE OR REPLACE PACKAGE comm_package IS

g_comm NUMBER := 0.10;

--initialized to 0.10

PROCEDURE reset_comm(p_comm IN NUMBER);

END comm_package;

/

(50)

PACKAGE BODY

CREATE OR REPLACE PACKAGE BODY comm_package IS

FUNCTION validate_comm (p_comm IN NUMBER) RETURN BOOLEAN IS

v_max_comm NUMBER; BEGIN

SELECT MAX(commission_pct)

INTO v_max_comm FROM employees;

IF p_comm > v_max_comm THEN RETURN(FALSE); ELSE RETURN(TRUE);

END IF;

END validate_comm;

PROCEDURE reset_comm (p_comm IN NUMBER) IS

BEGIN

IF validate_comm(p_comm)

THEN g_comm:=p_comm; --reset global variable ELSE

(51)