Apostila BD

11. BANCOS DE DADOS................................................................................41.1. Banco de Dados (BD)...................................................................................41.2. Sistema de Gerncia de Banco de Dados (SGBD)....................................4

1.2.1.Processamento de Dados sem Banco de Dados....................................51.2.2.Processamento de dados com uso de SGBD.........................................51.2.3.Principais Componentes de um SGBD..................................................61.2.4.Funes de um SGBD.............................................................................6

1.3. Fluxo Operacional de Dados e Controle.....................................................81.4. Abstrao de Dados....................................................................................101.5. Modelos de Bancos de Dados....................................................................111.6. Independncia de Dados.............................................................................111.7. Especificao CODASYL para arquitetura BD.......................................121.8. Especificao ANSI/X3/SPARC para arquitetura BD............................121.9. Funes relacionadas ao SGBD ................................................................14

1.9.1.Administrador de Dados.......................................................................141.9.2.Administrador de Banco de Dados......................................................14

1.10.Arquiteturas para uso do SGBD ................................................................151.10.1.Mono-usurio.......................................................................................151.10.2.Multi-Usurio com Processamento Central ......................................151.10.3.Arquitetura Cliente/Servidor ..............................................................15

1.11.Fases do Projeto de BD ..............................................................................161.11.1.Construir o Modelo Conceitual.........................................................161.11.2.Construir o Modelo Lgico ................................................................161.11.3.Construir o Modelo Fsico..................................................................161.11.4.Avaliar o Modelo Fsico .....................................................................161.11.5.Implementar o BD...............................................................................16

2. MODELAGEM DE DADOS....................................................................172.1. Conceitos .....................................................................................................172.2. Requisitos para Modelagem de Dados......................................................172.3. Modelos Conceituais ..................................................................................172.4. Modelos Lgicos.........................................................................................18

2.4.1.Modelo Relacional.................................................................................182.4.2.Modelo de Rede.....................................................................................192.4.3.Modelo Hierrquico ..............................................................................20

2.5. Modelo de Dados Fsico.............................................................................23

23. MODELO ENTIDADE-RELACIONAMENTO (M.E.R.)....................233.1. Introduo ....................................................................................................233.2. Entidade .......................................................................................................233.3. Relacionamento...........................................................................................24

3.3.1.Auto-relacionamento.............................................................................253.3.2.Cardinalidade de Relacionamentos......................................................253.3.3.Cardinalidade Mxima..........................................................................263.3.4.Classificao de Relacionamentos Binrios .......................................273.3.5.Relacionamento ternrio .......................................................................303.3.6.Cardinalidade mnima ...........................................................................30

3.4. Notaes Alternativas.................................................................................313.5. Atributo........................................................................................................32

3.5.1.Domnio..................................................................................................323.5.2.Tipos de Atributos.................................................................................323.5.3.Atributo de Relacionamento.................................................................333.5.4.Identificador de Entidades ....................................................................333.5.5.Relacionamento Identificador (Entidade Fraca).................................343.5.6.Identificador de Relacionamentos........................................................34

3.6. Generalizao/Especializao....................................................................353.7. Entidade Associativa (Agregao) ............................................................373.8. Relacionamento Mutuamente Exclusivo ..................................................38

4. O MODELO RELACIONAL....................................................................394.1. Caractersticas das Tabelas - Modelo Relacional ...................................404.2. Conceitos Bsicos .......................................................................................41

4.2.1.Chave primria : (primary key) ............................................................414.2.2.Chave estrangeira : (foreign key) .........................................................414.2.3.Chave candidata ou alternativa ............................................................41

4.3. Normalizao...............................................................................................424.3.1.Ilustrao de um sistema a ser normalizado .......................................434.3.2.Anlise de Dependncia Funcional......................................................464.3.3.Formas Normais.....................................................................................504.3.4.Roteiro Prtico para Normalizao......................................................504.3.5.Exemplo de Normalizao....................................................................52

4.4. Transposio D.E.R para D.T.R (Diagrama de Tabelas Relacionais)...564.4.1.Simbologia adotada no modelo relacional ..........................................564.4.2.Anlise da Entidade no D.E.R..............................................................574.4.3.Anlise de Relacionamento ..................................................................57

34.5. Restries de integridade no modelo Relacional .....................................654.5.1.Integridade Lgica.................................................................................654.5.2.Integridade Fsica ..................................................................................65

4.6.Linguagens Relacionais................................................................................804.7.SQL (Structured Query Language) .............................................................85

4.7.1.DDL (Data Definition Language .........................................................864.7.2.DML (Data Manipulation Language).................................................934.7.3.DCL (Data Control Language).......................................................... 1074.7.4.Transaction Control............................................................................ 1104.7.5.Restries de Integridade usando Stored Procedures e Triggers ... 113

5. EXERCCIOS:......................................................................................... 1275.1.Exercicios de Modelagem de Dados......................................................... 1275.2.Exerccios de Normalizao:..................................................................... 1305.3.Exerccios de SQL...................................................................................... 1335.4.Execcios de Algebra Relacional .............................................................. 138

6. BIBLIOGRAFIA............................... .................................................142

41. BANCOS DE DADOS

1.1. BANCO DE DADOS (BD)

Um Banco de Dados (BD) pode ser definido como uma coleo dedados interrelacionados, armazenados de forma centralizada ou distribuda,com redundncia controlada, para servir a uma ou mais aplicaes.

1.2. SISTEMA DE GERNCIA DE BANCO DE DADOS (SGBD)

Conjunto de software para gerenciar (definir, criar, modificar,usar) um BD e garantir a integridade e segurana dos dados. O SGBD ainterface entre os programas de aplicao e o BD. Em ingls denominadoDataBase Management System (DBMS).

51.2.1. PROCESSAMENTO DE DADOS SEM BANCO DE DADOS

Dados de diferentes aplicaes no esto integrados, pois so projetadospara atender a uma aplicao especfica.

Problemas da falta de integrao de dados:O mesmo objeto da realidade mltiplas vezes representado na base de

dados. Exemplo: dados de um produto em uma indstria.Redundncia no controlada de dados: No h gerncia automtica da

redundncia, o que leva a inconsistncia dos dados devido aredigitao de informaes

Dificuldade de extrao de informaes: os dados so projetados paraatender aplicaes especificas gerando dificuldades para ocruzamento de informaes

Dados pouco confiveis e de baixa disponibilidade

1.2.2. PROCESSAMENTO DE DADOS COM USO DE SGBD

Os dados usados por uma comunidade de usurios so integrados noBanco de Dados. Cada informao armazenada uma nica vez, sendo que aseventuais redundncias so controladas pelo sistema em computador, ficandotransparentes para os usurios.

61.2.3. PRINCIPAIS COMPONENTES DE UM SGBD

Dicionrio de dados (Data Dictionary):

Descreve os dados e suas relaes em forma conceitual e independente deseu envolvimento nas diversas aplicaes. Fornece referncias cruzadas entreos dados e as aplicaes.

Linguagem de definio de dados (DDL - Data Definition Language):

Descreve os dados que esto armazenados no BD. As descries dos dadosso guardadas em um meta banco de dados.

Linguagem de acesso (DML - Data Manipulation Language):

Usada para escrever as instrues que trabalham sobre a base de dados,permitindo o acesso e atualizao dos dados pelos programas de aplicao.

Linguagem de consulta (QUERY):

Permite que o usurio final, com poucos conhecimentos tcnicos, possaobter de forma simples, informaes do BD.

Utilitrios administrativos:

Programas auxiliares para carregar, reorganizar, adicionar, modificar adescrio do BD, obter cpias de reserva e recuperar a integridade fsica emcaso de acidentes.

1.2.4. FUNES DE UM SGBD

Um princpio bsico em BD determina que cada item de dado deveriaser capturado apenas uma vez e ento armazenado, de modo que possa tornardisponvel para atender a qualquer necessidade de acesso qualquer momento.

7Alguns pontos importantes so:Independncia dos dados: O SGBD deve oferecer isolamento das

aplicaes em relao aos dados. Esta caracterstica permitemodificar o modelo de dados do BD sem necessidade de reescreverou recompilar todos os programas que esto prontos. As definiesdos dados e os relacionamentos entre os dados so separados doscdigos os programas.

Facilidade uso/desempenho: Embora o SGBD trabalhe com estruturasde dados complexas, os arquivos devem ser projetados para atender adiferentes necessidades, permitindo desenvolver aplicaes melhores,mais seguras e mais rapidamente. Deve possui comandos poderososem sua linguagem de acesso.

Integridade dos dados: O SGBD deve garantir a integridade dos dados,atravs da implementao de restries adequadas. Isto significa queos dados devem ser precisos e vlidos.

Redundncia dos dados: O SGBD deve manter a redundncia de dadossob controle, ou seja, ainda que existam diversas representaes domesmo dado, do ponto de vista do usurio como se existisse umanica representao.

Segurana e privacidade dos dados: O SGBD deve assegurar que estess podero ser acessados ou modificados por usurios autorizados.

Rpida recuperao aps falha: Os dados so de importncia vital eno podem ser perdidos. Assim, o SGBD deve implementar sistemasde tolerncia a falhas, tais como estrutura automtica de recover euso do conceito de transao.

Uso compartilhado: O BD pode ser acessado concorrentemente pormltiplos usurios.

Controle do espao de armazenamento: O SGBD deve mantercontrole das reas de disco ocupadas, evitando a ocorrncia de falhaspor falta de espao de armazenamento.

81.3. FLUXO OPERACIONAL DE DADOS E CONTROLE

PROGRAMA DE APLICACAO

SISTEMA OPERACIONAL

BASE DADOS

1

AREA LOCAL DO PROGRAMA

10

S.G.B.D 5

AREA DE 9 ENTRADA/SAIDA

2 6

ESQUEMAS, DICIONARIO

DE DADOS, DIRETORIOS 3 7

FLUXO DE DADOS

FLUXO DE CONTROLE

8

4

91 - O programa do usurio comunica-se com o SGBD utilizando DMLpedindo a leitura de um registro lgico.

2 - O SGBD emite um comando para o S.O. para leitura dosesquemas (META DADOS).

3 - O S.O. acessa os esquemas.

4 - Os Meta-dados so transferidos para rea de E/S do SGBD.

5 - O SGBD consulta os Meta-dados para saber como traduzir ocomando do usurio.

6 - O SGBD emite os comandos para que o S.O. leia os registros fsicos necessrios.

7 - O S.O. acessa a base de dados.

8 - Os registros fsicos so transferidos para rea de E/S do SGBD.

9 - O SGBD seleciona os dados necessrios para formar o(s) registro(s) lgico(s) pedidos pelo usurio, se necessrio faz a transformao, e coloca o registro lgico na rea do usurio/aplicao.

10 - O SGBD envia ao programa de aplicao um cdigo indicando fimde comando.

10

1.4. ABSTRAO DE DADOS

Um propsito central de um SGBD proporcionar aos usurios umaviso abstrata dos dados, isto , o sistema esconde certos detalhes de como osdados so armazenados ou mantidos. No entanto, os dados precisam serrecuperados eficientemente.

A preocupao com a eficincia leva a concepo de estruturas dedados complexas para representao dos dados no BD. Porm, uma vez queSGBD so freqentemente usados por pessoas sem treinamento na rea decomputao, esta complexidade precisa ser escondida dos usurios. Isto conseguido definindo-se diversos nveis de abstrao pelos quais o BD podeser visto:

NVEL FSICO: o nvel mais baixo de abstrao, no qual se descrevecomo os dados so armazenados. Estruturas complexas, de baixonvel, so descritas em detalhe.

NVEL CONCEITUAL: o nvel que descreve quais os dados sorealmente armazenados no BD e quais os relacionamentos existentesentre eles. Este nvel descreve o BD como um pequeno nmero deestruturas relativamente simples. Muito embora a implementao deestruturas simples no nvel conceitual possa envolver estruturascomplexas no nvel fsico, o usurio do nvel conceitual noprecisa saber disto.

NVEL VISO: Este o nvel mais alto de abstrao, no qual se expeapenas parte do BD. Na maioria das vezes os usurios no estopreocupados com todas as informaes do BD e sim com apenasparte delas (Vises dos Usurios)

11

1.5. MODELOS DE BANCOS DE DADOS

Um modelo de (banco de) dados uma descrio dos tipos deinformaes que esto armazenadas em um banco de dados, ou seja, adescrio formal da estrutura de BD.

Estes modelos podem ser escritos em linguagens textuais ou linguagensgrficas. Cada apresentao do modelo denominado esquema de banco dedados.

Se tomarmos como exemplo uma indstria, o modelo de dados devemostrar que so armazenadas informaes sobre produtos, tais como cdigo,descrio e preo. Porm o modelo de dados no vai informar quais produtosesto armazenados no Banco de Dados.

No projeto de um banco de dados, geralmente so considerados 3modelos: conceitual, lgico e fsico.

Modelo conceitual: uma descrio mais abstrata da base de dados.No contm detalhes de implementao e independente do tipo deSGBD usado. o ponto de partida para o projeto da base de dados.

Modelo Lgico: a descrio da base de dados conforme vista pelosusurio do SGBD (programadores e aplicaes). dependente do tipode SGBD escolhido, mas no contm detalhes da implementao(uma vez que o SGBD oferece abstrao e independncia de dados).

Modelo fsico (interno): Descrio de como a base de dados armazenada internamente. Geralmente s alterada para ajuste dedesempenho. A tendncia dos produtos modernos ocultar cada vezmais os detalhes fsicos de implementao.

1.6. INDEPENDNCIA DE DADOS

Independncia de dados a nvel fsico: a capacidade de se modificar o modelofsico, sem precisar reescrever os programas de aplicao.

Independncia dados a nvel lgico: a capacidade de se modificar o esquemalgico, sem a necessidade de reescrever os programas de aplicao.Modificaes no nvel lgico so necessrias sempre que a estrutura lgicado BD for alterada. Em alguns casos a recompilao pode ser requerida.

12

1.7. ESPECIFICAO CODASYL PARA ARQUITETURA BD

Primeira especificao padro de BD conhecida, no papel 1960, implementada 1971 - Modelo DBTG-CODASYL(Data Base Task Group Conference on Data Systems and Languages)

Introduziu o conceito de SCHEMA ( descrio completa do BD) e SUB-SCHEMA (descrio do dados que so conhecidos por um ou mais programasde aplicao)

1.8. ESPECIFICAO ANSI/X3/SPARC PARA ARQUITETURA BD

Proposta de modelo de referncia para arquiteturas de BD. Teve incio em 1972 (Relatrio provisrio ) e terminou em 1983 (Relatrio final ).

Abordagem proposta por um grupo de trabalho estabelecido peloSPARC(Standard Planning and Requeriments Committe) doANSI/X3(American National Standards Committe on Computers andInformation Processing).

Os objetivos do grupo de estudo, foram os de determinar as reas, seexistentes, de tecnologia de BD onde fosse apropriada uma atividade depadronizao, e de produzir um conjunto de recomendaes para aes emcada uma dessas reas.

Trabalhando sobre estes objetivos, o grupo verificou que osINTERFACES seriam os nicos aspectos do SBD possivelmente passveis deserem padronizados.

13

Grupo sugere trs esquemas:

ESQUEMA EXTERNO 1 ESQUEMA EXTERNO 2 ESQUEMA EXTERNO 3

S.G.B.D. ESQUEMA CONCEITUAL

ESQUEMA INTERNO

BD

APLICACAO 1 APLICACAO 2 APLICACAO 3

VISAO USUARIO

VISAO GLOBAL (de toda empresa)

VISAO IMPLEMENTACAO (fisica)

14

1.9. FUNES RELACIONADAS AO SGBD

1.9.1. ADMINISTRADOR DE DADOS

Gerenciar o dado como um recurso da empresa.

Planejar, desenvolver e divulgar as bases de dados da empresa.

Permitir a descentralizao dos processos, mas manter centralizado os dados.

Permitir fcil e rpido acesso as informaes a partir dos dados armazenados

O grande objetivo de administrador de dados permitir que vrios usurioscompartilhem os mesmos dados. Deste modo, os dados no pertencem anenhum sistema ou usurio de forma especfica, e sim, organizao comoum todo. Assim, o administrador de dados se preocupa basicamente com aorganizao dos dados e no com o seu armazenamento.

1.9.2. ADMINISTRADOR DE BANCO DE DADOS

O DBA (DataBase Administrator) pessoa ou grupo de pessoasresponsvel pelo controle do SGBD. So tarefas do DBA:

Responsabilidade pelos modelos lgico e fsico (definindo a estrutura dearmazenamento)

Coordenar o acesso ao SGBD (usurios e senhas)

Definir a estratgia de backup

Melhorar o desempenho do SGBD

Manter o dicionrio de dados

15

1.10. ARQUITETURAS PARA USO DO SGBD

1.10.1. MONO-USURIO

BD est no mesmo computador que as aplicaesNo h mltiplos usuriosRecuperao geralmente atravs de backupTpico de computadores pessoais

1.10.2. MULTI-USURIO COM PROCESSAMENTO CENTRAL

BD est no mesmo computador que as aplicaesMltiplos usurios acessando atravs de terminaisTpico de ambientes com mainframe

1.10.3. ARQUITETURA CLIENTE/SERVIDOR

Multi-usurioServidor dedicado ao Banco de Dados, executando o SGBDAs estaes clientes executam apenas as aplicaesTrfego na rede menorArquitetura atualmente em uso

16

1.11. FASES DO PROJETO DE BD

1.11.1. CONSTRUIR O MODELO CONCEITUAL

Modelo de alto nvel, independente da implementaoEtapa de levantamento de dadosUso de uma tcnica de modelagem de dadosAbstrao do ambiente de hardware/software

1.11.2. CONSTRUIR O MODELO LGICO

Modelo implementvel, dependente do tipo de SGBD a ser usadoConsidera as necessidades de processamentoConsidera as caractersticas e restries do SGBDEtapa de normalizao dos dados

1.11.3. CONSTRUIR O MODELO FSICO

Modelo implementvel, com mtodos de acesso e estrutura fsicaConsidera necessidades de desempenhoConsidera as caractersticas e restries do SGBDDependente das caractersticas de hardware/software

1.11.4. AVALIAR O MODELO FSICO

Avaliar o desempenho das aplicaesAvaliar os caminhos de acesso aos dados e estruturas utilizadas

1.11.5. IMPLEMENTAR O BD

Etapa de carga (load) dos dadosGerar as interfaces com outras aplicaes

17

2. MODELAGEM DE DADOS

2.1. CONCEITOS

Abstrao: processo mental atravs do qual selecionamos determinadaspropriedades ou caractersticas dos objetos e exclumos outras, consideradasmenos relevantes para o problema que esta sendo analisado.

Modelo: uma abstrao, uma representao simplificada, de uma parcela domundo real, composta por objetos reais.

Modelagem: atividade atravs da qual se cria um modelo.

Modelo de dados: Um modelo de dados uma descrio das informaes quedevem ser armazenadas em um banco de dados, ou seja, a descrioformal da estrutura de BD (descrio dos dados, dos relacionamentos entreos dados, da semntica e das restries impostas aos dados).

2.2. REQUISITOS PARA MODELAGEM DE DADOS

Entender a realidade em questo, identificando os objetos que compe a parteda realidade que vai ser modelada..

Representar formalmente a realidade analisada, construindo um modelo dedados.

Estruturar o modelo obtido e adequ-lo ao SGBD a ser usado, transformando omodelo conceitual em modelo lgico.

2.3. MODELOS CONCEITUAIS

So usados para descrio de dados no nvel conceitual. Proporcionamgrande capacidade de estruturao e permitem a especificao de restries dedados de forma explcita. Exemplos:

Modelo Entidade-Relacionamento (M.E.R.)Modelo de Semntica de dadosModelo InfolgicoModelos Orientados para Objetos (OO)

18

2.4. MODELOS LGICOS

So usados na descrio dos dados no nvel lgico. Em contraste commodelos conceituais, esses modelos so usados para especificar tanto aestrutura lgica global do BD como uma descrio em alto nvel daimplementao.

2.4.1. MODELO RELACIONAL

Um BD relacional possui apenas um tipo de construo, a tabela. Umatabela composta por linhas (tuplas) e colunas (atributos). Os relacionamentosentre os dados tambm so representados ou por tabelas, ou atravs dareproduo dos valores de atributos.

Idias bsicas Edward F. Codd , laboratrio pesquisas da IBM em 1970

Exemplo : Considere o BD composto de clientes e contas.

NOME RUA CIDADE N CONTAJos Rua A JF 40Juca Rua B RJ 30Juca Rua B RJ 38 *Carlos Rua C SP 45Carlos Rua C SP 38 *

N CONTA SALDO40 1.000,0030 2.000,0038 2.500,0045 3500,00

* compartilham a mesma conta, devem ser scios

19

2.4.2. MODELO DE REDE

O BD em rede um grafo, onde os ns representam os registros e osarcos representam os relacionamentos entre os registros, atravs de ligaespai-filho. Diferente do modelo hierrquico, um registro pode possuir diversosregistros pai.

Origem na linguagem de programao Cobol, Primeiro SGBD comercialIDS (Integrated Data Store) projetado para a General Eletric na dcada de 60.

Extenso : DBTG-CODASYL(Data Base Task Group Conference onData Systems and Languages) , 1 especificao padro de BD em 1971.

Exemplos : TOTAL, IDMS, ADABAS

JOSE RUA A JF

CARLOS RUA C SP

40 1.000,00

45 3.500,00

38 2.500,00

30 2.000,00JUCA RUA B RJ

20

2.4.3. MODELO HIERRQUICO

Um BD hierrquico uma coleo de rvores de registros. Os registrosso usados para representar os dados e ponteiros so usados para representar orelacionamento entre os dados, numa ligao do tipo pai-filho. A restrio que um determinado registro somente pode possuir um registro pai.

Exemplo : 1 SGBD da IBM IMS (Information Management System),DMS2 SGBD da Unisys.

JOS RUA A JF JUCA RUA B RJ CARLOS RUA C SP

40 1.000,00 30 2.000,00

45 3.500,0038 2.500,00

38 2.500,00

21

EXEMPLOS DOS MODELOS

A) MODELO RELACIONAL :

FORNECEDOR ( fabricante )

RAZO TECNOLOGIAUnisys PropriaElebra Control DataFlex Disk ShugartIBM PropriaMicrolab Ampex

PEA (disco)

CODIGO TIPO ACIONAMENTO410 Flexvel Step Motor416 Rgido Voice Coil Linear421 Rgido Step Motor427 Rgido Voice Coil Rotatrio

FORNECIMENTO

RAZAO CODIGO PREOUnisys 416 6.360,00Elebra 416 2.480,00Elebra 410 230,00Flex Disk 421 250,00Flex Disk 427 900,00IBM 416 5.808,00Microlab 427 1.100,00

22

B) MODELO REDE

UNISYS PROP. ELEBRA CON.DATA FLEX DISK SHUGART IBM PROP. MICROLAB AMPEX

6.360,00 2.480,00 230,00 250,00 900,00 5.808,00 1.100,00

410 FLEXIVEL STEP M 416 RIGIDO VOICE 421 RIGIDO STEP M 427 RIGIDO VC DAT

C) MODELO HIERRQUICO

416 RIGIDO VOICE COIL LINEAR

410 FLEXIVEL STEP M

UNISYS PROPRIA 6.380,00

ELEBRA C. DATA 2.420,00

ELEBRA C. DATA 230,00 IBM PROPRIA 5.808,00

427 RIGIDO VOICE COIL ROTATORIO

421 RIGIDO STEP M

F DISK SHUGART 900,00

F DISK SHUGART 250,00 MICROLAB AMPEX 1.100,00

23

2.5. MODELO DE DADOS FSICO

Usados para descrever os dados em seu nvel mais baixo. Capturam osaspectos de implementao do SGBD.

3. MODELO ENTIDADE-RELACIONAMENTO (M.E.R.)

3.1. INTRODUO

Apresentado por Peter Chen, em 1976 a tcnica mais difundida para construir modelos conceituais de bases de

dados o padro para modelagem conceitual, tendo sofrido diversas extensesEst baseado na percepo de uma realidade constituda por um grupo bsico

de objetos chamados ENTIDADES e por RELACIONAMENTOS entreestas entidades

Seu objetivo definir um modelo de alto nvel independente de implementaoO modelo representado graficamente por um Diagrama de Entidade-

Relacionamento (DER), que simples e fcil de ser entendido por usuriosno tcnicos

Conceitos centrais do MER: entidade, relacionamento, atributo,generalizao/especializao, agregao (entidade associativa)

3.2. ENTIDADE

Conjunto de objetos da realidade modelada sobre os quais deseja-se manterinformaes no Banco de Dados

Uma entidade pode representar objetos concretos da realidade (pessoas,automveis, material, nota fiscal) quanto objetos abstratos (departamentos,disciplinas, cidades)

A entidade se refere a um conjunto de objetos; para se referir a um objeto emparticular usado o termo instncia (ou ocorrncia)

No DER, uma entidade representada atravs de um retngulo que contm onome da entidade

PESSOA DEPARTAMENTO

24

3.3. RELACIONAMENTO

toda associao entre entidades, sobre a qual deseja-se manter informaesno Banco de Dados.

Os relacionamentos representam fatos ou situaes da realidade, onde asentidades interagem de alguma forma

Um dado por si s no faz uma informao, pois no tem sentido prprio; necessrio que haja uma associao de dados para que a informao sejaobtida.

Exemplos:Fornecimento: entre as entidades FORNECEDOR e MATERIALMatrcula: entre as entidades ALUNO e DISCIPLINAFinanciamento: entre as entidades PROJETO e AGENTE

FINANCEIRONo DER, os relacionamentos so representados por losangos, ligados s

entidades que participam do relacionamento

Diagrama de ocorrncias de relacionamentos:

DEPARTAMENTO EMPREGADOLOTAO

25

3.3.1. AUTO-RELACIONAMENTO

Relacionamento entre ocorrncias da mesma entidade.

Diagrama de ocorrncias no auto-relacionamento:

O papel da entidade no relacionamento indica a funo que umaocorrncia de uma entidade cumpre em uma ocorrncia de um relacionamento.

3.3.2. CARDINALIDADE DE RELACIONAMENTOS

A cardinalidade de uma entidade em um relacionamento expressa onmero de instncias da entidade que podem ser associadas a uma determinadainstncia da entidade relacionada.

Devem ser consideradas duas cardinalidades:Cardinalidade mnima de uma entidade o nmero mnimo de instncias

da entidade associada que devem se relacionar com uma instncia daentidade em questo.

Cardinalidade mxima de uma entidade o nmero mximo deinstncias da entidade associada que devem se relacionar com umainstncia da entidade em questo.

marido

esposa

PESSOA

CASAMENTO

26

3.3.3. CARDINALIDADE MXIMA

No projeto para BD relacional (como neste curso) no necessriodistinguir as cardinalidades que sejam maiores que 1. Assim, so usadosapenas as cardinalidades mximas 1 e n (muitos).

27

3.3.4. CLASSIFICAO DE RELACIONAMENTOS BINRIOS

A cardinalidade mxima usada para classificar os relacionamentosbinrios (aqueles que envolvem duas entidades).

a) Relacionamentos 1:1 (um-para-um)

Uma instncia da entidade A est associada com no mximo uma instnciada entidade B.Uma instncia da entidade B est associada com no mximo uma instnciada entidade A.

A B

A1 B1

A2 B2

A3 B3

28

b) Relacionamentos 1:N (um-para-muitos)

. Uma instncia da entidade "A" esta associada a qualquer nmero deinstncias da entidade "B".. Uma instncia da entidade "B", todavia, pose estar associada a nomximo uma instncia da entidade "A"

A1 B1

A2 B2

B3

B4

29

c) Relacionamentos N:N (muitos-para-muitos)

Uma instncia da entidade "A" esta associada a qualquer nmeroinstncias da entidades "B". Uma instncia da entidade "B" esta associada aqualquer nmero de instncia da entidades "A"

A B

A1 B1

A2 B2

A3 B3

A4 B4

30

3.3.5. RELACIONAMENTO TERNRIO

o relacionamento formado pela associao de trs entidades

Cardinalidade em relacionamentos ternrios:

3.3.6. CARDINALIDADE MNIMA

A cardinalidade mnima usada para indicar o tipo de participao daentidade em um relacionamento. Esta participao pode ser:

Parcial ou Opcional: quando uma ocorrncia da entidade pode ou noparticipar de determinado relacionamento; indicado pelacardinalidade mnima = 0 (zero).

Total ou Obrigatria: quando todas as ocorrncias de uma entidadedevem participar de determinado relacionamento; indicado pelacardinalidade mnima > 0 (zero).

31

Exemplos:

Um cliente pode fazer pedidos ou no, mas todos os pedidos devemestar associados a um cliente.

Todos os departamentos devem possuir pelo menos um empregadoalocado, e todos os empregados devem estar alocados em um departamento.

Parcialidade mnima: para um departamento ser criado, devem existempelo menos 10 empregados alocados.

3.4. NOTAES ALTERNATIVAS

Notao Santucci/MERISE: semntica participativa

Notao Setzer: semntica associativa

Notao Heuser: semntica associativa

1 NCLIENTE REALIZA

PEDIDO

1 NDEPTO ALOCA EMPREGADO

10


(0,N)

(1,1)DEPTO

ALOCA EMPREGADO


(1 (0,NDEPTO ALOCA EMPREGADO

32

3.5. ATRIBUTO

um dado que associado a cada ocorrncia de uma entidade ourelacionamento.Os atributos no possuem existncia prpria ou independente - esto sempreassociados a uma entidade ou relacionamento

Exemplos:Funcionrio: Matrcula, Nome, EndereoMaterial: Cdigo, DescrioFinanciamento: Valor total, MesesFornecedor: Nome, Endereo

3.5.1. DOMNIO

o conjunto de valores vlidos que um atributo pode assumir.Ex: Estado civil: solteiro, casado, divorciado, vivo

3.5.2. TIPOS DE ATRIBUTOS

a) Opcional/MandatrioOpcional: o atributo pode possuir um valor nulo (vazio). Ex: nmero de

telefoneMandatrio: o atributo deve possuir um valor vlido, no nulo. Ex:

nome do cliente

b) Monovalorado/MultivaloradoMonovalorado: o atributo assume um nico valor dentro do domnio.

Ex: data de nascimentoMultivalorado: o atributo pode assumir um nmero qualquer de valores

dentro do domnio. Ex: Telefone para contato

33

c) Atmico/CompostoAtmico: o atributo no pode ser decomposto em outros atributos. Ex:

IdadeComposto: o atributo composto por mais de um atributo. Ex: Endereo

3.5.3. ATRIBUTO DE RELACIONAMENTO

Assim como as entidades, os relacionamentos tambm podem possuiratributos.

3.5.4. IDENTIFICADOR DE ENTIDADES

Conjunto de atributos que tem a propriedade de identificar univocamente cadaocorrncia de uma entidade

Toda entidade deve possuir um identificadorO identificador deve ser mnimo, nico, monovalorado e mandatrio

34

3.5.5. RELACIONAMENTO IDENTIFICADOR (ENTIDADE FRACA)

Existem casos em que uma entidade no pode ser identificada apenascom seus prprios atributos, mas necessita de atributos de outras entidades comas quais se relaciona. Este relacionamento denominado RelacionamentoIdentificador. Alguns autores denominam uma entidade nesta situao deEntidade Fraca.

3.5.6. IDENTIFICADOR DE RELACIONAMENTOS

Uma ocorrncia de relacionamento diferencia-se das demais pelasocorrncias das entidades que participam do relacionamento. No exemplo

No exemplo, uma ocorrncia de ALOCAO identificada pelaocorrncia de Engenheiro e pela ocorrncia de Projeto. Ou seja, para cada par(engenheiro, projeto) h no mximo um relacionamento de alocao.

Em certos casos, ser necessrio o uso de atributos identificadores derelacionamentos. Por exemplo:

Como o mesmo mdico pode consultar o mesmo paciente em diversasocasies, necessrio o uso de um atributo que diferencie uma consulta daoutra.

35

3.6. GENERALIZAO/ESPECIALIZAO

A generalizao um processo de abstrao em que vrios tipos de entidadeso agrupados em uma nica entidade genrica, que mantm as propriedadescomuns

A especializao o processo inverso, ou seja, novas entidades especializadasso criadas, com atributos que acrescentam detalhes entidade genricaexistente

A entidade genrica denominada superclasse e as entidades especializadasso as subclasses. A superclasse armazena os dados gerais de uma entidade,as subclasses armazenam os dados particulares

Este conceito est associado idia de herana de propriedades. Isto significaque as subclasses possuem, alm de seus prprios atributos, os atributos dasuperclasse correspondente.

Usada quando necessrio caracterizar entidades com atributos prprios ouparticipao em relacionamentos especficos

36

Uma generalizao/especializao pode ser total ou parcial:

total quando, para cada ocorrncia da entidade genrica, existe sempreuma ocorrncia em uma das entidades especializadas.

parcial quando nem toda ocorrncia da entidade genrica possui umaocorrncia correspondente em uma entidade especializada.

37

3.7. ENTIDADE ASSOCIATIVA (AGREGAO)

O uso desta abstrao necessrio quando um relacionamento deve serrepresentado como uma entidade no modelo conceitual. Isto ocorre quando necessrio estabelecer um relacionamento entre uma entidade e umrelacionamento.

Para atender a esta situao foi criado o conceito de Entidade Associativa ouAgregao. A agregao simplesmente um relacionamento que passa a sertratado como entidade.

Considerando o exemplo

Se for necessrio adicionar a informao que, a cada consulta um oumais medicamentos podem ser prescritos ao paciente, ser necessrio criar umanova entidade (MEDICAMENTO). Esta entidade deve se relacionar com asconsultas, mas CONSULTA um relacionamento. Deve ser criada ento umaentidade associativa.

38

Outra forma alternativa de se representar a entidade associativa

3.8. RELACIONAMENTO MUTUAMENTE EXCLUSIVO

Neste tipo de relacionamento uma ocorrncia de um entidade pode estarassociada com ocorrncias de outras entidades, mas no simultaneamente.

AVIO

PASSAGEIRO

CARGATRANSPORTE

TRANSPORTE

39

4. O MODELO RELACIONAL

Foi introduzido pelo pesquisador da IBM Edward F. Codd, 1970.Duas caractersticas marcantes, razes de sucesso :

. estrutura de dados simples e uniforme

. fundamentao terica slida

o modelo que opera com os dados organizados como um conjunto derelaes.O modelo de dados Relacional representa o banco de dados com uma coleode tabelas

Representao tabular :

Toda relao pode ser vista como uma tabela, onde cada linha uma tupla e emcada coluna esto valores de um mesmo domnio.

Exemplo :

FORNECIMENTO

FORNECEDOR PEA PROJETO QUANTIDADE1 2 5 182 3 7 254 1 1 4

Relao = tabelaTupla = linhaAtributo = coluna

40

4.1. CARACTERSTICAS DAS TABELAS - MODELO RELACIONAL

a) Cada Tabela tem um nome nico atravs do qual ela referenciada.b) Cada Tabela contm um nmero fixo de colunas(grau tabela).c) No existem linhas iguais.d) A ordem das linhas irrelevante(identificao no feita pela

localizao, mas sim pelo valor da chave primria).e) Cada coluna tem um nome nico(diferente das demais colunas).f) A ordem das colunas irrelevante(a coluna identificada pelo seu nome).g) Cada coluna contm valores atmicos(no so permitidos grupos de

valores).h) Cada valor de coluna extraido de um determinado DOMNIO(conjunto

de valores possveis).i) Duas ou mais colunas podem ser definidas sobre um mesmo Domnio.j) Operaes sobre colunas diferentes exigem que as colunas pertenam ao

mesmo Domnio,k) Conexes entre Tabelas somente sero expressas atravs de valores das

colunas(Chave Estrangeira).

41

4.2. CONCEITOS BSICOS

4.2.1. CHAVE PRIMRIA : (PRIMARY KEY)

um atributo(coluna) ou uma combinao de atributos cujos valoresdistinguem uma linha das demais dentro de uma tabela.

NUMFUNC NOMEFUNC CPFFUNC DEPTOFUNCChave primria

4.2.2. CHAVE ESTRANGEIRA : (FOREIGN KEY)

um atributo ou uma combinao de atributos, cujos valores aparecemnecessariamente na chave primria de uma tabela.A chave estrangeira o mecanismo que permite a implementao derelacionamentos(navegabilidade) em um banco de dados relacional.

NUMFUNC NOMEFUNC CPFFUNC DEPTOFUNCChave primria Chave estrangeira

DEPTO NOMEDPTOChave primria

4.2.3. CHAVE CANDIDATA OU ALTERNATIVA

Em alguns casos, mais de um atributo ou combinaes de atributos podemservir para distinguir uma linha das demais. Um dos atributos (ou combinaode atributos) escolhido como chave primria, os demais atributos (oucombinaes) so denominados chaves CANDIDATAS

NUMFUNC NOMEFUNC CPFFUNC DEPTOFUNCChave primria Chave candidata Chave estrangeira

42

4.3. NORMALIZAO

O que ? o processo formal de exame e agrupamento de dados numa forma capaz

de suportar melhor as mudanas futuras, minimizando o impacto destasmudanas sobre a base de dados.

Segundo Edward F. Codd , normalizao um processo sistemtico ereversvel, que consiste em substituir um determinado conjunto de relaes porsucessivos conjuntos nos quais as relaes tenham uma estrutura mais simplese regular.

Principais objetivos :

Reduzir as redundnciasReduzir a necessidade de reestruturar as relaes quando novos tipos de

dados so introduzidos

Escopo :

A partir deste processo pode-se, gradativamente, substituir um conjuntode entidades e relacionamentos por um outro, o qual se apresenta purificadoem relao as anomalias de (incluso, alterao, excluso)

Concluso :

Durante a Modelagem Conceitual poderemos estar trabalhando sobreestruturas no normalizadas, pois o objetivo deste modelo com arepresentao semntica da realidade da empresa em primeiro lugar.

Nossa proposta que seja feita uma reviso a nvel de transposio doDER para o DTR, verificando as regras de normalizao antes da transposioentre os modelos Conceitual e Lgico da realidade modelada.

43

4.3.1. ILUSTRAO DE UM SISTEMA A SER NORMALIZADO

PEDIDO(Num-Ped, Data-Ped, Num-Cli, Nome-Cli, End-Cli ((Cod-Prod, Nome-Prod, Qtde-Ped,Preo-Prod, Total-Prod)), Total-Ped)

( ) Dentro dos parenteses esto os tens de dados que constituem oPedido

(( )) Os parenteses duplos envolvem os atributos do item da tupla doPedido. Esses (( )) so utilizados para indicar que mais do que um Pedido delinha pode compor um Pedido:

O tem de linha do Pedido chamado de GRUPO DE REPETIO

ProdutosNum-Ped

Data-Ped

Num-Cli

Nome-Cli

End-CliCod-Prod

Nome-Prod

Qtde-Ped

Preo-Prod

Total-prod

Total-Ped

100 1/3/99 100 Joo Rua A, 19 102030

BananaMaaLaranja

101520

0,101,000,20

1,0015,00 4,00

20,00200 2/4/99 100 Joo Rua A, 19 20

40MaaMamo

2010

1,000,50

20,00 5,00

25,00300 3/5/99 200 Jlio Rua B, 19 10

50BananaPra

2010

0,100,50

2,00 5,00

7,00400 4/7/99 300 Carlos Rua C, 20 10 Banana 10 0,10 1,00 1,00

44

Nesta EstruturaO que acontece se:

- O Cliente mudar o endereo

Estes problemas ocorrem na vida real

Devemos analisar tambm a redundncia, um mesmo Cliente cadavez que fizer um pedido vamos guardar (nome-cli, end-cli).

Anomalias de armazenamento.

1 Incluso :S possvel incluir um novo Cliente a partir de um pedido.

Se nosso sistema fosse, nica e exclusivamente baseado na tabelaapresentada at o momento, no poderamos cadastrar um novo Cliente emnossa tabela, a menos que surgisse um pedido para ele.

2 Excluso :Se houver a excluso do Pedido nmero 400, toda a informao do

Cliente Carlos ser perdida.

Neste caso, podemos perceber que o fato de um pedido conter, em suaestrutura, os dados do Cliente, vinculados diretamente a sua existncia, podenos levar simplesmente, perder esses dados quando um pedido for excludo.

3 Alterao :Se algum dado do Cliente 100 mudar, teremos que efetuar esta operao

em vrias linhas da tabela.

Neste caso ser necessrio processar a alterao em cada uma das linhasde cada um dos pedidos pertencentes a esse cliente

45

Um analista experiente, intuitivamente separaria os vriosatributos do Pedido em arquivos(TABELAS) distintos.

CLIENTEPEDIDOITEM-PEDIDOPRODUTO

A Normalizao realiza formalmente esta separao dos atributosem registros normalizados(CLIENTE, PEDIDO, ITEM-PEDIDO, PRODUTO)baseado na Dependncia existente entre cada atributo e sua chave primria.

Ela consegue essa separao de ENTIDADES baseada no naintuio(como acontece com um analista de sistemas experiente), mas atravsde uma metodologia formal, que no requer experincia anterior comcomputadores.

46

4.3.2. ANLISE DE DEPENDNCIA FUNCIONAL

Tcnica de normalizao adotada em nosso curso.

Dependncia Funcional :

O atributo B funcionalmente dependente do atributo A se, em qualquerinstante, um valor em A determina, de modo nico, o valor correspondente emB, na mesma relao.

Exemplo:

EMPREGADO#Num-Emp Nome-Emp Vlr-Sal-Emp

O Nome-Emp funcionalmente dependente do Num-Emp, pelo fato de cadaNum-Emp est associado sempre ao mesmo Nome-Emp.

Para denotar esta dependncia funcional, usa-se uma expresso na formaNum-Emp Nome-Emp. A expresso denota que a coluna Nome-Empdepende funcionalmente da coluna Num-Emp. Diz-se que a coluna Num-Emp o determinante da dependncia Funcional.De forma geral, o determinante de uma dependncia funcional pode ser umconjunto de colunas e no somente uma coluna como na definio acima.

47

Prope trs tipos de dependncias entre os atributos de uma tabela.

a) Dependncia Funcional Total:

Os atributos de uma tabela tem que depender da chave primria e somente dachave primria.Um atributo C totalmente funcionalmente dependente da chave primriacomposta pelo atributos A e B , quando for funcionalmente dependente de Ae B e no dependente funcionalmente de qualquer parte da chave primria.Exemplo :

ALOCAO# Num-Emp# Cod-Proj Qtde-horas-trab

- A quantidade de horas trabalhadas num projeto no funcionalmentedependente do cod-proj, porque no significa o total de horas do projeto eno funcionalmente dependente da matrcula do funcionrio, porque nosignifica o total de horas trabalhadas pelo empregado.- A quantidade de horas trabalhadas determinada, de modo nico, pelacomposio da matrcula do empregado e do cdigo do projeto, porquesignifica a quantidade de horas trabalhadas por empregado em umdeterminado projeto.

b) Dependncia Funcional Parcial :O atributo C parcialmente funcionalmente dependente da chave primria

composta pelos atributos A e B quando for funcionalmente dependente deA ou B e no de ambos A e B

# Cod-mat # Cod-forn

Nom-forn

Prc-mat

O atributo nom-forn funcionalmente dependente somente do atributocod-forn. O nome do fornecedor determinado, de modo nico pelo cdigodo fornecedor, independente dos materiais que so fornecidos.

48

A dependncia funcional parcial ocorre quando a chave primria darelao composta e se constitui numa anomalia que se deve ser evitada.

A soluo para o problema da dependncia parcial consiste na criaode uma nova relao, que ser composta pelo atributo ou atributos quedependem de parte da chave e a chave que determine, de modo nico estesatributos

# Cod-mat # Cod-forn

# Cod-forn Nom-forn

Prc-mat

c) Dependncia Funcional Transitiva

- O atributo C dependente funcional transitivo de A se C funcionalmentedependente de B e B funcionalmente dependente de A, na mesma relao.

# Num-emp DFT

D D Nom-emp F F T Data-adm-emp A B C

Cod-proj DF Data-term-proj

DF DF

- O atributo data-term-proj funcionalmente dependente do atributocod-proj, que por sua vez funcionalmente dependente do atributo Num-emp. Ento data-term-proj dependente transitivo de Num-emp.- A dependncia funcional transitiva constitui numa anomalia que deve serevitada.- A soluo para o problema da D.F.T. consiste na criao de uma novarelao que ser composta pelo atributo ou atributos que so dependentesfuncionais transitivos tendo como chave primria o atributo que determina atransitividade.

# Num-emp # Cod-proj

Nom-emp Data-term-proj

Data-adm-emp

Cod-proj

49

Resultado da anlise da dependncia Funcional:

Uma relao estar normalizada segundo a anlise da dependncia funcional,quando possuir uma nica chave primria, todos os atributos no chavesforem totalmente funcionalmente dependentes da chave primria eindependentes entre si, ou seja, aps a eliminao da dependncia funcionalparcial e transitiva, caso exista.

50

4.3.3. FORMAS NORMAIS:

1a Forma Normal :

Uma relao estar na 1a FN se no houver atributo representandoagrupamento e nem atributo repetitivo(multivalorado).

2a Forma Normal :

Uma relao estar na 2a FN, se e somente se, estiver na 1a FN e os seusatributos no chaves forem dependentes funcionais completos da chaveprimria.

3a Forma Normal :

Uma relao estar na 3a FN, se e somente se, estiver na 2 FN e todos osseus atributos no chaves forem dependentes no transitivos da chave primria.

4.3.4. ROTEIRO PRTICO PARA NORMALIZAO:

A)TRANSFORMAO DE RELAES NO NORMALIZADAS EMRELAOES NA 1 FN.- Escolher uma chave primria para a relao

- Separar da relao os atributos(ou grupos) repetitivos, transformando arelao em outras duas relaes.

. Uma delas contendo o grupo repetitivo e que ter como chave acombinao da chave primria da relao no normalizada e uma chave (ou +)escolhida(s) entre os atributos repetitivos. (Regra Geral)

. Outra que permanece com a chave original e os atributos restantes.

- Transformar atributos COMPOSTOS em atributos ATMICOS

51

B)TRANSFORMAO DAS RELAES EM 1 FN PARA RELAESNA 2 FN.- Examinar as relaes com chave primria composta e verificar se todos osatributos dependem funcionalmente de toda a chave ou apenas de parte dela.

- Os atributos que dependem de parte da chave, formam uma nova relao, cujachave primria a parte da chave da relao em 1 FN da qual dependem.

- Apenas os atributos que dependem totalmente da chave compostapermanecem na relao original.

C) TRANSFORMAO DAS RELAES EM 2 FN PARA RELAESEM 3 FN.- Examinar as dependncias funcionais entre todos os atributos das relaes em2 FN.

- Aqueles atributos que dependem de outro atributo da relao, que no a suachave, vo constituir uma nova relao cuja chave o atributo do qualdependem.ATENO : Esta chave continua como atributo na tabela Base pois oelo de ligao entre ambas.

52

4.3.5.EXEMPLO DE NORMALIZAO:

ENTIDADEATRIBUTO

PEDIDO

Num-Ped XData-Ped \Num-Cli \Nome-Cli \End-Cli \Cod-Prod ( \ )Nome-Prod ( \ )Qtde-Ped ( \ )Preo-Prod ( \ )Total-Prod ( \ )Total-Ped \

53

1 FN liminar atributos multivalorados e atributos representam agrupamento

ENTIDADEATRIBUTO

PEDIDO ITEM PED

Num-ped X XData-Ped \Num-Cli \Nome-Cli \Nome-log \Numero-log \Cidade-log \Estado-log \Cep-log \Cod-Prod XNome-Prod \Qtde-Ped \Preo-Prod \Total-Prod \Total-Ped \

54

2 FN Eliminar D.F.P

ENTIDADEATRIBUTO

PEDIDO ITEM PED PRODUTO

Num-Ped X XData-Ped \Num-Cli \Nome-Cli \Nome-Log \Numero-Log \Cidade-Log \Estado-Log \Cep-Log \Cod-Log X XNome-Prod \Qtde-Ped \Preo-Prod \Total-Prod \Total-Ped \

55

3 FN Eliminar D.F.T- Redundncia deve ser evitada. No devo guardar o que posso

calcular(Cuidado - carroa)

ENTIDADEATRIBUTO

PEDIDO ITEM PED PRODUTO CLIENTE

Num-Ped X XData-Ped \Num-Cli \ XNome-Cli \Nome-Log \Numero-Log \Cidade-Log \Estado-Log \Cep-Log \Cod-Prod X XNome-Prod \Qtde-Ped \Preo-Prod \Total-ProdTotal-Ped

56

4.4. TRANSPOSIO D.E.R PARA D.T.R (DIAGRAMA DE TABELASRELACIONAIS)

4.4.1. SIMBOLOGIA ADOTADA NO MODELO RELACIONAL

. James Martin

.

um opcional

um obrigatrio

vrios

N : N

1 : N

1 : 1

Bachman Notao de setas

57

4.4.2. ANLISE DA ENTIDADE NO D.E.R

Toda Entidade vai virar uma Tabela no D.T.R

4.4.3. ANLISE DE RELACIONAMENTO

As ligaes entre as tabelas assumem um papel importante, pois atravs delas que so representados os relacionamentos do modelo relacional.

Regra Geral : Toda vez que um relacionamento tiver atributo, este relacionamentovai ser representado por uma TabelaRepresentao do Relacionamento no D.T.R

. Relacionamento vira Tabela

. Relacionamento vai ser representado por uma Chave Estrangeira

4.4.3.1 Mapeamento Relacionamento 1 p/ 1

A) - As duas relaes possuem a mesma chave primria.H uma forte razo para unir as duas relaes em uma s. Combinam-se

os atributos permanecendo uma nica chave primria.

PRODUTO

#COD-PRODDESC-PROD

PRC-UNIT

ESTOQUE-PROD

#COD-PRODQTDE-EST

DATA-ULT-MOV

PRODUTO

#COD-PRODDESC-PROD

PRC-UNITQTDE-EST

DATA-ULT-MOV

58

B) - As duas relaes possuem diferentes chaves primriasB.1) - Pelo menos uma das entidades possue participao total no

relacionamento. O atributo Num-emp foi transposto para a relaodepartamento, com o objetivo de representar a restrio de que tododepartamento possui um gerente que empregado da empresa.

B.2) - Ambas entidades possuem participao parcial no relacionamentoDefine-se uma terceira relao correspondendo ao relacionamento.

DEPTO CHEFIA EMPREGADO

DEPTO

#COD-DEPTONOME-DEPTO

NUM-EMP

1 1

EMPREGADO

#NUM-EMPNOME-EMP

HOMEM MULHERCASAMENTO

HOMEM

#CPF-HNOME-H

MULHER

#CPF-MNOME-M

HOMEM

#CPF-HNOME-H

CASAMENTO

#CPF-H#CPF-M

DATA-CAS

MULHER

#CPF-MNOME-M

1 1

59

4.4.3.2 Mapeamento Relacionamento 1 p/ N

A) - A entidade do lado 1 possui participao total no relacionamento.A chave primria da relao do lado "um" parte integrante da relao dolado muitos.

B) - A entidade do lado um possui participao parcial norelacionamento. Define-se uma terceira relao correspondendo aorelacionamento.

CLIENTE PEDIDOFAZ

CLIENTE

#COD-CLINOME-CLICGC-CLI

PEDIDO

#NRO-PEDDATA-PEDCOD-CLI

1 N

HOMEM MULHERCASAMENTO

HOMEM

#CPF-HNOME-H

MULHER

#CPF-MNOME-M

HOMEM

#CPF-HNOME-H

CASAMENTO

#CPF-H#CPF-M

DATA-CAS

MULHER

#CPF-MNOME-M

1 N

60

4.4.3.3 Mapeamento Relacionamento N p/ N- Um relacionamento N:N sempre pode ser resolvido em dois relacionamentos1:N. Uma relao de interseo dever ser implementada.

4.4.3.4 Mapeamento Relacionamento Mltiplo

FORNECEDOR MATERIALFORNECIMENTO

PROJETO

N N

N

FORNECEDOR

#COD-FORNNOME-FORN

FORNECIMENTO

#COD-FORN#COD-MAT#COD-PROJ

QTDE

MATERIAL

#COD-MATDESC-MAT

PROJETO

#COD-PROJNOME-PROJ

FORNECEDOR MATERIALFORNECIMENTO

FORNECEDOR

#COD-FORNNOME-FORN

FORNECIMENTO

#COD-FORN#COD-MAT

QTDE

MATERIAL

#COD-MATDESC-MAT

N N

61

4.4.3.5 Mapeamento Agregao

MEDICO PACIENTEATENDE

EXAME

SOLICITA

MEDICO

#COD-MEDNOME-MED

ATENDE

#COD-MED#COD-PAC

DATA-CONS

PACIENTE

#COD-PACNOME-PAC

SOLICTA

#COD-MED#COD-PAC

#COD-EXAMERESULTADO-EX

EXAME

#COD-EXAMEDESC-EXAME

N N

N

N

62

4.4.3.6 Mapeamento Auto Relacionamento

DISCIPLINA

PRE-REQUISITO

DISCIPLINA

#COD-DISCNOME-DISC

PRE-REQUISITO

#COD-DISC-P#COD-DISC-S

DATA-PRE

NN

63

4.4.3.7 Mapeamento Hierarquia de Classe

CLIENTE

TIPO FISCAL

PESSOA FSICA

PESSOA JURDICA

COD-CLI

NOME-CLI

CPF CGC

64

a) Mapear em uma nica Relao

CLIENTE# COD-CLI NOME-CLI CPF-CLI /CGC-CLI

b) Mapear nas Subclasses as relaes

PESSOA FSICA # COD-CLINOME-CLI CPF-CLI

PESSOA JURDICA# COD-CLI NOME-CLI CGC-CLI

c) Mapear como Relaes distintas

CLIENTE# COD-CLI NOME-CLI

PESSOA FSICA # COD-CLI CPF-CLI

PESSOA JURDICA# COD-CLI CGC-CLI

65

4.5. RESTRIES DE INTEGRIDADE NO MODELO RELACIONAL

4.5.1. INTEGRIDADE LGICA

. Conjunto de regras que existem para o modelo de dados, assim como umconjunto de regras de negcio, que regem a manipulao do BD, de forma ano ferir nenhuma destas regras estabelecidas

4.5.2. INTEGRIDADE FSICA

. Conjunto de procedimentos operacionais que garantem a integridade do BD,mesmo em situaes de falha de algum componente do ambiente onde o BD manipulado

4.5.1- INTEGRIDADE LGICA

a) Restrio de ChaveUma relao deve ter pelo menos uma chaveb) Restrio de Integridade de EntidadeNenhum valor da chave primria de uma relao pode ser nulac) Restrio de Integridade de RefernciaA chave estrangeira deve ter correspondncia com a chave primria em outratabela ou ser nulad) Restrio de Integridade Semntica ou Regras do NegcioSo regras ditadas pelo negcio e no so mapeadas pelo M.E.R por se tratarde condies especiais

EX: . Valor mnimo de depsito para abertura de uma conta R$10.000,00. Conta corrente sem movimento a 180 dias ser encerrada.

Podem ser cumpridas e implementadas pelos SGBDs Relacionais, atravs domecanismo de Regras ou gatilhos (Triggers), hoje existentes no SQL

66

4.5.1.1 - INTEGRIDADE REFERENCIAL DE INSERO

1 - Respeitar as cardinalidades mnimas2 - Antes de INSERIR uma nova linha em uma tabela que contem um valor dechave estrangeira, necessrio que j exista uma linha em uma tabela com estevalor de chave primria.Caso contrrio, a operao de INSERO deve ser rejeitada ou uma linhacom a chave primria dever ser inserida na respectiva tabela.

DEPARTAMENTONUM-DEPTO

DESCRIO

100 R.H200 COBRANA300 INFORMTICA

FUNCIONRIONUM-FUNC

NOME NUM-DEPTO

9999 LUIZ 1008888 VERA 3009898 ALBERTO 200

4.5.1.2 - INTEGRIDADE REFERENCIAL DELEO

. Quando uma linha de uma tabela deletada ento:

a) Todas as ocorrncias de chave estrangeira desta chave primria tambmdevem ser deletadas (CASCATA)b) Os valores de chave estrangeira devem ser atualizados para nulo(SETNULL)c) A operao de deleo pode ser rejeitada, se existir uma ocorrncia de chaveestrangeira da chave primria a ser deletada. (RESTRITA)

67

4.5.1.3 - INTEGRIDADE REFERENCIAL ATUALIZAO:

. Se uma chave primria atualizada, ento

a) Mudar para nulas todas as ocorrncias existentes de chave estrangeira comoantigo valorb)Mudar todas as ocorrncias de chave estrangeira do antigo valor para o novovalorc)Rejeitar a atualizao

68

SISTEMAS DE BANCOS DE DADOSFORMULRIOS PARA OS MODELOS CONCEITUAL E LGICO DE BANCO DE DADOS

Este texto visa apresentar os formulrios usados para documentao do projeto deBanco de Dados.

Dentro do projeto pretende-se abordar duas fases bsicas:- O Modelo Conceitual: um modelo de alto nvel, independente da implementao. O

principal objetivo desta fase uma produo de uma descrio formal dos dados levantados apartir dos requisitos dos usurios. O modelo adotado o Modelo de Entidades eRelacionamentos (MER), estendido com o conceito de abstrao de dados. Nesta fase gerado um Diagrama de Entedidades e Relacionamentos (DER).

- O Modelo Lgico: um modelo implementvel, que seja processvel por determinadaclasse de SGBD. O modelo adotado o Modelo Relacional. Nesta fase do projeto gerado umDiagrama de Tabelas Relacionais (DTR), derivado do DER da fase anterior. Durante o projetolgico, devero ser levadas em considerao as necessidades de processamento, anormalizao dos dados e as restries de integridade das tabelas.

A documentao do projeto de dados constar ento de 3 formulrios:a) O Modelo Conceitual de Dados (Anexo 1), composto pelo Diagrama de Entidades e

Relacionamentos (DER) - entidades, relacionamentos, cardinalidade, participao dasentidades nos relacionamentos, abstraes de dados (agregao,generalizao/especializao).

b) O Modelo Relacional (Anexo 2), composto pelo Diagrama de Tabelas Relacionais(DTR), com a identificao das tabelas e das ligaes entre as mesmas.

c) A Descrio da Tabela (Anexo 3), sendo usado um formulrio para cada tabela,composto pela descrio dos dados da tabela e as restries aplicveis. As restries paragarantir a integridade dos dados sero consideradas sob 3 aspectos:

- Integridade de chave:Em cada tabela ser definida uma chave primria, com valores no-nulos.

- Restrio de existncia:Devem ser analisadas as restries no caso de incluso de uma nova tupla

ou de alterao de uma tupla existente.Deve ser mantida a integridade referencial, no caso de tabelas que

possuam chaves estrangeiras.- Restrio de persistncia:

Devem ser analisadas as restries no caso de excluso de uma tupla, afim de ser mantida a integridade referencial.

3 situaes podem ser consideradas:

a) Remoo em CASCATA: propaga a remoo ocorrida em uma tabelapara as outras tabelas relacionadas atravs de uma chave estrangeira.

b) Bloqueio total (Regra Restrita): a remoo no permitida se a tupla referenciada por outra tabela atravs de uma chave estrangeira; caso contrrio aremoao permitida.

c) Nulificao (Regra SET NULL): a remoo de uma tupla que referenciada por outra tabela, atravs de uma chave estrangeira, implica ematribuir valores nulos para estas chaves estrangeiras.

69

Anexo 1Modelo de Dados Nome Sistema Data

CES Diagrama de Entidades e Relacionamentos - DER

70


CES Diagrama de Tabelas Relacionais - DTR

71


CES Descrio de Tabela- DT

Nome daTabela

Cdigo daTabela

Descrio Sumria da Tabela

Composio da Tabela

Nome do Elemento de Dado Tipo Cdigos para o Tipo de Elemento de Dado:CP - Chave PrimriaCS - Chave SecundriaPO - Preenchimento ObrigatrioCE - Chave Estrangeira

Restries de Integridade da Tabela

Cdigo daTabela

Cdigo do Restries em relao Tabela Relacionada

Relacionada Relacionamento

XX YY Incluso:Alterao:Excluso:

72

Exerccio Padro Seo Eleitoral

EXERCCIO - UMA SEO ELEITORAL

A narrativa a seguir descreve o funcionamento de uma seo eleitoral durante uma eleio:

Um eleitor fornece a sua identificao e esta validada. Se for um eleitor vlido ele recebeautorizao para votar. Um eleitor vlido aquele cadastrado na seo, identificado pelo nmero de seuTtulo de elitor (Num_Tit_Ele). Para cada eleitor existem informaes armazenadas: nome (Nome_Ele),Data de nascimento (Data_Nasc_Ele), Endereo(End_Ele), Zona Eleitoral(Num_Zona_Ele) e SeoEleitoral(Num-Seo_Ele).

Os votos recebidos so armazenados, sendo gerado um comprovante de votao, entregue aoeleitor. O voto uma associao entre um eleitor com os candidatos. Cada voto tem uma data (Data_Vot)associada e vlido quando o candidato citado vlido. Os candidatos so identificados atravs de suainscrio na Justia Eleitoral (Num_Insc_Cand), alm de seu nome (Nome_Cand) e partido ao qual sefilia (Partido_Cand).

Se o eleitor no comparecer seo para votar, ele pode justificar-se, enviando um documento Justia Eleitoral. Se a justificativa aceita, ela registrada, sendo gerado um comprovante dejustificativa, enviado ao eleitor. As justificativas so registradas atravs de um nmero de identificao,alm de informaes sobre o eleitor e do local de origem. Existem eleitores que no comparecem votao e que, ainda assim, no justificam sua absteno.

Baseie-se na narrativa apresentada para fazer:

a) Um diagrama de fluxo de dados - DFD;b) Um diagrama de entidades e relacionamentos - DER;c) Transponha o DER para o Modelo Relacional, usando as Regras de Mapeamento.

73

Diagrama de Fluxo de dados

1VALIDARELEITOR

ELEITOR

3GERAR

CONFIRMAOVOTO

4VALIADAR

JUSTIFICATIVA

5REGISTRAR

JUSTIFICATIVA

2REGISTRAR

VOTO

6GERAR

CONFIRMAPJUSTIFICATIVA

ELEITORES

ELEITORES

JUSTIFICATIVAS

VOTOS

CANDIDATOS

ELEITOR

ELEITOR NOAUTORIZADO

AUTORIZAO

IDENTIFICAOELEITOR

VOTO

CONFIRMAOVOTO

COMPROVANTEVOTO

JUSTIFICATIVA

JUSTIFICATIVANAO ACEITA

JUSTIFICATIVAACEITA

CONFIRMAOJUSTIFICATIVA

COMPROVANTEJUSTIFICATIVA

74

Diagrama de Entidade e Relacionamento

ELEITOR JUSTIFICATIVA FAZ

VOTA

CANDIDATO

1 1

N

N

75

Diagrama de Tabelas Relacionais

T1-ELEITOR

T3-CANDIDATO

T2-VOTA

T4-JUSTIF

R1

R2

R3

76

Modelo de Dados Nome Sistema DataCES Descrio de Tabela- DT Eleio MMM/AA

Nome daTabela

Eleitor Cdigo daTabela

T1

Descrio Sumria da TabelaCadastro dos eleitores vlidos

Composio da Tabela

Nome do Elemento de Dado Tipo Cdigos para o Tipo de Elemento de Dado:Num-Tit-Ele CP,PO CP - Chave PrimriaNome-Ele PO CS - Chave SecundriaData-Nasc-Ele PO PO - Preenchimento ObrigatrioEnd-Ele PO CE - Chave EstrangeiraNum-Zona-Ele PONum-Seo-Ele PO


Cdigo daTabela

Cdigo do Restries em relao Tabela Relacionada [ I - Incluso A - AlteraoE - Excluso]


T2 R2 I : Sem restriesA : No permitida a alterao da CPE : Restrita


77


Nome daTabela

Voto Cdigo daTabela

T2

Descrio Sumria da TabelaVotos realizados pelos eleitores

Composio da Tabela

Nome do Elemento de Dado Tipo Cdigos para o Tipo de Elemento de Dado:Num-Tit-Ele CP,CE,PO CP - Chave PrimriaNum-Insc-Cand CP,CE,PO CS - Chave SecundriaData-Voto PO PO - Preenchimento Obrigatrio

CE - Chave Estrangeira


Cdigo daTabela

Cdigo do Restries em relao Tabela Relacionada [ I - Incluso A -Alterao E - Excluso]


T1 R2 I : O eleitor deve estar cadastrado e no deve possuir nenhumajustificativaA : No permitida a alterao das CPE : Sem restries

T3 R3 I : O candidato deve estar cadastradoA : No permitida a alterao das CPE : Sem restries

78


Nome daTabela

Candidato Cdigo daTabela

T3

Descrio Sumria da TabelaCandidatos cadastrados para a eleio

Composio da Tabela

Nome do Elemento de Dado Tipo Cdigos para o Tipo de Elemento de Dado:Num-Insc-Cand CP,PO CP - Chave PrimriaNome-Cand PO CS - Chave SecundriaPartido-Cand PO PO - Preenchimento Obrigatrio

CE - Chave Estrangeira


Cdigo daTabela

Cdigo do Restries em relao Tabela Relacionada [ I - Incluso A -Alterao E Excluso]



79


Nome daTabela

Justif Cdigo daTabela

T4

Descrio Sumria da TabelaJustificativas apresentadas pelos eleitores ausentes eleio

Composio da Tabela

Nome do Elemento de Dado Tipo Cdigos para o Tipo de Elemento de Dado:Num-Justif CP,PO CP - Chave PrimriaLocal-Justif PO CS - Chave SecundriaData-Justif PO PO - Preenchimento ObrigatrioMotivo-Justif PO CE - Chave EstrangeiraNum-Tit-Ele CE,PO


Cdigo daTabela

Cdigo do Restries em relao Tabela Relacionada [ I - Incluso A -Alterao E - Excluso]


T1 R1 I : O eleitor deve estar cadastrado e no deve possuir nenhum votoA : No permitida a alterao das chaves CP ou CEE : Sem restries

80

4.6.LINGUAGENS RELACIONAIS

- FORMAIS LGEBRACLCULO TUPLAS

DOMNIO- - COMERCIAIS SQL

QUEL (Linguagem Consulta) INGRES (1976)QBE (Query by Example) IBM (1975)

HISTRICO. 1970: Edward F. Codd

Artigo Modelo Relacional de Dados para grandes BD compartilhados1 prottipo de um SGBD relacional. SYSTEM/R

. 1974/1975 : Criada a Linguagem SEQUEL

. 1975: QBE(Qurey by Example) - IBM

. 1976/1977: Verso SEQUEL/2 (alterado SQL)

. 1976: Criada a Linguagem QUEL - INGRES

. 1978/1979: ORACLE (Oracle Corporation)

. 1981: Diversos fabricantes lanam produtos baseados no SQL

. 1982: Criao comit na ANSI para proposta padro

. 1983: DB2 (IBM)

. 1986: O padro ANSI SQL utilizado SQL 1

. 1988: DB2 verso 2 (IBM)

SQL 1: Padro original no havia clusula para especificar chave; modificadoem 1989SQL 2: aprovado em 1992: implementa conexo cliente/servidorSQL 3: em fase de aprovao; implementa o Modelo Orientado a Objeto

81

4.6.1 - LGEBRA RELACIONAL

Matemticamente falando, uma tabela (relao) um conjunto , um conjuntode linhas.No modelo relacional temos o B.D. representado como uma coleo de tabelas,quando queremos manipular ( recuperar ) dados em geral o resultado nos apresentado como uma tabela, derivada de alguma forma de outras tabelas.A lgebra relacional um conjunto de operaes e relaes.

4.6.1.1 - Operaes tradicionais- union- intersection- diference- cartesian

4.6.1.2- Operaes especiais- project- select- join- divide

82

4.6.1 - LGEBRA RELACIONAL

Operadores SQL possuem equivalentes diretos em lgebra

Um S.G.B.D. para ser considerado completamente relacional tem que suportar:- B.D.R. ( conceito domnio, chave, ...)- Uma linguagem que seja pelo menos to potente quanto a lgebra.

4.6.1.1 - Operaes Tradicionais

. UNIONR1 union R2 giving R3

R1 COD NOME CIDADE R2 COD NOME CIDADE R3 COD NOME CIDADE

S1 JOAO RJ S1 JOAO RJ S1 JOAO RJ

S2 JOSE RJ S3 BETO SP S2 JOSE RJ

S3 BETO SP

. INTERSECTIONR1 intersection R2 giving R4

R4 COD NOME CIDADE

S1 JOAO RJ

. DIFERENCER1 diference R2 giving R5

R5 COD NOME CIDADE

S2 JOSE RJ

83

4.6.1.1 - Operaes Tradicionais

. CARTESIAN

R6 cartesian R7 giving R8

R6 A B R7 C D R8 A B C D

A1 B1 C1 D1 A1 B1 C1 D1

A2 B2 C2 D2 A1 B1 C2 D2

A2 B2 C1 D1

A2 B2 C2 D2

4.6.2.2- Operaes Especiais

. PROJECTProject R1 over Cod giving R9

R1 COD NOME R9 COD

F1 JOSE F1

F2 JOAO F2

F3 MARIA F3

F4 PEDRO F4

. SELECTSelect R10 where salario > 5.000 giving R11

R10 COD DEPTO SALARIO R11 COD DEPTO SALARIO

F1 D1 1.000 F3 D1 6.000

F2 D2 4.000

F3 D1 6.000

84

4.6.2.2- Operaes Especiais

. DIVIDEDivide R12 by R13 over Cod giving R14

R12 COD B R13 B R14 COD

A1 B1 B2 A3

A2 B1 B3 A7

A3 B2

A7 B2 A2 B3

A3 B3

A7 B3

JOIN NATURALO JOIN na verdade duplica a coluna que passada como argumento. (

Ns adotamos que no )

R15 A B C D R16 A E F R17 A B C D E F

S1 ZE 20 RJ S1 5 6 S1 ZE 20 RJ 5 6

S2 JO 10 SP S2 10 7 S2 JO 10 SP 10 7

S3 15 8 !

!

A

S1

S2

Join R15 and R16 over A giving R17

Estamos trabalhando com JUNO baseada em igualdade de valores(EQUI-JOIN). Mas poderamos ter JUNO "maior que", JUNO "noigual", etc...

Uma EQUI-JOIN com uma das colunas idnticas eliminadas chama-seJOIN NATURAL.

85

4.7.SQL (STRUCTURED QUERY LANGUAGE)

Mais que uma linguagem de consulta, oferece funes para DEFINIO,MANIPULAAO e CONTROLE dos dados de um Banco de dados.

DDL (Data Definition Language)

- CREATE : criao de novas estruturas- ALTER : alterao de estruturas- DROP : remoo de estruturas

DML (Data Manipulation Language)

- INSERT : Insero de registros- DELETE : deleo de registros- UPDATE : atualizao de registros- SELECT : Seleo de registros

DCL (Data Control Language)

- GRANT : concesso de privilgios a tabelas e vises- REVOKE : revogao de privilgios a tabelas e vises

Transaction Control

- COMMIT : efetiva uma alterao no banco de dados- ROLLBACK : desfaz uma alterao antes de ser efetivada no banco- SAVEPOINT : permite uma subdiviso lgica de uma transao longa

Restries de integridade usando

-.STORED PROCEDURES-.TRIGGERS

86

Dicionrio de Dados (Catlogo)

. um BD de sistema, que pode ser consultado por meio de comandosSELECT da SQL, contendo:

.informaes sobre as tabelas bsicas

.as vises

.os direitos de acesso

.as identificaes dos usurios, etc. Sua forma exata uma caracterstica de cada sistema e no da SQL

4.7.1 - DDL (DATA DEFINITION LANGUAGE)

a)CREATE

a-1) CREATE TABLE nome_tabela(nome_coluna tipo [(tamanho)] [restrio_coluna],nome_coluna tipo [(tamanho)] [restrio_coluna],[restrio_tabela] );

.restrio: um mecanismo pelo qual voc limita ou restringe o tipo de dadoque uma coluna pode armazenar.

.restrio_coluna: referencia somente uma coluna, aceitando todos os tipos derestries.

[CONSTRAINT nome_restrio] tipo_restrio

.restrio_tabela: referencia uma ou mais colunas. S no aceita o tipo NOTNULL.

[CONSTRAINT nome_restrio] tipo_restrio (coluna, ...)

Tipos de restries[NOT] NULL :

Indica se a coluna pode ou no receber valores nulos. O default NULLUNIQUE :

Indica que a coluna ou combinao de colunas no pode ter valoresrepetidos.

87

PRIMARY KEY :Indica que a coluna ou combinao de colunas forma a chave primria.

Chave Estrangeira

REFERENCES nome_tabela_pai(nome_coluna_pai)[ON DELETE CASCADE]

Usada a nvel de coluna, indica que a coluna uma chave estrangeira.

FOREIGN KEY(nome_coluna_filho)REFERENCES nome_tabela_pai(nome_coluna_pai)[ON DELETE CASCADE]

Usada a nvel de tabela, indica que a coluna ou combinao de colunas uma chave estrangeira.

ON DELETE CASCADEIndica quando uma linha na tabela_pai deletada haver uma deleo das

linhas correspondentes (chave estrangeira) na tabela_filho.Obs : O default na ausncia da clausula ON DELETE CASCADE

RESTRICT...EExxiissttee aaiinnddaa ooppoo nnoo ppaaddrroo SSQQLL//22 OONN DDEELLEETTEE SSEETT NNUULLLL ee

OONN UUPPDDAATTEE CCAASSCCAADDEE..

CHECKNo permite que valores que violem a condio estabelecida sejam gravados nacoluna.

Tipos de dados permitidosCHAR(n): Tipo de dado caracter de tamanho fixo.VARCHAR(n): Tipo de dado caracter de tamanho varivel, sendo sempredefinido seu tamanho mximo(n).NUMBER(n): Tipo numrico.NUMBER(p,q): Tipo numrico de ponto decimal (p : posies sendo q: casasdecimais).DATE: Tipo data

88

Exemplos

a) Restries a nvel de Tabela

CREATE TABLE depto (num_depto NUMBER(2),nome_depto VARCHAR(15),local_depto VARCHAR(15),CONSTRAINT depto_PK

PRIMARY KEY (num_depto),CONSTRAINT depto_nome_depto_UK

UNIQUE (nome_depto));

CREATE TABLE emp (num_emp NUMBER(6),nome_emp VARCHAR(30),salario_emp NUMBER(7,2),sexo_emp CHAR(1),cargo_emp VARCHAR(30),num_depto NUMBER(7) NOT NULL,CONSTRAINT emp_PK

PRIMARY KEY (num_emp),CONSTRAINT sexo_emp_CK

CHECK (sexo_emp in (M, F)),CONSTRAINT emp_num_depto_FK

FOREIGN KEY (num_depto)REFERENCES depto (num_depto)ON DELETE CASCADE

);

89

b) Restries a nvel de coluna

CREATE TABLE depto (num_depto NUMBER(2) PRIMARY KEY,nome_depto VARCHAR(15) UNIQUE KEY,local_depto VARCHAR(15)

);

CREATE TABLE emp (num_emp NUMBER(6) PRIMARY KEY,nome_emp VARCHAR(30),salario_emp NUMBER(7,2),sexo_emp CHAR(1) CHECK ( sexo_emp in (M, F)),cargo_emp VARCHAR(30),num_depto NUMBER(7) NOT NULL REFERENCES

depto(num_depto) ON DELETE CASCADE);

90

a-2) CREATE [UNIQUE] INDEX nome_ndiceON nome_tabela (nome_coluna1, nome_coluna2, ...);

Sugestes criao ndices:. Colunas usadas frequentemente na clusula WHERE. FOREIGN KEYS pois esto geralmente envolvidas em JOINS. PRIMARY KEYS e UNIKE KEYS (normalmente o Sistema criaautomaticamente um UNIQUE INDEX).

Exemplo:

CREATE INDEX emp_nome_emp_idxON emp (nome_emp);

Observaes:1 ndices no podem ser alterados; devem ser removidos (com DROP) erecriados2 A deciso de se usar ou no um ndice em resposta a uma solicitaoespecfica de dado no tomada pelo usurio mas sim pelo sistema

91

b)ALTER Comando usado para alterar a estrutura de uma tabela:

. adicionando ou alterando colunas.

. inserindo ou removendo restries

b.1) Adicionando ou modificando colunas de uma tabelaALTER TABLE nome_tabela

[ ADD (nome_coluna tipo[(tamanho)],...)][ MODIFY (nome_coluna tipo[(tamanho)],...)]

Exemplo:ALTER TABLE emp

ADD (data_nasc_emp date);

ALTER TABLE empMODIFY (nome_emp(60) NOT NULL);

b.2) Adicionando ou removendo uma restrio de uma tabelaALTER TABLE nome_tabela

[ ADD restrio_tabela][ DROP PRIMARY KEY | UNIQUE (nome_coluna) |

CONSTRAINT nome_restrio [CASCADE] ];Exemplo:

ALTER TABLE deptoADD CONSTRAINT depto_local_depto_UK

UNIQUE (local_depto);

ALTER TABLE deptoDROP PRIMARY KEY CASCADE;Neste exemplo o comando remove a restrio PRIMARY KEY na tabela

Depto e remove a restrio FOREIGN KEY associada na tabela Emp.Obs : Aqui estamos removendo apenas as constraints associadas as

tabelas e no os registros de fato.

92

b.3) Habilitando e desabilitando uma restrio em uma tabela

ALTER TABLE nome_tabelaENABLE | DISABLE nome_restrio [CASCADE];

Exemplo :ALTER TABLE depto

ENABLE CONTRAINT depto_local_depto_uk;

c)DROP

Para excluir uma tabela ou ndice

c.1) Excluir uma tabela, onde os ndices tambm so excludosDROP TABLE nome_tabela [CASCADE CONSTRAINTS];

Exemplo:DROP TABLE emp;

DROP TABLE depto CASCADE CONSTRAINTS;Neste exemplo o comando exclui a tabela depto e remove todas as

restries FOREIGN KEY que fazem referncia a PRIMARY KEY destatabela.

Obs : Aqui CASCADE CONSTRAINTS desfaz apenas as restriesassociadas chave primria e no excluiu os registros associados pelas chavesestrangeiras.

c.2) Exclui um ndiceDROP INDEX nome_indice

Exemplo :

DROP INDEX emp_nome_emp_idx

93

4.7.2.-DML (DATA MANIPULATION LANGUAGE)

a) INSERT

a-1) Adicionar novas linhas em uma tabelaINSERT INTO nome_tabela [(nome_coluna1 [,nome_coluna2 ...] )]

VALUES ( valor1 [, valor2 ...]);

Exemplo:INSERT INTO depto

VALUES (100, INFORMATICA, JUIZ DE FORA);

INSERT INTO emp (Num_Emp, Nome_Emp, Sexo, Num_Depto)VALUES (1313, TEREZA CRISTINA, F, 100);

a-2) Copiando linhas de uma outra tabela:INSERT INTO nome_tabela [(nome_coluna1 [, nome_coluna2...])]

Subquery;

Exemplo:CREATE TABLE gerente

num_emp NUMBER(6) PRIMARY KEY,nome_emp VARCHAR(30);

INSERT INTO gerenteSelect num_emp, nome_empFrom empWhere cargo_emp = GERENTE;

94

b)DELETE

DELETE FROM nome_tabela[WHERE condio] ;

Exemplo:DELETE FROM depto

WHERE local_depto = JUIZ DE FORA;

DELETE FROM depto;Deleta todas as linhas da tabela se for omitida WHERE

95

c)UPDATE

c.1) Atualizar linhas de uma tabelaUPDATE nome_tabela

SET nome_coluna = valor [, nome_coluna = valor][WHERE condio];

ExemploUPDATE emp

SET nome_emp = JAIR BATISTA , sexo_emp = MWHERE num_emp = 1313;

Obs : Todas as linhas de uma tabela so atualizadas se voc omitir a clusulaWHERE.

c.2) Atualizar linhas a partir de uma SubqueryUPDATE nome_tabela

SET (nome_coluna, nome_coluna ...) =(SELECT nome_coluna, nome_coluna, ...

FROM nome_tabelaWHERE condio);

Exemplo:UPDATE emp

SET (cargo_emp, num_depto) =(SELECT cargo_emp, num_depto

FROM empWHERE num_emp = 1313)

WHERE num_emp = 1320;

96

d)SELECT

Comando usado para fazer consultas as bases de dados

d.1) Forma Bsica:

SELECT [DISTINCT] nome_coluna [,nome_coluna...] FROM nome_tabela

Seleo de colunas especficas : Basta relacionar as colunas desejadasExemplo:

SELECT num_emp, nome_empFROM emp;

Seleo de todas as colunas : Substituir os nome das colunas por *Exemplo:

SELECT *FROM emp;

Evitando duplicaes: Usar a palavra DISTINCT na clusula SELECTExemplo:

SELECT DISTINCT nome_emp, cargo_empFROM emp;

Usando Pseudnimos: Uma consulta SQL normalmente usa o nome da colunacomo cabealho; possvel definir um pseudnimo para a coluna queaparecera no cabealhoExemplo:

SELECT num_emp Numero do EmpregadoFROM emp;

97

d.2) Uso clusula WHEREUsada para filtrar um subconjunto de linhas de uma tabela

SELECT nome_colunasFROM nome_tabela

[WHERE condio]

Operadores:

= igual a< > diferente de> maior que< menor queBetween ... and... entre dois valoresIn(lista) qualquer valor da listaLike corresponde a um gabarito% : corresponde a uma seqncia de zero ou mais caracteres- : corresponde a um caracterIS NULL valor nulo

Obs : Os quatro ltimos podem ser negados atravs do operador NOT:. NOT BETWEEN, NOT IN , NOT LIKE, IS NOT NULL

Conjuno de Condies: vrias condies podem ser conectadas na clusulaWHERE usando o operador AND

Exemplo:WHERE nome_cargo = GERENTE AND num_depto > 1000;

Disjuno de Condies : usando operador OR

Exemplo:WHERE num_depto = 1313 OR local_depto LIKE _J %;Obs: _ :primeira posio, J: segunda posio

98

d.3 ) Uso Clusula ORDER BYOrdenando o resultado de uma consulta

SELECT nome_colunasFROM nome_tabela

[WHERE condio][ORDER BY {nome_coluna, ...} [ASC|DESC]]

Exemplo:ORDER BY nome_emp

d.4) Juno de TabelasAs linhas de uma tabela podem ser combinadas(JOIN) s linhas de outra tabelaatravs de valores comuns em colunas correspondentes.Exemplo:

SELECT emp_name, depto_nameFROM emp e, depto dWHERE e.num_depto = d.num_depto;

d.5) Produto CartesianoTodas as linhas da primeira tabela so combinadas com todas as linhas dasegunda tabela.Ocorre na omisso da clusula WHERE.

Exemplo:Tabela EMP com 14 registrosTabela DEPTO com 4 registros

SELECT nome_emp, nome_deptoFROM emp, depto;

99

d.6) Funes de Manipulao de Valores

Operadores aritmticos

+ soma- subtrao* multipllicao/ diviso

Funes Numricas

SIGN(nmero) : -1 se negativo, 1 se positivo

MOD(dividendo, divisor) retorna o resto da diviso

ROUND (nmero, [n_casas]) retorna o numero arrendondado com n casas

TRUNC(nmero,[n_casas] retorna o nmero truncado com n casas

Funes Alfanumricas

CHR(nmero) retorna o caracter cujo cdigo ASCII seja igual ao nmeroEx: CHAR(75) k

LOWER(string) retorna a string toda em letra minusculaEx: LOWER(EXEMPLO FUNC) exemplo func

UPPER(string) retorna a string toda em letra maisculaEx: UPPER(exemplo func) EXEMPLO FUNC

LENGH(sring) retorna o tamanho da string

Funes de GrupoRetornam um valor para um grupo de linhas

SELECT funo_grupo(nome_coluna)FROM nome_tabela[WHERE condio][ORDER BY nome_coluna]

100

Obs: Se a clusula SELECT contiver funes de grupo, o resultado sersomente uma linha. Assim na clusula SELECT no podem aparecer resultadosindividuais junto com expresses que contenham funes de grupo. Neste casodeveramos usar GROUP BYExemplo : SELECT nome-emp, AVG(salario_emp)

FROM emp WHERE num_depto = 30;

COUNT(* | [DISTINCT] nome_coluna) : Retorna a quantidade de linhas dogrupo

COUNT(*) : inclui linhas duplicatas e linhas que contenham valores NULL.

COUNT(nome_coluna) : retorna o nmero de linhas com valores NOT NULLpara a coluna(expresso) especificada.

COUNT(DISTINCT nome_coluna) : retorna o nmero de linhas com valoresdistintos.

MAX([DISTINCT] expresso) : Retorna o valor mximo dessa expresso oucoluna dentro do grupo

MIN([DISTINCT] expresso) : Retorna o valor mnimo dessa expresso oucoluna dentro do grupo

SUM([DISTINCT] expresso) : Retorna o somatrio da expresso de todas aslinhas do grupo

AVG([DISTINCT] expresso) : Retorna a mdia aritmtica de todas as linhas.Valores NULL so ignorados.

AVG(NVL(nome_coluna,0)) : considera como zero os NULL na mdiaaritmtica.

101

d.7) Uso da Clusula GROUP BYUsada para dividir as linhas de uma tabela em grupos menores.O SQL recupera cada grupo de linhas de acordo com os valores da(s)expresso(es) especificada(s) na clusula GROUP BY.

SELECT nome_colunas , funo_de_grupo(nome_coluna)FROM nome_tabela

[WHERE condio][GROUP BY exp1, exp2...]

A clusula GROUP BY dever vir sempre aps a clusula WHERE (ou aps aclusula FROM quando no existir WHERE)Quando a clusula GROUP BY utilizada possvel combinar resultadosindividuais com funes de grupo na clusula SELECT.

Quando a clusula GROUP BY utilizada, possvel combinar resultadosindividuais com funes de grupo na clusula SELECT, desde que aquelesresultados individuais sejam usados no GROUP BY.

ExemploSELECT num_ depto, COUNT(nome_emp)

FROM empGROUP BY num_depto;

Observao:Quando estiver usando o GROUP BY certifique-se que todas as colunas nousadas na funo de grupo estejam includas na clausula GROUP BY

Usando a clusula WHERE voc pode selecionar linhas antes de agrupar.

Usando GROUP BY para mltiplas colunas:SELECT num_depto, cargo_emp, SUM(salario_emp)

FROM empGROUP BY num_depto, cargo_emp;

102

d.8) Uso da Clusula GROUP BY com HAVING

A clusula WHERE no pode ser usada para restringir funes de grupo.Exemplo:

SELECT num_depto, AVG(salario_emp)FROM empWHERE AVG(salario_emp) > 2000GROUP BY num_depto;

Os grupos definidos pela clusula GROUP BY podem ser filtrados pelaclusula HAVING.

Exemplo:

SELECT num_depto, AVG(salario_emp)FROM empGROUP BY num_deptoHAVING AVG(salario_emp) > 2000

SELECT nome_colunas , funo_de_grupo(nome_coluna)FROM nome_tabela

[WHERE condio][GROUP BY exp1, exp2...][HAVING funo_de_grupo(nome_coluna)][ORDER BY nome_coluna]

Exemplo

SELECT cargo_emp, SUM(salario_emp)FROM emp

WHERE cargo_emp NOT LIKE GEREN%GROUP BY cargo_empHAVING SUM(salario_emp) > 5000ORDER BY SUM(salario_emp);

103

d.9) Uso de Subqueries

SubqueriesSo comandos SELECT que so utilizados em condies de clusulas

WHERE ou HAVING para prover resultados que so utilizados para completara consulta principal.

Exemplo

SELECT nome_emp, cargo_empFROM empWHERE cargo_emp = ( SELECT cargo_emp

FROM empWHERE nome_emp = JONES);

Operadores ANY e ALLQuando a subquery retornar mais de um valor, os operadores ANY e ALLpodem ser utilizados para compatibilizar o resultado da subquery com o tipo dooperador de comparao.

Exemplosalario_emp > ANY

Essa condio ser verdadeira quando salario_emp for maior que qualquer umdos resultados da query.

salario_emp < ANY Essa condio ser verdadeira quando salario_emp for menor que qualquer umdos resultados da query.

salario_emp > ALL Essa condio ser verdadeira quando salario_emp for maior que todos osresultados da subquery.

salario_emp < ALL Essa condio ser verdadeira quando salario_emp for menor que todos osresultados da subquery.

104

Exemplo

SELECT num_emp, nome_emp, cargo_empFROM emp

WHERE salario_emp < ANY (SELECT salario_empFROM emp

WHERE cargo_emp = GERENTE);

Operadores IN e NOT IN igual para qualquer membro da lista

... WHERE cargo_emp IN (SELECT cargo_emp

FROM empWHERE nome_emp LIKE A%);

SELECT nome_emp, salario_empFROM empWHERE salario_emp IN (800, 950, 13000);

Operadores de ConjuntosComo o resultado de um query um conjunto de linhas voc pode realizaroperaes de conjuntos entre queries:

UNION : Unio entre os resultados das queries;INTERSECT : Interseo entre os resultados das queries;MINUS : Subtrao entre os resultados das queries.

105

ExemploSELECT nome_emp, cargo_emp, salario_emp

FROM empWHERE salario_emp IN(

SELECT salario_empFROM empWHERE nome_emp = CARLOS

UNIONSELECT salario_emp

FROM empWHERE nome_emp = MARIO);

Operador EXIST e NOT EXISTEXIST: retorna verdadeiro se uma determinada subquery retornar ao menosuma linha e falso caso contrrioNOT EXIST: retorna o resultado contrrio do EXIST.

106

d.10) Criao e Uso de Vises

OBJETIVORestringir acesso certas pores dos dados por questes de segurana. Pr definircertas consultas definindo tabelas virtuais que podero ser utilizadas por outrasconsultas.

VISOPode ser vista como uma tabela virtual, isto , uma tabela que realmente no existecomo tal, mas sim como derivao de uma ou + tabelas bsicas.Uma definio da viso fica armazenada no dicionrio, esta definio mostra comoela derivada das tabelas bsicas.

CRIAO

CREATE VIEW nome_viso [(nome_coluna [, nome_coluna..])]AS

Obs : Na clusula AS a Subquerie no pode conter : ORDER BY

Exemplo

A) CREATE VIEW emp_visaoAS SELECT num_emp, nome_emp, cargo_empFROM emp

Documents

Apostila BD