@@ -130,31 +130,29 @@ The following procedures are implemented to ascertain data quality in the databa
- Traceability: Every record is automatically amended with the information who created it (column created_by) and at what time (column created_at) and who last modified it (column updated_by) and at which time (column updated_by). To document how pre-existing data were mapped on the controlled vocabulary of the database, for several attributed there is an additional text-column, where the original value should be stored. This column has the suffix \_reported. Deleted data from the tables containing the data are not deleted when user delete them, but marked as deleted and kept hidden from the ordinary user ("soft-deletion"). They can be restored by the administrators on request.
- Constraints: This database makes use of five user relevant constraints.
* Primary key: Ensures that uniqueness as well as a not-null condition of a column entry or a combination of column entries are satisfied. As user are not supposed to import the id column as it is automatically generated, its only relevance to the users is, that the id column needs to get selected as custom key when deleting or updating data (compare issue #10).
* Not Null: Ensures that a not-null condition of a column entry or a combination of column entries are satisfied. In the \__column_definition\_ view those columns are marked in the \_mandatory\_ column.
* Check: Ensures the satisfaction of certain conditions based on logical queries or the existence of other column entries in this row.
*<details>
- Primary key: Ensures that uniqueness as well as a not-null condition of a column entry or a combination of column entries are satisfied. As user are not supposed to import the id column as it is automatically generated, its only relevance to the users is, that the id column needs to get selected as custom key when deleting or updating data (compare issue #10).
- Not Null: Ensures that a not-null condition of a column entry or a combination of column entries are satisfied. In the \__column_definition\_ view those columns are marked in the \_mandatory\_ column.
- Check: Ensures the satisfaction of certain conditions based on logical queries or the existence of other column entries in this row.
-<details>
<summary>Example1</summary>
Check constraints for numeric values: Wherever possible numeric values are checked on insertion for their plausibility (e.g.areas, discharges, inhabitants) to be within a meaningful range, usually \> 0 and smaller a maximum value. The catchment area of a river must be larger than 0 km² and smaller than 7000000 km² (size of the Amazon river basin). CONSTRAINT check_catchment_area_range CHECK ( catchment_area_km2 \> 0 AND catchment_area_km2 \< 7000000 )
</details>
*<details>
-<details>
<summary>Example2</summary>
Values are checked based on values in other columns: If a measurement is marked as below an analytical limit, the value of the limit must be given in the corresponding column. If a measurement is marked as below the analytical limit of detection (LOD), the LOD must be given in the corresponding column.CONSTRAINT check_lod_available CHECK ((lod \> 0 AND value_below_lod IS TRUE) OR (value_below_lod IS NOT TRUE))
</details>
* Unique key: Ensures that uniqueness of a column entry or a combination of column entries are satisfied. Some unique keys are supposed to store aggregated information about its row. If this is the case, it can be seen in the description of the column. In the \__column_definition\_ view those columns are marked in the \__col_constraints\_ column with (u).
* Foreign keys: Ensures the case sensitive referencing of a column or a combination of column entries in the so called "child table" originally migrated from the "parent table", hence providing referential integrity. In the \__column_definition\_ view those columns are marked in the \__col__constraints\_ column with (f).
- Unique key: Ensures that uniqueness of a column entry or a combination of column entries are satisfied. Some unique keys are supposed to store aggregated information about its row. If this is the case, it can be seen in the description of the column. In the \__column_definition\_ view those columns are marked in the \__col_constraints\_ column with (u).
- Foreign keys: Ensures the case sensitive referencing of a column or a combination of column entries in the so called "child table" originally migrated from the "parent table", hence providing referential integrity. In the \__column_definition\_ view those columns are marked in the \__col__constraints\_ column with (f).
- Controlled vocabulary: Those are a special version of foreign keys, here used for qualitative attributes: Whenever a qualitative attribute is recorded, controlled vocabularies (a list of allowed values for that column) are used. These assure, that the same thing is always called the same (and without typos) and no further data cleaning is necessary before data evaluation. Controlled vocabularies are stored in separate tables. These tables can not be changed by the ordinary user, but only by admins. Deletion in controlled vocabularies are not recorded in the database itself (no soft delete mechanism), here the admin is responsible to keep a backup. In the \__column_definition\_ view those columns are marked in the \__controlled_vocabulary\_ and. Possible entries for those columns can be seen in the \__controlled_vocabularies\_ view.
<details>
<summary>Example</summary>
The Accreditation status of measurements are managed by a boolean column with controlled vocabulary used according to the following schema:
