Vulnerability schema

Vulnerability DB schema

The vulnerability schema includes physical fragility and vulnerability relationships (single and multi-hazard curves) and will be extended to include socioeconomic indicators and indexes. The schema distinguishes key information describing the function, including: - function type (i.e fragility, vulnerability, damage-to-loss); - development approach (empirical, analytical, judgement, hybrid, code-based); - mathematical model used (including exponential, cumulative lognormal/normal); - the intensity measure and asset type the function relates to; - loss parameter / engineering demand parameter values.

It also defines the countries for which the function was developed, to provide guidance on geographic relevance when applying a function. All data is stored with metadata focussed on providing the information required to assess its suitably for risk modelling projects - including source, licence, development method, units, and hazard processes it relates to.

The schema consists of three base tables (f_core, f_specifics, and f_additional) and five supporting tables (f_scoring, damage_scale, edp_table, lp_table, reference_table). The vulnerbaility schema is linked to the cf_Common tables, which contain supports consistency across the four schema.

Screenshot ERD (vulnerability schema): vulnerability table contents (purple) and links to common tables (yellow).

Table: mover.f_core

The f_core table comprises data fields for recording all necessary fragility function attributes required by a user to reproduce the function. It also comprises fields that contain useful information for the scoring the fragility functions (which can be recorded in the f_scoring table). Separate entries are made for fragility functions associated with different damage states. The data schema permits recording of the functional form and parameters of fragility functions, but is also flexible enough to also allow the entry of discrete forms of fragility representation, i.e. damage probability matrices (DPM).

Req	Field name	Type	Reference table	Description
*	id	INT		Unique number ID
*	hazard_type_primary	VARCHAR	cf_common.hazard_type	Primary hazard involved
	hazard_type_secondary	VARCHAR	cf_common.hazard_type	Secondary hazard involved
*	process_type_primary	VARCHAR	cf_common.process_type	Primary process involved
	process_type_secondary	VARCHAR	cf_common.process_type	Secondary process involved
*	occupancy	ENUM	cf_common.occupancy_enum	Type of occupancy
	taxonomy_source	VARCHAR		Source of taxonomy
	taxonomy	VARCHAR		Name of taxonomy
*	asset_type	VARCHAR	ged4all.asset	Type of asset
	asset_notes	VARCHAR		Additional info on asset
*	country_iso	VARCHAR		3-char country code(s), comma separated
	applicability_notes	VARCHAR		Specific sub-area within a country and/or region.
*	scale_applicability	ENUM	mover.scale_app_enum	Administrative level of application
*	function_type	ENUM	mover.function_type_enum	Type of function: Fragility or Vulnerability or Damaga-to-loss
	approach	ENUM	mover.f_subtype_enum	Type of methodological approach
*	f_relationship	ENUM	mover.f_relationship_enum	Type of relationship: Mathematical or Discrete
	f_math	ENUM	mover.f_math_enum	Parametric or bespoke
	f_math_model	ENUM	mover.f_mathtype_enum	Type of mathematical model
	bespoke_model_ref	VARCHAR		Reference study of the bespoke model
*	f_reference	VARCHAR		Literature article or report
*	licence_code	VARCHAR	cf_common.license	Type of licence
	licence_reference	VARCHAR		Url associated with licence

Table: mover.f_specifics

The f_specifics table comprises data fields for recording specific optional details of the fragility function.

Req	Field name	Type	Reference table	Description
*	f_specifics_id	INT		Unique number ID
	par_names	VARCHAR		Parameters values names Example: MIDR , Ash depth
	ub_par_value	VARCHAR		Upper bound parameters value (Value1; Value2)
	ub_par_perc	VARCHAR		Upper bound parameters percentiles (Perc1; Perc2)
	med_par_value	VARCHAR		Median parameter values (Med1; Med2)
	lb_par_value	VARCHAR		Lower bound parameters value (Value1; Value2)
	lb_par_perc	VARCHAR		Lower bound parameters percentiles (Perc 1;Perc 2)
	damage_scale_code	VARCHAR		Code that identifies the damage scale
	dm_state_name	ENUM	mover.damage_states_all_enum	Damage states studied in the reference study of the function
	n_dm_states	CHAR(1)		Number of damage states studied in the reference study of the function
	f_disc_im	VARCHAR		Intensity measure values for the characterization of discrete functions
	f_disc_ep	VARCHAR		This field lists the associated exceeded probability values to the IM values of the previous field
	lp_code	VARCHAR
	lp_loss_value	VARCHAR
	edp_cpde	VARCHAR		Code related to specific engineering demand parameter (EDP) used to the DS thresholds
	edp_name	VARCHAR		Specific engineering demand parameter (EDP) used to the DS thresholds
	edp_dmstate_thre	VARCHAR		Specific damage state EDP threshold
	im_code	VARCHAR		Code of intensity measure
	im_name	VARCHAR		Name of intensity measure
	im_range	VARCHAR		Range of intensity measures as min;max (e.g. 0;500)
	im_units	VARCHAR		Unit of intensity measrue
	im_method	ENUM	mover.im_method_enum	Type of source of the im data
	im_sim_type	ENUM	mover.sim_method_enum	Type of simulation, Physics-based or IMPE
	impe_referenec	VARCHAR		Reference study of the IMPE simulation
	data_countries	VARCHAR		ISO code(s) of countries to which data refer
	im_data_source	VARCHAR		Reference studies for the IM data sources
	n_events	INT		Number of events the function has been built on
	n_assets	INT		Number of assets the function has been built on

Table: mover.f_additional

The f_additional table comprises data fields for recording additional, optional specific attributes of the fragility function that helps to understand the analysis which generated the function.

Req	Field name	Type	Reference table	Description
*	f_additional_id	INT		Unique number ID
*	nonsampling_err	ENUM	mover.nonsampling_err_enum	Is there sampling error?
	type_nonsampling_err	ENUM	mover.type_nonsampling_err_enum	Type of non sampling error
	is_fix_nonsam_err	BOOLEAN		Has non sampling error being fixed?
	is_data_aggregated	BOOLEAN		Has data been aggregated?
	n_data_points_aggr	INT		Number of aggregated data points used for the evaluation of data quality
	is_data_disaggr	BOOLEAN		Has data been disaggregated?
	an_analysis_type	ENUM	mover.an_analysis_type_enum	Type of analysis for Analytical functions
	em_analysis_type	ENUM	mover.em_analysis_type_enum	Type of analysis for Empirical functions
	jd_analysis_type	ENUM	mover.jd_analysis_type_enum	Type of analysis for Judgement functions
	is_fit_good	BOOLEAN		Is the fit good overall?
	fit_ref	ENUM	mover.fit_ref_enum	Reference model for fitting
	val_data_source	VARCHAR		If validation has been done, source of the independent data
	val_study_reference	VARCHAR		Reference of the Validation study
	sample	ENUM	mover.sample_enum	Type of sampling

Table: mover.f_scoring

The f_scoring_ table provides a recording and scoring geogrpahic relevance of vulnerability and fragility functions to one or more countries - i.e. a function developed for a particular country is highly relevant for that country and less relevant for other countries where the building stock differs.

Req	Field name	Type	Reference table	Description
*	id	INT		Unique number ID
*	f_core_id	INT	mover.f_core	ID of the function
*	geo_applicability	VARCHAR		ISO code(s) of countries to which the data applies. Can be different from countries of model development, has associated score of geographic relevance
*	geographic_relevance_score	ENUM	mover.geographic_relevance_score_enum	How well the data applies to the countries of application

Table: mover.damage_scale

The damage_scale_ table provides details on damage scales. The dm_scale_ table is called upon by the Fragility Function module.

Req	Field name	Type	Reference table	Description
*	id	INT		Unique number ID
	asset_type	VARCHAR	ged4all.asset	Type of asset
	occupancy	ENUM	cf_common.occupancy_enum	Type of occupancy
	hazard_type	VARCHAR	cf_common.hazard_type	Type of hazard
	process_type	VARCHAR	cf_common.process_type	Type of hazard process
	asset_notes	VARCHAR		Additional info on asset
	taxonomy_source	VARCHAR		Source of taxonomy
	taxonomy	VARCHAR		Name of taxonomy
*	n_dm_states	CHAR(1)		Number of damage states that the damage scale studies
*	dm_states_id	VARCHAR		Lists all the damage states as they are identified in the specific damage scale, e.g. 1;2;3;4
*	dm_states_name	VARCHAR		List all damage states names as they are identified in the specific damage scale, e.g. 1;2;3;4. Different damage scales will have different names associated to a damage state
	damage_scale_name	VARCHAR		Name of the damage scale in use
	damage_scale_code	VARCHAR		Code to identify the damage scale
	damage_scale_type	ENUM	mover.dm_scale_ty_enum	Type of damage scale in use
	dm_scale_reference	VARCHAR		Reference study of the damage scale (Author_Years e.g. Crowley et al_2004)
*	is_edp_thre	BOOLEAN		Is the damage scale is associated to an EDP threshold?
*	is_dm_factor	BOOLEAN		Is the damage scale is associated to a damage factor?
*	is_casualties	BOOLEAN		Is the damage scale is associated to a casualties estimation?
*	is_downtime	BOOLEAN		Is the damage scale is associated to a downtime estimation (e.g. service interruption)?

Table: mover.edp_table

The edp_table provides details of Engineering Demand Parameters (EDP) for analytical fragility functions. The edp_table is called upon by the Fragility Function module. In analytical approaches, Engineering Demand Parameters (EDP) are typically used as a proxy of damage level, with EDPs chosen such that they are indicative of the damage state of the entire asset. For instance, in earthquake engineering ranges of values of roof drift or inter-storey drift are commonly adopted to represent specific damage states.

Req	Field name	Type	Description
*	edp_code	VARCHAR	Unique alphanumeric code
	edp_name	VARCHAR	Engineering Demand Parameter (EDP) name (e.g. peak flow acceleration)
*	description	VARCHAR	Engineering Demand Parameter (EDP) description
	units	VARCHAR	Unit of measure of the EDP

Table: mover.lp_table

The lp_table_ describes Loss Parameters (LP) and the units used to describe the loss in a vulnerability curve.

Req	Field name	Type	Description
*	lp_code	VARCHAR	Loss parameter unique code
	lp_name	VARCHAR	Loss parameter name
*	description	VARCHAR	Loss parameter description
	units	VARCHAR	Unit of measure of the LP

Table: mover.reference_table

The reference_table_ stores all the information necessary to identify reference studies associated to the functions, damage scales, and intensity measures contained in the schema. It is designed to provide the user with a complete bibliography of the reference studies consulted during the data entry process.

Req	Field name	Type	Description
*	author_year	VARCHAR	Reference study of the damage scale (Author_Year e.g. Crowley et al_2004)
*	title	VARCHAR	Title of the publication
	issn	VARCHAR	International Standard Serial Number associated with reference
	doi	VARCHAR	Digital Object Identifier url associated with reference

Types

Includes 17 types related to the definitions and formulas of IM, damage scales, EDPs, and loss parameters are provided in the following sections to facilitate data entry, as these are adopted in pre-populated drop-down menus. Some sub-types apply only to certain main types.

ENUM name	Types	Description
function_type_enum	Fragility Vulnerability Damage-to-Loss	Type of function
f_relationship_enum	Mathematical Discrete	Type of relationship
f_subtype_enum	Empirical Analytical Judgement Hybrid - Analytical/Empirical Hybrid - Analytical/Judgement Hybrid - Empirical/Judgement Hybrid - Analytical HF/LF Code - based	Approach used to build the function Note: HF = High Fidelity LF = Low Fidelity
im_method_enum	Recorded Surveyed Simulated Unknown	Method for collecting the intensity measure
an_analysis_type_enum	Advanced Simplified N/a	Type of analysis for Analytical functions
em_analysis_type_enum	Least squares GLM GAM N/a	Type of analysis for Empirical functions
f_math_enum	Parametric Bespoke N/a	Types of models that apply to Empirical functions
jd_analysis_type_enum	Delphi Cookes N/a	Type of analysis for Judgment functions
f_mathtype_enum	Cumulative Lognormal Cumulative Normal Exponential Bespoke DtL - Beta PDF DtL - Normal PDF DtL - Lognormal PDF DtL - Uniform PDF DtL - Bespoke PDF N/a	Mathematical model utilized for the derivation of the function Note: Dtl = Damage-to-loss
sim_method_enum	Physics-based IMPE	Type of simulation
dm_scale_ty_enum	Existing Bespoke Unknown	Type of damage scale
fit_ref_enum	AIC BIC Kolmogorov-Smirnov	Reference model for fitting
scale_app_enum	Country (Level 0) Sub-country (Level 1) Local (Level 2) Asset (Level 3)	Geographic scale applicability
geographic_relevance_score	Excellent Good Needs improvement Unusable	Overall performance quality for geographic context
sample_enum	Single-asset class Multi-assets classes Single-asset	Type of sampling approach
nonsampling_err_enum	Yes No Unknown	Is there a non-sampling error?
type_nonsampling_err_enum	Under coverage Incomplete data Measurement error Unknown	Type of non-sampling error
damage_states_all_enum	No damage Light damage Minor damage Moderate damage Major damage Heavy damage Complete damage Collapsed Washed away Not applicable	Range of damage states (DEV NOTE: supposedly to include all damage states from all functions included; this is not sustainable)