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.
O cenário: um catálogo de CDs
O esquema
| Tabela | Colunas principais | PK | FK |
|---|---|---|---|
| gravadora | nome, endereco, telefone, contato, url — CHAR(n) | codigo_gravadora | — |
| cd | nome_cd, preco_venda NUMERIC(14,2), data_lancamento DATE, estilo, vendas | codigo_cd | codigo_gravadora → gravadora cd_indicado → cd |
| musica | nome_musica, duracao NUMERIC(6,2) | codigo_musica | — |
| autor | nome_autor | codigo_autor | — |
| faixa | numero_faixa | (codigo_musica, codigo_cd) | codigo_musica → musica codigo_cd → cd |
| musica_autor | — | (codigo_musica, codigo_autor) | codigo_musica → musica codigo_autor → autor |
| cd_categoria | menor_preco, maior_preco | nã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.
Trechos de SQL que o caso ensina
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.
Conferindo os dados da carga
⚠ 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.
Revisão rápida
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.