1 第 10 回 データーベース Access3 学 習 内 容 1.データベース 講 義 情 報 の 作 成 1.1 データベース 講 義 情 報 について 1.2 Excelからのデータインポート 2. 選 択 クエリの 作 成 2.1 課 題 1 選 択 クエリの 練 習 1 2.2 課 題 2 選 択 クエリの 練 習 2 3.クロス 集 計 クエリの 作 成 3.1 課 題 3 クロス 集 計 クエリの 練 習 4. 追 加 課 題 SQLの 作 成
1.データベース 講 義 情 報 の 作 成 2 今 回 作 成 する 講 義 情 報 のデータベースは, 講 義 の 科 目 と 担 当 教 員 や 開 講 の 曜 日 や 時 間 などシラバスの 基 本 情 報 を 管 理 するものです. 以 下 のように, 講 義 科 目, 科 目 コード, 教 員 コード, 科 目 区 分 コード のテーブルか らなります. 後 で 別 のテーブルも 作 成 しますが,まずはこの4つのテーブルの 意 味 を 確 認 してくださし. 今 回 は,Excelデータからデータをインポートするという 方 法 でテーブルを 作 成 していきます.ExcelとAccessはデータのやり 取 りがしやすく,Excelのデータ をAccessで 利 用 したり, 逆 にAccessのデータをExcelで 利 用 するために,イン ポート エクスポートといった 機 能 が 用 意 されています. ここで 使 うデータは, 専 修 大 学 のシラバスを 参 考 にAccessの 演 習 用 データと して 編 集 を 加 えたものです. 特 に 単 位 数 など 大 学 のデータと 同 じものではあり ません. 混 同 しないように 注 意 してこの 授 業 内 だけで 使 用 してください. データベース 講 義 情 報 講 義 科 目 講 義 科 目 や 開 講 時 期, 曜 日, 時 限, 教 員 などを 管 理 する 教 員 コード 教 員 コードと 教 員 名 を 管 理 する 科 目 コード 科 目 コードと 科 目 名, 科 目 区 分, 単 位 数 を 管 理 する 科 目 区 分 コード 科 目 区 分 コードと 科 目 区 分 名 を 管 理 する
1.1データベース 講 義 情 報 について 3 テーブルの 各 フィールドの 意 味 を 理 解 してください. 開 講 されているすべての 講 義 には, 異 なる 講 義 コード がふられています. 科 目 コード は 講 義 科 目 の 名 前 を 表 します.たとえば, 専 修 大 学 入 門 ゼミ ナール の 場 合, 担 当 の 先 生 によって 講 義 コード は 違 いますが, 科 目 コード は 同 じということになります. 教 員 コードは 各 教 員 を 識 別 するための コードです. 科 目 区 分 コード は 専 修 大 学 入 門 科 目 教 養 科 目 専 門 科 目 な どの 区 分 を 表 します. 青 字 が 主 キーのフィールドです. テーブルのフィールド 間 のリレーションは 下 記 のようになります. データベース 講 義 情 報 科 目 コード 講 義 科 目 科 目 コード 講 義 コード 科 目 名 科 目 コード 開 講 時 期 曜 日 時 限 教 員 コード リレーション シップ 科 目 区 分 コード 単 位 数 教 員 コード 教 員 コード リレーション シップ 科 目 区 分 コード 科 目 区 分 コード 科 目 区 分 名 教 員 名
1.1データベース 講 義 情 報 について 4 表 計 算 ソフトとデータベースのデータの 管 理 方 法 の 違 い 表 計 算 ソフトを 使 う 場 合 は,1つの 表 の 中 に 必 要 となる 情 報 をすべていれて しまう 使 い 方 が 一 般 的 です.しかしデータベースの 場 合 は, 情 報 の 特 性 に 合 わ せてテーブルを 別 に 作 成 し, 対 応 するコードで 関 連 (リレーションシップを 作 成 して)をつけて 管 理 します. 大 量 のデータを 扱 う 場 合, 表 計 算 ソフトの 方 法 では,いくつかの 問 題 点 が 生 じます. 例 えば 今 回 の 場 合, 科 目 名 や 教 員 名 など 重 複 した 情 報 が 多 いと,それ に 合 わせてデータ 量 が 大 きくなってしまいます.また, 科 目 名 や 教 員 名 に 変 更 があった 場 合, 関 係 するすべてのレコードを 修 正 しなければならなくなります. こうした 不 都 合 を 解 消 するために,データベースではなるべくデータが 重 複 しないように,データを 一 定 の 規 則 に 従 って 分 割 してテーブルに 保 存 するとい うことが 行 われます.このような 処 理 を 正 規 化 と 言 います. 正 規 化 は,RDBな どのように 大 量 のデータを 整 合 性 を 保 って 管 理 するために 必 要 となる 方 法 です. 表 計 算 ソフト 講 義 情 報 科 目 名 開 講 時 間 曜 日 時 限 教 員 名 科 目 区 分 名 単 位 数 データベース 講 義 情 報 講 義 科 目 講 義 コード 科 目 コード 開 講 時 期 曜 日 時 限 教 員 コード 科 目 コード 科 目 コード 科 目 名 科 目 区 分 コード 単 位 数 教 員 コード 教 員 コード 教 員 名 科 目 区 分 コード 科 目 区 分 コード 科 目 区 分 名
1.2 Excelからのデータインポート 5 Excelファイルには, 講 義 科 目, 科 目 コー ド, 教 員 コード, 科 目 区 分 コードのシート が 作 成 されています.これらのシートから データを 取 り 込 む 方 法 でテーブルを 作 成 し ていきます. まずデータベースを 作 成 します. データベース 名 : 講 義 情 報 Excelファイルからデータをインポートし てテーブル 講 義 科 目 を 作 成 します [ 外 部 データ]リボンの インポートとリ ンク から [Excelスプレッドシートのイン ポート]を 選 択 し,スプレッドシートイン ポートウィザードを 起 動 します. ファイル 名 を 選 択 し 現 在 のデータベー スの 新 しいテーブルにソースデータをイン ポートする を 選 択 します OK ワークシートを 選 んで 次 へ
1.2 Excelからのデータインポート 6 先 頭 行 をフィールド 名 として 使 う に チェックを 入 れデータを 確 認 して 次 へ インポートのオプションでフィールド 名 講 義 コード データ 型 短 いテキスト イ ンデックス はい( 重 複 なし) 次 へ 次 のフィールドに 主 キーを 設 定 する で 講 義 コード を 選 びます インポート 先 のテーブルを 講 義 科 目 完 了.インポート 操 作 の 保 存 は チェックしないで 閉 じます データシートビューとデザインビューで 作 成 された 内 容 を 確 認 します ほかの3テーブルも 同 じようにしてデータ をインポートします Excelファイルの, 科 目 コード, 教 員 コード, 科 目 区 分 コードのシートから 同 じ 方 法 で 科 目 コード, 教 員 コード, 科 目 区 分 コード のテーブルを 作 成 します
1.2 Excelからのデータインポート 7 それぞれのテーブルが 下 記 のように 作 成 されているか 確 認 してください 講 義 科 目 科 目 コード 教 員 コード フィールド 名 データ 型 講 義 コード 短 いテキスト 主 キー 科 目 コード 短 いテキスト 開 講 時 期 短 いテキスト 曜 日 短 いテキスト 時 限 数 値 型 教 員 コード 短 いテキスト フィールド 名 データ 型 科 目 コード 短 いテキスト 主 キー 科 目 名 短 いテキスト 科 目 区 分 コード 短 いテキスト 単 位 数 数 値 型 フィールド 名 データ 型 教 員 コード 短 いテキスト 主 キー 教 員 名 短 いテキスト 科 目 区 分 コード フィールド 名 データ 型 科 目 区 分 コード 短 いテキスト 主 キー 科 目 区 分 名 短 いテキスト
1.2 Excelからのデータインポート 8 今 までのインポートの 方 法 ではなく, 先 週 まで 学 習 したテーブル 作 成 の 方 法 で, 下 記 のテーブル 履 修 科 目 を 作 ります. 作 成 リボンの テーブル から 作 成 してください.これは, 自 分 が 履 修 した 講 義 コードの 一 覧 となります. 必 要 と 考 えられるリレーションシップも 作 成 してください. 履 修 科 目 フィールド 名 データ 型 講 義 コード 短 いテキスト 主 キー 履 修 年 数 値 型
2. 選 択 クエリの 作 成 9 各 テーブルから 必 要 なフィールドを 抽 出 するクエリ query 辞 書 によるとクエリとは,question の 形 式 張 った 語 で, 疑 問 質 問 な どの 意 味 があります.データベースでは 問 い 合 わせ や 照 会 の 意 味 で 使 っています.つまり,データベースにいろいろな 条 件 をつけて 問 い 合 わせを して,テーブルのデータからその 条 件 に 見 合 うものを 引 き 出 してくることがで きます. クエリは, 各 テーブルから 問 い 合 わせの 条 件 に 応 じて 抽 出 したデータをテー ブルと 同 じ 形 式 で 表 示 しますが, 一 時 的 に 抽 出 するだけで, 実 際 のデータがク エリに 保 存 はされていません. 売 上 伝 票 テーブル アドレス 帳 住 所 録 ID 氏 名 郵 便 番 号 住 所 自 宅 TEL 携 帯 TEL 電 子 メールア ドレス 生 年 月 日 年 賀 状 の 送 付 グルー プ クエリ ( 条 件 に 合 うレコー ドを 抽 出 する) 氏 名 住 所 生 年 月 日 グループ
2. 選 択 クエリの 作 成 10 これからの 練 習 では,デザインビューからクエリを 作 成 します. 前 回 の 画 面 と 操 作 方 法 を 思 い 出 してください. リボン[ 作 成 タブ]から[クエリデザイン]を 選 択 テーブルの 表 示 ダイアログボックスで 必 要 なテーブルやクエリを 表 示 し[ 追 加 ] をクリック), 表 示 するフィールドを 設 定 します テーブルの 表 示 ダイアログボック スで 追 加 したテーブルやクエリが 表 示 されます フィールドを 選 んで 下 に ドラッグしてもいいです 抽 出 条 件 などを 指 定 していきます フィールドを 選 択 しても いいです
2. 選 択 クエリの 作 成 11 クエリの 種 類 クエリの 種 類 には, 以 下 のものがありますが,ここでは, 選 択 クエリ,クロ ス 集 計 クエリ.を 練 習 していきます. 選 択 クエリ パラメータクエリ クロス 集 計 クエリ テーブルから 条 件 にあうデータを 選 択 してその 結 果 をデータ シートに 表 示 することができます.また,データの 合 計 値,カウ ント 値, 平 均 値 なども 計 算 することができます. 最 も 使 われるク エリです. レコードの 抽 出 条 件 や,フィールドに 挿 入 する 値 等 のデータを 入 力 する 独 自 のダイアログ ボックスを 表 示 するクエリです. 合 計,カウント, 平 均 等 の 集 計 値 を 1 行 ごとに 表 示 し,データ を 一 連 の 項 目 別 に 集 計 するクエリです. アクション クエリ 1 つの 操 作 で 多 くのレコードを 変 更 するクエリです. 削 除, 更 新, 追 加,テーブル 作 成 の 4 種 類 があります. SQL クエリ SQL ステートメントを 使 って 作 成 するクエリです.
2. 選 択 クエリの 作 成 クエリの 抽 出 条 件 で 使 えるワイルドカードと 比 較 演 算 子 は 以 下 のものがあります. 意 味 例 例 の 意 味 * 任 意 の 数 の 文 字 と 一 致 港 区 * 港 区 で 始 まる 文 字 列 ( 港 区 番 町 など)と 一 致 ( 先 頭 または 最 後 文 字 に 使 用 可 )? 任 意 の1 文 字 と 一 致 港 区? 番 町 港 区 三 番 町, 港 区 1 番 町 と 一 致 [ ] カッコ 内 の 任 の1 文 字 と 一 致 加 藤 [ 様 君 ] 加 藤 様, 加 藤 君 と 一 致! カッコ 内 の 文 字 以 外 と 一 致 加 藤 [! 様 ] 加 藤 君 と 一 致. 加 藤 様 には 一 致 しない - 範 囲 内 の 任 意 の 文 字 と 一 致 a[a-c]d [a-c]はa,b,cを 表 す.aad,abd,acdと 一 致 # 任 意 の 数 字 と 一 致 1###9 12329,など1で 始 まり9で 終 わる5 桁 の 数 字 と 一 致 演 算 子 意 味 例 例 の 意 味 = 同 じ = abc abc と 同 じ 文 字 列, より 小 さい,より 大 きい <500 500 より 小 さい 数 値 =, = 以 下, 以 上 >=#2006/04/01# 2006 年 4 月 1 日 以 降 違 う <> abc abc ではない 文 字 列
2.1 課 題 1 選 択 クエリの 練 習 1 13 デザインビューの 画 面 で, 使 うテーブルを 表 示 し,リレーションが 作 成 されてい るか 確 認 してからクエリを 作 成 していきます.クエリを 実 行 した 後 は, 必 ず 正 しく 表 示 されているか 確 認 してください. 選 択 クエリを 作 成 して, 条 件 に 相 当 するデータを 抽 出 します( 使 うテーブルは, 講 義 科 目 科 目 コード 教 員 コード 科 目 区 分 コード です). 下 記 のフィールドの 一 覧 が 表 示 されるクエリを 作 成 して, 講 義 科 目 一 覧 クエ リ として 保 存 してください. 講 義 コード, 科 目 名, 開 講 時 期, 曜 日, 時 限, 教 員 名, 科 目 区 分 名, 単 位 講 義 科 目 一 覧 クエリ で 出 力 した 一 覧 表 からフィルターの 機 能 などを 使 って, 自 分 が 現 在 ( 今 年 度 の 後 期 ) 履 修 している 科 目 の 講 義 コードを 抽 出 してくださ い( 必 要 であればメモしてください). テーブル 履 修 科 目 にデータを 入 力 します 自 分 が 履 修 している 講 義 コードと 履 修 年 2014 を 入 れます. 2014 年 に 履 修 した 科 目 という 条 件 で, 以 下 のフィールドが 表 示 されるクエリを 作 成 し, 2014 年 履 修 科 目 クエリ として 保 存 してください( 使 うテーブルに, 履 修 科 目 を 追 加 します). 科 目 名, 開 講 時 期, 曜 日, 時 限, 教 員 名, 単 位 数, 履 修 年
2.1 課 題 1 選 択 クエリの 練 習 1 14 2014 年 履 修 科 目 クエリ を 用 いて,2014 年 に 履 修 した 科 目 の 総 単 位 数 を 求 めるクエリを 作 成 します. 作 成 リボンの クエリデザイン のデザインビューから, テーブルの 表 示 で 2014 年 履 修 科 目 クエリ を 追 加 します. 履 修 年 と 単 位 数 をフィールドに 追 加 します 集 計 ( 表 示 されていない 場 合 は 右 クリックで 表 示 できます)をクリック し, 単 位 数 フィールドの 集 計 の 方 法 を 合 計 として,クエリ 名 を 2014 年 後 期 取 得 単 位 クエリ として 保 存 します ウィザードを 利 用 して 作 成 する 方 法 テーブル/クエリでクエリ 2014 年 履 修 科 目 を 選 択 して, 単 位 数 フィールドを 選 択 しま す 集 計 を 行 うかどうかの 選 択 で 集 計 する を 選 択 します 集 計 のオプション をクリックして 合 計 に チェックします
2.2 課 題 2 選 択 クエリの 練 習 2 15 クエリのデザインビューの 抽 出 条 件 を 使 用 してクエリを 作 成 します. 開 講 時 期 が 後 期 または 通 年 で, 科 目 区 分 名 が 専 門 科 目 で, 単 位 数 が 2 のものを 選 んで, 時 限 で 昇 順 ソートして, 科 目 名, 開 講 時 期, 科 目 区 分 名, 曜 日, 時 限, 教 員 名, 単 位 数 の 一 覧 表 を 作 成 してください( 後 期 通 年 科 目 一 覧 クエリ ) さらに ネットワーク と 名 前 が 付 く 科 目 を 抽 出 してください( ネット ワーク 科 目 一 覧 クエリ ) 作 成 したクエリの 抽 出 結 果 から,さらに 条 件 を 設 定 して 新 しいクエリを 作 成 することもできます.デザインビューので,テーブルの 表 示 から クエリ (または 両 方 )のタブを 選 んで, 使 用 するクエリを 追 加 し,このク エリを 基 にして 新 しいクエリを 作 成 します. 後 期 通 年 科 目 一 覧 クエリ から, 自 分 のクラス 担 任 の 科 目 を 選 択 して 上 と 同 じ 項 目 の 一 覧 表 を 作 成 してください( クラス 担 任 科 目 一 覧 クエリ ) テーブルA クエリX テーブルA クエリY テーブルB テーブルからクエリを 作 成 できます クエリX テーブルとクエリからさらに 新 しいクエリを 作 成 できます
3.クロス 集 計 クエリの 作 成 16 クエリでデータを 集 計 したいときは, クロス 集 計 クエリを 使 います.クロス 集 計 クエリは 一 つのテーブルから 作 成 されますので, 複 数 のテーブルの フィールドを 使 う 場 合 は,まず 必 要 な フィールドを 含 むクエリを 先 に 作 成 し て,そのクエリを 使 ってクロス 集 計 ク エリを 作 成 します.ここでは, 講 義 科 目 一 覧 クエリ を 使 い クエリウィ ザード を 使 ってクロス 集 計 クエリを 作 成 します. クエリウィザード から クロス 集 計 クエリ ウィザード を 選 択 します. OK 講 義 科 目 一 覧 クエリ を 表 示 させま す 次 へ 行 見 出 しのフィールドを 設 定 します. ここでは 時 限 とします. 次 へ
3.クロス 集 計 クエリの 作 成 17 列 見 出 しのフィールドを 設 定 します. ここでは 曜 日 とします. 次 へ 集 計 する 値 のフィールドと 集 計 方 法 を 設 定 します.ここでは 講 義 コード と カウント とします. 次 へ クエリ 名 を 確 認 して( 他 の 物 を 使 いた ければ 修 正 して) 完 了 表 示 された 実 行 結 果 を 確 認 します
3.1 課 題 3 クロス 集 計 クエリの 練 習 18 講 義 科 目 一 覧 クエリのクロス 集 計 の 結 果 から 次 の 問 に 答 えてください. 1. 火 曜 日 の1 限 に 開 講 されている 科 目 数 をカウントしてください. 2. 火 曜 日 で 開 講 数 が 最 も 多 い 時 限 はいつでしょうか. 3. 一 週 間 を 通 して, 最 も 開 講 数 が 多 い 曜 日 時 限 はいつでしょうか. 新 しいクロス 集 計 クエリ( 名 前 自 由 )を 作 成 して 次 の 問 に 答 えてください. 4. 水 曜 日 の2 限 で 専 門 科 目 として 開 講 されている 科 目 数 をカウントしてくだ さい
4. 追 加 課 題 SQLの 機 能 19 SQLの 機 能 データベース 管 理 システムを 通 してデータ を 扱 う 場 合,その 方 法 は2つあります. ひとつは, DBMS(ここではAccessにあた ります)が 提 供 するユーザインターフェース を 通 してデータベースを 操 作 するものです. 今 までの 学 習 では,Accessが 提 供 するイン ターフェースを 利 用 してテーブルやクエリを 作 成 してきました. もうひとつの 方 法 は,SQL(structured query language)という 言 語 を 記 述 して 直 接 データベースを 操 作 するものです. 今 回 の 演 習 で 使 ったAccessのユーザインターフェース も, 内 部 ではSQLに 変 換 されて 処 理 が 行 われ ています.クエリなどを 選 択 してビューを SQLビュー とすると,どのようなSQL 文 が 作 成 されているか 見 ることができます.C 言 語 などのプログラム 言 語 を 使 って,データ ベースを 操 作 する 場 合 はこのようなSQL 文 を プログラム 内 に 記 述 して 操 作 することになり ます. データベース 管 理 システム(DBMS) データベース SQL 機 能 DBMS 提 供 の インターフェース ( 内 部 でSQL 変 換 ) 利 用 者 応 用 ソフトウェア (SQLを 記 述 )
4. 追 加 課 題 SQLの 機 能 20 SQLについて 調 べて,テーブル 講 義 科 目 からフィールド 曜 日 と 時 限 を 表 示 するクエリとなるSQL 文 を 作 成 してください.