[GABARITO] view_projetoBiblioteca

CREATE DATABASE biblioteca;

USE biblioteca;



CREATE TABLE Funcionarios
(
    cpf VARCHAR(11) NOT NULL PRIMARY KEY,
    nome VARCHAR(40) NOT NULL,
    endereco VARCHAR(40) NOT NULL,
    telefone VARCHAR(12) NOT NULL,
    salario NUMERIC(10,2) NOT NULL DEFAULT 0.00,
    funcao VARCHAR(30)
);

INSERT INTO
    Funcionarios (cpf, nome, endereco, telefone, salario, funcao)
VALUES
    ("61254590871", "Lucia Vicentim", "Salto", 21316565, 0, "Bibliotecaria"),
    ("20321295096", "João Alberto Smith", "Itatiba", 22447865, 0, NULL),
    ("32361298734", "Luís Henrique Talles", "Campinas", 21531785, 0, NULL),
    ("45403612087", "Francisco José Almeida", "Indaiatuba", 25417761, 0, NULL);



CREATE TABLE Editoras
(
    codigo INT NOT NULL PRIMARY KEY,
    nome VARCHAR(40) NOT NULL,
    endereco VARCHAR(40),
    contato VARCHAR(12) NOT NULL
);

INSERT INTO
    Editoras
VALUES
    (2134000, "Saraiva", "São Paulo", "08003434"),
    (2287000, "Eras", "Brasília", "08002432"),
    (3557000, "Summer", "Curitiba", "08002198"),
    (6655000, "Pontos", "São Paulo", "08005600"),
    (9898000, "Marks", "Rio de Janeiro", "0800900");



CREATE TABLE Usuarios
(
    cpf VARCHAR(11) NOT NULL PRIMARY KEY,
    nome VARCHAR(40) NOT NULL,
    endereco VARCHAR(40),
    telefone VARCHAR(12) NOT NULL
);

INSERT INTO
    Usuarios (CPF, nome, telefone, endereco)
VALUES
    ("10122020232", "Maria de Lourdes Amaral", "35440089", NULL),
    ("19321122213", "José Francisco de Paula", "27219756", NULL),
    ("70964411900", "Luiza Souza Prado", "34559087", NULL),
    ("45399109881", "Raquel Santos", "87603451", NULL),
    ("22539910976", "Ivete Medina Chernell", "48170352", NULL);



CREATE TABLE Autores
(
    codigo INT NOT NULL PRIMARY KEY,
    nome VARCHAR(40) NOT NULL,
    nacionalidade VARCHAR(40) NOT NULL
);

INSERT INTO
    Autores (nome, nacionalidade, codigo)
VALUES
    ("Ethevaldo Siqueira", "Brasileira", 85668900),
    ("Ana Lucia Jankovic Barduchi", "Brasileira", 77548854),
    ("Adélia Prado", "Brasileira", 55490076),
    ("Walter Isaacson", "Americana", 22564411),
    ("Steven K. Scott", "Americana", 90984133);




CREATE TABLE Livros
(
    numero INT NOT NULL PRIMARY KEY,
    titulo VARCHAR(80) NOT NULL,
    genero VARCHAR(40) NOT NULL,
    edicao SMALLINT NOT NULL,
    ano_publicacao YEAR NOT NULL,
    cpf_funcionario VARCHAR(11),
    codigo_editora INT NOT NULL,
    CPF_usuarioRetirar VARCHAR(11),
    CPF_usuarioReservar VARCHAR(11),
    FOREIGN KEY (cpf_funcionario) REFERENCES Funcionarios (cpf),
    FOREIGN KEY (codigo_editora) REFERENCES Editoras (codigo),
    FOREIGN KEY (CPF_usuarioRetirar) REFERENCES Usuarios (cpf),
    FOREIGN KEY (CPF_usuarioReservar) REFERENCES Usuarios (cpf)
);


INSERT INTO
    Livros (numero, titulo, genero, edicao, ano_publicacao, CPF_funcionario, codigo_editora, CPF_usuarioRetirar, CPF_usuarioReservar)
VALUES
    (87659908, "Tecnologias que Mudam Nossa Vida", "Tecnologia", 2, 2007, NULL, 2134000, NULL, "70964411900"),
    (67392217, "Empregabilidade – Competências Pessoais e Profissionais", "Administração", 22, 1977, "32361298734", 9898000, NULL, NULL),
    (45112239, "Steve Jobs – a Biografia", "Biografia", 48, 2011, NULL, 2287000, "19321122213", NULL),
    (77680012, "A Duração do Dia", "Poesia", 1, 2010, NULL, 2134000, "10122020232", NULL),
    (32176500, "Salomão – O Homem Mais Rico que Já Existiu", "Romance", 2, 2011, "61254590871", 6655000, NULL, NULL),
    (67554421, "Bagagem", "Poesia", 5, 1972, NULL, 6655000, NULL, "19321122213"),
    (10277843, "O Pelicano", "Romance", 12, 1984, NULL, 2134000, NULL, NULL);


CREATE TABLE Livros_Autores
(
    numero_livro INT NOT NULL,
    codigo_autor INT NOT NULL,
    PRIMARY KEY (numero_livro, codigo_autor),
    FOREIGN KEY (numero_livro) REFERENCES Livros (numero),
    FOREIGN KEY (codigo_autor) REFERENCES Autores (codigo)
);

INSERT INTO
    Livros_Autores (numero_livro, codigo_autor)
VALUES
    (10277843, 55490076),
    (32176500, 90984133),
    (45112239, 22564411),
    (67392217, 77548854),
    (67554421, 55490076),
    (77680012, 55490076),
    (87659908, 85668900),
    (10277843, 85668900);


# Outras operações na base de dados.

UPDATE Funcionarios SET funcao = "Auxiliar", salario = 800 WHERE CPF = "32361298734";

DELETE FROM Usuarios WHERE CPF = "45399109881";

DELETE FROM Livros_Autores WHERE numero_livro = 32176500 AND codigo_autor = 90984133;

UPDATE livros SET CPF_usuarioReservar = NULL, CPF_usuarioRetirar = "70964411900" WHERE numero = 87659908;




# Visão 1:

# Vamos organizar os dados somente de autores brasileiros, assim para todas as consultas
# que envolvem essa informação não será preciso pesquisar toda a base de dados que temos,
# mas apenas esta visão.

# Visão ‘livros_autores_brasileiros’:
#   seleciona o número, gênero, nome dos livros e seus autores, sendo todos
#   com nacionalidade brasileira.


CREATE VIEW
    livros_autores_brasileiros
AS
    SELECT
        l.numero, l.titulo, l.genero, aut.nome, aut.nacionalidade
    FROM
        livros l JOIN livros_autores la JOIN autores aut
    ON
        l.numero = la.numero_livro AND la.codigo_autor = aut.codigo
    WHERE
        aut.nacionalidade = "Brasileira"
    ORDER BY
        numero;


SELECT * FROM livros_autores_brasileiros;


# Visão 2:

# Em um sistema de biblioteca em que funcionários podem retirar livros, é comum procurar
# relatórios sobre esse tipo de movimentação.

# Visão ‘Funcionarios_Livros’:
#   selecione os funcionários que costumam retirar livros, indicando o número, título,
#   gênero dos livros e o CPF, nome, função e salário dos funcionários.


CREATE VIEW
    funcionarios_livros
AS
    SELECT
        l.numero, l.titulo, l.genero, f.CPF, f.nome, f.funcao, f.salario
    FROM
        livros l JOIN funcionarios f
    ON
        f.CPF = l.CPF_funcionario
    WHERE
        l.CPF_funcionario IS NOT NULL;


SELECT * FROM Funcionarios_Livros;