Transcript relation - Grenet.fr

Management des Systèmes
d’Information (MSI)
Cours : chapitre 1
SQL - Relationnel
Michel TOLLENAERE
1
Cours MSI 2A ICL session automne 2014
Cours de Management des
Systèmes d’Information -- GI2A4GML0328
Objectifs :
- Modéliser, concevoir un SI (UML) : identifier les besoins, réaliser,
déployer, récupérer données et +.
- Programmer dans MS-ACCESS (relationnel – SQL)
- Préparer les enseignements ERP et PLM de 3ème année et option IAE
Programmation
- 16 sept .. 7 novembre : cours et TD (SQL , UML, études de cas)
- Novembre : 6h de formation MS ACCESS 2010
- en décembre : TP et projet (12h encadrées + 20h travail perso)
Évaluation :
- Projet / TP
- Examen (QCM en janvier)
Michel TOLLENAERE
2
Cours MSI 2A ICL session automne 2014
Ils vous ont précédé
Michel TOLLENAERE
3
Cours MSI 2A ICL session automne 2014
Chap 1 : modèle relationnel - SQL
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) version 5.6
ORACLE (Oracle corp.) version 12.1 + Business suite
Access (Microsoft) versions 1997 – 2003 – 2007 – 2010 - 2013
SQL Server (Microsoft) version 2014
Ingres (Computer Associates)
Michel TOLLENAERE
4
Cours MSI 2A ICL session automne 2014
Un point d’histoire
Historique : E.F. CODD
dans les années 70
1923 – 2003
Turing award 1981
Michel TOLLENAERE
5
Cours MSI 2A ICL session automne 2014
Les “technologies” en 2014
Michel TOLLENAERE
6
Cours MSI 2A ICL session automne 2014
Un point d’histoire du business
Nombreux outils :
MySQL (logiciel
libre)
ORACLE (Oracle
corp.)
Access (Microsoft)
SQL Server
(Microsoft)
SAP …
Michel TOLLENAERE
7
Cours MSI 2A ICL session automne 2014
Qu’est ce qu’un SI ?
Constituant
PERSONNE
n-uplet1
n-uplet2
n-uplet3
n-uplet4
Num_securite_sociale
1 76 02 99 167 098
2 76 04 95 165 008
1 78 12 38 122 4332
1 68 02 99 5649 876
STAGE
n-uplet1
n-uplet2
n-uplet3
n-uplet4
n-uplet5
Num_securite_sociale
1 76 02 99 167 098
2 76 04 95 165 008
1 68 02 99 5649 876
2 76 04 95 165 008
1 76 02 99 167 098
Constituant
Nom
Dupont
Durand
Faure
Dumontier
propriétés
Constituant
Prenom
Marcel
Elisabeth
Bertrand
Michel
Constituant
Organisations
Code_postal Telephone
41500 06 08 78 65 88
31900 02 99 167 098
38700 04 38 56 45 32
75016 01 55 45 34 87
propriétés
D_type_stageTitre
Inge_Adjoint Définition d'une politique Qualité
Inge_Adjoint Mise en place d'un SI pour la maintenance
EDT
Reconfiguration des achats
EDT
Reconfiguration des achats
PFE
Mise en place d'un ERP
Task 2
Task 3
S.I.
Task 1
Tâches, processus
Information structurée,
bases de données
Quels systèmes ?
Michel TOLLENAERE
8
Cours MSI 2A ICL session automne 2014
Constituant
PERSONNE
n-uplet1
n-uplet2
n-uplet3
n-uplet4
Num_securite_sociale
1 76 02 99 167 098
2 76 04 95 165 008
1 78 12 38 122 4332
1 68 02 99 5649 876
STAGE
n-uplet1
n-uplet2
n-uplet3
n-uplet4
n-uplet5
Num_securite_sociale
1 76 02 99 167 098
2 76 04 95 165 008
1 68 02 99 5649 876
2 76 04 95 165 008
1 76 02 99 167 098
Constituant
Nom
Dupont
Durand
Faure
Dumontier
propriétés
Constituant
Prenom
Marcel
Elisabeth
Bertrand
Michel
• Consulter
• Ajouter
• Modifier
• Supprimer
Constituant
Code_postal Telephone
41500 06 08 78 65 88
31900 02 99 167 098
38700 04 38 56 45 32
75016 01 55 45 34 87
propriétés
D_type_stageTitre
Inge_Adjoint Définition d'une politique Qualité
Inge_Adjoint Mise en place d'un SI pour la maintenance
EDT
Reconfiguration des achats
EDT
Reconfiguration des achats
PFE
Mise en place d'un ERP
Information structurée,
bases de données
Quels systèmes ?
Michel TOLLENAERE
9
Cours MSI 2A ICL session automne 2014
Notion de modèle
• Qu’est ce qu’un modèle ? (Minsky 1968)
http://web.media.mit.edu/~minsky/papers/MatterMindModels.txt
A* est un modèle de A pour un observateur O
ssi A* aide O à répondre aux questions qu’il se pose
sur A.
Observateur
Modèle
Où sont construites les ailes ?
Michel TOLLENAERE
10
Système observé
Cours MSI 2A ICL session automne 2014
Concepts de systémique (Lemoigne 1977)
Système
Système de pilotage
(ou de décision)
Décisions
Informations
externes
Informations
traitées
Système d ’informations
Ordres,
consignes
Flux entrants
Michel TOLLENAERE
Informations
vers l’extérieur
Informations
collectées
Système opérant
11
Flux sortants
Cours MSI 2A ICL session automne 2014
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}
Espace discret (date)
• D_titre = { chaine de 200 caractères}
ou réel (temps),
• D_date_début = {date}
non Fini !
Michel TOLLENAERE
12
Cours MSI 2A ICL session automne 2014
Codage des caractères
ASCII 7 bits
(128 caractères)
ASCII étendu IBM
(256 caractères)
ASCII étendu Apple
(256 caractères)
Unicode (UTF-8,
UTF-16, UTF-32)
Michel TOLLENAERE
13
Cours MSI 2A ICL session automne 2014
Codage des nombres (et téléphone)
1 500,23 €
1500.28 $
06-88-40-21-15
+33-6-88-40-21-15
Michel TOLLENAERE
la perte de la sonde martienne Mars Climate Orbiter le 23 septembre
1999 a mis en évidence un manque de formalisation dans les
processus de transfert de données entre des partenaires du projet.
« Les ingénieurs de Lockheed Martin Astronautics (Denver dans le Colorado), la firme
qui a conçu et fabriqué la sonde martienne, avaient apparemment gardé la mauvaise
habitude de travailler avec les unités du système Anglo-Saxons. De leur côté, les
ingénieurs du Jet Propulsion Laboratory (Pasadena en Californie) travaillaient depuis
des années dans le système métrique, reconnu au niveau international comme étant
le système de référence. Il semble que lors du transfert des données entre le centre
de Lockheed et celui du JPL, personne ne se soit rendu compte qu'il fallait convertir
les données ! » (Mars 1999)
14
Cours MSI 2A ICL session automne 2014
Codage des dates (et heures)
12-10-2014
10-12-2014
14-12-10
Date debut
date fin
Durée (j)
Michel TOLLENAERE
15
01/03/2014
01/01/2015
306
01/03/1492 01/03/2493
01/01/2015 02/01/2015
#VALEUR!
-174645
Cours MSI 2A ICL session automne 2014
Code barrre – RFID – QR code
QR code Version 3,
29×29, 35-77 caractères.
RFID « Radio Frequency IDentification »,
• Les étiquettes passives. Ces étiquettes à moindre coût (qques
0,05€) sont plus petites et possèdent une durée de vie quasi-illimitée.
Exemples d’applications opérationnelles :
• Traçabilité des bouteilles de gaz (Air Liquide, AGA),
• Suivi des colis (Wal Mart),
• Suivi des vêtements de travail loués (Elis),
• Identification des animaux : remplace le tatouage (Ordicam),
• Gestion des livres d’une bibliothèque,
• Identification des bogies et wagons (SNCF).
Michel TOLLENAERE
16
Cours MSI 2A ICL session automne 2014
Donnée, information
• Donnée = signe + code
– exemple : code ASCII (7 bits) ou étendu
Immatriculation :
12-10-2012
588 DGA 38 un véhicule immatriculé dans
10-12-2012
l’Isère avant avril 2009
588 DGA 38
1 56 05 99 131 088
06-88-40-21-15
+33-6-88-40-21-15
N° SS : un homme
1560599131088
de 58 ans
156 05 99 131 088
né à l’étranger
1 500,23
• Information = donnée + modèle d’interprétation
1500.28
Michel TOLLENAERE
17
Cours MSI 2A ICL session automne 2014
Définitions : « Relations »
Une relation (ou table) est un sous ensemble d’un produit
cartésien de domaines. Une relation est définie par un
nom.
Le degré d’une 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
Michel TOLLENAERE
18
Cours MSI 2A ICL session automne 2014
Définitions : « Constituants »
On appelle constituant (ou colonne ou attribut) d’une
relation (ou table) le nom donné à une colonne de la
table. On note R+ l’ensemble des constituants de R.
Exemples : les constituants d’une 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 )
Michel TOLLENAERE
19
Cours MSI 2A ICL session automne 2014
Définitions : « n-uplets »
Un n-uplet - ou tuple (en anglais), instance,
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
n-uplet1
n-uplet2
n-uplet3
n-uplet4
1 76 02 99 167 098
2 76 04 95 165 008
1 78 12 38 122 4332
1 68 02 99 5649 876
Dupont
Durand
Faure
Dumontier
STAGE
n-uplet1
n-uplet2
n-uplet3
n-uplet4
n-uplet5
Num_securite_sociale
1 76 02 99 167 098
2 76 04 95 165 008
1 68 02 99 5649 876
2 76 04 95 165 008
1 76 02 99 167 098
D_type_stageTitre
Date-debut
Inge_Adjoint Définition d'une politique Qualité
10/01/2014
Inge_Adjoint Mise en place d'un SI pour la maintenance 11/01/2014
EDT
Reconfiguration des achats
21/02/2013
EDT
Reconfiguration des achats
21/02/2013
PFE
Mise en place d'un ERP
21/02/2013
Michel TOLLENAERE
Marcel
Elisabeth
Bertrand
Michel
20
41500
31900
38700
75016
06 08 78 65 88
02 99 167 098
04 38 56 45 32
01 55 45 34 87
Cours MSI 2A ICL session automne 2014
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
Constituant
Num_securite_sociale
1 76 02 99 167 098
2 76 04 95 165 008
1 68 02 99 5649 876
2 76 04 95 165 008
1 76 02 99 167 098
propriétés
Michel TOLLENAERE
Constituant
D_type_stage
Inge_Adjoint
Inge_Adjoint
EDT
EDT
PFE
Constituant
Titre
Définition d'une politique Qualité
Mise en place d'un SI pour la maintenance
Reconfiguration des achats
Reconfiguration des achats
Mise en place d'un ERP
Constituant
Date-debut
10/01/2014
11/01/2014
21/02/2013
21/02/2013
21/02/2013
propriétés
21
Cours MSI 2A ICL session automne 2014
Définitions : clé, contraintes d’intégrité
Toute relation ou table doit comporter parmi
l’ensemble 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é d’une relation
• PERSONNE (Num_securite_sociale, Nom , Prénom , Code_postal, Téléphone)
• STAGE ( Num_securite_sociale , type_stage , Titre, Date-début )
Michel TOLLENAERE
22
Cours MSI 2A ICL session automne 2014
Définitions : Schéma de relations
Le schéma d’une 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} )
Michel TOLLENAERE
23
Cours MSI 2A ICL session automne 2014
Relationnel : opérateurs ensemblistes
• Soient R1 et R2 deux relations, on peut définir entre R1 et
R2 un certain nombre d’opé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)
Michel TOLLENAERE
• produit cartésien T = (R1 *R2)
ou T = product (R1, R2)
• jointure naturelle
T = (R1 >< R2)
24
Cours MSI 2A ICL session automne 2014
Définitions : opérateurs exemples
• Soient R1 et R2 deux relations de même schéma
R1
A
a
c
x
B
c
d
y
C
R2
10
15
20
A
f
c
m
• union T = (R1 R2)
ou T = Union (R1, R2)
C
10
15
12
T
A
B
a
c
x
f
m
• différence T = (R1 - R2)
ou T = Minus (R1, R2)
B
a
x
C
c
y
A
c
25
10
15
20
10
12
A
T
C
c
d
y
g
p
T
• intersection T = (R1 R2)
ou T = inter (R1, R2)
Michel TOLLENAERE
B
g
d
p
10
20
B
d
C
15
Cours MSI 2A ICL session automne 2014
Définitions : opérateur jointure
• Soient R1 et R2 deux relations de schémas distincts,
R1
A
a
c
x
B
c
d
y
C
R2
M
10
15
20
A
a
c
• jointure naturelle
T = (R1 >< R2)
T = R1 join (c=c) R2
26
P
10
15
8
T
Michel TOLLENAERE
C
far
car
min
B
c
d
150
1000
1200
C
M
10 far
15 car
P
150
1000
Cours MSI 2A ICL session automne 2014
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 < n
est une relation R’ de schéma (Ai1, Ai2, Ai3, ... Aip ) dont les nuplets sont obtenus par élimination des attributs de R
n’appartenant 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)
Michel TOLLENAERE
27
Cours MSI 2A ICL session automne 2014
Déf. : opérateurs projection, restriction
• Soient R1 une relation,
R1
A
a
c
x
c
x
B
C
c
d
y
d
y
• projection de R1 sur les attributs A et B
T = PA, B, (R1)
10
15
20
35
60
T
A
B
a
c
x
c
d
y
Attention à la suppression des doublons
• restriction (ou sélection) de la
relation R1 par une qualification
Q = (C<30).
T = s( C < 30) (R)
Michel TOLLENAERE
28
T
A
a
c
x
B
c
d
y
Cours MSI 2A ICL session automne 2014
C
10
15
20
Déf. : fonctions de calcul
Opération CALCULER
R = CALCULER (R0, fonction1, fonction2, ...)
ou
Exemple
R0 = LIGNE_COMMANDE
N°BonCommande
N = CALCULER (R0, fonction)
R =  fonction 1, fonction 2 (R0)
On désire obtenir le chiffre d'affaires total HT,
ainsi que le nombre total de produits commandés :
R1 = CALCULER (LIGNE_COMMANDE,
Somme (Quantité*PuHT),
Somme (Quantité))
CodeProduit
Quantité
PuHT
96008
A10
10
83
96008
B20
35
32
96009
A10
20
83
96010
A15
4
110
96010
B20
55
32
Somme
(Quantité*PuHT)
Somme
(Quantité)
5 810
124
R1 =  SUM Quantité*PuHt , SUM Quantité (LIGNE_COMMANDE)
Les calculs et/ou comptage portent sur la relation R0.
La relation résultat ne comportera qu'une ligne avec autant de colonnes que de résultats
demandés ou pourra simplement être considérée comme un nombre « N » utilisable
ultérieurement en tant que tel dans le cas où un seul résultat est attendu.
Michel TOLLENAERE
29
Cours MSI 2A ICL session automne 2014
Déf. : fonctions « regrouper et calculer »
Exemple
R0 = LIGNE_COMMANDE
R= REGROUPER_ET_CALCULER
(R0, att1, att2, ..., fonction1, fonction2, ...)
N°BonCommande
R =  att1, att2, …, fonction 1, fonction 2,… (R0)
On désire obtenir le montant total HT
de chaque bon de commande :
CodeProduit
Quantité
PuHT
96008
A10
10
83
96008
B20
35
32
96009
A10
20
83
96010
A15
4
110
96010
B20
55
32
R2= REGROUPER_ET_CALCULER
(LIGNE_COMMANDE,
N°BonCommande, MontantHt : Somme (Quantité*PuHT))
N°
BonCommande
Montant
HT
96008
1 950
96009
1 660
96010
2 200
R =  N°BonCommande, MontantHt : SUM Quantité*PuHT (LIGNE_COMMANDE)
Le regroupement s'effectue sur un sous ensemble des attributs de la relation R0
La relation résultat comportera autant de lignes que de groupes de n-uplets, les fonctions
s'appliquant à chacun des groupes séparément.
Michel TOLLENAERE
30
Cours MSI 2A ICL session automne 2014
Définitions : fonctions d’agrégation
Les Fonctions d'agrégation
Elles sont utilisées dans les opérateurs :
CALCULER et REGROUPER_ET_CALCULER.
Les fonctions statistiques de base :
Elles portent sur un ou plusieurs groupes de n-uplets et évidemment sur un
attribut de type numérique (ou Date).
Somme (attribut) : total des valeurs d'un attribut
Moyenne (attribut) : moyenne des valeurs d'un attribut
Minimum (attribut) : plus petite valeur d'un attribut
Maximum (attribut) : plus grande valeur d'un attribut
Remarque : les valeurs "non renseignées" de l'attribut sont ignorées.
La fonction de comptage : Compte ()
La fonction de comptage donne le nombre de n-uplets d'un ou de plusieurs
groupes de n-uplets. Il n'est donc pas nécessaire de préciser d'attribut.
Michel TOLLENAERE
31
Cours MSI 2A ICL session automne 2014
Définitions : contraintes dans une BD
L’intérêt de l’usage de Base de données est que la BD permet la
définition de « contraintes d’intégrité » qui seront automatiquement
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 d’intégrité référentielle
Michel TOLLENAERE
32
Cours MSI 2A ICL session automne 2014
Déf. : contrainte d’inté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 s’utiliser dès qu’une clé primaire
d’une table est utilisée comme référence dans une autre table.
On la nomme « clé étrangère » de la seconde table.
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)
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 )
Michel TOLLENAERE
33
Cours MSI 2A ICL session automne 2014
Formes Normales
Permettent d’éliminer toute forme de redondance dans une BD.
Redondance (Larousse)
•
•
•
•
•
Abondance excessive et non justifiée de termes dans le discours.
Caractère d'un énoncé qui réitère, sous plusieurs formes différentes, un même trait signifiant.
Terme redondant, redite.
En informatique et dans les télécommunications, duplication d'informations afin de garantir leur
sécurité en cas d'incident (commandes redondantes, disques redondants…)
En Systèmes d’infos, information inutilement recopiée (posera pb en cas de modification)
A propos des formes normales,
http://fsmrel.developpez.com/basesrelationnelles/normalisation/
Michel TOLLENAERE
34
Cours MSI 2A ICL session automne 2014
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 ou
plusieurs téléphones, dans la table PERSONNE ; par contre
prénom peut être une chaine de nn caractères
Michel TOLLENAERE
35
Cours MSI 2A ICL session automne 2014
Dépendance fonctionnelle
Cette notion permet de capturer l’idée de dépendance entre
informations en visant à limiter la redondance.
Soit R une relation et X et Y des groupes d’attributs 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
Michel TOLLENAERE
36
Cours MSI 2A ICL session automne 2014
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
Michel TOLLENAERE
37
Cours MSI 2A ICL session automne 2014
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
n’est pas en 3eme FN.
Michel TOLLENAERE
38
Cours MSI 2A ICL session automne 2014
SQL (Structured Query Language)
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)
norme SQL3 (1999) implémentée dans Oracle
Dans la suite, le code SQL est représenté en ARIAL 12, les commentaires en Italique.
MySQL 5.6
Michel TOLLENAERE
http://dev.mysql.com/downloads/
39
Cours MSI 2A ICL session automne 2014
Instructions SQL
SQL comporte 3 parties
1) - Le DML (Data Manipulation Language)
Sélection d’information, création et mise à jour d’enregistrements
SELECT, INSERT, UPDATE, DELETE, JOIN
2) - Le DDL (Data Definition Language)
Création des tables, des attributs et des contraintes d’intégrité
CREATE, ALTER, DROP, RENAME
3) - Le DCL (Data Control Language)
Pour contrôler l’accès aux données
GRANT, REVOKE
Michel TOLLENAERE
40
Cours MSI 2A ICL session automne 2014
Select … from …. where ….
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 NomElv
FROM Eleves
WHERE VilleElv = ‘Mexico';
 Liste des matières pour lesquelles l'élève "Simon-Suisse" a eu au
moins une note.
SELECT NomMat
FROM Notes
WHERE 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
Michel TOLLENAERE
41
Cours MSI 2A ICL session automne 2014
Select …..
Le SELECT est la commande de base du SQL destinée à extraire des
données d'une base ou calculer de nouvelles données à partir
d'existantes...
Voici la syntaxe générale d'une commande SELECT :
SELECT [DISTINCT ou ALL] * ou liste de colonnes
FROM nom de table ou de la vue
[WHERE prédicats]
[GROUP BY ordre des groupes]
[HAVING condition]
[ORDER BY ] liste de colonnes
NOTA : dans cette syntaxe, les mots clef du SQL sont en gras, les paramètres en
minuscule et entre crochets on trouve les parties optionnelles
En fait l'ordre SQL SELECT est composé de 6 clauses dont 4 sont optionnelles.
Clauses de l'ordre SELECT :
SELECT
Spécification des colonnes du résultat
FROM
Spécification des tables sur lesquelles porte l'ordre
WHERE
Filtre portant sur les données (conditions à remplir pour que les lignes soient présentes dans le résultat)
GROUP BY
Définition d'un groupe (sous ensemble)
HAVING
Filtre portant sur les résultats (conditions de regroupement des lignes)
ORDER BY
Tri des données du résultat
Michel TOLLENAERE
42
Cours MSI 2A ICL session automne 2014
L’éditeur d’expressions
Dans
MS – Access
(de 95 à 2013)
pour « calcul »
(d’un nouvel
atribut)
ou « sélection »
(d’entités)
Michel TOLLENAERE
43
Cours MSI 2A ICL session automne 2014
Jointures ….
dans MS Access
Michel TOLLENAERE
44
Cours MSI 2A ICL session automne 2014
Jointures ….
Michel TOLLENAERE
SELECT CLIENT.Telephone, COMMANDE.Montant
FROM CLIENT INNER JOIN COMMANDE
ON CLIENT.Num_client = COMMANDE.Num_client
WHERE (((COMMANDE.Montant)>10000));
45
Cours MSI 2A ICL session automne 2014
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;
46
Michel TOLLENAERE
46
Cours MSI 2A ICL session automne 2014
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 l’attribut « numéro » !!
personne.Numéro = stage.Numéro;
Michel TOLLENAERE
47
Cours MSI 2A ICL session automne 2014
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;
Michel TOLLENAERE
48
Cours MSI 2A ICL session automne 2014
Jointures ….
Michel TOLLENAERE
49
Cours MSI 2A ICL session automne 2014
Jointures interne, externe G et D….
Michel TOLLENAERE
50
Cours MSI 2A ICL session automne 2014
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;
Michel TOLLENAERE
51
Cours MSI 2A ICL session automne 2014
Groupby, Having ….
1.1

La close GROUP BY
Liste des élèves par ville.
SELECT NomElv, VilleElv
FROM Eleves
GROUP BY VilleElv;
1.2 La close HAVING
Elle ne s'utilise qu'avec le GROUP BY et permet de donner la ou les conditions que
doivent remplir ces groupes.


Liste des élèves regroupés par ville où habitent plus de 10 élèves.
SELECT NomElv, VilleElv
FROM Eleves
GROUP BY VilleElv
HAVING Count(NomElv) > 10;
Liste des matières où plus de 35 notes ont été données.
SELECT NomMat
FROM Notes
GROUP BY NomMat
HAVING Count(Note) > 35;
Michel TOLLENAERE
52
Cours MSI 2A ICL session automne 2014
Order by….
1.1 La close ORDER BY
Elle permet de spécifier l'ordre dans lequel vont être affichées les lignes.


Liste des matières dans l'ordre alphabétique.
SELECT NomMat
FROM Matieres
ORDER BY NomMat;
Liste des matières par ordre décroissant de coef., puis par ordre alpha. de nom.
SELECT NomMat
FROM Matieres
ORDER BY Coef Desc, NomMat Asc;
1.2 Récapitulatif
SELECT
noms des colonnes à afficher
FROM
nom de la table où se trouvent les colonnes
susmentionnées
WHERE
condition(s) à remplir par les lignes
GROUP BY
condition(s) de regroupement des lignes
HAVING
condition(s) à remplir par le groupe
ORDER BY ordre (Asc, Desc) d'affichage
Michel TOLLENAERE
53
Cours MSI 2A ICL session automne 2014
1 Les requêtes multi-tables
Soit 4 tables :

Eleves (RefElv, NomElv, PreElv, VilleElv, ClasseElv),

Classes (NomCla, Niveau),

Cours (NomCla, NomMat, NbHeure),

Matieres (NomMat).
1.1 Requêtes où les données sélectionnées sont dans plusieurs
tables


Michel TOLLENAERE
Liste des élèves avec leur niveau.
SELECT NomElv, PreElv, Niveau
FROM Eleves JOIN Classes ON ClasseElv = NomCla
WHERE Eleves.ClasseElv = Classes.NomCla;
Liste des élèves et nom des cours qu'ils suivent pendant plus de 3 heures.
SELECT NomElv, NomMat
FROM Eleves JOIN Cours ON ClasseElv = NomCla
WHERE (Eleves.ClasseElv = Cours.Nomcla) AND (Cours.NbHeure > 3)
(il faut faire d'abord les jointures puis les sélections)
54
Cours MSI 2A ICL session automne 2014
Opérations booléennes avec des jointures
• différence T = (R1 - R2)
ou T = Minus (R1, R2)
• intersection T = (R1 R2)
ou T = inter (R1, R2)
EnsembleA EnsembleB
A001
A002
A003
A004
A005
A006
A007
A008
A008
A009
A009
A010
A010
A011
A012
A013
A014
Michel TOLLENAERE
55
Cours MSI 2A ICL session automne 2014
Opérations booléennes avec des jointures
• différence T = (EnsA - EnsB)
ou T = Minus (EnsA, EnsB)
EnsembleA EnsembleB
A001
A002
A003
A004
A005
A006
A007
A008
A008
A009
A009
A010
A010
A011
A012
A013
A014
Michel TOLLENAERE
56
Cours MSI 2A ICL session automne 2014
Différence avec la clause NOT IN
• différence T = (EnsA - EnsB)
ou T = Minus (EnsA, EnsB)
Autre méthode
EnsembleA EnsembleB
A001
A002
A003
A004
A005
A006
A007
A008
A008
A009
A009
A010
A010
A011
A012
A013
A014
Michel TOLLENAERE
SELECT EnsembleA.ClefA
FROM EnsembleA
Where EnsembleA.ClefA Not In (Select EnsembleB.ClefB From EnsembleB);
57
Cours MSI 2A ICL session automne 2014
Définition de structures….
2 – Création des tables, des attributs et
des contraintes d’intégrité
Le DDL (Data Definition Language)
CREATE
ALTER
DROP
RENAME
3 – Restreindre des droits d’acces
GRANT
REVOKE
Michel TOLLENAERE
58
Cours MSI 2A ICL session automne 2014
Conclusion
• en combinant les opérations :
• booléennes,
• de calcul,
• de sélection et
• tri
Fournir les
bonnes
informations
pour l’action
• en disposant des données :
• à jour,
• bien structurées
(suite du cours UML)
Michel TOLLENAERE
59
Cours MSI 2A ICL session automne 2014
Cours MSI-2A filière
ICL
Références biblio
version 1.4 du 5 novembre 2012
Un ouvrage : "Bases de données - de la modélisation au SQL",
Laurent Audibert, eds Ellipses 2009 (6 exemplaires à la BU - GI)
Un cours en ligne (en anglais) avec un simulateur interactif
http://www.sqlcourse.com/index.html
A propos des formes normales, François de Sainte Marie
http://fsmrel.developpez.com/basesrelationnelles/normalisation/
A propos de MS Access 2010,
http://cerig.efpg.inpg.fr/tutoriel/bases-de-donnees/sommaire.htm
http://databases.about.com/
Michel TOLLENAERE
60
Cours MSI 2A ICL session automne 2014