Modelagem de Dados

DER
DIAGRAMA ENTIDADE RELACIONAMENTO 

  

DLD
DIAGRAMA LOGICO DE DADOS 

  

FISICO SQL
-- SCRIPT FISICO (DDL) 

 -- 

 -- Data Criacao ...........: 30/09/2025 

 -- Autor(es) ..............: Paulo Henrique Virgilio Cerqueira 

 -- Banco de Dados .........: postgres:13-alpine 

 -- 

 -- PROJETO => 01 Base de Dados 

 -- => 03 Tabelas 

 -- ------------------------------------------------------------------------------------- 

 

 CREATE TABLE video ( 

 idVideo SERIAL NOT NULL, 

 hash CHAR(64) NOT NULL UNIQUE, 

 dataUpload TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 

 CONSTRAINT pk_video PRIMARY KEY (idVideo) 

 ); 

 

 CREATE TABLE audio ( 

 idAudio SERIAL NOT NULL, 

 idVideo INT UNIQUE NOT NULL, -- 1:1 com video 

 tempo INTERVAL, 

 formato VARCHAR(10), 

 hash CHAR(64) NOT NULL UNIQUE, 

 CONSTRAINT pk_audio PRIMARY KEY (idAudio), 

 CONSTRAINT fk_audio_video FOREIGN KEY (idVideo) REFERENCES video(idVideo) 

 ON DELETE CASCADE 

 ); 

 

 CREATE TABLE transcricao ( 

 idTranscricao SERIAL NOT NULL, 

 idAudio INT UNIQUE NOT NULL, -- 1:1 com audio 

 textoTranscricao TEXT NOT NULL, 

 dataProcessamento TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 

 sumario VARCHAR(255), 

 CONSTRAINT pk_transcricao PRIMARY KEY (idTranscricao), 

 CONSTRAINT fk_transcricao_audio FOREIGN KEY (idAudio) REFERENCES audio(idAudio) 

 ON DELETE CASCADE 

 );