在MySQL中创建Teach数据库的六张表

在MySQL中，要创建一个名为"Teach"的数据库，并为其设计六个关联性强的表，我们可以按照常见的业务场景来设置，例如教学管理、学生信息、课程安排、教师信息、学生课程注册以及成绩记录等。以下是这六张表的基本结构示例：

1. 表一：Teachers (教师信息)

CREATE TABLE Teachers (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    subject VARCHAR(50),
    email VARCHAR(100) UNIQUE
);

2. 表二：Students (学生信息)

CREATE TABLE Students (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    grade INT,
    contact VARCHAR(50)
);

3. Courses (课程信息)

CREATE TABLE Courses (
    id INT PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    teacher_id INT,
    FOREIGN KEY (teacher_id) REFERENCES Teachers(id)
);

4. CourseRegistrations (学生课程注册)

CREATE TABLE CourseRegistrations (
    student_id INT,
    course_id INT,
    registration_date DATE,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (course_id) REFERENCES Courses(id)
);

5. Grades (成绩记录)

CREATE TABLE Grades (
    registration_id INT,
    student_id INT,
    course_id INT,
    score DECIMAL(3,1),
    assessment_date DATE,
    PRIMARY KEY (registration_id),
    FOREIGN KEY (registration_id) REFERENCES CourseRegistrations(registration_id),
    FOREIGN KEY (student_id) REFERENCES Students(id),
    FOREIGN KEY (course_id) REFERENCES Courses(id)
);

6. Classrooms (教室信息 - 可选，用于记录上课地点)

CREATE TABLE Classrooms (
    id INT PRIMARY KEY,
    room_number VARCHAR(20),
    capacity INT
);