Aula01 - Modelo Relacional

(1)

UNIVERSIDADE GREGÓRIO SEMEDO

Faculdade de Engenharia e Novas Tecnologias

Base de Dados II

(2)

www.sousavarela.co.cc

Objectivos

• História e terminologia.

• COMO as tabelas são usadas para REPRESENTAR dados.

• Relações matemáticas versus relações do modelo relacional.

• Propriedades das relações de bases de dados.

• COMO identificar chaves candidatas, primárias e estrangeiras.

• Integridade existencial e integridade referencial.

• COMO formular consultas em álgebra relacional.

• Critérios de avaliação de DBMS relacionais

(3)

Breve História do Modelo Relacional (MR)

E.F. Codd

(1970)

• ‘A relational model of data for large shared data banks’. Communications of the ACM, 13(6), pp 377-387.

Objectivos

• Permitir um grau elevado de independência dos dados.

• Fornecer mecanismos para lidar com a semântica, consistência e redundância dos

dados.

• Permitir a expansão de DMLs orientadas por (teoria) conjuntos.

Desenvolvimento e Comercialização

• Sistema R (IBM, California, finais da década de 70): protótipo

• DBMS relacional:

desenvolvimento da SQL

comercialização DBMSs relacionais na década de 80 (DB2 e SQL/DS da IBM;

ORACLE da ORACLE Corporation)

(4)

Identificação de Entidades por Abstracção

Entidade ?

Abstracção dum conjunto de objectos que possuem características/atributos comuns.

Atributo ?

Característica comum aos objectos abstraídos por uma entidade.

Boxer Pastor Alemão Dobermann Fox-Terrier Porsche Renault Peugeot Toyota Página 4

CÃO

CARRO

(5)

• Cada coluna representa um atributo.

• Cada linha representa uma instância (ou ocorrência) da entidade CÃO.

Representação de Entidades por Tabelas

CÃO

nome raca sexo dnasc

Yankee boxer M Jan 85

Rocky pastor F Mai 98

(6)

A entidade CÃO vista pela administração municipal

• Agora, a entidade CÃO tem mais atributos.

• E temos mais uma entidade DONO que de algum modo está relacionada com a

entidade CÃO.

Página 6

CÃO

n_licenca nome raca sexo nm_dono d_lic

DONO

(7)

A entidade CÃO vista pela clínica veterinária

• Agora, embora a entidade CÃO tenha o mesmo número de atributos relativamente

à base de dados da edilidade, os seus atributos são diferentes.

• A entidade DONO tem mais um atributo que a mesma entidade da base de dados

da edilidade.

CÃO

nome raca sexo peso nm_dono d_nasc

DONO

(8)

Teorias ?

Teoria dos Conjuntos + Lógica de Predicados.

Conceito matemático fundamental ?

Relação.

Conceito análogo em bases de dados ?

Tabela.

BRANCH

(agência duma empresa imobiliária)

Página 8

Matemática do Modelo Relacional

Bno Street Area City Postcode Tel_No Fax_No

B7 16 Argyll St Dyce Aberdeen AB2 3SU 01224-67125 01224-67111

B5 22 Deer Rd Sidcup London SW1 4EH 0171-886212 0171-886214

(9)

Relações (em matemática)

Relação ?

Qualquer subconjunto dum produto cartesiano.

Exemplo:

Conjuntos (ou Domínios de Atributos):

A = {1,2}B = {3,4,5}

Produto Cartesiano:

A×B = {(1,3),(1,4),(1,5),(2,3),(2,4),(2,5)}

Relação (exemplo):

(10)

Página 10

Relação ?

R é um conjunto de n-tuplas (A

₁

:d

₁

, A

₂

:d

₂

,…,A

_n

:d

_n

)

com d

₁

∈

_D

1

, d

2

∈

D

2

,…, d

n

∈

D

n

.

Exemplo:

Conjuntos (ou Domínios de Atributos):

Bno, Street, Area, City, Postcode, Tel_No, Fax_No

Produto Cartesiano:

Bno × Street × Area × City × Postcode × Tel_No × Fax_No

Relação (exemplo):com um só 7-tupla

{(B5, 22 Deer Rd, Sidcup, London, SW1 4EH, 0171-886-1212, 0171-886-1214)}

(11)

Propriedades de Relações

• Cada relação tem um nome diferente.

• Cada valor dum atributo é atómico (1NF).

• Cada atributo tem um nome distinto.

• Os valores dum atributo pertencem todos ao mesmo domínio.

• A ordem dos atributos é irrelevante.

• Cada tupla é distinto.

• A ordem dos tuplas é irrelevante.

(12)

Grau ? G

É o número de atributos duma relação.

Cardinalidade ? C

É o número de tuplas duma relação.

Exemplo:

BRANCH

(agência duma empresa imobiliária)

Página 12

Grau e Cardinalidade de uma Relação

Bno Street Area City Pcode Tel_No Fax_No

B7 16 Argyll St Dyce Aberdeen AB2 3SU 01224-67125 01224-67111 B5 22 Deer Rd Sidcup London SW1 4EH 0171-886212 0171-886214 B3 163 Main St Partick Glasgow G11 9QX 0141-339178 0141-339439

G = 7

C

=

(13)

Super-Chave ?

É um atributo ou conjunto de atributos que identificamen unicamente uma tupla duma relação.

Chave Candidata ?

É uma super-chave,

tal que nenhum subconjunto próprio é uma super-chave.

Exemplo: BRANCH

London tem 2 agências

Cada agência tem um identificador único

Chaves Relacionais

UNICIDADE

IRREDUTABILIDADE

B7 16 Argyll St Dyce Aberdeen AB2 3SU 01224-67125 01224-67111

(14)

Chave Primaria ?

É a chave primária que é seleccionada para identificar as tuplas duma relação.

Chave Alternatica?

É uma chave candidata que não é seleccionada como chave primária.

Exemplo: BRANCH

Chave primária: Bno

Chaves alternativas: Tel_No, Fax_No

BRANCH

(

agência duma empresa imobiliária

)

Página 14

Chaves Relacionais (

cont

. 1)

B7 16 Argyll St Dyce Aberdeen AB2 3SU 01224-67125 01224-67111 B5 22 Deer Rd Sidcup London SW1 4EH 0171-886212 0171-886214 B3 163 Main St Partick Glasgow G11 9QX 0141-339178 0141-339439 B4 56 Clover Dr London NW10 6BJ 0181-963108 0181-453799

(15)

Chave Estrangeira?

É um atributo ou conjunto de atributos duma relação que é chave candidata de alguma (possivelmente a mesma) relação.

Exemplo: STAFF

Chave estrangeira: Bno

Chaves Relacionais (

cont

. 2)

Sno FName LName Address Tel_No Position Sex DOB Salary NIN Bno

SL21 SG37 SG14 SA9 John Ann David Mary White Beech Ford Howe

19 Taylor St, Cranford, London 81 George St, Glasgow PA1 2JR 63 Ashby St, Partick, Glasgow G11 2 Elm Pl, Aberdeen AB2 3SU

0171-884-5112 0141-848-3345 0141-339-2177 Manager Snr Asst Deputy Assistant M F M F 10/1/45 11/10/60 3/24/58 2/19/70 30000 12000 18000 9000 WK442011B WL432514C WL220658D WM532187D B5 B3 B3 B7

(16)

Todos os DADOS, assim como as inter-RELAÇÕES existentes entre os dados,

são representados por um conjunto de tabelas (relações) relacionadas entre

si:

• Cada tabela tem um nome único pelo qual é referenciada. Este nome identifica uma classe de entidades.

• Cada coluna tem um nome que se refere a um atributo das entidades duma classe.

• Cada coluna contém valores atómicos do domínio do atributo.

• Cada linha (tupla) representa uma entidade única ou uma inter- relação entre entidades.

• Cada relação contém zero ou mais tuplas.

• Não pode haver campos ou células vazias. Se o valor dum campo é desconhecido (ou não é aplicável) esse campo é preenchido com um valor nulo.

Página 16

(17)

ALUNO

(duma universidade)

Nome Numero disciplina

José Silva 10567 Física, Química, Bases de dados

Maria Cebola 8723 AMI, Álgebra

Nome Numero disciplina

José Silva 10567 Física

José Silva 10567 Química

José Silva 10567 Bases de dados

Maria Cebola 8723 AMI

ALUNO

(duma universidade)

INCORRECTO

CORRECTO

Atomicidade: exemplo da base de dados duma

Universidade

(18)

Há dois tipos de integridade relacional:

• Integridade existencial (entity integrity)

Nenhum atributo duma chave primária pode ser NULL

• Integridade referencial

se uma chave estrangeira (CE) existe na relação,

então

o valor da CE tem de ser obrigatoriamente um valor da chave primária existente numa outra relação

senão

o valor da CE é obrigatoriamente NULL, o que significa que não referencia nenhum valor da chave primária duma outra tabela.

• NULL:

Representa ausência de valor.

Página 18

(19)

Álgebra relacional

= conjunto de relações + conjunto de operações

Operações fundamentais:

• Selecção • Projecção • Produto Cartesiano • União • Diferença

Operações suplementares:

• Junção • Intersecção • Divisão

Álgebra Relacional

(20)

Consideremos mais as seguintes relações/tabelas da nossa empresa

imobiliária:

Página 20

Pno Street Area City PostCode Type NoRooms Rent Ono

P14 Rua dos Moleiros 2 Norte Viseu 3520 Apartamento 2 35000 O46 P94 Rua das Carmelitas 45 Alta Covilha 6200 Casa 1 25000 O87 P4 Bairro dos Olhos, Lote 3, 4EB Verde Golega 2350 Apartamento 2 30000 O40 P36 Quinta do Lago, Mansao Eulalia Golfe Vale de Lobo 4242 Mansao 10 300000 O93 P21 Rua das Flores 18 Baixa Coimbra 3000 Casa 5 50000 O87 P16 Rua do Imaginario 3, 5 Esq Sul Regabofe 1212 Atelier 1 20000 O93

Ono FName Lname Street Tel

O46 Joao Calisto Rua da Ferrajota 2, 7300 PORTALEGRE 054-34567 O87 Pedro Vasconcelos Rua do Arco 23, 1000 LISBOA 01-319786 O40 Cristina Alvito Alameda das Torres Novas 100, 2320 ENTRONCAMENTO 049-717988 O93 Maria Calas Beco da Boavista 5, 5100 NAZARE 040-45567

Rno FName Lname Street Tel Type MaxRent

R76 Joao Fagundes Rotunda da Alegria 12, 4500 TOMAR 038-89700 Apartamento 70000 R56 Alda Segundo Avenida das Antas 345, 1200 LISBOA 01-450678 Estudio 50000 R74 Miguel Andrade Bairro das Mesuras 45, 3500 VISEU 032-422775 Casa 60000 R62 Mariana Tavora Largo da Independencia 42, 3100 TONDELA 032-31234 Duplex 100000

PROPERTY_FOR_RENT

OWNER

RENTER

(21)

σ

_predicado

(R)

Define uma relação que contém só as tuplas (linhas) de R que satisfazem uma dada condição (predicado).

Exemplo:

σ

_{Salary > 10000}

(STAFF)

Liste todo o pessoal com salário superior a 10000€.

SQL:

SELECT

*

FROM

STAFF

(22)

π

_col

1 ,…,col

n

(R)

Define uma relação que contém um subconjunto vertical de R que extrai os valores dos atributos especificados, eliminando duplicados.

Exemplo:

π _{sno,FName,lname,salary}(STAFF)

Produz uma lista de salários de todo o pessoal, mostrando somente os dados dos

atributos especificados, nomeadamente Sno,FName, LName e Salary.

SQL:

SELECT Sno, FName, LName, Salary

FROM STAFF

Página 22

(23)

R×S

Define uma relação que resulta da concatenação de cada tupla de R com todos as tuplas de S.

Exemplo:

(π

rno,fname,lname

(RENTER)) × (π

rno,pno,comment

(VIEWING))

Produz uma lista de todos os nomes e todos comentários de todos os inquilinos que tenham visto uma propriedade.

SQL:

SELECT RENTER.Rno, FName, LName, VIEWING.Rno, Pno, Comment

(24)

R ∪ S

A união das relações R e S com i,j tuplas, respectivamente, é uma relação obtida pela concatenação dos (i+j) tuplas, com eliminação dos tuplas repetidos.

NOTA_{: R e S são obrigatoriamente compatíveis (union-compatible).}

Exemplo:

π

area (BRANCH) ∪

π

area (PROPERTY_FOR_RENT)

Construa uma lista de todas as areas onde existe ou uma agência (branch) ou uma propriedade para arrendar (property).

SQL:

SELECT Area FROM BRANCH UNION SELECT Area FROM PROPERTY_FOR_RENT Página 24

União

(25)

R - S

A diferença entre as relações R e S com i, j tuplas, respectivamente, é uma relação que contém as tuplas de R que não pertencem a S.

NOTA_{: R e S são obrigatoriamente compatíveis (union-compatible).}

Exemplo:

π

city

(BRANCH) -

π

city

(PROPERTY_FOR_RENT)

Construa uma lista de todas as cidades (cities) onde existe ou uma agência (branch) mas nenhuma propriedade para arrendar (property).

SQL:

SELECT City

FROM

BRANCH

MINUS

SELECT

City

Diferença

(26)

R

_P

S

Uma θ-junção define uma relação a partir do produto cartesiano de R e S que contém os tuplas que satisfazem o predicado P.

NOTA:O predicado P é da forma R.ai θ S.bi, onde θ é um dos operadores

relacionais {<, <=, >, >=, =, ~=}.

Exemplo:

π

Rno,FName,Lname (RENTER) Renter.Rno=Viewing.Rno

π

Rno,Pno,Comment

(VIEWING)

Construa uma lista dos nomes e comentários de todos os arrendatários que já visitaram uma propriedade.

SQL:

SELECT

RENTER.Rno,Fname,Lname,VIEWING.Rno,Pno,Comment

FROM

RENTER, VIEWING

WHERE

RENTER.Rno= VIEWING.Rno

Página 26

(27)

R

S

A junção natural é uma =-junção de R e S sobre todos os atributos comuns. A ocorrência de um atributo comum é eliminada do resultado.

Exemplo:

π

Rno,FName,LName(RENTER)

π

Rno,Pno,Comment(VIEWING)

Construa uma lista dos nomes e comentários de todos os arrendatários que já visitaram uma propriedade.

SQL:

SELECT

RENTER.Rno,FName,LName, VIEWING.Rno,Pno,Comment

FROM

RENTER

,

VIEWING

(28)

R

S

A junção exterior (à esquerda) é uma junção na qual as tuplas de R que não encontram correspondência nas colunas comuns de S também são incluídos na relação resultante.

NOTA_{: Os valores em falta na segunda relação são colocados a NULL.}

Exemplo:

π

pno,street,city (PROPERTY_FOR_RENT) VIEWING

Produza um relatório sobre as visitas a propriedades.

SQL:

SELECT

PROPERTY_FOR_RENT.Pno,Street,City,Comment

FROM

PROPERTY_FOR_RENT

LEFT JOIN

VIEWING

ON

PROPERTY_FOR_RENT.Pno = VIEWING.Pno

Página 28

(29)

R ∩ S = R - (R - S)

É o conjunto das tuplas que pertencem simultaneamente a R e S.

NOTA_{: R e S devem ser compatíveis (union-compatible).}

Exemplo:

π

fname,lname,salary > 10000 (STAFF) ∩

π

fname,lname,salary < 20000 (STAFF)

Produza uma lista dos funcionários com salários compreendidos entre 10000 e 20000 exclusivé.

SQL:

SELECT

FName, LName,Salary

FROM

STAFF

(30)

R ÷ S

É o conjunto das tuplas de R definidas sobre os atributos C que têm correspondência para qualquer tupla de S.

NOTA_{: C = A - B,}

A é o conjunto de atributos sobre o qual R é definida, B é o conjunto de atributos sobre o qual S é definida.

Exemplo:

π

rno,pno (VIEWING) ÷

π

pno (

σ

rooms=3 (PROPERTY_FOR_RENT))

Identifique todos os arrendatários que visitaram todas as propriedades com 3 quartos.