E-R图

ER 图

数据库结构

结构图

关系模式

设备 (设备id 设备类型 出厂号 描述 位置 型号 管理人工号 厂家 报修电话 维保日期 购买时间 录入员)
路由器 (设备id 唯一标识 操作系统 管理 管理IP 账号 密码 配置文件 端口属性连接信息 )
服务器(设备id 管理 管理IP 账号 密码 cpu 内存(单位MB) 硬盘 ip地址 支持的端口和服务)
交换机( 设备id ip地址 )
员工(姓名 性别 工号 电话 用户名 密码 部门)
故障记录 ( 唯一标识 故障级别 维修结果 设备id
位置信息 ( 位置信息id 楼号 房间号 机架号 机架中的层 |)

建表

用户表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for employee
-- ----------------------------
DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee` (
`eno` int NOT NULL AUTO_INCREMENT,
`password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`empl_type` enum('administrator','operator','repairer','root') CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`tele` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`gender` enum('男','女') CHARACTER SET ucs2 COLLATE ucs2_general_ci NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
PRIMARY KEY (`eno`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

设备表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for eqpt
-- ----------------------------
DROP TABLE IF EXISTS `eqpt`;
CREATE TABLE `eqpt` (
`eqpt_id` int NOT NULL,
`eqpt_type` enum('router','swich','server') CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`factory_no` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`local_id` int NOT NULL,
`model` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`manager` int NOT NULL,
`factory` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`trb_tele` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`maintenance_date` datetime NOT NULL,
`purchase_date` datetime NOT NULL,
`enterone` int NOT NULL,
PRIMARY KEY (`eqpt_id`) USING BTREE,
INDEX `fk_loca`(`local_id`) USING BTREE,
INDEX `fk_manager`(`manager`) USING BTREE,
INDEX `fk_enter`(`enterone`) USING BTREE,
CONSTRAINT `fk_enter` FOREIGN KEY (`enterone`) REFERENCES `employee` (`eno`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_loca` FOREIGN KEY (`local_id`) REFERENCES `eqpt_location` (`loca_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_manager` FOREIGN KEY (`manager`) REFERENCES `employee` (`eno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

设备位置表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for eqpt_location
-- ----------------------------
DROP TABLE IF EXISTS `eqpt_location`;
CREATE TABLE `eqpt_location` (
`local_id` int NOT NULL,
`building` int NOT NULL,
`room` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`rack` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`layer` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
PRIMARY KEY (`loca_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

维修记录表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for maintenance_record
-- ----------------------------
DROP TABLE IF EXISTS `maintenance_record`;
CREATE TABLE `maintenance_record` (
`trb_id` int NOT NULL,
`level` int NOT NULL,
`res_id` int NOT NULL,
`eqpt_record` int NOT NULL,
PRIMARY KEY (`trb_id`) USING BTREE,
INDEX `fk_level`(`level`) USING BTREE,
INDEX `fk_trb_res`(`res_id`) USING BTREE,
INDEX `fk_record_eqpt`(`eqpt_record`) USING BTREE,
CONSTRAINT `fk_level` FOREIGN KEY (`level`) REFERENCES `trb_level` (`trblevel`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_record_eqpt` FOREIGN KEY (`eqpt_record`) REFERENCES `eqpt` (`eqpt_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_trb_res` FOREIGN KEY (`res_id`) REFERENCES `trb_res` (`res_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

路由器表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for router
-- ----------------------------
DROP TABLE IF EXISTS `router`;
CREATE TABLE `router` (
`eqpt_id` int NOT NULL,
`system` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`manage` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`manage_ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`router_account` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`router_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`config` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`p_c_info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (`eqpt_id`) USING BTREE,
CONSTRAINT `fk_eqptrouter` FOREIGN KEY (`eqpt_id`) REFERENCES `eqpt` (`eqpt_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

服务器表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for server
-- ----------------------------
DROP TABLE IF EXISTS `server`;
CREATE TABLE `server` (
`eqpt_id` int NOT NULL,
`manage` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`manage_ip` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`server_account` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`server_password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`cpu` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
`ram` int NOT NULL,
`rom` int NOT NULL,
`ipaddress` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`sup_p_s` int NULL DEFAULT NULL,
PRIMARY KEY (`eqpt_id`) USING BTREE,
INDEX `fk_service`(`sup_p_s`) USING BTREE,
CONSTRAINT `fk_eqptserver` FOREIGN KEY (`eqpt_id`) REFERENCES `eqpt` (`eqpt_id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `fk_service` FOREIGN KEY (`sup_p_s`) REFERENCES `service_info` (`service_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;


交换机表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for switch
-- ----------------------------
DROP TABLE IF EXISTS `switch`;
CREATE TABLE `switch` (
`eqpt_id` int NOT NULL,
`ipaddress` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (`eqpt_id`) USING BTREE,
CONSTRAINT `fk_eqptswitch` FOREIGN KEY (`eqpt_id`) REFERENCES `eqpt` (`eqpt_id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

故障等级表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for trb_level
-- ----------------------------
DROP TABLE IF EXISTS `trb_level`;
CREATE TABLE `trb_level` (
`trblevel` int NOT NULL,
`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (`trblevel`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

维修结果表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for trb_res
-- ----------------------------
DROP TABLE IF EXISTS `trb_res`;
CREATE TABLE `trb_res` (
`res_id` int NOT NULL,
`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
PRIMARY KEY (`res_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

服务信息表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for service_info
-- ----------------------------
DROP TABLE IF EXISTS `service_info`;
CREATE TABLE `service_info` (
`service_id` int NOT NULL,
`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`agreement` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NULL DEFAULT NULL,
`port` int NULL DEFAULT NULL,
PRIMARY KEY (`service_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_bin ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

  • 其中故障等级表内容固定如下
1
2
3
4
5
6
+----------+-----------------------------------------------------+
| trblevel | description |
+----------+-----------------------------------------------------+
| 0 | 管理员重启、修改配置可修正的问题。 |
| 1 | 需要更换硬件、维修硬件修改问题 |
+----------+-----------------------------------------------------+