/*
 * 博碩 DB20060　SQL基礎講座的表單製作 Script，修改適合在 PostgreSQL 上使用
 * 適合 PostgreSQL，於 8.3.7 版以上測試無誤
 * 採取 BSD 授權條約散布，請在自行修改後及採用時註明出處
 * 修改者：謝啟仁	修改版本：第二次修改	修改時間：2009/6/16
 */

-- 開始一個異動行程
BEGIN;

-- 舊表單刪除
DROP TABLE IF EXISTS "販賣資料", "商品清單", "負責人清單", "顧客清單", "分店負責人清單";

-- 表單製作：商品清單
CREATE TABLE "商品清單" (
    "商品ID"     NUMERIC NOT NULL,
    "商品名稱"     VARCHAR(30) NOT NULL,
    "群組名稱" VARCHAR(20) NOT NULL,
    "進貨單價"   NUMERIC,
    "販賣單價"     NUMERIC,
    CONSTRAINT pk_shohin PRIMARY KEY ("商品ID")
);

-- 表單製作：負責人清單
CREATE TABLE "負責人清單" (
    "負責人ID"   NUMERIC NOT NULL,
    "負責人姓名"   VARCHAR(20) NOT NULL,
    "姓名拼音"   VARCHAR(20) NOT NULL,
    MGR_ID     NUMERIC,
    "出生日期"   DATE NOT NULL,
    "性別"       NUMERIC NOT NULL,
    CONSTRAINT pk_tanto PRIMARY KEY ("負責人ID"),
    CONSTRAINT fk_mgr FOREIGN KEY (MGR_ID) REFERENCES "負責人清單"("負責人ID"),
    CONSTRAINT ck_gender CHECK ("性別"=0 OR "性別"=1)
);

-- 表單製作：顧客清單
CREATE TABLE "顧客清單" (
    "顧客ID"     NUMERIC NOT NULL,
    "顧客名稱"     VARCHAR(20) NOT NULL,
    "聯絡地址"     VARCHAR(20) NOT NULL,
    CONSTRAINT pk_kokyaku PRIMARY KEY ("顧客ID")
);

-- 表單製作：販賣資料
CREATE TABLE "販賣資料" (
    "傳票編號"   NUMERIC NOT NULL,
    "列編號"     NUMERIC NOT NULL,
    "處理日"     DATE NOT NULL,
    "商品ID"     NUMERIC NOT NULL,
    "負責人ID"   NUMERIC NOT NULL,
    "顧客ID"     NUMERIC NOT NULL,
    "數量"       NUMERIC NOT NULL,
    CONSTRAINT pk_uriage PRIMARY KEY ("傳票編號", "列編號"),
    CONSTRAINT fk_shohin FOREIGN KEY ("商品ID") REFERENCES "商品清單"("商品ID"),
    CONSTRAINT fk_tanto FOREIGN KEY ("負責人ID") REFERENCES "負責人清單"("負責人ID"),
    CONSTRAINT fk_kokyaku FOREIGN KEY ("顧客ID") REFERENCES "顧客清單"("顧客ID")
);

-- 表單製作：分店負責人清單
CREATE TABLE "分店負責人清單" (
    "分店負責人ID"   NUMERIC NOT NULL,
    "分店負責人姓名"   VARCHAR(20) NOT NULL,
    "姓名拼音"   VARCHAR(20) NOT NULL,
    MGR_ID     NUMERIC,
    "出生日期"   DATE NOT NULL,
    "性別"       NUMERIC NOT NULL,
    CONSTRAINT pk_siten_tanto PRIMARY KEY ("分店負責人ID"),
    CONSTRAINT fk_siten_mgr FOREIGN KEY (MGR_ID) REFERENCES "分店負責人清單"("分店負責人ID"),
    CONSTRAINT ck_siten_gender CHECK ("性別"=0 OR "性別"=1)
);

-- 異動行程結束
COMMIT;