/* 1) Users: 소셜 로그인(카카오) 기반 계정 관리 */
CREATE TABLE Users (
user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '사용자 고유 ID (PK)',
provider VARCHAR(50) NOT NULL COMMENT 'OAuth 제공자 (예: kakao)',
provider_user_id VARCHAR(100) NOT NULL UNIQUE COMMENT '제공자별 유저 고유 ID (카카오)',
name VARCHAR(100) COMMENT '사용자 이름',
email VARCHAR(255) COMMENT '카카오 제공 이메일(선택 동의)',
phone VARCHAR(20) COMMENT '연락처',
date_joined TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '가입 일시'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='소셜 로그인 사용자 계정';
/* 2) Students: 학생 도메인 프로필 */
CREATE TABLE Students (
student_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '학생 고유 ID (PK)',
user_id INT NOT NULL COMMENT 'Users.user_id(FK)',
major VARCHAR(100) COMMENT '전공',
year INT COMMENT '학년',
interests TEXT COMMENT '관심 분야/키워드',
CONSTRAINT fk_students_user
FOREIGN KEY (user_id) REFERENCES Users(user_id) ON DELETE CASCADE,
KEY idx_students_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='학생 프로필';
/* 3) AISessions: AI 상담 세션 */
CREATE TABLE AISessions (
session_id INT AUTO_INCREMENT PRIMARY KEY COMMENT 'AI 상담 세션 ID (PK)',
student_id INT NOT NULL COMMENT 'Students.student_id(FK)',
started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '세션 시작',
ended_at TIMESTAMP NULL DEFAULT NULL COMMENT '세션 종료',
status VARCHAR(50) DEFAULT 'completed' COMMENT '상태',
logs TEXT COMMENT '대화 기록',
CONSTRAINT fk_ai_sessions_student
FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
KEY idx_ai_sessions_student (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='AI 상담 세션 기록';
/* 4) GraduationSimulations: 졸업 시뮬레이션 실행 */
CREATE TABLE GraduationSimulations (
simulation_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '시뮬레이션 ID (PK)',
student_id INT NOT NULL COMMENT 'Students.student_id(FK)',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성 시각',
status VARCHAR(20) DEFAULT 'pending' COMMENT '상태',
CONSTRAINT fk_gs_student
FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
KEY idx_gs_student (student_id),
KEY idx_gs_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='졸업 시뮬레이션 실행 기록';
/* 5) Feedback: 세션 피드백 */
CREATE TABLE Feedback (
feedback_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '피드백 ID (PK)',
session_id INT NOT NULL COMMENT 'AISessions.session_id(FK)',
student_id INT NOT NULL COMMENT 'Students.student_id(FK)',
rating INT COMMENT '평점(1~5)',
comments TEXT COMMENT '의견',
feedback_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '일시',
CONSTRAINT fk_feedback_session
FOREIGN KEY (session_id) REFERENCES AISessions(session_id) ON DELETE CASCADE,
CONSTRAINT fk_feedback_student
FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
KEY idx_feedback_session (session_id),
KEY idx_feedback_student (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='세션 피드백';
/* 6) Assessments: 학생 평가/검사 */
CREATE TABLE Assessments (
assessment_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '평가 ID (PK)',
student_id INT NOT NULL COMMENT 'Students.student_id(FK)',
type VARCHAR(100) COMMENT '평가 유형',
result TEXT COMMENT '결과 요약',
date_conducted DATE COMMENT '실시 일자',
CONSTRAINT fk_assessments_student
FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
KEY idx_assessments_student (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='학생 평가 기록';
/* 7) CareerPaths: 커리어 마스터 */
CREATE TABLE CareerPaths (
path_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '직업 경로 ID (PK)',
name VARCHAR(200) NOT NULL COMMENT '직업명',
description TEXT COMMENT '설명',
field VARCHAR(100) COMMENT '분야',
education_requirement VARCHAR(255) COMMENT '요구 학력/자격'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='커리어 경로';
/* 8) Courses: 학교 편람 과목 */
CREATE TABLE Courses (
course_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '과목 ID (PK)',
name VARCHAR(200) NOT NULL COMMENT '과목명',
description TEXT COMMENT '설명',
credits INT COMMENT '학점',
field_of_study VARCHAR(100) COMMENT '분야'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='교과목 마스터';
/* 9) StudentCourses: 수강 이력 (M:N) */
CREATE TABLE StudentCourses (
student_id INT NOT NULL COMMENT 'Students.student_id(FK)',
course_id INT NOT NULL COMMENT 'Courses.course_id(FK)',
grade VARCHAR(5) COMMENT '성적',
date_enrolled DATE COMMENT '수강 시작일',
PRIMARY KEY (student_id, course_id),
CONSTRAINT fk_stucourses_student
FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
CONSTRAINT fk_stucourses_course
FOREIGN KEY (course_id) REFERENCES Courses(course_id) ON DELETE CASCADE,
KEY idx_stucourses_course (course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='학생 수강 이력';
/* 10) DegreeRequirements: 졸업 요건 */
CREATE TABLE DegreeRequirements (
requirement_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '요건 ID (PK)',
major VARCHAR(100) NOT NULL COMMENT '전공명',
category VARCHAR(50) COMMENT '분류(core/elective)',
course_id INT NULL COMMENT '필수 과목(Courses FK)',
required_credits INT COMMENT '필요 학점',
CONSTRAINT fk_dreq_course
FOREIGN KEY (course_id) REFERENCES Courses(course_id) ON DELETE SET NULL,
KEY idx_dreq_major (major),
KEY idx_dreq_course (course_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='전공별 졸업 요건';
/* 11) SimulationOutcomes: 시뮬레이션 결과(요건별) */
CREATE TABLE SimulationOutcomes (
simulation_id INT NOT NULL COMMENT 'GraduationSimulations.simulation_id(FK)',
requirement_id INT NOT NULL COMMENT 'DegreeRequirements.requirement_id(FK)',
student_credits INT COMMENT '이수 학점',
needed_credits INT COMMENT '추가 필요 학점',
fulfilled TINYINT(1) COMMENT '충족 여부(BOOLEAN)',
PRIMARY KEY (simulation_id, requirement_id),
CONSTRAINT fk_sout_simulation
FOREIGN KEY (simulation_id) REFERENCES GraduationSimulations(simulation_id) ON DELETE CASCADE,
CONSTRAINT fk_sout_requirement
FOREIGN KEY (requirement_id) REFERENCES DegreeRequirements(requirement_id) ON DELETE CASCADE,
KEY idx_sout_requirement (requirement_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='졸업 시뮬레이션 결과';
/* 12) Recommendations: 통합 추천(커리어/과목/시뮬레이션 과목) */
CREATE TABLE Recommendations (
rec_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '추천 ID (PK)',
student_id INT NOT NULL COMMENT 'Students.student_id(FK)',
item_type VARCHAR(20) NOT NULL COMMENT 'career|course|simulation_course',
item_id INT NOT NULL COMMENT 'CareerPaths.path_id 또는 Courses.course_id',
score DECIMAL(5,2) NULL COMMENT '적합도(커리어용)',
source VARCHAR(20) DEFAULT 'ai_session' COMMENT 'ai_session|simulation',
session_id INT NULL COMMENT 'AISessions.session_id(FK)',
simulation_id INT NULL COMMENT 'GraduationSimulations.simulation_id(FK)',
recommended_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '추천 일시',
justification TEXT COMMENT '사유',
CONSTRAINT fk_recs_student
FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
CONSTRAINT fk_recs_session
FOREIGN KEY (session_id) REFERENCES AISessions(session_id) ON DELETE SET NULL,
CONSTRAINT fk_recs_simulation
FOREIGN KEY (simulation_id) REFERENCES GraduationSimulations(simulation_id) ON DELETE SET NULL,
KEY idx_recs_student (student_id),
KEY idx_recs_session (session_id),
KEY idx_recs_simulation (simulation_id),
KEY idx_recs_item (item_type, item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='통합 추천';
/* 13) Goals: 학생 목표 */
CREATE TABLE Goals (
goal_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '목표 ID (PK)',
student_id INT NOT NULL COMMENT 'Students.student_id(FK)',
description TEXT COMMENT '목표 내용',
priority INT DEFAULT 1 COMMENT '우선순위',
target_date DATE COMMENT '예정일',
status VARCHAR(50) DEFAULT 'pending' COMMENT '상태',
CONSTRAINT fk_goals_student
FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
KEY idx_goals_student (student_id),
KEY idx_goals_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='학생 목표 관리';
/* 14) Roadmaps: 로드맵 세트 */
CREATE TABLE Roadmaps (
roadmap_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '로드맵 ID (PK)',
student_id INT NOT NULL COMMENT 'Students.student_id(FK)',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '생성 일시',
CONSTRAINT fk_roadmaps_student
FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
KEY idx_roadmaps_student (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='학생별 로드맵';
/* 15) RoadmapItems: 로드맵 단계 */
CREATE TABLE RoadmapItems (
roadmap_id INT NOT NULL COMMENT 'Roadmaps.roadmap_id(FK)',
step_order INT NOT NULL COMMENT '단계 순서',
item_type VARCHAR(50) NOT NULL COMMENT 'career|course',
item_id INT NOT NULL COMMENT 'CareerPaths.path_id 또는 Courses.course_id',
PRIMARY KEY (roadmap_id, step_order),
CONSTRAINT fk_ritems_roadmap
FOREIGN KEY (roadmap_id) REFERENCES Roadmaps(roadmap_id) ON DELETE CASCADE,
KEY idx_ritems_item (item_type, item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='로드맵 단계';
/* 16) Resources: 학습 자료 */
CREATE TABLE Resources (
resource_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '자료 ID (PK)',
title VARCHAR(200) NOT NULL COMMENT '자료 제목',
description TEXT COMMENT '설명',
link VARCHAR(500) COMMENT 'URL',
type VARCHAR(50) COMMENT '유형'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='학습/참고 자료';
/* 17) StudentResources: 학생-자료 매핑 */
CREATE TABLE StudentResources (
student_id INT NOT NULL COMMENT 'Students.student_id(FK)',
resource_id INT NOT NULL COMMENT 'Resources.resource_id(FK)',
PRIMARY KEY (student_id, resource_id),
CONSTRAINT fk_stures_student
FOREIGN KEY (student_id) REFERENCES Students(student_id) ON DELETE CASCADE,
CONSTRAINT fk_stures_resource
FOREIGN KEY (resource_id) REFERENCES Resources(resource_id) ON DELETE CASCADE,
KEY idx_stures_resource (resource_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='학생-자료 매핑';