编辑代码

-- 1. 院系表
CREATE TABLE departments (
  department_id INT PRIMARY KEY AUTO_INCREMENT,
  department_name VARCHAR(50) NOT NULL
);

-- 2. 学生表
CREATE TABLE students (
  student_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  department_id INT NOT NULL,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 3. 教师表
CREATE TABLE teachers (
  teacher_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  department_id INT NOT NULL,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- 4. 课程表
CREATE TABLE courses (
  course_id INT PRIMARY KEY AUTO_INCREMENT,
  course_name VARCHAR(100) NOT NULL,
  credits INT NOT NULL CHECK (credits > 0)
);

-- 5. 开课实例表
CREATE TABLE offerings (
  offering_id INT PRIMARY KEY AUTO_INCREMENT,
  course_id INT NOT NULL,
  teacher_id INT NOT NULL, -- 责任教师
  semester VARCHAR(20) NOT NULL, -- 如 "2024-秋季"
  capacity INT NOT NULL CHECK (capacity > 0),
  FOREIGN KEY (course_id) REFERENCES courses(course_id),
  FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id)
);

-- 6. 选课记录表
CREATE TABLE enrollments (
  enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
  student_id INT NOT NULL,
  offering_id INT NOT NULL,
  enroll_date DATE NOT NULL,
  UNIQUE (student_id, offering_id), -- 防止重复选课
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (offering_id) REFERENCES offerings(offering_id)
);

-- 7. 教师授课表（支持多教师）
CREATE TABLE teaching_assignments (
  assignment_id INT PRIMARY KEY AUTO_INCREMENT,
  teacher_id INT NOT NULL,
  offering_id INT NOT NULL,
  FOREIGN KEY (teacher_id) REFERENCES teachers(teacher_id),
  FOREIGN KEY (offering_id) REFERENCES offerings(offering_id)
);