Post on 16-Jun-2022
Bases de données
Pierre-Alexandre Hébert
hebert@lisic.univ-littoral.fr
0. Objet du cours
0.1 Programme du module
De la conception à l’implantation d’une base de données + manipulation– Introduction aux bases de données– Modélisation : modèle E-A et UML– Modèle relationnel– Algèbre relationnel– Langage SQL– Normalisation– Contrôle d’accès aux données (SQL)
0.2 Historique
1969 : Essor des bases de données, grâce aux travaux de Codd sur l’approche relationnelle
1974 : Ancêtre du langage SQL, SEQUEL, par Chamberlin
1979 : Premier SGBD relationnels commerciaux (Oracle), puis SQL/DS cher IBM
1989, 1999 : normalisation ANSI du langage SQL
1. Introduction aux bases de données
1.1 Base de données
Définition– Ensemble volumineux, structuré et minimalement
redondant de données, liées entre elles, stockées sur supports numériques, servant pour les besoins d’une ou plusieurs applications, interrogeables, modifiables par un ou plusieurs utilisateurs, travaillant potentiellement en parallèle
– Ex d’une compagnie aérienne voyageurs, vols, avions, personnel, réservations, etc.
1.2 SGBD
Système de Gestion de Bases de Données– logiciel prenant en charge la structuration, le
stockage, la mise à jour, la maintenance, et la consultation d’une base de données
– Interface avec les données
Exemples– Oracle, MySQL et Access = SGBD relationnels
1.2 SGBD
Intérêt des SGBD– Avant leur émergence
anarchie– applications de gestions de données indépendantes– langages propres, normes propres, fichiers propres
conséquences– multiplication des tâches de saisie, de développement et de
support : coût important– redondance des données– incohérences des données– non-portabilité des traitements– dépendance vis à vis des moyens matériels– restructuration des données dissuasive
1.2 SGBD
Objectifs– Indépendance physique des données– Indépendance logique des données– Manipulation des données par des non-informaticiens
(IHM)– Administration facilitée des données– Optimisation de l’accès aux données– Préservation de la cohérence des données (intégrité)– Partage des données– Sécurité des données (confidentialité)– Sûreté des données (persistance)
1.3 Notion de données
Types de données– Ensemble d’objets partageant des caractéristiques
communes : classe
Données– Élément correspondant à un type de données :
occurrence, instance
Exemple– Livre = (Numéro ISBN, Titre, Auteur, Editeur)– (2100068245, Introduction aux bases de données
relationnelles, Mata-Toledo, EdiScience)
1.4 Conception de BDD
Trois niveaux de modélisation des BDD (Namur 1974)– Niveau conceptuel
description du réel, sans contraintes techniques
– Niveau logique description d’une solution de modélisation, techniquement
orientée, mais indépendante de tout choix d’implémentation
– Niveau physique solution technique : programmation + optimisation
+ Niveau externe (ANSI 1975)– prise en compte du point de vue des utilisateurs
vues, confidentialité
1.5 Exemple
Cas traité– Stages effectués par des étudiants
Niveau Conceptuel - UML
Etudiant
Num. InseeNom
Prénom
Stage
Num. StageTitre
Encadrant
Est effectué par
0..1 1
1.5 Exemple
Niveau Logique – Relationnel– Deux tables relationnelles
Etudiant(Num. INSEE, Nom, Prénom) Stage(Num. Stage,Num. INSEE, Titre, Encadrant)
Niveau physique– création des tables en SQL sur MySQL– entrée des données– exemple de consultation des étudiants :
Num. INSEE Nom Prénom
140116244795612 Dali Salvador
150086238218765 Picasso Robert
1.6 Notion de langage de données
Définition– Langage informatique permettant de décrire le
modèle d’une base de données d’une manière assimilable par la machine (niveau physique)
Langage de Définition de Données (LDD) Langage de Contrôle de Données (LCD) Langage de Manipulation de Données (LMD)
1.7 Notion d’administration de données
Administrateur entreprise– gestion du schéma conceptuel et des règles de
contrôle des données
Administrateur de données– gestion des schémas externes et de leur
correspondance avec le schéma conceptuel
Administrateur de bases de données– gestion du schéma interne (= logique + physique) et
de sa correspondance avec le schéma conceptuel
2. Le niveau conceptuel
2.1 Introduction
Rôle– Expression d’une vue abstraite de la base de
données, de manière graphique– Restriction à l’aspect statique des données
Basée sur– une analyse de la situation existante et des besoins
Deux modèles principaux– Entité-Association ou Entité-Relation
c’est le modèle conceptuel des données de Merise– UML
Plus récent Spectre plus large : POO
2.2 Analyse de l’existant
Etude des usages– Analyse des documents existants
fiches, procédures, bilans, statistiques, intranet, etc.
– Recueil d’expertise métier– Dialogue avec les usagers– Etude des systèmes d’informations existants
2.3 Modèle E-A : définitions
Entité– concept concret ou abstrait du monde à modéliser
Attribut– donnée élémentaire, ou propriété, qui sert à caractériser
entités et associations
Identifiant ou Clé– attribut particulier permettant d’identifier chaque occurrence
d’une entité
Exemple Etudiant
Num. InseeNom
Prénom
2.3 Modèle E-A : définitions
Attribut– atomique
une seule valeur par occurrence– élémentaire
on ne peut le dériver d’autres attributs
Association– Lien permettant de relier les entités entre elles
en général : verbal
Occurrence– Élément particulier d’une entité ou d’une association
Degré d’une association = Arité– Nombre d’entités participant à l’association– Association binaire : degré 2
2.3 Modèle E-A : définitions
Cardinalité– Couple de valeurs (min,max) indiqué à l’extrémité de chaque
lien d’une association– Caractérise la nature de l’association en fonction des
occurrences des entités concernées– Les cardinalités traduisent les possibilités de participation
(min,max) d’une occurrence quelconque d’une entité aux occurrences d’une association
ExempleLivre
Num. ISBNTitre
Editeur
Auteur
Num. AuteurNom
Prénom
(1,1) (1,N)
Ecrit par
2.4 Modèle E-A : Extension
Extension du modèle E-A– Attributs composites
ex: adresse = Numéro, Rue, N°appt, Ville, Code Postal
– Attributs multivaluésex: âges des enfants d’un employé
– Attributs dérivésex: âge dérivé de la date de naissance
– Sous-type d’entitéHéritage
2.5 Modèle E-A : entité de type faible
Entité de type faible– Elles n’existent qu’en référence à une entité
identifiante– Entité non identifiée
sa clé locale (identifiant relatif) n’identifie chacune de ses occurrences que parmi celles associées à la même occurrence de l’entité identifiante
Exemple
Projet
N°ProjetNom
Date début
Tâche
N° TâcheDescription(0,N) (1,1)
composer ( R )
2.6 Modèle UML : définitions Diagrammes de classes
– 1997 : naissance de l’Unified Modeling Language– modélisation objet indépendante de tout langage– peut se substituer au modèle E-A
Classes [entité]– Description abstraite d’un ensemble d’objets de même structure et de
même comportement Objet [Occurrence] Attributs [idem]
– propriétés typées servant à caractériser les classes et relations Méthodes [absent]
– opérations programmées sur les objets d’une classe Association [idem]
– relie une classe à plusieurs autres classes Multiplicité [cardinalité]
2.7 Modèle UML : ex. de classe
Classe Homme
Homme
N°INSEE : string(15)Nom : string(20)Sexe : boolDateNaissance : date/ Age : integer
Rire()
<< description >>Age = Maintenant() - DateNaissance
2.8 Notation des cardinalités
Correspondance E-A / UML
Attention : notation de la cardinalité inversée
Entité-Association UML
0,1 0..1
1,1 1 (ou pas de cardinalité)
0,N 0..* ou *
1,N 1..*
N,M ou N,N N..N
Livre
Num. ISBNTitre
Editeur
Auteur
Num. AuteurNom
Prénom
1..* 1
Ecrit par >
2.9 Trois grands types de cardinalité pour les asso. binaires
Associations 1-1– cardinalités maximales égale à 1– ex: « Étudiant effectue Stage »
Associations 1-N : « père-fils »– une cardinalité maximale à 1, l’autre à N– ex: « UV sous la responsabilité d’un Enseignant »– rôles « père », « fils » représentables en UML
Association N-N– cardinalités maximales à N– ex: « Étudiant vœux Stage »– Supporte les attributs
2.10 Modèle UML : asso. N-N avec attributs
Classe-association sous UML
Departement
CodeNom
Budget
Logiciel
NomEditeur
1..* 1..*
Achat
DateCoût
2.11 Modèle UML : Composition
La composition associe une classe composite et des classes parties, telle que tout objet partie appartient à un et un seul objet composite
Association 1:N Composition non partageable Cycles de vie liés
Livre
ISBNTitre
Chapitre
NuméroNom
1 1..*
2.12 Modèle UML : Héritage
UML ; mais existe aussi dans Merise/2 L’héritage permet de représenter le rapport de
généralisation/spécialisation entre classes « A’ est hérite de A » signifie que « A’ est une
sous-classe de A » A’ hérite alors de l’ensemble des attributs et
méthodes de A
Homme
NomPrénom
Age
Super-hérosSuper-pouvoirs
2.12 Modèle UML : Héritage
Classe abstraite– non instanciée– donc forcément héritée
Notation italique
Mammifère Humain
FemmeHomme
2.13 Modèle UML : Héritage Multiple
Exemple
EnseignantN°
Matière
Eleve IUFM
StagiaireN°
Date inscription
2.14 Règles de validation
Origine– Inspirées des principes de normalisation du modèle
relationnel
Objectif– s’assurer de la cohérence de la BDD dès le modèle
conceptuel
Quel modèle– UML– mais existe de façon équivalente dans Merise
2.15 Règles de validation
Règles sur les attributs– atomiques (ou non-décomposables)– non-redondant
1 seule occurrence dans le diagramme
1ère forme normale [du modèle conceptuel]– toute classe a son identifiant– tout attribut = monovalué– rapport à la dépendance fonctionnelle
1 valeur identifiant => 1 valeur attribut
ExempleSuper-héros
IdNom
PrénomSuper-pouvoirs[1..n]
Super-héros
IdNom
Prénom
Super-pouvoir
IdDescription
1..*1..*
2.15 Règles de validation
2ième forme normale [du modèle conceptuel]– tout attribut d’une classe dépend exclusivement de l’identifiant
de la classe– tout attribut d’une classe-asso. dépend conjointement des
identifiants des classes liées à l’association
ExempleSuper-héros
IdNom
Prénom
Super-pouvoir
IdDescription
1..*1..*
Usage
DateDate dernière intervention
Si dernière intervention indépendante du type de super-pouvoir utilisé
2.15 Règles de validation
3ième forme normale [du modèle conceptuel]– tout attribut d’une classe dépend de l’identifiant de la classe– tout attribut d’une classe-asso. dépend conjointement des
identifiants des classes liées à l’association– pas de dépendance entre attributs non-identifiants
Exemple
Logiciel
NomPrix
EditeurSiège social éditeur
Logiciel
Nom
Editeur
NomSiège social
11..*
< Edite
2.16 Exemple E-A : Centre Médical
2.17 Exemple E-A : Tennis
2.18 Exemple E-A : Journal
2.19 Exemple E-A : Transport
2.20 Exemple UML : Usine
3. Le niveau logique
3.1 Introduction
Modèle logique– modèle formel qui spécifie la manière dont les données vont exister
dans l’application (alors que le modèle conceptuel spécifie la réalité existante)
Modèle relationnel– Ensemble de concepts destinés à formaliser logiquement l’utilisation
de fichiers plats indépendamment de la façon dont ils seront physiquement stockés dans la mémoire numérique
Description des données Manipulation des données
Objectifs– indépendance représentation interne des données et l’application– gestion des problèmes de cohérence et de redondances des
données– utilisation de langages basés sur des théories solides
3.2 Définitions
Domaine– ensemble dans lequel les données prennent leurs
valeurs– définition en intention / en extension
Relation– caractérisée par un ensemble d’attributs, définis
chacun sur un domaine– constituée d’éléments qui se distinguent par au
moins une valeur de leurs attributs tuples ou n-uplets ou vecteurs
– définie (en extension) par l’ensemble de ses n-uplets
3.2 Définitions
Produit cartésien– Le produit cartésien des domaines D1,D2,…,Dn noté
D1xD2x…xDn est l’ensemble des tuples que l’on peut définir sur ces domaines
– Exemple D1 = {1,2} D2 = {A,B,C} D1xD2 = {1A,1B,1C,2A,2B,2C}
Relation = objet mathématique– sous-ensemble du produit cartésien des domaines
de ses attributs– Exemple
R = {1B,1C,2C}
3.3 Représentation d’une relation
Relation représentée sous forme de table
Correspondances– Ligne
enregistrements = tuples
– Colonne champs = attributs
Remarque– pas d’ordres entre les lignes
INSEE Nom Prénom
140116244795612 Dali Salvador
150086238218765 Picasso Robert
3.4 Clés
Clé– Ensemble d’attributs minimal qui permet de déterminer tout
tuple de la relation de façon unique– Minimalité
tout sous-ensemble de la clé ne suffit pas à déterminer les tuples– Exemple
N°INSEE = clé de la relation Etudiant (N°INSEE,Nom) clé, car non minimal
Toute relation a au moins une clé Clé primaire / clé candidates
– clé primaire : l’élue ; en général, la plus simple– clés candidates : les autres clés
Clé étrangère : lien entre relations– groupe d’attributs correspondant à une clé dans une autre
relation
3.4 Clés
Détermination d’une clé– basée sur la sémantique
Si les clés candidates sont trop complexes– création d’une clé artificielle– si vraiment utile
(Mauvais) exemple : boîtes de conserves
Contenu Marque Prix
Ravioli Auchan 1.8
Ravioli Buitoni 2.4
ID Contenu Marque Prix
12 Ravioli Auchan 1.8
13 Ravioli Buitoni 2.4
3.5 Schéma relationnel
Schéma d’une relation– précise les couples attributs/domaines– la clé primaire– les clés étrangères
Exemple– Personne(Numéro:entier, Nom:chaîne, LieuNaissance=>Ville)– Pays(Nom:chaîne, Population:entier, Superficie:entier,
Chef=>Personne)– Région(Nom:chaîne, Pays=>Pays, Superficie:entier)– Ville(Codepostal:CP, Nom, Région=>Région.Nom
Pays=>Région.Pays)
– où CP = « 1 ou 2 lettres – entier »
3.6 Passage UML vers Relationnel
Transformation des classes– toute entité devient une relation– l’identifiant devient sa clé primaire
Transformation des associations– Associations 1-N : « père-fils »
Inclusion d’une clé étrangère dans la relation correspondant à la classe fils, qui désigne la clé primaire de la classe père
Logiciel
Nom
Editeur
NomSiège social
11..*
< Edite
Editeur(Nom,SiègeSocial)Logiciel(Nom,NomEditeur=>Editeur)
3.6 Passage UML vers Relationnel
Transformation des associations– Associations N-N, n-aires et classes-asso
L’association/classe-asso. devient une relation Sa clé primaire est définie comme la concaténation des
identifiants des classes associées Ces identifiants deviennent chacun clé étrangère
Avion
ImmatType
CompagnieN°
Nom
0..*1..*
Compagnie(N°, Nom)Affréter(Immat=>Avion, NomComp=>Compagnie, Date)Avion(Immat, Type)
AffréterDate
3.6 Passage UML vers Relationnel
Transformation des associations– Associations 1-1
Si une cardinalité minimale à zéro– ajout d’un attribut clé étrangère dans l’autre entité
Si deux cardinalités à zéro : au choix Sinon : fusion des deux entités !
Rôle de la sémantique– ex: homme/femme/mariage
Stage
N°Entreprise
EdudiantINSEENom
0..11
Stage(N°, Entreprise)Etudiant(INSEE, Nom, N°Stage=>Stage)
< Effectue
3.6 Passage UML vers Relationnel
Transformation des compositions– Comme une association 1:N– mais la clé de la classe composite est ajoutée à la
clé de la classe partie (tout en restant décrite comme une clé étrangère)
Livre
ISBNTitre
ChapitreN°
Nom
1..N1
Livre(ISBN, Titre)Chapitre(N°, ISBN=>Livre, Nom)
composé de >
3.7 Traduction de l’héritage
Décomposition par distinction– Transformation par référence– Classes mère et filles sont transformées en
relations– La clé primaire de la classe mère devient clé
primaire et clé étrangère dans ses classes fillesDocument
TitreAuteur
LivreISBN
Editeur
ThèseDiscipline
Document(Titre, Auteur)Livre(Titre=>Document, ISBN, Editeur)Thèse(Titre=>Document, Discipline)
3.7 Traduction de l’héritage
Décomposition descendante– Transformation par les classes filles– Classes filles sont transformées en relations
les attributs de la classes mère y sont reproduits la clé de la classe mère devient clé primaire
– Si la classe mère n’est pas abstraite elle devient une relation qui ne contient que les tuples qui ne sont
pas des instances de ses classes fillesDocument
TitreAuteur
LivreISBN
Editeur
ThèseDiscipline
Livre(Titre, ISBN, Editeur, Auteur)Thèse(Titre, Discipline, Auteur)
3.7 Traduction de l’héritage
Décomposition ascendante– Transformation par la classe mère– Classe mère transformée en relation– Migration des attributs des classes filles dans la relation de la
classe mère
Héritage et clé primaire– C’est toujours la clé primaire de la classe mère qui est retenue
comme clé primaire des classes filles (même à plusieurs niv.)
Document
TitreAuteur
LivreISBN
Editeur
ThèseDiscipline
Document(Titre, Auteur, Editeur, Discipline, ISBN)
3.7 Choix de la transformation de l’héritage
Décomposition par distinction– Limites : lourdeur, car classes filles impliquées dans 2 relations– Avantages : pas de redondance ni attributs inutiles (valeurs
nulles rendues nécessaires)– Usage : général, surtout si classe mère non abstraite
Décomposition descendante– Limites : redondance => risque d’incohérence– Avantages : moins de tuples, pas de clés étrangères– Usage : classe mère abstraite + héritage exclusif
Décomposition ascendante– Limites : introduction de valeurs nulles– Avantages : moins de tuples, pas de clés étrangères– Usage : classe mère non-abstraite + héritage complet
3.8 Les opérateurs relationnels : présentation
Objectif– manipuler les tables de la base de données– soit, à partir de tables, obtenir d’autres tables– à la base du langage SQL
Basé sur– l’algèbre relationnel
Opérateurs de base– Union, différence, projection, restriction, produit
cartésien Opérateurs additionnels
– Intersection, Jointure, Division
3.9 Les opérateurs relationnels : les opérateurs ensemblistes
Opérateurs binaires– portant sur des relations de même schéma– définissant une relation de même schéma
Union– Union(R1,R2) définit la relation dont l’ensemble des
tubles sont ou dans R1 ou dansR2
Exemple Homme(Nom, Prénom, Âge)
(Dupont, Pierre, 20)
(Durand, Jean, 30)
Femme(Nom, Prénom, Âge)
(Martin, Isabelle, 20)
(Tintin, Hélène, 30)
Union
3.9 Les opérateurs relationnels : les opérateurs ensemblistes
Union externe– avec des tables ayant des schémas différents– complétées avec des valeurs nulles
De même– Intersection– Différence– vides dans l’exemple précédent
3.10 Les opérateurs relationnels : la projection
Opération unaire La projection de R1 sur une partie de ses
attributs définit une relation R2
– dont le schéma se restreint aux attributs mentionnés– dont les tuples sont issus de ceux de R1
Exemple
Personne(Nom, Prénom, Âge)
(Dupont, Pierre, 25)
(Durand, Jean-Claude, 54)
Projection(Personne, Âge)
(25)
(54)
3.11 Les opérateurs relationnels : la restriction
Opération unaire La restriction de R1 selon la condition C définit
la relation R2 comme l’ensemble des tuples de R1 vérifiant C
Exemple
Personne(Nom, Prénom, Âge)
(Dupont, Pierre, 25)
(Durand, Jean-Claude, 54)
Restriction(Personne, Âge>30)
(Durand, Jean-Claude, 54)
3.12 Les opérateurs relationnels : le produit cartésien
Opération binaire Le produit cartésien de R1 et R2 définit R3 :
– ayant comme schéma la juxtaposition des attributs de R1 et R2
– ayant comme tuples toutes les combinaisons de couples que l’on peut constituer avec tuple de R1 et un tuple de R2
3.12 Les opérateurs relationnels : le produit cartésien
Exemple
Étudiant(Nom, Prénom)
(Dupont, Pierre)
(Durand, Jean-Claude)
Produit(Étudiant, Module) = Ensemble des inscriptions
(Dupont, Pierre, Mathématiques, 1)
(Dupont, Pierre, Électronique, 2)
(Durand, Jean-Claude, Mathématiques, 1)
(Durand, Jean-Claude, Électronique, 2)
Module(Nom, Niveau)
(Mathématiques, 1)
(Électronique, 2)
3.13 Les opérateurs relationnels : la jointure
C’est une restriction de produit cartésien, dont la condition porte sur des attributs communs aux deux relations
Exemple : jointure d’égalité
Homme(Nom, Prénom, Âge)
(Dupont, Pierre, 25)
(Durand, Jean-Claude, 54)
Jointure(Homme, Enfant, Homme.Nom = Enfant.Nom)
(Dupont, Pierre, 25, Dupont, Laura, 3)
(Dupont, Pierre, 25, Dupont, Léon, 5)
Enfant(Nom, Prénom, Âge)
(Dupont, Laura, 3)
(Dupont, Léon, 5)
3.13 Les opérateurs relationnels : la jointure
Jointure naturelle– condition = égalité sur des attributs identiques– Jointure + projection permettant d’éliminer l’attribut
doublon
Homme(Nom, Prénom, Âge)
(Dupont, Pierre, 25)
(Durand, Jean-Claude, 54)
Jointure naturelle(Homme, Enfant, Homme.Nom = Enfant.Nom)
(Dupont, Pierre, 25, Laura, 3)
(Dupont, Pierre, 25, Léon, 5)
Enfant(Nom, Prénom, Âge)
(Dupont, Laura, 3)
(Dupont, Léon, 5)
3.13 Les opérateurs relationnels : la jointure
Jointure externe sur R1 et R2
– Jointure augmentée des tuples de R1 et R2 exclus de la jointure, complétés par des valeurs nulles
Jointure externe gauche sur R1 et R2
– seuls les tuples de R1 sont ajoutés
Jointure externe droite sur R1 et R2
– seuls les tuples de R2 sont ajoutés
3.13 Les opérateurs relationnels : la jointure
Exemples de jointures externesHomme(Nom, Prénom, Âge)
(Dupont, Pierre, 25)
(Durand, Jean-Claude, 54)
Enfant(Nom, Prénom, Âge)
(Dupont, Laura, 3)
(Dupont, Léon, 5)
Jointure externe droite(Homme, Enfant, Homme.Nom = Enfant.Nom)
(Dupont, Pierre, 25, Dupont, Laura, 3)
(Dupont, Pierre, 25, Dupont, Léon, 5)
Jointure externe(Homme, Enfant, Homme.Nom = Enfant.Nom)
(Dupont, Pierre, 25, Dupont, Laura, 3)
(Dupont, Pierre, 25, Dupont, Léon, 5)
(Durand, Jean-Claude, 54, NULL, NULL, NULL)
3.14 Les opérateurs relationnels : la division
Opération binaire La division de R1 par R2 est la relation dont le
produit cartésien avec R2 est inclus dans R1
Exemple
Employé(Nom, Prénom, Âge, Métier)
(Dupont, Pierre, 25, Informaticien)
(Durand, Jean-Claude, 54, Informaticien)
(Durand, Jean-Claude, 54, Ingénieur)
Métier(Nom)
(Informaticien)
(Ingénieur)
Division(Employé)
(Durand, Jean-Claude, 54)
4. Le niveau physique : le langage SQL (variante MySQL)
4.1 Introduction
Niveau physique– Implémentation et manipulation d’une base de
données sur un SGBD
Langage SQL– langage standardisé, implémenté sur tous les SGBD
(Oracle, Access, MySQL, etc.)– indépendamment de la plate-forme utilisée, il permet
La définition des données (LDD) Leur manipulation (LMD) Leur contrôle (LCD)
4.2 Langage SQL
Provient du langage System R d’IBM (1976) Apparaît pour la première fois dans une
application commerciale avec Oracle (fin 1970) 1ère normalisation ANSI : SQL-86 2ième normalisation ANSI : SQL-92 = SQL2
– c’est la norme actuelle
SQL a été conçue en vue d’une intégration multi-langage– C, C++, Java, PHP, Python, etc.
Langage déclaratif et non procédural
4.3 LDD : types de données
Nombres entiers– BIGINT[(x)] : 8 octets– INTEGER[(x)] ou INT [(x)] : 4 octets
x = nombre minimal de chiffres affichés, si ZEROFILL– MEDIUMINT[(x)] : 3 octets– SMALLINT[(x)] : 2 octets– TINYINT[(x)] : 1 octet– BIT, BOOL, BOOLEAN = TINYINT(1)
Leur déclaration peut être suivi de :– [UNSIGNED]– [ZEROFILL]
4.3 LDD : types de données
Nombres réels ([UNSIGNED] [ZEROFILL])– FLOAT[(n,d)] : 4 octets– DOUBLE [(n,d)] = REAL[(n,d)] : 8 octets – DECIMAL[(n,d)] = NUMERIC[(n,d)]
nombre stocké comme une chaîne de caractères 1 caractère par chiffre
Chaînes de caractères– CHAR(n) [BINARY]
chaîne de longueur fixe, occupant n caractères BINARY : comparaisons tiennent compte de la classe
– VARCHAR(n) [BINARY] Chaîne de longueur variable, occupant au plus n
caractères
4.3 LDD : types de données
Chaînes de caractères insensibles à la casse– TINYTEXT : 28 (– 1) caractères– TEXT : 216 caractères– MEDIUMTEXT : 224 caractères– LONGTEXT : 232 caractères
Chaînes correspondantes sensibles à la casse– TINYBLOB– BLOB– MEDIUMBLOB– LONGBLOB
4.3 LDD : types de données
Dates & Temps– DATE : AAAA-MM-JJ– YEAR : AAAA– TIME : HH:MM:SS– TIMESTAMP[(n)] : jour entre 1/1/1970 et 1/1/2037
n = 14, 12, 10, 8 ou 6 nombre de caractères utilisés pour l’affichage, selon
format, et codage de l’heure ou pas si valeur non précisée, par défaut c’est la date courante
– DATETIME : date et heure AAAA-MM-JJ HH:MM:SS
4.4 LDD : création d’une base et de tables
Création d’une base
Dans MySQL, chaque table est stockée dans un fichier
SyntaxeCREATE TABLE [IF NOT EXISTS] <nom_table>(
<nom_colonne1> <type> [<propriété>]*,<nom_colonne2> <type> [<propriété>]*,…<nom_colonne1> <type> [<propriété>]*,[<contraintes_de_table>]
);
CREATE DATABASE <nom_base>;
4.4 LDD : création de tables
Propriétés de colonne– AUTO_INCREMENT– DEFAULT <valeur par défaut>– NULL– Contraintes d’intégrité : règle définissant la cohérence de la
base NOT NULL UNIQUE (si aussi NOT NULL alors colonne = clé candidate) PRIMARY KEY (définit clé primaire) ( NOT NULL + UNIQUE) CHECK(<condition>)
Contraintes d’intégrité sur une table– PRIMARY KEY(<liste_d’attributs>)– UNIQUE(<liste_d'attributs>)– + gestion des clés étrangères
4.5 LDD : gestion des clés étrangères sous MySQL
Table déclarant la clé étrangère et table référencée– de type InnoDB
Spécification de la clé étrangère
Déclaration d’un index au niveau de la table comportant les clés étrangères
CREATE TABLE [IF NOT EXISTS] <nom table>(
…) TYPE = InnoDB;
FOREIGN KEY (<liste_colonnes>)REFERENCES <table> (<liste_colonnes>)
[ON UPDATE CASCADE|SET NULL|NO ACTION|RESTRICT][ON DELETE CASCADE|SET NULL|NO ACTION|RESTRICT]
INDEX <nom_index> (<liste_colonnes>)
4.5 LDD : gestion des clés étrangères sous MySQL
Exemple
DROP TABLE IF EXISTS Magasin;CREATE TABLE Magasin(
n_mag TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,nom VARCHAR(100) NOT NULL,adresse VARCHAR(100) NOT NULL,tel VARCHAR(10),code_postal MEDIUMINT(5) UNSIGNED ZEROFILL NOT NULL,PRIMARY KEY(n_mag),INDEX id_cp (code_postal),FOREIGN KEY (code_postal) REFERENCES Poste(code_postal)ON UPDATE CASCADE
) TYPE = InnoDB;
4.6 LDD : suppression et modification de tables
Suppression
Modification
Altérations– Type de colonne
– Nom et type de colonne
DROP TABLE [IF EXISTS] <nom_table1>, …, <nom_tableN>
ALTER TABLE <nom_table> <alteration>
MODIFY <nom_colonne> <nouvelle_definition_colonne>
CHANGE <ancien_nom_colonne> <nouveau_nom_colonne> <nouvelle_definition_colonne>
4.6 LDD : modification de tables
Altérations– Ajout d’une colonne
– Suppression d’une colonne
– Renommage de la table
– Suppression ou ajout d’une valeur par défaut
– Suppression ou ajout de contraintes d’intégrité
ADD <nom_col> <def_col> [FIRST|AFTER <nom_col>]
DROP <nom_col>
ALTER <nom_col> {SET DEFAULT <valeur> |DROP DEFAULT}
RENAME AS <nouveau_nom_table>
DROP PRIMARY KEYADD CONSTRAINT {PRIMARY KEY|UNIQUE} <liste_colonnes>
4.7 LMD : requête
Définition de la requête SQL– composition d’un produit cartésien, d’une restriction et d’une
projection
Syntaxe
– SELECT Sous-ensembles des attributs devant apparaître dans la réponse
(c’est le schéma de la table résultat)
– FROM Tables utilisables dans la requête : produit cartésien cumulé
– WHERE Condition devant être vérifiée par tout tuple du produit cartésien
pour apparaître dans la table résultat
SELECT <liste_d’attributs_projetés>FROM <liste_de_relations>WHERE <condition>
4.7 LMD : requête
Opérateurs de comparaison– =, !=, <, >, <=, >=– P BETWEEN P1 AND P2– P LIKE <chaine>
caractères _ et % autorisés– P IN (C1,C2,…,CN)– P IS [NOT] NULL
Opérateurs logiques– NOT– OR– AND– XOR
4.7 LMD : requête
Exemple avec une table
Exemple de jointure naturelle
SELECT *– sélection de tous les attributs
SELECT [DISTINCT]– élimination des doublons– une table SQL n’est pas une relation mathématique
SELECT Nom, PrenomFROM PersonneWHERE Age>18
SELECT Parent.Nom, Enfant.PrénomFROM Parent, EnfantWHERE Enfant.Nom = Parent.Nom
4.7 LMD : requête
Renommage des attributs
Utilisation d’alias pour les tables
Remarque importante– Contrairement à ce que son nom indique, SELECT
n’opère pas une sélection (ou restriction), mais une projection
SELECT A AS A’
SELECT Nom, VilleFROM Magasin as M, Code_Postal as PWHERE M.code_postal = P.code_postal;
4.8 LMD : requête et algèbre relationnel
Produit cartésien
Projection
Restriction
Jointure
SELECT *FROM R1, R2, …, RN
SELECT P1, P2, …, PNFROM R
SELECT *FROM RWHERE <condition>
SELECT *FROM R1, R2, …, RNWHERE <condition>
4.8 LMD : requête et algèbre relationnel
Jointure par la clause ON
Auto-jointure
Jointure externe
SELECT *FROM R1 [INNER] JOIN R2ON <condition>
SELECT *FROM R AS R1, R AS R2WHERE R1.Nom = R2.nom
SELECT NumFROM Avion LEFT OUTER JOIN VolWHERE Avion.Num = Vol.Num
4.8 LMD : requête et algèbre relationnel
Opérateurs ensemblistes– Union
– Intersection
– Différence
SELECT * FROM R1UNIONSELECT * FROM R2
SELECT * FROM R1INTERSECTSELECT * FROM R2
SELECT * FROM R1EXCEPTSELECT * FROM R2
4.9 LMD : requête et tri des tuples
Syntaxe de : ORDER BY
Tri décroissant– attribut suivi du mot clé : DESC
Exemple
SELECT <liste_des_attributs_projetés>FROM <liste_de_relations>WHERE <condition>ORDER BY <liste_ordonnée_des_attributs>
SELECT *FROM PersonneORDER BY Nom, Age DESC
4.10 LMD : fonctions de calcul
Fonctions de calcul, sur un attribut de relation– COUNT(<col>)
nombre de tuples ayant une valeur d’attribut non nulle
– SUM(<col>) somme des valeurs (colonne de type numérique)
– AVG(<col>) moyenne des valeurs
– MAX, MIN(<col>)– VARIANCE(<col>)
ExempleSELECT MIN(Age), MAX(Age), AVG(Age)FROM Employes
4.11 LMD : requête et agrégats
Agrégat = groupe– SQL permet de partitionner horizontalement une
table en sous-tables, en fonction des valeurs d’un ou de plusieurs attributs
– chaque agrégat est alors susceptible de subir l’application de fonctions de calcul
Syntaxe de GROUP BY … HAVINGSELECT <liste_ d'attributs_de_partitionnement_à_projeter_
et_de_fonctions_de_calcul>FROM <liste_de_relations>WHERE <condition_pré_agrégation>GROUP BY <liste_ordonnée_ d'attributs_de_partitionnement>HAVING <condition_post_agrégation(sur_fonctions_calcul)>
4.11 LMD : requête et agrégats
Exemple
Remarques– un tuple par groupe– au niveau du SELECT, un attribut projeté directement sans
calcul, doit nécessairement apparaître dans la clause GROUP BY
– s’il n’y a pas de fonction de calcul, ni dans le SELECT, ni dans le HAVING, alors la clause GROUP BY est inutile
SELECT Societe.Nom, AVG(Personne.Age)FROM Personne, SocieteWHERE Personne.NomSoc = Societe.NomGROUP BY Societe.NomHAVING Count(Personne.NumSS)>10
4.12 LMD : requêtes imbriquées
Sous-requête d’appartenance : IN
Sous-requête d’existence : EXISTS
SELECT Num_SSFROM ChercheurWHERE (Nom, Prenom, Age) IN
(SELECT Nom, Prenom, AgeFROM Enseignant
)
SELECT Chercheur.NomFROM ChercheurWHERE EXISTS
( SELECT *FROM UniversiteWHERE Universite.Nom =
Chercheur.Nom)
4.12 LMD : requêtes imbriquées
Sous-requête de comparaison ALL
Sous-requête de comparaison ANY
SELECT NomFROM ChercheurWHERE Age > ALL
(SELECT AgeFROM Etudiant
)
SELECT NomFROM ChercheurWHERE Age > ANY
(SELECT AgeFROM Etudiant
)
4.13 LMD : Insertion de données
Insertion directe– syntaxe
– Exemple
Insertion par le biais d’une sélection
INSERT INTO <nom_table> (<liste_ordonnée_des_attributs_à_remplir>)
VALUES (<liste_ordonnée_des_valeurs_à_affecter>)
INSERT INTO Virement(Date, Montant, Objet)VALUES (1975-07-14, 1000, ‘Prime de fin d’annee’)
INSERT INTO Credit(Date, Montant, Objet)SELECT Date, Montant, ‘Annulation de débit’FROM DebitWHERE Debit.Date = 2005-12-25
4.14 LMD : Mise à jour de données
Mise à jour directe– syntaxe
– Exemple
Mise à jour par calcul sur les anciennes valeurs
UPDATE <table>SET <Liste_d’affectations_Propriété=Valeur>WHERE
<condition_filtre_des_tuples_à_modifier>
UPDATE CompteSET Total = Total * 6,55957WHERE Monnaie = ‘Euro’
UPDATE CompteSET Monnaie = ‘Euro’WHERE Monnaie = ‘Franc’
4.15 LMD : Suppression de données
Syntaxe
Exemple– suppression de tous les tuples d’une relation
– suppression sélective
DELETE FROM <table>WHERE <condition_filtre_des_tuples_à_supprimer>
DELETE FROM FaussesFacturesWHERE Auteur = ‘moi’
DELETE FROM FaussesFactures
4.16 LCD : Attribution de droits
Syntaxe
Droits– SELECT, INSERT, DELETE, UPDATE, ALTER– ALL PRIVILEGES
transmet tous les droits précédents, sauf celui de les transmettre
– WITH GRANT OPTION autorise l’utilisateur à transmettre ses propres droits
Utilisateurs– PUBLIC
renvoie à tous les utilisateurs
GRANT <liste_de_droits> ON <nom_table>TO <utilisateur> [WITH GRANT OPTION]
4.16 LCD : Attribution de droits (MySQL)
Droits sur un SGBD– remplacer le nom de la table par *.* permet de fixer
les droits sur l’ensemble des bases du système
Droits sur une BD– remplacer le nom de la table par <nom_bd>.*
permet de fixer les droits sur toutes les tables de la base
Création d’un utilisateur– affectation de droit + éventuellement mot de passe
GRANT ALL PRIVILEGES ON Bibliotheque.*TO Chef_bibliothecaire IDENTIFIED BY ‘mdp_chef’
4.17 LCD : Révocation de droits
Syntaxe
Remarque– Lorsque le droit d’un utilisateur est supprimé, la
suppression se répercute en cascade sur tous les utilisateurs à qui il avait transmis ce droit
REVOKE <liste_droits> ON <nom_table> FROM <utilisateur>
Bibliographie
- Programmer avec MySQL : SQL, transactions, PHP, Java, optimisations, C. Soutou, Eyrolles, 2013
- Introduction aux bases de données relationnelles, R.A. Mata-Toledo, Schaum, 2003
- Modélisation des bases de données, C. Soutou, Eyrolles, 2015
- Polycopié NF04, Université de Technologie de Compiègne