简介这份数据库设计案例文档面向计算机专业学生、数据库课程学习者及需要完成课程设计或毕业设计的人群以酒店管理系统为背景提供一套可直接参考的数据库设计范本。资源包内含1个doc文档大小约233KB内容围绕总经理、财务、住宿、娱乐四个子系统展开逐一给出功能说明与数据库表结构设计。读者可从中获取职工信息表、部门信息表、收支登记表、财务汇总表、客人信息表、房间管理表、房间类别表及娱乐项目表等核心数据表的字段定义并附有数据字典、数据结构与数据流清单便于理解实体关系与业务流程的对应逻辑。目前已有257人学习下载适合作为数据库原理课程设计、E-R建模练习及表结构规范书写的参考材料也可用于快速搭建酒店管理类信息系统的数据层设计思路。1. 从一份 .doc 说起酒店管理系统数据库设计到底交付了什么如果你手头正躺着一份《数据库设计案例-酒店管理系统.doc》大概率是课程设计、毕设开题或者面试前想找个完整案例把 E-R 图到物理设计这条链路走一遍。这份文档的价值不在于它有多复杂而在于它把一个中等规模酒店抽象成四个子系统——总经理、财务、住宿、娱乐——并且老老实实走完了需求分析、数据字典、分 E-R 图、视图集成、逻辑结构设计、用户子模式、物理结构设计这一整套流程。换句话说它不是那种只给你几张表就完事的速成模板而是一份能让你看清“一个数据库是怎么从业务描述一步步收敛成关系模式”的完整推演记录。适合谁适合正在做数据库课设、需要交一份有推导过程的设计文档的人也适合想复习 E-R 图集成和范式判定这些基本功的从业者。下面我不复述文档而是把它拆成能照着复现的步骤顺带把几个容易翻车的地方标出来。2. 需求到数据字典四个子系统怎么切、数据项怎么定2.1 子系统划分的取舍逻辑文档里最值得先想清楚的一点是它为什么把饮食部门“砍掉”了。原文的判断是饮食部门实时性强、持续时间短人工操作反而比电脑更有效率真正需要长期保留的只有财务信息。于是饮食子系统的功能被并入财务子系统最终留下总经理、财务、住宿、娱乐四个部分。这个取舍不是拍脑袋它背后是一条实用原则——不是所有业务都值得进数据库只有需要长期保留、需要共享、需要汇总的信息才值得。你在做自己的设计时也可以套这个尺子先问这条数据会不会被反复查询、会不会跨部门共享、要不要留痕上报三个都不沾的就别硬塞进表里。住宿子系统的职责相对完整房间分类编号、制定收费标准、登记旅客入住退房、统计客满程度、登记本部门财务流动。娱乐子系统则聚焦在项目管理和收支财务处理上。总经理子系统管职工和部门财务子系统做汇总。四个子系统各自有分 E-R 图最后再集成。2.2 数据字典的四个组成部分文档把数据字典拆成数据项、数据结构、数据流、数据存储、处理过程五块这是标准做法。数据项部分列了 35 项比如职工号是整数类型且有唯一性性别是枚举类型男、女年龄整数范围 18 到 100工龄 0 到 100入住时间和退出时间格式都是**/**。这些约束看着琐碎但它们是后面建表时字段类型和 CHECK 约束的直接来源。数据结构部分把数据项组合成有业务含义的单元比如职工信息 职工号、姓名、性别、年龄、工龄、级别、部门、职务、备注房间 房间号、房间类别、状态客人信息 房间号、客人数量、联系人名、身份、证件类型、证件号码、入住时间、退出时间、备注。数据流部分描述了信息在子系统之间的流动方向比如“顾客基本信息”从“来客登记”流向“顾客信息”存储“住房单价”从“住房信息”流向“住宿管理部门收入”。数据存储部分则明确了每个存储的输入输出数据流。提示数据字典不是写完就锁死的。你在做逻辑设计时如果发现某个数据项在多个关系里重复出现回头改数据字典比改一堆表结构省事得多。2.3 把数据字典落成建表语句的示范文档给的是关系模式我把它转成可执行的 SQL你对照着看字段类型和约束是怎么从数据项描述里推出来的。以职工、部门、客房三张表为例-- 职工表职工号唯一性别枚举年龄和工龄有范围约束 CREATE TABLE employee ( emp_id INT PRIMARY KEY, -- 职工号整数唯一 emp_name VARCHAR(10) NOT NULL, -- 姓名文本长度10 gender ENUM(男,女) NOT NULL, -- 性别枚举 age INT CHECK (age BETWEEN 18 AND 100), work_years INT CHECK (work_years BETWEEN 0 AND 100), level_id INT, -- 级别号 dept_id INT, -- 部门号外键 job_title VARCHAR(20), -- 职务 remark VARCHAR(200), FOREIGN KEY (dept_id) REFERENCES department(dept_id) ); -- 部门表部门号唯一部门经理参照职工号 CREATE TABLE department ( dept_id INT PRIMARY KEY, dept_name VARCHAR(50) NOT NULL, manager_id INT, -- 部门经理参照职工号 emp_count INT DEFAULT 0, -- 职工数量 finance_id INT, -- 财务状况编号 FOREIGN KEY (manager_id) REFERENCES employee(emp_id) ); -- 客房表客房号唯一状态枚举管理人员参照职工号 CREATE TABLE room ( room_no VARCHAR(10) PRIMARY KEY, -- 客房号数字串唯一 room_type VARCHAR(20) NOT NULL, -- 类别 dept_id INT, location VARCHAR(50), equipment VARCHAR(200), -- 设备说明 price DECIMAL(10,2), -- 收费标准 manager_id INT, -- 管理人员号 status ENUM(空闲,已入住,维修) DEFAULT 空闲, FOREIGN KEY (dept_id) REFERENCES department(dept_id), FOREIGN KEY (manager_id) REFERENCES employee(emp_id) );逻辑说明employee表的gender用 ENUM 对应数据字典里的枚举类型age和work_years用 CHECK 约束把范围写死这样插入脏数据时数据库直接拦下来。department表的manager_id外键指向employee但这里有个循环依赖——部门经理本身是职工职工又属于部门。文档在 E-R 图调整时用“等级”属性来表示领导关系来简化建表时你可以先插部门再插职工或者把外键约束延迟到事务提交时检查。room表的status用 ENUM 对应“该房是否已被入住”的枚举描述默认值设为空闲。参数说明VARCHAR(10)对应数据字典里“文本类型长度为 10 字符”的姓名DECIMAL(10,2)用于收费标准保留两位小数CHECK约束的范围直接抄数据字典里的 18…100 和 0…100。如果你用的数据库不支持 ENUM换成VARCHAR加CHECK (gender IN (男,女))效果一样。3. E-R 图集成与逻辑结构设计从分图到 BCNF 的完整推演3.1 四个分 E-R 图的实体与联系文档对每个子系统都画了分 E-R 图并做了调整。经理子系统的实体有职工、工资、部门、账单联系是“组成”职工与部门、“核算”部门与账单。娱乐子系统的实体有项目、职工、顾客、款项、折扣规则、账单联系包括“负责”职工与项目、“选择”顾客与项目、“应付”顾客与款项、“对应”款项与折扣规则、“核算”部门与账单。住宿子系统的实体有顾客、客房、职工、款项、折扣规则、订单、账单联系有“住宿”顾客与客房、“预约”订单与客房、“负责”职工与客房、“应付”顾客与款项、“预订”顾客与订单、“核算”部门与账单。财务子系统的实体有部门、职工、账单、总账、财务状况联系是“组成”“核算”“结算”“下发”“汇总”。调整准则文档写得很清楚能作为属性对待的尽量作为属性属性是不可分的数据项。具体调整包括用职工的“等级”属性表示领导关系把工资单独作为实体以强调出勤工资把款项单独作为实体以强调折扣把账单作为实体以简化财务子系统。这些调整的动机都是让后续的关系模式更干净避免在集成时出现结构冲突。3.2 视图集成时三类冲突的处理文档在集成时检查了属性冲突、命名冲突、结构冲突。属性冲突里属性域冲突和取值单位冲突都不存在命名冲突里同名异义和异名同义也不存在结构冲突里“同一对象在不同应用中具有不同抽象”这个问题在分 E-R 图设计阶段就提前解决了——把任何分图中作为实体出现的属性全部作为实体。这个做法值得学与其等到集成时再改不如在分图阶段就统一抽象层次。文档也承认系统简单所以初步 E-R 图就是基本 E-R 图没有冗余需要消除。集成后的总 E-R 图给出了 12 个实体职工、工资、部门、项目、顾客、客房、款项、折扣规则、订单、账单、总账、财务状况。每个实体的属性都在文档里列全了比如客房 客房号、类别、部门号、位置、设备、收费标准、管理人员号、状态。3.3 关系模式转换与范式判定逻辑结构设计部分把实体和联系都转成了关系模式。实体直接对应关系1:1 和 n:1 联系合并到实体关系中n:m 联系单独建关系。文档明确写了合并规则工资和职工的 1:1 合并、顾客和订单的 1:1 合并、折扣规则和款项的 1:1 合并、职工和部门的 n:1 合并、部门和财务状况的 n:1 合并、客房和部门的 n:1 合并、项目和部门的 n:1 合并、总账和财务状况的 n:1 合并、账单和总账的 n:1 合并、账单和项目的 n:1 合并。n:m 联系转成三个独立关系预约订单号、客房号、始定时间、结束时间、住宿顾客号、房间号码、住宿时间、选择顾客号、项目号、发生时间、经受人号、备注。范式判定结果大部分关系是 BCNF预约、住宿、选择是 3NF。文档还做了两处优化顾客关系删除了“使用时间”理由是必要性不强且可在别的关系中查到总账关系删除了“净利”理由是可由收入支出计算且不常查询。但财务状况关系保留了“净利润”因为查询频繁保留冗余换效率文档自己说“利大于弊”。-- n:m 联系转成的独立关系以住宿为例 CREATE TABLE accommodation ( guest_id INT, -- 顾客号 room_no VARCHAR(10), -- 房间号码 stay_time DATETIME, -- 住宿时间 PRIMARY KEY (guest_id, room_no, stay_time), FOREIGN KEY (guest_id) REFERENCES guest(guest_id), FOREIGN KEY (room_no) REFERENCES room(room_no) ); -- 预约关系订单号和客房号是多对多 CREATE TABLE reservation ( order_id INT, room_no VARCHAR(10), start_time DATETIME, -- 始定时间 end_time DATETIME, -- 结束时间 PRIMARY KEY (order_id, room_no), FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (room_no) REFERENCES room(room_no) );逻辑说明accommodation表用三元主键顾客号、房间号、住宿时间保证同一顾客同一房间同一时间只有一条记录。reservation表用订单号和客房号做联合主键始定时间和结束时间作为普通属性。这两个表都是 n:m 联系直接转换的结果没有冗余字段。参数说明DATETIME对应数据字典里“格式/”的时间描述实际建表时用数据库原生时间类型比存字符串更利于查询和比较。外键约束保证引用完整性删除顾客时如果还有住宿记录数据库会阻止删除这对应文档里“级联删除”的需求——你可以在外键上加ON DELETE CASCADE来实现自动清理。3.4 用户子模式与水平分解文档还设计了三个用户子模式经理子系统的职工关系只保留职工号、姓名、级别、部门号、职务、部门经理、实际工资住宿子系统的客房关系只保留客房号、位置、设备、收费标准、管理人员号、状态经营管理子系统的顾客关系只保留顾客编号、住宿号、姓名、级别、应收款、使用时间、备注。这是典型的按角色裁剪视图让不同岗位的人只看到自己关心的字段。水平分解部分把职工关系按部门拆成负责人员、服务人员、经手人员三个关系理由是“公司内人员查询时一般只用到自己所属单位的信息”。这个做法在数据量大时能提升查询效率但代价是跨部门统计时要 UNION 三个表。文档没有展开这个代价你在实际项目里要权衡。4. 物理结构设计与避坑磁盘分配、系统配置和五个血泪教训4.1 存储结构设计的实际考量文档在物理设计阶段做了两件事确定数据库存放位置和确定系统配置。存放位置方面把经常存取的部分和存取频率较低的部分分别放在两个磁盘上。经常存取的部分包括职工、工资、客房、款项、折扣规则、项目、顾客存取频率较低的部分包括部门、账单、订单、总账、财务状况。备份数据和日志文件保存在磁带中。系统配置方面文档选了 Windows 9x 作为微机操作系统理由是界面好、能发挥硬件作用、适合酒店机构并且强调硬件和数据库要能逐步扩展。注意文档里的 Windows 9x 和磁带备份是那个年代的产物你复现时不必照搬。核心思路是冷热数据分离和备份介质独立放到今天就是把热表放 SSD、冷表放 HDD、备份走对象存储或独立磁盘。4.2 五个常见翻车点现象一E-R 图集成后出现同名异义字段。原因不同子系统里都叫“编号”但一个指账单编号、一个指项目编号。解决在分 E-R 图阶段就给每个实体的主键起带前缀的名字比如bill_id、project_id别偷懒用id。现象二n:m 联系转关系时漏掉联系属性。原因只建了双方主键的联合表忘了“始定时间”“结束时间”“发生时间”这些属于联系本身的属性。解决转换前把 E-R 图上联系旁边的属性全部列出来一个不落地放进关系模式。现象三范式判定时把该保留的冗余删了。原因看到“净利润可由收入减支出算出”就删掉结果每次查询都要现算性能崩了。解决像文档那样区分——不常查询的冗余可以删查询频繁的冗余要保留用空间换时间。现象四外键循环依赖导致建表失败。原因部门表引用职工表的经理号职工表又引用部门表的部门号先建哪个都报错。解决先建不带外键的表插入数据后再用ALTER TABLE加外键或者把其中一个外键设为可空先插部门再插职工。现象五水平分解后跨部门查询变复杂。原因把职工表拆成三张按部门分的表统计全酒店人数时要 UNION。解决如果跨部门统计是高频操作就别水平分解改用分区表或索引优化水平分解只适合“各查各的”场景。5. 从文档到可运行库验证设计与几个进阶技巧把文档里的关系模式真正跑起来最直接的办法是写一个初始化脚本建表、插测试数据、跑几条查询验证约束是否生效。我一般会先建一个hotel_db库按依赖顺序建表先建department和employee时先不加外键插完基础数据再用ALTER TABLE补上。测试数据至少覆盖边界年龄插 17 和 101 应该被 CHECK 拦下性别插“未知”应该被 ENUM 拦下删除还有住宿记录的顾客应该被外键拦下。这几条跑通说明你的约束设计是有效的。进阶用法上文档里的“财务状况”表保留了净利润冗余你可以进一步用触发器或物化视图来自动维护这个冗余字段。比如在账单表上建一个 AFTER INSERT 触发器每次插入收支记录就更新对应财务状况的总收入和总支出净利润随之重算。这样既保留了查询效率又不会让冗余数据变脏。-- 用触发器维护财务状况的汇总字段 DELIMITER // CREATE TRIGGER trg_update_finance AFTER INSERT ON bill FOR EACH ROW BEGIN UPDATE finance_status SET total_income total_income NEW.income_amount, total_expense total_expense NEW.expense_amount, net_profit total_income - total_expense WHERE finance_id NEW.finance_id; END // DELIMITER ;逻辑说明bill表每插入一条收支记录触发器就更新finance_status表里对应财务状况的总收入、总支出和净利润。这样查询财务状况时直接读汇总字段不用每次扫全表。参数说明NEW.income_amount和NEW.expense_amount是账单表里的收入数和支出数NEW.finance_id关联到财务状况表。注意触发器的更新顺序——先更新收入和支出再算净利润否则净利润用的是旧值。验证设计是否合理还有一个笨办法但很有效把文档里的数据流图拿出来每条数据流都问一句“这条流对应的表能不能支持这个查询”。比如“住房单价”从住房信息流向住宿管理部门收入你就查room表的price字段能不能按房间类型聚合出收入。如果查不出来说明关系模式漏了字段或者联系转错了。从那以后我每次做完逻辑设计都会强制走一遍“数据流反查”——拿数据流图逐条对关系模式对不上的地方就是坑。希望帮到你。本文还有配套的精品资源点击获取