Lista de Exercı́cios de BD
Lucas Peres da Silva
20 de outubro de 2009
Sumário
Exercı́cio 01
p. 3
Modelo Entidade Relacionamento . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
p. 3
Modelo Relacional . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
p. 3
Seleções . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
p. 6
Exercı́cio 02
p. 10
Modelo Entidade Relacionamento . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
p. 10
Modelo Relacional . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
p. 10
Seleções . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
p. 12
Exercı́cio 03
p. 14
Modelo Entidade Relacionamento . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
p. 14
Modelo Relacional . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
p. 14
Seleções . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
p. 16
3
Exercı́cio 01
Modelo Entidade Relacionamento
Situação
Situação
Sid
(1,N)
(0,N)
Contém
Sistema Natural
Espécies
(0,N)
Esta Em
(1,1)
Animais
Localização
Região
Estado
Lid
Cidade
Faz_Fronteira
Modelo Relacional
DROP TABLE I F EXISTS E s p e c i e s ;
DROP TABLE I F EXISTS E s t a d o ;
DROP TABLE I F EXISTS C i d a d e ;
DROP TABLE I F EXISTS P l a n t a s ;
DROP TABLE I F EXISTS A n i m a i s ;
DROP TABLE I F EXISTS S i s t e m a N a t u r a l ;
DROP TABLE I F EXISTS S i s t e m a N a t u r a l h a s E s p e c i e s ;
CREATE TABLE E s t a d o (
Plantas
4
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
F a z F r o n t e i r a BOOL NULL,
UF VARCHAR( 2 ) NULL,
Nome VARCHAR( 2 0 ) NULL,
R e g i a o ENUM( ’ N o r t e ’ , ’ S u l ’ , ’ L e s t e ’ , ’ O e s t e ’ ) NULL,
PRIMARY KEY( ID )
);
INSERT INTO E s t a d o ( ID , F a z F r o n t e i r a , UF , Nome , R e g i a o )
VALUES
( 1 , 1 , ’PR ’ , ’ P a r a n á ’ , ’ S u l ’ ) ,
( 2 , 0 , ’ SP ’ , ’ S ão P a u l o ’ , ’ S u l ’ ) ,
( 3 , 1 , ’RS ’ , ’ Rio Grande do S u l ’ , ’ S u l ’ ) ,
( 4 , 0 , ’DF ’ , ’ D i s t r i t o F e d e r a l ’ , ’ N o r t e ’ ) ;
CREATE TABLE C i d a d e (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
E s t a d o I D INTEGER UNSIGNED NOT NULL,
Nome VARCHAR( 5 0 ) NULL,
PRIMARY KEY( ID ) ,
INDEX C i d a d e F K I n d e x 1 ( E s t a d o I D )
);
INSERT INTO C i d a d e ( ID , E s t a d o I D , Nome ) VALUES
( 1 , 1 , ’ L o n d r i n a ’ ) , ( 2 , 1 , ’ Cambé ’ ) ,
( 3 , 1 , ’ R o l â n d i a ’ ) , ( 4 , 1 , ’ J a n d a i a ’ ) ,
( 5 , 2 , ’ Campinas ’ ) , ( 6 , 2 , ’ Campinas ’ ) ,
(7 , 2 , ’ Sertaozninho ’ ) , ( 8 , 3 , ’ Porto Alegre ’ ) ,
( 9 , 3 , ’ V a r z e a Grande ’ ) , ( 1 0 , 4 , ’ B r a z i l i a ’ )
;
CREATE TABLE E s p e c i e s (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
Nome VARCHAR( 5 0 ) NULL,
T i p o ENUM( ’ Animal ’ , ’ P l a n t a ’ ) NULL,
PRIMARY KEY( ID )
);
5
INSERT INTO E s p e c i e s ( ID , Nome , T i p o ) VALUES
( 1 , ’ Cogumelo do S o l ’ , ’ P l a n t a ’ ) ,
( 2 , ’ A r v o r e de Maçã ’ , ’ P l a n t a ’ ) ,
( 3 , ’ Macaco P r e g o ’ , ’ Animal ’ ) ,
( 4 , ’ Bixo P r e g u i ç a ’ , ’ Animal ’ ) ,
( 5 , ’ Grama ’ , ’ P l a n t a ’ )
;
CREATE TABLE P l a n t a s (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
E s p e c i e s I D INTEGER UNSIGNED NOT NULL,
PRIMARY KEY( ID , E s p e c i e s I D ) ,
INDEX P l a n t a s F K I n d e x 1 ( E s p e c i e s I D )
);
INSERT INTO P l a n t a s ( ID , E s p e c i e s I D ) VALUES
(NULL, 1 ) ,
(NULL, 2 ) ,
(NULL, 5 )
;
CREATE TABLE A n i m a i s (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
E s p e c i e s I D INTEGER UNSIGNED NOT NULL,
PRIMARY KEY( ID , E s p e c i e s I D ) ,
INDEX A n i m a i s F K I n d e x 1 ( E s p e c i e s I D )
);
INSERT INTO A n i m a i s ( ID , E s p e c i e s I D ) VALUES
(NULL, 3 ) ,
(NULL, 4 )
;
CREATE TABLE S i s t e m a N a t u r a l (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
C i d a d e I D INTEGER UNSIGNED NOT NULL,
S i t u a c a o ENUM( ’ E x c e l e n t e ’ , ’Bom ’ , ’ M édio ’ , ’ R e g u l a r ’ , ’ P é s s i m o ’ ) NULL,
Nome VARCHAR( 2 5 5 ) NULL,
6
PRIMARY KEY( ID ) ,
INDEX S i s t e m a N a t u r a l F K I n d e x 1 ( C i d a d e I D )
);
INSERT INTO S i s t e m a N a t u r a l ( ID , C i d a d e I D , S i t u a c a o , Nome ) VALUES
( 1 , 1 , ’ P é s s i m o ’ , ’ Rio I g a p ó ’ ) ,
( 2 , 1 , ’Bom ’ , ’ Mata do Godoy ’ ) ,
(3 , 5 , ’ Excelente ’ , ’ C e n t r a l Park ’ ) ,
( 4 , 5 , ’ P é s s i m o ’ , ’ Bosque B o n i t o ’ )
;
CREATE TABLE S i s t e m a N a t u r a l h a s E s p e c i e s (
S i s t e m a N a t u r a l I D INTEGER UNSIGNED NOT NULL,
E s p e c i e s I D INTEGER UNSIGNED NOT NULL,
S i t u a c a o ENUM( ’ E x c e l e n t e ’ , ’Bom ’ , ’ M édio ’ , ’ R e g u l a r ’ , ’ P é s s i m o ’ ) NULL,
PRIMARY KEY( S i s t e m a N a t u r a l I D , E s p e c i e s I D ) ,
INDEX S i s t e m a N a t u r a l h a s E s p e c i e s F K I n d e x 1 ( S i s t e m a N a t u r a l I D ) ,
INDEX S i s t e m a N a t u r a l h a s E s p e c i e s F K I n d e x 2 ( E s p e c i e s I D )
);
INSERT INTO S i s t e m a N a t u r a l h a s E s p e c i e s ( S i s t e m a N a t u r a l I D , E s p e c i e s I D , S i t u a c a o )
VALUES
( 1 , 1 , ’Bom ’ ) ,
(1 ,2 , ’ Excelente ’ ) ,
( 1 , 3 , ’ M édio ’ ) ,
( 1 , 4 , ’Bom ’ ) ,
(1 ,5 , ’ Excelente ’ ) ,
( 2 , 1 , ’ P é s s i m o ’ ) ,
(2 ,2 , ’ Regular ’ ) ,
(2 ,3 , ’ Regular ’ )
;
Seleções
• Query 01:
Seleção de todos os sitemas naturais e qual a localização (municipio/cidade) de cada sistema natural.
Algebra Relacional:
πsnome,cnome (ρ(S1(snome → 4, cnome → 7), (Sistema Natural ./Sistema Natural.Cidade Id=Cidade.ID Cidade)))
7
CRT:
CRD:
SQL:
SELECT S i s t e m a N a t u r a l . Nome , C i d a d e . Nome FROM S i s t e m a N a t u r a l INNER JOIN C i d a d e ON
( S i s t e m a N a t u r a l . C i d a d e I D = C i d a d e . ID )
• Query 02:
Contagem do número de sistemas naturais por municı́pio.
Algebra Relacional:
ρ(R1(sn id → 1, cnome → 7, cidade id → 5),
(Sistema Natural ./Sistema Natural.Cidade Id=Cidade.ID Cidade))
πcnome,total (ρ(R2(total → (γ cidade id,
count(sn .id))), R1))
CRT:
CRD:
SQL:
SELECT C i d a d e . Nome , COUNT( S i s t e m a N a t u r a l . ID ) AS t o t a l FROM S i s t e m a N a t u r a l
INNER JOIN C i d a d e ON ( S i s t e m a N a t u r a l . C i d a d e I D = C i d a d e . ID )
GROUP BY C i d a d e . ID
• Query 03:
Seleção de Espécies na cidade de Londrina.
Algebra Relacional:
8
ρ(E1(sn id → ID), Sistema Natural)
ρ(E2(ci id → ID),Cidade)
ρ(E3(snhe id → ID), Sistema Natural has Especies)
ρ(E4(es id → ID, enome → nome), Especies)
ρ(R1, (E1 ./E1.Cidade Id=E2.ID E2))
ρ(R2, (R1 ./R1.sn id=E3.Sistema Natural
ID
E3))
ρ(R3, (R2 ./R2.especies ID=E4.ID E4))
πenome (R3)
CRT:
CRD:
SQL:
SELECT E s p e c i e s . Nome
FROM E s p e c i e s
INNER JOIN S i s t e m a N a t u r a l h a s E s p e c i e s ON
( S i s t e m a N a t u r a l h a s E s p e c i e s . E s p e c i e s I D = E s p e c i e s . ID )
INNER JOIN S i s t e m a N a t u r a l ON
( S i s t e m a N a t u r a l h a s E s p e c i e s . S i s t e m a N a t u r a l I D = S i s t e m a N a t u r a l . ID )
INNER JOIN C i d a d e ON
( S i s t e m a N a t u r a l . C i d a d e I D = C i d a d e . ID )
WHERE C i d a d e . Nome = ’ L o n d r i n a ’
• Query 04:
Seleção dos nomes dos sistemas naturais e o nome da região em estados que fazem fronteiras com outros paı́ses.
Algebra Relacional:
ρ(E1(ci id → ID, cnome → nome),Cidade)
ρ(E2(es id → ID), Estado)
ρ(E3(sn id → ID), Sistema Natural)
ρ(R1, (E1 ./E1.Estado Id=E2.ID E2))
ρ(R2, (R1 ./R1.ci id=E3.Cidade ID E3))
πenome (σcnome=0 Londrina0 (R2))
9
CRT:
CRD:
SQL:
SELECT S i s t e m a N a t u r a l . Nome , E s t a d o . R e g i a o
FROM S i s t e m a N a t u r a l
INNER JOIN C i d a d e ON ( S i s t e m a N a t u r a l . C i d a d e I D = C i d a d e . ID )
INNER JOIN E s t a d o ON ( C i d a d e . E s t a d o I D = E s t a d o . ID )
WHERE E s t a d o . F a z F r o n t e i r a = t r u e
10
Exercı́cio 02
Modelo Entidade Relacionamento
Modelo Relacional
DROP TABLE I F EXISTS P r o f e s s o r ;
DROP TABLE I F EXISTS P a s s o s ;
DROP TABLE I F EXISTS E s t i l o ;
DROP TABLE I F EXISTS Video ;
DROP TABLE I F EXISTS F o t o s ;
DROP TABLE I F EXISTS E s t i l o h a s P a s s o s ;
DROP TABLE I F EXISTS E s t i l o h a s P r o f e s s o r ;
CREATE TABLE P r o f e s s o r (
11
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
Nome VARCHAR( 2 5 5 ) NULL,
PRIMARY KEY( ID )
);
CREATE TABLE P a s s o s (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
Nome VARCHAR( 2 5 5 ) NULL,
D e s c r i c a o VARCHAR( 2 5 5 ) NULL,
PRIMARY KEY( ID )
);
CREATE TABLE E s t i l o (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
Nome VARCHAR( 2 5 5 ) NULL,
PRIMARY KEY( ID )
);
CREATE TABLE Video (
P a s s o s I D INTEGER UNSIGNED NOT NULL,
D e s c r i c a o INTEGER UNSIGNED NULL,
P e r s p e c t i v a ENUM( ’Dama ’ , ’ C a v a l e i r o ’ ) NULL,
INDEX V i d e o F K I n d e x 1 ( P a s s o s I D )
);
CREATE TABLE F o t o s (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
P a s s o s I D INTEGER UNSIGNED NOT NULL,
Ordem INTEGER UNSIGNED NULL DEFAULT ’ 0 ’ ,
D e s c r i c a o VARCHAR( 2 5 5 ) NULL,
PRIMARY KEY( ID ) ,
INDEX F o t o s F K I n d e x 1 ( P a s s o s I D )
);
CREATE TABLE E s t i l o h a s P a s s o s (
E s t i l o I D INTEGER UNSIGNED NOT NULL,
P a s s o s I D INTEGER UNSIGNED NOT NULL,
PRIMARY KEY( E s t i l o I D , P a s s o s I D ) ,
INDEX E s t i l o h a s P a s s o s F K I n d e x 1 ( E s t i l o I D ) ,
INDEX E s t i l o h a s P a s s o s F K I n d e x 2 ( P a s s o s I D )
12
);
CREATE TABLE E s t i l o h a s P r o f e s s o r (
E s t i l o I D INTEGER UNSIGNED NOT NULL,
P r o f e s s o r I D INTEGER UNSIGNED NOT NULL,
PRIMARY KEY( E s t i l o I D , P r o f e s s o r I D ) ,
INDEX E s t i l o h a s P r o f e s s o r F K I n d e x 1 ( E s t i l o I D ) ,
INDEX E s t i l o h a s P r o f e s s o r F K I n d e x 2 ( P r o f e s s o r I D )
);
Seleções
• Query 01:
Seleção de todos os professores, que mechem as cadeiras com as damas, no requebrado de machicho.
Algebra Relacional:
ρ(E1(p id → ID), Pro f essor)
ρ(E2, Estilo has Pro f essor)
ρ(E3(esnome → Nome), Estilo)
ρ(R1, (E1 ./E1.ID=E2.Pro f essor ID E2))
ρ(R2, (R1 ./R1.estilo id=E3.ID E3))
π pnome (σesnome=0 Londrina0 (R2))
CRT:
CRD:
SQL:
SELECT P r o f e s s o r e s . nome FROM P r o f e s s o r e s
INNER JOIN E s t i l o h a s P r o f e s s o r e s ON ( E s t i l o h a s P r o f e s s o r e s . P r o f e s s o r I D )
INNER JOIN E s t i l o ON ( E s t i l o h a s P r o f e s s o r e s . E s t i l o I D )
WHERE E s t i l o . nome = ’ m a c h i c h o ’
• Query 02:
Seleção de paços do estilo pagode.
Algebra Relacional:
13
ρ(E1(es id → ID, esnome → Nome), Estilo)
ρ(E2, Estilo has Passos)
ρ(E3(panome → Nome), Passos)
ρ(R1, (E1 ./E1.ID=E2.Estilo ID E2))
ρ(R2, (R1 ./R1.passo id=E3.ID E3))
π panome (σesnome=0 Pagode0 (R2))
CRT:
CRD:
SQL:
SELECT P a s s o s . Nome , P a s s o s . D e s c r i c a o FROM P a s s o s
INNER JOIN E s t i l o h a s P a s s o s ON ( E s t i l o h a s P a s s o s . P a s s o I D = P a s s o s . ID )
INNER JOIN E s t i l o ON ( E s t i l o h a s P a s s o s . E s t i l o I D = E s t i l o . ID )
• Query 03:
Seleção de todos os professores de lambada e axé.
Algebra Relacional:
ρ(E1(po id → ID, ponome → Nome), Pro f essor)
ρ(E2(es id → ID, esnome → Nome), Estilo)
ρ(E3, Estilo has Pro f essor)
ρ(R1, (E1 ./E1.ID=E2.ID E2))
ρ(R2, (R1 ./R1.es id=E3.Estilo ID E3))
π ponome (σesnome=0 Pagode0 ∨esnome=0 Axe0 (R2))
CRT:
CRD:
SQL:
SELECT P r o f e s s o r e s . nome FROM P r o f e s s o r e s
INNER JOIN E s t i l o h a s P r o f e s s o r e s ON ( E s t i l o h a s P r o f e s s o r e s . P r o f e s s o r I D )
INNER JOIN E s t i l o ON ( E s t i l o h a s P r o f e s s o r e s . E s t i l o I D )
WHERE E s t i l o . nome = ’ p a g o d e ’ OR E s t i l o . nome= ’ Axé ’
14
Exercı́cio 03
Modelo Entidade Relacionamento
Modelo Relacional
DROP TABLE I F EXISTS P e s s o a ;
DROP TABLE I F EXISTS Bagagem ;
DROP TABLE I F EXISTS R e g i s t r a d a ;
DROP TABLE I F EXISTS I t e m ;
DROP TABLE I F EXISTS Medicamento ;
DROP TABLE I F EXISTS A l i m e n t o ;
CREATE TABLE P e s s o a (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
Nome VARCHAR( 2 5 5 ) NULL,
T i p o ENUM( ’ J o g a d o r e s ’ , ’ M a s s a g i s t a ’ , ’ C o z i n h e i r o ’ , ’ S e r v i ç o s G e r a i s ’ ) NULL,
PRIMARY KEY( ID )
15
);
CREATE TABLE Bagagem (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
P e s s o a I D INTEGER UNSIGNED NOT NULL,
RFID INTEGER UNSIGNED NULL,
PRIMARY KEY( ID , P e s s o a I D ) ,
INDEX Bagagem FKIndex1 ( P e s s o a I D )
);
CREATE TABLE R e g i s t r a d a (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
B a g a g e m P e s s o a I D INTEGER UNSIGNED NOT NULL,
Bagagem ID INTEGER UNSIGNED NOT NULL,
PRIMARY KEY( ID ) ,
INDEX R e g i s t r a d a F K I n d e x 1 ( Bagagem ID , B a g a g e m P e s s o a I D )
);
CREATE TABLE I t e m (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
R e g i s t r a d a I D INTEGER UNSIGNED NOT NULL,
T i p o ENUM( ’ A l i m e n t o ’ , ’ Medicamento ’ ) NULL,
Qtde FLOAT NULL,
Q t d e C o n s u m i d a FLOAT NULL,
PRIMARY KEY( ID ) ,
INDEX I t e m F K I n d e x 1 ( R e g i s t r a d a I D )
);
CREATE TABLE Medicamento (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
I t e m I D INTEGER UNSIGNED NOT NULL,
L o t e VARCHAR( 2 5 5 ) NULL,
T i p o VARCHAR( 2 5 5 ) NULL,
PRIMARY KEY( ID ) ,
INDEX Me dic ame nto FK Ind ex1 ( I t e m I D )
);
CREATE TABLE A l i m e n t o (
ID INTEGER UNSIGNED NOT NULL AUTO INCREMENT ,
I t e m I D INTEGER UNSIGNED NOT NULL,
16
T i p o VARCHAR( 2 5 5 ) NULL,
M a n t e r R e s f r i a d o BOOL NULL,
M a n t e r C o n g e l a d o BOOL NULL,
V a l i d a d e DATE NULL,
PRIMARY KEY( ID ) ,
INDEX A l i m e n t o F K I n d e x 1 ( I t e m I D )
);
Seleções
• Query 01:
Seleção da quantidade e tipo de medicamentos que será embarcada para a viagem a copa 2010.
Algebra Relacional:
πquantidade (σtipo=0 Medicamento0 (Item))
CRT:
{Medicamentos | Medicamentos ∈ Item ∧ Item.tipo =0 Medicamento0 } CRD:
hquantidadei|hRegistrada ID, Tipo, Qtde, QtdeC onsumidai ∈ Medicamentos} SQL:
SELECT I t e m . Tipo , I t e m . Qtde
FROM I t e m
WHERE I t e m . T i p o = ’ Medicamento ’
• Query 02:
Seleção da Quantidade de Massagistas da comissão técnica.
Algebra Relacional:
σ(γ total,count(tipo=0 Massagista0 )) (Pessoa)
CRT:
CRD:
SQL:
SELECT COUNT( ∗ ) FROM P e s s o a
WHERE T i p o = ’ M a s s a g i s t a ’