CREATE TABLE Member
		(mId           CHAR(8)          NOT NULL,
		 pId           CHAR(10)         NOT NULL,
		 name          VARCHAR(8)       NOT NULL,
		 birthday      DATETIME,
		 phone         VARCHAR(10),
		 address       VARCHAR(40),
		 email         VARCHAR(20),
		 introducer    CHAR(8),
		PRIMARY  KEY (mId),
		UNIQUE   (pId),
		FOREIGN KEY (introducer) REFERENCES Member(mId));

CREATE TABLE Product
		(pNo           CHAR(6)          NOT NULL,
		 pName         VARCHAR(30),
		 unitPrice     DECIMAL(10,2),
		 catalog       VARCHAR(20),
		PRIMARY KEY (pNo ),
		CONSTRAINT UnitPrice_Check CHECK (unitPrice > 100));

CREATE TABLE Author
		(pNo           CHAR(6)          NOT NULL,
		 name          VARCHAR(8),
		PRIMARY KEY (pNo, name ),
		FOREIGN KEY (pNo) REFERENCES Product (pNo));

/* transTime原為TIMESTAMP，會遇到資料無法新增，先改成DATETIME最後再手動改回來。 */
CREATE TABLE "Transaction"
		(tNo           CHAR(5)          NOT NULL,
		 transMid      CHAR(8)          NOT NULL,
		 transTime     DATETIME         NOT NULL,
		 method        VARCHAR(5)       NOT NULL,
		 bankId        VARCHAR(14)      NOT NULL,
		 bankName      VARCHAR(20),
		 cardType      VARCHAR(10),
		 cardId        VARCHAR(10),
		 dueDate       DATETIME,
		PRIMARY KEY (tNo),
		FOREIGN KEY (transMid) REFERENCES Member(mId));

/* cartTime原為TIMESTAMP，會遇到資料無法新增，先改成DATETIME最後再手動改回來。 */
CREATE TABLE Cart
		(mId           CHAR(8)          NOT NULL,
		 cartTime      DATETIME         NOT NULL,
		 tNo           CHAR(5)          NOT NULL,
		PRIMARY KEY (mId, cartTime),
		FOREIGN KEY (tNo) REFERENCES "Transaction"(tNo)
		ON UPDATE CASCADE,
		FOREIGN KEY (mId) REFERENCES Member(mId)
		ON DELETE CASCADE  ON UPDATE CASCADE);

/* browseTime原為TIMESTAMP，會遇到資料無法新增，先改成DATETIME最後再手動改回來。 */
CREATE TABLE "Browse"
		(mId           CHAR(8)          NOT NULL DEFAULT  'a0910001',
		 pNo           CHAR(6)          NOT NULL,
		 browseTime    DATETIME,     
		PRIMARY KEY (mId, pNo, browseTime),
		FOREIGN KEY (mId) REFERENCES Member(mId)  ON UPDATE CASCADE,
		FOREIGN KEY (pNo) REFERENCES Product (pNo));

/* cartTime原為TIMESTAMP，會遇到資料無法新增，先改成DATETIME最後再手動改回來。 */
CREATE TABLE "Order"
		(pNo           CHAR(6)          NOT NULL,
		 mId           CHAR(8)          NOT NULL,
		 cartTime      DATETIME         NOT NULL,
		 amount        INT                       DEFAULT  0,
		PRIMARY KEY (pNo ,mId, cartTime ),
		FOREIGN KEY (pNo) REFERENCES Product (pNo),
		FOREIGN KEY (mId, cartTime) REFERENCES Cart (mId, cartTime));

CREATE TABLE Record
		(tNo           CHAR(5)          NOT NULL,
		 pNo           CHAR(6)          NOT NULL,
		 salePrice     DECIMAL(10,2),
		 amount        INT,
		PRIMARY KEY (tNo,pNo),
		FOREIGN KEY (tNo) REFERENCES "Transaction" (tNo),
		FOREIGN KEY (pNo) REFERENCES Product (pNo));