Capítulo 5
4-Modelo Fisico
Introdução
O modelo conceitual é usado como o primeiro passo para a criação do Banco de Dados. Ele organiza as ideias iniciais e construi um modelo geral, com entidades, relacionamentos e atributos. Não está associado a SGBDs ou tipos de banco.
O modelo lógico descreve a estrutura do banco de dados, conforme vista pelo usuário do SGBD. Nesse modelo já temos que antentar para o tipo de banco de dados que vamos trabalhar. No caso do banco relacional, o modelo lógico já deve definir as tabelas e suas relações.
Detalhes de armazenamento interno de informações, que não tem influencia sobre a programação de aplicações no SGBD, mas podem influenciar a performance da aplicações (por exemplo, as estruturas de arquivos usadas no acesso as informações) não fazem parte do modelo lógico.
Estas características "físicas" são representadas (obviamente) no modelo físico.
O modelo físico trata, portanto, de questões da implementação da estrutura física de organização dos dados, que inclui, por exemplo, o entendimento (e o projeto e/ou a "sintonia") da estrutura de arquivos, de memórias, da programação de acesso interno aos dados, da escolha do dispositivo de memória, etc.
O projeto do modelo físico é feito apenas por profissionais que fazem sintonia de banco de dados, procurando otimizar a performance. As linguagens e notações para o modelo físico não são padronizadas e variam de produto a produto. A tendência em produtos mais modernos é esconder o modelo físico do usuário e transferir a tarefa de otimização ao próprio SGBD.
Link para os exercícios sobre modelagem física (lista 4).
Aspectos Práticos da Modelagem Física
Não é objetivo desse curso entrar em detalhes do estudo da estrutura interna do banco ou do SGBD. Não é nosso objetivo discutir detalhes da organização dos dados na memória ou das estruturas de programação e linguagens dos SGBDs.
Vamos trabalhar no modelo físico sobre outros aspectos (menos teórico e mais prático).
Nesse nosso curso, veremos nessa parte que chamamos "modelo fisico" alguns detalhes da transformção do modelo lógico para a real construção do banco de dados no SGBD mysql.
Vamos trabalhar com a modelagem lógica no mysqlworkbech e ver como relacioná-la com a real implantação do banco de dados.Mas chamamos atenção para o fato de não estarmos fazendo a "modelagem física" como definida teoricamente. Estamos "programando o banco de dados" já considerando que a modelagem física, incluindo questões de otimização, segurança, backup, etc já foram pensadas quando o SGBD mysql foi escrito.
Importante também entendermos que os diagramas do mysyqlworkbench não são diagramas Entidade-Relacionamento. Estão mais para um modelo relacional (lógico), pois já consideram as tabelas e suas estruturas.
Estudo de Caso: do Modelo ER para o modelo Relacional
Passo a passo usando o MysqlWorkBench
Nessa seção vamos fazer passo a passo uma modelagem relacional usando o MysqlWorkBench e transformando o modelo lógico em um banco de dados no mysql.
Vamos usar um exemplo simples: um banco com estudantes e disciplinas por eles cursadas. Temos duas entidades: Estudante e Disciplina. E fazemos as (muito simples) seguintes considerações:
Cada estudante pode matricular em várias disciplinas.
Cada disciplina pode ser cursada por vários estudantes
Atributos de Estudantes: nome, matrícula, cpf, telefone
Atributos de Disciplinas: nome, ID, ID_Departamento
Esse modelo conceitual pode ser representado no diagrama ER dado na figura 1.
Figura 1: Exemplo modelo ER simples para o Estudo de Caso
Para esse modelo temos os atributos simples das duas entidades e um atributo multivalorado para a entidade estudante: o atributo telefone deve ser considerado multivalorado pois podemos ter mais de uma instância de "telefone" para cada estudante.
Criando o Modelo Relacional no MysqlWorkbench
O mapeamento do modelo ER dado na figura 1 para o modelo lógico é bem simples de ser feito e pode ser resumido pelas seguintes etapas:
As entidades estudante e disciplina se tornam tabelas.
Como temos um relacionamento N:N a melhor forma de definirmos o relacionamento é criando uma nova tabela, que expressa o relacionamento, tendo como campos as chaves primárias das tabelas de estudante e disciplina
O atributo multivalorado é mapeado numa tabela que está relacionada com a entidade estudante.
Tendo essas ideias bem definidas, vamos trabalhar esse mapeamento no MysqlWorkbench.
Passo 1: Iniciando o MysqlWorkbench
A figura 2 mostra a tela inicial do MysqlWorkBench
Figura 2: Tela inicial do MysqlWorkbench. Clique aqui para ver a figura aumentada.
Quando entramos no MysqlWorkbench podemos iniciar um novo modelo ou uma nova conexão com algum banco de dados. Ou usar as conexões e modelos existentes.
No nosso caso, vamos iniciar um novo modelo. O MysqlWorkbench chama de modelo ER (mas a rigor não é...).
Figura 3: Tela inicial para a criação do modelo relacional.
Quando optamos por criar um novo modelo, a tela que se abre é a dada na figura 3. Repare que o MysqlWorkbench cria um "esquema" (schema) com o nome mydb. Esse nome default pode (e deve) ser mudado. No caso desse exemplo mudamos o nome do Schema para EstudanteDisciplina.
A tela aberta é um espaço para criarmos as tabelas e definir os relacionamentos.
Criando Tabelas
Figura 4: Mostrando a criação da tabela "Estudante"
Para criar a tabela, clicamos no ícone correspondente (indicado na figura 3) e clicamos na área de projeto. Uma tabela inicial será criada. Temos que alterar o nome e inserir os campos (colunas). A figura 5 mostra o resultado da criação da tabela Estudante.
Figura 5: Representação da Tabela "Estudante"
Fazemos o mesmo para criar as tabelas "Telefone" e "Disciplina". A figura 6 mostra o resultado da criação das 3 tabelas do Banco.
Figura 6: Representação das Tabelas "Estudante", "Telefone" e "Disciplina"
Criando Relacionamentos
Uma vez criadas as tabelas podemos definir os relacionamentos. Precisamos saber a cardinalidade. As opções são bem claras no workbench. Veja na figura 3.
Na figura 6 mostramos o diagrama após conectarmos as tabelas. A tabela adicional é criada automaticamente quando criamos um relacionamento N:N. Para unirmos as tabelas (criando o relacionamento).
Nesse figura já mostramos o modelo após renomearmos o schema (veja na parte de baixo da janela o espaço para alteração do nome do schema).
Figura 7: Tabelas conectadas. Repare na cardinalidade dos relacionamentos. Repare que foi criada uma tabela adicional. Essa tabela é criada automaticamente pelo Workbench. Repare também que mudamos o nome do Schema de mydb para EstudanteDisciplina.
Gerando o Banco de Dados no Mysql
O MysqlWorkbench oferece uma ferramenta para convertermos o modelo relacional em um banco de dados configurado no mysql. É a opção Forward Engineer, que está no menu Database, como mostrado na figur 8a
Figura 8: Destaque das opções do menu "Database". A opção Forward Engineer inicia o processo para converter o modelo relacional em um banco de dados.
Ao clicar na opção Forward Engineer inicia-se o processo para converter o modelo relacional em um banco de dados.
O primeiro passo é a definição da conexão com o mysql. No caso da figura 9 temos o exemplo de uma conexão com o mysql local, cujos parâmetros já estão salvos (host, user e password).
Figura 9: Primeiro passo para a conversão do modelo relacional para o banco de dados é a definição da conexão com o SGBD. A figura mostra os parâmetros de uma conexão com o mysql local.
Após a etapa inicial de conexão com o mysql, o processo continua, e solicita configurações específicas do banco que será criado (figura 10) e opções para definir quais arquivos serão salvos (figura 11). Recomenda-se deixar configurado as opções default.
Figura 10: Etapa para a criação do banco de dados: opções de configuração do banco gerado. A sugestão é não alterar as opções default. |
Figura 11: Etapa para a criação do banco de dados: opções de configuração para os arquivos que serão gerados. A sugestão é não alterar as opções default. |
Script SQL com os comandos de criação do Banco de Dados
Nessa penúltima etapa, o processo apresenta o script (comandos do mysql) que será executado. É importante que você entenda o script. Uma opção interessante é salvar o arquivo .sql que é criado pelo processo. Veja a figura 12. Nela é mostrada parte do arquivo SQL de criação do Banco e das tabelas.
Figura 11: Penúltima etapa do processo de criação do banco de dados no mysql: geração do arquivo SQL que será "rodado" no mysql para criação do Banco e das Tabelas. Repare no nome do Banco de Dados criado (ele deverá ser o banco que vamos trabalhar no mysql). Repare também nos comandos mysql do arquivo gerado.
Veja no link abaixo o arquivo SQL gerado para o caso específico desse exemplo.
LINK: Arquivo SQL criado pelo processo "forward engineer" do MysqlWorkbench.
Veja o arquivo. Entenda o que ele faz. Ao gerar o seu arquivo (fazendo todo o processo, como pedido nos exercícios) entenda o que o arquivo SQL criado contém.
A última janela que se abre (figura 12), finaliza o processo. Nesse ponto o banco de dados será criado no seu mysql e você estará em condições de acessá-lo, seja via o MysqlWorkbench, via terminal ou via PHPMyadmin (dentre outras ferramentas).
Figura 12: Última etapa do processo de criação do banco de dados no mysql.
Trabalhando com o Banco de Dados Criado
Tendo sido realizado o processo com sucesso, podemos entrar no mysql e verificar a existência do novo banco. A figura 13 apresenta o acesso ao mysql e a verificação de que de fato o banco novo foi criado.
Figura 13: Acesso ao mysql e confirmação da existência do Banco de Dados.
Operações no Terminal do Mysql
A partir desse ponto temos o banco de dados para trabalharmos. Repare que o processo de sua criação foi todo feito no MysqlWorkBench, desde sua concepção até sua criação no SGBD. Por isso associamos esse processo ao que chamamos de "modelagem física". Ou seja, o banco de dados "real" (que existe "fisicamente" do seu SGBD) foi criado a partir de um modelo relacional.
Repare que a rigor, teoricamente, segundo os autores renomandos de estudo de banco de dados, a modeagem física é um processo diferente, e envolve conhecimento das configurações internas do SGBD.
Estando no mysql podemos verificar as tabelas criadas, por exemplo. Veja algumas ações que podemos fazer antes de trabalharmos efitivamente no Banco.
Exemplo De Ações no Banco de Dados Criado
mysql> show databases like 'Estudante%';
+-----------------------+
| Database (Estudante%) |
+-----------------------+
| EstudanteDisciplina |
+-----------------------+
1 row in set (0.00 sec)
mysql> use EstudanteDisciplina
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------------------+
| Tables_in_EstudanteDisciplina |
+-------------------------------+
| Disciplina |
| Disciplina_has_Estudante |
| Estudante |
| Telefone |
+-------------------------------+
4 rows in set (0.01 sec)
mysql> describe Disciplina;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| idDisciplina | int | NO | PRI | NULL | |
| Nome | varchar(45) | YES | | NULL | |
| DepID | int | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
mysql> describe Disciplina_has_Estudante;
+-------------------------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------------+------+------+-----+---------+-------+
| Disciplina_idDisciplina | int | NO | PRI | NULL | |
| Estudante_CPF | int | NO | PRI | NULL | |
+-------------------------+------+------+-----+---------+-------+
2 rows in set (0.01 sec)
A título de exercício, alterei o nome da tabela "intermediária" criada pelo MysqlWorkBench para definir a relação N:N entre estudante e disciplina. Isso poderia ter sido feito no próprio WorkBench. Mas uma vez criado o banco, podemos também renomear suas tabelas. Veja o exemplo a seguir.
Exemplo: renomeando uma tabela.
mysql> alter table
-> Disciplina_has_Estudante
-> rename to
-> Matriculado
-> ;
Query OK, 0 rows affected (0.22 sec)
mysql> show tables;
+-------------------------------+
| Tables_in_EstudanteDisciplina |
+-------------------------------+
| Disciplina |
| Estudante |
| Matriculado |
| Telefone |
+-------------------------------+
4 rows in set (0.01 sec)
Agora que temos o banco de dados criado, podemos trabalhar com ele, inserindo dados e fazendo consultas.
Conclusão
Foram apresentados os passos para a criação do banco de dados a partir do modelo relacional do MysqlWorkbench. Existem ferramentas online que fazem ações semelhantes. Lembramos que o que chamamos aqui de de "modelagem física" difere da definição formal encontrada na literatura.
Note que o processo de criação do banco de dados via MysqlWorkbench facilita bastante o trabalho de criação das tabelas. Se fizemos com o devido cuidado a montagem das tabelas e das relações entre elas no Workbench estaremos certoe de termos um Banco de Dados consistente, que está livre de anomalias e redundâncias.
Nos exercícios dados a seguir você é convidado a fazer todo o processo e aprofundar um pouco mais nos conceitos vistos aqui.
Exercícios
Esses exercícios compõem a lista 4 do nosso curso de Banco de Dados.
Exercício 1
Execute todos os passos apresentados no estudo acima, partindo de um modelo vazio, criando as tabelas e conectando com o mysql. Ao final do processo, verifique o banco criado no seu mysql. Insira dados para testes.
Exercício 2
Repare que na figura 6 temos as três tabelas criadas, mas sem definições de relacionamentos entre elas. A partir dessa observação pense e responda:
Podemos criar o banco de dados a partir dessa estrutura, sem definir os relacionamentos?
Se sim, analise o impacto e as consequências para a administração do banco de dados. Você acha que definir o banco apenas com as tabelas, sem existir relacionamentos entre elas é um problema?
Experimente fazer todo o processo (como foi feito no estudo da criação do banco acima), a partir dessa figura 6
Exercício 3
Melhore o modelo do estudo de caso (Estudante/Disciplina) colocando restrição de número de estudantes que podem cursar uma determinada disciplina.
Essa alteração deve ser feita em qual(uais) modelo(s)? (ER, Lógico ou Físico)
Exercício 4
Melhore o modelo do estudo de caso (Estudante/Disciplina) criando a entidade departamento e associando a ela disciplinas. Cada departamento deve ser responsável por N disciplinas. E cada disciplina só pode ser ofertada por um departamento específico. Fica a cargo do projetista definir os atributos e relacionamentos necessários. Implemente o modelo ER, Lógico (MysqlWorkbench) e Físico (mysql)
Exercício 5
Faça o que é pedido:
Crie o modelo ER (diagrama DER) para um banco de dados de uma escola (bem simplificado)
O modelo conceitual deve ter tratar as informações pessoais (responsáveis, endereço, dados de idade, escola anterior, etc.) e acadêmicas dos estudantes. Deve conter informações do ano letivo, das disciplinas cursadas, das ocorrências do aluno na escola. O sistema que será criado deve prever informações trimestrais do desempenho do aluno. Deve-se ter um sistema para registro de notas e frequências. Fica a cargo do projetistas fazer a concepção do modelo conceitual e gerar o DER.
Uma vez gerado o DER, use o MysqlWorkBench para criar o modelo relacional
A partir do modelo relacional, crie o banco de dados usando o processo de "Forward Engineer" do MysqlWorkBench
Conecte com seu mysql (ou, opcionalmente com o mysql da máquina newton) e crie o banco.
Insira dados e faça testes.