Instituto Superior de Engenharia de Lisboa Área Departamental de Engenharia de Electrónica e Telecomunicações e de Computadores Licenciatura Engenharia Informática e de Computadores (1º Sem 2012/2013) Sistemas de Informação I – Trabalho (Fase 2/3/4) Docentes: Lara Santos e Rui Joaquim Objectivos: Pretende-se com esta parte do trabalho que os alunos exercitem os conceitos relacionados com a passagem de modelos conceptuais (EA) para modelos lógicos (relacional). Pretende-se ainda que apliquem os conceitos referentes à teoria da normalização, e exercitem a álgebra relacional. Os alunos devem aplicar os conceitos relacionados com a criação do modelo físico, utilizando SQL/DDL, e manipulação dos dados utilizando SQL/DML. Pretende‐se ainda que os alunos exercitem ainda a utilização de interfaces SQL-CLI (call level interface), designadamente JDBC, e a utilização de transacções como forma de assegurar a atomicidade. Enunciado do trabalho Após a realização da fase 1 do trabalho, os alunos têm agora a oportunidade de reflectir sobre o modelo que apresentaram, comparando-o com a sugestão de modelação apresentada em anexo. Deverão identificar diferenças ou falhas, possíveis melhorias ou diferentes opções de modelação, juntamente com a apresentação do conjunto final de regras de negócio a aplicar ao modelo conceptual. Após realizarem as correcções e alterações que considerem necessárias e/ou relevantes, prosseguirão para a passagem do modelo conceptual para o modelo lógico. O modelo lógico a apresentar será o modelo relacional e deverá ser acompanhado de todas as restrições que não são garantidas pelo modelo, com excepção daquelas já apresentadas em conjunto com o modelo conceptual. Após a obtenção do modelo lógico, os alunos deverão garantir que este se encontra normalizado até à 3ª Forma Normal, antes de prosseguirem para as restantes etapas. Os alunos deverão apresentar as dependências funcionais e os passos necessários à normalização das relações do modelo. Os alunos poderão ainda decidir fazer alterações, simplificações ou optimizações ao modelo, desde que devidamente indicadas e fundamentadas. Com base no modelo lógico obtido será então construído, em SQL, o modelo físico do sistema, e realizadas interrogações em álgebra relacional e SQL. Por fim, os alunos têm agora a oportunidade de utilizar a API JDBC para, numa aplicação Java, acederem e manipularem os dados existentes no modelo físico. Nota: Após a criação do modelo físico deverão popular a base de dados com informação que permita às interrogações apresentarem resultados pertinentes. Na etapa de população da base de dados, os alunos deverão ter particular atenção ao cumprimento das restrições de integridade, utilizando de forma adequada o controlo transaccional. Pretende‐se: Deverão ser produzidos os seguintes resultados: 1. Análise do modelo entidade-associação entregue na Fase 1 do trabalho, indicação e justificação das diferenças encontradas face ao modelo fornecido (em anexo) pelos docentes; justificação das alterações a realizar e das opções de modelação a manter. Apresentação do modelo de dados conceptual final revisto que inclui o diagrama entidade-associação, descrição das entidades, seus atributos e associações e a descrição final de todas as regras de negócio aplicáveis. 2. O modelo de dados lógico que resulta da passagem do EA, com indicação clara de todas as chaves estrangeiras e chaves candidatas e primárias, incluindo todas as restrições que não são garantidas pelo modelo lógico obtido e que derivem da passagem do modelo conceptual para o modelo lógico, nomeadamente as obrigatoriedades. Para cada relação do modelo devem também ser apresentadas as respectivas dependências funcionais. Deverá ser garantida a normalização até à terceira forma normal e, caso se justifique, realizadas as alterações ao modelo conceptual que sejam necessárias para manter a sua compatibilidade com o modelo lógico normalizado. 3. Construção do modelo físico em SQL/DDL com base no modelo lógico obtido na etapa 2. Os alunos devem ter particular atenção ao tipo de dados escolhidos, bem como à implementação de chaves candidatas e chaves estrangeiras. Os alunos deverão utilizar para o efeito o SQL Server, e procurar incluir todas as restrições de integridade que seja possível suportar na forma declarativa. Para esta etapa os alunos deverão apresentar o código SQL necessário para criar, destruir, e carregar com dados o modelo físico do sistema (tabelas). Os alunos deverão apresentar para o efeito três scripts distintos: criação, destruição e carregamento; os alunos são encorajados a assinalar, em comentário ao longo do código, referências às restrições de integridade implementadas. O script de carregamento deve contemplar a inserção de dados que permitam testar todas as interrogações a realizar no ponto seguinte. 4. Construção de interrogações na Linguagem SQL e de expressões em Álgebra Relacional, que permitam obter determinada informação, considerando o modelo lógico/físico obtidos. Para cada alínea deve ser construída uma e uma só interrogação SQL. De entre todas as alíneas, os alunos devem seleccionar no mínimo nove interrogações para as quais apresentam a respectiva expressão em álgebra relacional. Os alunos deverão construir as interrogações que permitam obter: a) Listar o preçário (valores base) praticado pelas empresas para os seus grupos, nos vários locais. b) Listar todos os clientes (número de cliente, nome e morada, tipo de documento, número do documento) indicando se são do tipo empresa ou do tipo particular; no caso das empresas apresentase o NIPC e o tipo de documento assume-se “NIPC”; c) Listar todos os veículos, com pelo menos 4 portas, a gasolina, alugados pelo Cliente número 930, em Lisboa (cidade), por mais de 3 dias e pagos com voucher; d) Apresentar a lista de veículos que nunca foram alugados por clientes do sexo feminino; apresentar a matrícula do veículo e nome e NIPC da respectiva empresa e o grupo a que o veículo pertence. e) Listar todos os veículos (matricula, ref, empresa, marca e modelo, grupo) disponíveis para aluguer entre 25-12-2012 e 31-12-2012, e qual o valor base a praticar. Apresentar também o grupo de upgrade (para o caso de ser necessário realizar o upgrade). Nota: um veículo só se encontra disponível nestas datas se não estiver afecto a nenhuma reserva nesses dias. f) Listar todos os veículos da categoria “compacto” que foram alugados com GPS, no Porto e respectivos clientes (número e nome) que os alugaram; g) Indicar para cada local a lista das empresas que disponibilizam veículos nesse local, indicando o valor base mais barato que oferecem e a que grupo corresponde. h) Listar as empresas que oferecem em média uma maior quantidade de extras gratuitos por grupo, seleccionando apenas as empresas e respectivos grupos que disponibilizam recolhas em Coimbra (cidade) na “Estação Coimbra B” (local). i) Apresentar para cada empresa o número total de alugueres já realizados e pagos e respectivo valor facturado. Apresentar o nome, NIPC e morada de cada empresa. j) Apresentar a lista de clientes (número e nome) que, em 2012 alugaram veículos a todas as empresas. k) De entre as empresas que têm mais do 2 veículos da categoria “luxo”, quais aquelas que têm um maior número de condutores por aluguer. l) Apresentar por ordem alfabética os clientes particulares que fizeram, em 2012, uma despesa acima da média: Nota: apresentar toda a informação sobre o cliente e respectiva despesa. 5. Criação de uma aplicação Java (em modo linha de comandos ou GUI) que, para além de outras funcionalidades que pretenda incluir, permita realizar as seguintes operações (garantido todas as restrições de integridade aplicáveis): a) Listar o preçário (valores base) praticado pelas empresas para os seus grupos nos vários locais. b) Adicionar um novo cliente. c) Fazer uma Reserva, incluindo a escolha de quais os extras pretendidos e apresentação do valor total a pagar de acordo com a tarifa base diária definida e o número de dias do aluguer, e incluindo o preço dos extras escolhidos. d) Realizar o pagamento de uma reserva. Nota: Os alunos não devem descuidar a importância de aceder aos dados de forma correta, em detrimento de aspectos relacionados com a interface com o utilizador. Prazo de entrega: Data aconselhada para entrega das alíneas 1 a 4: 16 de Janeiro de 2013 Data limite para entrega do trabalho: 30 de Janeiro de 2013 Nota importante: Todos as alíneas são de entrega obrigatória e têm nota mínima de 9,5 valores.