Tables and Constraints

Deployments

Tables

Django Model Project

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=64)

e.g. IAGOS

long_name [optional]

CharField(max_length=128)

start

DateTimeField

Project start date

end [optional]

DateTimeField

Project end date

description [optional]

TextField

Description of the project

is_infrastructure [false]

BooleanField

Project is infrastructure

Django Model Campaign

Key

Field

Django field

Comments

PK

id

AutoField

FK

project

ForeignKey

FK

deployments

ManyToManyField

name

CharField(max_length=32)

Name of the campaign

description [optional]

TextField

Description of the campaign

start

DateTimeField

Campaign start date

end [optional]

DateTimeField

Campaign end date

Django Model Region

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=64)

e.g. Europe

Django Model Country

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=64)

e.g. Germany

Django Model GeoCoordinate

Key

Field

Django field

Comments

PK

id

AutoField

FK

region

ForeignKey

latitude

DecimalField(max_digits=15)

longitude

DecimalField(max_digits=15)

Django Model MeasurementLocation

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=64)

e.g. SAPHIR chamber

description [optional]

TextField

FK

country

ForeignKey

FK

region

ForeignKey

altitude

DecimalField(max_digits=15)

latitude

DecimalField(max_digits=15)

longitude

DecimalField(max_digits=15)

active_from

DateTimeField

Availability start date

active_to [optional]

DateTimeField

Availability end date

Django Model Deployment

Key

Field

Django field

Comments

PK

id

AutoField

internal_no

IntegerField

FK

project

ForeignKey

FK

component

ForeignKey

FK

aircraft

ForeignKey

FK

measurement_location

ForeignKey

start

DateTimeField

Start date of the deployment

end [optional]

DateTimeField

End date of the deployment

values[optional]

TextField

e.g. Night time correction

comments [optional]

TextField

Django Model Airline

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=64)

e.g. Deutsche Lufthansa AG

code

CharField(max_length=3)

e.g. DLH

Django Model Aircraft

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=64)

e.g. IAGOS-CORE-01

FK

airline

ForeignKey

FK

aircraft_type

ForeignKey

name

CharField(max_length=64)

e.g. IAGOS-CORE-1

registration_code

CharField(max_length=16)

e.g. MAY2020MK

serial_no

CharField(max_length=16)

e.g. 2217BC2

internal_no

PositiveIntegerField

e.g. 42

active_from

DateTimeField

Availability start date

active_to [optional]

DateTimeField

Availability end date

Django Model AircraftType

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=32)

e.g. A340-300

Django Model Airport

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=64)

e.g. Frankfurt Airport

iata_code

CharField(max_length=3)

e.g. FRA

FK

country

ForeignKey

FK

region

ForeignKey

altitude

DecimalField(max_digits=15)

latitude

DecimalField(max_digits=15)

longitude

DecimalField(max_digits=15)

Django Model Flight

Key

Field

Django field

Comments

PK

id

AutoField

FK

aircraft

ForeignKey

FK

departure_airport

ForeignKey

departure

DateTimeField

FK

arrival_airport

ForeignKey

arrival

DateTimeField

flight_id [optional]

PositiveIntegerField

Project flight number

comments [optional]

TextField

Constraints

Deployments

Table project

Type

Description

1

BLANK-CHECK

The name of the project must not be empty.

2

UNIQUE-CHECK

The name of the project must be unique.

3

LIMIT-CHECK

The name of the project can have maximum 64 characters.

4

LIMIT-CHECK

The long_name of the project can have maximum 128 characters.

5

NULL-CHECK

The start of the project must not be null.

6

DATE-CHECK

The start of the project must be before the end of the project.

7

LOGIC-CHECK

The series of the project must be inside the project period.

8

LOGIC-CHECK

The periods of the campaigns must be inside the project period.

9

DELETE-CHECK

If a project is deleted, every related deployment and every related campaign will also be deleted.

Table campaign

Type

Description

1

NULL-CHECK

The project must not be null.

2

BLANK-CHECK

The name of the campaign must not be empty.

3

UNIQUE-CHECK

The name of the campaign must be unique.

4

LIMIT-CHECK

The name of the campaign contains maximum 32 characters.

5

NULL-CHECK

The start of the campaign must not be null.

6

DATE-CHECK

The start of the campaign must be before the end of the campaign.

7

LOGIC-CHECK

The period of the campaign must be inside the project period.

8

LOGIC-CHECK

All deployments that are assigned to the campaign must be inside the campaign period.

Table country

Type

Description

1

BLANK-CHECK

The name of the county must not be empty.

2

UNIQUE-CHECK

The name of the county must be unique.

3

LIMIT-CHECK

The name of the county contains maximum 64 characters.

4

DELETE-CHECK

If a country is deleted, every related location and every related airport will also be deleted.

Table measurement_location

Type

Description

1

BLANK-CHECK

The name of the location must not be empty.

2

UNIQUE-CHECK

The name of the location must be unique.

3

LIMIT-CHECK

The name of the location contains maximum 64 characters.

4

NULL-CHECK

The country must not be null.

5

NULL-CHECK

The longitude must not be null.

6

NULL-CHECK

The latitude must not be null.

7

NULL-CHECK

The altitude must not be null.

8

LIMIT-CHECK

The longitude must only have 15 digits and seven of them should be decimal places.

9

LIMIT-CHECK

The latitude can only have 15 digits and seven of them should be decimal places.

10

LIMIT-CHECK

The altitude can only have 15 digits and seven of them should be decimal places.

11

NULL-CHECK

The field active_from must not be null.

12

DATE-CHECK

The active_from must be before active_end.

13

LOGIC-CHECK

All deployments that are assigned to the location must be inside the availability period.

14

DELETE-CHECK

If a location is deleted, every related deployment will also be deleted.

Table deployment

Type

Description

1

NULL-CHECK

The project must not be null.

2

NULL-CHECK

The component must not be null.

3

NULL-CHECK

Only the aircraft or the measurement_location is null. The other one must be declared.

4

NULL-CHECK

The start of the deployment must not be null.

5

DATE-CHECK

The start of the deployment must be before the end of the deployment.

6

DATE-CHECK

The period of the deployment must be inside the project period.

7

LOGIC-CHECK

Two deployments of the same component can not overlap.

8

LOGIC-CHECK

The deployment period must be inside the availability period of the location or the aircraft.

9

LOGIC-CHECK

All series periods must be inside the deployment period.

10

DELETE-CHECK

If a deployment is deleted, every related series will also be deleted.

Table airline

Type

Description

1

BLANK-CHECK

The name of the airline must not be empty.

2

UNIQUE-CHECK

The name of the airline must be unique.

3

LIMIT-CHECK

The name of the airline contains maximum 64 characters.

4

BLANK-CHECK

The code of the airline must not be empty.

5

UNIQUE-CHECK

The code of the airline must be unique.

6

LIMIT-CHECK

The code of the airline contains maximum 3 characters.

7

LOGIC-CHECK

If an airline is deleted, every related aircraft will also be deleted.

Table aircraft_type

Type

Description

1

BLANK-CHECK

The name of the aircraft-type must not be empty.

2

UNIQUE-CHECK

The name of the aircraft-type must be unique.

3

LIMIT-CHECK

The name of the aircraft-type contains maximum 64 characters.

4

DELETE-CHECK

If an aircraft-type is deleted, every related aircraft will also be deleted.

Table aircraft

Type

Description

1

BLANK-CHECK

The registration_code of the aircraft must not be empty.

2

BLANK-CHECK

The serial_no of the aircraft must not be empty.

3

UNIQUE-CHECK

The registration_code of the aircraft must be unique.

4

UNIQUE-CHECK

The serial_no of the aircraft must be unique.

5

LIMIT-CHECK

The registration_code of the aircraft contains maximum 16 characters.

6

LIMIT-CHECK

The serial_no of the aircraft contains maximum 16 characters.

7

NULL-CHECK

The airline must not be null.

8

NULL-CHECK

The aircraft_type must not be null.

9

NULL-CHECK

The internal_no must not be null.

10

NULL-CHECK

The field active_from must not be null.

11

DATE-CHECK

The active_from must be before active_end.

12

LOGIC-CHECK

All deployments that are assigned to the aircraft must be inside the availability period.

13

LOGIC-CHECK

All flights that are assigned to the aircraft must be inside the availability period.

14

DELETE-CHECK

If an aircraft is deleted, every related deployment and every related flight will also be deleted.

Table airport

Type

Description

1

BLANK-CHECK

The name of the airport must not be empty.

2

BLANK-CHECK

The iata_code of the airport must not be empty.

3

UNIQUE-CHECK

The name of the airport must be unique.

4

UNIQUE-CHECK

The iata_code of the airport must be unique.

5

LIMIT-CHECK

The name of the airport contains maximum 64 characters.

6

LIMIT-CHECK

The iata_code of the airport contains maximum 3 characters.

7

NULL-CHECK

The longitude must not be null.

8

NULL-CHECK

The latitude must not be null.

9

NULL-CHECK

The altitude must not be null.

10

LIMIT-CHECK

The longitude can only have 15 digits and seven of them should be decimal places.

11

LIMIT-CHECK

The latitude can only have 15 digits and seven of them should be decimal places.

12

LIMIT-CHECK

The altitude can only have 15 digits and seven of them should be decimal places.

13

UNIQUE-CHECK

Every position (longitude, latitude, altitude) must be unique.

14

DELETE-CHECK

If an airport is deleted, every related flight will also be deleted.

Table flight

Type

Description

1

NULL-CHECK

The aircraft must not be null.

2

NULL-CHECK

The departure_airport must not be null.

3

NULL-CHECK

The arrival_airport must not be null.

4

NULL-CHECK

The departure must not be null.

5

NULL-CHECK

The arrival must not be null.

6

DATE-CHECK

The departure must be before the arrival.

7

LOGIC-CHECK

The flights of an aircraft can not overlap.

8

LOGIC-CHECK

Every flight must be inside the availability period of the aircraft.

9

DELETE-CHECK

Every flight must be inside the availability period of the aircraft.

Components

Tables

Django Model ComponentClass

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=64)

e.g. Sensor

Django Model ComponentType

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=64)

e.g. PT100

description [optional]

TextField

FK

component_class

ForeignKey

Django Model ComponentTypeParamete

Key

Field

Django field

Comments

PK

id

AutoField

FK

component_type

ForeignKey

FK

parameter

ForeignKey

Django Model Component

Key

Field

Django field

Comments

PK

id

AutoField

internal_no

IntegerField

FK

component_type

ForeignKey

serial_no [optional]

CharField(max_length=128)

Serial number

FK

component_status [optional]

ForeignKey

Django Model ComponentStatus

Key

Field

Django field

Comments

PK

id

AutoField

FK

component

ForeignKey

FK

status

ForeignKey

start

DateTimeField

Start date of the status

end [optional]

DateTimeField

End date of the status

comments [optional]

TextField

Django Model Status

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=32)

e.g. Defect

description [optional]

TextField

Django Model RelationFunctionality

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=64)

e.g. T_PMT

description [optional]

TextField

Django Model ComponentRelation

Key

Field

Django field

Comments

PK

id

AutoField

FK

component

ForeignKey

FK

parent

ForeignKey

FK

maincomponent

ForeignKey

installation_timestamp

DateTimeField

removal_timestamp [optional]

DateTimeField

FK

functionality

ForeignKey

Django Model Parameter

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=32)

e.g. T

long_name [optional]

CharField(max_length=128)

e.g. Temperature

cf_standard_name [optional]

CharField(max_length=128)

e.g. air_temperature

unit [optional]

CharField(max_length=32)

e.g. K

description [optional]

TextField

Django Model ComponentParameter

Key

Field

Django field

Comments

PK

id

AutoField

FK

component

ForeignKey

FK

parameter

ForeignKey

Django Model FunctionType

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=64)

e.g. First degree polynomial

description [optional]

TextField

Django Model Function

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=64)

e.g. Linear Regression

FK

function_type

ForeignKey

FK

calibration_parameters

ManyToManyField

Django Model FunctionParameter

Key

Field

Django field

Comments

PK

id

AutoField

FK

function

ForeignKey

FK

parameter

ForeignKey

value

FloatField

Django Model Calibration

Key

Field

Django field

Comments

PK

id

AutoField

FK

component

ForeignKey

timestamp

DateTimeField

Creation date of the calibration

description [optional]

TextField

Django Model CalibrationParameter

Key

Field

Django field

Comments

PK

id

AutoField

FK

component

ForeignKey

FK

parameter

ForeignKey

value

FloatField

Django Model CalibrationFunctionRelation

Key

Field

Django field

Comments

PK

id

AutoField

FK

calibration

ForeignKey

FK

function

ForeignKey

order_no

IntegerField

Constraints

Components

Table component_class

Type

Description

1

BLANK-CHECK

The name of the class must not be empty.

2

UNIQUE-CHECK

The name of the class must be unique.

3

LIMIT-CHECK

The name of the class contains maximum 64 characters.

4

DELETE-CHECK

If a class is deleted, every related type will also be deleted.

Table component_type

Type

Description

1

BLANK-CHECK

The name of the type must not be empty.

2

UNIQUE-CHECK

The name of the type must be unique.

3

LIMIT-CHECK

The name of the type contains maximum 64 characters.

4

NULL-CHECK

The component_class must not be null.

5

DELETE-CHECK

If a type is deleted, every related component will also be deleted.

Table component

Type

Description

1

BLANK-CHECK

The component_type of the component must not be empty.

2

LIMIT-CHECK

The serial_no of the type contains maximum 128 characters.

3

LIMIT-CHECK

The component can only have one current status.

4

DELETE-CHECK

If a component is deleted, every related component will also be deleted.

Table status

Type

Description

1

BLANK-CHECK

The name of the status must not be empty.

2

UNIQUE-CHECK

The name of the status must be unique.

3

LIMIT-CHECK

The name of the status contains maximum 32 characters.

4

DELETE-CHECK

If the status is deleted, every related component_status will also be deleted.

Table component_status

Type

Description

1

NULL-CHECK

The component must not be null.

2

NULL-CHECK

The status must not be null.

3

NULL-CHECK

The start must not be null.

4

DATE-CHECK

The start must be before the end.

5

LOGIC-CHECK

The statuses of a component can not be overlap.

Table component_type_parameter

Type

Description

1

NULL-CHECK

The component_type must not be null.

2

NULL-CHECK

The parameter must not be null.

3

UNIQUE-CHECK

The combination of component_type and parameter must be unique.

5

DELETE-CHECK

If a component type parameter is deleted, every time series and component parameter will also be deleted.

Table component_parameter

Type

Description

1

NULL-CHECK

The component can not be null.

2

NULL-CHECK

The parameter can not be null.

3

UNIQUE-CHECK

The combination of component and parameter must be unique.

4

DELETE-CHECK

If a component type parameter is deleted, every time series and component parameter will also be deleted.

Table component_relation

Type

Description

1

NULL-CHECK

The component must not be null.

2

NULL-CHECK

The parent must not be null.

3

NULL-CHECK

The main_component must not be null.

4

NULL-CHECK

The installation_timestamp must not be null.

5

DATE-CHECK

The installation_timestamp must be before the removal_timestamp.

6

DATE-CHECK

The installation periods of a component can not overlap.

Table parameter

Type

Description

1

BLANK-CHECK

The name of the parameter must not be empty.

2

LIMIT-CHECK

The long_name of the parameter contains maximum 32 characters.

3

LIMIT-CHECK

The cf_standard_name of the parameter contains maximum 128 characters.

4

LIMIT-CHECK

The unit of the parameter contains maximum 32 characters.

5

DELETE-CHECK

If a parameter is deleted, every time series and component parameter will also be deleted.

Table calibration

Type

Description

1

NULL-CHECK

The component must not be null.

2

NULL-CHECK

The timestamp must not be null.

3

DELETE-CHECK

If a calibration is deleted, every calibration parameter will also be deleted.

Table calibration_parameter

Type

Description

1

NULL-CHECK

The calibration must not be null.

2

NULL-CHECK

The parameter must not be null.

3

NULL-CHECK

The value can not be null.

4

UNIQUE-CHECK

A parameter and a calibration can be combined only once.

Table function_type

Type

Description

1

BLANK-CHECK

The name of the function type must not be empty.

2

UNIQUE-CHECK

The name of the function type must be unique.

3

LIMIT-CHECK

The name of the function type contains maximum 32 characters.

4

DELETE-CHECK

If a type is deleted, every related function will also delete.

Table function

Type

Description

1

BLANK-CHECK

The name of the function type must not be empty.

2

UNIQUE-CHECK

The name of the function type must be unique.

3

LIMIT-CHECK

The name of the function type contains maximum 32 characters.

4

NULL-CHECK

The function_type must not be null.

5

DELETE-CHECK

If a function is deleted, every function parameter will also delete.

Table function_parameter

Type

Description

1

NULL-CHECK

The function must not be null.

2

NULL-CHECK

The parameter must not be null.

3

NULL-CHECK

The value must not be null.

4

UNIQUE-CHECK

A parameter and a function can be combined only once.

Table calibration_function_relation

Type

Description

1

NULL-CHECK

The function must not be null.

2

NULL-CHECK

The calibration must not be null.

Series

Tables

Django Model DataLevel

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=32)

e.g. Final data

description

CharField(max_length=64)

e.g. Calibrated data

Django Model DataValidity

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=32)

e.g. Limited

description

CharField(max_length=64)

e.g. Doubtful

Django Model DataDescription

Key

Field

Django field

Comments

PK

id

AutoField

name

CharField(max_length=32)

description

TextField

Django Model DataSeries

Key

Field

Django field

Comments

PK

id

AutoField

FK

component_parameter

ForeignKey

FK

deployment

ForeignKey

FK

data_description

ForeignKey

period_start

DateTimeField

period_end

DateTimeField

timestamp

DateTimeField

Creation date

revision

PositiveIntegerField

comments

PositiveIntegerField

Django Model DataPoint

Key

Field

Django field

Comments

PK

id

BigAutoField

FK

data_series

ForeignKey

FK

data_validity

ForeignKey

timestamp

DateTimeField

value

FloatField

Django Model DataPointExtension

Key

Field

Django field

Comments

PK

id

BigAutoField

FK

data_point

ForeignKey

FK

data_description

ForeignKey

values

JSONField

Django Model SeriesRelation

Key

Field

Django field

Comments

PK

id

BigAutoField

FK

series

ForeignKey

FK

related_series

ManyToManyField

FK

related_functions

ManyToManyField

FK

related_calibrations

ManyToManyField

description

TextField

Constraints

Series

Table data_validity

Type

Description

1

BLANK-CHECK

The name of the validity must not be empty.

2

UNIQUE-CHECK

The name of the validity must be unique.

3

LIMIT-CHECK

The name of the validity contains maximum 32 characters.

4

LIMIT-CHECK

The description of the validity contains maximum 64 characters.

5

DELETE-CHECK

If a validity is deleted, every data point will also delete.

Table data_level

Type

Description

1

BLANK-CHECK

The name of the level must not be empty.

2

UNIQUE-CHECK

The name of the level must be unique.

3

LIMIT-CHECK

The name of the level contains maximum 32 characters.

4

LIMIT-CHECK

The description of the level contains maximum 64 characters.

5

DELETE-CHECK

If a level is deleted, every data point will also be deleted.

Table data_description

Type

Description

1

BLANK-CHECK

The name of the validity must not be empty.

2

UNIQUE-CHECK

The name of the validity must be unique.

3

LIMIT-CHECK

The name of the validity contains maximum 32 characters.

Table data_series

Type

Description

1

NULL-CHECK

The data_description must not be null.

2

NULL-CHECK

The data_level must not be null.

3

NULL-CHECK

The component_parameter must not be null.

4

NULL-CHECK

The deployment must not be null.

5

NULL-CHECK

The timestamp must not be null.

6

NULL-CHECK

The period_start must not be null.

7

NULL-CHECK

The period_end must not be null.

8

NULL-CHECK

The revision must not be null.

9

DATE-CHECK

The period_start must be before the period_end.

10

LOGIC-CHECK

The period of the series must be inside the period of the deployment.

11

UNIQUE-CHECK

The series must have a unique revision.

12

DELETE-CHECK

If a series is deleted, every related data point will also be deleted.

Table data_point

Type

Description

1

NULL-CHECK

The data_series must not be null.

2

NULL-CHECK

The data_validity must not be null.

3

NULL-CHECK

The timestamp must not be null.

4

NULL-CHECK

The value must not be null.

5

LOGIC-CHECK

There can not be two points that are assigned to the same series with the same timestamp.

Table data_point_extension

Type

Description

1

NULL-CHECK

The data_point must not be null.

2

NULL-CHECK

The data_description must not be null.

3

NULL-CHECK

The values must not be null.

UML Diagram

../_images/db_schema.png