DB設計で行うこと

  1. DB設計の標準化
    1. データ項目:カラム名・型
    2. データ整合性:範囲・一意制約・非ヌル・参照制約
    3. コード体系
  2. DB設計はレイヤごとに抽象度の違う作業を行う
    モデルでいうとスキーマでいうと具体例、成果物
    (プログラム)(DB設計から見ると)ユーザインタフェース
    外部スキーマSQLビュー。論理データ独立性
    概念データモデル概念スキーマER図。業務仕様
    論理データモデル内部スキーマテーブル定義義。物理データ独立性
    物理データベースデータベースファイル・ディスク物理設計

概念データモデル

  1. 拡張ER図(論理データモデルもER図で表すことができる)
  2. EntityのRelationship(関連)を表したもの
           カーディナリティ(多重度)
          1    n      n      n
    [顧客]----->[受注]<----->[商品]
    エンティティ       関連        エンティティ
  3. 連関 = n:nの関連をさける為に便宜的に作られるエンティティ
          1    n      1    n          n   1
    [顧客]----->[受注]----->[受注明細]<----[商品]
  4. 汎化(継承)
       会員     スーパータイプ
        |
      _△_
     |    |
    法人  個人  サブタイプ
    会員  会員
    実装方法説明
    排他的サブタイプ3テーブル作る
    共存的サブタイプ1テーブル作り、データ項目(カラム)の内使わない項目(カラム)はNULLにする

論理データモデル

  1. 論理データモデルの種類
    種類説明
    階層型モデルXMLの様に親子型のデータ構造。親:子=1:n
    ネットワーク型モデル親:子=n:n
    関係型データモデル所謂RDBのモデル。ドメイン(データの集まり)とドメインの関係(Relation)でデータ構造をあらわす方式
  2. 関係データモデル
    1. 関係
      関係表記法説明
      R∪S=TOR
      R−S=T
      R∩S=TAND
      直積RXS=TRの要素とSの要素の順列組み合わせ。R1S1、R1S2、...、R2S1、R2S2、...
      R÷S=Tこのとき SXT=R
      射影R[A]=TRから重複無くA番目の要素を取り出したもの。SELECT DISTINCT A FROM R
      選択R[AθB]=TSELECT * FROM R WHERE R.A θ R.B。θは何らかの演算子(=,>,<,etc.)
      等結合R[AθB]S=TSELECT * FROM R,S WHERE R.A θ S.B
      自然結合R[AθB]S=T等結合から重複を取り除いたもの。SELECT DISTINCT * FROM R,S WHERE R.A θ S.B
    2. データ構造
      RDB関係モデルであらわすと拡張ER図で表すと
      テーブル関係エンティティ
      属性属性
      タプルオカレンス(インスタンス)
    3. タプルの識別子
      識別子説明NULL属性
      スーパーキータプルを一位に識別できる属性の集合
      候補キースーパーキーの内極小なもの。無駄な項目を含んでいないもの
      主キー(PRIMARY KEY)候補キーの内実際に使うもの不可
      外部キー(FOREIGN KEY)関連先のテーブルの主キーが入るカラム不可
    4. 関数従属性図からの候補キーの見つけ方
      1. 関数従属性に従い、全ての属性を導ける属性集合を見つける
      2. 要はどこからも矢印が来ていない属性(下図の´ぁ砲鮓つけ出し、それらの組み合わせで全ての要素が説明できるかどうかを調べる。
        、
            ↓
            
        
        あ
    5. テーブル定義書から主キーの見つけ方
      1. 〜コードが主キーになっているんだろうなぁと目星をつける
      2. ER図を書いて外部キーの目星をつける
      3. 問題文を読む

トランザクション

  1. トランザクションはACID属性を持つ
    A原子性(Atomicity)トランザクションは全て実行されるか、全く実行されないかのどちらか
    C一貫性(Consistency)トランザクションは、データベース内部で整合性が保たれる
    I独立性(Isolation)複数のトランザクションが走っても、単独で実行した時と同じ結果が得られる
    D耐久性(Durability)トランザクションは障害が発生しても回復できるようにしなければならない
  2. 原子性(A)
    • BEGIN / COMMIT / ROLLBACK
  3. 一貫性(C)
    • ロックの種類
      他のユーザからの共有ロック占有ロックどんなときにつかう?
      共有ロック×読み込み時に他から変更されたくない時
      占有ロック××変更時
    • 2相ロック
      共有ロックしてから占有ロックすること
      SELECT * FROM A_TBL WHERE id='000001' FOR UPDATE
      UPDATE a_COLUMN SET a_COLUMN='AAAA' WHERE id='000001'
    • ロックの粒度
      1. DB
      2. TABLE
      3. PAGE
      4. TAPLE(RECORD)
    • デッドロック
    • ユーザ操作も含んだロック
      • 楽観ロック?・・・TIMESTAMP行やVERSION行などを使った早い者勝ちの変更
      • 悲観ロック・・・LOCKフラグ行を使って他からレコードを仕えなくする
  4. 独立性(I)
    ISOLATION LEVEL別名ダーティーリード繰り返し不可ファントムリード
    0READ_UNCOMMITED発生発生発生
    1READ_COMMITED防げる発生発生
    2REPEATABLE_READ防げる防げる発生
    3SERIALIZABLE防げる防げる防げる
    • ダーティーリード
      • 別トランザクションがコミットしていない変更が見える
    • 繰り返し不可
      • トランザクション内で発行したSELECT文と同じSELECT文をもう一度発行しても結果が同じ事は保証されない
      • 別トランザクションによるUPDATEが許可されている
    • ファントムリード
      • トランザクション内で発行したSELECT文と同じSELECT文をもう一度発行してた場合、行数が増減している可能性がある
      • 別トランザクションによるINSERT/DELETEが許可されている
  5. 耐久性(D)
          |          tx1成功        tx2成功
          | ----------->| ----------->| ------X
          |                               システム障害発生
    チェックポイント                        (現状)
    1. ロールバック:障害発生時に現状からチェックポイントの状態まで戻ること
    2. ロールフォーワード:チェックポイントからコミットされた状態(tx2コミット)まで進めること

分散トランザクション

  1. 1相コミット
    #主サイト(通信)従サイト1従サイト2
    1従サイト1にコミット要求コミット
    2従サイト2にコミット要求コミット
    • (問題)障害発生時にコミットした従サイトとロールバックした従サイトがでてしまう
  2. 2相コミット
    #主サイト(通信)従サイト1従サイト2
    1従サイト1にコミット準備要求セキュア状態
    2従サイト2にコミット準備要求セキュア状態
    3従サイト1にコミット要求コミット
    4従サイト2にコミット要求コミット
    • (利点)全ての従サイトがコミットできる状態にあることを確認してからコミットするので、DBMS上のデータに起因する障害による従サイト同士の不整合は防げる
    • (問題)障害発生時に従サイトがセキュア状態のまま待ち続けてしまう
  3. 3相コミット
    #主サイト(通信)従サイト1従サイト2
    1従サイト1にコミット準備要求セキュア状態
    2従サイト2にコミット準備要求セキュア状態
    3従サイト1にプリコミット要求(なにもしない)
    4従サイト2にプリコミット要求(なにもしない)
    3従サイト1にコミット要求コミット
    4従サイト2にコミット要求コミット
    • 従サイト側でセキュア状態からのタイムアウト処理ができるようになった
      1. 従サイトは一定時間内にプリコミット要求が届かない場合にはROLLBACK
      2. その後主サイトからプリコミット要求が来た場合、主サイトに「ごめん!主サイト反応が遅いから勝手にROLLBACKしちゃった」と謝る
      3. 主サイトは分散トランザクション全体をROLLBACKすれば、従サイト間の一貫性は保持される(全ての従サイトがROLLBACKされる)

チューニング

  1. INDEX
    CREATE INDEX 従業員テーブルINDEX表 ON 従業員テーブル (name(10))
    名前の最初10文字でインデックス表を作る
    1. INDEXの実装(Hash)
      1. Keyword : シノニム・・・重複したHashキー
      2. Keyword : オープンハッシュ・・・ シノニムが発生した時には最寄りのあいているハッシュ値を使う
      3. Keyword : チェイニング・・・Hash値をリストにする
        一般のHash表
         Hashキー Hash値
         0001     AAAAA
         0002     BBBBB
         0003     CCCCC
        
        チェイニング法
         Hashキー Hash値のリスト
         0001     AAAAA,AAAAB,AAAAC
         0002     BBBBB,BBBBC,BBBBD
         0003     CCCCC,CCCCD,CCCCE
    2. B Tree
    3. B+ Tree : B Treeのリーフのリストを用意してシーケンスアクセスの性能を高めたもの
  2. 非正規化(副問い合わせが頻発する場合)
  3. ロックの粒度を小さくできないか?
  4. DBファイルが乗っているディスクを分ける
  5. ディスクキャッシュ/メモリ を沢山積む

正規化

  1. 第n正規形
    正規形テーブル内の属性を全て単純属性にするテーブル内のキー項目に対する部分関数従属を削除テーブル内の推移的関数従属を削除
    非正規形×××
    第一正規形××
    第二正規形×
    第三正規形
  2. 正規化しても良いのか?
    • 正規化は「情報無損失分解」
  3. なぜ正規化するのか?
    • 「更新時異常」を起こさない為
    • マスタ項目が散在していると(たとえば受注ごとに単価や商品名を持っていると)更新時に全てのレコードを調べて変更しなければならない。そんなの絶対何か変なことが起きるって!ということ
    • 「一事実一カ所」要は情報の局所化というあたりまえのこと
  4. 第一正規化
    • 繰り返し属性(非単純属性)を排除
      -----------------------------------------------------------------------------
      受注番号 商品番号1 商品名1 単価1 商品番号2 商品名2 単価2 商品番号3 商品名3 
      単価3 数量 顧客番号 顧客名 顧客住所
      PK(受注番号)
      -----------------------------------------------------------------------------
                                 ↓
      -----------------------------------------------------------------------------
      受注番号 商品番号 商品名 単価 数量 顧客番号 顧客名 顧客住所
      PK(受注番号,商品番号)
      -----------------------------------------------------------------------------
  5. 第二正規化
    • 第一正規化を行った後
    • キー項目に対する部分関数従属な行をを削除(キー項目が分かれば直接分かるものを削除)
      -----------------------------------------------------------------------------
      受注番号 商品番号 商品名 単価 小計 数量 顧客番号 顧客名 顧客住所
      PK(受注番号,商品番号)
      -----------------------------------------------------------------------------
                                 ↓
      -----------------------------------------------------------------------------
      受注番号 商品番号 数量 小計 顧客番号 顧客名 顧客住所
      PK(受注番号,商品番号)
      
      商品番号 商品名 単価 PK(商品番号)
      -----------------------------------------------------------------------------
  6. 第三正規化
    • 第二正規化を行った後
    • キー項目からの推移的関数従属な非キー項目を削除(キー項目が分かれば間接的に分かる非キー項目を削除)
    • 導出項目を削除
      -----------------------------------------------------------------------------
      受注番号 商品番号 数量 小計 顧客番号 顧客名 顧客住所
      PK(受注番号,商品番号)
      
      商品番号 商品名 単価 PK(商品番号)
      -----------------------------------------------------------------------------
                                 ↓
      -----------------------------------------------------------------------------
      受注番号 商品番号 数量 顧客番号    ※小計を削除
      PK(受注番号,商品番号)
      
      商品番号 商品名 単価 PK(商品番号)
      
      顧客番号 顧客名 顧客住所 PK(顧客番号)
      -----------------------------------------------------------------------------
  7. 第三正規化で止めた方が良い理由
    1. 「全体のデータ構造から業務的な関係性が失われるから」
  8. 第四正規化
    • 多値従属性
      id属性X属性Y属性Z
      t11aA
      t21aB
      t31bA
      t41bB
      t1,t4があれば、t2,t3もあるような関係(そんなデータ現実的に在るんかいな?)
    • 第三正規化を行った後
    • 多値従属性のある項目でテーブルを分割する
      -----------------------------------------------------------------------------
      商品種別 商品名 製造元 PK(商品番号) // ボイスゴット正規化済み
      -----------------------------------------------------------------------------
                                 ↓
      -----------------------------------------------------------------------------
      商品種別 商品名 PK(商品種別)
      商品種別 製造元 PK(商品種別)
      -----------------------------------------------------------------------------
  9. 第五正規化
    • 第三正規化を行った後
    • キー項目でテーブルを分割する
    • 結合従属性を満たす最小のテーブル構成にする。つまり表結合により元の情報が復元できる最小単位までテーブルを分割すること。
      -----------------------------------------------------------------------------
      卸先店 商品種別 製造元メーカー(PK 卸先店,商品種別,製造元)
      -----------------------------------------------------------------------------
                                 ↓
      -----------------------------------------------------------------------------
      卸先店 商品種別         (PK 卸先店,商品種別)
      卸先店 製造元メーカー   (PK 卸先店,製造元)
      商品種別 製造元メーカー (PK 商品種別,製造元)
      -----------------------------------------------------------------------------
  10. ボイス・ゴット正規化
    • テーブル内の全ての属性がキー項目との自明な関数従属性にあるもの
      Y ⊂ X のとき、X->Yが常に成立すること
      ex.
      X : 商品番号
      Y : 商品番号、商品名、単価
    • ボイス・ゴット正規化は第五正規化(従って第一・二・三正規化も)を満たす
    • ボイス・ゴット正規化されているからといって第四正規化されているとは限らない

SQL文(テーブル管理)

  1. CREATE SCHEMA
    CREATE SCHEMA スキーマ名 AUTHORIZATION ユーザ
  2. CREATE TABLE
    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
    )
    • FOREIGN KEY で指定する項目は、主キーでなければならない
    • FOREING KEY に対して、参照先が変更された時の動作を指定できる
      1. ON DELETE : 参照先テーブル側でレコードが削除されたとき
        NO ACTION何もしない。FOREIGN KEY属性にON〜を書かないとこれが設定される
        CASCADEレコードを削除する
        SET NULL外部参照列にNULLをセット
        SET DEFAULT外部参照列にデフォルト値をセット
      2. ON UPDATE : マスタテーブル側でレコードが更新されたとき
        NO ACTION何もしない。FOREIGN KEY属性にON〜を書かないとこれが設定される
        CASCADE外部参照列を更新する
        SET NULL外部参照列にNULLをセット
        SET DEFAULT外部参照列にデフォルト値をセット
  3. CREATE ASSERTION (表明:TABLEに対する後付の制約)
    CREATE ASSERTION USER_TBL CHECK( AGE>18 AND AGE<30 )
  4. CREATE VIEW
    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
    • 更新・削除できないVIEW表
      1. 集合関数を使ったもの(AVG,MAXなど)
      2. GROUP BY,ORDER BYを使ったもの
      3. 表の結合や合併をしているもの
      4. 副問い合わせをしているもの
      5. DISTINCT句で重複を排除しているもの
  5. DROP TABLE USER_TBL
  6. DROP VIEW USER_POSTING_LIST
  7. GRANT 権限 ON テーブル/ビュー TO ユーザー[,ユーザー]
    権限できること
    ALL PRIVILEGES全部
    SELECTSELECT
    INSERTINSERT
    DELETEDELETE
    UPDATEUPDATE
    • VIEW表に対する権限は、参照する全ての実TABLEに対する権限が必要
  8. REVOKE 権限 ON テーブル/ビュー TO ユーザー[,ユーザー]

SQL文(SELECT)

  1. SELECT * FROM 受注テーブル
  2. 表示する列を指定する
    SELECT
     商品番号 ,
     正価
    FROM 受注テーブル
  3. 行数を返す
    SELECT
     COUNT(*)
    FROM 受注テーブル
  4. 計算する
    SELECT
     商品番号 ,
     正価 ,
     正価*1.05 AS 請求額
    FROM 受注テーブル
  5. 列を連結
    SELECT 
     '商品番号='|| 商品番号 ,
     '正価='|| 正価 ,
     '請求額='|| 正価*1.05 AS 請求額 
    FROM 受注テーブル
  6. 条件検索
    SELECT *
    FROM 受注テーブル
    WHERE 顧客ID='00456'
  7. NULL値の検索
    SELECT *
    FROM 受注テーブル
    WHERE 納品先住所 IS NULL
  8. 重複を取り除く(例はID00456の顧客が過去に注文した商品の一覧)
    SELECT DISTINCT
     商品番号
    FROM 受注テーブル
    WHERE 顧客ID='00456'
  9. 範囲指定検索
    SELECT *
    FROM 受注テーブル
    WHERE 受注日 BETWEEN '2000-01-01' AND '2004-12-31'
     
    (eq.)
    SELECT *
    FROM 受注テーブル
    WHERE 受注日 >= '2000-01-01' AND 受注日 <='2004-12-31'
  10. 複数からの選択検索
    SELECT *
    FROM 受注テーブル
    WHERE 受注日 IN('2003-11-10','2003-11-11')
  11. 副問い合わせ(休業日の受注一覧)
    SELECT *
    FROM 受注テーブル
    WHERE 受注日 IN( SELECT 日時
                     FROM 日付マスタ
                     WHERE 日時属性='休業' )
     
    SELECT *
    FROM 受注テーブル X
    WHERE EXISTS( SELECT *
                  FROM 日付マスタ Y
                  WHERE X.受注日 = Y.日付 AND Y.日時属性='休業' )
  12. WHERE LIKE 'ビック%'
    • "_":一桁の任意の文字
    • "%":0桁を含むN桁の任意の文字

SQL文(GROUP BY/ORDER BY)

  1. SELECT 受注日,AVG(受注金額) AS 平均受注金額 FROM 受注テーブル GROUP BY 受注日
  2. SELECT 受注日,MAX(受注金額) AS 最大受注金額 FROM 受注テーブル GROUP BY 受注日
  3. SELECT 受注日,MIN(受注金額) AS 最小受注金額 FROM 受注テーブル GROUP BY 受注日
  4. SELECT 受注日,SUM(受注金額) AS 合計受注金額 FROM 受注テーブル GROUP BY 受注日
  5. SELECT 受注日,COUNT(*) AS 受注件数 FROM 受注テーブル GROUP BY 受注日
  6. GROUPに対して条件を付ける
    1件以上の受注があった日の受注金額の合計
    SELECT 受注日,SUM(受注金額) AS 合計受注金額
    FROM 受注テーブル
    GROUP BY 受注日
    HAVING COUNT(*) > 0
  7. 昇順(小→大)
    SELECT 受注日,SUM(受注金額) AS 合計受注金額
    FROM 受注テーブル
    GROUP BY 受注日
    ORDER BY 受注日  // 省略時はASC
     
    SELECT 受注日,SUM(受注金額) AS 合計受注金額
    FROM 受注テーブル
    GROUP BY 受注日
    ORDER BY 受注日 ASC
  8. 降順(大→小)
    SELECT 受注日,SUM(受注金額) AS 合計受注金額
    FROM 受注テーブル
    GROUP BY 受注日
    ORDER BY 2 DESC
    ORDER BYの後にSQL関数を書くとエラー → 行数で指定

SQL文(JOIN)

  1. ここで扱うテーブル
    • UPPER_TBL
      IDPHRASE
      1秋の田の かりほの庵の 苫をあらみ
      2春過ぎて 夏来にけらし 白妙の
      3あしびきの 山鳥の尾の しだり尾の
      4田子の浦に うち出でて見れば 白妙の
      5奥山に 紅葉ふみわけ 鳴く鹿の
    • LOWER_TBL
      IDPHRASE
      1我が衣手は 露にぬれつつ
      2衣ほすてふ 天の香具山
      3ながながし夜を ひとりかも寝む
      6白きを見れば 夜ぞ更けにける
      7三笠の山に 出でし月かも
  2. 内部結合(INNER JOIN)
    SELECT U.ID , U.PHRASE , L.PHRASE
    FROM UPPER_TBL U , LOWER_TBL L 
    WHERE U.ID = L.ID
     
    U.IDU.PHRASEL.PHRASE
    1秋の田の かりほの庵の 苫をあらみ我が衣手は 露にぬれつつ
    2春過ぎて 夏来にけらし 白妙の衣ほすてふ 天の香具山
    3あしびきの 山鳥の尾の しだり尾のながながし夜を ひとりかも寝む
  3. 外部結合(OUTER JOIN)
    1. 全外部結合(FULL JOIN)
      SELECT U.ID , U.PHRASE , L.PHRASE
      FROM UPPER_TBL U FULL JOIN LOWER_TBL L ON U.ID = L.ID
      U.IDU.PHRASEL.PHRASE
      1秋の田の かりほの庵の 苫をあらみ我が衣手は 露にぬれつつ
      2春過ぎて 夏来にけらし 白妙の衣ほすてふ 天の香具山
      3あしびきの 山鳥の尾の しだり尾のながながし夜を ひとりかも寝む
      4田子の浦に うち出でて見れば 白妙のnull
      5奥山に 紅葉ふみわけ 鳴く鹿のnull
      6null白きを見れば 夜ぞ更けにける
      7null三笠の山に 出でし月かも
    2. 左外部結合(LEFT JOIN)
      SELECT U.ID , U.PHRASE , L.PHRASE
      FROM UPPER_TBL U LEFT JOIN LOWER_TBL L ON U.ID = L.ID
      U.IDU.PHRASEL.PHRASE
      1秋の田の かりほの庵の 苫をあらみ我が衣手は 露にぬれつつ
      2春過ぎて 夏来にけらし 白妙の衣ほすてふ 天の香具山
      3あしびきの 山鳥の尾の しだり尾のながながし夜を ひとりかも寝む
      4田子の浦に うち出でて見れば 白妙のnull
      5奥山に 紅葉ふみわけ 鳴く鹿のnull
    3. 右外部結合(FULL JOIN)
      SELECT L.ID , U.PHRASE , L.PHRASE
      FROM UPPER_TBL U RIGHT JOIN LOWER_TBL L ON U.ID = L.ID
      L.IDU.PHRASEL.PHRASE
      1秋の田の かりほの庵の 苫をあらみ我が衣手は 露にぬれつつ
      2春過ぎて 夏来にけらし 白妙の衣ほすてふ 天の香具山
      3あしびきの 山鳥の尾の しだり尾のながながし夜を ひとりかも寝む
      6null白きを見れば 夜ぞ更けにける
      7null三笠の山に 出でし月かも

SQL文(INSERT/UPDATE/DELETE)

  1. INSERT
    INSERT
     INTO 受注テーブル(
                  受注ID
                  顧客ID,
                  商品ID,
                  数量,
                  納品フラグ,
                  受注日
                  納品日,
                  削除フラグ)
     VALUES( '034564',
             '130576'
             'J94045',
             4,
             0
             '2005-03-31'
             null
             '0')
  2. UPDATE
    UPDATE 受注テーブル 
     SET 納品フラグ=1 , 納品日='2005-04-02'
     WHERE 受注ID = '034564'
  3. DELETE
    DELETE FROM 受注テーブル
     WHERE 削除フラグ='1'

SQL文(トランザクション)

  1. BEGIN
  2. COMMIT
  3. ROLLBACK

Computer


トップ   編集 凍結 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS   sitemap
Last-modified: 2006-02-16 (木) 22:35:26 (3947d)
ISBN10
ISBN13
9784061426061