Reviso de Sistemas de Banco de Dados

Roteiro!Conceitos e caractersticas de banco de dados

!

!Ciclo de vida de um sistema de banco de dados!

!Modelo Entidade-Relacionamento!

!Modelo Relacional!Normalizao!lgebra Relacional!SQL

Sistemas de Banco de Dados

Sistemas de Banco de Dados!Abstrao de dados

!

!Independncia de dados!

!Esquema e Instncia

Arquitetura em Trs Camadas de Abstrao

Projeto Conceitual

Projeto Lgico

Projeto Fsico

!

ALUNO!

DISCIPLINACURSA

ALUNOcod_alu: int

nom_alu: string

end_alu: string

CURSAcod_alu: int

cod_disc: int

nota: real

DISCIPLINAcod_disc: int

nom_disc: string

hor_disc: string

N N

cod_alu nom_alu end_aluTabela: ALUNO

cod_alu cod_disc nota

Tabela: CURSA

Sistema Gerenciador de Banco de Dados!Conjunto integrado de programas que permite descrever, armazenar, mantnipular, interrogar e tratar o conjunto de dados que compem o banco de dados.

!!Prov independncia fsica e lgica dos dados

!!Controle de redundncia de dados

!!Facilidade de consulta aos dados

!!Garantia de integridade dos dados

!!Compartilhamento dos dados

!!Segurana dos dados

Linguagens de Banco de Dados!Linguagem de Definio de Dados (DDL)!Define o esquema do banco de dados

CREATE TABLE ALUNO( cod_alu INT, nom_alu VARCHAR(35), end_alu VARCHAR(30) );

!Linguagem de Manipulao de Dados (DML)!Manipula (consulta, insere, deleta, atualiza) as instncias do banco de dados

SELECT nom_alu

FROM ALUNO

Ciclo de Vida de um Sistema de Banco de Dados

!Fase 1: Coleta e anlise dos requisitos!

!Fase 2: Projeto Conceitual usando um modelo de dados conceitual. Ex: MER!Escolha da tecnologia de banco de dados (tecnologia relacional, por exemplo) e do SGBD

!

!Fase 3: Projeto Lgico mapeamento do modelo conceitual (MER) para o modelo do SGBD escolhido (relacional)!

!Fase 4: Projeto Fsico implementao do banco de dados usando SGBD e vendo estruturas fsicas de armazenamento atravs dos critrios:

! tempo de resposta! espao utilizado! nmero de transaes

Projeto Conceitual!Desenho do esquema conceitual utilizando um modelo de dados

!

!Alto nvel de abstrao!

!Independente do SGBD!MER Modelo Entidade-Relacionamento

Componentes Bsicos do MER

!Entidades!Fortes e Fracas!

!Relacionamentos!Grau!Binrios, N-rios ou Auto-relacionamentos!Cardinalidade!1:1!1:N!M:N

Escolha do Paradigma de Banco de dados e de um SGBD!Fatores tcnicos, econmicos e polticos!Custos:!do SGBD!do hardware!da manuteno!criao ou converso!Pessoal!Treinamento!Operao

!Popularidade, familiaridade, desempenho, suporte...

Projeto Lgico

!Transformar o Modelo de Dados Conceitual, em um modelo de dados lgico com base em alguma tecnologia de banco de dados. !

!No modelo relacional, transformar as entidades (conceitual) em tabelas (lgico) e definir as restries de integridade dos dados

Modelo Lgico!Modelo Relacional!Relaes!Esquema da relao!Domnios!

!Atributos!

!Chaves !Candidata, Primria e Estrangeira!

!Restries de Integridade!

!lgebra Relacional

Mapeamento de Modelos!Mapear: mudar de representao

!

!Mtodo: mapear as representaes do Modelo ER para representaes equivalentes no Modelo Relacional.!

!Muitas ferramentas CASE (Computer-Aided Software Engineering) baseadas no modelo entidade-relacionamento (MER) convertem automaticamente o modelo lgico para um esquema de banco de dados relacional e geram a DDL para um SGBD especfico.

Mapeamento de Entidades!Todas as entidades so mapeadas para uma tabela contendo os mesmos atributos do MER.

!

!Ex: EMPREGADO (RG,nome,idade)

Mapeamentos de Entidades Fracas!Identificador da entidade forte torna-se

!

!parte da chave primria na tabela correspondente entidade fraca (tabelaFraca)!

!chave estrangeira na tabelaFraca!Ex: DEPENDENTE (RG_emp,RG_dep, nome_dep)

Mapeamento de Atributos!Mantenha nomes de atributos curtos e padronizados

!!Indexe atributos muito consultados

!!Atributos simples se transformam em colunas

!!Compostos!Viram atributos simples!Ex: CLIENTE(cod_cli, nom_cli, rua_cli,no_cli,cid_cli)

!!Multivalorados!Viram outra tabela onde a parte da chave primria a chave estrangeira da tabela originria!Ex: CLIENTE (cod_cli, nom_cli,)

Mapeamento de Especializaes!Trs alternativas so geralmente adotadas!

1. tabela nica para entidade genrica e suas especializaes

!

2. tabelas para a entidade genrica e as entidades especializadas

!

3. tabelas apenas para as entidades especializadas!

Exemplos1.Conta (no_conta, sal_conta, tx_juros,lim_esp,

tipo_conta)!

2. Conta (no_conta, sal_conta)

Poupana (no_conta, tx_juros)

Corrente (no_conta, lim_esp)

!

3. Poupana (no_conta, sal_conta,tx_juros)

Corrente (no_conta, sal_conta,lim_esp)

Mapeamentos de Relacionamentos!Recomendaes de mapeamento baseiam-se na anlise da cardinalidade dos relacionamentos

!

com base nesta anlise, algumas alternativas de mapeamento podem ser adotadas

!

1. entidades relacionadas podem ser fundidas em uma nica tabela!

2. tabelas podem ser criadas para o relacionamento!

3. chaves estrangeiras podem ser criadas em tabelas a fim de representar adequadamente o relacionamento

Relacionamento 1:1!Escolha uma das relaes envolvidas no relacionamento!E inclua como chave estrangeira nesta relao a chave primria da outra relao

Relacionamento 1:N! tabela que representa a entidade no lado N, acrescenta-se como chave estrangeira a chave primria da entidade do lado 1!Ex: Pedido(cod_ped, data_ped, cod_cli)

Relacionamento de N:N! criada uma nova tabela contendo como chaves estrangeiras as chaves primrias das entidades participantes, mais os atributos do relacionamento.!Ex: Pedido_Contem_Produto (cod_ped,cod_pro,qtde_pro)

Auto-Relacionamento!Valem as mesmas recomendaes anteriores!Ex: Empregados (RG, Nome, Idade)

Gerncia(RGe, RGg) Empregados (RG, Nome, Idade, RGg)

Relacionamento Ternrio!O mapeamento ocorre de forma semelhante ao descrito para relacionamentos N:N

Normalizao! uma ferramenta para ser utilizada no projeto lgico de BD!Objetivo 1: remover redundncias entre atributos no-chave.!Objetivo 2: manter integridade dos dados aps remoo, insero, atualizao.

!

!O processo de normalizao consiste em decompor um conjunto de tabelas em um outro conjunto de tal forma que o novo conjunto no tenha anomalias tornando o esquema do bd mais simples e regular.

Exemplo de Tabela no Normalizada

!Manuteno do histrico de entrada de estrangeiros no pas!

!Deseja controlar a entrada de cada estrangeiro! necessrio saber se o estrangeiro j tem ficha na Polcia Federal!para todos os controles relativos entrada, necessrio saber as informaes relativas nacionalidade e respectiva embaixada!em caso de expratriamento por morte, a embaixada comunica PF, e o estrangeiro removido do sistema

Exemplo de Tabela no Normalizada

Data Passap Nome Vo Nacionalidade Nasc J-ficha Embaixada

10/09/12 Pas1 Joo RG 121 Argentina 1/1/65 No Endereo 1

10/09/12 Pas2 Jos AR876 Argentina 22/4/50 Sim Endereo 1

10/09/12 Pas3 Hans RG 121 Alemanha 12/09/50 No Endereo 2

11/09/12 Pas4 Maria RG 121 EUA 12/07/70 No Endereo 3

11/09/12 Pas5 Kanda JL 234 Japo 12/08/78 No Endereo 4

20/09/12 Pas1 Joo VS 987 Argentina 1/1/65 No Endereo 1

Redundncia e Anomalias!A tabela do exemplo exibe dados redundantes.

!

!Estas redundncias produzem vrias anomalias:!Anomalia de atualizao!Anomalia de remoo!Anomalia de insero

Anomalias de Insero! necessrio inserir valores nulos para os atributos cujo valor ainda no foi determinado ou valores invlidos, quando esses atributos pertencem chave primria, o que conduz a inconsistncia

!

!No exemplo, quando se insere uma nova estrangeiro necessrio introduzir valores nulos e valores invlidos para os atributos que ainda no so conhecidos. A "alternativa" consiste em no inserir dados de um novo estrangeiro enquanto a alocao no for efetuada

!

!A duplicao de alguns dados poder dar origem a erros de insero, o que resulta em inconsistncia.

Anomalias de Alterao!A existncia de redundncia conduz ao perigo de aps uma atualizao, apenas parte dos dados terem sido atualizados.

!

!No exemplo, se alterar o endereo da embaixada da Argentina, poder ocorrer uma anomalia deste tipo se no se atualizaram todas as ocorrncias da mesma embaixada

Anomalias de Remoo!A remoo de determinados dados podem levar eliminao de outra informao que no se pretendia apagar.

!

!A remoo de uma estrangeiro pode conduzir perda de informao relativa a uma embaixada

Dependncia Funcional!Considere dois conjuntos de atributos X e Y de uma relao R.

!

!XY (o atributo X determina funcionalmente o atributo Y) sempre que duas tuplas quaisquer de R tiverem o mesmo valor para X, elas possuem tambm o mesmo valor para Y. Ou seja, para qualquer par de tuplas t1 e t2 em R, se

t1[X] = t2[X], ento t1[Y] = t2[Y]

!

!Se v(X) ocorrer em duas ou mais linhas em R, v(Y) deve ser o mesmo em todas estas linhas.

Determinante: X Determinado: Y

Dependncia Funcional Exemplos

!Empregado(matrcula, nome, CPF, salrio, matricula_supervisor)!matrcula nome!matrcula CPF!CPF matrcula!{CPF, matrcula} {nome, CPF }!matrcula {matricula_supervisor, salrio}!

!Projeto(nmero, nome, verba, localizao)!nmero {nome, localizao}!

!Alocao (matrcula_empr, nmero_proj, qtd_horas)!{matrcula_empr, nmero_proj} qtd_horas

Propriedades de uma DF!Dependncia funcional total (completa):!Se X determina totalmente Y, ento Y no funcionalmente dependente de nenhum subconjunto dos atributos que compem X !cidade --> ddd (total)!cidade, nome --> ddd (parcial)

!

!Sejam a, b, g atributos (simples ou compostos)! Transitividade:!se a b e b g, ento a g!cpf cidade e cidade DDD, ento cpf DDD

Normalizao x DF!O conhecimento de dependncia funcional til na deteco de redundncias.!

!Considere uma relao R com trs atributos, XYZ.!Se nenhuma dependncia funcional existe, ento no h redundncia.!Porm se X Y, temos vrias tuplas em R podendo ter o mesmo valor para X e, conseqentemente, tero o mesmo valor para Y.

!

!Sintetizando: o processo de normalizao consiste basicamente em remover dependncias funcionais.

Formas Normais!Para eliminar redundncias e anomalias de um conjunto de relaes, precisamos levar este conjunto atravs de diversas formas normais:!1a Forma Normal (1FN)!2a Forma Normal (2FN)!3a Forma Normal (3FN)!

!Dizemos que uma relao est em uma certa FN, se esta relao obedece ao conjunto de regras estabelecidas por esta FN.!

!A normalizao um processo em cadeia: !Para uma relao estar na 3FN, ela deve estar na 2FN que por sua vez deve estar na 1FN

1a Forma Normal 1FN!Uma tabela est na 1FN se:

!

!No tem grupos repetidos de atributos!

!Cada um de seus atributos atmico!Um atributo atmico se no h necessidade de decompor este valor

2a Forma Normal 2FN!Uma tabela est na 2FN se est na 1FN, e cada um dos atributos no pertencentes chave primria for dependente total dessa chave.!

!Ou seja, se uma coluna de uma tabela no pertence chave e pode ter seu valor determinado por parte da chave dita como dependente parcialmente da chave.!

!Portanto, para levar um conjunto de tabelas 2FN, o objetivo remover dependncias parciais.

2a Forma Normal 2FN !Carro = ( placa, licena_dono, nome_dono, modelo, qtd_km_rodados, qtd_km_rodados_por_dono)

!

!

!Relao1 = ( licena_dono, nome_dono )!Relao2 = ( placa_carro, modelo, qtd_km_rodados)!Relao3=(placa_carro,licena_dono, qtd_km_rodados_por_dono)

3a Forma Normal 3FN!Uma tabela est na 3FN se est na 2FN, e cada um dos atributos no pertencentes chave primria NO possui dependncia funcional transitiva com essa chave.!

!Carro ( placa, modelo, quantidade_km_rodados, cdigo_fabricante, nome_fabricante )

!

!Carro ( placa, modelo, quantidade_km_rodados, cdigo_fabricante )!Fabricante ( cdigo, nome )

Voltando ao Exemplo Inicial!Quanto objetos a tabela ESTRANGEIROS descreve ao mesmo tempo?!pases (nacionalidade)!estrangeiros (passaporte)!entradas no pas (data, passaporte)!

!Quantos relacionamentos a tabela ESTRANGEIROS descreve ao mesmo tempo?!estrangeiros e entrada no pas!estrangeiros e sua nacionalidade

Dependncias Funcionais!Estrangeiros!passaporte nome, nacionalidade, nasc, j-ficha!

!Nacionalidade!nacionalidade embaixada, ex -visto!

!Entrada!data, passaporte voo

Depois da NormalizaoData Passap Voo10/09 Pas1 RG12110/09 Pas2 AR87610/09 Pas3 RG12111/09 Pas4 RG12111/09 Pas5 JL23420/09 Pas1 VS987

Passap Nome Nacionalidade Nasc J-ficha

Pas1 Joo Argentina 1/1/65 No

Pas2 Jos Argentina 22/4/50 Sim

Pas3 Hans Argentina 12/09/50 No

Pas4 Maria EUA 12/07/70 No

Pas5 Kanda Japo 12/08/78 No

Nacionalidade EmbaixadaArgentina Endereo 1Alemanha Endereo 2EUA Endereo 3Japo Enddereo 4

ENTRADAS

ESTRANGEIROS

NACIONALIDADE

Projeto Fsico!Especificao em SQL do esquema relacional para o SGBD escolhido.!Critrios a atender :!Tempo de resposta! Espao de armazenamento!Volume de transaes suportado

!Estruturas de armazenamento e de recuperao de informaes!Mecanismos de acesso devem ser escolhidos, visando sempre o aprimoramento da performance dos aplicativos de BD.

Projeto Fsico!Devem ser especificados no apenas as tabelas criadas, mas tambm!os ndices necessrios,

!

!as restries de integridade ,!

!algumas operaes de incluso, excluso e atualizao de dados para cada tabela, !

!bem como as consultas que a aplicao deve realizar.

Implementao do Sistema de Banco de Dados

!Com instrues da DDL e da DML (SQL)!Faz-se a carga do Banco de Dados

Dvidas??? Perguntas???

Questionamentos???