基本情報技術概論 (第13回) 期末試験 2/10 (水) 5,6限 教室未定 データベース ＤＢ 埼玉大学 理工学研究科 堀山 貴史 1 データベース ＤＢ データに構造を与えて管理し、 更新や検索などが容易に行えるようにしたもの 例） 列車の切符販売、商品の販売管理、銀行の送金処理 DBMS 注文表 7/7 に注文された 商品は？ 利用者 S20 と T10 伝表 番号 受注日 顧客 番号 商品 番号 数量 0001 2007/07/07 100 S20 130 0002 2007/07/07 200 T10 50 0003 2007/07/08 300 S10 80 0004 2007/07/10 200 S20 240 ※ データベース管理システム （Database Management System） 2 論理データモデル データを、どのような形で表現するか (データの論理的構造とその内容を定義したもの) ________________ 階層モデル 木構造（階層構造）で、データの構造を表現 ________________ ネットワークモデル 木構造を発展させ、ネットワークでデータの構造を表現 ________________ 関係モデル （リレーショナル モデル） ２次元の表で、データの構造を表現 関係データベース （リレーショナル ＤＢ） で採用 3 関係データベースの例 注文表 伝表番号 受注日 顧客番号 商品番号 数量 0001 2007/07/07 100 S20 130 0002 2007/07/07 200 T10 50 0003 2007/07/08 300 S10 80 0004 2007/07/10 200 S20 240 顧客表 商品表 顧客番号 氏名 住所 商品番号 商品名 単価 100 赤塚不二夫 ○○○ S10 鉛筆 100 200 手塚治虫 △△△ S20 消しゴム 50 300 藤子不二雄 □□□ T10 ノート 500 4 関係データベースの用語 属性 （列、項目） 関係 注文表 伝表番号 受注日 顧客番号 商品番号 数量 0001 2007/07/07 100 S20 130 0002 2007/07/07 200 T10 50 0003 2007/07/08 300 S10 80 0004 2007/07/10 200 S20 240 行 （組） ドメイン（定義域） … 属性が取りえる値の集合 例） 商品番号のドメイン： S10，S20，T10，… 5 関係データベースの用語 主キー （続き） … 表の行を一意に識別する １つの表に主キーが同じ行は存在しない 外部キー … 他の表を参照する （関連付ける） ____________ 参照される側の表では、これが主キー 注文表 顧客表 伝表番号 受注日 顧客番号 商品番号 数量 顧客 番号 0001 2007/07/07 100 S20 130 100 0002 2007/07/07 200 T10 50 200 0003 2007/07/08 300 S10 80 300 0004 2007/07/10 200 S20 240 氏名 住所 赤塚不二夫 ○○○ 手塚治虫 △△△ 藤子不二雄 □□□ 顧客表の「主キー」 200 の行を参照すると、 氏名と住所が分かる 6 ＳＱＬ 関係データベースを定義・操作するためのデータベース言語 データベース定義 表の定義 … CREATE TABLE 表名 属性の定義 ビュー定義 … CREATE VIEW ビュー名 ビューの定義 etc. データベース操作 データ挿入 … I NSERT I NTO 表名 （各属性の値） データ削除 … DELETE FROM 表名 削除の指定 データ更新 … UPDATE 表名 SET 属性名 = 更新値 SELECT etc. データベース制御 7 データ操作 （関係演算） ____________ 射影 指定した属性（列）を取り出す 例） SELECT 商品番号，数量 FROM 注文表 取り出す属性 注文表 伝表 番号 受注日 顧客 番号 商品 番号 数量 0001 2007/07/07 100 S20 130 S20 130 0002 2007/07/07 200 T10 50 T10 50 0003 2007/07/08 300 S10 80 S10 80 0004 2007/07/10 200 S20 240 S20 240 商品番号 数量 8 関係データベースの操作 （関係演算） ____________ 選択 条件を満たす行を取り出す 例） SELECT ＊ FROM 注文表 WHERE 数量 >= 100 すべての属性 条件 注文表 伝表 番号 受注日 顧客 番号 商品 番号 0001 2007/07/07 100 S20 0002 2007/07/07 200 0003 2007/07/08 0004 2007/07/10 数量 130 伝表 番号 受注日 顧客 番号 商品 番号 数量 T10 50 0001 2007/07/07 100 S20 130 300 S10 80 0004 2007/07/10 200 S20 240 200 S20 240 9 関係データベースの操作 （関係演算） ____________ 結合 複数の表を、共通の属性でくっつける 例） SELECT 伝票番号，顧客番号，商品名，数量 FROM 注文表，商品表 WHERE 注文表．商品番号 ＝ 商品表．商品番号 複数の表 複数の表にある属性は、 表の名をつけて区別 注文表 伝表 番号 0001 0002 受注日 2007/07/07 2007/07/07 顧客 番号 商品 番号 数量 100 200 S20 T10 130 50 商品表 商品番号 S20 T10 商品名 消しゴム ノート 単価 50 500 伝表 番号 顧客 番号 商品名 数量 0001 100 消しゴム 130 0002 200 ノート 50 10 練習問題： SQL SELECT 氏名 FROM 学生 WHERE 住所 = ‘新宿’ SELECT COUNT(氏名) FROM 学生 WHERE 住所 = ‘新宿’ 行数を数える 学生 氏名 合田知子 青木俊介 川内聡 坂口祐子 所属 理 工 人文 経済 住所 新宿 渋谷 渋谷 新宿 11 練習問題： 学生 SELECT 氏名，学部名，所在地 FROM 学生，学部 WHERE 所属 = 学部名 氏名 合田知子 青木俊介 川内聡 坂口祐子 学部 SQL 学部名 理 工 経済 所属 理 工 経済 経済 住所 新宿 渋谷 渋谷 新宿 所在地 新宿 新宿 渋谷 12 練習問題： 学生 SELECT 氏名 FROM 学生，学部 WHERE 所属 = 学部名 AND 所在地 = ‘新宿’ 氏名 合田知子 青木俊介 川内聡 坂口祐子 学部 SQL 学部名 理 工 経済 所属 理 工 経済 経済 住所 新宿 渋谷 渋谷 新宿 所在地 新宿 新宿 渋谷 13 練習問題： SQL SELECT 数量 FROM 注文 WHERE 数量 >= 70 SELECT AVG (数量) FROM 注文 WHERE 数量 >= 70 … 商品番号ごとに SELECT 商品番号, SUM(数量) FROM 注文 それぞれ集計 WHERE 数量 >= 70 GROUP BY 商品番号 注文 伝表 顧客番号 商品番号 数量 0001 100 S10 130 0002 200 T10 50 0003 300 S20 80 0004 200 S20 240 AVG（ ） … 平均を求める 14 論理データ独立性 ________________ 利用者が見るデータ構造は、論理データモデル （論理的なデータ構造）から 独立させたい 例） 論理データ 注文表 伝表番号 モデル 顧客表 商品表 ビュー 売場担当 受注日 顧客番号 商品番号 数量 顧客番号 氏名 住所 商品番号 商品名 単価 伝表番号 受注日 発送担当 伝表番号 氏名 氏名 商品名 単価 住所 商品名 数量 数量 15 データベースの３層スキーマ ________________ 外部スキーマ 利用者が見るデータ構造 「ビュー」に相当 ________________ 概念スキーマ 論理的なデータ構造 「論理データモデル」に相当 論理データ 独立性 物理データ 独立性 ________________ 内部スキーマ データを、記憶装置上にどう記録するか （物理 的構造） 16 練習問題： SQL (ビュー) 以下のビュー定義により定義される、ビュー「収益商品」表を示せ CREATE VIEW 収益商品 AS SELECT * FROM 商品 WHERE 売値 – 仕入値 >= 40 商品 商品コード S1 S3 S5 品名 パソコンT パソコンS パソコンR 形式 T08 S08 R08 売値 150 200 140 仕入値 100 170 80 以下の更新操作をした後の、ビュー「収益商品」表を示せ 商品コードが S 3 の行の売値を 250 に更新する 17 参考： トランザクション 一連のデータベース操作 （これ以上は分解できない） 例） 銀行： 顧客Ａの普通預金１万円を定期預金に 普通預金の表で、 顧客Ａの預金 -１万円 定期預金の表で、 顧客Ａの預金 +１万円 一連の操作が終了後、まとめて確定する （コミット） トランザクションが異常終了した場合には、 一連の操作をすべて取り消す （ロールバック） トランザクション １ トランザクション ２ INSERT UPDATE COMMIT UPDATE DELETE ROLLBACK 18 参考： その他、知っておくべき事項 データの正規化 同じデータの繰り返しを排除するように、表を設計 第１正規形、第２正規形、第３正規形、主キーの選択 SQL 文 (もう少し複雑な SQL 文もある) データベースの障害回復 ロール フォワード … バックアップとログから復元 ロール バック … 異常終了からの回復 ウォーム スタート / コールド スタート トランザクション処理 排他制御 … 同一資源に対し、１つのトランザクションが 更新中は、他に更新を許さない 19 20 練習問題解答： SQL SELECT 氏名 FROM 学生 WHERE 住所 = ‘新宿’ SELECT COUNT(氏名) FROM 学生 WHERE 住所 = ‘新宿’ 行数を数える 学生 氏名 合田知子 青木俊介 川内聡 坂口祐子 所属 理 工 人文 経済 住所 新宿 渋谷 渋谷 新宿 氏名 合田知子 坂口祐子 ・２ 21 練習問題解答： 学生 SELECT 氏名，学部名，所在地 FROM 学生，学部 WHERE 所属 = 学部名 氏名 合田知子 青木俊介 川内聡 坂口祐子 学部 SQL 学部名 理 工 経済 所属 理 工 経済 経済 所在地 新宿 新宿 渋谷 住所 新宿 渋谷 渋谷 新宿 氏名 合田知子 青木俊介 川内聡 坂口祐子 学部名 理 工 経済 経済 所在地 新宿 新宿 渋谷 渋谷 22 練習問題解答： 学生 SELECT 氏名 FROM 学生，学部 WHERE 所属 = 学部名 AND 所在地 = ‘新宿’ 氏名 合田知子 青木俊介 川内聡 坂口祐子 学部 SQL 学部名 理 工 経済 所属 理 工 経済 経済 所在地 新宿 新宿 渋谷 住所 新宿 渋谷 渋谷 新宿 氏名 合田知子 青木俊介 23 練習問題解答： SQL SELECT 商品番号, 数量 FROM 注文 WHERE 数量 >= 70 SELECT AVG (数量) FROM 注文 WHERE 数量 >= 70 注文 商品番号 数量 S10 130 S20 80 S20 240 (130 + 80 + 240) / 3 = 150 SELECT 商品番号, SUM(数量) FROM 注文 WHERE 数量 >= 70 GROUP BY 商品番号 伝表 顧客番号 商品番号 数量 商品番号 数量 0001 100 S10 130 S10 130 0002 200 T10 50 S20 320 0003 300 S20 80 0004 200 S20 240 24 練習問題解答： 以下のビュー定義により定義される、ビュー「収益商品」表を示せ CREATE VIEW 収益商品 AS SELECT * FROM 商品 WHERE 売値 – 仕入値 >= 40 収益商品 商品コード S1 S5 SQL (ビュー) 品名 パソコンT パソコンR 形式 T08 R08 売値 150 140 仕入値 100 80 商品コードが S 3 の行の売値を 250 に更新する 収益商品 商品コード S1 S3 S5 品名 パソコンT パソコンS パソコンR 形式 T08 S08 R08 注：ビュー「収益商品」に、S 3 の行が増える 売値 150 250 140 仕入値 100 170 80 25 26 27 この教材のご利用について この文面は、TOKYO TECH OCW の利用 条件を参考にしました この教材は、以下に示す利用条件の下で、著作権者にわざわざ許諾を 求めることなく、無償で自由にご利用いただけます。講義、自主学習は もちろん、翻訳、改変、再配布等を含めて自由にご利用ください。 非商業利用に限定 この教材は、翻訳や改変等を加えたものも含めて、著作権者の許 諾を受けずに商業目的で利用することは、許可されていません。 著作権の帰属 この教材および教材中の図の著作権は、次ページ以降に示す著 作者に帰属します。この教材、または翻訳や改変等を加えたもの を公開される場合には、「本教材 (or 本資料) は http://www.al.ics. saitama-u.ac.jp/horiyama/OCW/ の教材です (or 教材を改変した ものです」 との旨の著作権表示を明確に実施してください。なお、 この教材に改変等を加えたものの著作権は、次ページ以降に示す 著作者および改変等を加えた方に帰属します。 同一条件での頒布・再頒布 この教材、または翻訳や改変等を加えたものを頒布・再頒布する 場合には、頒布・再頒布の形態を問わず、このページの利用条件 28 に準拠して無償で自由に利用できるようにしてください。 この教材のご利用について 配布場所 http://www.al.ics.saitama-u.ac.jp/horiyama/OCW/ この powerpoint ファイルの著作者 堀山 貴史 2007-2010 [email protected] 改変等を加えられた場合は、お名前等を追加してください 図の著作者 p. 2, 16 クリップアート : Microsoft Office Online / クリップアート その他 堀山 貴史 29
© Copyright 2024 ExpyDoc
