TaskProject/tablecreation.sql

CREATE TABLE Student (
    sid SERIAL PRIMARY KEY,
    sname VARCHAR(255),
    major VARCHAR(255),
    level VARCHAR(255),
    byear INTEGER
);

CREATE TABLE Faculty (
    fid SERIAL PRIMARY KEY,
    fname VARCHAR(255),
    department VARCHAR(255)
);

CREATE TABLE Project (
    pid SERIAL PRIMARY KEY,
    pname VARCHAR(255),
    sdate DATE,
    edate DATE,
    pi INTEGER,
    copi INTEGER
);

CREATE TABLE Assigned (
    sid INTEGER,
    pid INTEGER,
    sdate DATE,
    edate DATE,
    FOREIGN KEY (sid) REFERENCES Student(sid),
    FOREIGN KEY (pid) REFERENCES Project(pid),
    PRIMARY KEY (sid, pid)
);
-- Table: public.users

-- DROP TABLE IF EXISTS public.users;

CREATE TABLE IF NOT EXISTS public.users
(
    id integer NOT NULL DEFAULT nextval('users_id_seq'::regclass),
    username character varying(50) COLLATE pg_catalog."default" NOT NULL,
    password_hash character varying(100) COLLATE pg_catalog."default" NOT NULL,
    email character varying(100) COLLATE pg_catalog."default",
    created_at timestamp without time zone DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_username_key UNIQUE (username)
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public.users
    OWNER to postgres;

REVOKE ALL ON TABLE public.users FROM aaron;

GRANT INSERT, SELECT ON TABLE public.users TO aaron;

GRANT ALL ON TABLE public.users TO postgres;