DCC011 IntroduçãoaBancodeDadosIntrodução a …homepages.dcc.ufmg.br/~mirella/DCC011/aula11.pdf · relacional, SQL. •ProjetodebancosdedadosProjeto de bancos de dados ... Resumo

CC0DCC011Introdução a Banco de DadosIntrodução a Banco de Dados

Álgebra RelacionalMirella M. Moro

Departamento de Ciência da ComputaçãoUniversidade Federal de Minas Gerais

[email protected]

Programa

• Introdução• Conceitos básicos, características da abordagem de banco de dados, modelos de dados, esquemas e instâncias, arquitetura de um sistema de banco de dados, componentes de um sistema de gerência , p gde banco de dados.

• Modelos de dados e linguagens• Modelo entidade‐relacionamento (ER), modelo relacional, álgebra relacional, SQL.

• Projeto de bancos de dados• Projeto de bancos de dados• Fases do projeto de bancos de dados, projeto lógico de bancos de dados relacionais, normalização.

• Novas Tecnologias e Aplicações de Banco de Dados

DCC011 ‐ profa. Mirella M. Moro 2

Modelos de Dados

•Modelo Conceitual• Modelo Entidade‐Relacionamento

•Modelo Lógicog• Modelo Relacional

• TODOS OS DADOS SÃO RELAÇÕES (“tabelas”)i ál b ífi i l d d d l õ• Possui uma álgebra específica para manipular os dados das relações

• Álgebra Relacional

•Modelo FísicoModelo Físico


Álgebra Relacional1. Introdução2 Problemas2. Problemas3. Seleção4. Projeção5 S i5. Sequencia6. Conjunto7. Produto Cartesiano8. Junções

Introdução

• Linguagens de consultas: Permitem manipulação e g g p çrecuperação de dados de um BD

•O modelo relacional suporta LCs simples e O ode o e ac o a supo ta Cs s p es epoderosas:

• Forte fundamentação teórica baseada em lógica• Permite otimizações

• Ling. de consulta ling. de programação• LCs não tem a intenção de suportar cálculos complexos• LCs suportam acesso fácil e eficiente a grandes conjuntos d d dde dados

Database Management Systems, R. Ramakrishnan(tradução, autorizada, de Anna & Mario Nascimento)


LCs relacionais formais

Duas LCs matemáticas formam a base para as LCs p“reais” (p.ex., SQL), e p/ implementação:

1. Álgebra relacional: Predominantemente geb a e ac o a edo a te e teoperacional, útil para representar planos de execução

2. Cálculo Relacional : Permite usuários descreverm o que querem, ao invés de como querem (não operacional, declarativa)Entender álgebra e cálculo é uma chave para entender SQL e processamento de consultas.



Consultas

Uma consulta é aplicada para instâncias de relação, e p p ç ,o resultado de uma consulta é também uma instância de relação

• Esquemas de consumo relações para uma consulta são fixadas (mas consultas rodarão independente de exemplos!)exemplos!)

• O esquema para o resultado de uma propensa consulta é também fixada! Determinada por definição de construção de linguagem de consulta



Álgebra Relacional: Resumo

• Dadas duas relações R1 e R2• Operações Básicas

• Selection () Seleciona em sub‐conjunto de tuplas da relação• Projection () Apaga colunas indesejadas da relação• Cross‐product () Permite combinar R1 e R2• Set difference ( ) Tuplas em R1 mas não em R2• Set‐difference () Tuplas em R1, mas não em R2• Union () Tuplas em R1 e em R2

• Operações Adicionais:p ç• Interseção, junção, divisão, renomear

Desde que cada operação retorna uma relação, operações podem ser compostas !



ALUNOSMatr Nome Sexo Cr Cod Nome Depto Coord

CURSOSMatr Nome Sexo Cr1 A F CC

Cod Nome Depto CoordCC Ciência da

Computação DCC RGMatemática2 B M CC

3 C M CCMC Matemática

Computacional DCC TNSI Sistemas de

Informação DCC CDJ4 D F MC5 E M MC

ECA Engenharia de Controle e Automação

ENG XYZ

MATRICULAS5 E M MC6 F M SI Matr Disc T Sem

MATRICULAS

7 G F SI8 H F SI

1 DCC011 Z 201621 DCC851 A 20162

9 I M SI10 J M ECA

1 DCC834 A 201612 DCC011 Z 2016110 J M ECA 2 DCC011 Z 20161... ... ... ...

9DCC011 ‐ profa. Mirella M. Moro

ProblemasALUNOS

1. (ALUNOS)

2 (ALUNOS)2. (Matr=4) (ALUNOS)

3. (Matr>4 AND Sexo = F) OR (Cr = MC) (ALUNOS)

4 (ALUNOS)4. Matr (ALUNOS)5. Matr, Nome, Sexo, Cr (ALUNOS)6. Sexo, Cr (ALUNOS)

7. Nome (Matr=4) (ALUNOS) [outras opções próximo slide]

(ALUNOS) [ t õ ó i lid ]8. Nome(Matr>4 AND Sexo = F) OR (Cr = MC) (ALUNOS) [outras opções próximo slide]


Em Resumo(lembrar que podem ser compostas)

SELEÇÃO •Seleciona linhas de uma

PROJEÇÃO•Projeta colunas de umaSeleciona linhas de uma

relação de acordo com a condição lógica

Projeta colunas de uma relação de acordo com uma lista especificadaç g

especificada•Comando

p•Comandoπ (Relação)Comando

σ condiçãoLógica (Relação)π coluna (Relação)π col1,col2,etc (Relação)

<atributo > <operador> <valor><atributo> <operador> <atributo>operador: <, =, >, <>, <=, >=,AND, OR, XOR, NOT


Detalhes ImportantesALUNOS

7. Nome (Matr=4) (ALUNOS)7a (M t 4) N (ALUNOS)7 . (Matr=4) Nome (ALUNOS)7b. Nome (Matr=4) Nome,Matr (ALUNOS)

8. Nome(Matr>4 AND Sexo = F) OR (Cr = MC) (ALUNOS)( ) ( )

8a. (Matr>4 AND Sexo = F) OR (Cr = MC) Nome(ALUNOS)8b (ALUNOS)8b. Nome(Matr>4 AND Sexo = F)OR(Cr = MC)(ALUNOS)8c. Nome(Matr>4) (Sexo = F) (Cr = MC) (ALUNOS)8d. Nome(Matr>4) AND (Sexo = F) AND (Cr = MC) (ALUNOS)


ProblemasALUNOS

1. Nome das meninas de todos os cursosN (S F) (ALUNOS)Nome (Sexo=F) (ALUNOS)

2. Nome das meninas da ComputaçãoNome (Sexo=F AND Cr=CC) (ALUNOS)

3. Nome e curso de todas as meninasNome, CR (Sexo=F) (ALUNOS)

4 Quantidade de meninas por curso4. Quantidade de meninas por curso

5. Curso com número máximo de meninas


Mais ProblemasALUNOS

1. Nomes de todos os alunos acompanhados dos nomes de seus cursos

2. Nomes de todos os alunos acompanhados das disciplinas matriculadas neste semestre


Mais Problemas

1. Nomes de todos os alunos acompanhados dos nomes de seus cursospa. Nome,Nome (ALUNOS, CURSOS)b. ALUNOS Nome CURSOS Nome (ALUNOS, CURSOS)ALUNOS.Nome,CURSOS.Nome ( , )c. ALUNOS.Nome,CURSOS.Nome (ALUNOS CURSOS)


ALUNOS.Nome,CURSOS.Nome (ALUNOS CURSOS)

O l d d d i éO resultado do produto cartesiano éMatr Nome Sexo Cr Cod Nome Depto Coord1 A F CC CC Ciência da

Computação DCC RG1 A F CC MC Matemática

Computacional DCC TN1 A F CC SI Sistemas de

Informação DCC CDJ1 A F CC ECA Engenharia de

Controle e Automação ENG XYZCiência daQ l é 2 B M CC CC Ciência da

Computação DCC RG2 B M CC MC Matemática

Computacional DCC TN2 B M CC SI Sistemas de DCC CDJ

Qual é o problema

???? 2 B M CC SI Informação DCC CDJ2 B M CC ECA Engenharia de

Controle e Automação ENG XYZ3 C M CC CC Ciência da

C ã DCC RG

agora????

3 C M CC CC Computação DCC RG3 C M CC MC Matemática

Computacional DCC TN... ... ... ... ... ... ... ...


ALUNOS.Nome,CURSOS.Nome (ALUNOS CURSOS)

O l d d d i éO resultado do produto cartesiano éMatr Nome Sexo Cr Cod Nome Depto Coord1 A F CC CC Ciência da

Computação DCC RG1 A F CC MC Matemática

Computacional DCC TN1 A F CC SI Sistemas de

Informação DCC CDJ1 A F CC ECA Engenharia de

Controle e Automação ENG XYZCiência dal d 2 B M CC CC Ciência da

Computação DCC RG2 B M CC MC Matemática

Computacional DCC TN2 B M CC SI Sistemas de DCC CDJ

Resolvendo

2 B M CC SI Informação DCC CDJ2 B M CC ECA Engenharia de

Controle e Automação ENG XYZ3 C M CC CC Ciência da

C ã DCC RG3 C M CC CC Computação DCC RG3 C M CC MC Matemática

Computacional DCC TN... ... ... ... ... ... ... ...


ALUNOS.Nome,CURSOS.Nome (ALUNOS.Cr=CURSOS.Cod)

(ALUNOS CURSOS)(ALUNOS CURSOS)

Matr Nome Sexo Cr Cod Nome Depto Coord1 A F CC CC Ciência da

Computação DCC RG2 B M CC CC Ciência da

Computação DCC RG3 C M CC CC Ciência da

Computação DCC RG4 D F MC MC Matemática

Computacional DCC TNMatemátical d 5 E M MC MC Matemática

Computacional DCC TN6 F M SI MC Matemática

Computacional DCC TN7 G F SI SI Sistemas de DCC CDJ

Resolvendo

7 G F SI SI Informação DCC CDJ8 H F SI SI Sistemas de

Informação DCC CDJ9 I M SI SI Sistemas de

I f ã DCC CDJ9 I M SI SI Informação DCC CDJ10 J M ECA ECA Engenharia de

Controle e Automação ENG XYZ


Mais Problemas

2. Nomes de todos os alunos acompanhados das disciplinas matriculadas neste semestre a. Nome Disc (ALUNOS MATRICULAS)a. Nome,Disc (ALUNOS MATRICULAS)b. Nome,Disc (ALUNOS.Matr=MATRICULAS.Matr)

(ALUNOS MATRICULAS)(ALUNOS MATRICULAS)c. Nome,Disc (ALUNOS MATRICULAS)


Nome Disc (ALUNOS MATRICULAS)Nome,Disc ( )

Matr Nome Sexo Cr Disc T Sem1 A F CC DCC011 Z 201621 A F CC DCC851 A 201621 A F CC DCC834 A 201612 B M CC DCC011 Z 20161... ... ... ... ... ... ...

JUNÇÃO NATURALIgualdade de todos os pares de atributos de mesmo nome (domínio) - Igualdade de todos os pares de atributos de mesmo nome (domínio)

- Os atributos de mesmo nome aparecem uma vez só no resultado


Produto Cartesiano, Junção Natural EquiJunçãoJunção Natural, EquiJunção

ALUNOS (Matr, Nome, Sexo, Cr)CURSOS (Cod, Nome, Depto, Coord) MATRICULAS (Matr, Disc, T, Sem)

O Oa. ALUNOS CURSOSb. ALUNOS CURSOSc. ALUNOS MATRICULASd CURSOS MATRICULASd. CURSOS MATRICULASe. ALUNOS Matr MATRICULASMatr

f. ALUNOS Cr=Cod CURSOS


Série de exemplos dos livrosSérie de exemplos dos livros


Esquema utilizado pelo livro – INGLÊS

EMPLOYEE (ssn, fname, lname, address,bdate, superssn, dno)superssn REFERENCIA EMPLOYEEdno REFERENCIA DEPARTMENT

DEPARTAMENT (dnum, dname, mgrssn, mgrinitialdate)mgrssn REFERENCIA EMPLOYEE.ssn

PROJECT (pnumber, pname, plocation, dnum)dnum REFERENCIA DEPARTAMENT

DEPT_LOCATIONS (dnumber,dlocation)dnumber REFERENCIA DEPARTAMENT

WORKS_ON (ssn, pnumber, hours)ssn REFERENCIA EMPLOYEEpnumber REFERENCIA PROJECT


Operação de Seleção

(DNO = 4 AND SALARY>25000) OR (DNO=5) (EMPLOYEE)


Operação de SeleçãoOperação de Seleção

Base de Dados:

??????????


Operação de Projeção

•Projeta as tuplas de uma relação sobre um•Projeta as tuplas de uma relação sobre um determinado conjunto de atributos

•Notação:•Notação: <atributos> (<nome da relação>)

•Exemplos:•Exemplos:• LNAME,FNAME,SALARY (EMPLOYEE)• (EMPLOYEE)SEX,SALARY (EMPLOYEE)



LNAME,FNAME,SALARY (EMPLOYEE) SEX,SALARY (EMPLOYEE)



Base de Dados:???

??????


Sequência de Operações

•Várias operações podem ser combinadas para•Várias operações podem ser combinadas para formar uma expressão da álgebra relacional

• ( (EMPLOYEE))• FNAME,LNAME,SALARY (DNO = 5 (EMPLOYEE))



P d ifi l õ t á i lt Pode-se especificar relações temporárias e alterar o nome dos atributos da relação resultante:

TEMP (EMPLOYEE) TEMP DNO=5 (EMPLOYEE) R(FIRSTNAME, LASTNAME, SALARY) FNAME,LNAME,SALARY (TEMP)

R



Base de Dados:


Operações de ConjuntoProduto CartesianoProduto Cartesiano

•Cada linha de S1 é combinada com cada linha de R1.

• Esquema resultante tem um campo por campo S1 e R1, com nomes campos ‘herdados’ se possível.



Operações de ConjuntoProduto CartesianoProduto Cartesiano

C fli A b S1 R1Base de Dados: •Conflito: Ambos S1 e R1 têm um campo chamado sid

Base de Dados:

sid.



Operações de Conjunto

•Produto Cartesiano: combina as tuplas de duas relações.•Notação: R S•Exemplo:Exemplo:

FEMALE_EMPS SEX = ‘F’ (EMPLOYEE)

EMPNAMES (FEMALE EMPS)EMPNAMES FNAME, LNAME, SSN (FEMALE_EMPS)


Produto CartesianoEMP_DEPENDENTS EMPNAMES DEPENDENT

ACTUAL DEPENDENTS SSN ESSN (EMP DEPENDENTS)ACTUAL_DEPENDENTS SSN = ESSN (EMP_DEPENDENTS)

RESULT (ACTUAL DEPENDENTS)RESULT FNAME,LNAME,DEPENDENT_NAME (ACTUAL_DEPENDENTS)


Operação de Junção

•Combina as tuplas de duas relações que satisfazem uma•Combina as tuplas de duas relações que satisfazem uma determinada condição

•Permite processar relacionamentos entre relações•Permite processar relacionamentos entre relações•Notação: R <cond> S•A condição de junção é geralmente da forma

<cond1> AND <cond2> AND ... AND <condn>,onde <condi> é uma expressão A B, sendo A um atributo de R, B um atributo de S e um dos operadores { ≤ ≥ }{=,<, ≤,>, ≥, ≠}


Operação de JunçãoBase de Dados:




•Exemplo:p• DEPT_MGR DEPARTMENT MGRSSN=SSN EMPLOYEE

• Uma operação de junção que envolva apenas condições deUma operação de junção que envolva apenas condições de igualdade é chamada de eqüi‐junção

• Uma junção natural é uma eqüi‐junção na qual o segundo atributo de cada condição de igualdade é eliminado da relação resultante (uma junção natural é geralmente definida sobre atributos de mesmo nome)mesmo nome)

• Notação: R S


Exemplos de Junção Natural

• DEPT(DNAME,DNUM,MGRSSN,MGRSTARTDATE) DEPARTMENT(DNAME,DNUM,MGRSSN,MGRSTARTDATE)

PROJ_DEPT PROJECT DEPT

• DEPT LOCS DEPARTMENT DEPT LOCATIONSDEPT_LOCS DEPARTMENT DEPT_LOCATIONS


Exemplos de Junção Base de Dados:Natural


Outras Operações de ConjuntoOutras Operações de Conjunto



•União: Efetua a união de duas relações compatíveis•União: Efetua a união de duas relações compatíveis•Notação: R S

•Diferença: Efetua a diferença entre duas relações compatíveis•Notação: R S

• Interseção: Efetua a interseção de duas relações ç ç çcompatíveis•Notação: R Sç


Operações de ConjuntoOperações de Conjunto

D l õ R(A1 A2 A ) S(B1 B2 B ) ã ( iã )• Duas relações R(A1,A2,..., An) e S(B1,B2,..., Bn) são (união) compatíveis quando tiverem o mesmo grau e dom(Ai) = dom(Bi)( )

• Exemplo:• RESULT1 SSN ( DNO = 5 (EMPLOYEE))• RESULT2(SSN) SUPERSSN (DNO = 5 (EMPLOYEE))• RESULT RESULT1 RESULT2

Figura 6.3 Resultado da operação UNIÃO, RESULTADO ¬ RESULTADO1 U RESULTADO2.



Duas relações compatíveis

STUDENT INSTRUCTORSTUDENT INSTRUCTORSTUDENT INSTRUCTOR

STUDENT - INSTRUCTOR

INSTRUCTOR - STUDENTFigura 6.4 As operações de conjunto UNIÃO, INTERSEÇÃO e SUBTRAÇÃO.

(a) Duas relações união compatíveis(a) Duas relações união compatíveis.(b) ALUNO U INSTRUTOR. (c) ALUNO INSTRUTOR.(d) ALUNO INSTRUTOR. (e) INSTRUTOR ALUNO.



Base de Dados:


Exercícios

Autor (CodAutor, NomeAutor, CodEndereco, CodInst)(CodEndereco) referencia Endereco(CodEndereco) referencia Endereco(CodInst) referencia Instituicao

Artigo (CodArtigo, Titulo, AnoPublicacao)AutorArtigo (CodAutor, CodArtigo)

(CodAutor) referencia Autor(CodArtigo) referencia Artigo(CodArtigo) referencia Artigo

Instituicao (CodInst, NomeInst, CodEndereco)(CodEndereco) referencia Endereco

Endereco (CodEndereco, Rua, Numero, Bairro, Cidade, Estado, Pais, Cep)

1 Obter os títulos dos artigos seguidos do nome seus autores1. Obter os títulos dos artigos seguidos do nome seus autores.2. Obter os nomes dos autores que publicaram artigos em 1998 e 1999.


Documents

DCC011 IntroduçãoaBancodeDadosIntrodução a …homepages.dcc.ufmg.br/~mirella/DCC011/aula11.pdf · relacional, SQL. •ProjetodebancosdedadosProjeto de bancos de dados ... Resumo