データベース

暗記

aaa 2024年04月20日カード82 いいね0

2024.04.20

階層モデル、網モデル、関係モデル

階層モデル：木構造で親は一つで子は複数可、多対多は冗長に、網モデル：多対多も可、関係モデル：二次元の表
3層スキーマ、ビューとインデックスはどれか

外部スキーマ、論理スキーマ、内部スキーマ
ビューは外部、インデックスは内部
インメモリデータベース、その指向

メモリに配置。列指向（カラム指向）
連関エンティティ

多対多のときに、介入されたエンティティ。
顧客と商品が多対多のときに、間に注文を入れることで顧客と注文が一対多、注文と商品が多対一になる。
交差データ

顧客と商品の二つが特定されたときに、注文テーブルの注文日や注文数量が特定されるときに、注文日や注文数量は交差データ
弱エンティティ、強エンティティ、依存エンティティ、独立エンティティ

弱エンティティ：弱実体。顧客や商品に依存することで存在できている注文エンティティ、依存エンティティとも。強エンティティ：強実体。独立エンティティ。他のエンティティに依存せずに存在する顧客や商品。
ドメイン（定義域）

データの型。kingakuという型を登録して、integerの0以上の列に使ったり。
ドメイン登録のSQL

CREATE DOMAIN KINGAKU INTEGER CHECK(KINGAKU >= 0)

create table 商品 (番号 char(4), 商品名 char(4), 単価 kingaku)
のような
スーパキー、主キー、代理キー、候補キー

スーパキーの中に候補キー。候補キーの中で主キーじゃないものが代理キー。
スーパキー：表中の行を一意に識別できる列、候補キー：スーパキーの中で余分な属性を含まずに一意に識別できる列。空値も許される。主キー：一意制約とnot null制約で主キー制約。
代用のキー

複合キーが多くなると運用で煩雑になるため、連番等の人工的な属性を追加して主キーにしたり。
多値従属、そのx, yの表現

x→→y。xに対して一つのyではなくyの集合が決まる関係。xで果物が決まれば、yはりんごやぶどうの中のどれかに決まるみたいな。
独立属性（決定項）、従属属性（従属項）

x→y。xが決まればyが決まる関係。
完全関数従属、部分関数従属

xがx1、x2から構成されるときに、{x1, x2}→yが完全関数従属、x1→yが部分関数従属
xが一つの属性だけで構成される場合は常に完全関数従属
推移的関数従属、完全推移的関数従属

推移的関数従属：x→y、y→z、y→xが成立していないときにx→z、完全全推移的関数従属：z→yも成立していなければ完全
第一正規形、第二正規形、第三正規形

第一正規形：表は作れるけどエンティティに分かれていない、第二正規形：主キーになってる項目は表内で従属していないように。第三正規形：主キーになっていない項目も表内で従属していないように。
表の和、共通、差、直積

和：a+b。unionは重複を削除。union allは重複を削除しない。a union b。共通：積。どちらにも属する行。a intersect b。差：aに属してbに属さない行。a except b。直積：selectのfromで複数表を設定した場合。cross joinと一致。行数は二つの表の行数を掛けた数。
射影のパターンの数、列数をn

列ごとに取り出すかどうかのパターンがあるので、2^n
Θ（シータ）結合

結合列の値を>, ≧, =, ≠, <, ≦で結びつける演算。
商演算

表R/表Sのとき、表Sの行の内容を含む表Rの行のうち、表Sに含まれない列を取り出す。その際に重複行も削除される。
grant, revoke、declare cursor, open, fetch, close

grant：表に対するアクセス権の付与、revoke：アクセス権の削除、declare cursor：カーソルの割り当て（宣言・定義）、open：カーソルのオープン、fetch：カーソルからデータの取り出し、close：カーソルのクローズ
DDL、DML

DDL：データ定義言語、DML：データ操作言語
select文

select [ALL | distinct]　選択リスト
　　from 参照表リスト
　　[where 条件]
　　[group by グループ化列名リスト]
　　[having グループ選択条件]
nullかどうかもしくはnullじゃないかどうかの判定、between述語、in述語、like述語

nullかどうか：A IS （NOT)　NULL、between：A BETWEEN 10 and 20、IN述語：A IN (10, 20, 30)。副問い合わせではA IN (select A from B where C BETWEEN 10 and 20)、LIKE：A LIKE '%子'。%は0文字以上、_は1文字
AVG, ASC, DESC

AVG：平均、ASK, DESC：order by A ASK（DESC）
内結合（INNER JOIN）

普通のFROMで複数の表を指定して、whereで条件した場合と同じ。
select A
FROM B表 INNER JOIN C表
ON A表.D = C表.D
最後にon句の代わりに「using D」とする方法も
外結合（OUTER JOIN）、{LEFT, RIGHT, FULL} OUTER JOIN

FROM A {LEFT, RIGHT, FULL} OUTER JOIN B
ON A.C = B.C

LEFT, RIGHT OUTER JOIN：キーワードの左側（右側）の表を基準にして、右側にない行はNULLで埋めて結合。FULL：どちらかにない行はNULLで埋めて結合。どちらかに存在する行は出力される。
最後にon句の代わりに「using C」とする方法も
nullじゃないほうを値として使用

SELECT COALESCE (A, B)
AがNULLでなければAを、AがNULLであればBを、どちらもNULLであればNULLを返す。
(NOT) EXISTS

副問い合わせの結果が存在すればtrue。
select A from B
where EXISTS
(select * from A where B.C = D.C)
UNION, INTERSECT, EXCEPT, CROSS JOIN

UNION：和、INTERSECT：共通、EXCEPT：差、CROSS JOIN：直積
select * FROM A
(UNION | INTERSECT | EXCEPT | CROSS JOIN)　
SELECT * FROM B
WITH句

一時的な表を作成。共通表式。
WITH AA (A, B)
AS (SELECT C, COUNT(*)
FROM D GROUP BY E)
SELECT A, B
FROM AA
WHERE B = (SELECT MAX(B) FROM AA)
case句

条件で出力変化。
select a,
case
when b > 10 then "big"
when b = 10 then "equal"
else "small"
end as hantei
from C
insert文、副問い合わせでinsert

INSERT INTO A [(列名リスト)]
　　VALUES (値リスト)
INSERT INTO A [(列名リスト)]
　　問い合わせSELECT文
update文

update A表 set A=B [where 条件]
delete文

delete from A [where 条件]
NO ACTION, RESTRICT, CASCADE, SET DEFAULT, SET NULL

参照動作。
NO ACTION：一旦実行するが、参照行があれば削除（変更）は取り消される
RESTRICT：参照行があれば削除（変更）は拒否される
CASCADE：参照している行があればその行も削除（変更）される。更新が連鎖する。
SET DEFAULT：参照している行があればdefault値に変更される
SET NULL：参照している行があればNULL値に変更される
references

REFERENCES 非参照表（参照する列リスト）
[ON DELETE 参照動作]
[ON UPDATE 参照動作]
character, character varying, integer, numeric(n[, m]), bit(n), bit varying, blob

character：char(n)。nバイトの固定長文字。character varying：varchar(n)。最大nバイトの文字。integer：int。整数値。numeric(n[, m])：最大n桁、小数点以下m桁。bit：nビットの固定長ビット列。bit varying：varbit。最大nビットのビット列。blob(x)：ビット列。k（キロ）、M（メガ）、G（ギガ）を用いて指定。
FOREIGN KEY

外部キー。referencesでは商品表等、どの表を参照するか。
FOREIGN KEY (A) REFERENCES 別表(別表の列) ON DELETE CASCADE
trigger文

CREATE TRIGGER トリガ名
　　（AFTER | BEFORE） INSERT ON 表名
　　　　INSERTをきっかけに実行する更新処理
updateの場合はUPDATE [of 列名A, 列名B]で列名の指定も可
CREATE VIEW文

CREATE VIEW ビュー名 [(列A, 列B)]
AS 問い合わせSELECT文
grant文

grant (権限リスト | ALL PRIVILEGES) ON 表名 TO 利用者リスト
特定の列にのみ
grant select(A列, B列) ON C表 to ユーザD
grantの権限を他のユーザーにも与えられるように

そのユーザに与えた権限を他の人にも付与する許可
grant select on A表 to ユーザB WITH GRANT OPTION
A表をselectする権限をユーザBに付与して、ユーザBはselect権限を他の人にも渡す権限も持つ
revoke

revoke (権限リスト | all privileges) ON 表名 from 利用者リスト
EXEC

1行だけをINTO句に指定した変数で受け取る
EXEC SQL SELECT 列名リスト INTO :ホスト変数名リスト from 表名 [where 条件]

exec sql select A, B into :C, :D from E表 where F = '100';
カーソル処理の流れ

EXEC SQL DECLARE カーソル名 CURSOR FOR select文
(select文の問い合わせにカーソル名を付ける)

EXEC SQL OPEN カーソル名
(select文が実行されカーソルは実行結果の先頭行)

while(1) {
EXEC SQL FETCH カーソル名 INTO 結果受け取りのホスト変数
(一行ずつ取り出してホスト変数へ。カーソルは次の行。取り出す行がなくなった時SQLCODEにNOT FOUND)
SQLCODEは0ならば正常終了、負はNOTFOUND又はエラー

行が残っていればfetch文へ
}

データの終わりならば
EXEC SQL CLOSE カーソル名
カーソルで取り出した行をupdateやdeleteするSQL文

WHERE CURRENT OF カーソル名
コミットやロールバックのSQL文

EXEC SQL COMMIT、EXEC SQL ROLLBACK
ACID特性

原子性：Atomicity。全て完了か全く実行されていないか。all or nothing。commitとrollback。
一貫性：Consistency。データの矛盾がなく整合性がとれていること。
隔離性：Isolation。複数を同時に実行しても順に実行した場合が一致。独立性。
耐久性：Durability。いったん正常終了した結果は消失しないこと。
TPモニタ

アプリケーションとDBMSの間のミドルウェア。トランザクションのACID特性を保証するトランザクション管理機能。
変更消失、ダーティリード

変更消失：一つのトランザクションが開始されてからコミットするまでの間に別のトランザクションが読み込み始めると隔離性が保たれなくなることがある。ダーティリード：あるトランザクションがコミットされる前の値を読んでしまうこと。
解決方法としてロック。同時実行制御。
2層ロック方式のデッドロック、直列可能性

2層ロックは全てのロックを獲得した後に操作を行ってから全ての解除をする。直列可能性は保証されるがデッドロックの可能性は残る。
木規約のデッドロック、直列可能性

木でデータに順番をつけてそこまで辿るものをロックする。直列可能性は保証されてデッドロックも回避できる。
多版同時実行制御

他のトランザクションが更新中のデータに参照要求を行った場合、トランザクションの実行前のデータを使用して同時実行性と一貫性を確保。
アンリピータブルリード、ファントムリード

整合性を欠いたデータ参照。
アンリピータブルリード：再度読み込んだデータが他のトランザクションより更新されていて前回読み込んだ値と一致していない。ファントムリード：再度読み込んだデータの中に新たに追加されたデータや削除されたものがあって一致していない。
時刻印方式（時刻印アルゴリズム）、楽観的方式

いずれもロックを使っていないためにデッドロックは発生しない。
時刻印方式：トランザクションの開始時刻をT、データの最終読み込み時刻をTr、最終書き込み時刻をTw。読み込みはTw≦Tのときのみで終了後にTrにTを設定、書き込みはTw≦TかつTr≦TのときのみでTwにTを設定。
楽観的方式：同じデータへのアクセスはめったに発生しないと考え、書き込む直前に他のトランザクションで更新されていたらロールバック。
WALプロトコル

write ahead log（ログを先に書く）。メモリ上のバッファの内容をデータベースに書き出す前にログを残す。ログが残っていればメモリ上のバッファのデータが消失してもundoやredoができる。
チェックポイント時の処理、チェックポイントの効果、チェックポイントの発生タイミング

データバッファとログバッファを書き出す。障害の発生時にはチェックポイントからの回復処理を行えばよくなる。データベースバッファに空きがなくなったときかログファイルが切り替わるときに発生。
コミット時にはデータバッファとログバッファは書き出されるかどうか

コミット時にはデータバッファは書き出されないがログバッファは書き出される。
RPO、RTO

RPO：目標復旧時点。recovery point objective。障害発生前のどの時点に復旧するか。データ損失の最大許容範囲。RTO：目標復旧時間。recovery time objective。どのぐらいの時間で復旧させるか。障害停止の最大許容時間。
フルバックアップ、差分バックアップ、増分バックアップ

フルバックアップ：全体をバックアップ、差分バックアップ：直前のフルバックアップから、増分バックアップ：直前のフルバックアップまたは増分バックアップから
ビットマップインデックス、ハッシュインデックス

データ値の種類が少ない場合に、男・女のそれぞれを概念的に男列、女列のように用意してそれぞれに0,1を登録して管理するようなインデックス。
ハッシュインデックス：ハッシュを用いて一意検索。範囲や不等号には不向き。
B+木インデックス

値一致検索だけではなく範囲検索にも優れている。木によって範囲や順次検索。木の深さに比例するため索引部に多くのキー値を持たせるて効率化。
オプティマイザ、コストベース、ルールベース

オプティマイザ：クエリ最適化、コストベース：I/O回数やバッファの使用状況といった「統計情報」を基に最適なアクセス方法や結合順序、結合方法を選択。
ルールベース：SQL文を所定のルールに基づいてアクセスパスを選択。同じSQL文ならば同じアクセスパス。全表を走査するほうが高速な場合でもインデックスがあればインデックスを使用。
DD/D、集中管理方式、分散管理方式

DD/D：データディクショナリ/ディレクトリ。表の格納場所や表の構造などの情報を持つデータ辞書。集中管理方式：分散DBのグローバルDD/Dで一つのサイトでDD/Dを管理。他のサイトを調べる必要はないがアクセスが集中しやすく、そこで障害が起こると全体に影響する。
分散管理方式：重複保有なしは各サイトがDD/Dを持って表の変更は各サイトを変更するだけでよいが自サイトにない表は他のサイトを調べる必要。完全重複は各サイトに全てのDD/Dを持たせる方法で自サイトのDD/Dを見れば全てが把握できるが変更のときは全てのサイトを変更する必要あり。部分重複は各サイトにいくつかのDD/Dを持たせる。
セミジョイン法、ハッシュセミジョイン法

分散DBの表結合。セミジョイン法：いくつかの列の情報だけを送って成功したものだけを返す。ハッシュセミジョイン法：セミジョイン法にハッシュ結合を組み合わせ。結合列のハッシュ値を送ってハッシュ結合。
分散DBの結合の入れ子ループ法、その計算量

1行ずつ相手のサイトに送って、相手先では全ての行と比較して結合。計算量はO(n^2)。
分散DBのマージジジョイン法（ソートマージ法）、ハッシュ法

マージジョイン法：結合列の値でソートした二つの表を突き合わせて結合。分散DBではソートした表を相手に送る。
ハッシュ結合：行数の少ない表の結合列のハッシュ表を作成して結合列をハッシュにかけて結合。
レプリケーション

時間間隔でマスタデータベースとデータの同期を取る。
2層コミットメント、セキュア状態（中間状態）、ブロック状態、3相コミットメント

二つのDBともコミットが可能な場合にコミット、セキュア状態：コミット可否の問い合わせの返答が返ってきたときにコミットもロールバックも可能な状態、ブロック状態：セキュア状態中に操作側にトラブルが発生するとコミットかロールバックか判断不能な状態に。3相コミットメント：セキュアの後にプリコミットしてコミット。
データウェアハウス、ETL、データクレンジング、データマート

データウェアハウス：様々な活動を通して得られたデータを蓄積しておいて意思決定支援に利用するデータベースあるいはその管理システム。
ETL：元のデータベースから抽出、変換、データウェアハウスへ書き出しの流れを。データクレンジング：データウェアハウス用にデータを変換。ETLツールではその機能も。
データマート：データウェアハウスのデータを用途や部門別に切り出したデータベース。
OLAP、スライス、ドリルダウン、ロールアップ、ダイス

OLAP：オンライン分析処理。多次元データを様々な視点から対話的に分析。
スライス：一つの属性項目の特定の値で切り出す。
ドリルダウン：任意の切り口のデータを深いレベルで。
ロールアップ：ドリルダウンの逆に浅いレベルで。
ダイス：時間/地域/製品を地域/製品/時間のように回転。
MOLAP、ROLAP、そのテーブル

MOLAP：多次元そのままで管理するOLAP。ROLAP：関係データベースでスタースキーマ構造で管理するOLAP。ファクトテーブル、ディメンションテーブル。
スタースキーマ、ファクトテーブル、ディメンションテーブル

スタースキーマ：多次元構造に適合したリレーショナルスキーマ。ファクトテーブル：事実テーブル。スタースキーマの中央に置く。ディメンションテーブル。ファクトテーブルの周りに置く次元テーブル、切り口。外部キーで繋がっている。
マーケットバスケット分析、クラスタ分析

POSデータやeコマースのログを分析して顧客が一緒に購入している商品の組み合わせを発見するデータ分析手法。一人の一回の購入データをマーケットバスケットデータ。
クラスタ分析：似たものを集めたクラスタに分類。
CEP

複合イベント処理。complex event processing。刻一刻と発生する膨大なデータをリアルタイムで分析して処理。
ビッグデータの三つのV。５つのV。

Variety：データ種類が多様。Volume：データ量が膨大。Velocity：発生速度、発生頻度が高い。5つの場合、Value：価値。Veracity：正確さ・信頼性。
キーバリューDB(アルファベットでも)、カラム指向DB、ドキュメント指向DB、グラフ指向DB

キーバリューDB：KVS。key value store。データをキーに関連付けて。カラム指向DB：キーバリューDBにカラムの概念を持たせたもの。ドキュメント指向DB：キーバリューペアーの拡張、XMLやJSONのドキュメント単位で管理する。グラフ指向DB：ノードとノードの間のエッジやノードとエッジの属性で全体を構造化して管理
BASE特性

結果整合性の特性。Basically Available：可用性が高く基本的にいつでも利用可能。Soft state：厳密に常に整合性を保つ必要はない。Eventually consistent：最終的には一貫性が保たれる。
データレイク

ビッグデータの貯蔵場所。
単一障害点、アルファベットでは

その箇所が故障するとシステム全体が停止となる箇所のこと。SPOF。single point of failure。
CAP定理

一貫性（整合性）：Consistency、可用性：Availability、分断耐性：Partition Tolerance。
alter table、制約、列の追加削除変更

alter table 表1 add constraint 制約名制約(列名リスト)
alter table 予約明細 add constraint not_duplication UNIQUE(部屋ID, 宿泊日)

ALTER TABLE TABLE1 ADD column COLUMN4 VARCHAR(10)
alter table table1 drop column column5
alter table table1 alter column column5 int
次の単語帳へ (5/8) テストする

よく頑張りました

暗記スタート

データベース

暗記

ビューア設定