Enhanced Data Models for Advanced Applications

1. Enhanced Data Models forAdvanced Applications Nguyễn Toàn Nguyễn Hữu Vũ

2. 1. 2. 3. 4. Active Database and Triggers Temporal Database Concepts Multimedia Database Deductive Database Contents

3. 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.

4. 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

5. Example EMPLOYEE DEPARTMENT • 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)

6. Example EMPLOYEE DEPARTMENT • R1: • Event: INSERT new employee • Condition: Dno for new employee is not NULL • Action: update value of Total_sal

7. Example EMPLOYEE DEPARTMENT 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;

8. Example EMPLOYEE DEPARTMENT • 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

9. Example EMPLOYEE DEPARTMENT 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;

10. 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>

11. 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>

12. 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;

13. Design and Implementaion Issues • The 1st issue: activation, deactivation and grouping of rules. • Deactivate command • Activate command • Drop command • Rule sets • Process rules command

14. 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 • …

15. 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

16. 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)

17. 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

18. 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.

19. Time Representation, Calendars, and Time dimension • Points – Chronon • Calendar • Event Information vs Duration (State) Information • Valid Time and Transaction Time Dimensions

20. Incorporating Time in RDBValid Time Relations EMPLOYEE DEPARTMENT Tuple Versioning Vst, Vet : DATE EMP_VT DEPT_VT

21. Incorporating Time in RDBValid Time Relations EMP_VT DEPT_VT DEPT_VT

22. Incorporating Time in RDBTransaction Time Relations EMPLOYEE DEPARTMENT Tst, Tet : Timestamp EMP_TT DEPT_TT

23. Incorporating Time in RDBBitemporal Relations EMPLOYEE DEPARTMENT Vst, Vet : DATE Tst, Tet :TIMESTAMP EMP_VT DEPT_VT

24. Incorporating Time in RDBImplementation 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

25. Temporal Querying Constructsand 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)

26. Temporal Querying Constructsand 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)

27. 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

28. 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.

29. 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."

30. 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.

31. 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

32. 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

33. 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

34. 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

35. 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

36. 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

37. Audio Source characteristic • Stored as recorded message • Discrete transforms are used to identify main characteristic of a voice-> make similar-based indexing and retreiving

38. 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

39. 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

40. 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).

41. 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

42. 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 goalis to conclude the color of my pet Fritz, given that he croaks and eats flies.

43. 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, cross- • reference 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 • “ ”

44. Examples • There are some rules: • most widely used model :vedor IBM

45. Thank You ! www.themegallery.com