データベーススペシャリスト合格に必要となる知識を一問一答形式で確認していきます。当サイトでは,IPAの情報処理技術者試験に最短最速で合格するための方法と直前対策についてまとめています。
SQL
NULLに対して比較演算子を利用する際の注意点を述べよ。
NULLに比較演算子を適用すると,3値理論における「UNKNOWN(計算不能)」になる。したがって,NULLには比較するための演算子が専用で用意されており,IS NULLを利用する。
SQLのLIKE演算子で特定の文字をエスケープしたい場合はどうすればよいか。
ESCAPE句を利用する。自分でエスケープ文字を指定することができる。例えば_
をエスケープしたい場合は
SELECT * FROM table WHERE column LIKE '!_test' ESCAPE '!'
と利用する。ただし,自分が指定したエスケープ文字の直後しかエスケープされないため注意する。
「全てのXに対して」と「あるXが存在する」を表現したい場合はどうすればよいか。
比較演算子の直後にANYとALLを利用する。
3000 < ANY (2000, 3000, 4000) -- TRUE
3000 < ALL (2000, 3000, 4000) -- FALSE
{SELECT文X} EXCEPT {SELECT文Y}はどのような結果を表すか。
X-Y
の差集合を表す
COALESCE関数の意味とユースケースを述べよ。
与えられた引数のうち,NULLでない最初の引数を返す。これは,カラムにNULLが含まれる場合の代替値を手軽に指定するために利用される。
SELECT column_A COALESCE(column_B, 'column_B is NULL!!') FROM table
HAVING句の意味についてGROUP BYの処理順に着目して説明せよ。
HAVING句はSELECT句・WHERE句・GROUP BY句の結果に対する絞り込みを行う。一般的に,GROUP BY句の処理順は下記のようになっている。
- WHERE句で絞り込む
- GROUP BY句でグループ化する
- SELECT句の結果を返す
例えば,下記のSQLを考える。
SELECT column_A, SUM(column_B) FROM table
GROUP BY column_A
いま,SUM(column_B)が0より大きいcolumn_Aを表示させたい場合は,
SELECT column_A, SUM(column_B) FROM table
GROUP BY column_A
HAVING SUM(column_B) > 0
と書く。なぜWHERE句を使ってはいけないかというと,いま我々が条件付けしたいSUM(column_B)がSELECT句を経ないと確定していないからである。あくまでも,WHEREはSELECTで結果を返す際の絞り込みを行うものであり,GROUP BYの結果に対する絞り込みは行わないのである。
ざっくりと,以下のように理解するとよい。
- 集計前の絞り込み:WHERE句
- 集計後の絞り込み:HAVING句
ある処理が集計前か集計後かという観点は,データベースを扱う上で非常に大切である。
INとANY/ALLが等しくなるように演算子を組み合わせなさい。
- 「IN (X)」と「=ANY (X)」は等価
- 「NOT IN (X)」と「<>ALL (X)」は等価
「=ANY (X)」は「=となる,あるXが存在する」という意味なのでINと等価。「<>ALL」は「全てのXに対し,<>となる」という意味なのでNOT INと等価。
相関副問い合わせを説明せよ。
副問い合わせの内部から,主問い合わせの表や列を利用する副問い合わせのこと。相関問い合わせ典型例として「異なるテーブルに値が存在する行のみを抽出する」というユースケースがあり,EXISTS演算子が利用される。
SELECT column_A FROM table_X WHERE EXISTS
(SELECT * FROM table_Y WHERE table_X.column_B = table_Y.column_B)
内部結合と外部結合を説明せよ。
内部結合と外部結合は,結合相手の行が見つからない場合の挙動によって分類されている。
- 内部結合:行が消滅する
- 外部結合:行をNULLとして出力する
さらに,外部結合は下記のように分類される。
- 左外部結合:左表の各行について結合相手が見つからない場合でもNULLを出力する
- 右外部結合:右表の各行について結合相手が見つからない場合でもNULLを出力する
- 完全外部結合:左表および右表の各行について結合相手が見つからない場合でもNULLを出力する
FULL JOINが使えない場合にUNIONを使って代替する方法を述べよ。
SELECT * FROM table_left
LEFT JOIN table_right ON table_right.ID = table_left.ID -- 左外部結合の結果
UNION
SELECT * FROM table_right
RIGHT JOIN table_right ON table_right.ID = table_left.ID -- 右外部結合の結果
原子性・分離性・トランザクション・コミット・副作用・分離レベル・ロックを構造化せよ。
DBMSはSQLの同時実行や意図しない中断からデータの正確性を担保するために,原子性・一貫性・分離性・永続性を保つ制御を行う必要がある。これらはそれぞれAtomicity・Consistency・Isolation・Durabilityと表されるため,これらの頭文字を取ってACID特性と呼ばれている。
ACID特性 | 意味 | 担保するための機能 |
---|---|---|
原子性 | トランザクションが 「全て実行完了」か「1つも実行完了していない」である | コミットメント制御 |
一貫性 | データの内容が矛盾した状態にならない | 排他制御 |
分離性 | トランザクションが同時実行されても 独立で実行した結果と等価になる | 排他制御 |
永続性 | 記録したデータは消滅しない | 障害回復 |
まず前提として,ACID特性を担保するために,DBMSではトランザクション(TX)処理を行う。TXとはユーザ側からみたSQLの意味のある塊である。原子性を担保するためには,コミットメント制御を行う。コミットによりTXを確定し,ロールバックにより特定のコミット時点への切り戻しを行うことができる。一貫性と分離性を担保するためには,排他制御(ロック)を用いる。ロックには排他ロックと共有ロックがあり,排他ロック中は他からのロックを一切受け付けず,共有ロック中は他からの共有ロックのみを許す。永続性を担保するためには,障害回復(バックアップ)を用いる。
データの正確性が担保されていない状態を副作用とよび,以下の三種類が代表的である。
- Lost update:他のトランザクションによって値が書き換えられてしまうこと
- Dirty read:コミットされていない変更を読み取る
- Non-repeatable read:同じ行を読み取る際に他TXによる更新が影響を及ぼすこと
- Phantom read:同じ条件で行を読み取る際に他TXによる挿入・削除が影響を及ぼすこと
Lost updateは最も基本的な同時実行の弊害である。Dirty readは「まだコミットされていない汚いトランザクション」と理解する。Non-repeatable readは「反復不能読み取り」と訳すことができ,同一の行を複数回読み取る際に,毎回結果が異なる(=反復不能である)副作用を表している。Phantom readは「幻影読み取り」と訳すことができ,ある行を同一の条件で読み取る際に,毎回条件が異なる(=幻影が見え隠れする)副作用を表している。
これらの副作用を防ぐために,上述のTXとロックを用いるが,厳しいロックをかけるほどデータベースとしてのパフォーマンスが低下するというトレードオフの関係にある。このトレードオフを調整するために,DBMSでは分離レベルという機能が備わっている。
分離レベル | Lost update | Dirty read | Non-repeatable read | Phantom read |
---|---|---|---|---|
READ UNCOMMITTED | - | $\cm$ | $\cm$ | $\cm$ |
READ COMMITTED | - | - | $\cm$ | $\cm$ |
REPEATABLE READ | - | - | - | $\cm$ |
SERIALIZABLE | - | - | - | - |
表中の$\cm$は「発生の恐れがあること」を表している。ロックをかけることで,最も基本的なロストアップデートは起こらないようになっている。
SELECT句のロックについて説明せよ。
SELECT句で指定した行は自動で共有ロックがかかる。FOR UPDATE句を利用すると排他ロックを強制できる。
表ロックのかけかたを説明せよ。
LOCK TABLE table IN {EXCLUSIVE || SHARE} MODE
デッドロックの防止策を二つ述べよ。
- トランザクションの時間を短くする
- 同じ順番でロックする
ロックエスカレーションとは何か。
パフォーマンス効率のために,多数の行ロックがかけられると自動で表ロックに切り替わる機能
2フェーズコミットとは何か。
2つ以上のデータベースでトランザクションの整合性を保つため,コミットを「確定準備」と「確定」の2つのフェーズに分けること
UNIQUE制約のかけられたカラムで複数のNULLが挿入されることは許されるか。
許される。NULLとNULLの比較はTRUEではなくUNKNOWNになるため。
SQLを分類せよ。
分類 | 主体 | 具体的な命令 |
---|---|---|
DML(Data Manipulation Language) | データ自体を出し入れする者 | SELECT INSERT UPDATE DELETE EXPLAIN LOCK TABLE |
TCL(Transaction Control Language) | データ自体を出し入れする者 | COMMIT ROLLBACK SET TRANSACTION SAVEPOINT |
DDL(Data Definition Language) | データの入れ物を準備する者 | CREATE ALTER DROP TRUNCATE |
DCL(Data Control Language) | データの入れ物を準備する者 | GRANT REVOKE |
DELETE・DROP・TRUNCATEの違いを説明せよ。
DROPのみテーブルを削除する挙動になる。DELETEとTRUNCATEの違いは下記である。
DELETE | TRUNCATE | |
---|---|---|
分類 | DML | DDL |
WHEREに対する挙動 | 指定可能 | 指定不可 |
ロールバック | 可能 | 不可 |
パフォーマンス | 低速 | 高速 |
一言で表すと | データ削除 | テーブル初期化 |
TRUNCATEはDROP+CREATEと理解すれば分かりやすい。
テーブルに対して適用可能な制約を列挙せよ。
- NOT NULL
- UNIQUE
- CHECK
- PRIMARY KEY
- FOREIGN KEY
特に,「PRIMARY KEY」と「FOREIGN KEY」が単なるラベル付けではないことに注意されたい。「このカラムは主キーですよ」とか「このカラムは外部キーですよ」と意思表示をしているのではなく,内部的に下記のような制約を表しているのである。
- PRIMARY KEY:NOT NULLかつUNIQUE
- FOREIGN KEY:参照整合性を崩壊させる操作の禁止
データベース設計の流れを説明せよ。
- 概念設計:要件の整理
- 論理設計:ER図の作成
- 物理設計:DDLの作成
エンティティ・属性・関連をそれぞれ一言で説明せよ。
- エンティティ:テーブル
- 属性:カラム
- 関連:テーブル間のリレーション
論理設計でER図の整理を行う際は何を意識すればよいか。
多対多を2つの1対多に分解すること。なぜなら,RDBは多対多をテーブルで表現できないから。
正規化の目的は何か。
一つの事実は一箇所にまとめることにより,ヒューマンエラーを防止すること。
正規化の流れを説明せよ。
- 第一正規形への変形:繰り返し列(=複数行の結合)の排除
- 第二正規形への変形:部分関数従属(=複合主キーの一部への関数従属)の排除
- 第三正規化への変形:推移関数(=間接的な関数従属)の排除
もう少し抽象レイヤーで捉えると,下記のようになる。
- $m$行$n$列の綺麗なテーブルに変形する
- 綺麗な関数従属に変形する
第一正規形への変形は,$m$行$n$列の綺麗なテーブルに変形することを目的としている。具体的には,複数行の結合が行われている箇所を別テーブルに切り離し,新しく主キーを追加する。このままだと切り離し元のテーブルとの関連付けが失われてしまうため,切り離し元のテーブルの主キーをコピーする。これで第一正規形の完成だ。
第二正規形と第三正規形は,綺麗な関数従属に変形することを目的としている。そこで,関数従属について深堀りしておく。下記定義を用いる。
- $y=f(x)$に対して「$y$は$x$に関数従属する」と定義する
- $x$が複合主キーの一部である場合「$y$は$x$に部分関数従属する」と定義する
- $z=g(f(x))$に対して「$z$は$x$に推移関数従属する」と定義する
- 部分関数従属でも推移関数従属でもない関数従属を「綺麗な関数従属」と定義する
部分関数従属でない関数従属を完全関数従属という。
第一正規形への変形で切り離した先のテーブルでは,切り離し元の主キーと新しく導入した主キーの複合キーが設定されている場合がある。そのテーブルにおいては,常に部分関数従属する列が存在する可能性がある。そこで,第一正規形への変形と同様に,この部分関数従属している列を別テーブルに切り出して主キーをコピーする。このとき,第一正規形への変形と異なるのは,切りさした先のテーブルでは新たな主キーの設定は不要だということである。なぜなら,元々部分関数従属している時点で元のテーブルに主キーが存在するため,主キーが存在しないという第一正規形への変形とは状況が異なるからである。
綺麗な関数従属にもっていくためには,部分関数従属だけでなく推移関数従属にも対応する必要がある。ただし,これも第二正規形への変形と全く同様で,推移関数従属している列を別テーブルに切り出して主キーをコピーするだけだ。
以上の手続きにより,第三正規形への変形(=データベースの正規化)は完了した。詳しくは下記を参照されたい。
射影と選択を説明せよ。
- 射影:SELECT DISTINCT {選択項目リスト} FROM table
- 選択:SELECT * FROM table WHERE {条件}
射影は選びたい列を指定することで列を抽出し,選択は選びたい条件を指定することで行を抽出する。
内部結合を行う構文を二つ述べよ。
-- JOINを使わないパターン
SELECT column_A FROM table_X, table_Y
WHERE table_X.column_B = table_Y.column_C
-- JOINを使うパターン1
SELECT column_A FROM table_X (INNER) JOIN table_Y
ON table_X.column_B = table_Y.column_C
-- JOINを使うパターン2 (column_Bがtable_Xとtable_Yで共通の場合)
SELECT column_A FROM table_X (INNER) JOIN table_Y
USING (column_B)
等結合と自然結合を説明せよ。
- 等結合:列の重複をそのまま表示
- 自然結合:列の重複を取り除いて表示
「与えられた命令とガッツリ等しく結合」と「見やすいように自然に重複を取り除いて結合」と覚える。`
和両立を説明せよ。
対象テーブルが下記を満たすこと。
- 属性の次数(列数)が同じ
- 各属性のドメイン(属性がとり得る値の集合)が同じ
等結合を関数演算で表現しなさい。
直積と選択で表現できる。直積ですべての組み合わせを列挙した上で結合条件を満たす行を選択する。
INとEXISTSの違いを説明せよ。
いずれも「異なるテーブルに値が存在する行のみを抽出する」を実現できるが,それまでの過程が異なる。INの場合は,最初に問い合わせの結果を得て一時テーブルに保存し,主問い合わせの各行と一時テーブルを比較していく。EXISTSの場合は,各行処理を行う。INでは副問い合わせ元のインデックスを使うことになるが,EXISTSでは副問い合わせ先のインデックスを使うことになる。したがって,問い合わせ内がシンプルな場合はINで,問い合わせ内が複雑な場合はEXISTSが適しているケースが多い。
INSERTで複数行追加する方法を述べよ。
INSERT INTO table_X (column_A, column_B)
SELECT column_A, column_B FROM table_Y
SELECTの結果,複数のタプルが返ってくると理解すればよい。ただし,WEB上の資料ではVALUESは省略されている記法が多かったため,それに準じている。
参照制約のオプションを説明せよ。
下記の形式で指定する。
FOREIGN KEY (参照元のキー) REFERENCES 参照先テーブル名 (参照先列名)
{ON DELETE || ON UPDATE} {NO ACTION || CASCADE || SET NULL}
- NO ACTION:整合性を保てない操作を試みた場合は拒否する
- CASCADE:整合性を保てない操作を試みた場合は削除して整合性を保つ
- SET NULL:整合性を保てない操作を試みた場合はNULLで整合性を保つ
ASSERTIONキーワードの使い方を説明せよ。
テーブル間にまたがる制約など複雑な制約をクエリで記述できる。複数テーブルにまたがる可能性のある制約であるため,CREATEして使う。
CREATE ASSERTION assert_end_date CHECK ({SQL文})
DOMAINキーワードの使い方を説明せよ。
独自のデータ型を定義できる。複数テーブルにまたがる可能性のある制約であるため,CREATEして使う。
CREATE DOMAIN AGE AS SMALLINT CHECK ({条件文})
CONSTRAINTキーワードの使い方を説明せよ。
制約に名前を付けることができる。
-- 文法
CREATE TABLE テーブル名 {
列名 データ型 CONSRTRAINT 制約名 制約
}
-- 例
CREATE TABLE table_X {
ID CHAR(4) CONSTRAINT PK PRIMARY KEY -- PRIMARY KEYという制約にPKという名前を付けている
}
基底表とは何か。
viewを作成した際,もとになったテーブル。
view自体を直接更新できる条件のイメージは何か。
view→基底表が全単射であるというイメージ。前提として,viewは基底表から抽出して得られるものなので,view→基底表は全射である。一方,全射である場合はviewのある行を更新すると,基底表のどの行を更新して整合性を保てばよいのか分からなくなってしまうため,単射である必要がある。したがって,viewが更新できる条件はview→基底表が全単射であるというイメージだ。
具体的には,集計関数やGROUP BYを使ってviewを作っていると,view→基底表は単射ではなくなってしまうため,viewを更新することができない。
ビューと権限についてまとめよ。
ビューと権限は,ビュー作成時の権限とビュー操作時の権限に分けられる。ビュー作成時の権限は以下の通りである。特に,作成時は基底表のSELECT権限が必要になる点に注目する。
基底表のSELECT権限 | 基底表のGRANT権限 | ビューの作成権限 | ビューの操作権限移譲 |
---|---|---|---|
なし | - | なし | - |
$\cm$ | - | $\cm$ | なし |
$\cm$ | $\cm$ | $\cm$ | $\cm$ |
ビュー操作時の権限は以下の通りである。特に,操作時はビューの権限が必要になる点に注目する。
権限 | ビューの所有者 | ビューの所有者以外 |
---|---|---|
SELECT | $\cm$ | ビューの操作権限そのもの |
INSERT UPDATE DELETE | 基底表の権限と同じ | ビューの操作権限そのもの |
導出表とは何か。
実表に対するRDBの操作によって得られる仮想的な表。ビューも導出表の一種。
SQLにおけるカーソルとは何か。
SELECTの結果および特定のオブジェクトを示すポインタのこと。通常,SQLでは複数行・複数列を対象とするバッチ処理が行われるが,プログラミング言語内でSQLを用いる場合には,各行に対する逐次処理を行いたい場面がある。そこで,プログラミング言語内におけるストアドプロージャとして,SELECT文の結果および処理対象へのポインタを保持することで,逐次処理を可能にする考え方がある。
バッチ処理を行う通常のSQLを対話型処理,カーソルを用いた逐次処理を非対話型処理という。
カーソルを用いる流れは,以下のとおりである。
- カーソルを宣言
- カーソルを開く
- データの取得と繰り返し処理の実行
- カーソルを閉じる
具体的には,下記のような記法を用いる。ただし,言語によっては毎回のSQL実行文の冒頭でEXEC SQLを,実行文の末尾でEND-EXECまたはセミコロンを明示する必要がある点に注意する。
-- カーソルを宣言
EXEC SQL DECLARE {カーソル名} CURSOR FOR {SELECT文} END-EXEC
-- カーソルを開く
EXEC SQL OPEN {カーソル名} END-EXEC
-- データの取得と繰り返し処理の実行
EXEC SQL LOOP FETCH {カーソル名} INTO {変数名}
{変数に対する処理}
END LOOP END-EXEC
-- カーソルを閉じる
EXEC CLOSE {カーソル名} END-EXEC
{変数に対する処理}部分でUPDATEやDELETEを行う場合は,下記のような記法を用いる。ただし,カーソルの定義部分で集計関数・GROUP BY・ORDER BY・結合等を行なっている場合はUPDATE/DELETEすることができない点に注意する。
EXEC SQL {UPDATE文 || DELETE文} WHERE CURRENT OF {カーソル名} END-EXEC
非対話型処理でも,対話型処理のように各行へのSQL文をまとめてバッチ処理することが可能である。その場合は,COMMITやROLLBACKをコード中で明記する必要がある。
SQLSTATEとは何か。
カーソルを用いたSQLの処理部分において,エラーを識別するためにステータスコードが格納される変数。プログラムのソースコードでカーソルを用いる場合には,処理を行う前にあらかじめSQLSTATEという変数を宣言しておく必要がある。
社員テーブルからマネージャの数と一般社員の数を部署コード別に集計するSQLを書け。
SELECT 部署コード,
COUNT (CASE WHEN 役職='マネージャ' THEN 1 ELSE NULL END) AS 主任の人数,
COUNT (CASE WHEN 役職='一般社員' THEN 1 ELSE NULL END) AS 一般社員の人数,
FROM 社員 GROUP BY 部署コード
GRANTとREVOKEの記法を述べよ。
-- シンプルなGRANT
GRANT {権限種別} ON {テーブル名 || ビュー名} TO {ユーザ名 || ロール名}
-- 対象ユーザ・ロールに「他のユーザ・ロールに権限を移譲できる権限」を与えるGRANT
GRANT {権限種別} ON {テーブル名 || ビュー名} TO {ユーザ名 || ロール名} WITH GRANT OPTION
-- ロールをユーザにアタッチするGRANT
GRANT {ロール名} TO {ユーザ名}
-- シンプルなREVOKE
REVOKE {権限種別} ON {テーブル名 || ビュー名} FROM {ユーザ名 || ロール名}
{権限種別}に関しては,列を指定することもできる。例えば,電話番号の列のみUPDATE権限を与えたい場合は,
UPDATE(電話番号)
と記述することができる。
多対多のリレーションを解消する方法を述べよ。
間にバッファとして関連エンティティを挿入する。1対多と多対1に紐解くことができる。
強エンティティと弱エンティティを説明せよ。
あるエンティティが他のエンティティの存在に依存していないとき,そのエンティティは強エンティティという。同様に,あるエンティティが他のエンティティの存在に依存しているとき,そのエンティティは弱エンティティという。
X→YとY→Zは成立していてY→Xではない関係は推移的関数従属か。
推移的関数従属の定義そのものである。逆に,Y→Xとなる場合は推移的関数従属にはならない点に注意する。
第一正規形・第二正規形・第三正規形である理由の定型文を説明せよ。
- 第一正規形
-
全ての属性が単一値で,候補キー{A,B}の一部であるBに非キー属性のCが部分関数従属するため。
- 第二正規形
-
全ての属性が単一値で,候補キーへの部分関数従属がなく,推移的部分関数従属A→B→Cがあるため。
全ての属性が単一値で,候補キーに完全関数従属し,推移的部分関数従属A→B→Cがあるため。
- 第三正規形
-
全ての属性が単一値で,候補キーへの部分関数従属がなく,推移的部分関数従属もないため。
全ての属性が単一値で,候補キーに完全関数従属し,推移的部分関数従属もないため。
詳しくは下記を参照されたい。
ウィンドウ関数を説明せよ。
SELECT文で抽出した各行に複数行にまたがった処理を加えることができる関数。主に選択項目リストで用いる。
SELECT 関数名 (知りたい列名) OVER (PARTITION BY グループ化したい列名) AS 別名 FROM テーブル名
PARTITION BYとGROUP BYを間違えないように注意されたい。GROUP BYは問答無用でグループ化してしまうが,PARTITION BYはあくまでもグループごとに集めたテーブルを区切る働きをする。例えば,下記の例では部署ごとに区切ったテーブルのAVGを求めて各行に挿入している。
SELECT 部署, 社員, 労働時間, AVG (労働時間) OVER (PARTITION BY 部署) AS 部署平均 FROM 勤務表
SUM/AVG/MAX/MIN/COUNTといった代表的な集計関数の他,下記のような特徴的な関数が利用可能。
関数名 | 処理 |
---|---|
ROW_NUMBER () | 行番号(1始まり)を取得 |
LAG (列名, n) | 列名のn行前(LAGするから前)の値を取得する nはオプション |
LEAD (列名, n) | 列名のn行後(LEADするから後)の値を取得する nはオプション |
RANK () | 順位付けをする 同順位が存在する場合に飛び番が存在する |
DENSE_RANK () | 順位付けをする 同順位が存在する場合に飛び番が存在しない |
NTILE (n) | n個に等分し,その分割した集合に順位付けをする |
SQLで移動平均を求める方法を述べよ。
ウィンドウ関数の範囲指定とORDER BYを利用する。範囲指定方法には,下記の二通りある。
- ROWS(行数指定)
- RANGE(閾値指定)
範囲の開始地点と終了地点の方法には,下記の記法を用いることができる。
関数名 | 処理 |
---|---|
CURRENT ROW | 現在の行 or 値を指定 |
n PRECEDING | n行前の行 or 値を指定 nの代わりにUNBOUNDEDを指定すると先頭を表す |
n FOLLOWING | n行後の行 or 値を指定 nの代わりにUNBOUNDEDを指定すると末尾を表す |
例えば,下記のように用いる。なお,終了地点の「AND CURRENT ROW」を省略することもできる。
-- ROWSの利用(2行前から現在の行までの3ヶ月移動平均)
SELECT 年月, 月間売り上げ, AVG (月間売り上げ)
OVER (ORDER BY 年月 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS 3ヶ月平均
FROM 月間売上表
-- RANGEの利用(年月が1ヶ月単位なので2ヶ月前から今月までの3ヶ月移動平均)
SELECT 年月, 月間売り上げ, AVG (月間売り上げ)
OVER (ORDER BY 年月 RANGE BETWEEN 2 PRECEDING AND CURRENT ROW) AS 3ヶ月平均
FROM 月間売上表
SQLのトリガーを説明せよ。
テーブル操作(INSERT/UPDATE/DELETE)をきっかけに特定の処理を走らせる仕組みのこと。テーブル全体に対する設定であるため,CREATEを用いて定義する。基本的な記法は以下のとおりである。
CREATE TRIGGER {トリガー名} {BEFORE || AFTER || INSTEAD OF} {操作種別} ON {テーブル名}
REFERENCING {NEW || OLD} {TABLE(省略時は行)} AS {変数名}
FOR EACH {ROW STATEMENT}
BEGIN ATOMIC
{被トリガー処理}
END
具体的には,下記のようなユースケースが挙げられる。
-- UPDATE時に適用開始日を自動挿入する
CREATE TRIGGER トリガー1 BEFORE UPDATE ON 商品
REFERENCING NEW AS NEW1
FOR EACH ROW
BEGIN ATOMIC
SET NEW1.適用開始日 = COALESCE(NEW1.適用開始日, CURRENT_DATE)
END
-- UPDATE時に他テーブルにINSERTする
CREATE TRIGGER トリガー2 AFTER UPDATE ON 商品
REFERENCING OLD AS OLD1 NEW AS NEW1
FOR EACH ROW
BEGIN ATOMIC
INSERT INTO 商品履歴
VALUES (OLD2.ID, OLD2.商品名, ADD_DAYS (NEW1.適用開始日, -1)) -- 適用開始日の1日前
END
トランザクションの途中までロールバックしたい場合どうすればよいか。
セーブポイントを利用する。
{SQL文1}
{SQL文2}
SAVEPOINT X
{SQL文3}
{SQL文4}
-- ここでSQL文3の直前までロールバックしたい場合
ROLLBACK TO SAVEPOINT X
正規化を行わないことによる副作用を述べ,それらを説明する定石を説明せよ。
副作用は「更新時異状」とよばれる。「異常」ではないため注意する。各種操作に対して,下記のような定石を用いる。
操作種別 | 問題 | 文言 |
---|---|---|
INSERT | 主キーがNULL | {ある特定の条件下において}は主キーがNULLとなるため登録できない |
INSERT | 主キーが重複 | {ある特定の条件下において}は主キーが重複するため登録できない |
UPDATE | 更新漏れ | {ある特定の条件下において}は複数の属性を同時に更新しないと整合性が保てない |
DELETE | 情報損失 | {ある特定の条件下において}は主キーの情報が損失してしまう |
見積行数/平均行長/ページサイズ/空き領域率/バッファサイズから所要量/バッファヒット率を求めよ。
見積行数 | 平均行長 | ページサイズ | ページヘッダ | 空き領域率 | バッファサイズ |
---|---|---|---|---|---|
10,000 | 1,080 byte | 16,000 byte | 100 byte | 30% | 500ページ |
データ所要量は下記のように求められる。
- データ所要量 = 必要ページ数 * ページサイズ
- 必要ページ数 = 見積行数 / 1ページあたりの平均行数
- 1ページあたりの平均行数 = 1ページあたりの行長 / 平均行長
- 1ページあたりの行長 = (ページサイズ - ヘッダサイズ) * (1-空き領域率)
バッファヒット率は下記のように求められる。
- バッファサイズ / 必要ページ数
まず,1ページあたりの行長は
(16000 - 100) \times (1 - 0.3) &= 11130
\end{align}
で,11300byteとなる。次に,1ページあたりの平均行数は
11130 / 1080 &\fallingdotseq 10.3
\end{align}
で,約10行となる次に,必要なページ数は
10000 / 10 &= 1000
\end{align}
で,約1000ページとなる。すると,データ所要量は
1000 \times 16000 &= 1.6 \times 10^{7}
\end{align}
で,約16MBとなる。1000ページのテーブルを500ページのバッファ領域で管理するため,バッファヒット率は
500 / 1000 &= 0.5
\end{align}
で,約50%となる。
クラスタ索引とは何か。
データの順番と索引(インデックス)の順番が等しくなるような索引
分散型データベースの結合演算をまとめよ。
演算名 | 目的 | 特徴 |
---|---|---|
入れ子ループ法 | - | 最もベーシックな貪欲法 |
セミジョイン法 | 通信量削減 | 結合属性の列だけを結合先に転送 結合先で結合処理を行なってから結果を返す |
マージジョイン法 ソートマージ法 | 計算量削減 | 結合元と結合先をそれぞれソートしてから結合する 等結合では効率が悪く,索引があればソートは不要 |
ハッシュ法 | 計算量削減 | 結合条件に関するデータ量が大きい場合に ハッシュという短い値が利用できるため有効 ハッシュの性質上等号以外の結合条件には使えない |
午前II対策
Materialized viewとは何か。
結果をキャッシュするView。体現ビューともよばれる。
BPEL/BPELを説明せよ。
略語 | 単語 | 説明 |
---|---|---|
BPEL | Business Process Execution Language | 複雑なビジネスプロセスを記述するための言語 複数のWebサービスを呼び出すアーキテクチャを採用 |
BPMN | Business Process Modeling Notation | ビジネスプロセスを可視化するモデリング記法 |
Business Process Modeling and Notationの略称で,ビジネスプロセスを視覚化する手法のことを指す。
データウェアハウスとデータマートの違いは何か。
- ウェアハウス:ローデータを保存。時系列分析など。
- データマート:必要となるデータを扱いやすい形で抽出。属性分析など。
マートは「小売店」という意味なので,必要となる情報の小売店というイメージで理解する。
CPRM/DTCP-IP/CSS/HDCPを説明せよ。
単語 | 用途 | 説明 | |
---|---|---|---|
CPRM | Content Protection for Recordable Media | デジタル放送の録画 | コピーワンスの番組を録画 |
DTCP-IP | Digital Transmission Content Protection over IP | AV機器 家庭内LAN | 著作権保護されたコンテンツの伝送 |
CSS | Content Scramble System | DVD | 映像コンテンツを暗号化 暗号鍵の保存 |
HDCP | High-bandwidth Digital Content Protection | HDMI端子付きAV機器 | デジタル信号の暗号化 |
「2相ロック方式に従うトランザクションは競合直列可能性が保証される」は正しいか。
正しいとしてしまう。実際にはトランザクションの順序を規定しないとデッドロックの発生可能性は否めないが,他の選択肢と比較して消去法で選ぶ流れになる。
ESBとは何か。
Enterprise Service Busの略称で,サービス指向アーキテクチャ(SOA)の中核となるミドルウェア。SOAのコンポーネント同士をバス型のトポロジーで接続する。
NoSQLではロックは利用されるか。
されない。ロックはRDB特有のものと捉える。というより,トランザクションまわりのボトルネックを排除したいという要請がNoSQLが登場した背景にある。
PGPとは何か。
Pretty Good Privacyの頭文字を取った略称で,対称暗号技術と非対称暗号技術を併用して電子メールの暗号化,復号の機能をもつツールである。
IndexedDBとは何か。
Webブラウザ用のストレージの機能として,トランザクション処理のAPIを定義するという仕様。Indexedという単語は「ユーザ側にDBをキャッシュ的に構築してしまう」というイメージで捉える。
アムダールの法則を説明せよ。
並列度を上げていったときに,並列化できない部分がボトルネックとなるという主張。改善割合を$R$,性能向上率をAとおくと,全体の性能向上率は
\frac{1}{(1-R)+R/A}\label{アムダール}
\end{align}
と表される。これは,性能改善は処理時間によって評価されるとし,性能改善後の処理時間を考えると分かりやすい。全体の処理時間を$1$とする。このとき,改善を適用しない部分の処理時間は$1-R$となる。ここで,例えば性能向上率が$2$倍のときは処理時間は$1/2$倍になるという関係に注意すると,改善を適用する部分の処理時間に関しては,性能向上率が$A$であることから,単位時間あたり$R/A$となる。したがって,全体の処理時間は
(1-R)+R/A
\end{align}
となる。先ほどと同様に,処理時間が$2$倍になると性能は$1/2$倍になるため,式($\ref{アムダール}$)を求めることができる。
ディスクの分散配置は何とよばれるか。
ストライピング
トリアージとインシデントレスポンスはどちらの方が先に行うか。
トリアージ。インシデントの優先付けを行う。
DLPとは何か。
Data Loss Preventionの頭文字を取った略称で,機密データの特定と持ち出し防止策のことを指す。災害対策によるバックアップではないため注意が必要。
RAID0〜DAID3を説明せよ。
- RAID0:ストライピング(分散書き込み)
- RAID1:ミラーリング(分散保持)
- RAID01:ストライピングをミラーリング
- RAID10:ミラーリングをストライピング
- RAID2:データをストライピング・訂正用のハミング符号を3台に分散(耐久性強い)
- RAID3:データをストライピング・検知用のパリティを1台に保存(あまり使われない)
RAID3〜RAID5のストライピング単位と冗長ディスク構成の表を書け。
RAID3 | RAID4 | RAID5 | |
---|---|---|---|
ストライピングの単位 | ビット | ブロック | ブロック |
冗長ディスクの構成 | 固定 | 固定 | 分散 |
2相コミットでは,全てのサブトランザクションからコミット了承応答が届いても,必ずしも全てのサブトランザクションをコミットするとは限らない。
No。必ずコミットする。
2相コミットを用いても,サブトランザクションが実行されるサイトに主サイトの指示が届かず,サブトランザクションをコミットすべきかロールバックすべきか分からない場合がある。
Yes。参加者がコミット可否を応答してから調停者がダウンした場合など。
リスク分析の対象は投機的リスクと純粋リスクのどちらか。
純粋リスク
リスク予想損失額に予防のコストは含まれるか。
含まれない。
リスク分析で行うことは「現実に発生すれば損失をもたらすリスクが,情報システムのどこにどのように潜在しているかを識別し,その影響の大きさを測定すること」は正しいか。
正しい。影響測定までする。
「リスクを金額で測定するリスク評価額は,損害が現実になった場合の1回当たりの平均予想損失額で表される」は正しいか。
正しくない。発生確率を掛けた期待値で評価する。
「親:子=0..1:*」ではどちらが主キーでどちらが外部キーになるか。
- 主キー:子(0..1に着目する。0..1をキーとして「使う」立場なので主キー)
- 外部キー:親
ECCとは
Error Check and Correctの略称で,ハミング符号や垂直水平パリティを用いて誤り検知・訂正を行う仕組み。
BASE特性は強い整合性を担保するか。
担保しない。結果整合性を担保する。
概念データモデルと論理データモデルをそれぞれ説明せよ。
- 概念データモデル:対象世界の情報構造を抽象化して表現したもの
- 論理モデル:関係モデル・階層モデル・ネットワークモデル等の具体的なモデルのこと
サブスキーマは外部・概念・内部スキーマのいずれであるか。
外部スキーマ
SOAを(サービス指向アーキテクチャ)を支えるミドルウェアは何か。
ESB(Enterprise Service Bus)。サービスをバス型のトポロジーで接続し,非同期処理を実現できる。
デッドロックを防ぐためには参照するレコードにも専有ロックをかけるべきか。
かけるべきではない。参照時も専有ロックをかけてしまうとデッドロックが発生しやすくなってしまう。
デッドロックを検出するデータ構造およびその方法を述べよ。
待ちグラフでループが存在する場合はデッドロックが発生している。
GROUP BYを用いた選択項目リストでCOUNT(列名)とCOUNT(*)にはどのような違いがあるか。
- COUNT(列名):NULLを除外してCOUNT
- COUNT(*):NULLを含めてCOUNT
表領域はメモリとディスクのどちらに保存するか。
ディスク
表領域の分類とその詳細を答えよ。
表領域種別 | 特徴 |
---|---|
ユーザ表領域 | ユーザが作成したテーブルや索引を格納する 表領域の中で最も大きな容量を占める |
システム表領域 | データディクショナリを格納する データベースのメタ情報が該当する |
ログ・ロールバック表領域 | 更新前ログ・更新後ログを格納する |
一時表領域 | SQLで一時的に利用される表領域 ソート時などに利用される |
スタースキーマでモデル化し,一定期間内に発生したイベントを格納するテーブルは何か。
ファクトテーブル。外部キーでデシジョンテーブルと紐づき,スター型のスキーマの中心に位置する。
BASE特性を満たすNoSQLデータベースの特徴を述べよ。
- BA(Basically Available):可用性優先
- S(Soft State):厳密な状態を要求しない
- E(Eventually Consistent):結果整合性
これらのことから,以下のような特徴が挙げられる。
- ノード間の通信が遮断されても稼働し続ける(可用性優先)
- データの更新は,いつかは全てのノードに反映される(結果整合性)
ある二つのトランザクションの直接可能性が保証されると判断する根拠を述べよ。
2相ロックに則っていること。すなわち「必要なデータを全てロックした後のみアンロックしている」こと。
例をいくつか挙げておく。アンロックの順番はロックの逆になる点に注意する。
-- LOCKの前にREADがあるため保証されない
READ a
LOCK a
UNLOCK a
-- 必要なデータを全てロックする前にアンロックしているため保証されない
LOCK a
WRITE a
UNLOCK a
LOCK b
WRITE b
UNLOCK b
-- 2相ロックしているため保証される
LOCK a
WRITE a
LOCK b
WRITE b
UNLOCK b
UNLOCK a
データモデルを構造化せよ。
表領域種別 | 特徴 |
---|---|
論理データモデル 外部スキーマ | 概念データモデルを関係モデル・ネットワークモデル・階層モデル等の形式で表現する 概念スキーマよりもアプリケーション側に位置する |
概念データモデル 概念スキーマ | 実世界の情報を抽象化して表現し,データ構造と制約を表現する 外部スキーマと内部スキーマの間に位置する |
物理データモデル 内部スキーマ | 論理データモデルをコンピュータ上で実装する 概念スキーマよりもデータベース側に位置する |
WALを日本語であえて翻訳するとどのような意味になるか。
「まずログを吐け」となる。データベースの実更新の前に更新前ログと更新後ログを吐き出すことで,トランザクションの原子性と永続性を担保している。データベースの実更新の前に更新後ログを吐き出す点がポイント。
複合キーが複雑な場合はどのような対処を行うか。
連番等で代理キー(サロゲートキー)を作成して,元から存在する複合キーは全て代理キーとして設定する。
DBMSのログはトランザクションの再実行とロールフォワードのどちらに利用されるか。
ロールフォワードに利用される。トランザクションの再実行はログではなくシンプルに再実行する。
関数従属性保存が成り立つ正規形を述べよ。
- 第一正規形
- 第二正規形
- 第三正規形
Hadoop/Spark/Cassandra/Accumulo/Digdagの特徴を一言で説明せよ。
表領域種別 | 特徴 |
---|---|
Hadoop | MapReduceに基づいたバッチ処理に特化 並列分散処理を行うバッチシステム |
Spark | RDD(Resilient Distributed Dataset)と呼ばれるデータ構造を用いる |
Cassandra | マスタノードをもたない分散型データベースシステム |
Accumulo | マスタノードをもたない分散型KVS |
Digdag | ワークフロー管理ツール |
CEPとは何か。
Complex Event Processingの略称で,複合イベント処理とよばれている。ストリームデータをメモリ上に展開し,あらかじめ設定した条件に合致した場合に対応するアクションを実行する処理のことを指す。
$k$次のB木が深さ$n$のときに保持するレコード数はいくつか。
B木の定義より,各ノードは最大$2k$個のレコードと$2k+1$個のポインタを保持する。全てのポインタが子を参照しているとき,各ノードは$2k+1$個の子を持つことに注意すると,ノード数は下記のように求められる。
\sum_{m=1}^{n}2k(2k+1)^{m-1} &= \frac{2k\left\{(2k+1)^{n}-1\right\}}{(2k+1)-1}
= (2k+1)^{n}-1
\end{align}
MVCCとは何か。
MultiVersion Concurrency Controlの頭文字を取った略称で,多版同時実行制御とよばれている。この制御方式は2相コミットメントにおけるトランザクションの同時並行性を向上させるための仕組みで,トランザクション開始時点のスナップショットに基づいて,更新中のデータに対する参照要求と参照中のデータに対する更新要求を並行して処理することができる。
2相ロックではデッドロックは発生する可能性はあるか。
ロック対象のデータに順序を定めていない場合は,発生する可能性はある。
NULLIF(A, B)を説明せよ。
AとBが等しいときにNULLを返す関数
主キーと外部キーを関係データベースで捉えたとき,どちらが1でどちらが多か。
- 主キー属性を持つ表:1
- 外部キー属性を持つ表:多
例えば,診療科表を主キーを持つ表,医師表を外部キー(診療科表の主キー)を持つ表としたときに,1つの診療科には複数の医師が在籍すると捉えられるので,診療科表:医師表は1対多の関係になる。
OLAP(OnLine Analytical Processing)の操作をまとめよ。
小さくする/大きくする | 入れ替える/別指定する | |
---|---|---|
集計単位 | ドリルダウン/ロールアップ | - |
分析軸 | - | ダイシング/スライシング |
なお,OLAPは「データウェアハウスやデータマートのデータで多次元分析を行う」操作のことを指す。
関係データモデルでスラッシュ/が付いている属性は何を表すか。
導出属性であること。他の属性から計算等で一意に定めることができる属性。
CAP定理を説明せよ。
分散処理システムにおいては,一貫性・可用性・分断耐性の三つの特性のうち,最大でも同時に二つまでしか満たすことができないという定理。それぞれの特性はConsistency・Availability・Partition toleranceと表されるため,これらの頭文字を取ってCAP定理となっている。
ケアレスミス集
カラムの空欄埋め問題
カラムの指定だけでなくテーブルの指定も忘れずに。{カラム名}
だけでなく{テーブル名.カラム名}
とする。
「想定よりも処理時間が長くなる」への記述問題
「索引(インデックス)が設定されておらず,全行を参照する必要があるから」など。索引が思い浮かぶように。
テーブルのストレージ容量を見積もる問題
有効ページ長 × 必要ページ数
ではなく,ページ長 × 必要ページ数
とする。有効ページ長とすると空き容量分を見積もることができていない。
BETWEENは端点を含むか含まないか
含む
参照制約のON DELETEの後にNULLを指定する文法
NULL
ではなくSET NULL
属性のドメインから最大行数を計算する問題
月単位で行を生成するのに,1年を1とカウントしない。1年は12ヶ月なので12とカウントする。
コメント