Temporal Entity-Relationship Model

This is an extension of ER-Diagrams to Temporal Databases.

Example:

  • td-er-temp-ex1.png
  • temporality can be added to attributes, entities and relationships


Translation to Relational Schema

Entities and Lifecycles

td-er-temp-entity.png

If an entity is temporal, we create an additional table EntityLifecycle

  • where we keep all changes
  • a lifecycle shows the state at some period of time of this entity

Employee

Name BirthDate Address Salary Projects
Peter 8/9/64 Rue de la Paix 5000 {MADS, HELIOS}

EmployeeLifecycle

Name FromDate ToDate Status
Peter 7/94 6/96 Active
Peter 7/96 6/97 Suspended
Peter 7/97 6/98 Active


Note that there could be several type of lifecycle tables

  • Continuous (not intermittent)
  • Discontinuous (can be intermittent and after a while continued)
  • td-continious.png


Temporal Attributes

Translating attributes

  • If there's a clock then we keep the history of all changes to this attribute
  • If not - the attribute is not temporal and we keep only the current version


td-er-temp-atr.png

  • Each temporal attribute is modeled with a separate table


Employee

Name BirthDate
Peter 8/9/64

EmployeeLifecycle

Name FromDate ToDate
Peter 7/94 7/98

EmployeeAddress

Name Address FromTime ToTime
Peter Bd St Germain 1/85 12/87
Peter Bd St Michel 1/88 12/94
Peter Rue de la Paix 1/95 now

EmployeeSalary

Name Salary FromTime ToTime
Peter 4000 7/94 7/95
Peter 5000 8/95 now

EmployeeProjects

Name Projects FromTime ToTime
Peter {MADS} 7/94 8/95
Peter {MADS, HELIOS} 9/95 now


td-er-temp-atr2.png

  • there's a clock for the manager - so it's temporal
  • when updating manager - adding an element to the manager history
  • when updating project.name - just update the name
  • when updating the project - just update the name, but start a new history for manager


Generalization

Temporality is inherited

td-er-temp-inheritance1.png

  • Temporary and Permanent are implicitly temporal
  • they inherit temporality from Employee but use the lifecycle of Employee (don't have their own)

td-er-temp-inheritance2.png

  • but in this case Student and Faculty have their own lifecycles


Relationships

td-er-temp-rel.png

  • note that the one-to-many relationship is temporal
  • and it has a temporal attribute

Employee

Name BirthDate Address
John 3/7/55 Bd Haussman
Peter 8/10/64 Rue de la Paix

WorksOn

Employee Project Hours
John HELIOS
30 x/x/x x/x/x
John MADS
25 x/x/x x/x/x
35 x/x/x x/x/x
Peter MADS
25 x/x/x x/x/x
35 x/x/x x/x/x

Project

Name Manager Budget
MADS Christine 5000
HELIOS Yves 6000
  • lifecycles for Employee, WorksOn and Project are not shown here
  • if WorksOn wasn't temporal, it wouldn't have a lifecycle
  • same for Employee and Project


Sources