DB2花店管理系统数据库课程设计：从E-R图到触发器完整实现

简介这份资源是大连交通大学数据库课程设计的完整文档面向正在学习数据库原理、需要完成课程设计或毕业设计前期训练的高校学生。内容以花店管理系统为案例完整覆盖系统调研、需求分析、概念结构设计、逻辑结构设计、物理设计、系统调试与维护、系统评价等数据库设计全流程并涉及IBM DB2与SQL语言的实际应用。压缩包内共1个doc文档约630KB包含摘要、目录及需求分析、E-R图转换、索引与表空间建立、触发器设计等章节可直接作为课程设计报告参考模板。目前已有261人学习下载。读者可借此理清数据库设计的阶段划分与文档组织方式掌握数据字典、流程图、局部视图集成、关系模式转换等关键方法并对照花店管理系统的建表、查询、修改、删除等操作为独立完成同类课程设计提供可复用的思路与结构参考。1. 花店管理系统数据库设计一份能直接跑通的 DB2 课程设计文档如果你正在搜「数据库课程设计 花店管理系统」大概率是两种情况要么下周就要交一份带 E-R 图、关系模式、建表 SQL 和触发器的完整文档要么你手里已经有一份模板但跑不起来想找一份真正能在 DB2 里落地的参考。这份资源就是大连交通大学的一份数据库课程设计文档核心是用 IBM DB2 加 SQL 语言把花店从采购到销售的全流程做成一个关系数据库信息管理系统。它覆盖了需求分析、概念结构设计、逻辑结构设计、物理设计、数据库实施这五个标准阶段七个基本表、视图、索引、表空间、触发器都有涉及。适合数据库原理刚学完、需要一份完整流程参照的在校生也适合想快速回顾 DB2 建库建表套路的从业者。下面我按「文档里到底有什么 → 怎么照着建 → 哪里容易翻车」的顺序拆一遍。2. 需求分析与概念设计七个基本表和 E-R 图怎么落2.1 数据字典里的七个基本表文档在需求分析阶段把系统拆成了花市信息系统、花店信息系统、店员信息系统、鲜花信息系统四个子系统最终落到七个基本表。这个拆分逻辑是自顶向下的结构化分析方法先定全局框架再逐层细化。七个表的结构定义如下表名含义组成字段花市花市基本信息花市编号、花市名称、花市地址花店花店基本信息花店编号、花店名称、花店地址、花店电话花店采购信息采购关系花市编号、花店编号店员店员基本信息店员编号、店员姓名、工资、花店编号鲜花鲜花基本信息鲜花名称、价格、花语鲜花销售信息销售关系鲜花名称、花店编号、销售额会员信息会员管理文档正文提及但未展开字段这里有个设计上的取舍值得注意花店采购信息表和鲜花销售信息表本质上是多对多关系的桥接表它们的主键是两列组合。花店采购信息表用花市编号花店编号做主键鲜花销售信息表用花店编号鲜花名称做主键。这种设计在第三范式下是合理的因为采购关系和销售关系各自有独立于实体的属性——采购关系本身没有额外属性销售关系带了销售额。2.2 局部 E-R 图到全局 E-R 图的集成概念设计阶段用的是自底向上方法先画局部 E-R 图再集成。文档里画了花市、花店、店员、鲜花四个实体的属性图然后两两集成花店和花市通过「采购」联系花店和鲜花通过「销售」联系花店和店员通过「工作」联系。最终的系统总体 E-R 图里花店是核心实体分别与花市、鲜花、店员发生联系。采购联系是 m:n 的因为一个花店可以从多个花市采购一个花市也可以供货给多个花店。销售联系也是 m:n一个花店销售多种鲜花一种鲜花可以在多个花店销售销售额是销售联系的属性。工作联系是 1:n一个花店有多个店员一个店员只属于一个花店。提示集成 E-R 图时最容易出的问题是联系属性放错位置。销售额是「花店-鲜花」这个联系上的属性不是鲜花实体本身的属性因为同一种鲜花在不同花店的销售额不同。如果你把销售额画到鲜花实体上逻辑设计阶段就会出问题。2.3 从 E-R 图到关系模式的转换规则E-R 图转关系模式有固定的映射规则每个实体转一个关系实体的属性变成关系的属性实体的键变成关系的键。对于 1:n 联系把 1 端的键加到 n 端作为外键。对于 m:n 联系必须单独建一个关系键是两端实体键的组合。按这个规则文档得到的关系模式是花市花市编号花市名称花市地址花店花店编号花店名称花店地址花店电话花店采购信息表花市编号花店编号店员店员编号店员姓名工资花店编号鲜花鲜花名称价格花语鲜花销售信息表鲜花名称花店编号销售额店员表里的花店编号是外键指向花店表。花店采购信息表的两个字段分别是花市表和花店表的外键。鲜花销售信息表的两个字段分别是鲜花表和花店表的外键。2.4 数据依赖分析与第三范式验证逻辑设计的优化阶段做了函数依赖分析。花市表里花市编号决定花市名称和花市地址花店表里花店编号决定花店名称、地址、电话店员表里店员编号决定姓名、工资和所属花店编号鲜花表里鲜花名称决定价格和花语。鲜花销售表里鲜花名称花店编号联合决定销售额。检查第三范式的要求是每个非主属性都完全函数依赖于候选键且不存在传递依赖。花店表里花店编号直接决定地址和电话没有传递依赖。店员表里店员编号决定花店编号花店编号又决定花店名称但花店名称不在店员表里所以没有传递依赖问题。鲜花销售表里销售额完全依赖于鲜花名称花店编号这个组合键不存在部分依赖。最终分解结果满足第三范式。3. 物理设计与建库实施DB2 表空间、索引和建表 SQL3.1 表空间创建目录容器与文件容器的选择文档在物理设计阶段建了六个表空间其中 dms02 到 dms06 是数据库管理的DMSsms01 是系统管理的SMS。DMS 表空间用文件容器SMS 表空间用目录容器。这个选择不是随便定的DMS 适合存放数据量大、需要精细控制存储位置的表SMS 适合存放临时数据或小表。-- 连接到目标数据库 connect to ag02wmn; -- 创建 DMS 表空间使用文件容器 create regular tablespace dms02 managed by database using (file d:\dms\dms02 14) extentsize 2; -- 创建长字段表空间 create long tablespace dms03 managed by database using (file d:\dms\dms03 728) extentsize 8; -- 创建 SMS 表空间使用目录容器 create regular tablespace sms01 managed by system using (d:\sms\sms01,d:\sms\sms02) extentsize 4;managed by database表示由数据库管理器管理空间分配managed by system表示由操作系统管理。extentsize是区段大小单位是页决定了每次扩展分配多少页。文件路径后面的数字是文件页数比如14表示这个容器有 14 页。SMS 表空间的目录容器不需要指定页数由系统自动管理。注意DMS 表空间的文件容器路径必须提前创建好目录DB2 不会自动建目录。如果路径不存在建表空间会直接报 SQL0290N。SMS 表空间的目录容器也一样目录必须存在。3.2 索引建立与 PCTFREE 参数文档给花市表和店员表各建了一个索引用的是花市名称和店员姓名做索引键。索引建在非主键列上目的是加速按名称查询的场景。-- 在花市表的名称列上建索引 CREATE INDEX USER.花市索引 ON USER.花市 (花市名称 ASC) PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COLLECT SAMPLED DETAILED STATISTICS; CONNECT RESET; -- 在店员表的姓名列上建索引 CREATE INDEX USER.店员索引 ON USER.店员 (店员姓名 ASC) PCTFREE 10 MINPCTUSED 10 ALLOW REVERSE SCANS PAGE SPLIT SYMMETRIC COLLECT SAMPLED DETAILED STATISTICS; CONNECT RESET;PCTFREE 10表示每个索引页留 10% 的空闲空间用于后续插入新键值时避免页分裂。MINPCTUSED 10是页合并的阈值当页的使用率低于 10% 时会触发合并。ALLOW REVERSE SCANS允许反向扫描索引对ORDER BY ... DESC有用。PAGE SPLIT SYMMETRIC是页分裂策略对称分裂比默认的中间分裂更适合随机插入场景。COLLECT SAMPLED DETAILED STATISTICS开启详细统计信息收集采样方式收集对查询优化器友好。3.3 建表 SQL 与约束设计文档要求至少建六张表每张表有主键必要的外键至少一个 Check 约束至少一个视图。按文档给出的表结构建表 SQL 如下-- 花市表 CREATE TABLE 花市 ( 花市编号 CHAR(10) NOT NULL PRIMARY KEY, 花市名称 VARCHAR(20) NOT NULL, 花市地址 VARCHAR(50) NOT NULL ); -- 花店表 CREATE TABLE 花店 ( 花店编号 CHAR(10) NOT NULL PRIMARY KEY, 花店名称 VARCHAR(20) NOT NULL, 花店电话 VARCHAR(20) NOT NULL, 花店地址 VARCHAR(50) NOT NULL ); -- 店员表带外键和 Check 约束 CREATE TABLE 店员 ( 店员编号 CHAR(10) NOT NULL PRIMARY KEY, 店员姓名 VARCHAR(20) NOT NULL, 工资 DECIMAL(10,2) NOT NULL CHECK (工资 0), 花店编号 CHAR(10) NOT NULL, FOREIGN KEY (花店编号) REFERENCES 花店(花店编号) ); -- 鲜花表 CREATE TABLE 鲜花 ( 鲜花名称 VARCHAR(20) NOT NULL PRIMARY KEY, 价格 DECIMAL(10,2) NOT NULL, 花语 VARCHAR(20) NOT NULL ); -- 花店采购信息表复合主键 CREATE TABLE 花店采购信息 ( 花市编号 CHAR(10) NOT NULL, 花店编号 CHAR(10) NOT NULL, PRIMARY KEY (花市编号, 花店编号), FOREIGN KEY (花市编号) REFERENCES 花市(花市编号), FOREIGN KEY (花店编号) REFERENCES 花店(花店编号) ); -- 鲜花销售信息表复合主键 CREATE TABLE 鲜花销售信息 ( 花店编号 CHAR(10) NOT NULL, 鲜花名称 VARCHAR(20) NOT NULL, 销售额 DECIMAL(10,2) NOT NULL, PRIMARY KEY (花店编号, 鲜花名称), FOREIGN KEY (花店编号) REFERENCES 花店(花店编号), FOREIGN KEY (鲜花名称) REFERENCES 鲜花(鲜花名称) );CHECK (工资 0)是文档要求的 Check 约束防止录入负工资。外键约束保证了引用完整性店员表的花店编号必须在花店表里存在采购信息表的两个编号必须分别在花市表和花店表里存在。复合主键用PRIMARY KEY (列1, 列2)的写法DB2 会自动为复合主键建唯一索引。3.4 视图创建与权限分配文档要求为不同用户建立不同视图来实现权限控制。建三个用户 user1、user2、user3user1 和 db2admin 一起成为 admin 组成员拥有 SYSADM 权限user2 拥有 DBADM 权限user3 只被授予某张表上的所有特权。-- 创建视图只暴露鲜花名称和价格隐藏花语 CREATE VIEW 鲜花公开信息 AS SELECT 鲜花名称, 价格 FROM 鲜花; -- 授予 user3 对视图的查询权限 GRANT SELECT ON 鲜花公开信息 TO USER user3; -- 授予 user3 对花店表的全部特权 GRANT ALL PRIVILEGES ON TABLE 花店 TO USER user3;视图的作用是把敏感字段藏起来。花语字段可能涉及商业信息公开视图里就不放。GRANT ALL PRIVILEGES把增删改查权限一次性给出去适合需要完整操作权限的场景。如果只想给查询权限用GRANT SELECT就够了。4. 触发器设计与数据库运行避坑与排查4.1 触发器设计库存联动更新文档要求设计一个触发器。在花店管理系统里最合理的触发器场景是当鲜花销售信息表插入一条销售记录时自动更新对应鲜花的某个统计字段。但文档里的鲜花表没有库存字段所以更实际的触发器是记录销售日志或者校验销售额不能为负。-- 创建销售日志表 CREATE TABLE 销售日志 ( 日志编号 INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY, 花店编号 CHAR(10), 鲜花名称 VARCHAR(20), 销售额 DECIMAL(10,2), 操作时间 TIMESTAMP DEFAULT CURRENT TIMESTAMP, PRIMARY KEY (日志编号) ); -- 创建触发器插入销售记录时自动写日志 CREATE TRIGGER 销售记录触发器 AFTER INSERT ON 鲜花销售信息 REFERENCING NEW AS NEW_ROW FOR EACH ROW MODE DB2SQL BEGIN ATOMIC INSERT INTO 销售日志 (花店编号, 鲜花名称, 销售额) VALUES (NEW_ROW.花店编号, NEW_ROW.鲜花名称, NEW_ROW.销售额); END;AFTER INSERT表示在插入操作完成后触发。REFERENCING NEW AS NEW_ROW给新插入的行起别名方便在触发器体里引用。FOR EACH ROW表示行级触发器每插入一行触发一次。MODE DB2SQL是 DB2 的触发器模式。BEGIN ATOMIC到END之间是触发器体里面可以写多条 SQL 语句。4.2 常见问题排查五个血泪踩坑记录现象一建表时报 SQL0601N 表已存在。原因是你之前已经建过同名表或者建表脚本重复执行了。解决方法是先执行DROP TABLE 表名再建或者用CREATE TABLE IF NOT EXISTSDB2 部分版本支持。更稳妥的做法是在脚本开头统一加 DROP 语句按外键依赖的反序删除。现象二插入数据时报 SQL0530N 外键约束冲突。原因是你在子表里插入了一条父表里不存在的外键值。比如往店员表插入花店编号为 H001 的记录但花店表里没有 H001。解决方法是先插父表数据再插子表数据或者临时关闭外键检查不推荐。排查时用SELECT * FROM 花店 WHERE 花店编号 H001确认父表里到底有没有这条记录。现象三触发器创建时报 SQL0104N 语法错误。原因是 DB2 的触发器体必须用BEGIN ATOMIC ... END包裹而且语句之间要用分号分隔。如果你在命令行里直接执行分号会被当成语句结束符。解决方法是在触发器创建语句前后加--#SET TERMINATOR 改变语句终止符或者把触发器写进脚本文件用db2 -td -f执行。现象四表空间创建时报 SQL0290N 表空间无法访问。原因是文件容器或目录容器的路径不存在或者 DB2 实例用户没有该路径的写权限。解决方法是提前用mkdir建好目录Windows 下确认 DB2 服务账户对该目录有完全控制权限。另外注意路径分隔符Windows 用反斜杠Linux 用正斜杠。现象五查询时索引没生效全表扫描。原因是查询条件没有命中索引列或者统计信息过期导致优化器选错了执行计划。解决方法是先用EXPLAIN看执行计划确认是否走了索引。如果统计信息过期执行RUNSTATS ON TABLE 表名 AND INDEXES ALL重新收集。另外注意如果查询返回的行数超过表总行数的 20% 左右优化器可能主动选择全表扫描这是正常行为。4.3 数据库运行与结果验证建完表、载入数据、建好触发器和视图之后文档要求对每个表抓图验证。验证步骤是先SELECT * FROM 表名确认数据载入成功再执行几个典型查询确认索引和视图工作正常。-- 验证查询每个花店的鲜花销售总额 SELECT 花店编号, SUM(销售额) AS 总销售额 FROM 鲜花销售信息 GROUP BY 花店编号 ORDER BY 总销售额 DESC; -- 验证通过视图查询鲜花公开信息 SELECT * FROM 鲜花公开信息 WHERE 价格 50; -- 验证触发器插入一条销售记录后查日志表 INSERT INTO 鲜花销售信息 VALUES (H001, 红玫瑰, 200.00); SELECT * FROM 销售日志 WHERE 花店编号 H001;第一个查询验证分组聚合和排序。第二个查询验证视图是否正常返回数据。第三个查询验证触发器是否在插入后自动写了日志。如果日志表里没有新记录说明触发器没生效回去检查触发器的AFTER INSERT和REFERENCING NEW写法。5. 进阶技巧把课程设计文档变成可复用的 DB2 建库脚本5.1 脚本化执行与错误处理课程设计文档里的 SQL 是分散在各章节的实际执行时需要按依赖顺序串起来。我一般会整理成一个主脚本按「建库 → 建表空间 → 建表 → 建索引 → 建视图 → 建触发器 → 插数据 → 验证」的顺序排列。DB2 命令行执行脚本用db2 -tvf script.sql-t表示用分号做语句终止符-v表示回显每条命令-f指定脚本文件。如果脚本中途报错DB2 默认会继续执行后面的语句这会导致错误累积。更稳妥的做法是在脚本开头加UPDATE COMMAND OPTIONS USING STOP ON ERROR让 DB2 遇到错误就停下来。这样你能第一时间定位问题而不是等脚本跑完再从头排查。5.2 用系统表验证建库结果建完之后怎么确认所有对象都建对了查 DB2 的系统表是最快的方式。SYSCAT.TABLES里有所有表的信息SYSCAT.INDEXES里有索引SYSCAT.VIEWS里有视图SYSCAT.TRIGGERS里有触发器。-- 查看当前模式下所有表 SELECT TABNAME, TYPE FROM SYSCAT.TABLES WHERE TABSCHEMA USER AND TYPE T; -- 查看所有索引及其对应的表 SELECT INDNAME, TABNAME, UNIQUERULE FROM SYSCAT.INDEXES WHERE TABSCHEMA USER; -- 查看所有触发器 SELECT TRIGNAME, TABNAME, TRIGTIME, TRIGEVENT FROM SYSCAT.TRIGGERS WHERE TRIGSCHEMA USER;UNIQUERULE字段告诉你索引是否唯一U表示唯一索引D表示允许重复。TRIGTIME是触发时机BEFORE/AFTERTRIGEVENT是触发事件INSERT/UPDATE/DELETE。这三个查询跑一遍建库结果一目了然比逐个表去SELECT *高效得多。5.3 从课程设计到实际项目的差距这份文档作为课程设计是完整的但放到实际项目里还有几个明显差距。第一没有考虑并发控制多个用户同时插入销售记录时触发器可能产生锁等待。第二没有分区设计鲜花销售信息表数据量大了之后查询会变慢。第三没有备份恢复策略DB2 的BACKUP DATABASE和RESTORE DATABASE命令文档里完全没提。第四权限设计太粗GRANT ALL PRIVILEGES在实际环境里应该拆成 SELECT、INSERT、UPDATE、DELETE 分别授予。不过话说回来课程设计的目的是验证你理解了数据库设计的完整流程不是交付生产系统。这七个表、两个索引、一个触发器、一个视图的规模刚好够把需求分析到实施的全链路走一遍。我建议你在复现的时候把文档里的 SQL 全部手敲一遍而不是复制粘贴敲的过程中你会自然发现哪些字段类型选得不对、哪些约束漏了。从那以后我每次拿到类似的课程设计文档都会先跑一遍系统表查询确认对象齐全再逐条验证约束和触发器这个习惯帮我省了很多返工时间。希望帮到你。本文还有配套的精品资源点击获取