sql_livros_postgres

SET SEARCH_PATH TO livros;

DROP TABLE IF EXISTS AUTORIA CASCADE;
DROP TABLE IF EXISTS LIVRO CASCADE;
DROP TABLE IF EXISTS TIPO CASCADE;
DROP TABLE IF EXISTS AUTOR CASCADE;
DROP TABLE IF EXISTS EDITORA CASCADE;
DROP TABLE IF EXISTS CIDADE CASCADE;

/* Cidade (codCid, nome, UF) */
CREATE TABLE cidade (
  codCid INT,
  nome VARCHAR(60),
  UF CHAR(2),

  CONSTRAINT pk_cidade PRIMARY KEY (codCid)
);

/* Editora (codEd, nome, endereco, codCid)
    codCid referencia Cidade (codCid) */
CREATE TABLE editora (
  codEd INT,
  nome VARCHAR(60),
  endereco VARCHAR(100),
  codCid INT,

  CONSTRAINT pk_editora PRIMARY KEY (codEd),
  CONSTRAINT fk_editora_cidade FOREIGN KEY (codCid) REFERENCES Cidade (codCid)
);

/* Autor (codAutor, nome, email, dataNasc, codCid)
    codCid referencia Cidade (codCid) */
CREATE TABLE autor (
  codAutor INT,
  nome VARCHAR(60),
  dataNasc DATE,
  codCid INT,

  CONSTRAINT pk_autor PRIMARY KEY (codAutor),
  CONSTRAINT fk_autor_cidade FOREIGN KEY (codCid) REFERENCES Cidade (codCid)
);

/* Tipo (codTipo, nome) */
CREATE TABLE tipo (
  codTipo INT,
  nome VARCHAR(100),
  CONSTRAINT pk_tipo PRIMARY KEY (codTipo)
);

/* Livro (codLivro, titulo, idioma, codTipo, codEd, precoSugerido)
    codTipo referencia Tipo (codTipo)
    codEd referencia Editora (codEd) */
CREATE TABLE livro (
  codLivro INT,
  titulo VARCHAR(100),
  idioma VARCHAR(50),
  codTipo INT,
  codEd INT,
  precoSugerido NUMERIC(15,2),

  CONSTRAINT pk_livro PRIMARY KEY (codLivro),
  CONSTRAINT fk_livro_tipo FOREIGN KEY (codTipo) REFERENCES Tipo (codTipo),
  CONSTRAINT fk_livro_editora FOREIGN KEY (codEd) REFERENCES Editora (codEd)
);

/* Autoria (codAutor, codLivro)
    codAutor referencia Autor (codAutor)
    codLivro referencia Livro (codLivro) */
CREATE TABLE autoria (
  codAutor INT,
  codLivro INT,

  CONSTRAINT pk_autoria PRIMARY KEY (codAutor, codLivro),
  CONSTRAINT fk_autoria_autor FOREIGN KEY (codAutor) REFERENCES Autor (codAutor),
  CONSTRAINT fk_autoria_livro FOREIGN KEY (codLivro) REFERENCES Livro (codLivro)
);

DELETE FROM AUTORIA;
DELETE FROM LIVRO;
DELETE FROM TIPO;
DELETE FROM AUTOR;
DELETE FROM EDITORA;
DELETE FROM CIDADE;

INSERT INTO cidade VALUES (1, 'Passo Fundo', 'RS');
INSERT INTO cidade VALUES (2, 'Porto Alegre', 'RS');
INSERT INTO cidade VALUES (3, 'Rio de Janeiro', 'RJ');
INSERT INTO cidade VALUES (4, 'Codisburgo', 'MG');
INSERT INTO cidade VALUES (5, 'São Paulo', 'SP');

INSERT INTO editora VALUES (1, 'Pearson', 'Rua ABC, 2013', 1);
INSERT INTO editora VALUES (2, 'Bookman', 'Rua XYZ, 829', 2);
INSERT INTO editora VALUES (3, 'Nova Fronteira', '', 3);
INSERT INTO editora VALUES (4, 'Record', '', 3);

INSERT INTO autor VALUES (1, 'Machado de Assis', TO_DATE('21/06/1839', 'dd/mm/yyyy'), 3);
INSERT INTO autor VALUES (2, 'Guimarães Rosa',   TO_DATE('27/06/1908', 'dd/mm/yyyy'), 4);
INSERT INTO autor VALUES (3, 'Assis de Machado', TO_DATE('01/01/1999', 'dd/mm/yyyy'), 1);

INSERT INTO tipo VALUES (1, 'Romance policial');
INSERT INTO tipo VALUES (2, 'Drama psicológico');
INSERT INTO tipo VALUES (3, 'Ficção');
INSERT INTO tipo VALUES (4, 'Contos');
INSERT INTO tipo VALUES (5, 'Realismo psicológico');

INSERT INTO livro VALUES (1, 'Meu 1o livro', 'Português', 1, 1, 23.00);
INSERT INTO livro VALUES (2, 'Meu 2o livro', 'Inglês', 1, 2, 45.00);
INSERT INTO livro VALUES (3, 'Meu 3o livro', 'Inglês', 1, 2, 33.00);
INSERT INTO livro VALUES (4, 'Sagarana', 'Português', 4, 3, 19.00);
INSERT INTO livro VALUES (5, 'Dom Casmurro', 'Português', 5, 4, 18.90);

INSERT INTO autoria VALUES (3, 1);
INSERT INTO autoria VALUES (3, 2);
INSERT INTO autoria VALUES (3, 3);
INSERT INTO autoria VALUES (2, 4);
INSERT INTO autoria VALUES (1, 5);