Lista 3 - OR (2023.2)

Prévia do material em texto

<p>Lista 3: SGBD_OR (2023.2)</p><p>1. Crie um modelo lógico OR correspondente ao diagrama ER abaixo, referente</p><p>ao gerenciamento de uma oficina. No esquema abaixo, vale ressaltar que o</p><p>cargo do funcionário determina o seu salário.</p><p>(nota: para essa questão, utilize varray para modelar os atributos multivalorados e faça uma</p><p>nested table para modelar o relacionamento (1:N): Serviço - Produto) \ (Observação: Como é</p><p>apenas para praticar, foi delimitado onde utilizar varray e nested table; porém, na prova, é</p><p>dever do aluno saber onde e se é necessário utilizar varray e nested table na questão.)</p><p>2. Faça as seguintes consultas em OR, nas tabelas criadas a partir do esquema</p><p>acima:</p><p>a) Qual o serviço mais comum na oficina?</p><p>b) Qual a média salarial dos funcionários da oficina?</p><p>c) Qual o funcionário que efetuou mais serviços?</p><p>d) Quais os nomes dos funcionários que não possuem supervisores?</p><p>Quais os cargos deles?</p><p>e) Quais os modelos dos carros que possuem a cor ‘VERMELHO’?</p><p>f) Qual a marca de carro mais frequente na oficina? Qual é a cor mais</p><p>comum?</p><p>g) Qual o serviço mais caro? Qual o mais barato?</p><p>h) Consulte o nome, cpf e os números de celular dos funcionários que</p><p>têm o cargo de ‘Mecânico’.</p><p>i) Quais os produtos utilizados no 'Serviço de Freios' e o preço de</p><p>cada um deles?</p><p>j) Mostre quanto cada cliente gastou no total, ou seja, contando com</p><p>todos os atendimentos realizados, para cada cliente. (Ordene do</p><p>cliente que mais gastou para o que menos gastou)</p><p>k) Qual foi o atendimento mais caro? Selecione o serviço que foi</p><p>feito, a data, o funcionário que fez o serviço, além do custo e a</p><p>placa e modelo do carro em que esse serviço foi feito.</p><p>l) Qual a empresa que fornece 'Bateria' para o serviço de 'Troca de</p><p>Bateria' da Oficina? Mostre o nome da empresa e seus telefones.</p><p>m) Quais os serviços que utilizam a máquina 'Elevador Automotivo'?</p><p>3. Logo abaixo temos o esquema OR de uma doceria, a partir desse esquema</p><p>realize as seguintes consultas:</p><p>CREATE OR REPLACE TYPE tp_pessoa AS OBJECT (cpf VARCHAR2(14), nome</p><p>VARCHAR2(30), data_nascimento DATE,MEMBER PROCEDURE</p><p>exibirInformacoes(SELF tp_pessoa)) NOT FINAL NOT INSTANTIABLE;</p><p>/</p><p>CREATE OR REPLACE TYPE tp_cliente UNDER tp_pessoa (</p><p>quantidade_de_compras INTEGER, OVERRIDING MEMBER PROCEDURE</p><p>exibirInformacoes(SELF tp_cliente));</p><p>/</p><p>CREATE OR REPLACE TYPE tp_fornecedor AS OBJECT (cnpj VARCHAR2(18),</p><p>nome_empresa VARCHAR2(30));</p><p>/</p><p>CREATE OR REPLACE TYPE tp_ingrediente AS OBJECT (nome VARCHAR2(30),</p><p>empresa REF tp_fornecedor, quantia_estoque VARCHAR2(20));</p><p>/</p><p>CREATE OR REPLACE TYPE tp_nt_ingredientes AS TABLE OF tp_ingrediente;</p><p>/</p><p>CREATE OR REPLACE TYPE tp_doce AS OBJECT (codigo_doce VARCHAR2(7),</p><p>nome VARCHAR2(30), categoria VARCHAR2(20), preco_unidade NUMBER,</p><p>ingredientes tp_nt_ingredientes);</p><p>/</p><p>CREATE OR REPLACE TYPE tp_pedido AS OBJECT (codigo_pedido</p><p>VARCHAR2(10), valor NUMBER(5,2), numero_itens INTEGER);</p><p>/</p><p>CREATE OR REPLACE TYPE tp_contem AS OBJECT (pedido REF tp_pedido,</p><p>doce REF tp_doce, quantidade_doces INTEGER);</p><p>/</p><p>CREATE OR REPLACE TYPE tp_compra AS OBJECT (pedido REF tp_pedido,</p><p>data_compra DATE, cliente REF tp_cliente);</p><p>/</p><p>CREATE TABLE tb_cliente OF tp_cliente (cpf PRIMARY KEY);</p><p>/</p><p>CREATE TABLE tb_fornecedor OF tp_fornecedor (cnpj PRIMARY KEY,</p><p>nome_empresa NOT NULL);</p><p>/</p><p>CREATE TABLE tb_doce OF tp_doce (codigo_doce PRIMARY KEY, nome NOT</p><p>NULL, categoria NOT NULL, preco_unidade NOT NULL) NESTED TABLE</p><p>ingredientes STORE AS nt_ingredientes;</p><p>/</p><p>CREATE TABLE tb_pedido OF tp_pedido (codigo_pedido PRIMARY KEY, valor</p><p>NOT NULL, numero_itens NOT NULL);</p><p>/</p><p>CREATE TABLE tb_contem OF tp_contem (pedido WITH ROWID REFERENCES</p><p>tb_pedido, doce WITH ROWID REFERENCES tb_doce, quantidade_doces NOT</p><p>NULL);</p><p>/</p><p>CREATE TABLE tb_compra OF tp_compra (pedido WITH ROWID REFERENCES</p><p>tb_pedido, data_compra NOT NULL, cliente WITH ROWID REFERENCES</p><p>tb_cliente);</p><p>/</p><p>a) Qual o doce mais caro? Informe o nome, sua categoria e o preço.</p><p>b) Qual o doce mais vendido pela doceria? Informe o seu código,</p><p>nome e quantidade vendida.</p><p>c) Quais os ingredientes do ‘Brownie Recheado’? Informe os nomes</p><p>dos ingredientes e seus fornecedores.</p><p>d) Qual cliente gastou mais em um único pedido? Quanto foi gasto?</p><p>e) Escreva o TYPE BODY de tp_cliente, escrevendo a função</p><p>‘exibirinformacoes’, que deve exibir as informações de um cliente.</p><p>f) Crie uma função que, dado o CPF do cliente, retorne quanto ele</p><p>gastou, levando em conta todas as compras que ele fez.</p>