简介面向计算机与数据库相关专业学生的课程设计文档围绕外聘教师管理系统的设计与实现展开适合需要完成数据库课程设计、了解信息管理系统开发流程的读者参考。压缩包内仅1个doc文件约389KB内容为完整的课程设计报告含目录、前言、正文与参考文献等章节。正文以SQL Server 2000作为后台数据库、Power Designer为设计工具依次讲解设计目的及意义、设计环境、总体方案、方法与步骤、创新与关键技术、调试及性能分析、结果分析等模块并给出外聘教师信息查询、更新、删除、插入及存储过程实现思路。报告还涉及权限管理、数据备份恢复、云计算与数据加密等扩展方向可帮助读者梳理需求分析、概要设计、数据库设计到测试部署的完整脉络。已有130人学习下载。1. 一份课程设计里藏着的外聘教师管理系统建模链路外聘教师是教务系统里最难缠的一类数据人不在编、课时按学期走、课酬按级别算、院系还要单独报账。这份外聘教师管理系统课程设计稿的工具链看着确实老——PowerDesigner 建模、SQL Server 2000 落库、Windows XP 上跑查询分析器灌数据——但它走完了一条现在很多团队反而缺的链需求分析 → 概念模型 CDM → 物理模型 PDM → 生成 DDL → 灌数据 → 查询维护。为什么说它值得拆因为现在太多项目直接上 ORM 自动建表跳过建模结果表结构一改就迁不动、字段类型一错就返工。这份稿子里外聘教师信息表用姓名课程号做主键姓名用 char(10)这些做法恰好是能拿来当反面教材的活样本。这篇适合三类人看要交数据库课程设计的学生、手里有老教务库要往新版 SQL Server 迁的 DBA、以及想补一补数据库设计基本功的后端。2. PowerDesigner 里把外聘教师业务画成 CDM 与 PDM2.1 先定实体和基数别一上来就摆字段原稿把教师和外聘教师信息表拆成两张表然后在后者里同时塞进姓名和课程号当联合主键。这是拿一张表硬扛教师—授课的多对多关系第二个学期再插同一门课直接撞主键。我在动手前会把实体先收敛成六张实体主标识符关键属性与谁有联系院系院系号院系名称1:n 教师课程课程号课程名称、授课学时n:m 教师经授课教师教师编号姓名、性别、职称、学历n:m 课程授课教师编号课程号学期代课金级别、地点、时间关联教师与课程工资工资单号基本工资、补助、代课费n:1 教师领导院系号教师编号任职时间关联院系与教师把授课独立成实体是这一步最关键的动作。它原本是教师和课程之间的一条关系线一旦带上代课金级别、授课地点、授课时间这些属性就必须升格为实体否则这些字段只能塞进教师表或课程表两张表都会被撑爆。2.2 CDM 到 PDM 的转换动作PowerDesigner 的操作路径是固定的按顺序走就行File → New Model → Conceptual Data ModelCDM 阶段不绑 DBMS保持与具体数据库无关。画实体和属性勾选P主标识符和MMandatory可空字段不要勾 M。用 Relationship 连线把基数设成1..n/0..n哪个实体是支配方Dominant role要明确。Tools → Check Model先跑一遍把孤立实体、无主标识符的实体清掉。Tools → Generate Physical Data ModelDBMS 选Microsoft SQL Server 2000生成新 PDM。命名约定得在生成前定好PD 会根据模板自动拼约束名事后改名很痛苦对象命名规则示例主键约束PK_表名PK_ExternalTeacher外键约束FK_子表_父表FK_Teaching_Teacher唯一约束UQ_表名_列名UQ_ExternalTeacher_No检查约束CK_表名_列名CK_ExternalTeacher_Gender非聚集索引IX_表名_列摘要IX_Teaching_Course_Term另外勾上Generate new PDM而不是覆盖CDM 改一版就重新生成一次物理模型永远跟着概念模型走避免两边字段对不上。2.3 Check Model 拦不下的问题用元数据查询补Check Model只能检查模型内部的完整性检查不了这张表是不是真的有外键落到库里。老库没文档时我习惯直接查系统视图反过来核对-- 列出所有外键及其引用关系用来和 PDM 逐条对照 SELECT fk.name AS FKName, OBJECT_NAME(fk.parent_object_id) AS ChildTable, COL_NAME(fkc.parent_object_id, fkc.parent_column_id) AS ChildColumn, OBJECT_NAME(fk.referenced_object_id) AS ParentTable, COL_NAME(fkc.referenced_object_id, fkc.referenced_column_id) AS ParentColumn, fk.delete_referential_action_desc AS OnDelete FROM sys.foreign_keys AS fk JOIN sys.foreign_key_columns AS fkc ON fkc.constraint_object_id fk.object_id ORDER BY ChildTable, FKName;delete_referential_action_desc这一列在排查为什么这条教师记录删不掉时特别好用它直接告诉你外键挂的是NO_ACTION还是CASCADE。原稿生成的 DDL 里全是on delete restrict等于给每个教师记录上了锁。2.4 反向工程接手一个没有模型的库如果只拿到一个在跑的教务库Database → Reverse Engineer Database用 ODBC 连上去勾选 Tables、Views、ProceduresPD 会把现有结构还原成 PDM。还原出来的模型通常问题一堆字段类型是历史遗留的char、约束名是系统随机生成的、索引全是主键自带的那一个。别急着改先导出成 DDL 进 Git作为后续所有变更的基线。3. 生成的 DDL 别直接用类型、主键与约束的三处硬伤3.1 逐条读一遍原稿的建表语句原稿生成的这段是核心问题所在create table 外聘教师信息表 ( 姓名 char(10) not null, 代课信_课程号 integer not null, 系部 char(10) not null, 课程号 integer, 工资 integer, constraint PK_外聘教师信息表 primary key clustered (姓名, 代课信_课程号) );char(10)在 SQL Server 里是 10 字节定长一个汉字占 2 字节实际只能装 5 个汉字超过就截断或直接报错——欧阳建国勉强够欧阳建国老师就废了。主键选姓名, 课程号更要命同名教师插不进去同一教师下学期带同一门课也插不进去。工资用integer没有小数位课时费单价 87.5 这种数直接存不进去。原稿写法风险建议改法char(10)存姓名中文只放 5 个字nvarchar(30)姓名 课程号 做联合主键重名、换学期撞键代理键INT IDENTITYinteger存工资无小数位无法算课酬decimal(10,2)bigint存基本工资精度浪费参与计算还得转decimal(10,2)全链路on delete restrict离职教师记录删不掉加Status列做逻辑停用无创建/更新时间出问题查不到谁改的CreatedAt DEFAULT SYSDATETIME()3.2 重写一版能落地的表结构-- 外聘教师主表代理键 业务唯一键分离 CREATE TABLE dbo.ExternalTeacher ( TeacherId INT IDENTITY(1,1) NOT NULL, TeacherNo VARCHAR(20) NOT NULL, -- 业务编号唯一 TeacherName NVARCHAR(30) NOT NULL, Gender CHAR(1) NOT NULL, -- M / F Title NVARCHAR(20) NULL, -- 职称 Degree NVARCHAR(20) NULL, -- 学历 DeptId INT NOT NULL, Status TINYINT NOT NULL CONSTRAINT DF_ET_Status DEFAULT (1), -- 1在职 0停用 CreatedAt DATETIME2(0) NOT NULL CONSTRAINT DF_ET_CreatedAt DEFAULT (SYSDATETIME()), CONSTRAINT PK_ExternalTeacher PRIMARY KEY (TeacherId), CONSTRAINT UQ_ExternalTeacher_No UNIQUE (TeacherNo), CONSTRAINT CK_ExternalTeacher_Gender CHECK (Gender IN (M,F)), CONSTRAINT FK_ExternalTeacher_Dept FOREIGN KEY (DeptId) REFERENCES dbo.Department (DeptId) ); GO -- 授课安排把多对多关系显式实体化带上学期维度 CREATE TABLE dbo.Teaching ( TeachingId INT IDENTITY(1,1) NOT NULL, TeacherId INT NOT NULL, CourseId INT NOT NULL, TermCode VARCHAR(10) NOT NULL, -- 如 2024-2025-1 PayLevel TINYINT NOT NULL, -- 代课金级别 0-19 TeachPlace NVARCHAR(60) NULL, TeachTime NVARCHAR(60) NULL, CONSTRAINT PK_Teaching PRIMARY KEY (TeachingId), CONSTRAINT UQ_Teaching UNIQUE (TeacherId, CourseId, TermCode), CONSTRAINT FK_Teaching_Teacher FOREIGN KEY (TeacherId) REFERENCES dbo.ExternalTeacher (TeacherId), CONSTRAINT FK_Teaching_Course FOREIGN KEY (CourseId) REFERENCES dbo.Course (CourseId) ); GO关键改动就三处TeacherId做代理主键承担外键引用、TeacherNo加唯一约束承担业务语义、UQ_Teaching把同一学期同一教师同一课程这个业务规则交给数据库而不是靠代码判断。Status列是给离职场景留的口子教师停用只改这一位历史授课和工资记录全留着报表口径不受影响。工资表可以用计算列把汇总干掉CREATE TABLE dbo.Salary ( SalaryId INT IDENTITY(1,1) NOT NULL, TeacherId INT NOT NULL, TermCode VARCHAR(10) NOT NULL, BasePay DECIMAL(10,2) NOT NULL CONSTRAINT DF_Sal_Base DEFAULT (0), Subsidy DECIMAL(10,2) NOT NULL CONSTRAINT DF_Sal_Sub DEFAULT (0), CourseFee DECIMAL(10,2) NOT NULL CONSTRAINT DF_Sal_Fee DEFAULT (0), TotalPay AS (BasePay Subsidy CourseFee) PERSISTED, SettledAt DATETIME2(0) NULL, CONSTRAINT PK_Salary PRIMARY KEY (SalaryId), CONSTRAINT UQ_Salary UNIQUE (TeacherId, TermCode), CONSTRAINT FK_Salary_Teacher FOREIGN KEY (TeacherId) REFERENCES dbo.ExternalTeacher (TeacherId) ); GOPERSISTED让计算列实际存盘并可以被索引比每次SELECT时现算稳。原稿那张工资表拿工资汇总做主键汇总值一变主键就变这是设计上的死结。3.3 二十条 INSERT 合成一条再大就上 bcp原稿用二十条独立的insert into 代课信息表灌测试数据改成多行 VALUES 更省往返INSERT INTO dbo.Course (CourseCode, CourseName, ClassHours) VALUES (C-01, N数据库原理, 64), (C-02, N数据结构, 72), (C-03, N计算机网络, 56), (C-04, N操作系统, 64), (C-05, N软件工程, 48); GO数据量上了几千行就别在查询分析器里贴了走命令行批量导入# -c 字符模式-t 列分隔符-S 实例-d 目标库-b 每批 10000 行 bcp dbo.Course in /tmp/course.csv -c -t, -b 10000 \ -S 127.0.0.1 -U sa -P ****** -d TeacherMgmt-b不设的话默认整表一个事务日志会爆。导入前把目标表唯一约束和触发器临时禁用ALTER TABLE ... NOCHECK CONSTRAINT导完再WITH CHECK CHECK CONSTRAINT重新启用速度能差好几倍。3.4 老库字段往新表的映射老表.列老类型新表.列新类型转换说明外聘教师信息表.姓名char(10)ExternalTeacher.TeacherNamenvarchar(30)RTRIM去尾部补空格外聘教师信息表.工资integerSalary.BasePaydecimal(10,2)CAST(... AS decimal(10,2))代课信息表.代课金级别integerTeaching.PayLeveltinyint值域 0–19tinyint 够教师.编号char(10)ExternalTeacher.TeacherNovarchar(20)去空格后查重迁移脚本要写成幂等的重跑不会插重复INSERT INTO dbo.ExternalTeacher (TeacherNo, TeacherName, Gender, Title, Degree, DeptId) SELECT LTRIM(RTRIM(t.编号)), LTRIM(RTRIM(t.姓名)), M, NULLIF(LTRIM(RTRIM(t.职称)), ), NULLIF(LTRIM(RTRIM(t.学历)), ), DefaultDeptId FROM 旧库.dbo.教师 AS t WHERE LTRIM(RTRIM(t.编号)) AND NOT EXISTS ( SELECT 1 FROM dbo.ExternalTeacher AS n WHERE n.TeacherNo LTRIM(RTRIM(t.编号)));NULLIF把空字符串转成 NULL避免新表里出现一堆和NULL混着的情况——char字段迁移过来最常见的坑就是这个。4. 存储过程封装外聘教师信息的增删改查4.1 这类教务业务为什么适合走存储过程外聘教师信息的特点是访问频率低、字段变更少、查询口径经常被教务处改来改去、权限必须卡死在数据库层。用存储过程正好对上执行计划一次编译反复复用教务处账号只授EXECUTE不授表权限SQL 注入拿不到表名也白搭口径变了改一个 proc 就行不用等前端发版。代价是版本管理麻烦所以脚本必须进 Git且每个 proc 头部写清楚变更日期和作者。4.2 多条件查询 分页CREATE OR ALTER PROCEDURE dbo.usp_ExternalTeacher_Search DeptId INT NULL, CourseId INT NULL, NameLike NVARCHAR(30) NULL, PageIndex INT 1, PageSize INT 20 AS BEGIN SET NOCOUNT ON; -- 参数兜底防止前端传 0 或超大页长把库拖垮 IF PageIndex 1 SET PageIndex 1; IF PageSize 1 OR PageSize 200 SET PageSize 20; SELECT t.TeacherId, t.TeacherNo, t.TeacherName, t.Gender, t.Title, t.Degree, d.DeptName, c.CourseName, g.PayLevel, g.TeachPlace, g.TeachTime, g.TermCode FROM dbo.ExternalTeacher AS t JOIN dbo.Department AS d ON d.DeptId t.DeptId LEFT JOIN dbo.Teaching AS g ON g.TeacherId t.TeacherId LEFT JOIN dbo.Course AS c ON c.CourseId g.CourseId WHERE t.Status 1 AND (DeptId IS NULL OR t.DeptId DeptId) AND (CourseId IS NULL OR g.CourseId CourseId) AND (NameLike IS NULL OR t.TeacherName LIKE % NameLike %) ORDER BY t.TeacherId OFFSET (PageIndex - 1) * PageSize ROWS FETCH NEXT PageSize ROWS ONLY; END; GO三个条件全部用IS NULL OR的写法一个 proc 覆盖按院系查、按课程查、按姓名模糊查、全量查四种调用方式前端不用维护四条 SQL。LEFT JOIN是刻意的——刚录入还没排课的教师也要能被查出来用INNER JOIN这些人会凭空消失。OFFSET/FETCH是 SQL Server 2012 之后才有的语法原稿的 2000 环境得用ROW_NUMBER()套子查询。4.3 新增时把校验和事务绑在一起CREATE OR ALTER PROCEDURE dbo.usp_ExternalTeacher_Insert TeacherNo VARCHAR(20), TeacherName NVARCHAR(30), Gender CHAR(1), Title NVARCHAR(20) NULL, Degree NVARCHAR(20) NULL, DeptId INT, NewId INT OUTPUT AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; -- 出错自动回滚整个事务 IF EXISTS (SELECT 1 FROM dbo.ExternalTeacher WHERE TeacherNo TeacherNo) THROW 50001, N教师编号已存在, 1; BEGIN TRY BEGIN TRAN; INSERT INTO dbo.ExternalTeacher (TeacherNo, TeacherName, Gender, Title, Degree, DeptId) VALUES (TeacherNo, TeacherName, Gender, Title, Degree, DeptId); SET NewId SCOPE_IDENTITY(); -- 只取当前作用域的自增值 COMMIT TRAN; END TRY BEGIN CATCH IF XACT_STATE() 0 ROLLBACK TRAN; THROW; -- 原样抛出保留错误号和消息 END CATCH END; GOSCOPE_IDENTITY()不能换成IDENTITY后者会被触发器里的插入带偏拿到别的表的自增值这是踩过就忘不了的坑。XACT_ABORT ON加上TRY...CATCH里的XACT_STATE()判断是为了处理事务已经被数据库判死的情况——这时候再发ROLLBACK会直接报错。要注意TRY...CATCH和THROW都是 SQL Server 2005 之后才有的2000 上只能靠ERROR配合GOTO收尾。4.4 调薪并发不能先查后改同一学期两个教务处老师同时点调整课酬如果先SELECT出旧值再UPDATE成新值后提交的那次会覆盖前一次。正确姿势是在UPDATE上加锁CREATE OR ALTER PROCEDURE dbo.usp_Salary_Adjust TeacherId INT, TermCode VARCHAR(10), DeltaFee DECIMAL(10,2) AS BEGIN SET NOCOUNT ON; SET XACT_ABORT ON; BEGIN TRAN; UPDATE dbo.Salary WITH (UPDLOCK, ROWLOCK) SET CourseFee CourseFee DeltaFee WHERE TeacherId TeacherId AND TermCode TermCode; IF ROWCOUNT 0 BEGIN ROLLBACK TRAN; THROW 50002, N当期结算记录不存在无法调薪, 1; END COMMIT TRAN; END; GOUPDLOCK在读取时就拿更新锁拦住并发写ROWLOCK把锁粒度压到行级教务高峰时不会把整张工资表锁死。ROWCOUNT 0判断必须有——没有结算记录时静默成功是最难查的 bug。4.5 把权限收敛到存储过程这一层CREATE ROLE role_teacher_reader; GRANT EXECUTE ON dbo.usp_ExternalTeacher_Search TO role_teacher_reader; DENY SELECT, INSERT, UPDATE, DELETE ON dbo.ExternalTeacher TO role_teacher_reader; GODENY在 SQL Server 权限模型里优先于GRANT即使这个角色后来被加进了某个有表权限的组DENY依然生效。教务处的只读账号只走 proc前端 SQL 注入就算成功了也摸不到表。5. 索引与执行计划让外聘教师查询扛住开学季5.1 三个索引覆盖九成查询外聘教师的查询模式很固定按院系筛、按课程筛、按教师编号精确定位。按这三条建索引就够-- 按院系 在职状态筛覆盖列表页输出列 CREATE NONCLUSTERED INDEX IX_ExternalTeacher_Dept_Status ON dbo.ExternalTeacher (DeptId, Status) INCLUDE (TeacherNo, TeacherName, Title); GO -- 按课程 学期反查授课教师 CREATE NONCLUSTERED INDEX IX_Teaching_Course_Term ON dbo.Teaching (CourseId, TermCode) INCLUDE (TeacherId, PayLevel); GO -- 教师编号唯一走 UQ_ExternalTeacher_No 自带的唯一索引即可前两个索引都把 SELECT 输出列放进了INCLUDE这样查询只扫索引页不回基表也就是常说的覆盖索引。开学季一上午几百次列表查询日志文件读量能压下来一个量级。5.2 用 STATISTICS IO 判断有没有白读改完索引别急着上线先在测试库上量一遍SET STATISTICS IO, TIME ON; EXEC dbo.usp_ExternalTeacher_Search DeptId 3, PageIndex 1, PageSize 20; SET STATISTICS IO, TIME OFF;看输出里的logical reads一个 20 行的分页查询如果逻辑读上千说明索引没吃到。想找该建什么索引直接问系统SELECT TOP 10 mid.statement AS TableName, migs.user_seeks, migs.avg_user_impact, mid.equality_columns, mid.included_columns FROM sys.dm_db_missing_index_group_stats AS migs JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle mig.index_group_handle JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle mid.index_handle ORDER BY migs.avg_user_impact * migs.user_seeks DESC;avg_user_impact是预估提升百分比优先看它高且user_seeks也高的条目。这个视图给的是建议不是命令重复列、宽包含列的建议要自己筛一遍再执行。执行计划里的现象常见原因处理方式Clustered Index Scan谓词列没索引按 DeptIdStatus 建复合索引Key Lookup 反复回表索引未覆盖输出列用 INCLUDE 补上预估 1 行、实际 5 万行统计信息过期UPDATE STATISTICS 表名计划时好时坏参数嗅探OPTION (RECOMPILE)或OPTIMIZE FOR更新锁等待超时调薪 proc 未加 ROWLOCK收紧锁粒度并缩短事务把SET STATISTICS IO, TIME OFF加在测试脚本末尾跑一遍开学季最重的那个分页查询逻辑读稳在几百页以内、执行时间在几十毫秒量级这套外聘教师库基本就能交出去了。本文还有配套的精品资源点击获取