## 4 Cloud Server
### Data Processing Architecture
#### 4.1.3 Database  
The database management system exploited in PROMET&O was MySQL [55], being open source, well documented, and established in the market. Indeed, the project scope did not require extensive scalability and strict performance requirements. With such constraints, timeseries-oriented databases in both SQL and NoSQL flavours, such as Timescale [56] and InfluxDB [57], could be investigated.  
The database schema was designed to support general uses cases of IoT sensor deployment. The Entity Relationship (ER) diagram of the developed database is shown in Figure 9. The _BOARD_ table represents a general IoT device, identified by a serial number and a vendor model linked via a foreign key to the _VENDOR_MODEL_ table. Each IoT device generates multiple timeseries of measurements, each of them identified by a logical sensor from the _LOGICAL_SENSOR_ table. Logical sensors have properties such as acquisition frequency and unit of measure, described in the _UNIT_OF_MEASURE_ table.  
The _MEASURE_ table, which is central in this design, records data acquisition by each logical sensor using tuples in the format (_sensorId, timestamp, data_), with the foreign key being composed of the tuple (_sensorId, timestamp_). This arrangement prioritizes _sensorId_ for its lower cardinality than _timestamp_, speeding up the filtering. To reduce the space disk, the space allocated for storing each measurement should be minimized. Therefore, _sensorId_ is an _UNISGINED_SMALL_INT_ and _data_ is in single precis  
Figure 9: Database ER diagram.  
bytes, respectively. The _timestamp_ is stored as a _DATETIME_ without fractional seconds, occupying five bytes. As the date are always expressed in coordinated universal time (UTC); no timezone conversion occurs during storage or retrieval. To sum up, eleven bytes are required to store each measurement. This optimization, however, imposes constraints on measurement precision, number of logical sensors, and sampling frequency, which can be adjusted by modifying data type sizes.  
The _PHYSICAL_SENSOR_ and _LOGICAL_PHYSICAL_CONNECTION_ tables are used to express the connection between an hardware sensor installed on the device and the produced timeseries (logical sensor). This allows to represent hardware sensors measuring different parameters and the possibility of an hardware sensor being replaced due to a failure, with each new one acting as the same logical sensor.  
For tracking device configurations, the _BOARD_CONFIG_ and _PARAM_TYPE_ tables are used, while the _EXPERIMENT_, _BOARD_EXPERIMENT_, and _BOARD_LOCATION_ tables monitor device usage across measurement campaigns and deployment locations.  
Additional tables, shown in Figure 10, are used to store measurement aggregations computed by the scheduler. The _FIVE_MIN_AVG_MEASURE_ table contains data aggregations for each logical sensor over five minute intervals. The mean, maximum, minimum, and standard deviation of the measurements are computed over the five minute interval and stored inside the table. The _HOUR_AVG_MEASURE_ stores the same information but for hour averages. The _VIEW_UPDATE_ table contains, for each aggregation table, the table name and the timestamp of the last update.