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

  • Published on
    12-Jul-2018

  • View
    212

  • Download
    0

Transcript

Bases de Dados 1Bases 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 2BDs: 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.comBases de Dados 3Bases 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 + SGDBsBases de Dados 4Exemplo de BDsttulo ano durao tipoA Testemunha1987 120 crO Silncio dos Inocentes 1992 130 crnome ttuloFHarrison FordJudy FosterParticipaHar rison FordA Testemunha Guerra das EstrelasActorFilmeO Silncio dos InocentesFilme( ttulo, ano, durao, tipo)Actor( nome, endereo,)Participa( nomeA,ttuloF)nome endereoHarrison Ford 789 Palm Dr, Beverly Hills300 Stars Av., Beverly HillsJudy Foster Quais os filmes em que Harrison Ford participou? Ordene os filmes por ano de estreia.Bases de Dados 5Exemplo de BDscodAluno nomeAluno curso95001 Catarina CC96005 Carlos MATcodDisc nomeDisc creditos depart.BD Bases de Dados 4 CCAL Analise de Dados 4 MAcodDisc sem anoLic anoLec profBD 1 3 1999 F. SilvaIA 2 3 1999 M. FilgueirascodAluno codDisc nota95001 BD 1496005 BD 14AlunosDisciplinasFuncionamentoClassificaes Listar as disciplinas frequentadas e classificaes obtidas pelo aluno Carlos.Bases de Dados 6Desenvolvimento 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 7Caractersticas 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 8Caractersticas 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/99PerformanceBases de Dados 9Caractersticas 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 10Capacidades 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 RecuperaoBases de Dados 11Desenho/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 dadosBases de Dados 12Fases no Desenho de BDsProblemaRequisitos/AnliseRequisitos da BDs1. Fase: entrevistas com potenciais utilizadores da BDs compreender e documentar os seus requisitos produzir um conjunto de requisitos o mais detalhadoe completo possvelDesenho conceptualEsquema conceptual(modelo de Alto-Nvel)2. Fase: definir um esquema conceptual da BDs que incluadescrio das entidades da BD, relacionamentosentre entidades e restries evitar detalhes de implementaoDesenho lgico Independente do SGBDsBases de Dados 13Fases no Desenho de BDs (cont.)3. Fase: implementao da BDs usando um SGBDs comercialEsquema da BDs(especifico de SGBDs)4. Fase: estruturas em memria e organizao dos ficheirosda BDs ficheiros de indicesDesenho lgico(Mapeamento do Modelo de Dados)Dependente do SGBDsDesenho fsicoEsquema internoBases de Dados 14Formalismos 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-objectosBases de Dados 15Modelo 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 entidadestrabalha(empregado, departamento)Bases de Dados 16Modelo 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 17Modelo 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 TipoEMPREGADO(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 EmpresaBases de Dados 20 Identificar entidades-tipo e atributos: 1. DEPARTAMENTO( Nome, Num, {Local}, Gerente, GerData)atributos com valores mltiplos: Local2. 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-EmpresaBases de Dados 21Falta 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 EmpregadoTrabalhaEm( Projecto, Horas) atributo-composto-multivalor da entidade Projecto:Trabalhadores( Empregado, Horas)Construo do modelo ER para a BD-EmpresaBases de Dados 22Falta representar (entre outros):um Departamento tem um Director que o dirige; um Director um empregadoDirige( 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)RelacionamentosBases de Dados 23Com 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 BDsBases 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 relacionamentotrabalhaEm(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-muitostrabalhaPara(Empregado, Departamento) M : N -- muitos-para -muitostrabalhaEm(Empregado, Projecto, Horas) 1 : 1 -- um-para-muitosdirige(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 FracasBases 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/totaldependeDe(Dependente,Empregado) N:1 total/parcialcontrola(Departamento,Projecto) 1:N parcial/totaldirige(Empregado,Departamento, GerData) 1:1 parcial/parcialsupervisiona(Empregado,Empregado) 1:N parcial/parcialSupervisor SupervisionadotrabalhaEm(Empregado,Projecto,Horas) M:N total/totalModelo ER para a BDs sobre uma EmpresaBases de Dados 29 nfase na representao dos esquemas em vez de instncias de entidades e relacionamentos. Notao para os diagramas:Diagramas ERRelacionamento-identificadorRelacionamentoEntidade-FracaEntidade-TipoAtributoAtributo-ChaveAtributo-MultivalorAtributo-DerivadoAtributo-CompostoBases de Dados 30Diagramas ER (cont.)E1 E2RE1 E2RER1 N(min,max)Participao-total de E2em R1:NRestrio-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 totalBases de Dados 31Exemplo 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 paraa direitaEmpregado DepartamentotrabalhaPara(1,1) (4,N)Bases de Dados 32O 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 algumacaracterstica comum e pertencentes mesma entidade-tipo superclasse corresponde entidade-tipo que aglutina os vrios sub-conjuntos de entidades, i.e. subclasses. Ex:EmpregadoTcnicosSecretrias Engenheiros DirectoressubclassessuperclasseBases de Dados 33O 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 isaEmpregadoSecretria isaEmpregadoTcnico isaBases de Dados 34EER: Herana de Atributos As subclasses herdam todos os atributos da sua superclasse uma subclasse com todos os atributos que herda da superclasse, umaentidade-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 subclasseBases de Dados 35EER: 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 outrasentidades-tipo ou outras subclasses Bases de Dados 36Diagrama EERTcnico EngenheiroSecretriaDirectorProjectoSindicatoVelEscritaQualificao EngTipodirigeSalrioEfiliadoddEmpregadoEmpEfectivoEmpPrazoEscaloNumBINome 3 especializaes de EmpregadodSalrioBases de Dados 37EER: 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:VeculoCarro CamioNpassVelMaxTonelagemNeixosdPreoNregMatriculaBases de Dados 38Tipos 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)doBases de Dados 39Tipos 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 40Desenho 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.)RelaesSGBD RelacionalSGBD O-OConceptualizao ImplementaoBases de Dados 41Modelo Relacional Introduzido por Codd (1970) Base de Dados = Conjunto de relaes Relao TabelaFilme Ttulo Ano Durao TipoZorro 1998 cor120Filme (Ttulo, Ano, Durao, Tipo) EsquemaGuerra das Estrelas 124 corMighty Ducks 1991 104 coratributostabelatuplo1977Bases de Dados 42Esquema Relacional de uma BDsEmpregado(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 43Modelo 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 alternativasBases de Dados 44Restries 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 aodepartamento 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 45Interpretao 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 relacionalBases de Dados 46Operao 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 noexistente. 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 47Operao 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 queest 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 48Converso 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 relaoEmpregadoNomePnomeUnomeSexoEBI...Empregado Pnome Unome EBI Sexo ...Bases de Dados 49ER 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 REmpregadoNome...EBI...Dependente EBI Nome Sexo ...DependentedependeDeChave-externaBases de Dados 50ER 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 SEmpregadoDnumDirDataEBI...Departamento Dnome Dnum DirBI DirDataDepartamentodirigeChave-externa(0,1) (1,1)Bases de Dados 51ER 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 SEmpregadoDnumEBI...Empregado Pnome ... EBI ... DNumDepartamentotrabalhaParaChave-externaN 1Bases de Dados 52ER 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 SEmpregadoPnumEBI...trabalhaEm EBI Pnum HorasProjectotrabalhaEmchaves-externasechave-principalM NHorasBases de Dados 53ER 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 SEmpregadoPnumEBI...trabalhaEm EBI Pnum HorasProjectotrabalhaEmchaves-externasechave-principalM NHorasNota: os relacionamentos 1:1 e 1:N tambm podem ser transformados de acordo com o passo 5. Bases de Dados 54ER 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 DlocalEx: um departamento pode tervrias localizaes. DlocalNota: 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 55ER 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 QtdFornecedorFnomePnomePnumProjectoProdutofornecimentoQtdBases de Dados 56Os 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 modelaoBases de Dados 57Exerccio 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 58EER 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, 1Bases de Dados 59Boas 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 60SQL - Structured Query Languagel 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 rpidol 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 61Resumo da sintaxe do SQLCREATE TABLE ( [],...[, ...] )DROP TABLE ALTER TABLE ADD INSERT INTO [(,...)](VALUES (,...,) | )DELETE FROM [WHERE ]UPDATE SET =,... | [WHERE ]CREATE VIEW [(,...)] AS DROP VIEW Bases de Dados 62Resumo da sintaxe do SQL (2)SELECT [DISTINCT] (FROM ( {} | )[WHERE ][GROUP BY [HAVING ] ][ORDER BY [], ...] = (ASC | DESC) = (*|( | (([DISTINCT]Bases de Dados 63Restries 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 64Restries da relao: Restringem os valores permitidos a determinados atributos da relao So especificadas como parte da instruo CREATE TABLE chaves: PRIMARY KEYCREATE 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 65Restries 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 66Manuteno 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 67Restries 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 68Asseres 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, ExecutivoWHERE 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 minutosBases de Dados 69Triggers (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 TriggerSalarioAFTER UPDATE OF salario ON ExecutivoREFERENCINGOLD as oldTuple,NEW as newTupleWHEN (oldTuple.salario > newTuple.salario)UPDATE ExecutivoSET salario = oldTuple.salarioWHERE #certificado = newTuple.#certificadoFOR EACH ROWBases de Dados 70Triggers (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