Download - 1 Bases de données Alexandre Abellard [email protected] IUT Toulon – GEII Semestre 3 / 2008-09
1
Bases de donnéesBases de données
Alexandre AbellardAlexandre [email protected]@univ-tln.frtln.fr
IUT Toulon – IUT Toulon – GEIIGEII
Semestre 3 / Semestre 3 / 2008-092008-09
2
SommaireSommaire
Définitions (Bases De Données, Système de Gestion de Bases de Définitions (Bases De Données, Système de Gestion de Bases de Données)Données)
Classifications des SGBDClassifications des SGBD
Bases de données relationnellesBases de données relationnelles
Modèle Relationnel (Entité – Association)Modèle Relationnel (Entité – Association)
Méthode MERISEMéthode MERISE
Langage SQLLangage SQL
3
DéfinitionDéfinition
Base de donnéesBase de données (BDD ; (BDD ; ang. : databaseang. : database) = ensemble structuré et organisé ) = ensemble structuré et organisé permettant le stockage de données (informations) liées entre elles, afin d'en permettant le stockage de données (informations) liées entre elles, afin d'en faciliter l'exploitation (ajout, mise à jour, recherche de données).faciliter l'exploitation (ajout, mise à jour, recherche de données).
Exemple : données concernant les livres d’une bibliothèque, le personnel d’une entreprise, les comptes clients dans une banque…
Elles sont stockées sur des mémoires secondaires (disques)
Notion apparue en 1964 (BDD informatisée).
4
ObjectifsObjectifs
Mémoriser des donnéesStructurées (livres, personnes, employés, disques…)Documents (texte, images, films …)
Retrouver en ligne la bonne donnée au bon momentLe salaire de Dupont, sa photoL’avoir de votre compte en banqueQuels sont les livres que Gérard Bouchard a emprunté ?
Mettre à jour les données variant dans le temps
Volume de données de plus en plus grandsGiga, Tera, Péta bases (1015 octets)Numériques, Textuelles, Multimédia (images, films,...)De plus en plus de données archivées
5
DonnéesDonnées
Les données sont structurées et identifiées
Données élémentaires ex: Votre salaire, Votre note de BDDDonnées composées ex: Votre CV, vos résultats de l'annéeIdentifiant (n° Sécurité Sociale, n° Machine…)
De plus en plus de données faiblement structuréesTextes libres, documents audios, vidéos, imagesIl faut aussi les stocker et les interroger
Ces données sont stockées, créées, modifiées… via un Ces données sont stockées, créées, modifiées… via un Système de Gestion de Système de Gestion de Bases de DonnéesBases de Données (SGBD ; (SGBD ; ang : database management systemang : database management system). Constitué de ). Constitué de parties matérielles et logicielles. Permet la communication entre l’utilisateur et la parties matérielles et logicielles. Permet la communication entre l’utilisateur et la base proprement dite.base proprement dite.
6
Exemples de SGBD sur Exemples de SGBD sur InternetInternet
E-commerce Films, cinéma
Système d’Information Géographique
Moteur de recherche
Réservation de voyages
Encyclopédie
Ecoute de musique
7
Bases de DonnéesBases de Données
Une BDD doit contenir toutes les données nécessaires à son organisation.
Les données effectives et les programmes qui les gèrent ne sont pas dans le même ensemble logiciel.
Une BDD doit toujours être conçue pour un public particulier et dans un but précis.
L’accent est mis sur des données pertinentes concernant un ou plusieurs objets L’accent est mis sur des données pertinentes concernant un ou plusieurs objets ou ou entitésentités..
8
Un CD peut être caractérisé par :- nom du groupe/de l’artiste- nom de l’album- nombre de titres- durée- maison de disque- distributeur- style- année- société pressage CD- studio d’enregistrement- période d’enregistrement- nom de l’ingénieur du son- numéro du code-barres- etc.
Une BDD ne peut contenir que des informations partielles sur les entités.
Bases de DonnéesBases de Données
On ne sélectionne que des On ne sélectionne que des caractéristiques caractéristiques
pertinentespertinentes pour pour l’utilisateur: l’utilisateur: attributsattributs..
Exemple : Intéressons-nous à une entité « Compact Disc ».
9
Pour un même type d’entité, on choisira des attributs suivant les souhaits de l’utilisateur.
Ex : être humain :
- Nom- Prénom- Date de naissance- Taille- Poids- Mensurations- Diplôme le plus élevé- Expérience professionnelle- Compétences en bases de données- Langages informatiques maîtrisés- Langues étrangères parlées- Connaissances en philosophie
Gestion de personnelSociété
d’informatique
Gestion de personnelAgence de
mannequins
Bases de DonnéesBases de Données
10
Les attributs peuvent avoir des valeurs différentes :
- type : entier, chaîne de caractères, réel, booléen, date
Et des domaines de définition différents : de 2 à n valeurs possibles.
=> Domaine des attributs
Exemple :
Nom : n’importe quelle chaîne de caractères (limitée à 30 lettres)
Date de naissance 01/01/1940 < x < 01/01/1990
Diplôme : « Baccalauréat », « DUT », « BTS », « Licence », « Mastère », « Doctorat »
Salaire : 1000 < y < 10000
Chef d’équipe : Oui, Non
Bases de DonnéesBases de Données
11
Plusieurs types d’utilisateurs peuvent accéder à la BDD :
L’administrateur de la BDDL’administrateur de la BDD
• possède tous les droits (privilèges) sur la BDD = créer, modifier, supprimer, consulter… des données
• peut donner et supprimer des droits aux autres utilisateurs
Les utilisateurs finauxLes utilisateurs finaux
• ceux qui sont devant leur poste de travail, et qui vont émettre des requêtes pour extraire des informations (consulter des données) :
- quels livres d’une bibliothèque parlent des bases de données ?- quels ingénieurs d’une société se sont déjà servis de Access ?- etc.
• peuvent parfois modifier les données du système
Les programmeurs d’applicationsLes programmeurs d’applications
accèdent aux données grâce à des programmes qu’ils développent. Celles-ci permettent d’accéder aux données et de les modifier.
UtilisateursUtilisateurs
12
Système de Gestion Système de Gestion de Bases de Donnéesde Bases de Données
Constitution :
- Partie matérielle : système informatique sur lequel est installée la BDD.
- Partie logicielle : programmes au niveau client (avec différentes possibilités d’afficher, modifier… suivant les droits utilisateurs) ou au niveau serveur (outil de gestion de données)
13
Architecture client/serveur
Serveur
Client(s)
Utilisateur(s)
Requêtes
Réponses
Console
Systèmes de Gestion Systèmes de Gestion de Bases de Donnéesde Bases de Données
14
Trois rôles importants :
• conserver les donnéesconserver les données – elles doivent rester précises, cohérentes et avec le minimum de redondance possible
• fournir l’accès aux donnéesfournir l’accès aux données – en lecture et en écriture, facilement et rapidement (et gérer le fait que plusieurs personnes peuvent en même temps accèder à la BDD).
• maintenir la sécurité des donnéesmaintenir la sécurité des données – niveaux d’autorisation ne donnent que certains droits aux utilisateurs, éviter les modifications simultanées d’une même donnée par plusieurs utilisateurs… (gérer la confidentialité)
Systèmes de Gestion Systèmes de Gestion de Bases de Donnéesde Bases de Données
Avant de programmer, il faut savoir comment ces données vont s ’organiser, comment elles vont être interrogées… ModélisationModélisation
15
Moyen « schématique » d’expliquer la disposition logique des données, et les relations entre les différentes parties qui les composent.
Modèles de donnéesModèles de données
Objectifs
Permettre une meilleure compréhension Le monde réel est trop complexeOmission des détails
Faciliter la visualisation du système- Diagrammes avec notations simples et précises- Compréhension visuelle
Nous étudierons le modèle Nous étudierons le modèle relationnelrelationnel
Permettre une conception progressive
Abstractions et raffinements successifs
Découpage en modules
Génération des structures de données (et de traitements)
16
Organisation d’une BDDRBDDR (ang. : Relational database) sous forme de table.
Une ligne par élément, chaque colinne représentant un attribut.
Nom Prénom Age Provenance
Emploi
On travaille sur un modèle abstrait : on n’a aucune information sur l’implantation physique (concrète) des données. La partie logicielle du SGBD prend en charge cette implantation.
Bases de Données Bases de Données RelationnellesRelationnelles
17
Quand on doit gérer plusieurs tables au sein d’une même BDD, on utilise un langage de définition de données (ang. Data Definition Language, DDL) qui permet de décrire les différents champs de chaque table.
Fournisseurs
Clients Employés
Code Code Code
Nom Nom Nom
Adresse Prénom Prénom
Matériel fourni
Commandes passées
Grade
Paiement dû
Livraison dûe
Salaire
Bases de Données Bases de Données RelationnellesRelationnelles
Remplissage/Mise à jour de la BDD : via un langage de manipulation de données (ang. Data Manipulation Language, DML).
18
Nom Prénom Age Vit à Emploi
Maltese Corto 35 Malte Aventurier
Haddock Archibald 45 Moulinsart Marin
MacLeod Connor 450 Ecosse Escrimeur
Derrick Stefan 60 Allemagne Cascadeur
Simpson Homer 38 Springsfield
Sécu. Centrale nucléaire
Colonnes : Attributs
Lignes :Cardinalité
(ici = 5)
Domaine : valeurs possibles de l’attribut. Noté Dom(Nom), Dom(Prénom)…Exemple Dom(Nom) < 20 caractères ; 18 < Dom(Age) < 500 ; etc.
FICHIER_CLIENTSChaque table a un
nom
Bases de Données Bases de Données RelationnellesRelationnelles
On suppose que chaque case ne comporte qu’au plus une seule valeur.
19
Outil d’analyse permettant de construire des schémas théoriques de raisonnement, afin de montrer les relations qu’il existe entre les différentes entités, combien d’entités entrent en jeu avec une autre… Formalisé en 1976 (P. Chen).
Modèle Entité-AssociationModèle Entité-Association
Les entités regroupent des données (ce sont les propriétés) afin de former des types d'objets.
Les associations définissent les relations ou interactions entre les entités (« nécessite », « est incluse dans », « est responsable de »…).
Exemple, dans la gestion d’un emploi du temps :- entités « professeurs »- entités « enseignements »- entités « horaires »
Un enseignement nécessite un professeur (un et un seul).Un enseignement nécessite 2 h (4 h…) à un moment dans l’emploi du temps.Un professeur réalise au moins un enseignement.etc.
20
Nous allons illustrer cette méthode par une étude de cas: Le plan de cette étude de cas est dérivé de la méthode MERISE.
Modèle Entité-AssociationModèle Entité-Association
Se jeter sur son écran et son clavier (comportement typique chez le développeur débutant) amènera à bien des déceptions voire au découragement.
La méthode consiste à se poser une série de questions dans un ordre bien précis et à y répondre complètement avant de passer à l'étape suivante. Une étape bâclée obligera, au mieux, à reprendre toutes les étapes suivantes. D'où l'importance d'un travail bien préparé.
21
Principes Fondateurs
La méthode MERISE est basée sur la séparation des données et des traitements à effectuer. Elle décompose l'analyse du système en plusieurs modèles conceptuels, logiques et physiques.
Etapes essentielles
• Le MCC (Modèle Conceptuel de la Communication)
• Le MCD (Modèle Conceptuel des Données)
• Le MLD (Modèle Logique des Données)
• Le MPD (Modèle Physique des Données)
• Le MCT (Modèle Conceptuel des Traitements)
• Le MOT (Modèle Organisationnel des Traitements)
Méthode MERISEMéthode MERISENée en 1979 (appel d’offre du Ministère de l’Industrie français). Nombreuses évolutions.
22
Le MCD (Modèle Conceptuel des Données)
A pour but d'écrire de façon formelle les données qui seront utilisées par l'application, d’où une représentation des données, facilement compréhensible, permettant de décrire l'application à l'aide d'entités.
Le MCD fait appel à deux notions principales: les entités et les associations.
Les entités regroupent des données (ce sont les propriétés) afin de former des types d'objets.
Les associations définissent les relations ou interactions entre les entités.
Cette étape est la plus importante. ll s'agit d'une représentation graphique qui permet de décrire toutes les relations existantes entre les entités.
Méthode MERISE : Le MCDMéthode MERISE : Le MCD
23
La définition des données
Réalisation d’une description exhaustive des informations à traiter dans l'application. Cette description doit être organisée et structurée sans redites.
Exemple : Réaliser un BDD qui permette de gérer les séances de TP à l’IUT GEII de Toulon, en prenant en compte : enseignants responsables, matière, promotion et matériels. On suppose qu’il n’y a qu’une matière par série de TP, et qu’une série de TP ne peut concerner qu’une promotion. Une série de TP peut être constituée de plusieurs séances de TP, et une caractéristique peut être commune à plusieurs matériels.
Méthode MERISE : Le MCDMéthode MERISE : Le MCD
24
La définition des données
Objet Occurrences Remarques
Les enseignants unique 1 enseignant par matière
De l’option GEII unique 1 seul à l’IUT de Toulon
De l’IUT de Toulon unique 1 seul dans le monde
La liste des matériels dénombrable On peut en faire la liste exhaustive.
Les caractéristiques de matériels dénombrable On peut en faire la liste exhaustive
Les séries de TP dénombrable Se décompose en séances de TP
Les séances de TP dénombrable On peut en faire la liste exhaustive
Une matière Unique 1 matière par série de TP
Une promotion Unique 1 promotion par série de TP
Méthode MERISE : Le MCDMéthode MERISE : Le MCD
25
La définition des données : Restructuration
On restructure les données pour ne garder que les informations les plus « signifiantes » pour notre application (on supprime tout ce qui est « unique »).
Objet Occurrences Dépendances
Les séries de TP dénombrable Regroupe des séances de TP
Les séances de TP dénombrable Décompose une série de TP,Regroupe des matériels.
La liste des matériels dénombrable Décompose une séance de TP, Regroupe des caractéristiques.
Les caractéristiques de matériels dénombrable Décompose un matériel
Méthode MERISE : Le MCDMéthode MERISE : Le MCD
26
Méthode MERISE : Le MCDMéthode MERISE : Le MCD
S3 – TP d’électrotechnique ET3PS3
S2 – TP d’API ARS2PS2
S1 – TP d’electronique EN1PS1
S1 – TP de langage CII1PS1
Libellé complet Matière Promo.
Séries de TP
Caractéristiques
672Nb_brochesFPGA
15000Nb_elts_logFPGA
20ContinuA
Val.TypeCatégorie
Carte Altera DE2CALTC
Ampèremètre de tableA30AA
PC – salle E200PC-E200 PC
LibelléTypeCatégorie
Matériels
2 ARS2P S2
1ARS2PS2
3II1PS1
2II1P S1
1II1PS1
Numéro Matière Promotion
TP Ascenseur
Initiation grafcet
Lang. C : boucles
Lang. C : les « if »
Initiation au C
Intitulé
Séances de TP
27
Représentation du modèle entité-association :
Entité
attributs
(min,max)
cardinalité
Méthode MERISE : Le MCDMéthode MERISE : Le MCD
associationattribut
Exemple :
Une série de TP contient 1 ou plusieurs séances de TP => (1,n).Une séance de TP est incluse dans au maximum une série de TP => (0,1).
Séries de TP…
Séances de TP…
(1,n) (0,1)contient
28
Le MCD (Modèle Conceptuel des Données):
Exemple :
Matériels ...
Caractéristiques ... possèdent
1,n
0,n0,n
Un matériel en Un matériel en stock est stock est
nécessaire pour nécessaire pour 0 à n séances 0 à n séances
de TPde TP
1,n
Un matériel Un matériel possède 1 à n possède 1 à n
caractéristiquescaractéristiques
Une Une caractéristique caractéristique
peut peut correspondre à correspondre à
0 jusqu’à n 0 jusqu’à n matérielsmatériels
Méthode MERISE : Le MCDMéthode MERISE : Le MCD
Une série de Une série de TP contient 1 TP contient 1 à n séances à n séances
de TPde TP
Séances ...
Séries ...
1,n 0,1
Une séance de Une séance de TP est contenue TP est contenue
dans 0 à 1 dans 0 à 1 séries de TPséries de TP
contient
nécessite
Une séance de Une séance de TP nécessite 1 TP nécessite 1 à n matérielsà n matériels
possède
29
Méthode MERISE : cas Méthode MERISE : cas particulierparticulier
• Relation binaire aux cardinalités (1,1) - (1,1)
Dès le MCD, on peut fusionner les 2 entités en une seule, en regroupant leurs propriétés.
Entité 1
propr1propr2...
Entité 2
propr1’propr2’...
1,1 1,1
Associe
Entité 1+2
propr1propr2…
propr1’propr2’
30
Il peut y avoir plusieurs associations entre 2 entités si la sémantique est différente.
Méthode MERISE : Le MCDMéthode MERISE : Le MCDAssociations multiplesAssociations multiples
Est propriétaire de
Est locataire de
Personne
NomPrénomNuméro Sécu. Sociale
Appartement
NuméroEtage
0,n
0,n
1,n
0,n
Un Un appartement appartement
est la est la propriété d ’au propriété d ’au
moins 1 moins 1 personnepersonne
Une personne Une personne peut ne pas peut ne pas
être être propriétaire, propriétaire,
ou posséder 1 ou posséder 1 à n à n
appartementsappartements
31
Méthode MERISE :Méthode MERISE :Les attributs d’associationLes attributs d’association
Etudiant
……...
Examen
……...
1,n 1,n
Passe
Il peut arriver qu’un paramètre ne puisse pas être associé à une seule entité, car il est en fait lié à 2 entités (ou plus).
• Pas dans l’entité « Etudiant » : cela signifierait que quelque soit l’examen passé, l’étudiant a une note qui sera toujours la même.• Pas dans l’entité « Examen » : cela signifierait que l’examen donnerait toujours lieu à la même note, quelque soit l’étudiant qui le passe.
Où ranger la note ?
Alors ?
Exemple : une entité « Etudiant », une entité « Examen ».
32
Méthode MERISE :Méthode MERISE :Les attributs d’associationLes attributs d’association
Etudiant
……...
Examen
……...
1,n 1,n
Passe
Solution : la note est à la fois liée à l’examen et à l’étudiant. On place donc la note dans l’association reliant « Etudiant » à « Examen » : attribut d’association
Note
33
Méthode MERISE :Méthode MERISE : Relation réflexive Relation réflexive
Il peut arriver qu’une entité puisse être impliquée elle-même plusieurs fois dans une relation (boucle).
Personne
……... Command
e
(0,n)Chef
(0,1)Subordonné
Une personne peut être le chef de 0 à n personnes, ou le subordonné de 0 à 1 personne. Mais ce subordonné peut lui même commander de 0 à n personnes. Et le chef peut lui même être commandé par un autre chef…
Note : Les cardinalités minimales sont toujours égales à 0, sinon on aurait une boucle « sans fin ».
Exemple :
34
Le MLD (Modèle Logique des Données)
Permet de mettre en forme le MCD.
Le MLD fait appel à deux notions principales :
• les tables• les relations
Méthode MERISE : Le MLDMéthode MERISE : Le MLD
Elles sont définies à partir de la modélisation « entité – association » établie lors de la phase précédente.
Les tables sont la réalisation informatique des entités.Les relations définissent les liaisons entre tables ainsi que les règles d'intégrité des données inscrites dans les tables. Elles auront une influence considérable sur le fonctionnement du moteur de base de données.
La conception du MLD consiste essentiellement à:Traduire les entités en Tables (et les propriétés en Champs)Traduire les associations en Relations (en portant attention à leur Cardinalité)
35
Le MLD (Modèle Logique des Données) :
Méthode MERISE : Le MLDMéthode MERISE : Le MLD
Transformation des entités en tables
La transformation des entités en tables se fait trois étapes:
• Transformation des propriétés en champs • Mise en évidence de l'index principal grâce à l'identifiant de l'entité
• Création de la clef primaire de la table.
36
Le MLD (Modèle Logique des Données) :
Transformation des propriétés en champ :
On donne un nom de champ à chaque propriété. On prendra garde à donner des noms simples permettant de l'identifier clairement parmi tous les autres champs de la base.
On doit donner à chaque champ un type qui permettra de stocker correctement les données: type texte dont on spécifie le nombre maximum de caractères; type numérique (entiers, flottants, etc.); type time (date, heure, etc.); etc.
Méthode MERISE : Le MLDMéthode MERISE : Le MLD
EtreHumain
Nom de la tableNom de la table
NumSecu entierNom char(30)Prénomchar(20)DateNaiss dateLieuNaiss char(30)Sexe booléen
types types
Nom des Nom des champschamps
37
Le MLD (Modèle Logique des Données) :
On choisit dans l’ensemble des champs de l’entité un champ ou un n-uplet de champs, qui puisse différencier parfaitement une ligne d’une autre dans la table.
On nomme ce champ (ou cet ensemble de champs) : clé primaire.
Méthode MERISE : Le MLDMéthode MERISE : Le MLD
Il est préférable que la clef primaire ne soit composée que d'un seul champ.
Si plusieurs champs sont nécessités pour former la clé primaire de la table, il est recommandé de créer un champ supplémentaire qui servira de clef primaire car ce type de combinaison de champs est difficile à maintenir.
EtreHumain
NumSecu entier
Nom char(30)Prénomtexte(30)DateNaiss dateLieuNaiss texte(30)Sexe booléen
Clé primaireClé primaire
38
Transformation des associations en relations
Méthode MERISE : Le MLDMéthode MERISE : Le MLD
Suivant la cardinalités des relations entre entités dans le MCD, le MLD va pouvoir être construit directement via certaines règles.
Plusieurs cas peuvent se produire :
• cas (X,n)-(X,n)• cas (X,1)-(X,n)• cas (0,1)-(1,1)
39
MCD MCD MLD : précisions MLD : précisions• Relation binaire aux cardinalités (X,n) - (X,n), X=0 ou X=1
On utilise une table de jointure ayant comme Clé Primaire une clé composée des identifiants des 2 entités. On dit que la Clé Primaire de la nouvelle table est la concaténation des Clés Primaires des deux autres tables.
L’attribut d’association passe dans la table de jointure.
Entite2
att1’ entier
att2’ timeatt3’ flottant
Entité 1
……...
Entité 2
……...
X,n X,n
Associe
Entite1
att1 entier
att2char(30)att3booléen
Associer
att1 entieratt1’ entieratt_asso entier
att_asso
40
MCD MCD MLD : précisions MLD : précisions• Relation binaire aux cardinalités (X,1) - (X,n), X=0 ou X=1
La Clé Primaire de la table à la cardinalité (X,n) devient une Clé Etrangère dans la table à la cardinalité (X,1) Va nous permettre de créer un lien entre les tables.
L’attribut d’association passe dans la table à la cardinalité (X,1).
Entite2
att1’ entier
att2’ timeatt3’ flottant
Entite1
att1 entier
att’1 entier
att2 char(30)att3 booléenatt_asso entier
Entité 1
……...
Entité 2
……...
X,1 X,n
Associe
att_asso
41
MCD MCD MLD : précisions MLD : précisions
• Relation binaire aux cardinalités (0,1) - (1,1)
La Clé Primaire de la table à la cardinalité (0,1) devient une Clé Etrangère dans la table à la cardinalité (1,1).
L’attribut d’association passe indifféremment dans l’une ou l’autre des tables.
Entite2
att1’ entier
att1entier
att2’ timeatt3’flottant
Entité 1
……...
Entité 2
……...
0,1 1,1
Associe
Entite1
att1 entier
att2char(30)att3 booléenatt_asso entier
att_asso
42
MCD MCD MLD : cas MLD : cas d’une relation réflexived’une relation réflexive
• Relation binaire aux cardinalités (0,1) - (0,1)
• Relation binaire aux cardinalités (0,1) - (0,n)
• Relation binaire aux cardinalités (0,n) - (0,n)
Un attribut clé étrangère est ajouté dans la nouvelle table, attribut éventuellement suffixé (renommé) par le rôle de la liaison à la cardinalité (0,1). Cette clé étrangère référence la clé primaire de la même table.Attention : pas applicable sous Access, obligation de créer 2 tables.
Même règle, sauf qu’il y a 2 clés étrangères. Il pourra y avoir possibilité plus tard d’éliminer l’une d’entre elles.
Création d’une 2ème table, dont la clé primaire est la concaténation de deux clés, référant à la clé primaire de la 1ère table. Ces attributs sont suffixés (renommés) par le rôle de chacune des liaisons correspondantes
43
MCD MCD MLD : cas MLD : cas d’une relation réflexived’une relation réflexive
Exemple : relation (0,1) – (0,n)
Personne
NumPersonne…...NumChef
Exemple : relation (0,n) – (0,n)
Article
NumArticle…...
Composer
NumArticleComposantNumArticleComposéDe
44
MLD : autre représentationMLD : autre représentation
Il arrive parfois que le MLD se représente alors de manière textuelle.
Exemple :
est équivalent à
Entite1( att1, att2, att3)Entite2( att1’, att1, att2’, att3’)
Entite1
att1 entier
att2char(30)att3booléen
Entite2
att1’ entier
att1entieratt2’ timeatt3’flottant
Note : le type des Note : le type des données ne figure données ne figure pas.pas.
45
Introduction
Le modèle relationnelLe modèle relationnel
Dans le cadre d'un projet d'informatisation, la conception d'une base de données relationnelle passe d'abord par l'identification des objets de gestion (=entités) et des règles de gestion du domaine modélisé). Une fois énoncées et validées, ces règles nous conduisent automatiquement à la structure du modèle relationnel correspondant.
Des opérations vont pouvoir être effectuées sur les instances des différentes tables, afin de procéder à la recherche d ’informations dans la base de données.
46
SQLSQL
DéfinitionDéfinition
SQL (Structured Query Language - Langage de requêtes structuré) est un langage utilisé pour interroger ou manipuler les Bases de Données Relationnelles. C ’est :
• un langage de définition de données (DDL), • un langage de manipulation de données (DML),• un langage de contrôle de données (DCL, Data Control Language).
Début du développement dans les années 70 (IBM). Première version commerciale en 1979. Dernière normalisation en 2003.
De nombreux SGBDR utilisent SQL : Access, MySQL, Oracle, DB2, SQL Server...
47
Opérations de baseOpérations de base
Le modèle relationnelLe modèle relationnel
ProjectionProjectionFormalisme : R = PROJECTION (R1, liste des attributs).Cet opérateur ne porte que sur 1 table.Il permet de ne retenir que certains attributs spécifiés d'une table.On obtient toutes les lignes de la table à l'exception des doublons.
Nom Superficie
Population
Mer-océan
Groix 230014,82 Atlantique
Corse 2600008680 MéditerranéeOuessan
t93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
Iles
R = PROJECTION (Iles, Nom, Population)
Nom PopulationGroix 2300
Corse 260000
Ouessant
932
Mayotte 200000
Tahiti 170000
48
Opérations de baseOpérations de base
Le modèle relationnelLe modèle relationnel
ProjectionProjection
Formalisme : R = PROJECTION (R1, liste des attributs).
Espece Categorie
Rosé des prés
Conserve
Rosé des prés
Sec
Coulemelle Frais
Champignons
Espece Categorie
Rosé des prés
Conserve
Rosé des prés
Sec
Coulemelle Frais
Rosé des prés
Sec
Conditionnement Bocal
Verrine
Boîte
Sachet plastique
R = PROJECTION (Champignons, Espèce, Catégorie)
Pas de doublonPas de doublon
49
SQLSQLInstructionsInstructions
SELECT : permet de récupérer des informations dans la BDD.
SELECT [DISTINCT, ...] liste_sélection [FROM...] [ WHERE condition_recherche ] [GROUP BY expression][ORDER BY expression] ;
Exemple :
Nom Superficie
Population
Mer-océan
Groix 230014,82 Atlantique
Corse 2600008680 MéditerranéeOuessan
t93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
Iles SELECT Nom,Population FROM Iles;
Nom PopulationGroix 2300
Corse 260000
Ouessant
932
Mayotte 200000
Tahiti 170000
50
SQLSQLSELECTSELECT ProjectionProjection
SELECT Nom, Population FROM Iles; n’est pas une vraie projection, car s’il y a des doublons dans la table d ’origine, ils apparaîtront dans la table résultante.
SELECT Espece, Categorie FROM Champignons;
Pour faire une projection, on écrit donc : SELECT DISTINCT Espèce, Categorie FROM Champignons;
(DISTINCT permet d ’éliminer les doublons).
Espece Categorie
Rosé des prés
Conserve
Rosé des prés
Sec
Coulemelle Frais
Rosé des prés
Sec
DoublonDoublonChampignons
Espece Categorie
Rosé des prés
Conserve
Rosé des prés
Sec
Coulemelle Frais
Rosé des prés
Sec
Conditionnement Bocal
Verrine
Boîte
Sachet plastique
51
Opérations de baseOpérations de base
Le modèle relationnelLe modèle relationnel
SélectionSélection
Formalisme : R = SELECTION (R1, condition)
• Cet opérateur porte sur 1 table.
• Il permet de ne retenir que les lignes répondant à une condition exprimée à l'aide des opérateurs arithmétiques ( =, >, <, >=, <=, <>) ou logiques de base (ET, OU, NON).
• Tous les attributs de la table R1 sont conservés dans R.
52
Opérations de baseOpérations de base
Le modèle relationnelLe modèle relationnel
SélectionSélection
Formalisme : R = SELECTION (R1, condition)
Nom Superficie
Population
MerOcean
Groix 230014,82 Atlantique
Corse 2600008680 MéditerranéeOuessan
t93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
Iles
R = SELECTION (Iles, Population>180000)
Nom Superficie
Population
MerOcean
Corse 2600008680 MéditerranéeMayotte 200000374 Indien
R = SELECTION (Iles, MerOcean=« Atlantique »)
Nom Superficie
Population
MerOcean
Groix 230014,82 Atlantique
Ouessant
93215,58 Atlantique
53
SQLSQLSELECTSELECT SélectionSélection
SELECT […] FROM […] WHERE […]
SELECT * FROM Iles WHERE MerOcean="Atlantique";
SELECT * FROM Iles WHERE Population > 150000;
SELECT * FROM Iles WHERE Population BETWEEN 100000 AND 200000;
SELECT * FROM Iles WHERE MerOcean IN ( "Pacifique", "Indien", "Antarctique")
SELECT * FROM Codes WHERE CodePostal LIKE ‘56%’;
SELECT * FROM Codes WHERE CodePostal LIKE ‘56_9_’;
Exemples :
CodePostal commence par « 56 »CodePostal commence par « 56 »
CodePostal est de la forme CodePostal est de la forme « 56x9x » « 56x9x »
On affiche tous les attributsOn affiche tous les attributs
54
Opérations de baseOpérations de base
Le modèle relationnelLe modèle relationnel
JointureJointure
• Cet opérateur porte sur 2 tables qui doivent avoir au moins un attribut défini dans le même domaine (ensemble des valeurs permises pour un attribut).• La condition de jointure peut porter sur l'égalité d'un ou de plusieurs attributs définis dans le même domaine (mais n'ayant pas forcément le même nom).• Les n-uplets de la relation résultat sont formés par la concaténation des n-uplets des relations d'origine qui vérifient la condition de jointure.
Remarque : Des jointures plus complexes que l'équijointure peuvent être réalisées en généralisant l'usage de la condition de jointure à d'autres critères de comparaison que l'égalité (<, >, , , ).
55
Opérations de baseOpérations de base
Le modèle relationnelLe modèle relationnel
JointureJointureFormalisme : R = JOINTURE (R1, R2, condition d'égalité entre attributs)
Nom SuperficiePopulation MerOcean
Ile de Groix 230014,82 Atlantique
Corse 2600008680 Méditerranée
Ouessant 93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
Iles
CodePostal
IleVille
98714Papeete Tahiti
17480Château-d'Oléron
Oléron
20000Ajaccio Corse29242Ouessant Ouessant
56590Groix Ile de Groix
Codes
20200Bastia Corse
R = JOINTURE (Iles, Codes, Iles.Nom=Codes.Ile)
Nom Superficie Population MerOcean
Ile de Groix 230014,82 Atlantique
Corse 2600008680 MéditerranéeOuessant 93215,58 Atlantique
Tahiti 1700001043 Pacifique
CodePostal IleVille
98714Papeete Tahiti
20000Ajaccio Corse
29242Ouessant Ouessant
56590Groix Ile de Groix
Corse 2600008680 Méditerranée
20200Bastia Corse
56
SQLSQLSELECTSELECT JointureJointure
SELECT […] FROM Table1,Table2,Table3… WHERE […]
SELECT * FROM Iles,Codes WHERE Iles.Nom=Codes.Ile;
Nom Superficie Population MerOcean
Ile de Groix 230014,82 Atlantique
Corse 2600008680 Méditerranée
Ouessant 93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
Iles
CodePostal IleVille
98714Papeete Tahiti
17480Château-d'Oléron Oléron
20000Ajaccio Corse
29242Ouessant Ouessant
56590Groix Ile de Groix
Codes
20200Bastia Corse
Nom SuperficiePopulation MerOcean
Ile de Groix 230014,82 Atlantique
Corse 2600008680 Méditerranée
Ouessant 93215,58 Atlantique
Tahiti 1700001043 Pacifique
CodePostal
IleVille
98714Papeete Tahiti
20000Ajaccio Corse
29242Ouessant Ouessant
56590Groix Ile de Groix
Corse 2600008680 Méditerranée
20200Bastia Corse
Exemple :
57
Opérations ensemblistesOpérations ensemblistes
Le modèle relationnelLe modèle relationnel
UnionUnion
• Cet opérateur porte sur deux tables qui doivent avoir le même nombre d'attributs définis dans le même domaine. On parle de tables ayant le même schéma.
• La relation résultat possède les attributs des tables d'origine et les n-uplets de chacune, avec élimination des doublons éventuels.
58
Opérations ensemblistesOpérations ensemblistes
Le modèle relationnelLe modèle relationnel
UnionUnion
Formalisme : R = UNION (R1, R2)
Nom Réalisateur
Star Wars G. Lucas
Brazil T. Gilliam
Dvd_Romeo
Batman T. Burton
La Marche de l ’Empereur
L. Jacquet
Dvd_Juliette
Nom Réalisateur
Nausicaa H. Miyazaki
Batman T. Burton
Batman 2 T. Burton
Star Wars G. Lucas
Nom Réalisateur
Star Wars G. Lucas
Brazil T. Gilliam
Batman T. Burton
La Marche de l ’Empereur
L. Jacquet
Nausicaa H. Miyazaki
Batman 2 T. Burton
R = UNION (Dvd_Romeo, DVD_Juliette)
59
SQLSQLSELECTSELECT UnionUnion
SELECT liste d'attributs FROM table1UNIONSELECT liste d'attributs FROM table2 ;
Nom Realisateur
Star Wars G. Lucas
Brazil T. Gilliam
Dvd_Romeo
Batman T. Burton
La Marche de l ’Empereur L. Jacquet
Dvd_Juliette
Nom Realisateur
Nausicaa H. Miyazaki
Batman T. Burton
Batman 2 T. Burton
Star Wars G. Lucas
Nom Realisateur
Star Wars G. Lucas
Brazil T. Gilliam
Batman T. Burton
La Marche de l ’Empereur L. Jacquet
Nausicaa H. Miyazaki
Batman 2 T. Burton
SELECT Nom, Realisateur FROM Dvd_RomeoUNIONSELECT Nom, Realisateur FROM Dvd_Juliette;
Exemple :
60
Opérations ensemblistesOpérations ensemblistes
Le modèle relationnelLe modèle relationnel
IntersectionIntersection
• Cet opérateur porte sur deux tables de même schéma.• La table résultat possède les attributs des tables d'origine et les n-uplets communs à chacune.
61
Opérations ensemblistesOpérations ensemblistes
Le modèle relationnelLe modèle relationnel
IntersectionIntersectionFormalisme : R = INTERSECTION (R1, R2)
Nom Réalisateur
Star Wars G. Lucas
Brazil T. Gilliam
Dvd_Romeo
Batman T. Burton
La Marche de l ’Empereur
L. Jacquet
Dvd_Juliette
Nom Réalisateur
Nausicaa H. Miyazaki
Batman T. Burton
Batman 2 T. Burton
Star Wars G. Lucas
Nom Réalisateur
Star Wars G. Lucas
Batman T. Burton
R = INTERSECTION (Dvd_Romeo, Dvd_Juliette)
62
SQLSQLSELECTSELECT IntersectionIntersection
Ancienne version : SELECT attribut1, attribut2, ... FROM table1WHERE attribut1 IN (SELECT attribut1 FROM table2) ;
Depuis SQL2 :SELECT attribut1, attribut2, ... FROM table1INTERSECTSELECT attribut1, attribut2, ... FROM table2 ;
INTERSECT pas implanté sur tous les SGBD.
63
SQLSQLSELECTSELECT IntersectionIntersectionExemple : SELECT Nom, Realisateur FROM Dvd_Romeo
WHERE Nom IN (SELECT Nom FROM Dvd_Juliette) ;
ou
SELECT Nom, Realisateur FROM Dvd_RomeoINTERSECTSELECT Nom, Realisateur FROM Dvd_Juliette;
Nom Realisateur
Star Wars G. LucasBrazil T. Gilliam
Dvd_Romeo
Batman T. Burton
La Marche de l ’Empereur
L. Jacquet
Dvd_Juliette
Nom Réalisateur
Nausicaa H. Miyazaki
Batman T. Burton
Batman 2 T. Burton
Star Wars G. Lucas
Nom Réalisateur
Star Wars G. Lucas
Batman T. Burton
64
Opérations ensemblistesOpérations ensemblistes
Le modèle relationnelLe modèle relationnel
DifférenceDifférence
• Cet opérateur porte sur deux tables de même schéma.• La table résultat possède les attributs des tables d'origine et les n-uplets de la première table qui n'appartiennent pas à la deuxième.• DIFFERENCE(DVD_JULIETTE,DVD_ROMEO) DIFFERENCE(DVD_ROMEO,DVD_JULIETTE)
65
Opérations ensemblistesOpérations ensemblistes
Le modèle relationnelLe modèle relationnel
DifférenceDifférenceFormalisme : R = DIFFERENCE (R1, R2)
Nom Réalisateur
Star Wars G. Lucas
Brazil T. Gilliam
Dvd_Romeo
Batman T. Burton
La Marche de l ’Empereur
L. Jacquet
Dvd_Juliette
Nom Réalisateur
Nausicaa H. Miyazaki
Batman T. Burton
Batman 2 T. Burton
Star Wars G. Lucas
Nom Réalisateur
R = DIFFERENCE (Dvd_Romeo, Dvd_Juliette)
Brazil T. Gilliam
La Marche de l ’Empereur
L. Jacquet
Liste des DVD de Roméo qui ne sont pas dans celle de Juliette.
66
SQLSQLSELECTSELECT DifférenceDifférence
SELECT attribut1, attribut2, ... FROM table1WHERE attribut1 NOT IN (SELECT attribut1 FROM table2) ;
Depuis SQL2 :SELECT attribut1, attribut2, ... FROM table1EXCEPTSELECT attribut1, attribut2, ... FROM table2 ;
EXCEPT pas implanté sur tous les SGBD.
67
SQLSQLSELECTSELECT DifférenceDifférenceExemple :SELECT Nom, Realisateur FROM Dvd_Romeo WHERE Nom NOT IN (SELECT Nom FROM Dvd_Juliette) ;ou
SELECT Nom, Realisateur FROM Dvd_RomeoEXCEPTSELECT Nom, Realisateur FROM Dvd_Juliette ;
Nom Réalisateur
Star Wars G. Lucas
Brazil T. Gilliam
Dvd_Romeo
Batman T. Burton
La Marche de l ’Empereur
L. Jacquet
Dvd_Juliette
Nom Réalisateur
Nausicaa H. Miyazaki
Batman T. Burton
Batman 2 T. Burton
Star Wars G. Lucas
Nom Réalisateur
Brazil T. Gilliam
La Marche de l ’Empereur
L. Jacquet
68
Opérations ensemblistesOpérations ensemblistes
Le modèle relationnelLe modèle relationnel
ProduitProduit
• Cet opérateur porte sur deux tables.• La table résultat possède les attributs de chacune des tables d'origine et ses n-uplets sont formés par la concaténation de chaque n-uplet de la première table avec l'ensemble des n-uplets de la deuxième.
69
Opérations ensemblistesOpérations ensemblistes
Le modèle relationnelLe modèle relationnel
ProduitProduitFormalisme : R = PRODUIT (R1, R2)
Numéro Nom
101 Dupont
102 Martin
Etudiants
Partiels
Libellé Coefficient
Anglais 2
Processeurs 3
Recherche opérationnelle
5
R = PRODUIT (Etudiants, Partiels)
Numéro Nom
101 Dupont
102 Martin
Libellé Coefficient
Anglais 2
Processeurs 3
Recherche opérationnelle
5
101 Dupont
101 Dupont
102 Martin
102 Martin
Anglais 2
Processeurs 3
Recherche opérationnelle
5
70
SQLSQLSELECTSELECT ProduitProduit
SELECT * FROM table1, table2 ;
Numéro Nom
101 Dupont
102 Martin
Etudiants
Partiels
Libellé Coefficient
Anglais 2
Processeurs 3
Recherche opérationnelle
5
SELECT * From Etudiants, Partiels;
Numéro Nom
101 Dupont
102 Martin
Libellé Coefficient
Anglais 2
Processeurs 3
Recherche opérationnelle
5
101 Dupont
101 Dupont
102 Martin
102 Martin
Anglais 2
Processeurs 3
Recherche opérationnelle
5
71
Opérations diversesOpérations diverses
Le modèle relationnelLe modèle relationnel
DivisionDivision
Formalisme : R=DIVISION(R1,R2)
• Cet opérateur porte sur 2 tables qui doivent avoir au moins un attribut défini dans le même domaine.• Tous les attributs du diviseur (R2) doivent être des attributs du dividende (R1).• La table dividende doit avoir au moins une colonne de plus que la table diviseur.• La table résultat, le quotient, possède les attributs non communs aux deux relations initiales et est formée de tous les n-uplets qui, concaténés à chacun des n-uplets du diviseur (R2) donne toujours un n-uplet du dividende (R1).
72
Opérations diversesOpérations diverses
Le modèle relationnelLe modèle relationnel
DivisionDivision
R=DIVISION(Liste_Joueurs, Liste_Equipes)
Liste_Equipes
Equipe
Marseille
PSG
Lorient
Nom
Fiorèse
On cherche qui a joué à Marseille, au PSG et à Lorient :seul Fiorèse a joué dans les 3 équipes.
Nom Equipe
Fiorèse Marseille
Pédron PSG
Abriel PSG
Abriel Lorient
Fiorèse Lorient
Liste_Joueurs
Marlet Marseille
Marlet Lorient
Fiorèse PSG
Pédron Lorient
Namouchi Lorient
73
SQLSQL
SELECTSELECT DivisionDivision
… Il n ’y a pas d ’instruction simple permettant de faire la division.Nécessite plusieurs SELECT imbriqués.
74
Opérations d’agrégationOpérations d’agrégation
Le modèle relationnelLe modèle relationnel
CalculCalculFormalisme : R=CALCULER(R0, fonction1, fonction2, ...) ou N=CALCULER(R0, fonction)
• Les calculs et/ou comptage portent sur la relation R0.• La table résultat ne comportera qu'une ligne avec autant de colonnes que de résultats demandés ou pourra simplement être considérée comme un nombre N utilisable ultérieurement en tant que tel dans le cas où un seul résultat est attendu.
75
Opérations d’agrégationOpérations d’agrégation
Le modèle relationnelLe modèle relationnel
CalculCalculFormalisme : R=CALCULER(R0, fonction1, fonction2, ...)avec fonction = Somme(attribut), Moyenne(attribut), Minimum(attribut), Maximum(attribut).
R=CALCULER(Liste_Commandes, Somme(Quantité), Somme(Quantité*PrixUnitaire))
Somme(Quantité)
Somme(Quantité*PrixUnitaire)
124 5810
Commande CodeProduit
Quantité PrixUnitaire96008 A10 10 83
96008 B20 35 32
96009 A10 20 83
96010 A15 4 110
96010 B20 55 83
Liste_Commandes
76
SQLSQLSELECTSELECT Fonctions de calculFonctions de calcul
SUM(attribut) : total des valeurs d'un attributAVG(attribut) : moyenne des valeurs d'un attributMIN(attribut) : plus petite valeur d'un attributMAX(attribut) : plus grande valeur d'un attribut
Fonction de comptageFonction de comptage
COUNT() qui peut s ’utiliser de plusieurs manières :COUNT() qui peut s ’utiliser de plusieurs manières :
COUNT(*) : nombre de lignes (n-uplets) d ’une table
COUNT(DISTINCT attribut) : nombre de valeurs différentes de l'attribut
77
SQLSQLSELECTSELECT Fonctions de calculs et de comptage : ExempleFonctions de calculs et de comptage : Exemple
Nom Fonction Salaire
Burns PDG 100000
Smithers
Dir.-adjoint
20000
Lenny Technicien 1500
Carl Technicien 1400
Homer Technicien 1200
PersonnelCentralePersonnelCentrale
SELECT COUNT(*) FROM PersonnelCentrale; 5
SELECT COUNT(*) FROM PersonnelCentrale WHERE Fonction=« Technicien »; 3
SELECT SUM(Salaire) FROM PersonnelCentrale WHERE Fonction=« Technicien »; 4100
SELECT MIN(Salaire) FROM PersonnelCentrale WHERE Fonction=« Technicien »; 1200
78
Opérations d’agrégationOpérations d’agrégation
Le modèle relationnelLe modèle relationnel
Regroupement et calculRegroupement et calculFormalisme : R=REGROUPER_ET_CALCULER(R0, att1, att2, ..., fonction1, fonction2, ...)
• Le regroupement s'effectue sur un sous ensemble des attributs de la relation R0.• La table résultat comportera autant de lignes que de groupes de n-uplets, les fonctions s'appliquant à chacun des groupes séparément.
79
Opérations d’agrégationOpérations d’agrégation
Le modèle relationnelLe modèle relationnel
Regroupement et calculRegroupement et calculFormalisme : R=REGROUPER_ET_CALCULER(R0, att1, att2, ..., fonction1, fonction2, ...)
R=REGROUPER_ET_CALCULER(Liste_Commandes, Commande, Somme(Quantité*PrixUnitaire))
Commande Somme(Quantité*PrixUnitaire)
96008 1950
96009
96010
1660
2200
Commande CodeProduit
Quantité PrixUnitaire96008 A10 10 83
96008 B20 35 32
96009 A10 20 83
96010 A15 4 110
96010 B20 55 83
Liste_Commandes
80
SQLSQLSELECTSELECT Regroupement et calculRegroupement et calcul
SELECT … FROM ...
GROUP BY (liste d’attributs)HAVING (condition);
Commande CodeProduit Quantité PrixUnitaire
96008 A10 10 83
96008 B20 35 32
96009 A10 20 83
96010 A15 4 110
96010 B20 55 32
Liste_Commandes
SELECT Commande, Sum(Quantité*PrixUnitaire) FROM Liste_Commandes GROUP BY Commande ;
Commande Sum(Quantité*PrixUnitaire)
96008 1950
96009
96010
1660
2200
SELECT Commande, Sum(Quantité*PrixUnitaire) FROM Liste_Commandes GROUP BY Commande HAVING Sum(Quantité)>40;
Commande
Sum(Quantité*PrixUnitaire)
96008 1950
96010 2200
81
Opérations d’agrégationOpérations d’agrégation
Le modèle relationnelLe modèle relationnel
TriTriFormalisme : R = TRI(R0, att1, att2, ...)
• Le tri s'effectue sur un ou plusieurs attributs, dans l'ordre croissant ou décroissant.• La table résultat a la même structure et le même contenu que la relation de départ.
82
Opérations diversesOpérations diverses
Le modèle relationnelLe modèle relationnel
TriTriFormalisme : R = TRI(R0, att1, att2, ...)
R=TRI(Iles, Mer_Océan , Population )
Nom Superficie
Population
MerOcean
Groix 230014,82 Atlantique
Corse 2600008680 MéditerranéeOuessan
t93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
Iles
Nom Superficie
Population
MerOcean
Groix 230014,82 Atlantique
Corse 2600008680 Méditerranée
Ouessant
93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
83
SQLSQLSELECTSELECT TriTri
SELECT … FROM ...
ORDER BY attribut1 ASC (ou DESC), attribut2 ASC (ou DESC)...
SELECT *FROM IlesORDER BY MerOcean DESC, Population ASC;
Nom Superficie
Population
MerOcean
Groix 230014,82 Atlantique
Corse 2600008680 MéditerranéeOuessan
t93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
Iles
Nom Superficie
Population
MerOcean
Groix 230014,82 Atlantique
Corse 2600008680 Méditerranée
Ouessant
93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
84
Opérations diversesOpérations diverses
Le modèle relationnelLe modèle relationnel
Attributs calculésAttributs calculés
Un attribut calculé est un attribut dont les valeurs sont obtenues par des opérations arithmétiques portant sur des attributs de la même table.Le calcul est spécifié lors d'une projection ou lors de l'utilisation d'une fonction.
Nom Superficie
Population
Population/SuperficieGroix 230014,82 155,20
Corse 2600008680 29,95
Ouessant
93215,58 59,82
Mayotte 200000374 534,76
Tahiti 1700001043 162,99
Ex : R=PROJECTION(Iles, Nom, Superficie, Population, Population/Superficie)
85
SQLSQLSELECTSELECT Attributs calculésAttributs calculés
Nom Superficie Population MerOcean
Groix 230014,82 Atlantique
Corse 2600008680 Méditerranée
Ouessant 93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
Iles
Nom Superficie PopulationPopulation/Superficie
Groix 230014,82 155,20
Corse 2600008680 29,95
Ouessant 93215,58 59,82
Mayotte 200000374 534,76
Tahiti 1700001043 162,99
Ex : SELECT Nom, Superficie, Population, Population/SuperficieFROM Iles;
Attributs renommésAttributs renommés
Ex : SELECT Nom, Population/Superficie AS DensiteFROM Iles;
Nom Densite
Groix 155,20
Corse 29,95
Ouessant 59,82
Mayotte 534,76
Tahiti 162,99
86
SQLSQLSELECTSELECT Prédicat EXISTSPrédicat EXISTS
Le prédicat EXISTS permet de tester l'existence ou l'absence de données dans la sous requête.Si la sous requête renvoie au moins une ligne, le prédicat est vrai.
Nom Fonction Salaire
Burns PDG 100000
Smithers
Dir.-adjoint
20000
Lenny Technicien 1500
Carl Technicien 1400
Homer Technicien 1200
PersonnelCentralePersonnelCentrale SELECT Sum(Salaire) FROM PersonnelCentrale WHERE EXISTS (
SELECT *FROM PersonnelCentraleWHERE Fonction=’Technicien’)
);
124100
SELECT Sum(Salaire) FROM PersonnelCentrale WHERE EXISTS (
SELECT *FROM PersonnelCentraleWHERE Fonction=’Ingénieur’)
);
NULL
87
Opérations diversesOpérations diverses
Le modèle relationnelLe modèle relationnel
Attributs renommésAttributs renommés
Il est possible de renommer n'importe quel attribut en le faisant précéder de son nouveau nom suivi de ":".
R=PROJECTION(R0, att1, att2, att3, att4, newatt1:att1*att2, newatt2:att3/att2)
R=PROJECTION(Liste_Commandes, Commande, CodeProduit, Montant:Quantité*PrixUnitaire)
Commande
CodeProduit
Quantité
PrixUnitaire
96008 A10 10 83
96008 B20 35 32
96009 A10 20 83
96010 A15 4 110
96010 B20 55 83
Liste_CommandesCommand
eCodeProdui
tMontant
96008 A10 830
96008 B20 1120
96009 A10 1660
96010 A15 440
96010 B20 1760
88
SQL - Types de donnéesSQL - Types de donnéesDe nombreux types de données sont traités par SQL.
Entiers
smallint -32768 à 32767 (16 bits)integer (ou int) +/- 2 milliards (32 bits)
Numérique
Réels à précision arbitraire
numeric(p,q) ou decimal(p,q) (p = précision ; q = échelle)
Permet de stocker des nombres de p chiffres dont q sont après la virgule.
Ex : pour stocker un nombre comme 742.23876, il faudra définir un numeric(8,5) ou decimal(8,5)
Permettent de faire des calculs exacts (pas d’arrondis), mais plus lents par rapport aux flottants.
89
SQL - Types de donnéesSQL - Types de données
Flottants
Numérique
double precision : réel à virgule flottante dont la représentation est binaire, de grande précision (64 bits. Amplitude : -1.8.10+308
à -2.23.10-308, 0, et 2.23.10-308 à 1.8.10+308
).
float(p,q) : réel à virgule flottante dont la représentation est binaire à échelle et précision obligatoire (jusqu ’à 64 bits).
real : réel à virgule flottante dont la représentation est binaire, de faible précision (32 bits. Amplitude : - 3,40.10+38 à -1,18.10-38 , 0, et 1,18.10-38 à 3,40.10+38 )
Calculs rapides, mais des arrondis peuvent se produire (ex : 3,14159 3,1415899999…).
90
SQL - Types de donnéesSQL - Types de données
char(n) ou character(n) : chaîne de caractères de longueur fixe n.
varchar(n) ou char varying(n) ou character varying(n) : chaîne de caractères de longueur variable n.
Délimiteurs : ’ ... ’ (’bonjour’)
Alphanumérique
Valeur CHAR(4)Place mémoire VARCHAR(4)
Place mémoire
’’ ’ ’ 4 octets ’’ 1 octet
’ab’ ’ab ’ 4 octets ’ab’ 3 octets
’abcd’
’abcd’ 4 octets ’abcd’ 5 octets
91
SQL - Types de donnéesSQL - Types de données
date : date du calendrier grégorien (résolution : 1 jour)
time : temps sur 24 heures (résolution : 1 microseconde)
timestamp : combiné date temps (résolution : 1 microseconde)
Date et heure
92
SQL – Types de donnéesSQL – Types de données
Booléen
… N’est pas défini dans la norme SQL.
Un type BOOLEAN (ou LOGICAL) est défini dans certaines SGBD.
Sinon : on utilise un type énuméré : enum(’V’,’F’) (la variable ne pourra avoir que 2 valeurs, V ou F).
93
SQL – Types de donnéesSQL – Types de données
Créations de domaines
Possibilité de créer son propre type de données.
CREATE DOMAIN Nom_Domaine AS Type_Donnée
Ex : CREATE DOMAIN CodePostal AS decimal(5,0);
94
SQL – Types de donnéesSQL – Types de données Créations de types
Possibilité de créer son propre type de données composé.
CREATE TYPE Type_Utilisateur AS OBJECT ( Nom_Champ Type_Donnée, ... Nom_ChampN Type_DonnéeN);
Exemple :
CREATE TYPE Personnel AS OBJECT (Nom AS varchar(20), Prenom AS varchar(20), Naissance AS date);
95
CREATE TABLE Nom_de_la_table (Attribut1 Type_de_donnée,Attribut2 Type_de_donnée,...);
CREATE TABLECREATE TABLE
SQL – Création de tablesSQL – Création de tables
96
Si on veut que certains champs aient une valeur si non renseignés, on rajoute une option DEFAULT.
Si on veut que certains champs soient obligatoirement renseignés (pas de « vide »), on rajoute une contrainte NOT NULL.
CREATE TABLE Nom_de_la_table (Attribut1 Type_de_donnée NOT NULL,Attribut2 Type_de_donnée,...);
CREATE TABLE Employes (NomEmploye char(50) NOT NULL,Salaire integer DEFAULT ’1000.00’,...);
SQL – Contraintes d’intégritéSQL – Contraintes d’intégrité
97
Si on veut une condition sur la valeur d ’un champ : CHECK.
CREATE TABLE clients(Nom char(30) NOT NULL,Age integer CHECK (age>0 AND age < 100),Courriel char(50) NOT NULL CHECK (Courriel LIKE "%@%"));
SQL – Contraintes d’intégritéSQL – Contraintes d’intégrité
98
SQL – Contraintes d’intégritéSQL – Contraintes d’intégrité
Contrainte d ’unicité : si on veut vérifier que la valeur saisie pour un champ n'existe pas déjà dans la table : UNIQUE.
CREATE TABLE ClientsBanque(Nom char(30) NOT NULL,Prenom char(30) NOT NULL,NumCompte integer,NumPEL integer,NumLivretA integer,UNIQUE(NumCompte),UNIQUE(NumPEL),UNIQUE(NumLivretA));
99
SQL – ContraintesSQL – Contraintes
Clause CONSTRAINT :permet de définir une contrainte sur un attribut lors de la création d'une table.
CONSTRAINT Définition_Contrainte
CREATE TABLE clients(NumClient integer PRIMARY KEY,Nom char(30) NOT NULL,Age integer CONSTRAINT verifAge CHECK (Age>0 AND Age < 100));
100
SQL – Définition d’une clé SQL – Définition d’une clé primaireprimaire
Clause PRIMARY KEY(att1,att2,…) . Deux écritures possibles :
CREATE TABLE clients(NumClient integer PRIMARY KEY,Nom char(30) NOT NULL,Age integer CHECK (age>0 AND age < 100));
ou
CREATE TABLE clients(NumClient integer,Nom char(30) NOT NULL,Age integer CHECK (age>0 AND age < 100),PRIMARY KEY (NumClient));
101
SQL – Définition d’une clé SQL – Définition d’une clé étrangèreétrangère
Clause FOREIGN KEY(…) REFERENCES ...
CREATE TABLE clients(NumClient integer PRIMARY KEY,Nom char(30) NOT NULL,Age integer CHECK (Age>0 AND Age < 100));
CREATE TABLE listecommandes(NumeroCommande integer,NumeroClient integer,DateCommande date NOT NULL,PRIMARY KEY (NumeroCommande),FOREIGN KEY (NumeroClient) REFERENCES clients(NumClient));
Si une valeur de NumClient n ’existe pas dans la table clients, elle ne pourra pas être utilisée comme valeur de NumeroClient dans listecommandes Contrainte d ’intégrité.
102
SQL – Contraintes d’intégrité SQL – Contraintes d’intégrité référentielleréférentielle
Ensemble de règles définies entre plusieurs tables qui permet de garantir qu ’une clé pointant sur une autre table fait référence à une valeur existante.
FOREIGN KEY(…) REFERENCES … ON DELETE ...
ON UPDATE ...
- ON DELETE CASCADE indique la suppression en cascade des lignes de la table étrangère dont les clés étrangères correspondent aux clés primaires des lignes effacées- ON DELETE SET NULL place la valeur NULL dans la ligne de la table étrangère en cas d'effacement d'une valeur correspondant à la clé
- ON DELETE SET DEFAULT place la valeur par défaut (qui suit ce paramètre) dans la ligne de la table étrangère en cas d'effacement d'une valeur correspondant à la clé
- ON DELETE RESTRICT interdit la suppression de la ligne.
ON DELETE est suivi d'arguments permettant de spécifier l'action à réaliser en cas d'effacement d'une ligne de la table faisant partie de la clé étrangère :
103
Exemple :
La suppression d ’un client (de son numéro NumClient) dans la table clients, va entraîner dans la table listecommandes, la suppression de toutes les lignes auquel le numéro du client (NumeroClient) correspond.
CREATE TABLE clients(NumClient integer PRIMARY KEY,Nom char(30) NOT NULL,Age integer);
CREATE TABLE listecommandes(NumeroCommande integer,NumeroClient integer,DateCommande date NOT NULL,PRIMARY KEY (NumeroCommande),FOREIGN KEY (NumeroClient) REFERENCES clients(NumClient) ON DELETE CASCADE);
SQL – Contraintes d’intégrité SQL – Contraintes d’intégrité référentielleréférentielle
104
FOREIGN KEY(…) REFERENCES … ON DELETE ...
ON UPDATE ...
- ON UPDATE CASCADE indique la modification en cascade des lignes de la table étrangère dont les clés étrangères correspondent aux clés primaires des lignes effacées- ON UPDATE SET NULL place la valeur NULL dans la ligne de la table étrangère en cas de modification d'une valeur correspondant à la clé
- ON UPDATE SET DEFAULT place la valeur par défaut (qui suit ce paramètre) dans la ligne de la table étrangère en cas de modification d'une valeur correspondant à la clé
- ON UPDATE RESTRICT interdit la modification d ’une ligne.
ON UPDATE est suivi d'arguments entre accolades permettant de spécifier l'action à réaliser en cas de modification d'une ligne de la table faisant partie de la clé étrangère.
SQL – Contraintes d’intégrité SQL – Contraintes d’intégrité référentielleréférentielle
105
SQL – Destruction de tablesSQL – Destruction de tables
DROP TABLE table1, table2, ... ;
Supprime non seulement le contenu, mais aussi la structure même des tables ainsi détruites.
Commande DROPCommande DROP
106
SQL – Modification de tablesSQL – Modification de tables
Ajouter un attribut :
ALTER TABLE nom_tableADD [COLUMN] nouvel_attribut type_de_donnees;
Ajouter plusieurs attributs :
ALTER TABLE nom_tableADD ( nouvel_attribut_1 type_de_donnees_1,
nouvel_attribut_2 type_de_donnees_2,
... nouvel_attribut_n
type_de_donnees_n);
Commande ALTER TABLECommande ALTER TABLE
107
SQL – Modification de tablesSQL – Modification de tables
Modifier un attribut :
ALTER TABLE nom_table MODIFY [COLUMN] attribut nouveau_type_de_donnees;
Modifier plusieurs attributs :
ALTER TABLE nom_tableMODIFY ( attribut_1 nouveau_ type_de_donnees_1,
attribut_2 nouveau_ type_de_donnees_2,... attribut_n nouveau_ type_de_donnees_n);
Commande ALTER TABLECommande ALTER TABLE
Modifier une contrainte (nommée avec CONSTRAINT) :
ALTER TABLE nom_table MODIFY CONSTRAINT nom_contrainte;
108
SQL – Modification de tablesSQL – Modification de tables
Supprimer un attribut :
ALTER TABLE nom_table DROP [COLUMN] attribut_a_supprimer;
Commande ALTER TABLECommande ALTER TABLE
Supprimer une contrainte (nommée avec CONSTRAINT) :
ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte;
109
SQL – Insertion de lignesSQL – Insertion de lignes
INSERT INTO table [(att1,att2…)] VALUES (…);
Exemple :CREATE TABLE films(Num integer PRIMARY KEY,Nom char(30) NOT NULL,Annee integer, check(Annee>1900 and Annee<2007),Genre char(20) NOT NULL DEFAULT ’Fantastique’);
INSERT INTO films VALUES (’19700’, ’Kill Bill 1’, ’2003’, ’Action’);
INSERT INTO films (Num,Nom,Annee) VALUES (’19701’, ’Le Seigneur des Anneaux 2’, ’2002’); L ’attribut « Genre » qui ne peut pas avoir une valeur NULL, prend sa valeur par défaut « Fantastique »
Insertion de lignes : INSERT INTOInsertion de lignes : INSERT INTO
INSERT INTO films VALUES (’19701’, ’Le Seigneur des Anneaux 2’, ’2002’, DEFAULT);
110
SQL – Suppression de lignesSQL – Suppression de lignes
DELETE FROM Nom_Table[WHERE Condition] ;
Exemples :DELETE FROM FilmsWHERE Annee= ’2002’;
Suppression de lignes : DELETE FROMSuppression de lignes : DELETE FROM
DELETE FROM FilmsWHERE Genre= ’Action’ OR GENRE=’Comédie’;
DELETE FROM Films; Efface toutes les données, mais garde la structure (attributs, types de données…) intacte.
Equivalent : TRUNCATE TABLE Films;
111
SQL – Modification de SQL – Modification de donnéesdonnées
UPDATE Nom_TableSET att1 = valeur1[, att2 = valeur2, ...][WHERE Condition]
Exemple :UPDATE PersonnelSET Nom= ’Lagaffe’, Service=’Courrier’WHERE Nom= ’Laggafe’;
Toutes les lignes de la table Personnel où Nom=« Laggafe » vont être modifiées.
UPDATEUPDATE
112
SQL – Création d’une table à SQL – Création d’une table à partir d’une table existantepartir d’une table existante
SELECT * INTO FilmsRecents FROM Films WHERE Annee >= ’2001';
Une nouvelle table « FilmsRecents » est créée à partir de la table « Films », on ne garde que les lignes où la valeur de l ’attribut Annee est supérieure ou égale à 2001.
SELECT INTOSELECT INTO
CREATE TABLE… AS
CREATE TABLE FilmsRecents AS SELECT * FROM Films WHERE Annee >= '2001';
113
SQL – Renommage SQL – Renommage d’attributsd’attributs
SELECT Num FROM ListeCommandes AS CdesMorbihanWHERE CodePostal LIKE ’56%’;
Le résultat sera une colonne nommée « CdesMorbihan » comprenant les lignes de ListeCommandes où la valeur de CodePostal commence par « 56 ».
ASAS
AS peut aussi servir à donner un autre nom (alias) aux tables.
114
SQL – Valeurs par défautSQL – Valeurs par défaut
Définir une valeur par défaut
Le langage SQL permet de définir une valeur par défaut lorsqu'un champ de la base n'est pas renseigné grâce à la clause DEFAULT. Cela permet notamment de faciliter la création de tables, ainsi que de garantir qu'un champ ne sera pas vide.
La clause DEFAULT doit être suivie par la valeur à affecter. Cette valeur peut être un des types suivants :
• constante numérique • constante alphanumérique (chaîne de caractères)• le mot clé NULL • le mot clé CURRENT_DATE (date de saisie) • le mot clé CURRENT_TIME (heure de saisie) • le mot clé CURRENT_TIMESTAMP (date et heure de saisie)
115
SQL – Opérations sur les SQL – Opérations sur les datesdates
ADD_MONTHS(d,n) : Ajoute n mois à la date d
LAST_DAY(d) : Donne le dernier jour du mois de la date d
MONTHS_BETWEEN(d1,d2) : Nombre de mois entre d1 et d2
NEW_TIME(d,a,b) : Donne la date et heure pour le fuseau horaire b d'une date d dans le fuseau a (a et b dépendent de la SGBD utilisée)
NEXT_DAY(d,ch) : Donne la date du 1er jour de la semaine ch qui suit ou est égal à la date d
SYSDATE : Date et heure courante du système
TO_CHAR(d,f) : Transforme la date d en chaîne de caractères suivant le format f
TO_DATE(ch,f) : Transforme la chaîne ch conforme au format f en date.
116
SQL – Opérations sur les SQL – Opérations sur les types de donnéestypes de données
Concaténation de chaînes de caractèresConcaténation de chaînes de caractères
SELECT chaine1 || chaine2 || chaine3 (etc…)FROM table;
Les chaînes de caractères peuvent être des attributs ou des chaînes explicites ( ’bonjour’ )
Conversion de type de donnéesConversion de type de données
CAST( expression AS type de données )
Exemples :
CAST (’31-01-2007 ’ as DATE);
117
SQL - JointuresSQL - JointuresDéfinition de jointuresDéfinition de jointures
Les jointures permettent d'exploiter pleinement le modèle relationnel des tables d'une base de données.
Elle sont faites pour mettre en relation deux (ou plus) tables concourant à rechercher la réponse à des interrogations. Une jointure permet donc de combiner les colonnes de plusieurs tables.
La condition de la commande ON permet de comparer les tables jointes par l'intermédiaire de champs dont les valeurs sont identiques et comparables.
SELECT tab.nom_champFROM nom_table AS tab[INNER | {{LEFT | RIGHT | FULL } [ OUTER]}] JOIN nom_table2 AS tab2ON Condition ;
118
SQL - JointuresSQL - JointuresINNER JOIN indique toutes les paires correspondantes des lignes renvoyées et supprime les lignes n'ayant pas de correspondance entre les deux tables.
Nom Superficie
Population
MerOcean
Groix 230014,82 Atlantique
Corse 2600008680 MéditerranéeOuessan
t93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
Iles
select I.Nom, I.MerOcean, V.VilleFROM Iles As IINNER JOIN VillesPrincipales As VON I.Nom = V.Nom ;
Corse Méditerranée
Ouessant
Atlantique
Mayotte Indien
Tahiti Pacifique
Ajaccio
Ouessant
Mamoudzou
Papeete
Nom Ville
Corse Ajaccio
Ouessant Ouessant
Mayotte Mamoudzou
Tahiti Papeete
VillesPrincipales
Noirmoutier Noirmoutier-en-l ’Ile
119
SQL - JointuresSQL - JointuresLEFT OUTER JOIN indique que toutes les lignes de la table de gauche ne respectant pas la condition de jointure sont incluses dans le jeu de résultats, et que les colonnes de sortie de l'autre table ont des valeurs NULL en plus de toutes les lignes renvoyées par la jointure interne.
select I.Nom, I.MerOcean, V.VilleFROM Iles As ILEFT OUTER JOIN VillesPrincipales As VON I.Nom = V.Nom ;
Nom
Groix
Corse
OuessantMayotte
Tahiti
MerOcean
Atlantique
MéditerranéeAtlantique
Indien
Pacifique
Ville
Ajaccio
Ouessant
Mamoudzou
Papeete
NULL
Nom Superficie
Population
MerOcean
Groix 230014,82 Atlantique
Corse 2600008680 MéditerranéeOuessan
t93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
Iles
Nom Ville
Corse Ajaccio
Ouessant Ouessant
Mayotte Mamoudzou
Tahiti Papeete
VillesPrincipales
Noirmoutier Noirmoutier-en-l ’Ile
120
SQL - JointuresSQL - JointuresRIGHT OUTER JOIN indique que toutes les lignes de la table de droite ne respectant pas la condition de jointure sont comprises dans le jeu de résultats, et que les colonnes de sortie correspondant à l'autre table ont des valeurs NULL en plus de toutes les lignes renvoyées par la jointure interne.
select I.Nom, I.MerOcean, V.VilleFROM Iles As IRIGHT OUTER JOIN VillesPrincipales As VON I.Nom = V.Nom ;
Corse
Ouessant
Mayotte
Tahiti
Méditerranée
Atlantique
Indien
Pacifique
Ajaccio
Ouessant
Mamoudzou
Papeete
NULL NULL Noirmoutier-en-l ’Ile
Nom Superficie
Population
MerOcean
Groix 230014,82 Atlantique
Corse 2600008680 MéditerranéeOuessan
t93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
Iles
Nom Ville
Corse Ajaccio
Ouessant Ouessant
Mayotte Mamoudzou
Tahiti Papeete
VillesPrincipales
Noirmoutier Noirmoutier-en-l ’Ile
121
SQL - JointuresSQL - JointuresFULL OUTER JOIN indique qu'une ligne de la table de gauche ou de droite, ne respectant pas la condition de jointure, est comprise dans le jeu de résultats et que les colonnes de sortie correspondant à l'autre table comportent des valeurs NULL.
select I.Nom, I.MerOcean, V.VilleFROM Iles As IFULL OUTER JOIN VillesPrincipales As VON I.Nom = V.Nom ;
Corse
Ouessant
Mayotte
Tahiti
Méditerranée
Atlantique
Indien
Pacifique
Ajaccio
Ouessant
Mamoudzou
Papeete
NULL NULL Noirmoutier-en-l ’Ile
Groix Atlantique NULL
Nom Superficie
Population
MerOcean
Groix 230014,82 Atlantique
Corse 2600008680 MéditerranéeOuessan
t93215,58 Atlantique
Mayotte 200000374 Indien
Tahiti 1700001043 Pacifique
Iles
Nom Ville
Corse Ajaccio
Ouessant Ouessant
Mayotte Mamoudzou
Tahiti Papeete
VillesPrincipales
Noirmoutier Noirmoutier-en-l ’Ile
122
SQL – Jointure complèteSQL – Jointure complèteA noterA noter
SELECT I.Nom, I.MerOcean, V.VilleFROM Iles As IFULL OUTER JOIN VillesPrincipales As VON I.Nom = V.Nom ;
est équivalent à :
SELECT I.Nom, I.MerOcean, V.VilleFROM Iles As ILEFT OUTER JOIN VillesPrincipales As VON I.Nom = V.NomUNIONselect I.Nom, I.MerOcean, V.VilleFROM Iles As IRIGHT OUTER JOIN VillesPrincipales As VON I.Nom = V.Nom;
123
SQL – Les vuesSQL – Les vues
Vue : définitionVue : définition
Une vue est une « table virtuelle » produite par une requête de sélection.
Une vue contient donc un jeu d'enregistrements constitué par une requête SELECT prédéfinie appliquée à une ou plusieurs tables d'une base de données.
A la différence d'une table, une vue ne consomme pas d'espace de stockage physique mais conserve des propriétés semblables dans la mesure ou toutes les opérations de sélection, d'insertion, de mise à jour ou encore de suppression de lignes de données avec néanmoins quelques restrictions comme l'interdiction de modification d'une vue créée à partir de plusieurs tables.
Les vues peuvent servir à recueillir des données régulièrement consultées et mises à jour.
124
SQL – Les vuesSQL – Les vues
Les vues sont souvent employées pour des raisons de sécurité. A l'aide de cet outil, il devient possible de ne montrer qu'une partie des données d'une table et ainsi, cacher des informations confidentielles.
Le créateur de la vue en détient la propriété et les privilèges afférents à l'instar d'une table. En conséquence, le propriétaire de la vue peut accorder des droits aux différents utilisateurs de la base de données.
Une vue peut être utilisée de la même façon qu'une table. Des requêtes de sélection ou de manipulation de données peuvent être appliquées à la vue.
Les vues peuvent même être créées depuis d’autres vues.
125
SQL – Les vuesSQL – Les vuesSyntaxe :Syntaxe :
Syntaxe : CREATE VIEW nom_vue AS SELECT (att1,att2…) FROM nom_table
Exemple : CREATE VIEW v AS SELECT Quantite, PrixUnit, Quantite*PrixUnit AS Valeur FROM table1;
DROP VIEW nom_vue : permet de détruire la vue
RENAME nom_vue to nouveau_nom : permet de la renommer.
ALTER VIEW nom_vue [(liste_d_attributs)] AS (SELECT…) : permet de modifier la vue.
126
SQL – Les indexSQL – Les index
Notion non comprise initialement dans la norme SQL, mais utilisée couramment dans de nombreuses SGBD.
Ce sont en revanche des éléments indispensables à une exploitation performante de base de données (surtout de grande dimension). En effet un index permet de spécifier au SGBDR qu'il convient de créer une structure de données adéquate afin de stocker les données dans un ordre précis.
Par conséquent les recherches et en particuliers les comparaisons, notamment pour les jointures, sont notablement accélérées.
127
SQL – Les indexSQL – Les index
Considérons le SELECT suivant :
SELECT * FROM employes WHERE nom = 'MARTIN'
Un moyen de retrouver la ou les lignes pour lesquelles nom est égal à MARTIN est de balayer toute la table.
Un tel moyen d'accès, si la table est grande, peut conduire à des temps de réponse prohibitifs (à partir de quelques centaines de lignes).
Création d ’index : une fois l’index créé (portant sur un ou plusieurs attributs classés par ordre croissant ou décroissant), il deviendra plus rapide d’accéder aux données dans la table concernée, puisque la recherche portera sur des valeurs d’index à partir de ou jusqu’à une certaine limite.
128
SQL – Les indexSQL – Les index
L'adjonction d'un index à une table ralentit les mises à jour (insertion, suppression, modification de la clé) mais accélère beaucoup la recherche d'une ligne dans la table.
L'index accélère la recherche d'une ligne à partir d'une valeur donnée de clé, mais aussi la recherche des lignes ayant une valeur d'index supérieure ou inférieure à une valeur donnée, car les valeurs de clés sont triées dans l'index.
129
SQL – Les indexSQL – Les index
Création d’un indexCréation d’un index
CREATE INDEX nom_index ON nom_table(attribut);
CREATE INDEX nom_index ON nom_table(attribut1, attribut2, …);
Suppression d’un indexSuppression d’un index
DROP INDEX nom_index [ON nom_table];
130
SQL - Contrôle de l ’accès SQL - Contrôle de l ’accès aux données aux données
Créer un utilisateurCréer un utilisateur
CREATE ROLECREATE ROLE
Permet de définir un « rôle » (entité représentant un utilisateur ou groupe d’utilisateurs) dans la base de données.
CREATE ROLE nom [ [ WITH ] option [ ... ] ]
131
SQL - Contrôle de l ’accès SQL - Contrôle de l ’accès aux données aux données
Créer un utilisateurCréer un utilisateur
CREATE ROLECREATE ROLE
Exemples :
CREATE ROLE jonathan LOGIN;
CREATE ROLE camille WITH LOGIN PASSWORD 'jw8s0F4' VALID UNTIL '2008-01-01';
CREATE ROLE ines WITH CREATEDB CREATEROLE;
CREATE ROLE bretons WITH ROLE erwan, gwendal, kilian WITH ADMIN OPTION;
Créer un rôle qui peut se connecter mais sans lui donner de mot de passe.
Créer un rôle qui peut se connecter, avec un mot de passe associé, valide jusqu ’à une certaine date.
Créer un rôle qui peut créer des bases de données et des rôles.
Créer un rôle formé de plusieurs rôles. WITH ADMIN OPTION permet aux rôles erwan, gwendal, kilian, d ’autoriser à d ’autres rôles l’appartenance à bretons.
132
SQL - Contrôle de l ’accès SQL - Contrôle de l ’accès aux données aux données
Donner des droitsDonner des droits
GRANTGRANT
GRANT [[SELECT, UPDATE, INSERT, DELETE, ALTER, INDEX] ALL] ON NomTable TO NomUtilisateur
Exemples :
GRANT UPDATE(Commande,Quantité) ON Commandes TO Bertrand;
Bertrand a le droit de modifier les valeurs des attributs « Commande » et « Quantité » dans la table Commandes.
GRANT SELECT ON Fournisseurs TO PUBLIC;
Tout le monde peut utiliser la commande SELECT dans la table Fournisseurs
133
SQL - Contrôle de l ’accès SQL - Contrôle de l ’accès aux données aux données
Retirer des droitsRetirer des droits
REVOKEREVOKE
REVOKE [[SELECT, UPDATE, INSERT, DELETE, ALTER, INDEX] ALL] ON NomTable TO NomUtilisateur
GRANT ALL ON Clients TO Sophie;REVOKE DELETE ON Clients TO Sophie;
Sophie a tous les droits de lecture/écriture sur la table Clients, sauf celui de supprimer des lignes.
Exemples :
REVOKE UPDATE(Commande,Quantité) ON Commandes TO Bertrand;
Bertrand n ’a plus le droit de modifier les valeurs des attributs « Commande » et « Quantité » dans la table Commandes.