ML Wiki
Machine Learning Wiki - A collection of ML concepts, algorithms, and resources.

Temporal Entity-Relationship Model

databases relational-databases

Temporal Entity-Relationship Model

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

Example:

  • Image
  • temporality can be added to attributes, entities and relationships

Translation to Relational Schema

Entities and Lifecycles

Image

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)
  • Image

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

Image

  • 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 |

Image

  • 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

Image

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

Image

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

Relationships

Image

  • 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

✏️ Edit on GitHub