SQLserver关于教学楼中教室在某个时间段是否被占用的数据库设计

需求：关于教学楼中教室在周几某个时间段是否被占用的数据库设计

具体代码如下：

-------------------教学楼维度
CREATE TABLE TeacBuild
(
 tbID int not null primary key,
 tbName varchar(100) 
)


insert into TeacBuild values(1,'教学楼A')
insert into TeacBuild values(2,'教学楼B')
insert into TeacBuild values(3,'教学楼C')
------------------教室维度
--drop table Room
CREATE TABLE Room
(
   seq    int identity(1,1),
   TID    int, 
   WeekID int,
   sno varchar(100),
   ID  INT NOT  NULL,
   Rid int not null ,
   Rname varchar(20),
   tbID int 
)

---------------------------------------------周一时间点为8点到16点的
INSERT INTO Room values(1,1,'F',1,101,'101教室',1)
INSERT INTO Room values(1,2,'T',1,101,'101教室',1)
INSERT INTO Room values(1,3,'T',1,101,'101教室',1)
INSERT INTO Room values(1,4,'T',1,101,'101教室',1)
INSERT INTO Room values(1,5,'T',1,101,'101教室',1)
---------------------------------------------周二时间点为8点到16点的
INSERT INTO Room values(2,1,'F',1,101,'101教室',1)
INSERT INTO Room values(2,2,'F',1,101,'101教室',1)
INSERT INTO Room values(2,3,'T',1,101,'101教室',1)
INSERT INTO Room values(2,4,'T',1,101,'101教室',1)
INSERT INTO Room values(2,5,'T',1,101,'101教室',1)
---------------------------------------------周三时间点为8点到16点的
INSERT INTO Room values(3,1,'F',1,101,'101教室',1)
INSERT INTO Room values(3,2,'T',1,101,'101教室',1)
INSERT INTO Room values(3,3,'T',1,101,'101教室',1)
INSERT INTO Room values(3,4,'T',1,101,'101教室',1)
INSERT INTO Room values(3,5,'T',1,101,'101教室',1)
-------------------------------------------------周四时间点为8点到16点的
INSERT INTO Room values(4,1,'F',1,101,'101教室',1)
INSERT INTO Room values(4,2,'F',1,101,'101教室',1)
INSERT INTO Room values(4,3,'T',1,101,'101教室',1)
INSERT INTO Room values(4,4,'T',1,101,'101教室',1)
INSERT INTO Room values(4,5,'T',1,101,'101教室',1)
---------------------------------------------周五时间点为8点到16点的
INSERT INTO Room values(5,1,'F',1,101,'101教室',1)
INSERT INTO Room values(5,2,'T',1,101,'101教室',1)
INSERT INTO Room values(5,3,'T',1,101,'101教室',1)
INSERT INTO Room values(5,4,'T',1,101,'101教室',1)
INSERT INTO Room values(5,5,'T',1,101,'101教室',1)

-----------------时间维度
CREATE TABLE DayTime
(
  TID int primary key,
  Tdesc varchar(20)
)

insert into DayTime values(1,'8:00')
insert into DayTime values(2,'10:00')
insert into DayTime values(3,'14:00')
insert into DayTime values(4,'16:00')
-----------------周记天数维度
--drop table WeekTime
CREATE TABLE WeekTime
(
  WeekID int primary key,
  Weekdesc varchar(20)  
)

insert into WeekTime values(1,'周一')
insert into WeekTime values(2,'周二')
insert into WeekTime values(3,'周三')
insert into WeekTime values(4,'周四')
insert into WeekTime values(5,'周五')

--状态维度表
--drop table STATUE
CREATE TABLE STATUE
( 
 id int,
 Sno varchar,
 Staname varchar(10)
)
---F为空 ，T为被占用
insert into STATUE values(1,'F','空')
insert  into STATUE values(2,'T','占用')

--查询结果
select  tb.tbName,rm.Rname,wt.Weekdesc,dt.Tdesc,su.Staname from Room rm --教室表
inner join TeacBuild tb on rm.tbID=tb.tbID --教学楼表
inner join DayTime dt ON dt.TID=rm.TID --时间段表
inner join WeekTime wt on wt.WeekID=rm.WeekID  --周表
inner join STATUE su on su.Sno=rm.sno --状态表
-----------------------------锁定一条数据
where 1=1 and
tb.tbID=1 and
rm.ID=1  and 
dt.TID=1 and 
wt.WeekID=1 and
su.id=1

上述数据查询后的结果为：

这是我自己写的一个简单设计，如果有大神有更好的意见，请指点一下，学习学习

转载于:https://www.cnblogs.com/OliverQin/p/5001587.html