-- online 為schema名稱,一個DB2的表格全名為 Schema_Name.Table_Name -- DB2 對於FOREIGN KEY CONSTRAINT 未支援 ON UPDATE CASCADE, 只提---供 NO ACTION & RESTRICT. DROP TABLE online.Member; CREATE TABLE online.Member (mId CHAR(8) NOT NULL, pId CHAR(10) NOT NULL, name VARCHAR(8) NOT NULL, birthday DATE, phone VARCHAR(10), address VARCHAR(40), email VARCHAR(20), introducer CHAR(8), CONSTRAINT PK_Member PRIMARY KEY (mId), CONSTRAINT UI_pId UNIQUE (pId), CONSTRAINT FK_Member FOREIGN KEY(introducer)REFERENCES online.Member(mId) ON DELETE SET NULL ON UPDATE NO ACTION); COMMENT ON TABLE online.Member is '會員'; COMMENT ON COLUMN online.Member.mId is '會員編號'; COMMENT ON COLUMN online.Member.pId is '身份証ID'; COMMENT ON COLUMN online.Member.name is '姓名'; COMMENT ON COLUMN online.Member.birthday is '生日'; COMMENT ON COLUMN online.Member.phone is '電話'; COMMENT ON COLUMN online.Member.address is '住址'; COMMENT ON COLUMN online.Member.email is '電子郵件'; COMMENT ON COLUMN online.Member.introducer is '介紹人'; COMMENT ON CONSTRAINT online.Member.PK_Member is '會員表格主鍵'; COMMENT ON CONSTRAINT online.Member.UI_pId is '身份証ID為唯一'; COMMENT ON CONSTRAINT online.Member.FK_Member is '參考到會員本身外部鍵'; DROP TABLE online.Transaction; CREATE TABLE online.Transaction (tNo CHAR(5) NOT NULL, transMid CHAR(8) NOT NULL, transTime TIMESTAMP NOT NULL, method VARCHAR(5) NOT NULL, bankId VARCHAR(14) NOT NULL, bankName VARCHAR(20), cardType VARCHAR(10), cardId VARCHAR(10), dueDate DATE, CONSTRAINT PK_Transaction PRIMARY KEY (tNo), CONSTRAINT FK_Member FOREIGN KEY (transMid) REFERENCES online.Member(mId)); COMMENT ON TABLE online.Transaction is '交易'; COMMENT ON COLUMN online.Transaction.tNo is '交易編號'; COMMENT ON COLUMN online.Transaction.transMid is '會員編號'; COMMENT ON COLUMN online.Transaction.transTime is '交易時間'; COMMENT ON COLUMN online.Transaction.method is '交易方式'; COMMENT ON COLUMN online.Transaction.bankId is '銀行代號'; COMMENT ON COLUMN online.Transaction.bankName is '銀行名稱'; COMMENT ON COLUMN online.Transaction.cardType is '信用卡種類'; COMMENT ON COLUMN online.Transaction.cardId is '信用卡號'; COMMENT ON COLUMN online.Transaction.dueDate is '到期日'; COMMENT ON CONSTRAINT online.Transaction.PK_Transaction is '交易表格主鍵'; COMMENT ON CONSTRAINT online.Transaction.FK_Member is '參考到會員的外部鍵'; DROP TABLE online.Product; CREATE TABLE online.Product (pNo CHAR(6) NOT NULL, pName VARCHAR(30), unitPrice DECIMAL(10,2), catalog VARCHAR(20), CONSTRAINT PK_Product PRIMARY KEY (pNo), CONSTRAINT UnitPrice_Check CHECK (unitPrice > 100)); COMMENT ON TABLE online.Product is '商品'; COMMENT ON COLUMN online.Product.pNo is '商品編號'; COMMENT ON COLUMN online.Product.pName is '商品名稱'; COMMENT ON COLUMN online.Product.unitPrice is '定價'; COMMENT ON COLUMN online.Product.catalog is '種類'; COMMENT ON CONSTRAINT online.Product.PK_Product is '商品表格主鍵'; COMMENT ON CONSTRAINT online.Product.UnitPrice_Check is '商品定價一定大於100元'; -- P.K 的欄位(name)一定是NOT NULL DROP TABLE online.Author; CREATE TABLE online.Author (pNo CHAR(6) NOT NULL, name VARCHAR(8) NOT NULL, CONSTRAINT PK_Author PRIMARY KEY (pNo, name), CONSTRAINT FK_Product FOREIGN KEY (pNo) REFERENCES online.Product(pNo)); COMMENT ON TABLE online.Author is '創作者'; COMMENT ON COLUMN online.Author.pNo is '商品編號'; COMMENT ON COLUMN online.Author.name is '創作者名稱'; COMMENT ON CONSTRAINT online.Author.PK_Author is '創作者表格主鍵'; COMMENT ON CONSTRAINT online.Author.FK_Product is '參考到商品的外部鍵'; DROP TABLE online.Browse; CREATE TABLE online.Browse (mId CHAR(8) NOT NULL WITH DEFAULT, pNo CHAR(6) NOT NULL, browseTime TIMESTAMP NOT NULL, CONSTRAINT PK_Browse PRIMARY KEY (mId, pNo, browseTime), CONSTRAINT FK_Member FOREIGN KEY (mId) REFERENCES online.Member(mId) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT FK_Product FOREIGN KEY (pNo) REFERENCES online.Product(pNo)); COMMENT ON TABLE online.Browse is '瀏覽'; COMMENT ON COLUMN online.Browse.mId is '會員編號'; COMMENT ON COLUMN online.Browse.pNo is '商品編號'; COMMENT ON COLUMN online.Browse.browseTime is '瀏覽時間'; COMMENT ON CONSTRAINT online.Browse.PK_Browse is '瀏覽表格主鍵'; COMMENT ON CONSTRAINT online.Browse.FK_Member is '參考到會員的外部鍵'; COMMENT ON CONSTRAINT online.Browse.FK_Product is '參考到商品的外部鍵'; DROP TABLE online.Cart; CREATE TABLE online.Cart (mId CHAR(8) NOT NULL, cartTime TIMESTAMP NOT NULL, tNo CHAR(5) NOT NULL, CONSTRAINT PK_Cart PRIMARY KEY (mId, cartTime), CONSTRAINT FK_Transaction FOREIGN KEY (tNo) REFERENCES online.Transaction(tNo) ON UPDATE NO ACTION, CONSTRAINT FK_Member FOREIGN KEY (mId) REFERENCES online.Member(mId) ON DELETE CASCADE ON UPDATE NO ACTION); COMMENT ON TABLE online.Cart is '購物車'; COMMENT ON COLUMN online.Cart.mId is '會員編號'; COMMENT ON COLUMN online.Cart.cartTime is '購物車產生時間'; COMMENT ON COLUMN online.Cart.tNo is '交易編號'; COMMENT ON CONSTRAINT online.Cart.PK_Cart is '購物表格主鍵'; COMMENT ON CONSTRAINT online.Cart.FK_Transaction is '參考到交易的外部鍵'; COMMENT ON CONSTRAINT online.Cart.FK_Member is '參考到會員的外部鍵'; DROP TABLE online.Order; CREATE TABLE online.Order (pNo CHAR(6) NOT NULL, mId CHAR(8) NOT NULL, cartTime TIMESTAMP NOT NULL, amount INT DEFAULT 0, CONSTRAINT PK_Order PRIMARY KEY (pNo, mId, cartTime ), CONSTRAINT FK_Product FOREIGN KEY (pNo) REFERENCES online.Product (pNo), CONSTRAINT FK_Cart FOREIGN KEY (mId, cartTime) REFERENCES online.Cart(mId, cartTime)); COMMENT ON TABLE online.Order is '訂購'; COMMENT ON COLUMN online.Order.pNo is '商品編號'; COMMENT ON COLUMN online.Order.mId is '會員編號'; COMMENT ON COLUMN online.Order.cartTime is '購物車產生時間'; COMMENT ON COLUMN online.Order.amount is '訂購數量'; COMMENT ON CONSTRAINT online.Order.PK_Order is '訂購表格主鍵'; COMMENT ON CONSTRAINT online.Order.FK_Product is '參考到商品的外部鍵'; COMMENT ON CONSTRAINT online.Order.FK_Cart is '參考到購物車的外部鍵'; CREATE TABLE online.Record (tNo CHAR(5) NOT NULL, pNo CHAR(6) NOT NULL, salePrice DECIMAL(10,2), amount INT, CONSTRAINT PK_Record PRIMARY KEY (tNo,pNo), CONSTRAINT FK_Transaction FOREIGN KEY (tNo) REFERENCES online.Transaction (tNo), CONSTRAINT FK_Product FOREIGN KEY (pNo) REFERENCES online.Product (pNo)); COMMENT ON TABLE online.Record is '記錄'; COMMENT ON COLUMN online.Record.tNo is '交易編號'; COMMENT ON COLUMN online.Record.pNo is '商品編號'; COMMENT ON COLUMN online.Record.salePrice is '售價'; COMMENT ON COLUMN online.Record.amount is '交易數量'; COMMENT ON CONSTRAINT online.Record.PK_Record is '記錄表格主鍵'; COMMENT ON CONSTRAINT online.Record.FK_Transaction is '參考到交易的外部鍵'; COMMENT ON CONSTRAINT online.Record.FK_Product is '參考到商品的外部鍵';