⭐ INTRODUCTION TO TEMPORAL DATABASES

A Temporal Database is a type of database that manages time-related data. Unlike traditional databases that store only the current state of data, temporal databases store:

Past states
Present state
Future states (in some systems)

This allows users to perform queries based on time, such as:

What was the salary of an employee last year?
Which customers were active on 01-01-2020?
When was a product price changed?

Temporal databases can handle changes over time and keep a complete history of data.

⭐ WHY TEMPORAL DATABASES?

Traditional databases overwrite old data whenever updates occur.
This is a major limitation when:

✔ Historical data must be preserved
✔ Time-based analysis is required
✔ Auditing and legal compliance is needed
✔ Tracking changes is important

Temporal databases solve this by storing not only what data is, but also when it is valid.

⭐ TWO TYPES OF TIME IN TEMPORAL DATABASES

Temporal databases track two kinds of time:

⭐ 1. Valid Time

The time period during which a fact is true in the real world.

Example:
Employee salary from 01-04-2023 to 31-03-2024.

⭐ 2. Transaction Time

The time when the data is stored in the database system.

Example:
Record inserted into DB on 05-04-2023.

✔ Temporal databases may support:

Valid-time only
Transaction-time only
Bi-temporal (both times)

⭐ CHARACTERISTICS OF TEMPORAL DATABASES

✔ Store historical data

Not just current data.

✔ Support temporal queries

Queries specify time intervals.

✔ Time-stamping

Each record has time interval attributes.

✔ Versioning

Multiple versions of the same record are stored.

✔ Non-destructive updates

Old values are preserved, not overwritten.

⭐ TEMPORAL DATA MODEL

To handle time, temporal databases extend the relational model:

✔ Add time attributes:

Valid_From
Valid_To
Transaction_Start
Transaction_End

✔ Support for intervals:

Time intervals represent the lifespan of data.

⭐ TEMPORAL RELATIONAL ALGEBRA & SQL EXTENSIONS

Temporal databases extend SQL to handle time.

Example Queries:

1. Temporal SELECT:
Find employee salary in Jan 2022:

SELECT Salary
FROM Employee_Salary
WHERE EmpID = 101
AND '2022-01-15' BETWEEN Valid_From AND Valid_To;

2. Temporal history query:
Show all past versions of a record:

SELECT *
FROM Employee_Salary
FOR SYSTEM_TIME ALL
WHERE EmpID = 101;

⭐ TEMPORAL DATA TYPES

Many DBMS support these data types:

PERIOD
INTERVAL
TIMESTAMP
TIMESTAMP WITH TIME ZONE

⭐ TYPES OF TEMPORAL DATABASES

✔ 1. Valid-time temporal database

Stores when a fact is valid.

✔ 2. Transaction-time temporal database

Stores when the fact was stored in the DB.

✔ 3. Bi-temporal database

Stores both valid and transaction time.

✔ 4. Snapshot database

Stores only current state, NOT temporal.

⭐ APPLICATIONS OF TEMPORAL DATABASES

Temporal databases are widely used in:

⭐ 1. Banking and Financial Systems

Track account balances historically
Audit trail for loans and transactions

⭐ 2. Healthcare Systems

Patient medical history
Medication timeline

⭐ 3. Business & HR Systems

Employee salary history
Promotions and job changes

⭐ 4. Government and Legal Records

Property ownership history
Tax records
Legislative changes

⭐ 5. Scientific and IoT Data

Temperature readings over time
Environmental monitoring

⭐ 6. E-Commerce & Retail

Price change history
Product availability timeline

⭐ ADVANTAGES OF TEMPORAL DATABASES

✔ Preserves historical data
✔ Supports temporal querying
✔ Maintains audit trails
✔ Ideal for analysis and forecasting
✔ Supports regulatory compliance
✔ Enables time-based analytics

⭐ DISADVANTAGES

✘ More storage required
✘ More complex schema design
✘ Query processing overhead
✘ Managing bi-temporal data is complex
✘ Not supported fully in all RDBMS

⭐ EXAMPLE (MCA EXAM STYLE)

Employee salary history table:

EmpID	Salary	Valid_From	Valid_To
101	30000	2020-01-01	2021-03-31
101	35000	2021-04-01	2022-03-31
101	40000	2022-04-01	2023-03-31

This table stores salary variations over time.

A query like:
“Find employee 101’s salary on 15-Feb-2022” becomes easy.

⭐ Perfect 5–6 Mark Short Answer

A Temporal Database stores time-related data such as past, present, and future states. It attaches time intervals (valid time and transaction time) to records to preserve history and support temporal queries. Temporal databases maintain multiple versions of records, support non-destructive updates, and are widely used in finance, HR, healthcare, government, and trend analysis. They extend the relational model with timestamped attributes to enable accurate time-based information retrieval.

Temporal Databases