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