Trabalho Prático 2
Introdução a Banco de Dados
Leonel Fonseca Ivo
Rangel Silva Oliveira
Dezembro de 2009
Modelo Conceitual
Modelo Conceitual
Esquema Relacional
Autor (CodAutor(nn), Nome(nn), Nacionalidade)
Publicacao (CodCat(nn), Tipo, Titulo(nn), Assunto(nn), Resenha, Capa, AnoPub, NumPags, Preco(nn),
QntEstoque(nn), UnidVendidas(nn), CodEditora(nn))
Editora (CGC(nn), Denominacao(nn), Logradouro(nn), Numero(nn), Cidade(nn), UF(nn))
Cliente (CPF(nn), Nome(nn), TelefoneContato, Logradouro(nn), Numero(nn), Cidade(nn), UF(nn))
Biografia (CodBiog(nn), CodAutor(nn), NomePai, NomeMae, DataNasc, CidadeNasc, DataFalecimento,
CidadeFalecimento, PrimeiraObra)
Livro (CodCat(nn), ISBN(nn), Genero(nn))
Dicionario (CodCat(nn), Idioma1(nn), Idioma2(nn))
Periodico (CodCat(nn), Classicacao(nn), Periodicidade)
Guia (CodCat(nn), Localidade(nn))
Mapa (CodCat(nn), Regiao(nn), ProjCartograca, TipoMapa(nn))
Escrito_por (CodAutor(nn), CodCat(nn))
Autoria_de (CodAutor(nn), CodCat(nn))
Faz_encomenda (CPF(nn), CodCat(nn), HoraEncomenda(nn), DataEncomenda(nn), Quantidade(nn))
GeneroLit (CodAutor(nn), Genero(nn))
Foto (CodAutor(nn), Foto(nn))
Imagem (CodCat(nn), Imagem(nn))
Restrições de Integridade
Restrições de Integridade
Criação do Banco de Dados
CREATE DATABASE Livraria;
CREATE TABLE Autor (
Nome varchar(45) NOT NULL,
Nacionalidade varchar(15) default NULL,
CodAutor int default NULL auto_increment,
PRIMARY KEY (CodAutor)
) ENGINE = INNODB;
CREATE TABLE Autoria_de (
CodCat int NOT NULL,
CodAutor int NOT NULL,
PRIMARY KEY (CodCat, CodAutor),
FOREIGN KEY (CodAutor) REFERENCES Autor(CodAutor)
ON DELETE RESTRICT
ON UPDATE CASCADE,
FOREIGN KEY (CodCat) REFERENCES Dicionario(CodCat)
ON DELETE RESTRICT
ON UPDATE CASCADE
) ENGINE = INNODB;
Consultas SQL
1. Liste a editora, a quantidade em estoque, o preço e o numero de unidades já vendidas de uma determinada
publicação.
SELECT Denominacao, QntEstoque, Preco, UnidVendidas
FROM Publicacao JOIN Editora
WHERE CodEditora=CGC AND Titulo='${titulo_publicacao}‘
2. Liste os ttulos, o g^enero e o ano de publicac~ao dos livros de um determinado autor.
SELECT Publicacao.Titulo, Livro.Genero, Publicacao.AnoPub
FROM ((Publicacao NATURAL JOIN Livro) NATURAL JOIN
(Escrito_por NATURAL JOIN Autor))
WHERE Autor.Nome='${nome_autor}'
3. Liste o titulo, o assunto, o ano de publicação, a editora e o numero de unidades vendidas das 10
publicações mais vendidas.
SELECT Titulo, Assunto, AnoPub, Denominacao, UnidVendidas
FROM Publicacao JOIN Editora
WHERE CodEditora=CGC
ORDER BY UnidVendidas DESC LIMIT 10
4. Liste a resenha da publicacao com nome especicado.
SELECT Resenha
FROM Publicacao
WHERE Titulo='${titulo}'
Consultas SQL
5. Liste o ttulo e o tipo de publicac~ao dos lancamentos do ano de 2009.
SELECT Titulo, Tipo
FROM Publicacao
WHERE AnoPub=2009
6. Liste o ttulo e o nome do autor dos livros de um dado g^enero literario.
SELECT Publicacao.Titulo, Autor.Nome
FROM (Publicacao NATURAL JOIN Escrito_por
NATURAL JOIN Autor NATURAL JOIN GeneroLit)
WHERE Genero='${genero_literario}'
7. Liste os ttulos e os tipos de mapas de uma determinada regi~ao.
SELECT Titulo, Mapa.TipoMapa
FROM (Publicacao NATURAL JOIN Mapa)
WHERE Mapa.Regiao='${regiao}'
8. Liste todas as imagens relacionadas a uma determinada publicac~ao.
SELECT Imagem
FROM (Publicacao NATURAL JOIN Imagem)
WHERE Titulo='${titulo_publicacao}'
9. Liste todas as fotos relacionadas a um determinado autor.
SELECT Foto
FROM (Autor NATURAL JOIN Foto)
WHERE Nome='${nome_autor}'