/* Table: MERCHANT, Owner: MISDBA */
CREATE TABLE MERCHANT (ID CHAR(6) NOT NULL,
        NAME VARCHAR(20) NOT NULL,
        SATRAP VARCHAR(10),
        INTRO VARCHAR(400),
CONSTRAINT MERCHANT_ID PRIMARY KEY (ID));

/* Table: PARTS, Owner: MISDBA */
CREATE TABLE PARTS (ID CHAR(10) NOT NULL,
        NAME VARCHAR(20) NOT NULL,
        PART_CHAR VARCHAR(400),
        AMOUNT INTEGER,
CONSTRAINT PARTS_ID PRIMARY KEY (ID));

/* Table: IM_EXPORT, Owner: MISDBA */
CREATE TABLE IM_EXPORT (ID CHAR(10) NOT NULL,
        IM_EX CHAR(1) NOT NULL,
        PERSON VARCHAR(10) NOT NULL,
        ACT_DATE TIMESTAMP NOT NULL,
        REMARK VARCHAR(400),
CONSTRAINT IM_EXPORT_ID PRIMARY KEY (ID));

/* Table: PORT_ITEMS, Owner: MISDBA */
CREATE TABLE PORT_ITEMS (ID INTEGER NOT NULL,
        PORT_ID CHAR(10) NOT NULL,
        PARTS CHAR(10) NOT NULL,
        AMOUNT INTEGER NOT NULL,
CONSTRAINT PORT_ITEMS_ID PRIMARY KEY (ID));

/* Table: BUY_SALES, Owner: MISDBA */
CREATE TABLE BUY_SALES (ID CHAR(10) NOT NULL,
        BUY_SALE CHAR(1) NOT NULL,
        MERCHANT CHAR(6) NOT NULL,
        STATE CHAR(1) NOT NULL,
        START_DATE TIMESTAMP,
        SEND_DATE TIMESTAMP,
        END_DATE TIMESTAMP,
        PRICES INTEGER,
CONSTRAINT BUY_SALES_ID PRIMARY KEY (ID));

/* Table: CONTACT_ITEMS, Owner: MISDBA */
CREATE TABLE CONTACT_ITEMS (ID INTEGER NOT NULL,
        CONTACT_ID CHAR(10) NOT NULL,
        PARTS CHAR(10) NOT NULL,
        UNIT_PRICE FLOAT,
        AMOUNT INTEGER,
CONSTRAINT CONTACT_ITEMS_ID PRIMARY KEY (ID));

/* Table: CONTACT_STATE, Owner: MISDBA */
CREATE TABLE CONTACT_STATE (CODE CHAR(1) NOT NULL,
        DESCRIP VARCHAR(20) NOT NULL,
CONSTRAINT CONTACT_STATE_ID PRIMARY KEY (CODE));

/*  CheckConstraint IM_EX_CHECK  */
ALTER TABLE IM_EXPORT
  ADD CONSTRAINT IM_EX_CHECK
  CHECK (IM_EX='I' OR IM_EX='O');
/*  ForeignKey PORT_ID  */
ALTER TABLE PORT_ITEMS
  ADD CONSTRAINT PORT_ID 
  FOREIGN KEY (PORT_ID) 
  REFERENCES IM_EXPORT;
/*  ForeignKey PORT_PARTS  */
ALTER TABLE PORT_ITEMS
  ADD CONSTRAINT PORT_PARTS 
  FOREIGN KEY (PARTS) 
  REFERENCES PARTS;
/*  CheckConstraint BUY_SALE_CHECK  */
ALTER TABLE BUY_SALES
  ADD CONSTRAINT BUY_SALE_CHECK
  CHECK (BUY_SALE='B' OR BUY_SALE='S');
/*  ForeignKey CONTACT_STATE  */
ALTER TABLE BUY_SALES
  ADD CONSTRAINT CONTACT_STATE 
  FOREIGN KEY (STATE) 
  REFERENCES CONTACT_STATE;
/*  ForeignKey MERCHANT  */
ALTER TABLE BUY_SALES
  ADD CONSTRAINT MERCHANT 
  FOREIGN KEY (MERCHANT) 
  REFERENCES MERCHANT;
/*  ForeignKey CONTACT_ID  */
ALTER TABLE CONTACT_ITEMS
  ADD CONSTRAINT CONTACT_ID 
  FOREIGN KEY (CONTACT_ID) 
  REFERENCES BUY_SALES;
/*  ForeignKey CONTACT_PARTS  */
ALTER TABLE CONTACT_ITEMS
  ADD CONSTRAINT CONTACT_PARTS 
  FOREIGN KEY (PARTS) 
  REFERENCES PARTS;

/*權限分配*/
GRANT SELECT ON PERSON TO LOGIN;
GRANT ALL ON MERCHANT TO MATER;
GRANT ALL ON PARTS TO MATER;
GRANT ALL ON IM_EXPORT TO MATER;
GRANT ALL ON PORT_ITEMS TO MATER;
GRANT ALL ON BUY_SALES TO MATER;
GRANT ALL ON CONTACT_ITEMS TO MATER;
GRANT SELECT ON CONTACT_STATE TO MATER;
GRANT SELECT, UPDATE ON COUNTER TO MATER;