2018-04-21

ER図の記法3選

ER図（実体関連図：Entity-relationship Diagram）は、主にデータベースを扱う際に、データモデルを表現するのに使用します。
ER図の記法はいくつかあるのですが、ここでは、メジャーな３つを紹介します。

後で述べますが、同じER図を描くのに、それぞれの分野で別々の記法を使用しているため、実はこの２つの記法は同じことを示していた（描き方が違うだけ）ということに気付かないことがあります（私がそうでした）。

バックマン線図

1969年にバックマン（C. W. Bachman）が発表した記法*1。
線・矢印を使ったシンプルな記法であり、情報処理技術者試験（応用情報技術者試験等）ではこの記法が使われています。

シンプルであることは欠点でもあり、他の2つの比較して、ぱっと見た時の意味が分からないのが、バックマン線図の欠点です。原著では下図に対して、

The direction of the arrow is read to mean that each employee is a member of a set of employees that belong to a particular department, and further, that each department has such a set of employees.

とあるだけで、ここに矢印を導入した理由や妥当性には触れていません。
基本的には「そういうものだ」と覚えるしかないのです。

Crow's Foot （カラスの足）記法

1980年代に、コンサルティングファームCACIにてバーカー（R. Barker）が考案した記法（バーカーはのちにオラクルに入社し、RDBMSソフトを開発）。
見た目が分かりやすいので、ER図の実践的教育の場面で良く使用されますが、表現できる内容が少ないので、複雑な関係性を表記する際には、この記法はあまり使われません。

クラス図（UML）

UML（統一モデリング言語：Unified Modeling Language）は、オブジェクト指向分析・設計の際に使用される記法群です。UMLの中に色々な記法が含まれており、クラス図はその中のひとつです。

多重度が数字で表記されるのでわかりやすく、また、それ以外の様々な情報を表現することができます。しかし、ER図としては機能が過剰であるので、ER図として用いられることはあまりありません。

図の転載元

*1:Data structure diagrams：http://citeseerx.ist.psu.edu/viewdoc/summary?doi=10.1.1.172.3370

2018-04-07

応用情報技術者試験「データベース」に出てきたSQLを眺めてみる

応用情報技術者試験

ONの後ろが空欄

ONの後ろの空欄は、「○○.●● = △△.●●」の形の条件文が入ります。
例1) 平成29年春期

SELECT 申請書.申請書ID,
       申請書.タイトル,
       申請書.申請日
       ユーザ.ユーザ名,
       部署マスタ.部署名
FROM 申請書
    INNER JOIN 承認申請 ON 申請書.申請書ID = 承認申請.申請書ID
    INNER JOIN ユーザ ON 申請書.申請者ユーザID = ユーザ.ユーザID
    INNER JOIN 部署マスタ ON ユーザ.部署ID = 部署マスタ.部署ID
WHERE (承認申請.承認申請状態 NOT IN ('可決', '否決'))
      AND
      ((申請書.申請者ユーザID = :ユーザID)
       OR
       (申請書.申請書ID
        IN (SELECT DISTINCT 申請書ID
            FROM 承認者情報
                --この行のON以降が空欄
                INNER JOIN 承認申請 ON 承認者情報.承認申請ID = 承認申請.承認申請ID
            WHERE 承認者情報.承認者ユーザID = ：ユーザID )
      ))

SELECT 申請書.申請書ID, 申請書.タイトル, t1.項目値 AS 金額, t2.項目値 AS 支払日
                                         --下の2行のON以降が共に空欄
    FROM 申請書 INNER JOIN 申請書項目 t1 ON 申請書.申請書ID = t1.申請書ID
                INNER JOIN 申請書項目 T2 ON 申請書.申請書ID = t2.申請書ID
    WHERE
    ((申請書.書式ID = '購買' AND t1.項目キー = 'amount' AND t2.項目キー = 'pay_data') OR
     (申請書.書式ID = '契約' AND t1.項目キー = 'pay_initial' AND t2.項目キー = 'start_date'))
    AND (承認申請.申請書ID = 申請書.申請書ID AND 承認申請.承認申請状態 = '可決')

例2) 平成23年秋期

SELECT 明細行番号, 日付, 費用種別.費用種別名, 金額
FROM 申請明細 INNER JOIN 費用種別
    ON 申請明細.費用種別コード = 費用種別.費用種別コード    --この行のON以降が空欄
WHERE 申請明細.申請書番号 = :申請書番号 AND 費用種別.証憑フラグ = 'Y'
ORDER BY 明細行番号

GROUP BYの後ろが空欄

GROUP BYの後ろの空欄には、SELECTの後ろに続く項目名が入ります。
例1）平成29年秋期

SELECT 品名コード, 品目名, 産地コード, 産地名,
SUM(t2.単価 * t1.パレット数) AS 合計返品金額, SUM(t1.パレット数) AS 合計返品数量
    FROM 返品 t1
        INNER JOIN 販売明細 t2 USING (販売番号, 販売明細番号)
        INNER JOIN 仕入明細 USING(仕入番号，仕入明細番号)
        INNER JOIN 品目 USING (品目コード)
        INNER JOIN 産地 USING (産地コード)
    WHERE 返品日 = CURRENT_DATE
    GROUP BY 品目コード，品目名，産地コード，産地名    --この行のGROUP BY以降が空欄
    ORDER BY 品目コード ASC, 産地コード ASC

COALESCEを含むSELECT文

COALESCE()関数が出てきたら、検索結果にNULLが含まれる場合があることを意味します。
この時、多くの場合外挿（OUTER JOIN）が行われていています。
例1) 平成28年春期

--このSELECT文にはCOALESCE()がある
SELECT ST.確認年月日, ST.店舗ID, ST.商品ID, COALESCE(SS.日間販売数量, 0),
    ST.日間在庫数量
FROM
    (SELECT SC.確認年月日, SC.店舗ID, SC.商品ID,
        AVG(SC.在庫数量) AS 日間在庫数量
     FROM 在庫 SC
     GROUP BY SC.確認年月日, SC.店舗ID, SC.商品ID) ST
     LEFT OUTER JOIN    --ここにLEFT OUTER JOINがいる
    (SELECT SL.販売年月日, SL.店舗ID, SD.商品ID,
        SUM(SD.販売数量) AS 日間販売数量
     FROM 販売 SL
        INNER JOIN 販売詳細 SD ON SL.販売ID = SD.販売ID
     GROUP BY SL.販売年月日, SL.店舗ID, SD.商品ID) SS
      ON ST.確認年月日 = SS.販売年月日
         AND ST.店舗ID = SS.店舗ID
         AND ST.商品ID = SS.商品ID

SELECT SF.売上年月, SF.店舗ID, IT.商品分別ID,
    AVG(SF.日間販売数量) AS 平均販売数量, AVG(SF.日間在庫数量) AS 平均在庫数量
FROM
    (SELECT TO_YYYYMM(SA.売上年月日) AS 売上年月, SA.店舗ID, SA.商品ID,
        SA.日間販売数量, SA.日間在庫数量
     FROM 売上ファクト SA) SF
    INNER JOIN 商品 IT ON SF.商品ID = IT.商品ID
GROUP BY SF.売上年月, SF.店舗ID, IT.商品分類ID
ORDER BY SF.売上年月 DESC，SF.店舗ID ASC，平均在庫数量 DESC

例2) 平成25年春期

INSERT INTO 発言キーワード重み (キーワードID, 重み)
--このSELECT文にはCOALESCE()がある
SELECT キーワード.キーワードID, COALESCE(OMOMI.CNT,0)
FROM キーワード
    LEFT OUTER JOIN    --ここにLEFT OUTER JOINがいる（ここは空欄であった）
    (SELECT 発言キーワード.キーワードID, COUNT(*) AS CNT
     FROM 発言キーワード
        INNER JOIN 発言 ON 発言.発言ID = 発言キーワード.発言ID
     WHERE 発言.社員番号 IS NULL
     GROUP BY 発言キーワード.キーワードID) OMOMI
    ON キーワード.キーワードID = OMOMI.キーワードID


SELECT 発言.発言ID, SUM(発言キーワード重み.重み) AS WEIGHT
FROM 発言
    INNER JOIN 発言キーワード ON 発言.発言ID = 発言キーワード.発言ID
    INNER JOIN 発言キーワード重み
        ON 発言キーワード.キーワードID = 発言キーワード重み.キーワードID
WHERE 発言.否定的フラグ = '1'
GROUP BY 発言.発言ID
ORDER BY WEIGHT DESC

平成28年秋期

SELECT t1.会員番号, t1.氏名, t6.商品分類番号,
    t6.商品分類名, SUM(t4.商品単価 * t4.個数) AS 購入金額合計
FROM 会員 t1
    INNER JOIN (SELECT t2.購入番号, t2.会員番号
        FROM 購入 t2 WHERE t2.購入日時 > :一年前) t3 ON t1.会員番号 = t3.会員番号
    INNER JOIN 購入明細 t4 ON t3.購入番号 = t4.購入番号
    INNER JOIN 商品 t5 ON t4.商品番号 = t5.商品番号
    INNER JOIN 商品分類 t6 ON t5.商品分類番号 = t6.商品分類番号
GROUP BY t1.会員番号, t1.氏名, t6.商品分類番号, t6.商品分類名

DECLARE cur CURSOR FOR
    SELECT t2.会員番号, t2.購入番号, t2.購入金額
    FROM 購入 t2
    WHERE t2.購入ステータス = '完了'
    AND t2.購入日時 <= :判定対象期限
    AND t2.判定処理状態 <> '判定処理済み'
    ORDER BY t2.会員番号，t2.購入日時 ;
UPDATE 会員 t1 SET t1.会員種別 = '一般会員';
SET current_kaiin_no = 0;
SET goukei = 0;
OPEN cur;
fetch_loop: LOOP
    FETCH cur INTO kaiin_no, kounyu_no, kounyu_kingaku;
    IF kaiin_no <> current_kaiin_no THEN
        SET current_kaiin_no = kaiin_no;
        SET update_flag = 0;
        SET goukei = 0;
    END IF;
    IF update_flag = 0 THEN
        SET goukei = goukei + kounyu_kingaku;
        UPDATE 購入番号 = kounyu_no;
            WHERE t2.購入番号 = kounyu_no;
        IF goukei >= 50000 THEN
            UPDATE 会員 t1 SET t1.会員種別 = '特別会員' WHERE t1.会員番号 = kaiin_no;
            SET update_flag = 1;
        END IF;
    ELSE
        UPDATE 購入 t2 SET t2.判定処理状態 = '繰越し' WHERE t2.購入番号 = kunyu_no;
    END IF;
END LOOP fetch_loop;
CLOSE cur;

平成27年秋期

WITH RECURSIVE 関連部署(部署ID, 部署名, 上位部署ID) AS (
    SELECT 部署.部署ID, 部署.部署名, 部署.上位部署ID
        FROM 部署 WHERE 部署.上位部署ID IS NULL
            AND :年月日 BETWEEN 部署.適用開始年月日 AND 部署.適用終了年月日
UNION ALL
    SELECT 部署.部署ID, 部署.部署名, 部署.上位部署ID
        FROM 部署, 関連部署 WHERE 部署.上位部署ID = 関連部署.部署ID
            AND :年月日 BETWEEN 部署.適用開始年月日 AND 部署.適用終了年月日
)
SELECT 部署ID, 部署名, 上位部署ID FROM 関連部署

平成27年春期

SELECT AC.*
FROM アクセスログ AC
WHERE EXISTS
    (SELECT * FROM 非営業日 NS
     WHERE AC.操作年月日 = NS.非営業年月日)

SELECT AC.*
FROM アクセスログ AC
    INNER JOIN 利用者 US ON AC.利用者ID = US.利用者ID
    INNER JOIN サーバ SV ON AC.サーバID = SV.サーバID
WHERE AC.操作結果 = 'F'
    AND US.部署ID <> SV.部署ID

平成26年春期

SELECT COUNT(*) AS 判定結果
FROM Webユーザ
WHERE ユーザID = :ユーザID
    AND パスワードハッシュ値 = HASH(:パスワード)

SELECT A.船便番号, A.船便名, C.座席クラス番号, C.座席クラス名,
    COUNT(*) AS 空席数
FROM 船便 A
    INNER JOIN 座席クラス C ON B.座席クラス番号 = C.座席クラス番号
WHERE A.出発日 = :出発日
  AND A.出発地 = :出発地
  AND A.到着地 = :到着地
  AND B.空席状況 = '0'
GROUP BY A.船便番号，A.船便名，C.座席クラス番号，C.座席クラス名

平成25年秋期

SELECT タイトルマスタ.タイトル, 会員マスタ.会員ID, 会員マスタ.氏名,
    会員マスタ.連絡先, 貸出予約.予約日時
FROM (タイトルマスタ
    INNER JOIN 貸出予約 ON タイトルマスタ.タイトルID = 貸出予約.タイトルID)
    INNER JOIN 会員マスタ ON 貸出予約.会員ID = 会員マスタ.会員ID
WHERE タイトルマスタ.タイトルID = :予約タイトルID
    AND 貸出予約.予約日時 = (SELECT MIN(貸出予約.予約日時) FROM 貸出予約
                             WHERE 貸出予約.タイトルID = :予約タイトル
                               AND 貸出予約.予約ステータス = '予約中')

平成24年春期

CREATE VIEW 統合検索
    (書籍名, 著者名, 出版社名, ISBN番号, 図書館名, 貸出可能冊数) AS
SELECT 書籍A.書籍名, 書籍A.著者名, 書籍A.出版社名, 書籍A.ISBN番号,
    'A市図書館', COUNT(書籍A.書籍番号) FROM 蔵書A, 書籍A
WHERE 蔵書A.書籍番号 = 書籍A.書籍番号
    AND 蔵書A.蔵書番号 NOT IN (SELECT 貸出記録A.蔵書番号 FROM 貸出記録A
                               WHERE 貸出記録A.返却日 IS NULL)
GROUP BY 書籍名, 著者名, 出版社名, 書籍A.ISBN番号
UNION
SELECT 書籍B.書籍名, 書籍B.著者名, 書籍B.出版社名, 書籍B.ISBN番号,
    'B市図書館', COUNT(書籍B.ISBN番号) FROM 蔵書B, 書籍B
WHERE 蔵書B.ISBN番号 = 書籍B.ISBN番号
    AND 蔵書B.蔵書番号 NOT IN (SELECT 貸出記録B.蔵書番号 FROM 貸出記録B
                               WHERE 貸出記録B.返却日 IS NULL)
GROUP BY 書籍名, 著者名, 出版社名, 書籍B.ISBN番号


CREATE VIEW 統合貸出予約
    (蔵書番号, 書籍名, 著者名, 出版社名, ISBN番号, 図書館名, 貸出状況) AS
SELECT 蔵書A.蔵書番号, 書籍A.書籍名, 書籍A.著者名, 書籍A.出版社名,
    書籍A.ISBN番号, 'A市図書館', 蔵書A.貸出状況) FROM 蔵書A, 書籍A
WHERE 蔵書A.書籍番号 = 書籍A.書籍番号
    AND 蔵書A.貸出状況 = '貸出可'
UNION
SELECT 蔵書B.蔵書番号, 書籍B.書籍名, 書籍B.著者名, 書籍B.出版社名,
    書籍B.ISBN番号, 'B市図書館', 蔵書B.貸出状況) FROM 蔵書B, 書籍B
WHERE 蔵書B.ISBN番号 = 書籍B.ISBN番号
    AND 蔵書B.貸出状況 = '貸出可'

平成23年秋期

SELECT 明細行番号, 日付, 費用種別.費用種別名, 金額
FROM 申請明細 INNER JOIN 費用種別
    ON 申請明細.費用種別コード = 費用種別.費用種別コード
WHERE 申請明細.申請書番号 = :申請書番号 AND 費用種別.証憑フラグ = 'Y'
ORDER BY 明細行番号


SELECT 申請書.組織コード, 組織.組織名,
    SUM (申請明細.金額) AS 組織交通費合計
FROM 申請書, 申請明細, 組織
WHERE 申請明細.日付 BETWEEN :指定月開始日 AND :指定月終了日
    AND 申請書.申請書番号 = 申請明細.申請書番号
    AND 申請書.組織コード = 組織.組織コード
GROUP BY 申請書.組織コード, 組織.組織名
ORDER BY 申請書.組織コード

平成23年特別

CREATE TABLE 新刊 (書籍番号 INTEGER, ジャンル INTEGER,
    PRIMARY KEY(書籍番号),
    FOREIGN KEY(書籍番号)
    REFERENCES 書籍(書籍番号))

DELETE FROM 新刊;
INSERT INTO 新刊 (書籍番号, ジャンル)
    SELECT 書籍番号, ジャンル FROM 書籍 WHERE 発行年月日 + 60 > :今日;


CREATE TABLE 購入傾向
    (顧客番号 INTEGER, ジャンル INTEGER, 購入冊数 INTEGER,
     PRIMARY KEY(顧客番号, ジャンル),
     FOREIGN KEY(顧客番号)
     REFERENCES 顧客(顧客番号))

DELETE FROM 購入傾向;
INSERT INTO 購入傾向 (顧客番号, ジャンル, 購入冊数)
    SELECT 注文.顧客番号, 書籍.ジャンル, SUM(注文明細.数量)
    FROM 注文, 注文明細, 書籍
    WHERE 注文.注文年月日＋180＞:今日
    AND 注文.注文番号 = 注文明細.注文番号
    AND 注文明細.書籍番号 = 書籍.書籍番号
    GROUP BY 注文.顧客番号, 書籍.ジャンル;


SELECT 書籍.書籍番号, 書籍.書籍名, 書籍.書籍説明 FROM 書籍, 新刊, 購入傾向
    WHERE 書籍.書籍番号 = 新刊.書籍番号
    AND 購入傾向.顧客番号 = :顧客番号
    AND 書籍.ジャンル = 購入傾向.ジャンル
    AND 購入傾向.購入冊数 * 10 > :購入総冊数


UPDATE 購入傾向 SET 購入冊数 =
    (SELECT 購入傾向.購入冊数 + 注文明細.数量 FROM 注文明細, 書籍
     WHERE 注文明細.注文番号 = :注文番号 AND 注文明細.注文明細番号 = :注文明細番号
     AND 注文明細.書籍番号 = 書籍.書籍番号 AND 書籍.ジャンル = 購入傾向.ジャンル)
WHERE 購入傾向.顧客番号 = :顧客番号
AND 購入傾向.ジャンル IN
    (SELECT 書籍.ジャンル FROM 注文明細, 書籍
     WHERE 注文明細.注文番号 = :注文番号 AND 注文明細.注文明細番号 = :注文明細番号
     AND 注文明細.書籍番号 = 書籍.書籍番号)

平成22年秋期

CREATE TABLE 請求
(請求書番号 CHAR(5),
顧客番号 CHAR(5), 請求日 CHAR(8), 計上年月 CHAR(6), 請求額 NUMERIC(10),
買上額 NUMERIC(10), 消込額 NUMERIC(10),
PRIMARY KEY (請求書番号),
FOREIGN KEY(顧客番号) REFERENCES 顧客(顧客番号))


SELECT 顧客番号, SUM(入金額-消込額)
    FROM 入金
    WHERE 入金日　<= :今月度末日
      AND 入金額 > 消込額
    GROUP BY 顧客番号

平成22年春期

SELECT t.店舗番号, t.店舗名, SUM(m.受注金額) AS 金額
FROM (店舗 t INNER JOIN (SELECT j.受注店舗番号, j.受注番号 FROM 受注 j
           WHERE j.受注日付 BETWEEN :指定月開始日 AND :指定月終了日) p
           ON t.店舗番号 = p.受注店舗番号)
          INNER JOIN 受注明細 m ON p.受注番号 = m.受注番号
GROUP BY t.店舗番号, t.店舗名
ORDER BY t.店舗番号


SELECT m.出荷店舗番号, SUM(m.受注金額) AS Web売上分
FROM 受注明細 m, 受注 j
WHERE (j.受注店舗番号 = 'A09999' ) AND ( j.受注番号 = m.受注番号 ) AND
      (j.受注日付 BETWEEN :指定月開始日 AND :指定月終了日)
GROUP BY m.出荷店舗番号
ORDER BY m.出荷店舗番号


SELECT t.店舗番号, t.店舗名, SUM(m.受注金額) AS 金額
FROM 店舗 t LEFT OUTER JOIN
    (SELECT j.受注店舗番号, j.受注番号 FROM 受注 j
        WHERE j.受注日付 BETWEEN :指定月開始日 AND :指定月終了日) p
            INNER JOIN 受注明細 m ON p.受注番号 = m.受注番号
    ON t.店舗番号 = p.受注店舗番号
GROUP BY t.店舗番号, t.店舗名
ORDER BY t.店舗番号

平成21年春期

SELECT TempTbl.商品番号, SUM(TempTbl.小計)
FROM (SELECT 注文明細.商品番号, 注文明細.数量 AS 小計
      FROM 注文明細
          INNER JOIN 商品 ON 注文明細.商品番号 = 商品.商品番号
      WHERE 注文明細.注文番号 = :注文番号
          AND 商品.セットフラグ = '0'
      UNION ALL
      SELECT セット商品.子商品番号 AS 商品番号,
               セット商品.数量 * 注文明細.数量 AS 小計
      FROM 注文明細
          INNER JOIN 商品 ON 注文明細.商品番号 = 商品.商品番号
          INNER JOIN セット商品 ON 商品.商品番号 = セット商品.セット商品番号
      WHERE 注文明細.注文番号 = :注文番号
          AND 商品.セットフラグ = '1') TempTbl
GROUP BY TempTbl.商品番号

SELECT DISTINCT 注文明細.注文明細番号, 注文明細.商品番号, 商品.商品名,
    注文明細.数量, 注文明細.販売単価, 注文明細.数量 * 注文明細.販売単価,
    CASE WHEN 新商品モデル.新商品番号 IS NOT NULL THEN '新モデルあり'
    ELSE '' END
FROM 注文明細
    LEFT OUTER JOIN 新商品モデル
        ON 注文明細.商品番号 = 新商品モデル.旧商品番号
    INNER JOIN 商品 ON 注文明細.商品番号 = 商品.商品番号
WHERE 注文明細.注文番号 = :注文番号
ORDER BY 注文明細.注文明細番号

2018-03-04

SQLの構文を整理する（その①：SELECT構文）

応用情報技術者試験

応用情報技術者試験の午後の問題でデータベースを選ぼうと計画していますが、
（どの分野が得点しやすいのかざっと調べたところ、
　データベースが取りやすそうだったという理由です）
SQLは実務で使ったこともないのでサッパリです。

なので、まずは構文を覚えようかなと思い、整理してみます。

SELECT [フィールド] FROM [テーブル]

[テーブル]の中の[フィールド]だけを抽出する構文
[フィールド]はカンマで区切ることで、複数指定することができる

SQLZOO SELECT basicsの1.の回答欄に、
以下のように入力し「Submit SQL」をクリックしてみましょう。

SELECT population
FROM world

すると、すべての国の「population」のフィールドが表示されます。

今度は以下のように入力し「Submit SQL」をクリックしてみましょう。

SELECT population, gdp
FROM world

すると、すべての国の「population」と「gdp」のフィールドが表示されます。

SELECT [フィールド] FROM [テーブル] WHERE [条件]

選択した[フィールド]の中で、[条件]を満たすものだけを表示する

ちなみに、"where"という英単語は「どこ」という意味の他、
接続詞として使うと「〜において」、「ここで、〜とする」という意味になります。
SQLZOO SELECT basicsの1.の回答欄に、
以下のように入力し「Submit SQL」をクリックしてみましょう。

SELECT population
FROM world
WHERE name = 'Germany'

すると、「population」のフィールドのうち、「name」が「Germany」のものだけが表示されます。

先ほどと同様、複数のフィールドを選択することもできます。

SELECT population, gdp
FROM world
WHERE name = 'Germany'

条件式には、AND, OR, NOTなどが使用できます。

SELECT population, gdp
FROM world
WHERE name = 'Germany' OR name = 'France'

と入力すれば、「Germany」と「France」の「population」「gdp」を表示することができます。
しかし、上記の例のように、同じフィールドに対し複数の条件を設定する場合は、
INを利用した方が楽です。
SQLZOO SELECT basicsの2.の回答欄に、
以下のように入力し「Submit SQL」をクリックしてみましょう。

SELECT name, population
FROM world
WHERE name IN ('Sweden', 'Norway', 'Denmark')

すると、「population」のフィールドのうち、
「name」が「Sweden」「Norway」「Denmark」のものが表示されます。
（この例では一緒に「name」も表示しています。

また、数字の範囲指定には、各種等号・不等号（=, <, >, <=, >=, <>*1）の他、
BETWEENも使用できます。
SQLZOO SELECT basicsの3.の回答欄に、
以下のように入力し「Submit SQL」をクリックしてみましょう。

SELECT name, area
FROM world
WHERE area BETWEEN 200000 AND 250000

すると、「area」が20万以上25万以下の「name」と「area」が表示されます。

*1:≠を意味する記号として、<>の他に!=も使用できます。

2018-02-22

不正アクセスの単語整理

応用情報技術者試験

セキュリティはAPでは午後必須科目ですから、
絶対に覚えたいところですね、17個もありますけど。

ちなみに、その17個は下記参考書に則って選択しています。
「平成29・30年版ニュースペックテキスト応用情報技術者」TAC出版

ソーシャルエンジニアリング

「ソーシャル」と聞くとどうしても「社会的な」と翻訳してしまいますが、
元々"social"という英語は「人のあつまりの」という意味がありますから*1、
ここから「人を介した（不正アクセス）技術」という意味でこの言葉が使われています。
総務省のHPによれば、

電話でパスワードを聞き出す
肩越しにキー入力を見る（ショルダハッキング）
ごみ箱を漁る（トラッシング）

が具体例として挙げられていますが、他にも手口は色々はあります。

キーロガー

キー入力の記録（ログ）を取ることで、パスワードなどの機密情報を盗み出す手法です。

バックドア

"back door"は「裏口」という意味です。
バックドアという不正アクセスは、

まず攻撃対象のパソコン・サーバに裏口を作るソフトを忍ばせ
そのソフトが実行されることで実際に裏口が作られ
その裏口から攻撃を受ける（データの盗み出しなど）

という手口です。

フットプリンティング

直訳すれば「足跡をつけること」であり、そこから、攻撃対象への事前調査を意味します。
whois情報等を調べて、攻撃対象のセキュリティシステムを推測したり、
CGIなどのWebサービスに悪意のある入力を与えた時の反応を見て、
ソフトウェアの弱点を推測したりする手口です。

ブルートフォース攻撃

"brute-force"に「強引な」という意味があります。
このうち"brute"は「獣の」という意味を持っていますが、ややマイナスな印象を持っており、
「野蛮な」「知性の低い」というニュアンスがあります。
総当たり攻撃などが分類されます。

パスワードリスト攻撃

名前の通り、攻撃者が別途入手したパスワードのリストを元に、
パスワードをこじ開けようとする手口です。
パスワードの流用を避ける理由は、パスワードリスト攻撃を回避するためです。

SQLインジェクション

"injection"は「挿入」という意味です。
次の項目のコマンドインジェクションもそうですが、
検索単語が入力されることが期待されているところに、SQLの命令文を混ぜて入力することで、
SQLが想定しない出力をさせて、不正に情報を得る手口のこと。
IPAのHPにも同様の事例が紹介されています。

コマンドインジェクション

上記のSQLインジェクションのOSコマンド版です。
具体的には、直接OSコマンドを実行できるシェルスクリプトに対して、
キーワード等が入力されることを期待差異れているところに、OSコマンドを混ぜて入力することで、
シェルスクリプトが想定しないOSコマンドを実行し、
不正に情報得たり、データ破壊を行ったりすることです。
IPAのHPでは、コマンドインジェクションで乗っ取ったサーバを経由して、
第三者のサーバを攻撃する事例が紹介されています。

クロスサイトスクリプティング

手口としては、上述のSQLインジェクションやコマンドインジェクションと似ています。
「クロスサイト」とは「２つのサイトを跨いで」という意味であり、ここでは、

不正なスクリプト（命令）を受け付けると、問題のある動作をしてしまうWebサイト
不正なスクリプトのリンクが貼られているサイト（掲示板サイトなど）

の２つが登場します。そして、ユーザが2.の方のサイトでリンクをクリックしてしまうと、
ユーザが意図せず、問題のあるWebサイトに不正なスクリプトを送ってしまい、
その結果として、ユーザが偽のWebサイトに誘導され、個人情報等をだまし取られてしまう、
というのが代表的な手口です。
IPAのHPで図付きで解説されていますが、私は余りピンときませんでした。

クロスサイトリクエストフォージェリ

"forgery"は「偽造」という意味で、「リクエストフォージェリ」で
「サーバへのリクエストを偽造する」ということを指しています。
登場するのは、上述のクロスサイトスクリプティングと似ていて、

偽造が容易なリクエストを受け付ける、問題のあるWebサイトサーバ
不正なリクエストを含むURLのリンクが貼られているサイト（掲示板サイトなど）

の２つです。Webサイトサーバへのリクエストは、
本来ユーザが正規の方法で（Webサイト上の操作で）送信しますが、
これを、不正なリクエストを含むURLのリンクをユーザが踏むことで、
ユーザが意図せず、Webサイトサーバにリクエストを送信してしまい、
それを偽造されたリクエストと見抜けないWebサイトサーバが、
ユーザからの意図したリクエストと判断し、それに従った処理をしてしまう、
というのが、クロスサイトリクエストフォージェリの流れです。
これも、IPAのHPに事例が載っています。

ゼロデイ攻撃

ソフトウェアはリリース後に脆弱性が明らかになることがあります。
通常は、脆弱性が発見されるとすぐにその箇所を修正するソフトが配布され、
その配布日をワンデイと呼びます。
ゼロデイ攻撃は、ワンデイより前にその脆弱箇所を突いた攻撃のことです。

ディレクトリトラバーサル攻撃

「ディレクトリ」は「フォルダ」とも呼ばれる、
ファイルを（仮想的に）分類・格納するシステムです（と今更説明するまでもないですが）。
"traversal"は「横断する」という意味で、「ディレクトリトラバーサル」とはつまり、
本来は横断されないはずのディレクトリへ不正に横断され、
外部からのアクセスが想定されていないデータへアクセスされてしまう攻撃です。
日経のHPが分かりやすいと思います。

DoS攻撃

"DoS"とは"Denial of Service"の略語で、「サービス妨害」という意味です。
昔からあるサーバ攻撃手法で、大量のリクエストをサーバに送り付ける等の方法で、
サーバ処理負荷を上昇させ、処理を遅くしたり、サーバをダウンさせたりする手口です。
情報を盗み出すというよりも、サービスの妨害やシステムの破壊を目的とした不正アクセスです。

フィッシング

元々は「釣り」を意味する"fishing"から来た言葉ですが、
「偽のWebサイトへ誘導して、個人情報等を盗み取る」という意味から
"phishing"（発音は同じ）と綴られることが多いです。

IPスプーフィング

"spoof"は「だます、ひっかける」という意味ですが、インターネットセキュリティの分野では
「なりすます」という意味を帯びます。
IPスプーフィングとは、IPアドレスをなりすますことで、攻撃をしやすくする手口です。
具体的には、あるサーバに対して、あたかも内部からのアクセスかのようにIPを偽造して
アクセスすることで、相手の防御システムをかいくぐる方法です。

セッションハイジャック

ユーザとサーバとの通信のやり取りを「セッション」と言います。
セッションハイジャックとは、このやり取りに紛れて、サーバに不正な操作を行う攻撃です。

DNSキャッシュポイズニング

DNSはURLをIPアドレスに変換する（名前解決する）サーバです。
DNSはこの変換を行うために、URLとIPアドレスの対応リストを持っていますが、
このリストにないURLを変換する時や、定期的なリスト更新のために、
別のDNSに対してIPアドレスを問い合わせることをします。
この機能を悪用して、

攻撃者がDNSに対して、DNSが持っていないだろうURLの名前解決をリクエストする
DNSが他のDNSに対して、同様のリクエストをする
その回答が送られてくる前に、攻撃者が偽のIPアドレスの回答を送り付ける
それを正規の回答と勘違いし、自分のリストに追加してしまう

というのが、DNSキャッシュポイズニングです。
"poisoning"は「毒を盛る」という意味です。
この攻撃を受けたDNSを使うと、該当するURLに対して偽のIPアドレスを回答してしまうので、
ユーザは正しいURLを入力したにも関わらず、偽のWebサイトに誘導されてしまうのです。

*1:そして、元々は「村の祭り」という意味でしかなかった「社会」という漢語を、"society"の訳語として日本人が当てたのです。

2018-02-17

原点を通る3次関数の接線の式を代数的に求めてみる

その他

3次関数の接線に関する問題は、接点の座標を使って接線の関数を立て、
その接線が3次関数上にない決められた点を通るので…という流れで解くのがセオリーです。
微分を用いた、いわゆる「逆手流」の解き方ですね。

3次関数 $y=px^3+qx^2+rx+s$ の接線が点 $(a,b)$ を通るときの、
接線の式を求めよ、という問題は、
接点の $x$ 座標を $t$ と置くことで、接線の式を
$y=(3pt^2+2qt+r)(x-t)+pt^3+qt^2+rt+s$ と表せるので、
これが点 $(a,b)$ を通るということは、
$b=(3pt^2+2qt+r)(a-t)+pt^3+qt^2+rt+s$ が成り立つので…という流れで、
接点の $x$ 座標を $t$ を求め、そこから接線の方程式を求める、
という方法です。

点 $(a,b)$ がたとえ原点、つまり点 $(0,0)$ であっても同様であり、
某有名予備校の某講師曰く、
「ここ（＝点 $(a,b)$ ）が原点だとぉ、誘惑に負けて
　 $y=mx$ なんて置いちゃう輩がいるけど、駄目だよぉ！」
というやつです。

じゃぁこれを敢えて $y=mx$ と置いて解こうとするとどうなるのか、
ちょっとやってみようと思います。

カルダノ＝タリタリアの公式

カルダノ＝タリタリアの公式（単にカルダノの公式とも）とは、
3次方程式の解の公式です。
中学・高校で、2次方程式 $ax^2+bx+c=0$ の解の公式
$x=\frac{-b\pm\sqrt{b^2-4ac}}{2a}$
を習いましたが、これの3次方程式バージョンです。

この公式自体については、他の書籍やWebサイトに譲りますが、
導出までの手順としては、

3次方程式 $ax^3+bx^2+cx+d=0$ を立体完成する
$x+\frac{b}{3a}=X$ と書き換える
更に $X=u+v$ で置き換える
書き換えた $u$ 、 $v$ の式は $u$ 、 $v$ の恒等式であることを利用して、 $u^3+v^3$ 、 $u^3v^3$ の値を求める
2次方程式の解と係数の関係を用いて、 $u^3$ 、 $v^3$ の値を求める
$u$ と $v$ との関係に注意して、3つの $u$ 、 $v$ の組を求める
ここから $X$ の値を算出し、最後に $x$ の値を算出する

という流れです。

今回は、この流れを途中まで利用して、
先の問題、「接線の式を敢えて $y=mx$ と置いて解いてみる」をやってみます。

方針

接線 $y=mx$ が3次関数 $y=f(x)$ と接しているということは、
これを連立させることで得られる3次方程式 $f(x)-mx=0$ が
重解を持っているということになります。

ところで、2次以上の方程式が複素数を解に持っている場合、
その共役の複素数も解であるという決まりがありますから、
3次方程式が重解を持っている、という時点で

その重解は実数である
残りの解も実数である

ことが判明し、3つの実数解（うち2つは重解）を持っていると言うことができます。

このこと踏まえて、先ほどのカルダノ＝タルタリアの公式の流れを進むと、
5.の「 $u^3$ 、 $v^3$ を求める」タイミングで、これが重解である必要が出てきます。
これを使って、 $m$ の値を求めます。

例題

例題として、次の問題を解いてみます。

3次関数 $y=x^3-3x^2-1$ の接線が原点を通るとき、この接線の関数を求めよ

普通に導関数を用いて答えを求めると、
（過程は省略しますが） $y=-3x$ 、 $y=\frac{15}{4}x$ が正解です。

回答

3次関数の接線として、 $y$ 軸に平行なものは存在しないので、
原点を通る直線の関数は実数 $m$ を用いて $y=mx$ と置ける。

これが3次関数 $y=x^3-3x^2-1$ に接しているので、
この2式を連立させ $y$ を消去することで得られる
3次方程式 $x^3-3x^2-mx-1=0$ が実数の重解を持つことになる。

ここで、この3次方程式を以下のように変形する：
$x^3-3x^2-mx-1=0$
$(x-1)^3-3x-mx=0$
$(x-1)^3-(m+3)(x-1)-(m+3)=0$

ここで $x-1=X$ と置くと、
$X^3-(m+3)X-(m+3)=0$ となり、
この $X$ の3次方程式が実数の重解を持つこととなる。

更に、 $X=u+v$ と置く。
すると、この3次方程式の解の一つの値に対して、自由に $u$ を定めることができ、
それに対応して $v$ が定まることになる。
すなわち、 $X=u+v$ で置き換えた
$(u+v)^3-(m+3)(u+v)-(m+3)=0$ は、 $u$ 、 $v$ の恒等式である。

上式を変形すると、
$u^3+v^3+\{3uv-(m+3)\}(u+v)-(m+3)=0$ となるが、
式中の $u+v$ について、

1) $u+v=0$ の場合
$u=-v$ であるので、この式は $m=-3$ となる。
なお、この時 $X=0$ 、 $x=1$ であるから、
重解を持つとした3次方程式に代入すると、こちらも $m=-3$ となり矛盾がない。

2) $u+v\neq0$ の場合
$u^3+v^3+\{3uv-(m+3)\}(u+v)-(m+3)=0$ が $u$ 、 $v$ の恒等式であるので、
$u^3+v^3=m+3$
$uv=\frac{m+3}{3}$ i.e. $u^3v^3=\frac{(m+3)^3}{27}$
が成り立つ。
従って、 $u^3$ 、 $v^3$ は、
$t$ の2次方程式 $t^2-(m+3)t+\frac{(m+3)^3}{27}=0$ の解である。

この解を、 $u^3=\alpha$ 、 $v^3=\beta$ とすると、
$u={}^{3}\sqrt{\alpha}, {}^{3}\sqrt{\alpha}\omega, {}^{3}\sqrt{\alpha}\omega^2$
$v={}^{3}\sqrt{\beta}, {}^{3}\sqrt{\beta}\omega, {}^{3}\sqrt{\beta}\omega^2$
( $\omega=\frac{-1+\sqrt{3}i}{2}$ と定義する、これは1以外の1の3乗根2つの内の1つであり $\omega^3=1$ を満たす。)
となるが、 $uv=\frac{m+3}{3}$ が成立する必要があるので、
1つの $u$ について、1つの $v$ が対応することになる。
具体的には、 $uv$ は実数である必要があるので、3つの $u$ 、 $v$ の組、
$(u,v)=({}^{3}\sqrt{\alpha}, {}^{3}\sqrt{\beta}), ({}^{3}\sqrt{\alpha}\omega, {}^{3}\sqrt{\beta}\omega^2), ({}^{3}\sqrt{\alpha}\omega^2, {}^{3}\sqrt{\beta}\omega)$ が解となる。

ところで、 $X$ の3次方程式が実数の重解を持つということは、
上記3つの組から算出した3つの $u+v$ の値の内、2つが同一であることを意味する。
つまり、 ${}^{3}\sqrt{\alpha}\omega={}^{3}\sqrt{\beta}\omega$ 、 ${}^{3}\sqrt{\alpha}\omega^2={}^{3}\sqrt{\beta}\omega^2$ が成り立つ必要があるので、
$u^3$ 、 $v^3$ を解とする $t$ の2次方程式 $t^2-(m+3)t+\frac{(m+3)^3}{27}=0$ の解も
重解であることになる。

この2次方程式の判別式を $D$ とすれば、
$D=(m+3)^2-4\cdot\frac{(m+3)^3}{27}=0$ となる。
この $m$ の3次方程式を解くと、
$(m+3)^2(1-4\cdot\frac{m+3}{27})=0$
$(m+3)^2(4m-15)=0$
$m=-3, \frac{15}{4}$
となる。

1), 2)より、 $m=-3, \frac{15}{4}$ であるから、
求める接線の関数は、
$y=-3x$ 、 $y=\frac{15}{4}x$ となる。

感想

これは、普通に接線の公式を使った方がいいわ。
$y=mx$ なんて置いちゃう輩の末路はこうなるということです。

補足

ちなみに、実は3次方程式にも解の公式というのは存在します。

$ax^3+bx^2+cx+d=0$ （ $a\neq0$ ）の時、
判別式 $D$ は
$D=-4ac^3-27a^2d^2+b^2c^2+18abcd-4b^3d$
となります。
この時、
$D>0$ ：異なる3つの実数解を持つ
$D=0$ ：重解を持つ（2重解or3重解はわからない）
$D<0$ ：1つの実数解と2つの虚数解を持つ
となります。

2018-02-10

再帰的（リカーシブ）・再入可能（リエントラント）・再使用可能・再配置可能

応用情報技術者試験

紛らわし首記4つの用語についてまとめ。

再帰的（リカーシブ）

プログラムを1つでも学んだことがあるなら、階乗を求める関数を見たことがあると思うが、
あれがまさしく再帰処理をしている。

int factorial(int n){
    if(n==0){
        return 1;
    }else{
        return factorial(n-1) * n;
    }
}

上記は階乗を求めるC言語の関数であるが、factorial()という関数の中で、factorial()を、
つまり自分自身を呼び出している。これがまさに再帰的であるということである。
殆どすべてのプログラム言語で、再帰処理は実行可能である。
BASICでさえ、書き方を工夫すれば再帰処理を実装することができる。

再入可能（リエントラント）

業界的には「リエントラント」の方が通じるらしいので、以降ではこちらで書き進める。
リエントラントな処理とは、その処理を実行中に、更に同じ処理が呼び出されたとしても、
両方の処理が正しく処理を完了できる、ということである。
まずは、この「更に同じ処理が呼び出される」という状況であるが、
以下の3つのパターンがある：

ある処理を実行中にその処理自身が自分を呼び出す
ある処理を実行中に割り込みが入り、その割り込み処理がその処理を呼び出す
別々の場所で、同じ処理が呼び出される

このうち、1)は先に紹介した再帰処理である。したがって、再帰処理はリエントラントである。
2)が、純粋なリエントラントである。一方、3)は「スレッドセーフ」と言われる特性であり、
厳密にはリエントラントとは別の概念である。

再使用可能

プログラムの実行には、①まず記憶装置からプログラムコードを読み取りメモリに配置してから、②メモリに配置したプログラムコードを実行する、という2つのステップを踏む。
複数のソフトウェアが同じプログラムコードを使用したい時、別々にメモリに配置してそれぞれがプログラムを実行しているのは無駄である。
可能であるならば、一度メモリに配置したプログラムコードを、複数のソフトウェアが共有して使うのが望ましい。これができるプログラムを「再使用可能」なプログラムと呼ぶ。
この再使用可能なプログラムが、複数ソフトウェアから同時に呼ばれたときに、
同時に処理を実行できるならば、このプログラムはリエントラントでもある。
しかし、リエントラントではないプログラムでも、再使用可能にすることはできる。
このようなプログラムは特に「逐次再使用可能」であると言う。

再配置可能

再配置可能を説明するためには、まずはメモリアドレスについて知らなければなりません。
プログラムは機械語の命令をつなげたものです。しかし一直線につなげたのでは分岐処理ができません。
分岐処理というと、ifやらforやらswitchやらがありますが、機械語の命令に直すタイミングで、
おおよそジャンプ命令に置き換わります。
ジャンプ命令とは「○○に移動し、そこから命令を始めよ」という命令であり、
C言語で言えばあの悪名高いgotoのことです。
コーディングのマナー、あるいはルールとしてgoto命令を使うなということは色々なところで
言われていますが、実は機械語の世界ではgotoのオンパレードなのですね。
さて、gotoを使うとなると、ジャンプする先を指定しなければいけません。
これをどうやって指定するかという時に、メモリアドレスの話か出てきます。
プログラムは実行時にメモリ上に配置されますから、ジャンプ先として指定するのは
メモリのアドレスになります。この時、

「xx番メモリへ移動せよ」と命令するのが絶対アドレスによるジャンプ命令
「ここからnn先のメモリへ移動せよ」と命令するのが相対アドレスによるジャンプ命令

となります。
絶対アドレスによるジャンプ命令を使うとなると、このプログラムがメモリに配置される際に、
その場所は固定されていなければいけません。そうしないと、配置場所が変わるたびに、
ジャンプしたい先のメモリ位置が変わってしまうからです。
一方、相対アドレスによるジャンプ命令であれば、このプログラムはメモリのどこに配置さても
問題ありません。
再配置可能なプログラムの書き方の一つの方法は、相対アドレスによるジャンプ命令だけで
プログラムを作るという方法です。
他の手法としては、仮想アドレスでメモリ管理するというものもあり、実はこちらの方が主流です。

2018-01-28

リスク／チャンス対応策

応用情報技術者試験

リスク対応策

回避

リスク原因を取り除いたり、そのリスクがプロジェクトに影響しないように、プロジェクトの方を変更すること。
【例】

客先の要求を明確化し、不明瞭な要件をなくす
技術者間コミュニケーションを改善し、コミュニケーションエラーが発生しないようにする

転嫁（移転）

リスクを第三者に負ってもらうことで、リスク発生時の損失を第三者に補填させること*1。
なお、転嫁をする場合は基本的には、第三者に（主に金銭的な）対価を支払う必要があるため、この金銭的コストが新たなリスクになる（二次リスク）。
【例】

納入遅延に伴う違約金を補償してくれる保険に加入する
協力会社の技術者がスキル不足であった場合、別のスキルの高い技術者と交代できるよう契約する

軽減

リスク発生時の影響の大きさを低減させること。
【例】

簡潔な構造を採用し、リスクが発生した時に対処しやすいようにしておく
テスト回数を増やし、できるだけ不具合を洗い出せるようにしておく

受容

以上3つの対策を取らないことを「リスク受容」と言い、リスクが発生した時にそれを受け入れること。
このリスク受容には以下の2種類がある。

積極的リスク受容
消極的リスク受容

積極的リスク受容とは、リスク受容の事前準備をしておくことである。具体的には、代替計画（コンティンジェンシー計画）の準備や、予備予算の確保などである。
消極的リスク受容とは、つまり何も事前準備をせず、リスクが発生した時に対応策を考えることである。

上記4つのどれが最適かはケースバイケースである。リスクの発生確率、影響度合い、手持ちのリソーセス（人的・時間的・金銭的）に応じて、決めなければならない。場合によっては何もしない（消極的リスク受容）が最適であることもある。
また、リスク内容が既知なのか未知なのかも関係する。すなわち、既知のリスクであれば、それを回避したり軽減することができるが、未知のリスクに対しては基本的には転嫁と受容しか対策することができない。転嫁する場合でも、保険や契約内容にはスコープがある程度存在するはずなので、本当の未知のリスクへの対策はせいぜい予備リソーセス確保による積極的リスク受容くらいしかない。

チャンス対応策

活用

チャンスの原因となるものを引き寄せたり、チャンスの影響がプロジェクトに波及するようにすること。
【例】

能力の高い人材・機材などを導入する
流行りの技術を導入（インターネットやビッグデータ）したり、成長著しい市場に参画する*2

第三者と協力することで、チャンス発生確率を高めたり、新たなチャンスを引き寄せたりすること。
【例】

他社との協業

強化

チャンス発生時の影響を高めること。

受容

チャンス受容とは、何もせず、チャンスが到来した時にそれを受け入れることである。

表にまとめると以下のようになる。

	リスク	チャンス
発生確率	回避	活用
第三者	転嫁（移転）	共有
影響	軽減	強化

*1:余談であるが、「リスク転嫁」と「責任転嫁」は別物である。例えば、リスクがあるモジュールの責任部署を他部署ににすることは、責任転嫁することにより自部署が責められるリスクを転嫁しているだけであり、プロジェクトのリスクは全く転嫁されていない。どこの部署が責任を取ろうが、そのリスクは発生するのである。このときに、「他部署の方が本件に精通しているから」と言って、リスク軽減を装って責任を転嫁するのは、サラリーマンの常套手段である（ただの私見）。

*2:勿論、これが自社にとって経験の少ないものである場合、導入・参画することのリスクも発生する。流行言葉が大好きな上層部がいると、リスクがチャンスを圧倒する事態になるのは言わずもがなである。