01/12/2022

Bases de données
relationnelles.

Dr. Med Ali Zoghlami

[Link]@[Link]

Dr. Mohamed Ali Zoghlami 1

Conception des bases de Données

 Au cours de l’étape de l’analyse de l’existant, le concepteur
recueil un ensemble d’informations:

 des documents
 des objets concrets ou abstraits
 des relations entre ces objets
 des règles de gestion
 des procédures de travail
 etc…

 Objectif : identifier et modéliser les objets du

monde réel ainsi que leurs relations afin de
donner une représentation stable du
domaine étudié sans tenir compte des
aspects techniques et économiques de
mémorisation et d’accès. 2
Dr. Mohamed Ali Zoghlami

1
 01/12/2022

Modèle Conceptuel de Données (MCD)

 Toute donnée recensée doit être mémorisée.

 Le MCD modélise cette mémoire (collective) du

système d’information.

 Un formalisme de référence :
◦ Le modèle Entité-Association.
◦ Particulièrement adapté aux Base de Données
relationnelles.

 Redondance interdite !
3
Dr. Mohamed Ali Zoghlami

Modèle Conceptuel de Données (MCD)

 Exemple

4
Dr. Mohamed Ali Zoghlami

2
 01/12/2022

Modèle Conceptuel de Données (MCD)

 Etapes de construction d’un MCD
1. Réaliser le dictionnaire de données

2. Éliminer les synonymes et polysèmes

◦ Synonymes : les propriétés désignant la même notion(Num_client et
code_client)
◦ Polysème : Une propriété désignant plusieurs notions (nom pour désigner
nom du client et celui du fournisseur)

3. Repérer les identifiants => Chaque identifiant désigne une entité

4. Placer les propriétés dans les entités (selon la règle id -> prop)

5. Construire des relations sémantiques entre les entités ainsi que

leurs cardinalités.

6. Placer les propriétés restantes dans les relations inter-entités

(selon la règle « id1, id2, idn » -> prop)

7. Simplifier et contrôler 5
Dr. Mohamed Ali Zoghlami

MCD…..le dictionnaire de données

 Le dictionnaire des données est un document qui

permet de recenser, classer et trier les données
collectées lors des entretiens ainsi que l’étude des
documents fournis par les interlocuteurs.
Pour être traitées de manière informatisée, les données
doivent être décrites dans un formalisme compris par le
système informatique qui va les gérer. Les formats
génériques utilisés sont:
 Le type alphabétique (rien que des caractères)
 Le type alphanumérique (des caractères, des chiffres)
 Le type numérique (les nombres)
 Le type date
 Le type logique (0-1, Vrai-Faux, Oui-Non)
6
Dr. Mohamed Ali Zoghlami

3
 01/12/2022

MCD…..le dictionnaire de données

 Exemple de DD

Code Type Nature Description Remarques et Contraintes

CLI_NUM N(3) El Numéro d’identification du client Unique – Affecté séquentiellement

CLI_NOM car(20) El Nom de famille du client

….
…. …. …. ….

Num_Fact N(10) El Numéro de la facture client

CLI_ADR car(100) Co Adresse complète du client Composition des rues, CP et ville

Num_cde N(5) El Numéro de la commande client

…
FAC_TOT N(6,2) Ca Montant Total de la facture = somme(ligne_facture)

FAC_LIG N(5,2) Ca Montant ligne facture = Qte * PU

Cod_art car(10) El Code de l’article

7
Dr. Mohamed Ali Zoghlami

MCD…..le dictionnaire de données

 Cas pratique

Bon De Cde

8
Dr. Mohamed Ali Zoghlami

4
 01/12/2022

MCD…..le dictionnaire de données

 Exemple de DD correspondant

9
Dr. Mohamed Ali Zoghlami

Modèle E/A…..identifier les entités/associations

 Entité

 Une Entité est une population d’individus

homogènes. Par exemple, les produits ou les articles
vendus par une entreprise peuvent être regroupés
dans une même entité « Article », car d’un article
à l’autre, les informations ne changent pas de
caractéristiques (à chaque fois, il s’agit de la
désignation, du prix unitaire, et de la qté en stock).

10
Dr. Mohamed Ali Zoghlami

5
 01/12/2022

Modèle E/A…..identifier les entités/associations

 Association

 Une Association est une liaison sémantique

(logique) entre plusieurs entités. Dans notre
exemple, l’association « passer » est une liaison
évidente entre les entités « commande » et « client»,
tandis que l’association « concerne» établit le lien
sémantique entre les entités « commande» et
«produit».

11
Dr. Mohamed Ali Zoghlami

Modèle E/A…..identifier les entités/associations

 Association

 Association binaire …

 Association ternaire…
Enseignant

 Association n-aire… Enseignem-

ent Matière

Classe

 Association réflexive…

12
Dr. Mohamed Ali Zoghlami

6
 01/12/2022

MCD…..identifier les entités/associations

 Attribut

 Un Attribut est une propriété d’une entité ou d’une

association. Par exemple, le prixUnitaire est un
attribut de l’entité « article », le nomClient est un
attribut de l’entité « client », la quantité
commandée est un attribut de l’association «
Concerne».

13
Dr. Mohamed Ali Zoghlami

Modèle E/A…..identifier les entités/associations

 Occurrence, cardinalité

 Une occurrence d’entité, est un élément particulier de

l’entité caractérisé par une valeur qui peut porter cet
élément.

 Les cardinalités d’une entité dans une association

indique le nombre minimum et le nombre maximum de
participations d’une occurrence de l’entité dans
l’association.

14
Dr. Mohamed Ali Zoghlami

7
 01/12/2022

Modèle E/A…..identifier les entités/associations

 Interprétation des cardinalités

 Min 0, 1
 Cardinalité mini = 0 : Certaines occurrences de l’entité
ne participe pas à l’association.
femme 0,? est_mère enfant

 Cardinalité mini = 1 : Toute occurrence de l’entité

participe au moins une fois à l’association.

auteur
livre est_écrit
1,?

15
Dr. Mohamed Ali Zoghlami

Modèle E/A…..identifier les entités/associations

 Interprétation des cardinalités

 Max 1, n
 Cardinalité max =1 : Quand une occurrence de l’entité
participe à l’association, elle n’y participe au plus
qu’une seule fois.
femme
enfant ?,1 est_fils

 Cardinalité max =n : Quand une occurrence de l’entité

participe à l’association, elle peut y participer
plusieurs fois.
voiture
personne ?,n possède

16
Dr. Mohamed Ali Zoghlami

8
 01/12/2022

Modèle E/A…..identifier les entités/associations

 Interprétation des cardinalités

 Les deux liens (pattes) de l’association réflexive

peuvent être labélisés.

17
Dr. Mohamed Ali Zoghlami

Modèle E/A…..identifier les entités/associations

 Exercice : Commercialisation des produits électroménagers
La société X est spécialisée dans la commercialisation des produits
électroménagers, chaque produit est caractérisé par un code, une
désignation et un prix. Tout produit fait partie d’une famille stockée dans
un rayon unique (la famille des appareils audio dans le rayon 1, la
famille des machines à laver dans le rayon 2, etc.).

Un rayon, caractérisé par un code, une localisation et une surface, peut

stocker jusqu’à 5 familles de produits.

Lorsqu’un client passe une commande d’achat des produits, cette

commande est prise en charge par un et un seul représentant de
l’entreprise jusqu’à la livraison.

Un représentant est caractérisé par une matricule, un nom et une adresse.

Une commande concerne un ou plusieurs produits.
 Travail demandé: Elaborer le modèle E/A
18
Dr. Mohamed Ali Zoghlami

9
 01/12/2022

Modèle E/A…..identifier les entités/associations

 Propriété spatio-temporelle

 Lorsqu’une entité ne possède qu’une seule propriété

qui est liée au temps (une date ou le libelle d’un mois),
elle ne se traduit pas comme table dans le schéma
relationnel. La propriété spatio-temporelle qu’elle
contient est simplement intégrée dans la relation
créée suite à la transformation de l’association.
louer voiture
client
1,n 0,n

1,n (Numéro d'ordre chnorologique pour

num différencier les différentes locations
d'un client pour la même voiture)

Date
dateJour
 Date …
21
Dr. Mohamed Ali Zoghlami

Modèle E/A…..identifier les entités/associations

 Propriété spatio-temporelle

? ?

22
Dr. Mohamed Ali Zoghlami

10
 01/12/2022

Modèle E/A…..identifier les entités/associations

 Contrainte d’intégrité fonctionnelle (CIF)

 Une CIF porte sur une association entre deux ou

plusieurs entités traduisant qu’il existe une DF entre
une (ou plusieurs) entité(s) source(s) et une entité
cible. Elle est schématisée comme suit :
Entité_A association Entité_B

CIF

 On peut intégrer directement la DF à l’association en

fléchant la patte la reliant à l’entité cible comme suit :
enfant 0,n mère
1,1 est fils
code_enf code_mère

23
Dr. Mohamed Ali Zoghlami

Modèle E/A…..identifier les entités/associations

 Contrainte d’intégrité fonctionnelle (CIF)

 Exemple de CIF entre plusieurs entités sources et une

entité cible.

 Connaissant la matière et la classe, on peut

déterminer un et seul enseignant.

24
Dr. Mohamed Ali Zoghlami

11
 01/12/2022

Modèle E/A…..identifier les entités/associations

 Identifiant relatif

 Toute entité doit avoir un identifiant permettant de

déterminer toutes les autres propriétés de l’entité.
 Certaines entités ont une existence totalement
dépendante d’autres entités.

 On les identifie par un identifiant relatif par rapport à

l’identifiant principal.
 Exemple : « Chambre » et « Hôtel »

25
Dr. Mohamed Ali Zoghlami

Modèle E/A…..identifier les entités/associations

 Etude de cas : Agence de location
Une entreprise loue du matériel à ses clients à travers ses
différentes agences. La durée de chaque location est précisée
dans le contrat établi entre l’entreprise et le client. La durée peut
être comprise entre une ou plusieurs semaines.

Le catalogue des prix de location est disponible dans chaque

agence.
L’entreprise souhaite informatiser le suivi des locations de
matériels. Une première étude des besoins a permis de dégager
les modules suivants :
 suivi des mouvements de matériel,
 prise en charge et suivi des contrats en cours.
Il vous est demandé une étude critique de la solution fournie en
annexe et d’élaborer le nouveau MCD.

26
Dr. Mohamed Ali Zoghlami

12
 01/12/2022

Modèle E/A…..identifier les entités/associations

 Etude de cas : Agence de location

Modèle E/A à critiquer

client
contrat code client
établit
N°contrat 0,n nom client
1,1
date contrat rue
date commande ville
date début location 1,n
0,n
concerne agence
comporte
1,n code agence
quantité louée raison sociale

0,1
exemplaire matériel matériel
Exemp_mat
code matériel code matériel
0,n
numéro exemplaire 0,n désignation matériel
état exemplaire prix unitaire

27
Dr. Mohamed Ali Zoghlami

MCD…..les types /sous-types d’entité

 Types et sous-types d’entité

 Extensions de modélisation :spécialisation/généralisation

 Une entité puisse être éclatée en deux ou plusieurs

autres entités auxquelles sont rattachées des propriétés
spécifiques

ou

 Elle est réduite à partir de deux ou plusieurs autres

entités.

29
Dr. Mohamed Ali Zoghlami

13
 01/12/2022

MCD…..les types /sous-types d’entité

 Spécialisation

 Entité salarié Salarié

Matricule
Nom
Prénom
Adresse
CongéDeMaternité
serviceMilitaire

 Deux types d’employés, les hommes et les femmes

qui ont des propriétés communes et d’autres
spécifiques à chaque type de salarié

 Eclater l’entité salarié et spécialiser chacune des

entités « Homme » et « Femme »
30
Dr. Mohamed Ali Zoghlami

MCD…..les types /sous-types d’entité

 Spécialisation

 Après éclatement salarié

matricule
nom
prénom
adresse

homme femme
service militaire congé matérnité
spécialisation

31
Dr. Mohamed Ali Zoghlami

14
 01/12/2022

MCD…..les types /sous-types d’entité

 Généralisation

 Dans la généralisation, contrairement à la

spécialisation, ce sont les entités sous-types qui
préexistent ; en conséquence, elles sont
indépendantes de l’entité générique et elles ont leurs
propres identifiants

tiers
généralisation
matricule
raison social
adresse

client fournisseur
code client code fourniss
mode de paiement délais de livraison

32
Dr. Mohamed Ali Zoghlami

MCD…..Etudes de cas
 Délégation de santé
Une délégation régionale de santé s’occupe de tous les hôpitaux de sa
région.
Chacun de ces hôpitaux est composé d’un certain nombre de services et
laboratoires et fait travailler un ensemble de médecins et d’infirmiers.
Les médecins peuvent avoir l’une des trois fonctions suivantes :

 Consultant indépendant : il s’occupe des patients externes, il n’est

rattaché ni à un service ni à un laboratoire.
 Chercheur : dans ce cas il est affecté à un laboratoire.
 Praticien : il est affecté à un service et il aura la charge d’un ou de
plusieurs patients internes de ce service et intervient dans le traitement
d’autres patients du même ou d’un autre service.

A chaque médecin praticien est affecté une équipe d’infirmiers qui doivent
s’occuper des patients dont le médecin a la charge.
 Elaborer le MCD
33
Dr. Mohamed Ali Zoghlami

15
 01/12/2022

Modèle Logique de Données (MLD)

 Règles de passage d’un modèle E/A vers un MLD:

 La transformation du modèle E/A vers le MLD

relationnel est effectuée selon les principes suivants :

MCD MLD
Entité devient Table
Association devient Table
ou se disparait
Propriété devient Attribut

Identifiant devient Clé

36
Dr. Mohamed Ali Zoghlami

Modèle Logique de Données (MLD)

 Règle1: cas d’une association binaire « *, n » - « 1, 1 ».

 La clé de la table issue de l’entité à cardinalité « *, n » (table mère) migre

vers la table issue de l’entité à cardinalité « 1, 1 »(table fille).

 Le schéma relationnel est :

Personne(CIN, nom, prénom, adresse)
37
Maison(coordonnées, surface, #CIN ) Dr. Mohamed Ali Zoghlami

16
 01/12/2022

Modèle Logique de Données (MLD)

 Règle2: cas d’une association binaire «*, n» - «0, 1».

 Création d’une table relative à l’association dont la clé est celui du

l’individu fils. La clé du père apparaît comme simple attribut dans cette
table. Le formalisme individuel est le suivant :

 Le schéma relationnel est :

Personne(CIN, nom, prénom, adresse)
Voiture(matricule, type, couleur)
38
Vendu(matricule, date_acquisition, CIN) Dr. Mohamed Ali Zoghlami

Modèle Logique de Données (MLD)

 Règle3: cas d’une association binaire « 0, 1 »- « 1, 1 ».

 La clé primaire de la première table migre, comme étant clé étrangère,

dans la deuxième table et vice-versa. .

 Le schéma relationnel est :

Employé(Matricule, NomPrénom, Grade, DateRec, NumImmat#)
Voiture(NumImmat, Modèle, Motorisation, Couleur, Matricule#)
 Rq: Cette solution n’est pas autorisée dans le cas des SGBD
qui n’acceptent pas des clés étrangères null.
Dr. Mohamed Ali Zoghlami
39

17
 01/12/2022

Modèle Logique de Données (MLD)

 Règle3: cas d’une association binaire « 0, 1 » - « 1, 1 ».

 Solution 2:
La clé primaire de la table côté « 0,1 » migre, comme étant clé étrangère,
dans la table côté « 1,1 » .

 Le schéma relationnel est :

Employé(Matricule, NomPrénom, Grade, DateRec)
Voiture(NumImmat, Modèle, Motorisation, Couleur, Matricule#)
40
Dr. Mohamed Ali Zoghlami

Modèle Logique de Données (MLD)

 Règle 4: cas d’une association binaire « 0, 1 » - « 0, 1 ».

 Pour ce cas de cardinalité, on peut soit créer une table spécifique à

l’association dont la clé est l’un des deux clés des tables issues des
entités participantes à l’association, l’autre clé sera comme clé étrangère ;
soit faire migrer la clé d’une table vers l’autre. On distingue quatre
solutions :

41
Dr. Mohamed Ali Zoghlami

18
 01/12/2022

Modèle Logique de Données (MLD)

 Règle 4: cas d’une association binaire « 0, 1 » - « 0, 1 ».

 Solution 1:

 Solution 2:

 Solution 3:

 Solution 4:

42
Dr. Mohamed Ali Zoghlami

Modèle Logique de Données (MLD)

 Règle 5: cas d’une association binaire « *, n » - « *, n ».

 L’association se transforme en une table dont la clé est la concaténation

des clés des tables issues des entités participantes à l’association. Si
cette association est porteuse de données, ses propriétés deviennent des
attributs de cette table.
 Le formalisme individuel est le suivant :

 Le schéma relationnel devient :

Commande(N°Cde, date_Cde)
Article(référence, désignation, prix_u)
43
Ligne_Cde(N°Cde, référence, Qtécdée) Dr. Mohamed Ali Zoghlami

19
 01/12/2022

Modèle Logique de Données (MLD)

 Règle 6: cas d’une association ternaire ou supérieure.

 L’association se transforme en une table dont la clé est la concaténation

des clés des tables issues des entités participantes à l’association.
 Le formalisme individuel est le suivant :

 Le schéma relationnel devient :

Etudiant(CIN, nom, prénom, adresse)
Matière(code_mat, libellé)
Semestre(N°semestre, année)
Contrôler(CIN, code_mat, N°semestre, année, date, note)
44
Dr. Mohamed Ali Zoghlami

Modèle Logique de Données (MLD)

 Règle 7: cas d’une association réflexive « *, n » – « 0, 1».

 Création d’une table relative à l’association dont la clé est celui de la table
issue de l’entité, possédant - sous un autre nom - l’identifiant de cette
entité comme simple attribut.
 Le formalisme individuel est le suivant :

 Le schéma relationnel devient :

Salarié(Matricule, nomPénom, grade, adresse)
Dirigé_par(Matricule, MatriculeChef)
45
Dr. Mohamed Ali Zoghlami

20
 01/12/2022

Modèle Logique de Données (MLD)

 Règle 8: cas d’une association réflexive « *, n » – « *, n »

 Pour ce type de modèle, il est nécessaire de créer une table relative à

l’association dont la clé est composée de deux fois l’attribut clé dont l’une
porte un autre nom.
 Le formalisme individuel est le suivant :

 Le schéma relationnel devient :

Ville(CodeVille, NomVille, NbrePopulation)
Distanciee(CodeVille, CodeVille2, Nbre_Km) 46
Dr. Mohamed Ali Zoghlami

Modèle Logique de Données (MLD)

 Etude de cas N° 1: A partir du MCD présenté ci-après, établir le MLD relationnel.

concerne produit
commande 1,n 0,n code_prod
qté_cdée
num_cde désignation
date_cde
1,1 1,1
1,1
pris en charge
fait partie

passer
représentant
0,n code_rep
nom_rep
1,n
tél
1,n famille
rayon
client code_rayon code_famille
code_cli stocker nom_famille
surface 1,5
nom_cli 1,1
localisation
adresse
47
Dr. Mohamed Ali Zoghlami

21
 01/12/2022

Modèle Logique de Données (MLD)

 Etude de cas N° 2:
Le schéma relationnel présenté ci-après décrit le système de
gestion des coûts des pièces de rechange lors des interventions de
réparation des véhicules. A partir de ce schéma, déduire le Modèle
Conceptuel de Données (MCD).

Piece(codpiece, designation, qtestck, couleur)

Fournisseur(codfr, nomfr, adrfr, soldefr )

Entree(codpiece, codfr, dateJour, qteEntree, prixAchat)

Intervention(NumInt, typeInt, dateDebut, dateFin)

Utilisation(NumInt, codpiece, dateJour, qteSortie, prixSortie)

49
Dr. Mohamed Ali Zoghlami

Modèle Logique de Données (MLD)

 Règle 9: Prise en compte des types et sous-types
A) La spécialisation.

 Solution 1 : Migration seulement de la clé du sur-type vers les sous-types

 Solution 2 : Migration de tous les attributs y compris la clé du sur-type vers
les sous-types.
 Solution 3 : On duplique les attributs du sur-type dans les sous-types et on
supprime le sur-type.
 Solution 4: On transfère la totalité des attributs des sous-types vers le sur-
type et on ajoute un attribut supplémentaire pour distinguer le type de
l’occurrence.

51
Dr. Mohamed Ali Zoghlami

22
 01/12/2022

Modèle Logique de Données (MLD)

 Règle 9: Prise en compte des types et sous-types
B) La généralisation.
Dans le cas d’une généralisation, les sous-types ont leurs propres identifiants
qui vont se transformer en clés respectives pour chaque table sous-type.
La totalité des attributs du sur-type migre vers les sous-types.

 Le modèle individuel devient :

52
Dr. Mohamed Ali Zoghlami

Modèle Logique de Données (MLD)

 Règle 10: Identifiant relatif

 La clé de la table fille n’a pas d’existence si elle n’est pas composée entre
autre de la clé de la table mère.

 Le modèle individuel devient :

 Le schéma relationnel devient :

Hôtel(N°hôtel, adresse)
Chambre(N°chambre, N°hôtel, surface) 53
Dr. Mohamed Ali Zoghlami

23
 01/12/2022

Modèle Logique de Données (MLD)

 Règle 11: cas d’une CIF

 Le schéma relationnel :
Enseignant(Matricule, nom, prénom, adresse)
Classe(CodeClasse, NomClasse, Discipline, Cycle)
Matière(CodeMatière, NomMatière, ChargeHoraire, Coef)
Enseigner(CodeClasse#, CodeMatière#, Matricule#)
54
Dr. Mohamed Ali Zoghlami

Le modèle relationnel
 Introduction
 le modèle relationnel est un modèle d'organisation
des données sous forme de Tables ou Relations
(Ensemble de valeurs).

 Les colonnes des tables s'appellent des attributs et les

lignes des tuples.

 Chaque table doit avoir une clé primaire constituée

par un ou plusieurs attributs permettant de
distinguer chaque tuple de la Relation par rapport à
tous les autres.

Dr. Mohamed Ali Zoghlami 55

24
 01/12/2022

Le modèle relationnel
 Les liens sémantiques existant entre les ensembles
sont réalisés par l'intermédiaire de clés étrangères
faisant elles-mêmes référence à des clés primaires
d'autres tables.
 Exemple de relation
Nom de la relation Clé étrangère
attribut

Etudiant CIN Nom Prénom dateNaiss CodeClasse

1212 Mhamdi Med 12/12/70 info2G
1313 Tounsi Salah 06/06/72 info1
tuples
1414 Abdallah Abdallah 07/11/70 info2G
1515 Ben Ali Wahid 14/08/73 info3I
1616 Ben Yahya Ali 01/06/74 info2G

Dr. Mohamed Ali Zoghlami 56

Le modèle relationnel
 Le modèle relationnel est le plus souvent décrit sous la
forme suivante, les clés primaires étant soulignées et les
clés étrangères marquées par un signe distinctif.

Client(codclt, nomclt, ville, codpost, caclt)

Commande(numcde, datecde, mntcde, codclt#)
Article(codart, nomart, couleur, qtestck, prixunit)
Lignecde(codart#, numcde#, qtecdee)

 La conception d’une base de données relationnelles passe d'abord

par l'identification des objets de gestion (client, commande, …) et
des règles de gestion du domaine modélisé.

 Un client peut passer une ou plusieurs commandes.

 Une commande passée elle est passée par un et un seul client.
 Une commande porte sur un ou plusieurs articles et un article figure
dans zéro ou plusieurs commandes.

Dr. Mohamed Ali Zoghlami 57

25
 01/12/2022

Le Modèle relationnel
 La normalisation
 La normalisation est un processus de décomposition
d'une relation en plusieurs relations plus réduites.

 L'utilité de la normalisation est d'éviter les

redondances et les anomalies de mise à jour.

 Le processus de normalisation est basé sur deux

principaux concepts:

◦ Les dépendances fonctionnelles qui traduisent des

contraintes sur les données.
◦ Les formes normales qui aboutissent à des relations bien
conçues. Dr. Mohamed Ali Zoghlami 58

Le Modèle relationnel
 Normalisation : Dépendance fonctionnelle

 On dit qu'il existe une dépendance fonctionnelle

(DF) entre X et Y, si la connaissance d'une valeur
de X détermine une et une seule valeur de Y. On
dit alors que X détermine Y. La notation adoptée
est : X Y

 Exemple: la connaissance de la valeur d’un

numéro de client nous permet de connaître sans
ambiguïté la valeur d’un et d’un seul nom pour ce
client.
 Formalisme:
NuméroClient (NomClient, adresse, téléphone, email)
 On dit Numéro client détermine NomClient, adresse,
téléphone et email Dr. Mohamed Ali Zoghlami
59

26
 01/12/2022

Le Modèle relationnel
 Normalisation : Dépendance fonctionnelle

 Dépendance fonctionnelle élémentaire:

 une DF X Y est élémentaire s’il n’existe pas une
propriété Z, sous-ensemble de X, décrivant une DF de
type Z Y.

Exemple:
CodeProduit Couleur
NuméroCommande, CodeProduit QtéCdée
CodeProduit, Désignation Couleur

La troisième DF n’est élémentaire car il existe à l’intérieur

d’elle une DF de type:
CodeProduit Couleur qui était déjà une DF élémentaire.60
Dr. Mohamed Ali Zoghlami

Le Modèle relationnel
 Normalisation : Dépendance fonctionnelle

 Dépendance fonctionnelle directe: on dit que la DF

X Y est directe s’il n’existe aucun attribut Z tel que
l’on puisse avoir X Z et Z Y.
En d’autres termes, cela signifie que la dépendance
entre X et Y ne peut être obtenue par transitivité.

 Exemple:
 La DF NumBonCommande NomClient n’est pas
directe puisqu’il existe un 3ème attribut CodeClient tel
que :
NumBonCommande CodeClient
CodeClient NomClient 61
Dr. Mohamed Ali Zoghlami

27
 01/12/2022

Le Modèle relationnel
 Normalisation : Dépendance fonctionnelle

 Dépendance fonctionnelle composée: c’est une DF

qui comporte plusieurs propriétés.

 Exemple:
(Numéro coureur, Type de course) (Temps)

 Connaissant le n° de coureur et le type de la

course, on peut connaitre de façon certaine le temps
chronométré d’un coureur précis sur une course
précise.

62
Dr. Mohamed Ali Zoghlami

Le Modèle relationnel
 Normalisation : Dépendance fonctionnelle

Clé primaire d’une relation :

Une clé d’une relation R(A1,A2,...,An) est un ensemble
minimum d'attributs de R qui détermine tous les autres
attributs.

 Elle est définie comme étant un sous ensemble X des

attributs A1,A2,... ,An tel que :

1. X A1,A2,... ,An
2. et il n’existe pas Z ⊂ X tel que Z A1,A2,... ,An

65
Dr. Mohamed Ali Zoghlami

28
 01/12/2022

Le Modèle relationnel
 Normalisation : Formes normales

 Formes normales :
Les formes normales peuvent être définies comme des
niveaux de qualité d'un modèle relationnel en
définissant les règles que celui-ci doit respecter.

Elles permettent de décomposer une relation en

plusieurs plus réduites sans perdre de l'informations
tout en préservant les dépendances fonctionnelles.

Cette décomposition permet d'éviter la redondance et

l'incohérence de données ainsi que les problèmes de
mise à jour. Dr. Mohamed Ali Zoghlami
66

Le Modèle relationnel
 Normalisation : Formes normales

 Formes normales :
Le processus de normalisation s'étale sur les formes
normales suivantes:

• 1FN, 2FN, 3FN,

• BCNF (forme normale de Boyce-Codd )
• 4FN, 5FN

Les 4 FN, et 5FN n'ont pas d'intérêt pratique puisqu'elles

sont considérées comme des raffinements du modèle
relationnel face aux problèmes d'optimisation.

67
Dr. Mohamed Ali Zoghlami

29
 01/12/2022

Le Modèle relationnel
 Normalisation : Formes normales

 1FN : Une relation est 1FN, si elle possède au

moins une clé et si les différents attributs de la
relation sont atomiques et chacun de ces attributs
dépend fonctionnellement de la clé et est non répétitif
pour une occurrence de celle-ci.
Exemple: La relation Père() n'est pas en 1FN
Soit la relation suivante:
car l'attribut PrénomEnfant est
Père(NumSS, NomPère, PrénomEnfant)
(121212, Durand, [toto, jojo, dupont]) répétitif si le père a plusieurs
enfants :
Père(NumSS, NomPère)
Après (121212, Durand)
Enfant (CodeEnf, PrénomEnfant, NumSS)
décomposition, (222222, toto, 121212)
on obtient : (333333, jojo, 121212)
(444444, dupont, 121212) 68
Dr. Mohamed Ali Zoghlami

Le Modèle relationnel
 Normalisation : Formes normales

 2FN : Une relation est en deuxième forme normale

(2FN), si et seulement si :
◦ Elle est en 1FN et,
◦ Chacun de ses attributs non-clé dépend de la totalité de la clé
et non d'une partie de la clé.

Exemple:
Soit la relation suivante:
ProduitCde(CodeProd,NumCde , Désignation, QtéCdée)

n'est pas en 2FN car : CodeProd Désignation

Après Produit(CodeProd, Désignation)

décomposition, Ligne_Cde(CodeProd#, NumCde# , QtéCdée)

on obtient : Dr. Mohamed Ali Zoghlami

69

30
 01/12/2022

Le Modèle relationnel
 Normalisation : Formes normales

 3FN : Une relation est en troisième forme normale

(3FN), si et seulement si :
◦ Elle est en 2FN et,
◦ Il n'existe pas des DF entre les attributs non clé (pas de
transitivité).
La 3FN est généralement reconnue comme étant la plus
importante à respecter.

Exemple: Soit la relation suivante:

Voitures(N°Immatriculation , Couleur, Marque, Modèle, Puissance)

n'est pas en 3FN car : Modèle Marque, puissance

Après
Voitures(N°Immatriculation , Couleur, Modèle)
décomposition, Modeles(Modèle, Marque, Puissance)
on obtient : 70
Dr. Mohamed Ali Zoghlami

Le Modèle relationnel
 Normalisation : Formes normales
 Exemple d’incohérences de données

Avant décomposition

N°Immatriculation Couleur Marque Modèle Puissance

132 TU 3232 Grise Renault Mégane 6
120 TU 2020 Rouge Volkswagen Mégane 5

Après décomposition incohérence

Table: Modèles

Duplication de Modèle Marque Puissance

la clé n’est pas Mégane Renault 6
autorisée
Mégane Golf5 Volkswagen 5
Table: Voitures
N°Immatriculation Couleur Modèle
132 TU 3232 Grise Mégane
71
120 TU 2020 Rouge Mégane ( ou Golf5
Dr. Mohamed )
Ali Zoghlami

31
 01/12/2022

Le Modèle relationnel
 Normalisation : Formes normales

 BCNF : Une relation est en forme normale de Boyce-

Codd (BCNF), si et seulement si :
◦ Elle est en 3FN et,
◦ Il n'existe pas des DF autres que
celles pour lesquelles un attribut
clé détermine un attribut non-clé.

Exemple:
Soit la relation suivante:
Localisation(Ville, Rue, CodePostale)

n'est pas en BCNF car : CodePostale Ville

Après
LocalisationVille(CodePostale, Ville)
décomposition, LocalisationRue(CodePostale, Rue)
on obtient : Dr. Mohamed Ali Zoghlami
72

Bases de données relationnelles

Implémentation avec
SQL & PL/SQL

Dr. Mohamed Ali Zoghlami 74

32
 01/12/2022

SQL comme LDD

 Introduction
 Le langage SQL (Structured Query language) est un
langage de définition, de manipulation et de
contrôle de données, il a été développé au milieu des
années 70 chez IBM.

 Ce langage permet d’effectuer certaines

opérations sur les schémas relationnels.

 Opérations de type: création, consultation,

recherche, mise à jour, etc

Dr. Mohamed Ali Zoghlami 75

SQL comme LDD

 Création des tables.
 Syntaxe:
CREATE TABLE [ user.] nom_table
{( def_colonne_1,
def_colonne_2,
...
def_colonne_n
[, contrainte_table]
) [| as sousRequête]};
Syntaxe simplifiée:
create table nomTable(définitions des colonnes)
Avec :
<def_colonne> :
nom_colonne type_colonne [DEFAULT expr] [contrainte_col]
Dr. Mohamed Ali Zoghlami 76

33
 01/12/2022

SQL comme LDD

 Création des tables.
a) Définition d’une colonne :
Définir une colonne, c’est lui donner un nom, un type et
éventuellement une contrainte de colonne. Les principaux types
dont SQL dispose sont les suivants :

 char(n) : type caractère de longueur fixe est égale à n. n c’est

un entier entre 1 et 255.

 Varchar2(n) : type chaîne de caractères de longueur variable. n

est un entier spécifiant la taille de la chaîne et est compris entre
1 et 2000.

 Number[(n[, m])] : nombre de précision n et d’échelle m.

 Long : type caractère de longueur variable allant jusqu’à 2 Go.

 Date : donnée de type date.
Dr. Mohamed Ali Zoghlami 77

SQL comme LDD

Création des tables.
b) Définition d’une Contrainte d’intégrité :
On distingue deux types de contraintes:

b.1) Contrainte de colonne: Elle peut être définie sur une

colonne de la table. La syntaxe de déclaration d’une
contrainte est la suivante :

[constraint <nom_contrainte>]
{ [not]null | {unique | primary key
| { references NomTable (NomColonne)
[on delete cascade] }
| check (condition) }

[ ]: optionnelle.
|: ou.

Dr. Mohamed Ali Zoghlami 78

34
 01/12/2022

SQL comme LDD

 Création des tables.
b.2) Contrainte de table: Elle peut être définie sur la
table toute entière. La syntaxe de déclaration de la
contrainte de table est la suivante :

[constraint <nom_contrainte>]
{unique(col1[,col2,…]) | primary key(col1[,col2,…]) }
| {foreign key(col1[,col2,…])
references NomTable (col1[,col2,…] )
| [on delete cascade] }
| check (condition)

[ ]: optionnelle.
|: ou
Dr. Mohamed Ali Zoghlami 79

SQL comme LDD

 Constraint: est optionnel et sert à attribuer un nom à la contrainte à définir,
avec lequel elle sera sauvegardée dans la base.

 Null : la colonne peut contenir des valeur nulles. Si l’option not null est
spécifiée, la colonne doit avoir une valeur lors de l’insertion.

 Unique : interdit la duplication de la même valeur pour la même colonne

dans d’autres lignes.

 Primary key : vérifie que la colonne est utilisée comme clé primaire. Les
valeurs nulles sont interdites. Une indexe sera créée sur cette colonne.

 Foreign key : définit une contrainte de clé étrangère

 References : définit une contrainte d’intégrité référentielle. La colonne est

donc une clé étrangère. Si la clé primaire est supprimée, les tuples ayant la
même valeur de clé étrangère seront supprimées : ceci se réalise si l’option
‘on delete cascade’ est spécifiée.

 Check : mot clé associé à une condition qui doit être vérifiée pour chaque
valeur inséré[Link]. Mohamed Ali Zoghlami 80

35
 01/12/2022

Le modèle relationnel: Introduction.

 Implémentation des tables.

Client(codclt, nomclt, ville, codpost, caclt)

Commande(numcde, datecde, mntcde, codclt#)
Article(codart, nomart, couleur, qtestck, prixunit)
Lignecde(codart#, numcde#, qtecdee)

Création de la table LigneCde

Contraintes de colonnes Contraintes de table

+
Contraintes de table
Dr. Mohamed Ali Zoghlami 81

SQL comme LDD

 Création des tables.
Exemple: table commande

 Create table commande(

Numcde number primary key, Datecde date
not null, Mntcde number, codclt number
constraint fk_clt references client(codclt) On
delete cascade) ;

Rq: la table client doit être créée en premier lieu car

sa clé primaire figure comme clé étrangère dans
la table commande.

Dr. Mohamed Ali Zoghlami 82

36
 01/12/2022

SQL comme LDD

 Création des tables: Exemple table lignecde.
 Create table lignecde(
codart number constraint fk_art references
article(codart), numcde number constraint fk_cde
references commande (numcde), qtecdee number
constraint ct_qt check (qtecdee>0), constraint
pk_lignecde primary key(codart, numcde) ) ;
 Ou encore
 Create table lignecde( codart number, numcde number,
qtecdee number, constraint ct_qt check (qtecdee>0),
constraint fk_art foreign key (codart) references
article(codart), constraint fk_cde foreign key(numcde)
references commande (numcde), constraint pk_lignecde
primary key(codart, numcde) ) ;
Dr. Mohamed Ali Zoghlami 83

SQL comme LDD

 Création d’une table à partir d’une autre.
On peut créer une nouvelle table à partir d’une
table déjà existante : la nouvelle table hérite
toutes les données de la table source.
Syntaxe :
CREATE table nom_de_la_table
[(alias_colonne1, alias_colonne2, ...)]
AS RequêteSelect;

 Exemple :
Create table myClient(codec, nomc, ca) as
Select codclt, nomclt, caclt from client
Where caclt > 1000 ;
Dr. Mohamed Ali Zoghlami 84

37
 01/12/2022

SQL comme LDD

 Modification de la structure de tables. Alter table
 Le langage LDD permet différentes modifications sur la
structure de la table dont :

- ajout de nouvelles colonnes : en utilisant l’option ‘add

nom_colonne’ ;

- Ajout de nouvelles contraintes d’intégrités ; en utilisant

l’option ‘add constraint ‘ nom_contrainte ;

- Modification des colonnes (type, taille, valeur par défaut

et la C.I not nul) en utilisant l’option ‘modify ’ ;

- Suppression des colonnes ou des contraintes

d’intégrités en utilisant l’option ‘drop’ ;

Dr. Mohamed Ali Zoghlami 85

SQL comme LDD

 Modification de la structure de tables. Alter table
Syntaxe de la commande :

Alter table < nom_table>

[ add { colonne|contrainte},…]
[modify{ colonne,…}]
[drop clause drop];

 la clause drop permet de supprimer colonne, une contrainte

d’unicité, référentielle ou de domaine. Sa syntaxe est :

drop {column nomColonne | primary key | unique (col1,

[col2)…)|constraint <nom_contrainte>}

 Notez bien qu’en cas de suppression d’une clé primaire, il faut

que cette dernière ne soit pas clé étrangère dans une autre
table.

2AGI1,2 : TD LDD , Ex7

Dr. Mohamed Ali Zoghlami 86

38
 01/12/2022

SQL comme LDD

 Modification de la structure de tables: Alter table

Ajout de colonne :
ALTER TABLE [<nom_user>.] <Table> ADD <def_col> ...
avec <def_col> :
nom_colonne[type_de_données] [DEFAULTexpr] ;

Ajout de contrainte :
ALTER TABLE [<nom_user>.] <Table>
ADD [CONSTRAINT nom_const] <type_const> ;
avec <type_const>
UNIQUE (col1[,col2 ...]) |
PRIMARY KEY(col1[,col2 ...]) |
FOREIGN KEY(col1[,col2 ...])
REFERENCES table((col1[,col2 ...]) ) |
CHECK (condition);

Dr. Mohamed Ali Zoghlami 87

SQL comme LDD

Modification de la structure d’une table:
Exemples:

Alter table client add typeclt varchar2(10);

Alter table client Modify nomclt varchar2(30) ;

Alter table my_client

drop column ca;

Alter table lignecde drop constraint ct_qt;

Dr. Mohamed Ali Zoghlami 88

39
 01/12/2022

SQL comme LDD

 Modification de la structure d’une table:
Exemple : ajout de deux contraintes référentielles.

alter table LIGNECDE

add constraint FK_LIGNECDE_CDE foreign key
(NUMCDE) references COMMANDE (NUMCDE) ;

alter table LIGNECDE

add constraint FK_LIGNECDE_ART foreign key
(CODART) references ARTICLE (CODART) ;

Dr. Mohamed Ali Zoghlami 89

SQL comme LDD

 Modification de la structure d’une table:
 Restrictions aux modifications de la structure d’une table

 AJOUT
- On peut ajouter une colonne de type NOT NULL, uniquement si la
table est vide.
- On ne peut ajouter une contrainte uniquement qu’au niveau table.

 MODIFICATION
- On peut rétrécir une colonne uniquement si elle est vide.
- On peut passer une colonne de NULL autorisé à NOT NULL
uniquement si la colonne ne contient pas de valeur NULL
- On ne peut pas modifier une contrainte.

 SUPPRESSION
- On peut supprimer une contrainte par son nom.

Dr. Mohamed Ali Zoghlami 90

40
 01/12/2022

SQL comme LDD

 Suppression d’une table: Drop table

 La commande drop table permet de supprimer

une table. La syntaxe est la suivante :
Drop table < nom_table> [cascade constraints] ;

 L’option ‘cascade constraints’ permet de

supprimer toutes les contraintes d’intégrités
référentielles se référant aux clés primaires de la
table à supprimer.

 Exemple :
Drop table myClient cascade constraints ;
H/D

Dr. Mohamed Ali Zoghlami 91

SQL comme LDD

 Création des vues.
 Une vue est une fenêtre logique sur une ou plusieurs
tables, elle est définie à partir d’une requête d’interrogation.

Dr. Mohamed Ali Zoghlami 92

41
 01/12/2022

SQL comme LDD

 Création des vues.
 Une vue est une fenêtre logique sur une ou plusieurs tables, elle
est définie à partir d’une requête d’interrogation.

Syntaxe :
CREATE [OR REPLACE] VIEW nom_de_vue
[(alias_colonne1, alias_colonne2, ...)]
AS RequêteSelect;

Exemples : Create or replace view vue1000 as

select * from client where caclt >1000 ;

Create or replace view vue_cde_1000 as

select [Link], nomclt
from client clt , commande c
Where [Link] =[Link] And [Link] >=1000 ;

Dr. Mohamed Ali Zoghlami 93

SQL comme LDD

 Création des vues.
 Remarques :
 La clause OR REPLACE permet de supprimer puis de recréer la vue
si elle existe .

 La modification d'une table de base affecte la vue.

 Le corps d'une vue ne peut pas contenir la clause ORDER BY.

 On ne peut pas effectuer des insertions, des mises à jours et des

suppressions dans une vue contenant une jointure, des opérateurs
ensemblistes, des fonctions de groupe, les clauses GROUP BY,
CONNECT BY ou START WITH et l'opérateur DISTINCT.

 Les tables systèmes qui contiennent les vues sont : all_views,

dba_views, user_views.

94
Dr. Mohamed Ali Zoghlami

42
 01/12/2022

SQL comme LDD

 Création des indexes.
 Les indexes créés sur les tables permettent d’accélérer
l’accès à l’information stockée dans les tables. Notons que
SQL sous Oracle crée systématiquement un index pour
chaque colonne définie unique (en particulier la clé primaire).
Syntaxe :
Create INDEX nom_index ON nom_table(liste d’attributs [ASC/DESC]) ;

Exemple :
Create index indClient on client (caclt asc) ;
Drop index indClient ; supprime l’index spécifié.
Dr. Mohamed Ali Zoghlami 95

SQL comme LDD

 Renommage des objets.
La primitive rename permet de renommer un
objet (table, vue, index, etc).

 Syntaxe :
Rename ancien_objet to nouv_objet ;

 Exemple :
Rename myClient to client_tmp ;

Dr. Mohamed Ali Zoghlami 96

43
 01/12/2022

SQL comme LDD

 Création de séquences :
 Une séquence est un objet de la base de données créée par
l’utilisateur et pouvant être partagé par plusieurs utilisateurs
pour générer des nombres entiers uniques et séquentiels.
 La syntaxe de création d’une séquence est la suivante :

CREATE SEQUENCE nomSequence

[INCREMENT BY n]
[START WITH m]
[MAXVALUE p]
[MINVALUE k];

 Remarque :
Vous pouvez interroger la vue système user_sequences pour
voir la structure de la séquence créée.

Dr. Mohamed Ali Zoghlami 97

SQL comme LDD

 Création de séquences :
 Une fois la séquence est créée, celle-ci génère des nombres
séquentiels qui peuvent être utilisés dans les tables.

 Les valeurs générées par une séquence peuvent être manipulées par
les pseudo-colonnes NEXTVAL et CURRVAL .

 La pseudo-colonne NEXTVAL donne le prochain numéro de séquence

et incrémente le compteur des valeurs, même pour des utilisateurs
différents.

 La pseudo-colonne CURRVAL donne le dernier numéro de séquence

utilisé.

 La pseudo-colonne NEXTVAL doit être utilisée pour que CURRVAL

contienne une valeur.

Dr. Mohamed Ali Zoghlami 98

44
 01/12/2022

SQL comme LDD

 Création de séquences :
 Exemple:
 Création d’une séquence Ma_Sequence qui commence la génération
automatique des entiers à partir de 100, avec un intervalle
d’incrémentation de 10 et dont la valeur maximale est 9999.

CREATE SEQUENCE Ma_Sequence

INCREMENT BY 10
START WITH 100
MAXVALUE 9999;

insertion des entiers uniques est séquentiels dans la table Ma_Table:

insert into Ma_Table values(Ma_Sequence.NEXTVAL, …..) ;

Affichage de la valeur actuelle de la séquence:

Select Ma_Sequence.CURRVAL from dual ;

99
Dr. Mohamed Ali Zoghlami

SQL comme LDD

 Création de synonymes :
Un synonyme est utilisé pour faciliter la manipulation d’un objet de la base (table,
vue, etc…), il fournit un autre nom à l’objet : alias. Sert à référencer les objets
sans indiquer leur propriétaire.

 La syntaxe ce création est la suivante:

CREATE [PUBLIC] SYNONYM
[<user>.]<nom_synonyme>
FOR [<user>.]<nom_objet> ;

 Remarques:
- [Public] : le synonyme est accessible par tous les utilisateurs.
- sert à référencier les objets sans indiquer leur base.
- un synonyme privé doit avoir un nom distinct dans le schéma d'un utilisateur.
- un synonyme public peut avoir le nom de l’objet dans son schéma.

100
Dr. Mohamed Ali Zoghlami

45
 01/12/2022

SQL comme LDD

 Création de synonymes :
Exemple de synonymes :
 cat : synonyme de la vue user_catalog (tables, vues, syn. dont l'utilisateur est
propriétaire).

 clu : synonyme de la vue user_clusters ( les différents clusters de l’utilisateur) : un

cluster est une jointure physique entre deux ou plusieurs tables.

 cols : synonyme de la vue user_tab_columns (les attributs des différentes tables

utilisateur).

 dict : synonyme de la vue DICTIONARY.

 ind : synonyme de la vue user_indexes (descriptions des index de l'utilisateur).

 syn : synonyme de la vue user_synonyms (synonymes dont l'utilisateur est propriétaire).

 tab : synonyme de la vue user_tables (tables dont l'utilisateur est propriétaire)

 ... 101
Dr. Mohamed Ali Zoghlami

SQL comme LDD

 Interrogation du dictionnaire de données.
 Le dictionnaire de données est une collection de tables créées et
gérées par le serveur Oracle, il contient les informations de la BD.
L’interrogation du DD permet d’afficher les différents objets créés dans
la base. Les vues système fréquemment consultées sont :
◦ USER_TABLES
◦ USER_INDEXES
◦ USER_CONSTRAINTS
◦ USER_SEQUENCES
◦ USER_OBJECTS
◦ USER_CATALOG
◦ USER_SYNONYMS

 Pour afficher les tables créées :

SELECT * FROM USER_TABLES ou SELECT * FROM TAB;

 Pour afficher les tables, les vues ,les synonymes et les séquences
appartenant à l’utilisateur
SELECT * FROM user_catalog;

Dr. Mohamed Ali Zoghlami 103

46
 01/12/2022

SQL: Langage de Manipulation de Données

C
 Insertion de données.
 L’insertion des données dans une relation peut se faire
soit depuis le clavier soit à partir d’une autre relation.

Syntaxe de la cde d’insertion depuis le clavier:

Insert into <nom_table> [(liste_de_colonnes)] values

(liste_de_valeurs);

Exemple: La structure de la relation client est la suivante

Client(codclt, nomclt, ville , caclt, codpost)

Insert into client values(12, ‘Med Tounsi’, ‘Tunis’, 100, 1002);

Insert into Client(codclt, nomclt, ville) values(30, ’STEG’,
’Ariana’ );
Dr. Mohamed Ali Zoghlami 104

SQL comme LMD

 Insertion des données.
Syntaxe de la cde d’insertion à partir d’une autre table:

Insert into <nom_table> [(liste_de_colonnes)] select ……;

Exemple:

Insert into client_tunis select codclt, nomclt, caclt

From client where ville=‘Tunis’;

 la relation client_tunis est déjà créée et elle a comme

structure (code, nom, ca).

 il devrait avoir une concordance entre le nombre et les

types de colonnes de la table et les données ramenées par
la requête.

Dr. Mohamed Ali Zoghlami 105

47
 01/12/2022

 consultation des données.

Toute opération de consultation se fait par l’instruction
SELECT dont la syntaxe est la suivante:

SELECT [ * | DISTINCT] att1 [, att2, att3, ...]

FROM Table1 [, Table2, Table3, ...]
[WHERE conditions de sélection et/ou de jointure]
[GROUP BY att1 [, att2, ...] [HAVING conditions de
sélection]]
[ORDER BY att1 [ASC | DESC] [, att2 [ASC | DESC], ...] ;

Dr. Mohamed Ali Zoghlami 106

SQL comme LMD

 Consultation simple.
ce type de consultation permet de retrouver les
données à partir d’une seule table.

la syntaxe est la suivante:

Select <liste de colonnes> from <nom_table>;

Exemple:
Select nomclt, caclt from client;
Select * from commande;

* pour afficher toutes les colonnes.

Dr. Mohamed Ali Zoghlami 107

48
 01/12/2022

SQL comme LMD

 Consultation avec qualification.
Une condition est soumise sur les données
recherchées. Ces conditions sont en 3 familles:

a) Condition de comparaison:
Dans ce cas la clause ‘where condition’ est
spécifiée. La syntaxe est la suivante:

Where expr1 opérateur_de_comparaison expr2

Where expr [not] between expr1 and expr2
Where expr [not] in (liste_de_valeurs)
Where nom_colonne [not] like ‘chaine’
Where nom_colonne is [not] null

Dr. Mohamed Ali Zoghlami 108

SQL comme LMD

 Consultation avec qualification.
 expr: désigne un nom de colonne, une constante, une
valeur nulle ou une combinaison de ces éléments par
des opérateurs arithmétiques (-, +, *, /).

 opérateur_de_comparaison={=, <, <=, >, >=, !=}

 Exemple:

 Select * from client where ville in (‘Tunis’, ‘Sousse’);

 Select * from commande where datecde between

’01/01/02’ and sysdate;

 Select codclt, nomclt, caclt from client where

nomclt like ‘%si’;

Dr. Mohamed Ali Zoghlami 109

49
 01/12/2022

SQL comme LMD

b) Condition de jointure:
 Une jointure est un lien entre deux tables disposant d’au
moins une colonne commune. La syntaxe est la suivante:

 Select <liste_de_sélection> from <liste_de_tables> where

<condition de jointure et autres>;

 Exemple: Lister tous les articles commandés entre

’01/01/18’ et ’31/12/18’.

Select [Link], NomArt, qtestck

From article A, commande C, lignecde L
Where [Link] = [Link]
And [Link] = [Link]
And datecde between ’01/01/18’ and ’31/12/18’;

 La lettre A est un alias utilisé pour éviter la confusion entre 2

tables contenant la même colonne.

Dr. Mohamed Ali Zoghlami 110

SQL comme LMD

J c) Condition de sous requêtes:
 SQL permet de comparer une expression ou une
colonne au résultat d’une autre requête. Les 2
requêtes sont dites imbriquées.

 Une condition de sous requêtes peut être

formulée par l’une des clauses suivantes:

 Where expr opérateur_de_comparaison {all |

any} (requête select);
 Where expr [not] in (requête select);
 Where [not] exists (requête select);

Dr. Mohamed Ali Zoghlami 111

50
 01/12/2022

SQL comme LMD

Condition de sous requêtes:
 Rq: les sous requêtes situées après les mots clés IN, ALL et ANY
doivent avoir le même nombre de colonnes que celui spécifié
dans expr.

 L’évaluation de ces sous requêtes peut renvoyer plusieurs

valeurs qui seront interprétées comme suit:

 ALL: la condition est vraie si la comparaison est vrai pour

chacune des valeurs retournées.

 ANY: la condition est vraie si la comparaison est vrai pour au

moins une des valeurs retournées

 IN: la condition est vraie si la comparaison est vrai pour une des
valeurs retournées.

 EXISTS: il est différent des autres, il renvoie le boolien VRAI ou

FAUX selon le résultat de la sous requête.

Dr. Mohamed Ali Zoghlami 112

Exemples d’utilisation des sous requêtes:

 Lister tous les articles dont la qtestck est

supérieure à toute quantité commandée du même
article.

 Select codart, NomArt, qtestck

From article x
Where qtestck > all (select qtecdee
from lignecde y
where [Link] = [Link]);

Dr. Mohamed Ali Zoghlami 113

51
 01/12/2022

SQL comme LMD

Exercices:

 Lister tous les articles dont la qtestck est

supérieure à au moins une quantité commandée
du même article.

 Lister tous les clients qui ont passé une

commande durant ce mois.

 Lister tous les clients qui ont passé au moins une

commande entre ’01/01/01’ et aujourd’hui et dont
la qtécdée est égale à la qtéstck.

Dr. Mohamed Ali Zoghlami 114

SQL comme LMD

Les fonctions d’agrégat:
 SQL offre différentes fonctions de calcul sur les
données. Ces fonctions sont:

 Count(): détermine le nombre de lignes ramenées par SELECT.

 SUM([distinct] colonne): calcule la somme de chaque colonne

spécifiée.

 MAX(colonne): retourne la valeur maximale prise par une

colonne.

 MIN(colonne): retourne la valeur minimale prise par une

colonne.

 AVG(colonne): calcule la moyenne des valeurs prise par une

colonne.

A/B TP2 : Exercice 1

Dr. Mohamed Ali Zoghlami 115

52
 01/12/2022

SQL comme LMD

Les fonctions d’agrégat:
 Rq: Les fonctions d’agrégat ne sont jamais
utilisées dans la clause where, si on voulait
spécifier une condition, on utilise la clause
HAVING.

 Exemple:

 Calcul de la valeur de stock de tous les articles

emmagasinés.

 Select SUM(prixunit * qtestck) valeurStck from

article;

Dr. Mohamed Ali Zoghlami 116

SQL comme LMD

Groupement de données:
 Avec les fonctions d’agrégat, SQL permet de
grouper des lignes de données ayant des valeurs
communes.
 Exemple:
 Le nbre de commandes passées par client:

Dr. Mohamed Ali Zoghlami 117

53
 01/12/2022

SQL comme LMD

Groupement de données:
 Exemple 2:
 Lister le nbre d’articles dans chaque commande:

Select Numcde, count(*) nbrearticle

From lignecde
Group by Numcde;

 Exemple 3:
lister le montant total de chaque commande (on
suppose que la table commande ne contient pas
un champ Mntcde).

Dr. Mohamed Ali Zoghlami 118

SQL comme LMD

Groupement de données avec condition:
 Dans le cas où une condition est à appliquer sur
le groupe de données, elle peut être exprimée via
la clause where | having. Si la condition comporte
une fonction d’agrégat, seulement la clause
having est utilisée.

 Exemple1: lister les commandes contenant plus

que cinq articles commandés.

Select numcde, count(*) from ligneCde

Group by numcde
Having count(*) > 5;

Dr. Mohamed Ali Zoghlami 119

54
 01/12/2022

SQL comme LMD

 Exemple2: lister la somme de toutes les
commandes dont le nombre de lignes
d’articles > à 4;

Select numcde, sum(qtecdee*prixunit)

totalcde
From article A, lignecde L
Where [Link]=[Link]
Group by numcde
Having count([Link])>4;

Dr. Mohamed Ali Zoghlami 120

SQL comme LMD

Tri des données retournées par l’ordre select:
 La clause ORDER BY permet de trier les données
retournées dans un ordre select. Le tri se fait sur
un ou plusieurs attributs d’une manière
croissante [ASC] ou décroissante [DESC].

 Exemple:

 Select * from article order by qtestck;

 Select * from article order by 5 desc;

 Select * from commande order by mntcde desc,

datecde;

Dr. Mohamed Ali Zoghlami 121

55
 01/12/2022

SQL comme LMD

Modification des données.
 La commande UPDATE permet de modifier les
données stockées dans la table. La modification
peur concerner une ou plusieurs colonnes et elle
peut affecter une ou plusieurs lignes.

 La syntaxe est la suivante:

Update <nom_table>
Set {colonne={expr | (requête sql)}
| (colonne1, colonne2 [, colonne3])= (requête sql)}
[where condition];
|
Dr. Mohamed Ali Zoghlami 122

SQL comme LMD

Modification des données.

 Exemple1: Augmenter le prix unitaire de 10% des

articles ayant le stock minimum.

Update article
Set prixunit = prixunit * 1.1
Where qtestck = (select min(qtestck) from article);

 Exemple2: Remplacer la qtestck et le prixunit de

l’article 1000 par ceux de l’article 2000.

Dr. Mohamed Ali Zoghlami 123

56
 01/12/2022

SQL comme LMD

Suppression des données.
 La commande DELETE permet de
supprimer un ou plusieurs tuples de la
table.

 Syntaxe:

Delete from <nom_table>

[where condition]

Dr. Mohamed Ali Zoghlami 124

Exercices.
 Quels sont les articles vendus : des plus vendus vers
les moins vendus (en qté).

 Quels sont les articles vendus aux clients habitant la

ville de tunis.

 Afficher pour chaque commande passée son montant

total: montant total= somme(qtecdee * prixunit).

 Mettre le résultat de la dernière requête dans une vue

‘vuetotalcde’ pour l’utiliser a fin d’afficher pour
chaque client son code, son nom et le total des
commandes passées par celui-ci.

Dr. Mohamed Ali Zoghlami 125

57
 01/12/2022

Exercices.
 Lister les clients qui ont commandé tous les
articles.

 Afficher la commande (numcde, datecde,

totalcde) dont le montant est le plus élevé.
Utiliser le résultat de la requête R3.

 Afficher le client (code, nom) qui a passé la

commande (numcde, datecde, totalcde) dont
le montant est le plus élevé.

Dr. Mohamed Ali Zoghlami 126

Les fonctions
Les fonctions numériques.
 ABS(n):
 COS(n)
 SIN(n)
 POWER(m,n)
 SQRT(n)
 LN(n)
 LOG(m,n)
 MOD(m,n)
 etc…..

Dr. Mohamed Ali Zoghlami 127

58
 01/12/2022

Les fonctions
Les fonctions de manipulation des chaines de caractères.

 CONCAT(ch1, ch2): concaténation de 2 chaînes.

 INITCAP(ch): la 1ère lettre de chaque mot de ch convertie en majuscule et

le reste en minuscules.

 LOWER(ch): toute la chaîne ch en minuscules.

 UPPER(ch) : toute la chaîne ch en majuscules.

 SUBSTR(ch,m[,n]): retourne la sous-chaîne de long n, extraite de ch

commençant en position m; si la valeur de n n’est pas spécifiée, la
fonction s’exécute jusqu’à la fin de la chaîne.

 LENGTH(ch): retourne la longueur de la chaîne ch.

 etc….

Dr. Mohamed Ali Zoghlami 128

Les fonctions
Les fonctions de manipulation des dates.

 SYSDATE: retourne la date système.

 ADD_MONTHS(d, n): retourne la date d + n mois avec n positif ou négatif.

 MONTHS_BETWEEN(d1, d2): la différence exprimée en nbre de mois.

 NEXT_DAY(d, nom_jour): retourne la date du prochain jour à partir de la

date d.

 LAST_DAY(d) : date du dernier jour du mois relatif à la date d.

 etc….

Dr. Mohamed Ali Zoghlami 129

59
 01/12/2022

Les fonctions
Les fonctions de conversion.

 TO_CHAR(date[, format]): convertit la date en une chaîne selon le format

spécifié. Par défaut le format date est DD-MON-YY.

 TO_CHAR(nombre[, format]): convertit le nombre en une chaîne selon le

format spécifié.
Exp: TO_CHAR(3.1489, ’99,99’)=‘ 3,14’

 TO_DATE(chaîne [,format]) convertit une chaîne en une date selon le

format spécifié.
Exp: TO_DATE(’12/O2/92’,’DD-MON-YYYY’)= 12-FEB-1992

 TO_NUMBER(chaîne) : convertit une chaîne en un nombre

Exp: TO_NUMBER(‘1234’) =1234
 etc….

Dr. Mohamed Ali Zoghlami 130

Les fonctions
Les fonctions diverses.

 NVL(Nombre1,Nombre2):retourne
Nombre1 s’il n’est pas nul ou Nombre2 si
non.

 NVL(chaine1, chaine2): retourne chaine1

si elle n’est pas nulle ou chaine2 si non.

 NVL(date1, date2): retourne date1 si elle

n’est pas nulle ou date2 si non.

Dr. Mohamed Ali Zoghlami 131

60
 01/12/2022

SQL comme LMD

Les opérateurs ensembliste.
 Le LMD SQL permet de réaliser trois opérations sur les
ensembles: l’union, l’intersection et la différence.

 L’union: permet de fusionner les données provenant de

plusieurs requêtes en supprimant les données redondantes.

 Exemple: soient petits_client et grands_client deux tables qui

ont la même structure.

La requête suivante:
Select codclt, nomclt, caclt from petits_client;
UNION
Select codclt, nomclt, caclt from grands_client;

Permet de lister les occurrences des deux tables en supprimant

les données redondantes.

Dr. Mohamed Ali Zoghlami 132

SQL comme LMD

Les opérateurs ensemblistes.
 L’intersection: permet d’obtenir les lignes
appartenant à la fois aux deux requêtes

 Exemple: lister les clients qui figurent à la fois

dans les deux tables petits_client et
grands_client.

Select codclt, nomclt, ville from petits_client;

INTERSECT
Select codclt, nomclt, ville from grands_client;

Dr. Mohamed Ali Zoghlami 133

61
 01/12/2022

SQL comme LMD

Les opérateurs ensemblistes.
 La différence: permet de lister les lignes du
résultat de la 1ère requête qui ne sont pas
comprises dans le résultat de la seconde.

 Exemple: quels sont les articles qui n’ont pas été

commandés.

Select codart
From article
MINUS
Select distinct codart from lignecde;

Dr. Mohamed Ali Zoghlami 134

62