Bases de données relationnelles et SQL

Bases de données relationnelles et SQL
[email protected]
Département informatique de Supélec
24 juin 2014
Bases de données relationnelles et SQL
Plan
1
Modèle entité-association EA (Entity-Relationship ER)
2
Modèle relationnel
3
Normalisation
4
Passage du modèle ER au modèle relationnel
5
Algèbre relationnelle
6
Langage de requêtes SQL
Bases de données relationnelles et SQL
Modèle entité-association EA : Entité
Repésentation conceptuelle d’une entité concrète ou abstraite du monde réel
décrire les données sans prise en compte des opérations
chaque entité possède une identité et un ensemble d’attributs
chaque attribut, peut être monovalué ou multivalué
décrire la relation is-a entre entités : une entité hérite des attributs de
l’entité qu’elle spécialise
Origines : Charles Bachman (1969), Peter Chen (1976)
Bases de données relationnelles et SQL
Modèle entité-association EA : Entité
Bases de données relationnelles et SQL
Modèle entité-association EA : Association
Exprimer des relations entre n entités (d’arité n ou n-aire)
Cardinalités minimale et maximale portées par chaque extrémité de
l’association
si E (n..m) − (k..l)E 0 alors ∀e de type E , e est relié au minimum à n et
au maximum à m entités de type E 0
Association peut avoir des attributs
Association peut être reflexive
Bases de données relationnelles et SQL
Modèle entité-association EA : Exemples
* désigne 0..n
Bases de données relationnelles et SQL
Modèle relationnel : Définition
Introduite par E. F. Codd
Relation R(A1 , A2 , . . . An ) sur (D 1 , D 2 , . . . D n )
Ai est attribut de domaine D i de valeurs atomiques ai
Intention de R
A = {A1 , A2 , . . . An } et un ensemble de dépendances F défini sur A
Extension de R, Ext(R), est l’ensemble de tuples (ou n-uplets) :
{(a1 , a2 , . . . an ) ∈ (D 1 xD 2 x . . . D n )}
Ex. :
Produit(numRef, designation, prixUnitaire, qteStock),
Client(numClient, nom, prenom, adresse, numTel)
–> une seule valeur de numéro de téléphone par client
Bases de données relationnelles et SQL
Modèle relationnel : Dépendance fonctionnelle
Définition
Exprimer les dépendances entre attributs de relations
Soit R(A), X ⊂ A détermine Y ⊂ A noté X → Y ssi :
∀(aX , aY ), (a0X , a0Y ) aX = a0X → aY = a0Y
aX : suite de valeurs des attributs de X d’un tuple a ∈ Ext(R(A))
Ex. :
numSecu → nom, prenom, adresse
titre, realisateur → dateSortie, nationalite, budget
commune, departement → codePostale
ville → pays ? ?
identifiantBillet → identifiantSiege
Raisonner sur les DFs :
règles d’inférence d’Armstrong (transitivité, augmentation, reflexivité, . . . )
fermeture transitive & couverture minimale de DFs
Bases de données relationnelles et SQL
Modèle relationnel : Dépendance fonctionnelle
DF élémentaire et clés d’une relation
Dépendance fonctionnelle élémentaire X → Y ssi :
6 ∃X 0 ⊂ X , X 0 → Y
Ex. :
salle, creneau, professeur → module, groupe
n’est pas une DFe si salle, creneau → module, groupe
Soit R(A), X ⊂ A est une clé de R ssi :
X → Y est une DFe et Y = A − X
Ex. :
EmploiDutemps(creneau, salle, professeur, groupe, module)
creneau, salle → professeur, groupe, module
creneau, professeur → salle, groupe, module
creneau, groupe → salle, professeur, module
Film(titre, realisateur, dateSortie, nationalite, budget, numExploitation)
titre, realisateur → dateSortie, nationalite, budget, numExploitation
numExploitation → titre, realisateur, dateSortie, nationalite, budget
Super clé si X = A
Bases de données relationnelles et SQL
Normalisation : Objectifs
Construire un schéma relationnel ayant les qualités permettant de :
préserver les dépendances et les données lors des mises à jour
minimiser la redondance
Ex. :
Produit(numRef, designation, prixUnitaire, fournisseurNom,
fournisseurAdresse, qteStock)
numRef→designation, prixUnitaire, fournisseurNom, fournisseurAdresse,
qteStock
fournisseurNom→fournisseurAdresse
Redondance : nom et adresse fournisseur
si mise à jour de l’adresse fournisseur
si un produit est supprimé
si un produit est inséré
Bases de données relationnelles et SQL
Normalisation : 1ère forme normale
Une relation est 1FN ssi toute valeur d’attribut pour un tuple est
atomique
une valeur d’attribut ne peut pas être multivaluée
une valeur d’attribut ne peut pas être composée
Ex. :
Personne(nom, prenom, adresse, numTel)
prenom et numTel ne peuvent pas avoir plusieurs valeurs
adresse ne peut être décomposée en numéro, rue, code postale, commune,
ville
Par définition dans le modèle relationnel
Bases de données relationnelles et SQL
Normalisation : 2ème forme normale
Soit R(A, F ), X est une clé de R, Y = A − X , R(A, F ) est 2FN ssi :
6 ∃X 0 ⊂ X et 6 ∃Y 0 ⊂ Y X 0 → Y 0 ∈ F
les attributs non-clés pleinement dépendants de cette clé
Ex. :
EmploiDutemps(creneau, salle, professeur, groupe, module)
creneau, professeur → salle, groupe, module
et si professeur → module
module dépend partiellement de la clé (creneau, professeur)
Bases de données relationnelles et SQL
Normalisation : 3ème forme normale
Soit R(A, F ), X est une clé de R, Y = A − X , est 3FN ssi :
R(A, F ) est en 2FN
6 ∃Y 0 , Y 00 ⊂ Y Y 0 → Y 00 ∈ F
attributs non-clés pleinement, directement dépendants de cette clé
Ex. :
EmploiDutemps(creneau, salle, professeur, groupe, module)
creneau, salle → professeur, groupe, module
et si professeur → module
module dépend transitivement de la clé (creneau, salle)
Fonctionnaire(numen, nom, categorie, classe, salaire)
numen → nom, categorie, classe, salaire
categorie, classe → salaire
salaire est transitivement dépendant de numen
Algorithmes de décomposition en relations : 1 relation constituée que
des attributs clés et non clés respectant 2FN, 3FN
Bases de données relationnelles et SQL
Passage du modèle ER au modèle relationnel : Règles
Tout attribut multivalué de E devient une entité E 0 en association
E 0 (0..1) − ∗E ou E 0 ∗ − ∗ E
Toute entité devient une relation dont les attributs sont ceux de l’entité
Toute association reliant E à E 0 devient une relation qui possède :
les attributs de la clé de E et de la clé de E 0
les attributs de l’association
la clé de la nouvelle relation contient au moins les clés de E et E 0
Cas association sans attributs E (0..1) − ∗E 0
ajouter les attributs de E 0 dans E
Cas is-a
ajouter à toute entité les attributs clé de l’entité dont elle hérite,
ces attributs sont également une clé de l’entité
Bases de données relationnelles et SQL
Passage du modèle ER au modèle relationnel : Exemples
Client(numClient, nom, prenom, adresse)
Commande(numCommande, date, montant, # numClient)
Produit(numRef, designation, prixUnitaire, qteStock, # nom)
LigneCommande( # numRef, # numCommande, qte)
Fournisseur(nom, adresse)
Telephone(numero, #numClient)
Bases de données relationnelles et SQL
Passage du modèle ER au modèle relationnel : Exemples
Personne(nom, prenom) ; estParent(# (nom, prenom),# (nom, prenom))
Sport(nomSport) ; JeuxOlympiques(date, ville) ; Sportif (nom, nationalite) ;
Figuration (# nom, #date, #nomSport, resultat)
Bases de données relationnelles et SQL
Algèbre relationnelle : Opérateurs algébriques
Produit cartésien de deux relations R(C ) = Ri (A) × Rj (B)
Ext(R) = {(t A , t B )/t A ∈ Ext(Ri ), t B ∈ Ext(Rj ) }
C = A ∪ B, |C | = |A| + |B|
Sélection d’une relation R 0 (C ) = σ(R(C ), δ), δ est une expression
booléenne
Ext(R 0 ) = {(t/t ∈ Ext(R), δ(t) }
Projection d’une relation R 0 (B) = π(R(C ), B)
B ⊆ C , Ext(R 0 ) = {t B /t ∈ Ext(R)}
Union
R(C ) = Ri (C ) ∪ Rj (C ), Ext(R) = Ext(Ri ) ∪ Ext(Rj )
Différence
R(C ) = Ri (C ) − Rj (C ), Ext(R) = Ext(Ri ) − Ext(Rj )
Bases de données relationnelles et SQL
Algèbre relationnelle : Exemple
piece
ecrou
ecrou
boulon
piece
ecrou
ecrou
boulon
-
R
fournisseur
Pierre
Paul
Alice
R
fournisseur
Pierre
Paul
Alice
-
S
fournisseur
Pierre
Alice
Pierre
-
piece
ecrou
boulon
boulon
V
piece
ecrou
boulon
boulon
-
projet
a
b
c
-
R ∪S
R −S
fournisseur
piece
fournisseur
Pierre
ecrou
Paul
Pierre
Paul
Alice
R ×V
fournisseur V.piece
projet
Pierre
ecrou
a
Pierre
boulon
b
Pierre
boulon
c
Paul
ecrou
a
Paul
boulon
b
Paul
boulon
c
Alice
ecrou
a
Alice
boulon
b
Alice
boulon
c
piece
ecrou
boulon
ecrou
boulon
R.piece
ecrou
ecrou
ecrou
ecrou
ecrou
ecrou
boulon
boulon
boulon
Bases de données relationnelles et SQL
Algèbre relationnelle : Exemple
piece
ecrou
ecrou
vis
boulon
R
fournisseur
Pierre
Paul
Paul
Alice
projet
a
a
a
c
π(R, {fournisseur , projet})
fournisseur
projet
Pierre
a
Paul
a
Alice
c
-
σ(R, (fournisseur =0 Paul 0 ))
piece
fournisseur projet
ecrou
Paul
a
vis
Paul
a
-
Combiner les opérateurs algébriques : expression de requêtes complexes
Exploiter leurs propriétés : algorithmes de réécriture et d’optimisation de
requêtes.
Bases de données relationnelles et SQL
Langage de requêtes SQL : Petite introduction
Base de données (BdD) : ensemble structuré de données stockées dans
un support persistant en vue de son utilisation (en local ou à distance)
par des programmes.
Système de Gestion de Bases de Données (SGBD) gère le stockage et
l’accès à une BdD
Système de Gestion de Bases de Données Relationnel (SGBDR)
implémente le modèle relationnel
Sybase, Oracle, SQLServer, DB2, . . .
MySQL, PostgreSQL , OpenIngres , . . .
SQL ("Structured Query Language") est le langage de requêtes
SQL-1 en 1989, SQL-2 en 1992, SQL-3 en 1999
Définition de schéma relationnel (LDD), manipulation de données (LMD),
de contrôle des accès aux données (LCD)
Bases de données relationnelles et SQL
Langage de requêtes SQL : Définition de schéma
relationnel
Création et suppression de tables
create table t (
col sqlType default value,
col sqlType [constraint c] primary key || unique || not null ||
check(condition) || references t (col) [on delete cascade],
[constraint c] primary key || unique (col[, ...]) || check(condition)
foreign key(col[, ...]) [on delete cascade], [...] )
drop table t [cascade constraints]
Notation :
t, col et c sont resp. le nom d’une table, colonne et contrainte
sqlType : integer, varchar, date, text, ...
[] : optionnel, || : ou
Bases de données relationnelles et SQL
Langage de requêtes SQL : Exemple de la vidéothèque
http ://wwwdi.supelec.fr/liesse/html/index.html
create table Film ( idFilm integer primary key, titre_VO text, titre_VF text,
annee integer, pays text) ;
create table Personne ( idPersonne integer primary key, nom text) ;
create table Acteur ( idPersonne integer, idFilm integer,
constraint Fk1Acteur foreign key(idPersonne) references
Personne(idPersonne),
constraint Fk2Acteur foreign key(idFilm) references Film(idFilm),
constraint UnActeur unique(idFilm, idPersonne)) ;
create table Realisateur ( idPersonne integer, idFilm integer,
constraint Fk1Realisateur foreign key(idPersonne) references
Personne(idPersonne),
constraint Fk2Realisateur foreign key(idFilm) references Film(idFilm),
constraint UnRealisateur unique(idFilm, idPersonne)) ;
Bases de données relationnelles et SQL
Langage de requêtes SQL : Exemple de la vidéothèque
create table Exemplaire ( numInventaire integer primary key, idFilm integer,
format varchar(8),
constraint FkExemplaire foreign key(idFilm) references Film(idFilm)
constraint CkExemplaire check(format in (’DVD’, ’Blu-ray’))) ;
create table Client ( numClient integer primary key, prenom text, nom text,
ville text, credit numeric, bonus numeric) ;
create table Emprunte ( numClient integer, numInventaire integer,
dateEmprunt Date, constraint Fk1Emprunte foreign key(numClient)
references Client(numClient),
constraint Fk2Emprunte foreign key(numInventaire) references
Exemplaire(numInventaire),
constraint PkEmprunte primary key(numInventaire, numClient,
dateEmprunt) ) ;
Bases de données relationnelles et SQL
Langage de requêtes SQL : Altérer un schéma existant
alter table t add/modify (col sqlType[,. . . ]) ;
alter table t add constraint c primary key || unique (col[, ...]) ;
alter table t add constraint c foreign key (col[, ...]) references
t(col[, ...]) ;
alter table t drop constraint c [cascade] ;
alter table t drop primary key||unique(col[, ...]) [cascade] ;
alter table t enable || disable c [cascade] ;
alter table t modify constraint c disable ;
alter table t1 rename to t2 ;
rename constraint c1 to c2 ;
Bases de données relationnelles et SQL
Langage de requêtes SQL : Mise à jour des données
insert into t values( val[, . . . ]) ;
insert into t (col[, . . . .]) values( val[, . . . ]) ;
insert into t [(col[, . . . ])] select . . . ;
update t set col = expression [, . . . .] [where condition] ;
update t set (col[,. . . ]) = select . . . [where condition] ;
delete from t [where condition] ;
Bases de données relationnelles et SQL
Langage de requêtes SQL : Exercice
Insérer les films suivants :
- L’Associé du diable, The Devil’s Advocate, 1998, USA, réalisé par
Taylor Hackford avec acteurs principaux Al Pacino, Keanu Reeves,
Charlize Theron.
- Scarface, 1983, USA, réalisé par Brian De Palma avec acteurs
principaux Al Paccino, Michelle Pfeiffer, Steven Bauer
- Into the Wild, 2007, USA, réalisé par Sean Penn avec acteur principal
Emile Hirsch
Bases de données relationnelles et SQL
Langage de requêtes SQL : Sélection de données
select [distinct] col [, . . . .] || * from t [, ...] [where condition]
[order by col [, . . . .] ] ;
par défaut, s’affiche à l’écran,
peut servir pour une autre clause from, ou clause where (sous-requête)
select col [, . . . .] from t [, . . . .][ where condition]
union || intersect || minus
select col [, . . . .] from t [, . . . .][ where condition]
[order by col [, . . . .] ] ;
Bases de données relationnelles et SQL
Langage de requêtes SQL : Sélection de données
Condition de la clause where :
exp > || >= || < || <= || = || <> || ! = exp
exp [not] between exp and exp
exp [not] in (valeur,[...])
exp [not] like chaîne (méta-caractères comme _ et %)
attribut is [not] null
exp : attribut, constante numérique ou alphanumérique ou date, fonctions
prédéfinies ou une expression numérique utilisant +, ∗, −, /
Condition composée : not, or, and.
Bases de données relationnelles et SQL
Langage de requêtes SQL : Exercice
Donner les films plus récents que 2008
Donner la liste des films de nationalité française et dont la date de sortie
est entre 1960 et 2000
Donner la liste des clients dont la ville est Gif-sur-Yvette et dont le
bonus est entre 0 et 1
Donner les identifiants des personnes acteurs et réalisateurs
Donner les identifiants des films et des personnes réalisant ou jouant
dans ces films ordonnées par film
Bases de données relationnelles et SQL
Langage de requêtes SQL : Produit cartésien et jointures
Produit cartésien
select [distinct] col [, ...] from t, t’ [, ...] ;
ou
select [distinct] col [, ...] from t cross join t’[cross join ...] ;
Jointure
select [distinct] col [, ...] from t, t’[...] where conditionJointure ;
ou
select [distinct] col [, ...] from t inner join t’ on (conditionJointure)
[inner join ...] ;
Jointure naturelle
select [distinct] col [, ...] from t natural join t’ [natural join ...] ;
Bases de données relationnelles et SQL
Langage de requêtes SQL : Jointure externe
Jointure externe gauche
select [distinct] col [, ...] from t left outer join t’ on
(conditionJointure) [left outer join ...] ;
Jointure externe droite
select [distinct] col [, ...] from t right outer join t’ on
(conditionJointure) [right outer join ...] ;
Jointure externe complète
select [distinct] col [, ...] from t full outer join t’ on
(conditionJointure) [full outer join ...] ;
Bases de données relationnelles et SQL
Langage de requêtes SQL : Exercice
Quels sont les films dans lesquels joue "Belmondo" ?
Quels sont les réalisateurs du film "Gran Torino" ?
Quels sont les films où "Clint Eastwood" est à la fois réalisateur et
acteur ?
Quels sont les films où jouent Alain Delon et Simone Signoret ?
Quels sont les exemplaires de films de Claude Chabrol actuellement
disponibles en location ?
Bases de données relationnelles et SQL
Langage de requêtes SQL : Sous-requêtes
Requête dont le résultat sert de valeur de référence dans le prédicat de la
requête principale.
where (exp[, ...]) operateur (select col[, . . . ])
où (exp[, ...]) doit correpondre à col[, . . . ]
opérateur : =, ! =, <>, <, >, <=, >=
suivi de all ou any.
= any est équivalent à in
! = all est équivalent à not in
where exists select . . . renvoie vrai si le select renvoie au moins une
ligne sinon faux
Bases de données relationnelles et SQL
Langage de requêtes SQL : Exercice
Reprendre les requêtes précédentes et utiliser les sous-requêtes si
possible.
Bases de données relationnelles et SQL
requêtes de groupage
Grouper les lignes ayant les mêmes valeurs sur des attributs sélectionnés
select [distinct] col [, ...] ou * from t [, ...] [where condition]
[group by col [, . . . ] ]
[having condition]
[order by col [, . . . ] [asc || desc]]
[Limit n] ;
Une seule ligne par groupe
Les colonnes projectées doivent faire partie des colonnes du group by
Les colonnes projetées peuvent être des fonctions d’agrégats appliquées
aux autres attributs
count (*), count(distinct col), sum(), avg()
La condition du having s’applique aux groupes
Bases de données relationnelles et SQL
Langage de requêtes SQL : Exercice
Donner le nombre de films par nationalité.
Donner le nombre de films par nationalité et par année
Donner le nombre d’exemplaires par format
Donner les 10 Films possédant le plus d’exemplaires
Donner la liste des villes par ordre décroissant en termes de nombre de
clients
Bases de données relationnelles et SQL