CREATE TABLE LGBRAND (
  BRAND_ID   NUMERIC(4,0) PRIMARY KEY,
  BRAND_NAME VARCHAR(100) NOT NULL,
  BRAND_TYPE VARCHAR(20)  
);

CREATE TABLE LGPRODUCT (
  PROD_SKU      VARCHAR(15) PRIMARY KEY,
  PROD_DESCRIPT VARCHAR(255) NOT NULL,
  PROD_TYPE     VARCHAR(255),
  PROD_BASE     VARCHAR(255),
  PROD_CATEGORY VARCHAR(255),
  PROD_PRICE    NUMERIC(10,2),
  PROD_QOH      NUMERIC(10,0),
  PROD_MIN      NUMERIC(10,0),
  BRAND_ID      NUMERIC(4,0),
  FOREIGN KEY (BRAND_ID) REFERENCES LGBRAND(BRAND_ID)   
);


CREATE TABLE LGVENDOR (
  VEND_ID     NUMERIC(6,0) PRIMARY KEY,
  VEND_NAME   VARCHAR(255) NOT NULL,
  VEND_STREET VARCHAR(50),
  VEND_CITY   VARCHAR(50),
  VEND_STATE  VARCHAR(2),
  VEND_ZIP    VARCHAR(5)   
);


CREATE TABLE LGSUPPLIES (
  PROD_SKU VARCHAR(15),
  VEND_ID  NUMERIC(6,0),
  PRIMARY KEY (PROD_SKU, VEND_ID),
  FOREIGN KEY (PROD_SKU) REFERENCES LGPRODUCT(PROD_SKU),
  FOREIGN KEY (VEND_ID) REFERENCES LGVENDOR(VEND_ID)    
);


CREATE TABLE LGDEPARTMENT (
  DEPT_NUM      NUMERIC(5,0) PRIMARY KEY,
  DEPT_NAME     VARCHAR(50) NOT NULL,
  DEPT_MAIL_BOX VARCHAR(3),
  DEPT_PHONE    VARCHAR(9),
  EMP_NUM       NUMERIC(6,0)  
);


CREATE TABLE LGEMPLOYEE (
  EMP_NUM      NUMERIC(6,0) PRIMARY KEY,
  EMP_FNAME    VARCHAR(20) NOT NULL,
  EMP_LNAME    VARCHAR(25) NOT NULL,
  EMP_EMAIL    VARCHAR(25),
  EMP_PHONE    VARCHAR(20),
  EMP_HIREDATE DATE,
  EMP_TITLE    VARCHAR(45),
  EMP_COMM     NUMERIC(2,2),
  DEPT_NUM     NUMERIC(5,0),
  FOREIGN KEY (DEPT_NUM) REFERENCES LGDEPARTMENT(DEPT_NUM)  
);


CREATE TABLE LGSALARY_HISTORY (
  EMP_NUM    NUMERIC(6,0),
  SAL_FROM   DATE,
  SAL_END    DATE,
  SAL_AMOUNT NUMERIC(10,2),
  PRIMARY KEY (EMP_NUM, SAL_FROM),
  FOREIGN KEY (EMP_NUM) REFERENCES LGEMPLOYEE (EMP_NUM) 
);


CREATE TABLE LGCUSTOMER (
  CUST_CODE    NUMERIC(38,0) PRIMARY KEY,
  CUST_FNAME   VARCHAR(20),
  CUST_LNAME   VARCHAR(20),
  CUST_STREET  VARCHAR(70),
  CUST_CITY    VARCHAR(50),
  CUST_STATE   CHAR(2),
  CUST_ZIP     CHAR(5),
  CUST_BALANCE NUMERIC(8,2)  
);


CREATE TABLE LGINVOICE (
  INV_NUM     NUMERIC(38,0) PRIMARY KEY,
  INV_DATE    DATE,
  CUST_CODE   NUMERIC(38,0),
  INV_TOTAL   NUMERIC(11,2),
  EMPLOYEE_ID NUMERIC(6,0),
  FOREIGN KEY(CUST_CODE) REFERENCES LGCUSTOMER(CUST_CODE),
  FOREIGN KEY(EMPLOYEE_ID) REFERENCES LGEMPLOYEE(EMP_NUM)       
);


CREATE TABLE LGLINE (
  INV_NUM    NUMERIC(38,0),
  LINE_NUM   NUMERIC,
  PROD_SKU   VARCHAR(15),
  LINE_QTY   NUMERIC,
  LINE_PRICE NUMERIC(8,2),
  PRIMARY KEY(INV_NUM, LINE_NUM),
  FOREIGN KEY(INV_NUM) REFERENCES LGINVOICE(INV_NUM),
  FOREIGN KEY(PROD_SKU) REFERENCES LGPRODUCT(PROD_SKU)  
);


/*LGDEPARTMENT*/
INSERT INTO LGDEPARTMENT VALUES (200, 'SALES', '475', '555-2824', 83509);
INSERT INTO LGDEPARTMENT VALUES (300, 'PURCHASING', '222', '555-4873', 83746);
INSERT INTO LGDEPARTMENT VALUES (250, 'CUSTOMER SERVICE', '100', '555-5555', 84001);
INSERT INTO LGDEPARTMENT VALUES (500, 'DISTRIBUTION', '348', '555-3624', 84052);
INSERT INTO LGDEPARTMENT VALUES (400, 'WAREHOUSE', '789', '555-1003', 83759);
INSERT INTO LGDEPARTMENT VALUES (600, 'ACCOUNTING', '957', '555-2333', 84583);
INSERT INTO LGDEPARTMENT VALUES (550, 'TRUCKING', '842', '555-0057', 83683);
INSERT INTO LGDEPARTMENT VALUES (280, 'MARKETING', '848', '555-8500', 84042);