Data model

A relational data model is employed. An entity-relationship (ER) diagram is shown in Figure 1. The data is stored locally in a SQLite database (file cranio.db).

_images/er_diagram.png

Figure 1. Entity-relationship diagram

Tables

The database tables are described in this section.

dim_session

Every time the application is launched an entry to the dim_session table is generated. The launch date and time is stored in column started_at. Application version string is stored in column sw_version.

dim_patient

Pseudonymized patient information. The patients are entered to the database from the application. The person entering the patient_id is responsible for pseudonymization. The creation date and time is stored in column created_at.

dim_document

Every time the Start button is clicked an entry to the dim_document table is generated. The following columns are also entered at this time:

  • session_id: Active session
  • patient_id: Active patient (operator selects from main window)
  • sensor_serial_number: Active sensor (operator selects from main window)
  • distractor_id: Active distractor (operator selects from main window)
  • started_at: Measurement start date and time
  • operator: Active operator (operator enters in the main window)

The following columns are updated once the event detection has finished:

  • notes: Measurement notes (operator enters in the notes window)
  • full_turn_count: Number of performed full turns (operator enters in the notes window)
  • missed_distractors: TODO: Is this really needed?
  • distraction_plan_followed: True/False indicating if the distraction plan was followed (operator enters in the notes window)

dim_hw_sensor

Sensor hardware information. The sensor_serial_number is automatically detected from the connected sensor. Column turns_in_full_turn is the sensor-specific number of turns in one full turn.

dim_hw_distractor

Distractor hardware information. Column displacement_mm_per_full_turn describes the amount of displacement generated from one full distractor turn and is determined during hardware calibration.

fact_annotated_event

Annotated event information. The user selects regions from the measured time-torque data during event detection. Column descriptions:

  • event_type: Event type identifier (e.g., D for distraction)
  • event_num: Event enumerator determined from the temporal location of the event with respect to other events in the document
  • document_id: Document to which the measurements belong to
  • event_begin: Left region boundary
  • event_end: Right region boundary
  • annotation_done: True/False indicating if the annotation has been done (operator enters during event detection). If False, the operator shall perform the annotation at a later time.
  • recorded: True/False indicating if the data was recorded for the event (operator enters during event detection)

dim_event_type

Event type lookup table. Populated with the following values during database initialization:

  • {event_type: "D", event_type_description: "Distraction event"}

fact_measurement

Measured torque_Nm (Nm) and time_s (s) values. Column defines document_id the document during which the data was recorded.

fact_log

Software log information. Columns:

  • log_id: autoincrement integer
  • session_id: session during which the log entry was generated
  • created_at: log entry date and time
  • logger: logger name
  • level: logging level
  • trace: error traceback
  • message: log message

dim_log_level

Log level lookup table. Populated with the following values during database initialization:

  • {level: 50, level_name: "CRITICAL"}
  • {level: 40, level_name: "ERROR"}
  • {level: 30, level_name: "WARNING"}
  • {level: 20, level_name: "INFO"}
  • {level: 10, level_name: "DEBUG"}
  • {level: 0, level_name: "NOTSET"}

Example SQL queries

Calculate average torque for all events:

SELECT patient_id, b.document_id, b.started_at, distractor_id, event_num, event_type, AVG(a.torque_Nm) as `avg. torque` FROM fact_measurement as a
INNER JOIN dim_document as b, fact_annotated_event as c USING (document_id)
INNER JOIN dim_event_type as d USING (event_type)
WHERE a.time_s > c.event_begin AND a.time_s < c.event_end
GROUP BY c.event_num;