Untitled

CREATE TABLE warehouses(
  wid SERIAL PRIMARY KEY,
  wname VARCHAR(255),
  wcountry VARCHAR(255),
  wregion VARCHAR(255),
  wcity VARCHAR(255),
  wstreet VARCHAR(255),
  wzipcode VARCHAR(255),
  wbudget DOUBLE PRECISION,
  racks_amt INTEGER
);

CREATE TABLE supplier (
  sid SERIAL PRIMARY KEY,
  sfirstname VARCHAR(255),
  slastname VARCHAR(255),
  scountry VARCHAR(255),
  scity VARCHAR(255),
  sstreet VARCHAR(255),
  szipcode VARCHAR(255),
  sphone VARCHAR(255)
);

CREATE TABLE racks (
  rid SERIAL PRIMARY KEY,
  rname VARCHAR(255),
  rcapacity VARCHAR(255),
  wid INTEGER REFERENCES warehouses(wid)
);

CREATE TABLE parts(
  pid SERIAL PRIMARY KEY,
  pname VARCHAR(255),
  pcolor VARCHAR(255),
  pmaterial VARCHAR(255),
  MSRP DOUBLE PRECISION,
  rid INTEGER REFERENCES racks(rid)
);

CREATE TABLE supplies (
  sid INTEGER,
  pid INTEGER,
  stock INTEGER,
  FOREIGN KEY (sid) REFERENCES supplier(sid),
  FOREIGN KEY (pid) REFERENCES parts(pid)
);

CREATE TABLE customer(
  cid SERIAL PRIMARY KEY,
  cfname VARCHAR(255),
  clname VARCHAR(255),
  czipcode VARCHAR(255),
  cphone VARCHAR(255)
);

CREATE TABLE users(
  uid SERIAL PRIMARY KEY,
  ufname VARCHAR(255),
  ulname VARCHAR(255),
  username VARCHAR(255),
  uemail VARCHAR(255),
  upassword VARCHAR(255),
  wid INTEGER REFERENCES warehouses(wid)
);

CREATE TABLE transactions(
  tid SERIAL PRIMARY KEY,
  tdate DATE,
  part_amount INTEGER,
  pid INTEGER,
  uid INTEGER,
  wid INTEGER,
  FOREIGN KEY (pid) REFERENCES parts(pid),
  FOREIGN KEY (uid) REFERENCES users(uid),
  FOREIGN KEY (wid) REFERENCES warehouses(wid)
);

CREATE TABLE Outgoing_Transaction(
  otid SERIAL PRIMARY KEY,
  unit_sale_price DOUBLE PRECISION,
  cid INTEGER,
  tid INTEGER,
  FOREIGN KEY (cid) REFERENCES customer(cid),
  FOREIGN KEY (tid) REFERENCES transactions(tid)
);

CREATE TABLE Incoming_Transaction(
  itid SERIAL PRIMARY KEY,
  unit_buy_price DOUBLE PRECISION,
  sid INTEGER,
  rid INTEGER,
  tid INTEGER,
  FOREIGN KEY (sid) REFERENCES supplier(sid),
  FOREIGN KEY (rid) REFERENCES racks(rid),
  FOREIGN KEY (tid) REFERENCES transactions(tid)
);

CREATE TABLE transfer(
  transferid SERIAL PRIMARY KEY,
  to_warehouse INTEGER,
  user_requester INTEGER,
  tid INTEGER,
  FOREIGN KEY (to_warehouse) REFERENCES warehouses(wid),
  FOREIGN KEY (user_requester) REFERENCES users(uid),
  FOREIGN KEY (tid) REFERENCES transactions(tid)
);