Bases de Dados - Departamento de Cincia de Computadores michel/BD0304/ Bases de Dados 3 Bases

  • Published on
    12-Jul-2018

  • View
    213

  • Download
    0

Transcript

  • Bases de Dados 1

    Bases de Dados

    Objectivos: aprender a usar efectivamente um Sistema de Gesto de Bases de Dados, atravs:

    desenho da BDs modelao (ER/EER/ODL) criao e manipulao da BDs => programao em SQL estrutura do SGBDs: ficheiros de indices, transaces, concorrncia, recuperao,...

    nfase: Modelo relacional Modelao: modelos ER, EER e ODL. Implementao: SQL

    Aulas prticas: desenvolvimento de uma BD modelao de uma BDs Implementao em SQL em MS-SQLserver ou Oracle Formulao de questes em SQL Interfaces BDs (formulrios ou relatrios em Access)

  • Bases de Dados 2

    BDs: Avaliao e Bibliografia

    Avaliao: trabalhos prticos (obrigatrios para obter frequncia -- mnimo 40%): 4/5 valores exame final: 16/15 valores (nota mnima: 6 valores)

    Bibliografia: Elmasri and Navathe, Fundamentals of Database Systems, 3rd. Ed., Addison-

    Wesley, 1999. J. Ullman and J. Widom, A first course in Database Systems, Prentice-Hall, 1997 Korth and Silberschatz, Database Systems Concepts, 3rd. Ed.,McGraw-Hill, 1996

    Encomendar livros pela Internet: www.bookpool.com www.amazon.co.uk ou www.amazon.com

  • Bases de Dados 3

    Bases de Dados (definio)

    Conjunto dados que se relacionam entre si

    Dados: representam factos da vida real que podem ser guardados

    Contactos: nomes, endereos, telefones, ... Escola: alunos, professores, disciplinas, turmas, horrio, ... Banco: cliente, conta, dependncia, ... Clube vdeo: loja, filme, fornecedor, empregado, scio, ...

    Vantagens:

    organizao de grandes volumes de informao

    uso de programas que facilitam a definio, criao, e manipulao da BDs,

    i.e. Sistemas de Gesto de Bases de Dados (SGBDs):

    Oracle, Sybase, DB2, SQL-server (Access), Adabas, Ingres, ...

    Sistema de BDs = BDs + SGDBs

  • Bases de Dados 4

    Exemplo de BDs

    ttulo ano durao tipoA Testemunha

    1987 120 crO Silncio dos

    Inocentes 1992 130 cr

    nome ttuloF

    Harrison FordJudy Foster

    Participa

    Har rison FordA Testemunha Guerra das Estrelas

    Actor

    FilmeO Silncio dos Inocentes

    Filme( ttulo, ano, durao, tipo)Actor( nome, endereo,)Participa( nomeA,ttuloF)

    nome endereo

    Harrison Ford 789 Palm Dr, Beverly Hills

    300 Stars Av., Beverly HillsJudy Foster

    Quais os filmes em que Harrison Ford participou? Ordene os filmes por ano de estreia.

  • Bases de Dados 5

    Exemplo de BDs

    codAluno nomeAluno curso95001 Catarina CC96005 Carlos MAT

    codDisc nomeDisc creditos depart.BD Bases de Dados 4 CCAL Analise de Dados 4 MA

    codDisc sem anoLic anoLec profBD 1 3 1999 F. SilvaIA 2 3 1999 M. Filgueiras

    codAluno codDisc nota95001 BD 1496005 BD 14

    Alunos

    Disciplinas

    Funcionamento

    Classificaes

    Listar as disciplinas frequentadas e classificaes obtidas pelo aluno Carlos.

  • Bases de Dados 6

    Desenvolvimento de BDs

    Consultar e actualizar informao da BDs Em que filmes participou o actor Harrison Ford?

    Quantos filmes foram produzidos em 1992?

    Definir utilizadores da BDs e permisses de acesso (administrao)

    Desenvolver ecrs grficos para operaes mais comuns

    bancos: ecrs especficos para os caixas no atendimento a clientes

    Criar relatrios especficos

    extracto de conta

    Tornar parte da BDs acessvel via Internet? Cada vez mais importante: compra de livros pela Internet, ...

  • Bases de Dados 7

    Caractersticas de uma abordagem de BDs

    Independncia dos dados o catlogo do sistema tem a descrio da BDs (meta-dados):

    estrutura de cada ficheiro usado para a BDs tipo e formato de cada item de dados restries vrias sobe os dados

    permite que o software do SGBDs seja independente da BD em vista.

    Separao entre dados e programas (abstrao) num sistema tradicional de ficheiros, a estrutura do ficheiro de dados est

    inserida nos programas que manipulam esses ficheiros dificuldades em alterar a organizao dos dados

    numa BDs, a estrutura dos ficheiros est no catlogo do SGBDs e portanto separada dos programas de acesso

    conduz a independncia dados/programa (propriedade: abstraco dos dados)

  • Bases de Dados 8

    Caractersticas de uma abordagem de BDs (cont.)

    Suporte de vises mltiplas dos dados permite fornecer diferentes perspectivas (ou vises) dos dados para diferentes

    utilizadores. Viso:

    pode ser um subconjunto da BDs ou pode conter dados virtuais que so derivados de ficheiros da BDs mas que no so

    explicitamente guardados. Exemplo: performance dos alunos de um curso (ordem alfabtica de aluno).

    nomeAluno nomeDisc nota sem anoLecCatarina EDA 12 1 98/99Catarina SO 13 2 98/99Pedro SO 14 2 98/99Pedro IG 15 2 98/99

    Performance

  • Bases de Dados 9

    Caractersticas de uma abordagem de BDs (cont.)

    Partilha de dados acesso multi-utilizador BDs => SGBDs tem de controlar concorrncia para

    garantir consistncia e correco nas actualizaes da BDs

    exemplo: vrios agentes de viagens a tentarem reservar um lugar num determinado voo. O SGBDs tem de garantir que um determinado lugar s pode ser acedido para reserva

    por apenas um agente.

  • Bases de Dados 10

    Capacidades desejveis para um bom SGBDs

    Controlar redundncia

    Restrio de acesso no autorizado passwords, diferentes nveis de acesso, acesso restricto a software com previlgios

    Providenciar mltiplas Interfaces com utilizador

    Garantir a satisfao de restries de integridade

    Providenciar Mecanismos de Backup e Recuperao

  • Bases de Dados 11

    Desenho/Modelao de Bases de Dados

    Porqu a fase de modelao? Acordar numa estrutura da BDs antes de enveredar por uma determinada

    implementao. Simplifica eventuais correces a fazer, facilita o entendimento dos dados por

    parte de no-especialistas, simplifica implementao, ...

    Envolve determinar: Quais as entidades a modelar Como que se relacionam as entidades Que restries existem no domnio Como conseguir um bom bom modelo de dados

  • Bases de Dados 12

    Fases no Desenho de BDs

    Problema

    Requisitos/Anlise

    Requisitos da BDs

    1. Fase: entrevistas com potenciais utilizadores da BDs compreender e documentar os seus requisitos produzir um conjunto de requisitos o mais detalhadoe completo possvel

    Desenho conceptual

    Esquema conceptual(modelo de Alto-Nvel)

    2. Fase: definir um esquema conceptual da BDs que inclua

    descrio das entidades da BD, relacionamentosentre entidades e restries

    evitar detalhes de implementao

    Desenho lgico Independente do SGBDs

  • Bases de Dados 13

    Fases no Desenho de BDs (cont.)

    3. Fase: implementao da BDs usando um SGBDs comercial

    Esquema da BDs(especifico de SGBDs)

    4. Fase: estruturas em memria e organizao dos ficheirosda BDs

    ficheiros de indices

    Desenho lgico(Mapeamento do Modelo de Dados)

    Dependente do SGBDs

    Desenho fsico

    Esquema interno

  • Bases de Dados 14

    Formalismos de modelao de BDs

    Modelo E/R - Entidades/Relacionamentos: mais prximo do relacional pode ser enriquecido para integrar conceitos de objectos: modelo EER

    Modelo ODL - Object-Definition Language mais no espirito de modelos orientados a objectos

    Ambos modelos podem ser traduzidos (semi-automaticamente) para esquemas relacionais.

    ODL traduz-se mais naturalmente num esquema orientado-a-objectos

  • Bases de Dados 15

    Modelo Entidade-Relacionamento

    Entidades: objecto ou conceito do mundo real com uma existncia independente com existncia fsica, e.g. carro, empregado, produto, aluno, etc. com existncia conceptual: uma empresa, uma profisso, um curso, etc.

    Atributos: propriedades que caracterizam (e esto associadas) uma entidade

    atributos de Pessoa: nome, sexo, data nascimento, morada, etc.

    Relacionamentos representam interaces entre 2 ou mais entidades

    trabalha(empregado, departamento)

  • Bases de Dados 16

    Modelo ER: Atributos

    Valores dos atributos = Informao da BD Domnios de atributos

    conj. de valores que podem ser atribudos a um atributo de uma entidade.

    Tipos de atributos: atributo simples ou atmico: no divisvel.

    atributo composto: divisvel em atributos simples com significado independente o atributo Endereo da entidade PESSOA pode ser decomposto em: Rua, Cidade e CdigoPostal.

    atributo de valor nico: tm apenas um valor para uma determinada entidade

    atributo de valores-mltiplos: pode tomar 1 ou mais valores de um conjunto de valores para a mesma entidade.

    entidade CARRO, atributo Cor-do-carro (vermelho, branco, cinza, ) entidade PESSOA, atributo TtuloAcadmico (licenciado, mestre, doutor,)

  • Bases de Dados 17

    Modelo ER: Atributos (cont.)

    Tipos de atributos: atributo derivado: pode ser derivado de outro atributo.

    Idade pode ser derivado de DataNascimento

    atributo com valor nulo (NULL) quando o atributo no aplicvel a uma determinada entidade. Ex: o atributo TtulosAcadmicos s se aplica a pessoas com curso superior, sendo

    nulo para as restantes.

    Interpretaes para o valor NULL:

    o atributo no se aplica;

    o valor do atributo no conhecido ou est em falta.

  • Bases de Dados 18

    Entidade Tipo: determina o esquema para um conjunto de entidades que partilham a mesma

    estrutura (atributos). caracteriza-se por um nome e uma lista de atributos.

    Esquema da entidade-tipo EMPREGADO:

    Atributo chave de uma entidade-tipo: o atributo que identifica de forma nivoca cada entidade.

    deve aparecer sublinhado.

    Ex: EMPRESA( Nome, Endereo, Presidente)

    PESSOA( Nome, NumBI, DataNasc, Endereo, )

    pode ser constitudo por mais do que um atributo simples.

    Modelo ER: Entidade Tipo

    EMPREGADO(Nome, Idade, Salrio)

  • Bases de Dados 19

    Uma empresa est dividida em departamentos. Cada departamento tem um nome, um nmero e um gerente. Inclui ainda a data em que o gerente comeou a gerir o departamento. O departamento pode ter vrias localizaes.

    Um departamento controla um determinado nmero de projectos. Cada projecto tem um nome, um nmero e uma localizao nica.

    Para cada empregado, guardar o nome, o nmero do BI, endere, salrio, sexo e data de nascimento.Um empregado pertence a um departamento, mas pode trabalhar em vrios projectos, que no so necessriamente controlados pelo mesmo departamento.Tomar nota do nmero de horas por semana que um empregado trabalha num dado projecto. Tomar nota do supervisor directo de cada empregado.

    Tomar nota do nmero de dependentes de cada empregado para efeitos de seguro. Para cada dependente, guardar o nome, sexo, data de nascimento e grau de parentesco para o empregado.

    Requisitos de uma BDs de uma Empresa

  • Bases de Dados 20

    Identificar entidades-tipo e atributos:

    1. DEPARTAMENTO( Nome, Num, {Local}, Gerente, GerData)

    atributos com valores mltiplos: Local

    2. PROJECTO( Nome, Num, {Localizao}, DepCtl)

    3. EMPREGADO( Nome(P,U), NumBI, Sexo, Endereo,Salrio,Dnasc,

    Dept, Super)

    4. DEPENDENTE( Empregado, DNome, Sexo, Dnasc, GrauPar)

    Construo do modelo ER para a BD-Empresa

  • Bases de Dados 21

    Falta representar:

    1 empregado trabalha em N projectos

    num. de horas que cada empregado trabalha em cada projecto Identificar

    entidades-tipo e atributos:

    Podemos representar esta info como:

    atributo-composto-multivalor da entidade Empregado

    TrabalhaEm( Projecto, Horas)

    atributo-composto-multivalor da entidade Projecto:

    Trabalhadores( Empregado, Horas)

    Construo do modelo ER para a BD-Empresa

  • Bases de Dados 22

    Falta representar (entre outros):

    um Departamento tem um Director que o dirige; um Director um empregado

    Dirige( Departamento, Empregado)

    O esquema Dirige traduz um relacionamento entre 2 entidades,

    Departamento e Empregado.

    No modelo ER, uma entidade no pode referenciar directamente outra entidade; tal necessidade traduz-se na definio de um relacionamento.

    Outros relacionamentos:TrabalhaPara(Empregado,Departamento)

    Controla(Departamento,Projecto) DependeDe(Dependente,Empregado)

    Supervisiona(Empregado,Empregado) TrabalhaEm(Empregado,Projecto,Horas)

    Relacionamentos

  • Bases de Dados 23

    Com a definio dos relacionamentos, algunds dos atributos so redundantes pelo que

    no devem ser includos no esquema. O esquema consiste:

    Entidades:DEPARTAMENTO( Nome, Num, {Local}, )

    PROJECTO( Nome, Num, {Localizao} )

    EMPREGADO( Nome(P,U), NumBI, Sexo, Endereo,Salrio,Dnasc)

    DEPENDENTE( DNome, Sexo, Dnasc, GrauPar)

    Relacionamentos:trabalhaPara(Empregado,Departamento) dependeDe(Dependente,Empregado)

    controla(Departamento,Projecto) dirige(Empregado,Departamento, GerData)

    supervisiona(Empregado,Empregado) trabalhaEm(Empregado,Projecto,Horas)

    Falta analisar o tipo de participao das entidades no relacionamentos.

    Esquema da BDs

  • Bases de Dados 24

    Grau de um relacionamento:

    nmero de entidades no relacionamento.

    Ex. de um relacionamento ternrio:

    fornece(Fornecedor, Produto, Projecto)

    Relacionamentos com atributos.Horas um atributo do relacionamento

    trabalhaEm(Empregado, Projecto, Horas)

    Relacionamentos (Grau e Atributos)

  • Bases de Dados 25

    Restries nos relacionamentos:

    permitem limitar as combinaes possveis entre entidades partic ipantes

    Ex: um empregado trabalha para apenas um departamento.

    Tipos de Restries:

    Cardinalidade dos Relacionamenos:

    tipo de participao das entidades no relacionamento

    1 : N -- um para-muitos

    trabalhaPara(Empregado, Departamento)

    M : N -- muitos-para -muitos

    trabalhaEm(Empregado, Projecto, Horas)

    1 : 1 -- um-para-muitos

    dirige(Empregado, Departamento)

    Relacionamentos (Restries)

  • Bases de Dados 26

    Tipo de participao:

    especifica se a existncia de uma instncia de entidade depende do seu relacionamento com

    outra entidade, via esse relacionamento.

    Participao total (dependncia existncial)

    quando todas as instncias de uma entidade esto relacionadas com alguma instncia de uma outra entidade participante no relacionamento.

    trabalhaPara(Empregado, Departamento)

    Participao parcial

    quando no se espera que todas as instncias de uma entidade participem no relacionamento.

    dirige(Empregado, Departamento)

    Restries nos Relacionamentos (cont.)

  • Bases de Dados 27

    Entidade Fraca:

    identificada pelo seu relacionamento (relacionamento identificador) com

    determinadas entidades (entidade identificadora)

    tem sempre participao total (dependncia existncial) em relao ao

    relacionamento-identificador.

    Possui uma chave-parcial, que o conjunto de atributos que univocamente

    determinam a entidade fraca relacionada com a mesma entidade-identificadora.

    Ex: dependenDe(Dependente, Empregado)

    Entidades Fracas

  • Bases de Dados 28

    Entidades-tipo:DEPARTAMENTO( Nome, Num, {Local}, )

    PROJECTO( Nome, Num, Local )

    EMPREGADO( Nome(P,U), NumBI, Sexo, Endereo, Salrio, Dnasc )

    DEPENDENTE( DNome, Sexo, Dnasc, GrauPar )

    Relacionamentos:trabalhaPara(Empregado,Departamento) N:1 total/total

    dependeDe(Dependente,Empregado) N:1 total/parcial

    controla(Departamento,Projecto) 1:N parcial/total

    dirige(Empregado,Departamento, GerData) 1:1 parcial/parcial

    supervisiona(Empregado,Empregado) 1:N parcial/parcialSupervisor Supervisionado

    trabalhaEm(Empregado,Projecto,Horas) M:N total/total

    Modelo ER para a BDs sobre uma Empresa

  • Bases de Dados 29

    nfase na representao dos esquemas em vez de instncias de entidades e relacionamentos.

    Notao para os diagramas:

    Diagramas ER

    Relacionamento-identificador

    Relacionamento

    Entidade-Fraca

    Entidade-Tipo

    Atributo

    Atributo-Chave

    Atributo-Multivalor

    Atributo-Derivado

    Atributo-Composto

  • Bases de Dados 30

    Diagramas ER (cont.)

    E1 E2R

    E1 E2R

    ER

    1 N

    (min,max)

    Participao-total de E2em R

    1:N

    Restrio-estruturalda participaa de E em R

    Uma entidade E participa num relacionamento R com restrio (min,max) em que0 min max e max 0, se para cada entidade e 5 E, e participa pelo menosmin eno mximo max instncias do relacionamento R.

    min = 0 -- participao parcialmax > 0 -- participao total

  • Bases de Dados 31

    Exemplo Restrio-Estrutural

    No diagrama: um empregado trabalha para um departamento Num departamento trabalham pelo menos 4 empregados

    Nomes para as entidades-tipo, atributos e relacionamentos deve ser feita com critrio: entidades - nomes singular atributos - nomes relacionamentos - nomes ou verbos de forma a facilitar a leitura da esquerda para

    a direita

    Empregado DepartamentotrabalhaPara(1,1) (4,N)

  • Bases de Dados 32

    O Modelo EER (ER Extendido)

    BDs recentes (Multimdia, GIS, CAD/CAM,) requerem novos conceitossemnticos de modelao: subclasse, superclasse, especializao e generalizao, herana de atributos, etc.

    Subclasses e Superclasses uma subclasse corresponde a um sub-conjunto de entidades com alguma

    caracterstica comum e pertencentes mesma entidade-tipo superclasse corresponde entidade-tipo que aglutina os vrios sub-conjuntos de

    entidades, i.e. subclasses. Ex:

    Empregado

    TcnicosSecretrias Engenheiros Directoressubclasses

    superclasse

  • Bases de Dados 33

    O Modelo EER (Relacionamento ISA)

    O relacionamento ISA (ou superclasse/subclasse) caracteriza a ligao entreas subclasses e a respectiva superclasse

    uma entidade membro de uma subclasse representa a mesma entidade-fsica de um membro da superclasse, apenas os papeis so diferentes.

    Ex. A entidade Director de nome X a mesma entidade X de Empregado;

    EmpregadoDirector isa

    EmpregadoSecretria isa

    EmpregadoTcnico isa

  • Bases de Dados 34

    EER: Herana de Atributos

    As subclasses herdam todos os atributos da sua superclasse uma subclasse com todos os atributos que herda da superclasse, uma

    entidade-tipo. Porqu a diviso em subclasses?

    Certos atributos aplicam-se a apenas algumas instncias da superclasse Alguns relacionamentos podem ter participao de apenas alguns membros de

    uma subclasse

  • Bases de Dados 35

    EER: Especializao

    Especializao o processo de de definio do conjunto das subclasses de uma entidade-tipo

    (superclasse da especializao) e.g. {Secretria, Engenheiro, Tcnico} especializa Empregado com

    base no tipo de trabalho. podemos ter vrias especializaes da mesma entidade-tipo com base em

    diferentes caractersticas. Podemos associar atributos especficos (extra) a cada subclasse estabelecer relacionamentos-tipo especficos entre uma subclasse e outras

    entidades-tipo ou outras subclasses

  • Bases de Dados 36

    Diagrama EER

    Tcnico EngenheiroSecretria

    Director

    ProjectoSindicato

    VelEscritaQualificao EngTipo

    dirige

    SalrioEfiliado

    d

    d

    Empregado

    EmpEfectivo

    EmpPrazo

    Escalo

    NumBI

    Nome 3 especializaes de Empregado

    d

    Salrio

  • Bases de Dados 37

    EER: Generalizao

    Generalizao: processo funcionalmente inverso da especializao. eliminam-se as diferenas entre vrias entidades-tipo, identificam-se as

    caracteristicas comuns que passaro a caracterizar uma nova superclasse da qual as entidades-tipo originais so subclasses especiais.Carro(Matricula, Nreg, Npass, VelMax, Preo)Camio(Matricula, Nreg, Neixos, Tonelagem, Preo)

    generalizando temos:

    Veculo

    Carro Camio

    NpassVelMax

    Tonelagem

    Neixos

    d

    Preo

    Nreg

    Matricula

  • Bases de Dados 38

    Tipos de Especializao/Generalizao

    Especializao definida-por-atributo quando a diviso em subclasses se basei numa condio de membro e.g. condio tipoTrabalho=secretria determina quais dos empregados vo

    pertencer subclasse Secretria.

    Especializao definida-por-utilizador quando no existe a condio.

    Especializao disjunta quando as subclasses so disjuntas, i.e. cada entidade pode ser membro de no

    mximo uma subclasse de especializao.

    Especializao com sobreposio quando a mesma entidade pode pertencer a mais do que uma subclasse, e.g. a

    superclasse Pessoa pode decompor-se em subclasses Empregado, Estudante, Licenciado (e.g. um assistente um empregado da universidade, licenciado e aluno de doutoramento)

    d

    o

  • Bases de Dados 39

    Tipos de Especializao/Generalizao (cont.)

    Especializao Total (linhas duplas nos diagramas) quando toda a entidade de uma superclasse tem de ser membro de alguma sub-

    classe. e.g. especializao {EmpEfectivo, EmpPrazo} de Empregado. Todos

    empregados esto numa das subclasses.

    Especializao Parcial (linha simples nos diagramas) permite que uma entidade no pertena a qualquer das subclasses.

    Temos assim 4 tipos de especializao: disjunta total; disjunta parcial; sobreposio total; sobreposia parcial o tipo de especializao determinado a partir do significado na vida real

    A generalizao de uma superclasse habitualmente total contm as entidades das subclasses de onde foi derivada.

  • Bases de Dados 40

    Desenho de BDs

    A modelao visa definir a estrutura da BDs antes da sua implementao, de forma a permitir a sua compreenso por parte dos utilizadores.

    Ideias(info a modelar)

    ER/EER(entidade/relacionamento)

    ODL(object definition lang.)

    Relaes

    SGBD Relacional

    SGBD O-O

    Conceptualizao Implementao

  • Bases de Dados 41

    Modelo Relacional

    Introduzido por Codd (1970) Base de Dados = Conjunto de relaes Relao Tabela

    Filme Ttulo Ano Durao TipoZorro 1998 cor120

    Filme (Ttulo, Ano, Durao, Tipo) Esquema

    Guerra das Estrelas 124 cor

    Mighty Ducks 1991 104 cor

    atributos

    tabela

    tuplo1977

  • Bases de Dados 42

    Esquema Relacional de uma BDs

    Empregado(Pnome,Unome,EBI,Dnasc,Morada,Sexo,Salrio,SuperBI,Ndep)

    Departamento(Dnome,Dnum, DirBI, DirData)

    Locais_Dep(Dnum,Dlocal)

    TrabalhaEm(EBI,Pnum,Horas)

    Projecto(Pnome,Pnum,Plocal)

    Dependente(EBI,Nome,Sexo,Dnasc,GrauParentesco)

  • Bases de Dados 43

    Modelo Relacional: conceitos

    Domnio: conjunto de valores de um dado tipo que caracterizam um atributo

    Tuplos: sequncia ordenada de valores (ordem da sequncia importante) tuplos de uma relao (ou tabela) no tm ordem os valores das componentes de um tuplo so atmicos

    no relacional no pode haver atributos compostos ou multivalor

    Chave de uma relao R identifica de forma nica os tuplos de R conjunto mnimo de atributos de R, t.q. no existem 2 tuplos distintos de R com

    valores iguais nesses atributos. Uma relao pode ter vrias chaves candidatas

    chave primria; chaves alternativas

  • Bases de Dados 44

    Restries Intrnsecas do Relacional

    Integridade de entidade os valores da chave-primria no podem ser nulos

    Unicidade da chave no podem existir 2 tuplos diferentes com valores iguais na chave

    Chave externa conjunto de atributos de uma relao que referenciam a chave de outra relao

    Integridade referncial um tuplo de uma relao que se refira a uma outra relao, tem de se referir a um

    tuplo existente nessa relao garante consistncia entre tuplos de 2 relaes e.g. os tuplos correspondentes ao empregado-supervisor com EBI 3456789 e ao

    departamento nmero 5 tm de existir, dado o seguinte empregado:

    Empregado(Joo,Pinto,7654321,19975-03-04,R. das Fontainhas,M,250000,3456789,5)

  • Bases de Dados 45

    Interpretao de uma relao

    Relaes uniformizam a representao de factos sobre entidades erelacionamentos

    Um esquema de uma relao deve ser visto como uma declarao

    Esquema de BD relacional = conjunto de esquemas relacionais +conjunto de restries de integridade

    Operaes no modelo relacional: actualizaes: inserir, remover e modificar consultas: lgebra relacional

  • Bases de Dados 46

    Operao inserir

    Permite inserir um ou mais tuplos numa tabela pode violar qualquer dos 4 tipos de restries:

    domnio: se um dos valores no pertencer ao domnio chave: o valor da chave do novo tuplo j existe num outro tuplo da tabela integridade de entidade: se o valor da chave do novo tuplo for null integridade referncial: se o valor de uma chave externa referir um tuplo no

    existente.

    Se uma das restries for violada, opta-se por: rejeitar a insero (com aviso ao utilizador) ou, tentar corrigir a razo para a rejeio ocorrer.

  • Bases de Dados 47

    Operao remover

    remove tuplos de valores de uma tabela pode violar apenas a integridade referncial:

    no caso de o tuplo a remover for referenciado por uma das chaves-externas de outro tuplo naBDs.

    Requer uma condio sobre os atributos de forma a selecionar o tuplo outuplos a serem removidos remover todos os empregados do departamento 10.

    Caso ocorra violao, opta-se por: rejeitar a operao, ou procurar propagar a operao e remover todos os tuplos que referenciam o que

    est a ser removido, ou alterar para null os valores dos atributos dos tuplos que referenciam o que est a

    ser removido

    Operao modificar = remover+inserir (regras destas operaes)

  • Bases de Dados 48

    Converso do Modelo ER para o Modelo Relacional

    Passo 1: entidade-tipo relao

    atributos simples da entidade atributos da relao atributos compostos atributos individuais na relao chave da entidade chave da relao

    EmpregadoNome

    Pnome

    Unome

    Sexo

    EBI...

    Empregado Pnome Unome EBI Sexo ...

  • Bases de Dados 49

    ER Relacional

    Passo 2: entidade-fraca relao Seja W uma entidade-fraca e E a entidade-identificadora de W: Criar uma relao R em que:

    atributos simples de W atributos de R chave-principal de E e chave-parcial de W chave-principal de R

    EmpregadoNome

    ...

    EBI

    ...

    Dependente EBI Nome Sexo ...

    DependentedependeDe

    Chave-externa

  • Bases de Dados 50

    ER Relacional

    Passo 3: relacionamento binrio 1:1 R(E1,E2) Sejam S e T as relaes correspondentes s entidade E1 e E2, respectivamente. Escolhes-se uma das relaes, e.g. S (a que corresponde entidade com

    participao total em R) e: incluir como chave externa em S a chave-principal de T incluir todos atributos simples do relacionamento R na relao S

    EmpregadoDnum

    DirData

    EBI

    ...

    Departamento Dnome Dnum DirBI DirData

    Departamentodirige

    Chave-externa

    (0,1) (1,1)

  • Bases de Dados 51

    ER Relacional

    Passo 4: relacionamento binrio 1:N R(E1,E2) Sejam S e T as relaes correspondentes s entidade E1 e E2, respectivamente. Escolhes-se a relao que corresponde entidade participante do lado N em R,

    suponha-se que S: incluir como chave externa em S a chave-principal de T incluir todos atributos simples do relacionamento R na relao S

    EmpregadoDnum

    EBI

    ...

    Empregado Pnome ... EBI ... DNum

    DepartamentotrabalhaPara

    Chave-externa

    N 1

  • Bases de Dados 52

    ER Relacional

    Passo 4: relacionamento binrio M:N R(E1,E2) Criar uma nova relao S para representar o relacionamento R.

    incluir como chave externa em S as chaves-principais das relaes que representam as entidades E1 e E2 participantes em R

    o conjunto das chaves-externas formar a chave-principal de S incluir todos atributos simples do relacionamento R na relao S

    EmpregadoPnum

    EBI

    ...

    trabalhaEm EBI Pnum Horas

    ProjectotrabalhaEm

    chaves-externase

    chave-principal

    M N

    Horas

  • Bases de Dados 53

    ER Relacional

    Passo 5: relacionamento binrio M:N R(E1,E2) Criar uma nova relao S para representar o relacionamento R.

    incluir como chave externa em S as chaves-principais das relaes que representam as entidades E1 e E2 participantes em R

    o conjunto das chaves-externas formar a chave-principal de S incluir todos atributos simples do relacionamento R na relao S

    EmpregadoPnum

    EBI

    ...

    trabalhaEm EBI Pnum Horas

    ProjectotrabalhaEm

    chaves-externase

    chave-principal

    M N

    Horas

    Nota: os relacionamentos 1:1 e 1:N tambm podem ser transformados de acordo com o passo 5.

  • Bases de Dados 54

    ER Relacional

    Passo 6: atributos-multivalor Para cada atributo A multivalor, cria-se uma nova relao S que

    inclui o atributo de A mais a chave-principal, K, da relao que representa a entidadeou relacionamento que tem A como atributo multivalor.

    A chave-principal de S ser acombinao de A e K.

    DepartamentoDnum

    ...

    Locais_Dep Dnum Dlocal

    Ex: um departamento pode tervrias localizaes.

    Dlocal

    Nota: os passos de 1 a 6 seriam suficientespara converter o esquema-ER da pag. 28 noesquema-relacional da pag. 44 (BDs Empresa).

  • Bases de Dados 55

    ER Relacional

    Passo 7: relacionamentos com aridade superior a 2 (mais de 2 entidades) Para cada relacionamento R com aridade n>2, criar uma nova relao S:

    incluir como chaves-externas em S, as chaves-principais das relaes que representamas entidades participantes.

    A chave-principal de S ser o conjunto de todas as chaves-externas. Incluir como atributos de S, todos os atributos do relacionamento R.

    Fornecimento Fnome Pnome Pnum Qtd

    FornecedorFnome

    Pnome

    Pnum

    Projecto

    Produto

    fornecimento

    Qtd

  • Bases de Dados 56

    Os STCP pretendem construir uma base de dados sobre os percursos dosseus autocarros. A base de dados deve guardar informao relativa aos autocarros, como sejam a matrcula, a data de entrada em servio, o nmero de quilmetros, a data da prxima reviso e o tipo (marca/modelo) deautocarro. Cada tipo de autocarro tem uma marca, um modelo, um nmerode lugares sentados e um nmero de lugares de p. A base de dados deveguardar tambm informao relativa aos percursos. Um percurso identificado por um nmero (e.g. 78, 35) e tem uma distncia total emquilmetros. Os percursos percorrem paragens. As paragens tm umnmero identificador, um nome, e uma localizao decomposta em local,rua e nmero. Existem limitaes aos percursos que um determinado tipode autocarro pode fazer, inerentes s suas dimenses. Estas limitaesdevem ficar registadas na base de dados. Existe um percurso especialpara quando um autocarro mais o respectivo condutor so alugados, eeste percurso no percorre paragens. Deve ser guardada tambm informao relativa aos condutores, como sejam o nmero de BI, o nome, a morada, a data de entrada em servio e os percursos que cada condutor est habilitado a fazer (um condutor pode estar habilitado a fazer vrios percursos).Na base de dados deve ficar registada tambm informao operacionaldiria, correspondente ao registo de sadas. Existem trs turnos desada, 6h, 14h e 22h. Um autocarro e um condutor fazem no mximo umasada por dia, podendo no fazer nenhuma. A informao do registo desada inclui a data, o turno, o condutor, o autocarro e o percurso atribudo.

    Exerccio de modelao

  • Bases de Dados 57

    Exerccio de modelao (cont.)

    Desenhe um diagrama-ER ou EER para a base de dados descrita acima indicando as chaves das entidades, a cardinalidade dosrelacionamentos e o tipo de participaes.

    Converta o diagrama da alnea anterior para o modelo relacional justificando os passos que efectua na converso.

    Diga se o seu modelo relacional consegue responder questo``Na data 24/12/00 no turno das 22h quantos autocarros fizeram opercurso 78?''. Justifique.

  • Bases de Dados 58

    EER Relacional Passo 8: Converter cada especializaao com m subclasses (S1,,Sm) e

    superclasse (generalizada) C, onde os atributos de C sao (k,a1,,an) em quek a chave primria, para relaoes usando uma das seguintes 4 opoes: 8a Criar uma relaao L para C com atributos atrib(L) = (k, a1,,an) e cp(L) =

    k. Criar uma relaao Li para cada subclasse Si, 1

  • Bases de Dados 59

    Boas relaes: como? o significado do esquema da relao deve ser simples de explicar

    no combinar os atributos de vrias entidades e relacionamentos numa nicarelao

    evitar relaes que permitam o aparecimento de anomalias de insero:

    Emp_Dep(Enome, EBI, Dnasc, Morada, Dnum, Dnome, DirDep)

    inserir um tuplo de um novo empregado, teremos de incluir valores sobre o departamento onde trabalha. Quando adicionamos info sobre o departamento, temosde ser consistentes com os valores adicionados sobre o mesmo departamento paraoutros empregados

    evitar ter atributos numa relao cujo valor pode ser nulo, pois nem semprese sabe qual a interpretao a aplicar.

    conceber relaes que possam ser combinadas por uma operao de junocom base numa condio de igualdade em atributos que so chaves-principais ou chaves-externas (evita-se gerar tuplos que no deviam existir --spurious tuples)

  • Bases de Dados 60

    SQL - Structured Query Language

    l SQL uma linguagem declarativa que permite:u definir a BDs ( uma DDL),

    u questionar e actualizar a BDs ( uma DML),

    u definir vises diferentes da BDs para diferentes utilizadores,

    u criar indices sobre ficheiros de forma a tornar o acesso mais rpido

    l SQL1 - 1a verso ANSI definida em 1986.l SQL2 - (ou SQL92) 2a verso ANSI definida em 1992.l SQL3 - est para brevel Base de dados que servir de exemplo:

    Filme(titulo,ano,comp, tipo, nomeEstdio, produtorC#)Actua(tituloFilme, anoFilme, nomeActor)Actor(nome,endereo, sexo, dataNasc)Executivo(nome, endereo, cert#, salrio)Estdio(nome, endereo, presC#)

  • Bases de Dados 61

    Resumo da sintaxe do SQL

    CREATE TABLE ( [],...[, ...] )

    DROP TABLE

    ALTER TABLE ADD

    INSERT INTO [(,...)]

    (VALUES (,...,) | )

    DELETE FROM [WHERE ]

    UPDATE SET =,... | [WHERE ]

    CREATE VIEW [(,...)] AS

    DROP VIEW

  • Bases de Dados 62

    Resumo da sintaxe do SQL (2)

    SELECT [DISTINCT] (FROM ( {} | )

    [WHERE ]

    [GROUP BY [HAVING ] ]

    [ORDER BY [], ...]

    = (ASC | DESC)

    = (*|( | (([DISTINCT]

  • Bases de Dados 63

    Restries e Triggers em SQL

    Elemento activo: (expresso ou comando) escrito uma vez, guardado na BDs, e espera-se que seja executado quando determinados eventos ocorrerem.

    em SQL2 temos vrias formas de indicar restries associadas a relaes que so testadas sempre que h modificaes nessas relaes:

    restries de chave restries de integridade referencial restries de domnio asseres genricas

    em SQL3 podem tambm estabelecer-se triggers (gatilhos) que so accionados com a ocorrncia de determinados eventos (i.e. quando determinada

    condio for verdadeira).

  • Bases de Dados 64

    Restries da relao:

    Restringem os valores permitidos a determinados atributos da relao

    So especificadas como parte da instruo CREATE TABLE

    chaves: PRIMARY KEY

    CREATE TABLE Actor (nome CHAR(30) PRIMARY KEY, ...);

    CREATE TABLE Filme (ttulo CHAR(50), ano NUMBER(4), ...,

    PRIMARY KEY (ttulo, ano) );

    pode proibir-se a existncia de valores nulos para atributos atravs do uso de NOT NULL

    quando so verificadas estas restries?

    Quando se altera a BDs, mas no caso da chave tal s se verifica na insero ou actualiza.

  • Bases de Dados 65

    Restries de Integridade referencial

    Chaves externas: obrigam a que os valores dados chave externa faam sentido, i.e. existam em tuplos da

    tabela referenciada.

    quando a chave externa possui um s atributo podemos usar:REFERENCES ()

    exemplo:CREATE TABLE Participa (

    filme CHAR(50),

    ano NUMBER(4),

    actor CHAR(30) REFERENCES Actor(nome),

    salario DECIMAL(9,2),

    FOREIGN KEY (filme, ano) REFERENCES Filem(titulo,ano)

    );

  • Bases de Dados 66

    Manuteno de Integridade referencial

    a restrio de integridade referencial verificada no fim de cada instruo SQL capaz de a violar, da seguinte forma:

    so proibidas inseres (INSERT) e actualizaes (UPDATE) na tabela referenciante que violem a integridade referencial.

    As eliminaes (DELETE) e actualizaes (UPDATE) na tabela referenciada que violem a integridade referencial so tratadas de acordo com a aco indicada:

    CASCADE - propaga as eliminaes tabela referenciante

    SET NULL - coloca valores nulos nos atributos referenciantes

    SET DEFAULT - coloca valores nulos nos atributos referenciantes

    por omisso: probe essas actualizaes ou eliminaes

    exemplo:CREATE TABLE Estudio (

    nome CHAR(30) PRIMARY KEY, morada VARCHAR(255),

    presidente INT REFERENCES Executivo(#cert)

    ON DELETE Set Null,

    ON UPDATE Cascade );

  • Bases de Dados 67

    Restries sobre atributos

    os valores permitidos para os atributos podem ser restringidos atravs de restries expressas na sua definio

    atravs de restries expressas num domnio usado na sua definio

    CHECK a condio envolve o atributo cujos valores se quer restringir

    mas pode conter qualquer coisa que pode seguir-se a WHERE numa interrogao SQL.

    exemplos:

    sexo CHAR(1) CHECK (sexo IN (F,M)),

    presidente INT CHECK (prsidente IN (SELECT #cert FROM Executivo)),

    restries de domnioCREATE DOMAIN sexoDom CHAR(1)

    CHECK (VALUE IN (F, M) );

    VALUE refere-se a um valor do domnio.

  • Bases de Dados 68

    Asseres

    Permitem verificar condies genricas: qualquer expresso que pode aparecer a seguir a WHERE

    declarao:CREATE ASSERTION CHECK ()

    qualquer alterao BDs que leve a condio a tomar o valor FALSO ser rejeitada a condio tem (obviamente) de dar um valor booleano

    exemplos:CREATE ASSERTION PresidenteRico CHECK

    (NOT EXISTS ( SELECT * FROM Estdio, Executivo

    WHERE presidente= #certificado AND salario< 10000000 ningum pode ser presidente de um estdio a menos que tenha um salrio de 10000000.

    CREATE ASSERTION SomaDurao CHECK

    (10000 >= ALL (SELECT SUM(Durao) FROM Filme GROUP BY Estdio); a durao total de todos os filmes de um dado estdio no deve exceder 10000 minutos

  • Bases de Dados 69

    Triggers (gatilhos) em SQL3

    baseiam-se em regras do tipo evento-condio-aco (ECA)

    a aco pode ser executada antes, depois ou em vez do evento que a dispara

    exemplos:CREATE TRIGGER TriggerSalario

    AFTER UPDATE OF salario ON Executivo

    REFERENCING

    OLD as oldTuple,

    NEW as newTuple

    WHEN (oldTuple.salario > newTuple.salario)

    UPDATE Executivo

    SET salario = oldTuple.salario

    WHERE #certificado = newTuple.#certificado

    FOR EACH ROW

  • Bases de Dados 70

    Triggers (gatilhos) em SQL3

    Execuo da aco: AFTER -- a condio WHEN testada depois do evento disparo

    BEFORE - a condio WHEN testada antes do evento disparo

    INSTEAD OF - a aco executada se a condio WHEN se verifica e o evento disparo nunca executado.

    Eventos disparo: UPDATE, INSERT, DELETE

    FOR EACH ROW indica um gatilho de tuplo

Recommended

View more >