Embed Size (px)
Citation preview
1 Modelo Entidade-Relacionamento Capítulo
1 Conteúdo deste Módulo 1. Modelos Semânticos 2. Elementos de um Diagrama Entidade-Relacionamento 3. O processo de Modelagem
1.1 Introdução
Reino
Real Imaginário
Representação
Reino
Reino
entendo
represento(modelagem)
Eu preciso entender o mundo real para representá-lo. A partir da minha imaginação posso representar o mundo real. A partir da representação posso criar um modelo que fará parte do mundo real. Em cada uma destas etapas em perco um pouco de informação.
Conclusões : - Nós não somos capazes de representar tudo o que imaginamos. - Nós somente representamos o que é interessante.
1.2 Elementos de um Diagrama de Entidades-Relacionamentos
Entidades
Uma entidade (tipo de objeto) representa uma coleção ou um conjunto de coisas (objetos) do mundo real cujos membros individuais (instâncias) compartilham das mesmas características.
Considerações importantes a respeito de Entidades : - Utiliza-se um substantivo no singular para nomear uma entidade : CLIENTE, EMPREGADO, PEDIDO, etc... - Cada instância de uma entidade só pode ser identificada de uma única forma. Isto quer dizer que se tivermos uma entidade CLIENTE, precisamos ser capazes de distinguirmos um cliente do outro (talvez pôr um número de conta, pelo C.G.C./C.P.F. ou ainda pôr um código). - Cada entidade deve exercer um papel no sistema em construção. Isto quer dizer que em um sistema de entrada de pedidos para nossa loja, pôr exemplo, pode ocorrer-nos que, além de clientes, temos um grupo de funcionários que também exerce um papel útil na loja, porem, se nosso sistema pode funcionar perfeitamente sem eles, eles não merecem ter um papel de entidade no modelo de nosso sistema. - As instâncias das entidades podem ser descritas pôr um ou mais elementos de dados (atributos). Desse modo, um CLIENTE pode ser descrito pelos atributos nome, endereço, limite de crédito e número de telefone. Observe que os atributos devem se aplicar a cada instância da entidade; cada cliente terá seu número de telefone, nome, endereço, etc.
- Com os mesmos atributos e relacionamentos só existe uma entidade. - Toda entidade deve ter um conjunto de atributos cujos valores identifiquem univocamente uma instância da entidade ( chave ). A indicação da chave é obrigatória. Iremos representar também os atributos de uma entidade através da seguinte notação :
Pessoa é uma entidade. Os atributos da entidade PESSOA são nome, idade e CPF.
O sublinhado no atributo CPF indica que ele é chave da entidade.
Observação : não se representa chaves secundárias. Se grifar mais de um campo, a chave é composta pôr todos eles. A ordem não é importante. Relacionamentos
Considerações importantes a respeito de Relacionamentos :
CLIENTE COMPRA MERCADORIA
- Um relacionamento pode interligar duas ou mais instâncias de um mesmo objeto. Isto significa que uma instância do relacionamento COMPRAR entre as entidades CLIENTE e MERCADORIA pode conter um cliente e várias mercadorias.
MÉDICO PACIENTE
TRATAR
COBRAR
- Pode haver mais de um relacionamento entre dois objetos.
CORRETOR
COMPRADOR
NEGOCIAPREÇO
VENDEDOR
- Um relacionamento pode ser descrito do ponto de vista de qualquer das entidades participantes. Como no exemplo abaixo: 1. Corretor de imóveis negocia preço entre comprador e vendedor. 2. Comprador negocia preço com vendedor pôr intermédio do corretor de imóveis. 3. Vendedor negocia preço com comprador pôr intermédio do corretor de imóveis.
Os objetos (entidades) são interligados pôr relacionamentos. É importante reconhecer que o relacionamento representa um conjunto
de conexões.
Pessoa
CPF Nome Idade
Iremos representar também os atributos de um relacionamento:
Entidades fracas
Pessoa Escola
Horário
Estudar
O horário que a entidade PESSOA estuda na escola é atributo do relacionamento ESTUDAR. Observe que relacionamentos não tem chaves !
Uma entidade fraca é aquela que só existe em função de um relacionamento. Se apagar o relacionamento apagaremos também a entidade fraca.
Observe o exemplo de entidade fraca descrito abaixo :
Observe que não há como existir uma sala que não esteja compondo um bloco, assim como não pode existir um bloco que não esteja localizado em um Campus. Isto quer dizer que sem o relacionamento localizar, a entidade bloco não existe, assim como sem o relacionamento compor a entidade sala também não existe. Basicamente, o que acontece é que não dá para encontrar uma sala apenas com o número da sala, precisamos também saber qual é o bloco. Da mesma forma não dá para identificar o bloco com precisão se não soubermos de qual campus estamos falando. Os relacionamentos localizar e compor são chamados de relacionamentos totais, pois são eles que determinam que as entidades bloco e sala respectivamente são fracas. Os relacionamentos totais são indicados através de uma seta. Exercícios 1)Represente as situações abaixo por meio de elementos de um diagrama entidade relacionamento a) Cliente compra veículos b) Alunos fazem provas c) Álbum tem fotos d) Funcionário e) Cidade possui ruas que possuem casas f) Pais tem estados que tem cidades g) Taxi faz corrida h) Médicos e suas cirurgias i) Pessoas compram produtos j) Animais suas raças e cores l) Aviões fazem vôos para varias cidades m) Escritores escrevem livros dos mais variados gêneros n) Mototaxistas fazem corridas de um bairro a outro
o) Vários loteamentos da cidade possuem quadra e lote p) Pessoas pedem pizzas em casa
Sala
Campus
Bloco
No. Sala Letra Bloco No.
Compor
Localizar
Papel Uma entidade participa num determinado
relacionamento desempenhando um papel. Só se representa o papel quando pode surgir dúvida.
PESSOA DISCIPLINACURSARcursa é cursada por
Ordem
EMPRESA
PESSOA
MERCADORIAVENDERvende é comprado
compra
ORDEM : TERNÁRIO
É o número de papéis que o relacionamento possui. A ordem é sempre maior ou igual a dois. Existem relacionamentos binários, ternários ou quaternários.
Auto-Relacionamento
PESSOA CASAR
marido
esposa
É quando o conjunto de entidades assume vários papéis em um relacionamento.
Cardinalidade
PESSOA ESCOLAESTUDARestuda matricula-se
N N
A cardinalidade indica quantas vezes a entidade participa do relacionamento.
Cada pessoa pode estudar em N escolas, portanto colocamos o N do lado da escola. Cada escola pode matricular N pessoas, portanto colocamos o N do lado da pessoa. Este tipo de cardinalidade se chama N:N.
Cada pedido pode gerar apenas 1 nota fiscal (no caso do exemplo acima), portanto colocamos o 1 do lado da nota fiscal. Cada nota fiscal será gerada a partir de apenas 1 pedido, portanto colocamos o 1 do lado do pedido. Este tipo de cardinalidade se chama 1:1. Cada nota fiscal pode se desdobrar em N duplicatas, portanto colocamos o N do lado da duplicata. Cada duplicata foi desdobrar de apenas uma nota fiscal, portanto colocamos o 1 do lado da nota fiscal. Este tipo de cardinalidade se chama 1:N. Um relacionamento binário pode ser: 1:1, 1:N ou N:N. Um relacionamento ternário pode ser : 1:1:1, 1:1:N, 1:N:N ou N:N:N. Já um relacionamento quaternário pode ser : 1:1:1:1, 1:1:1:N, 1:1:N:N, 1:N:N:N ou N:N:N:N. Exercícios 2) Adicione cardinalidade as entidades e relacionamentos criados no exercício 1
Duplicata
Pedido
Nota Fiscal
No. Ordem No. Nota No. Pedido
Desdobrar Gerar 1 1 1
1.3 O processo de Modelagem de dados (D.E.R.) partindo de uma descrição
do sistema O texto abaixo será utilizado para ilustrar os passos da modelagem : O cliente compra mercadorias na loja em várias datas. O cliente fornece seu nome e C.P.F. As mercadorias são identificadas pelo código de barras e possuem nomes reduzidos. Iogurte é a mercadoria preferida. A loja recebe fregueses de outras cidades também. As mercadorias possuem uma classificação : alimentícios, limpeza ou roupas. Os clientes compram com Nota Fiscal. As Notas Fiscais possuem Série e Número da Nota. 1º Passo Grifar todos os substantivos e circular os verbos que sejam relevantes:
O cliente
2º Passo Separar as palavras em verbos e substantivos: SUBSTANTIVOS VERBOS cliente fregueses compra mercadorias cidades fornece datas classificação são identificadas loja alimentícios é nome limpeza recebe C.P.F. roupas possuem código de barras Pedido compram nomes Série possuem Iogurte Número Observe que o substantivo cliente aparece mais de uma vez no texto, porém ele aparece apenas uma vez na tabela, pois nos dois lugares o texto esta se referindo à mesma coisa. Já o substantivo nome aparece duas vezes no texto, mas está também duas vezes na tabela, isto porque na primeira a palavra nome se refere ao nome do cliente, e na segunda situação a palavra nomes se refere ao nome da mercadoria, ou seja, coisas diferentes. No caso de verbos repetidos no texto, deve-se repeti-los também na tabela. 3º Passo Dentre os substantivos, classificá-los como: - Entidades, quando se tratar de um conjunto de objetos com as mesmas características, pôr
exemplo, as entidades cliente, mercadoria, loja, cidades e pedido;
compra mercadorias na loja em várias datas. O cliente fornece seu nome e C.P.F. As mercadorias são identificadas pelo código de barras e possuem nomes reduzidos. Iogurte é a mercadoria preferida. A loja recebe fregueses de outras cidades também. As mercadorias possuem uma classificação : alimentícios, limpeza ou roupas. Os clientes compram através de um Pedido. Os Pedidos possuem Série e Número.
- Atributos, quando se tratar de uma característica comum a um conjunto de objetos, pôr exemplo: nome e C.P.F. são características de cliente, portanto são atributos da entidade cliente, código de barras, nome e classificação são atributos da entidade mercadoria, e série e número são atributos do pedido, o substantivo datas não parece atributo nem do cliente nem da mercadoria, pois esta data se refere a data da compra, vamos deixar para classificá-lo depois;
- Instâncias, quando se tratar de um objeto, individualizado, dentre o conjunto de objetos a que ele pertence. Pôr exemplo, iogurte é uma mercadoria, portanto é um objeto que faz parte do conjunto de objetos mercadoria, sendo assim é uma instância da entidade mercadoria;
- Papéis, quando se tratar de uma entidade, só que, pelo fato de estar participando de um evento
qualquer recebe temporariamente outro nome. É um “apelido” para uma entidade, pôr exemplo o substantivo fregueses se refere aos clientes;
- Valores de atributo, quando se tratar de um valor válido para um certo atributo. Pôr exemplo,
alimentícios, limpeza e roupas são valores válidos para o atributo classificação da entidade mercadoria.
A palavra iogurte também poderia ser classificada como valor do atributo nome da entidade mercadoria. No fundo, não faz diferença, já que instâncias e valores de atributo serão desprezados posteriormente.
Depois de classificados os substantivos, temos : SUBSTANTIVOS CLASSIFICAÇÃO cliente entidade mercadorias entidade datas loja entidade nome atributo da entidade cliente C.P.F. atributo da entidade cliente código de barras atributo da entidade mercadoria nomes atributo da entidade mercadoria Iogurte instância da entidade mercadoria fregueses papel da entidade cliente cidades entidade classificação atributo da entidade mercadoria alimentícios valor de atributo do atributo classificação limpeza valor de atributo do atributo classificação roupas valor de atributo do atributo classificação Pedido entidade Série atributo da entidade pedido Número atributo da entidade pedido 4º Passo Identificar as entidades que realmente são entidades através das exigências básicas : 1. Uma entidade tem que ter atributos. Se os objetos que formam uma entidade não tem características comuns, como identificá-los como um
conjunto ? 2. Uma entidade tem que ter atributo chave. Uma entidade deve ter um atributo que consegue diferenciar um objeto do outro, este atributo nós
chamamos de atributo chave.
Ás vezes, para diferenciar um objeto de outro dentro da entidade nós precisaremos de dois ou mais atributos, neste caso teremos vários atributos em uma chave, porém a chave é sempre uma só ( veja o exemplo da entidade nota fiscal ).
3. Uma entidade deve ter mais de uma instância.
Para classificar algo como entidade este algo deve ser um conjunto de objetos, se no texto que estamos estudando ele fala de um objeto apenas, não podemos classificá-lo como entidade.
Uma entidade que deixe de cumprir pelo menos uma destas regras acima não pode ser classificada como entidade. No nosso exercício, temos então o seguinte : ENTIDADES cliente possui atributos, possui atributo chave (C.P.F.) e o texto se refere a vários clientes. mercadorias possui atributos, possui atributo chave (código de barras) e o texto se refere a várias
mercadorias. loja não possui atributos, não possui atributo chave e o texto se refere apenas a uma loja. cidades não possui atributos, não possui atributo chave e o texto se refere a várias cidades. pedido possui atributos, possui atributos chave (série e número) e o texto se refere a vários
pedidos. Concluímos então que loja não é entidade porque não obedece as três regras, enquanto que cidades também não é entidade porque não obedece a duas regras. Temos então que avaliar se estas informações não podem ser aproveitadas de outra forma, como atributo de alguma entidade, pôr exemplo. No caso acima a informação loja não será aproveitada, porém a informação cidades pode ser aproveitada como atributo do cliente. Veja, a cidade onde o cliente mora é uma característica do cliente. 5º Passo Repetir os passos 3 e 4 até ter certeza da classificação que você realizou. Você pode ficar em dúvida em algumas palavras ( deixando para classificá-las numa segunda passada ) ou mesmo classificar algumas coisas de maneira errada, como pôr exemplo, o substantivo classificação pode ser erroneamente definido como entidade e os substantivos alimentícios, limpeza e roupas como instâncias da entidade classificação. Você vai descobrir mais tarde que ele é na verdade um atributo da entidade mercadoria, isso o forçará a reclassificar os substantivos alimentícios, limpeza e roupas como valores de atributo. 6º Passo Dentre os verbos, classificá-los como : - Relacionamentos, quando na frase onde verbo aparece ele estiver ligando duas ou mais
entidades. Se o verbo estiver ligado a um papel, analise como se fosse a entidade correspondente.
- Funções, quando o verbo estiver ligando qualquer outra coisa que não seja entidade.
Acompanhe as análises abaixo :
O cliente compra mercadorias na loja.
O cliente fornece seu nome e C.P.F.
As mercadorias são identificadas pelo código de barras e possuem nomes reduzidos.
Iogurte é a mercadoria preferida.
A loja recebe fregueses de outras cidades também.
As mercadorias possuem uma classificação : alimentícios, limpeza ou roupas.
ENTIDADE ENTIDADE
RELACIONAMENTO
ENTIDADE ATRIBUTOS
FUNÇÃO
ATRIBUTOENTIDADE
FUNÇÃO
ENTIDADEINSTÂNCIA
FUNÇÃO
PAPEL DA ENTIDADE CLIENTE ( deve-se tratar como se fosse a própria entidade CLIENTE )
NÃO É NADA
FUNÇÃO
ENTIDADE ATRIBUTO
FUNÇÃO
Os clientes compram através de um Pedido. Observe que já temos um verbo comprar que já foi classificado como relacionamento, que significa exatamente a mesma coisa que este outro verbo comprar, podemos concluir então que estamos nos referindo ao mesmo relacionamento duas vezes no texto. Poderia aparecer no texto duas ocorrências do verbo comprar que não tivessem nada a ver uma com o outra, neste caso teríamos dois relacionamentos distintos.
Os pedidos possuem Série e Número Depois de classificados os verbos, temos : VERBOS CLASSIFICAÇÃO compra Relacionamento entre cliente, mercadoria e pedido fornece Função são identificadas Função é Função recebe Função possuem Função compram Repetição do Relacionamento comprar possuem Função 7º Passo Verificar se existem atributos de relacionamento. Estes atributos são características não das entidades, e sim de relacionamentos. Pôr exemplo : Data da compra não é uma característica do cliente pois o cliente pode comprar em diferentes datas. Também não e característica da mercadoria pois esta pode ser comprada em diferentes datas. A data então será um atributo do relacionamento comprar. 8º Passo Repetir os passos 3 até 6 até ter certeza da classificação que você realizou. 9º Passo Tendo realizado a classificação, temos o seguinte : SUBSTANTIVOS VERBOS cliente entidade compra Relacionamento cliente, mercadoria e
pedido mercadorias entidade fornece Função data atributo de comprar são identificadas Função loja é Função nome atributo de cliente recebe Função C.P.F. atributo de cliente possuem Função
ENTIDADEENTIDADE
RELACIONAMENT
ATRIBUTOENTIDADE
FUNÇÃO
código de barras atributo mercadoria compram Relacionamento comprar nomes atributo mercadoria possuem Função Iogurte instância fregueses papel de cliente cidades atributo de cliente classificação atributo mercadoria alimentícios valor de atributo limpeza valor de atributo roupas valor de atributo Pedido entidade Série atributo pedido Número da Nota atributo pedido Deve-se então desenhar o Diagrama utilizando as entidades, relacionamentos e atributos encontrados : 11º Passo O próximo passo é a análise da cardinalidade dos relacionamentos: Cada relacionamento deverá ser analisado separadamente através das frases: Um cliente pode comprar várias mercadorias. Isto significa que do lado da mercadoria a cardinalidade é N.
Pedido
Um cliente pode comprar com vários pedidos. Isto significa que do lado do pedido a cardinalidade é N. Uma mercadoria pode ser comprada pôr vários clientes. Isto significa que do lado dos clientes a cardinalidade é N. Uma mercadoria pode estar em vários pedidos. Isto significa que do lado dos pedidos a cardinalidade é N.
Mercadoria Compra
NCliente
Mercadoria Compra
Pedido
N NCliente
Pedido
Pedido
N
MercadoriaComprar
Cliente
Pedido
N
N N
N
Mercadoria Comprar
N
N Cliente
Um pedido pode conter várias
mercadorias. Isto significa que do lado das mercadorias a cardinalidade é N. Um pedido será feito para apenas um cliente. Isto significa que do lado dos clientes a cardinalidade é 1. Agora se acrescenta os atributos com suas chaves primárias Portanto a cardinalidade vai ficar assim: 12º Passo Verificar a existência de entidades fracas é o ultimo passo. As entidades fracas são fracas pôr causa de um relacionamento. Portanto analisaremos cada entidade em relação a cada relacionamento, através de uma pergunta. Você consegue imaginar um cliente que ainda não tenha comprado nada na loja ? A resposta é sim. Pois mesmo que uma pessoa ainda não comprou na loja, ela existe. Portanto cliente não é fraco em relação a comprar. Você consegue imaginar uma mercadoria que ainda não tenha sido vendida ? A resposta é sim. Pois uma mercadoria existe, apesar de ninguém te-la comprado ainda. Portanto ercadoria não é fraco em relação a comprar.
Mercadori
MercadoriaComprar
Cliente
Pedido
Comprar Cliente
N N
N N N
Pedido
N N 1
N N N ComprarCliente Mercadoria
N
N N
MercadoriaComprar
Cliente
Pedido
1 N
N
Pedido
Série Número
MercadoriaComprar
Cliente
Código NomeMer Classificaç
DataC.P.F. NomeCl
C.F.P. NomeCli CidadeCli
Você consegue imaginar um pedido que exista sem que tenha havido uma compra associada a ele ? A resposta é não. Pois um pedido depende da existência de uma compra para existir. Portanto pedido é fraco em relação a comprar.
Pedido
Observe que o relacionamento comprar torna apenas a entidade pedido fraca, sem interferir nas entidades cliente e mercadoria.
1.4 O D.E.R. resultante da modelagem será : Exercícios 1)Faça o diagrama entidade relacionamento das seguintes descrições citadas abaixo: a) Construa um diagrama E-R (incluindo as cardinalidades) para controle do prontuário de pacientes de um hospital. O hospital possui um conjunto de pacientes e um conjunto de médicos. No registro dos pacientes, temos o nome, RG, CPF, endereço e telefone. No registro dos médicos temos o nome, especialidade, RG, CPF, CRM, endereço e telefone. Cada paciente tem associado a si um prontuário (sua ficha), onde são registradas basicamente todas ocorrências, exames, consultas, medicamentos ministrados associadas a ele. Cada registro no seu prontuário, além de ter a data tem que estar relacionado a um médico, que é o responsável pela informação do registro cadastrado no sistema. b) Construa um diagrama E-R para uma companhia de seguros de automóveis com um conjunto de clientes, onde cada um possui um certo número de carros. Os dados do cliente são nome, RG, CPF, endereço e telefone. Do carro deve-se armazenar a placa, código Renavan, fabricante, modelo, e ano. Associado a cada carro há um histórico de ocorrências. Um carro podem possuir várias ocorrências ou nenhuma. Cada ocorrência deve ter uma data, local e descrição. c) Crie o MER de um sistema de BD para ser utilizado pelos departamentos da universidade. Um departamento é responsável por no mínimo uma disciplina. Uma disciplina por sua vez pode ter de zero à várias turmas que estão sendo oferecidas. Cada turma por sua vez possui no mínimo um aluno inscrito, e não pode existir uma turma que não esteja relacionada à uma disciplina. Um aluno cadastrado no sistema pode estar ou não matriculado em alguma disciplina, e pode também estar matriculado em várias disciplinas. Um departamento deve ter em seu registro a sigla, o nome e o nome do chefe. Cada disciplina deve ter um nome, um código, a descrição da ementa, e a bibliografia, além de livros que serão cadastrados no sistema com o nome e quantidade disponível. Cada Turma deve ter um registro do seu
Mercadoria Comprar
Código NomeMer
Classificação
DataC.P.F. NomeCli
Cliente
C.F.P. NomeCli CidadeCli
Pedido
Série Número
N N
N
MercadoriaComprar
Cliente
turno (manhã, tarde ou noite) e professor que ministra. Cada aluno deve ter em seus registros nome, RG, CPF, N de carteirinha, endereço e telefone. d) Estamos fazendo o BD de uma clínica veterinária. A dona deseja um registro de suas atividades, onde: Um cliente pode possuir de 1 a vários gatos e também cachorros. Cada animal só tem um dono. Associado ao animal há um histórico de consultas e um histórico de vacinas. Associado ao dono, há um histórico de tudo que ele já comprou na lojinha da clínica. Faça o MER completo para descrição acima (cardinalidade, etc) e depois invente atributos para estas entidades e relacionamentos e defina neles quais são chave primária, quais são chave estrangeira. e) Projete a estrutura de um banco de dados que objetiva armazenar as informações sobre a frota de aviões de uma companhia aérea e sobre seus pilotos.Basicamente a idéia associar cada fabricante/modelo de avião às aeronaves que a empresa possui. Assim, por exemplo, o sistema deve ser capaz de relacionar a aeronave cujo prefixo é PY-0456 com o tipo de aeronave Boeing 737-300. Além de associar as aeronaves da empresa com os tipos de aeronaves existentes no mercado, como exemplificado acima, o sistema também deve registrar qual piloto da empresa é apto a pilotar qual tipo de aeronave, por exemplo que o José da Silva sabe pilotar o Boeing 747-200 e o Airbus A-300. Falando agora das informações que devo armazenar, dos tipos de aeronaves do mercado me interessam 2 campos: o fabricante e o código do modelo. Das aeronaves de propriedade da empresa, eu devo armazenar o seu prefixo (é como a placa do automóvel) e a data que foi comprada, a capacidade de passageiros e seu número de vôos. Dos pilotos eu devo guardar o CPF, o número de registro dele no DAC, o nome completo, endereço e o telefone de contato. f) Crie um diagrama entidade relacionamento para um site de um jornal chamado a Boca do Povo. O site necessita ter um cadastro de repórteres contendo CPF, nome, nascimento os repórteres ficarão responsáveis por postarem notícias com nome texto e data de publicação cada notícia poderá conter um ou vários vídeos, assim como uma ou várias fotos. O site possui um caderno de notícias podendo os internautas escolherem por onde navegar(ex: esporte, policial, política). Para o site também é importante armazenar enquetes que serão cadastradas por um repórter que ficara responsável para gerenciar a mesma, pois cada repórter deve acessar a administração do site com seu email e senha e por fim os repórteres poderão enviar recados entre si contendo data e texto da mensagem e se a mesma foi lida ou não. 2 Modelo Relacional Capítulo
2 Conteúdo deste Módulo 1. Conceitos do Modelo Relacional 2. Teoria de Chaves 3. Mapeamento Modelo E-R -> Modelo Relacional
2.1 Introdução Em 1969, o Dr. E. F. Codd, do laboratório de Pesquisa da IBM en San José, descreveu num primeiro artigo as definições do que se tornaria o modelo de Banco de Dados Relacional que se apoiava em forte embasamento matemático, mais especificamente na teoria dos conjuntos, análise combinatória e metodologia estatística. Esse trabalho liderado por Codd recebeu o prestígio da ACM Turing Award em 1981. Seguindo o artigo original de Codd, diversos projetos de pesquisa foram criados com o objetivo de construir, na prática, um sistema de banco de dados relacional. Hoje numerosos produtos de banco de dados relacionais estão disponíveis comercialmente.
As definições descreviam os mecanismos de representação, armazenamento e recuperação de dados dispostos em conjuntos matemáticos lógicos de estrutura tabular (através de tabelas com linhas e colunas ). Iremos utilizar então a teoria de conjuntos para explicar os conceitos básicos do Modelo Relacional. O modelo relacional usa um conjunto de tabelas para representar tanto os dados como a relação entre eles. Cada tabela possui múltiplas colunas e cada uma possui um nome único. Referências Bibliográficas KORTH, Henry F. Sistemas de Bancos de Dados. São Paulo : Makron Books, 1999.
2.2 Conceitos do Modelo Relacional Considerando os seguintes conjuntos ao lado : Domínio do conjunto nome seria todos os nomes existentes no mundo. Atributos são conjuntos de valores amostrados de um domínio. Os atributos nome, idade e coddepto são cada um uma amostra de valores de um domínio. Produto Cartesiano é um novo conjunto resultante de todas as combinações dos valores dos conjuntos.
CODDEPTIDADE NOME
O 20
A notação para produto cartesiano é ⌧. O produto cartesiano de nome ⌧ idade ⌧ coddepto resultante seria um grande conjunto composto de todas as combinações possíveis dos valores de cada conjunto ( nome, idade e coddepto). Relação é um subconjunto do produto cartesiano dos atributos. PESSOA ⊂ (nome ⌧ idade ⌧ coddepto) Iremos considerar apenas algumas combinações : A relação resultante será a Relação PESSOA.
Tupla significa uma linha da relação, seria o mesmo que instância do D.E.R.. Quando nos referimos a PESSOA, estamos nos referindo a relação PESSOA. PESSOA = { nome, idade, coddepto } Quando nos referimos a t PESSOA indica as tuplas da relação PESSOA.
José 1 22 João 2 30 Rui 3 25 Manoel
CODDEPTO
IDADE NOME
José 20
1 João 22
2 Rui 30
3 Manoel 25
t PESSOA = { < José, 20, 1 >, < João, 22 ,1 >, < Rui, 30, 2 >, < Manoel, 25, 3 > } Quando nos referimos a t PESSOA (nome) indica todos os nomes que constam nas tuplas da relação PESSOA. t PESSOA (nome) = { José, João, Rui, Manoel } Quando nos referimos a t PESSOA (nome) 2 indica o valor do atributo nome na tupla 2 da relação PESSOA. t PESSOA (nome) 2 = João A representação de uma relação completa : Relação PESSOA
nome idade coddepto
José 20 1
João 22 1 Rui 30 2
Manoel 25 3 Intenção = {nome, idade, coddepto } Extensão = { < José, 20, 1 >, < João, 22 ,1 >, < Rui, 30, 2 >, < Manoel, 25, 3 > }
2.3 Teoria de Chaves Para explicar os conceitos abaixo usaremos as seguintes relações : PESSOA = { código, nome, CPF, endereço, coddepto } DEPARTAMENTO = { coddep, nomedep, chefe, salas } Super-chave é qualquer conjunto de atributos de uma relação que identificam univocamente uma tupla de uma relação. Algumas superchaves da relação PESSOA são : {código,nome,CPF,endereço,coddepto} {código, nome, CPF, endereço } {código, nome, CPF, coddepto } {código, nome, endereço, coddepto } {código, CPF, endereço, coddepto } {nome, CPF, endereço, coddepto } {código, nome, CPF } {código, nome, endereço }
{CPF, endereço } {nome, CPF, coddepto } {código, nome, coddepto } {código, CPF, coddepto } {código, CPF } {código} {CPF}
Qualquer combinação de atributos onde apareçam os atributos código e CPF será super-chave, pois são eles que realmente identificam uma PESSOA.
Tupla
Atributo Valor
Chave é qualquer super-chave da qual eu não posso tirar nenhum atributo e ela continua sendo chave. As chaves da relação PESSOA são: {código } {CPF } A chave define as super-chaves onde sobram apenas os atributos que realmente identificam as tuplas. Chave Primária é a chave com a qual eu organizo minha base para ter maior velocidade de acesso. A chave primária da relação PESSOA é : {código} A chave código foi escolhida porque provavelmente ela tem tamanho menor que a chave CPF, possibilitando uma maior velocidade de acesso. A partir de agora, a chave primária das relações estarão sempre grifadas : PESSOA = { código, nome, CPF, endereço, coddepto } DEPARTAMENTO = { coddep, nomedep, chefe, salas } Chave Candidata são as outras chaves que não sejam a primária. Portanto, a chave candidata da relação PESSOA é : {CPF} Atributo Primo são os atributos que não fazem parte da chave primária. Os atributos primos da relação PESSOA são : {nome, CPF, endereço, coddepto } Chave Estrangeira é qualquer conjunto de atributos que compõem a chave de outra relação que não a que está sendo considerada no momento. A única chave estrangeira da relação PESSOA é : {coddepto } Pois coddepto é chave da relação DEPARTAMENTO. Integridade Referencial As restrições de integridade referencial garantem a consistência dos relacionamentos entre as relações. Esta consistência se estabelece através da referência de valores comuns entre tabelas. Se na chave estrangeira de uma tabela existirem valores que "apontem" para valores da chave primária inexistente teremos uma estrutura corrompida. Não será possível estabelecer o relacionamento entre as tabelas, o que demonstra falta de integridade no banco de dados. Atributo Multivalorado é qualquer atributo que pode ter vários valores ao mesmo tempo. Geralmente será identificado pelo fato de estar definido no plural. O único atributo multivalorado que aparece nos exemplos é : {salas } O atributo salas indica que existam várias salas para cada departamento. Portanto cada tupla de DEPARTAMENTO teria registrado todas as salas que o departamento ocupa. Os demais atributos serão chamados de Monovalorados. Atributo Atômico é o atributo que não é dividido em nenhum momento dentro da aplicação que está sendo modelada. Portando, um atributo pode ser ou não atômico dependendo dos programas que serão feitos para manipulá-lo. O atributo endereço, por exemplo, será atributo atômico enquanto for utilizado sempre completo em toda a aplicação. Porém, se em algum momento da aplicação ele for partido ( nome da rua separado do bairro, por exemplo ) ele não poderá mais ser classificado como atributo atômico.
2.4 Mapeamento Modelo Entidade-Relacionamento → Modelo Relacional Este mapeamento pode ser realizado por um computador.
2.4.1 D.E.R. utilizado como exemplo para ilustrar os passos do mapeamento :
1º Passo1º Passo Mapear as entidades regulares ( normais ). Cada entidade é mapeada como uma relação que tem como chave primária a chave da entidade, e como atributos primos os demais atributos. ALUNO = {CodAlu, NomeAluno, Idade } PROFESSOR = {CodPro, NomePro} DISCIPLINA = {Sigla, NomeDis} 2º Passo Mapear as entidades fracas. Cada entidade fraca é mapeada como uma relação que tem como chave primária a chave da entidade fraca mais as chaves de todas as relações que mapeiam as entidades que participam dos relacionamentos que tornam a entidade fraca. Os atributos primos serão os demais atributos da entidade fraca. TURMA = {Número, Sigla, Salas } 3º Passo Mapear os relacionamentos binários de cardinalidade 1:1. Escolhe-se uma das relações que mapeiam as entidades que participam do relacionamento, e incluem-se como atributos primos a chave da outra relação e os atributos do relacionamento.
Sigla é a chave da relação DISCIPLINA, que mapeia a entidade DISCIPLINA, que participam do relacionamento TER, que torna a entidade TURMA uma entidade fraca.
Aluno
Professor
Disciplina
Turma
Orientar
Matricular-se
Monitorar
Ministrar
Ter
Numero Salas Notas
Data HorarioT
Sigla NomeDis
CodigoPro NomePro
LivroTexto
HorarioM
CodAlu NomeAluno
ade
Id
NN
NN 1 N
N 1 1 1 N
ALUNO = {CodAlu, NomeAluno, Idade, CodigoPro, Data }
Data é atributo do relacionamento ORIENTAR
CodigoPro é chave da relação PROFESSOR, que mapeia a entidade PROFESSOR, que participa do relacionamento ORIENTAR, que é binário 1:1.
4º Passo Mapear os relacionamentos binários de cardinalidade 1:N. Toma-se a relação que mapeia a entidade que participa do relacionamento com cardinalidade N, e incluem-se como atributos primos a chave da outra relação e os atributos do relacionamento. DISCIPLINA = { Sigla, NomeDis, CodigoPro, LivroTexto }
CodigoPro é chave da relação PROFESSOR, que mapeia a entidade PROFESSOR, que participa do relacionamento MINISTRAR com cardinalidade
LivroTexto é atributo do relacionamento MINISTRAR.
TURMA = {Número, Sigla, Salas, HorarioT } HorarioT é atributo do relacionamento
TER. Sigla é chave da relação DISCIPLINA, que mapeia a entidade
DISCIPLINA, que participa do relacionamento TER com cardinalidade 1, porém ela já faz parte da relação TURMA, não necessitando a sua inclusão como atributo primo.
5º Passo Mapear os relacionamentos binários de cardinalidade N:N. Para cada relacionamento N:N cria-se uma relação que tem como chave a soma das chaves das relações das entidades que participam do relacionamento , e como atributos primos os atributos do relacionamento. MATRICULAR = { CodAlu, Número, Sigla, Notas }
Notas é atributo do relacionamento MATRICULAR.
Número e Sigla é chave da relação TURMA, que mapeia a entidade TURMA, que participa do relacionamento
MATRICULAR-SE.
CodigoAlu é chave da relação ALUNO, que mapeia a entidade ALUNO, que participa do relacionamento MATRICULAR-SE.
6º Passo Mapear os relacionamentos ternários e quaternários. Para cada relacionamento ternário ou quaternário cria-se uma relação que tem como chave a soma das chaves das relações das entidades que participam do relacionamento , e como atributos primos os atributos do relacionamento. MONITORAR = { CodAlu, CodPro, Número, Sigla, HorarioM }
7º Passo Mapear os atributos multivalorados. Cada atributo multivalorado cria uma nova relação, que tem a chave primária a chave da relação onde se encontrava o atributo multivalorado mais a chave da “peça” onde o atributo está ligado e o atributo como monovalorado primo. O atributo Notas da relação MATRICULAR é multivalorado. A primeira providência e eliminá-lo da relação MATRICULAR : MATRICULAR = { CodAlu, Número, Sigla } Depois cria-se uma nova relação para armazenar a informação anteriormente guardada no atributo Notas : NOTAS = { CodAlu, Número, Sigla, NúmerodaProva, Nota } O atributo Salas da relação TURMA é multivalorado. A primeira providência e eliminá-lo da relação TURMA : TURMA = {Número, Sigla, HorarioT }
HorarioM é atributo do relacionamento MONITORAR.
Número e Sigla é chave da relação TURMA, que mapeia a entidade TURMA, que participa do
relacionamento MONITORAR.
CodigoPro é chave da relação PROFESSOR, que mapeia a entidade PROFESSOR, que participa do relacionamento MONITORAR.
CodigoAlu é chave da relação ALUNO, que mapeia a entidade ALUNO, que participa do relacionamento MONITORAR.
Nota é um atributo monovalorado identificado por NúmerodaProva.
CodAlu, Número e Sigla compõem a chave da
relação MATRICULAR, que deve fazer parte da chave primária da nova
relação. NúmerodaProva é chave que identifica a “peça” ( no
caso, uma nota apenas ).
Depois cria-se uma nova relação para armazenar a informação anteriormente guardada no atributo Salas : SALAS = {Número
, Sigla, NúmerodaSala } NúmerodaSala é chave que identifica a “peça” ( no caso, uma sala apenas ) e é também o atributo monovalorado correspondente. Número e Sigla compõem a chave da relação TURMA, que deve fazer parte da chave primária da nova relação.
2.5 As relações resultantes do mapeamento serão : PROFESSOR = {CodPro, NomePro}
ALUNO = {CodAlu, NomeAluno, Idade, CodigoPro, Data }
DISCIPLINA = { Sigla, NomeDis, CodigoPro, LivroTexto }
MATRICULAR = { CodAlu, Número, Sigla }
NOTAS = { CodAlu, Número, Sigla, NúmerodaProva, Nota }
TURMA = {Número, Sigla, HorarioT }
SALAS = {Número, Sigla, NúmerodaSala }
MONITORAR = { CodAlu, CodPro, Número, Sigla, HorarioM }
Exercícios
3) Aplique o teorema de chaves para todas as entidades criadas no exercício 2 do capitulo
anterior
3 Normalização de Relações Capítulo
3 Conteúdo deste Módulo 4. Dependência Funcional 5. Regras para encontrar dependências funcionais 6. Primeira Forma Normal 7. Segunda Forma Normal 8. Terceira Forma Normal
3.1 Introdução Normalização de relações é uma técnica que permite depurar um projeto de banco de dados, através da identificação de inconsistências ( informações em duplicidade, dependências funcionais mal resolvidas, etc ). À medida que um conjunto de relações passa para uma forma normal, vamos construindo um banco de dados mais confiável. O objetivo da Normalização não é eliminar todos as inconsistências, e sim controlá-las. Referências Bibliográficas KORTH, Henry F. Sistemas de Bancos de Dados. São Paulo : Makron Books, 1999.
3.2 Dependência Funcional X
1
2
3
4
Y
11
12
13
14
O Modelo Relacional pegou emprestado da teoria de funções da matemática o conceito de dependência funcional. Iremos utilizar então a teoria de funções para explicar a dependência funcional do Modelo Relacional. Considerando os seguintes conjuntos ao lado :
Y 13 12 11
0 1 2 3
Observe que existe uma dependência entre os valores dos conjuntos, que pode ser expressa pela função f(x) = x + 10, ou seja, y é função de x, ou seja, y = f(x) = x + 10. Esta dependência, esta função pode também ser expressa através do gráfico ao lado :
Agora, observe os conjuntos ao lado :
Observe que existe uma dependência entre os valores dos conjuntos, que pode ser expressa pela função f(CPF) = nome.
Ou seja, nome é função do CPF, ou seja, se eu tiver um número de CPF, poderei encontrar o nome da pessoa correspondente.
É claro que não existe uma figura gráfica que possa descrever esta função, mas ela existe.
NOME
José
João
Rui
Manoel
CPF
1
2
3
4
Esta dependência é expressa no Modelo Relacional da seguinte maneira :
CPF → NOME Leia-se a notação acima das seguintes maneiras : com um número de CPF eu posso encontrar o nome da pessoa, ou ainda : nome depende funcionalmente do CPF.
3.3 Regras para encontrar Dependências Funcionais 3.3.1 Separação A → BC então A → B e A → C
Exemplo : CPF → nome, endereço então CPF → nome e CPF → endereço
Leia o exemplo acima da seguinte maneira :
Se com um número de CPF eu encontro o nome e o endereço de uma pessoa, então com este mesmo número eu posso encontrar apenas o nome, e com este mesmo número eu posso encontrar apenas o endereço. 3.3.2 Acumulação A → B então AC → B
Exemplo : CPF → endereço então CPF, idade → endereço
Leia o exemplo acima da seguinte maneira : Se com um número de CPF eu encontro o endereço de uma pessoa, então com este mesmo número mais a idade da pessoa eu posso encontrar o endereço também. 3.3.3 Transitividade A → B e B → C então A → C Exemplo :
CPF → código-cidade e código-cidade → nome-cidade então CPF → nome-Cidade
Leia o exemplo acima da seguinte maneira : Se com um número de CPF eu encontro o código da cidade de uma pessoa, e com o código da cidade eu encontro o nome da cidade, então com o número do CPF eu posso encontrar o nome da cidade. 3.3.4 Pseudo-Transitividade A → B e BC → D então AC → D
Exemplo : CPF → código-funcionário e código-funcionário, mês → salário-funcionário então CPF, mês → salário-funcionário
Leia o exemplo acima da seguinte maneira : Se com um número de CPF eu encontro o código do funcionário, e com o código do funcionário mais um certo mês eu encontro o salário que ele recebeu naquele mês, então com o número do CPF mais um certo mês eu posso encontrar o salário que ele recebeu naquele mês.
3.4 Primeira Forma Normal Uma relação está na primeira forma normal se todos os seus atributos são monovalorados e atômicos. Quando encontrarmos um atributo multivalorado, deve-se criar um novo atributo que individualize a informação que esta multivalorada: BOLETIM = { matricula-aluno, materia, notas } No caso acima, cada nota seria individualizada identificando a prova a qual aquela nota se refere: BOLETIM = { matricula-aluno, materia, numero-prova, nota } Quando encontrarmos um atributo não atômico, deve-se dividi-lo em outros atributos que sejam atômicos: PESSOA = {CPF, nome-completo }
Vamos supor que, para a aplicação que utilizará esta relação, o atributo nome-completo não é atômico, a solução então será: PESSOA = {CPF, nome, sobrenome }
3.5 Segunda Forma Normal Uma relação está na segunda forma normal quando duas condições forem satisfeitas : 1. a relação estiver na primeira forma normal; 2. todos os atributos primos dependerem funcionalmente de toda a chave primária. Observe a relação abaixo : BOLETIM = { matricula-aluno, codigo-materia, numero-prova, nota, data-da-prova, nome-aluno, endereço-aluno, nome-materia } Fazendo a análise da dependência funcional de cada atributo primo, da seguinte maneira : matricula-aluno, codigo-materia, numero-prova, → nota codigo-materia, numero-prova → data-da-prova matricula-aluno → nome-aluno, endereço-aluno codigo-materia → nome-materia Concluimos então que apenas o atributo primo nota depende totalmente de toda chave primária. Para que toda a relação seja passada para a segunda forma normal, deve-se criar novas relações, agrupando os atributos de acordo com suas dependências funcionais : BOLETIM = { matricula-aluno, codigo-materia, numero-prova, nota } PROVA = {codigo-materia, numero-prova, data-da-prova } ALUNO = { matricula-aluno, nome-aluno, endereço-aluno } MATERIA = {codigo-materia, nome-materia } O nome das novas relações deve ser escolhido de acordo com a chave.
3.6 Terceira Forma Normal Uma relação está na terceira forma normal quando duas condições forem satisfeitas : 1. a relação estiver na segunda forma normal; 2. todos os atributos primos dependerem não transitivamente de toda a chave primária. Observe a relação abaixo : PEDIDO = {numero-pedido, codigo-cliente, data-pedido, nome-cliente, codigo-cidade-cliente, nome-cidade-cliente }
Fazendo a análise da dependência funcional de cada atributo primo, chegamos às seguintes dependências funcionais : Primeira Coluna Segunda Coluna Terceira Coluna Quarta Coluna
numero-pedido → codigo-cliente numero-pedido → data-pedido numero-pedido → codigo-cliente → nome-cliente numero-pedido → codigo-cliente → codigo-cidade-cliente numero-pedido → codigo-cliente → codigo-cidade-cliente → nome-cidade-cliente
Concluímos então que apenas os atributos primos codigo-cliente e data-pedido dependem não transitivamente totalmente de toda chave primária. Os outros atributos primos nome-cliente, codigo-cidade-cliente e nome-cidade-cliente possuem dependência transitiva, devemos resolver estas dependências transitivas seguindo o seguinte processo : No primeiro passo, a primeira coluna vai conter a chave primeira da nova relação, como já tínhamos uma relação com chave primária numero-pedido, esta é a que vai permanecer. Os atributos da Segunda coluna serão os atributos primos desta nova relação : PEDIDO = {numero-pedido, codigo-cliente, data-pedido } No segundo passo, a Segunda coluna vai conter a chave primária da nova relação, como a chave primária será codigo-cliente, a nova relação então deverá se chamar CLIENTE. Os atributos primos da terceira coluna serão os atributos primos desta nova relação : CLIENTE = {codigo-cliente, nome-cliente, codigo-cidade-cliente, nome-cidade-cliente } No terceiro passo, a terceira coluna vai conter a chave primária da nova relação, como a chave primária será codigo-cidade-cliente, a nova relação então deverá ser chamar CIDADE. Os atributos primos da Quarta coluna serão os atributos primos desta nova relação : CIDADE = {codigo-cidade-cliente, nome-cidade-cliente } Temos então as tres relações substituindo a relação original : PEDIDO = {numero-pedido, codigo-cliente, data-pedido } CLIENTE = {codigo-cliente, nome-cliente, codigo-cidade-cliente } CIDADE = {codigo-cidade-cliente, nome-cidade-cliente } O nome das novas relações deve ser escolhido de acordo com a chave. 4 Introdução a Banco de Dados Capítulo
4 pré-requisitos • Conhecimentos Básicos sobre Arquivos de Computadores; • Modelagem de Dados. Conteúdo deste Módulo
1. Conceitos de Bancos de Dados 2. Gerenciamento de Transações
3. Administração de Memória 4. Administração de Bancos de Dados 5. Usuários de Bancos de Dados 6. Visão Geral da Estrutura do Sistema 7. Histórico dos Bancos de Dados
4.1 Introdução
Arquitetura Convencional
Na arquitetura convencional de manipulação de dados, os programas dependem da estrutura dos dados. Se a estrutura for alterada ( inclusão ou exclusão de campos, alteração de tamanho dos mesmos ) tenho que mudar e recompilar os programas. Outro problema da arquitetura convencional é o acesso indiscriminado dos dados por todos os programas, este acesso descontrolado acaba trazendo complicações para os administradores dos dados.
Programa 3 Programa 1 Programa 2
Arquivos
Referências Bibliográficas KORTH, Henry F. Sistemas de Bancos de Dados. São Paulo : Makron Books, 1999.
4.2 Conceitos de Bancos de Dados Banco de Dados é um software que se propõe a concentrar todo o acesso aos dados neste gerenciador. Modelo é o conjunto de estruturas do Banco de Dados. Seria o conjunto de regras que permitem representar os dados. Modelagem é a aplicação de um modelo na vida real. É a utilização das regras.
Arquitetura de um Banco de Dados
A independência dos dados é o maior benefício dos Bancos de Dados A partir do momento que o acesso aos dados é feito pelo gerenciador, os programas que não acessam os dados que forem incluídos ou alterados não precisam ser modificados. O SGBD cuida do formato do arquivos. Nós nunca vemos o SGBD funcionando, só através de aplicativos e utilitários.
Programa 3 Programa 1 Programa 2
SGBD Sistema Gerenciador de Banco de Dados
Este acesso pode estar sendo controlado pelo sistema operacional, algumas vezes o SGBD dispensa até o sistema operacional, realizando o acesso físico sozinho.
Arquivos
Programas aplicativos são feitos especificamente para os clientes ( Controle de Estoque, Controle Financeiro ). Programas utilitários independem do conteúdo dos dados e dos clientes. São genéricos ( Construtor de Telas, Gerador Automático de Relatórios ). Os programas utilitários são adquiridos junto com o SGBD.
Os SGBD possuem recursos para se conseguir a corretude dos dados. Validade é o conjunto de valores válidos para um determinado dado. Todo dado possui um domínio de valores (discreto ou contínuo), se o valor informado estiver dentro do domínio, então é válido.
Validade
Corretude Completesa dos dados
Consistência
Alguns Bancos de Dados fazem este teste, noutros este item é testado pelo aplicativo. Completeza compreende o preenchimento de todos os dados que são essenciais. Se eu tenho determinado dado na estrutura que é essencial para a minha aplicação e ele não for preenchido corretamente eu não tenho completeza. Geralmente nos Bancos de dados se indica quais os campos que podem ou não deixar de ser preenchidos. Consistência é a principal regra da corretude. Todas as informações do banco de dados devem ser coerentes uma com as outras. Veja os exemplos abaixo : • a idade de uma pessoa e guardada em dois lugares diferentes no Banco de dados, será que a idade está
igual nos dois lugares ? • um certo campo guarda a soma de outros vários campos, será que o valor deste campo estará sempre
correspondendo a soma dos outros campos ? • um certo campo de código de cidade foi preenchido com o número 10, será que existe alguma cidade
cadastrada com o código 10 ? Integridade significa a segurança de que os dados estarão sempre corretos e disponíveis para consulta. Um banco de dados deve ser capaz de garantir a integridade dos dados e formas de recuperá-la se acontecer algum problema. Quanto maior for a aplicação, o volume de dados manipulados e o número de pessoas que manipulam esses dados, o banco de dados se torna mais necessário.
4.3 Gerenciamento de Transações Freqüentemente, muitas operações em um banco de dados constituem uma única unidade lógica de trabalho. Veja, por exemplo, uma transferência de fundos entre contas bancárias, responsável pelo débito na conta A e crédito na conta B. Antes de mais nada, é essencial que ocorram ambas as operações, de crédito e débito, ou nenhuma delas deverá ser realizada. Isto é, ou a transferência de fundos acontece como um todo ou nada deve ser feito. Esse tudo ou nada é chamado atomicidade. Ainda mais, é necessário que a transferência de fundos preserve a consistência do banco de dados. Ou seja, a soma de A + B deve ser preservada. Essas exigências de corretismo são chamadas de consistência. Finalmente, depois da execução com sucesso da transferência de fundos, os novos valores de A e B
devem persistir, a despeito das possibilidades de falhas no sistema. Esta persistência é chamada durabilidade. Uma transação é uma coleção de operações que desempenha uma função lógica única dentro de uma aplicação do sistema de banco de dados. Cada transação é uma unidade de atomicidade e consistência. Assim, exigimos que as transações não violem nenhuma das regras de consistência dos bancos de dados. Ou seja, o banco de dados estava consistente antes do inicio da transação e deve permanecer consistente após o termino com sucesso de uma transação. Entretanto, durante a execução de uma transação, será necessário aceitar inconsistências temporariamente. Essa inconsistência temporária, embora necessária, pode gerar problemas caso ocorra uma falha. É responsabilidade do programador definir, de modo apropriado, as diversas transações , de tal forma que cada uma preserve a consistência do banco de dados. Por exemplo, a transação para a transferência de fundos da conta A para a conta B poderia ser composta por dois programas distintos : um para debito na conta A e outro para credito na conta B. A execução destes dois programas um após o outro irá manter a consistência do banco de dados. Entretanto, cada programa executado isoladamente não leva o banco de dados de um para outro estado consistente. Logo, esses programas separados não são transações. Assegurar as propriedades de atomicidade e durabilidade é também responsabilidade do sistema de banco de dados – especialmente, os componentes de gerenciamento de transações. Na ausência de falhas, todas as transações se completam com sucesso e a atomicidade é garantida. No entanto, devido aos vários tipos de falhas possíveis, uma transação pode não se completar com sucesso. Se estivermos empenhados em garantir a atomicidade, uma transação incompleta não poderá comprometer o estado do banco de dados. Assim, o banco de dados precisa retornar ao estado anterior em que se encontrava antes do início dessa transação. É responsabilidade do sistema de banco de dados detectar as falhas e recuperar o banco de dados, garantindo seu retorno à seu último estado consistente. Pôr fim, quando acontecem muitas transações no banco de dados concorrentemente, a consistência do banco de dados pode ser violada, mesmo que essas transações, individualmente, estejam corretas. É responsabilidade do gerenciador de controle de concorrência controlar a interação entre transações concorrentes de modo a garantir a consistência do banco de dados. Os sistemas de banco de dados projetados para o uso em computadores pessoais podem não apresentar todas estas funções. Por exemplo, muitos sistemas de pequeno porte não permitem o uso simultâneo do banco de dados. Outros deixam as tarefas de backup e recuperação no banco para o usuário. Este tipo de produto está voltado à administração de pequenos volumes de dados, exigindo poucos recursos físicos - e especialmente memória principal. Apesar do baixo custo, esses sistemas de recursos limitados voltados para pequenos bancos de dados pessoais são inadequados às necessidades de empresas de médio e grande portes.
4.4 Administração de Memória Normalmente, os bancos de dados exigem um grande volume de memória. Um banco de dados corporativos é usualmente medido em termos de gigabytes, ou, para bancos de dados de grande porte, terabytes. Já que a memória do computador não pode armazenar volumes tão grandes de dados, as informações são armazenadas em discos. Os dados são transferidos dos discos para a memória quando necessário. Uma vez que essa transferência é relativamente lenta comparada à velocidade do processador, é imperativo que o sistema de banco de dados estruture os dados de forma a minimizar a necessidade de movimentação entre disco e a memória.
O objetivo de um sistema de banco de dados é simplificar e facilitar o acesso a aos dados. Visões de alto nível ajudam a alcançar esses objetivos. Os usuários do sistema não devem ser desnecessariamente importunados com detalhes físicos relativos à implementação do sistema. Todavia, um dos fatores mais importantes de satisfação ou insatisfação do usuário com um sistema de bloco de dados é justamente seu desempenho. Se o tempo de resposta é demasiado, o valor do sistema diminui. O desempenho de um sistema de banco de dados depende da eficiência das estruturas usadas para a representação dos dados, e de quanto o sistema está apto a operar essas estruturas de dados. Como acontece em outras áreas do sistemas computacionais, não se trata somente do consumo de espaço e tempo, mas também da eficiência de um tipo de operação sobre outra.
4.5 Administração de Banco de Dados Uma das principais razões que motivam o uso dos SGBDs é o controle centralizado tanto dos dados quanto dos programas de acesso a esses dados. A pessoa que centraliza esse controle do sistema é chamado administrador de dados (DBA). Dentre as funções de um DBA destacam as seguintes: Definição do esquema. O DBA cria o esquema do banco de dados original escrevendo um conjunto de definições que são transformadas pelo compilador DDL em um conjunto de tabelas armazenadas de modo permanente no dicionário de dados. Definição da estrutura de dados e métodos de acesso. O DBA cria estruturas de dados e métodos de acesso apropriados escrevendo com um conjunto de definições, as quais são traduzidas pelo compilador de armazenamento de dados e pelo compilador de linguagem de definição de dados. Esquema de modificações da organização física. Os programadores realizam relativamente poucas alterações no esquema do banco de dados ou na descrição da organização física de armazenamento por meio de um conjunto de definições que serão usadas ou pelo compilador DDL ou pelo compilador de armazenamento de dados e definição de dados. Gerando modificações na tabela apropriada, interna aos sistemas (por exemplo, no dicionário de dados). Especificação de regras de integridade. Os valores dos dados armazenados no banco de dados devem satisfazer certas restrições para a manutenção da sua integridade. Por exemplo, o número de horas que um empregado pode trabalhar durante uma semana não deve ser superior a um limite especificado. Tal restrição precisa ser explicitado pelo administrador de dados. As regras de integridade são tratadas por uma estrutura especial do sistema que é consultada pelo sistema de banco de dados sempre que uma atualização está em curso no sistema.
4.6 Usuários de Banco de Dados A meta básica de um sistema de banco de dados é proporcionar um ambiente para recuperação de informações e para o armazenamento de novas informações do banco de dados. Há quatro tipos de usuários de sistemas de banco de dados, diferenciados por suas expectativas de interação com o sistema : Programadores de aplicações são profissionais em computação que interagem com o sistema por meio de chamadas DML, as quais são envolvidas por programas escritos na linguagem hospedeira (por exemplo, COBOL, PL/, pascal, C). Esses programas são comumente referidos como programas de aplicação. Exemplos : em um sistema bancário incluem programas para gerar relação de cheques pagos, para crédito em contas, para débitos em conta e para transferência de fundos entre contas. Uma vez que a sintaxe da DML é, em geral, completamente diferente da sintaxe da língua hospedeira, as chamadas DML são, normalmente, precedidas por um caráter especial antes que o código apropriado possa ser gerado. Um pré-processamento, chamado pré-compilador DML, converte os comandos DML para as chamadas normais em procedimentos da linguagem hospedeira. O programa resultante é, então, submetido ao compilador da linguagem hospedeira, a qual gera o código de objeto apropriado.
Existem tipos especiais de linguagem de programação que combinam estruturas de controle de linguagem semelhantes ao pascal com estruturas de controle para manipulação dos objetos do banco de dados (por exemplo, relações). Essas linguagens, muitas vezes chamadas linguagens de quarta geração, freqüentemente incluem recursos especiais para facilitar a geração da formulários e a apresentação de dados no monitor. A maior parte dos sistemas de banco de dados comerciais inclui linguagens de quarta geração. Usuários navegantes são usuários comuns que interagem com o sistema chamando um dos programas aplicativos permanentes já escritos, como, por exemplo, um usuário que pede a transferência de dinheiro da conta A para a conta B por telefone, usando para isso um programa chamado transfer. Esse programa pede ao usuário o valor a ser transferido, o número da conta para crédito e o número da conta para débito. 5 Linguagem SQL
Capítulo
5 Pré-requisitos • Capítulo 2 – Modelo Relacional Conteúdo deste Módulo 1. Comandos DDL 2. Comando DML
5.1 Introdução SQL significa Structured Query Language, ou seja linguagem de consulta estruturada. Ela e baseada na álgebra relacional. É bem parecida com a linguagem natural ( no caso, o inglês ). O SQL e utilizado como linguagem interna de comunicação entre o aplicativo e a base de dados e entre bancos de dados de fabricantes diferentes. O SQL padrão tem apenas 19 comandos padronizados. Todos os fabricantes de aplicativos SQL adicionam comandos próprios criando assim seu próprio SQL. A linguagem SQL possui dois tipos de comandos muito bem definidos : • DDL - Data Definition Language, ou Linguagem de definição de dados permite definir a estrutura e a
organização dos dados. • DML - Data Modification Language, ou Linguagem de modificação dos dados permite incluir,
excluir, alterar e consultar dados armazenados nas estruturas. Referências Bibliográficas KORTH, Henry F. Sistemas de Bancos de Dados. São Paulo : Makron Books, 1999.
5.2 Comandos DDL CREATE DATABASE Empresa; O comando CREATE DATABASE ira criar uma nova base de dados chamada Empresa. USE EMPRESA;
O comando USE passa a instrução para o Gerenciador do Banco de Dados usar a Base de Dados Empresa. CREATE TABLE nome_da_tabela ( lista de atributos ) O comando CREATE TABLE ira criar novas tabelas na base de dados. Onde a lista de atributos deve conter o nome do atributo, o tipo e a informação se ele pode ser deixado vazio ou não. Existem três tipos básicos de colunas no MySQL : tipos numéricos, de string e texto, data e hora. 5.2.1 Tipos numéricos: São usados para armazenar números. mumeric ou decimal ( dec ) São usados para armazenar valores exatos com ponto flutuante e
geralmente são utilizados p/ armazenar valores monetários. integer ( int ) È um inteiro padrão, armazenado em 4 bytes, com uma faixa de 2 elevado
à 32 valores possíveis. Variações de INTEGER : tinyint, smallint, Armazenados em 1, 2, 3, 8 bytes respectivamente. mediumint,bigint float É um número de ponto flutuante com precisão. double É um número de ponto flutuante com dupla precisão. Os sinônimos para
DOUBLE são REAL e DOUBLE PRECISION. 5.2.2 Tipos de string e de texto char ( n ) É usado para armazenar strings com comprimento fixo (n). O comprimento máximo de uma coluna tipo char é de 255 caracteres.Este
tipo ocupa mais espaço no disco, mas é mais rápido p/ recuperar as linhas de uma tabela .
varchar ( n ) Armazena strings com comprimento variável. Faixa de 0 à 255. text, blob e variações Os tipos text são usados para armazenar partes maiores de texto do que vc
pode colocar em um char ou varchar. enum( ‘m’, ‘f’) Este tipo permite listar um conjunto de valores possíveis set O tipo set é parecido com enum, exceto pelo fato de que as linhas podem
conter um conjunto de valores a partir do conjunto enumerado. 5.2.3 Tipos de data e hora date O tipo date armazena uma data. O MySQL espera a data na ordem ISSO
ano-mês-dia; as datas são exibidas como YYYY-MM-DD. time Este tipo armazena hora, exibida como HH:MM:SS. datetime É uma combinação dos dois tipos anteriores. O formato é YYYY-MM-
DD HH:MM:SS. timestamp É um tipo de coluna útil. Se vc não definir esta coluna em uma linha em
particular ou defini-la para NULL, será armazenada a hora na qual a linha foi inserida ou a hora da última alteração.
year ( 2 ) ou ( 4 ) Este tipo armazena um ano. Pode ser de 2 ou 4 caracteres . Os tipos disponíveis são : • char ( n ) - string de tamanho fixo n • varchar ( n ) - string de tamanho variável onde n é o tamanho máximo • integer [ (n) ] - inteiro com padrão de 4 bytes • float (n,m) - real com tamanho n com m decimais depois da virgula e ainda not null significa um
atributo que deve ser preenchido obrigatoriamente.
Veja o exemplo : CREATE TABLE PESSOA ( CPF int (11) not null primary key, Nome char (30) not null, Idade int ) DROP TABLE nome_da_tabela O comando DROP TABLE ira excluir tabelas da base de dados. Veja o exemplo : DROP TABLE PESSOA ; 5.3 Comandos DML 5.3.1 Insert O comando INSERT insere novas linhas na tabela. Podem ser inseridas uma ou mais linhas especificadas por expressões de valor. Sintaxe Insert INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] nome_tabela [(nome_coluna,...)]
VALUES ((expressão | DEFAULT),...),(...),...
[ ON DUPLICATE KEY UPDATE nome_coluna=expressão, ... ]
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] nome_tabela [(nome_coluna,...)]
SELECT ...
or INSERT [LOW_PRIORITY | DELAYED] [IGNORE]
[INTO] nome_tabela
SET nome_coluna=(expressão | DEFAULT), ...
[ ON DUPLICATE KEY UPDATE nome_coluna=expressão, ... ] Exemplo: insert into pessoa(CPF Nome Idade) values (‘321.654.987-99’,’João Hercules’,14 ) 5.4.1 Select Sintaxe SELECT SELECT [DISTINCT | DISTINCTROW | ALL]
expressão_select,...
[FROM tabelas_ref
[WHERE definição_where]
[GROUP BY {inteiro_sem_sinal | nome_col | formula} [ASC | DESC], ...
[HAVING where_definition]
[ORDER BY {inteiro_sem_sinal | nome_coluna | formula} [ASC | DESC], ...]
[LIMIT [offset,] row_count | row_count OFFSET offset]
O comando SELECT realiza consultas às bases de dados, utilizando mais de uma tabela se necessário, basta separar o nome das tabelas por vírgula e referenciar as chaves primárias e estrangeiras no comando where. Para ajudar a refinar consultas no MySQL existem os operadores: Operadores – há três conjuntos principais de operadores no MySQL :
5.4.2 Operadores aritméticos ( + ) adição, ( - ) subtração, ( * ) multiplicação, ( / ) divisão
Operadores de Comparação
= Igualdade != ou <> Diferença < Menor que <= Menor ou igual a > Maior que >= Maior ou igual a n BETWEEN min AND max Teste de faixa n IN(conjunto) Testa se o elemento pertence a um conjunto.
Pode ser usada uma lista de valores literais, expressões ou uma subconsulta. n IS NULL Usado para testar um valor null Operadores lógicos AND ( e ), OR (ou), NOT ( não) ou ! , NOT n Funções de Controle de Fluxo IF (e1, e2, e3) ex : select name, if(job = ‘Programmer’, “nerd”, “not a nerd”) as resultado from employee; CASE valor WHEN [valor_comparado] THEN resultado [WHEN [valor_comparado] THEN resultado...] [ELSE resultado] END Ex: Select workdate, case when workdate < 2000-01-01 then “archived” when workdate < 2003-01-01 then “old” else “current” end From assignment; 5.4.3 Funções de string Existem duas categorias : Funções de processamento de string
concat (s1, s2, ....) Concatena as strings em s1, s2, .... conv (n, base_orig.,nova_base) Converte o num n da base_orig. para a nova_base. length ( s ) Retorna o comprimento em caracteres da string s. load_file (nome_arquivo) Retorna o conteúdo do arq. armazenado como string. locate (agulha,palheiro,pos) Retorna a posição inicial da string agulha na string palheiro. A pesquisa
começará a partir de posição. lower (s) e upper (s) Converte a string s em letra minúscula ou maiúscula. quote (s) Aplica o escape em uma string s. Isto inclui colocar a string entre aspas
simples e inserir uma ( \ ). replace Retorna uma string com base no destino,com todas as (destino, localizar,substituir) incidências de localizar substituídas po substituir. substring ( s, posição, Retorna uma substring de s de tamanho comprimento, comprimento ) começando na posição. trim (s) Remove os espaços em branco à esquerda e à direita de s. ltrim ( ) Remove apenas os espaços em branco à esquerda. rtrim ( ) Remove apenas os espaços em branco à direita. Funções de Comparação de string LIKE Executa a procura em strings com curingas. EX : select * from depto where name like ‘%cesar%’ ( curingas ( % e ( _ )) ) RLIKE Executa a procura por expressões regulares. STRCMP Comparação da string. 5.4.4 Funções Numéricas abs Retorna o valor absoluto de n, ou seja, o valor sem sinal. ceiling (n) Retorna o valor de n arredondado para cima até o próximo inteiro. floor ( n ) Retorna o valor de n arredondado para baixo até o próximo inteiro. mod (n, m) Divide n por m e retorna o resto da divisão. div (n, m) Divide n por m e retorna o quociente inteiro da divisão. power (n, m) Retorna n elevado à potencia de m. rand ( n ) Retorna um número aleatório entre 0 e 1. n é opcional. round (n, [, d]) Retorna n arredondado para o próximo inteiro. Se vc fornecer d, n será
arredondado para d casas decimais. sqrt ( n ) Retorna a raiz quadrada de n. 5.4.5 Funções de data e hora adddate ( data, INTERVAL n tipo ) Somar datas. subdate (data, INTERVAL n tipo ) Subtrair datas. Ex: adddate( ) – adicionaremos 1 ano e 6 meses a partir de 1º de janeiro de 1999. select adddate (“1999-01-01”, INTERVAL “1-6” YEAR-MONTH ) ; curdate ( ), curtime ( ), now ( ) Retornam a data atual, a hora atual e a data e hora atual. date_format (data, formato) Reformata a data em qualquer formato desejado.
time_fomat (hora, formato) Reformata a hora em qualquer formato desejado dayname ( data ) Retorna o nome do dia em data ( ex : ‘Monday’ ). 5.4.6 Funções de Agrupamento avg ( coluna ) Retorna o valor médio na coluna. count ( coluna ) Retorna o número de valores na coluna. min ( coluna ) Retorna o menor valor na coluna. max ( coluna ) Retorna o maior valor na coluna. std ( coluna ) Retorna o desvio padrão dos valores na coluna. sum ( coluna ) Retorna a soma dos valores na coluna. 5.5.1 Update UPDATE atualiza uma coluna em registros de tabelas existentes com novos valores. A cláusula SET indica quais colunas modificar e os valores que devem ser dados. A cláusula WHEREi, se dada, especifica quais linhas devem ser atualizadas. Senão todas as linhas são atualizadas. Sintaxe Update UPDATE nome_tabela [, nome_tabela ...]
SET nome_coluna1=expr1 [, nome_coluna2=expr2 ...]
[WHERE definição_where]
Exemplo: Update Pessoa set Idade=16 where CPF=’ 321.654.987-99’ 5.6.1 Delete DELETE deleta linhas de nome_tabela que satisfaçam a condição dada por definição_where, e retorna o número de registros deletados. Se você executar um DELETE sem cláusula WHERE, todas as linhas são deletadas. Sintaxe Delete DELETE FROM table_name
[WHERE definição_where]
[ORDER BY ...]
[LIMIT row_count]
Exemplo: Delete from Pessoa where CPF=’ 321.654.987-99’
