create table host_table (
  ho_id          int         not null,
  ho_name        varchar(30) not null,
  ho_id_ref      int         not null,
  ho_cluster_gen int         not null,
  ho_cpu         varchar(30) not null,
  primary key (ho_id),
  unique(ho_name)
);

create table plan_generator (
  pg_id    int         not null,
  pg_char  char        not null,
  pg_name  varchar(30) not null,
  primary key (pg_id),
  unique(pg_char),
  unique(pg_name)
);

create table card_provider (
  cp_id    int         not null,
  cp_name  varchar(30) not null,
  primary key (cp_id),
  unique(cp_name)
);

create table build_plan_class (
  bpc_id    int         not null,
  bpc_name  varchar(30) not null,
  primary key (bpc_id),
  unique (bpc_name)
);

create table build_plan_instance (
  bpi_id             int         not null,
  bpi_bpc            int         not null,
  bpi_cf_join_bundle int         not null,
  bpi_cf_mat         int,
  bpi_cf_concat      int,
  primary key (bpi_id),
  foreign key (bpi_bpc) references build_plan_class (bpc_id)
);

create table join_impl (
  ji_id          int         not null,
  ji_kind        int         not null,
  ji_flag_left   int         not null,
  ji_flag_right  int         not null,
  ji_name        varchar(20) not null,
  primary key (ji_id)
);

create table sch_relation (
  sr_id     int         not null,
  sr_name   varchar(30) not null,
  sr_card   BIGINT      not null,
  sr_relset BIGINT      not null,
  primary key (sr_id),
  unique (sr_name)
);

create table sch_attribute (
  sa_rel          int         not null,
  sa_lno          int         not null,
  sa_name         varchar(30) not null,
  sa_type_id      int         not null,
  sa_nullable     int         not null,
  sa_nodv         bigint      not null,
  sa_nodv_est     bigint      not null,
  sa_no_null      bigint      not null,
  sa_max_freq     bigint,
  sa_cnt_min      bigint,
  sa_cnt_max      bigint,
  sa_cnt_nodv     bigint,
  sa_cnt_nodv_est bigint,
  sa_cnt_mean     double,
  sa_cnt_std_dev  double,
  sa_cnt_skewness double,
  sa_cnt_kurtosis double,
  primary key (sa_rel, sa_lno),
  unique      (sa_rel, sa_name),
  foreign key (sa_rel) references sch_relation (sr_id)
);

create table work_package (
  wp_id  bigint not null,
  wp_no     int not null,
  wp_pg     int not null,
  wp_cp     int not null,
  wp_bp     int not null,
  primary key (wp_id),
  unique      (wp_no,wp_pg,wp_cp,wp_bp),
  foreign key (wp_pg) references plan_generator (pg_id),
  foreign key (wp_cp) references card_provider (cp_id),
  foreign key (wp_bp) references build_plan_instance (bpi_id)
);

create table query_graph (
  qg_name               varchar(30) not null,
  qg_no_node            int         not null,
  qg_no_edge            int         not null,
  qg_no_csg             bigint      not null,
  qg_no_ccp             bigint      not null,
  qg_no_log_plan        bigint      not null,
  qg_is_connected       int         not null,
  qg_is_acyclic         int         not null,
  qg_is_cyclic_directed int         not null,
  qg_is_acyclic_hyper   int         not null,
  qg_is_star            int         not null,
  qg_is_chain           int         not null,
  primary key (qg_name)
);

create table qg_node (
  qgn_qg         varchar(30) not null,
  qgn_id         int         not null,
  qgn_lno        int         not null,
  qgn_name_corr  varchar(30) not null,
  qgn_card_base  bigint      not null,
  qgn_card_sel   bigint      not null,
  primary key (qgn_qg, qgn_id),
  foreign key (qgn_lno) references sch_relation (sr_id)
);

create table qg_edge (
  qge_qg          varchar(30) not null,
  qge_id          int         not null,
  qge_left        int         not null,
  qge_right       int         not null,
  qge_selectivity double      not null,
  qge_uniq_both   int         not null,
  qge_uniq_left   int         not null,
  qge_uniq_right  int         not null,
  primary key (qge_qg, qge_id),
  foreign key (qge_qg)            references query_graph (qg_name),
  foreign key (qge_qg, qge_left)  references qg_node(qgn_qg, qgn_id),
  foreign key (qge_qg, qge_right) references qg_node(qgn_qg, qgn_id),
);

create table qg_join_attribute (
  jat_qg        varchar(30) not null,
  jat_id        int         not null,
  jat_node      int         not null,
  jat_sch_rel   int         not null,
  jat_sch_attr  int         not null,
  jat_eqv_class int         not null,
  primary key (jat_qg, jat_id),
  foreign key (jat_qg)                    references query_graph(qg_name),
  foreign key (jat_qg, jat_node)          references qg_node(qgn_qg, qgn_id),
  foreign key (jat_sch_rel, jat_sch_attr) references sch_attribute(sa_rel, sa_lno)
);

create table pc_desc (
  pcd_qg         varchar(30)   not null,
  pcd_pc         bigint        not null,
  pcd_size       int           not null,
  pcd_card       bigint        not null,
  pcd_pc_rel     bigint        not null,
  pcd_string     varchar(1777) not null,
  primary key (pcd_qg, pcd_pc),
  foreign key (pcd_qg) references query_graph (qg_name)
);

create table pc_member (
  pcm_qg    varchar(30) not null references query_graph,
  pcm_pc    bigint      not null,
  pcm_node  int         not null,
  primary key (pcm_qg, pcm_pc, pcm_node),
  foreign key (pcm_qg, pcm_pc) references pc_desc(pcd_qg, pcd_pc),
  foreign key (pcm_qg)         references query_graph(qg_name),
  foreign key (pcm_qg, pcm_node) references qg_node (qgn_qg, qgn_id)
);

create table pc_card (
  pcc_qg          varchar(30) not null,
  pcc_pc          bigint not null,
  pcc_meas_lb     bigint not null,
  pcc_meas_ub     bigint not null,
  pcc_card_true   double not null,
  pcc_card_ia_m   double,
  pcc_card_ia_s   double,
  pcc_card_ia_l   double,
  pcc_card_alt    double,
  pcc_card_c_bse  double,
  pcc_card_c_sel  double,
  pcc_card_j_bse  double,
  pcc_card_j_s30  double,
  pcc_card_j_sel  double,
  pcc_card_u_bse  double,
  pcc_card_u_sel  double,
  pcc_card_other  double,
  pcc_qerr_ia_m   double,
  pcc_qerr_ia_s   double,
  pcc_qerr_ia_l   double,
  pcc_qerr_alt    double,
  pcc_qerr_c_bse  double,
  pcc_qerr_c_sel  double,
  pcc_qerr_j_bse  double,
  pcc_qerr_j_s30  double,
  pcc_qerr_j_sel  double,
  pcc_qerr_u_bse  double,
  pcc_qerr_u_sel  double,
  pcc_qerr_other  double,
  primary key (pcc_qg, pcc_pc),
  foreign key (pcc_qg) references query_graph (qg_name),
  foreign key (pcc_qg, pcc_pc) references pc_desc(pcd_qg, pcd_pc)
);

create table csg_cmp_pair (
  ccp_qg         varchar(30) not null,
  ccp_pc_1       bigint      not null,
  ccp_pc_2       bigint      not null,
  ccp_union      bigint      not null,
  ccp_uniq_1     int         not null,
  ccp_uniq_2     int         not null,
  ccp_nodv_1     bigint      not null,
  ccp_nodv_2     bigint      not null,
  ccp_join_best  int         not null,
  ccp_swap_best  int         not null,
  ccp_cost_best  double      not null,
  ccp_join_worst int         not null,
  ccp_swap_worst int         not null,
  ccp_cost_worst double      not null,
  foreign key (ccp_join_best)  references join_impl (ji_id),
  foreign key (ccp_join_worst) references join_impl (ji_id)
);

create table plan_summary (
  ps_id                bigint      not null,
  ps_qg                varchar(30) not null,
  ps_wp                bigint      not null,
  ps_cost_tru          double      not null,
  ps_cost_tru_s        double      not null,
  ps_cost_pg           double      not null,
  ps_opt_cost_tru      double      not null,
  ps_opt_cost_pg       double      not null,
  ps_loss_factor       double      not null,
  ps_qerr_cost_pg      double      not null,
  ps_qerr_opt_cost_pg  double      not null,
  ps_pmej              double      not null,
  ps_sum_card_pc       bigint      not null,
  ps_max_card_pc       bigint      not null,
  ps_sum_card_build    bigint      not null,
  ps_max_card_build    bigint      not null,
  ps_sum_card_probe    bigint      not null,
  ps_max_card_probe    bigint      not null,
  ps_no_j_l            int         not null,
  ps_no_j_r            int         not null,
  ps_no_j_b            int         not null,
  ps_depth             int         not null,
  ps_nm_join_level     int         not null,
  ps_max_size_pc_nosel int         not null,
  ps_base_rel_right    bigint      not null,
  ps_runtime_ms        double      not null,
  ps_plan              varchar(7777),       
  ps_plan_log          varchar(7777),       
  primary key (ps_id),
  foreign key (ps_qg) references query_graph (qg_name),
  foreign key (ps_wp) references work_package (wp_id)
);

create table plan_op_join (
  poj_id                bigint  not null,
  poj_ps                bigint  not null,
  poj_ji                int     not null,
  poj_uniq_bld          int     not null,
  poj_uniq_prb          int     not null,
  poj_uniq              int     not null,
  poj_depth             int     not null,
  poj_level             int     not null,
  poj_left              bigint  not null,
  poj_right             bigint  not null,
  poj_pc_left           bigint  not null,
  poj_pc_right          bigint  not null,
  poj_pc                bigint  not null,
  poj_card_tru          double  not null,
  poj_card_pg           double  not null,
  poj_card_qerr         double  not null,
  poj_cost_tru          double  not null,
  poj_cost_tru_perc     double  not null,
  poj_cost_pg           double  not null,
  poj_cost_opt_tru      double  not null,
  poj_cost_opt_pg       double  not null,
  poj_cost_loc_tru      double  not null,
  poj_cost_loc_pg       double  not null,
  poj_cost_loc_opt_tru  double  not null,
  poj_cost_loc_opt_pg   double  not null,
  poj_qerr_cost_pg      double  not null,
  poj_qerr_cost_opt     double  not null,
  poj_qerr_cost_loc_pg  double  not null,
  poj_qerr_cost_loc_opt double  not null,
  poj_loss_factor       double  not null,
  poj_loss_abs          double  not null,
  poj_loss_rel          double  not null,
  poj_loss_loc          double  not null,
  poj_loss_loc_rel      double  not null,
  poj_loss_loc_bp       double  not null,
  poj_loss_loc_bp_rel   double  not null,
  poj_sum_loss_loc      double  not null,
  poj_ratio_case        int     not null,
  poj_ratio_qr          double  not null,
  poj_ey_w              char    not null,
  poj_ey_pc             char(2) not null,
  primary key (poj_id, poj_ps),
  foreign key (poj_ps)  references plan_summary (ps_id),
  foreign key (poj_ji)  references join_impl    (ji_id),
);

create table join2qgedge (
  j2e_jid  bigint      not null,
  j2e_ps   bigint      not null,
  j2e_qg   varchar(30) not null,
  j2e_eid  int         not null,
  foreign key (j2e_jid, j2e_ps) references plan_op_join (poj_id, poj_ps),
  foreign key (j2e_ps)          references plan_summary(ps_id),
  foreign key (j2e_qg)          references query_graph (qg_name),
  foreign key (j2e_qg, j2e_eid) references qg_edge (qge_qg, qge_id)
);

create table plan_exec (
  pe_host_id           int         not null,
  pe_qg                varchar(30) not null,
  pe_wp                bigint      not null,
  pe_sp_opt            int         not null,
  pe_old_like          int         not null,
  pe_codgen_kind       int         not null,
  pe_cost_tru          double      not null,
  pe_exec_no_run       int         not null,
  pe_exec_cc_min       double      not null,
  pe_exec_cc_max       double      not null,
  pe_exec_cc_avg       double      not null,
  pe_exec_cc_med       double      not null,
  pe_exec_res_card     bigint      not null,
  pe_exec_min_s        double      not null,
  pe_scan_cc_min       double      not null,
  pe_scan_cc_max       double      not null,
  pe_scan_min_s        double      not null,
  pe_res_card          bigint      not null,
  pe_lf_exec           double      not null,
  foreign key (pe_qg) references query_graph (qg_name),
  foreign key (pe_host_id) references host_table(ho_id),
  foreign key (pe_wp) references work_package (wp_id)
);

create table scan_exec (
  se_host_id      int         not null,
  se_qg           varchar(30) not null,
  se_rel_id       int         not null,
  se_sp_opt       int         not null,
  se_old_like     int         not null,
  se_no_run       int         not null,
  se_cc_min       double      not null,
  se_cc_max       double      not null,
  se_cc_avg       double      not null,
  se_cc_med       double      not null,
  se_res_card     bigint      not null,
  se_min_s        double      not null,
  primary key (se_host_id, se_qg, se_rel_id, se_sp_opt, se_old_like),
  foreign key (se_host_id) references host_table(ho_id),
  foreign key (se_qg,se_rel_id) references qg_node (qgn_qg, qgn_id),
  foreign key (se_qg) references query_graph (qg_name)
);

