CREATE TABLE Warehouse
(
  ID VARCHAR(32) NOT NULL,
  location VARCHAR(32) NOT NULL,
  postcode INT NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE Product
(
  ID VARCHAR(32) NOT NULL,
  NUMBER INT NOT NULL,
  description CHAR(50) NOT NULL,
  price NUMERIC(10) NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE Stock
(
  ID VARCHAR(32) NOT NULL,
  quantity INT NOT NULL,
  warehouseID VARCHAR(32) NOT NULL,
  productID VARCHAR(32) NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (warehouseID) REFERENCES Warehouse(ID),
  FOREIGN KEY (productID) REFERENCES Product(ID)
);

CREATE TABLE Customer
(
  ID VARCHAR(32) NOT NULL,
  name CHAR(50) NOT NULL,
  address VARCHAR(50) NOT NULL,
  postcode INT NOT NULL,
  PRIMARY KEY (ID)
);

CREATE TABLE CustomerOrder
(
  ID VARCHAR(32) NOT NULL,
  NUMBER NUMERIC(10) NOT NULL,
  DATE DATE NOT NULL,
  customerID VARCHAR(32) NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (customerID) REFERENCES Customer(ID)
);

CREATE TABLE Delivery
(
  ID VARCHAR(32) NOT NULL,
  NUMBER NUMERIC(10) NOT NULL,
  DATE DATE NOT NULL,
  customerID VARCHAR(32) NOT NULL,
  warehouseID VARCHAR(32) NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (customerID) REFERENCES Customer(ID),
  FOREIGN KEY (warehouseID) REFERENCES Warehouse(ID)
);

CREATE TABLE OrderLine
(
  ID VARCHAR(32) NOT NULL,
  quantity INT NOT NULL,
  customerOrderID VARCHAR(32) NOT NULL,
  productID VARCHAR(32) NOT NULL,
  deliveryID VARCHAR(32) NOT NULL,
  PRIMARY KEY (ID),
  FOREIGN KEY (customerOrderID) REFERENCES Product(ID),
  FOREIGN KEY (productID) REFERENCES CustomerOrder(ID),
  FOREIGN KEY (deliveryID) REFERENCES Delivery(ID)
);