CREATE TABLE INVOICE (
    INV_NUM     CHAR    (9)     NOT NULL,
    INV_DATE    DATE,
    SALES_NUM   CHAR    (2)     NOT NULL,
    CUST_NUM    CHAR    (3)     NOT NULL,

    CONSTRAINT INVOICE_INV_NUM_PK
    PRIMARY KEY (INV_NUM)
);


CREATE TABLE INVOICE_EQUIPMENT (
    INV_NUM   CHAR    (9)   NOT NULL,
    EQP_NUM   CHAR    (6)   NOT NULL,
    QTY       DECIMAL (5)   NOT NULL,
    CHARGE    DECIMAL (8,2)   NOT NULL,

    CONSTRAINT INVOICE_EQUIPMENT_INV_NUM_EQP_NUM_PK
    PRIMARY KEY (INV_NUM, EQP_NUM)
);


CREATE TABLE EQUIP_CLASS (
    EQP_CLASS_CODE  CHAR    (3)     NOT NULL,
    EQP_CLASS_DESC  CHAR    (20)    NOT NULL,

    CONSTRAINT EQUIP_CLASS_EQP_CLASS_CODE_PK
    PRIMARY KEY (EQP_CLASS_CODE)
);


CREATE TABLE SALESPERSON (
    SALES_NUM   CHAR  (2)  NOT NULL,
    SALES_NAME  CHAR  (15) NOT NULL WITH DEFAULT,

    CONSTRAINT SALESPERSON_SALES_NUM_PK
    PRIMARY KEY (SALES_NUM)
);


CREATE TABLE EQUIPMENT (
    EQP_NUM         CHAR  (6)  NOT NULL,
    EQP_DESC        CHAR  (30) NOT NULL,
    EQP_CLASS_CODE  CHAR  (3) NOT NULL,

    CONSTRAINT EQUIPMENT_EQP_NUM_PK
    PRIMARY KEY (EQP_NUM)
);


CREATE TABLE CUSTOMER (
    CUST_NUM         CHAR    (3)   NOT NULL,
    CLNAME           CHAR    (15)  NOT NULL,
    CFNAME           CHAR    (15)  NOT NULL,
    CUST_ADDRESS     CHAR    (30),
    CUST_PHONE       CHAR    (13),
    FEDERAL_GST_NUM  DECIMAL (9)   NOT NULL,
    SALES_NUM        CHAR    (2)   NOT NULL,

    CONSTRAINT CUSTOMER_CUST_NUM_PK
    PRIMARY KEY(CUST_NUM),
    CONSTRAINT CUSTOMER_FEDERAL_GST_NUM_UN
    UNIQUE (FEDERAL_GST_NUM),
    CONSTRAINT CUSTOMER_FEDERAL_GST_NUM_CK
    CHECK (FEDERAL_GST_NUM BETWEEN 100000000 AND 999999999)
);


ALTER TABLE INVOICE
ADD CONSTRAINT I_SALES_NUM_FK
FOREIGN KEY (SALES_NUM)
REFERENCES SALESPERSON(SALES_NUM)
ADD CONSTRAINT CUST_NUM_FK
FOREIGN KEY (CUST_NUM)
REFERENCES CUSTOMER(CUST_NUM)
;


ALTER TABLE INVOICE_EQUIPMENT
ADD CONSTRAINT INV_NUM_FK
FOREIGN KEY (INV_NUM)
REFERENCES INVOICE(INV_NUM)
ADD CONSTRAINT EQP_NUM_FK
FOREIGN KEY (EQP_NUM)
REFERENCES EQUIPMENT(EQP_NUM)
;

ALTER TABLE EQUIPMENT
ADD CONSTRAINT EQP_CLASS_CODE_FK
FOREIGN KEY (EQP_CLASS_CODE)
REFERENCES EQUIP_CLASS(EQP_CLASS_CODE)
;

ALTER TABLE CUSTOMER
ADD CONSTRAINT C_SALES_NUM_FK
FOREIGN KEY (SALES_NUM)
REFERENCES SALESPERSON(SALES_NUM)
;