CREATE TABLE 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(10),
  PRIMARY  KEY (mId),
  UNIQUE   (pId),
  FOREIGN KEY (introducer) 
  REFERENCES Member(mId) ON DELETE SET NULL
);

CREATE TABLE 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,
 PRIMARY KEY (tNo),
 FOREIGN KEY (transMid) 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 Browse
(mId CHAR(8)   DEFAULT 'a0910001' NOT NULL,
 pNo CHAR(6)   NOT NULL,
 browseTime     TIMESTAMP,     
 PRIMARY KEY (mId, pNo, browseTime),
 FOREIGN KEY (mId) REFERENCES Member(mId) ON DELETE SET NULL,
 FOREIGN KEY (pNo) REFERENCES Product (pNo));

CREATE TABLE Cart
(mId        CHAR(8)       NOT NULL,
cartTime    TIMESTAMP      NOT NULL,
tNo	    CHAR(5)    NOT NULL,
PRIMARY KEY (mId, cartTime),
FOREIGN KEY (tNo) REFERENCES Transaction(tNo),
FOREIGN KEY (mId) REFERENCES Member(mId) ON DELETE CASCADE
);


CREATE TABLE "Order"
(pNo         CHAR(6)    NOT NULL,
 mId         CHAR(8)    NOT NULL, 
 cartTime    TIMESTAMP   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 Author
(pNo   CHAR(6)     NOT NULL,
 name  VARCHAR(8),
 PRIMARY KEY (pNo, name),
 FOREIGN KEY (pNo) REFERENCES Product (pNo));

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));