MySQL设计工厂管理数据库(Ⅰ)—表结构设计
引言
学习MyQL第5天,主要在学习其的基础的操作知识,想通过实例练习巩固所学,遂成此文。
设计思路
工厂管理E-R图
由图易知:该数据库主要包括五个主体:
1、项目(project)
2、供应商(supplier)
3、仓库(storehouse)
4、零件(components)
5、职工(staff)
设计工厂管理逻辑图
根据E-R图要求,笔者尝试设计的逻辑图如下图所示:
实现过程
实现工具为MySQL,编辑工具为Navicat for MySQL
项目(project)表实现
CREATE TABLE `project` (
`Supplier_supply` int(10) NOT NULL COMMENT '供应量',
`project_ID` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '项目号',
`budget` int(10) NOT NULL COMMENT '预算',
`start_date` datetime(0) NOT NULL COMMENT '开工日期',
PRIMARY KEY (`project_ID`) USING BTREE,
INDEX `supply`(`Supplier_supply`) USING BTREE,
CONSTRAINT `Supplier_supply` FOREIGN KEY (`Supplier_supply`) REFERENCES `supplier` (`supply`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
职工(staff)表设计
CREATE TABLE `staff` (
`staff_ID` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '职工号',
`staff_name` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '职工姓名',
`age` int(3) NOT NULL COMMENT '年龄',
`job_title` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '职称',
`Whether_to_lead` enum('yes','no') CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '是否领导',
`storehouse_ID` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '所在仓库',
PRIMARY KEY (`staff_ID`) USING BTREE,
INDEX `storehouse_ID`(`storehouse_ID`) USING BTREE,
CONSTRAINT `storehouse_ID` FOREIGN KEY (`storehouse_ID`) REFERENCES `storehouse` (`storehouse_ID`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
零件(components)表设计
CREATE TABLE `components` (
`component_ID` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '零件号',
`comonent_name` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '零件名称',
`specification` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '规格',
`unit_price` decimal(10, 2) NOT NULL COMMENT '单价',
`inventory` int(25) NOT NULL COMMENT '库存量',
`description` varchar(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '描述',
PRIMARY KEY (`component_ID`, `inventory`) USING BTREE,
INDEX `inventory`(`inventory`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
供应商(supplier)表设计
CREATE TABLE `supplier` (
`supplier_ID` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '供应商号',
`name` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '姓名',
`address` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '地址',
`telephone_number` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '电话号码',
`account_number` char(22) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '账号',
`supply` int(10) NOT NULL COMMENT '供应量',
PRIMARY KEY (`supplier_ID`, `supply`) USING BTREE,
INDEX `supply`(`supply`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
仓库(storehouse)表设计
CREATE TABLE `storehouse` (
`storehouse_ID` char(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '仓库号',
`area` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '面积',
`s_telephone_number` varchar(11) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '电话号码',
`component_stock` int(10) NOT NULL COMMENT '零件库存',
PRIMARY KEY (`storehouse_ID`) USING BTREE,
INDEX `component_stock`(`component_stock`) USING BTREE,
CONSTRAINT `component_stock` FOREIGN KEY (`component_stock`) REFERENCES `components` (`inventory`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
实现效果
小结
笔者在逻辑结构设计上仍有欠缺,望读者不吝赐教,给笔者一些改进建议。另外,在表中各变量的数据类型的赋予上,仍有不足之处。
参考
编程规则主要来自:
《MySQL必知必会》章节1-12
慕课《MySQL数据库设计与应用》