Transcript - Courses - University of California, Berkeley

Object-Oriented, Intelligent and
Object-Relational Database
Models
University of California, Berkeley
School of Information Management and
Systems
SIMS 257: Database Management
11/7/2000
Database Management -- R. Larson
Review
• OLAP
• Data Mining
11/7/2000
Database Management -- R. Larson
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
11/7/2000
Database Management -- R. Larson
Data Cube
11/7/2000
Database Management -- R. Larson
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
11/7/2000
Database Management -- R. Larson
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)
11/7/2000
Database Management -- R. Larson
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?
11/7/2000
Database Management -- R. Larson
Data Mining Applications
•
•
•
•
•
•
Profiling Populations
Analysis of business trends
Target marketing
Usage Analysis
Campaign effectiveness
Product affinity
11/7/2000
Database Management -- R. Larson
Data Mining Algorithms
•
•
•
•
•
•
•
Market Basket Analysis
Memory-based reasoning
Cluster detection
Link analysis
Decision trees and rule induction algorithms
Neural Networks
Genetic algorithms
11/7/2000
Database Management -- R. Larson
Today
• Object-Oriented Database Systems
• Inverted File and Flat File DBMS
• Object-Relational DBMS
– OR features in Oracle
11/7/2000
Database Management -- R. Larson
Object-Oriented DBMS
Basic 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)
11/7/2000
Database Management -- R. Larson
Object-Oriented DBMS
Basic 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
11/7/2000
Database Management -- R. Larson
Object-Oriented DBMS
Basic Concepts
• The attribute values of an object represent
the object’s status.
– Status is accessed or modified by sending
messages to the object to invoke the
corresponding methods
11/7/2000
Database Management -- R. Larson
Object-Oriented DBMS
Basic 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.
11/7/2000
Database Management -- R. Larson
Object-Oriented DBMS
Basic 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).
11/7/2000
Database Management -- R. Larson
Object-Oriented DBMS
Basic Concepts
• An OODBMS is a DBMS that directly
supports a model based on the objectoriented 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.
11/7/2000
Database Management -- R. Larson
Generalization Hierarchy
employee
Employee No
Name
Address
Date hired
Date of Birth
calculateAge
Hourly
Salaried
consultant
Hourly Rate
Annual Salary
Stock Option
Contract No.
Date Hired
calculateWage
calculateStockBenefit
AllocateToContract
11/7/2000
Database Management -- R. Larson
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
11/7/2000
Database Management -- R. Larson
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
11/7/2000
Database Management -- R. Larson
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
11/7/2000
Database Management -- R. Larson
Intelligent Database Systems
• They represent the evolution and merging of
several technologies:
–
–
–
–
–
11/7/2000
Automatic Information Discovery
Hypermedia
Object Orientation
Expert Systems
Conventional DBMS
Database Management -- R. Larson
Intelligent Database Systems
Automatic
discovery
Expert
Systems
Intelligent
Databases
Hypermedia
Traditional
Databases
11/7/2000
Database Management -- R. Larson
Object
Orientation
Intelligent Database Architecture
High-Level
Tools
High-Level
User Interface
Intelligent Database
Engine
11/7/2000
Database Management -- R. Larson
Environment Components
Flexible queries
Error detection
Data
Dictionary
Automatic Discovery
Concept
Dictionary
11/7/2000
Database Management -- R. Larson
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
11/7/2000
Database Management -- R. Larson
Intelligent Databases
• Automatic Discovery
–
–
–
–
–
11/7/2000
Data comprehension
Form Hypotheses
Make queries
View results and perhaps modify hypotheses
Repeat
Database Management -- R. Larson
Intelligent Databases
• Automatic Error Detection
– Integrity Constraints
– Rule systems
– Analysis of data for anomalies
11/7/2000
Database Management -- R. Larson
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
11/7/2000
Database Management -- R. Larson
Intelligent Databases
• Intelligent User Interfaces
– Hyperlinked data in the data/knowledge base
– Multimedia presentations
– Dynamic linking of related information
11/7/2000
Database Management -- R. Larson
Intelligent Databases
• Intelligent Database Engine
–
–
–
–
–
–
–
–
–
11/7/2000
OO support
Inference features
Global optimization
Rule manager
Explanation manager
Transaction manager
Metadata manager
Access module
Multimedia manager
Database Management -- R. Larson
Object Relational Databases
• Background
• Object Definitions
– inheritance
• User-defined datatypes
• User-defined functions
11/7/2000
Database Management -- R. Larson
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)
11/7/2000
Database Management -- R. Larson
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
11/7/2000
Database Management -- R. Larson
Object Relational Extended SQL
(Illustra)
• CREATE TABLE tablename {OF TYPE
Typename}|{OF 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
11/7/2000
Database Management -- R. Larson
Object-Relational SQL in
ORACLE
• CREATE (OR REPLACE) TYPE typename
AS OBJECT (attr_name, attr_type, …);
• CREATE TABLE OF typename;
11/7/2000
Database Management -- R. Larson
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”
11/7/2000
Database Management -- R. Larson
Constructor Functions
• INSERT INTO Animal values
(ANIMAL_TY(‘Mule’, ‘Frances’,
TO_DATE(‘01-APR-1997’, ‘DD-MMYYYY’)));
• Insert a new ANIMAL_TY object into the
table
11/7/2000
Database Management -- R. Larson
Selecting from an Object Table
• Just use the columns in the object…
• SELECT Name from Animal;
11/7/2000
Database Management -- R. Larson
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);
11/7/2000
Database Management -- R. Larson
What Does the Table Look like?
• DESCRIBE CUSTOMER;
NAME
TYPE
----------------------------------------------------CUSTOMER_ID
NUMBER
PERSON
NAMED TYPE
11/7/2000
Database Management -- R. Larson
Inserting
• INSERT INTO CUSTOMER VALUES (1,
PERSON_TY(‘John Smith’,
ADDRESS_TY(‘57 Mt Pleasant St.’,
‘Finn’, ‘NH’, 111111)));
11/7/2000
Database Management -- R. Larson
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...
11/7/2000
Database Management -- R. Larson
Selecting from Abstract
Datatypes
• SELECT Customer_id, person.name from
Customer;
• SELECT Customer_id,
person.address.street from Customer;
11/7/2000
Database Management -- R. Larson
Updating
• UPDATE Customer SET
person.address.city = ‘HART’ where
person.address.city = ‘Briant’;
11/7/2000
Database Management -- R. Larson
Functions
• CREATE [OR REPLACE] FUNCTION
funcname (argname [IN | OUT | IN OUT]
datatype …) RETURN datatype (IS | AS)
{block | external body}
11/7/2000
Database Management -- R. Larson
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;
11/7/2000
Database Management -- R. Larson
Example
• Select NAME,
BALANCE_CHECK(NAME) from
Worker;
11/7/2000
Database Management -- R. Larson
TRIGGERS
• Create TRIGGER UPDATE_LODGING
INSTEAD OF UPDATE on
WORKER_LODGING for each row BEGIN
• if :old.name <> :new.name then update worker
set name = :new.name where name = :old.name;
• end if;
• if :old.lodging <> … etc...
11/7/2000
Database Management -- R. Larson