Temporal Databases – Detailed Notes
1. Introduction to Temporal Databases
● A temporal database is a database that supports handling of data involving time
aspects.
● Unlike conventional databases (which store only the current state), temporal databases
store historical data, present data, and sometimes future data.
● Example: Employee salary records over years, patient medical history, flight schedules.
Two main types of time:
1. Valid Time (VT): The period when a fact is true in the real world.
Example: Employee’s promotion valid from 01-Jan-2022 to 31-Dec-2022.
2. Transaction Time (TT): The period when a fact is stored in the database.
Example: The same promotion entered into DB on 05-Jan-2022.
3. Bitemporal Data: Contains both VT and TT.
2. Time Ontology, Structure, and Granularity
(a) Time Ontology
● Describes how time is perceived and structured in databases.
● Two main views of time:
1. Linear time: Time is a sequence of points, moving forward continuously. (Most
common model).
2. Branching time: Time can split into multiple possible futures (used in
simulations or "what-if" analysis).
(b) Structure of Time
● Time is generally modeled as an ordered set of points.
● Two approaches:
○ Point-based: Each moment in time is represented as a single point (e.g., 2025-
09-27 10:30:00).
○ Interval-based: Time is represented by intervals with start and end points (e.g.,
2025-01-01 to 2025-12-31).
(c) Time Granularity
● Refers to the level of detail at which time is represented.
● Examples of granularities:
○ Years → Months → Days → Hours → Minutes → Seconds → Milliseconds
● Databases must support conversion between granularities, e.g., summarizing daily
sales into monthly sales.
● Operations like upscaling (coarsening) and downscaling (refining) help switch
between granularities.
3. Temporal Data Models
Temporal data models extend traditional data models to include time dimensions.
(a) Conceptual Temporal Models
● Extend ER (Entity-Relationship) models with temporal aspects.
● Example: In a temporal ER model:
○ Entities can have temporal attributes (e.g., employee’s address with valid-time).
○ Relationships can be time-dependent (e.g., employee works in department
during certain years).
(b) Logical Temporal Models
● Based on relational, object-oriented, or NoSQL models.
● Common relational extensions:
Tuple Timestamping: Each tuple includes time attributes (start and end time).
Example:
Employee (EmpID, Name, Salary, VT_start, VT_end)
Attribute Timestamping: Each attribute is timestamped separately.
Example:
Employee (EmpID, Name(VT_start, VT_end), Salary(VT_start, VT_end))
(c) Types of Temporal Data Models
1. Valid-Time Model: Captures real-world time of facts.
2. Transaction-Time Model: Captures when facts are stored in DB.
3. Bitemporal Model: Combines both valid-time and transaction-time.
→ Useful for auditing, rollback, and history tracking.
4. Temporal Relational Algebra (TRA)
TRA is an extension of classical relational algebra to handle temporal aspects.
(a) Extensions of Basic Operators
Selection (σ): Extended to include time conditions.
Example:
σ Salary > 50000 AND VT CONTAINS '2024' (Employee)
Projection (π): Can project both attributes and temporal components.
Example:
π EmpID, Name, VT_start, VT_end (Employee)
Join (⋈): Temporal join requires overlapping time intervals.
Example:
Employee ⋈ Department
where [Link] overlaps [Link]
●
● Union / Intersection / Difference: Performed with consideration of time intervals.
● Aggregate Functions: Extended to time (e.g., SUM of sales per month).
(b) New Temporal Operators
● Slice: Extracts tuples valid during a particular time.
Example: Get employee records valid in Jan 2025.
● Shift: Shifts temporal values by a duration.
Example: Extend contract by 6 months.
● Coalesce: Merges overlapping/adjacent intervals into one.
(c) Example
Consider table:
Employee_Salary(EmpID, Salary, VT_start, VT_end)
Query: Find salary of employee 101 in 2023
σ EmpID = 101 AND [2023-01-01, 2023-12-31] OVERLAPS (VT_start, VT_end)
(Employee_Salary)
5. Applications of Temporal Databases
● Financial systems → tracking stock prices over time.
● Medical systems → storing patient treatment history.
● GIS & Environmental data → analyzing climate change trends.
● Legal & auditing → compliance with historical record keeping.
Q)
1) Define a temporal database. Explain the concepts of valid time, transaction time, and
bitemporal data with suitable examples showing how they differ in representing time-based
information.
2) What is time ontology in temporal databases?
Differentiate between linear and branching time models and describe their practical
applications in areas like forecasting or simulations.
3) Design a temporal table to store employee salary history using tuple timestamping.
Write an example query to display the salary of a specific employee during the year 2024 and
explain how temporal attributes support this query.
4. Create a temporal relational schema for a hospital database to track patients’
admission and discharge details over time.
Illustrate the use of a temporal join to retrieve patients who were admitted under a
specific doctor during a particular month.
5.
Explain the following w.r.t temporal database.
i) Tme ontology
ii)Structure of time
iii) Time granularity