Estudo de Caso e Modelagem de Banco de Dados

Prévia do material em texto

Modelagem de Banco de 
Dados 
Um Estudo de Caso 
Eduardo Lacerda e Juan Magalhães 
A modelagem de banco dados é um passo importante para o desenvolvimento de qualquer 
sistema de informações, pois neste momento, através de uma especificação do seu cliente, será 
possível identificar todos os elementos importantes da solução que será implementada. Serão 
levantados todos os dados que devem ser registrados em um banco de dados e qual a melhor 
maneira de armazena-los, preocupando-se em garantir a integridade da informação guardada e 
evitando duplicidade, além de identificar a forma que seu conteúdo se relaciona para que ele possa 
ser acessado posteriormente pela aplicação de maneira lógica e eficaz. 
 A maneira mais difundida de se modelar dados é através do diagrama de entidade e 
relacionamento, usado para identificar os elementos(entidades) do sistema, os vínculos entre esses 
elementos(relacionamentos), e quantificadores que representam a regras para expressar o 
comportamento desses vínculos entre os elementos(Cardinalidade). Esta matéria irá explorar os 
elementos deste diagrama de maneira descritiva visando poder ajudar a qualquer um que esteja 
ingressando no estudo de modelagem de dados, utilizando o modelo de entidade e relacionamento. 
 Uma vez que a modelagem de dados é concluída, o próximo passo é codificar os scripts 
SQL utilizando o diagrama de entidade e relacionamento como o modelo a se seguir para criar as 
suas tabelas no seu banco de dados, deixando esta base de dados mais organizada e integra. Esta 
matéria demonstra a implementação de uma base dados utilizando os scripts SQL explicando cada 
passo de sua construção seguindo a modelagem feita a partir da especificação hipotética para a 
criação de um sistema de um zoológico modelando os elementos do sistema e os transformando em 
tabelas para persistir as informações necessárias. 
Cenário considerado 
Um zoológico planeja adquirir um sistema para gerenciar as suas tarefas diárias. O sistema 
para esse zoológico deve ser capaz de cadastrar os seus animais, bem como os seus funcionários. 
O sistema deve, ainda, reconhecer a que classe determinado animal pertence: mamífero, 
réptil, ave. Além disso, cada classe deve conter uma descrição técnica sobre as suas características 
específicas. 
Os animais do zoológico devem ser identificados por um código de identificação, devendo o 
sistema registrar o seu nome, espécie, cor e altura. Cada animal é mantido em algum tipo de 
container, e o sistema deve saber o seu tipo, por exemplo: um poço, uma jaula, um viveiro, um 
tanque, etc. Cada container fica localizado em uma ala do zoológico, e o sistema deve indicar qual é 
a ala onde ele se encontra para facilitar o agendamento de atividades. 
Os funcionários que trabalham nesse zoológico podem ser veterinários, cuidadores de 
animais, zeladores ou trabalharem em setores administrativos. Todos os funcionários devem conter 
nome, data de nascimento, CPF, RG, endereço completo e o cargo que desempenham. No caso dos 
veterinários, deve conter ainda o CRMV (Carteira do Conselho Regional de Medicina Veterinária). 
O sistema deve possuir um módulo para agendar dia e hora para as consultas de cada animal 
com os veterinários, tendo em vista que cada animal deve ser tratado sempre pelo mesmo 
veterinário, para que ele possa fazer acompanhamentos em longo prazo com o seu paciente, 
ressaltando que um veterinário pode atender, no máximo, 15 animais, sendo este o número ideal 
para que o veterinário consiga manter a qualidade do seu trabalho. 
O sistema deve também ser capaz de agendar dia e horário específico para a limpeza dos 
containers dos animais. 
Diagrama Entidade-Relacionamento 
Analisando o cenário proposto e as necessidades apontadas, foram identificadas as seguintes 
entidades: Animal, Classe, Container, Funcionário e Cargo, pois percebemos que esses elementos 
continham características próprias. Notamos que o elemento “Ala” não possuía tais características, 
contudo, foi transformada em uma entidade para evitar problemas de deleção, visto que no ato da 
exclusão de uma Classe ou Container poderia se perder o registro de uma “Ala” do SGBD (ver 
Figura 1). 
 
Figura 1. Diagrama Entidade-Relacionamento 
O sistema precisa ser capaz de registrar as datas e horas das consultas realizadas e esses 
registros não podem ser perdidos. Para tanto, criamos a tabela associativa Histórico de Consultas. 
Importante dizer que, primeiramente, havíamos pensado em criar um relacionamento Consulta, que 
possuiria a data e a hora das consultas realizadas e a cardinalidade de 1 a 15, que é uma questão 
exigida na especificação. Entretanto, utilizando esta solução, corremos o risco de perder registro de 
consultas de veterinários que deixassem o zoológico, e também surgiriam problemas quando 
animais morressem ou deixassem o zoológico por qualquer motivo, visto que cada veterinário deve 
poder acompanhar no máximo 15 animais. Assim, quando algum animal deixasse de existir no 
sistema, seu histórico de consultas também deixaria de existir. Considerando esta situação, criamos 
o Histórico de Consultas como uma entidade, e o relacionamento Acompanha, que indica quais 
animais o veterinário está acompanhando atualmente, dentro do seu limite máximo de 15 animais. 
O relacionamento Limpeza precisava receber os atributos “Data” e “Hora”, para manter 
esses registros. Apesar de ser representada como um relacionamento entre Funcionário e 
Container, ela será uma tabela em sua implementação no bando de dados. 
Quanto ao “Endereço”, este aparenta ser um atributo de Funcionário, todavia, contém 
diversos atributos próprios, tornando-se uma entidade, e como endereços podem conter cidades 
diferentes, surge a entidade Cidade, para que os registros de Cidade não sejam perdidos na 
exclusão de endereços. 
Sobre as cardinalidades, consideramos que: 
• Um funcionário pode possuir apenas um cargo, e um cargo pode conter vários 
funcionários; 
• Um endereço pertence a apenas uma cidade, e uma cidade pode conter vários 
endereços; 
• Um funcionário só irá registrar um endereço, entretanto um mesmo endereço pode 
conter mais de um funcionário (Exemplo: pai e filho moram juntos e trabalham no 
mesmo lugar); 
• Um funcionário (veterinário) pode possuir nenhum ou diversos históricos de 
consultas, enquanto um histórico de consulta pertence a apenas um veterinário; 
• Um animal pode possuir nenhum ou diversos históricos de consultas, enquanto um 
histórico de consulta pertence a apenas um animal; 
• Um funcionário (zelador) pode precisar limpar nenhum ou diversos containers, e um 
container pode ainda não ter sido limpo, ou ser limpo por diversos zeladores; 
• Um animal pertence a apenas um container, enquanto um container pode ter nenhum 
ou vários animais; 
• Um container pertence a uma ala, e uma ala pode ter um ou vários containers; 
• Uma ala contém animais de uma classe (mamíferos, aves, etc.), e uma classe pode 
estar em uma ou várias alas; 
• E por fim, um animal pertence a apenas uma classe, e uma classe pode conter 
diversos animais. 
Script SQL 
A seguir mostraremos o script para criação do banco de dados com as tabelas e restrições do 
nosso modelo. A primeira etapa contém os scripts de criação das tabelas, atributos e definições das 
chaves primárias. As chaves estrangeiras foram definidas depois de todas as tabelas e chaves 
primárias já criadas para evitarmos problemas de dependência. 
CREATE DATABASE Zoologico;
Aqui criamos o nosso banco de dados Zoologico, onde serão criadas as tabelas e restrições 
do nosso script. 
2. CREATE TABLE Animal
3. (codAnim INT not null,
4. nome INT not null,
5. especie INT,
6. cor VARCHAR(30),
7. altura DECIMAL(2,2),
8. classe INT not null,
9. container INT,
10.
11. CONSTRAINT pkAnimal PRIMARY KEY (codAnim));
Criamosa tabela Animal para cadastrarmos os animais do Zoológico do cliente. “codAnim” 
é o que identificará unicamente cada animal, assim é definido como not null. 
Definimos “nome” e a “classe” como not null, pois todo animal deve ter um nome e uma 
classe. Porém, ele não necessariamente precisa de uma “espécie”, pode ser desconhecido por 
exemplo. O atributo “container” irá indicar onde o animal está alojado. Na linha 11, definimos o 
atributo “codAnimal” como chave primária da tabela Animal. 
12. CREATE TABLE Classe
13. (codClasse INT not null,
14. nomedaClasse VARCHAR(15),
15. descricao VARCHAR(1000),
16. 
17. CONSTRAINT pkClasse PRIMARY KEY (codClasse));
A criação da tabela Classe possui um “codClasse” para identificarmos unicamente cada 
Classe de Animal. O “nome” é como a classe é conhecida, e “descrição” é uma descrição com as 
principais características da classe (um texto, por isso o VARCHAR com 1000 de limite). Na linha 
17 definimos o atributo “codClasse” como chave primária (PRIMARY KEY) da tabela Classe, por 
este motivo este atributo foi definido como not null. 
18. CREATE TABLE Container
19. (codCont INT not null,
20. tipo VARCHAR(10) not null
21. ala INT,
22. 
23. CONSTRAINT pkContainer PRIMARY KEY (codCont));
Criamos a tabela Container com seus atributos “codCont” e “tipo” que vão armazenar um 
identificador único e que tipo de container é o referido, respectivamente. Ambos os campos são 
obrigatórios, assim possuem a especificação not null. Na linha 23 o atributo “codCont” é definido 
como chave primária da tabela Container. 
24. CREATE TABLE Ala
25. (numeroAla INT not null,
26. classe INT,
27.
28. CONSTRAINT pkAla PRIMARY KEY (numeroAla));
Aqui ocorre a criação da tabela Ala. Esta tabela possui um atributo “numeroAla” que é o seu 
número, este será a chave primária e assim é definido como not null. O atributo “classe” será uma 
referência a tabela Classe para identificar que classe de animal residi em determinada Ala. Na linha 
34 definimos a chave primária como sendo o atributo “numeroAla”. 
29. CREATE TABLE Funcionario
30. (matriculaFunc INT not null,
31. Nome VARCHAR(50) not null,
32. CPF BIGINT not null,
33. RG BIGINT,
34. CRMV INT
35. cargo INT not null
36. endereco INT,
37. 
38. CONSTRAINT pkFuncionario PRIMARY KEY (matriculaFunc));
Criamos a tabela Funcionario com todos os atributos exigidos. “matriculaFunc”, “nome”, 
“CPF”, e “cargo” são atributos que todo funcionário deve possuir, assim foram definidos como not 
null. “endereço” é do tipo INT pois referenciará um outro atributo de mesmo tipo. Os atributos 
“RG” e “CPF” são do tipo BIGINT pois receberão números muito grandes, não suportados pelo 
tipo INT. Na linha 38 definimos a chave primária da tabela Funcionario como sendo o atributo 
“matriculaFunc”. 
39. CREATE TABLE Cargo
40. (codCargo INT not null,
41. cargo VARCHAR(30) not null,
42. salario DECIMAL(6,2),
43. 
44. CONSTRAINT pkCargo PRIMARY KEY (codCargo));
A criação da tabela Cargo só exigiu a criação dos atributos “codCargo”, “cargo” e “salario”. 
Os atributos “codCargo” e “cargo” são definidos como not null pois são de preenchimento 
obrigatório. O campo “salario” é do tipo DECIMAL(6,2) pois irá receber valores que representam 
moeda e assim utilizam valores decimais, (6,2) representa que serão aceitos valores com 6 dígitos 
antes da vírgula e 2 após a vírgula. Definimos o atributo “codCargo” como sendo chave primária na 
linha 44. 
45. CREATE TABLE Endereco
46. (codEndereco INT not null,
47. rua VARCHAR(50) not null,
48. numero SMALLINT,
49. complemento VARCHAR(20),
50. cidade INT not null,
51. CEP INT not null,
52. 
53. CONSTRAINT pkEndereco PRIMARY KEY (codEndereco));
Neste script criamos a tabela Endereco e seus atributos “rua”, “numero”, “complemento”, 
“cidade” “CEP”. Os endereços devem possuir uma Rua, uma Cidade e um CEP, assim os atributos 
correspondentes foram definidos como not null. “numero” possui o tipo SMALLINT, pois este 
aceita valores até 32.767, o que é mais do que o necessário para os números. “cidade” é do tipo 
INT, pois referenciará um atributo da tabela Cidade. Na linha 53 o atributo “codEndereco” foi 
definido como sendo a chave primária da tabela Endereco. 
54. CREATE TABLE Cidade
55. (codCid INT not null,
56. nome VARCHAR(20) not null,
57. 
58. CONSTRAINT pkCidade PRIMARY KEY (codCid));
A criação da tabela Cidade necessitou somente dos atributos “codCid” para identificarmos 
unicamente cada cidade e o “nome” da cidade. O atributo “nome” é do tipo VARCHAR com limite 
de 20 caracteres para poder armazenar os nome das cidades. O atributo “codCid” é definido como 
chave primária na linha 58. 
59. CREATE TABLE HistoricoConsulta
60. (codConsult INT not null,
61. dataConsult DATE not null,
62. hora TIME not null,
63. veterinario INT not null,
64. animal INT not null,
65. 
66. CONSTRAINT pkHistorico PRIMARY KEY (codConsult));
Criamos a tabela HistoricoConsulta e todos os seu atributos são obrigatórios, assim são 
definidos como not null. “dataConsult” possui o tipo DATE para armazenar dados do tipo data (dd/
mm/aaaa). O atributo “hora” é do tipo TIME, que é um tipo específico que pode armazenar valores 
do tipo tempo (hh:mm:ss). Na linha 66 definimos a chave primária sendo o atributo “codConsult”. 
67. CREATE TABLE Limpeza
68. (codLimp INT not null,
69. dia DATE not null,
70. hora TIME not null,
71. container INT not null,
72. funcionario INT null,
73. 
74. CONSTRAINT pkLimpeza PRIMARY KEY (codLimp));
Criamos a tabela Limpeza com os atributos “codLimp”, “dia”, “hora”, “container” e 
“funcionário”, todos são obrigatórios e são definidos como not null. Os atributos “dia” e “hora” são 
dos tipos DATE e que são tipos específicos para lidar com Data (dd/mm/aaaa, por exemplo) e 
tempo (hh:mm:ss, por exemplo). Na linha 74 é definida a chave primária desta tabela, o atributo 
“codLimp”. 
75. CREATE TABLE Acompanha
76. (codAnim INT not null,
77. matriculaFunc INT not null,
78. 
79. CONSTRAINT pkAcompanha PRIMARY KEY (codAnim, matriculaFunc));
Criamos a tabela Acompanha e seus dois atributos “codAnimal” e “matriculaFunc”, ambos 
definidos como not null, pois são obrigatórios para atendermos a modelagem. Na linha 79 
definimos a chave primária como sendo uma combinação dos dois atributos (“codAnim”, 
“matriculaFunc”). 
A seguir começa a segunda etapa que foi a definição das chaves estrangeiras. Utilizamos o 
comando ALTER TABLE, pois as tabelas já estavam criadas e precisávamos alterá-las para 
adicionarmos as restrições de chave estrangeira. 
80. ALTER TABLE Animal ADD CONSTRAINT fkAnimClasse FOREIGN KEY (classe) 
REFERENCES Classe(codClasse);
81. ALTER TABLE Animal ADD CONSTRAINT fkAnimContainer FOREIGN KEY 
(container) REFERENCES Container(codCont);
Na linha 80 e 81 alteramos a tabela Animal definindo o atributo “classe” como chave 
estrangeira que referencia o atributo “codClasse” da tabela Classe (linha 80), e o atributo 
“container” como chave estrangeira que fará a relação com o atributo “codCont” da tabela 
Container. 
82. ALTER TABLE Container ADD CONSTRAINT fkContAla FOREIGN KEY (ala) 
REFERENCES Ala(numeroAla);
83. ALTER TABLE Ala ADD CONSTRAINT fkAlaClasse FOREIGN KEY (classe) 
REFERENCES Classe(codClasse);
Estas linhas (82 e 83) criam a relação de Container com Ala e de Ala com Classe. Na linha 
97 o atributo “ala” é definido como chave estrangeira da tabela Container que referencia o atributo 
“numeroAla” da tabela Ala. Na linha 83 definimos o atributo “classe” da tabela Ala como sendo a 
chave estrangeira que referencia o atributo “codClasse” em Classe. 
84. ALTER TABLE Funcionario ADD CONSTRAINT fkFuncAcompanha FOREIGN KEY 
(cargo) REFERENCES Cargo(codCargo);
85. ALTER TABLE Funcionario ADD CONSTRAINT fkFuncEnderec FOREIGN KEY 
(endereco) REFERENCES Endereco(codEndereco);
A tabelaFuncionario é alterada para a definição de “cargo” como chave estrangeira para a 
tabela Cargo através do atributo “codCargo”, linha 84, e para a definição de “endereco” como sendo 
a chave estrangeira para “codEndereco” da tabela Endereco na linha 85. 
86. ALTER TABLE Endereco ADD CONSTRAINT fkEnderCidade FOREIGN KEY 
(cidade) REFERENCES Cidade(codCid);
Na linha 86 alteramos a tabela Endereco para definirmos o atributo “cidade” como chave 
estrangeira para o atributo “codCid” da tabela Cidade. 
87. ALTER TABLE HistoricoConsulta ADD CONSTRAINT fkHistVeter FOREIGN KEY 
(veterinario) REFERENCES Funcionario(matricula);
88. ALTER TABLE HistoricoConsulta ADD CONSTRAINT fkHistAnim FOREIGN KEY 
(animal) REFERENCES Animal(codAnim);
Nas linhas 87 e 88 criamos as chaves estrangeiras da tabela HistoricoConsulta. O atributo 
“veterinario” referencia o atributo “matricula” da tabela Funcionario (linha 87) e o atributo 
“animal” referencia “codAnimal” na tabela Animal (linha 88). 
89. ALTER TABLE Acompanha ADD CONSTRAINT fkAcompAnim FOREIGN KEY 
(codAnim) REFERENCES Animal(codAnim);
90. ALTER TABLE Acompanha ADD CONSTRAINT fkAcompVet FOREIGN KEY 
(matriculaFunc) REFERENCES Funcionario(matricula);
As chaves estrangeiras de Acompanha são criadas nas linha 89 e 90. Na linha 89 o atributo 
“codAnimal” é definido para referenciar “codAnimal” da tabela Animal. Na linha 90 o atributo 
“matriculaFunc” é definido para referenciar o atributo “matricula” da tabela Funcionario. 
91. ALTER TABLE Limpeza ADD CONSTRAINT fkLimpFunc FOREIGN KEY 
(funcionario) REFERENCES Funcionario(matricula);
92. ALTER TABLE Limpeza ADD CONSTRAINT fkLimpCont FOREIGN KEY 
(container) REFERENCES Container(codCont);
Criamos as chaves estrangeiras da tabela Limpeza nas linhas 91 e 92. O atributo 
“funcionario” é definido como referência para o atributo “matricula” da tabela Funcionario. O 
atributo “container” é definido como chave para a tabela Container através do atributo “codCont”. 
Considerações finais 
 A modelagem de banco de dados demonstrou a sua grande importância para o perfeito 
funcionamento de um sistema de informação. Levantar corretamente os dados necessários e modelar o 
diagrama de entidade-relacionamento de forma fiel aos requisitos garante que as situações do mundo real 
poderão ser bem representadas no sistema, alcançando o objetivo de oferecer ao cliente uma forma de 
armazenar, manipular e recuperar os dados de forma segura e perene. 
 A modelagem de um banco de dados possui limitações quando tentamos modelar todos os possíveis 
cenários do mundo real, assim, é necessário uma grande capacidade do profissional de alcançar o maior nível 
de fidelidade possível sem afetar a performance e integridade do banco de dados, sendo assim um 
profissional de extrema importância no desenvolvimento de um sistema de informação.