PROJETO E-JOVEM

APOSTILA BANCO DE DADOS

Projeto E-JOVEM

ndice
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 17. 18. Introduo ............................................................................................................................. 4 Abstrao ............................................................................................................................... 5 Modelo ................................................................................................................................... 7 Ciclo de Vida de um Banco de Dados ............................................................................... 10 Tabelas ................................................................................................................................. 14 Vises ................................................................................................................................... 15 ndice.................................................................................................................................... 17 Linguagem de Banco de Dados.......................................................................................... 17 A linguagem SQL................................................................................................................ 22 A importncia da Modelagem de Dados........................................................................... 30 Modelo de Entidade-Relacionamento (MER) .................................................................. 31 O Modelo Relacional .......................................................................................................... 41 Banco de Dados Relacional ................................................................................................ 43 Modelo ER x Modelo Relacional ................................................................................... 46 Mapeamento do Modelo ER para o Modelo Relacional ................................................. 48 Exerccios Resolvidos ......................................................................................................... 66 Exerccios Propostos ........................................................................................................... 77

Projeto E-JOVEM

1. Introduo Imagine uma situao onde algum escreve em seu caderno apenas o numero 3, voc com certeza iria perguntar, o que significa esse 3? Agora imagine que essa mesma pessoa escreve em se caderno 3 reais, ou 3 cadeiras ou 3 kilos de batatas, dessa vez voc iria conseguir entender o que a pessoa escreveu essa a diferena entre um DADO e uma INFORMAO. Por definio temos que um dado um valor que sozinho no tem sentido algum. J uma informao um DADO contextualizado. Isso faz com que a informao se torne algo muito importante para todos ns, com elas que podemos tomar as mais diversas decises em nossas vidas e tambm um grande alerta para o seguinte problema: Informao Errada = Deciso Errada. Nas organizaes a importncia da informao para a tomada de, baseada nos seus dados gerados, tem impulsionado o desenvolvimento dos sistemas de processamento de informaes. Em tempos atrs o grande problema residia na limitao quantidade e capacidade de processamento de informao que era possvel tratar num computador, hoje em dia a tecnologia permite ultrapassar essas dificuldades. A necessidade das organizaes em possuir um sistema de gesto mais eficaz torna-as cada vez mais dependentes da informao existente e dos mtodos para trat-la. O tempo das enormes e fastidiosas listagens de computador j passou hoje a informao tem de ser compreensvel, completa, fcil e rpida de se obter.

1.1. Banco de Dados e SGBD Bancos de dados, (ou bases de dados), so conjuntos de dados com uma estrutura regular que organizam informao. Um banco de dados normalmente agrupa informaes utilizadas para um mesmo fim. Um banco de dados usualmente mantido e acessado por meio de um software conhecido como Sistema Gerenciador de Banco de Dados (SGBD).

Projeto E-JOVEM

Sistema de Gerenciamento de Banco de Dados So um conjunto de programas que fazem uma gesto autnoma da informao, de acordo com um modelo preestabelecido e adaptado empresa ou qualquer outro rgo que os utilizem. Diferena entre Banco de Dados e Sistema Gerenciador de Banco de Dados? Banco de Dados uma coleo de dados inter-relacionados e logicamente coerente, representando informaes sobre um domnio especfico. SGBD um software com recursos especficos para facilitar o processo de definio, construo, manipulao e o gerenciamento das informaes dos bancos de dados e o desenvolvimento de programas aplicativos. Exemplos de Banco de Dados: Agenda Telefnica Atlas Geogrfico Cadastro de Fornecedores Lista de Pedidos Fichas do acervo de uma biblioteca Exemplos de SGBD: Oracle PostgreSQL SQL Server MySQL HSQLDB Paradox FireBird

2. Abstrao O SGBD deve fornecer ao usurio uma representao conceitual dos dados, sem fornecer muitos detalhes de como as informaes so armazenadas. Um modelo de dados uma abstrao de dados que utilizada para fornecer esta representao conceitual utilizando conceitos lgicos como objetos, suas propriedades e seus relacionamentos. Uma abstrao depende mais do observador do que da realidade observada.

Projeto E-JOVEM

Existem 3 mecanismos de abstrao: Classificao/Instanciao: Consiste na categorizao dos objetos em grupos, com base em algum conjunto de propriedades comuns. A classificao estabelece um relacionamento ( INSTANCIA DE), entre cada elemento e a sua classe. Generalizao/Especializao: Consiste em um relacionamento de subconjunto entre elementos de duas ou mais categorias/conjuntos. Todas as propriedades definidas para a categoria generalizada so herdadas pelas categorias especializadas. Todo elemento de um subconjunto especializado tambm elemento do seu respectivo conjunto generalizado. Essa abstrao estabelece um relacionamento( UM) entre a categoria generalizada e as categorias especializadas. Agregao/Desagregao: Define uma nova categoria a partir de outras categorias. Essa abstrao estabelece um relacionamento ( PARTE DE) entre os componentes e a classe. Abstrao de Dados: Abstrao de dados o uso da abstrao para selecionar as propriedades relevantes de um conjunto de dados. Deve-se analisar o contexto e verificar a pertinncia de cada propriedade e quais propriedades so relevantes para armazenarmos em um banco de dados. Existem 3 nveis de abstrao: Nvel de Viso: consiste no nvel mais alto de abstrao. Descreve partes do BD, de acordo com as necessidades de cada usurio. a forma como os dados so vistos pelo usurio. Nvel Lgico: consiste nos dados que esto armazenados e seus relacionamentos. Neste nvel, o BD descrito atravs de estruturas relativamente simples, que podem envolver estruturas complexas no nvel fsico. Nvel Fsico: consiste no nvel mais baixo de abstrao. Descreve como os dados esto realmente armazenados, englobando estruturas complexas de baixo nvel. Cada nvel de abstrao tem interao e manipulao com o bando de dados de forma especfica.

Projeto E-JOVEM

3. Modelo Modelo uma representao no ambgua que visa ajudar a entender a abstrao de uma realidade. Complexidade Modelar BD envolve gerenciar Complexidades e Riscos. A complexidade e os riscos so proporcionais ao tamanho do BD. Modelos ajudam a minimizar a complexidade e planejar a soluo. Por que usar Modelos? Gerenciamento da Complexidade: Dividir para conquistar + princpio da abstrao. Comunicao entre as pessoas envolvidas: Servem como ponto de referncia comum e no ambguo. Reduo dos custos no desenvolvimento: mais barato corrigir erros no modelo do que na implementao. Predio do comportamento futuro do problemas/solues antes do desenvolvimento. sistema: Permite experimentar

Modelos de Dados Modelo de Dados um tipo de modelo para descrever dados, relaes de dados, semntica de dados e restries de consistncia. Instncia de BD Instncia de BD a coleo dos dados armazenados no BD em um determinado momento. Esquema de BD Esquema de BD a descrio de um BD segundo um modelo de dados. Modelagem de BD Modelagem de BD a atividade de especificao das estruturas de dados e regras de negcio para um esquema de BD.

Projeto E-JOVEM

Projeto de BD Projeto de BD a atividade de modelagem de dados em diversos nveis de abstrao de forma que o BD torne-se eficaz, eficiente e fcil de manter. O projeto de Banco de Dados se d em trs fases: Projeto Conceitual Projeto Lgico Projeto Fsico

Projeto Conceitual: a descrio do BD de maneira independente ao SGBD, ou seja, define quais os dados que aparecero no BD, mas sem se importar com a implementao que se dar ao BD. Desta forma, h uma abstrao em nvel de SGBD. Uma das tcnicas mais utilizadas dentre os profissionais da rea a abordagem entidaderelacionamento (ER), onde o modelo representado graficamente atravs do diagrama entidade-relacionamento (DER).

Projeto Lgico: Descreve o BD no nvel do SGBD, ou seja, depende do tipo particular de SGBD que ser usado. No podemos confundir com o Software que ser usado. O tipo de SGBD que o modelo lgico trata se o mesmo relacional, orientado a objetos, hierrquico, etc. Abordaremos o SGBD relacional, por serem os mais difundidos. Nele, os dados so organizados em tabelas. Aluno(mat_aluno, nome, endereco) Turma (cod_turma, sala, periodo) Obs: importante salientar que os detalhes internos de armazenamento, por exemplo, no so descritos no modelo lgico, pois estas informaes fazem parte do modelo fsico, que nada mais que a traduo do modelo lgico para a linguagem do software escolhido para implementar o sistema.

Projeto E-JOVEM

Projeto Fsico Inicia com Esquema Lgico e resulta no Esquema Fsico. uma descrio da implementao do Esquema Lgico segundo as estruturas de armazenamento e mtodos de acesso do SGBD, cujo o objetivo otimizar a manipulao dos dados. Esquema Fsico: uma descrio da estrutura do BD segundo a Linguagem de Definio de Dados (LDD) do SGBD alvo, que especificado segundo um Modelo Fsico Ex: LDD do Oracle.

Projeto E-JOVEM

4. Ciclo de Vida de um Banco de Dados Ciclo de Vida de BD o conjunto de fases que compreende desde a concepo at a manuteno e evoluo do BD.

1 Fase: Estudo Inicial do Banco de Dados Propsito Geral: Analisar situao da companhia: o Qual o ambiente geral da organizao e qual sua misso dentro deste ambiente? o Qual a estrutura da organizao? Definir problemas e restries: o o o o o Como funcionam os sistemas existentes? O que o sistema requer como entrada? O que o sistema gera como sada? Quais so as relaes operacionais entre as unidades de negcio? Quais so os limites e restries impostos sobre o sistema?

10

Projeto E-JOVEM

Definir objetivos o Qual o objetivo inicial do sistema proposto? o O sistema ir fazer interface com outro sistema? o O sistema deve compartilhar dados com outros sistemas?

Definir escopo e limites o Escopo Qual a extenso do projeto? o Limite Quais so as limitaes? De oramento; De hardware; De software.

2 Fase: Projeto do Banco de Dados a fase mais importante e apresenta as seguintes subfases. Projeto Conceitual; Seleo do SGBD; Projeto Lgico; Projeto Fsico.

3 Fase: Implementao e Carga Criao e insero de dados nas tabelas e outras questes importantes de implementao: Performance; Segurana; Backup e recovery; Integridade;

4 Fase: Teste e Avaliao O BD testado e aproveita-se para fazer ajustes finos de performance, integridade, segurana etc. Esta fase pode acontecer em paralelo com a programao da aplicao. Algumas aes so realizadas quando os testes falham: Ajustes Finos conforme manuais de referncias; Modificao do projeto fsico; Modificao do projeto lgico; Atualizao ou mudana de SGBD, de Hardware ou de Software.

11

Projeto E-JOVEM

5 Fase: Operao Nesta fase o BD considerado operacional. A partir desta fase, inicia-se o processo de evoluo do BD. Onde alguns problemas inesperados podem acontecer. E tambm a demanda por mudanas constante. 6 Fase: Manuteno e Evoluo Nesta fase voc deve realizar as seguintes aes: Manuteno preventiva; Manuteno corretiva; Manuteno adaptativa; Atribuio de novas permisses de acesso; Gerao de estatsticas de acesso ao BD para monitorar a performance; Auditoria peridica da segurana do BD; Resumos peridicos sobre o uso do sistema;

Quando criamos uma base de dados temos os seguintes objetivos: Diminuir o espao ocupado pela informao; Facilitar a atualizao da informao; Aumentar a velocidade de pesquisa; Evitar a redundncia de informao.

Quando no utilizar um SGBD: Em algumas situaes, o uso de um SGBD pode representar uma carga desnecessria aos custos quando comparado abordagem processamento tradicional de arquivos. Por exemplo: Alto investimento inicial na compra de software e hardware adicionais; Generalidade que um SGBD fornece na definio e processamento de dados; Sobrecarga na proviso de controle de segurana, controle de concorrncia, recuperao e integrao de funes.

Problemas adicionais podem surgir caso os projetistas de banco de dados ou os administradores de banco de dados no elaborem os projetos corretamente ou se as aplicaes no so implementadas de forma apropriada. Se o DBA no administrar o banco de dados de forma apropriada, tanto a segurana quanto a integridade dos sistemas podem ser comprometidas.

12

Projeto E-JOVEM

A sobrecarga causada pelo uso de um SGBD e a m administrao justificam a utilizao da abordagem processamento tradicional de arquivos em casos como: O banco de dados e as aplicaes so simples, bem definidas e no se espera mudanas no projeto; A necessidade de processamento em tempo real de certas aplicaes, que so terrivelmente prejudicadas pela sobrecarga causada pelo uso de um SGBD; No haver mltiplo acesso ao banco de dados.

Vantagens na utilizao de BDs Os benefcios aqui abordados dividem-se em trs categorias principais, nomeadamente: Benefcios de centralizao de dados: Reduo/Eliminao de redundncia de dados: evitar a repetio de informao desnecessria, reduzindo tambm o espao ocupado pela base de dados; Melhoria na concorrncia de dados: aumentar a eficincia no acesso aos dados; Obteno de informao antecipadamente: Aceder e obter informao de forma mais rpida e eficaz; Simplificao da infra-estrutura de informao: permitir uma estruturao e organizao da informao de forma mais simples permitindo, deste modo, alcanar os pontos referidos acima.

Benefcios resultantes de uma melhor gesto de dados: Organizao e controlo dos dados: a simplificao da prpria estrutura da base de dados implica benefcios na organizao dos dados o que uma mais valia para a gesto e controlo dos dados; Recuperao, backup e rasteio de dados: por vezes pode acontecer que se percam dados relevantes para a base de dados. Nestes casos importante que haja mecanismos de recuperao dos mesmos, de forma a ser possvel o seu restauro. importante, tambm, que a base de dados permita a execuo de backups, isto , de cpias de segurana da informao armazenada; Simplificao e aperfeioamento da segurana: a segurana crucial nos dias de hoje. No s para prevenir ataques externos base de dados, mas tambm como forma de restringir o acesso aos dados por parte de utilizadores sem privilgios para tal; Melhor integridade dos dados: apresentar os dados com rigor e qualidade.

13

Projeto E-JOVEM

Vantagens na utilizao de BDs: Melhoria de performance atravs de: Chamadas (calls) de aplicaes: permitem que outras aplicaes, que no aquela que gere a base de dados, aceda aos dados e os utilizem para obter determinados resultados; Mecanismos de recuperao (unit recovery mechanism, URM): so mecanismos que permitem a qualquer momento restaurar informao perdida pela base de dados.

Componentes de um Banco de Dados Um Banco de Dados composto pelas seguintes partes: Gerenciador de Acesso ao Disco: O SGBD utiliza o Sistema Operacional para acessar os dados armazenados em disco, controlando o acesso concorrente s tabelas do Banco de Dados. O Gerenciador controla todas as pesquisas queries) solicitadas pelos usurios no modo interativo, os acessos do compilador DML, os acessos feitos pelo Processador do Banco de Dados ao Dicionrio de Dados e tambm aos prprios dados. O Dicionrio de Dados contm o esquema do Banco de Dados, suas tabelas, ndices, forma de acesso e relacionamentos existentes. O Compilador DDL (Data Definition Language) processa as definies do esquema do Banco de Dados, acessando quando necessrio o Dicionrio de Dados do Banco de Dados. O Processador do Banco de Dados manipula requisies prpria Base de Dados em tempo de execuo. o responsvel pelas atualizaes e integridade da Base de Dados. O Processador de Pesquisas (queries) dos usurios, analisa as solicitaes, e se estas forem consistentes, aciona o Processador do Banco de Dados para acesso efetivo aos dados. As aplicaes fazem seus acessos ao pr-compilador DML da linguagem hospedeira, que os envia ao Compilador DML (Data Manipulation Language) onde so gerados os cdigos de acesso ao Banco de Dados. 5. Tabelas Tabelas em banco de dados so objetos criados para armazenar os dados fisicamente, onde os dados so armazenados em linhas (registros) e colunas (campos). Os dados de uma tabela normalmente descrevem um assunto tal como clientes, vendas etc.

14

Projeto E-JOVEM

6. Vises Consistem em tabelas do banco de dados que no armazenam dados, sua principal est no aumento de segurana por propiciar uma viso limitada e controlada dos dados que podem ser obtidos da base (a depender do SGBD utilizado) e a performance por utilizar uma consulta previamente otimizada, tornando desnecessrio este processo quando ela realizada. As vises apresentam os seguintes tipos: Viso idntica:

Viso por seleo de colunas:

15

Projeto E-JOVEM

Viso por seleo de linhas:

Viso por seleo de colunas:

Viso por juno de tabelas:

16

Projeto E-JOVEM

7. ndice uma ferramenta usada pelo gerenciador de Banco de Dados para facilitar a busca de linhas dentro de uma tabela. E apresenta os seguintes tipos: ndice nico: ndice criado a partir da chave primria, no permite a incluso de linhas duplicadas.

ndice de Performance: Facilita a busca de linhas na tabela

8. Linguagem de Banco de Dados Para podermos inserir, apagar, atualizar dados em uma base de dados, precisamos de uma linguagem de banco de dados. A linguagem que veremos aqui ser a de Consulta Estruturada (Structured Query Language) mais conhecida como SQL. Constituem subconjunto da linguagem, a Linguagem de Definio de Dados (Data Definition Language - DDL) e a Linguagem de Manipulao de Dados (Data Manipulation Language DML). SQL: Structured Query Language que no mais do que uma linguagem padro de comunicao com base de dados. Falamos, portanto, de uma linguagem normalizada que nos permite trabalhar com qualquer tipo de linguagem (ASP ou PHP) em combinao com qualquer tipo de base de dados (MS Access, Oracle, MySQL etc). DDL: o conjunto de comandos SQL responsveis pela definio dos dados, ou seja, pela criao de bancos, esquemas, tabelas, campos, tipos de dados, restries etc. utilizada pelo DBA e projetistas de banco de dados para definir seus esquemas. Veja abaixo os exemplo de DDL:
Criar Banco de Dados Sintaxe: CREATE DATABASE <nome_do_banco_de_dados>; Exemplo: CREATE DATABASE locadora;

17

Projeto E-JOVEM

Exclui Banco de Dados Sintaxe: DROP DATABASE <nome_do_banco_de_dados>; Exemplo: DROP DATABASE locadora; Criar Tabela Sintaxe: CREATE TABLE <nome_tabela> ( <nome_coluna_1> <tipo_dado> <restricao>, <nome_coluna_2> <tipo_dado> <restricao>, <nome_coluna_n> <tipo_dado> <restricao>, PRIMARY KEY (nome(s)_atributo(s)), FOREIGN KEY (nome_atributo) REFERENCES <nome_tabela> ); Alterar Tabela Para modificar a estrutura de uma tabela aps esta ter sido criada use a instruo ALTER TABLE. Sua sintaxe : ALTER TABLE <nome_tabela> alterao1, alterao2, ... , alteraoN; Renomear Tabela O seguinte commando tambm pode ser usado para renomear uma tabela: RENAME TABLE nome_tabela TO novo_nome_tabela [,nome_tabela2 TO novo_nome_tabela2] [,nome_tabelaN TO novo_nome_tabelaN];

Excluir Tabela Para excluir tabelas use o comando: DROP TABLE <nome_tabela> [, <nome_tabela2>,...]; DROP TABLE remove uma ou mais tabelas. Todos os dados e definies de tabela so removidos, assim tenha cuidado com este comando!

Criar ndice Sintaxe: CREATE INDEX <nome_indice> ON <nome_tabela> (<index_col_name>,...) <index_col_name>: <col_name> [ASC | DESC]

18

Projeto E-JOVEM

Uma especificao <index_col_name> pode finalizar com ASC ou DESC. Estas palavras chaves so permitidas para extenso futura para especificar o armazenamento do valor do ndice em crescente ou decrescente. Atualmente elas so analisadas, mas ignoradas; valores de ndice so sempre armazenados em ordem crescente. Excluir ndice Sintaxe: DROP INDEX <nome_indice> ON <nome_tabela>; DROP INDEX apaga o ndice chamado nome_indice da tabela nome_tabela. DROP INDEX pode no fazer nada em algumas verses do MySQL. Dependendo da verso o DROP INDEX mapeado em uma instruo ALTER TABLE para apagar o ndice. Criar View Sintaxe: CREATE [OR REPLACE] VIEW <nome_view> AS <SELECT statement> [<check options>]; Excluir View Sintaxe: DROP VIEW [IF EXISTS] <nome_view> [, <nome_view2>,];

DML: o conjunto de comandos SQL responsveis pela manipulao dos dados, como: inserir, consultar, atualizar e excluir. Obs: Atente para que suas consultas sejam: simples e claras; contenham somente campos estritamente necessrios; sejam otimizadas para o desempenho mximo.
Pesquisando no banco de dados A pesquisa no banco de dados feita com a instruo SELECT. Sua sintaxe bsica : SELECT <campos> FROM <nome_tabela> WHERE <condio> ORDER BY <campo>;

Especificando condies para a pesquisa: WHERE A clusula WHERE especifica a condio da pesquisa. Abaixo segue uma tabela com os operadores mais usados com WHERE.

19

Projeto E-JOVEM

OPERADOR = > < <= != OU < >

EXEMPLO nrcliente = 154 quantidade > 100 quantidade < 100 quantidade <= 100 quantidade != 0

DESCRIO Testa se dois valores so iguais. Testa se um valor maior que outro. Testa se um valor menor que outro. Testa se um valor menor ou igual a outro. Testa se dois valores so diferentes. Testa se um campo no est vazio. Testa se um campo est vazio (nulo, ou seja, nada foi inserido nele, nem um espao em branco). Testa se um valor est entre um valor mnimo (inclusive) e um valor mximo (inclusive).

IS NOT NULL endereco IS NOT NULL IS NULL endereco IS NULL

BETWEEN IN NOT IN LIKE

quantidade BETWEEN 0 AND 100

cidade IN Testa se um valor pertence a um conjunto. ("Recife","Olinda","Caruaru") cidade NOT IN Testa se um valor no pertence a um conjunto. ("Recife","Olinda","Caruaru") nome LIKE ("Samuel%") Testa se um valor corresponde a um padro (% equivale ao conhecido *, o curinga). Testa se um valor no corresponde a um padro ( _ equivale ao curinga que corresponde a um caracter). Testa se um valor corresponde a uma expresso regular.

NOT LIKE

nome NOT LIKE ("Samuel_")

REGEXP

nome REGEXP

Especificando mais de uma condio para a pesquisa

Para especificar mais de uma condio para a pesquisa use as palavras and e or: Se quiser selecionar registros que atendam a CONDIO1 e a CONDIO2, faa assim:
SELECT <campos> FROM <tabela> WHERE <CONDIO1> AND <CONDIO2>; Se quiser selecionar registros que atendam a CONDIO1 ou a CONDIO2, faa assim: SELECT <campos> FROM <tabela> WHERE <CONDIO1> OR <CONDIO2>;

20

Projeto E-JOVEM

Exibindo todos os registros de uma tabela Caso queira exibir todos os registros, voc pode usar o "*" (asterisco):

Inserindo dados I

Para inserir dados numa tabela use o comando:

INSERT INTO TABELA (campo1, campo2, ... , campon) VALUES (valor1, valor2, ... , valorn); Inserindo dados II

Outra sintaxe utilizada para inserir dados :

INSERT INTO TABELA SET campo1=valor1, campo2=valor2, ..., campon=valorn; Atualizando registros

Para atualizar registros use a instruo UPDATE. Sua sintaxe :

UPDATE <tabela> SET <coluna1 = valor1>,<coluna2 = valor2>,<...> WHERE <condio> LIMIT <nr1>, <nr2>;

21

Projeto E-JOVEM

9. A linguagem SQL Fundamentada no modelo relacional, inclui comandos para: Definio de dados; Atualizao; Consulta. Vantagens de SQL Independncia de fabricante. Portabilidade entre sistemas. Reduo de custos com treinamento. Comandos em ingls. Consulta interativa. Mltiplas vises de dados. Manipulao dinmica dos dados. Desvantagens de SQL A padronizao inibe a criatividade, fazendo com que a linguagem fique longe de ser a linguagem relacional ideal. Algumas crticas: Falta de ortogonalidade nas expresses; Discordncia com as linguagens hospedeiras no d suporte a alguns aspectos do modelo relacional. Enfoques de SQL Linguagem interativa de consulta (ad-hoc): usurios podem definir consultas independente de programas. Linguagem de programao para acesso a banco de dados: comandos SQL embutidos em programas de aplicao. Linguagem de administrao de dados: o DBA pode utilizar SQL para realizar suas tarefas. Linguagem cliente/servidor: os programas clientes usam comandos SQL para se comunicarem e compartilharem dados com o servidor. Linguagem para banco de dados distribudos: auxilia na distribuio de dados por vrios ns e na comunicao de dados com outros sistemas.

22

Projeto E-JOVEM

Caminho de acesso a outros bancos de dados em diferentes mquinas: auxilia na converso entre diferentes produtos em diferentes mquinas.

9.1. Extrao de Dados de uma Tabela Comando SELECT: selecionando atributos (Projeo). SELECT <lista de atributos> FROM <tabela> Exemplos: Listar nome e salrio de todos os empregados: SELECT Nome, Salrio FROM Empregado; Listar o contedo de Empregado: SELECT * FROM Empregado; O * selecionando todos os atributos. 9.1.1. Select e Condies Selecionando tuplas da tabela: SELECT <lista de atributos> FROM <tabela> WHERE <condio>

Onde condio : <nome atributo> <operador> <valor>

Operadores Relacionais: =, !=, <, <=, >, >=. Operadores Lgicos: AND, OR e NOT.

23

Projeto E-JOVEM

Selecionando tuplas da tabela. Exemplos: Listar nome e sexo dos empregados do departamento 15: SELECT Nome, Sexo FROM Empregado WHERE Num-Dep = 15; Listar nome e sexo dos empregados do departamento 15 com salrio > R$ 1.000,00: SELECT Nome, Sexo FROM Empregado WHERE Num-Dep = 15 AND Salrio > 1000;

9.1.2. Operadores BETWEEN e NOT BETWEEN Esses operadores substituem o uso dos operadores <= e >=: ... WHERE <nome atributo> BETWEEN <valor1> and <valor2> Exemplo: Listar os empregados com salrio entre R$ 1.000,00 e R$ 2.000,00 SELECT * FROM Empregado WHERE Salrio BETWEEN 1000 and 2000; Observao: A palavra between vem do ingls e significa entre. 9.1.3. Operadores LIKE e NOT LIKE Esses operadores s se aplicam sobre atributos do tipo char. Operam como = (igual) e != (diferente), utilizando os smbolos: % (substitui uma palavra) e _ (substitui um caracter): ...WHERE <nome atributo> LIKE <valor1> Exemplo: Listar os empregados que tem como primeiro nome Jos: SELECT Nome FROM Empregado WHERE Nome LIKE Jos%; 9.1.4. Operadores IN e NOT IN Os operadores IN e NOT IN procuram dados que esto ou no contidos em um dado conjunto de valores: ... WHERE <nome atributo> IN <valores> Exemplo: Listar o nome e data de nascimento dos dependentes com grau de parentesco M ou P: SELECT Nome, Data-Nasc FROM Dependentes WHERE Grau-P IN (M, P);

24

Projeto E-JOVEM

9.1.5. Operadores IS NULL e IS NOT NULL Os operadores IS NULL e IS NOT NULL identificam valores nulos dos atributos: ... WHERE <nome atributo> IS NULL Exemplo: Listar os projetos que no tenham local definido: SELECT * FROM Projeto WHERE Local IS NULL; 9.1.6. Ordenando os Dados Para ordenar (ORDER BY) os dados que foram resultados de selects feitos no banco utilizamos a seguinte sintaxe: SELECT <lista atributos> FROM <tabela> [WHERE <condio>] ORDER BY <Nome atributo> {ASC | DESC} Observao: ASC: Ordem ascendente. DESC: Ordem descendente. Exemplos: Listar todos os empregados ordenados ascendentemente por nome: SELECT * FROM Empregado ORDER BY Nome; Listar todos os empregados ordenados descendentemente por salrio: SELECT * FROM Empregado ORDER BY Salrio DESC; 9.1.7. Realizando clculo com informao selecionada Pode-se criar um campo que no pertena tabela a partir de clculos sobre atributos da tabela.

25

Projeto E-JOVEM

Exemplo: Mostrar o novo salrio dos empregados calculado com base no reajuste de 60% para os que ganham abaixo de R$ 1.000,00: SELECT Nome, Novo-salrio = (Salrio * 1.60) FROM Empregado WHERE Salrio < 1000; 9.1.8. Funes sobre conjuntos Para realizarmos operaes sobre conjuntos temos os seguintes comandos: MAX, MIN, SUM, AVG, COUNT. Exemplos: Mostrar o valor do maior salrio dos empregados: SELECT MAX (Salrio) FROM Empregado; Mostrar qual o salrio mdio dos empregados: SELECT AVG (Salrio) FROM Empregado; Quantos empregados ganham mais de R$1.000,00? SELECT COUNT (*) FROM Empregado WHERE Salrio > 1000; 9.1.9. Clusula DISTINCT A clusula DISTINCT elimina tuplas duplicadas do resultado de uma consulta. Exemplo: Quais os diferentes salrios dos empregados? SELECT DISTINCT Salrio FROM Empregado; 9.1.10. GROUP BY Para agrupar informaes selecionadas usamos: GROUP BY. Ele organiza a seleo de dados em grupos.

26

Projeto E-JOVEM

Exemplo: Listar os empregados por sexo: SELECT Nome, Sexo FROM Empregado GROUP BY Sexo; 9.1.11. HAVING HAVING agrupa informaes de forma condicional. Seleciona entre as tuplas resultantes, as que satisfazem uma dada condio.

Exemplo: Listar o nmero total de empregados que recebem salrios superiores a R$1.000,00, agrupados por departamento, mas s daqueles com mais de 5 empregados:

SELECT Num-Dep, COUNT (*) FROM Empregado WHERE Salrio > 1000 GROUP BY Num-Dep HAVING COUNT(*) > 5; 9.1.12. JOIN JOIN recupera dados de vrias tabelas. Cita as tabelas envolvidas na clusula FROM. Utiliza qualificadores de nomes para referenciar o nome do empregado, Empregado.Nome. Exemplos: Listar o nome do empregado e do departamento onde est alocado: SELECT Empregado.Nome, Departamento.Nome FROM Empregado,

Departamento WHERE Empregado.Num-Dep = Departamento.Numero; Listar os nomes dos departamentos que tm projetos: SELECT Departamento.Nome FROM Departamento, Projeto WHERE Projeto.Num-Dep = Departamento.Numero; Observao: Podemos utilizar as clusulas (NOT) LIKE, (NOT) IN, IS (NOT) NULL misturadas aos operadores AND, OR e NOT nas equaes de juno.

27

Projeto E-JOVEM

Exemplo: Listar os departamentos que tm projetos com nmero superior a 99 e localizados em RJ ou SP ordenados por nome de departamento: SELECT Departamento.Nome FROM Departamento, Projeto WHERE

Projeto.Local IN (RJ, SP) AND Projeto.Nmero > 99 AND Projeto.Num-Dep = Departamento.Codigo ORDER BY Departamento.Nome;

9.1.13. Juntando mais de duas tabelas Observe abaixo um exemplo onde fazemos a juno de mais de duas tabelas. Exemplo: Listar o nome dos empregados, com seu respectivo departamento que trabalhem mais de 20 horas em algum projeto: SELECT Empregado.Nome, Departamento.Nome FROM Empregado,

Departamento, Trabalha-em WHERE Trabalha-em.Horas > 20 AND Trabalha-em.Cad-Emp = Empregado.Cad AND Empregado.Num-Dep = Departamento.Nmero; 9.1.14. Utilizando consultas encadeadas O resultado de uma consulta utilizado por outra consulta, de forma encadeada e no mesmo comando SQL. Exemplo: Listar os departamentos que tenham qualquer projeto em RJ: SELECT Departamento.Nome FROM Departamento WHERE

Departamento.Nmero IN (SELECT Projeto.Num-Dep FROM Projeto WHERE Projeto.Local = RJ);

28

Projeto E-JOVEM

9.1.15. Adicionando tupla tabela Como j vimos em aulas passadas, para adicionar tupla tabela usamos o comando INSERT. Veja a sintaxe: INSERT INTO <tabela> (<lista de atributos>) VALUES (<valores>); Exemplo: INSERT INTO Empregado(Cad, Nome, Sexo, Salrio, Num_Dep, Cad_Supv) VALUES (015, Jos da Silva, M, 1000, 1, 020); 9.1.16. Adicionando tuplas usando SELECT Para adicionar tuplas tabela usando SELECT usamos a seguinte sintaxe: INSERT INTO <tabela> (<lista de atributos>) SELECT <lista de atributos> FROM <tabela> WHERE <condio>; Exemplo:
INSERT INTO depto-info (nome-depto, numemp, total-sal) SELECT D.nome, COUNT(*), SUM (E.salario) FROM Departamento D, Empregado E WHERE D.numero = E.Num-Dep GROUP BY D.nome HAVING COUNT (*) > 50;

9.1.17. Criando Vises (VIEW) So tabelas virtuais que no ocupam espao fsico. Exemplo: Criar uma viso dos empregados do departamento 10 que tenham mais de 20 horas de trabalho em projetos: CREATE VIEW Dep-10 AS SELECT Empregado.Nome, Trabalha-em.Num-Proj FROM Empregado, Trabalha-em WHERE Trabalha-em.Horas > 20 AND Trabalhaem.Cad-Emp = Empregado.Cad AND Empregado.Num-Dep = 10; 9.1.18. Privilgios Para garantir privilgios de acesso usamos GRANT e para revogar privilgios usamos REVOKE: GRANT <privilgios> ON <nome tabela/view> TO <usurio> REVOKE <privilgios> ON <nome tabela/view> FROM <usurio> Onde <privilgios>: SELECT, INSERT, DELETE, UPDATE, ALL PRIVILEGES e

29

Projeto E-JOVEM

<usurio>: usurio cadastrado, PUBLIC Exemplos: Garante privilgios ao empregado Carlos para realizar selects no banco: GRANT SELECT ON Empregado TO carlos;

Revoga os privilgios de selects na tabela Projeto para todos os usurios que no tem privilgios de um grupo especfico: REVOKE SELECT ON Projeto FROM PUBLIC; 10. A importncia da Modelagem de Dados Para desenvolvermos aplicaes que usam banco de dados deveremos possuir os conceitos bsicos sobre modelagem de dados. No importa se sua aplicao muito simples; a correta modelagem dos seus dados ir com certeza tornar sua aplicao mais robusta e mais fcil de manter. Alguns motivos que tornam a modelagem de dados fundamental: o projeto conceitual da base de dados; o alicerce da construo do sistema de informao; Representa o ambiente observado; Fornece processos de validao; Observa processos de relacionamentos entre objetos; a documentao do projeto das bases de dados.

Com isso evitamos: A redundncia de dados; A baixa performance do acesso aos dados (ndices e chaves mal elaborados); O mau funcionamento da programao x acesso aos dados.

E facilitaremos: A manuteno das bases (novas tabelas e campos); Insero de novos negcios (customizaes);

30

Projeto E-JOVEM

11. Modelo de Entidade-Relacionamento (MER) O Modelo de Entidade-Relacionamento (MER) foi desenvolvido para auxiliar o projeto de banco de dados, atravs da especificao de um esquema que define a organizao da base de dados. Por definio temos, Modelo baseado na percepo do mundo real, que consiste em um conjunto de objetos bsicos chamados entidades e nos relacionamentos entre esses objetos. 11.1. Entidade e Atributos

Entidade pode ser definida como um objeto do mundo real, concreto ou abstrato e que possui existncia independente. Cada entidade possui um conjunto particular de propriedades que a descreve chamado atributos. Um atributo pode ser dividido em diversas sub-partes com significado independente entre si, recebendo o nome de atributo composto. Um atributo que no pode ser subdividido chamado de atributo simples ou atmico. Os atributos que podem assumir apenas um determinado valor em uma determinada instncia so denominados atributo simplesmente valorado, enquanto que um atributo que pode assumir diversos valores em uma mesma instncia denominado multi valorado. Um atributo que gerado a partir de outro atributo chamado de atributo derivado. Entre os diversos atributos que definem uma entidade deve existir um ou mais campos que identifiquem inequivocamente cada registro. A este(s) atributo(s) d-se o nome de Atributo Identificador. Chave Primria um atributo identificador que representa univocamente cada ocorrncia ou registro de uma tabela. Existem dois tipos de chave primria: Simples - constituda apenas por um atributo; Composta - constituda por dois ou mais atributos.

31

Projeto E-JOVEM

Propriedades da Chave primria Uma chave primria deve ser: Unvoca O valor da chave primria deve ser nico para todos os registros. No Redundante No caso de uma chave composta no devem ser includos mais campos do que os necessrios. No Nula Nenhum dos valores que compem a chave primria pode conter valores nulos.

Chave Estrangeira ou Externa um atributo que definido como chave primria de uma tabela includo na estrutura de uma outra tabela. Exemplo: Consideremos as entidades Cds e Faixas, que identificam um Cd e as suas respectivas Faixas. CDs (CdigoCD, Ttulo, Intrprete, Gravadora) Faixas (CdigoCd, CdigoFaixa, Ttulo, Durao) O atributo CdigoCd da entidade Faixas faz parte da sua chave primria, no entanto como chave primria da entidade CDs considerado uma chave estrangeira na entidade Faixas. Observao: Os atributos chaves, nessa forma de representao da entidade Faixas, so os atributos sublinhados. Exemplo:

32

Projeto E-JOVEM

Relacionamento Conjunto de associaes do mesmo tipo entre ocorrncias de entidades. Exemplo: Todos os clientes de um banco tm contas = Relacionamento Tem Conjunto de associaes entre ocorrncias de entidades sobre as quais se deseja manter informaes no BD. Relacionamento x Instncia de Relacionamento: Para referir um relacionamento particular fala-se em instncia ou ocorrncia de relacionamento. Tem autores que consideram uma associao especfica como um relacionamento e o conjunto de associaes como conjunto de relacionamentos. Relacionamento e a Chave O relacionamento entre entidades um dos propsitos das bases de dados relacionais, da a importncia dada seleo da chave primria (vista na aula anterior), pois atravs destas que so estabelecidas as associaes entre as diferentes entidades. Exemplos:

Tipos de Relacionamentos So as formas como as entidades se relacionam num determinado modelo de informao. As associaes podem classificar-se em: unrias, binrias e complexas. Unrio e Binrio Relacionamento unrio associa uma entidade com ela prpria.

33

Projeto E-JOVEM

Neste caso, uma equipe joga com outra equipe. Relacionamento binrio associa duas entidades.

Neste tipo de relacionamento, um aluno pertence a uma turma. Complexos Relacionamento complexo associa mais do que duas entidades.

Tem-se que um atleta que pratica uma determinada modalidade disputa uma prova dessa modalidade. Observao: Regra geral, uma associao do tipo complexa ternria obrigatria entre as trs entidades. Cardinalidade Determina a quantidade (mnima e mxima) de ocorrncias de relacionamentos que uma instncia de entidade pode ter com outras instncias de entidades. Exemplo:

34

Projeto E-JOVEM

Cardinalidade Mnima de um Relacionamento Determina a quantidade mnima de ocorrncias de relacionamentos que uma instncia de entidade pode ter com outras instncias de entidades. Indica se a participao das ocorrncias de entidades no relacionamento obrigatria ou opcional. Valores vlidos: Min = 0 relacionamento opcional ou parcial Min > 0 relacionamento obrigatrio ou total Min Max Para efeito prtico apenas duas cardinalidades mnimas so relevantes, 0 (zero) e 1 (um). Cardinalidade Mxima de um Relacionamento Determina a quantidade mxima de ocorrncias de relacionamentos que uma instncia de entidade pode ter com outras instncias de entidades. Valores vlidos: Max > 0 Max Min Para efeito prtico apenas duas cardinalidades mximas so relevantes, 1 e n. Vrios Relacionamentos s vezes, pode existir mais de um relacionamento entre as mesmas entidades:

Observao: Pode-se nomear relacionamentos a partir dos nomes das entidades envolvidas. Exemplo: cliente_conta

35

Projeto E-JOVEM

Atributos do Relacionamento Quando um determinado relacionamento possui atributos, tambm conhecido como relacionamento valorado. Esta situao ocorre apenas em relacionamento N:N. Ex. Pedro trabalha no projeto Alfa 30 horas. Pedro - Elemento do conjunto de valores do atributo Nome da entidade Funcionrio. Alfa - Elemento do conjunto de valores do atributo Nome do Projeto da entidade Projeto. trabalha - Ligao existente entre um funcionrio e um projeto. Neste caso, este funcionrio trabalha 30 horas neste projeto, porm este mesmo funcionrio poder trabalhar outro nmero de horas em outro projeto, assim como outro funcionrio trabalha outro nmero de horas no mesmo projeto Alfa. Podemos concluir que 30 horas o atributo que pertence ao Pedro no projeto Alfa.

Relacionamento Identificador (Entidade Fraca) A entidade no tem atributos suficientes para formar seu identificador, da usa o identificador da entidade Forte para formar o seu. A entidade Dependente s existe quando est relacionada a outra entidade, ou seja, se a entidade Cliente for eliminada do banco a entidade tambm ser eliminada.

Relacionamento Identificador (Recurso) O termo Entidade Fraca deve ser usado com cautela, pois uma entidade fraca em um relacionamento no necessariamente tambm fraca em outro relacionamento, observe:

36

Projeto E-JOVEM

Auto-Relacionamento Representa uma associao entre ocorrncias de uma mesma entidade. Exige a identificao de papis.

Um Cliente pode ser patrocinador de vrios de clientes. Um Cliente s pode ser patrocinado por um Cliente.

Grau de relacionamento Corresponde ao o nmero de entidades, no necessariamente distintas, que participam de um relacionamento. Uma entidade pode ser tratada como um relacionamento de grau zero para efeito de comparao com outros relacionamentos. Tipos de Grau de Relacionamento Binrio: Uma ocorrncia do relacionamento deve envolver simultaneamente duas instncias de entidades Ternrio: Uma ocorrncia do relacionamento deve envolver simultaneamente trs instncias de entidades. No pode relacionar trs entidades em um momento e duas em outro.

37

Projeto E-JOVEM

Relacionamento Ternrio Cada ocorrncia de Possui relaciona 3 ocorrncias de entidade: Cliente, Produto e Conta!

Relacionamento Ternrio - Cardinalidade Pense na cardinalidade que prevista para a modelagem abaixo?

Ateno: A cardinalidade refere-se ao par das demais entidades. Entidade Associativa (ou Agregao) Substitui a associao entre relacionamentos, a qual no prevista pelo Modelo ER. um relacionamento que passa a ser tratado como entidade. Permite o uso de relacionamento opcional!

38

Projeto E-JOVEM

Generalizao/Especializao Permite atribuir propriedades particulares a um subconjunto de entidades especializadas. Permite a herana de propriedades (atributos): Agrega ao seu conjunto de propriedades as propriedades da entidade genrica.

Especializar

Tipos de Generalizao/Especializao Abaixo so descritas 4 tipos de generalizao/especializao que podem ocorrer entre entidades: 1. Total: Todas as entidades especializadas tm que estar relacionada entidade genrica. 2. Parcial: Pelo menos uma entidade genrica no est relacionada entidade especializada. 3. Exclusiva: A entidade genrica est associada a uma nica entidade especializada. 4. No exclusiva: A entidade genrica est associada a duas ou mais entidades especializadas.

Generalizar

39

Projeto E-JOVEM

Outros tipos de notao grfica A notao grfica mostrada abaixo tambm bastante usada no dia-a-dia, mas no a de Peter Chen que a que vimos em todos os exemplos anteriores, mas sim a de R. Elmasri & S. Navathe.

40

Projeto E-JOVEM

Ferramentas para modelagem Voc pode utilizar algumas das ferramentas abaixo para realizar a modelagem dos dados:
PowerDesigner; Erwin; DBDesigner; Microsoft Visio; brModelo

12. O Modelo Relacional Criado por Codd, nos anos 70, comeou a ser realmente utilizado nas empresas a partir de 1987, atravs do SGBDs. A abordagem relacional est baseada no princpio de que as informaes em uma base de dados podem ser consideradas como relaes matemticas e que esto representadas de maneira uniforme, atravs do uso de tabelas, ou falando de uma forma mais direta, um arquivo. Porm, um arquivo mais restrito que uma tabela. Toda tabela pode ser considerada um arquivo, porm, nem todo arquivo pode ser considerado uma tabela. Este princpio coloca os dados (entidades e relacionamentos) dirigidos para estruturas mais simples de armazenar dados, que so as tabelas. O conceito principal vem da teoria dos conjuntos (lgebra relacional) atrelado idia de que no relevante ao usurio saber onde os dados esto nem como os dados esto (transparncia). Os usurios manipulam objetos dispostos em linhas e colunas das tabelas, como mostra a _gura a seguir. O usurio pode lidar com estes objetos, conta com um conjunto de operadores e funes de alto nvel, constantes na lgebra relacional. Terminologia do modelo relacional: Tabela chamada de RELAO; Linha de uma tabela chamada de TUPLA; Coluna chamado de ATRIBUTO; o O tipo de dado que descreve cada coluna chamado de DOMNIO. Conceitos Gerais: Relao

12.1.

Dados os conjuntos S1, S2 , ..., Sn, no necessariamente distintos, diz-se que R uma relao sobre esses n conjuntos se R um conjunto de m tuplas, nas quais o primeiro elemento assume valores em S1, o segundo em S2 , e assim por diante.

41

Projeto E-JOVEM

12.2.

Conceitos Gerais: Atributos

Dada uma relao R, define-se como seus atributos os nomes das funes que mapeiam os valores de cada um dos elementos de cada tupla nos respectivos conjuntos S1, S2 , ..., Sn.

12.3.

Conceitos Gerais: Domnios

Dada uma relao R, o domnio do atributo Aj, dom (Aj) , o conjunto Sj no qual o atributo assume valores. Todo domnio possui uma descrio fsica e outra semntica. A descrio fsica serve para identificar o tipo e o formato dos valores que compem o domnio. o exemplo: char(13), (dd)dddd-dddd A descrio semntica serve para ajudar na interpretao de seus valores. o exemplo: Nmeros de telefone do Estado do RJ Conceitos Gerais: Esquemas de uma Relao

12.4.

O esquema de uma relao R, denotado por R (A1:D1, A2:D2 , ..., Am:Dm) , formado pelo nome da relao e pela lista de seus atributos e respectivos domnios. Usado para descrever uma relao atravs da especificao de seus atributos e domnios. Formado pelo nome da relao e uma lista de atributos A1, A2, ..., An, onde Aj o nome do papel desempenhado pelo domnio Dj no esquema da relao R. Tambm chamado de inteno da relao R. 12.5. Conceitos Gerais: Inteno X Extenso

Para uma mesma inteno pode existir mais de uma extenso

42

Projeto E-JOVEM

13. Banco de Dados Relacional O universo de um banco de dados relacional um conjunto finito, no vazio, de relaes. O esquema de um banco de dados relacional o conjunto dos esquemas das relaes que o formam, isto :

Uma instncia de um banco de dados relacional o conjunto das instncias de suas relaes. O mesmo esquema pode se aplicar a diferentes instncias de um banco de dados. 13.1. Caractersticas das Relaes No existe ordem entre as tuplas de uma relao; Desde que seja mantida a correspondncia entre atributos e valores, no existe ordem entre os atributos de uma relao. Todos os atributos de uma relao devem ser atmicos, isto , indivisveis em termos de valores e componentes. O esquema de uma relao pode ser interpretado como uma declarao ou um tipo de assertiva. Cada tupla da relao pode ser interpretada como um fato ou uma instncia particular dessa assertiva. Numa relao, todos os atributos devem ter nomes distintos: Conceitualmente, no existe nada que impea dois atributos de terem o mesmo nome. A restrio apenas de ordem prtica, para facilitar a consulta ao banco de dados. Mesmo que pudessem ter nomes iguais poderiam assumir valores em domnios diferentes. Nenhuma relao possui atributos em duplicata. Essa restrio decorre no apenas do fato de uma relao ser um conjunto, nos entidomatemtico do termo, como tambm do fato de suas tuplas representarem uma assertiva sobre o mundo real. No faz sentido representar o mesmo valor duas vezes no mesmo fato. Ex: Existe um ALUNO de Nome Antonio , com 19 anos de Idade, do Sexo M , cujo nome Antonio.

43

Projeto E-JOVEM

13.2.

Restries de Integridade Restries de Domnio especificadas atravs do tipo de dados de cada atributo do esquema de uma relao. Restries de Chave e de Integridade de Entidade especificadas atravs da definio de uma chave de acesso em cada relao. Restries de Integridade Referencial especificadas atravs de regras de relacionamento entre os esquemas das relaes que compem o esquema do banco de dados. Restries Semnticas especificadas atravs de regras sobre os esquemas do banco de dados. Restries de Domnio

13.3.

Especifica que o valor de cada atributo A deve ser um elemento atmico do domnio de A. Em geral, especificado atravs de tipos primitivos de dados, tais como integer, float, char, date, time, money, etc. Tambm podem ser descritos atravs da definio de subconjuntos de tipos primitivos ou de listas enumeradas. 13.4. Restries de Chave

O valor de um atributo-chave pode ser utilizado para identificar uma tupla especfica numa relao. Uma chave uma propriedade do esquema de uma relao, isto , uma propriedade que deve ser respeitada por todas as instncias da relao. Atributos cujos valores podem ser duplicados, no devem ser definidos como chaves de uma relao (Nome, por exemplo). Em geral, uma relao pode ter mais de uma chave. Nesse caso, cada chave da relao chamada de CHAVE CANDIDATA. A chave candidata escolhida para identificar as tuplas de uma relao chamada de CHAVE PRIMRIA. Em geral, entre todas as chaves candidatas, escolhe-se para chave primria aquela com o menor nmero de atributos.

44

Projeto E-JOVEM

13.5.

Integridade de Entidade

Estabelece que nenhum d o s atributos pertencentes a chave-primria de uma relao pode ter valor nulo. Isso para garantir a identidade individual das tuplas de uma relao, uma vez que a chave - primria utilizada para identificar cada uma de suas tuplas. 13.6. Integridade Referencial

Estabelece que qualquer tupla pertencente a uma relao R1 que referencie uma outra relao R2, tem de necessariamente referenciar uma tupla existente em R2. 13.7. Chave Estrangeira

Seja FK um conjunto de atributos de um esquema de relao R 1 . Diz-se que FK uma chave-estrangeira de R1s e FK satisfizer as seguintes condies: Os atributos pertencentes a FK assumirem valores dos mesmos domnios correspondentes aos atributos PK da chave-primria de um outro esquema de relao R2 ; e O v a l o r d e F K , em qualquer tupla de R 1 , for nulo ou igual ao valor de PK, em alguma tupla de R2 .

Utilizadas para manter a consistncia entre as tuplas de duas relaes distintas e relacionadas entre si. Decorrem tipicamente dos relacionamentos entre entidades definidos no modelo conceitual do banco de dados. Exemplo:

45

Projeto E-JOVEM

13.8.

Restries Semnticas

Especificadas atravs de regras sobre o esquema do banco de dados. Exemplos: 13.9. o salrio de um empregado tem de ser menor ou igual ao salrio de seu supervisor. o nmero de horas semanais que um empregado pode trabalharem projetos tem de ser menor ou igual a 56 . Notao Resumida

Exemplos: Departamento (deptonome, deptosigla, matriculagerente) matriculagerente referencia empregado Trabalha_em (matriculaemp, pcodigo, horas) matriculaemp referencia empregado pcodigo referencia projeto

14. Modelo ER x Modelo Relacional O modelo ER um Modelo Formal Um Diagrama Entidade-Relacionamento (DER) um modelo formal, preciso, no ambguo. Isto significa que diferentes leitores de um mesmo DER devem sempre entender exatamente o mesmo. Tanto assim, que um DER pode ser usado como entrada a uma ferramenta CASE na gerao de um banco de dados relacional. Por isso, de fundamental importncia que todos os envolvidos na confeco e uso de diagramas ER estejam treinados na sua perfeita compreenso. Sub-utilizao do modelo ER Observa-se em certas organizaes, que modelos ER so sub-utilizados, servindo apenas como ferramenta para apresentao informal de idias. Isso pode ser evitado com treinamento formal de todos envolvidos na modelagem e projeto do banco de dados.

46

Projeto E-JOVEM

A abordagem ER tem poder de expresso limitado Em um modelo ER, so apresentadas apenas algumas propriedades de um banco de dados. Em realidade, a linguagem dos DER uma linguagem muito pouco poderosa e muitas propriedades desejveis do banco de dados necessitam ser anotadas adicionalmente ao DER. Entendendo um pouco a transformao A transformao de um relacionamento n:n em entidade segue o seguinte processo: O relacionamento n:n representado como uma entidade. A entidade criada relacionada s entidades que originalmente participavam do relacionamento. A entidade criada tem como identificador: o As entidades que originalmente participavam do relacionamento; o Os atributos que eram identificadores do relacionamento original (caso o relacionamento original tivesse atributos identificadores). As cardinalidades da entidade criada nos relacionamentos de que participa sempre (1,1). As cardinalidades das entidades que eram originalmente associadas pelo relacionamento transformado em entidade so transcritas ao novo modelo conforme mostrado na figura da pgina anterior.

Modelo Relacional No modelo Lgico Relacional as entidades e os relacionamentos so trabalhados na forma de tabelas ou relaes, que so formadas por um conjunto de tuplas e atributos. A terminologia tabela mais comum nos produtos comerciais e na prtica. J a terminologia relao foi utilizada na literatura original sobre a abordagem relacional (da a denominao relacional) e mais comum na rea acadmica e nos livros texto. Modelo ER e Modelo Relacional Seja o Modelo ER de um indstria de automveis:

Teramos ento as seguintes Tabelas no Modelo Relacional:

47

Projeto E-JOVEM

PEA(CodPea, Descrio, Nome, Preo) FORNECEDOR(CNPJ, Nome, Telefone, CodPea) PRODUTO(CodProduto, Nome, Preo) COMPOSICAO(CodProduto , CodPea , Quantidade)

15. Mapeamento do Modelo ER para o Modelo Relacional Vamos mostrar o que observar e como fazer o mapeamento de um modelo EntidadeRelacionamentos (Modelo Conceitual) para um modelo Relacional (Modelo Lgico). As regras foram definidas tendo em vista dois objetivos bsicos: Bom desempenho: o Bom desempenho significa basicamente diminuir acesso ao disco Acessos ao disco consomem o maior tempo na execuo de uma instruo de banco de dados Simplificao do desenvolvimento e da aplicao de BD. Princpios das regras de mapeamento

15.1.

As regras foram definidas tendo por base, entre outros, os seguintes princpios: Evitar junes; Diminuir o nmero de chaves; Evitar campos opcionais. 15.1.1. Evitar junes Juno uma operao que busca dados de diversas linhas atravs da igualdade de campos. SGBD Relacional geralmente armazenam dados continuamente no disco: Um nico acesso ao disco traz todos os dados do bloco para a memria RAM. Junes envolvem comparaes entre diversas linhas: Junes requerem diversos acessos a disco (minimizam o desempenho). Assim, quando possvel, evite usar junes! Dica para evitar Junes: Ter os dados necessrios ao resultado da consulta em uma nica linha da tabela.

48

Projeto E-JOVEM

15.1.2. Diminuir o nmero de chaves Para implementar eficientemente o controle de chaves primrias o SGBD usa ndices para cada chave primria. ndices tendem a ocupar espao considervel em disco. A insero ou remoo de entradas em um ndice podem exigir diversos acessos a disco. Assim, quando possvel, diminua a quantidade de chaves primrias! Dica para diminuir chaves: Ter os dados subordinados as chaves primrias em uma nica tabela.

15.1.3. Evitar Campos Opcionais Campo opcional = campo que pode ser VAZIO (NULL). SGBD Relacional no desperdiam espao pelo fato de campos de uma linha estarem vazios (usam tcnicas de compresso de dados). Campo opcional no tem influncia na performance. Problema: Quando o SGBD Relacional no controla a obrigatoriedade do campo (quando o preenchimento de um campo depende de outro) Exemplo: Se a pessoa for casada deve preencher Campo X, Y e Z. Neste caso, o controle da obrigatoriedade deve ser feito por programao.

Assim, quando possvel, evite campos opcionais! Dica para evitar campos opcionais: 15.2. Ter apenas campos obrigatrios Esta regra mais "fraca" do que as precedentes

Regras para transformao do ER para o relacional Passos da transformao ER para Relacional: Traduo inicial de entidades e respectivos atributos; Traduo de relacionamentos e respectivos atributos; Traduo de generalizaes/especializaes.

49

Projeto E-JOVEM

15.2.1. Implementao inicial de entidades Cada entidade traduzida para uma tabela. Cada atributo da entidade define uma coluna desta tabela. Atributos identificadores da entidade correspondem chave primria da tabela. Esta uma traduo inicial: As prximas regras podem fundir tabelas. Exemplo: Modelo ER:

Modelo Relacional: Pessoa (Codigo,Nome,Endereo,DtNasc,DtAdm) 15.2.2. Traduo de Entidade Fraca Faz-se a traduo das entidades fracas em tabelas, seus atributos em campos e adiciona-se a chave primria da entidade forte na chave primria da entidade fraca.

Dependente (Codigo, NoSeq, Nome) Traduo de entidade fraca-recurso: Semelhante anterior. Entretanto faz-se sucessivamente!

50

Projeto E-JOVEM

Grupo (CodGrup, Nome) Empresa (CodGrup, NoEmpresa, Nome) Empregado (CodGrup, NoEmpresa, NoEmpreg, Nome) Dependente (CodGrup, NoEmpresa, NoEmpreg, NoSeq, Nome) 15.2.3. Nomes de colunas So freqentemente referenciados em programas: Use nomes das colunas que sejam curtos e significativos. Isto facilita a vida do programador.

No SGBDR os nomes das colunas no podem conter brancos.

No transcreva os nomes de atributos para nomes de colunas. Abrevie os nomes de atributos compostos de diversas palavras Exemplo: DtNasc, EstCivil, CartMotorista,... (use um padro Data => Dt)

Nomes de colunas no necessitam conter o nome da tabela: Prefira usar Nome do que usar NomeCli, NomeFor, NomePro Em SQL pode-se fazer Tabela.Coluna (Exemplo: Cliente.Nome)

51

Projeto E-JOVEM

15.2.4. Nome da coluna chave primria Chave primria: Pode aparecer em outras tabelas na forma de chave estrangeira Recomendvel: Nomes das colunas que compem a chave primria sufixados ou prefixados com o nome ou sigla da tabela na qual aparecem como chave primria Exemplo: CodCli, CodFor, CodProd 15.2.5. Implementao de relacionamento Alternativas bsicas: Tabela prpria Adio de colunas a uma das tabelas Fuso de tabelas A escolha de umas dessas alternativas depende da cardinalidade (mxima e mnima do relacionamento). 15.2.6. Tabela prpria Nesta, o relacionamento implementado atravs de uma tabela prpria. Esta contm os atributos identificadores das entidades participantes mais os atributos do relacionamento.

15.2.7. Adio de colunas Nesta, o relacionamento implementado atravs da insero de colunas na tabela oposta a multiplicidade mxima 1.

52

Projeto E-JOVEM

S possvel quando tem-se relacionamentos com pelo menos uma cardinalidade mxima 1.

15.2.8. Fuso de tabelas Nesta, o relacionamento implementado atravs da unio das tabelas participantes. S possvel para relacionamentos 1:1.

15.2.9. Implementao de relacionamento 1:1 1:1 onde ambas entidades tm relacionamentos opcionais:

Melhor traduo: Adio de Colunas - neste caso escolheu-se Mulher arbitrariamente e IdentH tem que ser nica. 1:1 onde ambas entidades tm relacionamentos opcionais:

53

Projeto E-JOVEM

Traduo alternativa: Tabela Prpria - neste caso Casamento torna-se uma tabela. 1:1 onde ambas entidades tm relacionamentos opcionais. Discusso: A soluo por adio de colunas a melhor, pois minimiza a quantidade de junes e chaves, entretanto, pode-se ter atributos opcionais, os quais devem ser tratados via programao. A soluo de tabela prpria aceitvel, mas a de fuso de tabela semanticamente invivel para o contexto. 1:1 onde uma entidade tem relacionamento opcional e a outra tem relacionamento obrigatrio:

Melhor traduo: Fuso de Tabela. 1:1 onde uma entidade tem relacionamento opcional e a outra tem relacionamento obrigatrio:

54

Projeto E-JOVEM

Traduo alternativa: Adio de Colunas. 1:1 onde uma entidade tem relacionamento opcional e a outra tem relacionamento obrigatrio. Discusso: A soluo por fuso de tabelas a melhor, pois elimina a necessidade de juno e diminuindo a qtd de chave. Entretanto, pode-se ter atributos opcionais, os quais devem ser tratados por programao. A soluo por tabela prpria pior que a soluo por adio de colunas, pois gera uma quantidade maior de junes e chaves. Ambas no tm campos opcionais.

1:1 onde ambas entidades tm relacionamento obrigatrio:

Melhor traduo: Fuso de Tabelas. 1:1 onde ambas as entidades tm relacionamento obrigatrio. Discusso: A soluo por fuso de tabelas a melhor, pois elimina a necessidade de juno, diminui a quantidade de chave e no tem atributos opcionais. Nenhumas das duas outras abordagens so solues adequadas, Pois: As entidades que participam do relacionamento seriam representadas atravs de duas tabelas distintas, mas com a mesma chave primria e relao um-para-um entre suas linhas.

55

Projeto E-JOVEM

15.2.10.

Maior nmero de junes. Maior nmero de chaves.

Implementao de relacionamento 1:N

Relacionamentos 1:N onde a entidade que tem multiplicidade mxima 1 obrigatria:

Melhor traduo: Adio de Colunas. 1:N onde a entidade que tem multiplicidade mxima 1 obrigatria:

Traduo Alternativa: Tabela Prpria. 1:N onde a entidade que tem multiplicidade mxima 1 obrigatria. Discusso: A soluo por adio de colunas a melhor, pois elimina a necessidade de juno extra, diminui a quantidade de chave e no tem atributos opcionais. A soluo de tabela prpria aceitvel, mas evite us-la, pois com esta, tem-se uma juno e uma chave a mais. Quanto a fuso de tabela, esta invivel para o contexto, pois implicaria em uma redundncia desnecessria de dados sobre departamento.

56

Projeto E-JOVEM

1:N onde a entidade que tem multiplicidade mxima 1 opcional:

Melhor traduo: Adio de Colunas. 1:N onde a entidade que tem multiplicidade mxima 1 opcional:

Traduo alternativa: Tabela Prpria. 1:N onde a entidade que tem multiplicidade mxima 1 opcional. Discusso: A soluo por adio de colunas a melhor, pois elimina a necessidade de juno extra e diminui a quantidade de chave. Entretanto, tem-se atributos opcionais, os quais devem ser tratados por programao. A soluo de tabela prpria aceitvel (tem juno a mais, mas no tem atributos opcionais). Contudo, a de fuso de tabela invivel para o contexto, pois implicaria em uma redundncia desnecessria de dados sobre financeira. Note: Em relacionamentos 1:N, sempre a melhor traduo a de Adio de Colunas.

57

Projeto E-JOVEM

15.2.11.

Implementao de relacionamento N:N

N:N em todas as combinaes de multiplicidade, nica traduo possvel de Tabela Prpria (onde a Chave Primria composta).

15.2.12.

Relacionamentos com grau maior que 2 As regras vistas at agora so aplicveis apenas a relacionamentos binrios. Para relacionamento com cardinalidade/grau maior que 2, no so definidas regras especficas. Mas no geral, o relacionamento transformado em uma entidade, onde sua CP normalmente composta de forma a atender a regra de negcio. Se no for afetar a regra de negcio transforme relacionamentos com cardinalidade/grau maior que 2 para relacionamentos binrios.

58

Projeto E-JOVEM

15.2.13.

Implementao de generalizao/especializao

Duas alternativas bsicas: 1. Uso de uma nica tabela para toda hierarquia; 2. Uso de uma tabela para cada entidade.

Uso de uma nica tabela para toda hierarquia: Todas as tabelas referentes s especializaes so fundidas em uma nica tabela, a qual contm: Chave primria correspondente ao identificador da entidade mais genrica; Caso no exista uma coluna tipo, a mesma deve ser adicionada; Uma coluna para cada atributo da entidade genrica; Uma coluna para cada chave estrangeira da entidade genrica; Uma coluna para cada atributo de cada entidade especializada; o Estas no devem ser colunas obrigatrias. Uma coluna para cada chave estrangeira da entidade especializada. o Estas tambm no devem ser obrigatrias.

59

Projeto E-JOVEM

Discusso: O uso de uma nica tabela para toda hierarquia minimiza junes e diminui a quantidade de chaves, entretanto, tem-se atributos opcionais, os quais devem ser tratados por programao. A soluo com uso de uma tabela para cada entidade no tem atributos opcionais, mas apresenta um nmero maior de junes e chaves. 15.2.14. Implementao de Atributos Multivalorados

Atributos Multivalorados: Transformar o atributo multivalorado em uma nova tabela fraca, incluindo na sua CP, a CP da tabela origem.

ATENO: Deve-se avaliar a real necessidade de se criar uma nova tabela (evitar juno!) Um cliente pode ter N telefones, mas se dois so suficientes ento:

60

Projeto E-JOVEM

Inconveniente: Consulta por telefone sero mais complicadas, pois devem referenciar todas as colunas referentes ao atributo multivalorado. 15.3. Normalizao

Obtido o esquema relacional correspondente ao documento, passa-se ao processo de normalizao. Este processo baseia-se no conceito de forma normal. Uma forma normal uma regra que deve ser obedecida por uma tabela para que esta seja considerada bem projetada. H diversas formas normais, isto , diversas regras, cada vez mais rgidas, para verificar tabelas relacionais. O processo de normalizao pode ser visto como o processo no qual so eliminados esquemas de relaes (tabelas) no satisfatrios, decompondo-os, atravs da separao de seus atributos em esquemas de relaes menos complexas, mas que satisfaam as propriedades desejadas. O processo de normalizao como foi proposto inicialmente por Codd conduz um esquema de relao atravs de um bateria de testes para certificar se o mesmo est na 1a, 2a e 3 Formas Normais. Estas trs Formas Normais so baseadas em dependncias funcionais dos atributos do esquema de relao. O processo de normalizao deve ser aplicado em uma relao por vez, pois durante o processo de normalizao vamos obtendo quebras e, por conseguinte, novas relaes. No momento em que o sistema estiver satisfatrio, do ponto de vista do analista, este processo iterativo interrompido. De fato existem literaturas indicando quarta, quinta formas normais, que no nos parece to importante, nem mesmo academicamente. A normalizao para formas apoiadas em dependncias funcionais evita inconsistncias, usando para isso a prpria construo da Base. Se a mesma consistncia for passvel de ser garantida pelo aplicativo, a normalizao pode ser evitada com ganhos reais no desempenho das pesquisas. No caso da consistncia no ser importante, tambm podemos no normalizar totalmente uma Base de Dados.

15.3.1. 1 Forma Normal A 1a Forma Normal prega que todos os atributos de uma tabela devem ser atmicos (indivisveis), ou seja, no so permitidos atributos multivalorados, atributos compostos ou atributos multivalorados compostos, ou seja, uma tabela encontra-se na 1FN quando no contm tabelas aninhadas. Portanto, a passagem 1FN consta da eliminao das tabelas aninhadas eventualmente existentes.

61

Projeto E-JOVEM

Alternativas para a 1 Forma Normal Para transformar um esquema de tabela no-normalizada em um esquema na 1FN h duas alternativas: Construir uma nica tabela com redundncia de dados: Cria-se uma tabela na qual os dados das linhas externas tabela aninhada so repetidos para cada linha da tabela aninhada. No caso da tabela da Figura 1, o esquema resultante seria o seguinte: ProjEmp (CodProj, Tipo, Descr, CodEmp, Nome, Cat, Sal, DataIni, aparecem

TempAl) Nesta tabela, os dados do projeto - CodProj, Tipo e Descr repetidos para cada linha da tabela de empregados - Emp. Construir uma tabela para cada tabela aninhada:

Cria-se uma tabela referente prpria tabela que est sendo normalizada e uma tabela para cada tabela aninhada. No caso da tabela da Figura 1, o esquema resultante seria o seguinte: Proj (CodProj, Tipo, Descr) ProjEmp (CodProj,CodEmp, Nome, Cat, Sal, DataIni, TempAl)

Dependncia Funcional Em uma tabela relacional, diz-se que uma coluna C2 depende funcionalmente de uma coluna C1 (ou que a coluna C1 determina a coluna C2) quando, em todas as linhas da tabela, para cada valor de C1 que aparece na tabela, aparece o mesmo valor de C2.

15.3.2. 2 Forma Normal Uma dependncia funcional X Y total se removemos um atributo A qualquer do componente X e desta forma, a dependncia funcional deixa de existir. A dependncia funcional X Y uma dependncia funcional parcial se existir um atributo A qualquer do componente X que pode ser removido e a dependncia funcional X Y no deixa de existir. A passagem segunda forma normal (2FN) objetiva eliminar certo tipo de redundncia de dados. A passagem segunda forma normal objetiva eliminar este tipo de redundncia de dados. Uma tabela encontra-se na segunda forma normal (2FN) quando, alm de encontrar-se na primeira forma normal, cada coluna no chave depende da chave primria completa.

62

Projeto E-JOVEM

Uma tabela que no se encontra na segunda formal contm dependncias funcionais parciais, ou seja, contm colunas no chave que dependem apenas de uma parte da chave primria. 3 Forma Normal Uma tabela encontra-se na 3FN quando, alm de estar na 2FN, toda coluna no chave depende diretamente de chave primria, isto , quando no h dependncias funcionais transitivas ou indiretas. Uma dependncia funcional transitiva ou indireta acontece quando uma coluna no chave primria depende funcionalmente de outra coluna ou combinao de colunas no chave primria. A passagem 3FN consta em dividir tabelas de forma a eliminar as dependncia transitivas.

16. SGBD A Seguir traremos exemplos de SGBDS, mas antes vamos a conceitos a respeitos dos SGBDS: Um Sistema de Gerenciamento de Banco de Dados o conjunto de programas de computador (softwares) responsveis pelo gerenciamento de uma base de dados. O principal objetivo retirar da aplicao cliente a responsabilidade de gerenciar o acesso, manipulao e organizao dos dados. O SGBD disponibiliza uma interface para que os seus clientes possam incluir, alterar ou consultar dados. Em bancos de dados relacionais a interface constituda pelas APIs ou drivers do SGBD, que executam comandos na linguagem SQL. Abaixo j trazemos alguns dos mais conhecidos SGBDs do mercado: MySQL; Oracle; PostgreSQL; Firebird; Sybase; HSQLDB; mSQL; SQL-Server; TinySQL; JADE; ZODB; IBM DB2.

63

Projeto E-JOVEM

16.1.

MySQL

um sistema de gerenciamento de banco de dados (SGBD), que utiliza a linguagem SQL (Structured Query Language - Linguagem de Consulta Estruturada) como interface. atualmente um dos bancos de dados mais populares, com milhes de instalaes pelo mundo. O MySQL possui as seguintes caractersticas: Suporta diferentes plataformas: Win32, Linux, FreeBSD, Unix, etc... Alta compatibilidade com linguagens como PHP, Java, Python, C#, Ruby e C/C++; Vrios sistemas de armazenamento de dados (batabase engine), como MyISAM, MySQL Cluster, CSV, Merge, InnoDB, entre outros; Recursos como transactions (transaes), conectividade segura, indexao de campos de texto, replicao, etc; Baixa exigncia de processamento (em comparao como outros SGBD); Excelente desempenho e estabilidade; Pouco exigente quanto a recursos de hardware; Facilidade de uso; um Software Livre; Suporte a vrios tipos de tabelas (como MyISAM, InnoDB e Maria), cada um especfico para um fim; Aceita Triggers; Aceita Stored Procedures e Functions; Instrues em SQL, como indica o nome. Replicao facilmente configurvel.

Abaixo so listadas algumas ferramentas de gerenciamento de banco de dados para o MySQL: SQLyog: um programa desenvolvido pela WEByog Enterprise que possibilita a edio de bancos de dados Mysql, que baseados na linguagem SQL. Utilizado na criao, edio, sincronizao de banco de dados internos e em servidores. disponibilizado em duas verses, Enterprise, paga, e Community, gratuita e de cdigo aberto. DBDesigner: Integra criao, modelagem, desenvolvimento e manuteno dos bancos em um ambiente simples e agradvel. O DBDesigner OpenSource distribudo sobre a licena GPL. PhpMyAdmin: um programa de computador desenvolvido em PHP para administrao do MySQL pela Internet. A partir deste sistema possvel criar e remover bases de dados, criar, remover e alterar tabelas, inserir, remover e editar campos, executar cdigos SQL e manipular campos chaves.

64

Projeto E-JOVEM

16.2.

Oracle

Apesar de ser proprietrio o SGBD da Oracle lder de mercado. O Oracle 9i foi pioneiro no suporte ao modelo web. O Oracle 10g, mais recente, se baseia na tecnologia de grid. Recentemente fora lanado o Oracle 11g que veio com melhorias em relao ao Oracle 10g. Alm da base de dados, a Oracle desenvolve uma sute de desenvolvimento chamada de Oracle Developer Suite, utilizada na construo de programas de computador que interagem com a sua base de dados. A Oracle tambm criou a linguagem de programao PL/SQL, utilizada no processamento de transaes. Abaixo so listadas algumas ferramentas de gerenciamento de banco de dados para o ORACLE: 16.3. DBDesigner; TOAD; SQLNavigator. PostgreSQL

um dos SGBDs de cdigo aberto mais avanados, contando com recursos como: Consultas complexas; Chaves estrangeiras; Integridade transacional; Controle de concorrncia multi-verso; Suporte ao modelo hbrido objeto-relacional; Triggers; Vises; Procedimentos armazenados em vrias linguagens.

Abaixo so listadas algumas ferramentas de gerenciamento de banco de dados para o PostgreeSQL: phpPgAdmin; PGExplorer;

65

Projeto E-JOVEM

17. Exerccios Resolvidos

1) O que um SGBD? Cite 3 exemplos. um software com recursos especficos para facilitar a manipulao das informaes de um BD e o desenvolvimento de programas aplicativos. Exemplos: Oracle, SQL Server, MySQL, Access, Interbase, Paradox, Sybase. 2) Quais as principais vantagens da utilizao de um Sistema de Banco de Dados em relao aos sistemas tradicionais de gerenciamento de arquivos? Rapidez na manipulao e no acesso informao; Reduo do esforo humano no desenvolvimento e utilizao das aplicaes; Disponibilizao da informao no tempo necessrio; Controle integrado de informaes distribudas fisicamente; Reduo da redundncia e de inconsistncia de informaes; Compartilhamento de dados; Aplicao automtica de restries de segurana; Reduo de problemas de integridade.

3) Indique alguns problemas que dificultariam o uso de banco de dados. Problemas: Custos iniciais (hardware, software, treinamento); Sobrecarga de processamento.

No usar se: Aplicaes e dados simples e fixos; Urgncia no tempo de resposta; Usurio nico.

66

Projeto E-JOVEM

4) Explique o que so Vises em SGBD? Um SGBD deve permitir que cada usurio visualize os dados de forma diferente daquela existente previamente no BD. Uma viso consiste de um subconjunto de dados do BD, mas no necessariamente estes devero estar armazenados no BD. Portanto, uma replicao de uma estrutura, para fins de acesso de forma diferenciada por outros aplicativos, no caracteriza o uso de um SGBD. 5) O sistema de banco de dados deve prover uma viso abstrata de dados para os usurios, isolando, desta forma, detalhes mais internos do BD. A abstrao se d em trs nveis. Explique estes nveis de abstrao.

Nvel fsico: tambm chamado de Esquema interno, o nvel mais baixo de abstrao. Descreve como os dados esto realmente armazenados, englobando estruturas complexas de baixo nvel. Nvel conceitual (ou lgico): conhecido tambm como Esquema Conceitual, descreve quais os dados esto armazenados e seus relacionamentos. Neste nvel, o BD descrito atravs de estruturas relativamente simples, que podem envolver estruturas complexas no nvel fsico. Nvel de vises do usurio: o nvel externo, descrevendo partes do BD que sero visualizadas pelos usurios de acordo com suas necessidades. Uma viso um subconjunto de dados do BD, sem que exista a necessidade de estarem armazenados no BD. 6) O que so modelos lgicos de dados? o conjunto de ferramentas conceituais para a descrio dos dados, dos relacionamentos entre os mesmos e das restries de consistncia e integridade. Em outras palavras, o conjunto de conceitos que podem ser usados para descrever a estrutura de um banco de dados (tipos de dados, relacionamentos e restries que devem ser mantidas sobre os dados).

67

Projeto E-JOVEM

7) Defina Esquema e Instncia de um Banco de Dados. Esquema: O esquema a definio das estruturas que compem o banco de dados; Espera-se que o esquema sofra nenhuma ou muito poucas alteraes depois de implementado;

O esquema independe dos dados a serem armazenados. Instncia: Instncia a materializao do banco de dados composto pelas estruturas mais os dados armazenados. Uma instncia um retrato do banco de dados em um determinado momento. Podemos ter a mesma estrutura replicada em vrios locais, cada uma com seu conjunto de dados.

8) Defina o Modelo Entidade-Relacionamento.

O Modelo Entidade-Relacionamento uma forma de se definir o esquema do banco de dados, composta pelos seguintes elementos: Entidades, Relacionamentos, Atributos, Chaves Primrias, Estrangeiras, e outras informaes descritas em forma textual. a principal ferramenta grfica para representao do Modelo de Dados.

9) As pessoas envolvidas num sistema de banco de dados podem ser divididas em usurios e administradores. Descreva os principais tipos de usurios e administradores existentes. Usurios Programador de aplicao - Desenvolve programas aplicativos que manipulam banco de dados Usurio sofisticado - Capaz de manipular dados num BD sem o uso de aplicativos utilizando as linguagens de consulta Usurio "ingnuo" - Manipula BD somente pelas interfaces definidas nos programas aplicativos

68

Projeto E-JOVEM

Administradores Administrador de dados - Define e atualiza do esquema do BD Administrador do SGBD o o o o o Define estrutura de armazenamento e estratgia de acesso; Concede autorizao de acesso a dados; Define controles de integridade; Define estratgias para backup; Monitora desempenho.

10) Criar uma tabela chamada DEPARTAMENTO com as seguintes colunas: COD_DEP, NOME_DEP. CREATE TABLE DEPARTAMENTO ( COD_DEP NUMBER(5), NOME_DEP VARCHAR2(50)); 11) Alterar a tabela anterior: criar uma chave primria chamada DEP_PK na coluna COD_DEP. ALTER TABLE DEPARTAMENTO ADD CONSTRAINT DEP_PK PRIMARY KEY (COD_DEP); 12) Renomear a tabela criada no item 1 para DEP. RENAME DEPARTAMENTO TO DEP; 13) Criar uma tabela chamada FUNCIONARIO com as seguintes colunas: COD_FUNC, NOME_FUNC. CREATE TABLE FUNCIONARIO ( COD_FUNC NUMBER(5), NOME_FUNC VARCHAR2(80));

14) Alterar a tabela anterior (item 4) conforme segue:

69

Projeto E-JOVEM

a. Renomear a tabela para FUNC. RENAME FUNCIONARIO TO FUNC; b. Criar uma chave primria na coluna COD_FUNC. ALTER TABLE FUNC ADD CONSTRAINT FUNC_PK PRIMARY KEY (COD_FUNC); c. Criar uma nova coluna chamada COD_DEP (mesmo tipo e tamanho da coluna com mesmo nome da tabela DEP). ALTER TABLE FUNC ADD COD_DEP NUMBER(5); d. Criar uma chave estrangeira chamada FUNC_DEP_FK na coluna COD_DEP referenciando a tabela DEP. ALTER TABLE FUNC ADD CONSTRAINT FUNC_DEP_FK FOREIGN KEY (COD_DEP) REFERENCES DEP (COD_DEP); e. Alterar a coluna NOME_FUNC para NOT NULL. ALTER TABLE FUNC MODIFY NOME_FUNC NOT NULL; 15) Criar uma constraint unique na coluna NOME_DEP da tabela DEP. ALTER TABLE DEP ADD CONSTRAINT DEP_NOME_UN UNIQUE (NOME_DEP); 16) Criar mais uma coluna chamada TEMP na tabela DEP. ALTER TABLE DEP ADD TEMP VARCHAR2(10); 17) Alterar a largura da coluna TEMP. ALTER TABLE DEP MODIFY TEMP VARCHAR2(15); 18) Alterar o nome da coluna TEMP para TEMP2. ALTER TABLE DEP RENAME COLUMN TEMP TO TEMP2; 19) Excluir a coluna TEMP2. ALTER TABLE DEP

70

Projeto E-JOVEM

DROP COLUMN TEMP2; 20) Criar uma tabela chamada DEP2 com base na tabela DEP (com todas as suas colunas e linhas). CREATE TABLE DEP2 AS SELECT * FROM DEP; 21) Criar uma coluna chamada ESTADO_CIVIL na tabela FUNC. ALTER TABLE FUNC ADD ESTADO_CIVIL VARCHAR2(10); 22) Criar uma constraint check na coluna ESTADO_CIVIL da tabela FUNC (valores aceitos: 'solteiro','casado','vivo', 'separado'. ALTER TABLE FUNC ADD CONSTRAINT FUNC_ESTADO_CIVIL_CK CHECK (ESTADO_CIVIL IN ('solteiro','casado','vivo','separado')); 23) Criar uma coluna chamada DATA_ADM na tabela FUNC (default: SYSDATE). ALTER TABLE FUNC ADD DATA_ADM DATE DEFAULT SYSDATE; 24) 15.Criar um ndice chamado NOME_FUNC_IN na coluna NOME_FUNC da tabela FUNC. CREATE INDEX NOME_FUNC_IN ON FUNC (NOME_FUNC); 25) Eliminar o ndice criado no item anterior. DROP INDEX NOME_FUNC_IN; 26) Criar uma view chamada DEP_VW com base nas colunas COD_DEP e NOME_DEP da tabela DEP. CREATE VIEW DEP_VW AS SELECT COD_DEP, NOME_DEP FROM DEP; 27) Excluir a view criada no item anterior. DROP VIEW DEP_VW; 28) Eliminar todas as linhas da tabela FUNC (utilizar o comando TRUNCATE). TRUNCATE TABLE FUNC; 29) Eliminar a tabela FUNC. DROP TABLE FUNC;

71

Projeto E-JOVEM

30) Eliminar a tabela DEP. DROP TABLE DEP; 31) Modele o sistema abaixo no Dia Modeler (sugesto) ou manualmente, se preferir.

O sistema Educare responsvel pelo controle de alunos, professores, salas, cursos, disciplinas, recursos didticos e coordenaes do campus. Os dados armazenados neste sistema esto descritos abaixo. O sistema permite realizar o cadastro da instituio e de seus campi. Para o cadastro da instituio necessrio entrar com nome, cidade, endereo e sigla. Cada campus descrito por nome, cidade, endereo e data de implantao. Para cada campus, possvel armazenar os blocos. No bloco, informamos cdigo de identificao (campo alfanumrico), rea (m2) e tipo (didtico ou administrativo). Os blocos possuem salas. Em um bloco didtico pode haver salas administrativas. Logo, preciso que o cadastro da sala indique se ela administrativa ou didtica. Para a sala, tambm guarda-se o nmero e rea (m2). Os alunos esto cadastrados no sistema por matrcula, nome, endereo e telefone. Cada aluno pode estar matriculado em at 10 disciplinas por vez. Uma disciplina descrita por cdigo e carga horria. O contedo da disciplina guardado em outra tabela atravs dos campos item e carga horria. O mesmo contedo pode estar em disciplinas diferentes. Por exemplo: a disciplina de Introduo Computao do curso de Engenharia em Alimentos possui o mesmo contedo da disciplina Informtica Bsica do curso de Cincias Contbeis. As disciplinas pertencem aos cursos. Um curso possui vrias disciplinas e a mesma disciplina pode estar em cursos diferentes. Sobre o curso, o sistema armazena o nome, cdigo e durao (em anos). O sistema tambm organiza os alunos em turmas, que so associadas aos cursos. Da turma, possvel conhecer seu cdigo e sala. Os professores ministram disciplinas. Pode ocorrer de mais de um professor ministrar a mesma disciplina. O professor cadastrado por matrcula, nome, cpf, endereo e telefone. Os cursos esto ligados a coordenaes (Informtica, Direito, Administrao, etc). Uma s coordenao pode ser responsvel por vrios cursos. No entanto, um curso no pode ser administrado por mais de uma coordenao. Da coordenao, o sistema guarda sigla, bloco, sala e ramal. O sistema tambm gerencia recursos didticos, tais como projetores multimdia, notebooks e DVDs. O recurso cadastrado por nome, cdigo e descrio. H o processo de reserva, sendo que cada recurso pode ser reservado por um professor para uma determinada data, bloco, sala e horrio.

72

Projeto E-JOVEM

32) Listar todos os dados da tabela funcionrios ordenados por matrcula: Select * From funcionario Order by matricula 33) Fornea o nmero total de empregados da companhia: Select count(*) From funcionrio 34) Liste todos os funcionrios que tenham entre 10 e 12 anos de servio (inclusive): Select * From funcionrio Where anos_servico between 10and12 35) Liste o nome, a matrcula e o salrio de todas as pessoas cujo salrio no esteja entre R$ 1.000,00 e R$ 3.000,00:

73

Projeto E-JOVEM

Select nome_func,matricula,salrio From funcionrio Where salrio <1000,00 or salrio>3000,00 36) Liste todos os funcionrios com 1, 2 ou 3 anos de servio, ou cujo valor de anos de servio seja nulo: Select * From funcionrio Where anos_servico=1 or anos_servico=2 or anos_servico=3 or anos_servico is null 37) Liste a matrcula, nome e salrio de todas as pessoas em ordem alfabtica por nome: Select matricula, nome_func, salrio From funcionrio Order by nome_func 38) Liste o departamento, a matrcula, o nome e o salrio dos funcionrios em ordem decrescente de salrio em seu departamento: Select depto,matricula,nome_func,salrio From departamento,funcionrio,lotado_depto Where matric_func=matricula and cd_depto=depto Order by salrio desc 39) Liste as matrculas, os nomes e os departamentos de todos os empregados que gerencia algum departamento: Select matricula, nome_func,nome_depto From funcionrio,departamento,gerencia_depto Where matricula_ger=matricula and num_div=depto 40) Liste a mdia salarial de cada departamento: Select avg(salrio) From departamento,funcionrio,lotado_depto Where matric_func=matricula and cod_depto=depto 41) Liste matrcula, nome, anos de servio de todos os gerentes de departamentos que recebem salrios maiores que R$ 2000,00 ou que possuam matrculas maiores que 30, e que tenham mais de 6 anos de servio: Select matricula,nome_func,anos-servico From funcionario,gerencia_depto Where matricula_ger=matricula and anos_servico>6 and (salrio>2000,00 or matricula>30)

74

Projeto E-JOVEM

42) Liste o departamento, matrcula, nome e salrio de todos os gerentes em ordem decrescente de salrio dentro de departamento: Select nome_depto,matricula,nome_func,salrio From departamento,funcionrio,gerencia_depto,lotado_depto Where matricula_ger=matricula and cd_depto=depto and matric_func=matricula Order by salrio desc 43) Descubra qual a mdia de ganhos totais e quantos empregados so considerados para clculo dessa mdia: Select avg(salrio), count(*) From funcionrio 44) Liste o nome, a matrcula e o salrio de todas as pessoas que no ganhem salrio inferior a R$ 1000,00: Select nome_func,matricula,salrio From funcionrio Where salrio> 1000,00 45) Liste a matrcula, nome, anos de servio e salrio de todas as pessoas com mais de 6 anos de servio e que, ou seja gerente de departamento ou ganhe salrio superior a R$ 3000,00: Select matricula,nome_func,anos_servico,salrio From funcionario,gerencia_depto,departamento,lotado_depto Where cod_depto=depto and matric_func=matricula and anos_servico>6 and (matricula_ger=matricula or salrio>3000,00) 46) Liste a matrcula, o nome e o salrio de todas as pessoas em ordem decrescente de salrio: Select matricula,nome_func,salrio From funcionrio Group by salrio desc 47) Liste o nome, a matrcula e o departamento de todas as pessoas que no trabalhem nos departamentos 10 e 20 Select nome_func,matricula,nome_depto

75

Projeto E-JOVEM

From funcionario,departamento,lotado_depto Where depto not in(10) and depto not in(20) and matricula=matric_func and cod_depto=depto 48) Liste a localizao, a matrcula e o nome de todos os empregados do departamento de compra e venda: Select localizao,matricula,nome_func From departamento,funcionrio,lotado_depto Where nome_depto=compra and nome_depto=venda and cod_depto=depto and matric_func=matricula 49) Liste o nome e a matrcula de todos os funcionrios alocados no projeto holografia: Select nome_func,matricula From funcionrio,projeto,alocado Where nome_proj=holografia and cod_proj=codigo_proj 50) Liste todos os funcionrios que trabalhem na zona sul:

mat_func=matricula

and

Select nome_func From departamento,funcionario,lotado_depto Where localizao=zona sul and cd_depto=depto and matric_func=matricula

76

Projeto E-JOVEM

18. Exerccios Propostos 1. Uma Empresa possui funcionrios. Um funcionrio trabalha em uma Empresa. 2. Os Atletas participam de competies. Em uma competio participam vrios atletas. 3. Deseja-se fazer um banco de dados para uma rede de hotelaria. Um hotel possui quartos. Cada quarto pertence a apenas um hotel. 4. Um soldado, que possui as caractersticas nome, Registro Militar (RM), data de nascimento, possui armas. Uma arma, que possui as caractersticas de srie, registro e calibre, de um soldado. Uma arma limpa por vrios soldados. Um soldado limpa vrias armas. 5. Um funcionrio supervisionado por um gerente (o gerente tambm um funcionrio). Um gerente (que tambm funcionrio) supervisiona vrios funcionrios. Do funcionrio deseja-se saber nome, cpf e endereo. 6. Um mdico trata de pacientes. Do mdico deseja-se saber CRM, nome e suas especialies. Um paciente, no qual h a necessidade de sabermos seu nome, endereo e idade, tratado por vrios mdico. Um paciente realiza vrios tipos de exames. Um tipo de exame, destes h a necessidade de guardar seu nmero, data e descrio, feito por vrios paciente. 7. O aluno cursa disciplinas lecionadas por um professor cada uma. Para cada aluno deve-se manter as informaes de, nome e seus telefones. Uma disciplina cursada por vrios alunos e lecionada por um professor. Das disciplinas deseja-se saber codigo, nmero de crditos e descrio. Os professores lecionam diversas disciplinas cada um e em cada disciplina possui diversos alunos. Dos professores deseja-se saber seu cdigo, nome e telefone. 8. Um mdico trata de pacientes. Do mdico deseja-se saber CRM, nome e suas especialies. O mdico pede exames para vrios pacientes. Um paciente, no qual h a necessidade de sabermos seu nome, endereo e idade, tratado por vrios mdico. Um paciente realiza vrios tipos de exames pedidos pelos mdicos. Um tipo de exame, destes h a necessidade de guardar seu nmero, data e descrio, feito por vrios paciente a pedido dos mdicos. 9. Uma empresa possui funcionrios. Os funcionrios podem ser engenheiros, secretrias ou tcnicos. 10. Uma empresa fabrica automveis. Os automveis podem ser carros de passeio, caminhes ou nibus. 11. D o conjunto de dependncias funcionais para o esquema relacional R(A,B,C,D) com chave primria nos atributos AB. Sabe-se que a relao est na 1FN mas no est na 2FN.

77

Projeto E-JOVEM

12. De_na a terceira forma normal. D um exemplo de uma relao em 2FN mas no em 3FN. Transforme a relao em relao em 3FN. 13. Modele a situao abaixo em um Diagrama Entidade-Relacionamento considerando:
O banco Dindin contratou a Hardsoft para construo de um sistema de gerenciamento da logstica do banco. Abaixo esto transcritas as informaes obtidas por entrevistas de levantamento de requisitos que ajudam no projeto do bando de dados do sistema. o O sistema deve gerenciar o cadastro de clientes, contas e transaes efetuadas. A respeito do cadastro de clientes, devem ser armazenados os dados: nome, endereos (rua, no. e bairro), telefones, data de nascimento e idade. o O banco Dindin possui agncias espalhadas em vrias cidades de vrios estados do pas. A agncia cadastrada no sistema por nome, endereo (rua, no. e bairro) e telefones. As agncias esto agrupadas em cidades. A cada cidade atribudo um cdigo e tambm informado o estado a que pertence.

14. Considerando os conceitos de banco de dados abordados na disciplina descreva e relacione:

A. Linguagens de banco de dados B. Usurios de banco de dados C. Arquitetura de SGBD

15. O que uma operao Atmica? Para as prximas questes utilize o enunciado abaixo para respostas:
CdigoCantor Nome Pas Msica CdigoMsica Nome Durao Categoria Canta Cantor Msica DataGravao Gravadora Gravadora CdigoGravadora NomeGravadora Pas

16. Considerando as relaes acima mostre: a) como cri-las em SQL. b) os comandos para insero de pelo menos dois registros em cada relao. c) altere o pas da gravadora Records para China.

78

Projeto E-JOVEM

d) supondo que existam msicas da Categoria MPB j cadastradas, altere a durao dessas msicas para 20 unidades. 17. Mostre como realizar as seguintes consultas sobre as tabelas da questo 1. a) Selecione o nome das msicas que no foram gravadas pelo cantor Tiririca. b) Selecione o nome das msicas que foram gravadas em 2000 pelo cantor Tiririca. c) Selecione quantas msicas foram gravadas pelo cantor Tiririca em novembro de 2000. d) Selecione o nome das gravadoras da Frana que possuem cantores da ndia. e) Selecione a durao total de todas as msicas do cantor Jess para cada uma das gravadoras nas quais esse cantor trabalhou. f) Selecione o nome do cantor da msica da gravadora Records que apresenta maior durao. 18. Uma transportadora area pretende implementar uma base de dados com a seguinte informao: A transportadora tem vrios avies. Cada avio tem, alm da matrcula, um nome, o modelo do avio, o nmero de lugares, e a indicao da sua autonomia. Na transportadora trabalham vrios pilotos. Sobre cada piloto pretendese guardar o nome e nmero de licena, assim como quais os modelos de avies que podem pilotar. Pretende-se ainda, guardar a informao relativa ao nome, data de nascimento de cada um dos descendentes (caso existam) dos pilotos. Cada avio faz vrios vos. Cada vo deve ter, pelo menos, a indicao da data e hora em que acontecer, dos locais de partida e de destino. Cada vo de um dado avio pilotado por um piloto. 19. Para guardar a informao relevante organizao de uma conferncia, vai ter de ser criada uma base de dados. Na conferncia so apresentados vrios artigos, cada um dos quais com um ttulo e um nmero. Cada artigo tem um ou mais autores. De cada autor, pretendese armazenar alm do nome, o endereo de email, e o nome e endereo da instituio a que esto associados. H ainda a informao relativa aos participantes da conferncia. De cada participante dever ser retida a informao do seu nome, morada (endereo) e endereo de e-mail. Alm disso, distingue-se entre os participantes os que so estudantes e os que no so. Cada participante no estudante tem de pagar antecipadamente a inscrio por transferncia bancria, pelo que necessrio guardar o nmero da transao. Para no

79

Projeto E-JOVEM

pagar, o estudante tem de enviar antecipadamente um comprovante e na base de dados deve ser armazenado o nome da universidade que o passou. 20. Quais so as principais diferenas entre um Sistema de Banco de Dados e um sistema baseado em arquivos. 21. O que o papel de uma entidade em um relacionamento? Quando necessrio especificar o papel das entidades em um relacionamento? 22. Considere o relacionamento POSSUI entre funcionrio e dependente. Considere que um dependente de um funcionrio possa tambm ser funcionrio? Como o diagrama deveria ser modificado para evitar o armazenamento redundante das informaes das pessoas que so tanto dependentes quanto empregados? 23. Tende em mente o modelos de dados relacional, o que voc entende por relao, tupla e atributo? 24. A secretaria de uma determinada universidade precisa gerenciar informaes sobre suas disciplinas, estudantes e professores. Um aluno pode estar cursando uma ou mais disciplinas, que por sua vez _e ministrada por um professor. Para cada disciplina guardam-se o seu nome, as salas onde podem ser ministradas e as disciplinas que lhe so pr-requisitos. Para cada alunos temos o nome, o RGA e o curso que est fazendo. Para cada professor, de interesse da secretaria o seu nome, telefone e o departamento onde est alocado.
USE O ENUNCIADO ABAIXO PARA RESPONDER AS PRXIMAS 10 QUESTES:

Considere o esquema de uma base de dados sobre carros composta pelos seguintes esquemas de relao (a chave primaria de cada esquema encontra-se sublinhada). Suponha a existncia de uma relao (tabela) para cada um dos esquemas abaixo e com o mesmo nome dos esquemas. AUTOMOVEIS(C_odigo, Ano, Fabricante, Modelo, Pa__s, Prec_oTabela) REVENDEDORAS(CGC, Nome, Propriet_ario, Cidade, Estado) CONSUMIDORES(Identidade, Nome, Sobrenome) NEGOCIOS(Comprador, Revenda, CodAuto, AnoAuto, Data, Prec_o) GARAGENS(CGCRevenda, CodoAuto, AnoAuto, Quantidade) A relao AUTOMOVEIS, cada automvel identificado por um cdigo juntamente com o seu ano de fabricao. Apenas revendedoras autorizadas, ou seja, cadastradas na relao REVENDEDORAS, podem vender os carros no mercado. O CGC identifica unicamente uma revendedora. Os consumidores tm identidade nica em territrio nacional e so cadastrados na relao CONSUMIDORES. Cada negcio efetuado registrado na tabela NEGOCIOS, com detalhamento de data, preo pago, identidade do comprador (consumidor), revendedora, cdigo e ano do automvel. Por uma relao

80

Projeto E-JOVEM

GARAGEM determina quais automveis as revendedoras tm a inteno de negociar e qual o seu potencial de venda a cada momento. Isto , a quantidade de carros que podem ser negociados pelas revendedoras. Com base no esquema e descrio acima, expresse as seguintes consultas Usando SQL: 25. Listar os carros (cdigo e ano) que custam menos do que 23.000,00. 26. Listar os nomes dos fabricantes dos automveis na base de dados e os respectivos paises de fabricao. 27. Listar os estados onde se vende o modelo Xantia, cujo fabricante _e a Citroen. 28. Quais revendedoras no vendem automveis de origem francesa? 29. Quais os nomes dos consumidores que compram apenas carros de 1996? 30. Listar os nomes das revendedoras, e de seus respectivos proprietrios, que venderam em 1995 carros de 1996 por valor abaixo da tabela. 31. Quantos automveis, independentes do ano de fabricao, podem ser negociados por cada revendedora? 32. Quais automveis, de um mesmo fabricante e modelo, so colocados a venda por mais.de uma revendedora? 33. Qual o carro cujo pereo de tabela _e o mais caro? 34. Qual o valor total pago em negcios efetuados por automvel e por revendedora? 35. Crie um modelo Hierrquico para: a) Controlar as aulas de educao Fsica; b) Controlar a freqncia de um laboratrio de Informtica. 36. Escreva a SQL de criao do banco do sistema abaixo, seguinte roteiro:
1. Criar base de dados; 2. Usar base criada; 3. Criar tabelas (...); 4. Descrever tabelas (...); 5. Destruir tabelas (...); 6. Destruir base de dados;

81

Projeto E-JOVEM

SISTEMA DE COMRCIO ELETRNICO Entidades e Atributos Cliente: CPF*, Nome*, Data de nascimento, Endereo*, Telefone Carrinho de Compra: Data*, Total* Produto: Nome*, Descrio*, Foto*, Preo*, Desconto* Estoque (do produto): Quantidade*, Quantidade Mnima* Formas de Envio: Tipo*, Prazo*, Preo* Formas de Pagamento: Data Limite*, Tipo*

37. Quais as perguntas que devem ser levantadas quanto a um projeto de BD? 38. Citar vantagens e desvantagens no uso da tecnologia de BD. 39. Citar problemas que podem ocorrer quanto ao processamento de consultas em um BD 40. Construa um modelo E-R com entidades, relacionamentos e atributos para o caso a seguir. O banco de dados de uma empresa mantm informaes sobre empregados, departamentos e projetos. Aps a coleta de requisitos e a fase de anlise, chegou-se seguinte descrio para modelagem de dados:

82

Projeto E-JOVEM

o A empresa organizada em departamentos. Cada departamento possui um nome e cdigo nicos, alm de um empregado que gerencia o departamento. O banco de dados deve armazenar a data em que o empregado passou a gerenciar o departamento. o Cada departamento controla um certo nmero de projetos, cada qual com seu ttulo e nmero nicos. o Para cada empregado, armazena-se seu nome, CPF, salrio, sexo e data de nascimento. Cada empregado vinculado a um nico departamento, mas pode trabalhar em vrios projetos que no so necessariamente controlados pelo seu departamento. Deve-se registrar no BD a carga horria semanal do empregado em cada projeto. Tambm deve-se manter informao sobre o supervisor direto de cada empregado. o Deseja-se igualmente que o BD armazene dados de dependentes de cada empregado. Para cada dependente, mantm-se seu nome, sexo, data de nascimento e relao com o empregado. 41. Defina os termos cardinalidade mxima e cardinalidade mnima. 42. Elabore um esquema de banco de dados para uma locadora de vdeo com pelo menos as seguintes entidades: 1. Filme, 2. Mdia (cpia de filme em fita(s) VHS ou DVD), 3. Cliente, 4. Reserva de filme, 5. Movimentao de mdia (compra, venda ou locao).
USE O ENUNCIADO ABAIXO PARA RESPONDER AS PRXIMAS 10 QUESTES:

Banco de Dados de uma Revenda Internacional de Automveis Este banco de dados foi criado por uma revenda de automveis com o objetivo de manter atualizado um cadastro com os modelos de carros de cada marca que cada uma das suas filiais tem disponveis na prpria loja. Para isto foram utilizadas as seguintes relaes: descrio dos modelos dos carros e suas respectivas marcas (Modelo), descrio das marcas dos carros (Marca) e descrio das filiais da revenda (Filial). A relao Disponveis representa a disponibilidade dos carros de cada marca em cada filial da loja. 43. Obter todos os dados de todas as filiais; 44. Obter o cdigo das marcas que esto disponveis na filial Filial_X; 45. Obter os cdigos dos modelos de marcas brasileiras que esto disponveis nas lojas situadas no Brasil; 46. Obter as cores predominantes dos modelos da marca Ford;

83

Projeto E-JOVEM

47. Obter os cdigos dos modelos e sua respectivas marcas, mas somente para modelos disponveis em lojas que estejam situadas em Curitiba; 48. Obter os cdigos das filiais que no expem nenhum carro de cor predominante azul e de marcas da Alemanha; 49. Obter os cdigos das filiais que possuem todos os carros que a marca Ford produz; 50. Obter os cdigos daqueles modelos que encontram-se disponveis em todas as filiais de Porto Alegre; 51. Obter os cdigos das marcas que tem determinado modelo disponvel em todas as filiais; 52. Obter os cdigos das filiais que tenham disponvel algum modelo de uma marca que fabrique modelos com cor predominante vermelha. 53. Liste todos os atributos de projeto em ordem crescente de cdigo 54. Descubra qual a mdia de ganhos totais e quantos empregados so considerados para clculo dessa mdia. 55. Liste o nome, a matrcula e o salrio de todas as pessoas que no ganhem salrio inferior a R$ 1.000,00. 56. Liste matrcula, nome, anos de servio e salrio de todas as pessoas com mais de 6 anos de servio e que, ou seja gerente de departamento ou ganhe salrio superior a R$ 3.000,00 57. Liste a matrcula, o nome e o salrio de todas as pessoas em ordem decrescente de salrio. 58. Liste o nome, a matrcula e o departamento de todas as pessoas que no trabalhem nos departamentos 10 e 20. 59. Liste a localizao, a matrcula e o nome de todos os empregados do departamento de compra e venda. 60. Liste a matrcula, o nome e o salrio de todos os coordenadores de projeto que no ganhem salrio inferior a R$ 1.500,00. 61. Liste o nome e a localizao de todos os departamentos subordinados diviso administrao. 4/4 62. Liste o nome e a matrcula de todos os funcionrios alocados no projeto holografia. 63. Liste o nome e a matrcula de todos os gerentes de departamento e o respectivo nome do departamento gerenciado e da diviso a que so subordinados.

84

Projeto E-JOVEM

64. Liste o salrio mdio para os departamentos que possuam salrio mdio superior mdia da companhia. 65. Liste o menor, o maior e a mdia de salrios de cada departamento que possua salrio mdio superior a R$ 1.000,00. 66. Liste o salrio mdio dos empregados por departamento e local para grupos de mais de um empregado. 67. Liste o salrio mdio dos empregados por local para grupos de mais de um empregado. 68. Liste a matrcula e os nomes dos coordenadores, e matrcula dos funcionrios que so supervisionados por eles.

85