Acervo.estudos
Trilhas Enviar material Entrar
mapa · Intermediário

3.9 Estudo de caso: Música

Estudo de caso de um catálogo de CDs no PostgreSQL: gravadoras, CDs, músicas, faixas e autores, restrições com ALTER TABLE, autorrelacionamento e as inconsistências da carga original.

por @julio · rev. 23 set · 5 telas

Minimundo do estudo de caso

O cenário: um catálogo de CDs

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
Sete tabelas e um autorrelacionamento

O esquema

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
TabelaColunas principaisPKFK
gravadoranome, endereco, telefone, contato, url — CHAR(n)codigo_gravadora—
cdnome_cd, preco_venda NUMERIC(14,2), data_lancamento DATE, estilo, vendascodigo_cdcodigo_gravadora → gravadora
cd_indicado → cd
musicanome_musica, duracao NUMERIC(6,2)codigo_musica—
autornome_autorcodigo_autor—
faixanumero_faixa(codigo_musica, codigo_cd)codigo_musica → musica
codigo_cd → cd
musica_autor—(codigo_musica, codigo_autor)codigo_musica → musica
codigo_autor → autor
cd_categoriamenor_preco, maior_preconão tem (deveria ser codigo_categoria)—

Os nomes em maiúsculas do script (GRAVADORA, Codigo_CD) viram minúsculas no PostgreSQL, porque não estão entre aspas duplas.

Restrições depois, autorreferência e junção por faixa

Trechos de SQL que o caso ensina

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
ALTER TABLE cd
    ADD CONSTRAINT pk_cd PRIMARY KEY (codigo_cd);

ALTER TABLE cd
    ADD CONSTRAINT fk_cd_indicado
    FOREIGN KEY (cd_indicado) REFERENCES cd (codigo_cd),
    ADD CONSTRAINT ck_nao_indica_a_si
    CHECK (cd_indicado <> codigo_cd);

-- o CD 2 passa a indicar o CD 3 (os dois já existem)
UPDATE cd SET cd_indicado = 3 WHERE codigo_cd = 2;
-- em que categoria de preço cai cada CD?
SELECT c.nome_cd, c.preco_venda, k.codigo_categoria
  FROM cd c
  LEFT JOIN cd_categoria k
         ON c.preco_venda BETWEEN k.menor_preco AND k.maior_preco;

O script usa CHAR(60) para nomes. CHAR completa com espaços até o tamanho; no PostgreSQL o recomendado para texto é VARCHAR(n) ou TEXT. A duração em NUMERIC "minutos.segundos" também engana: somar 2.50 + 2.50 dá 5.00, não 5min40s. Guardar segundos (INTEGER) ou usar INTERVAL evita isso.

O script roda, mas há inconsistências

Conferindo os dados da carga

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom

⚠ O original faz UPDATE CD SET CD_INDICADO = 8 … WHERE CODIGO_CD = 8: o CD indica a si mesmo. O certo é indicar outro CD (e um CHECK (cd_indicado <> codigo_cd) impede o erro).

⚠ A música 7, "Que País é Este", tem duração 2.64. No formato minutos.segundos do resto da tabela, 64 segundos não existe. Se a ideia era 2 min e 64 s, o certo é 3.04.

⚠ O autor "Sérgio Natureza" aparece com os códigos 21 e 36. O certo é um só cadastro, referenciado pelas músicas dele.

⚠ As músicas 42 ("Trenzinho Caipira") e 43 ("Luar do Sertão") não recebem autor em MUSICA_AUTOR, enquanto Villa-Lobos (22), Ferreira Gullar (23) e Catulo da Paixão Cearense (24) ficam sem música. Faltam as linhas (42, 22), (42, 23) e (43, 24).

⚠ O CD 1, "Mais do Mesmo" (Legião Urbana), está como "Bossa Nova", e o CD 4, "A Força que Nunca Seca" (Maria Bethânia), como "Internacional". O certo seria Rock e MPB.

⚠ CD_CATEGORIA não tem chave primária, e as faixas vão só até R$ 20,00: o CD 8 (R$ 21,90) não cai em nenhuma. O certo é PRIMARY KEY (codigo_categoria) e uma faixa que cubra todos os preços.

Teste antes de seguir

Revisão rápida

clique nos círculos para abrir e fechar ramos · arraste para mover · role para dar zoom
Como representar "um CD indica outro CD"?

Com uma FK na própria tabela cd (cd_indicado → codigo_cd): um autorrelacionamento.

Por que os CDs são inseridos sem indicação e só depois recebem UPDATE?

A FK exige que o CD indicado já exista. Inserindo todos antes, as indicações podem apontar para qualquer um.

O que significa REFERENCES gravadora, sem coluna?

A FK aponta para a chave primária da tabela gravadora.

Como achar a categoria de preço de um CD?

Junção por faixa: preco_venda BETWEEN menor_preco AND maior_preco.

← Voltar ao curso