Scripts - academico-db.txt

CREATE DATABASE "academico-db"
    WITH 
    OWNER = postgres
    ENCODING = 'UTF8'
    LC_COLLATE = 'Portuguese_Brazil.1252'
    LC_CTYPE = 'Portuguese_Brazil.1252'
    TABLESPACE = pg_default
    CONNECTION LIMIT = -1;
	
CREATE SEQUENCE public.enderecos_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 1;

ALTER SEQUENCE public.enderecos_id_seq
    OWNER TO postgres;	
	

CREATE SEQUENCE public.notas_id_seq
    INCREMENT 1
    START 1
    MINVALUE 1
    MAXVALUE 2147483647
    CACHE 1;

ALTER SEQUENCE public.notas_id_seq
    OWNER TO postgres;	
	

CREATE TABLE public.enderecos
(
    id integer NOT NULL DEFAULT nextval('enderecos_id_seq'::regclass),
    cep integer NOT NULL,
    rua character varying COLLATE pg_catalog."default" NOT NULL,
    bairro character varying COLLATE pg_catalog."default" NOT NULL,
    cidade character varying COLLATE pg_catalog."default" NOT NULL,
    estado character varying COLLATE pg_catalog."default",
    CONSTRAINT enderecos_pkey PRIMARY KEY (id)
)

TABLESPACE pg_default;

ALTER TABLE public.enderecos
    OWNER to postgres;	
	
CREATE TABLE public.professores
(
    matricula integer NOT NULL,
    nome character varying COLLATE pg_catalog."default" NOT NULL,
    sobrenome character varying COLLATE pg_catalog."default" NOT NULL,
    idade integer NOT NULL,
    naturalidade character varying COLLATE pg_catalog."default" NOT NULL,
    sexo "char" NOT NULL,
    cpf character varying COLLATE pg_catalog."default" NOT NULL,
    salario money NOT NULL,
    carga_horaria integer NOT NULL,
    id_endereco integer NOT NULL,
    CONSTRAINT professores_pkey PRIMARY KEY (matricula),
    CONSTRAINT id_endereco_fkey FOREIGN KEY (id_endereco)
        REFERENCES public.enderecos (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE public.professores
    OWNER to postgres;
-- Index: fki_id_endereco_fkey

-- DROP INDEX public.fki_id_endereco_fkey;

CREATE INDEX fki_id_endereco_fkey
    ON public.professores USING btree
    (id_endereco ASC NULLS LAST)
    TABLESPACE pg_default;	
	
CREATE TABLE public.alunos
(
    matricula integer NOT NULL,
    nome character varying COLLATE pg_catalog."default" NOT NULL,
    sobrenome character varying COLLATE pg_catalog."default" NOT NULL,
    idade integer NOT NULL,
    naturalidade character varying COLLATE pg_catalog."default" NOT NULL,
    sexo "char" NOT NULL,
    cpf character varying COLLATE pg_catalog."default" NOT NULL,
    curso character varying COLLATE pg_catalog."default" NOT NULL,
    matriculado boolean NOT NULL,
    aprovado boolean NOT NULL,
    id_endereco integer NOT NULL,
    CONSTRAINT alunos_pkey PRIMARY KEY (matricula),
    CONSTRAINT id_endereco_fkey FOREIGN KEY (id_endereco)
        REFERENCES public.enderecos (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE public.alunos
    OWNER to postgres;
-- Index: fki_id_endereco_alunos_fkey

-- DROP INDEX public.fki_id_endereco_alunos_fkey;

CREATE INDEX fki_id_endereco_alunos_fkey
    ON public.alunos USING btree
    (id_endereco ASC NULLS LAST)
    TABLESPACE pg_default;
	
CREATE TABLE public.notas
(
    id integer NOT NULL DEFAULT nextval('notas_id_seq'::regclass),
    valor real NOT NULL,
    peso integer NOT NULL,
    matricula_aluno integer NOT NULL,
    CONSTRAINT notas_pkey PRIMARY KEY (id),
    CONSTRAINT matricula_aluno_fkey FOREIGN KEY (matricula_aluno)
        REFERENCES public.alunos (matricula) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE public.notas
    OWNER to postgres;
-- Index: fki_matricula_aluno_fkey

-- DROP INDEX public.fki_matricula_aluno_fkey;

CREATE INDEX fki_matricula_aluno_fkey
    ON public.notas USING btree
    (matricula_aluno ASC NULLS LAST)
    TABLESPACE pg_default;