3.１●DBシステムの演習
１.DVD管理DBの作成
(1)手持ちのDVD等を管理するデータベースのERDモデルを提示します
※コード化されていないキーには曖昧さが含まれ予期せぬSQLの操作結果をもたらす原因となります（設計の不備です）
※主キーの文字列の前後のスペースにも十分注意してください
(2)データベースとテーブルを作成してください
【仕様】
ユーザ名：dvduser
パスワード：dvduser
サーバ名：自身のIPアドレス
DB名：dvddb
ポート番号：5432
テーブル名：ERDモデル参照
フィールド名：ERDモデル参照
フィールド属性型：購入日はdate型
購入日以外はすべてvarchar型
主キーと外部キー：適切に設定
参照制約：可能ならば設定
・データベースとユーザとパスワードの作成手順
dbaユーザ(postgres)で任意のデータベースに接続
↓
create database dvddb ;
↓
create user dvduser ;
↓
alter role dvduser with password 'dvduser';
↓
切断
・テーブルの作成手順
dvduserでdvddbに接続
（この時、実はパスワードを入力しなくても接続できます。なぜでしょうか？）
↓
CREATE TABLE 監督
(監督名 VARCHAR(20 ) NOT NULL,
出身地 VARCHAR( 10 ),
PRIMARY KEY (監督名 ))
;
CREATE TABLE 作品
(作品名 VARCHAR(20 ) NOT NULL,
監督名 VARCHAR( 20 ),
価格 VARCHAR( 10 ),
購入日 DATE NOT NULL,
製作会社名 VARCHAR( 20 ),
配給会社名 VARCHAR( 20),
PRIMARY KEY (作品名 ),
FOREIGN KEY( 監督名 )
REFERENCES 監督 ON DELETE CASCADE
ON UPDATE CASCADE)
;
CREATE TABLE 出演者
(名前 VARCHAR(20 ) NOT NULL,
出身地 VARCHAR( 10 ),
PRIMARY KEY (名前 ))
;
CREATE TABLE 出演作品
(作品名 VARCHAR(20 ) NOT NULL,
名前 VARCHAR( 20 ) NOT NULL,
役割 VARCHAR( 20 ),
受賞 VARCHAR( 20),
PRIMARY KEY ( 作品名 ,名前 ),
FOREIGN KEY( 作品名 )
REFERENCES 作品 ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY( 名前)
REFERENCES 出演者 ON DELETE CASCADE
ON UPDATE CASCADE)
;
(3)テストデータを作成してください
任意のテストデータを入力してください
例えばこんな感じで入力してください（すべての項目を埋める必要はありません）
INSERT INTO 監督(監督名,出身地 )
VALUES('ロバート・ゼメキス ','アメリカ ')
;
INSERT INTO 監督(監督名,出身地 )
VALUES('ジェームズ・キャメロン ','アメリカ ')
;
INSERT INTO 作品(作品名 ,監督名,価格 ,購入日 )
VALUES(' バック・トゥ・ザ・フューチャー１ ','ロバート・ゼメキス ','\3,800 ',' 1995-01-10')
;
INSERT INTO 作品(作品名 ,監督名,価格 ,購入日 )
VALUES(' バック・トゥ・ザ・フューチャー２ ','ロバート・ゼメキス ','\4,800 ',' 1996-02-20')
;
INSERT INTO 作品(作品名 ,監督名,価格 ,購入日 )
VALUES(' バック・トゥ・ザ・フューチャー３ ','ロバート・ゼメキス ','\5,800 ',' 1997-03-30')
;
INSERT INTO 作品(作品名 ,監督名,価格 ,購入日 )
VALUES(' ターミネーター１ ','ジェームズ・キャメロン ','\1, 800 ',' 2000-06-30')
;
INSERT INTO 出演者 (名前 ,出身地 )
VALUES(' マイケル・J・フォックス ','アメリカ')
;
INSERT INTO 出演者 (名前 ,出身地 )
VALUES(' クリストファー・ロイド ','アメリカ')
;
INSERT INTO 出演者 (名前 )
VALUES(' トーマス・F・ウィルソン ')
;
INSERT INTO 出演者 (名前 )
VALUES(' アーノルド・シュワルツェネッガー ')
;
INSERT INTO 出演作品 (作品名,名前 ,役割 )
VALUES(' バック・トゥ・ザ・フューチャー１ ','マイケル・J・フォックス ','マーティ・マクフライ')
;
INSERT INTO 出演作品 (作品名,名前 ,役割 )
VALUES(' バック・トゥ・ザ・フューチャー１ ','クリストファー・ロイド ','エメット・ブラウン博士 ')
;
INSERT INTO 出演作品 (作品名,名前 ,役割 )
VALUES(' バック・トゥ・ザ・フューチャー１ ','トーマス・F・ウィルソン ','ビフ・タネン')
;
INSERT INTO 出演作品 (作品名,名前 ,役割 )
VALUES(' ターミネーター１ ','アーノルド・シュワルツェネッガー ','T-800')
;
(4)一覧表を表示するSQLクエリを作成してください
【 問題】
表結合により以下の項目を表示する一覧表を作成してください
表示する項目名：作品名 監督名 出演者 役名
※ヒント：テキスト資料のP.28「7.2.3 三つ以上の表の結合」を参考にしてください。
【 解答】
SELECT 作品.作品名 ,作品.監督名,出演者.名前 AS 出演者,出演作品.役割 AS 役名
FROM 作品,出演作品 ,出演者
WHERE 作品.作品名=出演作品.作品名 AND
出演者.名前 =出演作品.名前
(5)ビューの作成
最後にSQLクエリを永続的にviewとして登録します。
検索SQLをviewにすることでクエリの結果をテーブルのような形で参照することが可能です。
以下のSQLを事項してください。
CREATE VIEW V_DVD一覧 AS
SELECT 作品.作品名 ,作品.監督名,出演者.名前 AS 出演者,出演作品.役割 AS 役名
FROM 作品,出演作品 ,出演者
WHERE 作品.作品名=出演作品.作品名 AND
出演者.名前 =出演作品.名前
create view コマンドを実行後 DBエクスプローラのビューツリーを展開してV_DVD一覧を右クリック
→全データを開く を実行してみてください
検索SQLの結果をテーブルを表示するのと同じやり方で表示することができました。
２．名簿管理DBの作成
・DB構築課題実習
DB構築手順
(1)ERDモデルの作成（テーブル設計）
(2)物理データベースの構築（データベース及びテーブル作成）
(3)SQL操作（データ入力）
(4)SQLクエリ作成（検索結果の妥当性評価）
(1)知人の連絡先を管理する名簿管理ERDモデルを提示します
分類とは自分との関係を表します（抽象的であいまいな項目です！）
例えば：学校での友人、職場での知人、地域での知人、などが考えられます
※コード化されていないキーには曖昧さが含まれ予期せぬSQLの操作結果をもたらす原因となります（設計の不備です）
※主キーの文字列の前後のスペースにも十分注意してください
(2)データベースとテーブルを作成してください
【仕様】
ユーザ名：meibouser
パスワード：meibouser
サーバ名：自身のIPアドレス
DB名：meibodb
ポート番号：5432
テーブル名：ERDモデル参照
フィールド名：ERDモデル参照
フィールド属性型：すべてvarchar型
主キーと外部キー：適切に設定
参照制約：削除も更新も波及で設定
・データベースとユーザとパスワードを作成します
dbaユーザ(postgres)で任意のデータベースに接続
↓
create database meibodb ;
↓
create user meibouser ;
↓
alter role meibouser with password 'meibouser';
↓
切断
・テーブルを作成します
meibouserでmeibodbに接続
（この時、実はパスワードを入力しなくても接続できます。なぜでしょうか？）
以下を入力します。
(3)テストデータを作成してください
例えばこんな感じで入力してください
①と②はエラーになるので入力を飛ばしても大丈夫です
①はなぜエラーになるのでしょうか
親のテーブル（T_分類 ）に「中学知人」というデータがないからです。
「中学友人」に変更すれば INSERT文 は成功してデータが登録されます。
INSERT INTOT_名簿(名前,住所,電話,関係名)
VALUES(' 田中次郎 ','神奈川県横浜市','045-123-4567 ','中学友人')
②はなぜエラーになるのでしょうか
自身のテーブル（T_家族） の主キーが「名前」だけでテーブル設計されているため2件目の同じ名前のデータ（つまり二人目以降の家族）が登録できないのです。
ERDはあっているようですので、これはテーブル実装のミスです。
T_家族 テーブルの主キーは「名前」と「家族関係」にしないといけません。これを複合キーといいます。
いったんT_家族 テーブルを削除して新たに作りなおしてデータを入力することにします。
最後になぜパスワードなしで接続できるのでしょうか？
PostgreSQLはデータベースへのアクセス制御をpg_hba.confとpostgresql.confで行います。
pg_hba.confで接続可能なIPアドレスの範囲やパスワード認証の有無を設定します。
パスワードなしでも接続されるのは、インストール直後にローカルアドレスから接続するユーザは信頼する設定にしたからです。
そこで、パスワードを要求する設定に変えます。
・pg_hba.confを修正します
メモ帳を開いて当該フォルダにある pg_hba.conf をドラッグ＆ドロップします
最後の行の trust を password に変更して保存します。
※詳しい設定方法はコメント文を読みます。
・データベースを再起動します
PostgreSQLはインストールが完了した段階でサービスとして自動的に起動されています。どのようにサービスに登録されているのかまず確認してみます。Windows10の場合、デスクトップ左下にあるスタートメニューを右クリックし、表示されたメニューの中から「コンピュータの管理」をクリックして下さい。
「コンピュータの管理」画面が表示されたら左側メニューの中の「サービスとアプリケーション」の中にある「サービス」をクリックして下さい。
「サービス」に関する情報が表示されます。(見にくいので「表示」メニューの中の「カスタマイズ」を選択し、「コンソールツリー」を非表示にしてます)。
「名前」の欄に「postgresql-x64-9.6」と書かれた行を探します。
「postgresql-x64-9.6」をダブルクリックして下さい。PostgreSQLサービスに関するプロパティが表示されます。
「スタートアップの種類」が「自動」に設定されているのでWindowsが起動する時に自動的にPostgreSQLもサービスとして開始されます。
この画面で「停止」をクリックして「開始」をクリックすることで再起動をかけます。
再起動できたら「OK」をクリックしてプロパティの画面を閉じて下さい。
以後cseの接続にはパスワードが必須となります。
(4)一覧表を表示するSQLクエリを作成してください
【 問題】
表結合により以下の項目を表示する一覧表を作成してください。
表示する項目名：関係名 名前 電話 家族関係 家族名
※ヒント：テキスト資料のP.27「7.2.2 二つの表の結合」を参考にしてください。
【 解答】
SELECT T_名簿.関係名 ,T_名簿.名前,T_名簿.電話 ,T_家族.家族関係,T_家族.家族名
FROM T_名簿 LEFT JOIN T_家族
ON T_名簿.名前=T_家族.名前
【 提出】
完成したSQLクエリをviewにしてSQLの内容と結果をWORD文書に張り付けて提出してください。
提出先 Teratermの「ssh scp」で自分のアカウントに転送
提出ファイル名 連絡先一覧表VIEW.doc