-- Database: "ExemploAula"

-- DROP DATABASE "ExemploAula";
-- CREATE DATABASE "ExemploAula"
--   WITH OWNER = postgres
--        ENCODING = 'SQL_ASCII'
--        TABLESPACE = pg_default;


-- DROP TABLE aluno;
CREATE TABLE aluno
(
  id serial NOT NULL,
  nome character varying(30),
  matricula character varying(10) not null,
  CONSTRAINT pk_aluno PRIMARY KEY (id)
  CONSTRAINT aluno_matricula_unq UNIQUE (matricula)
) 
WITHOUT OIDS;
ALTER TABLE aluno OWNER TO postgres;


-- DROP TABLE disciplina;
CREATE TABLE disciplina
(
  id serial NOT NULL,
  codigo character varying(5),
  nome character varying(30),
  creditos integer NOT NULL,
  CONSTRAINT pk_disciplina PRIMARY KEY (id),
  CONSTRAINT disciplina_codigo_unq UNIQUE (codigo)
) 
WITHOUT OIDS;
ALTER TABLE disciplina OWNER TO postgres;


-- DROP TABLE "alunodisciplina";
CREATE TABLE alunodisciplina
(
  idAluno integer NOT NULL,
  idDisciplina integer NOT NULL,
  CONSTRAINT aluno_disciplina_pk PRIMARY KEY (idAluno, idDisciplina),
  CONSTRAINT fk_aluno FOREIGN KEY (idAluno) REFERENCES aluno (id),
  CONSTRAINT fk_disciplina FOREIGN KEY (idDisciplina) REFERENCES disciplina (id),
  CONSTRAINT aluno_disciplina_unq UNIQUE (idAluno, idDisciplina)
);

--- aluno
insert into aluno (id, nome, matricula) values 
(1, 'joao',  '11111' ),
(2, 'maria', '22222' ),
(3, 'jose',  '33333' );

--- disciplina
insert into disciplina (id, codigo, nome, creditos) values 
(1, 'ALG',  'Algoritmos', 6),
(2, 'FPSW', 'FrameWorks', 4);


--- alunodisciplina
insert into alunodisciplina (idAluno, idDisciplina) values 
(1, 1),
(1, 2),
(2, 1),
(3, 2);

