SGBDR et Le langage SQL
H. MACHHOUR
Introduction générale
Aperçu sur le modèle relationnel Introduction au langage SQL SQL un langage de manipulation des données SQL un langage d’interrogation des données SQL un langage de définition de données Objectifs attendus: Implanter, consulter, interroger, mettre à jour une BDR, consolider les connaissances en SQL. Installer un SGBDR et administrer une BDR
23/10/2009
Language SQL
2
Le langage SQL Cours1: Modèle relationnel H. MACHHOUR
Plan
Introduction générale Présentation Eléments du modèle relationnel Algèbre relationnelle Exercices et TP
23/10/2009
Language SQL
4
Introduction
SQL
= Structured Query Language
Langage d'interrogation structuré
langage complet de gestion de bases de données relationnelles
Une base de données relationnelle Composée de relations (ou Tables) Structurée suivant les principes de l’algèbre relationnelle.
SQL est une implémentation de ces principes D’où l’importance de savoir quelques notions de l’algèbre/modèle relationnel
23/10/2009
Language SQL
5
Présentation du modèle relationnel
Basé sur le concept de relation Formulé par Edgar Frank Codd 1970, (IBM) « Chaque phénomène peut être décrit par une relation. Un phénomène d'un type déterminé est un nn-uplet (collection des valeurs qui permet de représenter un fait) de la relation ». Au départ, l’idée de de stocker des données hétérogènes dans des tables, puis établir des relations entre elles, était une «curiosité «curiosité intellectuelle » Premier prototype de Système de gestion de bases de données relationnelles (SGBDR) établie par Codd Depuis les années 80, cette technologie a été adoptée par l’industrie. En 1987, le langage SQL, qui étend l’algèbre relationnelle, a été standardisé
23/10/2009
Language SQL
6
Présentation du modèle relationnel
Vue utilisateur :
une relation est un tableau. Un nn-uplet est une ligne du tableau. Une base de données est un ensemble de tableau
Les tableaux sont manipulables par des langages non procéduraux (SQL)
23/10/2009
Language SQL
7
Présentation du modèle relationnel
Les objectifs : proposer des schémas de données faciles à utiliser ; mettre à la disposition des utilisateurs des langages de haut niveau ; optimiser les accès à la base de données ; améliorer l’intégrité et la confidentialité ; fournir une approche méthodologique dans la construction des schémas.
23/10/2009
Language SQL
8
Présentation du modèle relationnel
Modèle relationnel : les données sont organisées sous forme de tables à deux dimensions, encore appelées relations relations,, dont les lignes sont appelées n-uplet ou tuple en anglais ; les données sont manipulées par des opérateurs de l’algèbre relationnelle ; l’état cohérent de la base est défini par un ensemble de contraintes d’intégrité. d’intégrité.
23/10/2009
Language SQL
9
Éléments du modèle relationnel
Attribut-- Un attribut est un identificateur (un nom) Attribut décrivant une information stockée dans une base. Exemples d’attribut : l’âge d’une personne, le nom d’une personne, le numéro de sécurité sociale. En SQL en parle de colonne
Attributs
23/10/2009
Language SQL
10
Éléments du modèle relationnel
Domaine-- Le domaine d’un attribut est l’ensemble, Domaine fini ou infini, de ses valeurs possibles. Exemple: l’attribut numéro de sécurité sociale a pour domaine l’ensemble des combinaisons de quinze chiffres, nom a pour domaine l’ensemble des combinaisons de lettres (chaîne de caractères).
Domaines
~ En SQL c’est le type de données 23/10/2009
Language SQL
11
Éléments du modèle relationnel
Relation-- Une relation est un sousRelation sous-ensemble du produit cartésien de n domaines d’attributs (n > 0). Une relation est représentée sous la forme d’un tableau à deux dimensions dans lequel les n attributs correspondent aux titres des n colonnes.
Relation ou table
23/10/2009
Language SQL
12
Éléments du modèle relationnel
Schéma de relationrelation- Un schéma de relation précise le nom de la relation ainsi que la liste des attributs avec leurs domaines. Relation
Schéma de la relation 23/10/2009
Language SQL
13
Éléments du modèle relationnel
Degré- Le degré d’une relation est son nombre d’attributs. DegréOccurrence ou nn-uplets ou tuplestuples- Une occurrence, ou nn-uplets, ou
tuples, est un élément de l’ensemble figuré par une relation. Autrement dit, une occurrence est une ligne du tableau qui représente la relation. Cardinalité-- La cardinalité d’une relation est son nombre d’occurrences. Cardinalité
Degré=3, cardinalité=4
23/10/2009
Language SQL
14
Éléments du modèle relationnel
Clé candidatecandidate- Une clé candidate d’une relation est un ensemble minimal des attributs de la relation dont les valeurs identifient à coup sûr une occurrence.
La valeur d’une clé candidate est distincte pour toutes les tuples de la relation. La notion de clé candidate est essentielle dans le modèle relationnel.
Règle : Toute relation a au moins une clé candidate et peut en avoir plusieurs.
il ne peut jamais y avoir deux tuples identiques au sein d’une relation. Les clés candidates d’une relation n’ont pas forcément le même nombre d’attributs. Une clé candidate peut être formée d’un attribut arbitraire, utilisé à cette seule fin.
23/10/2009
Language SQL
15
Éléments du modèle relationnel
Clé primaireprimaire- La clé primaire d’une relation est une de ses clés candidates. Pour signaler la clé primaire, ses attributs sont généralement soulignés. Clé étrangèreétrangère- Une clé étrangère dans une relation est formée d’un ou plusieurs attributs qui constituent une clé primaire dans une autre relation. Schéma relationnelrelationnel- Un schéma relationnel est constitué par l’ensemble des schémas de relation. Base de données relationnellerelationnelle- Une base de données relationnelle est constituée par l’ensemble des nn-uplets des différentes relations du schéma relationnel. Clé primaire
Clé étrangère
Schéma relationnel 23/10/2009
Language SQL
16
Algèbre relationnelle
Objectifs
Approche opérationnelle, pas mathématique Décrire les opérations d’interrogation de DB bien formuler ses « requêtes » en SQL
On peut distinguer trois familles d’opérateurs relationnels :
Les opérateurs unaires (Sélection, Projection) :
Les opérateurs binaires ensemblistes (Union, Intersection Différence) :
produire une nouvelle relation à partir de deux relations de même degré et de même domaine.
Les opérateurs binaires ou nn-aires (Produit cartésien, Jointure, Division) :
plus simples, produire une nouvelle table à partir d’une autre table.
produire une nouvelle table à partir de deux ou plusieurs autres tables.
Les notations ne sont pas standardisées en algèbre relationnelle
23/10/2009
Language SQL
17
Algèbre relationnelle
Sélection : La sélection (parfois appelée restriction)
génère une relation regroupant exclusivement toutes les occurrences de la relation R qui satisfont l’expression logique E, Notation: σ(E)R ou encore RESTRICT(R/E). ?
Relation T= RESTRICT(Personne/ RESTRICT(Personne/Numéro≥5 Numéro≥5 ).
23/10/2009
Language SQL
18
Algèbre relationnelle
Projection: La projection consiste à supprimer les attributs autres que A1, … An d’une relation et à éliminer les nn--uplets en double apparaissant dans la nouvelle relation Notation: Π(A1, … An)R ou encore PROJECT(R/A1,A2,..,An)
?
Relation T= PROJECT(Personne/ PROJECT(Personne/Nom Nom).).
23/10/2009
Language SQL
19
Algèbre relationnelle
Union-- L’union est une opération portant sur deux Union
relations R1 et R2 ayant le même schéma et construisant une troisième relation constituée des nn-uplets appartenant à chacune des deux relations R1 et R2 sans doublon Notation: R= R1 U R2 ou R=UNION(R1,R2) ?
Relation R= UNION(R1,R2). 23/10/2009
Language SQL
20
Algèbre relationnelle
Intersection-- L’intersection est une opération portant sur Intersection
deux relations R1 et R2 ayant le même schéma et construisant une troisième relation dont les nn-uplets sont constitués de ceux appartenant aux deux relations. Notation: R= R1 ∩ R2 ou R=INTERSECT(R1,R2)
?
Relation R= INTERSECT (R1,R2). 23/10/2009
Language SQL
21
Algèbre relationnelle
Différence-- La différence est une opération portant sur deux Différence
relations R1 et R2 ayant le même schéma et construisant une troisième relation dont les nn-uplets sont constitués de ceux ne se trouvant que dans la relation R1. R= R1 - R2 ou R=MINUS(R1,R2)
?
Relation R= MINUS(R1,R2). 23/10/2009
Language SQL
22
Algèbre relationnelle
Produit cartésiencartésien- Le produit cartésien est une opération
portant sur deux relations R1 et R2 et qui construit une troisième relation regroupant exclusivement toutes les possibilités de combinaison des occurrences des relations R1 et R2. R= R1 x R2 ou R=PRODUCT(R1,R2)
?
Relation R= PRODUCT(R1,R2). 23/10/2009
Language SQL
23
Algèbre relationnelle
Jointure-- La jointure est une opération portant sur deux relations R1 et R2 qui construit Jointure
une troisième relation regroupant exclusivement toutes les possibilités de combinaison des occurrences des relations R1 et R2 qui satisfont l’expression logique E. Notation: R= R1 ∞E R2 ou encore JOIN(R1,R2). La jointure n’est rien d’autre qu’un produit cartésien suivi d’une sélection : R1 ∞E R2 = σE (R1 × R2)
?
Relation R= = Famille ∞ ((Age ≤ AgeC) et 23/10/2009
Language SQL
(Prix < 50))
Cadeau 24
Algèbre relationnelle
Equi--jointure Equi jointure-- Une equiequi-jointure est une jointure dans laquelle l’expression logique E
est un test d’égalité entre un attribut A1 de la relation R1 et un attribut A2 de la relation R2. L’equiL’equi-jointure est notée R1 ∞A1=A2 R2 = σA1=A2 (R1 × R2) Jointure naturellenaturelle- Une jointure naturelle est une jointure dans laquelle l’expression logique E est un test d’égalité entre les attributs qui portent le même nom dans les relations R1 et R2.
Dans la relation construite, ces attributs ne sont pas dupliqués mais fusionnés en une seule colonne par couple d’attributs. La jointure naturelle est notée R1 ∞ R2. R2. On peut préciser explicitement les attributs communs à R1 et R2 sur lesquels porte la jointure : R1 ∞A1, …, An R2
R = Famille ∞ Cadeau ou R = Famille ∞Age Cadeau 23/10/2009
Language SQL
25
Algèbre relationnelle
Division-- La division est une opération portant sur deux relations R1 et R2, telles que le Division schéma de R2 est strictement inclus dans celui de R1, qui génère une troisième relation regroupant toutes les parties d’occurrences de la relation R1 qui sont associées à toutes les occurrences de la relation R2 . R= R1 ÷ R2 ou R=DIVISION(R1,R2) R1 ÷ R2 génère une relation qui regroupe tous les nn-uplets qui, concaténés à chacun des nn-uplets de R2, donne toujours un nn-uplet de R1.
?
Relation R=DIVISION(R1,R2) 23/10/2009
Language SQL
26
Exercice et TP
23/10/2009
Language SQL
27
Exercice et TP Soit les Relations précedentes : PIECE ={ NOP NOP,, DESIGNATION, COULEUR, POIDS } SERVICE = {NOS {NOS,, INTITULE, LOCALISATION} ORDRE = {NOP {NOP,, NOS NOS,, QUANTITE} NOMENCLATURE = {NOPA {NOPA,, NOPC NOPC,, QUANTITE} QUANTITE}
Donner la formule et le résultat des requêtes suivantes : 1. Afficher les localisations des services (projection de service sur localisation). 2. afficher les services ayons pour localisation CASA 3. Désignation et couleur de toutes les pièces. 4. Extraire toutes les pièces ayons un poids entre 0 et 40. 5. Extraire toutes les pièces ayons un poids entre 0 et 40, et une couleur ivoire. 6. les commandes (ordres) ayons une quantité >= 10. 7. Extraire toutes les nomenclatures ayons NOPA ou NOPC égale à P6. 8. Désignation des pièces de couleur ivoire 9. Intitulé des services ayant en commande la pièce P2 10. Intitulé des services n’ayant pas en commande la pièce P1 11. Désignation et quantité des pièces commandées par le service S2.
23/10/2009
Language SQL
28