Untitled

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

CREATE TABLE supplier (
    sid SERIAL PRIMARY KEY,
    sfname VARCHAR(255) NOT NULL,
    slname VARCHAR(255) NOT NULL,
    scountry VARCHAR(255) NOT NULL,
    scity VARCHAR(255) NOT NULL,
    sstreet VARCHAR(255) NOT NULL,
    szipcode VARCHAR(255) NOT NULL,
    sphone VARCHAR(255) NOT NULL
);

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

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

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

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

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

CREATE TABLE transactions(
    tid SERIAL PRIMARY KEY,
    tdate DATE NOT NULL,
    part_amount INTEGER NOT NULL,
    pid  INTEGER NOT NULL,
    uid INTEGER NOT NULL,
    wid INTEGER NOT NULL,
    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 NOT NULL,
    cid INTEGER NOT NULL,
    tid INTEGER NOT NULL,
    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 NOT NULL,
    sid INTEGER NOT NULL,
    rid INTEGER NOT NULL,
    tid INTEGER NOT NULL,
    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 NOT NULL ,
    user_requester INTEGER NOT NULL,
    tid INTEGER NOT NULL,
    FOREIGN KEY (to_warehouse) REFERENCES warehouses(wid),
    FOREIGN KEY (user_requester) REFERENCES users(uid),
    FOREIGN KEY (tid) REFERENCES transactions(tid)
);