Employee Database UseCase Doc

Employee Database Schema:

Where the schema comes from:

The schema evolution of employee database is from paper Managing and Querying Transaction-time Databases under Schema Evolution

Queries from prima paper

Where the data set comes from:

The data set in MySql format is from https://github.com/datacharmer/test_db

The syntax of table name:

• v[x]_[table name]: the table in version x
• v_[table name]: the table in variational database

Schema:

• Plain database schema
  • schema version 1
    • v1_engineerpersonnel (empno, name, hiredate, title, deptname)
    • v1_otherpersonnel (empno, name, hiredate, title, deptname)
    • job (title, salary)
  • schema version 2
    • v2_empacct (empno, name, hiredate, title, deptname)
    • job (title, salary)
  • schema version 3
    • v3_empacct (empno, name, hiredate, title, deptno)
    • job (title, salary)
    • dept (deptname, deptno, managerno)
  • schema version 4
    • v4_empacct (empno, hiredate, title, deptno)
    • job (title, salary)
    • dept (deptname, deptno, managerno)
    • v4_empbio (empno, sex, birthdate, name)
  • schema version 5
    • v5_empacct (empno, hiredate, title, deptno, salary)
    • dept (deptname, deptno, managerno)
    • v5_empbio (empno, sex, birthdate, firstname, lastname)
• variational database
  • v_engineerpersonnel (empno, name, hiredate, title, deptname, presCond)
  • v_engineerpersonnel (empno, name, hiredate, title, deptname, presCond)
  • v_job (title, salary, presCond)
  • v_empacct (empno, name, hiredate, title, deptname, deptno, salary, presCond)
  • v_dept (deptname, deptno, managerno, presCond)
  • v_empbio (empno, sex, birthdate, name,lastname,firstname, presCond)

V-Schema for Employee UseCase

Note that elem == elem^True (Element has feature expression as True, we omit True here.

• v_engineerpersonnel^v1(empno, name, hiredate, title, deptname)
• v_otherpersonnel^v1 (empno, name, hiredate, title, deptname)
• v_empacct^{v2 or v3 or v4 or v5}(empno, name^{v2 or v3}, hiredate, title, deptname^v2, deptno^{v2 or v3 or v5}, salary^v5)
• v_job^{v2 or v3 or v4} (title, salary)
• v_dept^{v3 or v4 or v5} (deptname, deptno, managerno)
• v_empbio^{v4 or v5} (empno, sex, birthdate, name^v4, lastname^v5,firstname^v5)

Feature Model

( v1 and not v2 and not v3 and not v4 and not v5) or

(not v1 and v2 and not v3 and not v4 and not v5) or

(not v1 and not v2 and v3 and not v4 and not v5) or

(not v1 and not v2 and not v3 and v4 and not v5) or

(not v1 and not v2 and not v3 and not v4 and v5)

Populate the database

Divided employees into 5 variants based on their hire date.

Option 1: Copy data of old version to new version

• variant1: employees whose hire_date < '1988-01-01'
  • total number: 104967
• variant2: employees whose hire_date < '1991-01-01'
  • total number: 190407
• variant3: employees whose hire_date < '1994-01-01'
  • total number: 251149
• variant4: employees whose hire_date < '1997-01-01'
  • total number: 287673
• variant5: employees whose hire_date < '2000-01-28'
  • total number: 300024

Option 2: Chop the employees(total: 300024) into 5 parts and do not migrate data of old version to new version

• variant1: hire_date < '1988-01-01'
  • total number: 104967
• variant2: '1988-01-01' <= hire_date < '1991-01-01'
  • total number: 85440
• variant3: '1991-01-01' <= hire_date < '1994-01-01'
  • total number: 60742
• variant4: '1994-01-01' <= hire_date < '1997-01-01'
  • total number: 36524
• variant5: hire_date >= '1997-01-01'
  • total number: 12351

Steps taken to populate the employee_vdb based on option2:

• Steps taken to populate v-tables

1. Chop employees into 5 parts (A,B,C,D,E) based on hire_date
2. For v-table v_engineerpersonnel and v_otherpersonnel:
   1. populate employees in A whose title is Enginner into enginnerpersonnel
   2. insert the rest of employees of A into otherpersonnel
   3. The presCond value for these 2 tables shoule be v1
3. For v-table ``v_empacct`:
   1. Employees in B,C,D,E will be inserted into v_empacct

empno	name	hiredate	title	deptname	deptno	salary	presCond
B	xx	xx	xx	xx	xx	NULL	NULL
C	xx	xx	xx	xx	NULL	xx	NULL
D	xx	NULL	xx	xx	NULL	xx	NULL
E	xx	NULL	xx	xx	NULL	xx	xx

4. For v-table v_job, Employee in A,B,C,D,E will be inserted in it.
5. For v-table v_dept, employes in C, D, E will be inserted in it as follows:

deptname	deptno	managerno	presCond
C	xx	xx	xx
D	xx	xx	xx
E	xx	xx	xx

6. For v-table v_empbio, employee in D, E will be inserted in it accordingly:

empno	sex	birthdate	name	firstname	lastname	presCond
D	xx	xx	xx	xx	NULL	NULL
E	xx	xx	xx	NULL	xx	xx

NOTE:

The database is originally created in mySql, and converted to SQLite3 compatible dump by use tool mysql2sqlite.

• The dump file is located in ./VDBMS/databases/employeeDB
  • mySql dump file: emp_vdb.sql
  • sqlite3 dump file:emp_vdb.db