· 8 years ago · Mar 08, 2018, 03:36 PM
1-- MySQL dump 10.13 Distrib 5.6.35, for osx10.9 (x86_64)
2--
3-- Host: localhost Database: SensorRepository
4-- ------------------------------------------------------
5-- Server version 5.6.35
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10/*!40101 SET NAMES utf8 */;
11/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
12/*!40103 SET TIME_ZONE='+00:00' */;
13/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
14/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
15/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
16/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
17
18--
19-- Current Database: `SensorRepository`
20--
21
22CREATE DATABASE /*!32312 IF NOT EXISTS*/ `SensorRepository` /*!40100 DEFAULT CHARACTER SET utf8 */;
23
24USE `SensorRepository`;
25
26--
27-- Table structure for table `Attribute`
28--
29
30DROP TABLE IF EXISTS `Attribute`;
31/*!40101 SET @saved_cs_client = @@character_set_client */;
32/*!40101 SET character_set_client = utf8 */;
33CREATE TABLE `Attribute` (
34 `Attribute_Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key of the attribute.',
35 `Attribute_Name` varchar(100) NOT NULL COMMENT 'Name of the Attribute.',
36 `Attribute_Description` varchar(255) NOT NULL COMMENT 'Description of the attribute.',
37 `Attribute_Category_Id` int(11) NOT NULL COMMENT 'Reference to Attribute_Category.Attribute_Category_Id for indicating a category of an attribute.',
38 PRIMARY KEY (`Attribute_Id`),
39 KEY `fk_Attribute_Attribute_Category1_idx` (`Attribute_Category_Id`),
40 CONSTRAINT `fk_Attribute_Attribute_Category1` FOREIGN KEY (`Attribute_Category_Id`) REFERENCES `Attribute_Category` (`Attribute_Category_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
41) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8 COMMENT='This table is to store information about an attribute for a ';
42/*!40101 SET character_set_client = @saved_cs_client */;
43
44--
45-- Dumping data for table `Attribute`
46--
47
48LOCK TABLES `Attribute` WRITE;
49/*!40000 ALTER TABLE `Attribute` DISABLE KEYS */;
50INSERT INTO `Attribute` VALUES (1,'Short Name','Short name of the sensor (wearable, SOC, component)',1),(2,'Long Name','Long name of the sensor (wearable, SOC, component)',1),(3,'Manufacturer','Manufacturer of the sensor (wearable, SOC, component)',1),(4,'Typical Applications','Typical applications for the use of the sensor (wearable, SOC, component)',1),(5,'Type','The type of the Sensor (wearable, SOC, component)',1),(6,'Width','The width of the sensor (wearable, SOC, component)',1),(7,'Length','The length of the sensor (wearable, SOC, component)',1),(8,'Height','The height of the sensor (wearable, SOC, component)',1),(9,'FDA Approved','Conformity to the FDA specifications',1),(10,'Aplication Area Med.','The application Area of a Sensor (wearable, SOC, component)',1),(11,'Last Calibration Date','The last date of calibration of a sensor (wearable, SOC, component)',2),(12,'iOS compatibility','iOS compatibility of the Sensor',1),(13,'Android compatibility','Android compatibility of the Sensor',1),(14,'Bluetooth connectivity','Bluetooth connectivity of the Sensor',1),(15,'Medical field of application 1','First medical field of application of the sensor.',1),(16,'Medical field of application 2','Second medical field of application of the sensor.',1),(17,'Medical device','Information if the sensor is a medical device.',1);
51/*!40000 ALTER TABLE `Attribute` ENABLE KEYS */;
52UNLOCK TABLES;
53
54--
55-- Table structure for table `Attribute_Category`
56--
57
58DROP TABLE IF EXISTS `Attribute_Category`;
59/*!40101 SET @saved_cs_client = @@character_set_client */;
60/*!40101 SET character_set_client = utf8 */;
61CREATE TABLE `Attribute_Category` (
62 `Attribute_Category_Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key of the table.',
63 `Name` varchar(100) NOT NULL COMMENT 'Name of the category.',
64 `Description` varchar(255) NOT NULL COMMENT 'Description of the category.\n',
65 PRIMARY KEY (`Attribute_Category_Id`)
66) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
67/*!40101 SET character_set_client = @saved_cs_client */;
68
69--
70-- Dumping data for table `Attribute_Category`
71--
72
73LOCK TABLES `Attribute_Category` WRITE;
74/*!40000 ALTER TABLE `Attribute_Category` DISABLE KEYS */;
75INSERT INTO `Attribute_Category` VALUES (1,'Descriptive','Every descriptive information about a sensor (wearable, SOC, cmoponent)'),(2,'Administrative','Every administrative information about a sensor (wearable, SOC, component)'),(3,'Structural','Every structural information about a sensor (wearavle, SOC, component)');
76/*!40000 ALTER TABLE `Attribute_Category` ENABLE KEYS */;
77UNLOCK TABLES;
78
79--
80-- Table structure for table `Class`
81--
82
83DROP TABLE IF EXISTS `Class`;
84/*!40101 SET @saved_cs_client = @@character_set_client */;
85/*!40101 SET character_set_client = utf8 */;
86CREATE TABLE `Class` (
87 `Class_Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key of the class.',
88 `Class_Name` varchar(255) NOT NULL COMMENT 'Name of the class.',
89 `Class_Description` varchar(255) NOT NULL COMMENT 'Description of the class.',
90 PRIMARY KEY (`Class_Id`)
91) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='This table hold information of an type for objects.';
92/*!40101 SET character_set_client = @saved_cs_client */;
93
94--
95-- Dumping data for table `Class`
96--
97
98LOCK TABLES `Class` WRITE;
99/*!40000 ALTER TABLE `Class` DISABLE KEYS */;
100INSERT INTO `Class` VALUES (1,'Wearable','Wearable device (for example: Apple Watch, etc.)'),(2,'SOC','System on a Chip (for example: LSM6DS3, etc.)'),(3,'Component','Component (for example: BMA280, etc.)'),(4,'Stationary fitness device','A stationary fitness device (for example a weighting scale)');
101/*!40000 ALTER TABLE `Class` ENABLE KEYS */;
102UNLOCK TABLES;
103
104--
105-- Table structure for table `Object`
106--
107
108DROP TABLE IF EXISTS `Object`;
109/*!40101 SET @saved_cs_client = @@character_set_client */;
110/*!40101 SET character_set_client = utf8 */;
111CREATE TABLE `Object` (
112 `Object_Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key of the table.',
113 `Object_Name` varchar(255) NOT NULL COMMENT 'Name of the object.',
114 `Object_Description` mediumtext NOT NULL COMMENT 'Description of the object.',
115 `Class_Id` int(11) NOT NULL COMMENT 'Reference to Class.Class_Id for indicating a class for an object.',
116 PRIMARY KEY (`Object_Id`),
117 KEY `fk_Object_Class_idx` (`Class_Id`),
118 CONSTRAINT `fk_Object_Class` FOREIGN KEY (`Class_Id`) REFERENCES `Class` (`Class_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
119) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='This table represents all Information about an object.';
120/*!40101 SET character_set_client = @saved_cs_client */;
121
122--
123-- Dumping data for table `Object`
124--
125
126LOCK TABLES `Object` WRITE;
127/*!40000 ALTER TABLE `Object` DISABLE KEYS */;
128INSERT INTO `Object` VALUES (1,'BMA280','The BMA280 is a triaxial, low-g acceleration sensor with digital output for consumer applications. It allows measurements of acceleration in three perpendicular axes. An evaluation circuitry (ASIC) converts the output of a micromechanical acceleration-sensing structure (MEMS) that works according to the differential capacitance principle.',3),(2,'BMP280','The BMP280 is an absolute barometric pressure sensor especially designed for mobile applications. The sensor module is housed in an extremely compact 8-pin metal-lid LGA package with a footprint of only 2.0 × 2.5 mm2 and 0.95 mm package height. Its small dimensions and its low power consumption of 2.7 μA @1Hz allow the implementation in battery driven devices such as mobile phones, GPS modules or watches.',3),(3,'iPhone 6s','Apple iPhone 6s',1),(4,'iPhone 7','Apple iPhone 7',1),(5,'Apple Watch 1','Apple Watch Version 1',1),(6,'Apple Watch 3','Apple Watch Version 3',1),(7,'Samsung Gear Fit 2 Pro','Samsung Gear Fit 2 Pro Fitness Tracker',1),(8,'LSM6DS3','Gyroscope and Accelerometer on Samsung Gear Fit 2 Pro',2),(9,'Body-Cardio','Nokia Body-Cardio weighting scale.',4),(10,'Beurer BC 85','Beurer BC 85',1),(11,'Kardia Band','AliveCor Kardia Band',1);
129/*!40000 ALTER TABLE `Object` ENABLE KEYS */;
130UNLOCK TABLES;
131
132--
133-- Table structure for table `Object_Is_Related_To_Object`
134--
135
136DROP TABLE IF EXISTS `Object_Is_Related_To_Object`;
137/*!40101 SET @saved_cs_client = @@character_set_client */;
138/*!40101 SET character_set_client = utf8 */;
139CREATE TABLE `Object_Is_Related_To_Object` (
140 `Object_Is_Related_To_Object_Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary key of the table.',
141 `IsRelatedTo_Description` varchar(255) NOT NULL COMMENT 'Description of the relationship between source and target objects.',
142 `Requirements` mediumtext COMMENT 'Further requirements for the relationship (for example: Bluetooth, WLAN etc.).',
143 `Object_Id_Source` int(11) NOT NULL COMMENT 'Reference of Object.Object_Id for indicating the source of an relationship between objects.',
144 `Object_Id_Target` int(11) NOT NULL COMMENT 'Reference of Object.Object_Id for indicating the target of an relationship between objects.',
145 `Source_Id` int(11) NOT NULL COMMENT 'Reference to Source.Source_Id for indicating a given source for this information.',
146 PRIMARY KEY (`Object_Is_Related_To_Object_Id`),
147 KEY `fk_Object_has_Object_Object2_idx` (`Object_Id_Target`),
148 KEY `fk_Object_has_Object_Object1_idx` (`Object_Id_Source`),
149 KEY `fk_IsRelatedTo_Source1_idx` (`Source_Id`),
150 CONSTRAINT `fk_IsRelatedTo_Source1` FOREIGN KEY (`Source_Id`) REFERENCES `Source` (`Source_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
151 CONSTRAINT `fk_Object_has_Object_Object1` FOREIGN KEY (`Object_Id_Source`) REFERENCES `Object` (`Object_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
152 CONSTRAINT `fk_Object_has_Object_Object2` FOREIGN KEY (`Object_Id_Target`) REFERENCES `Object` (`Object_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
153) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='This table provides information about the relationships betw';
154/*!40101 SET character_set_client = @saved_cs_client */;
155
156--
157-- Dumping data for table `Object_Is_Related_To_Object`
158--
159
160LOCK TABLES `Object_Is_Related_To_Object` WRITE;
161/*!40000 ALTER TABLE `Object_Is_Related_To_Object` DISABLE KEYS */;
162INSERT INTO `Object_Is_Related_To_Object` VALUES (1,'Component of',NULL,1,3,3),(2,'Component of',NULL,2,4,3),(3,'Component of',NULL,8,7,3),(4,'Compatibility to','Bluetooth, WLAN',5,3,2),(5,'Extension of',NULL,11,5,3);
163/*!40000 ALTER TABLE `Object_Is_Related_To_Object` ENABLE KEYS */;
164UNLOCK TABLES;
165
166--
167-- Table structure for table `Role`
168--
169
170DROP TABLE IF EXISTS `Role`;
171/*!40101 SET @saved_cs_client = @@character_set_client */;
172/*!40101 SET character_set_client = utf8 */;
173CREATE TABLE `Role` (
174 `Role_Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key of the table.\n',
175 `Role_Name` varchar(255) NOT NULL COMMENT 'Name of the role for a User.\n',
176 `Role_Description` varchar(255) NOT NULL COMMENT 'Description of a Role.\n',
177 PRIMARY KEY (`Role_Id`)
178) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='This table stores the role information for users.';
179/*!40101 SET character_set_client = @saved_cs_client */;
180
181--
182-- Dumping data for table `Role`
183--
184
185LOCK TABLES `Role` WRITE;
186/*!40000 ALTER TABLE `Role` DISABLE KEYS */;
187INSERT INTO `Role` VALUES (1,'Admin','Do everything'),(2,'User (Read)','Only got read privileges'),(3,'User (Read/Write)','Got read/write privileges');
188/*!40000 ALTER TABLE `Role` ENABLE KEYS */;
189UNLOCK TABLES;
190
191--
192-- Table structure for table `Source`
193--
194
195DROP TABLE IF EXISTS `Source`;
196/*!40101 SET @saved_cs_client = @@character_set_client */;
197/*!40101 SET character_set_client = utf8 */;
198CREATE TABLE `Source` (
199 `Source_Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key of the Table.',
200 `Source_Name` varchar(255) NOT NULL COMMENT 'Name of the source.',
201 `Source_Description` varchar(255) NOT NULL COMMENT 'Description of the source.',
202 PRIMARY KEY (`Source_Id`)
203) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='This table hold the sources where an information is related ';
204/*!40101 SET character_set_client = @saved_cs_client */;
205
206--
207-- Dumping data for table `Source`
208--
209
210LOCK TABLES `Source` WRITE;
211/*!40000 ALTER TABLE `Source` DISABLE KEYS */;
212INSERT INTO `Source` VALUES (1,'Manufacturer datasheet','The induvidual manufacturer datasheet of a sensor (wearable, SOC, component)'),(2,'Manufacturer homepage','The official homepage of an manufacturer for the sensor (wearable, SOC, component)'),(3,'Third party homepage','A random homepage where the sensor (wearable, SOC, component) is listed'),(4,'Other','An other source for the information than manufacturer datasheet, manufacturer homepage and third party homepage.');
213/*!40000 ALTER TABLE `Source` ENABLE KEYS */;
214UNLOCK TABLES;
215
216--
217-- Table structure for table `User`
218--
219
220DROP TABLE IF EXISTS `User`;
221/*!40101 SET @saved_cs_client = @@character_set_client */;
222/*!40101 SET character_set_client = utf8 */;
223CREATE TABLE `User` (
224 `User_Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key of the table.',
225 `User_Name` varchar(255) NOT NULL COMMENT 'User name of the User.\n',
226 `Name` varchar(255) NOT NULL COMMENT 'Name of the User.',
227 `Surname` varchar(255) NOT NULL COMMENT 'Surname of the User.',
228 PRIMARY KEY (`User_Id`)
229) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COMMENT='This table holds information of the user.';
230/*!40101 SET character_set_client = @saved_cs_client */;
231
232--
233-- Dumping data for table `User`
234--
235
236LOCK TABLES `User` WRITE;
237/*!40000 ALTER TABLE `User` DISABLE KEYS */;
238INSERT INTO `User` VALUES (1,'MK','Koch','Marius'),(2,'Test','Test','Nutzer');
239/*!40000 ALTER TABLE `User` ENABLE KEYS */;
240UNLOCK TABLES;
241
242--
243-- Table structure for table `User_Has_Role`
244--
245
246DROP TABLE IF EXISTS `User_Has_Role`;
247/*!40101 SET @saved_cs_client = @@character_set_client */;
248/*!40101 SET character_set_client = utf8 */;
249CREATE TABLE `User_Has_Role` (
250 `Role_Id` int(11) NOT NULL COMMENT 'Reference of Role.Role_Id.',
251 `User_Id` int(11) NOT NULL COMMENT 'Reference of User.User_Id',
252 PRIMARY KEY (`Role_Id`,`User_Id`),
253 KEY `fk_Role_has_User_User1_idx` (`User_Id`),
254 KEY `fk_Role_has_User_Role1_idx` (`Role_Id`),
255 CONSTRAINT `fk_Role_has_User_Role1` FOREIGN KEY (`Role_Id`) REFERENCES `Role` (`Role_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
256 CONSTRAINT `fk_Role_has_User_User1` FOREIGN KEY (`User_Id`) REFERENCES `User` (`User_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
257) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='This table is for giving user roles.';
258/*!40101 SET character_set_client = @saved_cs_client */;
259
260--
261-- Dumping data for table `User_Has_Role`
262--
263
264LOCK TABLES `User_Has_Role` WRITE;
265/*!40000 ALTER TABLE `User_Has_Role` DISABLE KEYS */;
266INSERT INTO `User_Has_Role` VALUES (1,1),(1,2);
267/*!40000 ALTER TABLE `User_Has_Role` ENABLE KEYS */;
268UNLOCK TABLES;
269
270--
271-- Table structure for table `Value`
272--
273
274DROP TABLE IF EXISTS `Value`;
275/*!40101 SET @saved_cs_client = @@character_set_client */;
276/*!40101 SET character_set_client = utf8 */;
277CREATE TABLE `Value` (
278 `Value_Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key of the table.',
279 `Created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Automatically filled with the current timestamp to identify the creation date of an entry.',
280 `Reason` mediumtext NOT NULL COMMENT 'Reason for insertion of the entry.',
281 `Codesystem` varchar(255) DEFAULT NULL COMMENT 'Specifies the codesystem of a value (for example LOINC)',
282 `Code` varchar(255) DEFAULT NULL COMMENT 'Specifies the code of a value.',
283 `Boolean` tinyint(4) DEFAULT NULL COMMENT 'Represents an boolean value.',
284 `Integer` int(11) DEFAULT NULL COMMENT 'Represents an integer value.',
285 `String` varchar(255) DEFAULT NULL COMMENT 'Represents a string value.',
286 `DateTime` datetime DEFAULT NULL COMMENT 'Represents a DateTime value.',
287 `Real` double DEFAULT NULL COMMENT 'Represents a Real value.',
288 `Source_Id` int(11) NOT NULL COMMENT 'Reference to Source.Source_Id for indication a source to a value.',
289 `Value_Unit_Id` int(11) NOT NULL DEFAULT '4' COMMENT 'Reference to Value_Unit.Value_Unit_Id for indicating a unit for a value.',
290 `Object_Id` int(11) NOT NULL COMMENT 'Reference to an object that is related to the value.',
291 `Attribute_Id` int(11) NOT NULL COMMENT 'Reference to an attribute that is related to the value and object.',
292 `User_Id` int(11) NOT NULL COMMENT 'Reference to User.User_Id for indicating the user who entered the value.',
293 PRIMARY KEY (`Value_Id`),
294 KEY `fk_Value_Source1_idx` (`Source_Id`),
295 KEY `fk_Value_Value_Unit1_idx` (`Value_Unit_Id`),
296 KEY `fk_Value_Object1_idx` (`Object_Id`),
297 KEY `fk_Value_Attribute1_idx` (`Attribute_Id`),
298 KEY `fk_Value_User1_idx` (`User_Id`),
299 CONSTRAINT `fk_Value_Attribute1` FOREIGN KEY (`Attribute_Id`) REFERENCES `Attribute` (`Attribute_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
300 CONSTRAINT `fk_Value_Object1` FOREIGN KEY (`Object_Id`) REFERENCES `Object` (`Object_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
301 CONSTRAINT `fk_Value_Source1` FOREIGN KEY (`Source_Id`) REFERENCES `Source` (`Source_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
302 CONSTRAINT `fk_Value_User1` FOREIGN KEY (`User_Id`) REFERENCES `User` (`User_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
303 CONSTRAINT `fk_Value_Value_Unit1` FOREIGN KEY (`Value_Unit_Id`) REFERENCES `Value_Unit` (`Value_Unit_Id`) ON DELETE NO ACTION ON UPDATE NO ACTION
304) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8 COMMENT='This table stores values of the Attributes for a sensor.';
305/*!40101 SET character_set_client = @saved_cs_client */;
306
307--
308-- Dumping data for table `Value`
309--
310
311LOCK TABLES `Value` WRITE;
312/*!40000 ALTER TABLE `Value` DISABLE KEYS */;
313INSERT INTO `Value` VALUES (1,'2018-02-12 12:12:08','',NULL,NULL,NULL,NULL,'BMA280',NULL,NULL,1,4,1,1,2),(2,'2018-02-12 12:12:08','',NULL,NULL,NULL,NULL,'BMP280',NULL,NULL,1,4,2,1,2),(3,'2018-02-12 12:12:08','',NULL,NULL,NULL,NULL,'14 Bit, digital, triaxial acceleration sensor with intelligent on-chip motion-triggered interrupt controller',NULL,NULL,1,4,1,2,2),(4,'2018-02-12 12:12:08','',NULL,NULL,NULL,NULL,'BMP280 digital pressure Sensor',NULL,NULL,1,4,2,2,2),(5,'2018-02-12 12:12:08','',NULL,NULL,NULL,NULL,'Bosch',NULL,NULL,1,4,1,3,2),(6,'2018-02-12 12:12:08','',NULL,NULL,NULL,NULL,'Bosch',NULL,NULL,1,4,2,3,2),(7,'2018-02-12 12:12:08','',NULL,NULL,NULL,NULL,'Display profile switching, menu scrolling, gaming, pedometer, free-fall detection, e-compass tilt compensation, drop detection for warranty logging, advanced system power management for mobile applications',NULL,NULL,1,4,1,4,2),(8,'2018-02-12 12:12:08','',NULL,NULL,NULL,NULL,'Enhancement of GPS navigation, indoor navigation, outdoor navigation, leisure and sports applications, weather forecast, vertical velocity indication',NULL,NULL,1,4,2,4,2),(9,'2018-02-12 12:12:08','',NULL,NULL,NULL,NULL,'Accelerometer',NULL,NULL,1,4,1,5,2),(10,'2018-02-12 12:12:08','',NULL,NULL,NULL,NULL,'Barometer',NULL,NULL,1,4,2,5,2),(11,'2018-02-12 12:12:08','',NULL,NULL,NULL,NULL,NULL,NULL,2,1,1,1,6,2),(12,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,NULL,NULL,2,1,1,2,6,2),(13,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,NULL,NULL,2,1,1,1,7,2),(14,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,NULL,NULL,2.5,1,1,2,7,2),(15,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,NULL,NULL,0.95,1,1,1,8,2),(16,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,NULL,NULL,0.95,1,1,2,8,2),(17,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,'iPhone 6s',NULL,NULL,2,4,3,1,2),(18,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,'iPhone 7',NULL,NULL,2,4,4,1,2),(19,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,'Apple Inc.',NULL,NULL,2,4,3,3,2),(20,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,'Apple Inc.',NULL,NULL,2,4,4,3,2),(21,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,NULL,NULL,67.1,2,1,3,6,2),(22,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,NULL,NULL,67.1,2,1,4,6,2),(23,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,NULL,NULL,138.3,2,1,3,7,2),(24,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,NULL,NULL,138.3,2,1,4,7,2),(25,'2018-02-12 12:12:09','',NULL,NULL,0,NULL,NULL,NULL,7.1,2,1,3,8,2),(26,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,NULL,NULL,7.1,2,1,4,8,2),(27,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,'Watch Series 1',NULL,NULL,2,4,5,1,2),(28,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,'Apple Watch Series 1',NULL,NULL,2,4,5,2,2),(29,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,'Apple inc.',NULL,NULL,2,4,5,3,2),(30,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,'Watch Series 3',NULL,NULL,2,4,6,1,2),(31,'2018-02-12 12:12:09','',NULL,NULL,NULL,NULL,'Apple Watch Series 3',NULL,NULL,2,4,6,2,2),(32,'2018-02-12 12:12:09','Test changes',NULL,NULL,NULL,NULL,'Apple inc.',NULL,NULL,2,4,6,3,2),(33,'2018-02-12 12:12:09','',NULL,NULL,1,NULL,NULL,NULL,NULL,2,4,6,9,2),(34,'2018-02-12 12:12:09','','LOINC','8867-4',NULL,NULL,'Heart rate',NULL,NULL,4,4,6,15,2),(35,'2018-02-12 12:21:07','Test changes',NULL,NULL,NULL,NULL,'Apple',NULL,NULL,2,4,6,3,2),(36,'2018-02-12 12:21:19','',NULL,NULL,NULL,NULL,'Apple inc.',NULL,NULL,2,4,6,3,2),(37,'2018-02-15 22:21:26','',NULL,NULL,NULL,NULL,'Body-Cardio',NULL,NULL,2,4,9,1,2),(38,'2018-02-15 22:22:08','',NULL,NULL,NULL,NULL,'Nokia',NULL,NULL,2,4,9,3,2),(39,'2018-02-15 22:23:31','',NULL,NULL,NULL,327,NULL,NULL,NULL,2,1,9,6,2),(40,'2018-02-15 22:24:20','',NULL,NULL,NULL,327,NULL,NULL,NULL,2,1,9,7,2),(41,'2018-02-15 22:24:51','',NULL,NULL,NULL,18,NULL,NULL,NULL,2,1,9,8,2),(42,'2018-02-15 22:25:23','Subsequent insertion',NULL,NULL,NULL,NULL,'iOS 8 and higher',NULL,NULL,2,4,9,12,2),(43,'2018-02-15 22:25:23','Subsequent insertion',NULL,NULL,NULL,NULL,'Version 5.0 and higher',NULL,NULL,2,4,9,13,2),(44,'2018-02-15 22:25:23','Subsequent insertion',NULL,NULL,1,NULL,NULL,NULL,NULL,2,4,9,14,2),(46,'2018-02-16 11:31:31','Subsequent insertion','LOINC','29463-7',NULL,NULL,'Body weight',NULL,NULL,2,4,9,15,2),(47,'2018-02-16 11:31:31','Subsequent insertion','LOINC','\r\n8867-4',NULL,NULL,'Heart rate',NULL,NULL,2,4,9,16,2),(48,'2018-02-16 16:14:03','',NULL,NULL,NULL,NULL,'BC 85',NULL,NULL,2,4,10,1,2),(49,'2018-02-16 16:14:29','',NULL,NULL,NULL,NULL,'Bosch',NULL,NULL,2,4,10,3,2),(50,'2018-02-16 16:14:57','',NULL,NULL,NULL,NULL,NULL,NULL,96.5,2,1,10,6,2),(51,'2018-02-16 16:15:23','',NULL,NULL,NULL,NULL,NULL,NULL,68,2,1,10,7,2),(52,'2018-02-16 16:15:46','',NULL,NULL,NULL,NULL,NULL,NULL,21.5,2,1,10,8,2),(53,'2018-02-16 16:16:21','',NULL,NULL,0,NULL,NULL,NULL,NULL,2,4,10,14,2),(54,'2018-02-16 16:18:53','',NULL,NULL,0,NULL,NULL,NULL,NULL,2,4,10,17,2),(55,'2018-02-16 16:26:05','Error handling',NULL,NULL,NULL,NULL,'Beurer',NULL,NULL,2,4,10,3,2),(56,'2018-02-16 16:26:05','Error handling',NULL,NULL,1,NULL,NULL,NULL,NULL,2,4,10,14,2),(57,'2018-02-16 16:26:05','Error handling',NULL,NULL,1,NULL,NULL,NULL,NULL,2,4,10,17,2),(58,'2018-02-19 20:04:29','',NULL,NULL,NULL,NULL,'Kardia Band',NULL,NULL,1,4,11,1,2),(59,'2018-02-19 20:04:48','',NULL,NULL,NULL,NULL,'AliveCor',NULL,NULL,1,4,11,3,2),(60,'2018-02-19 20:06:27','',NULL,NULL,1,NULL,NULL,NULL,NULL,1,4,11,9,2);
314/*!40000 ALTER TABLE `Value` ENABLE KEYS */;
315UNLOCK TABLES;
316
317--
318-- Table structure for table `Value_Unit`
319--
320
321DROP TABLE IF EXISTS `Value_Unit`;
322/*!40101 SET @saved_cs_client = @@character_set_client */;
323/*!40101 SET character_set_client = utf8 */;
324CREATE TABLE `Value_Unit` (
325 `Value_Unit_Id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Primary Key of the table.',
326 `Value_Unit_Name` varchar(255) NOT NULL COMMENT 'Represents the name of the unit.',
327 `Value_Unit_Description` varchar(255) NOT NULL COMMENT 'Represents the description of the unit.',
328 PRIMARY KEY (`Value_Unit_Id`)
329) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COMMENT='This table stores units for the values.';
330/*!40101 SET character_set_client = @saved_cs_client */;
331
332--
333-- Dumping data for table `Value_Unit`
334--
335
336LOCK TABLES `Value_Unit` WRITE;
337/*!40000 ALTER TABLE `Value_Unit` DISABLE KEYS */;
338INSERT INTO `Value_Unit` VALUES (1,'mm','Millimeter'),(2,'cm','centimeter'),(3,'V','Volt'),(4,'No Value','No Value');
339/*!40000 ALTER TABLE `Value_Unit` ENABLE KEYS */;
340UNLOCK TABLES;
341
342--
343-- Temporary view structure for view `overviewsensor`
344--
345
346DROP TABLE IF EXISTS `overviewsensor`;
347/*!50001 DROP VIEW IF EXISTS `overviewsensor`*/;
348SET @saved_cs_client = @@character_set_client;
349SET character_set_client = utf8;
350/*!50001 CREATE VIEW `overviewsensor` AS SELECT
351 1 AS `Class_Name`,
352 1 AS `Object_Name`,
353 1 AS `Attribute_Name`,
354 1 AS `Category_Name`,
355 1 AS `Codesystem`,
356 1 AS `Code`,
357 1 AS `Boolean`,
358 1 AS `Integer`,
359 1 AS `String`,
360 1 AS `DateTime`,
361 1 AS `Real`,
362 1 AS `Value_Unit_Name`,
363 1 AS `Source_Name`,
364 1 AS `Created`,
365 1 AS `User_Name`,
366 1 AS `Reason`*/;
367SET character_set_client = @saved_cs_client;
368
369--
370-- Temporary view structure for view `relationship_between_objects`
371--
372
373DROP TABLE IF EXISTS `relationship_between_objects`;
374/*!50001 DROP VIEW IF EXISTS `relationship_between_objects`*/;
375SET @saved_cs_client = @@character_set_client;
376SET character_set_client = utf8;
377/*!50001 CREATE VIEW `relationship_between_objects` AS SELECT
378 1 AS `Object_Name_Source`,
379 1 AS `IsRelatedTo_Description`,
380 1 AS `Requirements`,
381 1 AS `Object_Name_Target`*/;
382SET character_set_client = @saved_cs_client;
383
384--
385-- Dumping routines for database 'SensorRepository'
386--
387/*!50003 DROP PROCEDURE IF EXISTS `Actual_Value_Overview` */;
388/*!50003 SET @saved_cs_client = @@character_set_client */ ;
389/*!50003 SET @saved_cs_results = @@character_set_results */ ;
390/*!50003 SET @saved_col_connection = @@collation_connection */ ;
391/*!50003 SET character_set_client = utf8mb4 */ ;
392/*!50003 SET character_set_results = utf8mb4 */ ;
393/*!50003 SET collation_connection = utf8mb4_unicode_ci */ ;
394/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
395/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
396DELIMITER ;;
397CREATE DEFINER=`root`@`localhost` PROCEDURE `Actual_Value_Overview`()
398Begin
399SELECT ValueOverview.Class_Name, ValueOverview.Object_Id, ValueOverview.Attribute_Id, ValueOverview.Object_Name, ValueOverview.Attribute_Name, ValueOverview.Category_Name, ValueOverview.Codesystem, ValueOverview.Code, ValueOverview.Boolean, ValueOverview.Integer, ValueOverview.String, ValueOverview.DateTime, ValueOverview.Real, ValueOverview.Value_Unit_Name, ValueOverview.Source_Name, ValueOverview.Created, ValueOverview.User_Name, ValueOverview.Reason
400FROM
401
402(SELECT Class.Class_Name, Value.Object_Id, Value.Attribute_Id, Object.Object_Name, Attribute.Attribute_Name, Attribute_Category.Name AS Category_Name, Value.Codesystem, Value.Code, Value.Boolean, Value.Integer, Value.String, Value.DateTime, Value.Real, Value_Unit.Value_Unit_Name, Source.Source_Name, Value.Created, User.User_Name, Value.Reason FROM
403Class, Object, Attribute, Value, Value_Unit, Source, User, Attribute_Category WHERE
404Class.Class_Id = Object.Class_Id AND
405Value.Object_Id = Object.Object_Id AND
406Value.Attribute_Id = Attribute.Attribute_Id AND
407Value.User_Id = User.User_Id AND
408Value.Value_Unit_Id = Value_Unit.Value_Unit_Id AND
409Value.Source_Id = Source.Source_Id AND
410Attribute.Attribute_Category_Id = Attribute_Category.Attribute_Category_Id
411ORDER BY Object.Object_Name, Attribute.Attribute_Name ASC) ValueOverview,
412(SELECT Value.Object_Id, Value.Attribute_Id, MAX(Value.Created) AS CreatedDate
413 FROM Value
414 GROUP BY Value.Object_Id, Value.Attribute_Id) AktValues
415WHERE ValueOverview.Object_Id = AktValues.Object_Id AND
416ValueOverview.Attribute_Id = AktValues.Attribute_Id AND
417ValueOverview.Created = AktValues.CreatedDate;
418END ;;
419DELIMITER ;
420/*!50003 SET sql_mode = @saved_sql_mode */ ;
421/*!50003 SET character_set_client = @saved_cs_client */ ;
422/*!50003 SET character_set_results = @saved_cs_results */ ;
423/*!50003 SET collation_connection = @saved_col_connection */ ;
424/*!50003 DROP PROCEDURE IF EXISTS `Overview_Revisioned_Values` */;
425/*!50003 SET @saved_cs_client = @@character_set_client */ ;
426/*!50003 SET @saved_cs_results = @@character_set_results */ ;
427/*!50003 SET @saved_col_connection = @@collation_connection */ ;
428/*!50003 SET character_set_client = utf8mb4 */ ;
429/*!50003 SET character_set_results = utf8mb4 */ ;
430/*!50003 SET collation_connection = utf8mb4_unicode_ci */ ;
431/*!50003 SET @saved_sql_mode = @@sql_mode */ ;
432/*!50003 SET sql_mode = 'NO_ENGINE_SUBSTITUTION' */ ;
433DELIMITER ;;
434CREATE DEFINER=`root`@`localhost` PROCEDURE `Overview_Revisioned_Values`()
435Begin
436SELECT Value.Value_Id, Class.Class_Name, Value.Object_Id, Value.Attribute_Id, Object.Object_Name, Attribute.Attribute_Name, Attribute_Category.Name AS Category_Name, Value.Codesystem, Value.Code, Value.Boolean, Value.Integer, Value.String, Value.DateTime, Value.Real, Value_Unit.Value_Unit_Name, Source.Source_Name, Value.Created, User.User_Name, Value.Reason FROM
437Class, Object, Attribute, Value, Value_Unit, Source, User, Attribute_Category WHERE
438Class.Class_Id = Object.Class_Id AND
439Value.Object_Id = Object.Object_Id AND
440Value.Attribute_Id = Attribute.Attribute_Id AND
441Value.User_Id = User.User_Id AND
442Value.Value_Unit_Id = Value_Unit.Value_Unit_Id AND
443Value.Source_Id = Source.Source_Id AND
444Attribute.Attribute_Category_Id = Attribute_Category.Attribute_Category_Id AND
445Value.Value_Id NOT IN (
446 SELECT ValueOverview.Value_Id
447FROM
448(SELECT Value.Value_Id, Value.Object_Id, Value.Attribute_Id, Value.Created FROM
449Value) ValueOverview,
450(SELECT Value.Object_Id, Value.Attribute_Id, MAX(Value.Created) AS CreatedDate
451 FROM Value
452 GROUP BY Value.Object_Id, Value.Attribute_Id) AktValues
453WHERE ValueOverview.Object_Id = AktValues.Object_Id AND
454ValueOverview.Attribute_Id = AktValues.Attribute_Id AND
455ValueOverview.Created = AktValues.CreatedDate)
456ORDER BY Object.Object_Name, Attribute.Attribute_Name ASC;
457END ;;
458DELIMITER ;
459/*!50003 SET sql_mode = @saved_sql_mode */ ;
460/*!50003 SET character_set_client = @saved_cs_client */ ;
461/*!50003 SET character_set_results = @saved_cs_results */ ;
462/*!50003 SET collation_connection = @saved_col_connection */ ;
463
464--
465-- Current Database: `SensorRepository`
466--
467
468USE `SensorRepository`;
469
470--
471-- Final view structure for view `overviewsensor`
472--
473
474/*!50001 DROP VIEW IF EXISTS `overviewsensor`*/;
475/*!50001 SET @saved_cs_client = @@character_set_client */;
476/*!50001 SET @saved_cs_results = @@character_set_results */;
477/*!50001 SET @saved_col_connection = @@collation_connection */;
478/*!50001 SET character_set_client = utf8mb4 */;
479/*!50001 SET character_set_results = utf8mb4 */;
480/*!50001 SET collation_connection = utf8mb4_general_ci */;
481/*!50001 CREATE ALGORITHM=UNDEFINED */
482/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
483/*!50001 VIEW `overviewsensor` AS select `class`.`Class_Name` AS `Class_Name`,`object`.`Object_Name` AS `Object_Name`,`attribute`.`Attribute_Name` AS `Attribute_Name`,`attribute_category`.`Name` AS `Category_Name`,`value`.`Codesystem` AS `Codesystem`,`value`.`Code` AS `Code`,`value`.`Boolean` AS `Boolean`,`value`.`Integer` AS `Integer`,`value`.`String` AS `String`,`value`.`DateTime` AS `DateTime`,`value`.`Real` AS `Real`,`value_unit`.`Value_Unit_Name` AS `Value_Unit_Name`,`source`.`Source_Name` AS `Source_Name`,`value`.`Created` AS `Created`,`user`.`User_Name` AS `User_Name`,`value`.`Reason` AS `Reason` from (((((((`class` join `object`) join `attribute`) join `value`) join `value_unit`) join `source`) join `user`) join `attribute_category`) where ((`class`.`Class_Id` = `object`.`Class_Id`) and (`value`.`Object_Id` = `object`.`Object_Id`) and (`value`.`Attribute_Id` = `attribute`.`Attribute_Id`) and (`value`.`User_Id` = `user`.`User_Id`) and (`value`.`Value_Unit_Id` = `value_unit`.`Value_Unit_Id`) and (`value`.`Source_Id` = `source`.`Source_Id`) and (`attribute`.`Attribute_Category_Id` = `attribute_category`.`Attribute_Category_Id`)) order by `object`.`Object_Name`,`attribute`.`Attribute_Name`,`value`.`Created` */;
484/*!50001 SET character_set_client = @saved_cs_client */;
485/*!50001 SET character_set_results = @saved_cs_results */;
486/*!50001 SET collation_connection = @saved_col_connection */;
487
488--
489-- Final view structure for view `relationship_between_objects`
490--
491
492/*!50001 DROP VIEW IF EXISTS `relationship_between_objects`*/;
493/*!50001 SET @saved_cs_client = @@character_set_client */;
494/*!50001 SET @saved_cs_results = @@character_set_results */;
495/*!50001 SET @saved_col_connection = @@collation_connection */;
496/*!50001 SET character_set_client = utf8mb4 */;
497/*!50001 SET character_set_results = utf8mb4 */;
498/*!50001 SET collation_connection = utf8mb4_general_ci */;
499/*!50001 CREATE ALGORITHM=UNDEFINED */
500/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
501/*!50001 VIEW `relationship_between_objects` AS select `O1`.`Object_Name` AS `Object_Name_Source`,`object_is_related_to_object`.`IsRelatedTo_Description` AS `IsRelatedTo_Description`,`object_is_related_to_object`.`Requirements` AS `Requirements`,`O2`.`Object_Name` AS `Object_Name_Target` from ((`object` `O1` join `object_is_related_to_object`) join `object` `O2`) where ((`O1`.`Object_Id` = `object_is_related_to_object`.`Object_Id_Source`) and (`O2`.`Object_Id` = `object_is_related_to_object`.`Object_Id_Target`)) */;
502/*!50001 SET character_set_client = @saved_cs_client */;
503/*!50001 SET character_set_results = @saved_cs_results */;
504/*!50001 SET collation_connection = @saved_col_connection */;
505/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
506
507/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
508/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
509/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
510/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
511/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
512/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
513/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
514
515-- Dump completed on 2018-03-08 15:27:24