9.期末課題企画

テーブル結合の復習

課題1/2

SQLのテーブル作成コマンド CREATE TABLE の動作を確認する

SQLZOOの問題用データベースを２つSQLコマンドで作成してみよう

世界の国のデータベース

ハリウッド映画のデータベース

　下記のテーブルの設計例のテンプレートに掲載したテーブル作成のSQLをXAMPPで実行する

・適当にデータベースを作成して（例　no09_学籍番号）

・SQLタブでCREATE TABLEコマンドを実行

回答をphpMyAdminのクエリ機能で作成してみる

・クエリ　タブで結合するテーブルを設定して、【クエリを更新】してSQLを表示する

注）クエリを実行してもテーブルにレコードが存在しないため空のテーブルが表示されるがレコードを挿入しておく必要はない

提出先 Webclass

第9回問1

1. worldsとcontinentsを結合して全フィールドと全レコードを表示するクエリ

2. moviesとactors（directorを含む）とcastsとactorsを結合して全フィールドと全レコードを表示するクエリ

１つの回答欄に両方とも記入すること

2021年度用の解説動画

今後のスケジュール

第9回（今回）

期末課題のデータベースの企画と設計

自作のオリジナルデータベースを企画する

第10回

CakePHPのチュートリアルとしてCMSデータベースを作成

CMSのテーブル設計は資料に掲載したSQLをそのまま利用（自作不要）

作業手順を確認して、練習をする回

第11～第15回

期末課題作成期間

自作のオリジナルデータベースをCakePHPで実装

追加課題

早期に期末課題が完成した受講生または追加点を狙う受講生用の課題

x. ビュー演習2022

x. インデックス演習2024

x. トランザクションとロールバック演習2022

x. セキュリティ設定演習2022

CakePHPについて

CakePHP のサイト
プログラム言語にPHP、データベースにMySQLを採用したWebアプリケーションフレームワーク
CakePHPの設計方針に従って開発を進めることでコーディング時間を減らして手軽にWebアプリケーションを実装できる
CMSチュートリアル
ニュースサイトの制作チュートリアル。ユーザ（筆者）と記事とタグ（ジャンル、複数設定可能）と中間テーブル（記事とタグ）の4つのテーブルによる実装例
チュートリアルにはユーザ認証を含む。この講義ではユーザ認証とアクセス制御については扱わない。
第10回でこのチュートリアルに取り組む
CakePHPのデータベースの規約
CakePHPの命名規約に従ったテーブル設計を行うことでアプリケーションを半自動的に構築できる。
期末課題のテーブル名、フィールド名（主キー、外部キー、見出し項目）はCakePHPの命名規約に従った英単語で設計する。
第9回課題では日本語のテーブル名やフィールド名のままでよい。

課題2/2

期末課題用のデータベースのテーマを考える

レコードの具体例を書いてみる

テーブルを設計する

テーブル名を決める

フィールド名を決める

主キー、外部キーを決める

フィールドの型を決める

第９回の課題ではテーブル名やフィールド名は　日本語でOK。フィールドの型も未定でOK

第１１回の課題では　英語　で表記する。フィールドの型も具体的に決める

テーマとテーブル設計に工夫が無い場合は評価を下げる

例）他の受講生のテーマとテーブル設計がほぼ同じ場合など

例）データベースが致命的に機能不足（テーマが実現できていない）

例）正規化不足

下記のテンプレートやデータベースの例、または第8回までで扱ったデータベースを参考にして期末課題用のデータベースを設計する。

提出先　Webclass

第9回問2

回答内容

テーマを書いて説明する。

「データベースのテーマ」何をどのように記録するのかが伝わるように書く。

テーブル設計を以下の書式で示す。

「テーブル名１（〇フィールド名１、フィールド名２、※フールド名３）」

先頭の〇は主キーの印、先頭の※は外部キーの印

例１）

「ボードゲーム貸し出し管理」ゲーム、利用者、貸し出し履歴の記録

利用者（〇id、名前、学籍番号）

メーカー（〇id、名前）

ゲーム（〇id、タイトル、※メーカー_id、デザイナー）

ジャンル（〇id、名前）

ゲーム＿ジャンル（〇※ゲーム_id、〇※ジャンル_id）

利用記録（〇id、※利用者_id、※ゲーム_id、利用日時、返却）

補足：　このデータベースでは1つのゲームに対して複数のジャンルを設定可能。また1つのジャンルに複数のゲームが含まれるのでゲームとジャンルは多対多の関係になる。このような場合、ゲーム＿ジャンルテーブルを用意してゲームとジャンルの対応関係を記録する。ゲームデザイナーテーブルは用意せずに名前を記録する。

例２）
「ボードゲームプレイ記録」ゲーム、参加者、順位を記録

参加者（〇id、プレイヤー名、チーム名）

メーカー（〇id、名前、国）

ゲーム（〇id、タイトル、※メーカー_id、デザイナー、発売年）

ジャンル（〇id、名前）

ゲーム＿ジャンル（〇※ゲーム_id、〇※ジャンル_id）

プレイ記録（〇id、※ゲーム_id、会場、プレイ開始日時、プレイ終了日時）

参加記録（〇id、※プレイ_id、※参加者_id、順位や得点）

企画の注意点：

ランキング表、売上表、成績表などは記録の集計結果として得られるものです。テーブルの設計には含めません。

ランキング表　→　投票や得点などを記録して、期間やジャンルなど特定の条件を定めてSQLのクエリー集計する。集計結果を並べ替えたビュー

売上表　→　販売記録を集計したビュー

成績表　→　履修記録を集計したビュー

ビューはテーブルをデータベースに実装した後で、SQLコマンドでデータベースに実装します。

データベースの想定利用者

個人、チーム、組織、企業など自由に想定して構いません。小規模な利用状況をお勧めします。

様々な利用者が必要とするような機能も企画する。並べ替え、集計、分類など。これらもSQLで実装する。

テーブル設計テンプレート

これまでの講義で扱ってきたテーブル設計をCakePHPのデータベース規約の命名規則で掲載しておきます。

修正してカスタマイズして期末課題の設計の土台として利用してもＯＫです。

CakePHPのCMSチュートリアルのテーブル設計の簡略版

usersユーザー（記者）、created作成日時、articles記事、tagsタグ、titleタイトル、body本文、published公開

CREATE TABLE users (

id INT AUTO_INCREMENT PRIMARY KEY,

email VARCHAR(255) NOT NULL,

created DATETIME

);

CREATE TABLE articles (

id INT AUTO_INCREMENT PRIMARY KEY,

user_id INT NOT NULL,

title VARCHAR(255) NOT NULL,

body TEXT,

published BOOLEAN DEFAULT FALSE,

created DATETIME,

FOREIGN KEY (user_id) REFERENCES users(id)

);

CREATE TABLE tags (

id INT AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(191),

UNIQUE KEY (title)

);

CREATE TABLE articles_tags (

article_id INT NOT NULL,

tag_id INT NOT NULL,

PRIMARY KEY (article_id, tag_id),

FOREIGN KEY (tag_id) REFERENCES tags(id),

FOREIGN KEY (article_id) REFERENCES articles(id)

);

SQLZOOのworldテーブル再設計版

CREATE TABLE continents (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50) NOT NULL UNIQUE KEY

);

CREATE TABLE worlds (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50) NOT NULL UNIQUE KEY,

continent_id INT NOT NULL,

area DECIMAL(10,0),

population DECIMAL(11,0),

gdp DECIMAL(14,0),

capital VARCHAR(60),

tld VARCHAR(5),

flag VARCHAR(255),

FOREIGN KEY (continent_id) REFERENCES continents(id)

);

SQLZOOのnobelテーブル再設計版

CREATE TABLE subjects (

id INT AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(15) NOT NULL UNIQUE KEY

);

CREATE TABLE nobels (

id INT AUTO_INCREMENT PRIMARY KEY,

yr YEAR,

subject_id INT NOT NULL,

name VARCHAR(50),

FOREIGN KEY (subject_id) REFERENCES subjects(id)

);

SQLZOOのUEFA EURO 2012データベースのテーブルの再設計版

CREATE TABLE eteams (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(3) NOT NULL UNIQUE KEY,

fullname VARCHAR(50) UNIQUE KEY,

coach VARCHAR(50)

);

CREATE TABLE stadiums (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100)

);

CREATE TABLE games (

id INT AUTO_INCREMENT PRIMARY KEY,

mdate DATETIME,

stadium_id INT NOT NULL,

team1 INT NOT NULL,

team2 INT NOT NULL,

FOREIGN KEY (stadium_id) REFERENCES stadiums(id),

FOREIGN KEY (team1) REFERENCES eteams(id),

FOREIGN KEY (team2) REFERENCES eteams(id)

);

CREATE TABLE players (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(100) NOT NULL,

eteam_id INT NOT NULL,

FOREIGN KEY (eteam_id) REFERENCES eteams(id)

);

CREATE TABLE goals (

id INT AUTO_INCREMENT PRIMARY KEY,

game_id INT NOT NULL,

player_id INT NOT NULL,

goal_time INT(11) NOT NULL,

FOREIGN KEY (game_id) REFERENCES games(id),

FOREIGN KEY (player_id) REFERENCES players(id)

);

SQLZOOの映画データベースのテーブルの再設計版

CREATE TABLE actors (

id INT AUTO_INCREMENT PRIMARY KEY,

name VARCHAR(50) NOT NULL

);

CREATE TABLE movies (

id INT AUTO_INCREMENT PRIMARY KEY,

title VARCHAR(50) NOT NULL,

yr YEAR,

actor_id INT NOT NULL COMMENT 'director',

budget INT(11),

gross INT(11),

FOREIGN KEY (actor_id) REFERENCES actors(id)

);

CREATE TABLE casts (

id INT AUTO_INCREMENT PRIMARY KEY,

movie_id INT NOT NULL,

actor_id INT NOT NULL,

ord INT(11),

FOREIGN KEY (movie_id) REFERENCES movies(id),

FOREIGN KEY (actor_id) REFERENCES actors(id)

);

SQLZOOに掲載されているデータベースの紹介

この講義で課題とした問題1～7の他に以下の様なデータベースが掲載されいる。

音楽　https://sqlzoo.net/wiki/Music_Tutorial/ja アルバム　と　トラック

教員と学科　https://sqlzoo.net/wiki/Using_Null/ja 教員連絡リスト　と　学科リスト

政党リスト　https://sqlzoo.net/wiki/Scottish_Parliament 議員　と　政党

学生アンケート　https://sqlzoo.net/wiki/NSS_Tutorial 回答者と回答内容

選挙結果　https://sqlzoo.net/wiki/Window_functions　選挙ごとの選挙区と立候補者と得票数

COVID-19の状況　https://sqlzoo.net/wiki/Window_LAG 国集計日感染者死亡者回復者の状況

バス路線　https://sqlzoo.net/wiki/Self_join　停留所　と　路線

試験結果　https://sqlzoo.net/wiki/DDL_Student_Records　学生　と　試験　と　成績

学習履歴　https://sqlzoo.net/wiki/Module_Feedback　学生とコースと成績

ヘルプディスク　https://sqlzoo.net/wiki/Help_Desk スタッフとシフトと顧客と応答履歴

ホテル宿泊記録　https://sqlzoo.net/wiki/Guest_House 部屋と宿泊者と利用期間

顧客受注管理　https://sqlzoo.net/wiki/AdventureWorks 顧客、製品、注文の管理

大学時間割　https://sqlzoo.net/wiki/Neeps 教室、授業、受講クラス、授業担当者の管理

ミュージシャンデータベース　https://sqlzoo.net/wiki/Musicians 音楽家　と　バンド　と　楽曲　と　演奏

仕立屋　https://sqlzoo.net/wiki/Dressmaker 型　サイズ　素材　の受注記録

混雑課金　https://sqlzoo.net/wiki/Congestion_Charging　監視カメラの通過記録と車両所有者への課金記録

エリートデンジャラス（作成中） https://www.sqlzoo.net/wiki/Elite_Dangerous 『Elite Dangerous』2014のゲーム。スペースフライトシミュレーター

過去のデータベース受講生の期末課題テーマの例

レポートフォルダの過去の受講生のテーマ一覧

AIで生成したデータベース課題テーマ

２０１１

２００６以前と２０１０

Webで運用されているデータベースやスマホのアプリ等もテーマ選定の参考にできる。他にも幾つもありそう。

参照

ゲームプログラム用データベースの設計の話題

ゲームプランナーが『バグの少ないデータを効率的に作る』ための最低限の知識と考え方

Page updated

Google Sites

Report abuse