Database schema description

Introduction

Totem Database was designed to store data from four different sources of data. As a result, on the schema are noticeable 4 sections:

  • general measurement section - this section is responsible for storing data related to global conditions as well as whole detector system.
  • sensor part measurement section - enables storing information about measurements performed by the sensor part of the detector. For example Roman Pot sensor part is VFAT
  • structure element measurement section - contains data connected with the detector measurements, for example: offsets of the roman pot detector
  • alignment section - consist of alignment data

Diagram

db.png

Schema description

Database schema still is developing, so this is the reason that some tables are currently not beeing used. This is the description for those tables that were used.

T3_VALIDITY_INTERVAL and T7_IOV_TYPE

Interval of validity is a central point of the schema as it enables searching for measurement at given time. It is connected directly or indirectly for almost all tables. T7_IOV_TYPE is a dictionary table which helps in searching fro IOV as it provides additional information about validity interval.

Table name T3_VALIDITY_INTERVAL
Description Keeps information about validity interval of some measurements i.e. RP offsets
Fields t3_iov_id primary key of table,number, no domain meaning
t3_run_id foreign key of T4_RUN_TIMELINE_MAP, specify run number to which this IOV belongs to
start_event number of the first event it this IOV
end_event number of the last event it this IOV
t3_iov_type foreign key of T7_IOV_TYPE, indicates given IOV type
start_time start time of the interval
end_time end time of the interval
Outside Relations T6_USED_ALIGNMENTS many to one
T8_DEFAULT_ALIGNMENTS many to one
T13_SENSOR_PART_STATUS many to one
T16_SENSOR_PART_MEASUREMENTS many to one
T17_STRUCT_ELEMENT_MEASUREMENT many to one
T18_GENERAL_MEASUREMENT many to one
T22_ROOT_FILES many to one

Table name T7_IOV_TYPE
Description Keeps information about type of validity interval
Fields t7_iov_type_id primary key of table,number, no domain meaning
iov_type_name name of the IOV type
Outside relations T3_VALIDITY_INTERVAL many to one

T15_MEASUREMENT_TYPE

Measurement type is a dictionary table that helps in identifying type of the measurement. Additionally, it stores information about unit of the measurement as well as meaning of it. Field typ_version enables storing measurement type with different columns, For example: You want to save measurement with name X. At moment you want to store X has 3 values(labels): a, b, c. After some time, installing new sensors, measurement X has also 4th value: d. In this situation new version of the type will be created (with the same name but different version).

Table name T15_MEASUREMENT_TYPE
Description Keeps information about available measurement types. If the measurement is a vector of double it stores vector of unit and description for each result.
Fields t15_measurement_id primary key of table,number, no domain meaning
measurement_unit array of strings containing units for each value
measurement_column_description array of strings containing labels for each value
measurement_description string string,a label for single value in case that measurement is contains one result
measurement_single_unit string, a unit for single value in case that measurement is contains one result
measurement_name string, name of the measurement
measurement_string_description array of strings, containing label for string values describing string values
type_version number, keeps information about version of measurement type
Outside relations T16_SENSOR_PART_MEASUREMENTS many to one
T17_STRUCT_ELEMENT_MEASUREMENT many to one
T18_GENERAL_MEASUREMENT many to one

T18_GENERAL_MEASUREMENT

This table enables storing general data concerning conditions in tunels - not a values related to any part of the detector structure. Field measurement version enables keeping different result for the same IOV. For example: You want to save elastic optics. During calculations you use some constants which give you result with precision 0.1%. After some time you use other constants which give you better precision. Thanks to this field you will be able to store both versions. Measurement_attribute field describes measurement and provide mechanism to store additional specific information for the measuremet i.e for the luminosity it is version key.

Table name T18_GENERAL_MEASUREMENT
Description Keeps general information about conditions in tunnels
Fields t18_general_meas_id primary key of table,number, no domain meaning
t18_iov_id foreign key of T3_VALIDITY_INTERVAL, specify IOV to which this measurement belongs to
t18_measurement_type foreign key of T15_MEASUREMENT_TYPE, specify type of this measurement
measurement_value array of numbers containing measurement values
measurement_timestamp timestamp, time when measurement was taken
measurement_single_value number, contains value of measurement in case that type consist of single value
measurement_string_value string, array of strings indicating conditions of the measurement
measurement_version number, indicating version of the measurement
measurement_attribute string, contains specific value of the measurement

T10_STRUCTURE_ELEMENT and T9_STRUCTURE_ELEMENT_TYPE

Both tables enable reflecting detector system in the database. T10_STRUCTURE_ELEMENT contain identifier of the part as well as parent of if. It keeps data thanks to which it is possible to map virtual detector element to the real part. Next dictionary table (T9_STRUCTURE_ELEMENT_TYPE) consists of the type_name of the part i.e: Roman Pot Silicon Detector, VFAT etc.

Table name T10_STRUCTURE_ELEMENT
Description Keeps information about structure of the detector system
Fields t10_element_id primary key of table,number, no domain meaning
t10_element_type foreign key of T9_STRUCTURE_ELEMENT_TYPE, specify type of the element
element_identifier string, contains name of the element, enables mappinf from real part to the database structure
t10_parent_element number, contains parent id in the hierarchical structure
Outside relations T5_ALIGNMENTS many to one
T10_STRUCTURE_ELEMENT one to one
T11_SENSOR_PART many to one
T17_STRUCT_ELEMENT_MEASUREMENT many to one

Table name T9_STRUCTURE_ELEMENT_TYPE
Description Keeps information about type of the structure element
Fields t9_structure_element_type_id primary key of table,number, no domain meaning
Fields structure_element_type_name string, name of the type
Outside relations T10_STRUCTURE_ELEMENT many to one

T17_STRUCT_ELEMENT_MEASUREMENT

This table contains data related to the part of the detector systems. Field measurement_version enable data versioning . Measurement_attribute helps in storing additional specific parameter e.x in case of RP Offset attribute keep optics.

Table name T17_STRUCT_ELEMENT_MEASUREMENT
Description Keeps information related to the detector parts
Fields t17_struct_elem_meas_id primary key of table,number, no domain meaning
t17_element_id foreign key of T10_STRUCTURE_ELEMENT specifing detector part to which this measurement belongs to
t17_iov_id foreign key of T3_VALIDITY_INTERVAL, specify IOV to which this measurement belongs to
t17_measurement_type foreign key of T15_MEASUREMENT_TYPE, specify type of this measurement
measurement_value array of numbers containing measurement values
measurement_single_value number, contains value of measurement in case that type consist of single value
measurement_timestamp timestamp, time when measurement was taken
measurement_version number, indicating version of the measurement
measurement_attribute string, contains specific value of the measurement

T5_ALIGNMENTS, T8_DEFAULT_ALIGNMENT and T6_USED_ALIGNMENTS

All of these tables keeps information about RP detector Alignment. T5_ALIGNMENTS contains values of the measurement connected to the detector element to which values belongs to. Field alignment_attribute enables storing additional specific value for given resuls i.e: optics. T8_DEFAULT_ALIGNMENT store actual values and with T6_USED_ALIGNMENTS table makes possible to keep others versions results.

Table name T5_ALIGNMENTS
Description Keeps values of the alignment
Fields t5_align_id primary key of table,number, no domain meaning
insert_timestamp time, keep iformation when data was saved to the DB
description string, contains additional information/comments about values
align_column_description array of strings containing labels for each value, indicating about meaning of each value
t5_element_id foreign key of T10_STRUCTURE_ELEMENT specifing detector part to which this alignment belongs to
align_value array of numbers contaning alignment values
alignment_attribute string, contains specific value of the alignment
Outside relations T2_ALIGNMENT_SOURCE many to one
T6_USED_ALIGNMENTS one to one
T8_DEFAULT_ALIGNMENT one to one

Table name T8_DEFAULT_ALIGNMENT
Description Keeps connection between the latest version of the alignment values for given IOV
Fields t8_id primary key of table,number, no domain meaning
t8_alignment_id foreign key of T5_ALIGNMENTS indicating alignment values to which this row belongs to
t8_interval_id foreign key of T3_VALIDITY_INTERVAL, specify IOV to which this measurement belongs to
alignment_version number, indicating version of the alignment

Table name T6_USED_ALIGNMENTS
Description Keeps connection between old versions of the alignment values
Fields t6_avail_align_id primary key of table,number, no domain meaning
t6_alignment_id foreign key of T5_ALIGNMENTS indicating alignment values to which this row belongs to
t6_validity_id foreign key of T3_VALIDITY_INTERVAL, specify IOV to which this measurement belongs to
alignment_version number, indicating version of the alignment

T4_RUN_TIMELINE_MAP and T19_EVENT_TIMELINE_MAP

Both tables keeps basic information about event and runs.

Table name T4_RUN_TIMELINE_MAP
Description Keeps information about runs
Fields t4_run_id primary key of table,number, no domain meaning
run_number numbers specifying run number
first_event_no number indicating first event in given run
last_event_no number indicating last event in given run
first_event_time time indicating when first event taken place
last_event_time time indicating when last event taken place
description string containing additional information about run
Outside relations T2_ALIGNMENT_SOURCE many to one
T3_VALIDITY_INTERVAL many to one
T19_EVENT_TIMELINE_MAP many to one

Table name T19_EVENT_TIMELINE_MAP
Description Keeps information about events
Fields t19_run_id foreign key of T4_RUN_TIMELINE_MAP, specify run number to which this IOV belongs to
event_no number, indicates number of the event (CMS number)
event_time time indicating when event take place
t19_file_number number of file from which event comes

T22_ROOT_FILES

This table was designed to store root files with an inelastic optics.

Table name T22_ROOT_FILES
Description Keeps information about inealstic optics
Fields t22_file_id primary key of table,number, no domain meaning
t22_iov_id foreign key of T3_VALIDITY_INTERVAL, specify IOV to which this file belongs to
t22_file_type string specifying file type
t22_value binary data containing file
insert timestamp time when file was saved
file_version number, indicating version of the alignment

OTHER TABLES

Other tables are not beeing used so there is no description for them. To find some additional information please check programmer guide for the TOTODAM.

Schema assumptions

Database contains some triggers that help data remain integral. Most of them are procedures ensuring that next measurement with the same parameter which are responsible for identify it(i.e: for RP Offest it is: start_time, end_time, measurement_type, measurement_attribute), get higher version in comparison to the actual measurement. Beside all this triggers there is few others which are really important. These triggers prevent from intersection of interval of validity of measurements with the same parameters.

Edit | Attach | Watch | Print version | History: r8 < r7 < r6 < r5 < r4 | Backlinks | Raw View | WYSIWYG | More topic actions
Topic revision: r8 - 2013-01-11 - unknown
 
    • Cern Search Icon Cern Search
    • TWiki Search Icon TWiki Search
    • Google Search Icon Google Search

    TOTEM All webs login

This site is powered by the TWiki collaboration platform Powered by PerlCopyright & 2008-2021 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
or Ideas, requests, problems regarding TWiki? use Discourse or Send feedback