Especialização em Tecnologia da Informação

1. Bancos de Dados Objeto-relacional e Orientados a Objetos Especialização em Tecnologia da Informação Maria Carolina (mcts@cin.ufpe.br)

2. Motivação

3. Roteiro • Metodologia • Exemplo • Minimundo • Modelagem • Implementação • Tipos • Tabelas • Apresentação de uma solução • Consultas

4. Metodologia

5. Metodologia escolhida • Uso de um caso de estudo • Firmar conceitos • Tirar dúvidas • Realizar implementação • ...

6. Caso prático

7. Domínio da aplicação • Qualquer estabelecimento comercial que precise armazenar informações sobre seus funcionários, clientes, produtos... • Supermercado simples • Loja que vende a varejo ou a atacado • ...

8. Minimundo (1) • Um cliente do supermercado é identificado por um código único, nome, endereço, telefone, CPF e data de nascimento. Cada cliente realiza vários pedidos, os quais só podem estar associados a um cliente.

9. Modelo Conceitual (1)

10. Minimundo (2) • Sobre um pedido, deseja-se armazenar um código de controle e a data de realização. A cada pedido, um ou mais produtos podem ser solicitados, mas é necessário saber a quantidade vendida e o valor de venda de cada unidade.

11. Modelo Conceitual (2)

12. Minimundo (3) • Um produto é reconhecido no sistema através de um código único, nome e preço. Cada produto é fornecido por um fornecedor, o qual é representado por um identificador, nome, endereço e CNPJ.

13. Modelo Conceitual (3)

14. Minimundo (4) • Cada pedido é atendido por apenas um funcionário, o qual é representado por um código único, nome, telefone, CPF, data de nascimento, endereço, função e salário, mas o mesmo funcionário é responsável por mais de um atendimento. Não existe a obrigatoriedade de que pessoas com a mesma função recebam o mesmo salário.

15. Modelo Conceitual (4.1)

16. Modelo Conceitual (4.2)

17. Minimundo (5) • Os funcionários possuem uma organização interna na empresa: eles estão distribuídos em departamentos, porém um funcionário trabalha em apenas um departamento. Cada departamento possui um nome e um código.

18. Modelo Conceitual (5)

19. Minimundo (Observações) • Quando houver necessidade de armazenar telefone de alguém envolvido no sistema, deve-se armazenar no máximo 3 telefones (residencial, celular e comercial). • Já quando se falar em endereço, lembrar de especificar cada campo: • Logradouro • CEP • Número • Bairro • Cidade • Estado

20. Implementação

21. Implementação • Implementar esse modelo conceitual usando os conceitos Objeto-relacional da Oracle. • Tipos • Tabela de tipos • Ref • Nested • Varray • ...

22. Contas Oracle • www.cin.ufpe.br/~mcts/ETI/OR-Oracle • ArquivoContas ORACLE.txt

23. Uma solução

24. Minha implementação • www.cin.ufpe.br/~mcts/ETI/OR-Oracle • ScriptCriacao.SQL • Arquivos adicionais • ScriptDROP.SQL • ScriptPovoamento.SQL

25. Consultas

26. Consulta (1) • Selecionar os dados dos clientes que moram em Recife. SELECT C.* from tb_cliente C WHERE C.endereco.cidade = 'Recife'

27. Consulta (2) • Selecionar os telefones de todos os funcionários. SELECT F.nome, T.* FROM tb_funcionario F, TABLE(SELECT telefones FROM tb_funcionario WHERE id = F.id) T ORDER BY 1,2;

28. Consulta (3) • Selecionar o nome e a quantidade de funcionários de cada departamento. SELECT D.nome, COUNT(value(FUNC)) from tb_departamento D, TABLE(SELECT dep.funcionarios FROM tb_departamentodep where dep.coddepartamento = D.coddepartamento) FUNC GROUP BY D.nome

29. Consulta (4) • Inserir um novo funcionário e associá-lo ao departamente de vendas. INSERT INTO tb_funcionario VALUES (8, null, 'Funcionario Novo', TO_DATE('20/08/1980', 'dd/MM/yyyy'), array_telefone(null, tp_telefone(81, 89632541), null), tp_endereco('Ponte Velha', 1548, 'Recife Antigo', 'Recife', 'PE', '20639-154'), 1000, 'Vendedor', NESTED_REF_PEDIDO()); INSERT INTO TABLE(SELECT D.FUNCIONARIOS FROM tb_departamento D WHERE D.nome = 'Vendas') F VALUES ((SELECT REF(Func) FROM tb_FuncionarioFunc WHERE Func.id = 8));

30. Consulta (5) • Selecionar quantos pedidos cada cliente já realizou. SELECT c.nome, COUNT(p.codigo) AS QtdePedidos FROM tb_cliente c LEFT OUTER JOIN tb_pedido p ON (p.ref_cliente = REF(c)) group by c.nome;

31. Consulta(6) • Selecionar o número de celular de cada funcionário • Escreva uma função que receba dois parâmetros: • Uma lista do tipo dos telefones • Índice onde se localiza o telefone que ser que buscar. • Utilize essa função para implementar essa consulta

32. Consulta (6) CREATE OR REPLACE FUNCTION getMember (vlisttb_funcionario.telefones%type, indice NUMBER) RETURN tp_telefone IS BEGIN IF indice <= vlist.last THEN RETURN vlist(indice); END IF; RETURN NULL; END getMember; SELECT nome, getMember(telefones, 2) FROM tb_funcionario; SELECT X.nome, DECODE(getMember(X.telefones, 2), null, 'No members', to_char (getMember(X.telefones, 2).toString)) FROM tb_funcionario X;

33. Consulta (7) • Selecionar o valor gasto por cada cliente que já realizou alguma compra. SELECT S.ref_pedido.ref_cliente.nome As nomeCLiente, SUM(S.valorvenda) AS QtdeGasta from tb_solicitacao S GROUP BY S.ref_pedido.ref_cliente.nome;

34. Dúvidas do projeto O que sobrar do tempo... =D

35. Bancos de Dados Objeto-relacional e Orientados a Objetos Especialização em Tecnologia da Informação Maria Carolina (mcts@cin.ufpe.br)