TADS:Banco de dados I: mudanças entre as edições

De Wiki Cursos IFPR Foz
Ir para navegaçãoIr para pesquisar
Linha 237: Linha 237:
6. Aula 11 de novembro de 2014
6. Aula 11 de novembro de 2014


  [[Mídia: | comandos sql-2014_11nov_11 ]] (Atualizado com correções- comandos e funções)  
  [[Mídia:comandos sql-2014-11nov-18.pdf | comandos sql-2014-11nov-18]] (Atualizado com correções- comandos e funções)  
  [[Mídia:Exercicios_2014_11nov_11.pdf | exercício Aula 11/11/2014]]
  [[Mídia:Exercicios_2014_11nov_11.pdf | exercício Aula 11/11/2014]]


Edição das 20h14min de 18 de novembro de 2014

Banco de Dados I

Carga Horária

60 horas -> 72 aulas

Ano Letivo 2014


Professora: Ana Paula Wauke (ana.wauke@ifpr.edu.br)
Horário de aula: 3a. feira - 19h00 - 20h35 | 5a feira - 20h55 - 22h35
Horário de atendimento: 6a. feira - 18h00 - 19h00

Objetivos

Fornecer conhecimentos sobre a concepção, utilização, técnicas de estruturação, manipulação de informações, modelos de representação e desenvolvimento de bancos de dados. Banco de dados relacional; normalização até a 3ª. Forma Normal.

Ementa

Sistemas de banco de dados; Projeto lógico de banco de dados; Bancos de dados relacionais; Diagrama entidade-relacionamento; Projeto físico de um banco de dados; Linguagem SQL. Álgebra Relacional. Linguagens de consulta declarativas. Normalização. Projeto Físico de Bancos de Dados. Estrutura interna de um Sistema Gerenciador de Banco de Dados.

Conteúdo Programático

  1. Introdução a Banco de Dados
    1. Dados X Informação
    2. Base de dados X sistema de banco de dados
    3. Tipos de usuários de um banco de dados
    4. Fases no desenvolvimento de um projeto de banco de dados
    5. Modelo de dados
  2. Modelo Entidade-Relacionamento
    1. Entidades e Atributos
    2. Chave primária
    3. Entidade fraca
    4. Relacionamento / Cardinalidade
    5. Especialização
    6. Agregação
  3. Modelo Relacional
    1. Chave estrangeira e Integridade referencial
    2. Conversão Modelo ER para Modelo Relacional
    3. Diagrama do Modelo Relacional
    4. Dicionário de Dados
    5. Normalização
  4. Linguagem SQL: DDL
  5. Linguagem SQL: DML
    1. Comandos INSERT, DELETE, UPDATE, SELECT
    2. cláusulas WHERE, FROM, LIKE, ORDER BY, GROUP BY
    3. Operadores IN, NOT IN, EXISTS, ALL, SOME
    4. Junção
  6. Desenvolvimento de Aplicação Prática Envolvendo Banco de Dados.
  7. Álgebra Relacional

Bibliografia Básica

  1. SILBERCHATZ, Abrahan, KORTH, Henri F., SHUDARSHAN, S., Sistema de Banco de Dados, 5a. Ed., Campus, 2006.
  2. HEUSER , Projeto de Banco de Dados. Editora Sagra Luzzato, 5a. Edição, 2004.
  3. ELMASRI, Ramez E., NAVATHE, Shamkant B., Sistema de Banco de Dados, 6a. Ed., Pearson, 2010.
  4. DATE, Chris J., Introdução a Sistemas de Banco de Dados, 8a. Ed, Campus, 2004.
  5. ABREU, Maurício P.; MACHADO, Felipe N. R.; Projeto de Banco de Dados: Uma visão prática; Ed. Érica, 2006.

Bibliografia Complamentar

  1. KROENKE, David M. ; Banco de Dados: Fundamentos, Projeto e Implementação - 6a edição; LTC Editora, 1999.
  2. SUEHRING, Steve; MySQL a Bíblia; Ed. Campus Elsevier, 2002;
  3. ANGELOTTI, Elaini Simoni, Banco de Dados, Editora do Livro Técnico, 2010.
  4. TAKAHASHI, Mana, Guia Mangá de Banco de Dados, Novatec, 2009.
  5. LECHETA, Ricardo R.; Google Android; 3ª Edição, 2013en, ed. Novatec

Link do Blog com material da aula, materiais e listas de exercício

  1. http://anapaulawauke.wordpress.com

Abaixo as listas de Exercícios. O conteúdo encontra-se dividido em partes. Neste primeiro momento os exercícios são para criar o modelo ER (modelo Entidade-Relacionamento) - ferramenta referência || brModelo. Em seguida será feito a transformação para o modelo Relacional (estrutura de tabelas) - ferramenta referência || MySql Workbench. No 2o. bimestre serão implementados para o SQL - ferramenta referência || MySql:

  1. Lista de Exercícios 1
  2. Lista de Exercícios 2
  3. Lista de Exercícios 3 - parte 1
  4. Lista de Exercícios 3 - parte 2
  5. Lista de Exercícios 3 - parte 3
  6. Lista de Exercícios 3 - parte 4
  7. Lista de Exercícios 3 - parte 5


1. Normalização Normalização até a 3FN

2. Dicionário de Dados Dicionário de Dados

3. SQL (Structured Query Language) Aula1 de SQL

4. Aula 24 de outubro de 2014 Exercício Aula 24/10/2014 

/* EXERCICIO 1 */
CREATE DATABASE dbCinema;
USE dbCinema;

CREATE TABLE tbSala(
numero_sala int PRIMARY KEY,
descricao_sala varchar(20) NOT NULL,
capacidade int NOT NULL
);

CREATE TABLE tbDiretor(
codigo_diretor int PRIMARY KEY,
nome_diretor varchar(20) NOT NULL
);

CREATE TABLE tbFilme(
codigo_filme int PRIMARY KEY,
nome_filme varchar(50) NOT NULL,
ano_lancamento int NOT NULL,
categoria_filme varchar(20) NOT NULL,
codigo_diretor int NOT NULL,
FOREIGN KEY (codigo_diretor) REFERENCES tbDiretor(codigo_diretor)
);

CREATE TABLE tbSalaFilme(
numero_sala int NOT NULL,
codigo_filme int NOT NULL,
data date NOT NULL,
horario time NOT NULL,
PRIMARY KEY(numero_sala, codigo_filme, data),
FOREIGN KEY (numero_sala) REFERENCES tbSala(numero_sala),
FOREIGN KEY (codigo_filme) REFERENCES tbFilme(codigo_filme)
);

CREATE TABLE tbPremio(
codigo_premio int PRIMARY KEY,
nome_premio varchar(20) NOT NULL,
ano_premiacao int NOT NULL,
codigo_filme int NOT NULL,
FOREIGN KEY (codigo_filme) REFERENCES tbFilme(codigo_filme)
);

/* EXERCICIO 2 */
INSERT INTO tbSala(numero_sala, descricao_sala, capacidade) VALUES (1, 'Sala Legal', 50);
INSERT INTO tbSala(numero_sala, descricao_sala, capacidade) VALUES (2, 'Sala 3D', 40);
INSERT INTO tbSala(numero_sala, descricao_sala, capacidade) VALUES (3, 'Sala Gigante', 200);
INSERT INTO tbSala(numero_sala, descricao_sala, capacidade) VALUES (8, 'Sala 6D', 150); 

INSERT INTO tbDiretor(codigo_diretor, nome_diretor) VALUES(1, 'João da Silva');
INSERT INTO tbDiretor(codigo_diretor, nome_diretor) VALUES(2, 'Pedro de Sousa');
INSERT INTO tbDiretor(codigo_diretor, nome_diretor) VALUES(3, 'Maria de Matos');

INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(1, 'A Revolta dos Mortos', 2014, 'Ação', 1);
INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(2, 'Plague: A destruição', 2015, 'Suspense', 2);
INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(3, 'Homem Aranha vs Homem de Ferro', 2014, 'Ação', 3);

INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(1, 3, NOW(), NOW());
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(2, 1, '2015-02-27', '13:00:00');
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(3, 2, NOW(), NOW());

INSERT INTO tbPremio(codigo_premio, nome_premio, ano_premiacao, codigo_filme) VALUES(1, 'Melhor Filme', 2015, 2);
INSERT INTO tbPremio(codigo_premio, nome_premio, ano_premiacao, codigo_filme) VALUES(2, 'Melhor Diretor', 2016, 1);
INSERT INTO tbPremio(codigo_premio, nome_premio, ano_premiacao, codigo_filme) VALUES(3, 'Maior Bilheteria', 2015, 3);

/* EXERCICIO 2.1 -- 2.7 */
INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(4, 'IFPR: O contágio', 2015, 'Terror', 1);
INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(5, 'A eleição', 2014, 'Terror', 3);

INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(8, 5, '2010-11-15', '13:00:00');
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(8, 4, '2015-11-15', '15:30:00');

INSERT INTO tbDiretor(codigo_diretor, nome_diretor) VALUES(4, 'Pedro Paulo Matos');
INSERT INTO tbDiretor(codigo_diretor, nome_diretor) VALUES(5, 'Jorge Penha');

INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(6, 'The Project', 2015, 'Comédia', 5);

INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(7, 'Titanic', 2001, 'Drama', 2);

INSERT INTO tbPremio(codigo_premio, nome_premio, ano_premiacao, codigo_filme) VALUES(4, 'Melhor Filme', 2010, 7);
INSERT INTO tbPremio(codigo_premio, nome_premio, ano_premiacao, codigo_filme) VALUES(5, 'Melhor Diretor', 2001, 7);
INSERT INTO tbPremio(codigo_premio, nome_premio, ano_premiacao, codigo_filme) VALUES(6, 'Maior Bilheteria', 2008, 7);

INSERT INTO tbPremio(codigo_premio, nome_premio, ano_premiacao, codigo_filme) VALUES(7, 'Melhor Filme', 2007, 4);
INSERT INTO tbPremio(codigo_premio, nome_premio, ano_premiacao, codigo_filme) VALUES(8, 'Melhor Diretor', 2009, 5);
INSERT INTO tbPremio(codigo_premio, nome_premio, ano_premiacao, codigo_filme) VALUES(9, 'Maior Bilheteria', 2008, 6);
INSERT INTO tbPremio(codigo_premio, nome_premio, ano_premiacao, codigo_filme) VALUES(10, 'Melhor Filme', 2010, 6);

INSERT INTO tbSala(numero_sala, descricao_sala, capacidade) VALUES (12, 'Sala Quase PT', 30);
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(12, 1, '2010-11-15', '13:00:00');
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(12, 2, '2010-11-15', '15:00:00');
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(12, 3, '2010-11-15', '17:00:00');

/* EXERCICIO 3 */
select * from tbDiretor;

/* EXERCICIO 4 */
select * from tbFilme where categoria_filme = 'Terror';

/* EXERCICIO 5 */
UPDATE tbSala SET capacidade=200 WHERE numero_sala = 8;

/* EXERCICIO 6*/
UPDATE tbSalaFilme SET numero_sala=8 WHERE numero_sala = 12 AND data = '2010-11-15';

/* EXERCICIO 7 */
delete from tbDiretor where nome_diretor='Pedro Paulo Matos';

/* EXERCICIO 8 */
SELECT nome_filme as 'nome' FROM tbFilme, tbDiretor WHERE tbFilme.codigo_diretor = tbDiretor.codigo_diretor AND nome_diretor = 'Jorge Penha';

/* EXERCICIO 9 */
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(1, 5, '2010-04-20', NOW());
SELECT nome_filme as 'nome', horario FROM tbFilme, tbSalaFilme WHERE tbFilme.codigo_filme = tbSalaFilme.codigo_filme AND tbSalaFilme.data = '2010-04-20';

/* EXERCICIO 10 */
SELECT nome_premio FROM tbPremio, tbFilme where tbPremio.codigo_filme = tbFilme.codigo_filme AND nome_filme = 'Titanic';

/* EXERCICIO 11 */
UPDATE tbSala SET capacidade=250 WHERE numero_sala = 3;
SELECT nome_filme FROM tbFilme f, tbSalaFilme sf, tbSala s WHERE f.codigo_filme = sf.codigo_filme AND s.numero_sala = sf.numero_sala AND capacidade > 200;

/* EXERCICIO 12 */
INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(8, 'Titanic - O retorno', 2001, 'Drama', 2);
INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(9, 'Os Vingadores 7 - O fim de Marte', 2012, 'Ação', 3);
INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(10, 'The movie', 2005, 'Comédia', 1);
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(3, 8, '2010-05-15', '13:00:00');
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(8, 9, '2010-05-15', '15:00:00');
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(2, 10, '2010-05-15', '17:00:00');
SELECT nome_filme as 'Filme', s.numero_sala as 'Sala', horario FROM tbFilme f, tbSalaFilme sf, tbSala s WHERE f.codigo_filme = sf.codigo_filme AND s.numero_sala = sf.numero_sala AND sf.data between '2010-05-01' AND '2010-05-31';

/* EXERCICIO 13 */
SELECT nome_filme as 'Premiados' FROM tbFilme f, tbPremio p WHERE f.codigo_filme = p.codigo_filme AND nome_premio = "Melhor Diretor" AND ano_premiacao IN  (2007, 2008, 2009, 2010);

/* EXERCICIO 14 */
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(3, 9, '2010-06-30', '15:00:00');
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(2, 10, '2010-06-15', '17:00:00');
SELECT nome_filme as 'nome' FROM tbFilme, tbSalaFilme WHERE tbFilme.codigo_filme = tbSalaFilme.codigo_filme AND tbSalaFilme.data >= '2010-06-01' AND  tbSalaFilme.data < '2010-07-01' AND tbFilme.categoria_filme = 'Comédia';

/* EXERCICIO 15 */
UPDATE tbDiretor SET nome_diretor='Severino Juca' WHERE codigo_diretor = 2;
SELECT descricao_sala as 'Descrição', categoria_filme as 'Categoria', nome_filme as 'Filme' FROM tbDiretor d, tbFilme f, tbSala s, tbSalaFilme sf WHERE      d.codigo_diretor = f.codigo_diretor AND f.codigo_filme = sf.codigo_filme AND s.numero_sala = sf.numero_sala AND nome_diretor = 'Severino Juca';

5. Aula 31 de outubro de 2014

5.1 Exercício Aula 24/10/2014 - Parte 1

5.2 Exercício Aula 24/10/2014 - Parte 2

5.3 Exercício Aula 24/10/2014 - Parte 3

6. Aula 11 de novembro de 2014 

 comandos sql-2014-11nov-18 (Atualizado com correções- comandos e funções) 
 exercício Aula 11/11/2014

INSERT INTO tbDiretor(codigo_diretor, nome_diretor) VALUES(6, 'Steven Spielberg');
INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(11, 'Jurassic Park', 1990, 'Ação', 6);
INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(12, 'A Lista de Schindler', 1995, 'Suspense', 6);
INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(13, 'Super 8', 2000, 'Comédia', 6);
INSERT INTO tbFilme(codigo_filme, nome_filme, ano_lancamento, categoria_filme, codigo_diretor) VALUES(14, 'Lincoln', 1997, 'Animação', 6);
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(12, 11, '2014-11-19', NOW());
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(12, 12, '2014-11-28', NOW());
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(12, 13, now(), NOW());
INSERT INTO tbSalaFilme(numero_sala, codigo_filme, data, horario) VALUES(12, 14, '2015-01-30', NOW());
INSERT INTO tbIngresso(numero_sala, codigo_filme, data, horario, data_venda, preco) VALUES(12, 11, '2014-11-19', NOW(), NOW(), 22.99);
INSERT INTO tbIngresso(numero_sala, codigo_filme, data, horario, data_venda, preco) VALUES(12, 12, '2014-11-28', NOW(), NOW(), 30.99);
INSERT INTO tbIngresso(numero_sala, codigo_filme, data, horario, data_venda, preco) VALUES(12, 13, '2014-11-11', NOW(), NOW(), 199.82);
INSERT INTO tbIngresso(numero_sala, codigo_filme, data, horario, data_venda, preco) VALUES(12, 14, '2015-01-30', NOW(), NOW(), 10.88);


Exercícios 14/11/2014 

tbFornecedores (id_fornecedor, nome_f, email, telf)
tbProdutos( id_produto, id_tipo_prod, nome_p, preco_uni, qt_stoq)
tbTipo_Produto( id_tipo_prod, nome_tipo_prod)
tbFornecimento (id_fornecimento, id_fornecedor, id_prod,data,qtprod_forn, valor_parcial)

Insira valores na base de dados, nas tabelas. Conforme consultas, insira valores para que apareça resultados.
1. Mostre os nomes dos produtos no stoq e a quantidade de cada uns deles, ordenados descendentemente.
2. Mostre o nome e tipo dos produtos com quantidade menor que 20 no stoq, ordenados em ordem alfabética.
3. Mostre os nomes dos produtos do tipo “informática” e do tipo “mobília”.
4.Mostre quantidade diária de cada produto fornecido por fornecedor.
5. Mostre os nomes dos fornecedores que não tem fornecido produtos.
6. Mostre quantos fornecimentos o fornecedor Deca forneceu. Repita o exercicio 6 usando tabelas derivadas.
7. Mostre o valor total de todas os fornecimentos do fornecedor LTC.
8. Liste todos os produtos que começam com "a" e terminam com "r".
9. Mostre o valor total gasto com todos os fornecimentos no ano de 2014. 
10. Mostre o maior valor gasto com fornecedor.
11. Mostre o fornecimento mais barato. (o menor valor gasto)
12. Mostre a quantidade de fornecimentos por fornecedores.
13. Mostre a quantidade de fornecimentos por fornecedores, apenas para os que tiverem mais de 3 fornecimentos.
14. Mostre o fornecedor de menor quantidade de fornecimentos.
15. Liste os fornecedores que não tiveram fornecimento algum no ano de 2014.
Disponível em “http://wiki.foz.ifpr.edu.br/wiki/index.php?title=TADS:Banco_de_dados_I&oldid=5590”