Transcript PowerPoint Template - exercise

Enhanced Data Models for
Advanced Applications
Nguyễn Toàn
Nguyễn Hữu Vũ
Contents
1.
Active Database and Triggers
2.
Temporal Database Concepts
3.
Multimedia Database
4.
Deductive Database
Active Database AND Triggers
What are “Rules”?
 Actions are automatically triggered by
certain events.
And “Triggers”?
 A technique for specifying certain types of
active rules.
“Triggers” has existed in early versions of the
SQL specification and they are now part of
SQL-99 standard.
Generalized Model For
Active Databases and Triggers
The Event-Condition-Action (ECA) model.
A rule has three components:
 Event(s): database update operation(s)
 Condition: determines whether the rule should
be excuted after the event occurred
 Action: action to be taken
• SQL command(s)
• External program
Example
EMPLOYEE
Name
Ssn
Salary
Dno
Dno
Total_sal
Manager_ssn
Supervisor_ssn
DEPARTMENT
Dname
 Events:
 Inserting new employee tuples.(R1)
 Changing the salary of existing employees.(R2)
 Changing the assignment of existing employee
from one department to another.(R3)
 Delete employee tuples.(R4)
Example
EMPLOYEE
Name
Ssn
Salary
Dno
Dno
Total_sal
Manager_ssn
Supervisor_ssn
DEPARTMENT
Dname
 R1:
 Event: INSERT new employee
 Condition: Dno for new employee is not NULL
 Action: update value of Total_sal
Example
EMPLOYEE
Name
Ssn
Salary
Dno
Dno
Total_sal
Manager_ssn
Supervisor_ssn
DEPARTMENT
Dname
R1:
CREATE TRIGGER Total_sal1
AFTER INSERT ON EMPLOYEE
FOR EACH ROW
WHEN (NEW.Dno IS NOT NULL)
UPDATE DEPARTMENT
SET Total_sal = Total_sal + NEW.Salary
WHERE Dno = NEW.Dno;
Example
EMPLOYEE
Name
Ssn
Salary
Dno
Dno
Total_sal
Manager_ssn
Supervisor_ssn
DEPARTMENT
Dname
 R3:
 Event: UPDATE Dno attribute
 Condition: always excuted
 Action: update the Total_sal of the employee’s
old department + update the Total_sal of the
employee’s new department
Example
EMPLOYEE
Name
Ssn
Salary
Dno
Dno
Total_sal
Manager_ssn
Supervisor_ssn
DEPARTMENT
Dname
R3:
CREATE TRIGGER Total_sal3
AFTER UPDATE OF Dno ON EMPLOYEE
FOR EACH ROW
BEGIN
UPDATE DEPARTMENT
SET Total_sal = Total_sal + NEW.Salary
WHERE Dno = NEW.Dno;
UPDATE DEPARTMENT
SET Total_sal = Total_sal - OLD.Salary
WHERE Dno = OLD.Dno;
END;
Oracle Triggers
<trigger>::= CREATE TRIGGER <trigger name>
(AFTER|BEFORE) <trigger events> ON <table name>
[FOR EACH ROW]
[WHEN <condition>]
<trigger actions>;
<triggering events>::= <trigger event> {OR <trigger
event>
<trigger event>::= INSERT|DELETE|UPDATE [OF
<column name> {, <column name>}]
<trigger action>::= <PL/SQL block>
SQL-99 Triggers
<trigger>::= CREATE TRIGGER <trigger name>
(AFTER|BEFORE) <trigger events> ON <table name>
REFERENCING (NEW | PLD) (ROW | TABLE) AS
<reference name>
FOR EACH (ROW | STATEMENT)
[WHEN <condition>]
<trigger actions>;
<triggering events>::= <trigger event> {OR <trigger
event>
<trigger event>::= INSERT|DELETE|UPDATE [OF
<column name> {, <column name>}]
<trigger action>::= <PL/SQL block>
SQL-99 Triggers
 R1:
CREATE TRIGGER Total_sal1
AFTER INSERT ON EMPLOYEE
REFERENCING NEW ROW AS N
FOR EACH ROW
WHEN (N.Dno IS NOT NULL)
UPDATE DEPARTMENT
SET Total_sal = Total_sal + N.Salary
WHERE Dno = N.Dno;
Design and Implementaion Issues
The 1st issue: activation, deactivation and
grouping of rules.





Deactivate command
Activate command
Drop command
Rule sets
Process rules command
Design and Implementaion Issues
The 2nd issue:
 Before, after, concurrently
 Separate transaction or a part of the same
transaction?
 Rule consideration
• Immediate consideration
• Deferred consideration
• Detached consideration
 Excution consideration
• …
Design and Implementaion Issues
The 3rd issue:
 Row-level rules
 Statement-level rules
Difficulty:
 No easy-to-use techniques for designing,
writing, verifying rules
 Limit the widespread use of active rules
Statement-Level Active Rules in
STARBURST
 R1S:
CREATE RULE Total_sal1 ON EMPLOYEE
WHEN INSERTED
IF EXISTS (SELECT * FROM INSERTED WHERE Dno IS NOT NULL)
THEN UPDATE
DEPARTMENT AS D
SET
D.Total_sal= D.Total_sal +
(SELECT SUM(I.Salary) FROM INSERTED
AS (WHERE D.Dno=I.Dno)
WHERE
D.Dno IN (SELECT Dno FROM INSERTED)
Potential Applications for Active
Databases
Allow notification of certain conditions that occur
 Eg: an active database may be used to monitor, say,
the temperature of an industrial furnace
Enforce integrity constraints by specifying the
types of events that may cause the caonstraints
to be violated
 Eg: active rule monitor the GPA of students whenever
a new grade is entered.
Maintenance of derived data
Temporal Database Concepts
Temporal Databases: encompass all database
applications that require some aspect of time
when organizing their information.
TDB applications (healthcare, insurance…)
The majority of DB have some temporal
information.
Time Representation, Calendars, and
Time dimension
Points – Chronon
Calendar
Event Information vs Duration (State)
Information
Valid Time and Transaction Time Dimensions
Incorporating Time in RDB
Valid Time Relations
EMPLOYEE
Name
Ssn
Salary
Dno
Supervisor_ssn
DEPARTMENT
Dname
Dno
Total_sal
Manager_ssn
Tuple Versioning
Vst, Vet : DATE
EMP_VT
Name
Ssn
Salary
Dno
Supervisor_ssn
Vst
Vet
DEPT_VT
Dname
Dno
Total_sal
Manager_ssn
Vst
Vet
Incorporating Time in RDB
Valid Time Relations
EMP_VT
Name
Ssn
Salary
Dno
Supervisor_ssn
Vst
Vet
DEPT_VT
Dname
Dno
Total_sal
Manager_ssn
Vst
Vet
DEPT_VT
Dname
Dno
Total_sal
Manager_ssn
Vst
Vet
Research
5
300000
12345678
2001-09-20
2007-03-31
PR
3
65000
52134521
2005-05-19
now
Research
5
500000
87654321
2007-04-01
now
Incorporating Time in RDB
Transaction Time Relations
EMPLOYEE
Name
Ssn
Salary
Dno
Supervisor_ssn
DEPARTMENT
Dname
Dno
Total_sal
Manager_ssn
Tst, Tet : Timestamp
EMP_TT
Name
Ssn
Salary
Dno
Supervisor_ssn
Tst
Tet
DEPT_TT
Dname
Dno
Total_sal
Manager_ssn
Tst
Tet
Incorporating Time in RDB
Bitemporal Relations
EMPLOYEE
Name
Ssn
Salary
Dno
Supervisor_ssn
DEPARTMENT
Dname
Dno
Total_sal
Manager_ssn
Vst, Vet : DATE
Tst, Tet :TIMESTAMP
EMP_VT
Name
Ssn
Salary
Dno
Supervisor_ssn
Vst
Vet
Tst
Tet
DEPT_VT
Dname
Dno
Total_sal
Manager_ssn
Vst
Vet
Tst
Tet
Incorporating Time in RDB
Implementation Considerations
Store all the tuples in the same table
Create two tables:
 Currently valid inforemation
 The rest of the tuples
Allows the DB administrator to have different
access paths, such as indexes for each
relation, and keeps the size of current table
reasonable
Another option is to vertically partition the
attributes of the TR into separate relations
Temporal Querying Constructs
and the TSQL2 Language
 TSQL2: extends SQL with constructs for temporal
databases
 CREATE TABLE statement is extended with an
optional AS-clause
 AS VALID STATE<GRANULARITY>
(valid time relation with valid time period)
 AS VALID EVENT<GRANULARITY>
(valid time relation with valid time point)
 AS TRANSACTION
(transaction time relation with transaction time period)
 AS VALID STATE<GRANULARITY> AND TRANSACTION
(bitemporal relation, valid time period)
 AS VALID EVENT<GRANULARITY> AND TRANSACTION
(bitemporal relation, valid time point)
Temporal Querying Constructs
and the TSQL2 Language
 Some of the more common operations used in
queries:
 [T.Vst, T.Vet] INCLUDES [T1, T2]
(T1>=T.Vst AND T2<=T.Vet)
 [T.Vst, T.Vet] INCLUDED IN [T1, T2]
(T1<=T.Vst AND T2>=T.Vet)
 [T.Vst, T.Vet] OVERLAPS [T1, T2]
(T1<=T.Vet AND T2>=T.Vst)
 [T.Vst, T.Vet] BEFORE [T1, T2]
(T1>=T.Vet)
 [T.Vst, T.Vet] AFTER [T1, T2]
(T2<=T.Vst)
 [T.Vst, T.Vet] MEETS_BEFORE [T1, T2]
(T1=T.Vet+1)
 [T.Vst, T.Vet] MEETS_AFTER [T1, T2]
(T2+1=T.Vst)
Multimedia Database
As hardware becomes more powerful and as
software becomes more sophisticated, it is
increasingly possible to make use of multimedia
data, such as images and video
Example
 Consider a police investigation of a large-scale drug operation. This
investigation may generate the following types of data
 Video data captured by surveillance cameras that record the
activities taking place at various locations.
 Audio data captured by legally authorized telephone wiretaps.
 Image data consisting of still photographs taken by investigators.
 Document data seized by the police when raiding one or more
places.
 Structured relational data containing background information,
back records, etc., of the suspects involved.
 Geographic information system data remaining geographic data
relevant to the drug investigation being conducted.
Multimedia Database - Possible
Queries
Image Query (by example):
 Police officer Rocky has a photograph in front of him.
 He wants to find the identity of the person in the picture.
 Query: “Retrieve all images from the image library in which
the person appearing in the (currently displayed) photograph
appears”
Image Query (by keywords):
 Police officer Rocky wants to examine pictures of “Big
Spender”.
 Query: "Retrieve all images from the image library in which
“Big Spender” appears."
Multimedia Database - Possible
Queries
Video Query:
 Police officer Rocky is examining a surveillance video of a particular person
being fatally assaulted by an assailant. However, the assailant's face is
occluded and image processing algorithms return very poor matches. Rocky
thinks the assault was by someone known to the victim.
 Query: “Find all video segments in which the victim of the assault appears.”
 By examining the answer of the above query, Rocky hopes to find other
people who have previously interacted with the victim.
Heterogeneous Multimedia Query:
 Find all individuals who have been photographed with “Big Spender” and
who have been convicted of attempted murder in South China and who
have recently had electronic fund transfers made into their bank accounts
from ABC Corp.
Multimedia Database
Multimedia databases : provide features to store,
query different types of multimedia information,
such as images,video clips,audio clips,
documents
 The types of queries are content-based
retrieval
In a multimedia database, it might be
reasonable to have a query that asks for, say,
the top 10 images that are similar to a fixed
image. This is in contrast to a relational
database,where the answer to a query is simply
a set
Model in multimedia
database
 Based on automatic analysis: DBMS scan to identify mathematic
characteristic of source  index
 Based on manual identification: person scan multimedia sources
to identify and catalogindex
Image Characteristic
An image : stored as a set of pixel or cell
values,or in compressed form (gif,jpeg,mpeg)
Each pixel: contain a pixel value
Compressed Standard: use various mathematic
transformation to reduce the number of pixels
Using homogeneity predicate: define
conditions to divide image into homogenerous
segment automaticaly
 Eg: adjacent cell have similar pixel ->a segment
Image Query
Typical Query: find images that similar to a given
image
2 main techniques to solve:
 Distance function: compare to find result
 Transformation approach: using some
transformation to transform image->find result
Video Characteristic
A video : represented as a sequences of images
Divided into: video segments, the images in
same segment similar object, activity (person,
house, car, talking, delivering)
Segment is indexed ,by frame segment trees
technique
Often be compressed
Text/document Characteristic
Document: contains full text
Indexed by keyword in text, using 2 techniques
SVD(sigular value decompostion), telescopeing
vector tree  group similar document
Audio Source characteristic
Stored as recorded message
Discrete transforms are used to identify main
characteristic of a voice-> make similar-based
indexing and retreiving
Deductive Database
A deductive database system is a database
system which can make Deductive deductions
(ie: conclude additional facts) based on rules
and facts stored in the database
 Specify rules through a declarative
language(datalog)
 Have an inference engine to deduce new fact
from rules
Facts
Facts are specified as the same way the
relations are specified in the Relational
Database
 Except it is not necessary to include the attribute
names.
 The meaning of an attribute value in a tuple is
determined solely by its position in the tuple.
 Eg:
• supervise(james,jennifer).
• supervise(james,franklin).
predicate
arguments
Rules
They specify “virtual relations” that are not
actually stored but that can be formed from the
facts by applying deduction mechanisms based
on the rule specifications.
 somewhat similar to relational views, but different in
the sense that it may involve recursion.
 Eg: Rules:
• subordinate(X,Y) :- superior(Y,X).
• superior(X,Y) :- supervise(X,Z), superior(Z,Y).
• superior(X,Y) :- supervise(X,Y).
Deductive Database
The evaluation of Prolog is based on backward
chaining technique
Forward chaining: starts with the available data
and uses inference rules to extract more data
Backward chaining: starts with the goal
Examples
 There are some rules:
 If X croaks and eats flies - Then X is a frog
 If X chirps and sings - Then X is a canary
 If X is a frog - Then X is green
 If X is a canary - Then X is yellow
 My goal is to conclude the color of my pet Fritz,
given that he croaks and eats flies.
Examples
















RDBMS provide well-proven, robust, reliable mechanisms for managing data
1) Portability.
Relational model uses SQL to access the data. SQL language is well standardized and is largely the same
across different database vendors. If you know how to write SQLs for Oracle, for example, you'll be able to
also do so for DB/2, Informix, Sybase and others.
It is often possible for applications to work with relational database systems from different vendors.
require in-depth knowledge of vendor-specific programming
SQL allows to build very sophisticated queries understood by any DB professional who knows SQL
There is no serious competing data management technology, and a huge amount of data is committed to
RDBMSs.
Relational databases let you manipulate data in complex, interesting
ways, allowing you to retrieve all records that match your specific criteria, crossreference different tables, and update records in bulk
And
when your data is saved in various formats on different computers, security and
privacy are especially daunting, not to mention backup, recovery, and availability
“”
Examples
 There are some
rules:
 most widely used
model :vedor IBM
www.themegallery.com