Comments
Description
Transcript
データベースの設計 - 広島県立教育センター
広島県立教育センター データベースの設計 ver1.0 広島県立教育センター www.hiroshima-c.ed.jp - 0 - 広島県立教育センター 第５章 ５−１ データベースの設計 設計の目的 データベースを構築するには，何を目的としたデータベースなのかを明確にし，処理する 内容を具体的にかつ系統的に，処理内容を分析・整理する必要があります。この分析・整理 した情報を基にデータベースを設計します。目的に沿って，収集した情報をいかに効率よく データベースに反映させることができるかが，設計の際に大切なことです。 データベース利用による利点 ○ 事務処理の軽減と効率化 ○ データの蓄積 ○ 蓄積されたデータの有効利用（検索・置換・抽出などが容易にできる） ５−２ 設計作業の流れ データベースの目的を明確にする。 事 前 準 備 ○ 処理に必要な情報を収集する。 ○ 処理内容を系統的に整理する。 ○ 処理する内容を，作業手順に従って詳細に整理する。 ○ 既存のテーブルと新規に作成するテーブルの項目名，データの形式等を確認する。 データベースの作成 ○ テーブルの項目名，データの形式など具体的な設計をする。 ○ フォームのラベル名，入力項目などの決定と，入力レイアウトの作成をする。 ○ レポートのラベル名，印刷項目などの決定と，印刷レイアウトの作成をする。 ○ ダミーデータの入力をする。 ○ テーブル，フォーム，レポートそれぞれのデバッグを行う。 ○ マクロ処理をする。 ○ 統合処理メニューの作成をする。 ５−３ テーブルの設計 (1) テーブル設計の留意点 データベースを利用するためには，項目（フィールド）の名前やデータの型，表示幅を 決める必要があります。この項目にデータの型や表示幅を設定した情報を「項目属性」と 呼びます。この「項目属性」を設定する際の留意点は次の通りです。 www.hiroshima-c.ed.jp - 1 - 広島県立教育センター ○ 何を目的とするデータベースなのかを十分検討しましょう。 ○ 項目名からデータの内容が具体的にわかるように，名前の付け方を工夫しましょう。 ○ 入力するデータが文字なのか，数字なのかをよく検討しましょう。 ○ 入力ミスを防止するための対策を考えましょう。 （入力データに，文字数の制限や半角・全角の区別をするなどの制限条件をつける等） ○ 入力が簡単にできる方法を工夫しましょう。 (2) データの型 テーブルを作成する際，フィールドに保存できる値の種類を「データ型」として設定して おく必要があります。Access の場合，次のようなデータ型があります。 データ型（Access の場合） 内 容 テキスト型 文字・数字などの文字列データ（半角最大 255 文字） メモ型 半角 255 文字以上，最大 64,000 文字までのデータ 整数型 −32,768 ∼ 32,768 までの整数 数 バイト型 0 ∼ 255 までの整数 値 長整数型 −2,147,483,648 ∼ 2,147,483,648 までの整数 型 単精度浮動小数点型 −3.4×10^38 ∼ 3.4×10^38までの数値 倍精度浮動小数点型 −1.797×10^ 308 ∼ 1.797×10^ 308 までの数値 日付／時刻型 日付や時刻のデータ 通貨型 金額のデータ オートナンバー型 自動で番号を割り振られるデータ（修正不可） Yes／No 型 ２つの選択肢のあるデータ OLE オブジェクト型 Word や Excel などで作成したデータ ハイパーリンク型 項目選択することのできるデータ (3) テーブルデザインビューのフィールドプロパティ項目 設定した「データ型」について，さらにフィールドプロパティで詳細の設定をしましょう。 フィールドプロパティ項目には，主に次ページのようなものがあります。 www.hiroshima-c.ed.jp - 2 - 広島県立教育センター 設定項目 内 容 テキスト型および数値型フィールドに格納される，データのサイズを設定しま フィールドサイズ す。テキスト型の場合は最大文字数を，数値型の場合にはバイト型から十進数 型までをリストで選択します。 オートナンバー型フィールドのみ設定できます。テーブルに新規レコードが追 新規レコードの値 加されるときの値の増加方法を設定します。通常の設定値としては［インクリ メント］で，新規レコード発生時に自動的に１ずつ増加します。 ［ランダム］に 設定すると，無作為に乱数（長整数型の値）が割り当てられます。 数値や日付／時刻，テキストのデータを表示する書式を指定します。選択でき 書 式 る書式はフィールド型によって異なりますが，定義済み書式やカスタム書式を 作成して使用することもできます。 フォーカスがフィールドに移動したときの IME 入力モードを設定します。 ［コン IME 入力モード トロールなし］を設定すると，フォーカスが移動する前の設定がそのまま使わ れ，それ以外の設定では，そのコントロールに指定した IME 入力モードへ自動 的に切り替わります。 IME 変換モード ふ り が な 漢字変換モードを切り替えます。通常の変換モードは［一般］ですが， ［人名／ 地名］にすれば人名地名辞書を優先的に使えるように設定されます。 入力された文字列のふりがなを，指定先のフィールドへ自動表示します。 入力された郵便番号に対する住所，または入力された住所に対応する郵便番号 住所入力支援 を指定先のフィールドへ自動入力します。テキスト形式のみ利用できます。 小数点以下表示桁数 数値型フィールドでは，小数点以下の桁数を設定できます。 テキスト型，数値型，日付／時刻型，通貨型のフィールドで設定できます。定 定 型 入 力 型入力はデータ入力を簡単にしたり，間違った入力値を制限したりします。例 えば郵便番号の入力方法を，”〒”000−0000 と設定しておけば，数値を入力 するだけで，指定した形に表示させることができます。 テーブルまたはクエリのデータシートビューで，フィールド名以外のわかりや 標 すい列見出しとして設定することができます。ルックアップウィザード型以外 題 のすべてのフィールド型で設定できます。標題プロパティを指定しなければ， フィールド名がそのまま使用されます。 既 定 値 入 力 規 新しいレコードが追加されるときに，フィールドに初期値として自動的に入力 される値を設定します。 制 エラーメッセージ 値 要 求 空文字列の許可 入力されるデータに対しての制限条件を設定します。例えば「月」フィールド に１から 12 までの値をデータとして許可するには，>0 And <13 と設定します。 入力規制プロパティの設定値に違反するデータが入力された場合に表示する独 自のメッセージを設定します。 （例）“入力し直してください”など フィールドに値が必要かどうか設定します。このプロパティを［はい］に設定 すると，フィールドの値の入力を必須とすることができます。 このフィールドに長さ０の文字列（“ ”）を入力してもよいかどうか設定しま す。 インデックスを設定します。インデックスを設定すると，並べ替えや検索を実 インデックス 行する速度が速くなります。［はい（重複あり）］では重複したデータの入力を 許可します。［いいえ（重複なし）］にすると，すでに入力されているデータの www.hiroshima-c.ed.jp - 3 - 広島県立教育センター 値を受け付けません。メモ型や OLE オブジェクト型，ハイパーリンク型では設 定できません。 Unicode とは，世界中のさまざまな言語を含む国際文字コード化システムの規 格です。Unicode は，２バイト文字を使用するために，フィールドに保存され Unicode 圧縮 る文字は多くの領域を占有します。このプロパティを［はい］にしておけば， この領域を圧縮することができます。 ※ フィールドのデータ形式により，プロパティで設定できる項目に違いがありますので 気を付けましょう。 ※ 入力規制について ○ 入力する値 設定項目「入力規制」による制限 フィールドの入力規制に 入力規制 >0 ０より大きい数値を入力できる >=0 ０以上の数値を入力できる <0 ０より小さい数値を入力できる <=0 ０以下の数値を入力できる =1 １だけを入力できる < >1 １以外を入力できる ○ Len 関数を利用する。 Len（［フィールド名］）< =６ 入力文字を６文字以内に制限する。 設定項目「定型入力」による制限 あらかじめ設定した入力規則に従った入力を支援します。 ０００−００００ ････ ハイフンで接続された３桁＋４桁の数字入力の制限 ・０ ： データがない場合は，「０」が表示されます。 ・９ ： データがない場合は，「空白」が表示されます。 ・固定した文字列を表示させたい場合は，「 “ 【 演習 】 “ 」で指定します。 次の条件により演習テーブルを作成しましょう。 フィールド名 データ型 設定条件 会員番号 オートナンバー型 新規レコードは１ずつ増加・主キーとして設定 名前 テキスト型 入力文字数を７文字以内に設定 ふりがな テキスト型 「名前」から自動表示・全角ひらがな 性別 テキスト型 ルックアップウィザードで「男性」「女性」を表示 郵便番号 テキスト型 定型入力で「〒○○○−○○○○」と表示 住所１ テキスト型 住所入力支援を利用 電話番号 テキスト型 定型入力で「（○○○○）−○○−○○○○」と表示 来店月 テキスト型 入会者数 数値型 規定値として１を表示 投資金額 通貨型 「¥」や「，」を自動表示 入力規制で１から１２までの値をデータとして許可 間違ったデータにはエラーメッセージを表示 www.hiroshima-c.ed.jp - 4 - 広島県立教育センター (4) テーブルの正規化 リレーショナルデータベースの場合，各テーブルにおけるデータの重複をなくし，データ の一貫性と整合性を図るために，次の手順により正規化を行います。 【 参考例 】 次のような注文伝票を作成するためのテーブルを設計しましょう。 注 文 伝 票 年 月 日 伝票番号 顧客番号 顧客名 注文金額 顧客住所 Ｎｏ 品番 品名 単価 数量 金額 合計 ① 現状分析 伝票番号 ： 想定する処理結果から，入力（保存）すべき項目を洗い出します。 顧客番号 顧客名 顧客住所 年月日 Ｎｏ 品番 品名 単価 数量 ※「金額」や「合計」「注文金額」など，計算で求めることができる項目は削除します。 ② 第一正規化 伝票番号 ③ 第二正規化 ： データ項目を検討し，固定部分と繰り返し部分とに分割します。 顧客番号 顧客名 顧客住所 年月日 Ｎｏ 品番 品名 単価 ： ○ 固定部分 「基本テーブル」となります。 数量 ○ 繰り返し部分 分割したテーブルをつなぐ「キー項目」を設定し，それぞれにテー ブル名をつけましょう。 ○テーブル名 伝票番号 ○テーブル名 ： 伝票マスタ 顧客番号 ： 伝票番号 顧客名 ※「伝票番号」をキー項目 顧客住所 年月日 とし，２つのテーブルに 関連性を持たせます。 注文明細表 Ｎｏ 品番 品名 単価 www.hiroshima-c.ed.jp - 5 - 数量 広島県立教育センター ④ 第三正規化 ： テーブルは，項目数ができるだけ少ない方が利用しやすいです。 ここではキー項目以外のデータを検討し，さらに分割していきます。 ○顧客マスタ 顧客番号 顧客名 顧客住所 ○伝票マスタ 伝票番号 年月日 顧客番号 ○注文明細表 伝票番号 Ｎｏ ○商品マスタ ⑤ 品番 数量 品番 品名 単価 リレーションシップの作成 ①∼④の手続きを経て作成した各テーブルについて，リレーションシップ（関係付け） をとってみましょう。 ５−４ フォームの設計 (1) 目 的 データ入力を補助し，内容を確認することがフォームの役割です。従って，テーブルの項 目（フィールド）の名前やデータの型，表示幅を知り，テーブル全体が把握できている必要 があります。また，データ入力をできるだけ簡単・容易にすることに心がけたレイアウトに することが大切です。 (2) フォーム設計の留意点 ○ 入力項目を十分に把握しておきましょう。 ○ ラベルから，データの内容が具体的に分かるような名前にしましょう。 ○ 入力ミスを防止するための対策を考えましょう。 （入力データに，文字数の制限や半角・全角の区別をするなどの制限条件をつける等） ○ 入力が簡単にできる方法と，レイアウトを工夫しましょう。 www.hiroshima-c.ed.jp - 6 - 広島県立教育センター ５−５ レポートの設計 (1) 目 的 検索結果や処理結果などを印刷するためには，レポートの設定を行う必要があります。こ のレポート作成には，印刷するテーブルの項目（フィールド）の名前やデータの型などを十 分に把握しておく必要があります。 (2) レポート設計の留意点 ○ 特に留意すべき点はレイアウトです。十分に検討しましょう。 ○ 単票形式，表形式，伝票形式の３種類の中から，目的の印刷に適した形式でレイアウ トを考えましょう。 ○ ラベルがデータの内容を示すものになるように，ラベル名を工夫しましょう。 ○ 印刷されるデータの最大文字数，文字位置などを十分検討しましょう。 ○ 印刷ミスを防止するための対策を考えましょう。 ○ 印字はプリンタの機種に依存するので，使用するプリンタの仕様を把握しておきまし ょう。 ※ レポート作成は，プリンタの設定がされていないと作成できません。 データベース（Access）活用の留意点 ○ 文字入力の際の留意点（検索条件，関数などを入力する際には要注意） ・ 半角全角，大文字小文字をきちんと区別しましょう。 ・ 空白も文字列として認識します。（画面上では確認できないので注意しましょう。） ・ 関数，計算式などの記述は，項目名以外はすべて半角文字です。 ○ データ作成の留意点 ・ 思いつきでファイル名や項目名，クエリ名を決めないようにしましょう。 ・ データベース作成で一番大切なのは，全体構想です。（データベースの設計を！） ・ 既存のデータベースは，できるだけ活用しましょう。（作業の効率化を！） ・ 項目数最大 255 項目，項目名は 20 文字まで可能です。 ・ Access が持っている機能は有効に活用しましょう。（住所支援入力・ふりがな入力 など） ○ 関数利用の留意点 ・ Excel の関数が利用できるので，有効に活用しましょう。 ・ 関数が利用できる場合は，積極的に利用しましょう。 ○ テーブル間でデータの共有をする ・ リレーションシップを設定しましょう。（キーとなる項目がそれぞれのテーブルに www.hiroshima-c.ed.jp - 7 - 広島県立教育センター ５−６ ＳＱＬによる操作 (1) ＳＱＬとは ＳＱＬ（Structured Query Language）は，直訳すれば「構造化された問い合わせ言語」と なります。データベースに対してデータを取り出したり，更新処理を行ったりするときに， どういった処理を行うのかを記述するためのものです。 Access の場合，表面上では読みとれませんが，クエリでの作業は実際にＳＱＬに置き換え られて実行されています。デザインビューを表示し，ＳＱＬビューに切り替えれば実際に動 作命令となっているＳＱＬを見ることができます。また，表示されたＳＱＬをコピーしてモ ジュールに取り込み，必要な部分を修正すれば，簡単にＳＱＬ文を作成することができます。 ○ 選択クエリ → SELECT 文 ○ 更新クエリ → UPDATE 文 ○ 追加クエリ → INSERT 文 ○ 削除クエリ → DELETE 文 に相当します。 (2) SELECT 文 表中におけるデータの読みとりは，リレーショナルデータベースの中でもっとも多く発生 するデータ操作です。この場合，「SELECT」を用いて操作します。 ① データの読み取り ○ SELECT ※ ○ ② ○ 「生徒台帳テーブルから，生徒ＩＤと生徒名を表示する」 生徒ＩＤ，生徒名 FROM 生徒台帳； テーブル内のすべてのフィールドを対象とする場合は， 「 ＊」で示します。 SELECT 条件式照会 ： ＊ ： SELECT FROM 生徒台帳； 「生徒台帳のすべてのフィールドを表示する」 ある一定の条件に基づいた特定行の読み取りを指定します。 ＊ FROM 試験結果 WHERE 国語 >= 80； 「試験結果テーブルから，国語が 80 点以上のレコードを取り出す」 ○ SELECT 生徒名 FROM 成績一覧表 WHERE 数学 <= 50； 「成績一覧表から，数学が 50 点以下の生徒名を取り出す」 ○ SELECT 生徒 ID，英語 FROM 試験結果 WHERE 英語 >= 60 AND 英語 <= 90； 「試験結果から，英語の点数が 60 点以上でかつ 90 点以下の生徒 ID と英語の 点数を表示する」 www.hiroshima-c.ed.jp - 8 - 広島県立教育センター ③ 集約関数と並べ替え ○ SELECT ： 特定の列をもとにグループの集計や並べ替えをします。 生徒 ID，SUM（点数） FROM 試験結果 GROUP BY 生徒 ID； 「試験結果から，生徒 ID ごとの点数合計を求めて表示する」 ○ SELECT 生徒 ID，SUM（点数） FROM 試験結果 ORDER BY ２ GROUP BY 生徒 ID DESC； 「試験結果から，生徒 ID ごとの点数合計を求め，合計点の多い順に表示する」 ※ ④ 結合処理 ○ ： SELECT DESC ： 降順 ASC ： 昇順 複数の表を特定の列で結びつける操作です。 生徒名，合計点 FROM 生徒台帳，試験結果 WHERE 生徒台帳．生徒 ID ＝ 試験結果．生徒 ID； 「生徒 ID で関連づけられた生徒台帳と試験結果から，生徒名と合計点を取り出す」 これ以外にも多くのＳＱＬステートメント（命令）がありますが，一般的な操作につ いてはクエリの機能を利用することで十分対応できます。 クエリで設定した操作を［SQL ビュー］で確認することから始めてみましょう。 --------------------------平成１５年１０月３１日 初版発行 発行 広島県立教育センター 〒739-0144 東広島市八本松南１丁目2-1 ℡（0824)28-2655（直通） www.hiroshima-c.ed.jp - 9 -