---------------------------------
-- Created by Mohit Singh
-- Tested on Oracle 11g XE
-- Following are the Entities : -
-- 1. ADDRESS
-- 2. CUSTOMER
-- 3. EMPLOY
-- 4. SHIP
-- 5. ITEM
-- 6. BILL
-- 7. INVOICE
-- 8. TRANSACTION
----------------------------------

-- ADDRESS TABLE

CREATE TABLE ADDRESS (
  ADDRESS_ID VARCHAR2(20) PRIMARY KEY,
  ADDRESS VARCHAR2(50) NOT NULL,
  CITY VARCHAR2(20) NOT NULL,
  PHONE VARCHAR2(10) NOT NULL,
  ADDRESS_TYPE VARCHAR2(8) NOT NULL,
  CHECK (ADDRESS_TYPE IN ('CUSTOMER', 'EMPLOY', 'SHIP')),
  UNIQUE (ADDRESS_ID,ADDRESS_TYPE)
);

-- CUSTOMER TABLE

CREATE TABLE CUSTOMER (
  CUSTOMER_ID VARCHAR2(20) PRIMARY KEY,
  CUSTOMER_NAME VARCHAR2(50) NOT NULL,
  ADDRESS_TYPE VARCHAR2(8) DEFAULT 'CUSTOMER' NOT NULL,
  CHECK (ADDRESS_TYPE =  'CUSTOMER'),
  CONSTRAINT BOOK_ADDRESS_FK FOREIGN KEY (CUSTOMER_ID,ADDRESS_TYPE) REFERENCES ADDRESS(ADDRESS_ID,ADDRESS_TYPE)
);

-- EMPLOY TABLE

CREATE TABLE EMPLOY (
  EMPLOY_ID VARCHAR2(20) PRIMARY KEY,
  EMPLOY_NAME VARCHAR2(50) NOT NULL,
  COMPANY_NAME VARCHAR2(50) NOT NULL,
  ADDRESS_TYPE VARCHAR2(8) DEFAULT 'EMPLOY' NOT NULL,
  CHECK (ADDRESS_TYPE =  'EMPLOY'),
  CONSTRAINT EMPLOY_ADDRESS_FK FOREIGN KEY (EMPLOY_ID,ADDRESS_TYPE) REFERENCES ADDRESS(ADDRESS_ID,ADDRESS_TYPE)
);

-- SHIP TABLE

CREATE TABLE SHIP (
  SHIP_ID VARCHAR2(20) PRIMARY KEY,
  SHIP_DATE DATE NOT NULL,
  SHIP_VIA VARCHAR2(20) NOT NULL,
  TERMS VARCHAR2(50) NOT NULL,
  ADDRESS_TYPE VARCHAR2(8) DEFAULT 'SHIP' NOT NULL,
  CHECK (ADDRESS_TYPE =  'SHIP'),
  CONSTRAINT SHIP_ADDRESS_FK FOREIGN KEY (SHIP_ID,ADDRESS_TYPE) REFERENCES ADDRESS(ADDRESS_ID,ADDRESS_TYPE)
);

-- ITEM TABLE

CREATE TABLE ITEM (
  ITEM_ID VARCHAR2(20) PRIMARY KEY,
  DESCRIPTION VARCHAR2(50) NOT NULL,
  PRICE NUMBER NOT NULL,
  STOCK NUMBER NOT NULL
);

-- INVOICE TABLE

CREATE TABLE INVOICE (
  INVOICE_ID VARCHAR2(20) PRIMARY KEY,
  CUSTOMER_ID VARCHAR2(20) NOT NULL,
  EMPLOY_ID VARCHAR2(20) NOT NULL,
  INVOICE_DATE DATE NOT NULL,
  CONSTRAINT INVOICE_CUSTOMER_FK FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID),
  CONSTRAINT INVOICE_EMPLOY_FK FOREIGN KEY (EMPLOY_ID) REFERENCES EMPLOY(EMPLOY_ID)
);

-- BILL TABLE

CREATE TABLE BILL (
  BILL_ID VARCHAR2(20) PRIMARY KEY,
  INVOICE_ID VARCHAR2(20) NOT NULL,
  SHIP_ID VARCHAR2(20) NOT NULL,
  BILL_DATE DATE NOT NULL,
  CONSTRAINT BILL_INVOICE_FK FOREIGN KEY (INVOICE_ID) REFERENCES INVOICE(INVOICE_ID),
  CONSTRAINT BILL_SHIP_FK FOREIGN KEY (SHIP_ID) REFERENCES SHIP(SHIP_ID)
);

-- TRANSACTION TABLE

CREATE TABLE TRANSACTION (
  TRANSCATION_ID VARCHAR2(20) PRIMARY KEY,
  BILL_ID VARCHAR2(20) NOT NULL,
  ITEM_ID VARCHAR2(20) NOT NULL,
  PRICE NUMBER NOT NULL,
  CONSTRAINT TRANSACTION_BILL_FK FOREIGN KEY (BILL_ID) REFERENCES BILL(BILL_ID),
  CONSTRAINT TRANSACTION_ITEM_FK FOREIGN KEY (ITEM_ID) REFERENCES ITEM(ITEM_ID)
)