--1. Crear la tabla "qa_posts" con los campos "ca_id" (integer, not null, primary key), "tags" (character varing (800), not null) y counted (boolean, not null, default false)

CREATE TABLE qa_posts (
    ca_id integer NOT NULL,
    tags character varying(800) NOT NULL,
    counted boolean DEFAULT false NOT NULL
);

ALTER TABLE ONLY qa_posts
    ADD CONSTRAINT qa_posts_pkey PRIMARY KEY (ca_id);

ALTER TABLE ONLY qa_posts
    ADD CONSTRAINT qa_posts_ca_id_fkey FOREIGN KEY (ca_id) REFERENCES categorys(ca_id);

--3. Crear la tabla "qa_words" con los campos "wordid" (serial, not null, primary key), "word" (character varying (80), not null) y tagcount (integer, not null, default 0)

CREATE TABLE qa_words (
    wordid integer NOT NULL,
    word character varying(80) NOT NULL,
    tagcount integer DEFAULT 0 NOT NULL
);

CREATE SEQUENCE qa_words_wordid_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER SEQUENCE qa_words_wordid_seq OWNED BY qa_words.wordid;

ALTER TABLE ONLY qa_words ALTER COLUMN wordid SET DEFAULT nextval('qa_words_wordid_seq'::regclass);

ALTER TABLE ONLY qa_words
    ADD CONSTRAINT qa_words_pkey PRIMARY KEY (wordid);
	
--5. Crear la tabla "words_sets" con los campos "wordid" (integer, not null, primary key) y "fs_id" (integer, not null, primary key)

CREATE TABLE words_sets (
    wordid integer NOT NULL,
    fs_id integer NOT NULL
);

ALTER TABLE ONLY words_sets
    ADD CONSTRAINT words_sets_pkey PRIMARY KEY (wordid, fs_id);
	
ALTER TABLE ONLY words_sets
    ADD CONSTRAINT words_sets_wordid_fkey FOREIGN KEY (wordid) REFERENCES qa_words(wordid);

--7. Crear la tabla "subsets" con los campos "subset" (integer, not null, primary key) y "superset" (integer, not null, primary key)

CREATE TABLE subsets (
    subset integer NOT NULL,
    superset integer NOT NULL
);

ALTER TABLE ONLY subsets
    ADD CONSTRAINT subsets_pkey PRIMARY KEY (subset, superset);