1 データベース 基本情報技術概論 ( 第 11 回 ) 埼玉大学 理工学研究科 堀山 貴史 ＤＢ.

Presentation on theme: "1 データベース 基本情報技術概論 ( 第 11 回 ) 埼玉大学 理工学研究科 堀山 貴史 ＤＢ."— Presentation transcript:

1 1 データベース 基本情報技術概論 ( 第 11 回 ) 埼玉大学 理工学研究科 堀山 貴史 ＤＢ

2 2 利用者 データに構造を与えて管理し、 更新や検索などが容易に行えるようにしたも の 例） 列車の切符販売、商品の販売管理、銀行の送金処理 データベース 注文表 伝表 番号 0001 0002 0003 0004 受注日 2007/07/07 2007/07/08 2007/07/10 顧客 番号 100 200 300 200 数量 130 50 80 240 商品 番号 S20 T10 S10 S20 DBM S 7/7 に注文された 商品は？ S20 と T10 ※ データベース管理システム （ Database Management System ） ＤＢ

3 3 論理データモデル データを、どのような形で表現するか ( データの論理的構造とその内容を定義したもの ) 階層モデル 木構造（階層構造）で、データの構造を表現 ネットワークモデル 木構造を発展させ、ネットワークでデータの構造 を表現 関係モデル （リレーショナル モデル） ２次元の表で、データの構造を表現 関係データベース （リレーショナル ＤＢ） で採用 ________________

4 4 注文表 関係データベースの例 伝表番号受注日顧客番号商品番号数量 0001 2007/07/07 100S201300002 2007/07/07 200T10500003 2007/07/08 300S10800004 2007/07/10 200S20240 顧客表 住所 商品表 ○○○ △△△ 顧客番号 100 200 300□□□ 氏名 赤塚不二夫 手塚治虫 藤子不二雄 単価 100 50 500 商品名 鉛筆 消しゴム ノート 商品番号 S10 S20 T10

5 5 関係データベースの用語 属性 （列、項目） 行 （組） 注文表 伝表番号受注日顧客番号商品番号数量 0001 2007/07/07 100S201300002 2007/07/07 200T10500003 2007/07/08 300S10800004 2007/07/10 200S20240 関係 ドメイン（定義域） … 属性が取りえる値の 集合 例） 商品番号のドメイン： S10 ， S20 ， T10 ， …

6 6 関係データベースの用語 （続き） 注文表 伝表番号受注日顧客番号商品番号数量 0001 2007/07/07 100S201300002 2007/07/07 200T10500003 2007/07/08 300S10800004 2007/07/10 200S20240 主キー … 表の行を一意に識別する １つの表に主キーが同じ行は 存在しない 外部キー … 他の表を参照する （関連付ける） 参照される側の表では、これが主キー ____________ 顧客表の「主キー」 200 の行を参照す ると、氏名と住所が分かる 顧客表 顧客 番号 100 200 300 住所 ○○○ △△△ □□□ 氏名 赤塚不二夫 手塚治虫 藤子不二雄

7 7 ＳＱＬ 関係データベースを定義・操作するためのデータベース 言語 データベース定義 表の定義 … CREATE TABLE 表名 属性の定義 ビュー定義 … CREATE V I EW ビュー名 ビューの 定義 etc. データベース操作 データ挿入 … I NSERT I NTO 表名 （各属性の 値） データ削除 … DELETE FROM 表名 削除の指定 データ更新 … UPDATE 表名 SET 属性名 = 更新 値 etc. データベース制御 SELECT

8 8 データ操作 （関係演算） 射影 指定した属性（列）を取り出す ____________ 例） SELECT 商品番号，数量 FROM 注文 表 注文表 伝表 番号 0001 0002 0003 0004 受注日 2007/07/07 2007/07/08 2007/07/10 顧客 番号 100 200 300 200 数量 130 50 80 240 商品番号 S20 T10 S10 S20 数量 130 50 80 240 商品 番号 S20 T10 S10 S20 取り出す属性

9 9 関係データベースの操作 （関係演 算） 選択 条件を満たす行を取り出す ____________ 例） SELECT ＊ FROM 注文表 WHERE 数量 >= 100 注文表 伝表 番号 0001 0002 0003 0004 受注日 2007/07/07 2007/07/08 2007/07/10 顧客 番号 100 200 300 200 数量 130 50 80 240 商品 番号 S20 T10 S10 S20 伝表 番号 0001 0004 受注日 2007/07/07 2007/07/10 顧客 番号 100 200 数量 130 240 商品 番号 S20 すべての属性 条件

10 10 関係データベースの操作 （関係演 算） 結合 複数の表を、共通の属性でくっつける ____________ 例） SELECT 伝票番号，顧客番号，商品名，数量 FROM 注文表，商 品表 WHERE 注文表．商品番号 ＝ 商品表．商品番号 注文表 伝表 番号 受注日 顧客 番号 数量 商品 番号 0001 2007/07/07 100130S20 商品表 商品番号単価商品名 S2050 消しゴム 20050T100002 2007/07/07 500 伝表 番号 0001 顧客 番号 100 数量 130 商品名 消しゴム 000220050 ノート 複数の表 複数の表にある属性 は、表の名をつけて 区別 T10 ノート

11 11 練習問題： SQL SELECT 氏名 FROM 学生 WHERE 住所 = ‘ 新宿 ’ SELECT COUNT( 氏名 ) FROM 学生 WHERE 住所 = ‘ 新 宿 ’ 行数を数え る 学生 所属 理 工 経済 人文 氏名 合田知子 青木俊介 川内聡 坂口祐子 住所 新宿 渋谷 新宿

12 12 練習問題： SQL SELECT 氏名，学部名，所在地 FROM 学生，学部 WHERE 所属 = 学部名 学生 学部 学部名 理 工 経済 所在地 新宿 渋谷 氏名 合田知子 青木俊介 川内聡 坂口祐子 所属 理 経済 工 住所 新宿 渋谷 新宿

13 13 練習問題： SQL SELECT 氏名 FROM 学生，学部 WHERE 所属 = 学部名 AND 所在地 = ‘ 新宿 ’ 学生 学部 学部名 理 工 経済 所在地 新宿 渋谷 氏名 合田知子 青木俊介 川内聡 坂口祐子 所属 理 経済 工 住所 新宿 渋谷 新宿

14 14 練習問題： SQL SELECT 数量 FROM 注文 WHERE 数量 >= 70 SELECT AVG ( 数量 ) FROM 注文 WHERE 数量 >= 70 SELECT 商品番号, SUM( 数量 ) FROM 注文 WHERE 数量 >= 70 GROUP BY 商品番号 AVG （ ） … 平均を求め る … 商品番号ごとに それぞれ集計 注文 伝表顧客番号商品番号数量 0001100S10130 0002200T1050 0003300S2080 0004200S20240

15 15 論理データ独立性 利用者が見るデータ構造は、論理データモデ ル（論理的なデータ構造）から 独立させたい 顧客表 商品表 顧客番号 商品番号 氏名 商品名 例） 顧客番号 住所 単価 論理データ モデル 受注日 注文表 伝表番号 ビュ ー 売場担当 発送担当 商品名氏名受注日伝表番号単価数量 商品名氏名伝表番号数量住所 商品番号数量 ________________

16 16 データベースの３層スキーマ 外部スキーマ 利用者が見るデータ構造 「ビュー」に相当 概念スキーマ 論理的なデータ構造 「論理データモデル」に相当 内部スキーマ データを、記憶装置上にどう記録するか （物理的構造） ________________ 論理デー タ独立性 物理デー タ独立性

17 以下のビュー定義により定義される、ビュー「収益商品」表 を示せ CREATE VIEW 収益商品 AS SELECT * FROM 商品 WHERE 売値 – 仕入値 >= 40 以下の更新操作をした後の、ビュー「収益商品」表を示せ 商品コードが S 3 の行の売値を 250 に更新する 17 練習問題： SQL ( ビュー ) S08 R08 形式 T08 商品 200 140 売値 150 170 80 仕入値 100 パソコン S パソコン R 品名 パソコン T S 3 S 5 商品コード S 1

18 18 参考： トランザクション 一連のデータベース操作 （これ以上は分解でき ない） 例） 銀行： 顧客Ａの普通預金１万円を定期預金に 普通預金の表で、 顧客Ａの預金 - １万円 定期預金の表で、 顧客Ａの預金 + １万円 一連の操作が終了後、まとめて確定する （コミット） トランザクションが異常終了した場合には、 一連の操作をすべて取り消す （ロールバック） INSER T UPDATE COMMIT DELETEROLLBAC K トランザクショ ン １ トランザクショ ン ２

19 19 参考： その他、知っておくべき事項 データの正規化 同じデータの繰り返しを排除するように、表を設 計 第１正規形、第２正規形、第３正規形、主キーの 選択 SQL 文 ( もう少し複雑な SQL 文もある ) データベースの障害回復 ロール フォワード … バックアップとログから 復元 ロール バック … 異常終了からの回復 ウォーム スタート / コールド スタート トランザクション処理 排他制御 … 同一資源に対し、１つのトランザク ションが 更新中は、他に更新を許さない

20 20

21 21 練習問題解答： SQL SELECT 氏名 FROM 学生 WHERE 住所 = ‘ 新宿 ’ SELECT COUNT( 氏名 ) FROM 学生 WHERE 住所 = ‘ 新 宿 ’ ・ ２・ ２ 行数を数え る 学生 所属 理 工 経済 人文 氏名 合田知子 青木俊介 川内聡 坂口祐子 住所 新宿 渋谷 新宿 氏名 合田知子 坂口祐子

22 22 練習問題解答： SQL SELECT 氏名，学部名，所在地 FROM 学生，学部 WHERE 所属 = 学部名 所在地 新宿 渋谷 氏名 合田知子 青木俊介 川内聡 坂口祐子 学部名 理 工 経済 学生 学部 学部名 理 工 経済 所在地 新宿 渋谷 氏名 合田知子 青木俊介 川内聡 坂口祐子 所属 理 経済 工 住所 新宿 渋谷 新宿

23 23 練習問題解答： SQL SELECT 氏名 FROM 学生，学部 WHERE 所属 = 学部名 AND 所在地 = ‘ 新宿 ’ 氏名 合田知子 青木俊介 学生 学部 学部名 理 工 経済 所在地 新宿 渋谷 氏名 合田知子 青木俊介 川内聡 坂口祐子 所属 理 経済 工 住所 新宿 渋谷 新宿

24 24 練習問題： SQL SELECT 商品番号, 数量 FROM 注文 WHERE 数量 >= 70 SELECT AVG ( 数量 ) FROM 注文 WHERE 数量 >= 70 SELECT 商品番号, SUM( 数量 ) FROM 注文 WHERE 数量 >= 70 GROUP BY 商品番号 (130 + 80 + 240) / 3 = 150 注文 伝表顧客番号商品番号数量 0001100S10130 0002200T1050 0003300S2080 0004200S20240 数量 130 80 240 商品番号数量 S10130 S20320 商品番号 S10 S20

25 25 収益商品 練習問題解答： SQL ( ビュー ) 以下のビュー定義により定義される、ビュー「収益商品」表 を示せ CREATE VIEW 収益商品 AS SELECT * FROM 商品 WHERE 売値 – 仕入値 >= 40 商品コードが S 3 の行の売値を 250 に更新する 収益商品 注：ビュー「収益商品」に、 S 3 の行が増 える R08 形式 T08 140 売値 150 80 仕入値 100 パソコン R 品名 パソコン T S 5 商品コード S 1 S08 R08 形式 T08 250 140 売値 150 170 80 仕入値 100 パソコン S パソコン R 品名 パソコン T S 3 S 5 商品コード S 1

26 26

27 27

28 この教材のご利用について この教材は、以下に示す利用条件の下で、著作権者にわざわざ許 諾を求めることなく、無償で自由にご利用いただけます。講義、 自主学習はもちろん、翻訳、改変、再配布等を含めて自由にご利 用ください。 非商業利用に限定 この教材は、翻訳や改変等を加えたものも含めて、著作権者 の許諾を受けずに商業目的で利用することは、許可されてい ません。 著作権の帰属 この教材および教材中の図の著作権は、次ページ以降に示す 著作者に帰属します。この教材、または翻訳や改変等を加え たものを公開される場合には、「本教材 (or 本資料 ) は http://www.al.ics. saitama-u.ac.jp/horiyama/OCW/ の教材です (or 教材を改変したものです」 との旨の著作権表示を明確に 実施してください。なお、この教材に改変等を加えたものの 著作権は、次ページ以降に示す著作者および改変等を加えた 方に帰属します。 同一条件での頒布・再頒布 この教材、または翻訳や改変等を加えたものを頒布・再頒布 する場合には、頒布・再頒布の形態を問わず、このページの 利用条件に準拠して無償で自由に利用できるようにしてくだ さい。 28 この文面は、 TOKYO TECH OCW の利用 条件を参考にしまし た

29 配布場所 http://www.al.ics.saitama-u.ac.jp/horiyama/OCW/ この powerpoint ファイルの著作者 堀山 貴史 2007-2009 horiyama@al.ics.saitama-u.ac.jp 改変等を加えられた場合は、お名前等を追加してください 図の著作者 p. 2, 16 クリップアート : Microsoft Office Online / クリップアート その他 堀山 貴史 29 この教材のご利用について