Temporal Databases Multimedia Databases DB2 Extenders by pua50703

VIEWS: 8 PAGES: 31

									 Temporal Databases
Multimedia & Databases
     DB2 Extenders


    nikos dimitrakas
     www.nikosdimitrakas.com
       nikos@dsv.su.se
          08-162099
          room 6626
                               1
          Temporal Databases
• Time
  – Transaction time
      When something is registered in the database
  – Valid time
      When something occurs in the real world




                                                     2
            Database Taxonomy
• Snapshot databases
   – no time – just the current state
• Historical databases
   – only valid time – records the full history/evolution of
     concepts in the database
• Rollback databases
   – only transaction time – records all previous states of the
     database
• Temporal databases
   – both valid and transaction time – combines the
     advantages of both historical and rollback databases.



                                                                  3
Database Taxonomy




                    4
                An Example
Consider the following trivial information:
• A database of people and their salary.




Snapshot database:
 PID           Name           Salary
 001           Bill           7500
 002           John           8100
 …             …              …


                                              5
            Snapshot Database

PID            Name          Salary
001            Bob           7500
002            John          8100
…              …             …

We can only see the current value. We do not
know when the value was entered in the
database nor whether it has ever been changed.




                                                 6
              Historical Database

 PID   Name   Salary   StartTime    EndTime
 001   Bob    6500     Jan 1998     Feb 1999
 001   Bob    8000     Feb 1999     Dec 1999
 001   Bob    7500     Dec 1999     <now>
 002   John   7900     May 1999     Mar 2001
 002   John   8100     Mar 2001     <now>
 …     …      …        …            …

We can see the current value for any point in
time.
We still do not know when the value was
entered in the database nor whether it has ever
been changed (error correction).
                                                  7
             Historical Database
               error correction
If we discover an error in the database we have the
possibility to change it for that period of time.
If we find out that Bob earned 7500 between February
1999 and April 1999 we can modify our data to the
following:
PID   Name    Salary   StartTime   EndTime
001   Bob     6500     Jan 1998    Feb 1999
001   Bob     7500     Feb 1999    Apr 1999
001   Bob     8000     Apr 1999    Dec 1999
001   Bob     7500     Dec 1999    <now>
002   John    7900     May 1999    Mar 2001
002   John    8100     Mar 2001    <now>
…     …       …        …           …                   8
             Rollback Database

PID   Name   Salary   TransTime
001   Bob    6500     Mar 1998
001   Bob    8000     Feb 1999
001   Bob    7500     Sep 2001
002   John   7900     Mar 2001
002   John   8100     Sep 2001
…     …      …        …

We can see all the values that have been valid
in the database.
We know when the values were entered in the
database, but we do not know when they were
valid. We can not change values in any of the
records. We can only add new records.            9
              Rollback Database
                interpretation
 PID   Name   Salary   TransTime
 001   Bob    6500     Mar 1998
 001   Bob    8000     Feb 1999
 001   Bob    7500     Sep 2001
 002   John   7900     Mar 2001
 002   John   8100     Sep 2001
 …     …      …        …

We can always see which value was current in the
database at any point in time. It can be an advantage
to know what errors were contained in the database
at a certain time!
                                                        10
              Temporal Database

PID   Name   Salary   StartTime   EndTime    TranTime
001   Bob    6500     Jan 1998    Feb 1999   Mar 1998
001   Bob    8000     Feb 1999    Dec 1999   Feb 1999
001   Bob    7500     Dec 1999    <now>      Sep 2001
002   John   7900     May 1999    Mar 2001   Mar 2001
002   John   8100     Mar 2001    <now>      Sep 2001
…     …      …        …           …          …


We can see the valid value for any point in time.
We can see when a value was entered in the
database.
We can see what errors have been corrected
and when.                                               11
             Temporal Database
              error correction
PID   Name   Salary   StartTime   EndTime    TranTime
001   Bob    6500     Jan 1998    Feb 1999   Mar 1998
001   Bob    8000     Feb 1999    Dec 1999   Feb 1999
001   Bob    7500     Dec 1999    <now>      Sep 2001
002   John   7900     May 1999    Mar 2001   Mar 2001
002   John   8100     Mar 2001    <now>      Sep 2001
001   Bob    7500     Feb 1999    Apr 1999   May 2003
001   Bob    8000     Apr 1999    Dec 1999   May 2003
…     …      …        …           …          …
 Errors can be corrected but the transaction time
 shows when they where corrected. We can
 therefore still see that until May 2003 the
 database ”believed” that Bob earned 8000
 between February 1999 and Dec 1999.                    12
                        Querying
                     Snapshot    Historical   Rollback    Temporal
How much does        7500        7500         7500        7500
Bob earn?
How much did Bob     cannot be   7500         maybe       7500
earn in March        expressed                8000
1999?
What was Bob’s       cannot be   cannot be    8000        8000
current salary in    expressed   expressed
March 1999?
What did we think    cannot be   cannot be    cannot be   7500
that Bob earned in   expressed   expressed    expressed
March 1999 (vt) a
year ago (tt)?
What did we think    cannot be   cannot be    cannot be   8000
that Bob earned in   expressed   expressed    expressed
March 1999 (vt)
three years ago
(tt)?                                                                13
                       Pros & Cons
• Pros
  – No missing data


• Cons
  – Requires more space
  – More complicated to update records
  – More complicated to query the database


• Workarounds
  – Horizontal segmentation (simplifies querying of current data)




                                                                    14
                         Multimedia
• Types
  –   Images, graphics
  –   Audio
  –   Video
  –   Text documents
  –   ??
• Aspects
  – Time
  – Size
  – Interpretation




                                      15
              Time & Dimensions
• Text
   – Time independent (discrete)
   – 1 dimension
• Audio
   – Time dependent (continuous)
   – 1 dimension
• Image
   – Time independent
   – 2 dimensions (height & width)
• Video
   – Time dependent
   – 3 dimensions (height, width & time)
                                           16
        Managing & Effects of Size
• Multimedia in file system (Reference in database)
• Multimedia in database
   – Stored separately
   – Mixed with “normal” data


• Storage choice affects performance
   – slower access to multimedia  faster access to normal data


• Vertical segmentation



                                                                  17
                  Applications
•   Travel industry
•   Entertainment industry
•   Medical databases
•   Text and photograph archives
•   Digital libraries
•   Electronic encyclopedias
•   Geographic information systems
•   Shopping guides
•   …



                                     18
                   Requirements
• Querying
   – On
      » Content dependent data
      » Content descriptive data
      » Content independent data
• Retrieval
   – Fast retrieval
   – Smooth retrieval for time dependent media
• Presentation
   – This can be placed outside the DBMS



                                                 19
              Multimedia Metadata
• Data about the multimedia objects
   – Content dependent data
     Data that can be derived from the contents of the multimedia
     object
     The color of the bird in the picture.
     The lyrics of the song.
   – Content descriptive data
     Data associated with the contents of the multimedia object, but
     cannot be automatically identified
     The breed of the bird in the picture.
     The singer’s sex.
   – Content independent data
     Data that is associated with the multimedia object, but does not
     relay to its contents
     The name of the photographer
     The name of the song writer, the brand of the microphone used.


                                                                        20
               Querying needs
• Find all images taken by J. Smith!
• Find all images with the same color, shape and
  texture as this image!
• Find all images which look like this image!
• Find all images with the same color distribution like
  this sunset photograph!
• Find all images which contain a car!
• Find all images which contain a car and a man who
  looks like this!
• Find all the songs that are about surfing!
• Find all documents that have the words church and
  song in the same sentence!
                                                      21
               Querying needs
• Find all videos with big explosions!
• Find a video where there is an explosion after a car
  chase!
• Find an instrumental audio clip with both violin and
  electric guitar!
• Show me the first 10 seconds of all the videos!
• Show me the 20 seconds exactly before the
  explosion in videos that contain explosions!
• Find all songs that are duets and end with a fade-
  out!
• Find all images with a dog standing next to a tree!
• …
                                                         22
                      Query Types
• Queries on the content of the media information
   – Find all images which contain a car!
   – Find all videos with big explosions!
   – Find an instrumental audio clip with both violin and electric
     guitar!
• Queries by example (QBE)
   – Find all images which look like this image!
• Time indexed queries
   – Show me the first 10 seconds of all the videos!
• Spatial queries
   – Find all images with a dog standing next to a tree!
   – Find a video where there is an explosion after a car chase!
   – Find all documents that have the words church and song in
     the same sentence!
• Application specific queries
   – Queries on content descriptive/content independent data (?)
• Combinations of the above                                          23
                  Querying
          Multimedia vs. Normal Data

                        Multimedia Data          Normal Data

Query Matching          Approximate              Exact

Sorting                 By Relevance             User Specified



 Examples:
 •All people that live in Stockholm sorted by name.
 •All pictures like this one.




                                                                  24
               Multimedia Analysis
• Identify features
   – There is a river in the picture
   – There is a violin playing
   – Blue eyes, brown hair
• Manually add features
   – The person on the picture is Indian
   – The dog’s name i Barky
• Identify relations
   – The car is on the left of the house
   – The person is inside the car
   – The saxophone solo was before the guitar solo
• Manually add relations
   – The dog is owned by Howard
   – James employs Roger
                                                     25
           Other Considerations
•   Similarity searches
•   Ranking results
•   Weighing conditions
•   Synonym management




                                  26
Example




          27
             IBM DB2 & Multimedia
• Built-in support for
  –   Basic content dependent data
  –   Query by example (image only)
  –   Spatial queries (text only)
  –   Returning only complete objects
• Possible to define other tables and columns
  for features and relations
• Possible to define functions
• Possible to extend (with programming)
• Multimedia Storage
  – in the database
  – outside the database
                                                28
 DB2 Extenders & Assignments
• Text                         • Audio, Video, Image
   – Exact searches               – Metadata searches
   – Linguistic searches       • Image only
   – Spatial searches             – Query by example
   – Synonym searches             – Color related searches
   – Similarity searches          – Similarity searches
   – Ranking results              – Ranking


        • Storage
           – In the database
               » mixed with other data (Text)
               » in separate tables (Multimedia)

                                                             29
              DB2 Extenders & Labs
• Text                                • Audio, Video, Image
   –   Create database                     –   Create database
   –   Load data                           –   Enable for multimedia
   –   Enable for text indexing            –   Load data
   –   Query database                      –   Query database

  We will not extract the actual multimedia objects,
  since that would require some presentation
  facilities which are not included in DB2

  This would be an interesting project for those interested in learning more
  about working with multimedia and wouldn’t mind doing a little
  programming.
  Project course: IS8/2i1410 Current Problems in Information Systems
  More on Text Retrieval:
                                                                               30
  ISBI/2I1068/2I4078 Internet Search Techniques and Business Intelligence
    Large Objects vs. Performance
            testing in DB2
1. Create a table with multimedia and normal
   columns!
2. Load the table with data!
3. Write a query for extracting only normal data
   (Varchars, Integers, etc.)!
4. Evaluate your query with an Access Plan!
5. Create a similar table only with the normal
   columns!
6. Load the same data to the new table!
7. Rewrite the query so that it uses the new table!
8. Evaluate the new query!
9. Compare the two results!                           31

								
To top