notes - uOttawa

Bases de Données II
(Automne 2009)
Professeur: Iluju Kiringa
[email protected]
http://www.site.uottawa.ca/~kiringa
SITE 5072
1
Revue du Cours « Bases de Données I »
Chapitres 2-5
2
Définition d’un SGBD -- Rappel


Une base de données centralisée est une très large
collection de données intégrées.
Les organisations font face à de larges quantités de
données qui doivent être gérées efficacement.
 Bien d’enterprises entreposent des GBs, voire des TBs de
données
 Quelques applications scientifiques entreposent des PBs de
données

Un système de gestion des bases de données (SGBD) est
un ensemble de logiciels conçus pour stocker et gérer
des bases de données.
3
Table des Matières de BD I

Fondations des SGBDs (Chap. 2-5, 19): :
 Design conceptuel - Chap. 2:
• Input: Exigences d’une application
• Output: Diagrammes ER (i.e., modèle ER)
 Design logique – Chap. 3:
• Input: Diagrammes ER
• Output: Modèle relationnel
 Normalisation – Chap. 19:
• Input: Modèle relationnel
• Output: Modèle relationnel en forme normales
 Algèbre et calcul relationnels – Chap. 4
 SQL – Chap. 5

Développement des applications utilisant des BDs (Chap. 6-7)
 Applications: SQL imbriqué, JDBC, SQLJ, procédures stockées
 Application Internet: HTTP, HTML, XML, architecture à 3 étages
4
Table des Matières de BD I & BD II

Stockage et indexage (Chap. 8-11):
 Disques et fichiers – Chap. 9:
• Hiérarchie des mémoires
• Gestion des disques et des fichiers sur disque
 Indexes à arbre – Chap. 10:
• Arbres ISAM
• Arbres B+
 Indexes à hachages – Chap. 11:
• Hachage statique
• Hachage extensible
• Hachage linéaire
5
Table des Matières de BD II

Évaluation des requêtes (Chap. 12-15):
 Tri externe - Chap. 13
 Evaluation des opérateurs relationnels -- Chap. 14
 Exemple d'optimisateur de requêtes: Système R – Chap. 15

Traitement des transactions (Chap. 16-18):
 Contrôle de l'accès simultané – Chap. 17
 Reprise – Chap. 18


Design physique (Chap. 20)
Matières avancées (Chap. 22,25, 26, 23 ou 27)





Bases de données parallèles et distribuées – Chap. 22
Bases de données orientées objet – Chap. 23
Entreposage de données et aide à la décision – Chap. 25
Exploration de données Chap. 26
Recherche documentaire et données XML – Chap. 27
6
Survol de la Conception des Bases de Données





Analyse des exigences (prérequis): Trouver ce que les utilisateurs
veulent faire avec la BD.
Design conceptuel: Utilise le résultat de l’AP pour développer une
description (sémantique) de haut niveau pour les données à stocker,
ensemble avec leurs contraintes. Le résultat de cet étape est
habituellement un diagramme ER.
Design logique: Choisir un SGBD et traduire le schéma du design
conceptuel (diagramme ER) dans le modèle des données du SGBD.
Le résultat de cette étape est le schéma logique des données.
décomposition du schéma: Analyse le schéma logique, identifie les
problèmes potentiels et résout ces derniers en décomposant les
schémas logiques à l’aide des formes normales.
Design physique: Considère la charge de travail attendue que la BD
supportera afin de décomposer davantage le design en vue de la
performance désirée.
7
Modèle Entité-Relation

Modèle entité-relation (ER)

Éléments de base:
•
•
•

Éléments avancés:
•
•
•
•

Entité: Objet du monde réel
Relation: Association entre deux ou plusieurs entités
Attributs: Description d’une entité ou d’une relation
Contraintes: Qualification d’une relation (Contrainte de clé vs. participation)
Entités faibles : Identifiables uniquement par la clé primaire d’une autre entité
Hiérarchies ISA : Semblable aux hiérarchies de la conception orientée objet
Agrégation: Agglutination d’un ensemble de relations avec les ensembles
d’entités associés par cette relation
Design conceptuel utilisant le modèle ER:



Devrait-on modeler un concept comme entité ou comme attribut?
Devrait-on modeler un concept comme entité ou comme relation?
Identification des relations: Binaire ou ternaire …? agrégation?
8
Concepts des Bases de Données Relationnelles

Relation: fait de 2 composantes:






Instance : une table, avec lignes et colonnes.
#lignes = cardinalité, #colonnes = degré / arité.
Schéma : spécifie le nom de la relation, plus le nom et le domaine
(type) de chaque colonne (attribut).
Une relation comme est un ensemble de lignes ou uplets
(tuples) (i.e., toutes les lignes sont distinctes); chaque tuple
a la même arité que le schéma de la relation.
Base de données relationnelles: un ensemble de relations,
chacune ayant un nom distinct.
Schéma relationnel d’une BD: ensemble de schémas des
relations dans la BD.
Schéma relationnel d’une instance de la BD: ensemble des
instances relationnelles de la BD.
9
Exemple de Relation
 Schema :
Instance :
Students(sid: string, name: string, login: string,
age: integer, gpa: real).
sid
53666
53688
53650
name
login
Jones jones@cs
Smith smith@eecs
Smith smith@math
age
18
18
19
gpa
3.4
3.2
3.8
Cardinalité = 3, arité = 5, toutes les lignes sont distinctes.
 Les systèmes commerciaux permettent des duplicata.
 Toutes les colonnes d’une instance relationnelle ont-elles à être
distinctes? Dépend de la présence ou non d’un ordre.

10
Création et Altération des Relations
CREATE TABLE Students
(sid: CHAR(20),
name: CHAR(20),
login: CHAR(10),
age: INTEGER,
gpa: REAL)
DROP TABLE Students
CREATE TABLE Enrolled
(sid: CHAR(20),
cid: CHAR(20),
grade: CHAR(2))
ALTER TABLE Students
ADD COLUMN firstYear: integer
INSERT INTO Students (sid, name, login, age, gpa)
VALUES (53688, ‘Smith’, ‘smith@ee’, 18, 3.2)
DELETE FROM Students S
WHERE S.name = ‘Smith’
11
Clé Primaire et Clé Étrangère en SQL
CREATE TABLE Enrolled
(sid CHAR(20)
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid),
UNIQUE (cid, grade) )
CREATE TABLE Enrolled
(sid CHAR(20)
cid CHAR(20),
grade CHAR(2),
PRIMARY KEY (sid,cid) )
CREATE TABLE Enrolled
(sid CHAR(20), cid CHAR(20), grade CHAR(2),
PRIMARY KEY (sid,cid),
FOREIGN KEY (sid) REFERENCES Students )
12
Design Logique: du Modèle ER au Relationnel
Le modèle ER représente le design initial et
« high-level » de la base de données.
 La tâche est de générer un schéma relationnel
qui soit le plus proche possible du modèle ER.
 La traduction est approximative car il est
difficile de traduire toutes les contraintes du
modèle ER en un modèle logique efficient.

13
Langages de Requêtes Relationnels

Deux langages de requêtes à caractère
mathématique forment le soubassement des
langages réels comme SQL et de
l’implémentation de ceux-ci :
 Algèbre relationnelle: Plus opérationnelle, très utile
pour représenter les plans d’exécution.
 Calcul relationnel: Permet aux utilisateurs de
décrire ce qu’ils veulent, plutôt que la manière
dont ce qu’ils veulent doit être calculé. (Non
opérationnel, déclaratif.)
14
Algèbre Relationnelle

Opérations de base:









Opérations additionnelles:



Sélection ( ) Sélectionne un sous-ensemble des lignes d’une
relation.
Projection ( ) Efface des colonnes d’une relation.
Produit Cartésien ( ) Permet de combiner deux relations.
Différence ( ) Contient des tuples de la relation 1, moins ceux
de la relation 2.
Union (  ) Contient les tuples des relations 1 et 2.
Intersection, join, division, « renaming »: Pas essentielles; utiles.
Les opérations retournant une relation, elles peuvent
être composées! (L’algèbre est close.)
15
Joins

Join conditionnel:
(sid)
22
31
sname rating age
dustin 7
45.0
lubber 8
55.5
S1 



R  c S   c ( R  S)
(sid) bid
58
103
58
103
S1. sid  R1. sid
day
11/12/96
11/12/96
R1
Le Schéma du résultat est le même que celui du
produit Cartésien.
Moins de tuples que dans le produit Cartésien;
pourrait être calculé de manière plus efficiente.
Parfois appelé theta-join.
16
Joins (Suite)

Equi-Join: Un cas spécial du join conditionnel où la
condition c contient seulement des égalités.
sid
22
58
sname
dustin
rusty
rating age
7
45.0
10
35.0
S1 


sid
bid
101
103
day
10/10/96
11/12/96
R1
Schéma du résultat similaire au produit Cartésien, mais
contient seulement une copie des attributs pour lesquels
l’égalité est spécifiée.
Join naturel: Equi-join dans lequel il y a égalité entre tous
les attributs ayant le même nom dans les deux relations.
17
Calcul Relationnel

Deux variantes:
 Calcul relationnel des tuples(TRC)
 Calcul relationnel des domaines (DRC).

Contient des variables, constantes, ops de comparaison,
opérateurs logiques, et quantificateurs.




TRC: Le domaine des variables est l’ensemble des tuples.
DRC: Le domaine des variables est le domaine des attributs.
TRC et DRC: souslangages de la logique du premier ordre.
Les expressions du calcul sont appelées des formules.
Une réponse est un assignement de constantes aux
variables qui rend la formule vraie.
18
Calcul Relationnel des Tuples

Forme de la requête:










t| p(t)

La réponse inclut tous les tuples t qui rendent
la formule p(t) vraie.

La formule est définie récursivement, en partant
des formules atomiques et en construisant des
formules de plus en plus grandes au moyen des
opérateurs (connecteurs) logiques.
19
Formules TRC

Formules atomiques:

RRname, R.a op S.b, R.a op constante

op =
, , , ,, 
Formule:
 Formule atomique, ou
  p, p  q, p  q , p et q étant des formules, ou
 R( p(R)), avec la variable R libre dans p(R), ou
 R( p(R)) , avec la variable R libre dans p(R)
 Les quantificateurs R et R lient R.


Une variable non liées est dite libre.
20
Variables Liées vs. Libres

Une restriction importante s’impose sur la
définition d’une requête t| p(t) :




 La variable t qui apparaît à la gauche de `|’ doit être la
seule variable libre dans la formule p(...).
21
Survol des Composantes de SQL

« Data manipulation language »: utilisé pour poser des
requêtes et insérer, effacer ou modifier des lignes.

« Data definition language »: utilisé pour créer, détruire
ou modifier les tables et vues.

Triggers et contraintes d’intégrité avancées: utilisés
pour spécifier des actions que le SGBD exécutera
automatiquement.

SQL incorporé: permet à SQL d’être appelé d’un langage
hôte.

SQL dynamique: permet de créer et d’exécuter des
requêtes pendant l’exécution d’un programme d’application.
22
Survol des Composantes de SQL (Suite)

Exécution client serveur et accès à distance aux BDs:
commandes sur l’accès à un serveur distant.

Gestion des transactions: contrôle l’exécution des
transactions.
Sécurité: contrôle l’accès des utilisateurs au système.
 Divers composantes: orientation objet, récursivité, aide à

la décision, XML, données spatiales, exploration des données
(data mining), etc.
DML, DDL, triggers et ICs seront vu dans ce module.
23
Requête SQL de Base




SELECT
FROM
WHERE
[DISTINCT] target-list
relation-list
qualification
relation-list Une liste des noms de relation (possiblement avec
une variable d’étendue («range-variable») après chaque nom).
target-list Une liste d’attributs des relations dans relation-list
qualification Comparaisons (Attr op const ou Attr1 op Attr2, où
op est une des opérations , ,  , , ,  ) combinées en
utilisant les particules logiques AND, OR et NOT.
DISTINCT est un mot-clé optionnel indiquant que la réponse ne
devrait pas contenir des duplicata. Par défaut les duplicata ne
sont pas éliminés.
24
Exemple d’Évaluation Conceptuelle
SELECT S.sname
FROM Sailors S, Reserves R
WHERE S.sid=R.sid AND R.bid=103
(sid) sname rating age
(sid) bid day
22 dustin
7
45.0
22
101 10/10/96
22 dustin
7
45.0
58
103 11/12/96
31 lubber
8
55.5
22
101 10/10/96
31 lubber
8
55.5
58
103 11/12/96
58 rusty
10
35.0
22
101 10/10/96
58 rusty
10
35.0
58
103 11/12/96
25
Opérateurs d’Agrégat

Ces opérateurs sont une extension
très significative de l’algèbre
relationnelle.
SELECT COUNT (*)
FROM Sailors S
SELECT AVG (S.age)
FROM Sailors S
WHERE S.rating=10
COUNT (*)
COUNT ( [DISTINCT] A)
SUM ( [DISTINCT] A)
AVG ( [DISTINCT] A)
MAX (A)
MIN (A)
Une seule colonne
SELECT S.sname
FROM Sailors S
WHERE S.rating= (SELECT MAX(S2.rating)
FROM Sailors S2)
SELECT COUNT (DISTINCT S.rating)
FROM Sailors S
WHERE S.sname=‘Bob’
SELECT AVG ( DISTINCT S.age)
FROM Sailors S
WHERE S.rating=10
26
GROUP BY et HAVING
SELECT
FROM
WHERE
GROUP BY
HAVING

[DISTINCT] target-list
relation-list
qualification
grouping-list
group-qualification
target-list contient (i) une liste d’attributs et (ii) les termes avec
opérations d’agrégat (p.ex., MIN (S.age)).


La liste d’attributs (i) doit être un sous-ensemble de la liste groupinglist. Intuitivement, chaque tuple de la réponse correspond à un groupe,
et un groupe est un ensemble de tuples qui ont la même valeur pour
tous les attributs dans grouping-list.
Si un attribut de target-list n’est pas dans grouping-list, plusieurs lignes
d’un même groupe peuvent avoir différentes valeurs pour de tels
attributs et il sera difficile de choisir les quelles parmi ces valeurs pour
inclusion dans la réponse de la requête.
27
GROUP BY et HAVING (Suite)
SELECT
FROM
WHERE
GROUP BY
HAVING

[DISTINCT] target-list
relation-list
qualification
grouping-list
group-qualification
group-qualification contient une liste d’attributs.


La liste group-qualification doit contenir un attribut
apparaissant comme argument d’un opérateur d’agrégat
dans target-list. Cela garantit que les expressions qui
apparaissent dans group-qualification auront une même
valeur par groupe.
SQL/99 contient 2 nouvelles fonctions: EVERY et ANY
applicables à chaque tuple dans un groupe. (Leur
sémantique est claire.)
28
Trouver l’age du plus jeune navigateur en age de
voter (i.e. âgé d’au moins 18 ans) pour chaque niveau
ayant au moins 2 tels navigateurs
SELECT S.rating, MIN (S.age)
AS mage
FROM Sailors S
WHERE S.age >= 18
GROUP BY S.rating
HAVING COUNT (*) > 1


Seul S.rating et S.age sont
mentionnés les clauses SELECT,
GROUP BY ou HAVING; d’autres
attributs sont `nonnécessaires’.
La 2ème colonne du résultat
reste sans nom par défaut.
(Utiliser AS pour la nommer.)
sid sname rating age
22 dustin
7
45.0
31 lubber
8
55.5
71 zorba
10 16.0
64 horatio
7
35.0
29 brutus
1
33.0
58 rusty
10 35.0
rating age
1
33.0
rating mage
7
45.0
7
35.0
7
35.0
8
55.5
Réponse
10 35.0
29
Résumé




SQL fut un facteur qui a joué un rôle important dans
l’acceptation rapide du modèle relationnel; de plus, c’est un
standard international.
L’évaluation des requêtes et le traitement des transactions
présupposera SQL ainsi que l’algèbre et le calcul relationnels.
Beaucoup de voies alternatives pour exprimer une requête
existent; un optimisateur cherchera le plan d’évaluation le
plus performant dans la mesure du possible.
Les matières avancées présupposeront aussi le modèle
relationnel et SQL.
30