Chapter 3 Data Management For Structural Vibration Mon-
3.3 Database Architecture
A relational database was chosen to store and access the experimental data, using MySQL as the backend [24]. The enhanced entity relationship diagram is presented in Figure 3.1. This database structure is used at the site where data is collected in the base stations and in the main database. The following paragraphs describe the tables in more detail, and are organized in alphabetical order for easy reference. The term ‘event’ means the monitoring system was triggered to record accelerations from its sensors thus indicating one or more recorded signals, and the term ‘hit’ means an impact present in a signal.
sensor_mac CHAR(16) PK date DATETIME PK z_offset FLOAT(5,1) z_scale FLOAT(5,1) calibration sensor_mac CHAR(16) PK date DATETIME PK vertical_axis TINYTEXT data LONGTEXT system TINYTEXT system_activation DATETIME accel_events mac CHAR(16) PK system CHAR(7) PK date DATETIME PK parameter CHAR(50) PK value TINYTEXT system_log name VARCHAR(255) PK description LONGTEXT
parameters system CHAR(7) PK
event_date DATETIME PK mac CHAR(16) PK parameter VARCHAR(255) FK value VARCHAR(255) event_parameters system CHAR(7) PK date DATETIME PK name TINYTEXT from_date DATETIME to_date DATETIME downloads date DATETIME PK location VARCHAR(45) PK type VARCHAR(45) comments LONGTEXT system CHAR(7) events Related One to Many PK = Primary Key FK = Foriegn Key
Figure 3.1: Enhanced Entity Relationship Diagram for Human Activity Database
accel_events Here, all the data collected from the sensors is stored including
information about the orientation of the sensor and the system the sensor belongs to. Each table row has a system and system_activation for referencing items stored in the system_log table. Knowing when the system was activated allows one to grab information from the log pertaining to a specific operational period of the system. The acceleration data captured is first converted into units of gravity through the calibration table, and then, is stored as a space delimited string in the field data.
calibration The Agua Mansa sensors report data to the base station in pts/g,
where the data needs to be converted into units of acceleration. This table stores the calibration information with the base station using the latest dated calibration values for unit conversion. Sensor Media Access Control Addresses (MAC Addresses) must be available in the calibration table before data is recorded into theaccel_event table. Base station programs enforce this policy. For more information on calibration and conversion formulas see Section 2.3.1.
downloads This table is used to track when data from a system for a specific
time period has been transfered from the base station to the SDII servers. When downloading data, the user enters his name or, in the case of the web service, the name
webservice is entered. Subsequent downloads of data from a base station reference
the table to reduce the amount of data to transfer in each download, and thus the amount of time it takes for the download process. This is used in the base station for use with the Data Mobile Ubiquitous LAN Extension (MULE), and the main server for use with the web service (see Section 2.3.1 for more details).
events All reported human-induced vibration events are recorded here with in-
formation on the system that saw the event, the date and time the event occurred, comments from the reporter, and the location of installation. No patient information
(e.g. name, weight) is stored to protect privacy. The fieldtype intentionally is flexible if other significant human activity besides a human fall is reported as this can be of interest for refining fall detection algorithms or research into other activity.
event_parameters Acceleration events have various parameters, or metrics, that
can be used to describe them. Calculated metrics from the parameters table are
stored in this table for signals present in accel_events. This includes parameters
like maximum amplitude and signal category. Access the parameters database table
for a current list of available metrics created by this research and their descriptions. Table 3.1 provides the parameters available at the time of this writing.
parameters The parameter table defines parameters and removes the chance that
a it can be misspelled. Other tables reference this one via foreign keys to ensure integrity. Table 3.1 describes the parameters, available as of 2016 February 1, in alphabetical order by their database names.
Table 3.1: Acceleration Parameters
Name DB Name Description
Amplitude Hits amphit Amplitude within the record (g);
There is one number per hit
Maximum Amplitude ampmax Max of the absolute value of the
signal (g); See Section 4.2.2
Manual Category category-manual Category of signal manually clas-
sified: (0) not sure what category, (1) noise, (2) some activity but in- distinct peaks or shape, (3) dis- tinct peaks and shape, (4) sensor error; Used for training an SVM
Name DB Name Description
SVM Category category-svm Category of signal created by the
SVM: (0) not sure what category, (1) noise, (2) some activity but in- distinct peaks or shape, (3) dis- tinct peaks and shape; See Chap- ter 4
Dispersion Ratio dr The ratio of desired signal to envi-
ronmental noise; See Section 4.2.5
Error errornorm Normalized error of the exponen-
tial curve
Location location Location of the event; For exam-
ple, a specific room in a house Max Amplitude
Difference mad The max change between descend-ing sorted, absolute values of the signal (g); NaN are ignored; See Section 4.2.3
Accepted Hits naccept Number of hits accepted during
analysis
NaN Density nandensity The density of NaN values in the
record; See Section 4.2.1
Number Rejected nreject Number of potential hits rejected
during analysis
Rate of Dispersion rod How much dispersion is present
within a signal; See Section 4.2.4
Signal Energy senergy Energy present in a signal process-
ing sense (g2s); See Section 2.8.1
Hit Time thit Time of impacts within the record
(in seconds since the beginning of the record); There is one number per hit
Natural Freq. and
Damping wnzetahit Estimation of ωnζ for each hit
system_log The system log tracks activity of the system, including errors and
installation dates, during a system’s operation. This is for both the base station and the sensors themselves. Base stations (i.e. the laptops) store a blank string, 0, or their MAC Addresses for the fieldmac. Sensors store in the log using their own MAC Addresses so parameters such as reliability or actual sampling rate can be determined (see Chapter 2). The table does not reference another table by foreign key unlike that
of the parameter and event_parameter table relationship. This allows for maximum
flexibility when programming the system so that new log info can be added at will. Thus, parameter names in this table tend to be more verbose and self-descriptive for clarity. Table 3.2 describes the parameters in the system logs, as of 2016 February 1, in alphabetical order of their database names.
Table 3.2: Parameters in the System Log Table
DB Name Description
data_received Number of data points received from
the sensor in the time span er- ror_log_length
error_log_length Length of time between system log
saves (s); Saved when system starts
installed Indicates system installed, reference
the date of the log for when the install occurred
lost_sync Number of times the base station lost
synchronization with a sensor in the time span error_log_length
malformed_data Number of bad data packets received
by the base station for the sensor in the time span error_log_length
DB Name Description
malformed_sync Number of bad synchronization pack-
ets received by the base station for the sensor in the time span er- ror_log_length
maximum threshold Indicates the maximum threshold for
the bandpass acceleration threshold (pts/g); Saved when system starts
minimum threshold Indicates the minimum threshold for
the bandpass acceleration threshold (pts/g); Saved when system starts
overflow Number of times the accelerometer
overflowed the sensor’s buffer in the time span error_log_length
reset Number of times a sensor reset itself
in the time span error_log_length
sample_rate The expected sample rate; Saved when
system starts
soft_version Base station software version; depre-
cated
suite_version System Tool Suite Version; Saved
when system starts
sync_received Number of synchronization packets re-
ceived by the base station for the sen- sor in the time span error_log_length
threshold Acceleration threshold to cross for ac-
tivation (pts/g); Saved when system
starts
trigger_count Number of times a sensor was
triggered in the time span er- ror_log_length
trigger_level Acceleration value that triggered the
sensor (pts/g)
uninstall Indicates the end of a system’s instal-
lation period, reference the date of the log for when the install occurred
DB Name Description
webservice-accel_events-duplicates Number of duplicate acceleration
event records the web service tried to upload
webservice-accel_events-failure Indicates the web service failed to up-
load acceleration events
webservice-accel_events-sent Number of acceleration event records
the web service sent
webservice-accel_events-timeout Number of timeouts the web service
had when trying to send acceleration event data
webservice-calibration-duplicates Number of duplicate calibration
records the web service tried to upload
webservice-calibration-failure Indicates the web service failed to up-
load calibrations
webservice-calibration-sent Number of calibration records the web
service sent
webservice-calibration-timeout Number of timeouts the web service
had when trying to send calibration data
webservice-downloads-duplicates Number of duplicate download records
the web service tried to upload
webservice-downloads-failure Indicates the web service failed to up-
load downloads
webservice-downloads-sent Number of download records the web
service sent
webservice-downloads-timeout Number of timeouts the web service
had when trying to send downloads data
webservice-system_log-duplicates Number of duplicate system log
records the web service tried to upload
webservice-system_log-failure Indicates the web service failed to up-
load system logs
DB Name Description
webservice-system_log-sent Number of system log records the web
service sent
webservice-system_log-timeout Number of timeouts the web service
had when trying to send system logs data
3.4 Conclusion
The data management plan outlined in this chapter, has worked well over the many years of the human activity through structural vibrations study and provides a foundation for future data curation. Researchers have the infrastructure to handle and analyze the multitude of acceleration records collected, as well as tools for easy access to data while mitigating the possibility of accidental changes in the data. Identifiable participant information is not recorded, providing privacy and promoting research towards user-independent fall detection. The flexibility and scalability of this plan will provide valuable research infrastructure for years to come.