prev head next
３−１ ＤＢ構造
本システムのデータはログの類を除いて、全てＰｏｓｔｇｒｅＳＱＬ上におかれます。
以下にＰｏｓｔｇｒｅＳＱＬ上のテーブルの一覧と作成のＳＱＬにコメントをつけて説
明します。
３−１−１ テーブル一覧
1)SCORE スコア スコアリングする指標
2)RESORT リゾート リゾート、名前、説明
3)RVALUE リゾートスコア値 各リゾートの持つスコア値
4)QUESTION 質問 質問一覧
5)ANSWER 回答 質問に対応した回答の選択肢
6)AVALUE 回答スコア値 各回答のスコア値
7)USER 回答者 回答者に関する情報
8)KEKKA 結果 質問実施により得られた総括的な情報
9)KREC 回答結果 個々の質問に関する結果
10)SESSION セッション情報 アプレット実行時の進行状況を保持・更新す
る。
1)〜6)は内部のメンテナンスシステムによりデータ作成・修正され、アプレッ
ト実行時に参照されます。
7)〜9)はアプレット上で利用者が入力したデータより作成されます。
10)はアプレットの進行状況により追加、逐次更新されます。進行状況の管理
のためにあるもので、システムの機能に直接関係ありません。
３−１−２ 各テーブル詳細
ＳＱＬコマンドなので、だいたいは理解していただけるとおもいますが、ＳＱ
Ｌをご存知でない方や、ＰｏｓｔｇｒｅＳＱＬ独自拡張な文法については、Ｐｏ
ｓｔｇｒｅＳＱＬのホームページでも参照してください。
１）スコア
評価指標を保持するテーブルです。
CREATE TABLE "score" ( -- スコア
"snm" bigint NOT NULL, -- スコア番号
"inf" character varying(200) NOT NULL, -- スコアの説明文
Constraint "score_pkey" Primary Key ("snm")
);
２）リゾート
候補であるリゾートに関する情報を保持します。
CREATE TABLE "resort" ( -- リゾート
"rnm" bigint NOT NULL, -- リゾート番号
"nam" character varying(60) NOT NULL, -- リゾート名称
"inf" character varying(500), -- リゾートの説明文
"fil" character varying(200), -- 付加情報（未使用）
Constraint "resort_pkey" Primary Key ("rnm")
);
３）リゾートスコア値
リゾートに付随するスコア値を保持します。２）のリゾートに付随して０個
以上が作成されます。
CREATE TABLE "rvalue" ( -- リゾートに付随するスコア値
"rnm" bigint NOT NULL, -- 対応するリゾート番号
"snm" bigint NOT NULL, -- 対応するスコア番号
"scn" bigint NOT NULL, -- スコア値
Constraint "rvalue_pkey" Primary Key ("rnm",
"snm")
);
４）質問
質問に関する情報を保持します。
CREATE TABLE "question" ( -- 質問
"qnm" bigint NOT NULL, -- 質問番号
"flg" shortint, -- フラグ 0:複数選択不可
1:複数選択可
"inf" character varying(500) NOT NULL, -- 質問文
Constraint "question_pkey" Primary Key ("qnm")
);
５）回答
回答に関する情報を保持します。４）の質問に付随して２個以上が作成され
ます。
CREATE TABLE "answer" ( -- 回答
"anm" bigint NOT NULL, -- 回答の番号
"qnm" bigint NOT NULL, -- 対応する質問番号
"inf" character varying(500) NOT NULL, -- 回答文
Constraint "answer_pkey" Primary Key ("anm")
);
６）回答スコア値
回答に関するスコア情報を保持します。５）の回答に付随して０個以上が作
成されます。
CREATE TABLE "avalue" ( -- 回答スコア
"anm" bigint NOT NULL, -- 回答の番号
"snm" bigint NOT NULL, -- 対応するスコア番号
"scn" bigint NOT NULL, -- スコア値
Constraint "avalue_vkey" Primary Key ("anm",
"snm")
);
７）回答者
利用者に関する情報を保持します。
CREATE SEQUENCE usq START 1001 -- 自動付番
MAXVALUE 2000;
CREATE TABLE "usr" ( -- 回答者
"unm" bigint NOT NULL -- ユーザ番号
DEFAULT NEXTVAL('usq'),
"nam" character varying(40) NOT NULL -- ユーザ名
UNIQUE,
"age" bigint, -- 年齢
"uex" bigint, -- 性別 1:男 2:女
"mad" character varying(40), -- メールアドレス
"adr" character(2), -- 居住地コード
"tim" timestamp with time zone -- 登録日時
DEFAULT now(),
"fil" character varying(200), -- 付加情報（現在未使用）
Constraint "usr_pkey" Primary Key ("unm")
);
ユーザ番号は自動設定
ユーザ名、年齢、性別、メールアドレスはユーザ入力値
８）結果
回答による集計結果の総括を保持します。
CREATE SEQUENCE ksq MINVALUE 1001 -- 自動付番
MAXVALUE 20000;
CREATE TABLE "kekka" ( -- 結果
"knm" bigint NOT NULL -- 結果番号
DEFAULT NEXTVAL('ksq'),
"unm" bigint NOT NULL, -- ユーザ番号
"tim" timestamp with time zone -- 登録日時
DEFAULT now(),
"tsc" integer NOT NULL, -- 最高スコア値
"mnr" bigint NOT NULL, -- 最高スコアリゾート番号
Constraint "kekka_pkey" Primary Key ("knm")
);
９）結果一覧
利用者が選択した個々の回答情報を保持します。
CREATE TABLE "krec" ( -- 結果一覧
"knm" bigint NOT NULL, -- 結果番号
"anm" bigint NOT NULL, -- 回答番号
"val" bigint NOT NULL, -- スコアに対する重み
--（現状1のみ）
Constraint "krec_pkey" Primary Key ("knm",
"anm" )
);
１０）セッション情報
各アプレットの進行状況を保持します。
CREATE SEQUENCE ssq MINVALUE 100001; -- 自動付番
CREATE TABLE "session" ( -- 結果
"sid" bigint NOT NULL -- セッションID
DEFAULT NEXTVAL('ssq'),
"ver" bigint NOT NULL, -- バージョン
"dit" bigint NOT NULL, -- データ種別
"tim" timestamp with time zone -- 初期登録日時
DEFAULT now(),
"ipa" integer, -- IPアドレス
"sta" integer NOT NULL, -- 処理ステータス
"tup" timestamp with time zone -- 最終更新日時
DEFAULT now(),
Constraint "sid_pkey" Primary Key ("sid")
);
３−１−３ データ処理の流れ
データ処理の流れを簡単に説明します。
１）〜６）は事前に準備されています。システムの利用を開始すると、４）、５）の質
問、回答情報が参照されアプレットに渡されます。
アプレットはそれを表示し、利用者は回答を選択します。
選択された回答情報は９）結果一覧に格納されます。
そして、各リゾートのスコア値の計算、上位リゾートのスコア値、情報がアプレットに
返されます。
スコア値の計算は次の一文のＳＱＬでおこなわれます。
pgsql=# select aa.nam,bb.stl from
pgsql-# resort aa,
pgsql-# (
pgsql(# select a.rnm as rnm, sum(a.scn * b.atl ) as stl from
pgsql(# rvalue a,
pgsql(# ( select snm,sum(scn) as atl from avalue where anm
in
pgsql(# (select anm from krec where knm = 1001 )
pgsql(# group by snm ) b
pgsql(# where a.snm = b.snm group by a.rnm
pgsql(# ) bb
pgsql-# where bb.rnm = aa.rnm;
nam | stl
--------+-----
グアム | 296
沖縄 | 418
タヒチ | 265
江ノ島 | 270
(4 rows)
上の例は１−３のスコア計算表内容をＤＢ化けし、psql上でＳＱＬを実行しました。
結果番号が1001番の回答について各リゾートの集計をおこなっています。
手計算でおこなったときは、１時間は費やしました。
結果をみるとそんなにかからないとおもうかもしれませんが、ない状態からですから、
大変です。それが上記の一文（といってもずいぶん長いけど）のＳＱＬで一瞬にして得られるのです。
※ 参考までに関連するテーブルのデータを付録−１に添付しました。
興味のある方はＯｒａｃｌｅやＩｎｆｏｒｍｉｘでも試してみてください。
prev head next