数据库系统设计-SQL语句

题目：

假设我们要为宿舍管理系统设计一个数据库，这个数据库包含用户，学生，宿舍，住宿，报修等信息。请用SQLY语句实现创建数据库，创建表，注意表中要实现各种完整性如建立主键，外键，check约束等，最后向每个表中输入5行数据。

一、表结构及样例数据

1. 学生表 (Student)

表结构:

列名	数据类型	允许空值	说明
sno	CHAR(8)	NO	主键，唯一标识学生
sname	CHAR(8)	NO	学生姓名
age	SMALLINT	YES	学生年龄
sex	CHAR(2)	YES	性别，仅限“男”或“女”
sdept	VARCHAR(50)	YES	学生所在的系别

样例数据:

sno	sname	age	sex	sdept
20210001	张三	20	男	计算机系
20210002	李四	21	女	电子信息系
20210003	王五	22	男	土木工程系
20210004	赵六	19	女	化学系
20210005	孙七	23	男	物理系

2. 宿舍表 (Dormitory)

表结构:

列名	数据类型	允许空值	说明
dorm_id	CHAR(6)	NO	主键，唯一标识宿舍
capacity	SMALLINT	NO	宿舍容量，必须大于0
available	SMALLINT	YES	当前可用床位数，限制范围在0到capacity之间

样例数据:

dorm_id	capacity	available
A101	4	3
A102	4	2
B201	6	4
B202	6	5
C301	8	8

3. 住宿表 (Accommodation)

表结构:

列名	数据类型	允许空值	说明
accomm_id	INT	NO	主键，自增字段
sno	CHAR(8)	NO	外键，关联到学生表
dorm_id	CHAR(6)	NO	外键，关联到宿舍表
start_date	DATE	NO	入住宿舍的开始日期
end_date	DATE	YES	退宿日期，允许为空表示仍在住宿中

样例数据:

accomm_id	sno	dorm_id	start_date	end_date
1	20210001	A101	2024-09-01	NULL
2	20210002	A102	2024-09-01	NULL
3	20210003	B201	2023-09-01	2024-07-01
4	20210004	B202	2024-09-01	NULL
5	20210005	C301	2024-08-15	NULL

4. 报修表 (Repair)

表结构:

列名	数据类型	允许空值	说明
repair_id	INT	NO	主键，自增字段
dorm_id	CHAR(6)	NO	外键，关联到宿舍表
report_date	DATE	NO	报修日期
repair_description	VARCHAR(255)	NO	报修描述，说明故障或维修需求
status	VARCHAR(20)	YES	报修状态，仅限“待处理”、“处理中”、“已完成”

样例数据:

repair_id	dorm_id	report_date	repair_description	status
1	A101	2024-10-01	空调不制冷	处理中
2	A102	2024-09-15	门锁损坏	已完成
3	B201	2024-08-20	水管漏水	待处理
4	B202	2024-10-05	电灯不亮	处理中
5	C301	2024-09-10	床铺损坏	已完成

5. 管理员表 (Admin)

表结构:

列名	数据类型	允许空值	说明
admin_id	CHAR(6)	NO	主键，唯一标识管理员
admin_name	CHAR(8)	NO	管理员姓名
phone	VARCHAR(15)	NO	管理员电话，仅限数字
dorm_id	CHAR(6)	NO	外键，关联到宿舍表，表示负责的宿舍

样例数据:

admin_id	admin_name	phone	dorm_id
A001	王老师	13812345678	A101
A002	李老师	13987654321	A102
B001	张老师	13698765432	B201
B002	赵老师	13587654321	B202
C001	孙老师	13712345678	C301

6. 访客登记表 (VisitorRegister)

表结构:

列名	数据类型	允许空值	说明
visit_id	INT	NO	主键，自增字段
visitor_name	CHAR(8)	NO	访客姓名
sno	CHAR(8)	YES	外键，关联到学生表
visit_date	DATE	NO	访问日期
visit_purpose	VARCHAR(255)	NO	访问目的
admin_id	CHAR(6)	NO	外键，关联到管理员表

样例数据:

visit_id	visitor_name	sno	visit_date	visit_purpose	admin_id
1	刘一	20210001	2024-10-01	探望朋友	A001
2	陈二	20210002	2024-09-28	递送物品	A002
3	张三	20210003	2024-10-02	参观校园	B001
4	李四	NULL	2024-10-03	业务洽谈	B002
5	王五	20210005	2024-10-05	学习交流	C001

二、 创建数据库并使用

-- 创建数据库
CREATE DATABASE DormitoryManagement;

--使用数据库
USE DormitoryManagement;

三、创建表并插入数据（完整代码）

-- 创建学生表，包括学号、姓名、年龄、性别和所在系的信息
CREATE TABLE Student (
    sno CHAR(8) PRIMARY KEY, -- 学号，主键
    sname CHAR(8) NOT NULL, -- 姓名，不允许为空
    age SMALLINT, -- 年龄
    sex CHAR(2) CHECK (sex IN ('男', '女')), -- 性别，限制为“男”或“女”
    sdept VARCHAR(50) -- 所在系，最多50个字符
);

-- 插入数据到学生表
INSERT INTO Student (sno, sname, age, sex, sdept) VALUES
('20210001', '张三', 20, '男', '计算机系'),
('20210002', '李四', 21, '女', '电子信息系'),
('20210003', '王五', 22, '男', '土木工程系'),
('20210004', '赵六', 19, '女', '化学系'),
('20210005', '孙七', 23, '男', '物理系');

-- 创建宿舍表，包括宿舍编号、最大容量和当前可用床位数
CREATE TABLE Dormitory (
    dorm_id CHAR(6) PRIMARY KEY, -- 宿舍编号，主键
    capacity SMALLINT CHECK (capacity > 0), -- 宿舍容量，必须大于0
    available SMALLINT -- 当前可用床位数
);

-- 插入数据到宿舍表
INSERT INTO Dormitory (dorm_id, capacity, available) VALUES
('A101', 4, 3),