Introdução às Bases de Dados Relacionais Base de dados Modelos
Propaganda
Base de dados Introdução às Bases de Dados Relacionais Introdução às BD relacionais Álgebra relacional SQL • Uma base de dados (BD) é uma coleção estruturada de dados tal que é possível armazenar e extrair conhecimnto eficientemente. • Um Sistema de Gestão de Base de Dados (SGBD/DBMS) é um programa informático para armazenar uma BD Alexandre Gonçalves, DECivil, IST [email protected] Modelos / Níveis • As questões mais importantes... MUNDO REAL informação informal informação formal dados instanciação dos dados MODELO DESCRITIVO MODELO CONCEPTUAL MODELO OPERATIVO MODELO INTERNO Escolha de um modelo descrição das estruturas e transações* manipulação e definição da estrutura estruturas de dados externas – – – – – – Identificação das entidades e relações Capacidade de representar o comportamento Facilidade de interrogação Espaço exigido Políticas de acesso Robustez (consistência e integridade dos dados) estruturas de dados internas * ações de modificação de conteúdo da BD Desenho de uma BD 1. Análise de requisitos • • que dados serão guardados que operações são mais frequentes e sujeitas a requisitos de performance 2. Desenho da BD conceptual • • descrição dos dados em alto-nível muitas vezes usa-se o modelo E/R, um modelo de dados semântico 3. Desenho da BD lógica • conversão da BD conceptual para um esquema (schema) no modelo de dados do SGBD Desenho de uma BD 4. Refinamento do esquema • • análise da coleção de relações ex: normalização 5. Desenho da BD física • • análise de volumes de trabalho expectáveis ex: construção de índices 6. Desenho da aplicação e segurança • aspetos de implementação particulares Modelo Relacional • Modelo para estruturar informação baseado na teoria de conjuntos e lógica de predicados. • Objetivos: armazenar dados no mínimo espaço possível; obtenção de informação no mínimo tempo; captura das relações inter-dados. • Outros modelos possíveis: hierárquicos (arborescentes), em rede, orientados a objetos, orientados a conceitos, ... Definições • DOMÍNIO (conjunto de valores) • RELAÇÃO (=TABELA) (esquema com nome e conjunto de atributos) • • • • ATRIBUTO (=COLUNA) TUPLO (=LINHA) CHAVE (K é chave para R significa KR) RESTRIÇÕES DE INTEGRIDADE RESTRIÇÕES DE INTEGRIDADE TABELA CLUBE J GM GS EUR TREINADOR Barcelona 6 20 4 CL Tito Vilanova Chelsea 6 14 4 CL Di Matteo Porto 6 13 3 CL Vítor Pereira AEK 4 9 9 Europe L. Man.Utd 6 13 5 CL Fiorentina 5 12 8 Europe L. Siniša Mihajlović Alex Ferguson CLUBE JOGADOR SALÁRIO GOLOS Porto Porto Barcelona Boga Zé Manel Roger 24000 20305 44333 5 0 12 GDA Pardal 12000 2 Consistência entre tabelas relacionadas NORMALIZAÇÃO 1NF 2NF 3NF BCNF 4NF 5NF NORMALIZAÇÃO •Normalizar evita (ou pelo menos, minimiza) algumas anomalias e redundância •Anomalias: atualização / inserção / eliminação EQUIPA JOGADOR SALÁRIO TREINADOR Barcelona Boga 24000 Tito Vilanova Chelsea Juan Gol 20305 Mourinho Chelsea John Doe 33500 Mourinho AEK Agounis 44333 Traianos Dellas Barcelona Smithson 12000 Gerardo Martino FORMAS NORMAIS NORMALIZAÇÃO – 1ª FN COMPRA Data 120 6 Jun 2007 Item Papel Tinta Quant 31 41 133 14 Jul 2007 Item Papel Lápis Quant 100 12 1ª: «Uma relação está na 1FN se todos os seus atributos são simples» 2ª: «Uma relação está na 2FN se estiver na 1FN e todos os atributos não primários são dependentes da chave por inteiro» 3ª: «Uma relação está na 3FN se estiver na 2FN e todos os atributos não primários são independentes uns dos outros» NORMALIZAÇÃO - 1ª FN TRANSAC TION Date ITEM Quant 120 6 Jun 2007 Papel 31 120 6 Jun 2007 Tinta 41 133 14 Jul 2007 Papel 100 133 14 Jul 2007 Lápis 12 FORMAS NORMAIS 1ª: «Uma relação está na 1FN se todos os seus atributos são simples» 2ª: «Uma relação está na 2FN se estiver na 1FN e todos os atributos não primários são dependentes da chave por inteiro» • Não há linhas duplicadas nos atributos-chave • Não há vazios em atributos-chave 3ª: «Uma relação está na 3FN se estiver na 2FN e todos os atributos não primários são independentes uns dos outros» NORMALIZAÇÃO – 2ª FN NOME Ana José Maria Maria José Maria David PROJETO Prj1 Prj1 Prj2 Prj3 Prj2 Prj1 Prj4 Descrição Orçamento do projeto Horas 400 400 175 137 175 400 260 30 10 250 10 25 10 75 NORMALIZAÇÃO – 2ª FN PROJETO Orçamento Prj1 400 NOME PROJETO Horas Prj2 175 Prj3 137 Prj4 260 Ana José Maria Maria José Maria David Prj1 Prj1 Prj2 Prj3 Prj2 Prj1 Prj4 30 10 50 10 25 10 75 FORMAS NORMAIS NORMALIZAÇÃO – 3ª FN PROJETO Gestor Data nasc. 1ª: «Uma relação está na 1FN se todos os seus atributos são simples» Prj1 Joaquim Jan 75 Prj3 Teresa Mai 86 2ª: «Uma relação está na 2FN se estiver na 1FN e todos os atributos não primários são dependentes da chave por inteiro» Prj2 Joaquim Jan 75 Prj4 Daniel Nov 88 Prj5 Alfreda Mai 86 3ª: «Uma relação está na 3FN se estiver na 2FN e todos os atributos não primários são independentes uns dos outros» NORMALIZAÇÃO – 3ª FN PROJETO Gestor GESTOR Data nasc. Prj1 Prj3 Prj2 Prj4 Prj5 Joaquim Teresa Joaquim Daniel Alfreda Alfreda Mai 86 Joaquim Jan 75 Teresa Mai 86 Daniel Nov 88 NORMALIZAÇÃO além da 3ª FN A maior parte das tabelas na 3NF estão livres de anomalias de atualização, inserção e remoção. Mas há ainda etapas de normalização além da 3FN: •Boyce-Codd NF (BCNF): para todo o XY, X é chave candidata ou inclui uma chave candidata (qualquer atributo, do qual outro seja funcionalmente dependente [um determinante], é chave candidata) •4FN: remove redundâncias com base em dependências multivaloradas •5FN: toda a dependência de junção é consequência das chaves candidatas NORMALIZAÇÃO BCNF RELAÇÕES ENTRE TABELAS VENDEDOR# NOME VENDEDOR PRODUTO PREÇO 1 Big Sell Sal 21 2 Vizinho Pimenta 140 1 Big Sell Pimenta 130 2 Vizinho Salsa 78 Se os nomes de vendedor são únicos, (VENDEDOR#; NOME VENDEDOR) e (VENDEDOR#; PRODUTO) são chaves candidatas, e VENDEDOR# e NOME VENDEDOR são determinantes que não são chaves candidatas • 1M (um para muitos) • MM (muitos para muitos) • 11 (um para um) RELAÇÃO 1M RELAÇÃO MM chave estrangeira PRODUTOS LIVROS PAÍSES ID NOME PAÍS Tapete 5 1 Tapete 2 1 Abacate 6 22 Caril 1 3 Lâmpada 10 50 NUM 1 3 41 22 50 NOME CAPITAL Paquistão Islamabad Índia Nova Deli Burkina Faso Uagadugu Guatemala Guatemala Polónia Varsóvia ID AUTOR 5 13 2 13 ID NUM NOME AUTORIA ID NUM Mist. Estrada de Sintra 6 62 Mist. Estrada de Sintra 62 47 Anna Karenina SELEÇÃO PROJEÇÃO PRODUTO CARTESIANO JUNÇÃO JUNÇÃO NATURAL 31 62 47 NOME PAÍS Salman Rushdie Reino Unido Eça Portugal R. Ortigão Portugal Tolstoi Rússia PROTEGIDOS ANIMAIS PAÍS ID NOME 5 Cão 24 Panda 56 Lobo 9 Leão Operadores • • • • • Versículos Satânicos NUM 13 RELAÇÃO 11 AUTORES TÍTULO TÍTULO O Chão que Ela Pisa 31 6 RELAÇÃO MM LIVROS AUTORES ESPÉCIE Canis Canis Panda Panda Canis Lupus Felix Leo NUM EXTINTO LEI DL 4249B 24 N DL 4399A 56 N Muitas relações 11 podem ser condensadas numa só tabela SELEÇÃO • • • • Denota-se por σcond(R) onde R é uma relação; e cond é a condição de seleção. O resultado da seleção é uma relação com os mesmos atributos que R mas cujos tuplos verificam a condição indicada. SELEÇÃO PROJEÇÃO • Denota-se por πlista_de_atributos(R). • O resultado é uma relação onde se encontram apenas os atributos da lista indicada, sendo retirados os tuplos idênticos, para que o resultado seja uma relação. S = σB=2(R) R A 5 2 6 1 B 2 3 2 4 C B1 B2 B2 B2 A 5 6 B 2 2 C B1 B2 PRODUTO CARTESIANO PROJEÇÃO • Denota-se por R X S X … X T. • O resultado é uma relação U com aridade igual à soma das aridades das relações argumento, cujos atributos formam os atributos de U. Os tuplos são as combinações possíveis de tuplos dos argumentos. S = πA,B(R) R A 5 2 5 2 B 2 3 2 3 C G 9 T 2 A 5 2 B 2 3 PRODUTO CARTESIANO R A 2 4 B 3 5 A 4 3 C K Y S U=RXS R.A 2 2 4 4 B 3 3 5 5 S.A 4 3 4 3 C K Y K Y JUNÇÃO • Denota-se por R Xθ S. • É um produto cartesiano seguido de uma seleção com condição θ. JUNÇÃO NATURAL JUNÇÃO R A 2 4 B 3 5 A 4 3 C 2 4 S T = R XB>C S R.A 2 4 4 B 3 5 5 S.A 4 4 3 JUNÇÃO NATURAL R A 2 4 B 3 5 A 4 3 C 2 4 T = R |X| S A 4 B 5 S SQL C 2 C 2 2 4 • Denota-se por R |X| S. • É uma junção com o operador “=”, seguida de uma projeção dos atributos distintos da relação resultado. SQL-Strutured Query Language • linguagem padrão para aceder e manipular BD relacionais: SQL permite estruturar, manipular, inserir, questionar • definida pelo ANSI (American National Standards Institute) • SQL usada em muitos ambientes (SGBD) diferentes, como MS Access, MS SQL Server, Oracle, Sybase, DB2, Informix, etc. SQL – Data Definition Language • SQL Data Definition Language (DDL) – – – – – CREATE TABLE ALTER TABLE DROP TABLE CREATE INDEX DROP INDEX • SQL Data Manipulation Language (DML) – – – – UPDATE DELETE INSERT INTO SELECT CREATE TABLE table_name {column1 data_type column2 data_type ... colunmn data_type } exemplo: CREATE TABLE Pinturas {ANO int(4) TITULO varchar(50) AUTOR varchar(50) LOCAL varchar (50)} ALTER TABLE table_name ADD column data_type exemplo: ALTER TABLE Pinturas ADD Materiais varchar(50) SQL – Data Manipulation Language UPDATE table SET column = new_value WHERE column = old_value DELETE FROM table WHERE column = value ex.: UPDATE Sistema_Solar SET type = ‘Planeta anão’ WHERE name = ‘Plutão’ ex.: DELETE FROM Países WHERE nome = ‘Sérvia e Montenegro’ INSERT INTO table VALUES (value1, value2, ..., valuen) ex.: INSERT INTO Pinturas VALUES (‘1507’, ‘Gioconda’, ‘Leonardo da Vinci’, ‘Louvre’, ‘Óleo sobre madeira’) SQL – Interrogação à BD SELECT {DISTINCT | ALL} [ * | [ expr-coluna ]… ] FROM [ tabela(s) ] … { WHERE condição } { GROUP BY [ coluna(s) ] … } { HAVING condição } { ORDER BY [esquema-ordenação] … } SQL – SELECT ... FROM ... SELECT ID, NOME FROM ANIMAIS RESPOSTA ANIMAIS ID NOME 5 Cão 24 Panda 56 Lobo 9 Leão ESPÉCIE Canis Canis Panda Panda Canis Lupus Felix Leo ID NOME 5 Cão 24 Panda 56 Lobo 9 Leão SQL - WHERE SELECT ID, NOME FROM ANIMAIS WHERE ESPÉCIE LIKE “Canis%” ID NOME 5 Cão 24 Panda 56 Lobo 9 Leão SQL – WHERE • A condição presente na cláusula WHERE pode conter os seguintes operadores: =, <, >, <=, >=, <>, AND, OR, NOT, BETWEEN, IN, LIKE, NULL, ANY e ALL RESPOSTA ANIMAIS ESPÉCIE Canis Canis Panda Panda ID NOME 5 Cão 56 Lobo Canis Lupus Felix Leo SQL - WHERE SELECT NOME, ID FROM PRODUTOS WHERE PAÍS IN (SELECT NUM FROM PAÍSES WHERE NOME LIKE “P%”) PRODUTOS RESPOSTA ID NOME PAÍS PAÍSES NUM NOME CAPITAL NOME Tapete Lâmpada Tapete ID 5 10 2 SQL SQL Exemplo 1: Obter os números dos jogadores e os números da liga dos jogadores residentes em Faro: JOGADORES NumJogador Jog1 Nome Jog2 JOGOS AnoNasc NumJogo Categ AnoSocio Resultado Morada Cidade Telef NumLiga Exemplo 2: Obter o número de jogador de quem jogou no dia 21/09/2013: Data MULTAS NumPagamento SELECT NumJogador, NumLiga FROM JOGADORES WHERE Cidade = ‘Faro’ NumJog Data Quantia SQL Exemplo 3: Obter os nomes e cidades dos jogadores que vivam em Tavira, Faro, Elvas ou Tomar: SELECT Nome, Cidade FROM JOGADORES WHERE Cidade IN (‘Tavira’, ‘Faro’, ‘Elvas’, ‘Tomar’) Exemplo 4: Obter o nome e número dos jogadores cujo nome comece por ‘B’ (maiúsculo) ou termine com ‘o’ (minúsculo): A.B denota o atributo B da relação A SELECT Jog1, Jog2 FROM JOGOS WHERE Data = ‘21/09/2013’ SQL Exemplo 5: Obter os nomes e números de liga dos jogadores que tenham número de liga: SELECT Nome, NumLiga FROM JOGADORES WHERE NumLiga IS NOT NULL SELECT Nome, NumJogador FROM JOGADORES WHERE Nome LIKE ‘B%’ OR Nome LIKE ‘%o’ SQL Exemplo 6: Obter os nomes dos jogadores que jogaram pelo menos um jogo: SELECT Nome FROM JOGADORES WHERE NumJogador IN (SELECT Jog1 FROM JOGOS) OR NumJogador IN (SELECT Jog2 FROM JOGOS) SQL Exemplo 7: Obter os nomes dos jogadores mais velhos que Teresa Horta: SELECT Nome FROM JOGADORES WHERE AnoNasc < (SELECT AnoNasc FROM JOGADORES WHERE Nome = ‘Teresa Horta’) Exemplo 8: Descobrir os nomes e ano de nascimento dos jogadores mais velhos: SELECT Nome, AnoNasc FROM JOGADORES WHERE AnoNasc <= ALL (SELECT AnoNasc FROM JOGADORES) SQL SQL – GROUP BY Exemplo 9: Quais os nomes e anos de nascimento dos jogadores que não se encontram entre os mais velhos ? SELECT Nome, AnoNasc FROM JOGADORES WHERE AnoNasc > ANY (SELECT AnoNasc FROM JOGADORES) • Serve para agrupar linhas através de um atributo comum AUTORES NUM 13 NOME PAÍS Salman Rushdie Reino Unido 31 Eça de Queiroz Portugal 62 47 R. Ortigão Portugal Tolstoi Rússia SQL – HAVING NUM 31 62 Exemplo 10: Quais os números dos autores dos países com mais que um autor? 31, 62 47 Exemplo 11: Obter os números dos jogadores dos quais foi recebida em multas a quantia de pelo menos 150: SELECT NumJog FROM MULTAS GROUP BY NumJog HAVING SUM(Quantia) >= 150 Exemplo 12: Quantos jogadores vivem em Évora ? SELECT COUNT(NumJogador) FROM JOGADORES WHERE Cidade = ‘Évora’ SQL Exemplo 13: Número de multas aplicadas SELECT COUNT(*) FROM MULTAS Exemplo 14: Qual o número de cidades em que residem os jogadores ? SELECT COUNT (DISTINCT Cidade) FROM JOGADORES NUM 13 SQL • Serve para indicar condições aos elementos agrupados com GROUP BY SELECT NUM FROM AUTORES GROUP BY PAÍS HAVING COUNT(NOME)>1 SELECT NUM FROM AUTORES GROUP BY PAÍS SQL – ORDER BY • Serve para ordenar os resultados por determinada coluna AUTORES NUM 13 NOME PAÍS Salman Rushdie Reino Unido 31 Eça de Queiroz Portugal 62 R. Ortigão Portugal 47 Tolstoi Rússia SELECT NUM FROM AUTORES ORDER BY NUM NUM 13 31 47 62 SQL Exercício Exemplo 15: Listar por ordem crescente o número de jogador e a data de cada multa aplicada: SELECT NumJog, Data FROM MULTAS ORDER BY NumJog Exemplo 16: Obter para cada jogador que pagou pelo menos uma multa o total pago e ordenar os resultados por esse valor: SELECT NumJog, SUM(Quantia) FROM MULTAS GROUP BY NumJog ORDER BY 2 DESC GRUPO PAÍS Continental Sky Team E.U.A. TAP Star Alliance Portugal Iberia One World Espanha Qantas One World Austrália AeroCondor Portugal Norfolk Air Austrália Lufthansa AERO_ CIDADE DEST _DEST COMPANHIA AERO _ORI MÊS NUM_P ASSAG CIDADE _ORI EWR N.York Continental LPL Fev 2762 Liverpool CO959 ORY Paris Lufthansa EWR Jan 10423 N.York LH4145 LIS Lisboa AeroCondor OPO Jan 9492 Porto LXQ33 LIS Lisboa AeroCondor OPO Fev 9001 Porto LXQ33 POR Porto TAP LIS Fev 1482 Lisboa TP9192 POR Porto Lufthansa FRA Fev 1728 Frankfurt LH4711 ORY Paris Iberia MAD Jan 6167 Madrid IB013 Paris Iberia MAD Fev 8914 Madrid IB013 CDG Paris TAP MAD Mar 34560 Madrid TP512 CDG Paris TAP MAD Fev 30050 Madrid TP512 EWR N.York TAP FNC Jan 4443 Funchal TP888 EWR N.York TAP FNC Fev 2790 Funchal TP888 Bibliografia Ramakrishnan & Gehrke, Database Management Systems, Ed. McGraw-Hill Na Biblioteca: • J.C.Date, An Introduction to Database Systems [D-51] • Ullman & Widom, A First Course in Database Systems [5399 – CMEST] • Date & Darwen, A Guide to the SQL Standard [D-34] • Batini, Ceri & Navethe, Conceptual Database Design [B-85] • Connolly, Begg & Strechan, Database Systems [5123 – CMEST] • Machado & Abreu, Projecto de Banco de Dados [5356 – CMEST] Star Alliance Alemanha Exercício VOO ORY • NOME 1) NORMALIZAR PARA A 3FN 2) QUERIES i. Quais são os voos de companhias portuguesas e quantos passageiros transportou cada um (no total do ano)? ii. Quais as companhias que têm voos em Fevereiro a partir de Paris ou com destino a Paris ? iii. Quais as cidades no Brasil para onde há voos do grupo Star Alliance ? iv. Quais os países onde há cidades com mais de um aeroporto ?
