SchemaSpy Analysis of zfindb.public - Anomalies

Generated by SchemaSpy

Things that might not be 'quite right' about your schema:

• Columns whose name and type imply a relationship to another table's primary key:

  Child Column	Implied Parent Column
  expression_experiment.xpatex_zdb_id	expression_experiment2.xpatex_zdb_id
  expression_experiment_bkup.xpatex_zdb_id	expression_experiment2.xpatex_zdb_id
  expression_experiment_temp.xpatex_zdb_id	expression_experiment2.xpatex_zdb_id
  feature_audit.feature_zdb_id	feature.feature_zdb_id
  feature_audit.id	databasechangeloglock.id
  feature_audit.rev	revinfo.rev
  finalct3.mrkr_zdb_id	marker.mrkr_zdb_id
  finalgo.mrkr_zdb_id	marker.mrkr_zdb_id
  genox_fish_annotation_search.genoxfas_pk_id	genox_fish_annotation_search_bkup.genoxfas_pk_id
  genox_fish_annotation_search_temp.genoxfas_pk_id	genox_fish_annotation_search_bkup.genoxfas_pk_id
  genox_group.gg_group_pk_id	genotype_group.gg_group_pk_id
  genox_group_member.ggm_pk_id	genotype_group_member.ggm_pk_id
  mapped_marker.marker_type	marker_types.marker_type
  mapped_marker.zdb_id	company.zdb_id
  panels.zdb_id	company.zdb_id
  primer_set.zdb_id	company.zdb_id
  publication_contact_tracking.pct_pk_id	processing_checklist_task.pct_pk_id
  return_recs.zdb_id	company.zdb_id
  snp_sequence.seq_mrkr_zdb_id	marker_sequence.seq_mrkr_zdb_id
  tmp_fish490.fish_zdb_id	fish.fish_zdb_id
  tmp_fish_thatfitthebill.fig_zdb_id	figure.fig_zdb_id
  tmp_fish_thatfitthebill.fish_zdb_id	fish.fish_zdb_id
  tmp_fish_thatfitthebill.genox_zdb_id	fish_experiment.genox_zdb_id
  tmp_fishnoeap.fig_zdb_id	figure.fig_zdb_id
  tmp_fishnoeap.fish_zdb_id	fish.fish_zdb_id
  tmp_fishnoeap.genox_zdb_id	fish_experiment.genox_zdb_id
  tmp_genox_restore.genox_zdb_id	fish_experiment.genox_zdb_id
  tmp_go.mrkr_zdb_id	marker.mrkr_zdb_id
  tmp_go.mrkrgoev_zdb_id	marker_go_term_evidence.mrkrgoev_zdb_id
  tmp_go1.mrkr_zdb_id	marker.mrkr_zdb_id
  tmp_go1.mrkrgoev_zdb_id	marker_go_term_evidence.mrkrgoev_zdb_id
  tmp_onlyfish.fish_zdb_id	fish.fish_zdb_id
  tmp_pub2.zdb_id	company.zdb_id

  33 instances of anomaly detected

• Tables without indexes:

  Table	Rows
  aa	111
  abregistry	33
  average_curation_time_metric	42
  average_time_per_person	0
  burgess_location_post	70
  cnechr	113
  cnechr1	113
  cnedata	113
  constructrel	71
  constructreln	24
  databasechangelog	1,499
  do_oevdispupdate_temp_first	0
  do_oevdispupdate_temp_second	0
  dupcrispr	19
  editconstruct	202
  ensdarg_post_proc	12,144
  ensdart_ottdart_mapping	39,237
  execweb	3
  expression_experiment_bkup	159,237
  expression_experiment_temp	159,244
  expression_pattern_figure_bkup	430,388
  expression_pattern_figure_temp	430,405
  expression_phenotype_term_controlled_vocab	8
  expression_result_bkup	430,388
  expression_result_temp	430,405
  featmkr	12,381
  feature_audit	0
  feature_community_contribution_audit	11
  feature_data	116
  feature_stats	639,438
  featuredata	638
  featuregenedata	20
  featurenogenedata	7
  featurezmp	6
  featurezmpdata	5
  fgmdnote	1,513
  final491	2,013
  finalct3	2,181
  finalgo	2,181
  fish_genox	29,250
  ftrconsequence	1,509
  ftrconsequencenew	73,564
  ftrmutdets	811
  ftrmutdetssanger	39,862
  genox_group_member	0
  inputensdarg	17,668
  jenk491	1,001
  jenk491b	2,002
  lines	78
  lines_tmp	63
  linkage_member_temp	5,843
  linkages_copy	454
  longest_bin_resident_metric	1,328
  missing_varseq	301
  missing_xpatfig	33
  monthly_average_curated_metric	68
  newfeaturedata	64
  pheno_term_fast_search	3,010,581
  pheno_term_fast_search_tmp	3,010,581
  phenotype_generated_curated_mapping_bkup	97,710
  phenotype_generated_curated_mapping_temp	97,728
  phenotype_observation_generated_bkup	195,534
  phenotype_observation_generated_temp	195,590
  phenotype_source_generated_bkup	79,864
  phenotype_source_generated_temp	79,878
  pmflankseq	36,815
  pre_db_link	3,793
  pre_feature_marker_relationship	1
  probe_lib	33
  remove_phenos_ids	27
  return_recs	1,749
  sanger_flanking_sequence	37,613
  sanger_flanking_sequence_post_proc	35,210
  sanger_location	112,614
  sanger_location_post	111,476
  sangerfeatures	54
  seqvar	1,620
  single	1
  tmp_addgene	0
  tmp_betterfishdump	3
  tmp_expression_phenotype_term	8
  tmp_feature	694
  tmp_figure	13,726
  tmp_final	1,001
  tmp_fish490	4,592
  tmp_fish_thatfitthebill	873
  tmp_fishnoeap	558
  tmp_ftrchrdate	35
  tmp_ftrchrdate2	730
  tmp_ftrnote	1,376
  tmp_genox_restore	0
  tmp_go	7,238
  tmp_go1	2,185
  tmp_onlydeficiencies	13
  tmp_onlyfish	9,232
  tmp_ottens	3,941
  tmp_pub	4,574
  tmp_pub2	76
  tmp_pub3	0
  tmp_pub5	1,385
  tmp_rnac_zfin	0
  tmp_seqref	36,815
  tmp_to_convert	887
  tmp_zeco_tt	4,249
  tmp_zirc_geno	0
  tmpcne	113
  tscriptens	1,039
  tscriptens1	1,518
  tscriptsequence	1,113
  updates	1,676,689
  xpat_exp_details_generated_bkup	142,551
  xpat_exp_details_generated_temp	0
  xpat_results_generated_bkup	308,334
  xpat_results_generated_temp	0
  zfa_uberon_mapping	55

  115 instances of anomaly detected

• Columns that are flagged as both 'nullable' and 'must be unique':

  Column
  author.author_first_name
  author.author_institution
  author.author_last_name
  bl_plate_tracking.blpt_plate
  candidate.cnd_suggested_name
  company.name
  expression_pattern_assay.xpatassay_abbrev
  htp_dataset.hd_summary
  htp_dataset.hd_summary
  htp_dataset.hd_title
  htp_dataset.hd_title
  htp_dataset_sample.hds_sample_id
  htp_dataset_sample.hds_sample_id
  htp_dataset_sample.hds_sample_title
  htp_dataset_sample.hds_sample_title
  htp_dataset_sample.hds_stage_term_zdb_id
  htp_dataset_sample.hds_stage_term_zdb_id
  linkage_group.lg_order
  map_metric.mapmetric_name
  ontology.ont_ontology_name
  person.email
  probe_library.probelib_name
  pub_tracking_status.pts_status
  pub_tracking_status.pts_status_qualifier
  stage.stg_name
  term_group.tg_genox_group
  xpatfs_old.etfs_term_zdb_id
  xpatfs_old.etfs_xpatres_pk_id
  zdb_submitters.login

  29 instances of anomaly detected

• Tables that contain a single column:

  Table	Column
  antibody_type	atbtype_name
  apato_tag	apatotag_name
  blast_database_type	bdbt_type
  clone_problem_type	cpt_type
  condition	condition_group
  curation_topic	curtopic_name
  editconstruct	cczdb
  featurenogenedata	allele
  genedom_family_name	gfam_name
  heavy_chain_isotype	hviso_name
  image_direction	imgdir_name
  image_form	imgform_name
  image_preparation	imgprep_name
  image_view	imgview_name
  inputensdarg	ensdarg
  light_chain_isotype	ltiso_name
  lines	line_name
  lines_tmp	line_name
  marker_history_event	mhistvnt_name
  marker_history_reason	mhistrsn_name
  mutagee	mutagee_name
  mutagen	mutagen_name
  noctua_model	nm_id
  remove_phenos_ids	phenos_id
  run_type	runtype_name
  sangerfeatures	abbrev
  single	sngl_one
  term_relationship_type	termreltype_name
  tmp_figure	figsource
  tmp_onlydeficiencies	featdef
  tmp_pub	pubzdb
  tmp_pub2	zdb_id
  tmp_pub3	pubzdb
  tmp_pub5	figsource
  tmp_to_convert	gene_id
  zdb_active_data	zactvd_zdb_id
  zdb_active_source	zactvs_zdb_id

  37 instances of anomaly detected

• Tables with incrementing column names, potentially indicating denormalization:

  Table
  aa
  average_curation_time_metric
  constructrel
  constructreln
  daily_indexed_metric
  featuredata
  ftrconsequencenew
  ftrmutdets
  ftrmutdetssanger
  marker_relationship_type
  monthly_average_curated_metric
  monthly_curated_metric
  pmflankseq
  probe_lib
  tmp_efs_map
  tmp_seqref

  16 instances of anomaly detected

• Columns whose default value is the word 'NULL' or 'null', but the SQL NULL value may have been intended:
  Anomaly not detected