SQLパズル 2問目

1.問題：欠勤

ジム・チャペラが社員の欠勤状況を記録するためのDBを作成しようとしていました．彼が考えていたテーブルは以下の通りです．

CREATE TABLE Absenteeism
(emp_id INTEGER NOT NULL PREFERENCES Personnel(emp_id),
 absent_date DATE NOT NULL,
 reason_code CHAR(40) NOT NULL REFERENCES EcuseList(reason_code),
 severity_points INTEGER NOT NULL CHECK(severity_points BETWEEN 1 AND 4)
 PRIMARY KEY(emp_id ,absent_date)
 );

Absenteeism ：欠勤テーブル
emp_id ：社員ID
absent_date ：欠勤日
reason_code ：理由コード
severity_points：罰点
Personnel：社員テーブル
EcuseList：言い訳テーブル
社員IDと欠勤日合わせての複合キーとなっています．基本的に社員IDは一意であり，理由コードは「気が乗らない」，「さぼりたい」などの欠勤理由，罰点は欠勤に対して与えるペナルティです．ここでの社員IDは外部キーであり、社員テーブルの社員IDが親にあたります。
以下の２つのビジネスルールを実現するSQL文を書くのがこの問題です．

• 社員は罰点を年間40ポイントためるとクビになります．つまり，Personalテーブルから該当する社員レコードが削除されます．
• ただし，2日以上連続して休んだ場合は「長期欠病」扱いとなり，2日目以降の欠勤には罰点がつかず，欠勤の総日数にもカウントされません．

2.導出過程と解答

2.1 社員ごとに40ポイント以上かの判定

社員IDをGROUP BYでまとめてから，HAVING句で40ポイント以上かの確認条件を書けばよいかと思います．ちなみに1年間で40ポイント以上というお話なので先にWHERE句で欠勤日を対象として1年前から今日までに絞ります．

SELECT emp_id
 FROM Absenteeism
 WHERE absent_day BETWEEN SYSDATE - INTERVAL '1' YEAR AND SYSDATE
 GROUP BY emp_id
 HAVING sum(severity_points) >= 40;

2.2 2日以上欠勤している場合の対処

2日目以降に罰点が入っていなければ特に問題ないと思います．つまり，1日目が4点，2日目以降が0点であれば大丈夫です．しかし，severity_pointsの制約を見ると，罰点が1から4点となっています．これでは，0点を入力することができません．そこで，severity_pointsの制約を書き換えます．

severity_points INTEGER NOT NULL CHECK(severity_points BETWEEN 0 AND 4)

これで0点も入力することが可能となります．
ちなみに長期休暇している社員の2日目以降を0点として更新する必要があるのであれば以下のようなUPDATE文が必要となります．

UPDATE Absenteeism AS A1
 SET severity_points = 0, reason_code = 'long term illness'
 WHERE EXISTS
         (SELECT * 
           FROM Absenteeism AS A2
           WHERE A1.emp_id = A2.emp_id AND
                 (A2.absent_day + INTERVAL '1' DAY) = A1.absent_day
           );

これはAbsenteeeismのとある社員の欠勤日に対して次の日も休んでいた場合，その日の罰点と理由コードを更新する処理です．
ただし，金曜日に欠勤して土日も病気で倒れており，次週の月曜日も欠勤した場合は上記の条件にあてはまりません．その場合はユーザ側で欠勤情報を記入する際，土日も欠勤していた扱いにすれば土，日，月曜日に罰点が入ることはありません．

2.3 解答

条件1つ目（2.1 社員ごとに40ポイント以上かの判定）と2つ目（2.2 2日以上欠勤している場合の対処）を踏まえたうえで罰点が40点以上の社員をPersonnelテーブルから削除するSQL文は以下のようになります．

DELETE FROM Personnel
WHERE emp_id =
       (SELECT A1.emp_id
         FROM Absenteeism AS A1
         WHERE absent_day BETWEEN SYSDATE - INTERVAL '1' YEAR AND SYSDATE
         GROUP BY A1.emp_id
         HAVING sum(severity_points) >= 40);

これでPersonnelテーブルの対象社員IDのレコードは削除されますが、Absenteeismテーブルの方には残ってしまいます。なので、子にあたるAbsenteeismの該当社員IDのレコードも削除されるよう制約を書き換えます。

emp_id INTEGER NOT NULL PREFERENCES Personnel(emp_id) ON DELETE CASCADE

これで問題なくAbsenteeismの方も削除されます。