Enhanced Data Models for Advanced Database Applications

1. Enhanced Data Models for Advanced Database Applications CS 95 Advanced Database Systems Handout 8

2. Conventional databases for large amounts of current only, complex, volatile, structured data, available within an organization e.g., Relational Databases Enhanced Data Models Active Temporal Spatial Multimedia Statistical Information retrieval

3. Active Databases contain a set of active rules Rule is triggered when a particular event occurs. eg. on an Update operation on a certain table The rule may initiate other, or even replacement operations to be performed on the database

4. Active Databases Usually implemented with triggers, a rule implementation found in many relational products. Such rules may be used to: notify (someone) whenever a particular condition occurs create audit trails enforce integrity constraints maintenance of derived data maintain consistency of views whenever the base table structures are modified.

5. Active Databases Syntax summary for specifying triggers in the Oracle System <trigger> ::= CREATE TRIGGER <trigger name> (AFTER|BEFORE) <triggering 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> Even PostgreSQL supports triggers. See the PostgreSQL Help for more information.

6. Temporal databases conventional databases without the 'current only' constraint 'remembers' every value, i.e., when an update occurs, old value, new value and time of change all have to be stored in the DB. Different systems implement temporal data differently as a minimum a timestamp column is added to each relation and forms part of the key

7. Temporal databases Applications include: Healthcare where the history of a patient is important Insurance Reservation systems Scientific databases SQL (usually) is extended so a query can provide an answer from the database at any specified time. eg: What was the status of supplier S1 on April 1, 1990?

8. Temporal databases TSQL standard specifies the TIME PERIOD as a the standard domain measurement A query over a time PERIOD may return many rows for the same piece of information - one row for every set of values the information held within the time period. New SQL operations are like: CONTAINS, PRECEDES, SUCCEEDS, OVERLAPS Problems: DB grows very big very quickly, which adversely affects performance - dramatically.

9. Spatial databases conventional databases with spatial features, such as support for 3-dimensional objects applications: CAD/CAM cartographic (mapping) - 2-D maps plus a description behind each object (road, bridge, house etc.) meteorological - weather patterns occur in 3-D drawing, drafting, etc.

10. Spatial databases Some objects may be static: e.g., bridge, road etc. Other objects may have dynamic qualities: e.g., ambulances and other vehicles. Even a simple task, such as the distance between two objects or determining whether two objects overlap, are difficult SQL queries so new operators are included to perform these tasks hence, spatial databases are often OODBMS

11. Spatial databases Types of queries particular to spatial database are: Range query: e.g., find all hospitals within a particular distance from a given location. Nearest neighbor query: e.g., find an object of a given type closest to a given point. Spatial joins or overlays: Joins the objects of two type. e.g., find all cities on a particular road OR find all homes near a given river. PostgreSQL supports many spatial objects, including point, line, box, circle etc.

12. Multimedia databases Multimedia databases store data such as images, audio and video clips and documents. Support content-based queries, e.g., retrieve all video clips with a certain person, or all clips containing a drag race won by a certain Pro Stock driver

13. Multimedia databases Two methods of identifying such content: Automatic analysis - involves mathematical analysis and pattern matching of data within the clip. A different approach is required for video, audio, image and text. Obviously, this is a very computative intensive process. Manual identification - manual preprocessing phase obtains such information which is stored with the clips and can also be used to build indexes.

14. Statistical databases for large amounts of simple, non-volatile, structured data access is via aggregate statements only - SUM, COUNT, MAX, MIN, AVERAGE, STANDARD DEVIATION etc. they protect the identity of the individual problems with compromise of the DB. i.e., a set of queries that may determine the identity of an individual. Often solved by not allowing results that are computed from a return of less than n rows.

15. Information Retrieval databases Read-only, very large databases heavily indexed - indexes take a long time to update, so the data is not updated in real time. New data snapshots with their indexes are built periodically e.g., phone lists and search engines e.g., AltaVista indexes every word on every web page in its database index consists of word, location within page, URL size is ??? GB+ (200GB in 1998)