gd-lms

Tables (24)

assignment

  • assignment_id — BIGINT
  • lecture_id — BIGINT — Foreign key → lecture.lecture_id — NOT NULL
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT
  • due_date — DATETIME — NOT NULL
  • max_score — INT
  • created_at — DATETIME
  • updated_at — DATETIME
  • is_deleted — ENUM('Y','N') — NOT NULL

assignment_submission

  • submission_id — BIGINT
  • assignment_id — BIGINT — Foreign key → assignment.assignment_id — NOT NULL
  • student_id — BIGINT — Foreign key → user.user_id — NOT NULL
  • content — TEXT
  • score — INT
  • feedback — TEXT
  • submitted_at — DATETIME
  • graded_at — DATETIME

attendance

  • attendance_id — BIGINT
  • session_id — BIGINT — Foreign key → lecture_session.session_id — NOT NULL
  • student_id — BIGINT — Foreign key → user.user_id — NOT NULL
  • status — ENUM('ABSENT','PRESENT','LATE') — NOT NULL
  • checked_at — DATETIME
  • created_at — DATETIME

auth_token

  • id — BIGINT
  • user_id — BIGINT — Foreign key → user.user_id — NOT NULL
  • token_value — VARCHAR(255) — NOT NULL
  • token_type — VARCHAR(30) — NOT NULL
  • issued_ip — VARCHAR(45)
  • created_at — DATETIME — NOT NULL
  • expires_at — DATETIME — NOT NULL
  • verified_at — DATETIME

automation_log

  • id — BIGINT
  • job_code — VARCHAR(50) — NOT NULL
  • run_date — DATE — NOT NULL
  • status — ENUM('SUCCESS','FAIL') — NOT NULL
  • message — TEXT
  • created_at — DATETIME — NOT NULL
  • INDEX — idx_run_date

building

  • building_id — BIGINT — 주요 소속/용도
  • building_code — VARCHAR(10) — NOT NULL — 총 층수
  • building_name — VARCHAR(80) — NOT NULL — 강의실 수
  • building_use — VARCHAR(120) — NOT NULL — 실습실 수
  • floors — INT — NOT NULL — 생성일시
  • lecture_rooms — INT — NOT NULL — 시설 PK
  • lab_rooms — INT — NOT NULL — 소속 건물 ID
  • created_at — TIMESTAMP — NOT NULL — 시설명 (예: 회의실, 대강당, 서버실)

building_facility

  • facility_id — BIGINT — 생성일시
  • building_id — BIGINT — Foreign key → building.building_id — NOT NULL — 학사일정 PK
  • facility_name — VARCHAR(80) — NOT NULL — 학사일정 코드 (ScheduleCode enum 1:1)
  • quantity — INT — 일정 제목
  • created_at — TIMESTAMP — NOT NULL — 시작일
  • =건물 — 특수시설(안내/설비용

college

  • college_id — BIGINT — 단과대학명
  • college_name — VARCHAR(80) — NOT NULL — 생성일시
  • created_at — TIMESTAMP — NOT NULL — 학과 PK

department

  • department_id — BIGINT — 학과명
  • college_id — BIGINT — Foreign key → college.college_id — NOT NULL — 학과 연간 입학 정원
  • department_name — VARCHAR(50) — NOT NULL — 학과 코드 (예: CSE, BUS, ENG 등)
  • annual_quota — INT — NOT NULL — 생성일시
  • department_code — VARCHAR(20) — 건물 PK
  • created_at — TIMESTAMP — NOT NULL — 건물 번호 (G-01)

enrollment

  • enrollment_id — BIGINT — Primary key — NOT NULL — 연구실
  • lecture_id — BIGINT — Foreign key → lecture.lecture_id — NOT NULL — 연구실 전화번호
  • user_id — BIGINT — NOT NULL — 임용일
  • status — ENUM('ENROLLED', 'DROPPED') — NOT NULL — 생성일
  • created_at — DATETIME — NOT NULL — 수정일
  • updated_at — DATETIME — NOT NULL — 질문_식별자(PK)

file_upload

  • file_id — BIGINT
  • board_type — VARCHAR(50) — NOT NULL
  • ref_id — BIGINT — NOT NULL
  • uuid — VARCHAR(36) — NOT NULL
  • original_filename — VARCHAR(255) — NOT NULL
  • uploaded_at — DATETIME
  • is_deleted — CHAR(1)

instructor

  • user_id — BIGINT — Foreign key → user.user_id — 작성자_식별자(FK user) - 학생
  • instructor_no — VARCHAR(30) — NOT NULL — 제목
  • department_id — BIGINT — Foreign key → department.department_id — NOT NULL — 질문 내용
  • office_room — VARCHAR(50) — 비공개 여부
  • office_phone — VARCHAR(20) — 처리상태
  • hire_date — DATE — 삭제여부(소프트)
  • created_at — DATETIME — NOT NULL — 생성일시
  • updated_at — DATETIME — NOT NULL — 수정일시

lecture

  • lecture_id — BIGINT — 강의실
  • lecture_title — VARCHAR(200) — NOT NULL — 정원
  • lecture_round — INT — NOT NULL — 진행 상태
  • user_id — BIGINT — Foreign key → user.user_id — NOT NULL — 승인 상태
  • department_id — BIGINT — Foreign key → department.department_id — NOT NULL — 생성일시
  • start_date — DATE — NOT NULL — 수정일시
  • end_date — DATE — NOT NULL — 분반
  • room — VARCHAR(100) — 시간표 식별자
  • capacity — INT — NOT NULL — 강의 식별자
  • status — ENUM('PLANNED', 'ONGOING', 'ENDED', 'CLOSED') — NOT NULL — 요일
  • validation — ENUM('CONFIRMED', 'PENDING', 'CANCELED') — NOT NULL — 시작 시간
  • created_at — DATETIME — NOT NULL — 종료 시간
  • updated_at — DATETIME — NOT NULL — 생성일시
  • section — CHAR(1) — NOT NULL — 수강_식별자(PK)

lecture_attendance_status

  • session_id — BIGINT — Foreign key → lecture_session.session_id
  • is_open — BOOLEAN — NOT NULL
  • opened_at — DATETIME
  • closed_at — DATETIME

lecture_schedule

  • schedule_id — BIGINT — 수강생_식별자(FK user, role=STUDENT)
  • lecture_id — BIGINT — Foreign key → lecture.lecture_id — NOT NULL — 수강상태
  • week_day — ENUM('MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN') — NOT NULL — 생성일시
  • start_time — TIME — NOT NULL — 수정일시
  • end_time — TIME — NOT NULL — users.user_id (PK, FK)
  • created_at — DATETIME — NOT NULL — 강사 교번

lecture_session

  • session_id — BIGINT
  • lecture_id — BIGINT — Foreign key → lecture.lecture_id — NOT NULL
  • session_date — DATE — NOT NULL
  • start_time — TIME — NOT NULL
  • end_time — TIME — NOT NULL
  • created_at — DATETIME

notice

  • notice_id — BIGINT
  • lecture_id — BIGINT — Foreign key → lecture.lecture_id
  • author_id — BIGINT — Foreign key → user.user_id — NOT NULL
  • notice_type — VARCHAR(50)
  • title — VARCHAR(200) — NOT NULL
  • content — TEXT — NOT NULL
  • view_count — INT
  • pinned — CHAR(1)
  • is_deleted — CHAR(1)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

qna_posts

  • qna_id — BIGINT — Primary key — NOT NULL — 출석 배점 (%)
  • lecture_id — BIGINT — Foreign key → lecture.lecture_id — NOT NULL — 과제 배점 (%)
  • author_id — BIGINT — Foreign key → user.user_id — NOT NULL — 중간 배점 (%)
  • title — VARCHAR(200) — NOT NULL — 기말 배점 (%)
  • content — TEXT — NOT NULL — 배점 확정 여부
  • is_private — ENUM('Y', 'N') — NOT NULL — 생성일
  • status — ENUM('OPEN', 'ANSWERED', 'CLOSED') — NOT NULL — 수정일
  • is_deleted — ENUM('Y', 'N') — NOT NULL — 강의 ID
  • created_at — DATETIME — NOT NULL — 학생 ID
  • updated_at — DATETIME — NOT NULL — 출석 점수

room

  • room_id — BIGINT — 정원
  • building_id — BIGINT — Foreign key → building.building_id — NOT NULL — 표시 이름
  • floor_no — INT — NOT NULL — 강의 식별자
  • room_no — VARCHAR(10) — NOT NULL — 강의명
  • room_code — VARCHAR(20) — NOT NULL — 강의 차수
  • room_type — ENUM('LECTURE', 'LAB') — NOT NULL — 강사 식별자
  • capacity — INT — NOT NULL — 소속 학과 ID
  • room_name — VARCHAR(100) — NOT NULL — 시작일
  • created_at — TIMESTAMP — NOT NULL

schoolSchedule

  • id — BIGINT — 일정 상세 메모
  • schedule_code — ENUM( 'LECTURE_OPEN_REQUEST', 'LECTURE_OPEN_REVIEW_DEPT', 'LECTURE_OPEN_REVISION_WINDOW', 'LECTURE_OPEN_APPROVAL_ADMIN', 'INSTRUCTOR_ASSIGN_CONFIRM', 'SECTION_QUOTA_CONFIRM', 'TIMETABLE_BUILD_DEPT', 'CLASSROOM_ASSIGN_ANNOUNCE', 'CLASSROOM_ASSIGN_FINAL', 'SYLLABUS_FINAL_INPUT', 'LMS_COURSE_SPACE_AUTO_CREATE', 'COURSE_REG_FRESHMAN', 'COURSE_REG_ENROLLED', 'COURSE_ADD_DROP', 'SEMESTER_START', 'CLASS_DAYS', 'SEMESTER_END', 'MIDTERM_EXAM', 'FINAL_EXAM', 'MIDTERM_GRADE_APPEAL', 'FINAL_GRADE_APPEAL', 'GRADE_INPUT_INSTRUCTOR' ) — NOT NULL — 생성일
  • title — VARCHAR(100) — NOT NULL — 수정일
  • start_date — DATE — NOT NULL — 강의실/공간 PK
  • end_date — DATE — NOT NULL — 소속 건물 ID
  • memo — TEXT — 층
  • created_at — DATETIME — NOT NULL — 호수
  • updated_at — DATETIME — NOT NULL — 강의실 코드 (G-05-401)
  • INDEX — idx_schedule_code
  • INDEX — idx_schedule_start
  • INDEX — idx_schedule_period
  • INDEX — idx_schedule_code_start

score

  • score_id — BIGINT
  • lecture_id — BIGINT — Foreign key → lecture.lecture_id — NOT NULL

score_policy

  • score_policy_id — BIGINT
  • lecture_id — BIGINT — Foreign key → lecture.lecture_id — NOT NULL — 중간고사 점수
  • attendance_weight — INT — NOT NULL — 기말고사 점수
  • assignment_weight — INT — NOT NULL — 총점
  • midterm_weight — INT — NOT NULL — 학점 (A+, A, B, C …)
  • final_weight — INT — NOT NULL — 모든 성적 입력 완료 여부
  • is_confirmed — BOOLEAN — 교수 성적 확정 여부
  • created_at — DATETIME — 생성일
  • updated_at — DATETIME — 수정일

student

  • student_id — BIGINT
  • department_id — BIGINT — Foreign key → department.department_id — NOT NULL
  • user_id — BIGINT — Foreign key → user.user_id — NOT NULL
  • student_number — INT — NOT NULL
  • student_grade — INT
  • status — ENUM('UNDERGRADUATE','GRADUATE') — NOT NULL
  • student_status — ENUM('ENROLLED','LEAVE','GRADUATED') — NOT NULL
  • enroll_date — DATETIME
  • end_date — DATETIME
  • tuition_account — VARCHAR(255)

user

  • user_id — BIGINT — 사용자 식별자
  • login_id — VARCHAR(50) — NOT NULL — 로그인 아이디
  • password_hash — VARCHAR(255) — NOT NULL — 비밀번호 해시
  • name — VARCHAR(50) — NOT NULL — 이름
  • birth_date — DATE — 생년월일
  • email — VARCHAR(100) — NOT NULL — 이메일
  • phone — VARCHAR(20) — 전화번호
  • role — ENUM('ADMIN', 'INSTRUCTOR', 'STUDENT') — NOT NULL — 역할
  • status — ENUM('ACTIVE', 'INACTIVE') — NOT NULL — 상태
  • gender — ENUM('M', 'F') — 성별
  • address — VARCHAR(255) — 주소
  • must_change_pw — BOOLEAN — NOT NULL — 비밀번호 변경 필수 여부
  • created_at — DATETIME — NOT NULL — 생성일시
  • updated_at — DATETIME — NOT NULL — 수정일시