[大数据] 数据库课程设计——某工厂的物料管理系统（附Java源码与课程设计报告）

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 数据库课程设计——某工厂的物料管理系统（附Java源码与课程设计报告） -> 正文阅读

[大数据]数据库课程设计——某工厂的物料管理系统（附Java源码与课程设计报告）

如需要系统Java源码，请联系作者微信：xinglibao391
如需要系统Java源码，请联系作者微信：xinglibao391
如需要系统Java源码，请联系作者微信：xinglibao391

题目：

某工厂的物料管理系统
实现物料的分类管理;
实现部门门和员工信息管理;
实现物料的入库和领用管理;
实现物料的转仓管理;
创建触发器，实现物料入库和领用时相应物料库存的自动更新;
创建触发器，实现转仓时转入仓库物料增加、转出仓库物料减少
创建存储过程统计各仓库各种物料的现存数量;
创建存储过程统计指定时间段内各种物料的入库数量和领用数量;
建立数据库相关表之间的参照完整性约束;

系统功能分析：

在这里插入图片描述

系统界面展示：

在这里插入图片描述

数据字典：

在这里插入图片描述

ER图：

在这里插入图片描述

SQL代码：

/*
 Navicat Premium Data Transfer

 Source Server         : mysql57
 Source Server Type    : MySQL
 Source Server Version : 50735
 Source Host           : localhost:13306
 Source Schema         : jingzhe_fm

 Target Server Type    : MySQL
 Target Server Version : 50735
 File Encoding         : 65001

 Date: 05/06/2022 10:49:03
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for fm_instore
-- ----------------------------
DROP TABLE IF EXISTS `fm_instore`;
CREATE TABLE `fm_instore`  (
  `instore_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '入库编号',
  `staff_id` int(11) NOT NULL COMMENT '员工编号',
  `staff_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `material_id` int(11) NOT NULL COMMENT '物料编号',
  `material_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料名称',
  `instore_num` int(11) NOT NULL COMMENT '入库数量',
  `warehouse_id` int(11) NOT NULL COMMENT '仓库编号',
  `warehouse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库名称',
  `instore_date` date NULL DEFAULT NULL COMMENT '入库时间',
  PRIMARY KEY (`instore_id`) USING BTREE,
  INDEX `fk9`(`staff_id`) USING BTREE,
  INDEX `fk10`(`material_id`) USING BTREE,
  INDEX `fk11`(`warehouse_id`) USING BTREE,
  CONSTRAINT `fk10` FOREIGN KEY (`material_id`) REFERENCES `fm_material` (`material_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk11` FOREIGN KEY (`warehouse_id`) REFERENCES `fm_warehouse` (`warehouse_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk9` FOREIGN KEY (`staff_id`) REFERENCES `fm_staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of fm_instore
-- ----------------------------
INSERT INTO `fm_instore` VALUES (1, 1, '唐三藏', 2, '铁钉', 4200, 1, '一号仓库', '2022-06-05');
INSERT INTO `fm_instore` VALUES (2, 2, '孙悟空', 2, '铁钉', 3500, 3, '三号仓库', '2022-06-05');

-- ----------------------------
-- Table structure for fm_material
-- ----------------------------
DROP TABLE IF EXISTS `fm_material`;
CREATE TABLE `fm_material`  (
  `material_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '物料编号',
  `material_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料名称',
  `material_spec` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料规格',
  `material_description` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料描述',
  `material_clazz` int(11) NULL DEFAULT NULL COMMENT '物料类别',
  PRIMARY KEY (`material_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of fm_material
-- ----------------------------
INSERT INTO `fm_material` VALUES (1, '门锁', '把', '', 1);
INSERT INTO `fm_material` VALUES (2, '铁钉', '斤', '', 1);
INSERT INTO `fm_material` VALUES (3, '螺丝', '斤', '', 1);
INSERT INTO `fm_material` VALUES (4, '电线', '捆', '', 4);
INSERT INTO `fm_material` VALUES (5, '插座', '个', '', 4);

-- ----------------------------
-- Table structure for fm_matl_whse
-- ----------------------------
DROP TABLE IF EXISTS `fm_matl_whse`;
CREATE TABLE `fm_matl_whse`  (
  `material_id` int(11) NULL DEFAULT NULL COMMENT '物料编号',
  `material_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料名称',
  `warehouse_id` int(11) NULL DEFAULT NULL COMMENT '仓库编号',
  `warehouse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库名称',
  `material_num` int(11) NULL DEFAULT NULL COMMENT '物料数量',
  INDEX `fk7`(`material_id`) USING BTREE,
  INDEX `fk8`(`warehouse_id`) USING BTREE,
  CONSTRAINT `fk7` FOREIGN KEY (`material_id`) REFERENCES `fm_material` (`material_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk8` FOREIGN KEY (`warehouse_id`) REFERENCES `fm_warehouse` (`warehouse_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of fm_matl_whse
-- ----------------------------
INSERT INTO `fm_matl_whse` VALUES (1, '门锁', 1, '一号仓库', 2300);
INSERT INTO `fm_matl_whse` VALUES (2, '铁钉', 1, '一号仓库', 9000);
INSERT INTO `fm_matl_whse` VALUES (2, '铁钉', 3, '三号仓库', 6000);
INSERT INTO `fm_matl_whse` VALUES (3, '螺丝', 1, '一号仓库', 4000);
INSERT INTO `fm_matl_whse` VALUES (5, '插座', 2, '二号仓库', 3600);

-- ----------------------------
-- Table structure for fm_outstore
-- ----------------------------
DROP TABLE IF EXISTS `fm_outstore`;
CREATE TABLE `fm_outstore`  (
  `outstore_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '领用编号',
  `staff_id` int(11) NOT NULL COMMENT '员工编号',
  `staff_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `material_id` int(11) NOT NULL COMMENT '物料编号',
  `material_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料名称',
  `outstore_num` int(11) NOT NULL COMMENT '领用数量',
  `warehouse_id` int(11) NOT NULL COMMENT '仓库编号',
  `warehouse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库名称',
  `outstore_date` date NULL DEFAULT NULL COMMENT '出库时间',
  PRIMARY KEY (`outstore_id`) USING BTREE,
  INDEX `fk5`(`staff_id`) USING BTREE,
  INDEX `fk6`(`material_id`) USING BTREE,
  CONSTRAINT `fk5` FOREIGN KEY (`staff_id`) REFERENCES `fm_staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk6` FOREIGN KEY (`material_id`) REFERENCES `fm_material` (`material_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of fm_outstore
-- ----------------------------
INSERT INTO `fm_outstore` VALUES (1, 1, '唐三藏', 2, '铁钉', 200, 1, '一号仓库', '2022-06-05');

-- ----------------------------
-- Table structure for fm_section
-- ----------------------------
DROP TABLE IF EXISTS `fm_section`;
CREATE TABLE `fm_section`  (
  `section_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '部门编号',
  `section_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  `section_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门联系方式',
  `section_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门地址',
  PRIMARY KEY (`section_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of fm_section
-- ----------------------------
INSERT INTO `fm_section` VALUES (1, '人事部', '13921525889', '河北省邯郸市惊蛰机械厂办公楼305');
INSERT INTO `fm_section` VALUES (2, '财务部', '13842452526', '河北省邯郸市惊蛰机械厂办公楼203');
INSERT INTO `fm_section` VALUES (3, '仓库管理部', '13921533289', '河北省邯郸市惊蛰机械厂办公楼315');
INSERT INTO `fm_section` VALUES (4, '安全消防部', '18121511869', '河北省邯郸市惊蛰机械厂办公楼101');

-- ----------------------------
-- Table structure for fm_staff
-- ----------------------------
DROP TABLE IF EXISTS `fm_staff`;
CREATE TABLE `fm_staff`  (
  `staff_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '员工编号',
  `staff_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `staff_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工联系方式',
  `staff_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工家庭住址',
  PRIMARY KEY (`staff_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of fm_staff
-- ----------------------------
INSERT INTO `fm_staff` VALUES (1, '唐三藏', '18965201179', '江苏省菏泽市西天区华府庄园253号');
INSERT INTO `fm_staff` VALUES (2, '孙悟空', '19632501179', '江苏省菏泽市西天区华府庄园234号');
INSERT INTO `fm_staff` VALUES (3, '朱八戒', '13921365889', '江苏省菏泽市西天区华府庄园263号');
INSERT INTO `fm_staff` VALUES (4, '朱高煦', '13845626361', '山东省闽南市海州区苍梧路59号');
INSERT INTO `fm_staff` VALUES (5, '朱高燧', '17511401171', '山东省闽南市海州区苍梧路59号');

-- ----------------------------
-- Table structure for fm_staff_section
-- ----------------------------
DROP TABLE IF EXISTS `fm_staff_section`;
CREATE TABLE `fm_staff_section`  (
  `staff_id` int(11) NOT NULL COMMENT '员工编号',
  `staff_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `section_id` int(11) NOT NULL COMMENT '部门编号',
  `section_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '部门名称',
  INDEX `fk1`(`staff_id`) USING BTREE,
  INDEX `fk2`(`section_id`) USING BTREE,
  CONSTRAINT `fk1` FOREIGN KEY (`staff_id`) REFERENCES `fm_staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk2` FOREIGN KEY (`section_id`) REFERENCES `fm_section` (`section_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of fm_staff_section
-- ----------------------------
INSERT INTO `fm_staff_section` VALUES (1, '唐三藏', 1, '人事部');
INSERT INTO `fm_staff_section` VALUES (2, '孙悟空', 3, '仓库管理部');
INSERT INTO `fm_staff_section` VALUES (3, '朱八戒', 2, '财务部');
INSERT INTO `fm_staff_section` VALUES (4, '朱高煦', 4, '安全消防部');
INSERT INTO `fm_staff_section` VALUES (5, '朱高燧', 2, '财务部');

-- ----------------------------
-- Table structure for fm_transfer
-- ----------------------------
DROP TABLE IF EXISTS `fm_transfer`;
CREATE TABLE `fm_transfer`  (
  `transfer_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '转仓编号',
  `staff_id` int(11) NOT NULL COMMENT '员工编号',
  `staff_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '员工姓名',
  `material_id` int(11) NOT NULL COMMENT '物料编号',
  `material_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '物料名称',
  `transfer_num` int(11) NOT NULL COMMENT '转仓数量',
  `in_warehouse_id` int(11) NOT NULL COMMENT '入仓编号',
  `in_warehouse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '入仓名称',
  `out_warehouse_id` int(11) NOT NULL COMMENT '出仓编号',
  `out_warehouse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '出仓名称',
  `transfer_date` date NULL DEFAULT NULL COMMENT '转仓时间',
  PRIMARY KEY (`transfer_id`) USING BTREE,
  INDEX `fk3`(`staff_id`) USING BTREE,
  INDEX `fk4`(`material_id`) USING BTREE,
  CONSTRAINT `fk3` FOREIGN KEY (`staff_id`) REFERENCES `fm_staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  CONSTRAINT `fk4` FOREIGN KEY (`material_id`) REFERENCES `fm_material` (`material_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of fm_transfer
-- ----------------------------
INSERT INTO `fm_transfer` VALUES (1, 1, '唐三藏', 1, '门锁', 300, 3, '三号仓库', 1, '一号仓库', '2022-06-05');
INSERT INTO `fm_transfer` VALUES (2, 2, '孙悟空', 1, '门锁', 400, 4, NULL, 1, '一号仓库', '2022-06-05');

-- ----------------------------
-- Table structure for fm_warehouse
-- ----------------------------
DROP TABLE IF EXISTS `fm_warehouse`;
CREATE TABLE `fm_warehouse`  (
  `warehouse_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '仓库编号',
  `warehouse_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库名称',
  `warehouse_address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库地址',
  `warehouse_phone` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '仓库联系方式',
  PRIMARY KEY (`warehouse_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of fm_warehouse
-- ----------------------------
INSERT INTO `fm_warehouse` VALUES (1, '一号仓库', '河北省邯郸市惊蛰机械厂一号仓库', '13921856889');
INSERT INTO `fm_warehouse` VALUES (2, '二号仓库', '河北省邯郸市惊蛰机械厂二号仓库', '17517542179');
INSERT INTO `fm_warehouse` VALUES (3, '三号仓库', '河北省邯郸市惊蛰机械厂三号仓库', '13845262548');

-- ----------------------------
-- Triggers structure for table fm_instore
-- ----------------------------
DROP TRIGGER IF EXISTS `trigger3`;
delimiter ;;
CREATE TRIGGER `trigger3` BEFORE INSERT ON `fm_instore` FOR EACH ROW begin
	set new.instore_date = now();
	set new.staff_name = (select fm_staff.staff_name from fm_staff where staff_id = new.staff_id);
	set new.material_name = (select fm_material.material_name from fm_material where fm_material.material_id = new.material_id);
	set new.warehouse_name = (select fm_warehouse.warehouse_name from fm_warehouse where fm_warehouse.warehouse_id = new.warehouse_id);
	
	update fm_matl_whse set material_num = material_num + new.instore_num where fm_matl_whse.warehouse_id = new.warehouse_id and fm_matl_whse.material_id = new.material_id;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table fm_matl_whse
-- ----------------------------
DROP TRIGGER IF EXISTS `trigger4`;
delimiter ;;
CREATE TRIGGER `trigger4` BEFORE INSERT ON `fm_matl_whse` FOR EACH ROW begin
	set new.warehouse_name = (select fm_warehouse.warehouse_name from fm_warehouse where fm_warehouse.warehouse_id = new.warehouse_id);
	set new.material_name = (select fm_material.material_name from fm_material where fm_material.material_id = new.material_id);
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table fm_outstore
-- ----------------------------
DROP TRIGGER IF EXISTS `trigger5`;
delimiter ;;
CREATE TRIGGER `trigger5` BEFORE INSERT ON `fm_outstore` FOR EACH ROW begin
	set new.outstore_date = now();
	set new.staff_name = (select fm_staff.staff_name from fm_staff where staff_id = new.staff_id);
	set new.material_name = (select fm_material.material_name from fm_material where fm_material.material_id = new.material_id);
	set new.warehouse_name = (select fm_warehouse.warehouse_name from fm_warehouse where fm_warehouse.warehouse_id = new.warehouse_id);
	
	if (select material_num from fm_matl_whse	where fm_matl_whse.warehouse_id = new.warehouse_id and fm_matl_whse.material_id = new.material_id) >= new.outstore_num then 
		
		update fm_matl_whse set material_num = material_num - new.outstore_num where fm_matl_whse.warehouse_id = new.warehouse_id and fm_matl_whse.material_id = new.material_id;
	end if;
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table fm_staff_section
-- ----------------------------
DROP TRIGGER IF EXISTS `trigger1`;
delimiter ;;
CREATE TRIGGER `trigger1` BEFORE INSERT ON `fm_staff_section` FOR EACH ROW begin
	set new.staff_name = (select fm_staff.staff_name from fm_staff where staff_id = new.staff_id);
	set new.section_name = (select fm_section.section_name from fm_section where fm_section.section_id = new.section_id);
end
;;
delimiter ;

-- ----------------------------
-- Triggers structure for table fm_transfer
-- ----------------------------
DROP TRIGGER IF EXISTS `trigger6`;
delimiter ;;
CREATE TRIGGER `trigger6` BEFORE INSERT ON `fm_transfer` FOR EACH ROW begin
	set new.transfer_date = now();
	set new.staff_name = (select fm_staff.staff_name from fm_staff where staff_id = new.staff_id);
	set new.material_name = (select fm_material.material_name from fm_material where fm_material.material_id = new.material_id);
	set new.in_warehouse_name = (select fm_warehouse.warehouse_name from fm_warehouse where fm_warehouse.warehouse_id = new.in_warehouse_id);
	set new.out_warehouse_name = (select fm_warehouse.warehouse_name from fm_warehouse where fm_warehouse.warehouse_id = new.out_warehouse_id);
	if (select material_num from fm_matl_whse	where fm_matl_whse.warehouse_id = new.out_warehouse_id and fm_matl_whse.material_id = new.material_id) >= new.transfer_num then 
	
		update fm_matl_whse set material_num = material_num + new.transfer_num where fm_matl_whse.warehouse_id = new.in_warehouse_id and fm_matl_whse.material_id = new.material_id;
		
		update fm_matl_whse set material_num = material_num - new.transfer_num where fm_matl_whse.warehouse_id = new.out_warehouse_id and fm_matl_whse.material_id = new.material_id;
	end if;
end
;;
delimiter ;

SET FOREIGN_KEY_CHECKS = 1;

课程设计心得：

理论联系实际才能做好一件事，学习一门课程同样是这样。通过此次课程设计项目实践，受益匪浅，从中学到了许多新知识，这些知识是在课堂中不能学到或者说很难学到的。并且对数据分析这一门课程有了更深一步的理解。在做课程设计项目过程中，可以把课堂上所学的理论知识，和实践联系起来，在所要开发的项目中渐渐学会了融会贯通。
这次课程设计项目，在这次项目中，我们完全投入到了数据分析的世界里。结束后明白了理论和实践要想充分地结合，需要非常扎实的基本功。这就说明学好基础知识是理论付诸实践的前提。在开发过程中我们学到了很多，希望在以后能充分利用实习的机会充实自己，用所学的理论知识充分去实践，在实践中又要努力去巩固理论知识。只有这样，才能把一门课程甚至一门学科学精、学透。
当然，课程设计项目中仍存在许多尚待改进之处，这就需要我们学习更多的知识，进行更深研究。