sql server的课计问题

Question

设计一个教务系统
基本要求：
至少五个表，
八个约束
三个视图，
十个函数，
二十个存储过程

Answer 1

上午好☀️☀️☀️️
本答案参考ChatGPT-3.5

设计一个教务系统需要考虑多个因素，包括数据库结构、查询优化、安全性等。以下是针对您的问题提出的SQL Server设计建议：

1. 数据库结构:

   • 教师表（Teacher）

     • teacher_id (主键)
     • name (姓名)
     • department (部门)
     • email (邮箱)

   • 学生表（Student）

     • student_id (主键)
     • name (姓名)
     • department (部门)
     • age (年龄)
     • major (专业)

   • 课程表（Course）

     • course_id (主键)
     • course_name (课程名称)
     • professor_id (教授ID)
     • student_id (学生ID)

   • 作业表（Assignment）

     • assignment_id (主键)
     • assignment_name (任务名称)
     • course_id (课程ID)
     • student_id (学生ID)

2. 视图：

   • 教师成绩表（TeacherScore）

     • teacher_id (主键)
     • student_id (外键)
     • score (成绩)

   • 学生成绩表（StudentScore）

     • student_id (主键)
     • teacher_id (外键)
     • score (成绩)

3. 函数：

   • 获取所有教师的全名
   • 计算每个学生的平均年龄
   • 根据学生选择的课程查看该课程的所有作业
   • 更新教师的工资

4. 存储过程：

   • 提交课程表到教师表的更新请求
   • 计算每个学生的最高分数
   • 删除学生评分表中某个课程的成绩

下面是一些可能需要的SQL代码示例：

CREATE TABLE Teacher (
    teacher_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    email VARCHAR(50)
);

CREATE TABLE Student (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    age INT,
    major VARCHAR(50)
);

CREATE TABLE Course (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL,
    professor_id INT,
    student_id INT
);

CREATE TABLE Assignment (
    assignment_id INT PRIMARY KEY,
    assignment_name VARCHAR(50) NOT NULL,
    course_id INT,
    student_id INT
);

CREATE VIEW TeacherScore AS SELECT T.name AS teacher_name, AVG(S.score) AS average_score FROM Teacher AS T INNER JOIN Student AS S ON T.teacher_id = S.student_id GROUP BY T.id;

CREATE VIEW StudentScore AS SELECT S.name AS student_name, AVG(T.score) AS average_score FROM Student AS S INNER JOIN Teacher AS T ON S.teacher_id = T.student_id GROUP BY S.id;

CREATE FUNCTION GetAllTeachers() RETURNS TABLE AS $$
SELECT T.name AS teacher_name;
$$ LANGUAGE SQL;

CREATE FUNCTION GetAverageAgeOfStudents(t_students INT) RETURNS TABLE AS $$
SELECT AVG(S.age) AS average_age FROM Student AS s INNER JOIN StudentScore AS T ON s.student_id = T.student_id WHERE T.course_id IS NOT NULL;
$$ LANGUAGE SQL;

CREATE PROCEDURE UpdateTeacherSalary(t_teacher INT, s_salary DECIMAL(10,2)) 
BEGIN
    UPDATE Teacher 
    SET salary=s_salary 
    WHERE teacher_id=t_teacher;
END;

CREATE PROCEDURE AddStudentScore(c_course INT, t_student INT, score DECIMAL(10,2)) 
BEGIN
    INSERT INTO Assignment 
    VALUES (c_course, t_student, score);
END;

CREATE PROCEDURE RemoveStudentScore(c_course INT, t_student INT) 
BEGIN
    DELETE FROM Assignment 
    WHERE c_course=c_course AND t_student=t_student;
END;

这只是一个基本的设计框架，实际应用中还需要考虑更多的细节和安全问题。您可能需要根据具体需求进行调整。