ERD_Fish

Tables (13)

catch_predictions (어획량 예측)

  • catch_prediction_id (어획량예측ID) — INT — Primary key — NOT NULL
  • species_id (어종ID) — INT — Foreign key → species.species_id
  • region_id (산지ID) — INT — Foreign key → region.region_id
  • feature_id (특징ID) — INT — Foreign key → feature_set.feature_id
  • target_date (예측대상일자) — DATE
  • predicted_catch (예측어획량) — 예측어획량
  • lower_bound (하한값) — DOUBLE
  • upper_bound (상한값) — DOUBLE
  • mae (평균절대오차) — DOUBLE
  • model_version (모델버전) — VARCHAR(50)

catch_raw (어획량)

  • catch_raw_id (어획량원본ID) — INT — Primary key — NOT NULL
  • species_id (어종ID) — INT — Foreign key → species.species_id
  • region_id (산지ID) — INT — Foreign key → region.region_id
  • catch_date (어획일자) — DATE
  • catch_amount (어획량) — DOUBLE
  • source (출처) — VARCHAR(100)

feature_set (학습용_특징테이블)

  • feature_id (특징ID) — INT — Primary key — NOT NULL
  • species_id (어종ID) — INT — Foreign key → species.species_id
  • region_id (산지ID) — INT — Foreign key → region.region_id
  • base_date (기준일자) — DATE
  • lag_feature (지연특징) — DOUBLE
  • rolling_feature (이동평균특징) — DOUBLE
  • season_feature (계절특징) — DOUBLE
  • weather_feature (날씨특징) — DOUBLE

fisher_view (어민 추천)

  • fisher_view_id (어민추천ID) — INT — Primary key — NOT NULL
  • scenario_id (시나리오ID) — INT — Foreign key → scenarios.scenario_id
  • timing_type (시기유형) — VARCHAR(50)
  • recommendation_text (추천내용) — VARCHAR(500)

predictions_log (저장 이력)

  • log_id (로그ID) — INT — Primary key — NOT NULL
  • scenario_id (시나리오ID) — INT — Foreign key → scenarios.scenario_id
  • saved_at (저장일시) — DATETIME

price_predictions (가격 예측)

  • price_prediction_id (가격예측ID) — INT — Primary key — NOT NULL
  • catch_prediction_id (어획량예측ID) — INT — Foreign key → catch_predictions.catch_prediction_id
  • target_date (예측대상일자) — DATE
  • predicted_price (예측가격) — DOUBLE
  • lower_bound (하한값) — DOUBLE
  • upper_bound (상한값) — DOUBLE
  • mae (평균절대오차) — DOUBLE
  • model_version (모델버전) — VARCHAR(50)
  • feature_id (특징ID) — INT

price_raw (가격)

  • price_raw_id (가격원본ID) — INT — Primary key — NOT NULL
  • species_id (어종ID) — INT — Foreign key → species.species_id
  • region_id (산지ID) — INT — Foreign key → region.region_id
  • price_date (가격일자) — DATE
  • price (가격) — DOUBLE
  • source (출처) — VARCHAR(100)

region (산지)

  • region_id (산지ID) — INT — Primary key — NOT NULL
  • region_name (산지명) — VARCHAR(50)
  • region_type (지역유형) — VARCHAR(20)

reports (리포트)

  • report_id (리포트ID) — INT — Primary key — NOT NULL
  • scenario_id (시나리오ID) — INT — Foreign key → scenarios.scenario_id
  • report_text (리포트내용) — VARCHAR(1000)

scenarios (시나리오)

  • scenario_id (시나리오ID) — INT — Primary key — NOT NULL
  • catch_prediction_id (어획량예측ID) — INT — Foreign key → catch_predictions.catch_prediction_id
  • price_prediction_id (가격예측ID) — INT — Foreign key → price_predictions.price_prediction_id
  • scenario_type (시나리오유형) — VARCHAR(50)

species (어종)

  • species_id (어종ID) — INT — Primary key — NOT NULL
  • species_name (어종명) — VARCHAR(50)

warehouse_view (물류창고 추천)

  • warehouse_view_id (창고추천ID) — INT — Primary key — NOT NULL
  • scenario_id (시나리오ID) — INT — Foreign key → scenarios.scenario_id
  • recommended_space (추천공간) — VARCHAR(100)
  • recommendation_text (추천내용) — text

weather_raw (날씨원본)

  • weather_raw_id (날씨원본ID) — INT — Primary key — NOT NULL
  • region_id (산지ID2) — INT — Foreign key → region.region_id — NOT NULL
  • observed_date (관측일자) — DATE
  • wave_height (파고) — DOUBLE
  • wind_speed (풍속) — DOUBLE
  • source (출처) — VARCHAR(100)
  • station_id (관측지점코드) — VARCHAR(50)
  • water_temp (수온) — DOUBLE