工厂管理系统课程设计：数据库从E-R图到触发器的实战解析

简介一份《数据库系统课程设计报告-工厂管理系统》是基于MySQL的数据库课程设计完整文档面向正在学习数据库原理、需要完成课程设计或毕业设计的学生也适合需要参考完整设计流程的数据库爱好者。内容以工厂管理系统为业务场景系统展示了从需求分析、数据建模到前台软件开发的完整链路能够帮助读者理解如何将现实业务转化为关系模型并落地实现。资源包内为1个docx文件大小仅781KB方便直接下载、打开阅读或按需修改。该文档目前已有65人浏览学习作为同类数据库设计的参考具有一定热度。报告涵盖系统需求分析、数据字典、实体属性与联系分析、概念与逻辑模型设计、物理实现含表设计、完整性约束、视图、索引、存储过程及触发器、功能调试以及前台软件设计等章节结构完整、层次清晰既可作为课程设计报告的撰写范例也能为MySQL建库建模和工厂管理系统开发提供直接参考。1. 工厂管理系统课程设计一份能照着从零复现的数据库项目数据库系统课程设计里工厂管理系统是出镜率极高的题目——车间、工人、产品、零件、仓库这五类实体一架E-R图一画两张多对多中间表一建麻雀虽小五脏俱全。这份报告的完整度比多数只交建表语句的版本高出不少需求分析、数据字典、CDM/PDM建模、建表脚本、视图索引存储过程触发器、前台页面样例整条链路都走通了。适合正在做课程设计要交报告和演示系统的学生按步骤复现也适合已经把表建好但外键、存储过程、触发器用得比较模糊的人拿它当一个综合复习样本。2. 需求分析与数据字典六个实体、三张多对多关系表是怎么梳理出来的2.1 业务规则先拆成“几对几”报告第一章的需求概述写得很精炼四条规则基本决定了全部表结构一个厂里有多个车间每个车间有车间号、车间主任姓名、地址和电话一个车间有多个工人每个工有职工号、姓名、年龄、性别和工种一个车间生产多种产品产品有产品号和价格一个车间生产多种零件一个零件也可能由多个车间制造零件有零件号、重量和价格一个产品由多种零件组成一种零件也可装配出多种产品产品与零件均存放在仓库中厂内有多个仓库仓库有仓库号、仓库主任姓名和电话。这里拆出三组关系工厂-车间是一对多车间-工人是一对多这两组用外键表达即可。车间-零件是多对多产品-零件是多对多这两组不能靠外键直接表达必须建中间表。判断准则就是“基数”只要关系两端都可能出现多条记录就必须有连接表。很多同学把“产品由零件组成”做成product表里加一个part字段数据一多就冗余到没法查这是最常见的设计翻车点。2.2 数据字典怎么读数据项、数据结构、数据流三层对起来看这份报告里数据字典按三层组织。数据项是每个字段的定义包括类型、长度、取值范围数据结构是由相关数据项组合成的表数据流描述数据从哪里来到哪里去。数据项这一层能直接挖出很多文档与实现的偏差。比如工厂表的数据项“厂名”定义是char(20)而物理实现里fno是varchar(4)字段语义对不上。再比如车间号取值范围0001-9999实现里却是char(10)。这类细节在答辩时最容易成为追问点我的习惯是把数据字典和建表脚本逐字段对齐一遍宁可改文档也不改设计。数据结构这一层报告里列出的表比后续建表脚本多两张产品-仓库表和零件-仓库表。数据字典既然写了物理实现就应该有对应的外键或中间表否则文档前后矛盾。课程设计评分时老师一般不看代码跑得多炫而是先翻需求和数据字典再翻建表脚本两处对不上就是硬伤。数据流这一层简单产品信息从车间流向仓库零件信息从车间流向仓库意味着产品、零件入库后要能被查询和统计。报告里的数据流画得比较简单但已经能说明“谁产生数据、谁消费数据”。数据库系统概论里E-R图例题也常拿这种“工厂-车间-零件”说事核心逻辑是一致的先定实体再定联系最后定基数。2.3 实体与联系六张实体表加两张中间表经过需求分析进入概念结构设计时识别出的实体有六个工厂、车间、工人、仓库、零件、产品。E-R图中各实体的属性也基本对齐需求比如车间有车间号、车间主任、地址、电话工有职工号、姓名、年龄、性别、工种、车间号零件有零件号、重量、价格、仓库号。联系的分析重点在基数判断上。车间与零件的“生产”联系是多对多产品和零件的“装配”联系是多对多因此要拆成独立的联系表。工厂与仓库也是“厂内多个仓库”仓库表里放工厂外键。到这里十张表的基本雏形就出来了。建议在看这类报告时自己画一遍E-R图把每个联系的基数标出来再对照表结构基本就能判断设计是否通顺。3. 逻辑结构与物理建表十张表的设计顺序与外键约束细节3.1 概念模型转逻辑模型的三条转换规则E-R图转化为关系模型有一组固定的转换规则一对一关系中可以把任一方的主键放进另一方作为外键也可单独建表一对多关系中把“一”方的主键放进“多”方作为外键多对多关系中单独建立一张中间表中间表的主键由两张父表的主键联合构成或另行增加一个自增主键。这份报告最终落成十张表工厂表、车间表、工人表、产品表、零件表、仓库表以及车间-零件、产品-零件两张中间表。这里要注意数据字典里的产品-仓库、零件-仓库两张表在逻辑模型里被并掉了——改为直接在产品表、零件表里加sno外键。这个手段不算错但文档层没同步后面就会出问题。概念模型分析对应PowerDesigner里的CDM图逻辑模型对应PDM图。实际做的时候CDM画好实体和联系后用PowerDesigner的Generate Physical Data Model功能可以直接转成PDM再Generate Database生成建表SQL比手写SQL省事得多。不过自动生成的SQL通常带着PowerDesigner自己的命名前缀需要手工整理一遍这份报告里的脚本应该就是整理过的版本。3.2 建表顺序父表在前子表在后把报告里的建表脚本整理之后核心部分如下。这里我把格式规整了一下逻辑保持一致create table factory( fno varchar(10) NOT NULL primary key, fdname varchar(10) ); create table workshop( workshop_no char(10) NOT NULL primary key, wdname varchar(10), waddress varchar(10), wtel varchar(20), fno varchar(10) NOT NULL, foreign key (fno) references factory(fno) ); create table workman( wno char(10) NOT NULL primary key, wname varchar(10), wage int, wtype varchar(10), workshop_no char(10) NOT NULL, foreign key (workshop_no) references workshop(workshop_no) ); create table storage( sno char(10) NOT NULL primary key, sdname varchar(10), stel varchar(20), fno varchar(10) NOT NULL, foreign key (fno) references factory(fno) ); create table product( pno varchar(10) NOT NULL primary key, pname varchar(10), pprice float, workshop_no char(10) NOT NULL, sno char(10) NOT NULL, foreign key (workshop_no) references workshop(workshop_no), foreign key (sno) references storage(sno) ); create table element( eno char(10) NOT NULL primary key, eweight float, eprice float, sno char(10) NOT NULL, foreign key (sno) references storage(sno) ); create table workshop_element( workshop_no char(10) NOT NULL, eno char(10) NOT NULL, primary key (workshop_no, eno), foreign key (workshop_no) references workshop(workshop_no), foreign key (eno) references element(eno) ); create table product_element( pno varchar(10) NOT NULL, eno char(10) NOT NULL, primary key (pno, eno), foreign key (pno) references product(pno), foreign key (eno) references element(eno) );脚本含义不复杂factory是顶层父表workshop通过fno外键关联工厂workman通过workshop_no关联车间storage通过fno关联工厂product和element分别通过workshop_no、sno关联车间和仓库两张中间表使用联合主键表达多对多关系。这里有个非常容易踩的坑原报告把storage表的创建放在最后而product、element都引用了storage(sno)也就是说product和element创建时storage根本不存在在SQL Server里直接执行会报“对象名storage无效”。我在复现时把storage提前到workshop之后、product之前执行顺序调整后整段脚本一次成功。字段类型上要注意父表与子表外键的类型和长度必须一致。factory.fno在工厂表里是varchar(10)workshop.fno也是varchar(10)join时才能命中索引。如果父表用char(10)、子表用varchar(10)SQL Server在比较时可能做隐式转换索引失效数据量上来后查询明显变慢。另一个细节是price、weight用float如果系统后面要接财务float不适合存钱decimal(10,2)才是标准做法——这点答辩时主动说出来反而加分。3.3 多对多中间表联合主键还是自增id车间-零件表用的是联合主键(workshop_no, eno)产品-零件表用的是(pno, eno)。联合主键的好处是天然防止重复的对应关系比如Ae10插两次会被数据库拒绝不需要额外写unique约束。需要注意如果将来业务需要记录“这个车间从什么时候开始加工这个零件”联合主键就不再合适因为时间字段会被要求参与唯一性判断。那时就建议改成自增id 业务唯一索引。课程设计里用联合主键完全够还能少写一个字段。产品-零件表的pno使用了varchar(10)而不是char(10)和product表保持一致没问题但与element表的char(10)做关联时存在定长和变长混用的隐式转换风险。建议全表编号类字段统一用varchar别一会儿char一会儿varchar省得后面排查慢查询时怀疑人生。4. 视图、索引、存储过程与触发器功能点落地与代码解析4.1 视图把常用查询封装成虚拟表报告里建了两个视图最常用的是IS_productcreate view IS_product as select sno, pname, pprice from product;这个视图取出产品名称和价格同时保留仓库外键sno前端做报表时直接select * from IS_product避免每页重复写字段清单。第二个视图IS_workman使用了select *这种做法在课程设计里能过但在实际系统中不推荐——一旦表结构增加字段视图结果集会跟着变化前端绑定字段就可能出错。更稳妥的写法是显式列出需要的列。视图本身不存储数据它只是一段被命名的查询语句。执行时数据库会把视图展开为对应的select所以视图不会因为“加了视图”就变快它解决的是语义封装问题底层表结构变了只要视图字段不变上层应用就不用改。4.2 索引主键索引与unique index的重复建立报告里给product和element建了两条索引create unique index propno on product(pno); create unique index eeno on element(eno);这里pno和eno已经是主键。主键在建表时已经自动创建了唯一索引SQL Server中默认是聚集索引再单独建unique index属于重复建立。这类代码在答辩时如果老师提问解释不清会比较尴尬。一个更合理的做法是给业务上需要唯一约束但本身不是主键的字段建唯一索引比如仓库表里的电话stel或者给经常做范围查询的价格字段建普通索引create index idx_element_eprice on element(eprice);这样查“重量小于80.4的零件”或“价格低于某个阈值的零件”时如果表数据量够大才有实际意义。课程设计里数据量就那么几十行索引效果看不出来但索引加在什么列上、为什么这么加要说得出理由。4.3 存储过程多个表一次更新报告中的manage存储过程演示了一个入口维护多张表的流程。原文代码贴出来后我把格式和begin...end补齐逻辑保持一致create procedure manage eno char(10), wname varchar(10), sdname char(10), sno char(10) as begin declare eweight1 float; set eweight1 80.4; if exists (select eweight from element where eweight eweight1) begin update element set eweight 80.4; end select * from element; select eprice from element where eno eno; update workman set wage 10 where wname wname; select * from workman; update storage set sdname sdname where sno sno; select * from storage; print 修改后的零件表; end;参数说明eno是零件号用于查询零件价格wname是工人姓名用于把该工人的wage字段改成10sdname是新的仓库主任姓名sno是仓库号用于定位要更新的仓库记录。过程体先判断全表零件重量是否低于80.4只要有就统一更新为80.4。这个存储过程现实中不会这么写——把员工工资改成10是写死的没有业务语义。但作为课程设计演示它展示了“一个事务里多条SQL集中管理”的形态。我在交类似作业时会给过程体加上事务包裹BEGIN TRANSACTION加COMMIT中途失败就ROLLBACK这样才算有“生产系统”的样子。过程体内还有一点要留意declare了eweight1却把更新值写死于UPDATE语句中其实可以直接用变量替换update element set eweight eweight1这样阈值改起来不用动存储过程结构。4.4 触发器价格变更记录与删除信息留存报告中的触发器针对product表且定义了更新和删除两个事件create trigger tri_update_delete_product on product for update, delete as if UPDATE(pprice) begin select inserted.pno, deleted.pprice as 原价格, inserted.pprice as 新价格 from deleted, inesrtd where deleted.pno inserted.pno end else if columns_updated() 0 begin select deleted.pno as 被删除的产品号, deleted.pname, deleted.pprice as 原价格 from deleted end这段代码有两个问题。第一inserted在表连接处写成了inesrtd是拼写错误实际执行会报“无效的对象名inesrtd”。第二delete事件触发时inserted表是空的从deleted和inserted做等值连接不会查出任何记录所以delete分支实际上拿不到数据。想要在delete时记录被删信息应该单独写for delete触发器或者使用if not exists(select 1 from inserted)来判断当前是删除操作。如果要给这段代码做点改造我会把update和delete拆成两个触发器update里做价格审计delete里把被删记录插入日志表。下面是一个标准的update价格审计触发器create trigger tri_product_price_audit on product after update as begin if UPDATE(pprice) begin insert into product_price_log(pno, old_price, new_price, update_time) select inserted.pno, deleted.pprice, inserted.pprice, getdate() from deleted inner join inserted on deleted.pno inserted.pno; end end;这里inserted和deleted是SQL Server自动维护的两个虚拟表inserted保存更新后的新值deleted保存更新前的旧值。做审计触发器时inner join两者取同一条记录的新旧价格再写入日志表。注意UPDATE(pprice)的判定只要这条update语句涉及pprice列无论值有没有真的变化都会进入分支。如果只想记录价格真正变化的行要加where deleted.pprice inserted.pprice的判断。5. 常见问题与避坑SQL Server 移植 MySQL 时的五处翻车记录5.1 坑一建表顺序导致的外键失败现象按原报告顺序执行建表脚本创建product表时报错“对象名storage无效”。原因product表里有foreign key (sno) references storage(sno)但storage表在脚本末尾才创建SQL Server要求外键引用的父表必须先存在。解决调整建表顺序。我把完整的执行顺序固定为factory、storage、workshop、workman、product、element、workshop_element、product_element。先建仓库再建产品和零件就是因为两个子表都引用了sno。以后拿到任何别人的建表脚本第一件事就是按“父表在前、子表在后”重排一遍别直接执行。5.2 坑二触发器里inserted写成inesrtd现象触发器执行update语句时报“无效的对象名inesrtd”。原因报告原文把inserted拼成了inesrtd。这类错误在复制粘贴的代码里极其常见尤其是触发器这class的代码网上模板多下载下来也不一定有人从头到尾跑过。解决把inesrtd改成inserted。判断关键字是否写对的快速办法在SQL Server Management Studio里选中疑似拼写快捷键AltF1可以查看对象定义如果解析不出来基本就是拼错了。5.3 坑三存储过程后面直接跟exec批次没分开现象在查询窗口里一次性执行create procedure和后面的exec manage...报错“manage 附近有语法错误”。原因SQL Server中create procedure必须是批处理的第一条语句后面的exec与它处于同一批次时无法识别。解决在create procedure和exec之间加一行GO让它们分属不同批次。批量执行课设脚本时存储过程、触发器都要用GO隔开。MySQL没有GO的概念用delimiter把整个过程体包起来即可。5.4 坑四数据字典与建表脚本不一致现象数据字典里有产品-仓库表、零件-仓库表建表脚本里却没有对应表同时product表和element表直接以sno外键关联仓库。原因设计阶段把“产品与零件均存入仓库”理解成了两张独立的关联表物理实现时又改成了外键直连文档和代码没同步。解决以建表脚本为准删除产品-仓库、零件-仓库的冗余描述同时在文档里注明“产品与零件通过仓库外键存储于仓库中”。反过来也可以保留两张中间表去掉product和element里的sno字段。我更倾向保留sno外键逻辑简单一个零件只属于一个仓库不需要引入多余表。5.5 坑五SQL Server写法搬到MySQL要改的关键语法现象把store procedure原样贴进MySQL报语法错误。原因SQL Server和MySQL存储过程语法差异明显。SQL Server用作参数前缀MySQL用in/out修饰参数SQL Server直接create procedureMySQL要先delimiter指定分隔符函数方面getdate()在MySQL里是now()。解决MySQL版本的manage存储过程写法如下delimiter $$ create procedure manage( in p_eno char(10), in p_wname varchar(10), in p_sdname varchar(10), in p_sno char(10) ) begin declare eweight1 float default 80.4; update element set eweight 80.4 where eweight eweight1; select eprice from element where eno p_eno; update workman set wage 10 where wname p_wname; update storage set sdname p_sdname where sno p_sno; select * from element; end$$ delimiter ;参数说明p_eno、p_wname、p_sdname、p_sno对应原SQL Server版本的四个入参。调用时用call manage(e03, 贺江平, 张攀攀, B2)不是exec。声明变量时var声明必须放在begin的最前部MySQL不允许在中间插入declare。触发器中SQL Server的getdate()要换成now()inserted/deleted两个虚拟表在MySQL里叫NEW/OLD这是一套完全不同的语法。6. 验证数据库功能的三步检查把整条链路跑通拿到这份报告别急着写文档先把数据库真正跑起来。我的验证习惯分三步。第一步核对表与外键。SQL Server里查系统视图select fk.name as 外键名, tp.name as 子表, ref.name as 父表 from sys.foreign_keys fk inner join sys.tables tp on fk.parent_object_id tp.object_id inner join sys.tables ref on fk.referenced_object_id ref.object_id order by ref.name, tp.name;MySQL用information_schemaselect table_name, column_name, referenced_table_name from information_schema.key_column_usage where referenced_table_name is not null order by referenced_table_name;跑出来的外键清单应该与十张表的逻辑设计一一对应workman→workshopworkshop→factoryproduct→workshop和storageelement→storage这两张中间表分别对应父表主键。如果外键缺失或指向错误问题几乎都出在建表顺序或字段类型不一致上。第二步造数后走存储过程。插入几条worker、element、storage数据调用manage过程观察结果集变化所有低于80.4的零件重量被更新指定工人工资变成10目标仓库主任姓名被替换。我一般会先把process里的更新语句加上事务包裹再测试确保中途失败能rollback这也算是在课设里主动展示“事务意识”。第三步验证触发器。先向product插入一条记录再执行update product set pprice 99 where pno p1然后select产品价格日志表应该能看到老价格和新价格两条记录。如果发现日志表为空先检查触发器是否创建成功再检查inserted/deleted的join条件是否写了等值关联。从那以后我每次拿到别人的数据库课程设计都会强制走一遍外键核对、存储过程调用、触发器生效验证这三步有问题的代码很快就能暴露希望帮到你。本文还有配套的精品资源点击获取