## 3 Results
### Applications of the Phenotypic Query Approach
#### 3.1.3 Use of SQL in the LIFE Research Study  
LIFE is an epidemiological and multi-cohort study conducted at the Leipzig Research Centre for Civilization Diseases (Leipzig University) [55; 56]. The aim of LIFE is to determine the prevalence and causes of common civilisation diseases such as obesity, depression, and dementia by investigating thousands of Leipzig inhabitants of different ages. All participants are examined in a (multi-day) program using a wide range of assessments [56; 57]. The assessments include interviews, self-completed questionnaires, instrumental examinations such as anthropometry, ECG, or MRI, and laboratory analyses of the collected samples. The data for each assessment, depending on the participant's examination program, are collected using special input systems and prepared entry forms. All collected data are integrated and harmonised in a central research database. This database consists of data tables related to assessments (i.e., investigations). The complexity of the tables (i.e., number of columns) varies widely. All instance data in the research database are described by metadata, which are composed of the table and column names, the corresponding data types, and the original question or description of the measurement and a possible code list. The metadata are stored in a dedicated metadata repository (MDR) and are linked to the instance data. The collected data are analysed in an increasing number of analysis projects. Each project is specified by a project agreement that describes the analysis goal, plan, and required data. Current project agreements typically require data from up to 50 assessments. The queries to retrieve the data can be very complex. They typically combine data from multiple research database tables, multiple selection expressions, and a variety of projected columns. Manually specifying such database queries for each analysis project would be very error-prone and time-consuming. To solve this problem, we developedan ontological framework for describing the metadata and an ontology-based tool, the LIFE Query Generator, which generates the required database queries from the ontological specification [57]. The ontological framework [57] consists of three interrelated layers. The integrated data layer includes all research data (instance data) from the research database. The metadata layer describes the instance data using the metadata specified in the MDR. Finally, the ontology layer is represented by the LIFE Investigation Ontology (LIO) and its mapping to the collected metadata in the MDR. LIO is the core component of the framework and a predecessor version of the COP. It is used as a general model to formulate phenotypic knowledge and generate SQL queries. LIO semantically classifies biomedical investigations in LIFE. Fundamentally, we differentiate between items (e.g., questions of a questionnaire or single laboratory parameters) and item sets (e.g., complete assessments). Item sets correspond to the data tables (or views) of the research database, whereas items are represented by their columns. The mappings between the collected metadata in the MDR (e.g., table and column names) and LIO categories are inherently generated. The LIFE Query Generator [57] transforms the ontological specifications to corresponding SQL statements. Using the LIFE Query Generator, the applicant can search for desired assessments and select the complete assessments or specific items of an assessment. These selections are used to create the projection (i.e., items for which data should be retrieved). Furthermore, inclusion and exclusion criteria can be specified at the item level. Per default, the query generator creates one query for each selected assessment or item set. In addition, experienced users can create new item sets containing items from different assessments. Such item sets result in join queries, using patient identifiers and examination time points as join criteria. The SQL queries are then generated and executed in the research database.