简介SQL数据库课程设计宾馆房间管理系统是一份面向软件工程、数据库等相关专业学生的实践性课程设计报告完整呈现基于关系数据库原理的宾馆客房管理系统分析与设计流程重点涉及SQL Server 2000与C#.NET联机应用开发。资源包内仅含1个doc文档压缩包约293KB格式规范便于直接阅读、编辑和作为模板使用。报告按课程设计过程分为数据库设计与程序设计两大部分前一部分从需求分析入手绘制业务流程图、数据流图定义数据字典并完成概念结构、逻辑结构与物理设计最终在SQL Server 2000中实现数据库表、约束和初始数据后一部分则基于C#.NET完成系统概要设计和登录验证、客房信息管理、入住登记、客户查询、结算等功能的程序实现。目前已有139人学习适合需要完成类似课程设计、撰写报告或准备答辩的读者参考尤其有助于快速把握宾馆管理类系统从数据建模到编码实现的完整思路。1. SQL数据库课程设计宾馆房间管理系统考的不是界面是这三件事SQL数据库课程设计里的“宾馆房间管理系统”题目老但每年依然能刷掉不少人。它考的不是前端界面多好看而是三件事关系模式拆得对不对、事务和存储过程写得出来写不出来、课程设计报告里的每一步能不能自圆其说。很多人把时间花在按钮和弹窗上答辩时被一句“房间为什么会被同时开两次”问住。这篇按建表、业务、排查、答辩的顺序把这个题目的完整落地路径走一遍适合正在做课设的学生也适合想用这个题目练手SQL事务、索引和慢查询优化的开发者。题目给得越宽自证的压力越大。一份合格的课程设计代码只是三分之一另外三分之二是数据建模和文字论证。下面先从ER图开始。2. 先画准ER图再动手房间、入住、预订三张核心表的关系设计动手写CREATE TABLE之前先把业务对象列全。宾馆房间管理系统最常见的功能模块是客房信息维护、散客入住、退房结账、预订登记、房态查询、消费记录和简单的经营统计。别把“权限管理”做得很夸张——课程设计不需要RBAC一张用户表管登录即可重点永远是房间、客户、入住、预订这几个核心实体。2.1 需求拆到什么样的粒度才算够需求分析的粒度决定你后面要建几张表。拆的时候用“一个操作背后要改动哪几张表”来判断比如“散客开房”至少涉及房间状态、客户档案、入住记录三处数据“退房结账”涉及入住时长计算、房间释放、账单汇总。粒度不够后面建表会被迫返工。三个核心业务场景可以这样拆场景需要记录的数据后续操作散客入住客人身份证、电话、房间号、入住时间、押金退房时计算房费预订登记预留房间、预期入住/离店日期、联系人到店后转入住退房结账入住时长、房价、额外消费释放房间、生成账单拆到“换房”这个场景时要特别注意换房表面上只是改一条记录实际要更新原房间和目标房间两个状态还要让入住记录指向新房间。常见做法是不单独建换房历史表直接在存储过程中更新stay.room_id并同步两侧房间状态报告里注明不保留历史即可课程设计不需要做审计。2.2 从ER图到关系模式三范式下最少变动的表结构ER图画完后转关系模式核心表七张房型、房间、客户、入住、预订、消费、系统用户。关系模式的主外键分布如下关系主键外键说明room_typeroom_type_id无房型和单价独立于具体房间roomroom_idroom_type_id每个房间归属一个房型guestguest_id无客户档案身份证做唯一约束reservationreservation_idroom_id, guest_id预订单不直接写入住staystay_idroom_id, guest_id每一次入住生成一条记录consumptionconsume_idstay_id房内消费一个入住可对应多条sys_useruser_id无系统登录账号这张表之所以这样拆核心是满足三范式。第一范式要求字段原子常见的错误是把多笔消费塞进一个字段第二范式要求非主键属性完整依赖主键stay表里不放guest_name通过guest_id关联第三范式要求去掉传递依赖最典型的例子是room表里不写price价格只放在room_type表。把price放到room表会出什么事大床房调价时你得像扫地一样逐个房间UPDATE漏掉一间报表就是对不上的。拆出room_type后改一次价格所有房间同步生效这就是消除传递依赖带来的更新异常收敛。另外一个高频错误是拿身份证号或电话号码做主键这两个字段适合做唯一约束而不是主键一旦主人变更主键变更带来的外键级联重建成本太高。3. 用建表SQL把ER图落地主外键、约束、默认值一次写对ER图画完下一步才是写SQL。常见做法是先用SQL Server把脚本跑通再用MySQL移植因为课程设计答辩环境不确定两套都要能跑。下面这份建库建表脚本SQL Server 2008以上版本都能直接执行MySQL主要改一下自增写法。3.1 建库建表脚本约束和默认值放在哪一层CREATE DATABASE HotelDB; GO USE HotelDB; GO CREATE TABLE room_type ( room_type_id INT IDENTITY(1,1) PRIMARY KEY, type_name NVARCHAR(20) NOT NULL, price DECIMAL(10,2) NOT NULL CHECK (price 0), bed_count INT NOT NULL DEFAULT 2, area DECIMAL(6,2) ); CREATE TABLE room ( room_id INT IDENTITY(1,1) PRIMARY KEY, room_no NVARCHAR(10) NOT NULL UNIQUE, room_type_id INT NOT NULL, floor INT NOT NULL CHECK (floor BETWEEN 1 AND 30), status NVARCHAR(10) NOT NULL DEFAULT N空闲 CHECK (status IN (N空闲, N占用, N维修)), remark NVARCHAR(100), CONSTRAINT fk_room_type FOREIGN KEY (room_type_id) REFERENCES room_type(room_type_id) ); CREATE TABLE guest ( guest_id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(20) NOT NULL, id_card NVARCHAR(18) NOT NULL UNIQUE, phone NVARCHAR(11) NOT NULL, register_time DATETIME NOT NULL DEFAULT GETDATE() ); CREATE TABLE stay ( stay_id INT IDENTITY(1,1) PRIMARY KEY, room_id INT NOT NULL, guest_id INT NOT NULL, checkin_time DATETIME NOT NULL DEFAULT GETDATE(), checkout_time DATETIME NULL, deposit DECIMAL(10,2) NOT NULL DEFAULT 200.00, total_cost DECIMAL(10,2) NULL, status NVARCHAR(10) NOT NULL DEFAULT N在住 CHECK (status IN (N在住, N已结账)), CONSTRAINT fk_stay_room FOREIGN KEY (room_id) REFERENCES room(room_id), CONSTRAINT fk_stay_guest FOREIGN KEY (guest_id) REFERENCES guest(guest_id) );IDENTITY(1,1)是SQL Server的自增写法MySQL改成AUTO_INCREMENT放在同一列定义里比如room_id INT AUTO_INCREMENT PRIMARY KEY。身份证字段用NVARCHAR(18)而不是数字类型原因是身份证有前导零且18位超过bigint上限这一点在第5章还会展开。CHECK约束写在表里相当于把业务规则下沉到数据库层界面层再错也进不来脏数据。提示SQL Server里外键不会自动建索引MySQL InnoDB会自动为外键加索引。这个差异在写性能报告和死锁分析时要体现不然答辩老师一问就露馅。接着建预订、消费和用户三张表CREATE TABLE reservation ( reservation_id INT IDENTITY(1,1) PRIMARY KEY, room_id INT NOT NULL, guest_id INT NOT NULL, reserve_date DATETIME NOT NULL DEFAULT GETDATE(), checkin_date DATE NOT NULL, checkout_date DATE NOT NULL, status NVARCHAR(10) NOT NULL DEFAULT N有效 CHECK (status IN (N有效, N已取消, N已入住)), deposit DECIMAL(10,2) NOT NULL DEFAULT 100.00, CONSTRAINT fk_res_room FOREIGN KEY (room_id) REFERENCES room(room_id), CONSTRAINT fk_res_guest FOREIGN KEY (guest_id) REFERENCES guest(guest_id) ); CREATE TABLE consumption ( consume_id INT IDENTITY(1,1) PRIMARY KEY, stay_id INT NOT NULL, item_name NVARCHAR(50) NOT NULL, amount DECIMAL(10,2) NOT NULL CHECK (amount 0), consume_time DATETIME NOT NULL DEFAULT GETDATE(), CONSTRAINT fk_cons_stay FOREIGN KEY (stay_id) REFERENCES stay(stay_id) ); CREATE TABLE sys_user ( user_id INT IDENTITY(1,1) PRIMARY KEY, username NVARCHAR(20) NOT NULL UNIQUE, password NVARCHAR(64) NOT NULL, role NVARCHAR(10) NOT NULL DEFAULT N前台 );reservation表里的checkin_date和checkout_date是DATE类型因为它们表示“计划日期”和stay表里记录实际发生的DATETIME时间点分开。consumption挂在stay_id下退房时汇总消费记录和房费一起算总账。sys_user表的password字段留了64位意思是密码要存哈希而不是明文这点在第5章安全性部分对应。3.2 测试数据不是凑数量每行数据都要能支撑一条查询建表完成后灌测试数据很多人直接手写几十条差不多的记录结果报表查询跑出来没有区分度。我一般先插基础维度数据再插业务流水数据让每条数据都能在后面的统计里“讲出故事”。INSERT INTO room_type(type_name, price, bed_count, area) VALUES (N大床房, 288.00, 1, 28), (N标准双人间, 358.00, 2, 32); INSERT INTO room(room_no, room_type_id, floor, status) VALUES (N201, 1, 2, N空闲), (N202, 1, 2, N空闲), (N301, 2, 3, N空闲); INSERT INTO guest(name, id_card, phone) VALUES (N王一, N110101199001010011, N13800000001), (N李二, N110101199202020022, N13900000002); INSERT INTO stay(room_id, guest_id, checkin_time, deposit, status) VALUES (1, 1, DATEADD(DAY, -2, GETDATE()), 200.00, N在住), (3, 2, DATEADD(DAY, -1, GETDATE()), 300.00, N在住); INSERT INTO consumption(stay_id, item_name, amount) VALUES (1, N矿泉水, 10.00), (1, N方便面, 12.00), (2, N矿泉水, 10.00);DATEADD(DAY, -2, GETDATE())是SQL Server的相对时间写法好处是今天跑和明天跑都能计算出“已住2天”的效果。测试数据不必多两间房在住、三间房空闲的状态覆盖就够消费记录故意让一个住客有两笔、另一个有一笔退房后做消费汇总时正好能看出GROUP BY的效果。这套数据在后面第6章的统计查询里会直接复用。4. 把入住到退房的全流程串进存储过程两个可直接改用的样例数据库课设要拿高分增删改查只是基础分核心业务必须写成存储过程和事务。常见做法是入住对应一个存储过程退房对应一个存储过程换房再一个。业务规则全部放进数据库层界面层只管调用既是三层架构的思路也让答辩有东西可讲。4.1 开房并发下房间状态怎么保证不被重复预订并发提交这件事我用一次血泪经验告诉你为什么不能省略UPDLOCK。两个前台同时给不同客人开同一间房如果代码先SELECT查到状态是“空闲”再UPDATE改成“占用”两步之间另一个会话也可能读到“空闲”结果就是一间房开给两个人。CREATE PROCEDURE sp_checkin room_no NVARCHAR(10), name NVARCHAR(20), id_card NVARCHAR(18), phone NVARCHAR(11), deposit DECIMAL(10,2) 200 AS BEGIN SET NOCOUNT ON; BEGIN TRY BEGIN TRANSACTION; DECLARE room_id INT, room_status NVARCHAR(10), guest_id INT; -- 用更新锁锁定这间房第二个会话必须等待 SELECT room_id room_id, room_status status FROM room WITH (UPDLOCK, ROWLOCK) WHERE room_no room_no; IF room_id IS NULL BEGIN RAISERROR(N房间号不存在, 16, 1); ROLLBACK TRANSACTION; RETURN; END; IF room_status N空闲 BEGIN RAISERROR(N房间不是空闲状态无法开房, 16, 1); ROLLBACK TRANSACTION; RETURN; END; -- 客户已存在则复用否则新增 SELECT guest_id guest_id FROM guest WHERE id_card id_card; IF guest_id IS NULL BEGIN INSERT INTO guest(name, id_card, phone) VALUES(name, id_card, phone); SET guest_id SCOPE_IDENTITY(); END; INSERT INTO stay(room_id, guest_id, checkin_time, deposit, status) VALUES(room_id, guest_id, GETDATE(), deposit, N在住); UPDATE room SET status N占用 WHERE room_id room_id; COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; THROW; END CATCH; END;关键参数和逻辑说明deposit默认200元代表开房押金WITH (UPDLOCK, ROWLOCK)把锁粒度控制在单行避免锁住整张room表。SCOPE_IDENTITY()是拿刚插入guest行的自增主键不能用IDENTITY因为后者可能被触发器改掉。事务把“查房状态—建客户—写入住—改房态”包成原子操作任何一步出错回滚不会出现房间占了但入住记录没写的情况。验证并发是否生效开两个SSMS查询窗口同时执行下面这条调用一个是成功返回另一个在房间状态判断处报错EXEC sp_checkin room_no N201, name N王五, id_card N110101199303030033, phone N13800000003;4.2 退房结账房费计算与账务平衡的写法和参数说明退房比开房多一个“算钱”的逻辑按天计价、不足一天按一天算、额外消费要加进总账。计费口径必须在需求文档里写明不然答辩老师拿“中午12点退房怎么算”这种问题答不上来就是硬伤。CREATE PROCEDURE sp_checkout stay_id INT, extra_amount DECIMAL(10,2) 0, final_cost DECIMAL(10,2) OUTPUT AS BEGIN SET NOCOUNT ON; DECLARE room_id INT, price DECIMAL(10,2), checkin_time DATETIME, days INT; BEGIN TRY BEGIN TRANSACTION; -- 锁定这条入住记录避免同一笔单被重复结账 SELECT room_id room_id, checkin_time checkin_time FROM stay WITH (UPDLOCK) WHERE stay_id stay_id AND status N在住; IF room_id IS NULL BEGIN RAISERROR(N入住记录不存在或已结账, 16, 1); ROLLBACK TRANSACTION; RETURN; END; SELECT price rt.price FROM room r JOIN room_type rt ON r.room_type_id rt.room_type_id WHERE r.room_id room_id; -- 按自然日计费不足一天按一天 SET days DATEDIFF(DAY, checkin_time, GETDATE()); IF days 1 SET days 1; SET final_cost days * price extra_amount; UPDATE stay SET checkout_time GETDATE(), total_cost final_cost, status N已结账 WHERE stay_id stay_id; UPDATE room SET status N空闲 WHERE room_id room_id; COMMIT TRANSACTION; END TRY BEGIN CATCH ROLLBACK TRANSACTION; THROW; END CATCH; END;说明extra_amount是房内消费以外的加项如果用了consumption表可以把它替换成(SELECT ISNULL(SUM(amount), 0) FROM consumption WHERE stay_id stay_id)这样连锁消费自动汇总。final_cost用OUTPUT关键字返回给调用方便于界面直接显示账单。这里更新顺序是“先stay后room”和4.1的“先room后stay”相反这个顺序差异会在第5章的死锁部分被放大。调用示例DECLARE cost DECIMAL(10,2); EXEC sp_checkout stay_id 1, extra_amount 22.00, final_cost cost OUTPUT; PRINT cost;5. 常见问题与避坑SQL注入到死锁五个答辩现场高频扣分点建表和存储过程写完接下来是答辩前最该回看的五个坑。这五个坑基本是每年课程设计翻车的高频区前三个直接关系到系统能不能在演示现场不出状况后两个关系到老师翻你数据库时会不会皱眉。5.1 登录框拼SQL导致注入字符串拼接把用户表拖走现象登录功能在界面层直接把账号密码拼进SQL字符串比如把username和password变量直接拼进SELECT * FROM sys_user的WHERE条件。在账号框输入一个特殊构造的字符串后整条SQL恒真无需密码就进入系统最坏情况可以把整张用户表拖走。原因查询字符串被当成可执行代码而不是数据。解决改成参数化查询后台直接调存储过程账号密码只作为参数传入服务端再把密码字段做哈希比对。课程设计报告里把“安全性设计”独立写一节把参数化的理由描述清楚这个细节很加分。5.2 房间状态超卖并发窗口怎么产生现象两个前台同时给不同客人开同一间房两笔操作都成功系统出现“一房两客”。原因代码先SELECT * FROM room WHERE room_no 201 AND status 空闲发现空闲后再UPDATE room SET status 占用。两个会话可能同时读到“空闲”再继续执行UPDATE第二个UPDATE同样成功。解决在事务里用UPDLOCK锁定读取是对的或者更直接一点把“先查后改”改成“先改后判”UPDATE room SET status N占用 WHERE room_no N201 AND status N空闲; IF ROWCOUNT 0 RAISERROR(N房间不可用, 16, 1);WHERE条件里带status空闲UPDATE自带锁ROWCOUNT为0说明没抢到占用资格。这个写法不需要显式事务也能堵住并发窗口。5.3 换房和结账交叉锁表死锁是怎么形成的现象操作员A给客人换房先改room表再改stay表操作员B同时给另一个客人结账先改stay表再改room表。两个事务各自持有一把锁并向对方要另一把锁SQL Server报错“事务与另一个进程已被锁定在 resource 上”。原因两个事务对room和stay两张表的加锁顺序不一致形成环形等待。解决统一所有存储过程的更新顺序。4.1开房是“先room后stay”4.2退房是“先stay后room”这两种顺序本身没问题但换房存储过程必须和退房保持同向。排查时用这条动态管理视图看当前阻塞链SELECT session_id, blocking_session_id, wait_type, wait_time FROM sys.dm_exec_requests WHERE blocking_session_id 0;也可以在前面加一句SET LOCK_TIMEOUT 3000;让锁等待超过3秒直接报错演示现场至少不会卡死。5.4 身份证字段用bigint前导零丢失和长度溢出现象建表时用id_card BIGINT导数据时身份证变成科学计数法或者18位身份证超出bigint上限变成乱码。原因身份证和电话号码是“看起来像数字的文本”不是数值类型。解决统一用NVARCHAR(18)和NVARCHAR(11)界面层限制输入长度。这个教训不只课设有用真实项目的客户主数据也一样。在报告物理设计一节补一句理由不用数字类型可以避免前导零和科学计数法这属于设计意识。5.5 密码到期导致演示连不上库登录策略坑现象头一天开发得好好的第二天答辩连不上数据库报“登录密码已过期”。原因SQL Server安装或组策略启用了密码过期策略sa或其他登录账号在密码超过有效期后必须修改。解决教学演示环境直接关闭策略ALTER LOGIN sa WITH CHECK_EXPIRATION OFF; ALTER LOGIN sa WITH CHECK_POLICY OFF;注意这是演示环境配置不是生产库做法。数据库的账号和密码单独写在报告一页方便老师复现环境也避免现场临时翻笔记的尴尬。6. 让答辩多拿十分的三件事测试数据、报告结构和必问考点6.1 用能讲故事的测试数据替代随机数字课程设计最容易被看出用心程度的地方是测试数据。手工插入一堆“abc、123”老师一眼就知道你没想过业务。我一般把测试数据设计成能回答业务问题的样子谁住得久、哪个房型最赚钱、哪个房间入住率最高。下面这条查询可以直接当报表截图放进文档SELECT TOP 3 r.room_no, rt.type_name, COUNT(s.stay_id) AS 入住次数, SUM(rt.price) AS 房费收入 FROM stay s JOIN room r ON s.room_id r.room_id JOIN room_type rt ON r.room_type_id rt.room_type_id WHERE s.status N已结账 GROUP BY r.room_no, rt.type_name ORDER BY 房费收入 DESC;这条查询涉及三张表连接加聚合正好回应“数据库设计有没有体现关系”这个必问题。测试数据量不用大但要覆盖不同状态每行都要能讲清楚它在验证哪条业务规则。6.2 .doc课程设计报告从ER图到SQL脚本的完整编排题目文件带着“.doc”说明交付物不只有代码还有一份完整的课程设计报告。常见目录是需求分析、概念设计ER图、逻辑设计关系模式加范式证明、物理设计建表脚本加索引说明、存储过程与视图实现、测试报告、总结。关键不在栏目多少在每一轮的文字都要能对上代码。ER图里的实体和第二章的关系模式表一一对应属性别画完了不建表。逻辑设计写出每个关系的主外键并证明它满足第三范式。测试报告贴执行结果时注明用了第3章哪一批序号的数据。附录放全部SQL脚本按建表、数据、存储过程、查询分节让老师一步步能跑出来。报告里的ER图、界面截图即使存成.doc也要保证图片清晰。文档和代码是同一套交付物代码能跑不是终点文档能让别人复现才是。6.3 三个必问考点的口头答案索引、视图、范式老师常问“这个表为什么加索引”。可以答stay表的room_id和guest_id是高频连接条件需要加索引room表的status字段区分度低不需要单列索引。外键会带来额外的锁范围索引加不加是在读性能和并发之间取舍。再问“视图和存储过程的区别”。可答视图是虚拟表封装复杂查询存储过程是可执行逻辑封装事务写操作。本系统两个都在用视图查报表存储过程写业务。最后问“范式怎么证明”拿stay表现说checkin_time只依赖stay_id不依赖room_id房费单价从room_type取避免传递依赖。这三段答顺了报告里的对应章节也就有了底气。我印象最深的一次答辩不是存储过程写错而是老师指着一行测试数据问“这间房状态明明是占用为什么入住率报表没把它算进去”——报表统计口径和status字段没对齐。从那以后我养成了一个习惯每条测试数据都要能在报告里讲清楚它在验证哪条业务规则。做这个题目时如果你也能从建表第一天就把数据、脚本和报告当成同一套交付物来维护答辩会轻松很多。希望帮到你。本文还有配套的精品资源点击获取
