day_1

Tables (9)

change_history (변경 이력)

  • id — BIGSERIAL — Primary key — NOT NULL
  • target_type — VARCHAR(2) — NOT NULL
  • target_id — BIGINT — NOT NULL — 다형 참조. target_type에 따라 student.id 또는 enrollment.id
  • change_type — VARCHAR(4) — NOT NULL
  • before_value — VARCHAR(50)
  • after_value — VARCHAR(50)
  • effective_date — DATE
  • term_id — BIGINT — Foreign key → term.id
  • reason — TEXT — NOT NULL
  • changed_by — BIGINT — NOT NULL
  • changed_at — TIMESTAMPTZ — NOT NULL

course (분반)

  • id — BIGSERIAL — Primary key — NOT NULL
  • term_id — BIGINT — Foreign key → term.id — NOT NULL
  • subject_id — BIGINT — Foreign key → subject.id — NOT NULL
  • professor_id — BIGINT — Foreign key → professor.id
  • division_no — SMALLINT — NOT NULL
  • subject_name_snapshot — VARCHAR(150) — NOT NULL — 개설 시점 교과목명 복사 — 교과 개편돼도 과거 성적표 불변
  • credit_snapshot — NUMERIC(3,1) — NOT NULL
  • subject_type_snapshot — VARCHAR(10) — NOT NULL
  • grading_type — VARCHAR(2) — NOT NULL
  • status — VARCHAR(2) — NOT NULL
  • capacity — INTEGER — NOT NULL
  • enrolled_count — INTEGER — NOT NULL — 조건부 UPDATE로 정원 초과를 원자적으로 차단
  • version — INTEGER — NOT NULL
  • schedule_text — VARCHAR(100)
  • schedule_bitmask — BIGINT — NOT NULL — 요일*12+교시를 비트로 접은 값. 시간표 충돌을 AND 1회로 판정
  • classroom — VARCHAR(50)
  • created_at — TIMESTAMPTZ — NOT NULL

department (학과)

  • id — BIGSERIAL — Primary key — NOT NULL
  • dept_code — VARCHAR(20) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • college_name — VARCHAR(100)
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

enrollment (수강 신청)

  • id — BIGSERIAL — Primary key — NOT NULL
  • student_id — BIGINT — Foreign key → student.id — NOT NULL
  • course_id — BIGINT — Foreign key → course.id — NOT NULL
  • term_id — BIGINT — Foreign key → term.id — NOT NULL — 파티션 키. course를 조인하지 않고 학기 필터하기 위한 중복 컬럼
  • status — VARCHAR(2) — NOT NULL
  • credit_snapshot — NUMERIC(3,1) — NOT NULL
  • is_retake — BOOLEAN — NOT NULL
  • applied_at — TIMESTAMPTZ — NOT NULL
  • canceled_at — TIMESTAMPTZ
  • score — NUMERIC(5,2)
  • grade_code — VARCHAR(3)
  • grade_point — NUMERIC(3,2)
  • grade_status — VARCHAR(2) — NOT NULL — NG미입력 상태로 시작 → 교수 입력(TM) → 확정(CF) → 정정(MD)
  • confirmed_by — BIGINT
  • confirmed_at — TIMESTAMPTZ

professor (교수)

  • id — BIGSERIAL — Primary key — NOT NULL
  • faculty_no — VARCHAR(20) — NOT NULL
  • login_id — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • email — VARCHAR(150)
  • department_id — BIGINT — Foreign key → department.id — NOT NULL
  • position_code — VARCHAR(2) — NOT NULL
  • hired_at — DATE
  • created_at — TIMESTAMPTZ — NOT NULL

student (학생)

  • id — BIGSERIAL — Primary key — NOT NULL
  • student_no — BIGINT — NOT NULL
  • login_id — VARCHAR(50) — NOT NULL
  • password_hash — VARCHAR(255) — NOT NULL
  • name — VARCHAR(50) — NOT NULL
  • email — VARCHAR(150)
  • department_id — BIGINT — Foreign key → department.id — NOT NULL
  • status_code — VARCHAR(2) — NOT NULL — 현재 학적상태. 원본 이력은 change_history(target_type=ST)
  • grade_year — SMALLINT — NOT NULL
  • completed_term — SMALLINT — NOT NULL
  • admitted_at — TIMESTAMPTZ — NOT NULL
  • graduated_at — TIMESTAMPTZ
  • created_at — TIMESTAMPTZ — NOT NULL
  • updated_at — TIMESTAMPTZ

subject (교과목)

  • id — BIGSERIAL — Primary key — NOT NULL
  • subject_code — VARCHAR(20) — NOT NULL
  • name — VARCHAR(150) — NOT NULL
  • department_id — BIGINT — Foreign key → department.id
  • credit — NUMERIC(3,1) — NOT NULL
  • subject_type — VARCHAR(10) — NOT NULL
  • target_grade — SMALLINT
  • is_active — SMALLINT — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

term (학기)

  • id — BIGSERIAL — Primary key — NOT NULL
  • year — SMALLINT — NOT NULL
  • semester_code — VARCHAR(2) — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE — NOT NULL
  • enroll_open_at — TIMESTAMPTZ
  • enroll_close_at — TIMESTAMPTZ
  • grade_confirm_at — TIMESTAMPTZ
  • is_closed — BOOLEAN — NOT NULL
  • created_at — TIMESTAMPTZ — NOT NULL

term_gpa (성적 요약)

  • student_id — BIGINT — Foreign key → student.id — NOT NULL
  • term_id — BIGINT — Foreign key → term.id — NOT NULL
  • attempted_credit — NUMERIC(5,1) — NOT NULL
  • earned_credit — NUMERIC(5,1) — NOT NULL
  • gpa — NUMERIC(3,2) — NOT NULL
  • cumulative_credit — NUMERIC(6,1) — NOT NULL
  • cumulative_gpa — NUMERIC(3,2) — NOT NULL
  • calculated_at — TIMESTAMPTZ — NOT NULL