ENSGI Cours MSI 2A Relationnel et SQL

1
Modèle relationnel

• Historique E.F. CODD dans les années 70
• modèle basé sur la théorie des ensembles
• des langages pour manipuler les données (SQL)
• une démarche pour représenter le  monde réel 

Nombreux outils MySQL (logiciel libre)
http//dev.nexen.net/docs/mysql/annotee/manuel_toc
.php Access (Microsoft) Ingres (Computer
Associates) SQL Server (Microsoft) ORACLE
(Oracle corp.)
http//cerig.efpg.inpg.fr/tutoriel/bases-de-donnee
s/
2

• Consulter
• Ajouter
• Modifier
• Supprimer

Information structurée, bases de données
3
Définitions  Domaine 
Domaines Di di1, di2, .. din, Dj d Î
R, Dk Di x Dj Di défini en extension, Dj
et Dk en intention

• Exemples
• D_Num_securite_sociale n Î 1013 3.1013
• D_Nom chaine de 30 caractères
• D_Prenom chaine de 20 caractères
• D_Code_postal chaine de 5 chiffres
• D_Telephone chaine de 10 chiffres
• D_couleur bleu, vert, rouge, jaune
• D_type_stage operateur, Enquete, EDT,
  inge_adjoint, PFE
• D_titre chaine de 200 caractères
• D_date_début date

4
Définitions  Relations 
Une relation (ou table) est un sous ensemble dun
produit cartésien de domaines. Une relation est
définie par un nom. Le degré dune relation est
égal par définition au nombre de facteurs de ce
produit cartésien.

• Exemples relations
• PERSONNE Í D_Num_securite_sociale x D_Nom x
  D_Prenom x D_Code_postal x D_Telephone
• STAGE Í D_Num_securite_sociale x D_type_stage
  x D_Titre x D_date_deb

Personne est de degré 5, Stage est de degré 4
5
Définitions  Constituants 
On appelle constituant (ou colonne ou attribut)
dune relation (ou table) le nom donné à une
colonne de la table. On note R lensemble
des constituants de R.

• Exemples les constituants dune relation
• PERSONNE Num_securite_sociale, Nom , Prenom
  , Code_postal, Telephone
• STAGE Num_securite_sociale , type_stage ,
  Titre, date_deb

Ou plus simplement

• PERSONNE (Num_securite_sociale, Nom , Prenom ,
  Code_postal, Telephone)
• STAGE ( Num_securite_sociale , type_stage ,
  Titre , date_deb )

6
Définitions  n-uplets 
Un n-uplet - ou tuple (en anglais), instance,
entité, ligne - d une relation est un élément de
cette relation

• Exemples 2 relations
• PERSONNE Í D_Num_securite_sociale x D_Nom x
  D_Prenom x D_Code_postal x D_Telephone
• STAGE Í D_Num_securite_sociale x D_type_stage
  x D_Titre x D_date-deb

7
Définitions propriétés, constituants, n-uplets

• Exemples 2 relations
• PERSONNE Í D_Num_securite_sociale x D_Nom x
  D_Prenom x D_Code_postal x D_Telephone
• STAGE Í D_Num_securite_sociale x D_type_stage
  x D_Titre x D_date-deb

8
Définitions clé, contraintes dintégrité
Toute relation ou table doit comporter parmi
lensemble de ses constituants un sous ensemble
qui identifie sans ambiguité chaque n-uplet ce
sous ensemble est appelé  clé  de la
relation. Les constituants de ce sous ensemble
sont présentés soulignés.
Exemples clé dune relation

• PERSONNE (Num_securite_sociale, Nom , Prénom ,
  Code_postal, Téléphone)
• STAGE ( Num_securite_sociale , type_stage ,
  Titre, Date-début )

9
Définitions Schéma de relations

• Le schéma dune relation ou définition en
  intention de cette relation est constitué des
  éléments
• le nom de la relation
• la liste des constituants et les domaines de
  chacun
• les contraintes d intégrité à respecter

Exemples PERSONNE ( Num_securite_sociale
n Î 1013 3.1013 , Nom chaine de 30
caractères, Prenom chaine de 20
caractères, Code_postal chaine de 5
chiffres , Telephone chaine de 10
chiffres ) STAGE ( Num_securite_sociale
n Î 1013 3.1013 , type_stage
operateur, Enquete, EDT, inge_adjoint, PFE,
titre chaine de 200 caractères
date-début date )
10
Définitions Schéma de Base de données
relationnelle
Le schéma dune base de données est constituée
par lensemble des schémas des relations qui la
constituent.
Exemple
CLIENT ( Num_client entier long ,
Nom chaine de 30 caractères, Prenom
chaine de 20 caractères, Code_postal chaine
de 5 chiffres , Telephone chaine de 10
chiffres ) COMMANDE ( Num_commande
entier long , date date, montant
monétaire client entier long ) ARTICLE
( Code_article entier long, Désignation
chaine de 50 caractères, prix monétaire
) LIGNECOMMANDE (code_article entier long
, Num_commande entier long , quantité
entier)
11
Schéma . qui peut aussi se représenter

• CLIENT
• Num_client entier long ,
• Nom chaine de 30 caractères,
• Prenom chaine de 20 caractères,
• Code_postal chaine de 5 chiffres ,
• Telephone chaine de 10 chiffres

• COMMANDE
• Num_commande entier long ,
• date date,
• montant monétaire
• client entier long

• LIGNECOMMANDE
• code_article entier long ,
• Num_commande entier long ,
• quantité entier

• ARTICLE
• Code_article entier long,
• Désignation chaine de 50 caractères,
• prix monétaire

12
Relationnel opérateurs ensemblistes

• Soient R1 et R2 deux relations, on peut définir
  entre R1 et R2 un certain nombre dopérations

R1 et R2 ont même schéma
Opérations entre relations de schéma distinct

• union T (R1 È R2)
• ou T Union (R1, R2)
• différence T (R1 - R2)
• ou T Minus (R1, R2)
• intersection T (R1 Ç R2)
• ou T inter (R1, R2)

• produit cartésien T (R1 R2)
• ou T product (R1, R2)
• jointure naturelle
• T (R1 gtlt R2)

13
Relationnel opérations unaires sur une relation

• Soit R une relation de schéma (A1, A2, A3, ...
  An )

• projection de R sur les attributs Ai1, Ai2,
  Ai3, ... Aip avec p lt n
• est une relation R de schéma (Ai1, Ai2, Ai3, ...
  Aip ) dont les n-uplets sont obtenus par
  élimination des attributs de R nappartenant pas
  à R  et par suppression des doublons.
• On note T P Ai1, Ai2, Ai3, ... Aip (R)
• ou T proj Ai1, Ai2, Ai3, ... Aip (R)
• restriction (ou sélection) de la relation R par
  une qualification Q est une relation R  de même
  schéma que R dont les n-uplets sont ceux de R
  satisfaisant Q.
• On note T sQ (R) ou T selectQ (R)

14
Définitions opérateurs exemples

• Soient R1 et R2 deux relations de même schéma

• union T (R1 È R2)
• ou T Union (R1, R2)
• différence T (R1 - R2)
• ou T Minus (R1, R2)
• intersection T (R1 Ç R2)
• ou T inter (R1, R2)

15
Définitions opérateurs exemples

• Soient R1 et R2 deux relations de schémas
  distincts,

• produit cartésien T (R1 R2)
• ou T product (R1, R2)

16
Définitions opérateurs exemples

• Soient R1 et R2 deux relations de schémas
  distincts,

• jointure naturelle
• T (R1 gtlt R2)

17
Définitions opérateurs exemples

• Soient R1 une relation,

• projection de R1 sur les attributs A et B
• T P A, B, (R1)

Attention à la suppression des doublons

• restriction (ou sélection) de la relation R1 par
  une qualification Q (Clt30).
• T s( C lt 30) (R)

18
Définitions contraintes dans une BD
Lintérêt de lusage de Base de données et que la
BD permet la définition de  contraintes
dintégrité  qui seront nécessairement vérifiées
à tout instant par la base ces contraintes
permettent de garantir la  cohérence  de la
base. Elles sont de trois types
1) contrainte de clé 2) contrainte de type de
données (date, intervalle, liste entier) 3)
contrainte dintégrité référentielle
19
Définitions contrainte dintégrité
référentielle
Permet de vérifier la présence de données
référencées dans des tables différentes. Une
contrainte d intégrité référentielle peut
sutiliser dès quune clé primaire dune table
est utilisée comme référence dans une autre
table. On la nomme  clé étrangère  de la
seconde table.
CLIENT (Num_client entier long , Nom
chaine de 30 caractères, Prenom chaine de 20
caractères, Code_postal chaine de 5 chiffres
, Telephone chaine de 10 chiffres
) COMMANDE (Num_commande entier long , date
date, montant monétaire client entier
long )
ARTICLE (Code_article entier
long, Désignation chaine de 50
caractères, prix monétaire ) LIGNECOMMANDE
(code_article entier long , Num_commande
entier long , quantité entier)
20
Définitions 1ère Forme Normale

• Une relation est en PREMIERE FORME NORMALE ssi
• les valeurs de tous les attributs qui la
  composent sont atomiques
• la relation possède une  clé 

Exemple

• PERSONNE (Num_securite_sociale, Nom , Prenom ,
  Code_postal, Telephone)

On ne peut gérer des listes de prénom ou
plusieurs adresses, dans la table PERSONNE par
contre prénom peut être une chaine de nn
caractères
21
Définitions Dépendance fonctionnelle
Cette notion permet de capturer lidée de
dépendance entre informations en visant à limiter
la redondance.
Soit R une relation et X et Y des groupes
dattributs de R, il existe une dépendance
fonctionnelle entre X et Y (on dit que X
détermine Y) si dans R chaque valeur de X
détermine une et une seule valeur de Y.
Attention, cette propriété doit être vérifiée
pour tous les n-uplets, y compris ceux à créer
22
Définitions 2ème Forme Normale

• Une relation est en DEUXIEME FORME NORMALE ssi
• elle est en première forme normale
• tout attribut n appartenant pas à une clé ne
  dépend pas d une partie de cette clé

Exemple

• PRET (Num_etudiant, NumLivre, Nom_etud ,
  Prenom_etud , NomLivre, Auteurs, date_emprunt)

N est pas en 2ème FN car  Nom_etud  ne dépend
que de Num_etudiant, et de même  NomLivre , et
 Auteurs  ne dépend que de NumLivre
23
Définitions 3ème Forme Normale

• Une relation est en TROISIEME FORME NORMALE (dite
  de Boyce Codd) ssi
• elle est en deuxième forme normale
• tout attribut n appartenant pas à une clé ne
  dépend pas d un autre attribut non clé

Exemple

• EMPLOYE (Num_securite_sociale, Nom , Prenom ,
  Service, Adresse_service)

Adresse_service dépend de service, la relation
nest pas en 3eme FN.
24
SQL
Le SQL (Structured Query Language) permet
d'interroger une base de données, d'en modifier
des informations. C'est un langage universel
d'interrogation des bases de données, qui permet
à différents systèmes d'échanger des données
entre eux. ACCESS peut être interrogé en SQL via
un macro langage qui  cache  le SQL (voir à ce
sujet le Menu  Requêtes ,  Spécifique SQL 
 SQL direct ). Normalisation ISO norme SQL1
(1986, 1989) norme SQL2 (1992) nouvelle norme
en préparation SQL3 Dans la suite, le code SQL
est représenté en ARIAL 12, les commentaires en
Italique.
http//wwwlsi.supelec.fr/www/yb/poly_bd/sql/tdm_sq
l.html
25
SQL comporte 3 parties 1) - Le DML (Data
Manipulation Language) Sélection dinformation,
création et mise à jour denregistrements SELECT,
INSERT, UPDATE, DELETE, JOIN 2) - Le DDL (Data
Definition Language) Création des tables, des
attributs et des contraintes dintégrité CREATE,
ALTER, DROP, RENAME 3) - Le DCL (Data Control
Language) Pour contrôler laccès aux données
GRANT, REVOKE
26
DML (Data Manipulation Language)
Les requêtes simples Soit 3 tables Eleves
(NomElv, AdrElv, VilleElv), Matieres (NomMat,
Coef, Intitule), Notes (NomElv, NomMat, Date,
Note). Attribut souligné clé primaire
27
DML (Data Manipulation Language)
Des enregistrements dans les tables

• Notes
• NomElv
• NomMat
• Date
• Note

• Eleves
• NomElv
• AdrElv
• VilleElv

• Matieres
• NomMat
• Coef
• Intitulé

Matiere
Eleves
Notes
NomMat Coef Intitulé
Gest-prod 3 Gestion de production
Gest-proj 2 Gestion de projets
MSI 3 Management des SI
Qualité 1 Qualité
Sports 2 Sports
Nomelv Adresse Ville
Bastien Montmartre Paris
Clerget-Gurnaud Bastille Paris
Deltour Romain Bastille Grenoble
Denoual St Michel Paris
Le Bas Cointrin Geneve
Miguel Goyena Plaza de la Constitucíon Mexico
Pelayo Menendez Garcia Anáhuac Mexico
Pop Trocadero Paris
Simon-Suisse Jet d'eau Geneve
Thevenot Ile verte Grenoble
Viard Gare Grenoble
Nomelv NomMat Date Valeur
Bastien Gest-prod 25/09/2004 15
Bastien MSI 09/09/2004 16
Bastien MSI 25/09/2004 15
Clerget-Gurnaud Gest-prod 25/09/2004 12
Clerget-Gurnaud MSI 09/09/2004 9
Clerget-Gurnaud MSI 25/09/2004 12
Deltour Romain Gest-prod 25/09/2004 17
Deltour Romain MSI 09/09/2004 17
Deltour Romain MSI 25/09/2004 20
Denoual Gest-prod 25/09/2004 8
Denoual MSI 09/09/2004 20
Denoual MSI 25/09/2004 10
Le Bas Gest-prod 25/09/2004 11
Le Bas MSI 09/09/2004 12
Le Bas MSI 25/09/2004 11
Miguel Goyena Gest-prod 25/09/2004 15
Miguel Goyena MSI 09/09/2004 15
Miguel Goyena MSI 25/09/2004 18
Pelayo Menendez Garcia Gest-prod 25/09/2004 13
Pelayo Menendez Garcia MSI 09/09/2004 14
Pelayo Menendez Garcia MSI 25/09/2004 13
Pop Gest-prod 25/09/2004 17
Pop MSI 09/09/2004 18
Pop MSI 25/09/2004 17
Simon-Suisse Gest-prod 25/09/2004 12
Simon-Suisse MSI 09/09/2004 10
Simon-Suisse MSI 25/09/2004 12
Thevenot Gest-prod 25/09/2004 11
Thevenot MSI 09/09/2004 11
Thevenot MSI 25/09/2004 14
Viard Gest-prod 25/09/2004 13
Viard MSI 09/09/2004 13
Viard MSI 25/09/2004 16
Simon-Suisse Qualité 30/09/2004 10
Le Bas Qualité 30/09/2004 17
Pelayo Menendez Garcia Qualité 30/09/2004 10
Bastien Qualité 30/09/2004 10
Pop Qualité 30/09/2004 17
Denoual Qualité 30/09/2004 8
Clerget-Gurnaud Qualité 30/09/2004 6
Thevenot Qualité 30/09/2004 4
Viard Qualité 30/09/2004 2
Miguel Goyena Qualité 30/09/2004 13
Deltour Romain Qualité 30/09/2004 15
28
DML (Data Manipulation Language)
1.1 L'interrogation simple Liste des
élèves. SELECT NomElv (ce qui doit être affiché
?)FROM Eleves (dans quelle table rechercher
linformation ?) Liste des matières avec leurs
coefficients. SELECT NomMat, CoefFROM
Matieres Toutes les notes classées par
élèves SELECT Notes.Nomelv, Notes.NomMat,
Notes.Date, Notes.Valeur FROM Notes ORDER BY
Notes.Nomelv
Toutes-les-notes/eleves
Nomelv NomMat Date Valeur
Bastien Gest-prod 25/09/2004 15
Bastien MSI 09/09/2004 16
Bastien MSI 25/09/2004 15
Clerget-Gurnaud Gest-prod 25/09/2004 12
Clerget-Gurnaud MSI 09/09/2004 9
Clerget-Gurnaud MSI 25/09/2004 12
Deltour Romain Gest-prod 25/09/2004 17
Deltour Romain MSI 09/09/2004 17
Deltour Romain MSI 25/09/2004 20
Denoual Gest-prod 25/09/2004 8
Denoual MSI 09/09/2004 20
Denoual MSI 25/09/2004 10
29
DML (Data Manipulation Language)
1.1 L'interrogation simple (suite) Toutes les
notes moyenne de matière classées par
élèves SELECT Toutes-les-notes/eleves.Nomelv,
Toutes-les-notes/eleves.NomMat,
Avg(Toutes-les-notes/eleves.Valeur) AS
MoyenneDeValeur FROM Toutes-les-notes/eleves GRO
UP BY Toutes-les-notes/eleves.Nomelv,
Toutes-les-notes/eleves.NomMat
Toutes-les-notes/eleves
Nomelv NomMat Date Valeur
Bastien Gest-prod 25/09/2004 15
Bastien MSI 09/09/2004 16
Bastien MSI 25/09/2004 15
Clerget-Gurnaud Gest-prod 25/09/2004 12
Clerget-Gurnaud MSI 09/09/2004 9
Clerget-Gurnaud MSI 25/09/2004 12
Deltour Romain Gest-prod 25/09/2004 17
Deltour Romain MSI 09/09/2004 17
Deltour Romain MSI 25/09/2004 20
Denoual Gest-prod 25/09/2004 8
Denoual MSI 09/09/2004 20
Denoual MSI 25/09/2004 10
Toutes-les-moyennes-mat/élèves
Nomelv NomMat MoyenneDeValeur
Bastien Gest-prod 15
Bastien MSI 15,5
Clerget-Gurnaud Gest-prod 12
Clerget-Gurnaud MSI 10,5
Deltour Romain Gest-prod 17
Deltour Romain MSI 18,5
On compose les requêtes
30
DML (Data Manipulation Language)
Moyenne par matière
SELECT Toutes-les-moyennes-mat/élèves.NomMat,
Avg(Toutes-les-moyennes-mat/élèves.MoyenneDeVale
ur) AS MoyenneDeMoyenneDeValeur FROM
Toutes-les-moyennes-mat/élèves GROUP BY
Toutes-les-moyennes-mat/élèves.NomMat
Moyennes par matière
NomMat Moyenne de la division
Gest-prod 13,09
MSI 14,22
Qualité 10,18
31
DML (Data Manipulation Language)

• 1.2 La close WHERE
• Elle permet de spécifier la ou les conditions que
  doivent remplir les lignes choisies.
• Liste des élèves habitant Mexico.
• SELECT NomElvFROM ElevesWHERE VilleElv
  Mexico'
• Liste des matières pour lesquelles l'élève
  "Simon-Suisse" a eu au moins une note.
• SELECT NomMatFROM NotesWHERE NomElv
  'Simon-Suisse'
• Remarque Dans la close WHERE, on ne peut
  utiliser que des propriétés qui sont dans la
  table sélectionnée

32
DML (Data Manipulation Language)
Les jointures

• Notes
• NomElv
• NomMat
• Date
• Note

• Matieres
• NomMat
• Coef
• Intitulé

• Toutes-les-moyennes-mat/élèves
• NomElv
• NomMat
• Moyenne

Tous les totaux/matiere
SELECT Toutes-les-moyennes-mat/élèves.Nomelv,
Matiere!CoefToutes-les-moyennes-mat/élèves!Moye
nneDeValeur AS Expr1, Matiere.Coef,
Matiere.Intitulé FROM Toutes-les-moyennes-mat/élè
ves INNER JOIN Matiere ON Toutes-les-moyennes-ma
t/élèves.NomMat Matiere.NomMat
Nomelv Total ds la matière Coef Intitulé
Bastien 45 3 Gestion de production
Bastien 46,5 3 Management des SI
Bastien 10 1 Qualité
Clerget-Gurnaud 36 3 Gestion de production
Clerget-Gurnaud 31,5 3 Management des SI
Clerget-Gurnaud 6 1 Qualité
Deltour Romain 51 3 Gestion de production
Deltour Romain 55,5 3 Management des SI
Deltour Romain 15 1 Qualité
Denoual 24 3 Gestion de production
Denoual 45 3 Management des SI
Denoual 8 1 Qualité
33
DML (Data Manipulation Language)
La somme des coefficients

• Matieres
• NomMat
• Coef
• Intitulé

SELECT Sum(Matiere.Coef) AS SommeDeCoef FROM
Matiere
SommeDeCoef
11
34
DML (Data Manipulation Language)
Les totaux puis les moyennes par élève,
SELECT Tous les totaux/matiere.Nomelv,
Sum(Tous les totaux/matiere.Expr1) AS
SommeDeExpr1 FROM Tous les totaux/matiere GROUP
BY Tous les totaux/matiere.Nomelv
Nom eleve Moyenne semestrielle
Deltour Romain 11,05
Pop 10,95
Miguel Goyena 9,77
Bastien 9,23
Pelayo Menendez Garcia 8,14
Viard 7,68
Le Bas 7,68
Simon-Suisse 7,18
Denoual 7,00
Thevenot 6,77
Clerget-Gurnaud 6,68
SELECT Total/eleve.Nomelv, Total/eleve!SommeDe
Expr1/Somme-coef!SommeDeCoef AS Expr1 FROM
Total/eleve, Somme-coef ORDER BY
Total/eleve!SommeDeExpr1/Somme-coef!SommeDeCoe
f DESC
35
DML (Data Manipulation Language)
Toutes-les-moyennes-mat/élèves_Analyse croisée
Nomelv Gest-prod MSI Qualité
Bastien 15 15,5 10
Clerget-Gurnaud 12 10,5 6
Deltour Romain 17 18,5 15
Denoual 8 15 8
Le Bas 11 11,5 17
Miguel Goyena 15 16,5 13
Pelayo Menendez Garcia 13 13,5 10
Pop 17 17,5 17
Simon-Suisse 12 11 10
Thevenot 11 12,5 4
Viard 13 14,5 2
36
DML (Data Manipulation Language)
dans MS Access
37
DML (Data Manipulation Language)
dans MS Access
SELECT CLIENT.Telephone, COMMANDE.Montant FROM
CLIENT INNER JOIN COMMANDE ON
CLIENT.Num_client COMMANDE.Num_client WHERE
(((COMMANDE.Montant)gt10000))
38
Les jointures
SELECT personne.Nom, personne.Prenom,
stage.Titre, stage.Date-debut,
stage.D_type_stage FROM personne INNER JOIN
stage ON personne.Numéro stage.Numéro
39
Les jointures
SELECT personne.Nom, personne.Prenom,
stage.Titre, stage.Date-debut,
stage.D_type_stage FROM personne INNER JOIN
stage ON personne.Numéro stage.Numéro
!! La jointure a été faite sur lattribut
 numéro  !! personne.Numéro stage.Numéro
40
Les jointures
SELECT personne.Nom, personne.Prenom,
stage.Titre, stage.Date-debut,
stage.D_type_stage FROM personne INNER JOIN
stage ON personne.Num_securite_sociale
stage.Num_securite_sociale
41
Les jointures
42
Les jointures
43
Les jointures
SELECT personne.Nom, personne.Prenom,
stage.D_type_stage, stage.Titre,
stage.Date-debut FROM personne LEFT JOIN stage
ON personne.Num_securite_sociale
stage.Num_securite_sociale
44
DML (Data Manipulation Language)
45
DML (Data Manipulation Language)
46
DML (Data Manipulation Language)
47
DDL (Data Definition Language)
2 Création des tables, des attributs et des
contraintes dintégrité Le DDL (Data Definition
Language) CREATE, ALTER, DROP, RENAME
48
DCL (Data Control Language)
3 Restreindre des droits dacces GRANT REVOKE