z-notes

SQL SERVER-设计学生信息管理系统的数据库表

一、场景描述

某大学需要开发一个学生信息管理系统，需设计数据库表结构，支持以下功能：

1. 存储学生基本信息（学号、姓名、性别、出生日期、班级、所属院系）。
2. 记录学生选修的课程及成绩（课程需区分必修课和选修课）。
3. 每位学生有唯一学号，每个课程有唯一课程编号。
4. 支持按院系统计学生人数和平均成绩。
5. 教师信息需记录姓名、工号、所属院系及职称。
6. 一门课程可由多名教师授课，一名教师可教多门课程。

二、进阶需求

1. 学生成绩需在 0~100 分之间，且必修课成绩不能为空。
2. 如果学生所属院系被撤销，需保留历史数据但标记为“已停用”。
3. 高频查询场景：按班级查询学生列表、按课程统计平均分。

三、题目要求

1. 设计满足上述需求的表结构（需包含字段、主键、外键、约束等）。
2. 写出建表 SQL 语句（使用 SQL Server 语法）。
3. 给出至少一个索引优化方案。
4. （可选）设计一个触发器或存储过程，实现插入成绩时自动检查有效性（如分数范围）。

四、解题

USE Market

-- 学生表(Student)
create table student1(
    SID VARCHAR(10) NOT NULL PRIMARY KEY,
    Sname NVARCHAR(20) NOT NULL,
    Ssex CHAR(2) CHECK(Ssex in ('男','女')),
    Sage DATE NOT NULL,
    ClassID VARCHAR(10) NOT NULL,
    DeptID VARCHAR(10) NOT NULL,
    IsActive BIT DEFAULT 1
)

-- 课程表(Course)
create table course1(
    CID VARCHAR(10) PRIMARY KEY,
    Cname NVARCHAR(50) NOT NULL,
    Ctype CHAR(4) CHECK(Ctype in ('必修','选修')),
    Credit INT CHECK (Credit > 0)
)

--- 教师表(Teacher)
create table Teacher1(
    TID VARCHAR(10) PRIMARY KEY NOT NULL,
    Tname NVARCHAR(20) NOT NULL,
    DeptID VARCHAR(10) NOT NULL,
    Title NVARCHAR(10)
)

-- 成绩表（SC）  
create table SC1(
    SID VARCHAR(10) REFERENCES Student1(SID),
    CID VARCHAR(10) REFERENCES Course1(CID),
    TID VARCHAR(10) REFERENCES Teacher1(TID),
    Score DECIMAL(5,1) CHECK(Score BETWEEN 0 AND 100),
    Primary key(SID,CID),
    CONSTRAINT FK_SC_TID FOREIGN KEY(TID) REFERENCES Teacher1(TID),
)

-- 院系表（Department）
create table Department(
    DeptID VARCHAR(10) PRIMARY KEY,
    DeptName NVARCHAR(50) NOT NULL,
    IsActive Bit DEFAUlT 1
)

-- 高频按班级查询学生列表
CREATE INDEX IX_STUDENT_CLASS ON Student1(ClassID);

-- 高频按课程统计平均分
CREATE INDEX IX_SC_Course ON SC1(CID) INCLUDE(Score);

-- 当院系标记为停用时，更新学生表的 IsActive 字段
UPDATE student1 SET IsActive=0 WHERE DeptId IN (SELECT DeptId FROM Department WHERE IsActive = 0);

--触发器示例（检查必修课成绩非空）

CREATE TRIGGER TR_SC_CheckRequiredCourse
ON SC1
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN Course1 c ON i.CId = c.CId
        WHERE c.Ctype = '必修' AND i.Score IS NULL
    )
    BEGIN
        RAISERROR('必修课成绩不能为空！', 16, 1);
        ROLLBACK TRANSACTION;
    END
END;

五、思维导图