Solução da Primeira Prova

Correção da 1a Prova de
GBD1 – 2009/2
Profa. Sandra de Amo
1. Você foi contratado para fazer o projeto de um banco de dados para um teatro. Seu cliente (o
gerente do teatro) deseja estocar informações a respeito :
• das peças que serão exibidas na temporada do ano 2009. Informações tais como tı́tulo, autor,
gênero, perı́odo em que vai ficar em cartaz ( data da primeira apresentação e data da última
apresentação). É possivel que um mesmo titulo corresponda a peças distintas, dependendo do
autor.
• dos autores : devem ser estocadas informações tais como nome, nacionalidade, data de nascimento.
• dos espectadores : devem ser estocadas informações tais como nome, telefone, e-mail (para
contato).
• das reservas de bilhetes : dia da reserva, preço do bilhete, número do lugar, nome da peça,
nome do espectador.
• dos atores que trabalham em cada peça e o nome do personagem que representam na peça. É
possivel que um ator represente diferentes personagens.
(a) (5 pontos) Faça um diagrama Entidade - Relacionamento para este banco de dados. Neste
diagrama devem aparecer as chaves das entidades e as restrições de participação e de chaves
dos relacionamentos. Caso você necessitar de informação extra para a modelagem você deverá
deixar bem bem explı́ıtas as hipóteses feitas.
(b) (5 pontos) Utilizando o diagrama que você projetou no item (a), especifique o modelo relacional correspondente. Atenção : Você deve dar os comandos SQL completos de criação
das tabelas, onde devem ser especificados os tipos dos atributos, as chaves primárias e candidatas e as chaves estrangeiras.
CodP
Tit
Nome
Gen
Dl
DF
Compõe
Peça
Nac
DN
Autor
Cpf
Ator
Preço
Reserva
Nome
Lugar
Atua-em
Personagem
Espectador
email
Nome
Tel
Id
Nome
Tabelas referentes às entidades
CREATE TABLE PEÇAS
(CodP Integer, Tit char(30),
Gen char(15), DI Date, DF Date,
Nome char(20) NOT NULL,
Primary Key (CodP),
Foreign Key (Nome) References Autor)
CREATE TABLE ESPECTADOR
(Nome char(30), Tel Integer,
email char(30),
Primary Key (Nome))
CREATE TABLE AUTOR
(Nome char(20), DN Date,
Nac char(15),
Primary Key (Nome))
CREATE TABLE ATOR
(CPF Integer, Nome char(30),
Primary Key (CPF))
CREATE TABLE PERSONAGEM
(Id Integer, Nome char(20),
Primary Key (Id))
Tabelas referentes aos relacionamentos
CREATE TABLE ATUA-EM
(CodP Integer, CPF Integer,
Id Integer, IdA Integer,
Primary Key (IdA),
Foreign Key (CodP) References PEÇAS,
Foreign Key (CPF) References ATOR,
Foreign Key (Id) References PERSONAGEM)
CREATE TABLE RESERVA
(Nome char(30), CodP Integer,
Preço Integer, Lugar Integer,
Primary Key (Nome,CodP),
Foreign Key (Nome) References Espectador,
Foreign Key (CodP) References Peça)
2. (10 pontos) Considere o seguinte diagrama referencial e as declarações de tabelas de um banco de
dados relacional :
R
{B}
T
{D}
{C}
S
{C}
Q
CREATE TABLE R
(A CHAR(2),
B CHAR(20),
C CHAR(2),
PRIMARY KEY (A),
FOREIGN KEY (B) REFERENCES Q(B)
ON DELETE CASCADE,
FOREIGN KEY (C) REFERENCES S(C));
CREATE TABLE Q
(B CHAR(20),
C CHAR(2),
D INTEGER ,
PRIMARY KEY (B)
FOREIGN KEY (C) REFERENCES S(C)
ON DELETE CASCADE,
FOREIGN KEY (D) REFERENCES T(D));
CREATE TABLE S
(C CHAR(2),
E CHAR(12),
PRIMARY KEY (C));
CREATE TABLE T
(D INTEGER,
E CHAR(12),
PRIMARY KEY (D));
Considere a seguinte instância deste banco de dados :
A
a1
a2
a3
a1
R
B
b1
b2
b3
b1
C
c4
c6
c7
c8
B
b1
b2
b3
b4
Q
C
c4
c5
c6
c7
S
D
d1
d1
d3
d4
C
c4
c5
c6
c7
c8
c9
T
E
e1
e2
e3
e4
e5
e6
D
d1
d2
d3
d4
E
e5
e6
e7
e5
(a) (5 pontos) Dê o comando SQL para suprimir de S todas as tuplas com valor do atributo C
igual a c5 . Explique a reação do sistema a este comando, justificando sua resposta.
Solução
DELETE FROM S
WHERE S.C = c5
Efeito: A tupla (b2,c5,d1) é deletada de Q e a tupla (a2,b2,c6) é deletada de R.
(b) (5 pontos) Explique a reação do sistema quando é submetido a um comando de supressão
em T das tuplas com valor do atributo E igual a e5 .
Solução
Nada acontece pois a chave Q → T é declarada (por default) como ON DELETE NO ACTION.
3. Considere o esquema de banco de dados relacional onde são estocadas informações sobre CINEMA.
O esquema é composto das seguintes tabelas :
FILME(Titulo,AnoProd,Duração, NomeStudio, CodProdutor, Custo)
chave primária = {Titulo} // chave estrangeira NomeStudio referencia STUDIO como Nome
chave estrangeira CodProdutor referencia EXECUTIVO como CodE
ATOR(NomeA,End,Sexo,DataNasc)
chave primária = {NomeA}
ATUA-EM(ID,Titulo,NomeA)
chave primária = { ID } // chave estrangeira Titulo referencia FILME
chave estrangeira NomeA referencia ATOR
EXECUTIVO(CodE,NomeE,End,SalAnual)
chave primária = {CodE}
STUDIO(Nome,End,CodPres)
chave primária = {Nome} // chave estrangeira CodPres referencia EXECUTIVO como CodE
Dê comandos SQL para executar as seguintes consultas sobre este banco de dados :
(a) (5 pontos) Dê o nome dos atores que atuaram em algum filme da ”Universal Pictures” com
duração superior a 2 horas.
Solução
ΠNomeA (σDuração > 2h AND NomeStudio = ’UP’ (Filme) 1 Atua-em)
(b) (5 pontos) Dê o titulo e o custo dos filmes produzidos pelo produtor Dino di Laurentis para
o estúdio “Metro Goldwin Mayer” e no qual trabalhou um ator nascido em 1950.
Solução
ΠTitulo,Custo (σNomeStudio = MGM (Filme) 1 ρCodE → CodProdutor (σNomeE = Dino di Laurentis (Executivo)
1 Atua-em 1 σDN = 1950 (Ator)))