Sistema Revolucionário de Gestão de Ficheiros

(1)

Sistema Revolucionário

de

Gestão de Ficheiros

Grupo LBD09

Relatório de Especificação da Base de Dados

Versão 1.2

Fernando Joaquim Leite Pereira (ei03042@fe.up.pt) Ivo André da Rocha Marinho (ei03117@fe.up.pt)

(2)

Conteúdos

Conteúdos...2

1. Introdução...3

2. Do Esquema Conceptual ao Relacional ...4

2.1. Esquema conceptual de dados ... 4

2.2. Regras de integridade de dados ... 5

2.3 Esquema Relacional ... 6

3. Estudo de Normalização – Afinação do E.R...23

4. Afinação da base de dados ...27

4.1 Análise da quantidade de informação presente em cada entidade... 27

4.2 Principais interrogações à base de dados – Análise / Optimização... 28

4.3 Esquema físico e selecção de índices ... 30

4.4 Comparação de performances ... 35

5. Verificação das regras de Negócio...37

(3)

1. Introdução

O conteúdo do Relatório de Especificação da Base de Dados consiste num esquema relacional obtido a partir de uma actualização do esquema conceptual de dados, definido no Relatório de Especificação da Informação, no estudo aprofundado da normalização do esquema definido e a sua respectiva afinação e modelo físico.

Inicialmente apresentam-se os vários passos efectuados e os padrões usados para obter o esquema relacional através do conceptual. Através do esquema conceptual é possível verificar as associações existentes entre as várias classes e a informação nelas contida. Usando todas as teorias sobre normalização, é assim possível obter o esquema relacional e verificar a sua conformidade com a forma normal de Boyce-Codd (BCNF), sendo efectuadas todas as afinações que sejam necessárias de forma a assegurar o funcionamento pretendido.

Após um estudo da carga previsível que o sistema irá suportar, foram convenientemente escolhidos os atributos que irão ficar em índices e os respectivos tipos de índice. Uma explicação detalhada sobre as escolhas dos índices é igualmente apresentada neste relatório, baseando-se numa selecção de consultas que irão ser mais frequentemente usadas no sistema e a melhor forma encontrada para garantir respostas mais eficientes. Quanto as regras de negócio que são identificadas no Relatório de Especificação de Informação, estas são novamente apresentadas neste relatório com novas actualizações e os respectivos gatilhos criados na linguagem do SQBD usado.

Por fim é apresentado o sript de criação da base de dados contendo todo o código SQL necessário.

(4)

2. Do Esquema Conceptual ao Relacional

Com base no esquema conceptual de dados apresentado no relatório anterior, ao qual foram introduzidas algumas alterações, e em regras de integridade adicionais (que não são captadas pelo mesmo), foi construído o esquema relacional. Ambos os diagramas serão, portanto, apresentados para uma maior facilidade na análise da implementação.

2.1. Esquema conceptual de dados

(5)

2.2. Regras de integridade de dados

• RIa1: Tamanhos de ficheiros não nulos (tamanho> 0).

• RIa2: Nomes de directórios e ficheiros não nulos e únicos para cada directório. • RIa3: E-mail terá de ser válido e único, e palavra-chave entre 4 e 8 caracteres. • RIa4:Data de partilha do directório é igual ou superior à data de criação. • RIa5: Votação das entradas entre 1 e 5.

• RIa6: Upload e download de cada utilizador são restritos a valores positivos. • RIa7: As datas deverão ser inseridas automaticamente recorrendo à hora de

sistema no momento de inserção do registo na BD. • RIa8: Não serão admitidos títulos de entradas nulos. • RIa9: Não serão admitidos nomes de categorias nulos.

• RIa10: Não serão admitidas descrições de tipos de ficheiros nulos. • RIa11: Não serão admitidas extensões nulas.

• RIa12: Qualidade de um ficheiro vídeo entre 0 a 5.

• RIa13: Cada ficheiro terá de estar obrigatoriamente associado a um directório. • RIa14: Na fila de espera, cada relação ficheiro-utilizador terá obrigatoriamente

de ter um estado associado.

• RIa15: Não serão admitidos títulos de mensagens nulos. • RIa16: Não serão admitidos nomes de utilizadores nulos.

• RIa17: Data do último login de um utilizador tem de ser superior a data de registo.

• RIa18: O cod_directorio_principal na tabela Utilizador terá de pertencer ao próprio utilizador.

• RIa19: O cod_imagem_principal na tabela Utilizador terá de pertencer ao próprio utilizador.

• RIa20: O email do utilizador terá de conter um “@” e um “.” de domínio.

• RIa21: O código do directório filho tem de ser diferente do código do directório pai.

• RIa22: Se o campo partilhado na tabela Directorio for igual a true então terá de existir um registo na tabela Partilha com o mesmo cod_directorio.

(6)

2.3 Esquema Relacional

Inicialmente apresenta-se uma listagem das tabelas e respectivos atributos, com a assinalação daqueles que representam papel de chave primária (sublinhado) ou estrangeira (negrito).

2.3.1 Nomenclatura

Definiu-se um padrão de identificação das entidades, quer sejam tabelas, atributos, restrições, índices ou triggers para desta forma a automatizar a criação de nomes e por outro lado permitir o total controlo e identificação de eventos relacionados com as mesmas.

Entidade

Regra

Exemplo

Todas Palavras ligadas por underscore

Tabela Capitalizada, singular Utilizador_Partilha

Atributo comum Minúsculas, singular data_ultimo_login

Atributo chave primária cod_’nome tabela’ cod_utilizador Atributo chave externa ‘chave_referenciada’_’descrição’ cod_emissor

Chave primária ‘nome tabela’_pk Amigo_pk

Chave externa ‘nome_tabela’_’nome_campo’_fk Amigo_cod_utilizador_fk Campo único ‘nome_tabela’_’nome_campo’_unique Utilizador_email_unique Campo check ‘nome_tabela’_’nome_campo’_ck Votacao_pontuacao_ck

Trigger ‘tabela’_’nome’_valida irectório_nome_valida

Índices chave ‘nome_tabela’_pkey (por omissão) Utilizador_pkey Índices não chave ‘nome_tabela’_’nome_campo’_index

(7)

2.3.2 Tabelas e atributos

T01. Utilizador (cod_utilizador, nome, email, passwd, data_registo, data_ultimo_login,

cod_imagem_principal, cod_directorio_principal, upload, download).

T02. Amigo (cod_utilizador, cod_amigo, data).

T03. Mensagem (cod_mensagem, cod_emissor, titulo, conteúdo, data).

T04. Mensagem_Receptor (cod_mensagem, cod_receptor, visto).

T05. Directorio (cod_directorio, nome, cod_pai, cod_dono, partilhado, data_criacao,

path_interna).

T06. Partilha (cod_directorio_partilhado, nome, data).

T07. Utilizador_Partilha (cod_directorio_partilhado, cod_utilizador,

permissao_escrita, favorito).

T08. Categoria (cod_categoria, nome, descricao).

T09. Entrada (cod_entrada, data_criacao, cod_criador, cod_categoria, titulo,

descricao,num_vistos).

T10. Comentario (cod_comentario, cod_entrada, cod_comentador, descricao, data).

T11. Votacao (cod_entrada, cod_votador, pontuacao).

T12. Tipo_Ficheiro (cod_tipo_ficheiro, descricao).

T13. Extensao (cod_extensao, cod_tipo_ficheiro, nome, descricao).

T14. Ficheiro (cod_ficheiro, nome, data_insercao, tamanho, cod_directorio, cod_extensao).

T15. Entrada_Ficheiro (cod_entrada, cod_ficheiro, data, descricao).

T16. Mensagem_Anexo (cod_mensagem, cod_ficheiro).

T17. Ficheiro_Fila (cod_ficheiro, cod_utilizador, estado, posicao_fila).

T18. Imagem (cod_ficheiro_imagem, largura, altura, resolucao, formato).

T19. Audio (cod_ficheiro_audio, duracao, bitrate, codec).

T20. Arquivo (cod_ficheiro_arquivo, tamanho_real).

T21. Documento (cod_ficheiro_documento, formato).

T22. Video (cod_ficheiro_video, largura, altura, duracao, qualidade, fps, canais_audio,

(8)

2.3.3 Descrição detalhada da arquitectura das tabelas

TABELA T01

Nome: Utilizador

Descrição: Guarda informação sobre os utilizadores com acesso ao _sistema Colunas:

Nome Domínio Valor por omissão

Admite

nulos? Indexar?

cod_utilizador INTEGER Não HASH

nome VARCHAR(50) Não

email VARCHAR(50) Não HASH

passwd VARCHAR(8) Não

data_registo TIMESTAMP now() Não

data_ultimo_login TIMESTAMP Sim

cod_imagem_principal INTEGER Sim

cod_directorio_principal INTEGER Sim

upload INTEGER 0 Sim

download INTEGER 0 Sim

R.I. Chave Primária: cod_utilizador

R.I. Referencial (chaves estrangeiras):

Colunas referenciantes Tabela _referenciada Colunas referenciadas Indexar?

Cod_imagem_principal Ficheiro cod_ficheiro cod_directorio_principal Directorio cod_directorio

R.I. de unicidade (unique):

Nome Lista de colunas Indexar?

Utilizador_email_unique email

Utilizador_cod_img_principal_unique cod_imagem_principal Utilizador_cod_directorio_principal_uniq

ue cod_directorio_principal

R.I. de atributos (check):

Nome Coluna Condição SQL

Utilizador_passwd_ck passwd CHECK (LENGTH (passwd)>=4) Utilizador_download_ck download CHECK (download>=0)

Utilizador_upload_ck upload CHECK (upload>=0)

Utilizador_email_ck email CHECK(email LIKE ‘%@%.%’)

R.I. de tuplos (check):

Nome Lista de colunas Condição SQL

(9)

data_ultimo_login (data_ultimo_login>data_regist o)

TABELA T02

Nome: Amigo

Descrição: Guarda informação sobre o grupo de amigos para cada _utilizador Colunas:

Nome Domínio Valor por _omissão Admite _nulos? Indexar?

cod_utilizador INTEGER Não B-TREE

cod_amigo INTEGER Não

data TIMESTAMP now() Sim

R.I. Chave Primária: cod_utilizador, cod_amigo R.I. Referencial (chaves estrangeiras):

Colunas

referenciantes Tabela referenciada Colunas referenciadas Indexar?

Cod_utilizador Utilizador cod_utilizador

cod_amigo Utilizador cod_utilizador

Nome Lista de colunas Indexar? R.I. de atributos (check):

- - -

TABELA T03

Nome: Mensagem

Descrição: Guarda o registo de todas as mensagens enviadas pelos _utilizadores Colunas:

cod_mensagem INTEGER Não HASH

cod_emissor INTEGER Não B-TREE

titulo VARCHAR(50) Não

(10)

R.I. Chave Primária: cod_mensagem R.I. Referencial (chaves estrangeiras): Colunas

cod_emissor Utilizador cod_utilizador

- - -

TABELA T04

Nome: Mensagem_Receptor

Descrição: Guarda o registo dos destinatários de cada mensagem

Colunas:

cod_mensagem INTEGER Não

cod_receptor INTEGER Não B-TREE

visto BOOLEAN false Não

R.I. Chave Primária: cod_mensagem, cod_receptor R.I. Referencial (chaves estrangeiras):

Colunas

Cod_mensagem Mensagem cod_mensagem

cod_receptor Utilizador cod_utilizador

- - -

(11)

TABELA T05

Nome: Directorio

Descrição: Guarda o registo de todos os directórios criados no sistema

Colunas:

Nome Domínio Valor por _omissão Admite nulos? Indexar?

cod_directorio INTEGER Não HASH

cod_pai INTEGER Sim

cod_dono INTEGER Sim

B-TREE

partilhado BOOLEAN Sim

data_criacao TIMESTAMP Sim

path_interna VARCHAR(512) Não

R.I. Chave

Primária: cod_directorio R.I. Referencial (chaves estrangeiras): Colunas

cod_pai Directorio cod_directorio

cod_dono Utilizador cod_utilizador

- - -

filho_pai_valida cod_directorio, cod_pai CHECK _{(cod_directorio<>cod_pai)}

TABELA T06

Nome: Partilha

Descrição: Guarda informação dos directórios partilhados

Colunas:

(12)

R.I. Chave Primária: cod_directorio_partilhado

Colunas referenciantes Tabela _referenciada Colunas referenciadas Indexar?

cod_directorio_partilhado Directorio cod_directorio

- - -

TABELA T07

Nome: Utilizador_Partilha

Descrição: Guarda informação dos utilizadores a quem estão partilhados _{cada directorio} Colunas:

cod_directorio_partilhado INTEGER Não

permissao_escrita BOOLEAN false Não

favorito BOOLEAN false Não

R.I. Chave Primária: cod_directorio_partilhado, cod_utilizador

cod_directorio_partilhado Partilha cod_directorio_partilhado cod_utilizador Utilizador cod_utilizador

- - -

(13)

- - -

TABELA T08

Nome: Categoria

Descrição: Guarda o registo das categorias de entradas existentes no _sistema Colunas:

cod_categoria INTEGER Não

descricao VARCHAR(256) Sim

R.I. Chave Primária: cod_categoria

Colunas referenciantes Tabela referenciada Colunas referenciadas Indexar? R.I. de unicidade (unique):

- - -

TABELA T09

Nome: Entrada

Descrição: Guarda informação das entradas criadas no sistema

Colunas:

cod_entrada INTEGER Não HASH

data_criacao TIMESTAMP now() Não

cod_criador INTEGER Não

cod_categoria INTEGER Não B-TREE

titulo VARCHAR(50) Não

(14)

R.I. Chave Primária: cod_entrada R.I. Referencial (chaves estrangeiras): Colunas

cod_categoria Categoria cod_categoria

cod_criador Utilizador cod_utilizador

- - -

TABELA T10

Nome: Comentario

Descrição: Guarda o registo dos comentários efectuados a cada entrada

Colunas:

Nome Domínio Valor por omissão Admite _nulos? Indexar?

cod_comentario INTEGER Não

cod_comentador INTEGER Não

cod_entrada INTEGER Não

data TIMESTAMP now() Não B-TREE

descricao TEXT Sim

R.I. Chave

Primária: cod_comentario R.I. Referencial (chaves estrangeiras): Colunas

referenciantes Tabela referenciada Colunas referenciadas Indexar?

cod_entrada Entrada cod_entrada

cod_comentador Utilizador cod_utilizador

- - -

(15)

- - -

TABELA T11

Nome: Votacao

Descrição: Guarda o registo das votações atribuídas pelos utilizadores a _{cada entrada} Colunas:

cod_entrada INTEGER Não B-TREE

cod_votador INTEGER Não

pontuacao SMALLINT Não

R.I. Chave Primária: cod_entrada, cod_votador R.I. Referencial (chaves estrangeiras):

Colunas

cod_votador Utilizador cod_utilizador

Votacao_pontuacao_ck pontuacao CHECK (pontuacao >= 0 AND pontuacao _{<= 5)}

- - -

TABELA T12

Nome: Tipo_Ficheiro

Descrição: Guarda o registo dos tipos de ficheiros presentes no sistema

Colunas:

cod_tipo_ficheiro INTEGER Não

descricao VARCHAR(30) Não

R.I. Chave Primária: cod_tipo_ficheiro R.I. Referencial (chaves estrangeiras):

(16)

Colunas

referenciantes Tabela referenciada Colunas referenciadas Indexar? R.I. de unicidade (unique):

- - -

TABELA T13

Nome: Extensao

Descrição: Guarda a informação sobre as extensões dos ficheiros presentes _{no sistema} Colunas:

cod_extensao INTEGER Não HASH

cod_tipo_ficheiro INTEGER Sim

R.I. Chave

Primária: cod_extensao R.I. Referencial (chaves estrangeiras): Colunas

cod_tipo_ficheiro Tipo_Ficheiro cod_tipo_ficheiro

- - -

(17)

TABELA T14

Nome: Ficheiro

Descrição: Guarda informação sobre os ficheiros presentes no sistema

Colunas:

cod_ficheiro INTEGER Não HASH

data_insercao TIMESTAMP now() Sim

tamanho INTEGER Sim

cod_directorio INTEGER Não B-TREE

cod_extensao INTEGER Sim B-TREE

R.I. Chave Primária: cod_ficheiro R.I. Referencial (chaves estrangeiras): Colunas

cod_directorio Directorio cod_directorio

cod_extensao Extensao cod_extensao

Ficheiro_tamanho_ck tamanho CHECK (tamanho>0)

- - -

TABELA T15

Nome: Entrada_Ficheiro

Descrição: Guarda o registo dos ficheiros incluídos em cada entrada

Colunas:

cod_entrada INTEGER Não B-TREE

cod_ficheiro INTEGER Não B-TREE

R.I. Chave Primária: cod_entrada, cod_ficheiro R.I. Referencial (chaves estrangeiras):

(18)

Colunas

cod_ficheiro Ficheiro cod_ficheiro

- - -

TABELA T16

Nome: Mensagem_Anexo

Descrição: Guarda o registo dos ficheiros incluídos em cada mensagem

Colunas:

cod_mensagem INTEGER Não B-TREE

cod_ficheiro INTEGER Não

R.I. Chave Primária: cod_mensagem, cod_ficheiro R.I. Referencial (chaves estrangeiras):

Colunas

cod_mensagem Mensagem cod_mensagem

- - -

TABELA T17

(19)

Descrição: Guarda a informação sobre a relação entre os utilizadores e os _{ficheiros, a respectiva fila de espera e o estado actual} Colunas:

cod_ficheiro INTEGER Não B-TREE

estado SMALLINT 0 Não

posicao_fila SMALLINT 0 Sim

R.I. Chave Primária: cod_ficheiro, cod_utilizador R.I. Referencial (chaves estrangeiras):

cod_utilizador Utilizador cod_utilizador

Ficheiro_Fila_estado_ck estado CHECK (estado>=1 AND estado<=3)

- - -

TABELA T18

Nome: Imagem

Descrição: Guarda informação extra sobre os ficheiros presentes no sistema _{do tipo Imagem} Colunas:

cod_ficheiro_imagem INTEGER Não HASH

largura SMALLINT Sim

altura SMALLINT Sim

resolucao SMALLINT Sim

formato VARCHAR(20) Sim

R.I. Chave Primária: cod_ficheiro_imagem R.I. Referencial (chaves estrangeiras): Colunas

(20)

cod_ficheiro_imagem Ficheiro cod_ficheiro

- - -

TABELA T19

Nome: Audio

Descrição: Guarda informação extra sobre os ficheiros presentes no sistema _{do tipo Audio} Colunas:

cod_ficheiro_audio INTEGER Não HASH

duracao INTEGER Sim

bitrate SMALLINT Sim

codec VARCHAR(20) Sim

R.I. Chave

Primária: cod_ficheiro_audio R.I. Referencial (chaves estrangeiras): Colunas

cod_ficheiro_audio Ficheiro cod_ficheiro

- - -

TABELA T20

(21)

Descrição: Guarda informação extra sobre os ficheiros presentes no sistema _{do tipo Arquivo} Colunas:

cod_ficheiro_arquivo INTEGER Não HASH

tamanho_real INTEGER Sim

R.I. Chave Primária: cod_ficheiro_arquivo R.I. Referencial (chaves estrangeiras): Colunas

cod_ficheiro_arquivo Ficheiro cod_ficheiro

- - -

TABELA T21

Nome: Documento

Descrição: Guarda informação extra sobre os ficheiros presentes no _{sistema do tipo Documento} Colunas:

cod_ficheiro_documento INTEGER Não HASH

formato VARCHAR(30) Sim

R.I. Chave Primária: cod_ficheiro_documento R.I. Referencial (chaves estrangeiras):

cod_ficheiro_documento Ficheiro cod_ficheiro

(22)

- - -

TABELA T22

Nome: Video

Descrição: Guarda informação extra sobre os ficheiros presentes no sistema _{do tipo Video} Colunas:

cod_ficheiro_video INTEGER Não HASH

largura SMALLINT Sim

altura SMALLINT Sim

duracao INTEGER Sim

qualidade SMALLINT Sim

fps SMALLINT Sim

canais_audio SMALLINT Sim

codec VARCHAR(20) Sim

lingua_audio VARCHAR(20) Sim

lingua_legenda VARCHAR(20) Sim

R.I. Chave Primária: cod_ficheiro_video R.I. Referencial (chaves estrangeiras): Colunas

cod_ficheiro_video Ficheiro cod_ficheiro

Vídeo_qualidade_ck qualidade CHECK (qualidade>=0 AND _{qualidade<=5)}

(23)

3. Estudo de Normalização – Afinação do E.R.

De forma a garantir que futuramente não irão existir problemas de redundância e de repetição de informação devido as dependências funcionais entre atributos das tabelas, foi efectuado um estudo e uma análise cuidada a fim de verificar se efectivamente todas as tabelas estavam em conformidade com a forma normal de Boyce-Codd (BCNF).

Tabela T01 – Utilizador – BCNF Nome: Utilizador

Chaves: cod_utilizador, email, cod_imagem_principal, cod_directorio_principal Dependências Funcionais:

cod_utilizador nome, email, passwd, data_registo, data_ultimo_login, cod_imagem_principal, cod_directorio_principal, upload, download email cod_utilizador, nome, passwd, data_registo, data_ultimo_login, cod_imagem_principal, cod_directorio_principal, upload, download

cod_directorio_principal cod_utilizador, nome, email, passwd, data_registo, data_ultimo_login, cod_imagem_principal, upload, download

cod_imagem_principal cod_utilizador, nome, email, passwd, data_registo, data_ultimo_login, cod_directorio_principal, upload, download

Tabela T02 – Amigo – BCNF Nome: Amigo

Chaves: cod_utilizador, cod_amigo Dependências Funcionais:

cod_utilizador, cod_amigo data

Tabela T03 – Mensagem – BCNF Nome: Mensagem

Chaves: cod_mensagem

Dependências Funcionais:

cod_mensagem cod_emissor, titulo, conteúdo, data

Tabela T04 – Mensagem_Receptor – BCNF Nome: Mensagem_Receptor

Chaves: cod_mensagem, cod_receptor Dependências Funcionais:

cod_mensagem, cod_receptor visto

(24)

Nome: Directorio Chaves: cod_directorio

cod_directorio nome, cod_pai, cod_dono, partilhado, data_criacao, path_interna

Tabela T06 – Partilha – BCNF Nome: Partilha

Chaves: cod_directorio_partilhado Dependências Funcionais:

cod_directorio_partilhado nome, data

Tabela T07 – Utilizador_Partilha – BCNF Nome: Utilizador_Partilha

Chaves: cod_directorio_partilhado, cod_utilizador Dependências Funcionais:

cod_directorio_partilhado, cod_utilizador permissao_escrita, favorito

Tabela T08 – Categoria – BCNF Nome: Categoria

Chaves: cod_categoria

cod_categoria nome, descricao

Tabela T09 – Entrada – BCNF Nome: Entrada

Chaves: cod_entrada

cod_entrada data_criacao, cod_criador, cod_categoria, titulo, descricao, num_vistos

Tabela T10 – Comentario – BCNF Nome: Comentario

Chaves: cod_comentario

cod_comentario cod_entrada, cod_comentador, descricao, data

(25)

Nome: Votacao

Chaves: cod_entrada, cod_votador Dependências Funcionais:

cod_entrada, cod_votador pontuacao

Tabela T12 – Tipo_Ficheiro – BCNF Nome: Tipo_Ficheiro Chaves: cod_tipo_ficheiro Dependências Funcionais: cod_tipo_ficheiro descricao Tabela T13 – Extensão – BCNF Nome: Extensao Chaves: cod_extensao Dependências Funcionais:

cod_extensao cod_tipo_ficheiro, nome, descricao

Tabela T14 – Ficheiro – BCNF Nome: Ficheiro

Chaves: cod_ficheiro

cod_ficheiro nome, data_insercao, tamanho, cod_directorio, cod_extensao

Tabela T15 – Entrada_Ficheiro – BCNF Nome: Entrada_Ficheiro

Chaves: cod_entrada, cod_ficheiro Dependências Funcionais:

cod_entrada, cod_ficheiro data, descricao

Tabela T16 – Mensagem_Anexo – BCNF Nome: Mensagem_Anexo

Chaves: cod_mensagem, cod_ficheiro Dependências Funcionais:

cod_mensagem, cod_ficheiro

(26)

Nome: Ficheiro_Fila

Chaves: cod_utilizador, cod_ficheiro Dependências Funcionais:

cod_utilizador, cod_ficheiro estado, posicao_fila

Tabela T18 – Imagem – BCNF Nome: Imagem

Chaves: cod_ficheiro_imagem

cod_ficheiro_imagem largura, altura, resolucao, formato

Tabela T19 – Audio – BCNF Nome: Audio

Chaves: cod_ficheiro_audio

cod_ficheiro_audio duracao, bitrate, codec

Tabela T20 – Arquivo – BCNF Nome: Arquivo Chaves: cod_ficheiro_arquivo Dependências Funcionais: cod_ficheiro_arquivo tamanho_real Tabela T21 – Documento – BCNF Nome: Documento Chaves: cod_ficheiro_documento Dependências Funcionais: cod_ficheiro_documento formato Tabela T22 – Vídeo – BCNF Nome: Video Chaves: cod_ficheiro_video Dependências Funcionais:

cod_ficheiro_video largura, altura, duracao, qualidade, fps, canais_audio, codec, lingua_audio, lingua_legenda

(27)

4. Afinação da base de dados

Após a obtenção do esquema lógico da base de dados, será necessário tomar em consideração o seu desempenho quando submetida à carga de utilização esperada. Será feita uma breve análise à quantidade relativa de acessos (leituras, alterações, remoções) e conforme esses valores procurar-se-á optimizar o desempenho do sistema de dados através da introdução de índices.

4.1 Análise da quantidade de informação presente em cada

entidade

O sistema deverá estar preparado para as expectativas mais arrojadas. Neste contexto pressupõe-se que o projecto poderá obter divulgação internacional e admita-se que uma em cada 1000 pessoas do mundo com ligação à internet utilizará o software. O resultado será 900.000 utilizadores. Por margem de segurança será admitido 1 milhão.

Repercussões no número de registos: T01 – Utilizadores: 1.000.000

T02 – Amigo: Media de 2 amigos por utilizador: 1.000.000 * 2 = 2.000.000 T03 – Mensagem: Media de 2 mensagens preservadas por emissor: 2.000.000 T04 – Mensagem_receptor: 1.5 receptores por cada mensagem: 3.000.000 T05 – Directório: Media de 5 directórios por utilizador: 5.000.000

T06 – Partilha: 2 partilhas por utilizador: 2.000.000

T07 – Utilizador_Partilha: 2 utilizadores por partilha: 4.000.000 T08 – Categoria: Independente: até 100 categorias

T09 – Entrada: 5 entradas por utilizador: 5.000.000 T10 – Comentário: 2 comentários por entrada: 10.000.000 T11 – Votacao: 10 votos por utilizador: 10.000.000

T12 – Tipo_Ficheiro: Independente: até 100 tipos T13 – Extensão: Independente: até 1000 extensões

T14 – Ficheiro: Media de 3 ficheiros por directório: 15.000.000 T15 – Entrada_Ficheiro: 1.2 ficheiros por entrada: 6.000.000 T16 – Mensagem_anexo: 1 anexo em cada 5 mensagens: 400.000 T17 – Ficheiro_Fila: 20 registos por utilizador: 20.000.000 T18 – Imagem: 5% dos ficheiros: 750.000

T19 – Audio: 20% dos ficheiros: 3.000.000 T20 – Arquivo: 30% dos ficheiros: 4.500.000 T21 – Documento: 3% dos ficheiros: 450.000 T22 – Video: 40% dos ficheiros: 6.000.000

Numa primeira análise, apercebemo-nos de que realmente o centro do programa, como o registo de ficheiros e a respectiva fila de prioridade, terá a maior utilização devendo por isso ser alvo de maior cuidado para possibilitar respostas em tempo útil.

(28)

4.2 Principais interrogações à base de dados – Análise /

Optimização

Q01 Login

Pesquisa-se pelo campo email na tabela Utilizador. Nada a optimizar.

Q02 Ver o grupo de amigos

Pesquisa pelo campo cod_utilizador, obtenção do código de amigos, junção do resultado com a tabela de utilizadores para mostrar alguma informação.

A junção antes de seleccionada a lista de amigos daria origem a uma tabela com todos os registos de utilizadores e apenas posteriormente seriam seleccionados os amigos.

O último esquema representa a melhor opção, traduzindo-se numa consulta SQL mais eficiente:

SELECT “Utilizador.nome” FROM “Amigo”, ”Utilizador”

WHERE Amigo.cod_utilizador=333 AND Utilizador.cod_utilizador=Amigo.cod_amigo

Q03 Mensagens enviadas pelo utilizador

Pesquisa de mensagens enviadas utilizando o atributo cod_emissor na tabela Mensagem. Nada a optimizar.

Q04 Mensagem recebidas

Pesquisa de mensagens recebidas, pesquisando na tabela Mensagem_Receptor os códigos das mensagens com acesso através do cod_receptor e com cada um destes os detalhes da mensagem na tabela Mensagem.

SELECT Mensagm.cod_mensagem, Mensagem.titulo FROM “Mensagem_Receptor”, “Mensagem”

WHERE Mensagem_Receptor=333 AND

Mensagem_Receptor.cod_mensagem=Mensagem.cod_mensagem

Q05 Mostrar todos os directórios e ficheiros dentro de um determinado directório

Esta interrogação é fulcral para o sucesso do sistema. A ordem de aplicação das restrições na tabela Directorio será determinada pela ordem dos campos do índice, ou

nome cod_amigo = cod_utl cod_utl=333 Utilizador Amigo nome amigo.cod_utl=333 cod_amigo = cod_utl Amigo Utilizador

(29)

seja cod_utilizador, cod_pai. Após obtenção dos mesmos apenas será necessário juntar aos resultados (com a mesmo método) dos ficheiros.

Q06 Ver directórios partilhados a um dado utilizador

Faz-se a selecção dos directórios partilhados para um dado utilizador na tabela Utilizador_Partilha, mostrando posteriormente detalhes sobre a partilha. Select idêntico ao ilustrado no esquema, sendo conveniente seleccionar 1º os registos de partilhas para esse utilizador.

Q07 Ver a que utilizadores esta partilhado um determinado directório

Selecção na tabela Utilizador_Partilha dos códigos dos utilizadores com acesso a um dado código de directório. O resultado dessa mesma selecção será utilizado para obter informação como o nome dos utilizadores.

Q08 Mostrar as entradas das categorias e assinalar as que são novas

Selecção na tabela Entrada dos códigos das entradas que pertençam a uma determinada categoria. Serão então visualizadas todas as características das entradas e verificado se a entrada é nova ou contêm comentários novos ao utilizador.

Q09 Mostrar conteúdo da entrada e os respectivos ficheiros

Selecção de uma determinada entrada na tabela Entrada e retorno das principais características e conteúdos. Ligar então a entrada a tabela Entrada_Ficheiro através do cod_entrada e listar todos os ficheiros que foram incluídos.

Q10 Listar comentários de uma entrada

Selecção na tabela Comentário de todos os registos que tenham como cod_entrada o código respectivo a entrada que se pretende visualizar.

Q11 Mostrar pontuação de cada entrada

Seleccionar na tabela Votação uma média dos registos em que o cod_entrada seja igual a entrada pretendida.

Q12 Listar ficheiros em fila de espera para um utilizador

Selecção de todos os registos da tabela Ficheiro_Fila em que o cod_utilizador seja igual ao pretendido, e através da consulta será possível visualizar o estado e a respectiva posição na fila de espera em caso de transferência.

Q13 Pesquisa de ficheiros que estejam partilhados

Selecção na tabela Ficheiro de todos os ficheiros cujos cod_ficheiro exista na tabela Entrada_Ficheiro e que obedeçam as restrições impostas na pesquisa.

Q14 Listagem fila de espera para ficheiros

Selecção de todos os registos da tabela Ficheiro_Fila em que o cod_ficheiro seja igual ao pretendido, e através da consulta será possível visualizar o conjunto de utilizadores da fila e os respectivos estados e posições.

Q15 Listar os ficheiros de uma determinada mensagem

Selecção na tabela Mensagem_Ficheiro de todos os registos que tenham o cod_mensagem pretendido, permitindo visualizar o grupo de ficheiros incluídos nessa mensagem.

(30)

4.3 Esquema físico e selecção de índices

Agora que conhecidas as principais interrogações e com base na quantidade de informação que cada uma poderá ter que manipular, serão procurados índices que melhorem o processo de manipulação dos dados.

Será apresentada para cada interrogação, caso necessite, o mecanismo encontrado para aumentar a sua eficiência.

Query Q01

Nome: Login

Tabela Afectada: Utilizadores

Colunas de pesquisa: email

Elevado numero de registos: Sim

Razões adicionais para indexar

Serviço crucial, utilizado muitas vezes.

Devolve conjunto de tuplos? Não

Solução: Index hash no campo email

Cluster? Não

Query Q02

Nome: Acesso aos amigos

Tabela Afectada: amigos

Colunas de pesquisa: cod_utilizador

-

Devolve conjunto de tuplos? Sim

Solução: Index B+Tree no campo email cod_utilizador

Cluster? Sim, acesso melhorado aos registos se contiguos

Outras

optimizações:

Melhorar acesso à informação dos utilizadores através do código (Tabela Utilizador). Registo único – HASH.

Útil para grande parte das queries do sistema!

Query Q03

Nome: Listar mensagens enviadas

Tabela Afectada: Mensagem

Colunas de pesquisa: cod_emissor

Serviço utilizado muitas vezes.

(31)

Cluster? Sim

Outras

optimizações:

Aceder ao conteúdo de uma mensagem:

O acesso a uma mensagem via cod_mensagem deverá ser acelerado dado que o serviço será utilizado para qualquer espécie de mensagem. Registo único – Hash.

Query Q04

Nome: Listar mensagens recebidas

Tabela Afectada: Mensagem_Receptor

Colunas de pesquisa: cod_receptor

Elevadíssimo número de registos, devolvendo vários tuplos que não devem estar dispersos

Solução: Índex B+Tree no campo cod_receptor

Cluster? Sim

Query Q05a

Nome: Listar directórios

Tabela Afectada: Directorio

Colunas de pesquisa: cod_utilizador,cod_pai

Caso de extrema utilização, convém resultados ordenados por nome

Solução: Index B+Tree para (cod_utilizador, cod_pai, nome)

Cluster? Sim, obrigando à coesão e ordenação do tuplos

Outras

optimizações:

O acesso à informação de um directório será efectuado segundo o seu código. Sendo esta pesquisa efectuada para cada directório visível, é crucial o seu desempenho. Registo único – Hash.

Query Q05b

Nome: Listar Ficheiros

Tabela Afectada: Ficheiros

Colunas de pesquisa: cod_directorio

Caso de extrema utilização, bastantes resultados, convém ordenados por nome

(32)

Solução: Índex B+Tree para (cod_directorio, nome)

Outras

optimizações:

O acesso à informação dos ficheiros será efectuado segundo o seu código. Desempenho crucial.

Registo único – Hash.

Tabelas afectadas: Ficheiro e todas contendo especificações de ficheiros (Áudio, Vídeo,...)

Query Q06

Nome: Listar directórios com acesso para um utilizador

Tabela Afectada: utilzador_partilha

Serviço base de sistema

Solução: Índex B+Tree para cod_utilzador

Cluster? Sim

Outras

optimizações:

O acesso à informação de partilha de um directório será efectuado segundo o seu código (tabela Partilha). Esta pesquisa é efectuada para cada directório partilhado visível. Registo único – Hash.

Query Q08

Nome: Listar Entradas por categoria

Tabela Afectada: Entradas

Colunas de pesquisa: cod_categoria

Caso de extrema utilização, a base d partilha pública

Solução: Índex B+Tree para cod_categoria

Cluster? Sim

Query Q09

Nome: Listar ficheiros de cada entrada

Tabela Afectada: Entrada_Ficheiro

Colunas de pesquisa: cod_entrada

(33)

Solução: Índex B+Tree para cod_entrada

Cluster? Sim.

Query Q10

Nome: Listar comentários de cada entrada

Tabela Afectada: Comentario

Colunas de pesquisa: cod_entrada

Caso de extrema utilização, resultados ordenados por data

Solução: Índex B+Tree para (cod_entrada, data)

Query Q11

Nome: Mostrar / inserir Pontuação

Tabela Afectada: Votacao

Colunas de pesquisa: cod_entrada, cod_utilizador

Caso polémico: utilização moderada/alta. Apenas devolve 1 tuplo mas Hash não permitido. Indexar com B-Tree de 2 campos pode não compensar o espaço em relação à tabela. Indexar por um campo diminuirá esse espaço mantendo prestação suficiente?

Devolve conjunto de tuplos? Não

Solução: Index B-Tree cod_entrada

Cluster? Sim, acelera pesquisa do registo pelo cod_utilizador

Query Q12

Nome: Listar ficheiros em fila de espera

Tabela Afectada: Ficheiro_Fila

Tabela de maior tamanho com extrema utilização.

Solução: Index B-Tree cod_utilizador

(34)

Query Q13a

Nome: Pesquisa de ficheiros

Tabela Afectada: Ficheiro

Colunas de pesquisa: nome, cod_extensao

A funcionalidade de pesquisa deverá ser altamente utilizada. Não fará sentido criar índices de pesquisa para todos os atributos e evitar índices duplos devido ao tamanho necessário. Indexar no cod_extensao parece sensato já que as pesquisas comuns por tipo de ficheiro originarão várias pesquisas neste atributo.

Solução: Index B+Tree cod_extensao

Cluster? Sim.

Outras optimizações: Indexar o código das extensões (Tabela extensões) como hash irá introduzir algum desempenho adicional para procurar o tipo de ficheiro em listagens extensas.

Query Q13b

Nome: Pesquisa entradas através ficheiros contidos

Tabela Afectada: Entrada_Ficheiro

Colunas de pesquisa: cod_ficheiro

A pesquisa de um código numa tabela tão grande poderá afectar gravemente o desempenho da operação. Dado que irão existir poucas repetições, o HASH poderia ser uma boa alternativa mas dada a estabilidade superior do B+Tree optou-se por este.

Devolve conjunto de tuplos? Sim, embora pequenos

Solução: Index B+Tree cod_ficheiro

Cluster? Nao

Outras

optimizações:

O acesso à informação da entrada será efectuado segundo o seu código (tabela Entrada). É comparado um Registo inequívoco – logo índice Hash em cod_entrada.

Query Q14

Nome: Listagem fila de espera para ficheiros

Tabela Afectada: Ficheiro_Fila

Colunas de pesquisa: cod_ficheiro

Esta funcionalidade não deverá uma utilização muito elevada mas reflectindo sobre o número de registos, uma pesquisa lenta nesta tabela poderia atrasar os acessos mais comuns a tabela, fulcrais no funcionamento da aplicação como Q11.

(35)

Solução: Index B+Tree cod_ficheiro

Cluster? Não

Query Q15

Nome: Listar os ficheiros de uma mensagem

Tabela Afectada: Mensagem_Anexo

Colunas de pesquisa: cod_mensagem

Funcionalidade com bastante utilização

Solução: Index B+Tree cod_mensagem

Cluster? Sim, agrupando os anexos por mensagem

4.4 Comparação de performances

Após aplicarmos todo o esforço no aumento de desempenho da base de dados,

tentaremos analisar o impacto de tais optimizações no sistema, com valores relativos. Assim imagina-se a base de dados no seu pleno estado de execução. A uma determinada altura um utilizador pesquisa por todos os ficheiros de Vídeo aos quais tem acesso via área partilhada.

SELECT ficheiro.nome, utilizador.nome FROM Tipo_Ficheiro, Extensoes, Ficheiro WHERE Tipo_Ficheiro.nome=’imagem’.

AND Tipo_Ficheiro.cod_tipo=Extensao.cod_tipo AND Extensao.cod_extensao=Ficheiro.cod_extensao

BD sem índices.

1a) O resultado seria o acesso aos tipos ficheiro até encontrar o procurado e obter o seu

código. Um sector de disco deveria ser suficiente.

1b) Pesquisar todos os registos de extensões com cod_tipo igual ao obtido em a) e obter

o código de extensão. Seria necessário percorrer todos os registos e esperar que o SGBD não saltasse entre blocos de disco. Numa perspectiva optimista aceder-se-ia a 3 blocos*10ms.

1c) Para cada código de extensão pesquisar todos os ficheiros que lhe obedecem.

Para cada extensão será necessário percorrer todos os ficheiros e testar se a extensão equivale. Seriam 500*12.000.000 comparações e muitos acessos ao disco.

(36)

BD com Índices 2a) equivalente a 1a)

2b) Pesquisar todos os registos de extensões com cod_tipo para obter_cod_extensões.

O acesso ao primeiro match, dado que se trata de uma B-Tree é de ordem O(1.5), o k é bastante rápido, sendo que a clusterização leva que todos os seguintes matches se encontram consecutivos, sendo portanto provável apenas a leitura de um bloco.

2c) Para cada código de extensão, será pesquisado o grupo de ficheiros relacionados.

Através da indexação em arvore B+Tree o acesso ao ficheiro será também rápido na medida em que para cada extensão obter cada ficheiro é de ordem O(1.5), não acedendo a blocos que não contêm entradas não correspondentes.

Pena não estar clusterizado para poupar leituras de disco mas esta opção já fora usada para o campo cod_directório, bem mais usado.

(37)

5. Verificação das regras de Negócio

No capítulo 2.3.3, podem ser visualizadas as regras de integridade de chave primária, chave estrangeira, UNIQUE, CHECK de domínio, CHECK de atributo e CHECK de tuplo.

A seguir são apresentadas as verificações, restrições e os gatilhos que implementam as regras do negócio enunciadas no REI ainda não tratadas.

Nome Descrição

directorio_nome_valida Implementa Regra de negócio RIa2a

Expressão SQL

CREATE FUNCTION directorio_nome_valida() RETURNS "trigger" AS '

BEGIN

IF (SELECT COUNT(*) FROM "Directorio" WHERE cod_pai=NEW.cod_pai AND nome=NEW.nome)>0 THEN

RAISE EXCEPTION ''O nome do directorio que escolheu ja existe''; END IF;

RETURN NEW; END;'

LANGUAGE 'plpgsql';

CREATE TRIGGER directorio_nome_valida BEFORE INSERT OR UPDATE ON "Directorio"

FOR EACH ROW EXECUTE PROCEDURE directorio_nome_valida();

ficheiro_nome_valida Implementa Regra de negócio RIa2b

Expressão SQL

CREATE FUNCTION ficheiro_nome_valida() RETURNS "trigger" AS '

BEGIN

IF (SELECT COUNT(*) FROM "Ficheiro" WHERE

cod_directorio=NEW.cod_directorio AND nome=NEW.nome)>0 THEN RAISE EXCEPTION ''O nome do ficheiro que escolheu ja existe''; END IF;

RETURN NEW; END;'

LANGUAGE 'plpgsql';

CREATE TRIGGER ficheiro_nome_valida BEFORE INSERT OR UPDATE ON "Ficheiro"

(38)

ficheiro_nome_valida Implementa Regra de negócio RIa4

Expressão SQL

CREATE FUNCTION partilha_data_valida() RETURNS "trigger" AS '

BEGIN

IF (SELECT data_criacao FROM "Directorio" WHERE cod_directorio=NEW.cod_directorio_partilhado)>NEW.data THEN

RAISE EXCEPTION ''A data de partilha não pode ser superior a data de criacao'';

END IF;

RETURN NEW; END;'

LANGUAGE 'plpgsql';

CREATE TRIGGER partilha_data_valida BEFORE INSERT OR UPDATE ON "Partilha"

FOR EACH ROW EXECUTE PROCEDURE partilha_data_valida();

criar_dir_home Atribui um directório principal a um utilizador aquando do seu _registo

Expressão SQL

declare

utilizador integer; BEGIN

utilizador = NEW.cod_utilizador;

insert into "Directorio" (cod_pai, cod_dono, nome) values (NULL,NEW.cod_utilizador, '_raiz');

NEW.cod_directorio_principal := (select cod_directorio from "Directorio" where cod_dono=utilizador);

RETURN NEW; END;

dir_principal_valida Implementa Regra de negócio RIa18

Expressão SQL

CREATE FUNCTION dir_principal_valida() RETURNS "trigger" AS '

BEGIN

IF (SELECT cod_dono FROM "Directorio" WHERE

cod_directorio=NEW.cod_directorio_principal)<>NEW.cod_utilizador THEN RAISE EXCEPTION ''O Directorio principal escolhido nao pertence ao utilizador em questao'';

(39)

END IF;

RETURN NEW; END;'

LANGUAGE 'plpgsql';

CREATE TRIGGER dir_principal_valida BEFORE INSERT OR UPDATE ON "Utilizador"

FOR EACH ROW EXECUTE PROCEDURE dir_principal_valida();

imagem_principal_valida Implementa Regra de negócio RIa19

Expressão SQL

CREATE FUNCTION imagem_principal_valida() RETURNS "trigger" AS '

BEGIN

IF (SELECT "Directorio".cod_dono FROM "Directorio", "Ficheiro" WHERE "Ficheiro".cod_ficheiro=NEW.cod_imagem_principal AND

"Ficheiro".cod_directorio="Directorio".cod_directorio)<>NEW.cod_utilizador THEN RAISE EXCEPTION ''A Imagem principal escolhida nao pertence ao utilizador em questao'';

END IF;

RETURN NEW; END;'

LANGUAGE 'plpgsql';

CREATE TRIGGER imagem_principal_valida BEFORE INSERT OR UPDATE ON "Utilizador"

FOR EACH ROW EXECUTE PROCEDURE imagem_principal_valida();

partilha_valida Implementa Regra de negócio RIa22 no caso de insert

Expressão SQL

CREATE FUNCTION partilha_valida() RETURNS "trigger" AS '

declare

directorio integer; BEGIN

directorio = NEW.cod_directorio_partilhado;

UPDATE "Directorio" SET partilhado=true WHERE cod_directorio=directorio; RETURN NEW;

END;'

LANGUAGE 'plpgsql';

CREATE TRIGGER partilha_valida AFTER INSERT ON "Partilha" FOR EACH ROW EXECUTE PROCEDURE partilha_valida();

(40)

partilha_valida2 Implementa Regra de negócio RIa22 para o caso de delete

Expressão SQL

CREATE FUNCTION partilha_valida2() RETURNS "trigger" AS '

declare

directorio integer; BEGIN

directorio = OLD.cod_directorio_partilhado;

UPDATE "Directorio" SET partilhado=false WHERE cod_directorio=directorio; RETURN NEW;

END;'

LANGUAGE 'plpgsql';

CREATE TRIGGER partilha_valida2 AFTER DELETE ON "Partilha" FOR EACH ROW EXECUTE PROCEDURE partilha_valida2();

(41)

6. Anexo: Script de criação da BD do projecto

-- TABLES

CREATE TABLE "Utilizador" (

cod_utilizador serial NOT NULL, upload integer DEFAULT 0,

download integer DEFAULT 0,

cod_directorio_principal integer, nome character varying(50) NOT NULL, email character varying(50) NOT NULL, passwd character varying(8) NOT NULL,

data_registo timestamp without time zone DEFAULT now() NOT NULL, data_ultimo_login timestamp with time zone,

cod_imagem_principal integer,

CONSTRAINT "Utilizador_download_ck" CHECK ((download >= 0)), CONSTRAINT "Utilizador_email_ck" CHECK (((email)::text ~~ '%@%.%'::text)),

CONSTRAINT "Utilizador_passwd_ck" CHECK ((length((passwd)::text) >= 4)),

CONSTRAINT "Utilizador_upload_ck" CHECK ((upload >= 0)),

CONSTRAINT utilizador_last_login_check CHECK ((data_ultimo_login > (data_registo)::timestamp with time zone))

);

CREATE TABLE "Directorio" (

cod_directorio serial NOT NULL, cod_pai integer,

cod_dono integer,

partilhado boolean DEFAULT false, nome character varying(30) NOT NULL,

data_criacao timestamp with time zone DEFAULT now(), path_interna character varying(512) NOT NULL,

CONSTRAINT filho_pai_valida CHECK ((cod_directorio <> cod_pai)) );

CREATE TABLE "Mensagem" (

cod_mensagem serial NOT NULL, conteudo text,

data timestamp with time zone DEFAULT now(), cod_emissor integer NOT NULL,

titulo character varying(50) NOT NULL );

CREATE TABLE "Mensagem_Receptor" ( cod_mensagem integer NOT NULL, cod_receptor integer NOT NULL,

visto boolean DEFAULT false NOT NULL );

CREATE TABLE "Mensagem_Anexo" ( cod_mensagem integer NOT NULL, cod_ficheiro integer NOT NULL );

CREATE TABLE "Ficheiro" (

(42)

data_insercao timestamp with time zone DEFAULT now(), tamanho integer,

cod_directorio integer NOT NULL, cod_extensao integer,

nome character varying(50) NOT NULL,

CONSTRAINT "Ficheiro_tamanho_ck" CHECK ((tamanho > 0)) );

CREATE TABLE "Amigo" (

cod_utilizador integer NOT NULL, cod_amigo integer NOT NULL,

data timestamp with time zone DEFAULT now() );

CREATE TABLE "Categoria" (

cod_categoria serial NOT NULL,

nome character varying(20) NOT NULL, descricao character varying(256) );

CREATE TABLE "Entrada" (

cod_entrada serial NOT NULL,

data_criacao timestamp with time zone DEFAULT now() NOT NULL, cod_criador integer NOT NULL,

cod_categoria integer NOT NULL,

titulo character varying(50) NOT NULL, descricao character varying(256), num_vistos integer DEFAULT 0 );

CREATE TABLE "Votacao" (

cod_entrada integer NOT NULL, cod_votador integer NOT NULL, pontuacao smallint NOT NULL,

CONSTRAINT "Votacao_pontuacao_ck" CHECK (((pontuacao >= 0) AND (pontuacao <= 5)))

);

CREATE TABLE "Comentario" (

cod_comentario serial NOT NULL, cod_entrada integer NOT NULL, cod_comentador integer NOT NULL, descricao text,

data timestamp with time zone DEFAULT now() NOT NULL );

CREATE TABLE "Entrada_Ficheiro" ( cod_entrada integer NOT NULL, cod_ficheiro integer NOT NULL,

data timestamp with time zone DEFAULT now(), descricao character varying(256)

);

CREATE TABLE "Ficheiro_Fila" ( cod_ficheiro integer NOT NULL, cod_utilizador integer NOT NULL, estado smallint DEFAULT 0 NOT NULL, posicao_fila smallint DEFAULT 0,

CONSTRAINT "Ficheiro_Fila_estado_ck" CHECK (((estado >= 1) AND (estado <= 3)))

(43)

CREATE TABLE "Partilha" (

cod_directorio_partilhado integer NOT NULL, nome character varying(50) NOT NULL,

data timestamp with time zone DEFAULT now() );

CREATE TABLE "Utilizador_Partilha" (

cod_directorio_partilhado integer NOT NULL, cod_utilizador integer NOT NULL,

permissao_escrita boolean DEFAULT false NOT NULL, favorito boolean DEFAULT false NOT NULL

);

CREATE TABLE "Extensao" (

cod_extensao serial NOT NULL, cod_tipo_ficheiro integer,

nome character varying(12) NOT NULL, descricao character varying(20) );

CREATE TABLE "Imagem" (

cod_ficheiro_imagem integer NOT NULL, largura smallint,

altura smallint, resolucao smallint,

formato character varying(20) );

CREATE TABLE "Audio" (

cod_ficheiro_audio integer NOT NULL, duracao integer,

bitrate smallint,

codec character varying(20) );

CREATE TABLE "Arquivo" (

cod_ficheiro_arquivo integer NOT NULL, tamanho_real integer

);

CREATE TABLE "Documento" (

cod_ficheiro_documento integer NOT NULL, formato character varying(30)

);

CREATE TABLE "Video" (

cod_ficheiro_video serial NOT NULL, largura smallint, altura smallint, duracao integer, qualidade smallint, fps smallint, canais_audio smallint,

codec character varying(20),

lingua_audio character varying(20), lingua_legenda character varying(20),

CONSTRAINT "Video_qualidade_ck" CHECK (((qualidade >= 0) AND (qualidade <= 5)))