Features of Active Database
- An active database supports definition and management of ECA rules.
- The active database must be able to evaluate conditions to execute action.
- Active Database must detect event occurrences.
- Active Database includes an event-driven architecture.
Types of Triggers in Oracle
Triggers can be classified based on the following parameters:
Classification based on the level
STATEMENT level trigger: It fires one time for a specified event statement.
ROW level trigger: It fires for each record that got affected in the specified event.
Classification based on the timing
BEFORE trigger: It fires before the specified event has occurred.
AFTER trigger: It fires after the specified event has occurred.
INSTEAD OF trigger: It fires after the specified event.
Classification based on the event
DML trigger: It fires when the DML event is issued, such as Insert, Update, Delete.
DDL trigger: It fires when the DDL event is issued, such as Create, Alter.
DATABASE trigger: It fires when the database event is issued, such as Logon, Logoff, Startup, Shutdown.
Row Level Trigger vs Statement Level Trigger
| Aspect | Row-Level Trigger | Statement-Level Trigger |
|---|
| Scope | Applies to individual rows within a table | Applies to an entire statement or transaction |
| Triggered By | Changes or events that affect specific rows | Specific SQL statement or database operation |
| Evaluation | Evaluated for each affected row individually | Evaluated once for the entire statement or transaction |
| Actions | Actions can be performed on the affected rows individually | Actions can affect multiple rows or entire result set |
| Performance | Rule evaluation for each row can impact performance | Rule evaluation is typically faster since it's done once |
| Primary Use | Specifically used for data auditing purpose | For enforcing all the additional security on the transactions performed on the table |
| Syntax | FOR EACH ROW clause is present in CREATE TRIGGER command | FOR EACH ROW clause is omitted in the CREATE TRIGGER command |
| Example | If 1500 rows are to be inserted into a table, the row-level trigger would execute 1500 times | If 1500 rows are to be inserted into a table, the statement-level trigger would execute only one time |
Example of Row level trigger
| Sid | Sname | Age |
|---|
| 1 | Rajan | 33 |
| 2 | Aarav | 15 |
| 3 | Kamala | 30 |
CREATE OR REPLACE TRIGGER Age_changes
BEFORE DELETE OR INSERT OR UPDATE ON Student
FOR EACH ROW
WHEN (NEW.Sid > 0)
DECLARE
age_diff NUMBER
BEGIN
age_diff := :NEW.age - :OLD.age;
dbms_output.put_line('Previous age:' || :OLD.age);
dbms_output.put_line('Current age:' || :NEW.age);
dbms_output.put_line('Age difference:' || age_diff);
END;
UPDATE student
SET age = age + 5
WHERE sid = 3;
Previous age: 30
Current age: 35
Age difference: 5
Applications of Active Database
- Data Monitoring activities, such as CIM, telecommunications, network management, program trading, medical and financial decision support systems, can greatly benefit from integration with Active Database.
- Production control, for example, power plants.
- Maintenance tasks, for example, airlift inventory control.
- Financial applications, for example, stock and bond trading.
- Telecommunications and network management.
- Air traffic control.
- Counter-integrated manufacturing.
- Statistics gathering and authorization tool.
Disadvantages of Active Database
- Insufficient methodological support in design and analysis.
- Lack of standardization.
- Missing development and administration tools for triggers.
- Weak performance.
- Optimizing large applications is rendered difficult by the segregation of transaction entry triggers and insufficient understanding of the software interaction.
- Lack of support for application development in many Active Database Management System prototypes.
Temporal Database Concepts
A temporal database stores data relating to time instances. It offers temporal data types and it stores information relating to past, present, and future time. A temporal database is a database with a built-in support for handling time-sensitive data.
Usually, databases store information only about the current state and not about the past states. For example, in an employee database, if the address or salary of a particular person changes, the database gets updated. The old value is no longer there. However, for many applications, it is important to maintain the past or historical values and the time at which this data was updated. That is, the knowledge of evolution is required. That is where temporal databases are useful. It stores information about the past, present, and future. Any data that is time-dependent is called the temporal data and these are stored in temporal databases.
Temporal databases store information about the states of the real world across time.
Applications of Temporal Database
Healthcare systems: Doctor needs patient health history for proper diagnosis.
Insurance system: Information about claims, accident history, time when policies are in effect needs to be maintained.
Reservation system: Date and time of all reservations is important.
Finance: Stock price histories need to be maintained.
Personal management: Salary and position history need to be maintained.
Banking: Credit histories.
Types of Time in Temporal Database
Valid Time
Valid time is a time period during which a fact is true in the real world. The association may be interpreted, the associated time is the time that the event occurred or the period during which the fact is considered to be true in the real world. If this interpretation is used, the associated time is often referred to as the valid time. A temporal database using this interpretation is called a valid time database.
For example, in a company, the salary of the employees have a valid time and end time.
| EMPNO | Name | Salary | Valid Time Start | Valid Time End |
|---|
| E1 | Indra | 8000 | 2072-03-30 | 2073-03-30 |
| E1 | Indra | 9000 | 2073-04-01 | 2074-03-30 |
| E2 | Bhupi | 5500 | 2072-03-30 | 2076-03-30 |
| E2 | Bhupi | 7500 | 2076-04-01 | now |
Transaction Time
Transaction time is the time period during which the fact is stored in a database was known. The associated time refers to the time when the information was actually stored in the database. That is, it is the value of the system time clock when the information is varied in the system. In this case, the associated time is called the transaction time. A temporal database using this interpretation is called transaction time database. Unlike valid time, here we can roll back data.
For example, the salary of the employees, they have a valid time and in time.
| EMPNO | Name | Salary | Transaction Start Time | Transaction End Time |
|---|
| E1 | Indra | 8000 | 2022-03-30, 10:02:33 | 2023-03-30, 10:02:33 |
| E1 | Indra | 9000 | 2023-04-01, 11:13:30 | 2024-03-30, 07:08:38 |
| E2 | Bhupi | 7500 | 2022-03-30, 10:01:43 | 2023-03-30, 11:12:23 |
| E2 | Bhupi | 7500 | 2024-04-01, 10:04:53 | Now |
Bi-temporal Data
It combines both valid and transaction time. It stores data with respect of both valid time and transaction time. In some applications, only one of the dimensions is needed, and in other cases, both time dimensions are required, in which case the temporal database is called bi-temporal database.
| EMPNO | Name | Salary | Transaction Time | Valid Start Time | Valid End Time |
|---|
| E1 | Indra | 8000 | 2022-03-30, 10:02:33 | 2022-03-30 | 2023-03-30 |
| E1 | Indra | 9000 | 2023-04-01, 10:02:33 | 2023-04-01 | 2024-03-30 |
| E2 | Bhupi | 7500 | 2022-03-30, 10:02:33 | 2022-03-30 | 2023-03-30 |
| E2 | Bhupi | 7500 | 2078-02-10, 02:33:00 | 2076-04-01 | now |
Spatial Databases
A Spatial databases is a database that is enhanced to store and access spatial data or data that is designed to geometry and space. These data are often associated with geographic locations and features or constructed features like cities. Data in spatial databases are used to store coordinates, points, lines, polygons, and topology. Some spatial databases handle more complex data like three-dimensional objects, topological coverage, and linear networks.
In a typical customer database, a company might query for all customers whose last name begins with a certain letter. In spatial databases, they can query for all customers within proximity of a particular store or find clusters of customers. These types of spatial transactions are not available in typical database.
Examples of Non-Spatial Data
- Names
- Phone numbers
- Email addresses of people
Examples of Spatial Data
- Weather and climate data
- Rivers
- Farms
- Ecological impact
- Medical imaging
- NASA satellites Imagery, Terabytes of data per day
Multimedia database is a collection of entirely related multimedia data that includes text, graphics, images, animation, video, audio, etc., and have vast amount of multi-source multimedia data. The framework that manages different types of multimedia data which can be utilized in different ways is known as multimedia database management system. Multimedia database provides a feature that allows users to store and query different types of multimedia information.
The main types of database queries that are needed to involve locating multimedia sources that contain certain objects of interest. For example, one may want to locate all video clips in a video database that include a certain person, say Michael Jackson. One may also want to retrieve video clips based on certain activities included in them, such as video clips where a soccer goal is scored by a certain player or team.
Contents of Multimedia Database Management System
Media data: It gives the actual data which represents an object.
Media format data: The information such as the resolution, sampling rate, encoding system, etc., about the format of the media data under consideration.
Media keyword data: The keyword description related to the generation of data, also known as content descriptive data. Eg. place, time, date of recording.
Media feature data: Media feature data contains data which is content dependent, such as kind of texture, distribution of, different shapes present in the data.
Design: The physical, conceptual and logical design of multimedia database is not addressed entirely, leading to performance and tuning issues.
Storage: The storage of database on the standard disk can lead to problems like representation mapping to disk, hierarchies, compression, etc.
Performance: Audio-video synchronization and audio playback applications are where physical limitations dominate. Parallel processing can reduce this problem, but this technique have not been completely developed yet. Multimedia database also consumes a lot of processing power and bandwidth.
Queries and retrievable: Multimedia such as image or audio video lead to retrievable and queries issues such as efficient query formation, query execution, etc.
Applications
Documents and record management
Knowledge dissemination: Example, electronic books, education and training, digital libraries.
Real-time monitoring and control: Example, marketing, advertising, retailing, entertainment, travel.
Deductive Database
A deductive database is a database system that can make deductions, that is, conclude additional facts based on rules and facts stored in the deductive database. Datalog is the language typically used to specify facts, rules, and queries in deductive databases.
A deductive database can be defined as an advanced database augmented with an inference system.
Database + Inference = Deductive Database
By evaluating rules against facts, new facts can be derived which in turn can be used to answer queries. It makes a database system more powerful.
A deductive database uses two main types of specification: facts and rules.
Facts
Facts are specified in a manner similar to the way relations are specified, except that it is not necessary to include the attribute names. In deductive database, the meaning of an attribute value in a tuple is determined solely by its position with the tuple.
Rules
Rules are somewhat similar to relational views. They specify virtual relations that are not actually stored but can be found from the facts by applying inference mechanisms based on the rule specification. The main difference between rules and views is that rules may involve recursion and hence may yield virtual relations that cannot be defined in terms of basic relational views.
Example
Facts:
supervised(Frank, Kim)
supervised(Frank, John)
supervised(Frank, Ash)
supervised(John, Eli)
supervised(John, Bob)
supervised(Bob, Asha)
Rules:
superior(X, Y) <- supervised(X, Y)
superior(X, Y) <- supervised(X, Z), superior(Z, Y)
subordinate(X, Y) <- superior(Y, X)
| Aspect | Information Retrieval | Data Retrieval |
|---|
| Definition | The software that deals with the organization, storage, retrieval, and evaluation of information from document repositories, particularly textual information, is called information retrieval | Data retrieval deals with the obtaining data from database management systems or DBMS. It is the process of identifying and retrieving the data from the database based on the query provided by the user or application |
| Purpose | Retrieves information about a subject | Determines the keywords in the user query and retrieves the data |
| Error Handling | Small errors are likely to go unnoticed | A single error object means total failure |
| Structure | Not always well-structured and is semantically ambiguous | Has a well-defined structure and semantics |
| Solution Provision | Doesn't provide a solution to the user or the database system | Provides solution to the user or the database system |
| Result Type | The results obtained are approximate matches | Results obtained are exact matches |
| Result Ordering | Results are ordered by relevance | Results are not necessarily ordered by relevance |
| Model Type | It is a probabilistic model | It is a deterministic model |