Упражнение 39

Вот пример неправильно понятого условия:

Решение 3.4.1

SELECT DISTINCT ship 
FROM Outcomes os
WHERE EXISTS (SELECT ship
            FROM Outcomes oa
            WHERE oa.ship = os.ship 
                AND result = 'damaged'
            ) 
    AND EXISTS (SELECT SHIP
                FROM Outcomes ou
                WHERE ou.ship=os.ship
                GROUP BY ship
                HAVING COUNT(battle)>1
                );

Это решение исполнено в стиле реляционного исчисления, а именно, разыскиваются такие корабли в таблице Outcomes, которые были повреждены (первый предикат EXISTS) и которые участвовали более чем в одной битве (второй предикат EXISTS).

Ошибка здесь состоит в том, что проигнорировано условие «сохранившиеся для будущих сражений», которое означает, что после битвы, в которой корабль получил повреждение, он принимал участие в более позднем сражении. Таким образом, для получения правильного решения этой задачи нужно анализировать даты сражений, которые содержатся в таблице сражений Battles.

Решение 3.4.2. Тот же результат даст и решение, использующее самосоединение:

SELECT DISTINCT o.ship
FROM Outcomes AS o, Outcomes AS o2
WHERE (o.result = 'damaged' 
       OR o2.result = 'damaged'
       ) 
    AND o.battle <> o2.battle 
    AND o.ship = o2.ship;

Здесь применяется соединение таблицы Outcomes с самой собой при условии, что корабль тот же самый, а битвы разные. Кроме того, в одной из битв корабль был поврежден. Как легко увидеть, отсутствует проверка на более раннюю дату сражения, в котором корабль был поврежден.

Решение 3.4.3

Как это ни покажется странным, но нижеприведенный запрос некоторое время принимался системой проверки.

SELECT s.name
FROM Ships s 
    JOIN Outcomes o ON s.name = o.ship 
    JOIN Battles b ON o.battle = b.name
GROUP BY s.name
HAVING COUNT(s.name) = 2 
    AND (MIN(result) = 'damaged' 
        OR MAX(result) = 'damaged'
        )
UNION
SELECT o.ship
FROM Classes c JOIN
Outcomes o ON c.class = o.ship 
    JOIN Battles b ON o.battle = b.name
WHERE o.ship NOT IN (SELECT name
                    FROM Ships
                    )
GROUP BY o.ship
HAVING COUNT(o.ship) = 2 
    AND (MIN(result) = 'damaged' 
        OR MAX(result) = 'damaged'
        );

Во-первых, объединяются запросы, которые выполняют соединение участвующих в сражениях кораблей (таблица Outcomes) с таблицами Ships и Classes соответственно. Кстати говоря, предикат

o.ship NOT IN (SELECT name FROM Ships)

во втором запросе явно лишний, так как UNION исключит возможные дубликаты.

Эти соединения не просто избыточны, они ошибочны, так как в описании базы данных сказано, что в таблице Outcomes могут быть корабли, отсутствующие в Ships. То есть если найдется не головной корабль, которого нет в таблице Ships и который отвечает условиям задачи, то он не попадет в результирующий набор вышеприведенного запроса.

Во-вторых, предикат

HAVING COUNT (o.ship) = 2

ограничивает возможные варианты только двумя сражениями корабля. А почему корабль не может принимать участие более чем в двух сражениях? Он же не обязательно был потоплен после того, как получил повреждение. Причем он мог участвовать в сражениях и до повреждения (например, с результатом ok). Тогда, если в следующем и последним для корабля сражении он был поврежден, то запрос 3.4.3 выведет этот корабль, хотя это и не отвечает условиям задачи.

В-третьих, автору не вполне понятно условие:

(MIN(result) = 'damaged' OR MAX(result) = 'damaged')

Однако вернемся к условию. В соответствии с описанием предметной области корабль может быть:

• поврежденным (damaged);
• остаться невредимым (ok);
• быть потопленным (sunk).

Поэтому условие MIN(result) = ‘damaged’ будет выполнено, если в одной из битв корабль был поврежден (при естественной сортировке текстовых строк буква «d» идет раньше, чем буквы «o» и «s»). Однако это совсем не означает, что поврежден он был прежде, чем принял участие в следующем сражении, что требуется по условиям задачи. Здесь нужно оценивать даты сражений. Что же касается MAX(result) = ‘damaged’, то это условие не будет выполняться, если результаты сражений были разные; если же они были одинаковые, то это не даст ничего нового по сравнению с первым условием на минимум.

Вот такое наложение ошибок давало правильный результат на обеих проверочных базах. Меры уже приняты: добавлены проверочные данные, на которых данное решение дает неверный результат. Как это и должно быть по логике этого запроса.

ПиР

Решить задачу на SQL-EX.RU