The Schema

Rather than defining one particular temporal relational database schema, we define instead a database schema using the ER Model. The advantage of this approach is that the ER schema described here may subsequently be mapped to a specific relational database schema in a way that is appropriate for the particular data model at hand. No single relational version of the ER schema fits all temporal relational data models.

The database schema is defined by the ER schema in Figure , containing three entity sets, namely Emp, Skill, and Dept, describing employees, skills, and departments, respectively. The attributes of the entity sets, and their interrelationships, are described next.

Figure: ER Diagram of Database Design

Entities in entity set Emp are represented by the attributes Name and Salary which record the names and salaries of employees. In addition, attributes Gender and D-birth indicate the gender and date of birth of employees. While the name and salary of an employee vary over time, both the gender and the date of birth are assumed to be time-invariant.

Skills, in entity set Skill, are represend by a single attribute, Name, which records the names of individual skills. The name of a skill is time-invariant. Entity sets Skill and Emp are connected via an n–m relationship set, EmpSkills. The skills of an employee may vary over time. For example, employees are considered to have the skill ``driving'' only during those interval(s) when they hold valid licenses.

The entity set Dept represents departments and is described by the attributes Name and Budget which record the names and budgets of departments, respectively. While the budget of a department varies over time, the name is assumed to be time-invariant. Employees are associated with departments by means of two relationship sets. First EmpDept records which employees work in which departments. This is a time-varying n–1 relationship set. Second MgrDept, also time-varying, is a 1–1 relationship set associating those employees that are managers with the departments they manage.

Attributes Name (of Emp, Skill, and Dept) are of type textString; attribute Gender is one of F (female) and M (male); Salary and Budget are of type integer; and D-birth is a user-defined time value which may be compared with valid times.

The entity sets obey the following snapshot functional dependencies:

For Emp: 

Name 
→ Salary 

Name 
→ Gender 

Name 
→ D-birth 


For Dept: 

Name 
→ Budget

Note that Name is the primary key of Emp (it is the only candidate key). For Skill, Name is the only attribute and is thus the key. For Dept, Name is the primary key.

It is emphasized that the notion of key does not capture correspondence between attribute values and the real-world objects they represent. As one consequence, it is possible in this ER schema, e.g., for an employee to change Name attribute value over time.

This concludes the description of the ER schema. Next, we exemplify how the ER schema may be mapped to a relational database schema. Figure  shows one such possible schema. As for the ER schema, the valid-time aspect of this schema is implicit.

Emp = (Id, Name, Salary, Gender,D-birth,

DeptName) 

Skill = (EmpId, Name) 

Dept = (Name, Budget, EmpId)

Relation Emp models the entity set Emp and the relationship set EmpDept. Attribute Id is a time-invariant key (i.e., values of this attribute identify employees). Relation Skill models the relationship set EmpSkills and the entity set Skill. Values of attribute EmpId in Skill, as well as in Dept, identify employees. Finally, relation Dept models relationship set MgrDept and entity set Dept.

In this design, Name is the snapshot primary key of Emp (it is the only candidate key). For Skill, there is no non-trivial key. For Dept, each of Name and EmpId is a candidate key, and Name is selected as the primary key.

Each of the relation schemas are in snapshot Boyce-Codd normal form.