SQL Server学生选课系统数据库设计：从建表到选课冲突的完整方案

简介这份资源是面向计算机相关专业在校学生的SQL Server学生选课系统数据库设计课程设计包适合作为期末大作业、课设答辩或项目初期立项的参考模板也便于初学者理解数据库建模与SQL编程的完整流程。压缩包共6个文件约139KB包含sql建库建表脚本、docx详细设计文档、md说明文件以及png结构示意图另附一份zip源码覆盖从需求分析到表结构落地的关键环节。目前已有441人学习下载说明其内容具备一定参考价值。读者可从中获取完整的选课系统数据库设计方案包括实体关系梳理、数据表字段定义、约束与索引设置思路以及可直接运行的SQL脚本便于对照文档理解设计意图也能在此基础上修改扩展为其他教务管理功能适合需要快速完成课设或学习数据库设计规范的同学使用。1. 学生选课系统数据库设计从建表到选课冲突一套能交课程设计的完整方案每年学期末总有一批人对着「数据库课程设计」六个字发愁。题目发下来是「学生选课系统」要求写文档、建库、写源码、做答辩但真动手时才发现表该怎么拆、选课冲突怎么防、容量满了怎么处理这些课本上一笔带过的东西恰恰是答辩老师最爱追问的地方。这份基于 SQL Server 的学生选课系统数据库设计要解决的就是从 ER 图到可运行脚本这一整条链路——它不是让你背范式而是让你交出一套能跑、能查、能讲清楚设计取舍的东西。适合正在做课程设计的学生也适合想拿一个完整案例练手 SQL Server 建库、约束、存储过程和事务的开发者。下面按「先立设计、再落脚本、最后排坑」的顺序讲透。2. 先把表拆对学生选课系统的实体识别与关系建模2.1 从业务动作反推实体而不是从课本抄 ER 图很多人一上来就画 ER 图结果画完发现字段对不上业务。我的习惯是先把业务动作列出来学生入学建档、教师开课、学生选课、退课、录入成绩、统计学分。每个动作背后至少有一个实体在承载数据。学生入学建档 → 学生表Student教师开课 → 教师表Teacher 课程表Course 开课表CourseOffering学生选课/退课 → 选课表Enrollment录入成绩 → 成绩字段挂在选课表上而不是单独建表统计学分 → 课程表里存学分选课表里存是否通过这里最容易翻车的是把「课程」和「开课」混成一张表。课程是「数据结构4 学分专业必修」开课是「2024 秋季张老师周三 3-4 节容量 60 人」。两者是一对多必须拆开。不拆的后果是同一门课不同学期开你得复制一堆课程信息改一个学分要改几十行。2.2 五张核心表 两张字典表的结构设计下面是我一般会用的表结构字段名用英文注释写清楚方便后面写文档直接贴。表名中文名关键字段说明Student学生表StudentID, Name, Gender, MajorID, Grade学号做主键Teacher教师表TeacherID, Name, Title, DeptID工号做主键Course课程表CourseID, CourseName, Credit, CourseType课程编号做主键CourseOffering开课表OfferingID, CourseID, TeacherID, Semester, Capacity, Enrolled自增主键外键关联课程和教师Enrollment选课表EnrollmentID, StudentID, OfferingID, SelectTime, Score联合唯一约束防重复选课Department院系表DeptID, DeptName字典表Major专业表MajorID, MajorName, DeptID字典表选课表上的(StudentID, OfferingID)要加唯一约束这是防重复选课的第一道防线。成绩字段允许为空表示还没录入。Enrolled字段是已选人数用来和Capacity比较判断是否满员。2.3 主键、外键、唯一约束该怎么定主键选择上学生表用学号、教师表用工号、课程表用课程编号这些都是业务主键稳定且唯一。开课表和选课表用自增 ID因为它们的业务键可能变化比如开课记录调整自增 ID 更省心。外键方面选课表的StudentID引用学生表OfferingID引用开课表开课表的CourseID引用课程表、TeacherID引用教师表。外键的作用不只是约束还能在写多表联查时帮你理清关系。唯一约束除了选课表的联合唯一还有学生表的学号、教师表的工号、课程表的课程编号这些在建表时直接加 UNIQUE 即可。提示外键要不要加 ON DELETE CASCADE 要慎重。学生退学删学生记录时如果级联删选课记录历史成绩就没了。我一般不加级联改用软删除或状态字段。3. 建库建表脚本一份能直接跑的 SQL Server 源码3.1 建库与建表的完整 T-SQL 脚本下面这段脚本可以直接在 SSMS 里执行建库、建表、加约束一次到位。注意 SQL Server 的IDENTITY(1,1)是自增NVARCHAR存中文更稳妥。-- 建库 IF DB_ID(StudentCourseDB) IS NULL CREATE DATABASE StudentCourseDB; GO USE StudentCourseDB; GO -- 院系表 CREATE TABLE Department ( DeptID INT PRIMARY KEY, DeptName NVARCHAR(50) NOT NULL ); -- 专业表 CREATE TABLE Major ( MajorID INT PRIMARY KEY, MajorName NVARCHAR(50) NOT NULL, DeptID INT NOT NULL, CONSTRAINT FK_Major_Dept FOREIGN KEY (DeptID) REFERENCES Department(DeptID) ); -- 学生表 CREATE TABLE Student ( StudentID CHAR(10) PRIMARY KEY, Name NVARCHAR(20) NOT NULL, Gender CHAR(2) CHECK (Gender IN (男,女)), MajorID INT NOT NULL, Grade INT NOT NULL, CONSTRAINT FK_Student_Major FOREIGN KEY (MajorID) REFERENCES Major(MajorID) ); -- 教师表 CREATE TABLE Teacher ( TeacherID CHAR(8) PRIMARY KEY, Name NVARCHAR(20) NOT NULL, Title NVARCHAR(20), DeptID INT NOT NULL, CONSTRAINT FK_Teacher_Dept FOREIGN KEY (DeptID) REFERENCES Department(DeptID) ); -- 课程表 CREATE TABLE Course ( CourseID CHAR(8) PRIMARY KEY, CourseName NVARCHAR(50) NOT NULL, Credit DECIMAL(3,1) NOT NULL CHECK (Credit 0), CourseType NVARCHAR(10) CHECK (CourseType IN (必修,选修)) ); -- 开课表 CREATE TABLE CourseOffering ( OfferingID INT IDENTITY(1,1) PRIMARY KEY, CourseID CHAR(8) NOT NULL, TeacherID CHAR(8) NOT NULL, Semester NVARCHAR(20) NOT NULL, Capacity INT NOT NULL DEFAULT 60, Enrolled INT NOT NULL DEFAULT 0, CONSTRAINT FK_Offering_Course FOREIGN KEY (CourseID) REFERENCES Course(CourseID), CONSTRAINT FK_Offering_Teacher FOREIGN KEY (TeacherID) REFERENCES Teacher(TeacherID), CONSTRAINT CK_Enrolled CHECK (Enrolled 0 AND Enrolled Capacity) ); -- 选课表 CREATE TABLE Enrollment ( EnrollmentID INT IDENTITY(1,1) PRIMARY KEY, StudentID CHAR(10) NOT NULL, OfferingID INT NOT NULL, SelectTime DATETIME NOT NULL DEFAULT GETDATE(), Score DECIMAL(5,1) NULL, CONSTRAINT FK_Enroll_Student FOREIGN KEY (StudentID) REFERENCES Student(StudentID), CONSTRAINT FK_Enroll_Offering FOREIGN KEY (OfferingID) REFERENCES CourseOffering(OfferingID), CONSTRAINT UQ_Student_Offering UNIQUE (StudentID, OfferingID) ); GO逻辑说明先建字典表院系、专业再建主表学生、教师、课程最后建关联表开课、选课这样外键引用不会报错。CK_Enrolled约束保证已选人数不会超过容量也不会为负这是数据库层面的兜底。参数说明CHAR(10)存学号固定 10 位比VARCHAR省空间DECIMAL(3,1)存学分支持 0.5 学分GETDATE()自动记录选课时间。3.2 选课存储过程事务 行锁防超选选课不是简单 INSERT要同时做三件事检查容量、插入选课记录、更新已选人数。这三步必须在一个事务里否则并发时会超选。CREATE PROCEDURE sp_SelectCourse StudentID CHAR(10), OfferingID INT AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; -- 加行锁读取开课信息防止并发超选 DECLARE Capacity INT, Enrolled INT; SELECT Capacity Capacity, Enrolled Enrolled FROM CourseOffering WITH (UPDLOCK, ROWLOCK) WHERE OfferingID OfferingID; IF Enrolled Capacity BEGIN ROLLBACK TRANSACTION; RAISERROR(课程已满, 16, 1); RETURN; END -- 插入选课记录唯一约束会拦截重复选课 INSERT INTO Enrollment (StudentID, OfferingID) VALUES (StudentID, OfferingID); -- 更新已选人数 UPDATE CourseOffering SET Enrolled Enrolled 1 WHERE OfferingID OfferingID; COMMIT TRANSACTION; END TRY BEGIN CATCH IF TRANCOUNT 0 ROLLBACK TRANSACTION; THROW; END CATCH END GO逻辑说明WITH (UPDLOCK, ROWLOCK)是关键它在读取开课记录时就加更新锁其他会话想同时选这门课会被阻塞等第一个事务提交后再读从而避免两个学生同时读到「还剩 1 个名额」然后都插入成功。TRY...CATCH保证出错时回滚不会留下脏数据。参数说明StudentID和OfferingID由调用方传入。RAISERROR抛出自定义错误前端可以捕获提示「课程已满」。3.3 退课与成绩录入的配套脚本退课逻辑和选课相反先删选课记录再减已选人数同样要事务。CREATE PROCEDURE sp_DropCourse StudentID CHAR(10), OfferingID INT AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; DELETE FROM Enrollment WHERE StudentID StudentID AND OfferingID OfferingID; IF ROWCOUNT 0 BEGIN ROLLBACK TRANSACTION; RAISERROR(未找到选课记录, 16, 1); RETURN; END UPDATE CourseOffering SET Enrolled Enrolled - 1 WHERE OfferingID OfferingID; COMMIT TRANSACTION; END TRY BEGIN CATCH IF TRANCOUNT 0 ROLLBACK TRANSACTION; THROW; END CATCH END GO成绩录入用一条 UPDATE 即可但要限制分数范围。CREATE PROCEDURE sp_InputScore EnrollmentID INT, Score DECIMAL(5,1) AS BEGIN IF Score 0 OR Score 100 BEGIN RAISERROR(分数必须在0-100之间, 16, 1); RETURN; END UPDATE Enrollment SET Score Score WHERE EnrollmentID EnrollmentID; END GO逻辑说明退课先删记录再减人数顺序不能反否则删失败时人数已经减了。成绩录入先校验范围避免脏数据进库。参数说明ROWCOUNT判断删除是否命中记录没命中说明学生根本没选这门课直接报错回滚。4. 查询与统计答辩最常被问的几张报表怎么写4.1 学生课表查询与已选学分统计学生登录后要看自己的课表这条查询要联三张表选课表、开课表、课程表。SELECT c.CourseName, t.Name AS TeacherName, o.Semester, c.Credit, e.Score FROM Enrollment e JOIN CourseOffering o ON e.OfferingID o.OfferingID JOIN Course c ON o.CourseID c.CourseID JOIN Teacher t ON o.TeacherID t.TeacherID WHERE e.StudentID 2024010001 ORDER BY o.Semester, c.CourseID;已选学分统计要注意只有成绩及格60或成绩为空在修的课才算学分挂科的不算。SELECT SUM(c.Credit) AS TotalCredit FROM Enrollment e JOIN CourseOffering o ON e.OfferingID o.OfferingID JOIN Course c ON o.CourseID c.CourseID WHERE e.StudentID 2024010001 AND (e.Score IS NULL OR e.Score 60);逻辑说明Score IS NULL表示还在修先计入Score 60表示已通过。两者取或挂科的自动排除。4.2 课程选课人数与容量对比报表教务最关心哪些课快满了、哪些课没人选。SELECT c.CourseName, t.Name AS TeacherName, o.Semester, o.Enrolled, o.Capacity, CAST(o.Enrolled * 100.0 / o.Capacity AS DECIMAL(5,1)) AS FillRate FROM CourseOffering o JOIN Course c ON o.CourseID c.CourseID JOIN Teacher t ON o.TeacherID t.TeacherID ORDER BY FillRate DESC;逻辑说明FillRate是满座率乘以 100.0 是为了避免整数除法丢精度。CAST保留一位小数方便排序和展示。参数说明如果想只看满座率超过 80% 的课加WHERE o.Enrolled * 1.0 / o.Capacity 0.8。4.3 成绩分布与挂科率统计答辩老师常问「你怎么统计挂科率」这条查询直接给答案。SELECT c.CourseName, COUNT(*) AS TotalCount, SUM(CASE WHEN e.Score 60 THEN 1 ELSE 0 END) AS FailCount, CAST(SUM(CASE WHEN e.Score 60 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,1)) AS FailRate FROM Enrollment e JOIN CourseOffering o ON e.OfferingID o.OfferingID JOIN Course c ON o.CourseID c.CourseID WHERE e.Score IS NOT NULL GROUP BY c.CourseName;逻辑说明CASE WHEN做条件计数只统计有成绩的记录。GROUP BY按课程分组得到每门课的挂科率。参数说明WHERE e.Score IS NOT NULL排除在修课程避免把没出成绩的算成挂科。5. 避坑与排查课程设计里最容易翻车的五个点5.1 并发选课导致超选现象是已选人数超过容量现象两个学生同时选最后一门课都提示成功但Enrolled变成Capacity 1。原因读取容量和更新人数之间没有加锁两个事务都读到了旧值。解决在存储过程里用WITH (UPDLOCK, ROWLOCK)读取开课记录把读和更新锁在同一个事务里。上面sp_SelectCourse已经这么做了。如果不想用锁也可以在UPDATE时加WHERE Enrolled Capacity条件根据ROWCOUNT判断是否成功。5.2 重复选课报主键冲突而不是友好提示现象学生重复点选课按钮前端收到「违反唯一约束」的英文报错。原因唯一约束UQ_Student_Offering拦截了重复插入但错误信息不友好。解决在存储过程里先查一下是否已选或者用TRY...CATCH捕获唯一约束错误错误号 2627转成中文提示「您已选过这门课」。前端也要做按钮防抖。5.3 删除学生时外键报错删不掉现象想删一个退学的学生提示外键冲突。原因选课表里有该学生的选课记录外键阻止删除。解决两种方案。一是先删选课记录再删学生用事务包起来二是给学生表加IsDeleted状态字段做软删除不物理删。课程设计里推荐第二种更贴近真实系统。5.4 学分统计把挂科也算进去了现象学生挂了一门 4 学分的课总学分还是显示 4 学分。原因统计时没加Score 60条件。解决统计已修学分时用WHERE Score IS NULL OR Score 60只算在修和通过的。这条在 4.1 的查询里已经体现。5.5 连接 SQL Server 报 SSL 证书链错误现象用 ODBC 或某些驱动连接时报「证书链是由不受信任的颁发机构颁发的」或「客户端无法建立连接」。原因驱动默认启用了加密但本地 SQL Server 用的是自签名证书不被信任。解决在连接字符串里加TrustServerCertificateTrue或EncryptFalse。SSMS 里连接时勾选「信任服务器证书」。这是本地开发环境的常见问题生产环境应该配正规证书而不是关掉加密。注意EncryptFalse只建议在本地课程设计环境用真实项目不要关加密。6. 把设计讲成故事答辩演示与脚本交付的收尾技巧课程设计最后要交文档和源码答辩时要讲清楚设计取舍。我的习惯是准备一个「演示脚本」按顺序跑几条关键 SQL让老师看到系统是活的。第一步插入测试数据。准备 2 个院系、3 个专业、5 个学生、3 个教师、5 门课程、5 条开课记录。数据不用多但要覆盖各种情况有必修有选修、有满员有未满、有成绩有在修。INSERT INTO Department VALUES (1, N计算机学院), (2, N外国语学院); INSERT INTO Major VALUES (1, N软件工程, 1), (2, N计算机科学, 1), (3, N英语, 2); INSERT INTO Student VALUES (2024010001, N张三, 男, 1, 2024), (2024010002, N李四, 女, 1, 2024), (2024010003, N王五, 男, 2, 2024); INSERT INTO Teacher VALUES (T001, N赵老师, N教授, 1), (T002, N钱老师, N副教授, 1); INSERT INTO Course VALUES (C001, N数据库原理, 4.0, N必修), (C002, N数据结构, 4.0, N必修), (C003, N日语入门, 2.0, N选修); INSERT INTO CourseOffering (CourseID, TeacherID, Semester, Capacity) VALUES (C001, T001, N2024秋, 60), (C002, T002, N2024秋, 50), (C003, T001, N2024秋, 30);第二步演示选课。调用sp_SelectCourse给张三选数据库原理再查选课表确认。EXEC sp_SelectCourse 2024010001, 1; SELECT * FROM Enrollment WHERE StudentID 2024010001;第三步演示冲突。让张三再选一次同一门课展示唯一约束报错或者把某门课容量改成 1让两个学生抢展示第二个被拦截。第四步演示统计。跑 4.2 的满座率报表和 4.3 的挂科率报表说明设计支持教务分析。交付物方面文档里要包含 ER 图、表结构说明、存储过程说明、测试用例。源码就是一个.sql文件按「建库 → 建表 → 存储过程 → 测试数据」的顺序组织别人拿到能一次跑通。我一般会在脚本开头写清楚执行顺序和注意事项比如「先执行建库部分再执行存储过程最后插入测试数据」。一个具体技巧把存储过程的RAISERROR消息统一成中文答辩时演示报错更直观。另外Enrollment表的SelectTime默认值用GETDATE()演示时能看到真实时间戳比写死时间更有说服力。血泪经验是别等到答辩前一天才跑脚本。我见过太多人本地建好了库换台机器就报外键顺序错误。把脚本从头到尾在干净实例上跑一遍是唯一的后悔药。希望帮到你。本文还有配套的精品资源点击获取