实验三 数据库设计实验
1. 实验目的
掌握数据库设计基本方法及数据库设计工具。
2. 实验内容和要求
掌握数据库设计基本步骤，包括数据库概念结构设计、逻辑结构设计，物理结构设计，数据库模式SQL语句生成。能够使用数据库设计工具进行数据库设计。
3. 实验重点和难点
实验重点：概念结构设计、逻辑结构设计。
实验难点：逻辑结构设计。逻辑结构设计虽然可以按照一定的规则从概念结构转换来，但是由于概念结构通常比较抽象，较少考虑更多细节，因此转换而成的逻辑结构还需进一步调整和优化。逻辑结构承接概念结构和物理结构，处于核心地位，因而是数据库设计的重点。
4. 实验过程
4.1 需求分析
4.1.1 设计目标
假定学校有多栋宿舍楼，每栋楼有多层，每层有多个宿舍，每个宿舍可住多名学生，设计一个学生宿舍管理系统对学校的学生宿舍进行规范管理。
4.1.2 设计要求
用户群体有：学生、宿管、系统管理员。
管理对象有：宿舍楼、宿舍、宿管、学生等。
要求每个宿舍最多可以住4位同学，每个同学只能在一个宿舍，不同类型宿舍的床位可以不同、费用标准可以不同，不同年级、院系、班级的学生也可以住同一间宿舍。系统要能够对宿舍楼、宿舍、宿管、学生、住宿信息等进行各种操作，并能随时进行各种查询、统计等处理。包括：宿舍楼管理、宿舍管理、宿管管理、学生管理、住宿管理、寝室分配、出入登记、宿舍报修、建议与反馈、各种信息查询等。不同的用户群体有不同的功能，其中宿管的管理权限比系统管理员稍低，仅能实现部分管理功能。
4.1.3 需求文档
系统分为三个模式：学生模式、宿管模式、系统管理模式
学生模式的功能有：查看个人信息、宿舍报修、建议与反馈、修改密码、退出。
宿管模式的功能有：查看个人信息、宿舍管理、学生管理、住宿管理、出入登记、宿舍报修、建议与反馈、修改密码、退出。
系统管理模式的功能有：宿舍楼管理、宿舍管理、宿管管理、学生管理、住宿管理、寝室分配、出入登记、宿舍报修、建议与反馈、删除数据库、退出。
其中，在管理各种数据的同时，要求能对其进行查询，例如：宿舍楼管理，要能增删改查宿舍楼信息；宿管模式的管理等功能相比系统管理模式将受到一定的限制，即宿管模式下仅能对该宿管所分配到的宿舍楼进行管理，仅能对该宿舍楼的宿舍、学生进行管理，所有信息都仅与其分配到的宿舍楼相关，无法查看、操作其他宿舍楼的信息；寝室分配功能要求能一键实现宿舍的自动分配，分配规则按照同一年级、院系、班级进行分配，不同年级或院系或班级的学生不会分配到同一宿舍，若需分配在一起，需手动进行分配……
4.2 概念结构设计
4.2.1 概念结构设计工具(E-R模型)
概念结构的设计使用E-R模型进行设计，从系统管理模式的视角下看，本系统主要可分为：宿舍楼管理、宿舍管理、宿管管理、学生管理、住宿管理，共5个子系统。
4.2.2 宿舍楼管理子系统
4.2.2.1 子系统需求
宿舍楼管理子系统可对宿舍楼进行增删改查操作，以及出入登记等功能。
4.2.2.2 E-R模型
4.2.2.3 子系统描述
一栋宿舍楼有一个宿管进行管理，一栋宿舍楼包含多个宿舍，学生出入宿舍楼可进行登记。
4.2.3 宿舍管理子系统
4.2.3.1 子系统描述
宿舍管理子系统可对宿舍进行增删改查操作，以及宿舍报修等功能。
4.2.3.2 E-R图
4.2.3.3 说明
一栋宿舍楼包含多个宿舍，一个宿舍可住多名学生，学生可进行宿舍报修。
4.2.4 宿管管理子系统
4.2.4.1 子系统描述
宿管管理子系统可对宿管进行增删改查操作。
4.2.4.2 E-R图
4.2.4.3 说明
一个宿管管理一栋宿舍楼。
4.2.5 学生管理子系统
4.2.5.1 子系统描述
学生管理子系统可对学生进行增删改查操作，以及宿舍报修、建议与反馈等功能。
4.2.5.2 E-R图
4.2.5.3 说明
一个学生住在一栋宿舍楼、一个宿舍中，学生可进行宿舍报修、建议与反馈。
4.2.6 住宿管理子系统
4.2.6.1 子系统描述
住宿管理子系统可对住宿信息进行增删改查操作。
4.2.6.2 E-R图
4.2.6.3 说明
一个学生住在一个宿舍中，一个宿舍可住有多名学生。
4.2.7 总体E-R图
上述5个子系统集成总系统，总体E-R图如下，各实体详细属性可见图中。
4.3 逻辑结构设计
4.3.1 关系数据模式
根据以上总体E-R图，可获得以下8个关系模型。
-
宿舍楼（楼号，层数，房间数，类别，居住性别，宿管编号）
-
宿舍（楼号，楼层，宿舍号，总床位数，剩余床位数，单价）
-
宿管（编号，密码，姓名，性别，手机）
-
学生（学号，密码，姓名，性别，出生年份，年级，院系，班级，手机，是否入住）
-
住宿信息（学号，楼号，楼层，宿舍号，入住时间）
-
出入信息（学号，楼号，类别，时间）
-
报修信息（学号，楼号，楼层，宿舍号，报修详细，是否处理）
-
建议与反馈（学号，详细信息）
4.3.2 视图设计
基于以上8个基本表，设计了6个视图用于某部分功能。
- 含学生姓名的住宿信息（学号，姓名，楼号，楼层，宿舍号，入住时间）
- 含学生姓名的出入信息（学号，姓名，楼号，类别，时间）
- 含学生姓名的报修信息（学号，姓名，楼号，楼层，宿舍号，报修详细，是否处理）
- 含学生姓名的建议与反馈（学号，姓名，详细信息）
- 含楼号的住宿学生信息（学号，密码，姓名，性别，出生年份，年级，院系，班级，手机，是否入住，楼号）
- 含学生姓名和楼号的住宿学生的建议与反馈（学号，姓名，详细信息，楼号）
4.4 物理结构设计
| 宿舍楼表floor | |||
|---|---|---|---|
| 字段名 | 数据类型 | 约束 | 注释 |
| floor_num | char(5) | 主键 | 楼号 |
| layer_amount | int | 非空 | 层数 |
| room_amount | int | 非空 | 房间数 |
| category | char(5) | 非空 | 类别 |
| sex | char(5) | 非空 | 居住性别 |
| admin_num | char(10) | 外键，非空，唯一 | 宿管编号 |
| 宿舍表dormitory | |||
|---|---|---|---|
| 字段名 | 数据类型 | 约束 | 注释 |
| floor_num | char(5) | 主键，外键 | 楼号 |
| layer | int | 主键 | 楼层 |
| room_num | int | 主键 | 宿舍号 |
| bed_total | int | 非空 | 总床位数 |
| bed_surplus | int | 非空 | 剩余床位数 |
| price | int | 非空 | 单价 |
| 宿管表admin | |||
|---|---|---|---|
| 字段名 | 数据类型 | 约束 | 注释 |
| admin_num | char(10) | 主键 | 编号 |
| password | char(20) | 非空 | 密码 |
| name | char(5) | 非空 | 姓名 |
| sex | char(1) | 非空 | 性别 |
| phone | char(15) | 非空 | 手机 |
| 学生表student | |||
|---|---|---|---|
| 字段名 | 数据类型 | 约束 | 注释 |
| stu_num | char(15) | 主键 | 学号 |
| password | char(20) | 非空 | 密码 |
| name | char(5) | 非空 | 姓名 |
| sex | char(1) | 非空 | 性别 |
| birth | int | 非空 | 出生年份 |
| grade | int | 非空 | 年级 |
| faculty | char(10) | 非空 | 院系 |
| class | char(10) | 非空 | 班级 |
| phone | char(15) | 非空 | 手机 |
| yes_no | char(1) | 非空 | 是否入住 |
| 住宿信息表stayInfo | |||
|---|---|---|---|
| 字段名 | 数据类型 | 约束 | 注释 |
| stu_num | char(15) | 主键，外键，非空 | 学号 |
| floor_num | char(5) | 外键，非空 | 楼号 |
| layer | int | 外键，非空 | 楼层 |
| room_num | int | 外键，非空 | 宿舍号 |
| time | date | 非空 | 入住时间 |
| 出入信息表in_out | |||
|---|---|---|---|
| 字段名 | 数据类型 | 约束 | 注释 |
| stu_num | char(15) | 外键，非空 | 学号 |
| floor_num | char(5) | 外键，非空 | 楼号 |
| category | char(1) | 非空 | 类别 |
| time | datetime | 非空 | 时间 |
| 报修信息表repair | |||
|---|---|---|---|
| 字段名 | 数据类型 | 约束 | 注释 |
| stu_num | char(15) | 外键，非空 | 学号 |
| floor_num | char(5) | 外键，非空 | 楼号 |
| layer | int | 外键，非空 | 楼层 |
| room_num | int | 外键，非空 | 宿舍号 |
| info | text | 非空 | 报修详细 |
| yes_no | char(1) | 非空 | 是否处理 |
| 建议与反馈表advice | |||
|---|---|---|---|
| 字段名 | 数据类型 | 约束 | 注释 |
| stu_num | char(15) | 外键，非空 | 学号 |
| info | text | 非空 | 详细信息 |
4.5 索引设计
除数据库自建的外键索引，未设计其他索引
4.5 物理结构实现
通过Navicat的模型建立功能构建实体和视图
4.6 SQL语句生成
由navicat生成的mysql语句如下：
admin表：
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`admin_num` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '编号',
`password` char(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '密码',
`name` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '姓名',
`sex` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '性别',
`phone` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '手机',
PRIMARY KEY (`admin_num`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
advice表：
DROP TABLE IF EXISTS `advice`;
CREATE TABLE `advice` (
`stu_num` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '学号',
`info` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '详细信息',
INDEX `advice_FK_stunum`(`stu_num` ASC) USING BTREE,
CONSTRAINT `advice_FK_stunum` FOREIGN KEY (`stu_num`) REFERENCES `student` (`stu_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
dormitory表：
DROP TABLE IF EXISTS `dormitory`;
CREATE TABLE `dormitory` (
`floor_num` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '楼号',
`layer` int NOT NULL COMMENT '楼层',
`room_num` int NOT NULL COMMENT '宿舍号',
`bed_total` int NOT NULL COMMENT '总床位数',
`bed_surplus` int NOT NULL COMMENT '剩余床位数',
`price` int NOT NULL COMMENT '单价',
PRIMARY KEY (`floor_num`, `layer`, `room_num`) USING BTREE,
INDEX `floor_num`(`floor_num` ASC) USING BTREE,
INDEX `layer`(`layer` ASC) USING BTREE,
INDEX `room_num`(`room_num` ASC) USING BTREE,
CONSTRAINT `dormitory_FK_floornum` FOREIGN KEY (`floor_num`) REFERENCES `floor` (`floor_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
floor表：
DROP TABLE IF EXISTS `floor`;
CREATE TABLE `floor` (
`floor_num` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '楼号',
`layer_amount` int NOT NULL COMMENT '层数',
`room_amount` int NOT NULL COMMENT '房间数',
`category` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '类别',
`sex` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '居住性别',
`admin_num` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '宿管编号',
PRIMARY KEY (`floor_num`) USING BTREE,
UNIQUE INDEX `admin_num`(`admin_num` ASC) USING BTREE,
CONSTRAINT `floor_FK_adminnum` FOREIGN KEY (`admin_num`) REFERENCES `admin` (`admin_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
in_out表：
DROP TABLE IF EXISTS `in_out`;
CREATE TABLE `in_out` (
`stu_num` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '学号',
`floor_num` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '楼号',
`category` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '类别（出/入）',
`time` datetime NOT NULL COMMENT '时间',
INDEX `inout_FK_stunum`(`stu_num` ASC) USING BTREE,
INDEX `inout_FK_floornum`(`floor_num` ASC) USING BTREE,
CONSTRAINT `inout_FK_floornum` FOREIGN KEY (`floor_num`) REFERENCES `floor` (`floor_num`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `inout_FK_stunum` FOREIGN KEY (`stu_num`) REFERENCES `student` (`stu_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
repair表：
DROP TABLE IF EXISTS `repair`;
CREATE TABLE `repair` (
`stu_num` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '学号',
`floor_num` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '楼号',
`layer` int NOT NULL COMMENT '楼层',
`room_num` int NOT NULL COMMENT '宿舍号',
`info` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '报修详细',
`yes_no` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '是否处理（是/否）',
INDEX `repair_FK_stunum`(`stu_num` ASC) USING BTREE,
INDEX `repair_FK_floornum`(`floor_num` ASC) USING BTREE,
INDEX `repair_FK_layer`(`layer` ASC) USING BTREE,
INDEX `repair_FK_dormitory`(`room_num` ASC, `floor_num` ASC, `layer` ASC) USING BTREE,
CONSTRAINT `repair_FK_dormitory` FOREIGN KEY (`room_num`, `floor_num`, `layer`) REFERENCES `dormitory` (`room_num`, `floor_num`, `layer`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `repair_FK_stunum` FOREIGN KEY (`stu_num`) REFERENCES `student` (`stu_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
stayinfo表：
DROP TABLE IF EXISTS `stayinfo`;
CREATE TABLE `stayinfo` (
`stu_num` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '学号',
`floor_num` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '楼号',
`layer` int NOT NULL COMMENT '楼层',
`room_num` int NOT NULL COMMENT '宿舍号',
`time` date NOT NULL COMMENT '入住时间',
PRIMARY KEY (`stu_num`) USING BTREE,
INDEX `stayinfo_FK_layer`(`layer` ASC) USING BTREE,
INDEX `stayinfo_FK_roomnum`(`room_num` ASC) USING BTREE,
INDEX `stayinfo_FK_dormitory`(`floor_num` ASC, `layer` ASC, `room_num` ASC) USING BTREE,
CONSTRAINT `stayinfo_FK_dormitory` FOREIGN KEY (`floor_num`, `layer`, `room_num`) REFERENCES `dormitory` (`floor_num`, `layer`, `room_num`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `stayinfo_FK_stunum` FOREIGN KEY (`stu_num`) REFERENCES `student` (`stu_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
student表：
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stu_num` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '学号',
`password` char(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '密码',
`name` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '姓名',
`sex` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '性别',
`birth` int NOT NULL COMMENT '出生日期（用于计算年龄）',
`grade` int NOT NULL COMMENT '年级',
`faculty` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '院系',
`class` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '班级',
`phone` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '手机',
`yes_no` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '是否入住（是/否）',
PRIMARY KEY (`stu_num`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
相关视图：
-- ----------------------------
-- View structure for adminview_advice
-- ----------------------------
DROP VIEW IF EXISTS `adminview_advice`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `adminview_advice` AS select `advice`.`stu_num` AS `stu_num`,`student`.`name` AS `name`,`advice`.`info` AS `info`,`floor`.`admin_num` AS `admin_num` from (((`advice` join `student` on((`advice`.`stu_num` = `student`.`stu_num`))) join `stayinfo` on((`advice`.`stu_num` = `stayinfo`.`stu_num`))) join `floor` on((`stayinfo`.`floor_num` = `floor`.`floor_num`)));
-- ----------------------------
-- View structure for adminview_student
-- ----------------------------
DROP VIEW IF EXISTS `adminview_student`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `adminview_student` AS select `student`.`stu_num` AS `stu_num`,`student`.`password` AS `password`,`student`.`name` AS `name`,`student`.`sex` AS `sex`,`student`.`birth` AS `birth`,`student`.`grade` AS `grade`,`student`.`faculty` AS `faculty`,`student`.`class` AS `class`,`student`.`phone` AS `phone`,`student`.`yes_no` AS `yes_no`,`stayinfo`.`floor_num` AS `floor_num` from (`student` join `stayinfo` on((`student`.`stu_num` = `stayinfo`.`stu_num`)));
-- ----------------------------
-- View structure for student_advice
-- ----------------------------
DROP VIEW IF EXISTS `student_advice`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `student_advice` AS select `advice`.`stu_num` AS `stu_num`,`student`.`name` AS `name`,`advice`.`info` AS `info` from (`student` join `advice` on((`student`.`stu_num` = `advice`.`stu_num`)));
-- ----------------------------
-- View structure for student_inout
-- ----------------------------
DROP VIEW IF EXISTS `student_inout`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `student_inout` AS select `in_out`.`stu_num` AS `stu_num`,`student`.`name` AS `name`,`in_out`.`floor_num` AS `floor_num`,`in_out`.`category` AS `category`,`in_out`.`time` AS `time` from (`student` join `in_out` on((`student`.`stu_num` = `in_out`.`stu_num`)));
-- ----------------------------
-- View structure for student_repair
-- ----------------------------
DROP VIEW IF EXISTS `student_repair`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `student_repair` AS select `repair`.`stu_num` AS `stu_num`,`student`.`name` AS `name`,`repair`.`floor_num` AS `floor_num`,`repair`.`layer` AS `layer`,`repair`.`room_num` AS `room_num`,`repair`.`info` AS `info`,`repair`.`yes_no` AS `yes_no` from (`student` join `repair` on((`student`.`stu_num` = `repair`.`stu_num`)));
-- ----------------------------
-- View structure for student_stayinfo
-- ----------------------------
DROP VIEW IF EXISTS `student_stayinfo`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `student_stayinfo` AS select `student`.`stu_num` AS `stu_num`,`student`.`name` AS `name`,`stayinfo`.`floor_num` AS `floor_num`,`stayinfo`.`layer` AS `layer`,`stayinfo`.`room_num` AS `room_num`,`stayinfo`.`time` AS `time` from (`student` join `stayinfo` on((`student`.`stu_num` = `stayinfo`.`stu_num`)));
5. 实验代码
/*
Navicat Premium Data Transfer
Source Server : 本地
Source Server Type : MySQL
Source Server Version : 80035 (8.0.35)
Source Host : localhost:3306
Source Schema : lab3
Target Server Type : MySQL
Target Server Version : 80035 (8.0.35)
File Encoding : 65001
Date: 12/12/2023 20:42:53
*/
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for admin
-- ----------------------------
DROP TABLE IF EXISTS `admin`;
CREATE TABLE `admin` (
`admin_num` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '编号',
`password` char(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '密码',
`name` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '姓名',
`sex` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '性别',
`phone` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '手机',
PRIMARY KEY (`admin_num`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for advice
-- ----------------------------
DROP TABLE IF EXISTS `advice`;
CREATE TABLE `advice` (
`stu_num` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '学号',
`info` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '详细信息',
INDEX `advice_FK_stunum`(`stu_num` ASC) USING BTREE,
CONSTRAINT `advice_FK_stunum` FOREIGN KEY (`stu_num`) REFERENCES `student` (`stu_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for dormitory
-- ----------------------------
DROP TABLE IF EXISTS `dormitory`;
CREATE TABLE `dormitory` (
`floor_num` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '楼号',
`layer` int NOT NULL COMMENT '楼层',
`room_num` int NOT NULL COMMENT '宿舍号',
`bed_total` int NOT NULL COMMENT '总床位数',
`bed_surplus` int NOT NULL COMMENT '剩余床位数',
`price` int NOT NULL COMMENT '单价',
PRIMARY KEY (`floor_num`, `layer`, `room_num`) USING BTREE,
INDEX `floor_num`(`floor_num` ASC) USING BTREE,
INDEX `layer`(`layer` ASC) USING BTREE,
INDEX `room_num`(`room_num` ASC) USING BTREE,
CONSTRAINT `dormitory_FK_floornum` FOREIGN KEY (`floor_num`) REFERENCES `floor` (`floor_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for floor
-- ----------------------------
DROP TABLE IF EXISTS `floor`;
CREATE TABLE `floor` (
`floor_num` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '楼号',
`layer_amount` int NOT NULL COMMENT '层数',
`room_amount` int NOT NULL COMMENT '房间数',
`category` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '类别',
`sex` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '居住性别',
`admin_num` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '宿管编号',
PRIMARY KEY (`floor_num`) USING BTREE,
UNIQUE INDEX `admin_num`(`admin_num` ASC) USING BTREE,
CONSTRAINT `floor_FK_adminnum` FOREIGN KEY (`admin_num`) REFERENCES `admin` (`admin_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for in_out
-- ----------------------------
DROP TABLE IF EXISTS `in_out`;
CREATE TABLE `in_out` (
`stu_num` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '学号',
`floor_num` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '楼号',
`category` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '类别（出/入）',
`time` datetime NOT NULL COMMENT '时间',
INDEX `inout_FK_stunum`(`stu_num` ASC) USING BTREE,
INDEX `inout_FK_floornum`(`floor_num` ASC) USING BTREE,
CONSTRAINT `inout_FK_floornum` FOREIGN KEY (`floor_num`) REFERENCES `floor` (`floor_num`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `inout_FK_stunum` FOREIGN KEY (`stu_num`) REFERENCES `student` (`stu_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for repair
-- ----------------------------
DROP TABLE IF EXISTS `repair`;
CREATE TABLE `repair` (
`stu_num` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '学号',
`floor_num` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '楼号',
`layer` int NOT NULL COMMENT '楼层',
`room_num` int NOT NULL COMMENT '宿舍号',
`info` text CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '报修详细',
`yes_no` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '是否处理（是/否）',
INDEX `repair_FK_stunum`(`stu_num` ASC) USING BTREE,
INDEX `repair_FK_floornum`(`floor_num` ASC) USING BTREE,
INDEX `repair_FK_layer`(`layer` ASC) USING BTREE,
INDEX `repair_FK_dormitory`(`room_num` ASC, `floor_num` ASC, `layer` ASC) USING BTREE,
CONSTRAINT `repair_FK_dormitory` FOREIGN KEY (`room_num`, `floor_num`, `layer`) REFERENCES `dormitory` (`room_num`, `floor_num`, `layer`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `repair_FK_stunum` FOREIGN KEY (`stu_num`) REFERENCES `student` (`stu_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for stayinfo
-- ----------------------------
DROP TABLE IF EXISTS `stayinfo`;
CREATE TABLE `stayinfo` (
`stu_num` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '学号',
`floor_num` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '楼号',
`layer` int NOT NULL COMMENT '楼层',
`room_num` int NOT NULL COMMENT '宿舍号',
`time` date NOT NULL COMMENT '入住时间',
PRIMARY KEY (`stu_num`) USING BTREE,
INDEX `stayinfo_FK_layer`(`layer` ASC) USING BTREE,
INDEX `stayinfo_FK_roomnum`(`room_num` ASC) USING BTREE,
INDEX `stayinfo_FK_dormitory`(`floor_num` ASC, `layer` ASC, `room_num` ASC) USING BTREE,
CONSTRAINT `stayinfo_FK_dormitory` FOREIGN KEY (`floor_num`, `layer`, `room_num`) REFERENCES `dormitory` (`floor_num`, `layer`, `room_num`) ON DELETE RESTRICT ON UPDATE RESTRICT,
CONSTRAINT `stayinfo_FK_stunum` FOREIGN KEY (`stu_num`) REFERENCES `student` (`stu_num`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`stu_num` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '学号',
`password` char(20) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '密码',
`name` char(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '姓名',
`sex` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '性别',
`birth` int NOT NULL COMMENT '出生日期（用于计算年龄）',
`grade` int NOT NULL COMMENT '年级',
`faculty` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '院系',
`class` char(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '班级',
`phone` char(15) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '手机',
`yes_no` char(1) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL COMMENT '是否入住（是/否）',
PRIMARY KEY (`stu_num`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = DYNAMIC;
-- ----------------------------
-- View structure for adminview_advice
-- ----------------------------
DROP VIEW IF EXISTS `adminview_advice`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `adminview_advice` AS select `advice`.`stu_num` AS `stu_num`,`student`.`name` AS `name`,`advice`.`info` AS `info`,`floor`.`admin_num` AS `admin_num` from (((`advice` join `student` on((`advice`.`stu_num` = `student`.`stu_num`))) join `stayinfo` on((`advice`.`stu_num` = `stayinfo`.`stu_num`))) join `floor` on((`stayinfo`.`floor_num` = `floor`.`floor_num`)));
-- ----------------------------
-- View structure for adminview_student
-- ----------------------------
DROP VIEW IF EXISTS `adminview_student`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `adminview_student` AS select `student`.`stu_num` AS `stu_num`,`student`.`password` AS `password`,`student`.`name` AS `name`,`student`.`sex` AS `sex`,`student`.`birth` AS `birth`,`student`.`grade` AS `grade`,`student`.`faculty` AS `faculty`,`student`.`class` AS `class`,`student`.`phone` AS `phone`,`student`.`yes_no` AS `yes_no`,`stayinfo`.`floor_num` AS `floor_num` from (`student` join `stayinfo` on((`student`.`stu_num` = `stayinfo`.`stu_num`)));
-- ----------------------------
-- View structure for student_advice
-- ----------------------------
DROP VIEW IF EXISTS `student_advice`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `student_advice` AS select `advice`.`stu_num` AS `stu_num`,`student`.`name` AS `name`,`advice`.`info` AS `info` from (`student` join `advice` on((`student`.`stu_num` = `advice`.`stu_num`)));
-- ----------------------------
-- View structure for student_inout
-- ----------------------------
DROP VIEW IF EXISTS `student_inout`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `student_inout` AS select `in_out`.`stu_num` AS `stu_num`,`student`.`name` AS `name`,`in_out`.`floor_num` AS `floor_num`,`in_out`.`category` AS `category`,`in_out`.`time` AS `time` from (`student` join `in_out` on((`student`.`stu_num` = `in_out`.`stu_num`)));
-- ----------------------------
-- View structure for student_repair
-- ----------------------------
DROP VIEW IF EXISTS `student_repair`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `student_repair` AS select `repair`.`stu_num` AS `stu_num`,`student`.`name` AS `name`,`repair`.`floor_num` AS `floor_num`,`repair`.`layer` AS `layer`,`repair`.`room_num` AS `room_num`,`repair`.`info` AS `info`,`repair`.`yes_no` AS `yes_no` from (`student` join `repair` on((`student`.`stu_num` = `repair`.`stu_num`)));
-- ----------------------------
-- View structure for student_stayinfo
-- ----------------------------
DROP VIEW IF EXISTS `student_stayinfo`;
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `student_stayinfo` AS select `student`.`stu_num` AS `stu_num`,`student`.`name` AS `name`,`stayinfo`.`floor_num` AS `floor_num`,`stayinfo`.`layer` AS `layer`,`stayinfo`.`room_num` AS `room_num`,`stayinfo`.`time` AS `time` from (`student` join `stayinfo` on((`student`.`stu_num` = `stayinfo`.`stu_num`)));
SET FOREIGN_KEY_CHECKS = 1;
6. 实验总结
数据库的设计的基本步骤包括数据库概念结构设计、逻辑结构设计，物理结构设计。其中概念结构设计通常用E-R图表示。逻辑结构设计比较重要，需要从E-R图转换得到，并需要完成一些细节，进行调整优化。物理结构设计则需要完成存储路径的设计和存储结构的设计，需要根据实际情况进行设计。例如每个关系的存储结构，具体的数据类型和长度都要符合实际，同时尽量减小开销。使用类似Navicat的工具可以辅助设计，方便的进行模型间的转化，并可以直接生成SQL语句，有必要掌握使用的基本方法。
ITHM = UNDEFINED SQL SECURITY DEFINER VIEW student_stayinfo AS select student.stu_num AS stu_num,student.name AS name,stayinfo.floor_num AS floor_num,stayinfo.layer AS layer,stayinfo.room_num AS room_num,stayinfo.time AS time from (student join stayinfo on((student.stu_num = stayinfo.stu_num)));
SET FOREIGN_KEY_CHECKS = 1;
6. 实验总结
数据库的设计的基本步骤包括数据库概念结构设计、逻辑结构设计，物理结构设计。其中概念结构设计通常用E-R图表示。逻辑结构设计比较重要，需要从E-R图转换得到，并需要完成一些细节，进行调整优化。物理结构设计则需要完成存储路径的设计和存储结构的设计，需要根据实际情况进行设计。例如每个关系的存储结构，具体的数据类型和长度都要符合实际，同时尽量减小开销。使用类似Navicat的工具可以辅助设计，方便的进行模型间的转化，并可以直接生成SQL语句，有必要掌握使用的基本方法。
1175
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
