BASES DE DONNEES

Salma REBAI JRIBI

[Link]@[Link]

ESME SUDRIA
ANNÉE UNIVERSITAIRE: 2019-2020
OBJECTIF & ACQUIS D’APPRENTISSAGE VISÉS

A l’issue de ce cours, chaque étudiant devra être capable de :

• Enumérer les fonctionnalités d’un système de gestion de bases de données (SGBD).
• Concevoir un modèle conceptuel de données (modèle Entité-Association) pour une situation problème concrète.
• Etablir un schéma relationnel normalisé d’une base de données à partir d’une modélisation conceptuelle.
• Créer et mettre à jour une base de données relationnelle avec le langage SQL.
• Formuler des requêtes SQL avancées pour l’interrogation et la manipulation (ajout, suppression, et mise à jour)
des données dans une base de données relationnelle.
• Concevoir et implémenter des Vues pour optimiser et automatiser ses traitements sur une base de données
relationnelle.

INGÉ 1 - BASES DE DONNÉES 2 © Salma REBAI-JRIBI

INFORMATIONS PRATIQUES

Modalités pédagogiques
• 12 séances de 2H de Cours Intégré (Cours-TD).
Evaluation
• Contrôle Continu (50%) : Devoir Surveillé (DS), 2H, Documents non autorisés.
• Examen Pratique (50%) : Examen (TP) individuel sur machine, 2H, Documents non autorisés.
Support de cours et TD disponibles sur Office Teams (SCOLARITE_Paris_Inge1_coworking)
Chargés de cours
• Paris : Salma REBAI-JRIBI; Raouda Kamoun; Maher REBAI
• Lille : Guillaume ROUX
• Lyon : Mazen SAID
• Bordeaux : Michel GILLET
Responsable du module : Salma REBAI-JRIBI ([Link]@[Link])
INGÉ 1 - BASES DE DONNÉES 3 © Salma REBAI-JRIBI
INTRODUCTION
La gestion des données est une nécessité

Service
achat

Entreprise
de
Service Service
production
production financier
ou de
service

Service
vente
Figure : Exemple de services dans l’entreprise
INGÉ 1 - BASES DE DONNÉES 4 © Salma REBAI-JRIBI
LES DONNÉES

Définition d’une donnée (au sens informatique) :

• Représentation d’une information en vue de son traitement informatique
• Les données peuvent être créées par l’utilisateur ou par le programme lui-même.
• Elles peuvent être sous forme de nombres, chaînes de caractères, images, ou sons…
 Stockage des données avec les fichiers :
• Inconvénients :
 Perte de temps
 Données redondantes
 Grand volume de stockage
 Coût important des mises à jour
 Inconsistance des données
 Problèmes de sécurité
• Solutions : Figure : Enregistrement des données dans un tableur Excel
 Création d’une base de données pour stocker une grande quantité d’information de façon pérenne.
INGÉ 1 - BASES DE DONNÉES 5 © Salma REBAI-JRIBI
BASE DE DONNÉES (BDD)

Définition:
• Ensemble structuré de données cohérentes qui modélisent un univers réel.
Avantages:
• Informations organisées de façon à être facilement accessibles, gérées et mises à jour, en simultanée par
plusieurs utilisateurs.
• Scalabilité et facilité de la prise de décision
 Faible redondance  Cohérence et besoin en stockage moindre.
 Données Structurées  Automatisation
 Exhaustivité des données  Qualité des décisions.
La structure de la BDD dépend du modèle choisi:
• Hiérarchique ou réseaux
• Objet
• Relationnel: Enregistre et manipule les données dans des tables (relations) à 2 dimensions (lignes & colonnes).
INGÉ 1 - BASES DE DONNÉES 6 © Salma REBAI-JRIBI
SYSTÈME DE GESTION DE BASE DE DONNÉES (SGBD)

Un SGBD est un logiciel système permettant la gestion et la manipulation d’une BDD,

selon un modèle fixé:
• Facilite la définition, la manipulation et le contrôle des données.
• Fournit les fonctionnalités d’administration de la Base.

Principes fondamentaux des SGBD :

• Unicité : pas de redondance d'informations dans la BD.
• Indépendance : indépendant du modèle de stockage.
• Concurrence : Gestion d’accès simultanés à une même donnée.
• Performance : temps d’exécution raisonnable.
• Confidentialité : Accessibilité des données en fonction des droits de l’utilisateur.
• Durabilité : les données stockées ne peuvent pas être perdues.
• Intégrité : garantie de la fiabilité et de la cohérence des données.
• Robustesse : tolérance aux problèmes matériels, logiciels ou humains.
INGÉ 1 - BASES DE DONNÉES 7 © Salma REBAI-JRIBI
ARCHITECTURE D’UN SGBD

L’architecture ANSI/SPARC
définit 3 niveau d’abstraction
des données pour les SGBD :
• Séparation entre la description
externe et la description interne
des données
• Indépendance entre le schéma
conceptuel et les programmes
d’applications.
• Indépendance entre le schéma
conceptuel des données et les
structures de stockage.
Figure: Architecture ANSI/SPARC - 1975
INGÉ 1 - BASES DE DONNÉES 8 © Salma REBAI-JRIBI
CYCLE DE VIE D’UNE BASE DE DONNÉES

INGÉ 1 - BASES DE DONNÉES 9 © Salma REBAI-JRIBI

CONCEPTION DES BASES DE DONNÉES
RELATIONNELLES
PROCESSUS DE CONCEPTION D’UNE BDD

INGÉ 1 - BASES DE DONNÉES 11 © Salma REBAI-JRIBI

CONCEPTION D’UNE BASE DE DONNÉES
3 Niveaux de modélisation des données

Modèle physique

INGÉ 1 - BASES DE DONNÉES 12 © Salma REBAI-JRIBI

MODÈLE CONCEPTUEL DE DONNÉES (MCD)

Objectif: Identification et représentation structurée des informations manipulées par le

système
• Décrire, et modéliser les objets, les évènements et les contraintes (règles de gestion) du problème étudié, sous
forme d’entités et leurs interrelations, à l’aide d’une représentation graphique.
• Cette modélisation est indépendante de toute considération technique (contraintes matérielles, d’implémentation…).
• Les règles de construction du MCD aboutissent à une représentation standard sans redondances et sans ambiguïtés.

Avantages du MCD
• Représentation graphique simple et accessible, facilitant le dialogue entre les acteurs techniques et non techniques.
• Puissance d’expression élevée (sémantiques, structures relationnelles, …) avec un nombre raisonnable de symboles.
• Formalisme peu ambiguë offrant une spécification détaillée.

Mais
• Propose que des structures (description des données): Pas d’opérations (langage de manipulation associé).
INGÉ 1 - BASES DE DONNÉES 13 © Salma REBAI-JRIBI
MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Modèle Entité-Association (E-A)
 Modèle Entité-Association (Entity-Relationship Model):
• Un modèle conceptuel de très haut niveau : structuration sémantique des données du monde réel.

• Proposé initialement en 1976 (P. Chen), de nombreuses extensions depuis.

• Correspond au niveau conceptuel de la méthode Merise (Méthode d'Étude et de Réalisation Informatique pour les
Systèmes d'Entreprise, 1978) , et est à la base de plusieurs autres méthodes de conception (OMT, UML…).

 3 Concepts de base
• Entités : les objets du monde réel.

• Associations (ou Relations) : les relations liant les

entités, et sont quantifiées par des cardinalités.

• Attributs (ou Propriétés) : renseignent certaines

informations sur une entité ou une association.

INGÉ 1 - BASES DE DONNÉES 14 © Salma REBAI-JRIBI

MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Modèle Entité-Association (E-A) : Entité
Définition - Une entité (ou type d’entité) est :
• une famille d’objets concrets ou abstraits du monde réel,
• homogènes et partageant les même caractéristiques (propriétés),
• ayant une existence propre et sont discernables des autres objets,
• et présentent un intérêt pour les besoins de gestion du système à représenter.

Exemples :
• Dans une banque, l’entité CLIENT, représente l’ensemble des personnes physiques ou morales possédant au
moins un compte bancaire.
• Les différents produits vendus par une entreprise peuvent être regroupés dans une même entité ARTICLE,
car tous les produits partagent les mêmes propriétés (désignation, référence, prix unitaire, etc.).

INGÉ 1 - BASES DE DONNÉES 15 © Salma REBAI-JRIBI

MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Modèle Entité-Association (E-A) : Entité

Une entité est caractérisée (définie) par :

• Un libellé (nom) pour la nommer.
• Un ensemble d’attributs (propriétés particulières) qui la décrivent.
• Une clé, constituée d’un ou plusieurs attributs, qui l’identifie de manière unique.

Chaque entité peut correspondre à plusieurs occurrences

(enregistrements , instances):
• Une occurrence est un élément particulier du monde réel, et est représentée par
un ensemble de valeurs des propriétés constituant cette entité.

• La clé de l’entité garantit l’unicité des occurrences: A chaque valeur de la clé

correspond une et une seule occurrence de l’entité.

INGÉ 1 - BASES DE DONNÉES 16 © Salma REBAI-JRIBI

MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Modèle Entité-Association (E-A) : Entité

Entité : Règles d’identification

• Plusieurs types d’identifiant (de clé):
 Identifiant simple « naturel » (ex: nom d’un pays, email) ou « artificiel » (ex: N° client)
 Identifiant composé (ex: titre_film+réalisateur )
 Identifiant relatif : comprenant des propriétés d’une ou plusieurs autres entités.

• Un identifiant doit être :

 Monovalué : une occurrence  valeur unique de l’identifiant.
 Discriminant : une valeur de l’identifiant  une occurrence unique
 Stable: on ne doit jamais avoir besoin de le modifier.
 Minimal: ensemble minimal d’attributs pour les identifiants composés

INGÉ 1 - BASES DE DONNÉES 17 © Salma REBAI-JRIBI

MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Modèle Entité-Association (E-A) : Attribut / Propriété

Définition – Un attribut (ou propriété ) est une information élémentaire qui caractérise
une entité ou une association :
• unique dans le modèle et ne peut être rattaché qu’à un seul concept.
• typé : le domaine des valeurs que peut prendre l'attribut est fixé a priori.
• atomique (ex: nom, prénom, âge ) ou composé (ex. adresse=n° rue + code_postal + ville).
• prend une valeur précise pour chaque occurrence.

Les attributs et les contraintes :

• La définition d’une propriété est complétée par la prise en compte des contraintes éventuelles associées.
• Les contraintes correspondent à des contrôles pour vérifier l’intégrité des données et la cohérence par
rapport au système à représenter :
 Les contraintes de valeurs (ex: date_ouverture_compte <= date_premier_virement, …)
 Les dépendances fonctionnelles (ex: à un ‘N° compte’ correspond une seule valeur ‘type de compte’ , …)

INGÉ 1 - BASES DE DONNÉES 18 © Salma REBAI-JRIBI

MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Exercice d’application : Entité/Attribut
Enoncé : Une compagnie aérienne souhaite gérer de façon automatisée son activité. Pour ce faire, la compagnie vous a
demandé de réaliser une base de données selon les règles de gestion suivantes :
• Les employés de la compagnie se composent du personnel navigant. On distingue les pilotes et les membres d'équipage (hôtesses,
stewart, ...). Chaque employé est caractérisé par un numéro de sécurité sociale, un nom, un prénom, une adresse et un salaire.
• Chaque appareil de la compagnie est identifié par un numéro d'immatriculation unique. Il possède un type (A320, B747, ...) et il
offre une certaine capacité en nombre de passagers.
• La compagnie gère plusieurs liaisons. Chaque liaison est desservie par au moins un vol, et est définie par une ville d'origine et une
ville de destination, et de plus, elle est identifiée par un numéro unique.
• Chaque vol est identifié par un numéro. Il est caractérisé par une période de validité définie par deux dates. Durant cette période,
ce vol a des horaires fixes (heure départ et heure arrivée). Un vol dessert une seule liaison, et est associé à un seul appareil.
• Pour chaque vol enregistré, on suppose qu'il existe un départ tous les jours pendant la période de validité de ce vol. Un départ est
défini par un numéro de vol et une date de départ. A un départ sont associés un ou deux pilotes et deux à quatre membres
d'équipage. Un départ est également caractérisé par un nombre de places libres et un nombre de places occupées. Une place
occupée est une place ayant donné lieu à une réservation avec émission d'un billet.
• Un billet est repéré par un numéro. Il comporte une date d'émission, un prix, le nom et le prénom du client. Un billet référence un
départ et un seul.
Question: Déterminer les différentes entités du système à modéliser.
INGÉ 1 - BASES DE DONNÉES 19 © Salma REBAI-JRIBI
MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Exercice d’application : Entité/Attribut
Corrigé : VOL
PILOTE EQUIPAGE
Num-Sécu-So Num-Sécu-So Num-Vol
Nom Nom Date_Début
Prenom Prenom Date_Fin
Adresse Adresse Heure_Départ
Salaire Salaire Heure_Arrivée

DEPARTS BILLET
APPREIL LIAISON
Num-Vol Num-Billet
Num-Immatricul Num-Liaison
Date-Départ Date_Emission
Type Ville_Départ
Nb_Places_Libres Prix
Capacité Ville_Arrivée
NB_Places_Occup Nom_Client
Prenom_Client

INGÉ 1 - BASES DE DONNÉES 20 © Salma REBAI-JRIBI

MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Modèle Entité-Association (E-A) : Association
Définition :
• Une association ( ou type d’association) désigne un ensemble de relations de même nature entre 2 ou
plusieurs occurrences d’entités, et ayant un intérêt pour la gestion du système à représenter.

Règles de définition :
• Les associations n’ont pas d’existence propre, elles n’existent qu’a travers les entités qu’elles relient.
• Une association est défini par un nom (en général, on utilise un verbe à l'infinitif) et une liste d’entités participantes
• Une association peut être, ou non, porteuse de propriétés particulières qui la décrivent (ex: date achat).
• Une association n’a pas d’identifiant explicite. Elle est identifiée par la concaténation des identifiants des entités reliées.
• Chaque occurrence d’une association est constituée d’une et une seule occurrence des entités participantes.

 Caractéristiques :
• Collection : l’ensemble des entités intervenant dans la relation.
• Dimension (ou degré, ou arité): le nombre d’entités de la collection.
INGÉ 1 - BASES DE DONNÉES 21 © Salma REBAI-JRIBI
MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Modèle Entité-Association (E-A) : Association

Exemple :
• Collection: { ETUDIANT, DIPLOME }
• Dimension: 2  Association binaire
• Identifiant de l’association : (nom, prenom,date_naissance)x(titre)

Un graphe d’occurrences possibles de l’association

INGÉ 1 - BASES DE DONNÉES 22 © Salma REBAI-JRIBI

MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Modèle Entité-Association (E-A) : Association
Différents types d’associations selon la dimension :
• Association réflexive (ou unaire) : associe les occurrences d’une même entité.
 Exemple : Une personne est frère d’une autre personne (relation symétrique)

 Exemple : Une personne est parent/enfant d’une autre personne (relation non symétrique).

• Association binaire : associe 2 entités.

• Association n-aire : met en relation n (n>2) entités ( Associations ternaires, quaternaires..)

 Exemple : Un enseignant note un élève pour une matière (n=3).

• Association plurielle: partage la même collection avec d’autres associations

 Exemple : Une personne peut être propriétaire, ou habiter
dans un logement géré par une agence immobilière.

INGÉ 1 - BASES DE DONNÉES 23 © Salma REBAI-JRIBI

MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Modèle Entité-Association (E-A) : Association

Définition - La Cardinalité d’une association :

• caractérise la participation d’une entité à une association.
• Un couple de 2 valeurs indiquant le nombre minimum et maximum de fois qu’une occurrence de l’entité peut
être impliquée dans des occurrences de l’association.
• Ces cardinalités dépendent des règles de gestion du système.

L’expression de la cardinalité est obligatoire sur chaque lien Association-Entité:

• La cardinalité minimale doit être inférieure ou égale à la cardinalité maximale.
• Les cardinalités maximales sont nécessaires pour concevoir le schéma de la base de données.
• Les cardinalités minimales sont nécessaires pour exprimer les contraintes d’intégrité.

INGÉ 1 - BASES DE DONNÉES 24 © Salma REBAI-JRIBI

MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Modèle Entité-Association (E-A) : Association
Cardinalités fréquemment utilisées

Signification:
• La cardinalité minimale à 0: des occurrences de l’entité peuvent exister sans être impliquées dans aucune association.
• La cardinalité minimale à 1 : une occurrence de l’entité ne peut exister que si elle est impliquée dans au moins une
occurrence de l’association.
• La cardinalité maximale à 1: toute occurrence de l’entité ne peut participer qu’à une seule occurrence de l’association
au plus
• La cardinalité maximale à N : une occurrence de l’entité peut être impliquée dans plusieurs occurrences de
l’association.

INGÉ 1 - BASES DE DONNÉES 25 © Salma REBAI-JRIBI

MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Modèle Entité-Association (E-A) : Association
Quelques exemples de liaisons :
• Exemple 1:
 Un client peut passer 1 ou plusieurs commandes.
 Une commande est passée par (associée à ) un et un seul client.

• Exemple 2 :
 Une commande concerne un ou plusieurs produits.
 Un produit est associé à aucune ou à plusieurs commandes.

• Exemple 3 :
 1) Un professeur peut ne pas enseigner, ou enseigner dans plusieurs
salles à différentes périodes (plusieurs couples (Salle, Période)).
 2) A une période donnée, il peut ne pas y avoir des professeurs
enseignant dans les salles, ou plusieurs professeurs enseignant dans
plusieurs salles différentes (plusieurs couples d’occurrences
(Professeur, Salle)).
 3) .…
INGÉ 1 - BASES DE DONNÉES 26 © Salma REBAI-JRIBI
MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Modèle Entité-Association (E-A) : Résumé
 Rappel : Quelques règles de construction / validation du MCD :
• Chaque entité doit avoir une propriété clé qui l’identifie.

• Chaque entité doit participer à au moins une association.

• Une propriété doit apparaître une seule fois dans le modèle.

• Chaque propriété doit avoir une seule valeur pour une occurrence donnée.

• Les attributs composés et/ou dérivées (calculables à partir d’autres attributs) sont a éviter.

• Une association, peut avoir ou non des attributs.

• Un attribut d’une association doit dépendre directement des identifiants de toutes les entités en relation.

• Les associations redondantes ou fantômes doivent être éliminées :

 Exemple : Une association ayant toutes ses cardinalités de type (1,1) doit être supprimée.
INGÉ 1 - BASES DE DONNÉES 27 © Salma REBAI-JRIBI
MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Exercice d’application
Enoncé : Dans un magasin de vente de disques, le responsable voulait créer une base de
données pour assurer la bonne gestion de ses ventes. Cette BDD repose sur les règles de
gestion suivantes :
• Les clients doivent être répertoriés dans la base de données.
• Un client est identifié par un numéro unique, un nom, un prénom, une date de naissance et une adresse.
• Un client peut acheter plusieurs disques à différentes dates. Chaque achat concerne au moins un disque.
• Un disque est réalisé par un seul artiste dont on connait le nom, le pays et le domaine, ainsi que la date de
réalisation du disque.
• Chaque disque est identifié par un titre, une date de gravure et un numéro de référence unique et est rangé dans
l’un des rayons du magasin à une date donnée. On souhaite garder l’historique des différents
déplacements/rangements des disques. Chaque rayon est identifié par un libellé et un numéro.
• Le directeur doit pouvoir visualiser la liste des clients, la liste des disques et leurs emplacements, ainsi que les achats
en fonction de leurs dates.
Question: Représenter le MCD correspondant à cette base de données.
INGÉ 1 - BASES DE DONNÉES 28 © Salma REBAI-JRIBI
MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Exercice d’application
Correction (MCD  Entités):
Dans cet exercice on remarque qu’on a:
• Plusieurs objets « Client » qui partagent les mêmes caractéristiques (Nom, Prénom, date_naissance et
Adresse).
• Plusieurs objets « Disque » qui partagent les mêmes caractéristiques (Titre, Date_Gravure).
• Plusieurs objets « Rayon » qui partagent les mêmes caractéristiques (Libellé, Num_Rayon).
• Plusieurs objets « Artiste » qui partagent les mêmes caractéristiques (Nom, Prénom, Pays, Domaine).

Création des 4 entités suivantes:

DISQUE RAYON CLIENT ARTISTE
Num_ref_Dsq Id Rayon Id Clt Id Artiste
Titre Libellé Nom Nom
Date_Gravure Num_Rayon Prénom Prénom
Date_Naiss Pays
Adresse
29 Domaine
INGÉ 1 - BASES DE DONNÉES 29 © Salma REBAI-JRIBI
MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Exercice d’application
Correction (MCD  Entités + Associations)
Associations:
• « Acheter » : reliant l’entité « Client » à l’entité « Disque », avec une propriété « Date_Achat ».
• « Ranger » : reliant l’entité « Rayon » à l’entité « Disque », avec une propriété « Date_Rangement ».
• « Réaliser » : reliant l’entité « Artiste » à l’entité « Disque » avec une propriété « Date_Réalisation ».
DISQUE
CLIENT Acheter Num_ref_Dsq Ranger
Id Clt Date_Achat Titre
Nom Date_Rangement
Date_Gravure
Prénom
Date_Naiss ARTISTE
Adresse RAYON
Id Artiste
Nom Réaliser Id Rayon
Prénom Date_Réalisation Libellé
Pays Num_Rayon
Domaine
INGÉ 1 - BASES DE DONNÉES 30 © Salma REBAI-JRIBI
MODÈLE CONCEPTUEL DE DONNÉES (MCD)
Exercice d’application
Correction (MCD Entités + Associations + Cardinalités)
Cardinalités :
• Une occurrence de « Client » peut acheter au minium 1 « Disque » et plusieurs (n) au maximum.
• Un «Disque» donné peut être acheté par 0 « Client » au minimum et par 1 « Client » au maximum.
• Détermination des autres cardinalités de la même manière.
DISQUE
CLIENT 1,n Acheter 0,1 Num_ref_Dsq 1,n Ranger
Id Clt Date_Achat Titre Date_Rangement
Nom Date_Gravure
Prénom
Date_Naiss ARTISTE 1,1 0,n
Adresse Id Artiste RAYON
1,n Réaliser
Nom Id Rayon
Date_Réalisation
Prénom Libellé
Pays Num_Rayon
INGÉ 1 - BASES DE DONNÉES
Domaine 31 © Salma REBAI-JRIBI
CONCEPTION D’UNE BASE DE DONNÉES
3 Niveaux de modélisation de données

Modèle physique

INGÉ 1 - BASES DE DONNÉES 32 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Modèle Relationnel
Intermédiaire entre le MCD et le MPD (Modèle Physique de Données)
C’est un ensemble de relations (tables relationnelles):
• Une relation est définie comme un tableau de données à deux dimensions : Les colonnes sont les attributs et les lignes sont les
tuples (enregistrements /occurrences / n-uplets ).
• Un ou plusieurs attributs permettent d’identifier de façon unique chaque tuple de la table: la clé primaire
 La clé primaire peut être simple (constituée d’un seul attribut) ou composée (constitué de plusieurs colonnes de la table).
 C’est un champ qui n’accepte ni des valeurs nulles, ni des doublons.

• Le lien entre 2 tables relationnelles est défini par une clé étrangère (Clé externe):
 Une clé étrangère est constituée d’une ou plusieurs colonnes faisant référence aux colonnes d’une autre table (la table référencée).
 La colonne de la table référencée doit faire partie d’une contrainte de clé primaire ou d’une contrainte d’unicité.
 Une clé étrangère garantit que les valeurs de chaque ligne de la table référençant existent dans la table référencée. Cela permet de
garantir l’intégrité et la cohérence des données enregistrées dans les tables.

• Chaque attribut appartient à un domaine qui définit l’ensemble, fini ou infini, de ses valeurs possibles (INT, STRING, etc.).

INGÉ 1 - BASES DE DONNÉES 33 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Passage du MCD au MLD
 Passage d’un Schéma Entité-Association à un Schéma Relationnel :

• Passage d’un modèle à deux structures ( entités + associations ) vers un modèle à une structure (relations).

• La sémantique n’est pas complètement préservée  il faut ajouter des contraintes d’intégrité.

 Règles de Passage :

• Etape 1: Chaque entité est transformée en une table avec le même nom.
 Chaque propriété de l’entité devient un attribut de la table (une colonne).

 Les attributs composant l’identifiant de l’entité sont transformés en clé primaire de la table (clé simple ou composé).

 Remarque : Cette transformation des propriétés concerne les propriétés simples et monovalués. La normalisation
des propriétés (attributs) devait être faite avant le passage au modèle relationnel (voir règles de construction du MCD).

• Etape 2 : Transformation des associations

 Des règles différentes selon les cardinalités de l’association.

INGÉ 1 - BASES DE DONNÉES 34 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Passage du MCD au MLD

 Exemple de transformation d’une entité en une table:

• Entité EMPLOYE (représentation graphique) :

• Schéma de la table EMPLOYE :

EMPLOYE ( Matricule, nom, age, adresse)

• Attributs de la table EMPLOYE

 Matricule : clé primaire

 Nom, Age, Adresse : autres attributs

INGÉ 1 - BASES DE DONNÉES 35 © Salma REBAI-JRIBI
MODÈLE LOGIQUE DE DONNÉES (MLD)
Passage du MCD au MLD
 Transformation des associations selon les cardinalités

• 3 Règles de base en fonction des cardinalités maximales sur les liens de l’association (à adapter selon le type
de l’association : binaire, ternaire, réflexive, etc… )

• Règle 1 : Association de type (*, n)  (*, 1)

 La clé primaire de la table issue de l’entité coté cardinalités (*,n) est dupliquée dans la table issue de l’entité coté cardinalités (*,1),
où elle devient une clé étrangère.
 Les éventuelles propriétés de l’association deviennent des attributs de la table issue de l’entité coté cardinalités (*,1).

• Règle 2 : Association de type (*, n)  (*, n)

 Création d’une nouvelle table ayant comme clé primaire, une clé composée des identifiants des entités reliées. Les éventuelles
propriétés de l’association deviennent les attributs de la nouvelle table.

• Règle 3 : Association de type (1 , 1)  (1 , 1)

 Une seule table pour les entités reliées, avec une clé primaire composée des leurs identifiants.

INGÉ 1 - BASES DE DONNÉES 36 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Passage du MCD au MLD
 Transformation des associations selon les cardinalités

• Exemple 1 : Association binaire de type (* , n)  (* , 1)

 La clé primaire de la table issue de l’entité coté cardinalités (*,n) est dupliquée dans la table issue de l’entité coté
cardinalités (*,1), où elle devient une clé étrangère.

 Les éventuelles propriétés de l’association deviennent des attributs de la table issue de l’entité coté cardinalités (*,1).

• Application :
 Le schéma MCD :

 Le schéma relationnel :

DEPARTEMENT (Nom_departement, Effectif)

EMPLOYE (Matricule, #Nom_departement, Nom, Age, Adresse)

INGÉ 1 - BASES DE DONNÉES 37 © Salma REBAI-JRIBI
MODÈLE LOGIQUE DE DONNÉES (MLD)
Passage du MCD au MLD
 Transformation des associations selon les cardinalités

• Exemple 2 : Association binaire de type (* , n)  (* , n)

 Création d’une nouvelle table ayant comme clé primaire, une clé composée des identifiants des 2 entités. Les
éventuelles propriétés de l’association deviennent les attributs de la nouvelle table.

• Application :
 Le schéma MCD :

 Le schéma relationnel :
COMMANDE (N°commande, Date, Statut) ;
PORTER (#N°article , #N°commande, Qte_commandée)
ARTICLE (N°article, Désignation, Prix)

INGÉ 1 - BASES DE DONNÉES 38 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Passage du MCD au MLD
 Transformation des associations selon les cardinalités

• Exemple 3 : Association binaire de type (1, 1)  (1, 1)

 Une seule table pour les deux entités, avec une clé primaire composée des 2 identifiants des entités.

• Application :
 Le schéma MCD : DIRECTEUR DEPARTEMENT
Id_Dir 1,1 Id_Depart
Nom
Diriger
1,1 Nom_Dep
Prénom Lieu
Grade

 Le schéma relationnel :
DIRECTEUR_DEPARTEMENT (ID_Dir, ID_Depart, Nom, Prénom, Grade, Nom_Dep, lieu)

INGÉ 1 - BASES DE DONNÉES 39 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Passage du MCD au MLD
 Transformation des associations selon les cardinalités

• Exemple 4 : Association binaire de type (0, 1)  (1, 1)

 La clé primaire de la table issue de l’entité côté cardinalités (0,1) est dupliquée dans la table issue de l’entité côté
cardinalités (1,1) où elle devient une clé étrangère.

• Application :
 Le schéma MCD :

 Le schéma relationnel :
EDIFICE (N°edifice, Type)
MAISON (N°maison, #N°édifice, Adresse)

• Remarque: Dans le cas d’une association de type (0, 1)  (0, 1), la clé primaire de l’une des 2 tables est
dupliquée dans l’autre table où elle devient clé externe qui peut prendre une valeur nulle.
INGÉ 1 - BASES DE DONNÉES 40 © Salma REBAI-JRIBI
MODÈLE LOGIQUE DE DONNÉES (MLD)
Passage du MCD au MLD
 Transformation des associations selon les cardinalités

• Exemple 5 : Association ternaire (ou plus) de type (* , n)  (* , n) (* , n)

 Création d’une nouvelle table ayant comme clé primaire, une clé composée des identifiants des 3 entités participants à
l’association. Les éventuelles propriétés de l’association deviennent les attributs de la nouvelle table.

• Exemple :
Le schéma MCD Schéma relationnel associé

MAISON (Coordonnées, Date_construction, Surface)

TYPE_TRAVAUX (N°type_travaux, Désignation)
RÉALISER (#N°entreprise, #Coordonnées, #N°type_travaux, Date, Montant)
ENTREPRISE (N°entreprise, Nom,Adresse)

INGÉ 1 - BASES DE DONNÉES 41 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Passage du MCD au MLD
 Transformation des associations selon les cardinalités

• Exemple 6 : Association ternaire (ou plus) de type (* , n)  (* , n) (* , 1)

 Les clés primaires des tables issues des entités coté cardinalités (*,n) sont dupliquées dans la table issue de l’entité coté
cardinalités (*,1), où elles deviennent clés étrangères. Les éventuelles propriétés de l’association deviennent des
attributs de la table coté (*,1).

• Application :
Le schéma MCD Schéma relationnel associé

EMPLOYE(ID_Employé , Nom, Prénom)

PRODUIT(ID_Prod, Désignation)
USINE(ID_Usine, #RefEmployé, #RefProduit, Nom, Adresse, Date)

INGÉ 1 - BASES DE DONNÉES 42 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Passage du MCD au MLD
 Transformation des associations selon les cardinalités

• Exemple 7 : Association réflexive de type (* , n)  (* , 1)

 La clé primaire de la table issue de l’entité est dupliquée dans cette table où elle devient une clé externe qui peut être
une valeur nulle. Les éventuelles propriétés de l’association deviennent des attributs de la table.

• Application :
 Le schéma MCD :

 Le schéma relationnel :
TACHE (N°tâche, #N°tâche_précédente, Désignation, Durée)

INGÉ 1 - BASES DE DONNÉES 43 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Passage du MCD au MLD
 Transformation des associations selon les cardinalités

• Exemple 8 : Association réflexive de type (* , n)  (* , n)

 La création d’une nouvelle table ayant comme clé, une clé composée de 2 fois l’identifiant de l’entité en question. Les
éventuelles propriétés de l’association deviennent des attributs de la table.

• Application :
 Le schéma MCD :

 Le schéma relationnel :

TRAVAUX (N°travaux, désignation, durée)

DÉCOMPOSER (#N°travaux, #N°travaux_ensemble)

INGÉ 1 - BASES DE DONNÉES 44 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Exercice d’application
Enoncé : Soit le schéma Conceptuel de Données (MCD) suivant:

DISQUE
CLIENT 1,n Acheter 0,1 Num_ref_Dsq 1,n Ranger
Id Clt Date_Achat Titre Date_Rangement
Nom Date_Gravure
Prénom
Date_Naiss ARTISTE 1,1 0,n
Adresse Id Artiste RAYON
Nom 1,n Réaliser
Id Rayon
Prénom Date_Réalisation Libellé
Pays Num_Rayon
Domaine

 Question: Etablir le schéma logique (MLD) correspondant à cette base de données.

INGÉ 1 - BASES DE DONNÉES 45 © Salma REBAI-JRIBI
MODÈLE LOGIQUE DE DONNÉES (MLD)
Exercice d’application
Correction :
Transformation des entités en tables:
• CLIENT(Id_Clt, Nom, Prénom, Date_Naiss,Adresse). ARTISTE(Id_Artiste, Nom, Prénom, Pays, Domaine).
• DISQUE(Num_ref_Dsq,Titre, Date_Gravure). RAYON(Id_Rayon, Libellé, Num_Rayon).

Représentation des cardinalités dans le MLD:

• L’association « Acheter » a un lien (1,n) et un autre lien (0,1). Il faut ajouter une clé étrangère dans la table
correspondante à la cardinalité (0,1):
DISQUE(Num_ref_Dsq, Titre, Date_Gravure, #refId_Client, Date_Achat).

• L’association « Ranger » a deux liens de type (*,n). Il faut créer une nouvelle table avec une clé composée :
RANGER(#refId_Rayon, #refId_Dsq, Date_Rangement)

• L’association « Réaliser » a un lien (1,n) et un autre lien (1,1). Il faut ajouter une clé étrangère dans la table
correspondante à la cardinalité (1,1):
DISQUE(Num_ref_Dsq,Titre, Date_Gravure #refId_Client, Date_Achat, #refId_Art, Date_réalisation).
INGÉ 1 - BASES DE DONNÉES 46 © Salma REBAI-JRIBI
MODÈLE LOGIQUE DE DONNÉES (MLD)
Exercice d’application

Correction (Passage MCD  MLD)

En résumé, le MLD est le suivant :
• CLIENT(Id_Clt, Nom, Prénom, Date_Naiss, Adresse).

• ARTISTE(Id_Artiste, Nom, Prénom, Pays, Domaine).

• DISQUE(Num_ref_Dsq, Titre, Date_Gravure #refId_Client, Date_Achat, #refId_Art, Date_réalisation).

• RAYON(Id_Rayon, Libellé, Numéro).

• RANGER(#refId_Rayon, #refId_Dsq, Date_Rangement)

INGÉ 1 - BASES DE DONNÉES 47 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
La Rétro-Conception
Objectif : Passer d’un schéma relationnel (MLD) à un schéma Entité-Association
(MCD) équivalent
• Corriger ou étendre une base de données existante dont on ne connait pas le modèle conceptuel.

Principe : Appliquer les règles de passage de MCD au MLD dans le sens inverse.
• Etape 1 : Chaque table dont la clé primaire ne contient pas de clé étrangère, devient une entité dont
l’identifiant est la clé primaire de la table, et les attributs sont le reste des colonnes non clés étrangères.
• Etape 2 : Chaque table dont la clé primaire est intégralement composée de clés étrangères, devient une
association multivaluée (toutes ses cardinalités maximales valent n) entre les entités correspondant aux
clés étrangères. Les autres colonnes non clés étrangères de la table deviennent les attributs de l’association.
• Etape 3 : Pour les tables possédant des clés étrangères à l’extérieur de la clé primaire, les colonnes clés
étrangères deviennent des associations monovalués (de type (*,1)--(*,n) ) vers l’entité correspondant à la
clé étrangère.

INGÉ 1 - BASES DE DONNÉES 48 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
La Rétro-Conception

Exercice d’application : Soit le schéma relationnel suivant d’une société agricole :

• Produits(Num, Nom, Catégorie)
• Récoltes(#N°Produit, #N°Produc, Quantité)
• Producteurs(Num, Nom, Prénom, Région)
• Clients(Num, Nom, Prénom, Téléphone,Ville)
• Commandes(N°Cde, Date, #N°Clt, #N°Produit, Quantité)
• Livraisons(N°Ordre, Date, #N°Cde, QteLivrée)

Question: En appliquant les règles de passage du MCD au MLD ("dans le sens inverse"),
déterminer le modèle Entité-Association équivalent à ce schéma relationnel.

INGÉ 1 - BASES DE DONNÉES 49 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
La Rétro-Conception
Exercice d’application : Correction
PRODUCTEURS
PRODUITS
Num Récolter
1,n 1,n Num
Nom
Quantité Nom 0,n
Prénom
Catégorie CLIENTS
Région
Num
Nom
Commander 1,n
Prénom
Quantité
Téléphone
Ville
LIVRAISONS COMMANDES
N°Ordre 1,n 1,1 Livrer 1,n N°Cde 1,1
Date Qte_Livrée Date

INGÉ 1 - BASES DE DONNÉES 50 © Salma REBAI-JRIBI

LA NORMALISATION
OPTIMISER LE MODÈLE RELATIONNEL
MODÈLE LOGIQUE DE DONNÉES (MLD)
Théorie de la normalisation
 Une mauvaise conception du schéma Entité-Association peut conduire à des relations
problématiques :
• Redondance des données qui aboutit à plusieurs anomalies et incohérences pendant la phase d’exploitation de
la base de données.

• Exemples d’anomalies de manipulation suite à un « mauvais » schéma relationnel :

 Anomalies de mise à jour: Si l’employé « Dupont » change
d’adresse, on aura besoin d’effectuer 2 mises à jour dans la table.

 Anomalies de suppression: La suppression du département

D890 engendre la suppression de toutes les informations sur
l’employé « Renault ».

 Anomalies d’insertion: Puisque la colonne « département »

n’accepte pas de valeur NULL, il sera impossible d’ajouter un
nouveau employé non affecté à un département donné.

INGÉ 1 - BASES DE DONNÉES 52 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Théorie de la normalisation
 La normalisation est une théorie permettant d’optimiser un modèle relationnel, de façon à
contrôler la redondance des données pour éliminer les anomalies qui en résultent :
• Définit un processus de décomposition progressive qui décompose une relation initiale non normalisée en un
ensemble équivalent de relations normalisées, sans perte d’informations.
• Les relations résultantes sont reliées par des clés primaires et des clés étrangères qui garantissent la
cohérence des données.

 La théorie de la normalisation est fondée sur deux concepts principaux :

• Les dépendances fonctionnelles
 Elles traduisent les contraintes et les propriétés sémantiques entre les données.

• Les formes normales

 Elles définissent les règles/critères qu’une relation doit respecter pour être bien conçue et sans anomalies.

INGÉ 1 - BASES DE DONNÉES 53 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Dépendance fonctionnelle (DF)

 Définition – Dépendance fonctionnelle (DF)

• Soient R(A1,A2, ... ,An) un schéma relationnel, X et Y des sous-ensembles de A1,A2, ... ,An :
On dit que X détermine Y, ou que Y dépend fonctionnellement de X, si et seulement si, pour toute valeur de
X, on détermine une valeur unique de Y. On note X→RY

• Exemple: Soit la relation Personne(NSS, Nom, Prénom, Marque, Modèle, Puiss, Date, Prix)
On peut poser les exemples de DF suivants:
 NSS→Nom NSS→Prénom Modèle→Marque Modèle→Puiss (NSS,Modèle,Date)→Prix …

Les DF traduisent la perception de la réalité (la sémantique des données et les contraintes qui les relient.)
• Les DF font partie du schéma d’une BD et doivent être contrôlées par le SGBD.
• l'identification des DF est la base indispensable pour déterminer dans quelle forme normale est une relation.

INGÉ 1 - BASES DE DONNÉES 54 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Dépendance fonctionnelle (DF)
Propriétés des dépendances fonctionnelles (ou axiomes d'Amstrong):
• Soit W, X,Y et Z des ensembles d'attributs non vides d'une relation [Link] quelques règles d’inférence:
 Réflexivité : (X ⊆ W) ⇒ (W →R X)

 Augmentation : (W →R X) ⇒ (W,Y →R X, Y)

 Transitivité : (W →R X ∧ X →R Y) ⇒ (W →R Y)

 Union : (W →R X ∧ W →R Y) ⇒ (W →R X, Y)

 Pseudo-transitivité : (W →R X ∧ X, Y →R Z) ⇒ (W,Y →R Z)

 Décomposition : (W →R X ∧ Y ⊆ X) ⇒ (W →R Y)

 Dépendance fonctionnelle élémentaire (DFE):

• Une DF de la forme (X →R Y) est dite élémentaire, si et seulement si, il n’existe aucun (Z ⊂ X ) tel que Z
détermine Y.
INGÉ 1 - BASES DE DONNÉES 55 © Salma REBAI-JRIBI
MODÈLE LOGIQUE DE DONNÉES (MLD)
Dépendance fonctionnelle (DF)
Définition - Clé candidate :
• Soit R une relation

 Si R ne contient qu'un seul attribut:

Cet attribut a une valeur distincte pour chaque occurrence et forme alors l'unique clé candidate de R.

 Si R contient plusieurs attributs:

Soient X et Y deux ensembles non vides disjoints d'attributs de R tels que X ∪ Y = R.

 Si on a X →R Y avec X est minimal, alors X est une clé candidate de R (il peut y en avoir plusieurs).

 Si aucun X ne peut être trouvé ainsi, toutes les occurrences de R sont distinctes et l'unique clé
candidate de R est formée de tous ses attributs.

 Si R comporte plusieurs clés candidates, l’une d’elle est choisie pour être la clé primaire.

INGÉ 1 - BASES DE DONNÉES 56 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Dépendance fonctionnelle (DF)
Exercice d’application : Un centre de loisirs souhaite créer sa base de données afin de gérer ses activités :
• On désire identifier chaque enfant par un numéro. On garde également le nom, le prénom et l'âge de l’enfant. Chaque
enfant appartient à un groupe, auquel on donne un nom et on y assigne un animateur responsable. Pour chaque animateur,
on connait le nom et le prénom.
• Pour chaque créneau horaire (représenté par une chaine contenant la date et l’heure, par exemple « 23-Septembre:14h-
16h »), une activité est proposée à chaque groupe. Chaque activité possède un type (ex: football, danse, bricolage,…).
• Une activité peut être suivie par plusieurs groupes durant le même créneau horaire (Ex: un match de football entre deux
groupes). Un groupe peut effectuer plusieurs fois la même activité à des créneaux différents. Par contre, tous les enfants
d’un même groupe suivent la même activité en même temps.

Question: L’activité du centre est décrite par la relation universelle suivante englobant ces informations:
Centre_Loisirs ( No-Enft, Nom-Enft, Prenom-Enft, Age-Enft, No-Groupe, Nom-Groupe, No-Anim,
Nom-Anim, Prenom-Anim, Creneau-Hor, No-Activite, Type-Activite )
Trouver la liste de dépendances fonctionnelles entre les attributs en respectant les hypothèses
annoncées. En déduire une clé candidate de cette relation. Est-elle unique ?
INGÉ 1 - BASES DE DONNÉES 57 © Salma REBAI-JRIBI
MODÈLE LOGIQUE DE DONNÉES (MLD)
Dépendance fonctionnelle (DF)

Exercice d’application : Correction (1/2)

• En respectant les hypothèses de gestion annoncées, la liste des dépendances fonctionnelles (DF) entre les
attributs est la suivante:
 No-Enft  Nom-Enft, Prenom-Enft, Age-Enft

 No-Enft  No-Groupe

 No-Groupe  Nom-Groupe

 No-Groupe  No-Anim

 No-Anim  Nom-Anim, Prenom-Anim

 No-Activite  Type-Activite

 Creneau-Hor, No-Groupe  No-Activite

INGÉ 1 - BASES DE DONNÉES 58 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Dépendance fonctionnelle (DF)

Exercice d’application : Correction (2/2)

• En appliquant les règles d’inférence (Transitivité, Pseudo-transitivité, et Union) sur les DF trouvées, on déduit
les dépendances fonctionnelles suivantes :
 No-Enft  Nom-Enft, Prenom-Enft,Age-Enft, No-Groupe, Nom-Groupe, No-Anim, Nom-Anim, Prenom-Anim
 Creneau-Hor, No-Groupe  No-Activite ,Type-Activite
 Creneau-Hor, No-Enft  No-Activite ,Type-Activite

• L’ensemble d’attributs minimal qui détermine tout les reste d’attributs de la relation « Centre_Loisirs » est
l’ensemble X = (No-Enft , Creneau-Hor). C’est la seule clé candidate, et qui représente alors la clé
primaire de la relation :
Centre_Loisirs ( No-Enft, Creneau-Hor, Nom-Enft, Prenom-Enft, Age-Enft, No-Groupe, Nom-Groupe,
No-Anim, Nom-Anim, Prenom-Anim, No-Activite, Type-Activite )

INGÉ 1 - BASES DE DONNÉES 59 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales
 Objectif : Définir une décomposition du schéma relationnel permettant de représenter les objets du
monde réel, de façon non redondante, tout en préservant les dépendances fonctionnelles entre
attributs.

• Exemple: Soit la relation Voiture(Numéro, Marque, Modèle, Puissance, Couleur) avec les dépendances
fonctionnelles suivantes :

Numéro → Marque ; Numéro → Modèle ; Modèle → Marque

Numéro → Couleur ; Numéro → Puissance ; Modèle →Puissance

La relation Voiture peut être décomposée en préservant les DF, en deux relations :
R1( Numéro, Modèle , Couleur ) et R2( Modèle, Puissance, Marque )

INGÉ 1 - BASES DE DONNÉES 60 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales
 On peut recenser les 6 formes normales suivantes, de moins en moins redondantes :
• la première forme normale ( 1FN )
• la deuxième forme normale ( 2FN )
• la troisième forme normale ( 3FN )
• la forme normale de Boyce-Codd ( FNBC )
• la quatrième forme normale ( 4FN )
• la cinquième forme normale ( 5FN )

 La 3FN est généralement reconnue comme la plus importante à respecter.

 Afin de mener une bonne conception on cherchera à obtenir un modèle relationnel en 3FN (au moins).
INGÉ 1 - BASES DE DONNÉES 61 © Salma REBAI-JRIBI
MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales
 Première Forme Normale (1FN)
• Contraintes à respecter :
 Les colonnes contiennent des valeurs scalaires (atomiques / monovalués).
 Les colonnes ne sont pas répétitives.

• Effets de normalisation :
 Facilite l’accès aux données et minimise les besoins de mises à jour.
• Exemple :
Id_emp Nom Adresse Téléphone Id_emp Nom Adresse Téléphone
Id_emp Téléphone
101 Dupont Paris 0658963247 101 Dupont Paris 0658963247
101 0658963247
0758649823 163 Martin Troyes 0655487921
101 0758649823
163 Martin Troyes 0655487921 123 Renault Caen 0699659878
163 0655487921
123 Renault Caen 0699659878 123 0699659878
INGÉ 1 - BASES DE DONNÉES 62 © Salma REBAI-JRIBI
MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales
 Deuxième Forme Normale (2FN)
• Contraintes à respecter :
 1 FN
 Tout attribut non clé est totalement dépendant de toute la clé. Autrement dit, aucun attribut non clé ne doit dépendre
d’une seule partie de la clé.
 Remarque: Cette forme concerne les relations (tables) avec une clé primaire composée.

• Effets de normalisation : Réduire la redondance des données et les besoins en espace disque.
• Exemple :
 La première table est en 1FN, mais pas en 2FN:
l’attribut « Age » dépend d’une seule partie de la
clé primaire (seulement de l’attribut « Id », mais pas de
« Matière »).
  Il faut décomposer la table/relation en 2 relations
respectant la 2FN, comme suit :
INGÉ 1 - BASES DE DONNÉES 63 © Salma REBAI-JRIBI
MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales
 Troisième Forme Normale (3FN)
• Contraintes à respecter :
 2FN.
 Un attribut non clé ne doit pas dépendre d’un ou plusieurs attribut(s) non clé.

• Effets de normalisation : Réduire la redondance des données et les besoins en espace disque.
• Exemple : Les colonnes non-clés « Pays » et « ville » de la première table dépendent de la colonne «code_zip» qui
est un attribut non-clé et dépendent aussi de la clé primaire «Id ».
 La table n’est pas en 3 FN (elle est en 1FN et 2FN). Pour la rendre normalisée en 3FN, on doit la décomposer:
Id Nom Prénom Pays Code zip Ville Id Nom Prénom Code zip Id-Code Pays Ville

101 Dupont John France 7500 Paris 101 Dupont John 7500 7500 France Paris
105 Martin Steve EC1Y 8SY EC1Y 8SY UK London
105 Martin Steve UK EC1Y 8SY London
163 Renault Max 10000 10000 France Troyes
163 Renault Max France 10000 Troyes
Clé étrangère faisant référence à la Clé primaire pour la table
INGÉ 1 - BASES DE DONNÉES
colonne (Id-Code) de la 2ème table
64
ajoutée © Salma REBAI-JRIBI
MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales
Algorithme de décomposition
• On considère la relation COMMANDE dont le schéma est donné par :
COMMANDE ( N°Commande, date, N°Client, nom, N°Article, désignation, quantité )

• La première forme normale : La mise d’une relation en première forme normale permet d’éliminer les groupes
répétitifs dans la table. La démarche à suivre est la suivante :
 Sortir le groupe répétitif de la relation initiale.

 Transformer le groupe répétitif en relation, rajouter la clé primaire de la relation initiale dans la clé de la nouvelle relation.

INGÉ 1 - BASES DE DONNÉES 65 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales
Algorithme de décomposition
• La deuxième forme normale :
 Exige que la relation soit en 1FN, et ne concerne que les relations avec une clé primaire composée

 Impose que les attributs non clé dépendent de la totalité de la clé primaire. Tout attribut qui dépendrait d’une partie de
la clé, sera exclu de la table. Le processus est le suivant :
 Regrouper dans une relation les attributs dépendant de la totalité de la clé, et conserver cette clé pour cette relation.

 Regrouper dans une autre relation, les attributs dépendant d’un sous-ensemble des attributs clé, et faire de cet ensemble la
clé primaire de la nouvelle relation.

INGÉ 1 - BASES DE DONNÉES 66 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales
Algorithme de décomposition
• La troisième forme normale :
 Exige que la relation soit en 2FN

 Permet d’éliminer des dépendances transitives au sein d’une relation. La démarche est la suivante :
 Conserver dans la relation initiale les attributs dépendant directement de la clé.

 Regrouper dans une autre relation les attributs dépendant de la clé par transitivité. L’attribut de transition reste dupliqué dans
la relation initiale et devient la clé primaire de la nouvelle relation.

INGÉ 1 - BASES DE DONNÉES 67 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales

Exercice d’application N°1 :

• Soit le schéma relationnel suivant (voir les Slides 58-59 pour les DF) :
Centre_Loisirs ( No-Enft, Creneau-Hor, Nom-Enft, Prenom-Enft, Age-Enft, No-Groupe,
Nom-Groupe, No-Anim, Nom-Anim, Prenom-Anim, No-Activite, Type-Activite )

• Question.1: Ce schéma relationnel est-il normalisé ? Est-il en 1FN, en 2FN ou en 3FN ? Expliquer et
justifier votre réponse pour chacune des formes normales.

• Question.2: Apporter les modifications nécessaires sur le schéma, pour qu’il respecte la 3FN, sans perte
d’information.

INGÉ 1 - BASES DE DONNÉES 68 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales

Exercice d’application N°1 : Correction (1/2)

• Question.1 :

 Le schéma relationnel « Centre_Loisirs » est en 1 FN : Pas de d’attributs répétitifs et multivalués.

 Le schéma n’est pas en 2 FN : La clé primaire de la table « Centre_Loisirs » est composée de

(« No-Enft » , « Creneau-Hor»), mais il y a plusieurs attributs non-clés qui dépendent seulement
d’une partie de cette clé.
 Contre-Exemple : L’attribut « Nom-Enft » dépend uniquement de la clé « No-Enft », mais pas de
l’attribut clé « Creneau-Hor »

 Ainsi, le schéma n’est pas en 3 FN.

INGÉ 1 - BASES DE DONNÉES 69 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales
Exercice d’application N°1 : Correction (2/2)
• Question.2 :
 Transformation en 2FN : Pour que la table « Centre_Loisirs » soit en 2FN, il faut que tous ses attributs non-clés
dépendent de la totalité de la clé primaire. En appliquant les règles de décomposition (Slide 68), on obtient:
 R1 : Loisirs ( #No-Enft, Creneau-Hor, No-Activite, Type-Activite )
 R2 : Enfant (No-Enft, Nom-Enft, Prenom-Enft, Age-Enft, No-Groupe, Nom-Groupe, No-Anim, Nom-Anim, Prenom-Anim )

 Transformation en 3 FN : Pour que R1 et R2 soient en 3FN, il faut que chaque attribut non-clé ne dépend pas
fonctionnellement d’un autre attribut non-clé. En appliquant les règles de décomposition (Slide 69), on obtient:

 R1.1 : Loisirs ( #No-Enft, Creneau-Hor, #No-Activite )

 R1.2 :Activite ( No-Activite, Type-Activite )
 R2.1 : Enfant ( No-Enft, Nom-Enft, Prenom-Enft, Age-Enft, #No-Groupe )
 R2.21 : Groupe ( No-Groupe, Nom-Groupe, #No-Anim )
 R2.22 :Animateur ( No-Anim, Nom-Anim, Prenom-Anim )
INGÉ 1 - BASES DE DONNÉES 70 © Salma REBAI-JRIBI
MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales
Exercice d’application N°2 :
• Soit le schéma relationnel suivant :
Video ( No-Cassette, Date-Location-Cassette, No-Client, Nom-Client, Prénom-Client, Adresse-Client,
Titre-Film, Durée-Film, Nom-Réalisateur, Année-Sortie-Film, Genre-Film, Durée-Location, Nationalité-
film, Nom-Editeur ,Adresse-Editeur , DateDébut-Abonnement-Client )

• Q.1 Sachant qu’un film est réalisé par un seul réalisateur et commercialisé par un seul éditeur et est
caractérisé par un genre et une nationalité, déterminer en quelle forme normale est le schéma relationnel
ci-dessus ? Expliquer et justifier votre réponse pour chacune des formes normales.

• Q.2 Apporter les modifications nécessaires sur le schéma, pour qu’il respecte la 3FN, sans perte
d’information.

INGÉ 1 - BASES DE DONNÉES 71 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales

Exercice d’application N°2 : Correction (1/2)

• Question.1 :

 Le schéma relationnel « Video » est en 1 FN : Pas de d’attributs répétitifs et multivalués.

 Le schéma n’est pas en 2 FN : La clé primaire de la table « Video » est composée de (« No-
Casette » , « Date-Location-Casette »), mais il y a plusieurs attributs non-clés qui dépendent
seulement d’une partie de cette clé.
 Contre-Exemple : L’attribut « Nom-Réalisateur » dépend uniquement de la clé « No-Casette », mais
pas de l’attribut clé « Date-Location-Casette »

 Ainsi, le schéma n’est pas en 3 FN.

INGÉ 1 - BASES DE DONNÉES 72 © Salma REBAI-JRIBI

MODÈLE LOGIQUE DE DONNÉES (MLD)
Les Formes Normales
Exercice d’application N°2 : Correction (2/2)
• Question.2 :
 Transformation en 2FN : Pour que la table « Video » soit en 2FN, il faut que tous ses attributs non-clés dépendent de
la totalité de la clé primaire. En appliquant les règles de décomposition (Slide 68), on obtient:
 R1: Video ( #No-Cassette, Date-Location-Cassette, No-Client, Nom-Client, Prénom-Client, Adresse-Client, Durée-
Location, DateDébut-Abonnement )
 R2 : Casette ( No-Cassette, Titre-Film, Durée-Film, Nom-Réalisateur, Année-Sortie-Film, Genre-Film, Nationalité-film,
Nom-Editeur,Adresse-Editeur )
 Transformation en 3 FN : En appliquant les règles de décomposition (Slide 69), on obtient:
 R1.1 : Location ( #No-Cassette, DateLocation-Cassette, Durée-Location, #No-Client )
 R1.2 : Client ( No-Client, Nom-Client, Prénom-Client,Adresse-Client, DateDébut-AbonnementClient )
 R2.1 : Casette (No-Cassette, #Titre-Film )
1
 R2.2 : Film ( Titre-Film, #Nom-Editeur, Durée-Film, Nationalité-Film, Nom-Réalisateur,AnnéeSortie-Film, GenreFilm )
2
 R2.2 : Editeur ( Nom-Editeur, Adresse-Editeur )
INGÉ 1 - BASES DE DONNÉES 73 © Salma REBAI-JRIBI
CRÉATION DES BASES DE DONNÉES
RELATIONNELLES

74
RAPPEL - CONCEPTION DE LA BASE
3 Niveaux de Modélisation de Données

Modèle physique

INGÉ 1 - BASES DE DONNÉES 75

© Salma REBAI-JRIBI
75
 MODÈLE PHYSIQUE DE DONNÉES (MPD)

Définition : Le modèle physique de données (MPD) correspond à l’implémentation du modèle

logique de données par un SGBD particulier :
• Description de la BDD dans la syntaxe du SGBD utilisé.
• Il s’agit du modèle relationnel (MLD) compété par :
 Les types des données stockées.
 Les contraintes d’intégrité.
• Pour un SGBD relationnel, le MPD est basé sur des requêtes SQL (Structured Query Language).

 Le langage SQL est un langage déclaratif standardisé :

• Développé par IBM en 1970, a fait l'objet de plusieurs normes ANSI/ISO dont la plus répandue est la norme SQL2(1992).
• Implémenté par tous les SGBD relationnels (MySQL, Oracle, etc…), et définit les différentes instructions pour la gestion
des BDD relationnelles (création, insertion, suppression, mise à jour, consultation…).
• Peut s’utiliser en mode interactif (une interface fournie par le SGBD), ou couplé avec un autre langage de programmation.

INGÉ 1 - BASES DE DONNÉES 76 © Salma REBAI-JRIBI

MODÈLE PHYSIQUE DE DONNÉES (MPD)
Types de données
Différents types possibles : alphanumérique, numérique, entier ou temporel.
• Le type de données doit répondre aux besoins fonctionnels de l’application, et il détermine l’encombrement de la
base sur le disque :
 Chaque type est codé sur plus ou moins d’octets, ce qui conditionne également la performance des opérations.

Types numériques :
• Les nombres entiers :
 INT / INTEGER : Entier long signé (codé sur 4 octets).
 SMALLINT: Entier court signé (2 octets) ; BIGINT: entier très long signé (8 octets) ; etc….

• Les nombres décimaux à virgule fixe :

 NUMERIC (X,Y) / DECIMAL (X,Y) : Nombre décimal à représentation exacte (5 à 17 octets), à échelle et précision facultatives.

• Les nombres décimaux à virgule flottante :

 FLOAT (X) : Nombre à virgule flottante dont la représentation est binaire à échelle et précision obligatoires (4 à 8 octets).
 REAL : Réel à virgule flottante à représentation binaire, de faible précision (6 chiffres significatifs, codé sur 4 octets).
 DOUBLE : Réel à virgule flottante à représentation binaire, de meilleure précision (16 chiffres significatifs, codé sur 8 octets).
INGÉ 1 - BASES DE DONNÉES 77 © Salma REBAI-JRIBI
MODÈLE PHYSIQUE DE DONNÉES (MPD)
Types de données
Types alphanumériques :
 CHAR(n) : Chaine de caractères de longueur fixe n, avec n <=255. (Coût: n octets)

 VARCHAR(n_max) : Chaine de caractères de longueur L variable 0< L < n_max, avec n_max <=255. (Coût: L+2 octets)

 TEXT : chaine de caractères de longueur L variable, 0 <L < 2 . ( Coût: L+2 octets)

 BINARY(n) ; VARBINARY (n_max) ; BLOB : pour des chaines de type binaire tels que des documents multimédia.

Types temporels :
 DATE : Date en format "AAAA-MM-JJ".

 DATETIME : Date avec précision de l’heure, selon le format "AAAA-MM-JJ HH:MM:SS".

 TIME : Pour les heures, selon le format "HH:MM:SS".

D’autres types :
 MONEY, BOOLEAN, etc. ...

INGÉ 1 - BASES DE DONNÉES 78 © Salma REBAI-JRIBI

MODÈLE PHYSIQUE DE DONNÉES (MPD)
Les contraintes d’intégrité
Définition : Les contraintes d’intégrité (CI) sont des assertions (des règles) invariantes dans le temps, qui
doivent être satisfaites par les données stockées pour être considérées comme cohérentes.
 Les CIs sont essentielles pour la conception d’une base de données

 Un schéma d’une BDD = un schéma relationnel (ensemble de relations / tables) + un ensemble de contraintes d’intégrité.

 Permettent de garantir la cohérence et l’intégrité de la base de données lors des insertions et mises à jour des données.

 Définies au moment de la création des tables de la BDD : Langage de définition de données de SQL.

 Sont contrôlées et vérifiées automatiquement par le SGBD :

 Toute opération de manipulation des données qui viole une contrainte d’intégrité ne sera pas validée.

Différentes catégories de contraintes d’intégrité :

• Les contraintes d’intégrité de domaine.
• Les contraintes d’intégrité de clé (d’unicité).
• Les contraintes d’intégrité référentielle.
INGÉ 1 - BASES DE DONNÉES 79 © Salma REBAI-JRIBI
MODÈLE PHYSIQUE DE DONNÉES (MPD)
Les contraintes d’intégrité

Contraintes d’intégrité de domaine :

• Les valeurs possibles pour un attribut sont restreintes à un ensemble de valeurs prédéfinies:
 Définition d’un type spécifique de données (Ex. : Entier long, Date, chaine de caractères, … )

 Définition d’une liste fermée de valeurs admises, d’un minium et/ou d’un maximum, d’une condition ou une dépendance
entre les valeurs prises par différents attributs, etc…
 Exemples : AnnéeEmbauche={1980..2002}, AnnéeEmbauche={1980, 1984, 1995}, 18≤ Age ≤ 65, AnnéeEmbauche>AnnéeNaissance ,
les espèces e3 et e6 ne peuvent pas cohabiter, etc…

 Peuvent mettre en jeu une ou plusieurs colonnes, et une ou plusieurs relations (CI Intra-relation et CI inter-relation).

 Exprimées à l’aide de la contrainte CHECK ( <Conditions> )

• Un n-uplet est inséré dans la base, si et seulement si, tous ses champs satisfont les CI de domaine précisées lors
de la création de la BDD.

INGÉ 1 - BASES DE DONNÉES 80 © Salma REBAI-JRIBI

MODÈLE PHYSIQUE DE DONNÉES (MPD)
Les contraintes d’intégrité
Contraintes d’intégrité de Clé (unicité) :
• Permettent de garantir l’unicité et/ou la non nullité des valeurs des colonnes clés des tables de la BDD.
 Définition d’une clé primaire (identifie de manière unique les tuples de la table): PRMARY KEY ( <liste d’attributs> )

 Définition d’une clé candidate non primaire :

 Contrainte UNIQUE ( <liste d’attributs> ) : garantit l’unicité des valeurs de l’ensemble des attributs dans la liste. Peut
être utile pour substituer une PK composite complexe par une PK technique (artificiel).

 Contrainte NOT NULL : garantit que la colonne ne prend pas une valeur nulle (valeur conventionnelle NULL pour une
information inconnue).

 Contrainte DEFAULT <Valeur> : Donne une valeur par défaut à une colonne, quand aucune valeur n’est spécifiée pour la
remplir.

 Ce sont des contraintes de type intra-relation, pouvant mettre en jeu une ou plusieurs colonnes.

INGÉ 1 - BASES DE DONNÉES 81 © Salma REBAI-JRIBI

MODÈLE PHYSIQUE DE DONNÉES (MPD)
Les contraintes d’intégrité
Contraintes d’intégrité référentielle :
• Permettent de garantir que chaque valeur non nulle d’une clé étrangère existe comme valeur clé dans la table
référencée.
 Exprimées à l’aide de la contrainte FOREIGN KEY ( <liste d’attributs> ) REFERENCES <nom table> (<colonnes>)

 La définition d’une CI référentielle entraine un certain nombre de vérification lors de la manipulation des données :

 Un ordre dans la création ou la destruction des tables.

 Vérification de l’existence de la valeur de la clé étrangère (dans la table référencée) lors de l’insertion d’un nouveau n-uplet.

 Vérification de l’absence d’une clé étrangère qui fait référence à la clé primaire d’un n-uplet de la table référencée qu’on cherche à le
modifier ou à le supprimer. Si c’est le cas, plusieurs stratégies sont envisageables :

o Suppression (ou mise à jour ) interdite, Suppression (ou mise à jour) en cascade, Suppression (ou mise à jour) avec initialisation
(Ex: valeur NULL si l’opération est possible).

 Peuvent mettre en jeu une ou plusieurs relations (CI Intra-relation ou inter-relation).

INGÉ 1 - BASES DE DONNÉES 82 © Salma REBAI-JRIBI

LE LANGAGE SQL (STRUCTURED QUERY LANGUAGE)
Différents types d’instructions

Le langage SQL est un langage déclaratif standardisé, qui définit différentes catégories
d’instructions pour la gestion des bases de données relationnelles :
• Langage de Définition de Données (LDD) : La définition de la structure et des objets de la BDD.

• Langage de Manipulation de Données (LMD) : La manipulation des données stockées dans la BDD.

• Langage de Contrôle de Données (LCD) : Le contrôle des droits d’accès des utilisateurs de la BDD.
• Langage de Contrôle de Transaction (LCT) : La gestion des transactions.

Remarque : Le langage SQL est non sensible à la casse (select = SELECT), chaque requête doit se terminer par « ; ».

INGÉ 1 - BASES DE DONNÉES 83 © Salma REBAI-JRIBI

LE LANGAGE SQL
Le Langage de Définition de Données (LDD)

Rôle: Définition de la structure et des objets d’une base de données

• Création de nouvelles bases de données.
• Création, modification, suppression des tables de la BDD.
• Définition du domaine de données et des contraintes d’intégrité.
• Gestion des vues dans la BDD.
• etc..…

Exemples d’instructions du LDD :

• CREATE : pour créer une base de données ou une table.
• ALTER : pour modifier un objet de la base de données.
• DROP : pour supprimer une table, une vue ou une base de données.
INGÉ 1 - BASES DE DONNÉES 84 © Salma REBAI-JRIBI
LE LANGAGE SQL
Le Langage de Manipulation de Données (LMD)

Rôle: Gestion et manipulation des données stockées dans la base de données

• Insertion, mise à jour et suppression des tuples.

• Recherche et visualisation des données contenues dans les tables.

Exemples d’instructions du LMD :

• INSERT : pour insérer des enregistrements.
• UPDATE : pour la mise à jour des données.

• DELETE : pour supprimer des données .

• SELECT : pour la sélection (consultation) des données.

INGÉ 1 - BASES DE DONNÉES 85 © Salma REBAI-JRIBI

 LE LANGAGE SQL
Les Langages de Contrôle de Données (LCD) et de Transaction (LCT)

Le Langage de Contrôle de Données (LCD)

• Rôle : Gestion des droits d’accès des utilisateurs à la base et à ses objets

• Exemples d’instructions du LCD:

 GRANT : pour donner des autorisations aux utilisateurs.
 REVOKE : pour retirer des droits précédemment attribués.
 DENY : pour interdire un droit.

 Le Langage de Contrôle de Transaction (LCT)

• Rôle : Gestion des transactions (validation ou annulation d’une séquence d’opérations sur les données).

• Exemples d’instructions du LCT:

 COMMIT : pour valider les opérations effectuées lors d’une transaction.
 ROLLBACK : pour annuler les opérations effectuées lors d’une transaction.

INGÉ 1 - BASES DE DONNÉES 86 © Salma REBAI-JRIBI

SQL – LANGAGE LDD
Création et exploitation d’une BDD
Création d’une base de données:
• Syntaxe : CREATE DATABASE [IF NOT EXISTS] db_name [create_specification]
 Remarque : Les spécifications permettent notamment de définir l’encodage des caractères de la base.

• Exemple : CREATE DATABASE Entreprise CHARACTER SET 'utf8' ;

Utilisation d’une base de données :
• Syntaxe : USE db_name ;
• Exemple : USE Enterprise ;

Suppression d’une base de données :

• Syntaxe : DROP DATABASE [IF EXISTS] db_name ;
• Exemple : DROP DATABASE IF EXISTS Entreprise ;
Modification d’une base de données :
• Syntaxe : ALTER DATABASE db_name alter_specification [, alter_specification, ….] ;
INGÉ 1 - BASES DE DONNÉES 87 © Salma REBAI-JRIBI
SQL – LANGAGE LDD
Création des tables
Requête de Création d’une table
• Syntaxe :
CREATE TABLE [IF NOT EXISTS] nom_table (
nom_col_1 Type_1 [CONSTRAINT nom_1_1] contrainte_col1_1 [CONSTRAINT nom_1_2] contrainte_col1_2 ,
… ….. ….. ,
nom_col_n Type_n [CONSTRAINT nom_n_1] contrainte_coln_1 …….. ,
[CONSTRAINT nom_1] contrainte_de_table_1 ,
… …. … ,
[CONSTRAINT nom_p] contrainte_de_table_p ) ;

• Remarque : Seules les noms de colonnes et leurs types sont obligatoires.

• Les contraintes de colonne : PRIMARY KEY ; NOT NULL ; AUTO_INCREMENT ; UNIQUE ; DEFAULT ; CHECK ; etc…
• Les contraintes de table : PRIMARY KEY (liste_colonnes) ; FOREIGN KEY (liste_colonnes) REFERENCES table (liste_colonnes)
; UNIQUE (liste_colonnes) ; CHECK (colonnes) ; etc…

INGÉ 1 - BASES DE DONNÉES 88 © Salma REBAI-JRIBI

SQL – LANGAGE LDD
Création des tables
Requête de Création d’une table
• Exemple 1: CREATE TABLE Adresse (
Pays VARCHAR(50) ,
CP VARCHAR(10) ,
Ville VARCHAR(20) NOT NULL ,
Code_Iso VARCHAR(3) ,
PRIMARY KEY (Pays, CP) ) ;
• Exemple 2 : CREATE TABLE Personne (
N°SS CHAR(13) PRIMARY KEY ,
Nom VARCHAR(25) NOT NULL ,
Age INTEGER(3) CHECK (Age BETWEEN 18 AND 65) ,
Mariage CHAR(13) ,
CodePostal VARCHAR(10) , 
Pays VARCHAR(50) ,
FOREIGN KEY (Mariage) REFERENCES Personne(N°SS) ,
FOREIGN KEY (Pays , CodePostal) REFERENCES Adresse (Pays , CP) ) ;
INGÉ 1 - BASES DE DONNÉES 89 © Salma REBAI-JRIBI
SQL – LANGAGE LDD
Modification des tables
Ajout ou modification d’une colonne :
• Syntaxe : ALTER TABLE nom_table {ADD / MODIFY} COLUMN ( nom_colonne type_données [constraintes ] , ...) ;
• Exemple : ALTER TABLE Personne ADD COLUMN (Taille SMALLINT) ;

Suppression d’une colonne :

• Syntaxe : ALTER TABLE nom_table DROP COLUMN nom_colonne ;
• Exemple : ALTER TABLE Personne DROP COLUMN Taille ;

Renommage d’une colonne :

• Syntaxe SQL :ALTER TABLE nom_table RENAME COLUMN ancien_nom TO nouveau_nom ; ( version MySQL > 8.0)
• Syntaxe MySQL :ALTER TABLE nom_table CHANGE [COLUMN ] ancien_nom nouveau_nom type_données ;
• Exemple 1 : ALTER TABLE Personne RENAME COLUMN CodePostal To Adresse ;
• Exemple 2 : ALTER TABLE Personne CHANGE COLUMN CodePostal Adresse INTEGER(5) ;
INGÉ 1 - BASES DE DONNÉES 90 © Salma REBAI-JRIBI
SQL – LANGAGE LDD
Modification et Suppression des tables
Ajout d’une contrainte de table :
• Syntaxe : ALTER TABLE nom_table ADD CONSTRAINT nom_contrainte Contrainte ;
• Exemple : ALTER TABLE Personne ADD CONSTRAINT cont1 UNIQUE (Nom, Prenom) ;

Suppression d’une contrainte de table :

• Syntaxe SQL : ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte ;
• Syntaxe spécifique MySQL : ALTER TABLE nom_table DROP FOREIGN KEY nom_contrainte ;
ALTER TABLE nom_table DROP INDEX nom_contrainte ;
• Exemple : ALTER TABLE Personne DROP INDEX cont1 ;

Suppression d’une table :

• Syntaxe : DROP TABLE nom_table ;
• Exemple : DROP TABLE Personne ;
INGÉ 1 - BASES DE DONNÉES 91 © Salma REBAI-JRIBI
MANIPULATION DES BASES DE DONNÉES
RELATIONNELLES
SQL – LANGAGE LMD
Alimentation d’une BDD

Ajout de nouveaux tuples dans une table : Instruction INSERT

• Insertion explicite des valeurs des propriétés du tuple à ajouter.
• Insertion des valeurs par l’intermédiaire d’une requête de sélection (valeurs existantes dans la base).

• Syntaxe 1: « Insertion explicite des valeurs » :

INSERT INTO nom_table ( nom_col1, nom_col2, …., nom_coln) VALUES ( val1, val2, …., valn) ;
 Les propriétés non valorisées prennent la valeur « NULL ».
 Si aucune propriété n’est spécifiée, les propriétés seront considérées dans leur ordre de définition (instruction CREATE).

 Exemples : INSERT INTO Personne (N°SS, nom, Prenom, Age) VALUES ( '4546788', 'Dupont' , 'Jean', 45) ;
INSERT INTO Livre (id, name, pub_date) VALUES (6578, 'BDD', '2017-03-30');

INGÉ 1 - BASES DE DONNÉES 93 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Alimentation d’une BDD

Ajout de nouveaux tuples dans une table : Instruction INSERT

• Insertion explicite des valeurs des propriétés du tuple à ajouter.
• Insertion des valeurs par l’intermédiaire d’une requête de sélection (valeurs existantes dans la base).

• Syntaxe 2: « Insertion par l’intermédiaire d’une sélection »

INSERT INTO nom_table ( nom_col1, nom_col2, …., nom_coln) SELECT …. ;

 La requête Select doit retourner le même nombre de propriétés que celles à valoriser.

 Exemple : INSERT INTO Credit (Date, Montant, Motif)

SELECT Date, Montant, 'Annulation de débit'
FROM Debit WHERE [Link] = '2001-12-25' ;

INGÉ 1 - BASES DE DONNÉES 94 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Mise à jour et Suppression des données
Suppression des n-uplets
• Syntaxe: DELETE FROM nom_table [ WHERE conditions_filtrer_tuples_à_supprimer ] ;
• Exemple : DELETE FROM Personne WHERE Age <20 ;

Suppression de toutes les occurrences d’une table

• Syntaxe: TRUNCATE TABLE nom_table ;
• Exemple : TRUNCATE table Credit ;

Modification des n-uplets

• Syntaxe: UPDATE nom_table SET nom_col_1= value1, nom_col_2=val2, …. , nom_coln=valn [ WHERE condition ] ;
• Exemple :
UPDATE Personne SET Age = 55 where N°SS = '14546788 ' ;
UPDATE Compte SET Total = Total * 1.2 where N°compte = '15672920' ;
INGÉ 1 - BASES DE DONNÉES 95 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD
Principe :
• Consulter et extraire les informations de la base de données respectant les critères de recherche (filtres, union,
tris, … ).
• Requêtes SQL (instruction SELECT) basées sur l’algèbre relationnelle.
 Définition: L’algèbre relationnelle est un ensemble d’opérations formelles agissant sur des tables (relations) pour produire
de nouvelles relations.
 Une requête peut combiner plusieurs opérations et plusieurs tables et résulte en une nouvelle table (existence éphémère)

Cinq Opérations relationnelles (algébriques) de base:

• 2 opérations unaires : Projection, Restriction (Sélection).
• 3 opérations binaires : Union , Différence et Produit cartésien.
• Autres opérations dérivées: Jointure, Intersection, Division, renommage.

INGÉ 1 - BASES DE DONNÉES 96 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD
La commande SELECT :
Syntaxe Simplifiée

SELECT [ALL | DISTINCT] { * | <liste_attributs>}

FROM nom_table1 [, nom_table2, ….]
[ WHERE conditions ]
[ ORDER BY col1[ ASC | DESC ], col2… ] ;

• La clause « SELECT » spécifie les attributs (colonnes) qui doivent apparaitre dans le résultat.
 « * » : permet de récupérer toutes les colonnes de la (les) table(s) interrogée(s) par la requête.
• La clause « FROM » spécifie les tables sur lesquelles portent la requête.
• La clause « WHERE » est facultative et énonce une condition pour restreindre les tuples sélectionnés.
 Les opérateurs logiques (AND, OR, NOT,…) peuvent être utilisés pour accumuler plusieurs conditions.
• La clause « ORDER BY » est facultative et permet de trier les tuples sélectionnés.

INGÉ 1 - BASES DE DONNÉES 97 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD
Exemple d’une commande SELECT :

• Exemple: Table Personne id Nom Prénom Age Poids taille

1 Evain Sofia 23 64 1,7
2 Bornon Aline 22 70 1,6
3 Martin Sofia 30 90 1,9
4 Monot Alexis 27 87 1,85

Nom Prénom
Requête SQL
Evain Sofia
SELECT Nom, Prénom
FROM Personne Bornon Aline
WHERE Age < 30 ; Monot Alexis

INGÉ 1 - BASES DE DONNÉES 98 © Salma REBAI-JRIBI

INTERROGATION DES BASES DE DONNÉES
RELATIONNELLES
EXEMPLES DE REQUÊTES
SQL – LANGAGE LMD
Interrogation d’une BDD – Projection
Projection :
• Filtrer un sous-ensemble de colonnes/attributs à récupérer.
• La clause « SELECT » : Sélection de certaines colonnes d’une table.
• Exemple: Table Personne id Nom Prénom Age Poids taille
1 Evain Sofia 23 64 1,7
2 Bornon Aline 22 70 1,6
3 Martin Sofia 30 90 1,9
4 Monot Alexis 27 87 1,85
Nom Prénom
Requête SQL
Evain Sofia
SELECT Nom, Prénom
FROM Personne ; Bornon Aline
Martin Pierre
Monot Alexis
INGÉ 1 - BASES DE DONNÉES 100 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD – Projection
Projection:
• La clause « SELECT » : Sélection de certaines colonnes d’une table.
• L’opérateur DISTINCT : Garder uniquement les valeurs distinctes des tuples (« ALL » par défaut).
• Exemple: Table Personne id Nom Prénom Age Poids taille
1 Evain Sofia 23 64 1,7
2 Bornon Aline 22 70 1,6
3 Martin Sofia 30 90 1,9
4 Monot Alexis 27 87 1,85
Prénom
Requête SQL Requête SQL Prénom
Sofia
SELECT Prénom Sofia
Aline SELECT DISTINCT Prénom
FROM Personne ; FROM Personne ; Aline
Sofia
Alexis
Alexis
INGÉ 1 - BASES DE DONNÉES 101 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD – Projection
Projection & Renommage :
• La clause « SELECT » : Sélection de certaines colonnes d’une table.
• L’opérateur AS : renommer des colonnes dans la table résultat.
• Exemple: Table Personne id Nom Prénom Age Poids taille
1 Evain Sofia 23 64 1,7
2 Bornon Aline 22 70 1,6
3 Martin Sofia 30 90 1,9
4 Monot Alexis 27 87 1,85

Prénom Poids_arrondi
Requête SQL
Sofia 64
SELECT Prénom, Poids As Poids_arrondi
FROM Personne ; Aline 70
Sofia 90

INGÉ 1 - BASES DE DONNÉES

Alexis 87 © Salma REBAI-JRIBI
102
SQL – LANGAGE LMD
Interrogation d’une BDD – Projection
Projection & Opérations mathématiques :
• La clause « SELECT » : Sélection de certaines colonnes d’une table.
• Utilisation des opérations arithmétiques (+, -, * , / ) pour créer une nouvelle colonne à partir d’autres existantes.
• Exemple: Table Personne id Nom Prénom Age Poids taille
1 Evain Sofia 23 64 1,7
2 Bornon Aline 22 70 1,6
3 Martin Sofia 30 90 1,9
4 Monot Alexis 27 87 1,85
Prénom Taille_cm
Requête SQL
Sofia 170
SELECT Prénom, Taille *100 As Taille_cm
FROM Personne ; Aline 160
Sofia 190

INGÉ 1 - BASES DE DONNÉES

Alexis 185 © Salma REBAI-JRIBI
103
SQL – LANGAGE LMD
Interrogation d’une BDD – Projection
Projection & Concaténation des champs :
• La clause « SELECT » : Sélection de certaines colonnes d’une table.
• La fonction « Concat() » de MySQL permet de concaténer plusieurs champs de type caractères.
• Exemple: Table Personne id Nom Prénom Age Poids taille
1 Evain Sofia 23 64 1,7
2 Bornon Aline 22 70 1,6
3 Martin Sofia 30 90 1,9
4 Monot Alexis 27 87 1,85
nomComplet Taille_cm
Requête SQL
Evain Sofia 170 cm
SELECT CONCAT(Nom , ' ', Prénom) As nomComplet ,
Bornon Aline 160 cm
CONCAT (Taille *100, ' cm') As Taille_cm
FROM Personne ; Martin Sofia 190 cm

INGÉ 1 - BASES DE DONNÉES 104

Monot Alexis 185 cm © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD – Restriction
Restriction :
• La clause « WHERE » : définie une condition pour filtrer les n-uplets à conserver.
• La condition est une expression booléenne exprimée à l’aide d’opérateurs de comparaison et d’opérateurs logiques.
 Opérateurs de comparaison : = , != , <> , < , <= , > , >= , etc…
 Opérateurs logiques pour combiner plusieurs conditions : OR, AND, NOT
 Peut porter sur une ou plusieurs colonnes des tables manipulées.

• Exemple: Table Achat Requête SQL

id client tarif date SELECT *
1 Pierre 102 2012-10-23 FROM Achat
WHERE client = ' Pierre ' ;
2 Song 47 2012-10-27
3 Marie 18 2012-11-05
4 Marie 20 2012-11-14 id client tarif Date
5 Alexis 160 2012-12-03 1 Pierre 102 2012-10-23
INGÉ 1 - BASES DE DONNÉES 105 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD – Restriction
Restriction & Projection :
• La clause « WHERE » : définie une condition pour filtrer les n-uplets à conserver.
 Opérateurs de comparaison : = , != , <> , < , <= , > , >= ,etc…
 Opérateurs Logiques pour combiner plusieurs conditions : OR, AND, NOT

• Exemple: Table Achat

id client tarif date Requête SQL
1 Pierre 102 2012-10-23 SELECT client, tarif
FROM Achat
2 Song 47 2012-10-27
WHERE tarif > 45 AND tarif <150 ;
3 Marie 18 2012-11-05
4 Marie 20 2012-11-14
5 Alexis 160 2012-12-03 client tarif
Pierre 102
Song 47
INGÉ 1 - BASES DE DONNÉES 106 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD – Projection & Restriction
Exercice d’application : Soit le Schéma relationnel suivant d’une BDD pour la gestion de l’activité d’une
chaine hotellière :
Hotel ( NumHotel , NomH, AdresseH, TelephoneH, Date_Inauguration, Nombre_Etoiles)
Client ( Id_Client , Nom, Date_Naissance, Adresse, Email )
Chambre ( NumChambre, Nbre_Lits,Vue, Tarif, #Hotel)
Reservation (NumReservation, Date_Reservation, Date_Arrivee, Date_Depart, #Chambre, #Client)
• Questions :
 Q1. Déterminer la liste des chambres qui ont été réservées le 10 Septembre 2019.

 Q2. Donner les noms et adresses des hôtels « 4 étoiles » qui ont été inaugurés entre janvier 2015 et décembre 2018.

 Q3. Trouver la liste des chambres doubles, ayant une « vue sur mer » et un tarif inférieur à 250 euro par nuit.

 Q4. Trouver les dates de réservation et les identifiants des clients qui ont réservé au moins 3 jours consécutifs dans une
chambre d’hôtel.
INGÉ 1 - BASES DE DONNÉES 107 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD – Projection & Restriction

Exercice d’application : Correction (1/2)

 Q1. Déterminer la liste des chambres qui ont été réservées le 10 Septembre 2019.
Select Chambre
From Reservation
Where Date_Reservation = '2019-09-10' ;

 Q2. Donner les noms et adresses des hôtels « 4 étoiles » qui ont été inaugurés entre janvier 2015 et décembre
2018.
Select NomH, AdresseH
From Hotel
Where Nombre_Etoiles = '4 étoiles' and
Date_Inauguration >= '2015-01-01' and Date_Inauguration <= '2018-12-31' ;
INGÉ 1 - BASES DE DONNÉES 108 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD – Projection & Restriction
Exercice d’application : Correction (2/2)
 Q3. Trouver la liste des chambres doubles, ayant une « vue sur mer » et un tarif inférieur à 250 euro par nuit.
Select NumChambre
From Chambre
Where Nbre_Lits = 2 and Vue = 'vue sur mer' and tarif < 250 ;

 Q4. Trouver les dates de réservation et les identifiants des clients qui ont réservé au moins 3 jours consécutifs
dans une chambre d’hôtel.
Select Date_Reservation, Client
From Reservation
Where datediff( Date_Depart, Date_Arrivee ) >= 3 ;

INGÉ 1 - BASES DE DONNÉES 109 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD – Restriction
Restriction :
• La clause « WHERE » : définie une condition pour filtrer les n-uplets à conserver.
 Opérateurs de comparaison : BETWEEN… AND… , NOT BETWEEN… AND…

• Exemple: Table Achat

Requête SQL Requête SQL
id client tarif date
SELECT client, tarif SELECT client, tarif
1 Pierre 102 2012-10-23
FROM Achat FROM Achat
2 Song 47 2012-10-27 WHERE tarif BETWEEN 45 AND 150 ; WHERE tarif NOT BETWEEN 45 AND 150 ;
3 Marie 18 2012-11-05
4 Marie 20 2012-11-14
5 Alexis 160 2012-12-03 client tarif id client tarif
Pierre 102 3 Marie 18
Song 47 4 Marie 20
5 Alexis 160
INGÉ 1 - BASES DE DONNÉES 110 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD – Restriction
Restriction :
• La clause « WHERE » : définie une condition pour filtrer les n-uplets à conserver.
 Opérateurs de comparaison : LIKE motif, NOT LIKE motif pour les conditions sur les chaines de caractères.
 Le caractère « % » peut être utilisé pour désigner une suite de 0 à N caractères inconnus, le caractère « _ » pour désigner un
caractère quelconque, les fonctions « UPPER( ) » et « LOWER( ) », etc…

Requête SQL
• Exemple: Table Achat
id client tarif date SELECT client, tarif, date
FROM Achat
1 Marie-Pierre 102 2012-10-23 WHERE client LIKE 'Ma%' ;
2 Song 47 2012-10-27
3 Marie-sophie 18 2012-11-05 client tarif date
4 Marie 20 2012-11-14 Marie-Pierre 102 2012-10-23
5 Alexis 160 2012-12-03 Marie-sophie 18 2012-11-05
Marie 20 2012-11-14
INGÉ 1 - BASES DE DONNÉES 111 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD – Restriction
Restriction :
• La clause « WHERE » : définie une condition pour filtrer les n-uplets à conserver.
 Opérateurs de comparaison : LIKE motif, NOT LIKE motif pour les conditions sur les chaines de caractères.
 Le caractère « % » peut être utilisé pour désigner une suite de 0 à N caractères inconnus, le caractère « _ » pour désigner un
caractère quelconque, les fonctions « UPPER( ) » et « LOWER( ) », etc…

• Exemple: Table Achat Requête SQL

id client tarif date
SELECT client, tarif, date
1 Marie-Pierre 102 2012-10-23 FROM Achat
2 Song 47 2012-10-27 WHERE UPPER(client) LIKE '%SO%' ;

3 Marie-sophie 18 2012-11-05
4 Marie 20 2012-11-14 client tarif date
5 Alexis 160 2012-12-03 Song 47 2012-10-27
Marie-sophie 18 2012-11-05
INGÉ 1 - BASES DE DONNÉES 112 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD – Restriction
Restriction :
• La clause « WHERE » : définie une condition pour filtrer les n-uplets à conserver.
 Opérateurs de comparaison « IS NULL » et « IS NOT NULL » : test sur la détermination ou indétermination
des valeurs d’un attribut.

• Exemple: Sélectionner les informations des produits dont le stock est renseigné
Table Approvisionnement Requête SQL
id Produit tarif stockProd dep SELECT *
FROM Approvisionnement
1 lait 102 null 75 WHERE stockProd IS NOT NULL ;
2 biscuit 47 2500 78
3 jus 18 1000 75 id Produit tarif stockProd dep
4 yaourt 50 null 98
2 biscuit 47 2500 78
3 jus 18 1000 75
INGÉ 1 - BASES DE DONNÉES 113 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD – Restriction

Restriction :
• La clause « WHERE » : définie une condition pour filtrer les n-uplets à conserver.
 Opérateurs de comparaison : IN (constante1, const2, …), NOT IN (const1, const2, …)

• Exemple: Table Achat

id client tarif date Requête SQL
1 Pierre 102 2012-10-23 SELECT client, tarif, date
FROM Achat
2 Song 47 2012-10-27 WHERE client NOT IN ('Pierre', 'Marie') ;
3 Marie 18 2012-11-05
4 Marie 20 2012-11-14
5 Alexis 160 2012-12-03 client tarif date
Song 47 2012-10-27
Alexis 160 2012-12-03
INGÉ 1 - BASES DE DONNÉES 114 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD – Tri
Tri du résultat:
• La clause « ORDER BY » : trier les tuples par rapport aux valeurs d’une ou plusieurs colonnes.
 ORDER BY … DESC (ordre décroissant), ORDER BY … ASC (ordre croissant).
 Ordre croissant par défaut. Requête SQL
SELECT id, Nom, Prénom, Age
• Exemple: Table Personne
FROM Personne
id Nom Prénom Age Poids Taille ORDER BY Prénom ASC, Age DESC ;
1 Evain Sofia 23 64 1,7
2 Bornon Aline 22 70 1,6 id Nom Prénom Age
3 Monot Alexis 27 87 1,85 3 Monot Alexis 27
4 Martin Sofia 30 90 1,9 2 Bornon Aline 22
4 Martin Sofia 30
1 Evain Sofia 23
INGÉ 1 - BASES DE DONNÉES 115 © Salma REBAI-JRIBI
INTERROGATION DES BASES DE DONNÉES
RELATIONNELLES
REQUÊTES AVANCÉES MULTI-TABLES
SQL – LANGAGE LMD
Interrogation d’une BDD - Imbrication des requêtes
Restriction - Requêtes imbriquées :
• La clause « WHERE » définie une condition pour filtrer les n-uplets à conserver.
 Opérateurs d’inclusion/exclusion : IN (sous-requête), NOT IN (sous-requête)
 Une requête peut utiliser le résultat renvoyé par une autre requête (sous requête).
 L’exécution de la requête principale restera en attente du résultat des sous requêtes.
Requête SQL
• Exemple 1 : Noms et tarifs des produits pouvant être approvisionnés auprès de
fournisseurs situés dans les mêmes départements que ceux des magasins ? SELECT Produit, tarif
FROM Approvisionnement
Table Approvisionnement Table Fournisseur
WHERE dep IN (
id Produit tarif dep Id_F Nom dep SELECT dep
1 lait 102 75 1 Produit frais 75 FROM Fournisseur ) ;
2 biscuit 47 78 2 L’agriculteur 99 Produit tarif
3 jus 18 75
lait 102
4 yaourt 20 98
jus 18
INGÉ 1 - BASES DE DONNÉES 117 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Imbrication des requêtes

Restriction - Requêtes imbriquées :

• La clause « WHERE » définie une condition pour filtrer les n-uplets à conserver.
 Opérateurs d’inclusion/exclusion : IN (sous-requête) pour , NOT IN (sous-requête)
Requête SQL
• Exemple 2 : Noms et téléphones des clients n’ayant passé aucune commande ?
SELECT nomClient, téléphone
Table Clients Table Commandes FROM Clients
WHERE num NOT IN (
num nomClient téléphone numCmde dateCmde numCl
SELECT DISTINCT numCl
1 Jean Dupont 0611546788 1 20-10-2017 1 FROM Commandes
2 Michel Keller 0745632345 2 01-01-2018 2 );

3 Martin Janset 0678902323 3 01-01-2018 2

4 14-10-2018 1 nomClient téléphone
5 30-09-2018 1 Martin Janset 0678902323

INGÉ 1 - BASES DE DONNÉES 118 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Imbrication des requêtes
Restriction - Requêtes imbriquées :
• La clause « WHERE » : définie une condition pour filtrer les n-uplets à conserver.
 Opérateurs : ANY ; ALL
 expr opérateur_comparaison ANY(sous-requête) : vraie si au moins un n-uplet de la sous-requête vérifie la comparaison.
 expr opérateur_comparaison ALL(sous-requête) : vraie si tous les n-uplets de la sous-requête vérifient la comparaison.

• Exemple : Donner les commandes passées après la dernière livraison des produits ? Requête SQL
Table Commande Table Approvisionnement SELECT *
id Produit tarif DateLiv dep FROM Commande
WHERE dateCmde > ALL (
1 lait 102 01-05-2018 75 SELECT dateLiv
2 biscuit 47 01-11-2017 78 FROM Approvisionnement) ;
3 jus 18 30-09-2018 75
4 yaourt 20 15-08-2018 98 numCmde dateCmde numCl
4 14-10-2018 1
INGÉ 1 - BASES DE DONNÉES 119 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes corrélées
Restriction : Requêtes corrélées
• La clause « WHERE » : définie une condition pour filtrer les n-uplets à conserver.
 Opérateur [NOT ] EXISTS (sous-requête) : Teste l’existence de tuples (lignes) retournés par la sous-requête.
 Aucun champ n’est spécifié dans la condition WHERE principale. La corrélation entre les deux requêtes est spécifiée dans la
clause where de la sous requête (on relie la clé de la table de la sous-requête à la clé de la table de la requête principale ).

• Exemple : Donner les informations des clients qui ont passé au moins une commande ?
Requête SQL
Table Client Table Commande
SELECT * FROM Client
numCl nomClient Téléphone numCmde dateCmde numCl
WHERE EXISTS (
1 Jean Dupont 0611546788 1 20-10-2017 1 SELECT * FROM Commande
2 Michel Keller 0745632345 2 01-01-2018 2 WHERE numCl = [Link] ) ;

3 Martin Janset 0678902323 3 01-01-2018 2

numCl nomClient Téléphone
4 14-10-2018 1
1 Jean Dupont 0611546788
5 30-09-2018 1
2 Michel Keller 0745632345
INGÉ 1 - BASES DE DONNÉES 120 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes avancées
Exercice d’application : Soit le Schéma relationnel suivant pour la gestion de l’activité d’une chaine hotellière :
Hotel ( NumHotel , NomH, AdresseH, TelephoneH, Date_Inauguration, Nombre_Etoiles)
Client ( Id_Client , Nom, Date_Naissance, Adresse, Email )
Chambre ( NumChambre, Nbre_Lits, Vue, Tarif, #Hotel)
Reservation (NumReservation, Date_Reservation, Date_Arrivee, Date_Depart, #Chambre, #Client)

• Questions:
 Q1. Donner la liste des chambres (numéros des chambres et des hôtels correspondants) qui n’ont jamais été réservées.

 Q2. Donner le numéro, le tarif et la vue des chambres qui ont été occupées par le client N°145787 depuis le début de
l’année 2017.

 Q3. Trouver l’hôtel ayant la chambre double avec le tarif le plus élevé dans la chaine hotellière.

 Q4. Donner la liste des chambres doubles ayant un tarif inférieur à celui proposé pour une chambre simple avec une « vue
sur mer », triées par ordre décroissant de leurs tarifs.
INGÉ 1 - BASES DE DONNÉES 121 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes avancées
Exercice d’application : Correction (1/2)
 Q1. Donner la liste des chambres (numéro des chambres et des hôtels correspondants) qui n’ont jamais été
réservées.
Select NumChambre, Hotel From Chambre
Where NumChambre NOT IN (
Select Chambre From Reservation
);

 Q2. Donner le numéro, le tarif et la vue des chambres qui ont été occupées par le client N°145787 depuis
l’année 2017.
Select NumChambre, Tarif, Vue From Chambre
Where NumChambre IN (
Select Chambre From Reservation
Where Client = 145787 AND Date_Arrivee >= '2017-01-01' ) ;
INGÉ 1 - BASES DE DONNÉES 122 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes avancées
Exercice d’application : Correction (2/2)
 Q3. Trouver l’hôtel ayant la chambre double avec le tarif le plus élevé dans la chaine hotellière.
Select Hotel From Chambre
Where Nbre_Lits =2 and Tarif >= ALL (
Select Tarif From Chambre Where Nbre_Lits = 2 ) ;

 Q4. Donner la liste des chambres doubles ayant un tarif inférieur à celui proposé pour une chambre simple
avec une « vue sur mer », triées par ordre décroissant de leurs tarifs.
Select NumChambre From Chambre
Where Nbre_Lits =2 and Tarif < ANY (
Select Tarif From Chambre
Where Nbre_Lits = 1 and lower(Vue) Like '% mer % ' )
Order by Tarif DESC ;
INGÉ 1 - BASES DE DONNÉES 123 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes ensemblistes
Opérations Ensemblistes:
• UNION : permet d’unir tous les tuples issus de plusieurs requêtes « SELECT ».
• INTERSECTION : permet de récupérer les tuples communs à plusieurs requêtes « SELECT ».
• DIFFERENCE : permet de récupérer les tuples de la première requête SELECT après élimination de ceux
résultants des autres requêtes.

• Syntaxe SQL :
< SELECT 1 > [ UNION / EXCEPT / MINUS/ INTERSECT ] < SELECT 2 >
 N.B: Les tables résultats des requêtes SELECT doivent avoir le même schéma.
 Seulement la commande UNION est supporté par MySQL.

INGÉ 1 - BASES DE DONNÉES 124 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes ensemblistes
Union :
• La clause « UNION » : permet d’unir les tuples résultats de deux ou plusieurs requêtes « SELECT ».

• Exemple : La liste des départements, où on peut trouver soit un Magasin d’approvisionnement soit un Fournisseur?
Table Approvisionnement Table Fournisseur
Requête SQL
id Produit tarif dep Id_F Nom dep
SELECT dep FROM Approvisionnement
1 lait 102 75 1 Produit frais 75 UNION
2 biscuit 47 78 SELECT dep FROM Fournisseur ;
2 L’agriculteur 99
3 jus 18 75
dep
4 yaourt 20 98
75 Les doublons sont
78 éliminés du résultat.

98
99
INGÉ 1 - BASES DE DONNÉES 125 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes multi-tables
Produit Cartésien :
• La clause « FROM » : spécifie les tables sur lesquelles portent la requête.
• Quand plusieurs tables sont énumérées, la table résultat est le produit cartésien de toutes les tables énumérées:
 Les colonnes de la table résultat correspond à la concaténation des différents attributs des tables spécifiées par la clause FROM
 Les lignes (enregistrements) de la table résultat correspond à la concaténation des différentes combinaisons entre les tuples des
tables énumérées.
 Les tables peuvent être renommées dans la requête pour simplifier leurs référencement : FROM nom_de_table [AS] nouveau_nom

• Exemple : Table Produit Table Fournisseur id Produit Nom dep

id Produit tarif dep Id_F Nom dep 1 lait Produit frais 75
1 lait 102 75 1 Produit frais 75 2 biscuit Produit frais 75
2 biscuit 47 78 2 L’agriculteur 99 3 yaourt Produit frais 75
3 yaourt 20 98 Requête SQL 1 lait L’agriculteur 99
SELECT [Link], [Link], [Link], [Link] 2 biscuit L’agriculteur 99
FROM Produit P , Fournisseur F ;
INGÉ 1 - BASES DE DONNÉES
3 yaourt L’agriculteur 99
© Salma REBAI-JRIBI
126
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes multi-tables
La Jointure :
• L’opération de jointure correspond à un produit cartésien entre deux ou plusieurs tables avec une
restriction des tuples selon des conditions sur une ou plusieurs colonnes :
 Permet d’effectuer des recherches sur des données réparties sur plusieurs tables.
 S‘utilise habituellement entre les tables reliées par des clés étrangères pour exploiter les relations (DF) entre les
données.

INGÉ 1 - BASES DE DONNÉES 127 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes multi-tables
La Jointure :
• Différents types de jointure possible

• Syntaxe SQL :
SELECT <liste colonnes>
FROM <TablePrincipale> tp [ INNER / LEFT/ RIGHT ] JOIN
<TableJointe> tj
ON <tp.nom_colonne = tj. nom_colonne>
[ AND / OR tp.cl2 = tj.cl2 ]
INGÉ 1 - BASES DE DONNÉES 128 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes multi-tables
La Jointure : INNER JOIN
• C’est la jointure par défaut, qui compare deux tables et retourne tous les
enregistrements comportant une concordance, en général, entre une clé primaire PK
et une clé étrangère FK.
• Exemple:

Requête SQL
SELECT e.ID_Eleve, [Link], [Link], [Link], [Link]
FROM Eleve e INNER JOIN Note n
ON n.Id_Eleve = e.Id_Eleve ;

INGÉ 1 - BASES DE DONNÉES 129 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes multi-tables
La Jointure : RIGHT JOIN
• La jointure droite « RIGHT JOIN » permet de lister tous les tuples de la deuxième table
(à droite du JOIN ), même s’il n’y a pas de correspondance dans la première table. Les
champs manquants prennent la valeur « NULL ».
• Exemple : Table Fournisseur Table Approvisionnement
Id_F Nom dep id Produit Tarif dep
1 Produit frais 75 1 lait 102 75
2 L’agriculteur 99 2 biscuit 47 78
3 jus 18 75
4 yaourt 20 98
Requête SQL id Produit tarif dep Id_F Nom dep
SELECT *
1 lait 102 75 1 Produit frais 75
FROM Approvisionnement RIGHT JOIN Fournisseur
ON [Link] = [Link] ; 3 jus 18 75 1 Produit frais 75
INGÉ 1 - BASES DE DONNÉES
NULL
130
NULL NULL NULL 2 L’agriculteur 99
© Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes multi-tables
Jointure : LEFT JOIN
• La jointure gauche « LEFT JOIN » permet de lister tous les tuples de la première table
(à gauche du JOIN ), même s’il n’y a pas de correspondance dans la deuxième table. Les
champs manquants prennent la valeur « NULL ».
• Exemple : Table Approvisionnement Table Fournisseur
id Produit Tarif dep Id_F Nom dep
1 lait 102 75 1 Produit frais 75
2 Biscuit 47 78 2 L’agriculteur 99
3 Jus 18 75
4 Yaourt 20 98
Requête SQL
SELECT *
FROM Approvisionnement LEFT JOIN Fournisseur
ON [Link] = [Link] ;
INGÉ 1 - BASES DE DONNÉES 131 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes multi-tables

Exercice d’application : Soit le Schéma relationnel suivant pour la gestion de l’activité d’une chaine hotellière :
Hotel ( NumHotel , NomH, AdresseH, TelephoneH, Date_Inauguration, Nombre_Etoiles)
Client ( Id_Client , Nom, Date_Naissance, Adresse, Email )
Chambre ( NumChambre, Nbre_Lits, Vue, Tarif, #Hotel)
Reservation (NumReservation, Date_Reservation, Date_Arrivee, Date_Depart, #Chambre, #Client)

• Questions:
 Q1. Donner la liste des chambres (nom de l’hôtel et numéro de chambre) qui n’ont jamais été réservées par des
clients.

 Q2. Donner les noms et les adresses des clients qui ont réservé au moins 3 jours consécutifs dans une chambre
d’hôtel, ayant une « vue sur mer ».

INGÉ 1 - BASES DE DONNÉES 132 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes multi-tables
Exercice d’application : Correction
 Q1. Donner la liste des chambres (nom de l’hôtel et numéro de chambre) qui n’ont jamais été réservées par
des clients.
Select [Link], [Link]
From Chambre Ch join Hotel H on [Link] = [Link]
Where [Link] NOT IN ( Select Chambre From Reservation ) ;

 Q2. Donner les noms et les adresses des clients qui ont réservé au moins 3 jours consécutifs dans une
chambre d’hôtel, ayant une « vue sur mer ».
Select Nom, Adresse
From Client Cl join Reservation R on Cl.Id_Client = [Link]
join Chambre Ch on [Link] = [Link]
Where datediff(R.Date_Depart, R.Date_arrivee) >= 3 and lower([Link] ) Like '% mer % ' ;
INGÉ 1 - BASES DE DONNÉES 133 © Salma REBAI-JRIBI
INTERROGATION DES BASES DE DONNÉES
RELATIONNELLES
REQUÊTES AVANCÉES - LES AGRÉGATS
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’agrégation
Les requêtes d’agrégation :
• Jusqu’à présent, les requêtes SQL étudiées permettent d’appliquer des opérations sur les données de la BDD
tuple à tuple (ligne par ligne).
• Les requêtes d’agrégation permettent d’exprimer des conditions et de faire des opérations sur des groupes de
tuples en même temps.
• Avec le langage SQL, nous pouvons :
 Agréger des valeurs sur un groupe de tuples en utilisant les fonctions d’agrégation :
 Ensemble de fonctions prédéfinies qui permettent d’effectuer des opérations statistiques sur un ensemble d’enregistrements
(récupérer le minimum ou le maximum des valeurs, calculer la moyenne ou la somme sur plusieurs enregistrements, etc…)
 Partitionner (logiquement) une relation/table en groupes de tuples et exprimer des conditions sur ces groupes, en
utilisant les opérateurs de groupement « GROUP BY » et « HAVING » :
 Ce groupement est souvent associé à des fonctions d’agrégation.

INGÉ 1 - BASES DE DONNÉES 135 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’agrégation
Les fonctions d’agrégation :
• S’appliquent sur l’ensemble des valeurs prises par un attribut pour en produire une valeur unique.
• Dans le cas de requêtes sans partitionnement, ces fonctions peuvent être utilisées uniquement dans la clause SELECT
 Dans ce cas, il ne faut pas mélanger les fonctions d’agrégation et les attributs simples dans le même « SELECT »

• Sept fonctions d’agrégation prédéfinies :

 COUNT ( [ DISTINCT | ALL ] Attr ) : renvoie le nombre des valeurs non nulles (et distinctes avec DISTINCT ) de l’attribut.
 COUNT(*) : compte le nombre de tuples (lignes) dans la table résultat.
 MAX ( Attr ) : renvoie la plus grande des valeurs de l’attribut dans les tuples résultats.
 MIN ( Attr ) : renvoie la plus petite des valeurs de l’attribut dans les tuples résultats.
 SUM ( [ DISTINCT | ALL ] Attr ) : renvoie la somme des valeurs de l’attribut dans les tuples résultats.
 AVG ( [ DISTINCT | ALL ] Attr ) : renvoie la moyenne des valeurs de l’attribut dans les tuples résultats.
 STDDEV ( Attr ) : retourne l’écart-type des valeurs de l’attribut.
 VARIANCE ( Attr ) : retourne la variance des valeurs de l’attribut.

INGÉ 1 - BASES DE DONNÉES 136 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’agrégation

Les fonctions d’agrégation :

• Exemple1 - fonction Count () : Donner le nombre des clients ayant effectué des achats d’une valeur supérieur à 30
euros ?

Table Achat
id client tarif date Requête SQL
1 Pierre 102 2012-10-23 SELECT COUNT( Distinct client ) As nbre_clients
FROM Achat
2 Simon 47 2012-10-27 WHERE tarif > 30 ;
3 Marie 18 2012-11-05
4 Marie 20 2012-11-14
5 Pierre 160 2012-12-03 nbre_clients
2

INGÉ 1 - BASES DE DONNÉES 137 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’agrégation

Les fonctions d’agrégation :

• Exemple 2 - fonctions de calculs statistiques : Donner le tarif minimum, le tarif maximum, le tarif moyen, ainsi que le
tarif total des achats effectués avant le « 30/11/2012 » ?

Table Achat
id client tarif date Requête SQL
1 Pierre 102 2012-10-23 SELECT MIN(tarif), MAX(tarif), AVG(tarif), SUM(tarif)
FROM Achat
2 Simon 47 2012-10-27 WHERE date < '2012-11-30' ;
3 Marie 18 2012-11-05
4 Marie 20 2012-11-14
5 Pierre 160 2012-12-03 MIN(tarif) MAX(tarif) AVG(tarif) SUM(tarif)
18 102 46.75 187

INGÉ 1 - BASES DE DONNÉES 138 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’agrégation
Les fonctions d’agrégation :
• Attention aux agrégats dans le « WHERE » et au mélange d’attributs et d’agrégats dans le « SELECT »
 Exemple 1: SELECT * FROM Achat WHERE tarif = MAX(tarif) ; => cette formulation est INTERDITE !
 Exemple 2: SELECT client, MAX(tarif) FROM Achat ; => cette formulation donne un résultat ERRONÉ !

• Solution : fonctions d’agrégation & imbrication

 Exemple: Donner le client ayant effectué l’achat avec le tarif le plus élevé ?
Table Achat Requête SQL
id client tarif date SELECT client, tarif
1 Pierre 102 2012-10-23 FROM Achat
WHERE tarif = (
2 Simon 47 2012-10-27 SELECT MAX(tarif) FROM Achat ) ;
3 Marie 18 2012-11-05
4 Marie 20 2012-11-14
client tarif
5 Pierre 160 2012-12-03
Pierre 160
INGÉ 1 - BASES DE DONNÉES 139 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’agrégation
Partitionnement / Groupement :
• Objectif: Pouvoir appliquer des fonctions ou exprimer des conditions, de manière dynamique, sur des groupes
de tuples.
 Exemple: somme des achats par client, le nombre de films par réalisateur, etc…
• La clause « GROUP BY » : Partitionnement logique horizontal d’une table en sous-tables (groupes de tuples), en
fonction des valeurs d’un ou plusieurs attributs.
 Tous les tuples d’un même groupe ont la même valeur pour les attributs spécifiés par la clause « GROUP BY » .
 Les fonctions de calcul opèrent sur chacun des groupes obtenus.
 Dans la clause « SELECT », on ne peut avoir que des fonctions d’agrégation ou les attributs de groupement.
 Exemple: Groupement des projets par ville afin de calculer le nombre total de projets par ville et le budget alloué par ville :

INGÉ 1 - BASES DE DONNÉES 140 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’agrégation

Groupement & fonctions d’agrégation :

• Exemple 1: Donner le total des dépenses (somme des achats) de chacun des clients ?

Requête SQL
Table Achat
SELECT client, SUM (tarif)
id client tarif date
FROM Achat
1 Pierre 102 2012-10-23 GROUP BY client ;
2 Simon 47 2012-10-27
3 Marie 18 2012-11-05
client SUM(tarif)
4 Marie 20 2012-11-14
Pierre 262
5 Pierre 160 2012-12-03
Simon 47
Marie 38
INGÉ 1 - BASES DE DONNÉES 141 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’agrégation

Groupement & fonctions d’agrégation :

• Exemple 2: Donner le total des dépenses de chacun des clients, pour ses achats effectués depuis début novembre
2012 ?

Table Achat Requête SQL

id client tarif date SELECT client, SUM (tarif) AS dépenses
1 Pierre 102 2012-10-23 FROM Achat
WHERE date >= '2012-11-01'
2 Simon 47 2012-10-27 GROUP BY client ;
3 Marie 18 2012-11-05
4 Marie 20 2012-11-14
client dépenses
5 Pierre 160 2012-12-03
Marie 38
Pierre 160

INGÉ 1 - BASES DE DONNÉES 142 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’agrégation

Groupement & Restriction :

• Clause « HAVING » : Spécifier des conditions de restriction sur les groupes obtenus par le « GROUP BY » :
 Sert à éliminer certains groupes, comme la clause « WHERE » sert à éliminer les occurrences (tuples).

 Mêmes règles de syntaxe que la clause « WHERE », Mais:

 « WHERE » : conditions exprimées sur les valeurs d’attributs  ne peut pas contenir des fonctions d’agrégation.

 « HAVING » : conditions exprimées sur les agrégats et les caractéristiques des groupes: fonctions d’agrégation (count(),
min(), etc…) appliquées aux attributs de groupement  ne peut pas contenir des conditions sur des valeurs
d’attributs.
 Une requête « SELECT » peut contenir à la fois les clauses « WHERE » et « HAVING ».
 La clause « WHERE » est évaluée avant le calcul d’agrégat (formation de la partition).
 La clause « HAVING » est évaluée après l’agrégat pour effectuer des restrictions sur les groupes .

INGÉ 1 - BASES DE DONNÉES 143 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’agrégation

Groupement & Restriction :

• Exemple 1: Donner les clients dont le total des dépenses (la somme des achats) dépassent 45 euros ?

Requête SQL
Table Achat
id client tarif date SELECT client, SUM (tarif) AS dépenses
FROM Achat
1 Pierre 102 2012-10-23 GROUP BY client
2 Simon 47 2012-10-27 HAVING dépenses > 45 ;
3 Marie 18 2012-11-05
4 Marie 20 2012-11-14 client dépenses
5 Pierre 160 2012-12-03
Pierre 262
Simon 47
INGÉ 1 - BASES DE DONNÉES 144 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Résumé

La Commande SELECT : Syntaxe Générale

• L’ordre de résolution standard des clauses :
 FROM  WHERE  GROUP BY  HAVING  SELECT  ORDER BY

Syntaxe Générale

SELECT { * | [ALL | DISTINCT] | Attr1 [ As nom_affiché ] [ , Attr2…. ] }

FROM nom_table1 [ [ AS ] alias ] [, nom_table2, ….]
[ WHERE conditions_avant_aggrégat ]
[ GROUP BY Attr1 [, Attr2,…] ]
[ HAVING conditions_aprés_aggrégat ]
[ ORDER BY col1[ ASC | DESC ] [, col2…] ] ;

INGÉ 1 - BASES DE DONNÉES 145 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’Agrégation
Exercice d’application : Soit le Schéma relationnel suivant pour la gestion de l’activité d’une chaine hotellière :
Hotel ( NumHotel , NomH, AdresseH, TelephoneH, Date_Inauguration, Nombre_Etoiles)
Client ( Id_Client , Nom, Date_Naissance, Adresse, Email )
Chambre ( NumChambre, Nbre_Lits, Vue, Tarif, #Hotel)
Reservation (NumReservation, Date_Reservation, Date_Arrivee, Date_Depart, #Chambre, #Client)

• Questions:
 Q1. Donner la requête SQL permettant d’afficher, pour chaque client (identifiant et nom), le nombre total de
réservations effectuées, triés par ordre décroissant du nombre de réservations effectuées.

 Q2. Donner les identifiants des clients ayant effectué au moins une réservation dans chacun des hôtels de la chaine.

 Q3. Donner le nom et l’adresse de l’hôtel qui a eu le plus de réservations effectuées durant l’année 2015.

INGÉ 1 - BASES DE DONNÉES 146 © Salma REBAI-JRIBI

SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’Agrégation
Exercice d’application : Correction (1/2)
 Q1. Donner la requête SQL permettant d’afficher, pour chaque client (identifiant et nom), le nombre total de
réservations effectuées, triés par ordre décroissant du nombre de réservations effectuées.
Select Cl.Id_Client, [Link], COUNT(*) as Nb_reservations
From Client Cl join Reservation R on Cl.Id_Client = [Link]
Group By Cl.Id_Client, [Link]
Order By Nb_reservations DESC ;

 Q2. Donner les identifiants des clients ayant effectué au moins une réservation dans chacun des hôtels de la chaine.
Select [Link]
From Reservation R join Chambre Ch on [Link] = [Link]
Group By [Link]
Having COUNT(DISTINCT [Link]) = ( Select COUNT(*) From Hotel) ;
INGÉ 1 - BASES DE DONNÉES 147 © Salma REBAI-JRIBI
SQL – LANGAGE LMD
Interrogation d’une BDD - Requêtes d’Agrégation
Exercice d’application : Correction (2/2)
 Q3. Donner le nom et l’adresse de l’hôtel qui a eu le plus de réservations effectuées durant l’année 2015.
Select [Link], [Link]
From Hotel H join Chambre Ch on [Link] = [Link]
join Reservation R on [Link] = [Link]
Where R.Date_Reservation like '2015-%-%’
Group by [Link]
Having Count (*) >= ALL (
Select Count(*)
From Hotel H join Chambre Ch on [Link] = [Link]
join Reservation R on [Link] = [Link]
Where R.Date_Reservation like '2015-%-%’
Group by [Link] ) ;
INGÉ 1 - BASES DE DONNÉES 148 © Salma REBAI-JRIBI
ALLER PLUS LOIN AVEC SQL
OPTIMISER SES REQUÊTES D’INTERROGATION
SQL – LES VUES (VIEW)

Définition :
• Une Vue est une table virtuelle dérivée d’une ou plusieurs tables de la BDD, contenant le résultat d’une requête
d’interrogation « SELECT » sur ces tables :
 Seulement la définition de la vue (la requête) est stockée dans la BDD, mais pas son contenu (données/tuples résultats).
 Le contenu de la vue est généré dynamiquement : la requête est ré-évaluée à chaque fois que le vue est référencée.
 Toute modification des données dans les tables d’origine est immédiatement visible dans la vue dés qu’elle est référencée à nouveau.
 Les vues s’utilisent dans les requêtes « SELECT » comme une table réelle de la BDD.

Création d’une Vue :

Syntaxe SQL
CREATE [ OR REPLACE ] VIEW nom_vue [ ( nom_colonne1 [, nom_col2, ...] ) ]
AS <Requête SELECT > ;

• Exemple : CREATE VIEW ParisCinemas AS SELECT * FROM Cinema WHERE ville = 'paris' ;
INGÉ 1 - BASES DE DONNÉES 150 © Salma REBAI-JRIBI
SQL – LES VUES (VIEW)

Intérêts de l’utilisation des vues :

• Simplification des requêtes :
 Enregistrer les requêtes complexes (plusieurs jointures, plusieurs imbrications…) et fréquemment utilisées pour simplifier
l'écriture des requêtes y faisant référence.
 Simplifier l'utilisation des tables comportant plusieurs colonnes, plusieurs tuples, ou des noms complexes, en créant des
vues avec des structures plus simples et/ou de nouvelles informations (calculs statistiques).
• Confidentialité et sécurité des données :
 Restreindre les données visibles aux utilisateurs pour cacher des données sensibles en utilisant des vues de projection
et/ou de sélection (Restriction d’attributs et/ou de tuples)
 Masquer à l’utilisateur l’existence de quelques tables (en définissant des vues basées sur des jointures ).

• Indépendance logique des applications :

 Interface entre les applications et la BDD : Adaptation des données aux applications utilisateurs.
 Indépendance des applications vis-à-vis des modifications de la BDD.
INGÉ 1 - BASES DE DONNÉES 151 © Salma REBAI-JRIBI
SQL – LES VUES (VIEW)
Requêtes de Création
Exemple 1: Vues pour la confidentialité des données
• Soit la table suivante : Employes (emp_id, emp_nom, emp_salaire, emp_prime)
• Confidentialité basée sur les attributs : Requête SQL
CREATE VIEW emp_V1 AS
SELECT emp_id, emp_nom FROM Employes ;

• Confidentialité basée sur les occurrences (tuples) :

Requête SQL
CREATE VIEW emp_V2 AS
SELECT * FROM Employes WHERE salaire < 9000 ;

• Confidentialité sophistiquée (combinaison d’attributs et de tuples ) :

Requête SQL
CREATE VIEW emp_V3 AS
SELECT emp_id, emp_nom FROM Employes
WHERE salaire < 9000 OR prime > 1000 ;
INGÉ 1 - BASES DE DONNÉES 152 © Salma REBAI-JRIBI
SQL – LES VUES (VIEW)
Requêtes de Création
Exemple 2 : Vues pour la simplification des requêtes
• Soit le Schéma relationnel suivant :
Livre (N°livre, titre, catégorie, auteur )
Lecteur (N°lecteur, ident_lecteur, adresse )
Prêt (#Livre, #Lecteur, date_emprunt, date_retour, date_relance )

• Question : Trouver les noms des lecteurs ayant emprunté plus de 3 romans policier de 'Simenon' le même jour.

Vue pour la restructuration des informations pertinentes pour la requête Requête pour la réponse

INGÉ 1 - BASES DE DONNÉES 153 © Salma REBAI-JRIBI

SQL – LES VUES (VIEW)
Requêtes de Création
Exemple 2 : Vues pour la simplification des requêtes
• Soit le Schéma relationnel suivant :
Livre (N°livre, titre, catégorie, auteur )
Lecteur (N°lecteur, ident_lecteur, adresse )
Prêt (#Livre, #Lecteur, date_emprunt, date_retour, date_relance )

• Question : Trouver les noms des lecteurs ayant emprunté plus de 3 romans policier de 'Simenon' le même jour.

Vue pour la restructuration des informations pertinentes pour la requête Requête pour la réponse
Requête SQL Requête SQL
CREATE VIEW lecteur_de_policier AS SELECT DISTINCT ident_lecteur
SELECT N°lecteur, ident_lecteur, N°livre, auteur, date_emprunt FROM lecteur_de_policier
FROM livre Lv join Prêt P on Lv.N°livre = [Link] WHERE auteur = 'Simenon'
join lecteur Lc on [Link] = Lc.N°lecteur GROUP BY N°lecteur, date_emprunt
WHERE [Link] = 'roman policier' ; HAVING COUNT(*) > 3;
INGÉ 1 - BASES DE DONNÉES 154 © Salma REBAI-JRIBI
SQL – LES VUES (VIEW)
Requêtes de Modification et de Suppression
Modification d’une Vue :
Syntaxe SQL
ALTER VIEW nom_vue [ ( nom_colonne1 [, nom_col2, ...] ) ] AS <Nouvelle_SELECT > ;

• Exemple :
Requête SQL
ALTER VIEW lecteur_de_policier AS
SELECT N°lecteur, ident_lecteur, N°livre, date_emprunt
FROM livre Lv join Prêt P on Lv.N°livre = [Link] join lecteur Lc on [Link] = Lc.N°lecteur
WHERE [Link] = 'roman policier’ AND date_emprunt >'2018-01-01' ;

Suppression d’une Vue :

Syntaxe SQL
DROP VIEW [ IF EXISTS ] nom_vue ;

• Exemple : DROP VIEW lecteur_de_policier;

INGÉ 1 - BASES DE DONNÉES 155 © Salma REBAI-JRIBI
SQL – Les fonctions intégrées
Fonctions de Date

Quelques fonctions prédéfinies sur MySQL pour la Manipulation des Date/Time :

Fonction / Syntaxe Description Exemple
DATE_ADD ( date, INTERVAL value addUnit ) Ajoute /soustrait un intervalle de SELECT DATE_ADD("2017-06-
/ DATE_SUB (date, INTERVAL value subUnit ) Time/Date à une date puis renvoie la date 15", INTERVAL 10 DAY);
CURRENT_DATE() / CURRENT_TIME ( ) Retourne la date/heure actuelle SELECT CURRENT_DATE();
Renvoie le nombre de jour entre deux
DATEDIFF (date1, date2) / SELECT DATEDIFF("2017-06-
dates/ renvoie la différence entre deux
TIMEDIFF (time1, time2) 25", "2017-06-15");
Time-DateTime sous la forme hh:mm:ss
SELECT DATE_FORMAT
DATE_FORMAT (date, format) Formate une date selon le format spécifié (BirthDate, "%W %M %d %Y")
FROM Employees;
DAY (date) / YEAR(date) /MONTH (date) / Retourne le jour, l’année, le mois, l’heure, SELECT DAY("2017-06-15
HOUR (dateTime ) / … …, à partir d’une Date /DateTime [Link]");
Retourne le nom du jour de la semaine
DAYNAME (date) SELECT DAYNAME(CURDATE());
pour une date donnée
NOW ( ) Retourne la date et l’heure actuelle SELECT NOW();
INGÉ 1 - BASES DE DONNÉES 156 © Salma REBAI-JRIBI
SQL – Les fonctions intégrées
Fonctions de Texte

Quelques fonctions prédéfinies sur MySQL pour la Manipulation des chaines de caractères :
Fonction / Syntaxe Description Exemple
Retourne la taille d’un chaine de SELECT LENGTH("SQL Tutorial")
LENGTH (text)
caractères AS LengthOfString ;
Convertit une chaine de caractères SELECT LOWER(CustomerName)
UPPER (text) / LOWER (text)
en majuscules/minuscules FROM Customers;
SELECT CustomerName,
Concatène deux ou plusieurs
CONCAT(expression1, expression2, ...) / CONCAT_WS (" ", Address,
expressions ensembles, en utilisant
CONCAT_WS(séparateur, expr1, expr2, …) PostalCode) AS Address
éventuellement un séparateur.
FROM Customers;
Retourne la position de la première
SELECT Name, POSITION ( "a" IN
POSITION(substring IN string) occurrence d'une sous-chaîne dans
Name ) FROM Customers;
une chaîne.
Remplace toutes les occurrences
SELECT REPLACE("XYZ FGH
REPLACE(string, from_string, new_string) d'une sous-chaîne dans une chaîne
XYZ", "X", "m");
par une nouvelle sous-chaîne.
INGÉ 1 - BASES DE DONNÉES 157 © Salma REBAI-JRIBI
SQL – Les fonctions intégrées
Fonctions Numériques

Quelques fonctions prédéfinies sur MySQL pour la Manipulation des nombres :

 Les fonctions d’agrégation déjà étudiées : SUM(), COUNT(), AVG(), MIN() , MAX(), …

Fonction / Syntaxe Description Exemple

Retourne la valeur absolue d’un
ABS(number) SELECT ABS(-243.5);
nombre
Retourne le signe d’un nombre (
SELECT SIGN(bénéfices) FROM
SIGN(number) retourne 1 si nombre > 0 ; 0 si
Ventes ;
nombre = 0 et -1 si nombre <0)
Arrondir un nombre réel vers le bas/le SELECT CEILING(ChiffreAffaire
FLOOR(number) / CEILING(number)
haut ) FROM Ventes;

INGÉ 1 - BASES DE DONNÉES 158 © Salma REBAI-JRIBI

Merci pour votre attention !