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

Question

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

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。对应的关系模型是否正确？

PILOT([id]、名字、姓氏、...)
接受培训([pilot_id、simulator_id、acquisition_date、expiration_date])
模拟器([id]，名称)

其中pilot_id指的是... blablabla。
方括号定义每个实体的主键。

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 子句，只有在其资格过期时才会显示。

请注意，使用相关子查询是因为，如果飞行员的资格已过期，但后来更新(并且仍然有效)，那么简单地说诸如过期日期小于 NOW() 之类的内容就会失败。

要查看谁将在 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 个月内不合格的飞行员，但现在他们已经合格。