智能家居数据库设计 - 用户、设备、场景、权限管理

create database smarthome; use smarthome; -- 用户表 create table userb ( UserID int primary key,-- 用户ID Username varchar(50),-- 用户名 Password varchar(50),-- 密码 Email varchar(100),-- 电子邮件 Phone varchar(15)-- 手机号码 ); insert into userb values (1001, '李聪', 'password', '2602175453@qq.com', '2602175453'), (1002, '方强', 'password', '2488794964@qq.com', '2488794964'), (1003, '莫先杰', 'password', '3038895530@qq.com', '3038895530'), (1004, '巫锦生', 'password', '2631235361@qq.com', '2631235361'), (1005, '虞岚英', 'password', '3154868435@qq.com', '3154868435'), (1006, '谭桂华', 'password', '195121655@qq.com', '195121655'), (1007, '何彩英', 'password', '3381793379@qq.com', '3381793379'), (1008, '张三', 'password', 'as@example.com', '1234567890'), (1009, '李四', 'password', 'ls@example.com', '0987654321'), (1010,'赵六', 'password', 'zl@example.com', '9876543210')

-- 创建角色表 CREATE TABLE UserRole ( RoleID int primary key, -- 角色ID RoleName varchar(50) -- 角色名称 ); insert into UserRole values(1, '我'),(2, '父亲'),(3, '母亲'),(4, '儿子'),(5, '女儿'),(6, '祖父'), (7, '祖母'),(8, '外祖父'),(9, '外祖母'),(10, '叔叔'),(11, '阿姨')

-- 用户角色关联表 CREATE TABLE UserRoleMapping ( UserRoleMappingID int primary key, -- 用户角色关联ID UserID int foreign key references userb(UserID), -- 用户ID 外键关联到用户表 RoleID int foreign key references UserRole(RoleID), -- 角色ID 外键关联到角色表 ); insert into UserRoleMapping values (1,1002, 1),(2,1002, 2),(3,1003, 3),(4,1004, 4),(5,1005, 5),(6,1006, 6),(7,1007, 7), (8,1008, 8),(9,1009, 9),(10,1010, 10)

--设备表 create table Device ( DeviceID int primary key,--设备ID DeviceName varchar(50),--设备名称 DeviceType int,--设备类型 UserID int foreign key references userb(UserID),--所属用户ID 外键关联到用户表 StatusID int foreign key references DeviceStatus(StatusID),--设备状态 外键关联到设备状态表 ); insert into Device values (1, '灯光1', 1, 1001, 1), (2, '空调', 2, 1001, 2), (3, '窗帘', 3, 1002, 1), (4, '电视', 4, 1002, 2), (5, '音响', 5, 1001, 1), (6, '温湿度传感器', 6, 1003, 1), (7, '洗衣机', 7, 1006, 2), (8, '冰箱', 8, 1005, 1), (9, '烤箱', 9, 1005, 1), (10, '风扇', 1, 1003, 2)

--设备类型表 create table DeviceType ( DeviceTypeID int primary key,--设备类型ID DeviceTypeName varchar(50)--设备类型名称 ); insert into DeviceType values(1, '灯光'),(2, '空调'),(3, '窗帘'),(4, '电视'),(5, '音响'), (6, '温湿度传感器'),(7, '洗衣机'),(8, '冰箱'),(9, '烤箱'),(10, '风扇')

--设备状态表 create table DeviceStatus ( StatusID int primary key,--设备状态ID StatusName varchar(50)--设备状态名称 ); insert into DeviceStatus values (1, '开'),(2, '关'),(3, '运行中'),(4, '停止'), (5, '故障'),(6, '待机'),(7, '正常'),(8, '异常'),(9, '低电量'),(10, '高温警告')

--房间表 create table Room ( RoomID int primary key,--房间ID RoomName varchar(50),--房间名称 UserID int foreign key references userb(UserID),--所属用户ID 外键关联到用户表 ); insert into Room values (1, '客厅',1001),(2, '卧室', 1001),(3, '厨房', 1002), (4, '餐厅',1003),(5, '书房', 1006),(6, '浴室', 1005),(7, '儿童房', 1007),(8, '办公室', 1010), (9, '阳台', 1006),(10, '花园', 1004)

--房间设备关联表 create table RoomDevice ( RoomDeviceID int primary key,--房间设备关联ID RoomID int foreign key references Room(RoomID),--房间ID 外键关联到房间表 DeviceID int foreign key references Device(DeviceID),--设备ID 外键关联到设备表 ); insert into RoomDevice values (1, 1, 1),(2, 1, 2),(3, 2, 3),(4, 2, 4), (5, 3, 5),(6, 3, 6),(7, 4, 7),(8, 4, 8),(9, 5, 9),(10, 5, 10)

--传感器数据表 create table SensorData ( DataID int primary key,--传感器数据ID DeviceID int foreign key references Device(DeviceID),--设备ID 外键关联到设备表 SensorType varchar(50),--传感器类型 SensorValue float,--传感器数值 Timestamp DATETIME,--数据采集时间 ); insert into SensorData values(1, 1, '温度', 25.5, GETDATE()),(2, 2, '湿度', 50.0, GETDATE()),(3, 3, '光照度', 500.0, GETDATE()), (4, 4, '二氧化碳浓度', 1000.0, GETDATE()),(5, 5, '噪音水平', 60.0, GETDATE()),(6, 6, '压力', 1001.2, GETDATE()), (7, 7, '电流', 2.5, GETDATE()),(8, 8, '电压', 220.0, GETDATE()),(9, 9, '湿度', 45.0, GETDATE()),(10, 10, '温度', 26.0, GETDATE())

--场景表 create table Scene ( SceneID int primary key,--场景ID SceneName varchar(50),--场景名称 SceneDescription varchar(100),--场景描述 UserID int foreign key references userb(UserID),--所属用户ID 外键关联到用户表 ); insert into Scene values(1, '回家模式', '打开灯光、调节温度', 1005),(2, '离家模式', '关闭所有设备、锁门', 1001), (3, '晚餐模式', '打开餐厅灯光、播放音乐', 1003),(4, '睡眠模式', '关闭所有灯光、调整温度', 1001), (5, '读书模式', '调暗灯光、播放轻音乐', 1001),(6, '派对模式', '彩色灯光、高音量音乐', 1001), (7, '工作模式', '调亮办公室灯光、提醒休息', 1001),(8, '早晨模式', '打开窗帘、播放清新音乐',1004), (9, '健身模式', '调整房间温度、开始计时', 1001),(10, '放松模式', '调暗灯光、播放自然声音', 1001)

--场景设备关联表 create table SceneDevice ( SceneDeviceID int primary key,--场景设备关联ID SceneID int foreign key references Scene(SceneID),--场景ID 外键关联到场景表 DeviceID int foreign key references Device(DeviceID),--设备ID 外键关联到设备表 StatusID int foreign key references DeviceStatus(StatusID),--设备状态 外键关联到设备状态表 ); insert into SceneDevice values(1, 1, 1, 1),(2, 1, 2, 2),(3, 2, 3, 3), (4, 2, 4, 4),(5, 3, 5, 5),(6, 3, 6, 6),(7, 4, 7, 7), (8, 4, 8, 8),(9, 5, 9,9),(10, 5, 10, 10)

--定时任务表 create table ScheduledTask ( TaskID int primary key,--任务ID TaskName varchar(50),--任务名称 TaskDescription varchar(100),--任务描述 ExecutionTime time,--执行时间 UserID int foreign key references userb(UserID),--所属用户ID 外键关联到用户表 DeviceID int foreign key references Device(DeviceID),--设备ID 外键关联到设备表 StatusID int foreign key references DeviceStatus(StatusID),--设备状态 外键关联到设备状态表 ); insert into ScheduledTask values(1, '早晨闹钟', '每天早上7点的闹钟', '07:00:00', 1001, 1, 1), (2, '晚餐提醒', '每天晚上6点的用餐提醒', '18:00:00', 1001, 2, 1), (3, '空调定时开关', '每天下午3点打开空调', '15:00:00', 1002, 3, 1), (4, '音乐播放器', '每天晚上8点开始播放音乐', '20:00:00', 1003, 4,3), (5, '温度调节', '每周五晚上9点调高温度', '21:00:00', 1004, 5, 5), (6, '照明控制', '每天晚上10点关闭灯光', '22:00:00', 1005, 6, 9), (7, '水泵定时', '每天中午12点启动水泵', '12:00:00', 1006, 7, 4), (8, '净化器定时', '每天上午9点启动净化器', '09:00:00', 1007, 8, 6), (9, '冷冻模式', '每周一至周五下午4点启动冷冻模式', '16:00:00', 1006, 9,2), (10, '风扇定时', '每天晚上11点关闭风扇', '23:00:00', 1004, 10, 8)

-- 消费记录表 create table ExpenseRecord ( RecordID int primary key, -- 记录ID UserID int foreign key references userb(UserID), -- 用户ID 外键关联到用户表 Amount decimal(10, 2), -- 消费金额 ExpenseTime datetime, -- 消费时间 ); insert into ExpenseRecord values(1, 1001, 100.00, '2022-01-01 09:30:00'),(2, 1002, 50.00, '2022-02-05 14:15:00'), (3, 1003, 80.00, '2022-02-10 18:45:00'),(4, 1004, 120.50, '2022-03-03 11:20:00'),(5, 1005, 200.00, '2022-04-15 16:30:00'), (6, 1006, 75.80, '2022-05-20 10:00:00'),(7, 1007, 150.00, '2022-06-08 13:45:00'),(8, 1008, 60.00, '2022-07-22 19:00:00'), (9, 1009, 90.50, '2022-08-12 15:10:00'),(10, 1010, 180.00, '2022-09-30 12:30:00')

-- 家庭成员表 create table FamilyMember ( MemberID int primary key, -- 成员ID MemberName varchar(50), -- 成员姓名 Relationship varchar(50), -- 成员关系 UserID int foreign key references userb(UserID), -- 所属用户ID 外键关联到用户表 ); insert into FamilyMember values(2, '李四', '母亲', 1002),(3, '王五', '儿子', 1003),(4, '赵六', '女儿', 1002),(5, '小明', '儿子', 1003), (6, '小红', '女儿', 1003),(7, '刘备', '父亲', 1004),(8, '关羽', '母亲', 1004),(9, '张飞', '儿子', 1005),(10, '诸葛亮', '妻子', 1005)

-- 家庭成员设备权限表 create table MemberDevicePermission ( PermissionID int primary key, -- 权限ID MemberID int foreign key references FamilyMember(MemberID), -- 成员ID 外键关联到家庭成员表 DeviceID int foreign key references Device(DeviceID), -- 设备ID 外键关联到设备表 HasPermission bit, -- 是否有权限 ); insert into MemberDevicePermission values(4, 2, 3, 1),(5, 3, 2, 1),(6, 4, 1, 0), (7, 4, 4, 1),(8, 5, 3, 1),(9, 5, 5, 0),(10, 6, 2, 1) -- 用户角色视图 create view vw_UserRole as select u.UserID, u.Username, r.RoleName from userb u join UserRoleMapping urm on u.UserID = urm.UserID join UserRole r on urm.RoleID = r.RoleID;

-- 设备房间视图 create view vw_DeviceRoom as select d.DeviceID, d.DeviceName, d.DeviceType, r.RoomName from Device d join RoomDevice rd on d.DeviceID = rd.DeviceID join Room r on rd.RoomID = r.RoomID;

-- 设备传感器数据视图 create view vw_DeviceSensorData as select d.DeviceName, s.SensorType, s.SensorValue, s.Timestamp from Device d join SensorData s on d.DeviceID = s.DeviceID;

-- 场景设备视图 create view vw_SceneDevice as select s.SceneName, d.DeviceName, ds.StatusName from Scene s join SceneDevice sd on s.SceneID = sd.SceneID join Device d on sd.DeviceID = d.DeviceID join DeviceStatus ds on sd.StatusID = ds.StatusID;

-- 定时任务设备视图 create view vw_ScheduledTaskDevice as select t.TaskName, d.DeviceName, ds.StatusName from ScheduledTask t join Device d on t.DeviceID = d.DeviceID join DeviceStatus ds on t.StatusID