Object-Oriented, Intelligent and Object-Relational Database Models

1
Object-Oriented, Intelligent and
Object-Relational Database Models

• University of California, Berkeley
• School of Information Management and Systems
• SIMS 257 Database Management

2
Review

• OLAP
• Data Mining

3
OLAP

• Online Line Analytical Processing
• Intended to provide multidimensional views of the
  data
• I.e., the Data Cube
• The PivotTables in MS Excel are examples of OLAP
  tools

4
Data Cube
5
Operations on Data Cubes

• Slicing the cube
• Extracts a 2d table from the multidimensional
  data cube
• Example
• Drill-Down
• Analyzing a given set of data at a finer level of
  detail

6
Data Mining

• Data mining is knowledge discovery rather than
  question answering
• May have no pre-formulated questions
• Derived from
• Traditional Statistics
• Artificial intelligence
• Computer graphics (visualization)

7
Goals of Data Mining

• Explanatory
• Explain some observed event or situation
• Why have the sales of SUVs increased in
  California but not in Oregon?
• Confirmatory
• To confirm a hypothesis
• Whether 2-income families are more likely to buy
  family medical coverage
• Exploratory
• To analyze data for new or unexpected
  relationships
• What spending patterns seem to indicate credit
  card fraud?

8
Data Mining Applications

• Profiling Populations
• Analysis of business trends
• Target marketing
• Usage Analysis
• Campaign effectiveness
• Product affinity

9
Data Mining Algorithms

• Market Basket Analysis
• Memory-based reasoning
• Cluster detection
• Link analysis
• Decision trees and rule induction algorithms
• Neural Networks
• Genetic algorithms

10
Today

• Object-Oriented Database Systems
• Inverted File and Flat File DBMS
• Object-Relational DBMS
• OR features in Oracle

11
Object-Oriented DBMSBasic Concepts

• Each real-world entity is modeled by an object.
  Each object is associated with a unique
  identifier (sometimes call the object ID or OID)

12
Object-Oriented DBMSBasic Concepts

• Each object has a set of instance attributes (or
  instance variables) and methods.
• The value of an attribute can be an object or set
  of objects. Thus complex object can be
  constructed from aggregations of other objects.
• The set of attributes of the object and the set
  of methods represent the object structure and
  behavior, respectively

13
Object-Oriented DBMSBasic Concepts

• The attribute values of an object represent the
  objects status.
• Status is accessed or modified by sending
  messages to the object to invoke the
  corresponding methods

14
Object-Oriented DBMSBasic Concepts

• Objects sharing the same structure and behavior
  are grouped into classes.
• A class represents a template for a set of
  similar objects.
• Each object is an instance of some class.

15
Object-Oriented DBMSBasic Concepts

• A class can be defined as a specialization of of
  one or more classes.
• A class defined as a specialization is called a
  subclass and inherits attributes and methods from
  its superclass(es).

16
Object-Oriented DBMSBasic Concepts

• An OODBMS is a DBMS that directly supports a
  model based on the object-oriented paradigm.
• Like any DBMS it must provide persistent storage
  for objects and their descriptions (schema).
• The system must also provide a language for
  schema definition and and for manipulation of
  objects and their schema
• It will usually include a query language,
  indexing capabilities, etc.

17
Generalization Hierarchy
18
Inverted File DBMS

• Usually similar to Hierarchic DBMS in record
  structure
• Support for repeating groups of fields and
  multiple value fields
• All access is via inverted file indexes to DBS
  specified fields.
• Examples ADABAS DBMS from Software AG -- used in
  the MELVYL system

19
Flat File DBMS

• Data is stored as a simple file of records.
• Records usually have a simple structure
• May support indexing of fields in the records.
• May also support scanning of the data
• No mechanisms for relating data between files.
• Usually easy to use and simple to set up

20
Intelligent Database Systems

• Intelligent DBS are intended to handle more than
  just data, and may be used in tasks involving
  large amounts of information where analysis and
  discovery are needed.

The following is based on Intelligent Databases
by Kamran Parsaye, Mark Chignell, Setrag
Khoshafian and Harry Wong AI Expert, March 1990,
v. 5 no. 3. Pp 38-47
21
Intelligent Database Systems

• They represent the evolution and merging of
  several technologies
• Automatic Information Discovery
• Hypermedia
• Object Orientation
• Expert Systems
• Conventional DBMS

22
Intelligent Database Systems
Automatic discovery
Expert Systems
Intelligent Databases
Hypermedia
Object Orientation
Traditional Databases
23
Intelligent Database Architecture
High-Level Tools
High-Level User Interface
Intelligent Database Engine
24
Environment Components
Flexible queries
Error detection
Data Dictionary
Automatic Discovery
Concept Dictionary
25
Intelligent Databases

• Data Dictionary contains the system metadata
• Concept Dictionary defines virtual fields based
  on approximate definitions
• Data Analysis and discovery
• Find patterns
• detect errors
• Process queries

26
Intelligent Databases

• Automatic Discovery
• Data comprehension
• Form Hypotheses
• Make queries
• View results and perhaps modify hypotheses
• Repeat

27
Intelligent Databases

• Automatic Error Detection
• Integrity Constraints
• Rule systems
• Analysis of data for anomalies

28
Intelligent Databases

• Flexible Query Processing
• Approximate and fuzzy queries
• SELECT NAME, AGE, TELEPHONE FROM PERSONEL WHERE
  NAME Dovid Smith and AGE IS-CLOSE-TO 19
• confidence factors
• Ranked query results

29
Intelligent Databases

• Intelligent User Interfaces
• Hyperlinked data in the data/knowledge base
• Multimedia presentations
• Dynamic linking of related information

30
Intelligent Databases

• Intelligent Database Engine
• OO support
• Inference features
• Global optimization
• Rule manager
• Explanation manager
• Transaction manager
• Metadata manager
• Access module
• Multimedia manager

31
Object Relational Databases

• Background
• Object Definitions
• inheritance
• User-defined datatypes
• User-defined functions

32
Object Relational Databases

• Began with UniSQL/X unified object-oriented and
  relational system
• Some systems (like OpenODB from HP) were Object
  systems built on top of Relational databases.
• Miro/Montage/Illustra built on Postgres.
• Informix Buys Illustra. (DataBlades)
• Oracle Hires away Informix Programmers.
  (Cartridges)

33
Object Relational Data Model

• Class, instance, attribute, method, and integrity
  constraints
• OID per instance
• Encapsulation
• Multiple inheritance hierarchy of classes
• Class references via OID object references
• Set-Valued attributes
• Abstract Data Types

34
Object Relational Extended SQL (Illustra)

• CREATE TABLE tablename OF TYPE TypenameOF NEW
  TYPE typename (attr1 type1, attr2 type2,,attrn
  typen) UNDER parent_table_name
• CREATE TYPE typename (attribute_name type_desc,
  attribute2 type2, , attrn typen)
• CREATE FUNCTION functionname (type_name,
  type_name) RETURNS type_name AS sql_statement

35
Object-Relational SQL in ORACLE

• CREATE (OR REPLACE) TYPE typename AS OBJECT
  (attr_name, attr_type, )
• CREATE TABLE OF typename

36
Example

• CREATE TYPE ANIMAL_TY AS OBJECT (Breed
  VARCHAR2(25), Name VARCHAR2(25), Birthdate DATE)
• Creates a new type
• CREATE TABLE Animal of Animal_ty
• Creates Object Table

37
Constructor Functions

• INSERT INTO Animal values (ANIMAL_TY(Mule,
  Frances, TO_DATE(01-APR-1997,
  DD-MM-YYYY)))
• Insert a new ANIMAL_TY object into the table

38
Selecting from an Object Table

• Just use the columns in the object
• SELECT Name from Animal

39
More Complex Objects

• CREATE TYPE Address_TY as object (Street
  VARCHAR2(50), City VARCHAR2(25), State CHAR(2),
  zip NUMBER)
• CREATE TYPE Person_TY as object (Name
  VARCHAR2(25), Address ADDRESS_TY)
• CREATE TABLE CUSTOMER (Customer_ID NUMBER, Person
  PERSON_TY)

40
What Does the Table Look like?

• DESCRIBE CUSTOMER
• NAME TYPE
• --------------------------------------------------
  ---
• CUSTOMER_ID NUMBER
• PERSON NAMED TYPE

41
Inserting

• INSERT INTO CUSTOMER VALUES (1, PERSON_TY(John
  Smith, ADDRESS_TY(57 Mt Pleasant St., Finn,
  NH, 111111)))

42
Selecting from Abstract Datatypes

• SELECT Customer_ID from CUSTOMER
• SELECT from CUSTOMER

CUSTOMER_ID PERSON(NAME, ADDRESS(STREET, CITY,
STATE ZIP)) --------------------------------------
--------------------------------------------------
----------- 1
PERSON_TY(JOHN SMITH, ADDRESS_TY(57...
43
Selecting from Abstract Datatypes

• SELECT Customer_id, person.name from Customer
• SELECT Customer_id, person.address.street from
  Customer

44
Updating

• UPDATE Customer SET person.address.city HART
  where person.address.city Briant

45
Functions

• CREATE OR REPLACE FUNCTION funcname (argname
  IN OUT IN OUT datatype ) RETURN datatype
  (IS AS) block external body

46
Example

• Create Function BALANCE_CHECK (Person_name IN
  Varchar2) RETURN NUMBER is BALANCE NUMBER(10,2)
  BEGIN
• SELECT sum(decode(Action, BOUGHT,
  Amount, 0)) - sum(decode(Action, SOLD, amount,
  0)) INTO BALANCE FROM LEDGER where Person
  PERSON_NAME
• RETURN BALANCE
• END

47
Example

• Select NAME, BALANCE_CHECK(NAME) from Worker

48
TRIGGERS

• Create TRIGGER UPDATE_LODGING INSTEAD OF UPDATE
  on WORKER_LODGING for each row BEGIN
• if old.name ltgt new.name then update worker
  set name new.name where name old.name
• end if
• if old.lodging ltgt etc...