データベースシステム論

提供: 静岡大学 横山研究室
移動先: 案内検索

講義スライド

本日の講義の関連情報

2016年度講義スライド

過去スライド

以下のスライドは昨年度のスライドです。内容は毎年ブラッシュアップしています。次回の授業までに、今年度のスライドに差し替えます。尚、SQL例文は今年度の授業にあわせて改編しています。

PostgreSQLの導入

エラーがでた人へ

インストール

まず、PostgreSQLのインストーラをダウンロードします。 インストーラは自分の使っているOSのビット数に合わせて32bitか64bitか選んでください。スクリーンショットは古いバージョンのものです。

  • 2016年度の講義では「Version 9.5.2」を使いましょう。


21881259814_d3b196ce2c_z.jpg


どちらかわからない場合は"Windowsキー+Pauseキー"を押してみてください。 下のウィンドウが開くので、ピンクの四角で囲んだところを見て確認してください。

10183593505_e05c587f42_z.jpg


ダウンロードが完了したら、インストーラを起動してください。 ここからは32bitと64bit共通になります。 起動すると次のウィンドウが出ます。 "Next"をクリックしてください。

10183657176_ec9a516aa0_o.png


次に、インストール先を聞かれるので、インストールしたい場所を選択してください。 デフォルトの設定で特に問題ないので、こだわりのない人はそのままで大丈夫です。

10183702043_a67c40123b_o.png


次は、データベースのデータを置くフォルダを聞かれます。 これもデフォルトのままで大丈夫です。

10183593265_6157fa09cd_o.png


次はパスワードです。 これはデータベースに接続するときに使うパスワードです。 下は確認用なので上と同じパスワードを入力してください。

10183657086_2cdc39f7a3_o.png


次はポート番号です。 これもデフォルトの5432のままで大丈夫です。 デフォルトの場合は接続時にポート番号を省略できるので、そのままにしておくと少し楽です。

10183657036_069dffa4d1_o.png


次はロケールの設定です。 これもデフォルトでかまいません。 ロケールについて詳しく知りたい人はこちらを参考にしてください。

10183593145_265a098f12_o.png


これでインストーラの設定は終わりです。 "Next"をクリックしてください。

10183701903_be2144181c_o.png


インストール終了まで数分待ちます。

10183492294_f994d99da2_o.png


インストールが終了するとこのような画面が出ます。 スタックビルダは拡張機能などをインストールする時に使うものです。 今回は使わないのでチェックを外して"Finish"をクリックしてください。

10183656956_14eb734e58_o.png

パスを通す

PostgreSQLと一緒にGUIツールもインストールされていますが、ここではコマンドでの操作で説明します。 なので、PostgreSQLにパスを通す作業を行います。 "Windowsキー+Pauseキー"を押して、次の画面が出たらピンク色で囲った"システムの詳細設定"をクリックしてください。

10183656986_6fcc0235a2_z.jpg


すると、次のウィンドウが出てくるので、一番下の"環境変数"をクリックしてください。

10183492134_9e95b87296_o.png


次のウィンドウが出てくるので、"Path"を選択して"編集"をクリックしてください。

10183701753_e5d2737c50_o.png


"変数値"のところの最後に、

;C:\Program Files\PostgreSQL\9.3\bin

を追加します。 ";"を忘れないよう注意してください。

※インストール場所を変更した人はインストールした場所に設定してください。

10183701713_ddddd7bd75_o.png

入力後はOKを押して全部のウィンドウを閉じてください。

郵便番号データの挿入

コマンドプロンプトを起動します。 "Windowsキー"を押し、"cmd"と入力後、エンターを押してください。

次に、以下のコマンドを入力してください。

psql -U postgres

ポート番号を変更した人は、"-p ポート番号"をコマンドに追加してください。 これは5433に変更した場合の例です。

psql -U postgres -p 5433

以下のようなエラーが出る場合は、パスが正しく通せていないので、パスを確認してください。

'pql' は、内部コマンドまたは外部コマンド、操作可能なプログラムまたはバッチ ファイルとして認識されていません。

コマンドを入力すると、パスワードの入力するようにメッセージが出るので、インストール時に設定したパスワードを入力します。 接続が成功すると以下の様な画面になります。

10203838165_967289d97e_b.jpg

ではまず、データベースの作成を行います。 ここでは、データベース名を"dbsys"、テーブル名を"zipcode"とします。 以下のコマンドを入力してください。

create database dbsys;

"CREATE DATABASE"と表示されればデータベースが作成できています。 次に、作成したデータベースに接続します。 以下のコマンドを入力してください。

\c dbsys;

コマンドを入力する場所の左の文字列が"postgres=#"から"dbsys=#"に変わったことを確認してください。 次に、テーブルを作成します。 テーブルの列名と内容は次のようになっています。

列名 概要
zip 郵便番号 4328011
kana1 都道府県名(全角カナ) シズオカケン
kana2 市区町村名(全角カナ) ハママツシナカク
kana3 その他住所(全角カナ) ジョウホク
addr1 都道府県名 静岡県
addr2 市区町村名 浜松市中区
addr3 その他住所 城北

以下のコマンドを入力してください。

create table zipcode

(
  zip integer,
  kana1 varchar(32),
  kana2 varchar(32),
  kana3 varchar(128),
  addr1 varchar(32),
  addr2 varchar(32),
  addr3 varchar(128)

);

"CREATE TABLE"と表示されれば成功です。 では、郵便番号データをデータベースに挿入します。 郵便番号データはこちらからダウンロードしてください。 ダウンロードが終わったら、以下のコマンドを入力してください。 "from"の後はダウンロードファイルの場所を指定します。 ここではCドライブ直下に置いた例となります。

copy zipcode from 'C:\KEN_ALL.CSV' CSV;

"COPY 123423"と出れば完了です。 お疲れ様でした。

ログアウト

以下のコマンドでpostgresのコマンドラインから抜け、Windowsのコマンドラインへ戻ります。

\q

SQLの実行

データベースへの接続

dbsysデータベースへの接続は次のコマンドです。(postgresのパスワードが要求されますので入れてください。)

デーブルの確認

以下のコマンドで、現在のデータベースの持っているテーブル一覧、およびあるテーブルの持つカラムの一覧が得られます。

テーブルのデータの全表示

テーブルに挿入した郵便番号を表示を行います。 次のSQLを入力してください。

select * from zipcode;

SQLを実行すると、次のように結果が表示されます。 件数が多いので途中でやめたい場合には"q"を押してください。

10204917896_8a5a1490da_z.jpg

浜松市のデータを表示

次は、浜松市だけのデータを表示します。 次のSQLを入力してください。

select * from zipcode where addr2 like '浜松市%';

SQLを実行すると、次のように結果が表示されます。

10204917796_31a4bf9407_z.jpg

結果をソートして表示

次は、検索結果をソートします。 次のSQLを入力してください。 この例では、"kana3"の列で昇順にソートしています。

select * from zipcode where addr2 like '浜松市%' order by kana3 asc;

SQLを実行すると、次のように結果が表示されます。

10204917846_24ab75a259_z.jpg

  • ポイント
    • 詳しくは次週以降にやりますが、like%を使うと前方一致検索、後方一致検索、中間一致検索ができます。
      • 前方一致検索:文字列の先頭がマッチしていれば結果を返す('浜松市%'は浜松市中区浜松市西区等にマッチする)
      • 後方一致検索:文字列の末尾がマッチしていれば結果を返す('%中区'は浜松市中区横浜市中区等にマッチする)
      • 中間一致検索:文字列の中間にマッチしていれば結果を返す('%松市%'は高松市浜松市中区等にマッチする)



提出課題

課題1:テーブルの作成とデータの挿入

以下の2つのテーブルを作成し、スキーマをレポートとして以下のフォームから送信せよ。尚カラムのデータ型等は各自適切なものを考えよ。

作成物

  • テーブル名:wine
    • 制約:wIDが主キー、dIDはvineyard(dID)を参照する外部キー
wID name dID price
1 シャブリ A 2400
2 ジュヴレシャンベルタン A 3000
3 サンテミリオン B 5800
4 オーメドック B 2200
5 サンセール C 2800
6 シャンパン D 4000
  • テーブル名:vineyard
    • 制約:dIDが主キー
dID district
A ブルゴーニュ
B ボルドー
C ロワール
D シャンパーニュ
E チリ

参考資料

提出物

テーブルを作成後以下の4つのコマンドの出力を提出する。(順番に実行した出力結果全てを提出せよ)

提出先

本年度は学務情報システムを使ってレポートを出します。

課題2:WHERE句とHAVING句の動作を説明せよ

講義スライドのGROUP BY句の説明で例示したWHERE句とHAVING句の実行結果の違いから以下の考察をせよ。

  • WHERE句において、wineテーブルがどのように処理されて、あのような結果になったのか
  • HAVING句において、wineテーブルがどのように処理されて、あのような結果になったのか
  • GOURP BYが指定された集約演算において、WHERE句とHAVING句の役割を端的に説明せよ

上記の三点それぞれについて文章で説明し、以下のフォームから提出せよ。

参考

  • WHERE句適用結果
dID round
C 2800
A 2700
B 2200
  • HAVING句適用結果
dID round
C 2800
A 2700

提出先

今回も学務情報システムを使って提出になります。

課題3:副問い合わせ

  • IN句、EXISTS句、スカラ副問い合わせにて以下の問い合わせを実現するSQLを記せ。
    • 提出物:クエリとその結果(コマンドラインのコピペでOK)
(全ワインの)平均価格未満のワインを1つでも持つ産地のdidと産地名(distinct)を表示せよ

提出先

  • 学務情報システム

課題4:正規化テーブルの作成

準備

手順

  • 上記で作ったテーブルをJOINして第一正規形で示したテーブル(以下にも記載)のVIEWを作ってみよう。
    • NATURAL JOINを使うと便利だよ!
    • ビューの名前はv_wineとしよう。
ワインセット ソムリエ 年齢 価格 ワイン 評価
高級白ワイン 田崎信哉 50 10000 シャルドネ カテナ ★★★
高級白ワイン 田崎信哉 50 10000 セミヨンブロークンウッド ★★★
高級白ワイン 田崎信哉 50 10000 おたる
お買い得白ワイン 横山昌平 36 3000 おたる ★★
お買い得白ワイン 横山昌平 36 3000 デリカート白 ★★★
お買い得白ワイン 横山昌平 36 3000 ガロフォリ白
お勧めワインセット 田崎信哉 50 150000 シャトー・ジレット ★★
お勧めワインセット 田崎信哉 50 150000 オーパスワン ★★★
お勧めワインセット 田崎信哉 50 150000 ヒル・オブ・グレイス

提出物

  • VIEW「v_wine」を作るためのSQL構文(CREATE VIEW文)

提出先

学務情報システム経由

試行課題

試行1:漢字・ひらがな・カタカナ交じり文字列のソート

準備

  • 以下のCREATE TABLE文を実行しテーブルを作れ

実行

  • 以下のSQL文を実行し、結果からどのようにソートされたか考えよ

試行2:右・左・全結合

試行3:一番安いワインを求める問い合わせ

個人用ツール
名前空間

変種
案内
ツール