UFCD 781 – Análise de Sistemas de Informação 

Designação da UFCD: Análise de Sistemas de Informação

Código da UFCD: 0781

Carga Horária: 50 horas

Nº de aulas: 20 aulas

Objetivos da Unidade:

• Planear a estrutura de uma base de dados.

• Criar e manipular tabelas relacionais e fazer consultas.

• Manipular registos na base de dados.

1 - Introdução a Base de Dados

1 - Introduction to Databases (EN):

Slides: 1-SI-UFCD_781_intro_database_10ºQ_EN.pptx 

Desenhar a Base de dados de uma Livraria 📖

Parte 1

Uma livraria guarda informações sobre os seus clientes, livros e encomendas.

• Cada cliente tem um ID, um nome e um email.

• Cada livro tem um código, um título e um autor.

• Cada encomenda tem um número de encomenda, uma data e um valor total.

Exercício:

1. Identifica as entidades do texto

2. Cria uma tabela para cada entidade com 4 registos cada uma

Não te esqueças de identificar uma chave primária para cada tabela.

PS: Não te preocupes com ligações ou relações ainda. 

Utiliza o Excel para criares as tabelas.

Espera pelo professor antes de começares a parte 2.

Parte 2

Agora a livraria quer saber quais livros cada cliente comprou em cada encomenda.

• Um cliente pode fazer várias encomendas.

• Cada encomenda contém um ou mais livros.

• A loja também quer guardar informações sobre os fornecedores, que têm um ID, nome e contacto.

• Cada livro é fornecido por um fornecedor.

Exercício:

1. Voltando a pegar nas tabelas da Parte 1, adiciona as novas tabelas que achares necessárias.

2. Para já, apenas adiciona às tabelas os campos necessários para ligar as tabelas (FK).

3. Define bem as chaves primárias e os campos que vão ligar tabelas.

Adiciona o resultado final ao Padlet: https://padlet.com/ticespan/si10q

SOLUÇÕES: solução_exercicio_livraria.xlsx 

👉 VAMOS REVER - Exercício: 

Empresa e Funcionários

Uma empresa pretende organizar a informação sobre os seus departamentos e os funcionários que neles trabalham.

• Cada departamento pode ter vários funcionários

• Cada funcionário trabalha em apenas um departamento

• Para cada departamento, a empresa guarda:

  • um identificador

  • o nome do departamento

• Para cada funcionário, a empresa guarda:

  • um identificador

  • o nome

  • o cargo

Desenha no Excel as tabelas necessárias, identificando os campos, as chaves primárias (PK) e as chaves estrangeiras (FK).

SOLUÇÃO: solução_exercicio_revisão.xlsx 

2 - CRIAR UM AMBIENTE DE TRABALHO PARA BDs

INICIAR UM SERVIDOR DE GESTÃO DE BASE DE DADOS (SGBD)

Materiais de estudo (Slides de Aula):

PORTUGUÊS (PT): 2-SI-UFCD_781_SQL.pptx 

ENGLISH (EN): 2-SI-UFCD_781_SQL_EN.pptx 

Exercícios - Primeiros passos 👣

1 - Atividade prática - CREATE TABLE: A minha primeira tabela com SQL

Recria a estrutura das tabelas, Departamentos e Funcionários, que desenhaste no último exercício, usando o CREATE TABLE.

1 - Conecta-te à tua base de dados no DBeaver

2- Cria um novo Script SQL

3 - Escreve o comando CREATE TABLE utilizando o nome da tabela,  nomes dos campos e tipos de dados apropriados.

4 - Executa.

5 - Navega a tua base de dados na interface do DBeaver e investiga se a tabela foi criada com sucesso.

2 - Atividade prática - INSERT INTO: Adicionar dados

Volta às tabelas, Departamentos e Funcionários, e utiliza o INSERT INTO para adicionares dados às tabelas.

1 - Utiliza o comando INSERT INTO para adicionares 4 novos registos a cada tabela.

3 - Executa e verifica se ocorreu erro.

Pergunta: O que acontece ser inserires duas vezes o mesmo id_funcionario?

Pergunta: O que acontece se inserires uma FK id_departamento que não existe na tabela Departamentos?

3 -  Atividade prática - SELECT: Consultar dados

Volta às tabelas, Departamentos e Funcionários, e utiliza o SELECT para:

1 - Obter todos os nomes e cargos dos funcionários da empresa

2 - Obter toda a informação guardada sobre os Departamentos

SOLUÇÃO:  solução_exercicio_funcionarios&departamento.sql 

4 -  Atividade prática - SELECT e WHERE: Filtragem de dados

Volta às tabelas, Departamentos e Funcionários, e cria o comando SELECT que devolve a resposta às seguintes perguntas:

1 - Como podes selecionar todos os dados do funcionário que tem o id_funcionario igual a 2?

2 - Cria um SELECT que retorne apenas os dados do funcionário chamado 'Carlos'.

3 - Seleciona o nome de todos os funcionários que pertencem ao departamento 10002

4 - Escreve uma consulta para listar todos os funcionários, exceto aqueles que têm o cargo de 'CEO'.

Ficha de Exercícios - SELECT + Filtros

Utiliza o seguinte ficheiro SQL para criares as tabelas necessárias para realizares o exercício:

Descarrega aqui 👉: base_dados_exemplo_cliente&compras.sql 

Executa o ficheiro SQL no DBeaver para criares a tabela Cliente e Compra.

Exercises sheet: Ficha_Exercicios_SQL_select_cliente&compra_GROUPBY_ENGLISH.pdf 

Recurso para treinarem em casa: https://www.sql-practice.com/

Ficha de Exercícios 2 - Revisão e consolidação de conhecimento

English version: 

Part 1: Consolidation_exercises_SQL_EN.pdf 

Part 2: Consolidation_exercises_SQL_EN_Part2.pdf 

Ao longo deste módulo aprenderam a criar e a trabalhar com bases de dados relacionais usando SQL. Chegou a altura de aplicar tudo o que sabem num projeto real.

O objetivo é que, a pares, construam uma base de dados completa sobre um tema à vossa escolha, desde a modelação inicial até às consultas e manipulação de dados, simulando a criação de um sistema de informação de uma organização.

Fases do Projeto:

Fase 2 — Criação da Estrutura (DDL)

Traduzam o vosso diagrama para SQL:

1. Criem a base de dados com CREATE DATABASE.

2. Criem todas as tabelas com CREATE TABLE, incluindo chaves primárias e chaves estrangeiras.

   • As colunas devem ter tipos de dados adequados.

   • O nome das tabelas e colunas deverão ser claros e sem erros

Fase 3 — Inserção de Dados (INSERT)

Populem a base de dados com dados realistas:

• Cada tabela deve ter no mínimo 8 a 10 registos.

• Os dados devem fazer sentido para o tema escolhido.

Fase 4 — Consultas (SELECT)

Esta fase tem dois passos. Primeiro pensam como analistas, depois traduzem para SQL.

Passo 1 — Definir as perguntas 

Antes de escreverem qualquer query, identifiquem pelo menos 8 perguntas interessantes que se podem fazer sobre os vossos dados. 

Exemplos de perguntas (para um clube desportivo):

• Quais são os 3 jogadores com salário mais alto?

• Quantos jogadores existem em cada posição?

• Quais os jogadores que nunca marcaram um golo?

• Em que jogos é que o clube marcou 3 ou mais golos?

As perguntas devem cobrir:

• Pelo menos 2 que impliquem filtros com WHERE

• Pelo menos 1 que implique ordenação de resultados

• Pelo menos 2 que impliquem contagens, somas ou médias (GROUP BY + agregação)

• Pelo menos 1 que filtre grupos (HAVING)

• Pelo menos 2 que envolvam dados de mais do que uma tabela (JOIN)

Passo 2 — Escrever as queries

Para cada pergunta, escrevam a query SQL que lhe responde. 

No ficheiro .sql, cada query deve estar acompanhada da pergunta como comentário.

Exemplo:

-- Pergunta: Quais são os 3 jogadores com salário mais alto?

SELECT nome, salario FROM Jogadores ORDER BY salario DESC LIMIT 3;

Fase 5 — Manipulação e Manutenção

Demonstrem que sabem modificar dados e estruturas:

1. Pelo menos 2 operações UPDATE com condições WHERE diferentes

2. Pelo menos 1 operação DELETE

3. Pelo menos 1 operação ALTER TABLE (ADD, MODIFY ou DROP COLUMN)

Entrega e apresentação

A entrega do trabalho será feita com:

1.  Um único ficheiro (.sql) com todo o código organizado e comentado, na seguinte ordem:

1. Criação da base de dados e das tabelas

2. Inserção dos dados

3. Queries de consulta (com a pergunta como comentário antes de cada query)

4. Operações de UPDATE, DELETE e ALTER TABLE

2. Uma apresentação PowerPoint com:

   1. A explicação do tema

   2. O diagrama do modelo da base de dados

   3. As tabelas utilizadas

   4. As perguntas e comandos SELECT utilizados para as responder