【DB2024 #3】RDBの設計 - ニフティ’s Notion

なぜDB設計が必要？

必要な情報をどういった構造でDB化するかを決めて、実際に設計する工程のことを DB設計 と呼ぶ
DBの役割を発揮させるために必要な工程
- データがバラバラに存在していたり整理されていなかったりすると、必要な時に情報を上手く利用することが出来ない

設計の三段階

概念設計
- DBで何を管理するかということを明確にするために概念モデルを作成
- 実装のことは考えず、業務で使われるデータを洗い出す
論理設計
- 概念設計で作成した概念モデルをRDBで扱える形式（=関係モデル）に変換
- 概念モデルに肉付けするイメージ
物理設計
- 作成した関係モデルから実際にテーブルを作成
- ここで初めて具体的にどのDBMS製品を使うのか考える

良いDBを考える

良いDBにするためのポイント

シンプルさ ：複雑な操作や構造などがなく、シンプルで使いやすいデータベース設計がされていること。

知識の継承 ：データベース設計がドキュメント化されており、新しいメンバーが簡単に理解できる状態になっていること。

セキュリティ ：ユーザーのデータが安全に保管されており、データ漏洩や不正アクセスが起きないような設計がされていること。

拡張性 ：アプリケーションの要件やユーザー数が増えた際にデータベース構造を簡単に変更やスケールアップできること

保守性 ：データベース設計が変更やアップデートがしやすいようになっており、長期的な保守が容易に行えること。

論理設計

主キーの整理

DBのテーブル内でデータ(レコード)を 一意に識別できる ように指定される項目のことを 主キー と呼ぶ
- プライマリーキー 、 PK と呼ぶことが多い

DBのキーの種類

基本的には主キーと外部キーを覚えておけばあまり困らない

種類	概要
主キー (Primary Key, PK)	テーブル内でレコードを一意に識別することができるように指定されるカラムレコードを識別するユニークなカラムのため、基本的に設定する複数のカラムを組み合わせて主キーとした場合は「複合キー」と呼ばれる
外部キー (Foreign Key, FK )	複数のテーブルの関係を結びつけるためのカラム
候補キー (Candidate Key)	主キーとして選べるカラム
代理キー (Alternate Key)	候補キーのうち、主キーとして選ばなかったカラム
非キー (Non Candidate Key )	候補キー以外(主キーになりえない)カラム
自然キー (Natural Key, NK)	人間が入力した値を主キーとして扱う場合の呼び名
人工キー (Surrogate Key, SK)	システムが自動で割り振った値を主キーとして使う場合の呼び名
スーパーキー (Super Key)	レコードを一意に決められさえすれば、どんなカラムの組み合わせでもよいキーつまり、レコードが特定できれば、カラム数は最小でなくてもよい候補キー(または主キー)に非キーをつけたもの

参考： https://itmanabi.com/db-key/

何が主キーになるか考えてみよう

学生番号	学部	キャンパス
21A2122028	文学部	多摩
21B1012045	経済学部	多摩
21C1415321	理工学部	後楽園

学籍番号はひとりひとりに異なるもの(ユニークなもの)が与えられるため、主キーとすることが可能
- 学籍番号によってその学生がどの学部にいるのか・キャンパスはどこか・なんのサークルに所属しているのか・何年生なのか・名前は何かを特定することができる

補足：主キーのデータ型について

以下の観点から、主キーに文字列型の使用は避けることが推奨されている

一意性の確保が面倒
時系列が不明
プライマリーキーはサイズが小さい方が良い

別途idカラムを設けて数字を割り振る方が良いとされている

正規化

データの重複をなくし整合的にデータを取り扱えるようにDBを設計することを、DBの 正規化 と呼ぶ
- 第1正規化
- 第2正規化
- 第3正規化　←ここまでが一般的に使う正規化
- ボイス-コッド正規化(第3.5正規化)
- 第4正規化
- 第5正規化
今回の講義では第1～3正規化までを取り扱います
- 基本的に業務で使うときも第３正規化までにとどめることが多い
- ボイス-コッド正規化以降は、第3正規化まで行うと自然と満たされていることが多い
  - 逆に、ボイス-コッド正規化以降は限られたシチュエーションで行う

第1正規化

一つのセルに対して一つの値 が入っている状態にする

現在、サークルや活動場所、役職などのセルに複数の値が入ってしまっている

学籍番号	学部	キャンパス	サークル	学年	氏名	役職	活動場所
21A2122028	文学部	多摩	音楽研究会,絵画同好会	3	今井直人	リーダー、無	4208号室、3206号室
21B1012045	経済学部	多摩	国際ボランティアサークル,フォークソング研究会	1	平岡直子	サブリーダー、合宿係	3104号室、講堂
21C1415321	理工学部	後楽園	アナウンス研究会,モダンジャズ研究会	4	前田洋一	会計、無	美術室、4105号室

一つのセルに一つの値のみが入っている状態

学籍番号	学部	キャンパス	サークル	学年	氏名	役職	活動場所
21A2122028	文学部	多摩	音楽研究会	3	今井直人	リーダー	4208号室
21A2122028	文学部	多摩	絵画同好会	3	今井直人	無	3206号室
21B1012045	経済学部	多摩	国際ボランティアサークル	1	平岡直子	サブリーダー	3104号室
21B1012045	経済学部	多摩	フォークソング研究会	1	平岡直子	合宿係	講堂
21C1415321	理工学部	後楽園	アナウンス研究会	4	前田洋一	会計	美術室
21C1415321	理工学部	後楽園	モダンジャズ研究会	4	前田洋一	無	4105号室

第2正規化

第1正規化が満たされた状態にする
部分関数従属 が解消された状態にする
- 主キーを構成する一部(またはすべて)のカラムによって一意に決まるカラムのことを決まるものを 部分関数従属なカラム と呼ぶ

以下のようなテーブルを例に考えてみましょう

学籍番号	学部	キャンパス	サークル	学年	氏名	役職	活動場所
21A2122028	文学部	多摩	音楽研究会	3	今井直人	リーダー	4208号室
21A2122028	文学部	多摩	絵画同好会	3	今井直人	無	3206号室
21B1012045	経済学部	多摩	国際ボランティアサークル	1	平岡直子	サブリーダー	3104号室
21B1012045	経済学部	多摩	フォークソング研究会	1	平岡直子	合宿係	講堂
21C1415321	理工学部	後楽園	アナウンス研究会	4	前田洋一	会計	美術室
21C1415321	理工学部	後楽園	モダンジャズ研究会	4	前田洋一	無	4105号室

｛ 学籍番号 , サークル名 ｝の組み合わせが主キーとなる(｛ 学籍番号 ｝だけではサークル名、役職、活動場所が一意に決まらないため、単体主キーとはならない)
- ｛ 学籍番号 ｝によって学部、キャンパス、学年、氏名が決定される
- ｛ サークル名 ｝によって活動場所が決定される

以下の3つに分離できる

学生テーブル

学籍番号	学部	キャンパス	学年	氏名
21A2122028	文学部	多摩	3	今井直人
21B1012045	経済学部	多摩	1	平岡直子
21C1415321	理工学部	後楽園	4	前田洋一

役職テーブル

学籍番号	サークル名	役職
21A2122028	音楽研究会	リーダー
21A2122028	絵画同好会	無し
21B1012045	国際ボランティアサークル	サブリーダー
21B1012045	フォークソング研究会	合宿係
21C1415321	アナウンス研究会	会計
21C1415321	モダンジャズ研究会	無し

サークルテーブル

サークル名	活動場所
音楽研究会	4208号室
絵画同好会	3206号室
国際ボランティアサークル	3104号室
フォークソング研究会	講堂
アナウンス研究会	美術室
モダンジャズ研究会	4105号室

第3正規化

第2正規化が満たされた状態にする
主キー以外でレコードが一意に決まる関係を抜き出す
- 段階的に何かが決まることを 推移的関数従属 と呼ぶ

先ほど第２正規化までした学生テーブルを見てみよう

学生テーブル

学籍番号	学部	キャンパス	学年	氏名
21A2122028	文学部	多摩	3	今井直人
21B1012045	経済学部	多摩	1	平岡直子
21C1415321	理工学部	後楽園	4	前田洋一

第2正規化はされた状態
｛ 学籍番号 ｝によって｛学部、学科、キャンパス、学年、氏名｝が全て一意に決まる
非キーである｛学部｝によっても｛ キャンパス ｝は一意に決まる
以下の二つに分離できる

学生テーブル

学籍番号	学部	学年	氏名
21A2122028	文学部	3	今井直人
21B1012045	経済学部	1	平岡直子
21C1415321	理工学部	4	前田洋一

キャンパステーブル

学部	キャンパス
文学部	多摩
経済学部	多摩
理工学部	後楽園

次: 【DB2024 #4】SQL