某航空培训中心数据库设计与查询

Question

我在一家为客户租用飞机模拟器会话的公司工作。在进入模拟器之前，飞行员(客户)必须对他将要驾驶的模拟器有一个有效的编队。有效编队是飞行员成功通过且未过期的编队(因此我们必须存储获取日期和到期日期)。

1.我的实体关系模型正确吗？

实体：

• Pilot：pilot_id，first_name，last_name，其他身份字段。
• 模拟器：simulator_id，名称

关系：

• (0，n) 飞行员被训练为(0，n) 模拟器：

复制粘贴在http://mocodo.wingi.net/上的代码以直观地看到它：

DATE: _acquisition_date, _expiration_date

PILOT: id, first_name, last_name
is Trained For, 0n PILOT, 0n SIMULATOR, 0n DATE
SIMULATOR: id, name

不要忘记点击“刷新”图标来查看结果，就在上面

2.对应的关系模型正确吗？

飞行员 (id，first_name，last_name，.)

被训练为 (pilot_id，simulator_id，acquisition_date，expiration_date)

模拟器 (id，name)

pilot_id指的是..。蓝唇。

方括号定义了每个实体的主键。

3.如何回答这些问题？

• 那些不能通过编队的模拟器里的飞行员是谁？
• 哪些飞行员不愿意在x个月内通过编队的模拟器中，而不包括那些已经不太合适的飞行员？

我等待的输出如下所示：

+------------------------------------------------+
|                Inapt pilots                    |
+------------+-----------------+-----------------+
| First name | Last name       | Simulator       |
+------------+-----------------+-----------------+
| John       | Doe             | Falcon 7X       |
| John       | Doe             | Embraer ERJ 140 |
| Foo        | Bar             | Falcon 20       |
+------------+-----------------+-----------------+

给每个机师一个他不能去的模拟器。

Answer 1

首先，注释:您不需要IS_TRAINED_FOR表中的过期日期。飞行员和模拟器ID加上获取日期是足够独特的。

回答你的问题：

1. 是的，您的数据模型可用于指定的目的。
2. 除了我上面的评论外，您的表定义也很好。
3. 您所要求的查询如下所示：

​

-- Get pilots that are no longer qualified for a simulator that they
-- have previously qualified for...
select distinct
  P.first_name
, P.last_name
, S.name
from PILOT P
  inner join IS_TRAINED_FOR T
on P.id = T.pilot_id
  inner join SIMULATOR S
on T.simulator_id = S.id
where NOT EXISTS (select Q.acquisition_date
              from IS_TRAINED_FOR Q
              where Q.acquisition_date <= NOW()
                and Q.expiration_date >= NOW()
                and Q.pilot_id = T.pilot_id
                and Q.simulator_id = T.simulator_id)

​

因为上面的查询使用内部联接，所以只显示曾经为模拟器合格的飞行员，但由于WHERE子句，只有在其限定过期时才会显示它们。

请注意，使用关联子查询的原因是，如果飞行员具有过期的限定条件，但后来被更新(并且仍然有效)，那么只需说像过期日期这样的内容比现在()更短，就会失败。

要查看在X个月中谁将被取消资格，只需在上面的查询中添加X个月到NOW()，如下所示：(在本例中，假设4个月.)

DATE_ADD(NOW(),INTERVAL 4 MONTH)

要查看从现在到x个月之后谁将被取消资格，请使用以下查询：

​

-- Get pilots whose qualifications will be expiring for a simulator that they
-- are currently qualified for...
select distinct
  P.first_name
, P.last_name
, S.name
from PILOT P
  inner join IS_TRAINED_FOR T
on P.id = T.pilot_id
  inner join SIMULATOR S
on T.simulator_id = S.id
where NOT EXISTS (select Q.acquisition_date
              from IS_TRAINED_FOR Q
              where Q.acquisition_date <= DATE_ADD(NOW(),INTERVAL 4 MONTH)
                and Q.expiration_date >= DATE_ADD(NOW(),INTERVAL 4 MONTH)
                and Q.pilot_id = T.pilot_id
                and Q.simulator_id = T.simulator_id)
  and T.acquisition_date <= NOW()
  and T.expiration_date >= NOW()
  and T.expiration_date <= DATE_ADD(NOW(),INTERVAL 4 MONTH

​

第二个查询显示的是在4个月内不合格的飞行员(例如)，他们现在是合格的。