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

1. 收集与整理数据

以学生管理系统为例，收集以下数据并输入到excel工作表中：

学生表：

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

班级表：

| 班级编号 | 班级名称 | 专业编号 | | -------- | -------- | -------- | | '001' | 一班 | '001' | | '002' | 二班 | '002' | | '003' | 三班 | '001' |

专业表：

| 专业编号 | 专业名称 | | -------- | -------- | | '001' | 计算机 | | '002' | 电子 |

课程表：

| 课程编号 | 课程名称 | 学分 | 专业编号 | | -------- | -------- | ---- | -------- | | '001' | 数据库 | 3 | '001' | | '002' | 计算机网络 | 4 | '001' | | '003' | 电路原理 | 3 | '002' |

成绩表：

| 学生编号 | 课程编号 | 成绩 | | -------- | -------- | ---- | | '001' | '001' | 85 | | '001' | '002' | 78 | | '002' | '001' | 90 | | '002' | '002' | 89 | | '003' | '001' | 80 | | '003' | '002' | 87 | | '004' | '003' | 92 | | '005' | '003' | 86 |

2. 设计数据库

(1) 数据库的概念结构设计：

根据以上数据特征，确定以下实体和联系：

学生（学生编号，姓名，年龄，班级编号）
班级（班级编号，班级名称，专业编号）
专业（专业编号，专业名称）
课程（课程编号，课程名称，学分，专业编号）
成绩（学生编号，课程编号，成绩）

绘制E-R图如下：

(2) 数据库的逻辑结构设计：

将实体和联系转换为关系模式，并对其进行范式分析和优化：

学生表（学生编号，姓名，年龄，班级编号）
班级表（班级编号，班级名称，专业编号）
专业表（专业编号，专业名称）
课程表（课程编号，课程名称，学分，专业编号）
成绩表（学生编号，课程编号，成绩）

对以上关系模式进行范式分析，均满足第三范式。

(3) 数据库的物理结构设计：

将关系模式转换为关系表，设计各个数据表的结构，确定各个字段的属性，包括字段数据类型、长度、是否为NULL值、主键等属性。

学生表（Student）：

| 字段名 | 数据类型 | 长度 | 是否为NULL值 | 主键 | | ---------- | -------- | ---- | ------------ | ---- | | 学生编号 | char | 10 | 否 | 是 | | 姓名 | nvarchar | 20 | 否 | | | 年龄 | int | | 是 | | | 班级编号 | char | 10 | 否 | |

班级表（Class）：

| 字段名 | 数据类型 | 长度 | 是否为NULL值 | 主键 | | ---------- | -------- | ---- | ------------ | ---- | | 班级编号 | char | 10 | 否 | 是 | | 班级名称 | nvarchar | 20 | 否 | | | 专业编号 | char | 10 | 否 | |

专业表（Major）：

| 字段名 | 数据类型 | 长度 | 是否为NULL值 | 主键 | | ---------- | -------- | ---- | ------------ | ---- | | 专业编号 | char | 10 | 否 | 是 | | 专业名称 | nvarchar | 20 | 否 | |

课程表（Course）：

| 字段名 | 数据类型 | 长度 | 是否为NULL值 | 主键 | | ---------- | -------- | ---- | ------------ | ---- | | 课程编号 | char | 10 | 否 | 是 | | 课程名称 | nvarchar | 20 | 否 | | | 学分 | int | | 是 | | | 专业编号 | char | 10 | 否 | |

成绩表（Score）：

| 字段名 | 数据类型 | 长度 | 是否为NULL值 | 主键 | | ---------- | -------- | ---- | ------------ | ---- | | 学生编号 | char | 10 | 否 | 是 | | 课程编号 | char | 10 | 否 | 是 | | 成绩 | int | | 是 | |

3. 创建数据库

在SQL Server中创建数据库，数据库的命名方式为“DB_本人学号”，数据库文件的参数自行设定（使用SQL代码实现）：

CREATE DATABASE DB_123456;

4. 创建数据表

(1) 根据事先设计的数据表结构，创建5个以上的数据表（使用SQL代码实现）：

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) 从事先准备的excel文件中导入所有的记录数据。如果导入数据过程中出现错误，认真分析错误原因，对数据表进行修改后重新导入，保证所有数据导入成功。

以导入学生表为例：

先将excel文件另存为csv格式，然后使用SQL Server Management Studio中的导入向导进行导入。

步骤1：选择数据源

步骤2：选择目标

选择“Student”表，并设置列映射。

步骤3：完成导入

5. 维护数据的完整性

设置数据库中各个数据表的主键、外键、唯一约束、默认值约束、检查约束和自动编号的标识列等。（使用SQL代码实现）。

-- 设置主键
ALTER TABLE Student ADD CONSTRAINT PK_Student PRIMARY KEY (学生编号);
ALTER TABLE Class ADD CONSTRAINT PK_Class PRIMARY KEY (班级编号);
ALTER TABLE Major ADD CONSTRAINT PK_Major PRIMARY KEY (专业编号);
ALTER TABLE Course ADD CONSTRAINT PK_Course PRIMARY KEY (课程编号);
ALTER TABLE Score ADD CONSTRAINT PK_Score PRIMARY KEY (学生编号, 课程编号);

-- 设置外键
ALTER TABLE Student ADD CONSTRAINT FK_Student_Class FOREIGN KEY (班级编号) REFERENCES Class (班级编号);
ALTER TABLE Class ADD CONSTRAINT FK_Class_Major FOREIGN KEY (专业编号) REFERENCES Major (专业编号);
ALTER TABLE Course ADD CONSTRAINT FK_Course_Major FOREIGN KEY (专业编号) REFERENCES Major (专业编号));
ALTER TABLE Score ADD CONSTRAINT FK_Score_Student FOREIGN KEY (学生编号) REFERENCES Student (学生编号);
ALTER TABLE Score ADD CONSTRAINT FK_Score_Course FOREIGN KEY (课程编号) REFERENCES Course (课程编号);

-- 设置唯一约束
ALTER TABLE Student ADD CONSTRAINT UQ_Student_Name UNIQUE (姓名);

-- 设置默认值约束
ALTER TABLE Student ALTER COLUMN 年龄 SET DEFAULT 20;

-- 设置检查约束
ALTER TABLE Score ADD CONSTRAINT CK_Score_Score CHECK (成绩 >= 0 AND 成绩 <= 100);

-- 设置自动编号的标识列
ALTER TABLE Student ADD ID int IDENTITY(1, 1);

6. 数据库备份

首先对数据库执行一次完整备份，备份设备为“DB_bakDevice” （备份过程请截图展示）。

步骤1：在SQL Server Management Studio中选择“新建查询”。

步骤2：使用备份命令备份数据库。

BACKUP DATABASE DB_123456
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\DB_123456.bak'
WITH FORMAT, NAME = 'DB_123456 Full Backup', INIT;

步骤3：执行备份命令。

7. 检索与操作数据表

用DML语句完成系统运行涉及的相关增删改查操作（10条以上），请将操作的内容用文字描述，操作的过程用SQL代码实现，操作结果截图展示。

(1) 查询所有学生的信息。

SELECT * FROM Student;

(2) 查询所有专业的名称。

SELECT 专业名称 FROM Major;

(3) 查询计算机专业的所有学生的姓名和年龄。

SELECT 姓名, 年龄 FROM Student WHERE 班级编号 IN (SELECT 班级编号 FROM Class WHERE 专业编号 = '001');

(4) 查询所有成绩大于等于90分的学生的姓名和所在班级名称。

SELECT Student.姓名, Class.班级名称 FROM Student
INNER JOIN Class ON Student.班级编号 = Class.班级编号
INNER JOIN Score ON Student.学生编号 = Score.学生编号
WHERE Score.成绩 >= 90;

(5) 查询所有学生的平均年龄。

SELECT AVG(年龄) AS 平均年龄 FROM Student;

(6) 向学生表中插入一条记录。

INSERT INTO Student (学生编号, 姓名, 年龄, 班级编号) VALUES ('006', '周八', 20, '002');

(7) 更新学生表中某个学生的年龄。

UPDATE Student SET 年龄 = 19 WHERE 学生编号 = '002';

(8) 删除学生表中某个学生的信息。

DELETE FROM Student WHERE 学生编号 = '006';

8. 创建与应用存储过程

使用SQL语言创建3个以上存储过程，并在JAVA代码中对其进行调用。请用文字描述其功能，用截图展示其调用结果。

(1) 创建存储过程查询学生信息

CREATE PROCEDURE sp_GetStudentInfo
@studentId char(10)
AS
BEGIN
    SELECT * FROM Student WHERE 学生编号 = @studentId;
END;
GO

该存储过程的功能是根据学生编号查询学生信息。

(2) 创建存储过程添加学生信息

CREATE PROCEDURE sp_AddStudentInfo
@studentId char(10),
@name nvarchar(20),
@age int,
@classId char(10)
AS
BEGIN
    INSERT INTO Student (学生编号, 姓名, 年龄, 班级编号) VALUES (@studentId, @name, @age, @classId);
END;
GO

该存储过程的功能是添加学生信息。

(3) 创建存储过程更新学生信息

CREATE PROCEDURE sp_UpdateStudentInfo
@studentId char(10),
@name nvarchar(20),
@age int,
@classId char(10)
AS
BEGIN
    UPDATE Student SET 姓名 = @name, 年龄 = @age, 班级编号 = @classId WHERE 学生编号 = @studentId;
END;
GO

该存储过程的功能是更新学生信息。

9. 创建与应用触发器

使用SQL语言创建3个以上触发器，并在程序运行过程中触发使其执行，请用文字描述其功能，用截图展示测试结果。

(1) 创建触发器记录学生信息变更

CREATE TRIGGER tr_StudentInfoChange
ON Student
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    INSERT INTO StudentLog (操作类型, 学生编号, 姓名, 年龄, 班级编号, 操作时间) 
    SELECT
        CASE WHEN EXISTS (SELECT 1 FROM inserted) THEN 'INSERT' 
             WHEN EXISTS (SELECT 1 FROM deleted) THEN 'DELETE' 
             ELSE 'UPDATE' 
        END,
        inserted.学生编号,
        inserted.姓名,
        inserted.年龄,
        inserted.班级编号,
        GETDATE()
    FROM inserted
    UNION ALL
    SELECT
        CASE WHEN EXISTS (SELECT 1 FROM inserted) THEN 'INSERT' 
             WHEN EXISTS (SELECT 1 FROM deleted) THEN 'DELETE' 
             ELSE 'UPDATE' 
        END,
        deleted.学生编号,
        deleted.姓名,
        deleted.年龄,
        deleted.班级编号,
        GETDATE()
    FROM deleted;
END;
GO

该触发器记录了学生信息变更的操作类型、学生编号、姓名、年龄、班级编号和操作时间。

(2) 创建触发器限制学生年龄

CREATE TRIGGER tr_StudentAgeLimit
ON Student
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT 1 FROM inserted WHERE 年龄 < 16 OR 年龄 > 25)
    BEGIN
        RAISERROR('学生年龄必须在16到25之间', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END;
    INSERT INTO Student SELECT * FROM inserted;
END;
GO

该触发器限制了学生年龄必须在16到25之间，如果年龄不在此范围内，则会抛出错误并回滚事务。

(3) 创建触发器更新成绩自动计算平均分

CREATE TRIGGER tr_ScoreChange
ON Score
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
    UPDATE Student
    SET 平均分 = (SELECT AVG(成绩) FROM Score WHERE 学生编号 = Student.学生编号)
    WHERE 学生编号 IN (SELECT 学生编号 FROM inserted UNION ALL SELECT 学生编号 FROM deleted);
END;
GO

该触发器在成绩表发生插入、更新或删除操作后，自动更新学生表的平均分。