CREATE TABLE DEPARTMENT (
  PINDEX  INTEGER  NOT NULL,
  CITY  VARCHAR(30)  DEFAULT ''  NOT NULL  UNIQUE,
  COUNTRY  VARCHAR(50)  DEFAULT '',

  CONSTRAINT PK_DEPARTMENT  PRIMARY KEY (PINDEX)
);

CREATE TABLE EMPLOYEE (
  PINDEX  INTEGER  NOT NULL,
  FIRSTNAME  VARCHAR(50)  DEFAULT '',
  LASTNAME  VARCHAR(50)  DEFAULT '',
  MONTHLYSALARY  NUMERIC(7,2)  DEFAULT 0,
  YEARSALARY  NUMERIC(7,2)  DEFAULt 0,
  STREET  VARCHAR(50)  DEFAULT '',
  ZIPCODE  VARCHAR(5)  DEFAULT '',
  CITY  VARCHAR(50)  DEFAULT '',
  COUNTRY  VARCHAR(50)  DEFAULT '',
  MOBILE  VARCHAR(50)  DEFAULT '',
  DEPTINDEX  INTEGER  NOT NULL,

  CONSTRAINT PK_EMPLOYEE  PRIMARY KEY (PINDEX)
);

CREATE TABLE SALE (
  PINDEX  INTEGER  NOT NULL,
  SALEMONTH  INTEGER  DEFAULT 0,
  SALEYEAR  INTEGER  DEFAULT 0,
  SALE  NUMERIC(7,2)  DEFAULT 0,
  DEPTINDEX  INTEGER  NOT NULL,

  CONSTRAINT PK_SALE  PRIMARY KEY (PINDEX)
);



ALTER TABLE EMPLOYEE ADD CONSTRAINT 
   FK_DEPARTMENT_EMPLOYEE FOREIGN KEY (DEPTINDEX) REFERENCES DEPARTMENT(PINDEX);


ALTER TABLE SALE ADD CONSTRAINT 
   FK_DEPARTMENT_SALE FOREIGN KEY (DEPTINDEX) REFERENCES DEPARTMENT(PINDEX);




ALTER TABLE EMPLOYEE
     ADD CONSTRAINT CH_MONTHLYSALARY CHECK (
  MONTHLYSALARY >0
);


