Database Applications: Active, Temporal & Multimedia

Database Applications: Unit 14 Study Notes

• This unit examines applications related to databases, including active databases, temporal databases, multimedia databases, and video database management systems (VDBMS).
• VDBMS gives integrated support for queries on storage management for video, semantic searches, and image query processing.

Objectives

• Explain active databases
• Discuss temporal databases
• Identify and explain text and media databases
• Explain video database management systems

Active Databases

• Active databases don't form distinct classes but define the range of database rule languages.
• Production-style rules in active databases automatically implement database operations in response to events and conditions.
• Unlike conservative databases, active databases identify situations in the database and trigger actions, usually database updates.

Design Principles for Active Rules

• Events trigger rules involuntarily, either by updating the database or initiating actions.
• Active databases contain triggers.
• Triggers can be defined as a method specifying active rules and are part of SQL 99 and later standards.
• DB2, Oracle, and MS SQL Server have trigger versions.

Starburst

• The following active rules are demonstrated in STARBURST R1S, R2S, and R3S

• R1S: CREATE RULE Total_sal1 ON EMPLOYEE

• R2S: CREATE RULE Total_sal2 ON EMPLOYEE

• R3S: CREATE RULE Total_sal3 ON EMPLOYEE

• Rule structure is studied using rule R1S with the CREATE RULE statement to name a rule and the ON clause to define the relation.

• The WHEN clause specifies events that trigger a rule, the IF clause states conditions, and the THEN clause states the actions.

• STARBURST uses SQL update commands like DELETE, INSERT, and UPDATE as triggers.

• Transition tables, including INSERTED, DELETED, NEW-UPDATED, and OLD-UPDATED, refer to transition tables related to keywords.

• For statement-level semantics, designers can refer to transition tables, modifying rules for row-level semantics.

• For R1S, the condition to check is: EXISTS (SELECT * FROM INSERTED WHERE Dno IS NOT NULL)

• The action updates the DEPARTMENT tuple linked to inserted employees by totaling their income to the Total_sal characteristic.

• The SUM aggregate function ensures that every salary is added.

• The UPDATE function is used to trigger rule R2S for updating employee salaries.

• Trigger R3S by updating the Dno attribute in EMPLOYEE when employees are shifted from one department to another.

• R3S updates both old and new departments related to reassigned employees by totaling new salaries and deducting old salaries from Total_sal.

• STARBURST implements delayed consideration for active rules, keeping triggered rules in a conflict set assessed upon transaction completion using the COMMIT WORK command.

• The PROCESS RULES command allows clients to begin rule consideration during a transaction, essential for evaluating multiple rules.

• Rule declaration syntax in STARBURST allows ordering rules by educating the system.

• Transition tables (INSERTED, DELETED, etc.) include total operations, influencing every table due to multiple operations applied.

Oracle

• Uses the Event-Condition-Action (ECA) model, containing three elements.

• Events generate rules for DB updates.

• The second element decides whether the generated rule is executed based on defined conditions.

• The third element explains the rule action as a set of SQL statements or an external program.

• Active rules can be applied on Total_sal to maintain salaries of each employee

• 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;

• CREATE TRIGGER Total_sal2 AFTER UPDATE OF Salary ON EMPLOYEE

• 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;

• CREATE TRIGGER Total_sal4 AFTER DELETE ON EMPLOYEE FOR EACH ROW WHEN (OLD.Dno IS NOT NULL ) UPDATE DEPARTMENT SET Total_sal = Total_sal – OLD.Salary WHERE Dno = OLD.Dno;

• CREATE TRIGGER Inform_supervisor1 BEFORE INSERT OR UPDATE OF Salary, Supervisor_ssn ON EMPLOYEE FOR EACH ROW WHEN ( NEW.Salary > ( SELECT Salary FROM EMPLOYEE WHERE Ssn = NEW.Supervisor_ssn ) ) inform supervisor(NEW.Supervisor_ssn, NEW.Ssn );

• Triggering rules happen after the triggering rule events.

• The ON clause explains the relation, and FOR EACH ROW explains the rule triggered for every row.

• The WHEN keyword is optional.

• Action is defined as a PL/SQL block with SQL statements or calls to external procedures.

• Events are SQL commands: INSERT, DELETE, UPDATE.

DB2

• IBM developed DB2, a relational model database server.

• It has three similar products: DB2 for LUW (Linux, Unix, and Windows), DB2 for z/OS (mainframe), and DB2 for iSeries.

• The DB2 LUW product runs on Linux and UNIX distributions, like Red Hat Linux, SUSE Linux, AIX, HP/UX, Solaris, and Windows systems.

• CREATE TRIGGER {BEFORE I AFTER} < trigger-event> ON [REFERENCING ] FOR EACH {ROW I STATEMENT} WHEN (<SQL-condition>) <SQL-procedure-statements> : INSERT I DELETE I UPDATE [ON ] : OLD AS I NEW AS I OLD_TABLE AS I NEW_TABLE AS

Application of Active Databases

• Active databases can announce conditions to happen, like observing the physical property of an organizational heating system.
• Active rules trigger when physical property records from sensors indicate a physical property increase to risk level.
• Active rules entail integrity constraints, assessing the conditions that assure constraints are not despoiled.
• Composite application constraints, known as business rules, might be enforced.
• Rules might notice the Average of Grade (AG) of pupils when a new grade is inserted
• Assure that subject requirements are fulfilled before permitting a pupil to apply for a subject.

Temporal Databases

• Temporal databases record time-referenced data.

• Applications include record keeping, banking, weather monitoring, and scheduling.

• They are suitable for organizing information on time constraints, aiding in the development of concepts for application developers and designers.

• Time is a factor in storing information for:

• Insurance to keep accident records and claims

• Healthcare to maintain patient histories

• Reservation systems to check availability

• Scientific databases to store time tracked experiments

• It may be desirable to keep PROJECT, JOB and SALARY histories of all the employees.

• The details about the YEAR, SEMESTER, COURSE and each SECTION are also included in the database.

• Temporal information is stored by many database applications, but the temporal feature is ignored.

• Temporal database types:

• Historical databases store data with respect to valid time.

• Rollback databases store data with respect to transaction time.

• Bitemporal databases store data with respect to both valid time and transaction time.

• Snapshot databases store a single real-world state usually the most recent.

• TimeDB databases store the history of data with respect to both valid time and transaction time

Multimedia Databases

• Multimedia databases facilitate storing and querying multimedia information.

• Information can demand for:

• Documents (like articles/books/journals)

• Images (like drawings/pictures)

• Video clips (like newsreels/ movies/home videos)

• Audio clips (like speeches/ phone messages/songs)

• The primary type of database query generally tries to locate multimedia sources

• Another scenario may be as of someone willing to retrieve video clips grounded on specific activities

• These types of queries are mentioned as content based retrieval

• Multimedia databases use models to index and manage sources grounded on contents.

• Two approaches to accomplish this task:

• Automatic analysis of the media sources

• Manual analysis of the objects and activites within

Video Database Management

• Video database management system is an open source system
• The extensions of the key database consists of store management for video, video pre-processing for representation of content and indexing, image and semantic-based query processing, real time buffer management.

Storage management for video

• Massive amount of data with real-time restrictions are carried by the buffer managers
• The database buffer zone and the streaming zone are split by the buffer pool.
• Stream manager are taken care of by the extended buffer management
• Interface direct buffer caching
• Storage manager assists buffer caching

Video Pre-Processing for Content Representation and Indexing

• Indexes take disk space.
• High-dimensional characteristic data are gathered for all video frames and shots.
• Problems occur in indexing and searching due to metadata extent and storage as multi-dimensional vectors.
• Increase potential indexing by including GiST v2.0.
• The predator's layer of query processing is changed for utilising the index of the Shore or GiST.
• The following statement is implemented with VDBMS inserted vector ADT.
• CREATE GSR INDEX <?eldname>
• for generating an example of GiST SR-tree for utilising all fields as access path in the queries
• Visual characteristic extraction and image resemblance are managed by multi-dimensional structure of indexing.
• Interface checks different indexing within VDBMS.
• Video query in addition operators were concerned with video query
• Video DBMS considers video techniques, the process of providing nearest queries to the access path performs image resemblance search.

Image and Semantic-Based Query Processing

• Uses image and semantic-based query processing for separating raw videos into shots
• Combines shots, visuals, and semantic signifiers

Real-Time Buffer Management

• Content based search is supported by continuous media servers and main memory
• Studies buffer for media streaming like Chang and Garcia-Molina’s prefetching
• Dynamic buffer allocation reduces memory requirement
• Buffer for delay-sensitive multimedia data
• Buffer allocation is goal-oriented for various DB
• Caching media streams enhances performance by decreasing addresses and minimizing wait time.
• Streaming is grounded on query outcomes, and buffer manager employs prefetching and caches pages
• VDBMS caching media streams incorporating data info while reducing initial latency and disk I/O.

Summary

• Active rules must be triggered automatically when events occur.
• The ECA model states the active database rules.
• The application inserts temperature readings from sensors, with active, triggered rules.
• Locating multimedia sources consists of certain objects of interest.
• Video pre-processing toolkit divides raw streams.

Glossary

• Active database: Takes action automatically to events
• Active rules: Automatically trigger the active rules when events take place
• Dynamic buffer allocation: Minimizes requirements for concurrent media streams.
• ECA: Event condition action
• Multimedia database: Lets users store and query more multimedia information
• VDBMS: Video Database Management Systems

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Get started for free



Related Documents

More Like This



5 questions

DBMS Triggers and Active Database Concepts Quiz and Flashcards



RevolutionaryVerdelite



79 questions

Active Directory Service in Windows



UnmatchedMandolin



108 questions

Active Directory Structure Overview



IndebtedOwl



40 questions

Pédagogie Active



Sandrine ORDONNEAU
Quick Share
Copy the link below or share directly to socials.
Anyone: Anyone can view and access this lesson.
Copy link Copied!
To share, set lesson to Public.
Use Quizgecko on...

Open

Browser
Continue
Got it!
Upgrade to continue
View Plans & Pricing





Feedback
Please use this form to submit feedback or report bugs. You can find answers to most questions in our Help Center.
Category Select a category General Bug General feedback Generating questions Playing a quiz Sharing a quiz
Feedback
Image (optional)
Submit
Create your free account
Continue with Google
Sign in with Apple
or continue with email
← Back
Your Name
Email
Password
Confirm Password
Already registered? Verify email →
By continuing, you agree to Quizgecko's Terms of Service and Privacy Policy.
Trusted by top students and educators worldwide





OK