Module 4

1. Explain the Generalized Model for Active Databases. Explain with an example

Ans:- An Active Database is a type of database that automatically responds to certain events
or changes in the database without user intervention.

This automatic behaviour is achieved using rules, usually called ECA Rules(Event–
Condition–Action)

Event -This is the cause that activates the rule. Most commonly, the event is a database
update operation, such as:

 INSERT
 DELETE
 UPDATE

Condition- Once the event happens, the database checks an optional condition

 The condition is like an IF statement.

 It decides whether the action should really be executed.

If:

 Condition = TRUE → Perform the action

 Condition = FALSE → Do nothing

Action- This specifies what operation should be performed when event and condition are
satisfied.

Example: Maintaining Department Total Salaries

Database Context

A simplified COMPANY database includes:

 EMPLOYEE(Name, Ssn, Salary, Dno, Supervisor_ssn)

 DEPARTMENT(Dname, Dno, Total_sal, Manager_ssn)

The example maintains the derived attribute Total_sal of each department.

Whenever an employee is inserted, updated, moved to another department, or deleted, the
Total_sal value must be automatically updated.
 Module 4

The four triggers handle different situations:

 R1 → New employee inserted

 R2 → Salary of existing employee updated
 R3 → Employee moved from one department to another (Dno updated)
 R4 → Employee deleted

R1: Trigger after INSERT

 Event: INSERT on EMPLOYEE

 Condition: [Link] IS NOT NULL
 Action: Add [Link] to Department.Total_sal

When a new employee joins a department, his/her salary must be added to the department's
Total_sal.

2: Trigger after updating Salary

 Event: UPDATE OF Salary

 Condition: [Link] IS NOT NULL
 Action: Adjust Total_sal by ([Link] – [Link])

When an employee’s salary changes, the department’s Total_sal must reflect the difference.

R3: Trigger after updating Dno (employee moved)

 Event: UPDATE OF Dno

 No condition

 Action:

1. Add [Link] to new department

2. Subtract [Link] from old department

When an employee is transferred, both departments must be updated.

Trigger after DELETE

 Event: DELETE on EMPLOYEE

 Condition: [Link] IS NOT NULL
 Action: Subtract [Link] from Total_sal

When an employee leaves the company, their salary must be removed from the Total_sal.

2. Explain Temporal Databases in detail. Discuss their types, features, and

advantages with suitable examples.
 Module 4

Ans- A temporal database is a database that can store and manage time-dependent data,
allowing users to query past, present, and future information.

Temporal databases mainly use two time dimensions:

1. Valid-Time Databases

[Link] Time = When a fact is true in the real world.

Example:
A salary of ₹50,000 is valid from Jan 2020 to Dec 2021.

[Link]-Time Databases

Transaction Time = When a fact is stored in the database.

Example:
A salary update was entered into the database on March 5, 2023.

3. Bi-Temporal Databases

Supports both valid time and transaction time.

It is the most powerful type.

Example:
A salary is valid from June 2021–2022, but it was entered into the database in October 2021.

4. Time-Series Databases:

Used for continuous time-stamped data such as:

 Stock prices
 Sensor data
 Weather measurements

Features of Temporal Databases

1. Time-stamped data

Each record has time attributes like:

 Valid_start, Valid_end
 Trans_start, Trans_end

2. Support for historical and future queries

“What was the salary in 2020?”

33. Automatic history management

 Module 4

Old values are preserved instead of overwritten.

4. Temporal extensions to SQL

SQL:2011 includes:

 PERIOD
 SYSTEM_TIME
 VALID_TIME
 Temporal predicates (OVERLAPS, CONTAINS, DURING)

5. No loss of information

Even deleted or updated data is kept as history.

6. Time granularity and chronons

Time is measured using chronons (smallest time units), such as:

 second
 millisecond
 day

Advantages of Temporal Databases

1. Accurate historical data(Keeps full time-based history.)

2. Better decision making(Used for trends, forecasting, analysis.)

3. Legal and compliance support(Required in banking, insurance, healthcare.)

4. Simplifies development(No need for manually maintaining “history tables”.)

5. Supports complex time queries(Overlap, before, after, during, intersection of times.)

6. High consistency(Reduces errors caused by overwriting old data.)

3. What is Spatial database? What are the common type and model for storing spatial data?
Categorize types of spatial operators

A spatial database is a database designed to store, manage, and process spatial data—that is, data
related to the location, shape, and geometry of objects in space.

a. Common Types and Models for Storing Spatial Data

Spatial data appears mainly in three basic forms:

A. Map Data (Vector Data)

Represents geographic features using three main shapes:

 Module 4

1. Points

 Single coordinate (x, y or latitude/longitude).

 Used for: buildings, towers, vehicles (moving or stationary).

2. Lines

 Represent objects with length.

 Examples: roads, rivers.
 Modeled as sequence of connected line segments.

3. Polygons (Areas)

 Represent objects with boundaries.

 Examples: cities, lakes, states, countries.

B. Attribute Data

This is descriptive information linked to map features.

Examples:
For a county → population, area, major city
For states/cities → census data, districts, demographic data

C. Image Data

Includes:

 Satellite images
 Aerial photos

These images can be overlaid with points/lines/polygons.

Categories of Spatial Operators

Spatial operators describe geometric relations and properties. They fall into three main
categories:

A. Topological Operators

 Check spatial relationships that do not change under rotation, scaling, or translation.
 Examples:
o open(region)
o close(region)
o inside(point, loop)

Used for conditions like “Find all freeways that intersect a city boundary.”
 Module 4

B. Projective Operators

 Capture concavity, convexity, and other shape-related properties.

 Example:
o convex hull
 Used to express whether an object lies inside the concave area of another.

C. Metric Operators

 Measure geometric properties and distances.

 Examples:
o length(arc)
o distance(point, point)
 Used to compute areas, directions, compactness, adjacency, etc.

4. How do you incorporate time in relational database using tuple versioning?

A temporal database stores not just the current data but also past versions of data.
Tuple versioning means: instead of overwriting old data, we create new tuples (versions)
with time periods.

There are three types of tuple-versioned temporal relations:

1. Valid-Time Relations (VT)

2. Transaction-Time Relations (TT)
3. Bitemporal Relations (BT) (both valid + transaction time)

1. Valid Time Relations (EMP_VT)

Valid time = when the fact is true in the real world.

We add two attributes:

 Vst → Valid Start Time

 Vet → Valid End Time (often "now")

Name ssn salary Vst vet

Smith 123 25000 2002-06-15 2003-05-31
Smith 123 30000 2003-06-01 now

 Each tuple = one version of the employee.

 When salary changes, we close the old version (set Vet to previous day) and insert a new
version.

A tuple is current if:

 Module 4

Vet = ‘now’ "now" means this version is valid up to the present.

Proactive Update

A proactive update is when the change is entered into the database before it becomes
effective in the real world.

2. Retroactive Update

A retroactive update is when the change is entered into the database after it becomes
effective in the real world

3. Simultaneous Update

A simultaneous update is when the change is applied to the database exactly at the same
time that it becomes effective in the real world.

2. Transaction Time Relations (EMP_TT)

Transaction time = when the change was applied to the database.

Attributes added:

 Tst → Transaction Start

 Tet → Transaction End (usually uc = until change)

Example:

Name ssn salary Tst Tet

Smith 123 25000 2001-05-01 2003-06-04
10:00 08:56
Smith 123 30000 2003-06-04 uc
08:56

 System automatically timestamps changes.

 You can “roll back” to any past database state by checking where a time T lies in the
interval [Tst, Tet].

3. Bitemporal Relations (EMP_BT)

Stores valid time + transaction time.

 Module 4

Attributes:

 Vst, Vet
 Tst, Tet

Name ssn salary Vst Vet Tst Tet

Smith 123 25000 2002-06-15 2003-05-31 2001-05-01 2003-06-04
10:00 08:56
Smith 123 30000 2003-06-01 Now 2003-06-04 uc
08:56

 Keeps full history of real-world changes (valid time)

 Keeps full history of when database changed (transaction time)

 No tuple is deleted — only new versions are added → append-only database.

 Supports corrections: two tuples can have same valid time but different transaction times.

5. Explain the Multimedia concepts.

A multimedia database (MMDB) is a database that stores, manages, and retrieves different
types of multimedia data such as images, videos, audio, and documents. These databases
support content-based retrieval, where multimedia objects are searched based on their
actual content rather than simple text labels.

Content-Based Retrieval

 When we search multimedia (images, videos, audio), we often want to find items
based on what they contain
 Find all videos that contain a car
 Find all images that have a tree

Two methods are there

1. Automatic Analysis Approach

The computer analyses the multimedia file automatically using mathematical and signal-
processing techniques

 ses algorithms to automatically extract features

 Different techniques for image, video, audio, and text

 Faster for large databases

 Module 4

 Uses mathematical transformations like:

 DFT – Discrete Fourier Transform

 DCT – Discrete Cosine Transform

2. Manual Annotation Approach

 A human manually looks at the multimedia file and identifies important objects or
activities.
 Example:
A person watches a video → tags frames containing “car”, “road”, “bridge” →
these tags are stored as metadata.

Image Representation

 Images can be stored in two ways:

 Raw Form
 Stored as a grid of pixels (m × n cells)
 Each pixel contains a value
 1 bit for black and white
 multiple bits for color and grayscale
 Compressed Form
 Used to save space. Standards include:
 GIF
 JPEG
 MPEG

Video Analysis and Indexing

 A video is made up of many frames (still images).Analyzing every single frame one
by one is too slow and unnecessary.
 So instead, the video is divided into video segments.
 Audio Sources Audio data can include:
 Recorded speeches
 Classroom presentations
 Phone conversations
 Surveillance audio recordings
 Like images and videos, audio must also be indexed so it can be searched easily.

Automatic Analysis of Images

 To support image search, the system must analyze images and represent them using
features that help define similarity.
 These features should be:
 easy to extract
 related to human perception
 good for computing similarity
 Most work uses low-level visual feature
 Module 4

 Color Features
 Color is the most popular feature for image retrieval.
 It is independent of image size and orientation.
 Usually represented using a color histogram:
 Counts how many pixels belong to each color range
 Based on three channels: RGB (Red, Green, Blue)

Object Recognition in Images

 Object recognition = identifying real-world objects in an image/video, even if:

 viewpoint changes
 size changes
 rotation occurs
 illumination varies
 A. Speech Analysis
 Often uses speech recognition to convert speech → text.
 Text makes indexing easier
 Metadata examples:
◦ length
◦ number of speakers
◦ content summary
 Some metadata is content-independent (e.g., file format)
 This is called text-based indexing.

Music Analysis

 Based on audio signal statistics → content-based indexing.

 Important sound features:
 Intensity
 Pitch
 Timbre
 Rhythm

6. Explain the Deductive database.

A deductive database is a database system that supports deduction of new facts using
logical rules.
It combines:

 Relational databases (store data as relations)

 Logic programming concepts, mainly Prolog and Datalog

It uses a declarative language—we specify what we want, not how to compute it.

The system contains an inference engine that automatically derives new facts from existing
facts + rules.

Deductive databases use two kinds of specifications:

 Module 4

a) Facts

 These are basic, stored information.

 Comparable to tuples in relations.
 Example:
SUPERVISE(franklin, john).

(b) Rules

 Used to derive new information.

 Similar to relational views, but can include recursion.
 Example of rule:
SUPERIOR(X, Y) :- SUPERVISE(X, Y).

3. Prolog and Datalog Notation

Predicate Structure

 Written as: p(a1, a2, …, an)

 Lowercase → constants
 Uppercase → variables

Fact example:
SUPERVISE(franklin, ramesh).

Query example:
SUPERIOR(james, Y)?

Rules format

Head :- Body.
Meaning: If Body is true → Head is true.

Example (recursive):
SUPERIOR(X, Y) :- SUPERVISE(X, Z), SUPERIOR(Z, Y).

This finds all direct + indirect subordinates

Rules allow knowledge that cannot be stored as basic relations.

Example:
To find all superiors (at all hierarchy levels), recursion is needed:

1. Direct case
SUPERIOR(X, Y) :- SUPERVISE(X, Y).
2. Indirect case (recursive)
SUPERIOR(X, Y) :- SUPERVISE(X, Z), SUPERIOR(Z, Y).

This lets the system deduce multi-level hierarchical relationships automatically.

 Module 4

Clausal Form and Horn Clauses

Deductive databases use Horn clauses, a restricted and efficient form of logic rules.

Horn clause structure

 At most one positive literal per clause.

 Can be transformed into rules:

P1 AND P2 AND … AND Pn ⇒ Q

Clause:

Datalog form:
Q :- P1, P2, …, Pn.

These are computationally efficient and form the basis of Datalog programs.

Interpretation of Rules

There are three interpretations:

(a) Proof-theoretic Interpretation

 Treats rules as axioms.

 Uses theorem proving to derive new facts.
 Example: Deriving
SUPERIOR(james, ahmad)
using multiple rule applications.

(b) Model-theoretic Interpretation

 Assigns truth values to all possible argument combinations.

 A “model” satisfies all rules.
 The minimal model contains only facts that are necessarily true.

(c) Computational / Inference Engine Interpretation

 Used by practical systems.

 The inference engine (bottom-up for Datalog, top-down for Prolog) tells how results
are computed.

. Datalog Programs and Safety

 A Datalog program = set of rules + facts

 A rule must be safe → must generate only finite results.
 A variable is limited if it appears:
1. In a regular predicate in the body,
2. In comparisons (e.g., X = 5), or
3. Equal to another limited variable
 Module 4

Unsafe example (infinite results):

BIG_SALARY(Y) :- Y > 60000.

Safe version:
BIG_SALARY(Y) :- EMPLOYEE(X), SALARY(X, Y), Y > 60000

6. How do you incorporate time in relational database using attribute versioning?

Attribute versioning stores all historical values inside one complex object, instead of
creating multiple tuples.

 A single object/tuple represents the real-world entity.

 Only the attributes that change over time are versioned.
 Each version of a time-varying attribute stores:
 <Valid_start_time, Valid_end_time, Value>
 Attributes that do not change are stored normally.

Thus, time is incorporated by adding time-periods to the attributes that vary.

Types of Attributes

a) Time-Varying Attributes

These attributes change over time.

Examples: Salary, Department, Supervisor.

They are stored as lists of versions, each with its own valid-time period.

(b) Non-Time-Varying Attributes

These attributes do not change over time.

Examples: Name, Ssn.

Possible ODL schema for a temporal valid time EMPLOYEE_VT object class using attribute
versioning

(non-time varying attribute)

class TEMPORAL_SALARY {

attribute Date Valid_start_time;

attribute Date Valid_end_time;

attribute float Salary;

};

Time is incorporated by creating a list of versions for each time-varying attribute.

 Module 4

Structure of a version entry:

<Valid_start_time, Valid_end_time, Value>

For example, a salary attribute might have:

<2019-01-01, 2020-05-31, 30000>

<2020-06-01, 2022-12-31, 35000>
<2023-01-01, now, 40000>

Lifespan Attribute (Object-Level Time)

To incorporate time for the whole object, an additional attribute called lifespan is used:

<Valid_start_time, Valid_end_time>

This tells when the object exists.

Incorporating Bitemporal Time

For bitemporal databases, each version stores five components:

<Valid_start_time, Valid_end_time,
Transaction_start_time, Transaction_end_time,
Value>

This allows:

 Valid time tracking

 Transaction time tracking
 Full temporal querying (AS OF, BEFORE, EVOLVED, etc.)

Lifespan

<2019-01-01, now>

Whenever a change occurs:

 The current version is closed (end time set)

Incorporating Bitemporal Time

For bitemporal databases, each version stores five components:

<Valid_start_time, Valid_end_time,

Transaction_start_time, Transaction_end_time, Value>

This allows:
 Module 4

Valid time tracking

Transaction time tracking

Full temporal querying (AS OF, BEFORE, EVOLVED, etc.)

Advantages of Attribute Versioning

 No duplication of all attributes (unlike tuple versioning)

 Efficient storage for objects with many stable attributes
 Allows asynchronous changes (only one attribute may change)
 Supports valid-time, transaction-time, and bitemporal histories

[Link] are two specification used by deductive database.

A deductive database combines the capabilities of traditional relational databases with the
inferencing power of logic programming. Instead of storing only data like relational databases,
deductive databases also store logical rules that allow the system to infer new facts automatically.
According to the textbook, a deductive database uses two main types of specifications: Facts and
Rules. Together, these form the logical foundation of the database.

1. Facts (Base Specifications)

Facts represent the basic stored information of the database. They correspond to the actual
data stored in a relational table.

(a) Definition

 A fact is a statement that is always considered true.

 Facts correspond to ground atomic formulas, meaning they contain no variables.
 They represent tuples in a relation, but without attribute names (positions determine
meaning).

(b) Representation

Facts are written using predicate names followed by constant arguments.

Example from textbook:

SUPERVISE(franklin, john).
SUPERVISE(franklin, ramesh).
SUPERVISE(jennifer, alicia).

In the above example:

 SUPERVISE is a predicate name.

 Each fact represents a tuple in a relation SUPERVISE(Supervisor, Supervisee).
 Module 4

(c) Characteristics

 They form the base relations of the deductive database.

 All facts are assumed to be true without requiring further justification.
 A fact’s meaning depends on:
o the predicate name, and
o the position of values within the predicate.

Example:
SUPERVISE(franklin, john) means Franklin supervises John only because of argument
positions.

(d) Purpose

Facts provide the foundation on which the inference engine applies rules to derive new
information.

2. Rules (Derived Specifications)

Rules specify how new information can be inferred from existing facts. They correspond to
virtual relations, similar to relational views, but more expressive.

(a) Definition

 A rule specifies a logical relationship using the form:

Head :- Body.
 The rule means: Head is true if the Body is true.
 Rules are used to compute new facts from existing ones.

(b) Structure of a Rule

A rule has:

 Head: A predicate being defined

 Body: One or more predicates that must be true

Example from textbook:

SUPERIOR(X, Y) :- SUPERVISE(X, Y).

SUPERIOR(X, Y) :- SUPERVISE(X, Z), SUPERIOR(Z, Y).

(c) Meaning

 Rule 1: Direct supervision

If X supervises Y, then X is superior to Y.
 Rule 2: Recursive rule
If X supervises Z and Z is superior to Y, then X is superior to Y.
 Module 4

(d) Recursion

Rules may include recursion, allowing deductive databases to express:

 hierarchical relationships
 transitive closure
 multi-level inference

This makes them more expressive than simple SQL views.

(e) Interpretation

Rules allow the inference engine to:

 generate new facts

 answer complex queries
 support reasoning

3. How Facts and Rules Work Together

 Facts are stored data (like relational tuples).

 Rules generate new factual information based on logic.
 The inference engine uses facts + rules to answer queries.

Example query:
SUPERIOR(james, Y)?
The system uses both stored facts and recursive rules to compute all subordinates of James.

This combination allows deductive databases to:

 perform logical reasoning

 handle recursive queries
 support complex relationships that SQL cannot easily express

9. Explain the major design and implementation issues involved in Active

Databases and Trigger Management.

Active databases extend traditional DBMSs by supporting ECA (Event–Condition–Action)

rules, commonly implemented as triggers. Although triggers provide powerful automation,
they introduce several important design and implementation issues. These issues must be
addressed to ensure correct, predictable, and safe behavior of active rules.

The major issues are explained below.

1. Rule Activation, Deactivation, and Rule Grouping

Active databases must allow:

 Module 4

 Activating a rule (making it operational)

 Deactivating a rule (preventing it from firing temporarily)
 Dropping a rule (permanently removing it)

A deactivated rule does not respond to triggering events, which is useful for:

 Testing
 Maintenance
 Temporary suspension of rules

Rules may also be organized into rule sets, allowing bulk activation, deactivation, or
deletion. Some systems support a PROCESS RULES command to explicitly invoke a rule or
a rule set.

2. Choice of Trigger Execution Time (Before, After, Instead Of)

Active databases must specify when the rule action should run relative to the triggering event:

(a) BEFORE triggers

Executed before the event.

Used for:

 Constraint checking
 Validating data
 Preventing illegal operations

(b) AFTER triggers

Executed after the event.

Used for:

 Logging
 Auditing
 Maintaining derived data

(c) INSTEAD OF triggers

Executed instead of the triggering event.

Used for:

 Updating views
 Redirecting operations to underlying base tables.
 Module 4

3. Coupling Modes: When to Evaluate Conditions and Execute Actions

A trigger has two important phases:

1. Condition Evaluation (Rule Consideration)

2. Action Execution

Both phases can be:

 Immediate
 Deferred
 Detached

(A) Condition Evaluation Options

1. Immediate Consideration
o Condition is evaluated within the same transaction.
o Variants:
 Before the event
 After the event
 Instead of the event
2. Deferred Consideration
o Condition is evaluated at end of transaction (before COMMIT).
o Used in systems like STARBURST.
3. Detached Consideration
o Condition is evaluated in a separate transaction.

(B) Action Execution Options

 Typically immediate in most commercial systems.

 But theoretically can be:
o Immediate
o Deferred
o Detached

Oracle uses:

 Immediate consideration
 Immediate execution
 Allows BEFORE and AFTER triggers.

STARBURST uses:

 Deferred consideration
 Actions run after COMMIT.
 Module 4

4. Row-Level vs Statement-Level Triggers

SQL operations may affect multiple rows, creating design challenges.

Row-Level Triggers

 Fired for each row affected.

 Access to OLD and NEW values for each tuple.
 Useful for detailed validation and constraint checking.

Statement-Level Triggers

 Fired once per SQL statement, regardless of number of rows affected.

 Used for:
o Logging
o Complex integrity conditions
o Performance efficiency

SQL-99 and Oracle allow both types; STARBURST supports statement-level triggers only.

5. Trigger Interactions and Termination Problems

Triggers can call other triggers, resulting in:

 Cascading activations
 Recursive triggering
 Infinite loops
 Non-termination