学生信息管理系统数据库设计与实现

1. 数据库表设计

| 标题 | 内容 | |---|---| | 学生编号 | 001 | | 姓名 | 张三 | | 年龄 | 18 | | 班级编号 | 001 | | 002 | 李四 | 19 | 002 | | 003 | 王五 | 20 | 003 | | 004 | 赵六 | 18 | 004 | | 005 | 钱七 | 19 | 005 |

CREATE TABLE Student(
    学生编号 char(10) PRIMARY KEY,
    姓名 nvarchar(20) NOT NULL,
    年龄 int NULL,
    班级编号 char(10) NOT NULL
);

CREATE TABLE Class(
    班级编号 char(10) PRIMARY KEY,
    班级名称 nvarchar(20) NOT NULL,
    专业编号 char(10) NOT NULL
);

CREATE TABLE Major(
    专业编号 char(10) PRIMARY KEY,
    专业名称 nvarchar(20) NOT NULL
);

CREATE TABLE Course(
    课程编号 char(10) PRIMARY KEY,
    课程名称 nvarchar(20) NOT NULL,
    学分 int NULL,
    专业编号 char(10) NOT NULL
);

CREATE TABLE Score(
    学生编号 char(10) NOT NULL,
    课程编号 char(10) NOT NULL,
    成绩 int NULL,
    PRIMARY KEY(学生编号, 课程编号)
);

2. 创建与应用触发器

触发器是一种特殊的存储过程，它会在特定的数据库操作（如插入、更新、删除）发生时自动触发执行。以下是三个触发器的创建和功能描述：

在Score表上创建一个触发器，当学生成绩更新时，自动计算该学生的平均成绩并更新到Student表中。

CREATE TRIGGER tr_UpdateAvgScore
ON Score
AFTER UPDATE
AS
BEGIN
    UPDATE s
    SET 平均成绩 = AVG(sc.成绩)
    FROM Student s
    INNER JOIN Score sc ON s.学生编号 = sc.学生编号
    INNER JOIN inserted i ON sc.学生编号 = i.学生编号
    WHERE s.学生编号 = i.学生编号
    GROUP BY s.学生编号
END

在Course表上创建一个触发器，当课程学分修改时，自动更新该专业的总学分。

CREATE TRIGGER tr_UpdateTotalCredit
ON Course
AFTER UPDATE
AS
BEGIN
    UPDATE Major
    SET 总学分 = (SELECT SUM(学分) FROM Course WHERE 专业编号 = Major.专业编号)
    FROM Major
    INNER JOIN inserted i ON Major.专业编号 = i.专业编号
END

在Student表上创建一个触发器，当学生删除时，自动删除该学生的成绩记录。

CREATE TRIGGER tr_DeleteScore
ON Student
AFTER DELETE
AS
BEGIN
    DELETE FROM Score WHERE 学生编号 IN (SELECT 学生编号 FROM deleted)
END

3. 数据库安全

创建SQL Server身份验证的登录名“SQLUser01”，密码为“abc@123”，默认数据库为你自己创建的这个数据库。

CREATE LOGIN SQLUser01 WITH PASSWORD = 'abc@123', DEFAULT_DATABASE = [数据库名]

在本数据库中创建1个SQL Server身份验证的数据库用户账户“db_user01”，并将其映射到登录名“SQLUser01”。

CREATE USER db_user01 FOR LOGIN SQLUser01

创建自定义角色“role01”，并且将数据库用户“db_user01”添加为该自定义角色的成员，然后为该角色授予相应的权限。

CREATE ROLE role01
EXEC sp_addrolemember 'role01', 'db_user01'

GRANT SELECT, INSERT, UPDATE, DELETE ON Student TO role01
GRANT SELECT, INSERT, UPDATE, DELETE ON Course TO role01

从角色“role01”收回部分权限。

REVOKE DELETE ON Student FROM role01

4. 测试结果截图

（请根据实际测试结果添加截图）

总结

本文详细介绍了学生信息管理系统数据库的设计与实现，包括数据库表的设计、触发器的创建和应用以及数据库的安全配置。通过学习本文，您可以了解如何使用SQL语言创建和应用触发器，以及如何配置数据库安全，以保护您的数据安全。