现有一个会议室预定系统，包含如下表实体：用户表（用户id，用户名，部门，职位）；会议室表（会议室id，办公楼编号，楼层，会议室门牌，会议室类型id）；会议室类型（类型id，容纳人数，是否有投影仪，是否有电话，是否有视频电话）；会议订阅表（用户id，会议室id，订阅开始时间，订阅结束时间）

a. 请根据以上表结构写出建表的sql语句，注意选择合适的字段类型和主键。

b. 有需求查出2017-09-11 10:00:00 至 2017-09-11 12:00:00没有被人预定过的会议室id列表，要求容纳人数在10人以上，有投影仪和视频电话。请问这个sql语句如何编写，如何添加索引加速这个查下。

c. 假设订阅一个会议室包括两个步骤：1.查询出可以预定的会议室列表。2.插入会议订阅表（包含订阅的时间和用户id，会议室id）表示预定。请问如果两个用户同时预定，如何利用数据库的特性保障这两个用户不会在同一时间段预定同一个会议室。

Oracle：

用户表（用户id，用户名，部门，职位）；

CREATE TABLE END_USER 
(
  END_USER_ID VARCHAR2(11) NOT NULL 
, END_USER_NAME VARCHAR2(45) NOT NULL 
, DEPARTMENT VARCHAR2(30) 
, JOB_POSITION VARCHAR2(12) 
, CONSTRAINT END_USER_PK PRIMARY KEY 
  (
    END_USER_ID 
  )
  ENABLE 
);

COMMENT ON COLUMN END_USER.END_USER_ID IS ‘用户ID’;

COMMENT ON COLUMN END_USER.END_USER_NAME IS ‘用户名’;

COMMENT ON COLUMN END_USER.DEPARTMENT IS ‘部门’;

COMMENT ON COLUMN END_USER.JOB_POSITION IS ‘职位’;

会议室表（会议室id，办公楼编号，楼层，会议室门牌，会议室类型id）；

CREATE TABLE MEETING_ROOM 
(
  MEETING_ROOM_ID NUMBER(3) NOT NULL 
, OFFICE_BUILDING_ID CHAR(4) NOT NULL 
, FLOOR NUMBER(2) NOT NULL 
, MEETING_ROOM_DOORPLATE VARCHAR2(4) NOT NULL 
, MEETING_ROOM_TYPE_ID NUMBER(2) 
, CONSTRAINT MEETING_ROOM_PK PRIMARY KEY 
  (
    MEETING_ROOM_ID 
  )
  ENABLE 
);

COMMENT ON COLUMN MEETING_ROOM.MEETING_ROOM_ID IS ‘会议室ID’;

COMMENT ON COLUMN MEETING_ROOM.OFFICE_BUILDING_ID IS ‘办公楼编号’;

COMMENT ON COLUMN MEETING_ROOM.FLOOR IS ‘楼层’;

COMMENT ON COLUMN MEETING_ROOM.MEETING_ROOM_DOORPLATE IS ‘会议室门牌’;

COMMENT ON COLUMN MEETING_ROOM.MEETING_ROOM_TYPE_ID IS ‘会议室类型ID’;

会议室类型（类型id，容纳人数，是否有投影仪，是否有电话，是否有视频电话）；

CREATE TABLE MEETING_ROOM_TYPE 
(
  MEETING_ROOM_TYPE_ID NUMBER(2) NOT NULL 
, CAPACITY_FOR_PEOPLE NUMBER(3) 
, PROJECTOR CHAR(1) DEFAULT ‘N’ 
, TELEPHONE CHAR(1) DEFAULT ‘N’ 
, VISUAL_TELEPHONE CHAR(1) DEFAULT ‘N’ 
, CONSTRAINT MEETING_ROOM_TYPE_PK PRIMARY KEY 
  (
    MEETING_ROOM_TYPE_ID 
  )
  ENABLE 
);

COMMENT ON COLUMN MEETING_ROOM_TYPE.MEETING_ROOM_TYPE_ID IS ‘会议室类型ID’;

COMMENT ON COLUMN MEETING_ROOM_TYPE.CAPACITY_FOR_PEOPLE IS ‘容纳人数’;

COMMENT ON COLUMN MEETING_ROOM_TYPE.PROJECTOR IS ‘是否有投影仪’;

COMMENT ON COLUMN MEETING_ROOM_TYPE.TELEPHONE IS ‘是否有电话’;

COMMENT ON COLUMN MEETING_ROOM_TYPE.VISUAL_TELEPHONE IS ‘是否有视频电话’;

会议订阅表（用户id，会议室id，订阅开始时间，订阅结束时间）；

CREATE TABLE MEETING_SUBSCRIPTION 
(
  END_USER_ID VARCHAR2(11) NOT NULL 
, MEETING_ROOM_ID NUMBER(3) NOT NULL 
, SUBSCRIPTION_START_TIME TIMESTAMP
, SUBSCRIPTION_END_TIME TIMESTAMP
, CONSTRAINT MEETING_SUBSCRIPTION_PK PRIMARY KEY 
  (
    END_USER_ID 
  , MEETING_ROOM_ID 
  )
  ENABLE 
);

COMMENT ON COLUMN MEETING_SUBSCRIPTION.END_USER_ID IS ‘用户ID’;

COMMENT ON COLUMN MEETING_SUBSCRIPTION.MEETING_ROOM_ID IS ‘会议室ID’;

COMMENT ON COLUMN MEETING_SUBSCRIPTION.SUBSCRIPTION_START_TIME IS ‘订阅开始时间’;

COMMENT ON COLUMN MEETING_SUBSCRIPTION.SUBSCRIPTION_END_TIME IS ‘订阅结束时间’;

b.
需要查出2017-09-11 10:00:00 至 2017-09-11 12:00:00没有被人预定过的会议室id列表，要求容纳人数（CAPACITY）在10人以上，有投影仪（PROJECTOR为’Y’）和视频电话（VISUAL_TELEPHONE为’Y’）。

— 查询符合条件的会议室，假定业务正常，订阅开始时间和结束时间都存在
SELECT m.MEETING_ROOM_ID
FROM MEETING_ROOM m
JOIN MEETING_ROOM_TYPE mrt ON m.MEETING_ROOM_TYPE_ID = mrt.MEETING_ROOM_TYPE_ID
WHERE mrt.CAPACITY_FOR_PEOPLE > 10
  AND mrt.PROJECTOR = ‘Y’
  AND mrt.VISUAL_TELEPHONE = ‘Y’
  AND m.MEETING_ROOM_ID NOT IN (
    SELECT ms.MEETING_ROOM_ID
    FROM MEETING_SUBSCRIPTION ms
    WHERE ms.SUBSCRIPTION_START_TIME IS NOT NULL
      AND ms.SUBSCRIPTION_END_TIME IS NOT NULL
      AND ms.SUBSCRIPTION_START_TIME < TO_TIMESTAMP(‘2017-09-11 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
      AND ms.SUBSCRIPTION_END_TIME > TO_TIMESTAMP(‘2017-09-11 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
  );

添加索引加速这个查询。

1. MEETING_SUBSCRIPTION：
在 SUBSCRIPTION_START_TIME 和 SUBSCRIPTION_END_TIME 列上创建复合索引，以加速时间范围的查询。

CREATE INDEX IDX_MEETING_SUBSCRIPTION_TIME ON MEETING_SUBSCRIPTION (SUBSCRIPTION_START_TIME, SUBSCRIPTION_END_TIME);

2. MEETING_ROOM：
为 MEETING_ROOM_TYPE_ID 列创建索引，以加速与 MEETING_ROOM_TYPE 表的连接。

CREATE INDEX IDX_MEETING_ROOM_TYPE ON MEETING_ROOM (MEETING_ROOM_TYPE_ID);

3. MEETING_ROOM_TYPE：
如果 CAPACITY_FOR_PEOPLE、PROJECTOR、TELEPHONE 和 VISUAL_TELEPHONE 列经常用于查询，考虑在这些列上创建索引。然而，因为这些列的过滤可能比较少，创建单列索引可能带来的收益有限。

CREATE INDEX IDX_MEETING_ROOM_TYPE_CAPACITY ON MEETING_ROOM_TYPE (CAPACITY_FOR_PEOPLE);
CREATE INDEX IDX_MEETING_ROOM_TYPE_PROJECTOR ON MEETING_ROOM_TYPE (PROJECTOR);
CREATE INDEX IDX_MEETING_ROOM_TYPE_VISUAL_TELEPHONE ON MEETING_ROOM_TYPE (TELEPHONE);
CREATE INDEX IDX_MEETING_ROOM_TYPE_VISUAL_TELEPHONE ON MEETING_ROOM_TYPE (VISUAL_TELEPHONE);

还可以考虑创建一个复合索引来覆盖 CAPACITY、PROJECTOR 和 VISUAL_TELEPHONE 列的组合条件。

CREATE INDEX IDX_MEETING_ROOM_TYPE_FILTER ON MEETING_ROOM_TYPE (CAPACITY_FOR_PEOPLE, PROJECTOR, TELEPHONE, VISUAL_TELEPHONE);

c.
可以利用数据库的事务和锁机制:
1. 在查询可以预订的会议室时，使用行级锁定来防止其他事务修改数据。

— 事务开始
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

— 查询可以预订的会议室并锁定行
SELECT m.MEETING_ROOM_ID
FROM MEETING_ROOM m
JOIN MEETING_ROOM_TYPE mrt ON m.MEETING_ROOM_TYPE_ID = mrt.MEETING_ROOM_TYPE_ID
WHERE mrt.CAPACITY_FOR_PEOPLE > 10
  AND mrt.PROJECTOR = ‘Y’
  AND mrt.VISUAL_TELEPHONE = ‘Y’
  AND m.MEETING_ROOM_ID NOT IN (
    SELECT ms.MEETING_ROOM_ID
    FROM MEETING_SUBSCRIPTION ms
    WHERE ms.SUBSCRIPTION_START_TIME IS NOT NULL
      AND ms.SUBSCRIPTION_END_TIME IS NOT NULL
      AND ms.SUBSCRIPTION_START_TIME < TO_TIMESTAMP(‘2017-09-11 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
      AND ms.SUBSCRIPTION_END_TIME > TO_TIMESTAMP(‘2017-09-11 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
  )
FOR UPDATE;

— 插入订阅记录
INSERT INTO MEETING_SUBSCRIPTION (END_USER_ID, MEETING_ROOM_ID, SUBSCRIPTION_START_TIME, SUBSCRIPTION_END_TIME)
VALUES (‘user_id’, ‘meeting_room_id’, TO_TIMESTAMP(‘2017-09-11 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), TO_TIMESTAMP(‘2017-09-11 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’));

— 提交事务
COMMIT;

2.在插入订阅记录时，通过检查冲突来确保没有其他用户在相同时间段预订同一个会议室。

— 事务开始
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

— 检查是否有冲突的预订记录
DECLARE
    conflict_count NUMBER;
BEGIN
    SELECT COUNT(*)
    INTO conflict_count
    FROM MEETING_SUBSCRIPTION ms
    WHERE ms.MEETING_ROOM_ID = 1
      AND ms.SUBSCRIPTION_START_TIME < TO_TIMESTAMP(‘2017-09-11 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)
      AND ms.SUBSCRIPTION_END_TIME > TO_TIMESTAMP(‘2017-09-11 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);
    
    — 如果没有冲突，则插入记录
    IF conflict_count = 0 THEN
      INSERT INTO MEETING_SUBSCRIPTION (END_USER_ID, MEETING_ROOM_ID, SUBSCRIPTION_START_TIME, SUBSCRIPTION_END_TIME)
      VALUES (虚假手机号, 1, TO_TIMESTAMP(‘2017-09-11 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), TO_TIMESTAMP(‘2017-09-11 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’));
    END IF;

END;

— 提交事务
COMMIT;

3. 在插入时检查订阅时间段的重叠，确保没有重复预订。
在 MEETING_SUBSCRIPTION 表中添加唯一约束，防止插入重复的预订记录。

ALTER TABLE MEETING_SUBSCRIPTION
ADD CONSTRAINT UNIQUE_MEETING_SUBSCRIPTION UNIQUE (MEETING_ROOM_ID, SUBSCRIPTION_START_TIME, SUBSCRIPTION_END_TIME);

插入记录时捕获唯一约束的违反错误并处理。

— 事务开始
BEGIN
  INSERT INTO MEETING_SUBSCRIPTION (END_USER_ID, MEETING_ROOM_ID, SUBSCRIPTION_START_TIME, SUBSCRIPTION_END_TIME)
  VALUES (‘user_id’, ‘meeting_room_id’, TO_TIMESTAMP(‘2017-09-11 10:00:00’, ‘YYYY-MM-DD HH24:MI:SS’), TO_TIMESTAMP(‘2017-09-11 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’));
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    — 处理唯一约束违反错误
    DBMS_OUTPUT.PUT_LINE(‘预订冲突，无法预订同一时间段的会议室！’);
END;

实验：

编辑于 2024-08-12 22:14:38