Base de données relationnelle
Niveau : S4 Gestion
Enseignante : Alami Laila
1
Limite d’utilisation des fichiers
Organisation
Manipulation : Ecriture de programmes
Redondances
Accesibilité
Fiabilité
…
2
Besoin des utilisateurs
Programmes flexibles
Données accessibles à tout non spécialiste
Systèmes d'informations globaux
Cohérence
Réponses immédiates .
3
Base de données - Définition
Ensemble structuré et cohérent de données,
stocké sur disque, accessible par un ou
plusieurs utilisateurs de façon sélective et en
un temps opportun
sur lesquelles on peut développer différentes
applications.
4
Une BD est faite pour
enregistrer les données,
les restituer à la demande,
tirer des conclusions en rapprochant les faits
les uns des autres.
5
SGBD
Un logiciel qui permet de
décrire,
modifier,
interroger,
administrer,
les données d'une base de données.
6
SGBD
Logiciel qui prend en charge
la structuration,
le stockage,
la mise à jour
et la maintenance des données ;
c'est, en fait, l'interface entre la BD et les
utilisateurs ou leurs programmes.
7
Cycle d’abstraction
Le niveau conceptuel : Conception de la BD
(MCD)
Le niveau logique : Modèle relationnel
(MLD)
Le niveau opérationnel : BD créée sur
disque sous Access2007 (MPD)
8
Le modèle relationnel
Formalisé par Codd en 1970
Son succès est dû à la puissance et à la
simplicité de ses concepts.
Le standard ANS/ISO : SQL
Version 2, 1992;
Version 3 : types récursifs + héritage
9
Objectifs du modèle R
Schémas de données faciles à utiliser,
Indépendance logique et physique,
Langage de haut niveau
Optimiser les accès à la base de données,
Améliorer l'intégrité et la confidentialité,
Approche méthodologique dans la
construction des schémas.
10
Relation ou Table
Relation Logique
Voiture (nomPropriétaire, marque, numéro)
Table physique
Voiture nomPropriétaire marque numéro
Bennani Renault 15402A15
Chraibi BMW 98410A17
Idrissi Peugeot 12101A15
Loulidi Renault 154210A15
11
Table – ligne/colonne
Attribut
12
Attribut
A chaque attribut correspond
Un nom (lettre, chiffre, trait d’union)
à un domaine (Type : entier, réel, chaine…)
Atomique : non décomposable
Brut : non calculé
Peut être clé – unique
Peut être null
Ordre des attributs quelconque
13
Valeur Null
Null = non renseigné
Valeur inexistante
Valeur non encore connue
Valeur impossible
14
Tuple
Ligne
Enregistrement ou article
Possède tous les attributs
Identifié par une clé
Ordre quelconque
15
Clé d’une relation : Primary key
Soit R (A1, A2, ..., An) un schéma de relation,
X un sous-ensemble de (A1, A2, ..., An),
X (groupe d’attributs) est une clé si et seulement si:
X est unique
Valeur obligatoire
X détermine tous les attributs (A1, A2, ..., An)
X est minimal
La clé dans un schéma de relation est soulignée
Exemple :
voiture ( NIMM, marque, modèle, prix, couleur)
emploiTemp(Jour, heure, nomEnseignant, codeMatière, Salle)
16
Clé étrangère : foreign Key
Personne (Cin , nom , prenom , tel)
Projet ( num , intitulé , date , Cin)
Clé étrangère
17
Exemple d’un schéma relationnel
Client (noclient , nom , téléphone)
Commande (nocom , date , noclient)
Produit (Pref , désignation , prix)
Ligne-cde (nocom , Pref , quantité)
Fournisseur(nofrs , nom)
Fournir (nofrs, Pref )
18
Contraintes d’intégrité
liées au modèle (pas de doublons dans une relation.)
de domaine
pas de doublon ni de valeur nulle pour la clé primaire
référentielles dites de clé étrangère qui impose que la valeur
d'attribut de la relation r1 apparaît comme valeur de clé dans
une autre relation r2.
19
Le langage : SQL
Structured Query Language
Langage de requête structuré
DDL : Data Définition Language
Création de database, de tables, index/clé, …
DML : Data Manipulation Language
Recherche
MAJ ( Insertion, suppression et modification)
20
DML : Langage da manipulation de données
Recherche : Select
Mise à jour
Insertion : Insert
Suppression : Delete
Modification : Update
21
Recherche SQL
Format général :
Select [All / Distinct] { <expression de valeurs> / * }
From <listeRelation >
[ where <conditions>]
[order by <listeAttributs> [asc/desc]>];
Une expression de valeurs peut être la liste des attributs ou des expressions
arithmétiques séparées par « , » .
* Remplace tous les attributs des relations mentionnées.
Une condition peut être simple ou composée (reliées par les opérateurs logiques
And, Or et Not)
22
Order by : pour un affichage trié selon les attributs et l’ordre mentionné
Exemple – Requête simple
Question : Donnez la liste des voitures Résultat :
Requête SQL :
Select *
From voiture;
23
Exemple – requête simple
Question : Donnez le modèle et le prix des voitures de marque « Renault »
Requête SQL : Résultat :
Select modèle , prix
From voiture
where marque = ‘’Renault’’;
24
Exemple – critère multiple
Question : Donnez le modèle, le prix et le prix TTC des voitures de marque « Renault »
de prix HT inférieur ou égale à 25 millions
Résultat :
Requête SQL :
Select modèle , prix, prix * 1.2 as TTC
From voiture
where (marque = ‘’Renault’’)
and (prix <= 25);
25
NB : Utilisez les parenthèses dans les conditions utilisant des opérateurs logiques.
Exemple – clause distinct
Question : Donnez les marques des voitures Requête2 SQL :
Select distinct ( marque )
Résultat:
From voiture;
Requête1 SQL :
Résultat:
Select marque
From voiture;
26
Exemple – clause distinct
Question : Donnez les différentes marques et modèles des voitures
Résultat:
Requête SQL :
Select distinct (marque) , modèle
From voiture;
Remarque : Distinct s’applique sur le tuple entier de la table voiture
27
Exemple – ordre des tuples affichés
Question : Donnez les NIMM , marque et modèle des voitures dans l’ordre de la
date d’achat
Résultat:
Requête SQL :
Select NIMM, marque , modèle , achat
From voiture
Order by achat;
28
Exemple – ordre des tuples affichés
Question : Donnez les marque, modèle , couleur et prix des voitures par ordre
croissant de marque et décroissant de prix
Résultat:
Requête SQL :
Select marque , modèle , couleur, prix
From voiture
Order by marque , prix desc;
29
Remarque : le prix est classé pour chaque
marque.
Fonctions agrégats
Fonctions de groupe ou d’évaluation d’ensemble
Count(n/*) : Donne le nombre de tuples
Max(n) : Donne la plus grande valeur de n
Min(n) : Donne la plus petite valeur de n
Sum(n) : Donne la somme des valeurs de n
AVG(n) : Donne la moyenne des valeurs de n
Attention : une fonction agrégat est utilisée dans un
select (Jamais dans un Where)
30
Exemple – fonction de groupe
Question : Donnez le nombre de voitures de marque « Peugeot »
Résultat:
Requête SQL :
Select count(*) as NombrePeugeot
From voiture
Where marque = "Peugeot";
31
Exemple – fonction de groupe
Question : Donnez le prix minimale et le prix maximale des voitures
Résultat:
Requête SQL :
Select min(prix) as Pmin, max(prix)as Pmax
From voiture;
32
Les sous relations : regroupement
Format : Group by <liste_attributs>
[Having <condition>] ;
Group by : partitionne la relation en sous relations tel que
chacune ait pour l'attribut indiqué par group by la même
valeur.
Having : a la même fonction que where, ne garde que les sous
relations qui satisfont la condition.
33
Group by – comment ça marche?
Group by marque sur la relation voiture :
34
Exemple – Group by
Question : Donnez le nombre de voitures de chaque marque
Résultat:
Requête SQL :
Select marque, count(*)as Nombre
From voiture
Group by marque;
35
Exemple – Group by + having
Question : Donnez le nombre de voitures de chaque marque ayant un prix
minimale inférieur à 30 millions
Résultat:
Requête SQL :
Select marque, count(*)as Nombre
From voiture
Group by marque
Having min(prix) < 30;
36
A noter
Un attribut mentionné dans group
Apparaît forcément dans select.
Select X , …
From …
…
Group by X
….
Une fonction agrégat ne peut pas être utilisée
dans la clause where, elle peut être utilisée
dans Having (après le group by)
37
Les requêtes multi-relations
Forme procédurale (FP)
requêtes imbriquées (opérateur in)
N requête , 1 table dans chaque From
Forme relationnelle (FR)
1 requête , plusieurs tables dans 1 From
Possibilité d’utiliser des variables de
désignation
38
Exemple1 – Jointures
Question : Donnez pour chaque matière: le code, le titre, le niveau et les volumes horaire
(cours , td et/ou TP)
Pour afficher les attributs de la table matières et ceux de la table VolumeH, il faut préciser
dans la requête SQL le lien entre les deux tables (Mcode) : La jointure.
Cad? matiè[Link] = [Link]
39
Exemple1 – Jointures - FR
Requête SQL (FR):
Select *
From matières , volumeH
Where (matiè[Link] = [Link]); Résultat:
40
Exemple1 – Jointures –FR
Variable de désignation
Requête SQL (FR):
M et V sont des variables de
Select * désignation appelées aussi variables
tuples.
From matières M , volumeH V M designe la relation matières
V désigne la relation volumeH
Where ( M. Mcode = [Link] ) ;
41
Exemple1 – Jointures –FP
Requête SQL (FP):
Dans la forme procédurale, chaque from utilise
Select * une seule table, la jointure est présentée sous
From matières forme d’une imbrication de requêtes.
Where Mcode in (Select Mcode * représente dans cette forme les attributs de la
Fom VolumeH); table matières seulement, il n’est pas possible
d’afficher les attributs type et Nbh de la table
VolumeH.
42
Exemple2 : Jointures
Soit le schéma relationnel suivant :
Voiture( NIMM , marque, modèle , prix ,couleur, achat)
Client(CIN , nom , ville , téléphone)
Appartenir ( CIN , NIMM)
43
Exemple : Forme Relationnelle
Question : Nom, cin et téléphone des clients ayant des
voitures de modèle 308 et de marque Peugeot.
Requête SQL
Select nom , [Link] , téléphone
From client C , Appartenir A , Voiture V
Where ([Link] = [Link]) and ([Link] = [Link]) and
(modele=“308”) and (marque=“Peugeot” );
Remarque : il est obligatoire de préciser la table ou la variable de
désignation pour chaque attribut appartenant à plusieurs table dans la
44
requête.
Exemle : [Link] : indique l’attribut cin de la table client.
Exemple : Forme Procédurale
Question : Nom, cin et téléphone des clients ayant des
voitures de modèle 308 et de marque Peugeot.
Requête SQL
Select nom , cin , téléphone
From client
Where cin in ( select cin
From Appartenir
Where NIMM in (select NIMM
From voiture
Where (modele=“308”) and (marque=“Peugeot” )));
Remarques:
Ici, la variable de désignation n’est pas nécessaire.
Les seuls attributs à afficher sont ceux appartenant à la relation qui existe dans le premier
From de cette forme procédurale. 45
Requête - jointure
Une requête de jointure est une requête qui
utilise N tables (avec N > 1)
N tables dans une requête suppose au moins
N-1 jointures.
46
Exemple
Clients (Nclient, Nom, Prénom, Adresse, Ville, Tel)
Ventes (Nvente, DateVente, Nrepresentant, Nclient,
Paiement)
LignesVentes(Nvente, Nproduit, NbreUnités)
Produits (Nproduit, designation, PrixUnitaire, Catégorie,
Disponible)
Représentant (Nrepresentant, Nom, Prénom,
DateNaissance, DateEmbauche, Adresse, ville, Tel,
SalaireBrut)
47
Attributs Type
Nclient, Nom, Prénom, Adresse, Ville, Tel, Paiement, Texte
designation, Catégorie
Nvente, Nproduit, NbreUnités, Nrepresentant, Numérique
PrixUnitaire, SalaireBrut
Logique
Disponible
Date (format jj/mm/aaaa)
DateVente, DateNaissance, DateEmbauche
48
Donnez une requête SQL pour répondre aux questions
suivantes:
1. Affichez la liste des produits disponibles de Prix unitaire supérieur à 500
DH. La table résultat de votre requête doit avoir les entêtes de colonnes
suivantes : Nproduit, designation, PrixUnitaire et Catégorie.
2. Affichez les représentants ayant un salaire brut supérieur à 3000 DH par
ordre décroissant de salaire et croissant de nom. La table résultat de votre
requête doit avoir les entêtes de colonnes suivantes : Nom, Prénom et
SalaireBrut.
3. Affichez la liste des ventes d’un mode de paiement donné (celui-ci sera
saisi par le clavier lors de l’exécution de la requête). La table résultat de
votre requête doit avoir les entêtes de colonnes suivantes : Nvente et
DateVente.
49
Réponses
1. Select Nproduit, designation, PrixUnitaire, catégorie
From Produits
Where (disponible = true) and (prixUnitaire > 500) ;
2. Select Nom, prénom, salaireBrut
From Représentant
Where salaireBrut > 3000
Order by salaireBrut desc , nom ;
3. Select Nvente, DateVente
From ventes
Where paiement = [Mode de paiement : ] ;
50
Donnez une requête SQL pour répondre aux questions
suivantes:
4. Affichez le nombre de produits disponibles de catégorie « Ordinateur ».
5. Affichez le nombre de représentant de chaque ville.
6. Affichez la liste des représentants ayant moins de 24 ans à la date de leur
embauche. La table résultat de votre requête doit avoir les entêtes de
colonnes suivantes : Nreprésentant, Nom, Prénom, dateNaissance,
DateEmbauche et SalaireBrut.
7. Affichez le numéro des représentant ayant effectué plus de 10 ventes par
mois. . La table résultat de votre requête doit avoir les entêtes de colonnes
suivantes : Nreprésentant, mm, aa et NbVente.
51
Réponses
4. Select count(*)
From Produits
Where (disponible = true) and (catégorie = "Ordinateur") ;
5. Select ville , count(*)
From Représentant
Group by ville ;
6. Select Nreprésentant, Nom, Prénom, dateNaissance, DateEmbauche,
SalaireBrut
From Représentant
Where year(DateEmbauche) – year(dateNaissance) < 24 ;
52
Réponse
7. Select Nreprésentant, month(dateVente) as mm , year(dateVente) as aa ,
count(*) as NbVente
From ventes
Group by Nreprésentant, month(dateVente) , year(dateVente)
Having count (*) > 10 ;
53
Donnez une requête SQL pour répondre aux questions
suivantes:
8. Affichez la vente pour laquelle le représentant a omis de mentionner le
mode de paiement. La table résultat de votre requête doit avoir les entêtes
de colonnes suivantes : Nvente, DateVente, Nreprésentant et
nomReprésentant.
9. Affichez la liste des ventes réalisées aujourd’hui. La table résultat de
votre requête doit avoir les entêtes de colonnes suivantes : Nvente,
Nproduit, désignation, NbreUnités et NomClient.
54
Réponses
8. Select Nvente, DateVente, [Link]ésentant, nom as nomReprésentant
From Ventes V , Représentant R
Where (Paiement = NULL) and ([Link]ésentant = [Link]ésentant) ;
9. Select [Link], [Link], désignation, NbreUnités , nom as NomClient
From LignesVentes L , Produits P, Ventes V , Clients C
Where ([Link] = [Link]) and ([Link] = [Link])
and ([Link] = [Link]) and (dateVente = date()) ;
55
L’opérateur ANY
X opc ANY (requête)
retourne vrai si y E tel que x opc y,
retourne faux sinon
Question : Donnez le NIMM d’une Mercedes dont le prix est
inférieur à une Peugeot.
Requête SQL :
Select NIMM From voiture
Where (marque = " Mercedes")
and (prix <= Any (Select prix From voiture
where marque = "Peugeot");
56
L’opérateur ALL
X opc ALL (requête)
retourne vrai si y E tel que x opc y,
retourne faux sinon
Question : Donnez le NIMM d’une Peugeot dont le prix est
supérieur à toutes les Mercedes.
Requête SQL :
Select NIMM From voiture
Where (marque = "Peugeot")
and (prix > ALL (Select prix From voiture
where marque = "Mercedes");
57
