Database Tables¶
This documentation is auto-generated from dictionary.json.
Tables¶
Comments¶
Stores any additional textual comments, notes, or observations related to a specific measured value
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Comment | ntext(1073741823) | - | Comment on the data in the Value table | - | |
| Comment ID | int (PK) | - | A unique ID is generated automatically by MySQL | - |
Contact¶
Stores detailed personal and professional information for people involved in projects (e.g., name, affiliation, function, e-mail, phone)
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Company | ntext(1073741823) | - | Company name | - | |
| Contact ID | int (PK) | - | Link to the Contact table | - | |
| nvarchar(100) | - | E-mail address | - | ||
| First Name | nvarchar(255) | - | First name of the contact | - | |
| Function | ntext(1073741823) | - | More detailed description about the functions | - | |
| Last Name | nvarchar(100) | - | Last name of the contact | - | |
| nvarchar(100) | - | LinkedIn account | - | ||
| Office Number | nvarchar(100) | - | Number of the office | - | |
| Phone | nvarchar(100) | - | Phone number | - | |
| Skype Name | nvarchar(100) | - | Skype name | - | |
| Status | nvarchar(255) | - | Status of the person. For example: "Master student", "Postdoc" or "Intern" | - | |
| Website | nvarchar(60) | - | Website URL of the contact or organization | - | |
| Contact City | nvarchar(255) | - | Address: name of the city | - | |
| Contact Country | nvarchar(255) | - | Address: name of the country | - | |
| Contact Street Name | nvarchar(100) | - | Address: name of the street | - | |
| Contact Street Number | nvarchar(100) | - | Address: number of the street | - | |
| Contact Zip Code | nvarchar(45) | - | Address: zip code | - |
Equipment¶
Stores information about a specific, physical piece of equipment (e.g., serial number, owner, purchase date, storage location)
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Equipment ID | int (PK) | - | Link to the Equipment table | - | |
| Equipment IDentifier | nvarchar(100) | - | Identification name of the equipments | - | |
| Equipment Model ID | int | - | Link to the Equipment model table | FK → Equipment_model_ID | |
| Owner | ntext(1073741823) | - | Name of the owner of the equipment | - | |
| Purchase Date | date | - | Date when the equipment was bought: 'YYYY-MM-DD | - | |
| Serial Number | nvarchar(100) | - | Serial number of the equipment | - | |
| Storage Location | nvarchar(100) | - | Where is the procedure stored | - |
Equipment Model¶
Stores detailed, non-redundant specifications for a specific sensor or instrument model (e.g., manufacturer, functions, method)
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Equipment Model | nvarchar(100) | - | Name of the equipment model. For example: ammo::lyser | - | |
| Equipment Model ID | int (PK) | - | Link to the Equipment model table | - | |
| Functions | ntext(1073741823) | - | Description of the functions of the equipment | - | |
| Manual Location | nvarchar(100) | - | Location where the manual is stored | - | |
| Manufacturer | nvarchar(100) | - | Name of the manufacturer | - | |
| Method | nvarchar(100) | - | Method behind the equipment | - |
Equipment Model Has Parameter¶
Links equipment models to the parameters they can measure
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Equipment Model ID | int (CK-1) | - | Link to the Equipment model table | FK → Equipment_model_ID | |
| Parameter ID | int (CK-2) | - | Link to the Parameter table | - |
Equipment Model Has Procedures¶
Links equipment models to the relevant maintenance procedures
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Equipment Model ID | int (CK-1) | - | Link to the Equipment model table | FK → Equipment_model_ID | |
| Procedure ID | int (CK-2) | - | Link to the Procedures table | FK → Procedure_ID |
Hydrological Characteristics¶
Stores the hydrological land use percentages (e.g., forest, wetlands, cropland, grassland) within the watershed
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Cropland | real | - | Percentage [%] of croplands | - | |
| Forest | real | - | Percentage [%] of forest areas | - | |
| Grassland | real | - | Percentage [%] of grasslands | - | |
| Meadow | real | - | Percentage [%] of meadow areas | - | |
| Urban Area | real | - | Percentage [%] of urban areas | - | |
| Watershed ID | int (PK) | - | Linked to the Watershed table | FK → Watershed_ID | |
| Wetlands | real | - | Percentage [%] of wetlands | - |
Metadata¶
Contains a list of all existing unique metadata combinations (represented by a series of foreign keys/IDs) that describe a single measurement
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Condition ID | int | - | A unique ID is generated automatically by MySQL | FK → Condition_ID | |
| Contact ID | int | - | Link to the Contact table | FK → Contact_ID | |
| Equipment ID | int | - | Link to the Equipment table | FK → Equipment_ID | |
| Metadata ID | int (PK) | - | A unique ID is generated automatically by MySQL | - | |
| Parameter ID | int | - | Link to the Parameter table | FK → Parameter_ID | |
| Procedure ID | int | - | Link to the Procedures table | FK → Procedure_ID | |
| Project ID | int | - | Link to the Project table | FK → Project_ID | |
| Purpose ID | int | - | A unique ID is generated automatically by MySQL | FK → Purpose_ID | |
| Sampling Point ID | int | - | Link to the Sampling_point table | FK → Sampling_point_ID | |
| Unit ID | int | - | A unique ID is generated automatically by MySQL | FK → Unit_ID |
Parameter¶
Stores the different water quality or quantity parameters that are measured (e.g., pH, TSS, N-components)
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Parameter | nvarchar(100) | - | Name of the parameter | - | |
| Parameter ID | int (PK) | - | Link to the Parameter table | - | |
| Unit ID | int | - | A unique ID is generated automatically by MySQL | FK → Unit_ID | |
| Parameter Description | ntext(1073741823) | - | Description of the parameter | - |
Parameter Has Procedures¶
Links parameters to the relevant measurement procedures
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Parameter ID | int (CK-1) | - | Link to the Parameter table | - | |
| Procedure ID | int (CK-2) | - | Link to the Procedures table | FK → Procedure_ID |
Procedures¶
Stores details for different measurement procedures (e.g., calibration, validation, standard operating procedures, ISO methods)
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Procedure ID | int (PK) | - | Link to the Procedures table | - | |
| Procedure Location | nvarchar(100) | - | Where is the procedure stored | - | |
| Procedure Name | nvarchar(100) | - | Title name of the procedure | - | |
| Procedure Type | nvarchar(255) | - | Type of the procedure. For example, SOP | - | |
| Procedures Description | ntext(1073741823) | - | Description of the procedure | - |
Project¶
Stores descriptive information about the research or monitoring project for which the data was collected
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Project ID | int (PK) | - | Link to the Project table | - | |
| Project Name | nvarchar(100) | - | Name of the project | - | |
| Project Description | ntext(1073741823) | - | Description of the project | - |
Project Has Contact¶
Links projects to the personnel involved in them
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Contact ID | int (CK-2) | - | Link to the Contact table | FK → Contact_ID | |
| Project ID | int (CK-1) | - | Link to the Project table | FK → Project_ID |
Project Has Equipment¶
Links projects to the specific equipment used within them
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Equipment ID | int (CK-2) | - | Link to the Equipment table | FK → Equipment_ID | |
| Project ID | int (CK-1) | - | Link to the Project table | FK → Project_ID |
Project Has Sampling Points¶
Links projects to the sampling points used within them
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Project ID | int (CK-1) | - | Link to the Project table | FK → Project_ID | |
| Sampling Point ID | int (CK-2) | - | Link to the Sampling_point table | FK → Sampling_point_ID |
Purpose¶
Stores information about the aim of the measurement (e.g., on-line measurement, laboratory analysis, calibration, validation, cleaning)
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Purpose | nvarchar(100) | - | Purpose of the data collection. For example, "Measurement", "Lab_analysis", "Calibration" and "Cleaning" | - | |
| Purpose ID | int (PK) | - | A unique ID is generated automatically by MySQL | - | |
| Purpose Description | ntext(1073741823) | - | Description of the purpose | - |
Sampling Points¶
Stores the identification, specific geographical coordinates (Latitude/Longitude/GPS), and description of a particular spot where a sample or measurement is taken
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Latitude GPS | nvarchar(100) | - | GPS coordinates. For example: 47°54′25.103" | - | |
| Longitude GPS | nvarchar(100) | - | GPS coordinates. For example: $73^{\circ}47^{\prime}00.024^{\prime\prime}$ | - | |
| Pictures | BLOB | - | Picture of the site | - | |
| Sampling Location | nvarchar(100) | - | Where the sample was taken. For example: "Biofiltration", "Sewer 01" or "Retention Tank" | - | |
| Sampling Point | nvarchar(100) | - | Where the sample was taken. For example: "Inlet", "Outlet" or "Upstream" | - | |
| Sampling Point ID | int (PK) | - | Link to the Sampling_point table | - | |
| Site ID | int | - | A unique ID is generated automatically by MySQL | FK → Site_ID | |
| Sampling Points Description | ntext(1073741823) | - | Description of the sampling point | - |
Site¶
Stores general site information, including address, site type, and a link to the associated watershed
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Picture | image(2147483647) | - | Picture of the site | - | |
| Province | nvarchar(255) | - | Address: name of the province | - | |
| Site ID | int (PK) | - | A unique ID is generated automatically by MySQL | - | |
| Site Name | nvarchar(100) | - | Name of the site | - | |
| Site Type | nvarchar(255) | - | For example: "WWTP", "River" or "Sewer_system" | - | |
| Watershed ID | int | - | Linked to the Watershed table | FK → Watershed_ID | |
| Site City | nvarchar(255) | - | Address: name of the city | - | |
| Site Country | nvarchar(255) | - | Address: name of the country | - | |
| Site Description | ntext(1073741823) | - | Description of the site | - | |
| Site Street Name | nvarchar(100) | - | Address: name of the street | - | |
| Site Street Number | nvarchar(100) | - | Address: number of the street | - | |
| Site Zip Code | nvarchar(100) | - | Address: zip code | - |
Unit¶
Stores the SI units of measurement (or other relevant units) corresponding to the parameters (e.g., mg/L, g/L, s)
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Unit | nvarchar(100) | - | SI-units only | - | |
| Unit ID | int (PK) | - | A unique ID is generated automatically by MySQL | - |
Urban Characteristics¶
Stores the urban land use percentages (e.g., commercial, residential, green spaces) within the watershed
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Agricultural | real | - | Percentage [%] of agricultural land use. For example farm land | - | |
| Commercial | real | - | Percentage [%] of commercial areas. For example stores or bank areas | - | |
| Green Spaces | real | - | Percentage [%] of green spaces | - | |
| Industrial | real | - | Percentage [%] of industrial areas. For example factories | - | |
| Institutional | real | - | Percentage [%] of institutional areas. For example schools, police stations or city hall | - | |
| Recreational | real | - | Percentage [%] of recreational areas. For example parks or sport fields | - | |
| Residential | real | - | Percentage [%] of residential areas. For example houses or apartment buildings | - | |
| Watershed ID | int (PK) | - | Linked to the Watershed table | FK → Watershed_ID |
Value¶
Stores each measured water quality or quantity value, its time stamp, replicate identification, and the link to its specific metadata set
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Comment ID | int | - | A unique ID is generated automatically by MySQL | FK → Comment_ID | |
| Metadata ID | int | - | A unique ID is generated automatically by MySQL | FK → Metadata_ID | |
| Number Of Experiment | numeric | - | Number of replica of an experiment | - | |
| Timestamp | int | - | Unix timestamp combining date and time of collected data | - | |
| Value | float | - | Value of collected data | - | |
| Value ID | int (PK) | - | A unique ID is generated automatically by MySQL | - |
Watershed¶
Stores general information about the watershed area, including surface area, concentration time, and impervious surface percentage
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Concentration Time | int | - | Concentration time in minutes [min] | - | |
| Impervious Surface | real | - | Percentage of the impervious surface of the watershed in percentage [%] | - | |
| Surface Area | real | - | Surface area of the watershed [ha] | - | |
| Watershed ID | int (PK) | - | Linked to the Watershed table | - | |
| Watershed Name | nvarchar(100) | - | Name of the watershed | - | |
| Watershed Description | ntext(1073741823) | - | Description of the watershed | - |
Weather Condition¶
Stores descriptive information about the prevailing weather conditions when the measurement was taken (e.g., dry weather, wet weather, snow melt)
Fields¶
| Field | SQL Type | Value Set | Required | Description | Constraints |
|---|---|---|---|---|---|
| Condition ID | int (PK) | - | A unique ID is generated automatically by MySQL | - | |
| Weather Condition | nvarchar(100) | - | Type of weather condition | - | |
| Weather Condition Description | ntext(1073741823) | - | Description of the condition | - |