私はサポートエンジニアになりたい

元教師・現開発エンジニアの挑戦

応用情報技術者試験のDB問題で高得点とるためのまとめ

エンティティの考え方

多重度

種類は以下の4つ。この中からどれを選ぶか考え方を考える。

ー:1対1

→:1対多

⇔:多対多

1対多(R2)

これを記述するパターンが1番多い。矢印の向きは主キーから外部キーへの向き。

A(主キー) → A(外部キー)

A(外部キー) ← A(主キー)

⇔:多対多

第3正規形にしないといけないため、多対多を書くことはなさそう。

属性

リレーションシップの矢印の向きと隣り合う属性をみて、判断。

隣り合うエンティティの主キー・外部キーになっているものが、空欄に記述する属性になる。

主キー:テーブルの行を一意に識別する。

隣に主キーがあれば、記述する属性は外部キー。

データウェアハウスでの考え方(あくまでR3)

要件を満たすためのデータモデルを作成するため、分析に不足している属性を本文から抜き出す。

現状のER図のままだとエラーになることが判明した(R1)

主キーが同一のレコードを登録して実行時エラーになるパターンが多そうです。

対応策として、エラー原因となっている表の主キーをみなおし、主キーを別の属性に変更することです。

正規化

第1正規系

テーブル中に複数の値をもつようなデータ項目を含まないよう整理

第2正規系か判断する

「主キーAと主キーBが存在し、非キー属性Cが異なれば主キーAは異なる」

→非キー属性Cは主キーAに関係従属し、主キーBには関係従属しない

なので第2正規系になっていない

第2正規系でなくても問題がない理由

本文に、「テーブルの内容は変更されることはない」と記載されるパターンが多い。

回答例:〇〇テーブルに追加された行の値がその後、変更されることはない

※テーブルの内容(主キー)が変更になった場合、複数の行を同時に変更しないといけないため、更新漏れが発生しそう

第2正規系

第1正規系の表に対して、主キーの一部の項目だけに従属するような項目を含まないよう整理

候補キー

主キーの候補になりうる項目

代替キー

主キー以外の候補キー

非キー属性

候補キー以外の項目

第3正規系

第2正規系の表に対して、主キー以外の項目に従属するような項目を含まないよう整理

SQL文の考え方

どの表が使われているか確認すること!

困ったら本文を探そう。見逃している条件があるかも!

SQLの実行順序

FROM

JOIN

WHERE

GROUP BY

SUMやAVEなど

HAVING

SELECT, DISTINCT

ORDER BY

LIMIT

SELECT 列名(または*) FROM テーブル名 WHERE 条件式

例:

SELECT COUNT(*) FROM 従業員

SELECT 単価 * 0.5 FROM 商品

細かい条件

SELECT DISTINCT 商品名 FROM 商品

重複を除く

SELECT 年齢, COALESCE(性別,2) FROM 会員

 NULL値を別の値に変換する

SELECT 日付, (CASE WHEN 時刻 < 12 THEN 0 ELSE 1 END) FROM 入館

SQL文で条件式を使う

JOIN句

INNER JOIN 結合するテーブル B ON A.属性 = B.属性

両方のテーブルにあるデータのみを取り出す(内部結合)
2つのテーブルにあって、合体できるデータのみを取り出します。

エンティティが1対1のときはこれを使用(R3)

LEFT JOIN 結合するテーブル B ON A.属性 = B.属性

左のテーブルをすべて表示して結合(外部結合)

RIGHT JOIN 結合するテーブル B ON A.属性 = B.属性

右のテーブルをすべて表示して結合(外部結合)

WHERE句による条件設定

WHERE 予約日 BETWEEN '20220306' AND '20220310'

範囲を指定する

WHERE 予約日 IN ('20220306', '20220310')

()内の値が対象

WHERE 名前 LIKE '_太郎%'

_は任意の1桁、%は任意の0~N桁の文字

WHERE 電話番号 IS NULL

NULLを抽出

WHERE 電話番号 NOT IS NULL

否定した条件

GROUP BY句(R3 SELECT文にCOUNTがあればGROUP BY)

SELECT 予約日, SUM(予約人数) FROM 予約明細 GROUP BY 予約日

SUM()以外にはAVG(),MAX(),MIN(),COUNT(*)などがある

GROUP BY句とHAVING句(R2)

SELECT 予約日, SUM(予約人数) FROM 予約明細 GROUP BY 予約日 HAVING COUNT(*) > 3

ORDER BY句

SELECT 予約番号, SUM(金額) FROM 予約明細 GROUP BY 予約番号 ORDER BY 予約番号 ASC

昇順はASC、降順はDESC

副問合せ

  1. 内側のSELECT文を実行
  2. 1で取得した値を用いて外側のSELECT文を実行

例:

SELECT 宿泊日, 利用料金 FROM 予約明細

 WHERE 顧客コード IN (SELECT 顧客コード FROM 予約 WHERE 担当ID = '100')

相関副問合せ(R2,H31)

EXISTSやNOT EXISTSを使った存在チェックで使用することが多い。

副問合せでは「該当データが存在するかどうか」という結果が分かればいいので、副問合せの列名は「*」を使用する。

  1. 主問合せを実行する(外側のSELECT文)
  2. 1で取得した値を用いて副問合せを実行する(内側のSELECT文)

例:

SELECT 施設ID, 部屋種別ID, COUNT(*) FROM 部屋

 WHERE NOT EXISTS (

  SELECT * FROM 予約明細 WHERE 予約明細.部屋ID = 部屋.部屋ID

    AND 予約明細.宿泊日 >= :チェックイン日付 AND 予約明細.宿泊日 < :チェックアウト日付)

 AND 施設ID = :施設ID AND 部屋種別ID = :部屋種別ID

 GROUP BY 施設ID, 部屋種別ID

 HAVING COUNT(*) >= :部屋数

INSERT

INSERT INTO 予約 (氏名, 日付, 担当者, コースコード) VALUES('山田山男', '2022-03-07', '佐藤佐子', '08')

試験問題では、VALUESの値が書かれているケースは少なそう。

INSERT INTO 予約 (コースコード) SELECT DISTINCT 予約 FROM コース

R1

UPDATE

UPDATA テーブル名 SET 列名 = 変更値, ・・・ WHERE 条件式

試験問題では、変更値がSELECT文になっているパターンが多い。

使用しているテーブル名を見て問題に答えること

DELETE

DELETE FROM テーブル名 WHERE 条件式

WITH(H31)

WITH 副問合せ名 AS (クエリ文):副問合せ名

UNION(H31)

同じ列名が存在する2つのSELECT文をつなぐ

SELECT 列名A FROM テーブルB UNION SELECT 列名A FROM テーブルC 

重複は1つにまとめる

SELECT 列名A FROM テーブルB UNION ALL SELECT 列名A FROM テーブルC 

重複もそのまま表示する

制約

非ナル制約

ある列にNULLが入らないようにする制約

UNIQUE制約(R2)

指定した列・列の組み合わせが一意であることを強制する制約

主キー制約

指定した列・列の組み合わせに1つだけ主キーを指定

検査制約

指定した列の内容を指定した条件を満足するもののみにする制約

参照制約

参照元テーブルに外部キーを指定する

データウェアハウス設計のスキーマ(R3)

スタースキーマ

1つのファクトテーブルの周囲に、複数のディメンションテーブルが単一の階層で関連付けられる。

ファクトテーブル:分析対象のデータを格納したテーブル

ディメンションテーブル:ファクトテーブルの外部キーから参照する主キーを持つマスタ系テーブル

スノーフレークスキーマ

スタースキーマのディメンションテーブルを正規化(階層化)した構造

データマート(R3)

データウェアハウスに格納されたデータから特定の用途に必要なデータだけを取り出し、構築する小規模なデータベース。

データウェアハウスで分析するのに時間がかかりすぎる場合、データマートを追加する。

つまり「データマートの表を見れば分析できる」という状態がのぞましい。

3層スキーマ

データモデルより前のデータベースの構成(概念スキーマ、外部スキーマ、内部スキーマ