注：有下划线的为主码，划在字上的为外码

（1)管理员（登录用户名，密码，姓名，性别，学校，邮箱，联系电话）；

CREATE TABLE Admin (

    username VARCHAR(50) PRIMARY KEY,

    password VARCHAR(50) NOT NULL,

    name VARCHAR(50) NOT NULL,

    gender CHAR(1),

    school VARCHAR(100),

    email VARCHAR(100),

    phone VARCHAR(15)

);

（2)学生（学号，密码，姓名，学校，性别，邮箱，联系电话，班级，专业号，入学年份）；

CREATE TABLE Student (

    student_id VARCHAR(20) PRIMARY KEY,

    password VARCHAR(50) NOT NULL,

    name VARCHAR(50) NOT NULL,

    school VARCHAR(100),

    gender CHAR(1),

    email VARCHAR(100),

    phone VARCHAR(15),

    class VARCHAR(50),

    major_id VARCHAR(20),

enrollment_year YEAR               

FOREIGN KEY (major_id)REFERENCES Major(major_id);

);

(3)教师（工号，密码，姓名，学校，性别，邮箱，联系电话，专业名，级别）；

CREATE TABLE Teacher (

    teacher_id VARCHAR(20) PRIMARY KEY,

    password VARCHAR(50) NOT NULL,

    name VARCHAR(50) NOT NULL,

    school VARCHAR(100),

    gender CHAR(1),

    email VARCHAR(100),

    phone VARCHAR(15),

    major_name VARCHAR(50),

level VARCHAR(20),

FOREIGN KEY (major_name)REFERENCES Major(major_name)

);

(4)课程（课程号，专业号，学分，课时，学期，类型，课程名，分数）；

CREATE TABLE Course (

    course_id VARCHAR(20) PRIMARY KEY,

    major_id VARCHAR(20),

    credits INT,

    hours INT,

    semester VARCHAR(20),

    type VARCHAR(20),

    course_name VARCHAR(100),

score DECIMAL(5, 2),

FOREIGN KEY (major_id)REFERENCES Major(major_id)

);

(5)学生课表（学号，课程号，专业号，学分，课程类型，课程名，学期，日期）；

CREATE TABLE StudentSchedule (

    student_id VARCHAR(20),

    course_id VARCHAR(20),

    major_id VARCHAR(20),

    credits INT,

    course_type VARCHAR(20),

    course_name VARCHAR(100),

    semester VARCHAR(20),

    date DATE,

PRIMARY KEY (student_id, course_id),

FOREIGN KEY (studentr_id)REFERENCES Student(student_id),

FOREIGN KEY (course_id)REFERENCES Major(course_id),

FOREIGN KEY (major_id)REFERENCES Major(major_id)

);

(6)专业表（专业号，专业名，专业课程，学生人数，要求总学分数）；

CREATE TABLE Major (

    major_id VARCHAR(20) PRIMARY KEY,

    major_name VARCHAR(100),

    major_courses TEXT,

    student_count INT,

    total_required_credits INT

);

（7）成绩表（学号，课程号，课程名，分数）

CREATE TABLE Grade (

    student_id VARCHAR(20),

    course_id VARCHAR(20),

    course_name VARCHAR(100),

    score DECIMAL(5, 2),

PRIMARY KEY (student_id, course_id)FOREIGN KEY (studentr_id),

FOREIGN KEY (course_id)REFERENCES Major(course_id),

);

有助于唯一性检查和完整性检查，而且可以加快连接查询的速度。

（1）学生表索引：

CREATE INDEX idx_student_major_id ON Student(major_id);

CREATE INDEX idx_student_enrollment_year ON Student(enrollment_year);

（2）教师表索引

CREATE INDEX idx_teacher_major_name ON Teacher(major_name);

（3）课程表索引

CREATE INDEX idx_course_major_id ON Course(major_id);

（4）学生课表索引

CREATE INDEX idx_schedule_student_id ON StudentSchedule(student_id);

CREATE INDEX idx_schedule_course_id ON StudentSchedule(course_id);

（5）成绩表索引

CREATE INDEX idx_grade_student_id ON Grade(student_id);

CREATE INDEX idx_grade_course_id ON Grade(course_id);

（1）学生表中加入触发器

CREATE TRIGGER trg_after_student_insert

AFTER INSERT ON Student

FOR EACH ROW

BEGIN

    UPDATE Major SET student_count = student_count + 1 WHERE major_id = NEW.major_id;

END;

（2）学生表中删除触发器

CREATE TRIGGER trg_after_student_delete

AFTER DELETE ON Student

FOR EACH ROW

BEGIN

    UPDATE Major SET student_count = student_count - 1 WHERE major_id = OLD.major_id;

END;

（3）成绩表插入触发器

CREATE TRIGGER trg_after_grade_insert

AFTER INSERT ON Grade

FOR EACH ROW

BEGIN

    UPDATE Course SET score = (SELECT AVG(score) FROM Grade WHERE course_id = NEW.course_id) WHERE course_id = NEW.course_id;

END;