cms-erd-p

Tables (24)

administrative_departments

  • admin_dept_id — INT — Primary key
  • dept_name — VARCHAR(100) — NOT NULL
  • dept_code — VARCHAR(20) — NOT NULL
  • parent_id — INT
  • dept_head_id — INT
  • description — TEXT
  • phone — VARCHAR(20)
  • location — VARCHAR(100)
  • is_active — BOOLEAN
  • sort_order — INT

assessment_questions

  • question_id — INT — Primary key
  • assessment_id — INT — NOT NULL
  • question_text — TEXT — NOT NULL
  • question_type — ENUM('객관식', '주관식', '척도형') — NOT NULL
  • options — JSON
  • question_order — INT — NOT NULL
  • category — VARCHAR(50)

career_assessments

  • assessment_id — INT — Primary key
  • assessment_name — VARCHAR(200) — NOT NULL
  • assessment_type — ENUM('진로탐색', '성격검사', '역량평가', '적성검사', '가치관검사') — NOT NULL
  • description — TEXT
  • total_questions — INT — NOT NULL
  • time_limit — INT
  • is_active — BOOLEAN
  • created_at — TIMESTAMP

colleges

  • college_id — INT — Primary key
  • college_name — VARCHAR(100) — NOT NULL
  • college_code — VARCHAR(10) — NOT NULL
  • description — TEXT
  • dean_id — INT
  • established_date — DATE
  • is_active — BOOLEAN
  • sort_order — INT
  • created_at — TIMESTAMP

companies

  • company_id — INT
  • company_name — VARCHAR(200) — NOT NULL
  • business_number — VARCHAR(12)
  • industry_code — VARCHAR(10)
  • company_type — ENUM('대기업', '중견기업', '중소기업', '스타트업', '공기업', '외국계')
  • company_size — ENUM('50인미만', '50-99인', '100-299인', '300-999인', '1000인이상')
  • headquarters_location — VARCHAR(200)
  • website — VARCHAR(200)
  • established_year — YEAR
  • annual_revenue — BIGINT
  • description — TEXT
  • welfare_benefits — TEXT
  • company_culture — TEXT
  • is_partner — BOOLEAN
  • recruitment_manager_info — JSON
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

counseling_records

  • record_id — INT
  • session_id — INT — NOT NULL
  • main_issues — JSON
  • counseling_content — TEXT — NOT NULL
  • assessment_results — TEXT
  • action_plans — TEXT
  • recommendations — TEXT
  • follow_up_needed — BOOLEAN
  • follow_up_date — DATE
  • next_session_scheduled — DATE
  • student_satisfaction — INT
  • counselor_notes — TEXT
  • confidentiality_level — ENUM('일반', '민감', '극비')
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

counseling_sessions

  • session_id — INT
  • student_id — INT — NOT NULL
  • counselor_id — INT
  • professor_id — INT
  • staff_id — INT
  • counseling_type_id — INT — NOT NULL
  • appointment_date — DATETIME — NOT NULL
  • actual_start_time — DATETIME
  • actual_end_time — DATETIME
  • status — ENUM('예약', '진행중', '완료', '취소', '노쇼')
  • location — VARCHAR(100)
  • method — ENUM('대면', '비대면', '전화')
  • student_request — TEXT
  • pre_questionnaire — JSON
  • created_at — TIMESTAMP

counseling_types

  • type_id — INT
  • type_name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • required_counselor_specialization — VARCHAR(100)
  • session_duration — INT
  • is_active — BOOLEAN

counselors

  • counselor_id — INT — Primary key
  • user_id — INT — NOT NULL
  • employee_number — VARCHAR(20) — NOT NULL
  • specialization — JSON — NOT NULL
  • license_info — JSON
  • office_location — VARCHAR(100)
  • office_phone — VARCHAR(20)
  • available_schedule — JSON
  • max_daily_sessions — INT
  • hire_date — DATE

departments

  • department_id — INT — Primary key
  • college_id — INT — NOT NULL
  • department_name — VARCHAR(100) — NOT NULL
  • department_code — VARCHAR(10) — NOT NULL
  • department_type — ENUM('학과', '학부', '전공', '부전공')
  • head_id — INT
  • description — TEXT
  • established_date — DATE
  • total_credits_required — INT
  • major_credits_required — INT
  • is_active — BOOLEAN
  • sort_order — INT
  • created_at — TIMESTAMP

extracurricular_programs

  • program_id — INT
  • program_name — VARCHAR(200) — NOT NULL
  • program_type — ENUM('특강', '워크샵', '세미나', '멘토링', '동아리', '봉사활동', '취업캠프') — NOT NULL
  • category — ENUM('진로', '취업', '창업', '글로벌', '봉사', '문화예술', '체육', '학술') — NOT NULL
  • organizer_dept_id — INT
  • instructor_info — TEXT
  • description — TEXT
  • target_audience — ENUM('전체', '학년별', '학과별', '특정대상')
  • target_details — JSON
  • capacity — INT
  • mileage_points — INT
  • start_date — DATETIME
  • end_date — DATETIME
  • application_start — DATETIME
  • application_end — DATETIME
  • location — VARCHAR(200)
  • requirements — TEXT
  • completion_criteria — TEXT
  • status — ENUM('기획중', '모집중', '진행중', '완료', '취소')
  • created_at — TIMESTAMP

job_categories

  • category_id — INT
  • category_name — VARCHAR(100) — NOT NULL
  • parent_id — INT
  • category_level — INT — NOT NULL
  • sort_order — INT
  • is_active — BOOLEAN

mileage_categories

  • category_id — INT
  • category_name — VARCHAR(100) — NOT NULL
  • max_points_per_semester — INT
  • description — TEXT
  • is_active — BOOLEAN

professors

  • professor_id — INT — Primary key
  • user_id — INT — NOT NULL
  • employee_number — VARCHAR(20) — NOT NULL
  • department_id — INT — NOT NULL
  • position — ENUM('교수', '부교수', '조교수', '겸임교수', '외래교수', '명예교수') — NOT NULL
  • office_location — VARCHAR(100)
  • office_phone — VARCHAR(20)
  • specialization — TEXT
  • research_interests — TEXT
  • hire_date — DATE
  • tenure_status — ENUM('tenured', 'tenure_track', 'non_tenure')
  • sabbatical_status — BOOLEAN

program_applications

  • application_id — INT
  • program_id — INT — NOT NULL
  • student_id — INT — NOT NULL
  • application_date — TIMESTAMP
  • status — ENUM('신청', '승인', '거부', '대기', '참여완료', '미완료')
  • attendance_rate — DECIMAL(5,2)
  • completion_status — BOOLEAN
  • completion_date — DATE
  • evaluation_score — INT
  • feedback — TEXT

recruitment_events

  • event_id — INT
  • event_name — VARCHAR(200) — NOT NULL
  • event_type — ENUM('설명회', '채용박람회', '기업탐방', '네트워킹', '면접캠프') — NOT NULL
  • organizer_id — INT
  • event_date — DATE — NOT NULL
  • start_time — TIME
  • end_time — TIME
  • location — VARCHAR(200)
  • participating_companies — JSON
  • target_students — JSON
  • capacity — INT
  • registration_required — BOOLEAN
  • registration_start — DATETIME
  • registration_end — DATETIME
  • description — TEXT
  • agenda — TEXT
  • dress_code — VARCHAR(100)
  • preparation_items — TEXT
  • status — ENUM('기획중', '모집중', '진행중', '완료', '취소')
  • created_at — TIMESTAMP

staff

  • staff_id — INT — Primary key
  • user_id — INT — NOT NULL
  • employee_number — VARCHAR(20) — NOT NULL
  • department_id — INT — NOT NULL
  • position — VARCHAR(100) — NOT NULL
  • job_title — VARCHAR(100) — NOT NULL
  • office_location — VARCHAR(100)
  • office_phone — VARCHAR(20)
  • hire_date — DATE
  • employment_type — ENUM('정규직', '계약직', '파견직', '인턴')

student_assessment_results

  • result_id — INT — Primary key
  • student_id — INT — NOT NULL
  • assessment_id — INT — NOT NULL
  • completed_at — TIMESTAMP — NOT NULL
  • total_score — INT
  • category_scores — JSON
  • result_summary — TEXT
  • recommendations — TEXT
  • counselor_notes — TEXT

student_mileage

  • mileage_id — INT
  • student_id — INT — NOT NULL
  • category_id — INT — NOT NULL
  • source_type — ENUM('비교과프로그램', '봉사활동', '동아리활동', '수상', '자격증', '기타') — NOT NULL
  • source_id — INT
  • points — INT — NOT NULL
  • reason — VARCHAR(200) — NOT NULL
  • semester_year — YEAR — NOT NULL
  • semester — ENUM('1', '2') — NOT NULL
  • approved_by — INT
  • approved_at — TIMESTAMP
  • status — ENUM('대기', '승인', '반려')
  • created_at — TIMESTAMP

student_mileage_summary

  • summary_id — INT
  • student_id — INT — NOT NULL
  • total_points — INT
  • current_semester_points — INT
  • last_updated — TIMESTAMP

student_portfolios

  • portfolio_id — INT — Primary key
  • student_id — INT — NOT NULL
  • career_goal — TEXT
  • target_companies — TEXT
  • target_positions — TEXT
  • skills — JSON
  • languages — JSON
  • certificates — JSON
  • awards — JSON
  • volunteer_activities — JSON
  • extracurricular_activities — JSON
  • projects — JSON
  • work_experience — JSON
  • self_introduction — TEXT
  • updated_at — TIMESTAMP

student_semester_records

  • record_id — INT
  • student_id — INT — NOT NULL
  • year — YEAR — NOT NULL
  • semester — ENUM('1', '2', '여름', '겨울') — NOT NULL
  • registration_date — DATE
  • semester_credits — DECIMAL(5,2)
  • semester_gpa — DECIMAL(3,2)
  • status — ENUM('등록', '휴학', '복학') — NOT NULL
  • tuition_paid — BOOLEAN
  • created_at — TIMESTAMP

students

  • student_id — INT — Primary key
  • user_id — INT — NOT NULL
  • student_number — VARCHAR(20) — NOT NULL
  • department_id — INT — NOT NULL
  • admission_year — YEAR — NOT NULL
  • admission_semester — ENUM('1', '2') — NOT NULL
  • student_status — ENUM('재학', '휴학', '졸업', '자퇴', '제적', '수료')
  • current_semester — INT
  • total_credits — DECIMAL(5,2)
  • major_credits — DECIMAL(5,2)
  • gpa — DECIMAL(3,2)
  • advisor_id — INT
  • graduation_date — DATE
  • military_service — ENUM('면제', '필요', '완료')

users

  • user_id — INT — Primary key
  • login_id — VARCHAR(50) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • email — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20)
  • name — VARCHAR(50) — NOT NULL
  • user_type — ENUM('student', 'professor', 'staff', 'counselor', 'admin') — NOT NULL
  • status — ENUM('active', 'inactive', 'suspended', 'retired')
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • last_login — TIMESTAMP