Untitled

CREATE TABLE warehouse (
    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 REFERENCES supplier(sid),
    pid INTEGER REFERENCES parts(pid),
    stock INTEGER NOT NULL,
    PRIMARY KEY(sid, 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 REFERENCES parts(pid),
    uid INTEGER REFERENCES users(uid),
    wid INTEGER REFERENCES warehouses(wid)
);

CREATE TABLE outgoing_transaction(
    otid SERIAL PRIMARY KEY,
    unit_sale_price DOUBLE PRECISION NOT NULL,
    cid INTEGER REFERENCES customer(cid),
    tid INTEGER REFERENCES transactions(tid)
);

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

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