| モデルでいうと | スキーマでいうと | 具体例、成果物 |
| (プログラム) | (DB設計から見ると)ユーザインタフェース | |
| 外部スキーマ | SQLビュー。論理データ独立性 | |
| 概念データモデル | 概念スキーマ | ER図。業務仕様 |
| 論理データモデル | 内部スキーマ | テーブル定義義。物理データ独立性 |
| 物理データベース | データベース | ファイル・ディスク物理設計 |
カーディナリティ(多重度)
1 n n n
[顧客]----->[受注]<----->[商品]
エンティティ 関連 エンティティ1 n 1 n n 1 [顧客]----->[受注]----->[受注明細]<----[商品]
会員 スーパータイプ
|
_△_
| |
法人 個人 サブタイプ
会員 会員
| 実装方法 | 説明 |
| 排他的サブタイプ | 3テーブル作る |
| 共存的サブタイプ | 1テーブル作り、データ項目(カラム)の内使わない項目(カラム)はNULLにする |
| 種類 | 説明 |
| 階層型モデル | XMLの様に親子型のデータ構造。親:子=1:n |
| ネットワーク型モデル | 親:子=n:n |
| 関係型データモデル | 所謂RDBのモデル。ドメイン(データの集まり)とドメインの関係(Relation)でデータ構造をあらわす方式 |
| 関係 | 表記法 | 説明 |
| 和 | R∪S=T | OR |
| 差 | R−S=T | |
| 積 | R∩S=T | AND |
| 直積 | RXS=T | Rの要素とSの要素の順列組み合わせ。R1S1、R1S2、...、R2S1、R2S2、... |
| 商 | R÷S=T | このとき SXT=R |
| 射影 | R[A]=T | Rから重複無くA番目の要素を取り出したもの。SELECT DISTINCT A FROM R |
| 選択 | R[AθB]=T | SELECT * FROM R WHERE R.A θ R.B。θは何らかの演算子(=,>,<,etc.) |
| 等結合 | R[AθB]S=T | SELECT * FROM R,S WHERE R.A θ S.B |
| 自然結合 | R[AθB]S=T | 等結合から重複を取り除いたもの。SELECT DISTINCT * FROM R,S WHERE R.A θ S.B |
| RDB | 関係モデルであらわすと | 拡張ER図で表すと |
| テーブル | 関係 | エンティティ |
| 列 | 属性 | 属性 |
| 行 | タプル | オカレンス(インスタンス) |
| 識別子 | 説明 | NULL属性 |
| スーパーキー | タプルを一位に識別できる属性の集合 | 可 |
| 候補キー | スーパーキーの内極小なもの。無駄な項目を含んでいないもの | 可 |
| 主キー(PRIMARY KEY) | 候補キーの内実際に使うもの | 不可 |
| 外部キー(FOREIGN KEY) | 関連先のテーブルの主キーが入るカラム | 不可 |
①→②
↓
③
④→⑤| A | 原子性(Atomicity) | トランザクションは全て実行されるか、全く実行されないかのどちらか |
| C | 一貫性(Consistency) | トランザクションは、データベース内部で整合性が保たれる |
| I | 独立性(Isolation) | 複数のトランザクションが走っても、単独で実行した時と同じ結果が得られる |
| D | 耐久性(Durability) | トランザクションは障害が発生しても回復できるようにしなければならない |
| 他のユーザからの | 共有ロック | 占有ロック | どんなときにつかう? |
| 共有ロック | ○ | × | 読み込み時に他から変更されたくない時 |
| 占有ロック | × | × | 変更時 |
共有ロックしてから占有ロックすること SELECT * FROM A_TBL WHERE id='000001' FOR UPDATE UPDATE a_COLUMN SET a_COLUMN='AAAA' WHERE id='000001'
| ISOLATION LEVEL | 別名 | ダーティーリード | 繰り返し不可 | ファントムリード |
| 0 | READ_UNCOMMITED | 発生 | 発生 | 発生 |
| 1 | READ_COMMITED | 防げる | 発生 | 発生 |
| 2 | REPEATABLE_READ | 防げる | 防げる | 発生 |
| 3 | SERIALIZABLE | 防げる | 防げる | 防げる |
| tx1成功 tx2成功
| ----------->| ----------->| ------X
| システム障害発生
チェックポイント (現状)
| # | 主サイト | (通信) | 従サイト1 | 従サイト2 |
| 1 | 従サイト1にコミット要求 | コミット | ||
| 2 | 従サイト2にコミット要求 | コミット |
| # | 主サイト | (通信) | 従サイト1 | 従サイト2 |
| 1 | 従サイト1にコミット準備要求 | セキュア状態 | ||
| 2 | 従サイト2にコミット準備要求 | セキュア状態 | ||
| 3 | 従サイト1にコミット要求 | コミット | ||
| 4 | 従サイト2にコミット要求 | コミット |
| # | 主サイト | (通信) | 従サイト1 | 従サイト2 |
| 1 | 従サイト1にコミット準備要求 | セキュア状態 | ||
| 2 | 従サイト2にコミット準備要求 | セキュア状態 | ||
| 3 | 従サイト1にプリコミット要求 | (なにもしない) | ||
| 4 | 従サイト2にプリコミット要求 | (なにもしない) | ||
| 3 | 従サイト1にコミット要求 | コミット | ||
| 4 | 従サイト2にコミット要求 | コミット |
CREATE INDEX 従業員テーブルINDEX表 ON 従業員テーブル (name(10)) 名前の最初10文字でインデックス表を作る
一般のHash表 Hashキー Hash値 0001 AAAAA 0002 BBBBB 0003 CCCCC チェイニング法 Hashキー Hash値のリスト 0001 AAAAA,AAAAB,AAAAC 0002 BBBBB,BBBBC,BBBBD 0003 CCCCC,CCCCD,CCCCE
| 正規形 | テーブル内の属性を全て単純属性にする | テーブル内のキー項目に対する部分関数従属を削除 | テーブル内の推移的関数従属を削除 |
| 非正規形 | × | × | × |
| 第一正規形 | ○ | × | × |
| 第二正規形 | ○ | ○ | × |
| 第三正規形 | ○ | ○ | ○ |
-----------------------------------------------------------------------------
受注番号 商品番号1 商品名1 単価1 商品番号2 商品名2 単価2 商品番号3 商品名3
単価3 数量 顧客番号 顧客名 顧客住所
PK(受注番号)
-----------------------------------------------------------------------------
↓
-----------------------------------------------------------------------------
受注番号 商品番号 商品名 単価 数量 顧客番号 顧客名 顧客住所
PK(受注番号,商品番号)
----------------------------------------------------------------------------------------------------------------------------------------------------------
受注番号 商品番号 商品名 単価 小計 数量 顧客番号 顧客名 顧客住所
PK(受注番号,商品番号)
-----------------------------------------------------------------------------
↓
-----------------------------------------------------------------------------
受注番号 商品番号 数量 小計 顧客番号 顧客名 顧客住所
PK(受注番号,商品番号)
商品番号 商品名 単価 PK(商品番号)
----------------------------------------------------------------------------------------------------------------------------------------------------------
受注番号 商品番号 数量 小計 顧客番号 顧客名 顧客住所
PK(受注番号,商品番号)
商品番号 商品名 単価 PK(商品番号)
-----------------------------------------------------------------------------
↓
-----------------------------------------------------------------------------
受注番号 商品番号 数量 顧客番号 ※小計を削除
PK(受注番号,商品番号)
商品番号 商品名 単価 PK(商品番号)
顧客番号 顧客名 顧客住所 PK(顧客番号)
-----------------------------------------------------------------------------| id | 属性X | 属性Y | 属性Z |
| t1 | 1 | a | A |
| t2 | 1 | a | B |
| t3 | 1 | b | A |
| t4 | 1 | b | B |
-----------------------------------------------------------------------------
商品種別 商品名 製造元 PK(商品番号) // ボイスゴット正規化済み
-----------------------------------------------------------------------------
↓
-----------------------------------------------------------------------------
商品種別 商品名 PK(商品種別)
商品種別 製造元 PK(商品種別)
----------------------------------------------------------------------------------------------------------------------------------------------------------
卸先店 商品種別 製造元メーカー(PK 卸先店,商品種別,製造元)
-----------------------------------------------------------------------------
↓
-----------------------------------------------------------------------------
卸先店 商品種別 (PK 卸先店,商品種別)
卸先店 製造元メーカー (PK 卸先店,製造元)
商品種別 製造元メーカー (PK 商品種別,製造元)
-----------------------------------------------------------------------------Y ⊂ X のとき、X->Yが常に成立すること ex. X : 商品番号 Y : 商品番号、商品名、単価
CREATE SCHEMA スキーマ名 AUTHORIZATION ユーザ
CREATE TABLE USER_TBL (
ID NUMERIC(16),
NAME VARCHAR(255) NOT NULL,
AGE NUMERIC(3) CHECK(AGE<30),
SEX CHAR(1) DEFAULT "F",
MAIL VARCHAR(255) NOT NULL,
PRIMARY KEY (ID)
)
CREATE TABLE MESSAGE_BOX_TBL (
MESSAGE_ID NUMERIC(16) PRIMARY KEY,
MESSAGE BLOB,
USER_ID NUMERIC(16) NOT NULL,
FOREIGN KEY (USER_ID) REFERENCES USER_TBL(ID)
ON DELETE CASCADE
ON UPDATE CASCADE
)
| NO ACTION | 何もしない。FOREIGN KEY属性にON〜を書かないとこれが設定される |
| CASCADE | レコードを削除する |
| SET NULL | 外部参照列にNULLをセット |
| SET DEFAULT | 外部参照列にデフォルト値をセット |
| NO ACTION | 何もしない。FOREIGN KEY属性にON〜を書かないとこれが設定される |
| CASCADE | 外部参照列を更新する |
| SET NULL | 外部参照列にNULLをセット |
| SET DEFAULT | 外部参照列にデフォルト値をセット |
CREATE ASSERTION USER_TBL CHECK( AGE>18 AND AGE<30 )
CREATE VIEW USER_POSTING_LIST ( ID , NAME , MSG_ID , MSG ) AS
SELECT USER.ID , USER.NAME , MSG.MESSAGE_ID , MSG.MESSAGE
FROM USER_TBL USER , MESSAGE_BOX MSG
WHERE USER.MAIL = MSG.MAIL
| 権限 | できること |
| ALL PRIVILEGES | 全部 |
| SELECT | SELECT |
| INSERT | INSERT |
| DELETE | DELETE |
| UPDATE | UPDATE |
SELECT 商品番号 , 正価 FROM 受注テーブル
SELECT COUNT(*) FROM 受注テーブル
SELECT 商品番号 , 正価 , 正価*1.05 AS 請求額 FROM 受注テーブル
SELECT '商品番号='|| 商品番号 , '正価='|| 正価 , '請求額='|| 正価*1.05 AS 請求額 FROM 受注テーブル
SELECT * FROM 受注テーブル WHERE 顧客ID='00456'
SELECT * FROM 受注テーブル WHERE 納品先住所 IS NULL
SELECT DISTINCT 商品番号 FROM 受注テーブル WHERE 顧客ID='00456'
SELECT * FROM 受注テーブル WHERE 受注日 BETWEEN '2000-01-01' AND '2004-12-31'
(eq.) SELECT * FROM 受注テーブル WHERE 受注日 >= '2000-01-01' AND 受注日 <='2004-12-31'
SELECT *
FROM 受注テーブル
WHERE 受注日 IN('2003-11-10','2003-11-11')SELECT *
FROM 受注テーブル
WHERE 受注日 IN( SELECT 日時
FROM 日付マスタ
WHERE 日時属性='休業' )
SELECT *
FROM 受注テーブル X
WHERE EXISTS( SELECT *
FROM 日付マスタ Y
WHERE X.受注日 = Y.日付 AND Y.日時属性='休業' )1件以上の受注があった日の受注金額の合計 SELECT 受注日,SUM(受注金額) AS 合計受注金額 FROM 受注テーブル GROUP BY 受注日 HAVING COUNT(*) > 0
SELECT 受注日,SUM(受注金額) AS 合計受注金額 FROM 受注テーブル GROUP BY 受注日 ORDER BY 受注日 // 省略時はASC
SELECT 受注日,SUM(受注金額) AS 合計受注金額 FROM 受注テーブル GROUP BY 受注日 ORDER BY 受注日 ASC
SELECT 受注日,SUM(受注金額) AS 合計受注金額 FROM 受注テーブル GROUP BY 受注日 ORDER BY 2 DESCORDER BYの後にSQL関数を書くとエラー → 行数で指定
| ID | PHRASE |
| 1 | 秋の田の かりほの庵の 苫をあらみ |
| 2 | 春過ぎて 夏来にけらし 白妙の |
| 3 | あしびきの 山鳥の尾の しだり尾の |
| 4 | 田子の浦に うち出でて見れば 白妙の |
| 5 | 奥山に 紅葉ふみわけ 鳴く鹿の |
| ID | PHRASE |
| 1 | 我が衣手は 露にぬれつつ |
| 2 | 衣ほすてふ 天の香具山 |
| 3 | ながながし夜を ひとりかも寝む |
| 6 | 白きを見れば 夜ぞ更けにける |
| 7 | 三笠の山に 出でし月かも |
SELECT U.ID , U.PHRASE , L.PHRASE FROM UPPER_TBL U , LOWER_TBL L WHERE U.ID = L.ID
| U.ID | U.PHRASE | L.PHRASE |
| 1 | 秋の田の かりほの庵の 苫をあらみ | 我が衣手は 露にぬれつつ |
| 2 | 春過ぎて 夏来にけらし 白妙の | 衣ほすてふ 天の香具山 |
| 3 | あしびきの 山鳥の尾の しだり尾の | ながながし夜を ひとりかも寝む |
SELECT U.ID , U.PHRASE , L.PHRASE FROM UPPER_TBL U FULL JOIN LOWER_TBL L ON U.ID = L.ID
| U.ID | U.PHRASE | L.PHRASE |
| 1 | 秋の田の かりほの庵の 苫をあらみ | 我が衣手は 露にぬれつつ |
| 2 | 春過ぎて 夏来にけらし 白妙の | 衣ほすてふ 天の香具山 |
| 3 | あしびきの 山鳥の尾の しだり尾の | ながながし夜を ひとりかも寝む |
| 4 | 田子の浦に うち出でて見れば 白妙の | null |
| 5 | 奥山に 紅葉ふみわけ 鳴く鹿の | null |
| 6 | null | 白きを見れば 夜ぞ更けにける |
| 7 | null | 三笠の山に 出でし月かも |
SELECT U.ID , U.PHRASE , L.PHRASE FROM UPPER_TBL U LEFT JOIN LOWER_TBL L ON U.ID = L.ID
| U.ID | U.PHRASE | L.PHRASE |
| 1 | 秋の田の かりほの庵の 苫をあらみ | 我が衣手は 露にぬれつつ |
| 2 | 春過ぎて 夏来にけらし 白妙の | 衣ほすてふ 天の香具山 |
| 3 | あしびきの 山鳥の尾の しだり尾の | ながながし夜を ひとりかも寝む |
| 4 | 田子の浦に うち出でて見れば 白妙の | null |
| 5 | 奥山に 紅葉ふみわけ 鳴く鹿の | null |
SELECT L.ID , U.PHRASE , L.PHRASE FROM UPPER_TBL U RIGHT JOIN LOWER_TBL L ON U.ID = L.ID
| L.ID | U.PHRASE | L.PHRASE |
| 1 | 秋の田の かりほの庵の 苫をあらみ | 我が衣手は 露にぬれつつ |
| 2 | 春過ぎて 夏来にけらし 白妙の | 衣ほすてふ 天の香具山 |
| 3 | あしびきの 山鳥の尾の しだり尾の | ながながし夜を ひとりかも寝む |
| 6 | null | 白きを見れば 夜ぞ更けにける |
| 7 | null | 三笠の山に 出でし月かも |
INSERT
INTO 受注テーブル(
受注ID
顧客ID,
商品ID,
数量,
納品フラグ,
受注日
納品日,
削除フラグ)
VALUES( '034564',
'130576'
'J94045',
4,
0
'2005-03-31'
null
'0')UPDATE 受注テーブル SET 納品フラグ=1 , 納品日='2005-04-02' WHERE 受注ID = '034564'
DELETE FROM 受注テーブル WHERE 削除フラグ='1'