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