Systeme d’information

http://www.agroparistech.fr/Systeme-d-Information.html

Partie Idu TD 1 au TD 5

——————————————————-Bases de donnees et MySQL

——————————————————-

Liliana Ibanesculiliana.ibanescu@agroparistech.fr

UFR d’informatiqueDepartement MMIP

Annee 2015 - 2016

Ce support de cours s’appuie sur l’ouvrage Bases de donnees. Concepts,utilisation et developpement de Jean-Luc Hainaut, paru en 2009 aux editionsDunod.

2

Table des matieres

1 Les bases de donnees (BD) 71.1 La base de donnees client commande . . . . . . . . . . . . . . . . . 81.2 La base de donnees gie agricole . . . . . . . . . . . . . . . . . . . 101.3 BD, tables, lignes et colonnes . . . . . . . . . . . . . . . . . . . . . . . . 121.4 Type de donnee . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12

1.4.1 Types de base . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121.4.2 Operateurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131.4.3 La valeur NULL . . . . . . . . . . . . . . . . . . . . . . . . . . . 14

1.5 Cle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141.5.1 Cle primaire . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141.5.2 Cles etrangeres . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

1.6 Modification et contraintes d’integrite . . . . . . . . . . . . . . . . . . . 161.6.1 Contraintes imposees par les colonnes obligatoires . . . . . . . . 161.6.2 Contraintes d’unicite imposees par les cles primaires . . . . . . . 161.6.3 Contraintes referentielles imposees par les cles etrangeres . . . . 17

1.7 Schema et contenu d’une base de donnee . . . . . . . . . . . . . . . . . . 191.8 Le langage SQL (Structured Query Language) . . . . . . . . . . . . . . . 211.9 Les systemes de gestion de bases de donnees (SGBD) . . . . . . . . . . . 22

2 Les instructions du langage SQL 232.1 Sites Web de reference pour SQL et MySQL . . . . . . . . . . . . . . . . 232.2 Le langage SQL DDL (Data Definition Language) . . . . . . . . . . . . . 23

2.2.1 Creation d’un schema . . . . . . . . . . . . . . . . . . . . . . . 232.2.2 Creation d’une table (CREATE TABLE) . . . . . . . . . . . . . 242.2.3 Suppression d’une table (DROP) . . . . . . . . . . . . . . . . . . 252.2.4 Modification du schema . . . . . . . . . . . . . . . . . . . . . . 25

2.3 Le langage SQL DML (Data Manipulation Language) . . . . . . . . . . . 262.3.1 Extraction de donnees . . . . . . . . . . . . . . . . . . . . . . . 262.3.2 Extraction simple (SELECT-FROM) . . . . . . . . . . . . . . . . 262.3.3 Extraction de lignes selectionnees (SELECT-FROM-WHERE) . . 272.3.4 Ordre des lignes d’un resultat (clause ORDER BY) . . . . . . . . 292.3.5 Les fonctions agregatives (ou statistiques) . . . . . . . . . . . . . 292.3.6 Extraction de donnees de plusieurs tables (jointure) . . . . . . . . 302.3.7 Les sous-requetes . . . . . . . . . . . . . . . . . . . . . . . . . . 332.3.8 Sous-requete ou jointure ? . . . . . . . . . . . . . . . . . . . . . 34

3

2.3.9 Ajout de lignes dans une table (INSERT) . . . . . . . . . . . . . 352.3.10 Suppression de lignes (DELETE) . . . . . . . . . . . . . . . . . . 352.3.11 Modification de lignes (UPDATE) . . . . . . . . . . . . . . . . . 36

3 Prise en main de l’environnement de developpement 373.1 Pre-requis . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373.2 Organisation du repertoire de travail . . . . . . . . . . . . . . . . . . . . 373.3 L’editeur de texte Notepad++ . . . . . . . . . . . . . . . . . . . . . . . 38

3.3.1 Demarrage de Notepad++ . . . . . . . . . . . . . . . . . . . . . 383.3.2 Creer un nouveau fichier . . . . . . . . . . . . . . . . . . . . . . 383.3.3 Ouvrir un fichier existant . . . . . . . . . . . . . . . . . . . . . . 38

3.4 MySQL et phpMyAdmin . . . . . . . . . . . . . . . . . . . . . . . . . 393.4.1 Utiliser MySQL avec phpMyAdmin . . . . . . . . . . . . . . . . 393.4.2 Operations sur une base de donnees avec MySQL . . . . . . . . . 393.4.3 Operations sur les tables d’une base de donnees . . . . . . . . . . 403.4.4 Operations sur une seule table . . . . . . . . . . . . . . . . . . . 40

3.5 Ressources en ligne . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40

4 TD 1 : Travaux diriges en BD, seance 1 – Requetes simples sur une table 414.1 Premiers pas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 414.2 Exercice 1a : requetes simples sur la base de donnees client commande 42

4.2.1 Exemples de requetes . . . . . . . . . . . . . . . . . . . . . . . . 424.2.2 Requetes generees par l’interface de phpMyAdmin/MySQL . . . 434.2.3 Requetes en SQL . . . . . . . . . . . . . . . . . . . . . . . . . . 44

4.3 Exercice 1b : requetes sur la BD gie agricole . . . . . . . . . . . . . 444.4 Pour terminer cet exercice . . . . . . . . . . . . . . . . . . . . . . . . . 454.5 Questions de revision . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

4.5.1 Q.R.1 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454.5.2 Q.R.2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454.5.3 Q.R.3 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45

5 TD 2 : Travaux diriges en BD, seance 2 – Requetes avec jointures entre tables 465.1 Exercice 2a : requetes sur la BD client commande . . . . . . . . . . 46

5.1.1 Exemples de requetes . . . . . . . . . . . . . . . . . . . . . . . . 465.1.2 Requetes en SQL . . . . . . . . . . . . . . . . . . . . . . . . . . 47

5.2 Exercice 2b : requetes sur la BD gie agricole . . . . . . . . . . . . . 485.3 Exercice 3 : fonctions agregatives . . . . . . . . . . . . . . . . . . . . . 48

5.3.1 Exemples de requetes sur la BD client commande . . . . . . 485.3.2 Exercice 3a : fonctions agregatives sur la BD client commande 495.3.3 Exercice 3b : fonctions agregatives sur la BD gie agricole . 49

5.4 Questions de revision . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505.4.1 Q.R.4 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505.4.2 Q.R.5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505.4.3 Q.R.6 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 515.4.4 Q.R.7 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51

4

6 TD 3 : Travaux diriges en BD, seance 3 – Requetes de mise a jour 526.1 Exercice 4 : requetes sur la BD client commande . . . . . . . . . . . 52

6.1.1 Exemples de requetes . . . . . . . . . . . . . . . . . . . . . . . . 526.1.2 Requetes generees par l’interface de phpMyAdmin/MySQL . . . 536.1.3 Requetes en SQL . . . . . . . . . . . . . . . . . . . . . . . . . . 54

6.2 Exercice 5 : Modification et contraintes d’integrite . . . . . . . . . . . . 556.2.1 Question 5a . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 556.2.2 Questions 5b . . . . . . . . . . . . . . . . . . . . . . . . . . . . 556.2.3 Question 5c . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 566.2.4 Question 5d . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 56

6.3 Questions de revision . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576.3.1 Q.R.8 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576.3.2 Q.R.9 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576.3.3 Q.R.10 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57

7 TD 4 : Travaux diriges en BD, seance 4 – Le systeme d’information Starboat 587.1 Cahier des charges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58

7.1.1 Description du contexte . . . . . . . . . . . . . . . . . . . . . . 587.1.2 Fonctionnalites attendues du SI . . . . . . . . . . . . . . . . . . 59

7.2 Exercice 6a : le schema . . . . . . . . . . . . . . . . . . . . . . . . . . . 597.3 Exercice 6b : les requetes . . . . . . . . . . . . . . . . . . . . . . . . . . 597.4 Exercice 6c : les donnees . . . . . . . . . . . . . . . . . . . . . . . . . . 597.5 Exercice 6d : questions . . . . . . . . . . . . . . . . . . . . . . . . . . . 60

5

6

Chapitre 1

Les bases de donnees (BD)

L’objectif de ce chapitre est de presenter de maniere formelle et succincte les notionset le vocabulaire utile en base de donnees. Pour plus de details vous pourrez vous re-porter a l’ouvrage Bases de donnees. Concepts, utilisation et developpement de Jean-LucHainaut, paru en 2009 aux editions Dunod.

Nous baignons dans un tourbillon d’informations : nous recevons des informations denotre environnement et nous lui en transmettons.

Les informations prennent la forme de donnees (symboles memorises et vehiculessur un support materiel ou immateriel). Pour stocker (conserver ou enregistrer) et traiter(interroger et transformer) ces donnees, on utilise des bases de donnees (BD), database(DB) en anglais (voir les §1.3 a §1.8).

La gestion d’une base de donnees pose des problemes complexes et elle est assureepar des logiciels specialises : les systemes de gestion de bases de donnees (SGBD), enanglais DBMS pour database management system (voir le §1.9).

Tout au long de ce cours nous utiliserons deux exemples de bases de donnees :

1. La base de donnees appelee client commande, decrite dans le §1.1, est uti-lisee pour enregistrer les clients, les produits et les commandes d’une entreprisede materiaux de construction ; le systeme d’information construit sur cette base dedonnees permettra, entre autres, d’editer des factures, de gerer les stocks et la comp-tabilite.

2. La base de donnees appelee gie agricole, decrite dans le §1.2, est utilisee parun GIE 1 agricole pour enregistrer les interventions de ses employes sur les parcellesdes agriculteurs ; le systeme d’information construit sur cette base de donnees per-mettra, entre autres, d’editer les fiches de paye des employes et les interventionseffectuees pour un agriculteur.

1. GIE = groupement d’interet economique

7

1.1 La base de donnees client commande

La base de donnees client commande est utilisee par une entreprise de materiauxde construction pour lui permettre d’editer des factures pour ses clients et de gerer sesstocks.

Le schema de la base de donnees client commande est presente dans la figure 1.1 :

1. la table client est utilisee pour enregistrer les proprietes concernant les clientsde l’entreprise : leur nom, leur adresse, etc.

2. la table produit est utilisee pour enregistrer les proprietes concernant les materiauxde construction disponibles a la vente : leur libelle, le prix a l’unite, la quantite enstock.

3. la table commande est utilisee pour enregistrer les proprietes concernant une com-mande : son numero, la date de la commande et l’identifiant du client (NCLI) quipermet de recuperer dans la table client toutes les informations relatives a ceclient.

4. la table detail est utilisee pour enregistrer les “lignes” des commandes : l’iden-tifiant de la commande (NCOM), l’identifiant du produit (NPRO) et la quantite com-mandee.

FIGURE 1.1 – Le schema de la base de donnees client commande.

A un instant donne, les lignes (ou enregistrements) de la BD client commandesont ceux donnes dans la figure 1.2.

8

table client table commande

table produit table detail

FIGURE 1.2 – Les lignes de la base de donnees client commande a un instant donne.

9

1.2 La base de donnees gie agricole

La base de donnees gie agricole est utilisee par un groupement d’interet econo-mique (GIE) agricole pour enregistrer les interventions de ses employes sur les parcellesdes agriculteurs. La main d’œuvre pour l’exploitation des parcelles est assuree par les em-ployes du GIE, payes selon un salaire journalier brut. La BD permet, entre autre, d’editerles fiches de payes mensuelles des employes et de lister les interventions effectuees pourun agriculteur.

Le schema de la base de donnees gie agricole est presente dans la figure 1.3 :

1. la table agriculteur est utilisee pour enregistrer les proprietes concernant unagriculteur : son nom, son prenom et son adresse.

2. la table parcelle est utilisee pour enregistrer les proprietes concernant les par-celles des agriculteurs : leur nom, leur lieu, leur superficie et l’identifiant de leurproprietaire.

3. la table employe est utilisee pour enregistrer les proprietes concernant les em-ployes.

4. la table intervention est utilisee pour enregistrer les interventions des em-ployes du GIE sur les parcelles des agriculteurs.

FIGURE 1.3 – Le schema de la base de donnees gie agricole.

A un instant donne, les lignes (ou enregistrements) de la BD gie agricole sontceux donnes dans la figure 1.4.

10

table agriculteur table tarif

table parcelle

table employe

table intervention

FIGURE 1.4 – Les lignes de la base de donnees gie agricole a un instant donne.

11

1.3 BD, tables, lignes et colonnesUne base de donnees est constituee d’un ensemble de tables.Une table contient une collection/suite de lignes, aussi appelees enregistrements.Une ligne d’une table est une suite de valeurs, chacune d’un type determine. Une ligne

regroupe les donnees relatives a une entite ou un fait du domaine d’application (la partiedu monde a laquelle on s’interesse). Toutes les lignes d’une table ont le meme format oustructure.

Une colonne est definie par son nom et le type de ses valeurs.

Exemple 1. La figure 1.5 represente les informations enregistrees a un instant donne dansla table produit de la BD client commande : la table a 7 lignes (enregistrements)decrivant chacune un produit. On trouve dans chaque ligne quatre valeurs representantrespectivement le code, le libelle, le prix a l’unite d’un produit, ainsi que la quantite restanten stock. La colonne LIBELLE contient des valeurs qui sont des chaınes de caracteres,les valeurs de la colonne PRIX sont des nombres decimaux (dont deux chiffres apres lavirgule) et les valeurs de la colonne QSTOCK sont des nombres entiers (cf a la figure 1.1).

FIGURE 1.5 – Les lignes de la table produit de la BD client commande a un instantdonne.

1.4 Type de donneeEn informatique, un type de donnee, ou simplement type, definit le type des valeurs

que peut prendre une donnee, ce qui permet de determiner les operateurs qui peuvent etreappliques a cette donnee.

1.4.1 Types de baseLes types de base sont les suivants :

– type booleen : les valeurs true et false.– type numerique : des nombres entiers, des nombres decimaux, des nombres reels.– type chaıne de caracteres : du texte.– type temporel : une date (jour, mois et annee), une heure (heure, minute et seconde).

12

1.4.2 Operateurs

Les operateurs utilises pour comparer des valeurs sont := egal a> plus grand que< plus petit que<> different de>= plus grand ou egal<= plus petit ou egal

L’interpretation de ces relations est evidente pour les valeurs numeriques. Pour les va-leurs temporelles, l’expression date1 < date2 s’interprete comme date1 est anterieure adate2 dans le calendrier. Pour les chaınes de caracteres, l’expression ch1 < ch2 pour toutechaınes ch1 et ch2, s’interprete comme ch1 est plus petite que ch2 selon l’ordre lexicogra-phique (celui du dictionnaire). Attention : le caractere “a” n’est pas egal a “A”.

Les operateurs logiques utilises pour les valeurs de type booleen sont :and : conjonctionor : disjonctionnot : negation

Lorsqu’une expression complexe comporte plusieurs operateurs, les priorites des ope-rateurs (ou precedence des operateurs) determinent l’ordre d’execution des operations.Cet ordre peut affecter considerablement la valeur du resultat. Un operateur de prioriteelevee est evalue avant un operateur de priorite basse. Lorsque deux operateurs dans uneexpression ont le meme niveau de priorite, ils sont evalues de gauche a droite en fonctionde leur position dans l’expression. En regle generale

– les operateurs arithmetiques de multiplication et de division sont prioritaires sur lesoperateurs arithmetiques d’addition et de soustraction ;

– les operateurs arithmetiques sont prioritaires sur les operateurs de comparaison ;– les operateurs de comparaison sont prioritaires sur les operateurs logiques ;– l’operateur logique and est prioritaire sur l’operateur or.Pour modifier la priorite habituelle des operateurs dans une expression il faut utiliser

des parentheses. Tout ce qui se trouve entre parentheses est evalue en premier pour pro-duire une seule valeur, qui est ensuite utilisee par un operateur en dehors des parentheses.

Exemple 2. L’expression 2+3×4 est calculee comme l’expression (2+(3×4)) car lamultiplication est prioritaire sur l’addition.L’expression 5×6 : 3 est calculee comme l’expression ((5×6) : 3) car la multiplicationet la division ont la meme priorite et l’evaluation se fait de gauche a droite. L’expression

age = 16+5 or age = 20 and ville = ’Paris’est calculee comme l’expression

((age = (16+5)) or ((age = 20) and (ville = ’Paris’)))car l’operateur arithmetique d’addition est prioritaire sur tous les autres operateurs, l’o-perateur de comparaison = est prioritaire aux operateurs logiques et l’operateur and estprioritaire sur l’operateur or.

13

1.4.3 La valeur NULLL’absence de valeur d’une colonne dans une ligne d’une table se signalera par l’affec-

tation de la valeur conventionnelle NULL a cette colonne.On peut imposer l’interdiction d’assigner la valeur NULL a une colonne : cette colonne

sera alors une colonne obligatoire. Si la valeur NULL est autorisee, cette colonne sera ditefacultative.

Contrainte : Toute tentative d’inserer une ligne qui ne possederait pas de valeur pourune colonne obligatoire serait automatiquement signalee comme erreur.

Exemple 3. Dans la table client de la BD client commande la colonne CAT, pourcategorie de client, est une colonne facultative. Si au moment de l’enregistrement d’unclient la valeur de la categorie de ce client n’est pas connue, alors il est possible de ne pasrenseigner cette colonne pour ce client et c’est le marqueur NULL qui sera enregistre.

Remarque : La valeur NULL a un statut particulier par rapport aux autres valeurs, sonusage entraıne de multiples difficultes et certains auteurs recommandent de l’eviter.

1.5 CleUne ligne dans une table regroupe des informations sur une entite. Une des propriete

importante dans une table c’est de pouvoir identifier de maniere unique une ligne a l’aided’un identifiant ou une cle (key en anglais).

Une cle peut etre composee de plusieurs colonnes de la table.

Exemple 4. Une ligne de la table client donne les informations relative a un client :son nom, son adresse, la ville ou il habite.Declarer que la colonne NOM est une cle de la table client, impliquerais alors, qu’a toutinstant, il n’existera pas deux (ou plusieurs) lignes ayant la meme valeur dans la colonneNOM.Declarer que le couple de colonnes (NOM, VILLE) est une cle de la table client, im-plique alors, qu’a tout instant, il n’existera pas deux (ou plusieurs) lignes ayant la memevaleur pour le couple (nom du client, ville).

1.5.1 Cle primaireParmi les identifiants d’une table, l’un est declare identifiant primaire ou cle pri-

maire(primary key en anglais).La cle primaire d’une table impose une contrainte d’unicite : le SGBD rejettera auto-

matiquement toute tentative d’insertion d’une ligne dont la valeur de la cle primaire estdeja presente dans la table.

Une cle primaire peut etre composee de plusieurs colonnes, qui doivent toutes etreobligatoires.

Il est recommande de toujours declarer une cle primaire dans une table.

14

Exemple 5. La cle primaire de la table employe de la base de donnees gie agricole 2

est representee par la colonne Emp Nss qui contient les numeros de securite sociale desemployes, qui sont donc uniques.

La cle primaire de la table detail de la base de donnees client commande 3 estrepresentee par le couple de deux colonnes NCOM et NPRO, ce qui impose qu’on ne pourrapas enregistrer deux lignes ayant le meme numero de commande et le meme numero deproduit (voir aussi l’exemple 9, page 16).

La cle primaire de la table intervention de la base de donnees gie agricole 4

est representee par ses trois premieres colonnes.

1.5.2 Cles etrangeresDans une table, appelee table enfant, une de ses colonnes, appelee cle etrangere (fo-

reign key en anglais), peut faire reference a la colonne qui est cle primaire dans une autretable, appelee table parent. Le couple constitue par une cle etrangere de la table enfant etla cle primaire de la table parent permet de relier des lignes dans des tables distinctes.

Exemple 6. La table parcelle (table enfant) de la base de donnees gie agricole 5

a ete definie avec une cle etrangere representee par la colonne Par Prop qui fait referencea la cle primaire Agr Id de la table agriculteur (table parent). Ceci implique quepour une ligne de la table parcelle, la valeur de la colonne Par Prop permet de larelier cette ligne a une ligne de la table agriculteur.Pour la version de la BD de la figure 1.4, sur la troisieme ligne de la table parcelle,la valeur 1 dans la colonne Par Prop permet de relier la parcelle decrite sur cette ligne,Plan des Bauges, avec la premiere ligne de la table agriculteur, en retrouvant ainsitoutes les informations concernant l’agriculteur : son nom (Dulhac), son prenom et sonadresse.

Exemple 7. La table detail (table enfant) de la base de donnees client commande 6

a deux cles etrangeres :1. la colonne NCOM, appelee dans la suite FK1, qui fait reference a la cle primaire, la

colonne NCOM, de la table commande (table parent) ;2. la colonne NPRO, appelee dans la suite FK2, qui fait reference a la cle primaire

NPRO, de la la table produit (table parent’).Pour la version de la BD de la figure 1.2, pour la derniere ligne de la table detail

– la valeur 30188 dans la colonne NCOM permet de relier cette ligne avec la derniereligne de la table commande en retrouvant ainsi la date de la commande, le 3 janvier2009 (par la cle FK1).

– la valeur PH222 dans la colonne NPRO permet de relier cette ligne avec l’avant-derniere ligne de la table produit en retrouvant ainsi toutes les informationsconcernant le produit PL. HETRE 200x20x2 (par la cle FK2).

2. voir la figure 1.3, page 103. voir la figure 1.1, page 84. voir la figure 1.3, page 105. voir la figure 1.3, page 106. voir la figure 1.1, page 8

15

On notera que le nom d’une colonne formant une cle etrangere peut etre le meme oupeut-etre different de celui de la cle primaire a laquelle elle fait reference.

Pour qu’une cle etrangere joue correctement le role de reference, il est necessaire quel’ensemble de ses valeurs dans la table enfant soit un sous-ensemble des valeurs de lacle primaire de la table parent. Cette propriete est appelee contrainte referentielle (voireaussi le §1.6.3, page 17). Elle est garantie par le SGBD pour autant qu’on ait explici-tement declare les cles etrangeres (c.a.d. creer les relations avec les cles primaires aux-quelles elles font reference) : toute operation qui conduirait a violer cette contrainte seraitautomatiquement rejetee.

1.6 Modification et contraintes d’integriteLes colonnes obligatoires, les cles primaires et les cles etrangeres, imposent aux

donnees des contraintes qui doivent etre satisfaites a tout instant. Ces contraintes, designeesgeneralement sous le terme de contraintes d’integrite, seront donc prises en compte lorsde toute tentative de modification sur les donnees. Ajouter une ligne, supprimer une ligneou modifier une valeur de colonne d’une ligne sont des operations qui ne sont autoriseesque si ces contraintes sont toujours respectees par les donnees apres ces operations. Si cescontraintes sont violees, on dit que les donnees ont perdu leur integrite.

1.6.1 Contraintes imposees par les colonnes obligatoiresSi une colonne est declaree obligatoire, chaque ligne doit en posseder une valeur. Lors

des operations de creation et de modification de lignes, cette colonne devra recevoir unevaleur, a l’exclusion de la valeur NULL.

1.6.2 Contraintes d’unicite imposees par les cles primairesUne cle primaire (cf. §1.5.1) impose une contrainte d’unicite signifiant qu’a tout ins-

tant les lignes d’une table possedent des valeurs distinctes pour la ou les colonnes consti-tuant la cle.

– La creation d’une ligne est autorisee s’il n’existe pas de ligne possedant la memevaleur pour la cle primaire.

– Pour la suppression d’une ligne il n’y a pas de contrainte.– La modification de la cle primaire d’une ligne est autorisee s’il n’existe pas deja

une ligne possedant cette nouvelle valeur de la cle primaire.

Exemple 8. La cle primaire de la table client de la BD client commande 7 estrepresentee par la colonne NCLI.Pour la version de la BD de la figure 1.2, la creation d’une nouvelle ligne ayant commevaleur C123 dans la colonne NCLI n’est pas autorisee car il existe deja une ligne, lasixieme, dans cette table ayant cette valeur dans la colonne NCLI.

Exemple 9. La cle primaire de la table detail de la BD client commande 8 est

7. voir la figure 1.1, page 88. voir la figure 1.1, page 8

16

representee par le couple de deux colonnes NCOM et NPRO.Pour la version de la BD de la figure 1.2, la creation d’une ligne ayant comme valeur30188 dans la colonne NCOM et PH222 dans la colonne NPRO n’est pas autorisee car ilexiste deja une ligne, la derniere, ayant ces valeurs pour la cle primaire.

1.6.3 Contraintes referentielles imposees par les cles etrangeresUne contrainte referentielle precise que dans une table enfant chaque colonne iden-

tifiee comme etant une cle etrangere doit a tout instant, pour chaque ligne, contenir unevaleur correspondant a la valeur d’une cle primaire dans une ligne de la table parent.

Exemple 10. La cle etrangere de l’exemple 6 page 15 pour la BD gie agricole im-pose que dans la colonne Par Prop de la table parcelle les seules valeurs accepteessont celles deja presentes dans la colonne Agr Id de la table agriculteur.

Exemple 11. Les deux cles etrangeres presentees dans l’exemple 7 page 15 pour la BDclient commande impose deux contraintes referentielles :

1. la premiere, cf. FK1, indique que toute valeur de la colonne NCOM dans detaildoit faire reference a une valeur de la colonne NCOM de la table commande ;

2. la seconde, cf. FK2, indique que toute valeur de la colonne NPRO dans detaildoit faire reference a une valeur de la colonne NPRO de la table produit.

Exemple 12. La table commande (table enfant) de la BD client commande doitrespecter la contrainte referentielle donnee par la colonne NCLI identifie comme etantune cle etrangere, appelee FK3, qui fait reference a la colonne NCLI de la table client(table parent) : dans la colonne NCLI de la table commande les seules valeurs accepteessont celles deja presentes dans la colonne NCLI de la table client.

La suppression dans la table parent d’une ligne referencee par d’autres lignes dansune table enfant sera executee selon une des strategies possibles suivantes, appelees deletemode :

– blocage : la suppression de la ligne dans la table parent est refusee ;– cascade : la suppression de la ligne dans la table parent est accompagnee de la

suppression des lignes correspondantes dans la table enfant ;– independance : la suppression de la ligne dans la table parent est accompagnee

par la mise a NULL des colonnes correspondant aux cles etrangeres des lignesconcernees dans la table enfant.

Exemple 13. Dans la base de donnees client commande la table detail a pour cleprimaire le couple de colonnes (NCOM, NPRO) (voir aussi l’exemple 5) et doit respecterles deux contraintes referentielles donnees dans l’exemple 11. Les consequences sur lamodification de cette table sont les suivantes :

1. La creation d’une ligne dans la table detail est autorisee seulement si :

(a) la valeur de la colonne NCOM de cette nouvelle ligne existe dans la colonneNCOM de la table commande (cf. FK1) ;

17

(b) la valeur de la colonne NPRO de cette nouvelle ligne existe dans la colonneNPRO de la table produit (cf. FK2) ;

(c) le couple de valeurs (NCOM, NPRO) n’existe pas deja dans une ligne de la tabledetail (voir aussi l’exemple 8).

2. La suppression d’une ligne dans la table detail est autorisee.3. La modification de la cle primaire d’une ligne dans la table detail, c’est-a-dire le

couple de valeurs (NCOM, NPRO), est autorisee seulement si ces valeurs respectentles contraintes 1(a), 1(b) et 1(c) vues ci-dessus.

Exemple 14. Dans la base de donnees client commande, la table commandei) a pour cle primaire la colonne NCOM,ii) est la table parent dans la cle etrangere FK1 (voir aussi l’exemple 7), etiii) est la table enfant dans la cle etrangere FK3 (voir aussi l’exemple 12).

Les consequences sur la modification de cette table sont les suivantes :1. La creation d’une ligne dans la table commande est autorisee seulement si :

(a) la valeur de la colonne NCOM de cette nouvelle ligne n’existe pas deja dansune ligne de la table commande (NCOM est cle primaire) ;

(b) la valeur de la colonne NCLI de cette nouvelle ligne existe dans la colonneNCLI de la table client (cf. FK3).

2. Si la strategie de suppression est en mode blocage, alors la suppression d’une lignelcomm dans la table commande est autorisee seulement s’il n’existe pas de lignesdans la table detail qui font reference a cette ligne lcomm (cf. FK1).

3. La modification de la cle primaire d’une ligne dans la table commande, c’est adire la valeur de la colonne NCOM, est autorisee seulement si cette valeur respectela contrainte 1(a).

Exemple 15. Dans la base de donnees client commande la table client a pourcle primaire la colonne NCLI et elle est la table parent dans la cle etrangere FK3. Lesconsequences sur la modification de cette table sont les suivantes :

1. La creation d’une ligne dans la table client est autorisee seulement si la valeurde la colonne NCLI de cette nouvelle ligne n’existe pas deja dans une ligne de latable.

2. Si la strategie de suppression est en mode blocage, alors la suppression d’une lignelcli dans la table client est autorisee seulement s’il n’existe pas de lignes dans latable commande qui font reference a cette ligne lcli (cf. FK3).

3. La modification de la cle primaire d’une ligne dans la table client, c’est a dire lavaleur de la colonne NCLI, est autorisee seulement si cette valeur n’existe pas dejadans une ligne de la table client.

Exemple 16. Dans la base de donnees client commande, si la strategie de suppres-sion est en mode cascade pour les cles etrangeres FK3 et FK1, alors la suppression d’uneligne dans la table client sera accompagnee de la suppression de toutes les lignes dela table commande qui y font reference (c.a.d. toutes les commandes de ce client), ainsique la suppression de toutes les lignes concernees de la table detail (c.a.d. toutes leslignes de toutes les commandes de ce client).

18

1.7 Schema et contenu d’une base de donneeUne base de donnees est composee de deux parties distinctes : son schema et son

contenu.Le schema d’une base de donnees specifie la liste des tables et pour chacune son

nom, la liste de ses colonnes, sa cle primaire et, eventuellement, sa/ses cle(s) etrangere(s).Pour chaque colonne il faut specifier son nom, son type et preciser si c’est une colonneobligatoire ou non.

Le contenu d’une base de donnees a un instant t est l’ensemble des lignes de toutesles tables.

Le contenu d’une base de donnees reelle est generalement volumineux (plusieursmillions de lignes) et est susceptible d’evoluer constamment. En revanche, le schemacomporte un nombre limite d’elements (quelques tables a quelques milliers de tables engeneral) presentant une relative stabilite dans le temps : on ne modifie la structure d’unebase de donnees que lorsque la structure du domaine d’application a representer evolue.

Il existe plusieurs conventions graphiques de representation d’un schema de BD,parmi lesquelles les plus utilisees sont les suivantes :

1. Une table est representee soit par un rectangle contenant le nom de la table et celuide chaque colonne, en liste horizontale (a la mode “EXCEL”), soit par une boıtedont le premier compartiment indique le nom de la table et ensuite les noms de sescolonnes en liste verticale.

2. La cle primaire est soit soulignee d’un trait continu, soit elle est indiquee en gras,soit elle est specifiee par la clause “id :”.

3. Une cle etrangere est soit soulignee d’un trait pointille, soit specifiee par la clause“ref :”.

4. Une contrainte referentielle est representee par une fleche qui part du nom de lacolonne qui est une cle etrangere et qui pointe vers la cle primaire referencee dansla table cible.

19

Exemple 17. La figure 1.6 donne trois representations graphiques du schema de la basede donnees client commande, decrite dans le §1.1. Nous utiliserons dans ce cours larepresentation graphique produite par MySQL, donnee dans la figure 1.1 et reprise dansla figure 1.7.

FIGURE 1.6 – Differentes representations graphiques d’un meme schema d’une BD(source : “Bases de donnees. Concepts, utilisation et developpement” de Jean-Luc Hai-naut).

FIGURE 1.7 – Le schema de la base de donnees client commande.

20

1.8 Le langage SQL (Structured Query Language)Les SGBD proposent un langage de requete denomme SQL (Structured Query Lan-

guage). Presente pour la premiere fois en 1973, ce langage a rapidement ete adopte commestandard potentiel et pris en charge par les organismes de normalisation ANSI et ISO quiont publie 3 normes : SQL-89, SQL-92 (denommee aussi SQL2) et SQL : 1999 (SQL3).Malheureusement, les editeurs de SGBD ne respectent pas integralement ces normes :ils ne reprennent qu’un sous-ensemble de specifications, modifient la syntaxe, voire l’in-terpretation des concepts retenus, et ajoutent leur propres fonctions. Dans ce cours, nousutilisons la syntaxe SQL2 dans sa version MySQL.

Le langage de bases de donnees SQL est compose de deux sous-langages :

1. SQL DDL (Data Definition Language) pour la definition et la modification desstructures (table, colonne, contrainte). Les instructions sont : CREATE, ALTER, etDROP ;

2. SQL DML (Data Manipulation Language) pour l’extraction et la modification desdonnees. Les instructions sont : SELECT, INSERT, DELETE, et UPDATE.

Une instruction SQL constitue une requete (en anglais query), c’est-a-dire la descrip-tion d’une operation que le SGBD doit executer.

Une requete SQL peut etre ecrite en utilisant le clavier, generee a partir d’une interfacegraphique, ou importee a partir d’un fichier.

Le resultat de l’execution d’une requete peut apparaıtre a l’ecran avec des eventuelsmessages d’erreurs. Pour la premiere partie, du cours nous utilisons cette formulationinteractive des requetes SQL.

Une requete peut egalement etre envoyee par un programme (ecrit en C, PHP ou Java,par exemple) au SGBD. Dans ce cas, le resultat de la requete est range par le SGBD,ligne par ligne, dans les variables du programme. Dans la deuxieme partie du cours, nousutiliserons le langage PHP pour envoyer des requetes au SGBD et exploiter ensuite leursresultats dans des programmes.

21

1.9 Les systemes de gestion de bases de donnees (SGBD)La gestion d’une base de donnees est assuree par des logiciels specialises : les SGBD.

Les fonctions d’un SGBD sont les suivantes :

1. Organisation des donnees : le SGBD organise les donnees en tables stockees surdisque et il cree les mecanismes garantissant un acces rapide aux donnees.

2. Gestion des donnees : le SGBD garantit l’evolution coherente des donnees et ilverifie que les contraintes (unicite, reference entre tables, etc.) sont respectees.

3. Acces aux donnees : le SGBD permet l’acces aux donnees a la fois par un utilisa-teur occasionnel et par des programmes de traitement de donnees.

4. Gestion des acces concurrents : le SGBD permet l’acces simultane aux donneespar des centaines voire des milliers d’utilisateurs. Il controle rigoureusement lesoperations simultanees sur les memes donnees.

5. Controle des acces : le SGBD garantit que seuls les utilisateurs autorises peuventacceder aux donnees et les modifier.

Les differents SGBD sur le marche se differencient par le perimetre d’utilisation desbases de donnees. Le perimetre influence le nombre d’utilisateurs simultanes, la tailledes bases de donnees et la puissance de calcul necessaire. Certains SGBD, utilises dansles entreprises, supportent de tres grandes bases de donnees et necessitent des ordinateurspuissants et tres couteux. D’autres SGBD fonctionnent sur des ordinateurs personnels bonmarche, avec des limites quant a la taille des bases de donnees et la puissance de calcul.Le marche des SGBD 9 se repartit entre

1. des SGBD commerciaux (payants) :– Oracle Database 10,– DB2 Database Software 11 d’IBM,– SQL Server 12 de Microsoft,– Access 13, edite par Microsoft, qui fait partie de la suite bureautique MS Office

Pro, etc.

2. des SGBD Open Source (ou libre) :– MySQL 14,– PostgreSQL 15, etc.

Nous utiliserons dans ce cours le SGBD MySQL 16, un logiciel SGBD libre.

9. Pour en savoir plus sur les parts de marche consultez, par exemple, http://www.mysql.com/why-mysql/marketshare/

10. http://www.oracle.com/fr/products/database/index.html11. http://www-01.ibm.com/software/data/db2/12. http://www.microsoft.com/france/serveur-cloud/sql/13. http://office.microsoft.com/fr-fr/access/14. http://www.mysql.fr/15. http://www.postgresql.org/16. http://www.mysql.fr/

22

Chapitre 2

Les instructions du langage SQL

Dans la suite nous presentons une syntaxe simplifiee des instructions du langage SQL,adaptee aux objectifs du cours. Le langage SQL est le standard utilise pour la definitiondu schema d’une base de donnee et pour la manipulation des donnees.

2.1 Sites Web de reference pour SQL et MySQL

Une version complete de la syntaxe SQL DDL (Data Definition Language) se trouve,par exemple, a l’adresse :http://sqlpro.developpez.com/cours/sqlaz/ddl/

Pour consulter la syntaxe des instruction du SQL DML (Data Manipulation Language)consultez les adresses suivantes :http://sqlpro.developpez.com/cours/sqlaz/select/http://sqlpro.developpez.com/cours/sqlaz/dml/

Le logiciel MySQL implemente le langage SQL, mais ne respecte pas toujours lanorme SQL. Sur le site de reference de MySQL se trouvent les instructions/commandespour la definition des donnees (l’implementation du langage SQL DDL) :http://dev.mysql.com/doc/refman/5.0/fr/data-definition.htmlet pour la manipulation des donnees :http://dev.mysql.com/doc/refman/5.0/fr/data-manipulation.html

2.2 Le langage SQL DDL (Data Definition Language)

Le langage SQL DDL offre des commandes de definition et de modification des struc-tures permettant de definir (creer), de supprimer et de modifier une table, une colonne ouune contrainte.

2.2.1 Creation d’un schema

Une base de donnees est definie par son schema. Pour creer un schema vide (sanstables), le langage SQL propose l’instruction suivante :

23

CREATE SCHEMA nom_schema

ou nom schema est remplace par le nom de la nouvelle base de donnees.Sous MySQL la syntaxe pour creer le schema d’une base de donnees est la suivante :

CREATE DATABASE nom_schema

ou nom schema est remplace par le nom de la nouvelle base de donnees.

Exemple 18. Pour creer en MySQL la base de donnees client commande il fautecrire :

1 CREATE DATABASE client_commande

Cette operation produit une nouvelle BD, sans tables.

2.2.2 Creation d’une table (CREATE TABLE)Pour creer une table, le langage SQL propose l’instruction CREATE TABLE :

CREATE TABLE nom_table( nom_colonne type,nom_colonne type,...nom_colonne type )

Il faut specifier le nom de la nouvelle table, nom table, ainsi que la description de sescolonnes : pour chaque colonne il faut specifier son nom, nom colonne, et le type deses valeurs. Sur les colonnes on peut ajouter des contraintes de colonne :

– pour definir une colonne obligatoire, il faut ajouter NOT NULL apres sa definition ;– pour definir une cle primaire, il faut ajouter PRIMARY KEY ;– pour definir une cle etrangere, il faut ajouterFOREIGN KEY REFERENCES table cible (colonne).

Cette operation produit une table vide (c’est-a-dire sans lignes).

Les colonnes et leurs types

SQL offre divers types de donnees, dites de base, possible pour une colonne d’unetable. On citera les principaux :

– smallint : entier signe court ;– integer ou int : entier signe long ;– numeric(p,q) : nombre decimaux de p chiffres dont q apres le point decimal ;

si elle n’est pas mentionnee, la valeur de q est 0 ;– decimal(p,q) : nombre decimaux d’au moins p chiffres dont q apres le point

decimal ; si elle n’est pas mentionnee, la valeur de q est 0 ;– float(p) ou float : nombre en virgule flottante ;– character(p) ou char : chaıne de longueur fixe de p caracteres ;– character varying ou varchar(p) : chaıne de longueur variable de p ca-

racteres ;

24

– date : date (annee, mois et jour) ;– time : instant (heure, minute, seconde, millieme de seconde) ;

La norme SQL 3 (1999) a rajoute 3 types fondamentaux : booleen, CLOB et BLOB.– boolean : type de donnees valant vrai ou faux ;– les Binary Large Objects (BLOB) : sorte de contenants generiques pouvant accueillir

des chaınes de bits de longueur non-bornee telles que des images, sequences video,sequences sonores ou musicales. Les Character Large Objects (CLOB) sont simi-laires, mais consideres comme etant formes de caracteres ; ce type est utilise pourstocker des textes de taille importante.

Exemple 19. Pour creer dans la base de donnees client commande la table client,la commande MySQL est la suivante :

1 CREATE TABLE client2 ( NCLI char(8) NOT NULL,3 NOM char(18) NOT NULL,4 ADRESSE char(24) NOT NULL,5 LOCALITE char(20) NOT NULL,6 CAT char(2) DEFAULT NULL,7 COMPTE decimal(9,2) NOT NULL,8 PRIMARY KEY (NCLI) )

Pour creer la table detail, la commande MySQL est la suivante :

1 CREATE TABLE detail2 ( NCOM char(8) NOT NULL,3 NPRO char(10) NOT NULL,4 QCOM int(11) NOT NULL,5 PRIMARY KEY (NCOM,NPRO)6 FOREIGN KEY (NCOM) REFERENCES commande (NCOM),7 FOREIGN KEY (NPRO) REFERENCES produit (NPRO) )

2.2.3 Suppression d’une table (DROP)

Pour supprimer une table, le langage SQL propose l’instruction suivante :

DROP nom_table

Attention : Toutes les donnees ainsi que la structure de la table seront perdues a la suitede cette operation !

2.2.4 Modification du schema

La modification du schema d’une base de donnees implique le plus souvent des modi-fications de donnees. Par exemple, l’ajout d’une colonne a une table contenant des lignesest suivi de la modification de cette colonne pour chacune des lignes (mises a NULL ou ala valeur par defaut). Pour pouvoir etre appliquees, ces operations de modification doiventrespecter les contraintes d’integrite. Nous donnons quelques exemples de regles :

25

• Ajout d’une colonne. Si la colonne est facultative, l’operation s’effectue sans con-trainte. Si elle est obligatoire, alors la table doit etre vide ou la colonne doit etreaccompagnee d’une valeur par defaut.• Suppression d’une colonne. Cette colonne ne peut pas intervenir dans la compo-

sition d’une cle primaire ou d’une cle etrangere. Si necessaire, ces cles doiventd’abord etre modifiees ou supprimes.• Ajout d’une cle primaire. Si la table n’est pas vide, les lignes doivent respecter la

contrainte d’unicite.• Suppression d’une cle primaire. Cette suppression n’est pas soumise a des condi-

tions sur les donnees. Cependant, cette cle primaire ne doit pas etre referencee parune cle etrangere.• Ajout d’une cle etrangere. Si la table n’est pas vide, les lignes doivent respecter la

contrainte referentielle.

Attention ! A cause de toutes ces regles, la modification du schema d’une base dedonnees n’est pas une operation frequente et s’il faut le faire alors il faut prendre desprecautions.

2.3 Le langage SQL DML (Data Manipulation Language)Le langage SQL DML (Data Manipulation Language) comporte deux grandes classes

de fonctions : l’extraction de donnees et la modification de donnees.

2.3.1 Extraction de donneesL’extration 1 de donnees fait l’objet d’une seule commande : la requete select.Une requete select simple contient trois parties principales :

1. la clause select precise le nom des colonnes dont on veut recuperer les valeursdans le resultat de la requete,

2. la clause from indique la table ou les tables sur lesquelles portent la requete. Toutesles colonnes de la clause select doivent appartenir a une des tables de la clausefrom.

3. la clause where specifie les conditions de selection des valeurs du resultat de larequete.

L’execution d’une requete select produit un resultat qui est une table volatile carses lignes sont envoyees a l’ecran, mais cette table n’est pas creee dans la base de donnees.

2.3.2 Extraction simple (SELECT-FROM)La requete select la plus simple, appelee projection, n’a pas de clause where et

permet l’affichage de toutes les lignes d’une table, mais en ne montrant que certaines

1. Une donnee extraite reste dans la base de donnees, on en extrait une copie ! La commande deleteest utilisee pour extraire (effacer) une donnee.

26

colonnes. Sa forme generale est :

SELECT liste_colonnesFROM nom_table

Exemple 20. Pour la base de donnees client commande, la requete

1 SELECT NCLI, NOM, LOCALITE2 FROM client

affiche pour toutes les lignes de la table client seulement les valeurs des trois colonnesNCLI, NOM, et LOCALITE.Pour obtenir les valeurs de toutes les colonnes, la requete est :

1 SELECT *2 FROM client

2.3.3 Extraction de lignes selectionnees (SELECT-FROM-WHERE)

Une requete de selection contient dans la clause where des conditions qui permettentde ne selectionner que certaines lignes d’une table. Sa forme generale est :

SELECT liste_colonnesFROM nom_tableWHERE condition

Exemple 21. Pour la base de donnees client commande, la requete

1 SELECT NCLI, NOM2 FROM client3 WHERE LOCALITE = ’ Tou louse ’

n’affiche que les lignes de la table client dont la valeur de la colonne LOCALITE estegale a la chaıne de caracteres ’Toulouse’. De plus, seules les valeurs des colonnes NCLIet NOM seront affichees.

Conditions de selection

Dans la clause where, pour construire la condition de selection on dispose :– des noms des colonnes de la table nom table ;– des operateurs du §1.4.2 ;– des constantes :• numeriques et decimales, comme par exemple : 123, -0.003, 7.12 ;• chaınes de caracteres : valeurs entre ’ et ’ (exemple : ’Jean Mercier’) ;

la presence du caractere ’ dans la chaıne se represente par son redoublement(exemple : ’rue de l’’Ete’) ;• dates : ’2009-02-14’ (standard SQL 2) ; autres variantes selon les SGBD :’14-02-2009’, ’14/02/2009’.

27

Pour les expressions composees 2 l’usage des parantheses permet de former des condi-tions plus elaborees, comme par exemple :

1 SELECT NCLI, NOM2 FROM client3 WHERE COMPTE >0 AND (CAT = ’C1 ’ OR LOCALITE = ’ P a r i s ’)

Conditions de selection plus complexes

Une condition peut porter sur la presence de la valeur NULL :

1 CAT is null2 CAT is not null

ou sur l’appartenance a un ensemble :

1 CAT in ( ’C1 ’, ’C2 ’, ’C3 ’)2 LOCALITE not in ( ’ Tou louse ’, ’ Namur ’, ’ Breda ’)

ou encore sur la presence de certains caracteres dans une valeur :

1 CAT like ’ 1 ’2 ADDRESSE like ’%Neuve%’

Dans les deux dernieres conditions, le signe ’ ’ designe un caractere quelconque et ’%’designe toute suite de caracteres, eventuellement vide.

Lignes dupliquees dans le resultat

Pour eliminer les lignes en double dans le resultat d’une requete, on utilise la clausedistinct

1 SELECT distinct LOCALITE2 FROM client

Donnees extraites et donnees derivees

La clause select permet aussi de specifier des donnees calculees ou encore desconstantes. Dans l’exemple :

1 SELECT NPRO, ’ = ’, 0.196*PRIX*QSTOCK AS ” montan t TVA”2 FROM produit

le resultat de la requete sera un tableau des montants TVA des articles en stock.MySQL offre plusieurs fonctions permettant de deriver des valeurs a partir des valeurs descolonnes des lignes extraites. Pour les chaınes de caracteres on peut utiliser, par exemple,les fonctions : lower, upper, substring, trim 3.

2. voir sur http://dev.mysql.com/doc/refman/5.0/fr/operator-precedence.html, la priorite des operateurs en MySQL

3. voir http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

28

2.3.4 Ordre des lignes d’un resultat (clause ORDER BY)Il est possible d’imposer un ordre de presentation specifique lors de l’affichage des

lignes du resultat d’une requete en utilisant la clause order by :

SELECT liste_colonnesFROM nom_tableWHERE conditionORDER BY liste_colonnes ASC|DESC

Par default, le classement se fait par ordre ascendant des valeurs. On peut egalementspecifier explicitement un ordre ascendant (ASC) ou descendant (DESC).

Exemple 22. Pour la base de donnees client commande les lignes resultant de larequete

1 SELECT *2 FROM client3 WHERE CAT is not null4 ORDER BY LOCALITE

vont apparaıtre classees par ordre alphabetique croissant sur le noms des localites.On peut indiquer plusieurs criteres de tri :

1 SELECT *2 FROM client3 ORDER BY LOCALITE, CAT

Les clients vont apparaıtre classes par localite, puis dans chaque localite, classes parcategorie.

L’ordre des composants du critere de tri est important. La requete

1 SELECT *2 FROM client3 ORDER BY CAT, LOCALITE

affiche les clients classes par categorie, puis dans chaque categorie, classes par localite.

2.3.5 Les fonctions agregatives (ou statistiques)Il existe des fonctions predefinies qui donnent une valeur “agregee” calculee pour les

lignes selectionnees par la requete select :• count(*) compte le nombre de ligne trouvees,• count(nom colonne) compte le nombre de valeurs de la colonne,• avg(nom colonne) calcule la moyenne des valeurs de la colonne,• sum(nom colonne) calcule la somme des valeurs de la colonne,• min(nom colonne) calcule le minimum des valeurs de la colonne,• max(nom colonne) calcule le maximum des valeurs de la colonne.

Il est a noter que ces fonctions, a l’exception de la premiere (count), ne considerent queles valeurs non NULL de la colonne. En outre, chaque valeur est prise en compte, memesi elle apparaıt plusieurs fois.

29

Exemple 23. Pour la base de donnees client commande la requete1 SELECT count(*)2 FROM client

compte le nombre de clients, la requete1 SELECT count(NCLI)2 FROM commande

compte le nombre de commandes, la requete1 SELECT count(distinct NCLI)2 FROM commande

compte le nombre de clients ayant passe au moins une commande, et la requete1 SELECT sum(QSTOCK*PRIX)2 FROM produit3 WHERE LIBELLE like ’%SAPIN%’

calcule le montant total de produits de type sapin en stock.

Attention : La requete1 SELECT MAX(DATECOM)2 FROM commande

affiche bien la date de la derniere commande enregistree dans la table commande, maisla requete

1 SELECT MAX(DATECOM), NCOM2 FROM commande

est fausse car elle ne permet pas de recuperer le numero de cette derniere commande ! Lasolution est d’utiliser une sous-requete (voir aussi le §2.3.7) :

1 SELECT NCOM, DATECOM2 FROM commande3 WHERE DATECOM in (SELECT MAX(DATECOM)4 FROM commande)

2.3.6 Extraction de donnees de plusieurs tables (jointure)Pour extraire des donnees correlees, stockees dans deux tables, on utilise une jointure

(join en anglais), definie par une condition de jointure, specifiant la regle selon laquelleles lignes des tables sont reliees :

SELECT liste_colonnesFROM nom_table_E, nom_table_PWHERE col_FK_E = col_PK_PAND condition

Dans la clause FROM on donne la liste des noms des tables a relier.Dans la clause WHERE on donne la condition de jointure qui se presente sous la formed’une egalite entre les valeurs de deux colonnes : col FK E = col PK P, ou

30

i) la colonne col FK E est la cle etrangere de la table nom table E (table enfant),ii) la colonne col PK P est la cle primaire de la table nom table P (table parent),

etiii) la table nom table P est la table parent referencee dans la table nom table E

par la cle etrangere col FK E.Dans la clause WHERE, en plus de la condition de jointure (obligatoire !), on peut ajouterd’autres conditions de selection des valeurs dans la partie condition.

Exemple 24. Pour la base de donnees client commande, la requete

1 SELECT NCOM, DATECOM, NOM, LOCALITE2 FROM commande, client3 WHERE commande.NCLI = client.NCLI

affiche pour chaque commande de la table commande, le nom et la ville du client qui apasse cette commande (voir la figure 2.1).

FIGURE 2.1 – Le resultat de la requete de l’exemple 24.

Les valeurs des colonnes NCOM et DATECOM sont extraites de la table commande(table enfant) tandis que les valeurs des colonnes NOM et LOCALITE sont extraites de latable client (table parent). La colonne commande.NCLI est une cle etrangere 4 de latable commande et fait reference a la cle primaire client.NCLI de la table client.

Remarque 1 : Si les deux tables ont des colonnes qui ont le meme nom, il faut le-ver l’ambiguıte et preciser a quelle table appartient la colonne, en utilisant la syntaxesuivante :

nom_table.nom_colonne

Remarque 2 : L’ordre des noms des tables dans la clause FROM ainsi que l’ordre desconditions dans la clause WHERE n’a pas d’importance. La requete

1 SELECT NCOM, DATECOM, NOM, LOCALITE2 FROM client, commande3 WHERE client.NCLI = commande.NCLI

est la meme que la requete de l’exemple 24 et que la requete suivante

4. notee FK3 dans l’exemple 11, page 17

31

1 SELECT NCOM, DATECOM, NOM, LOCALITE2 FROM client, commande3 WHERE commande.NCLI = client.NCLI

Le resultat d’une jointure entre deux tables est obtenu comme suit :

1. On construit une table (fictive) en couplant chaque ligne de la premiere table avecchaque ligne de la seconde, sans prendre en compte la clause where. Si on lance larequete de l’exemple 24 sur la base de donnees client commande contenant leslignes donnees dans la figure 1.2, page 9, alors cette table fictive contient 9 colonnes(3 colonnes de la table commande, plus 6 colonnes de la table client), et 112lignes (112 = 7 x 16 : 7 lignes de la table commande, multiplie par 16 lignes de latable client).

2. On selectionne, parmi les lignes ainsi obtenues, celles qui verifient la condition dejointure. Pour l’exemple 24 on garde 7 lignes sur les 112.

3. On ne retient alors que les colonnes demandees. Quatre colonnes seront afficheespour l’exemple 24.

Par extension, la jointure de trois tables reclamera deux conditions de jointure :

SELECT liste_colonnesFROM nom_table_E, nom_table_P, nom_table_3WHERE col_FK_E = col_PK_PAND col_FK_E2 = col_PK_P2AND condition

La deuxieme condition de jointure, col FK E2 = col PK P2, specifie la regle pourrelier la table nom table 3 a la table nom table E ou a la table nom table P.

Exemple 25. Pour la base de donnees client commande, la requete

1 SELECT NOM, commande.NCOM, commande.DATECOM, detail.NPRO, detail.QCOM2 FROM client, commande, detail3 WHERE client.NCLI = commande.NCLI4 AND detail.NCOM = commande.NCOM

affiche pour chaque client et pour chaque commande qu’il a passe le numero de produitet la quantite commande.

Pour avoir aussi le libelle du produit il faut faire une requete avec la jointure desquatre tables en imposant trois conditions de jointure

1 SELECT client.NOM, commande.NCOM, commande.DATECOM, detail.NPRO,2 detail.QCOM, produit.LIBELLE3 FROM client, commande, detail, produit4 WHERE client.NCLI = commande.NCLI5 AND detail.NCOM = commande.NCOM6 AND detail.NPRO = produit.NPRO

32

Attention ! Une requete sans condition de jointure porte le nom de produit cartesien :chaque ligne de la premiere table est couplee avec chaque ligne de la seconde table.Si dans la requete de l’exemple 24 on oublie d’imposer la condition de jointure, alors larequete

1 SELECT NCOM, DATECOM, NOM, LOCALITE2 FROM commande, client

est le produit cartesien des deux tables.Si on lance cette requete sur la BD client commande contenant les lignes donneesdans la figure 1.2, page 9, alors les 7 lignes de la table commande seront reliees a chacunedes 16 lignes de la table client et le resultat de cette requete contient 112 lignes (112 =7×16), ce qui, en general, n’a pas trop d’utilite.

2.3.7 Les sous-requetesUne sous-requete est une instruction select, cf. §2.3.3, qui intervient dans la clause

where d’une autre instruction select.

Exemple 26. Pour la base de donnees client commande, la requete1 SELECT NCLI2 FROM client3 WHERE LOCALITE = ’ Namur ’

donne les identifiants des clients qui habitent a Namur. Elle est utilisee comme sous-requete dans la requete

1 SELECT NCOM, DATECOM2 FROM commande3 WHERE NCLI in (SELECT NCLI4 FROM client5 WHERE LOCALITE = ’ Namur ’)

pour retrouver les commandes des clients qui habitent a Namur.

Exemple 27. Pour la base de donnees client commande, la requete1 SELECT NOM, COMPTE2 FROM client3 WHERE COMPTE in (SELECT max(COMPTE)4 FROM client)

affiche le nom et le compte du client qui a la plus grande valeur dans son compte.

Une sous-requete peut elle-meme contenir une sous-requete.

Exemple 28. Pour la base de donnees client commande, la requete1 SELECT NPRO2 FROM detail3 WHERE NCOM in (SELECT NCOM4 FROM commande5 WHERE NCLI in (SELECT NCLI6 FROM client7 WHERE LOCALITE = ’ Namur ’))

33

donne les references des produits des commandes des clients qui habitent a Namur.

Si la sous-requete renvoie une seule ligne, il est permis d’utiliser les operateurs decomparaison classiques ; par exemple :

1 SELECT *2 FROM client3 WHERE COMPTE > (SELECT COMPTE4 FROM client5 WHERE NCLI = ’ C400 ’)

2.3.8 Sous-requete ou jointure ?La jointure et la sous-requete permettent d’exprimer des conditions d’association entre

lignes, basees le plus souvent sur l’egalite des valeurs d’une cle etrangere avec celle d’unecle primaire.

Exemple 29. La requete de l’exemple 26

1 SELECT NCOM, DATECOM2 FROM commande3 WHERE NCLI in (SELECT NCLI4 FROM client5 WHERE LOCALITE = ’ Namur ’)

peut s’ecrire egalement sous la forme d’une jointure :

1 SELECT NCOM, DATECOM2 FROM commande, client3 WHERE commande.NCLI = client.NCLI4 AND LOCALITE = ’ Namur ’

Exemple 30. La requete de l’exemple 28

1 SELECT NPRO2 FROM detail3 WHERE NCOM in (SELECT NCOM4 FROM commande5 WHERE NCLI in (SELECT NCLI6 FROM client7 WHERE LOCALITE = ’ Namur ’))

peut s’ecrire egalement sous la forme d’une jointure :

1 SELECT NPRO2 FROM detail,commande, client3 WHERE commande.NCOM = detail.NCOM4 AND commande.NCLI = client.NCLI5 AND LOCALITE = ’ Namur ’

Attention : Les structures de select emboıtes qui utilisent des conditions de non-association (not in) ne peuvent pas s’exprimer par une jointure !

34

Exemple 31. La requete

1 SELECT NCOM, DATECOM, NCLI2 FROM commande3 WHERE NCOM not in (SELECT NCOM4 FROM detail5 WHERE NPRO = ’PA60 ’)

designe les commandes qui ne portent pas sur le produit PA60. Cette requete n’est pasequivalente a la requete

1 SELECT distinct commande.NCOM, DATECOM, NCLI2 FROM commande, detail3 WHERE commande.NCOM = detail.NCOM4 AND NPRO <> ’PA60 ’

qui designe les commandes qui portent au moins sur un produit different de PA60 (maisqui par ailleurs peuvent egalement porter sur le produit PA60).

2.3.9 Ajout de lignes dans une table (INSERT)Pour ajouter une ligne dans une table on utilise l’instruction INSERT :

INSERT INTO nom_table (liste_colonnes)VALUES (listes_valeurs)

Exemple 32. Pour la base de donnees client commande la requete

1 INSERT INTO detail (NCOM, NPRO, QCOM)2 VALUES ( ’ 30185 ’, ’PA45 ’, 12)

ajoute une ligne dans la table detail.

L’ordre des valeurs est le meme que celui des colonnes. Toute colonne non specifieedans la liste des colonnes prend la valeur NULL ou la valeur par defaut si celle-ci a etedeclaree comme propriete de la colonne. Toute colonne obligatoire (not null) doitrecevoir une valeur, sauf si on lui assigne une valeur par defaut lors de sa declaration.

Chaque valeur peut etre exprimee sous la forme d’une constante, ou plus generalementde toute expression dont l’evaluation donne une valeur de meme type que celui de lacolonne.

Dans tous les cas, les donnees inserees doivent respecter les contraintes d’integrite(unicite, integrite referentielle, colonnes obligatoires) attachees a la table dans laquelleles nouvelles lignes sont inserees, cf. §1.6.

2.3.10 Suppression de lignes (DELETE)L’instruction pour supprimer des lignes dans une table est DELETE :

DELETE FROM nom_tableWHERE condition

35

ou le format de la condition de la clause where est le meme que celui de l’instructionselect du §2.3.3.

Apres l’operation, la base de donnees doit etre dans un etat qui respecte toutes lescontraintes d’integrite (unicite, integrite referentielle, colonnes obligatoires) auxquelleselle est soumise et, en particulier, les contraintes referentielles.

2.3.11 Modification de lignes (UPDATE)L’instruction pour modifier des valeurs dans les lignes d’une table est UPDATE :

UPDATE nom_tableSET nom_colonne = nouvelle_valeur,

...nom_colonne = nouvelle_valeur,

WHERE condition

La modification sera effectuee sur toutes les lignes qui verifient la condition de selection.Les nouvelles valeurs peuvent etre obtenues par une expression arithmetique.

Exemple 33. Pour la base de donnees client commande, la requete

1 UPDATE produit2 SET PRIX = PRIX * 1.053 WHERE LIBELLE like ’%SAPIN%’

enregistre une augmentation de prix de 5% pour les produits de type sapin.

36

Chapitre 3

Prise en main de l’environnement dedeveloppement

Pour realiser les exercices proposes dans les seances de travaux diriges, il faut :

1. organiser son repertoire de travail selon la structure donnee dans le paragraphe 3.2 ;

2. disposer d’un editeur de texte pour enregistrer les solutions : Notepad++ 1 (voir leparagraphe 3.3) ;

3. disposer d’un Systeme de Gestion de Bases de Donnees (SGBD), comme MySQL 2

(voir le paragraphe 3.4).

3.1 Pre-requis

Savoir utiliser un editeur de texte basique (voir le paragraphe 3.3).Savoir utiliser le poste de travail (structure arborescente des repertoires, compres-

sion/decompression de fichiers, taper au clavier). Ressource en ligne pour l’autoforma-tion : http://www.agroparistech.fr/mmip/mc/agro/poste.html.

Savoir utiliser un navigateur pour Internet. Ressource en ligne pour l’autoformation :http://www.agroparistech.fr/mmip/mc/agro/firefox.html.

Savoir utiliser la messagerie electronique. Ressource en ligne pour l’autoformation :http://www.agroparistech.fr/mmip/mc/agro/messag.html.

Pas de pre-requis en bases de donnees.

3.2 Organisation du repertoire de travail

Votre repertoire de travail doit etre structure selon l’arborescence suivante :

1. Notepad++ peut etre telecharge sur http://sourceforge.net/projects/notepad-plus/

2. http://www.mysql.fr/

37

N: \wwwBD

TDExemples

de telle sorte que : tous les fichiers concernant le cours de Systeme d’information serontdans un sous repertoire www ; tous les fichiers concernant la premiere partie sur les basesde donnees seront dans un sous repertoire www\BD ; tous les fichiers concernant les exer-cices des TDs seront dans un sous repertoire www\BD\TD, etc.

Pour creer cette structure de repertoires, il faut :

1. telecharger l’archive archive BD.zip qui se trouve sur le site Web du courshttp://www.agroparistech.fr/Systeme-d-Information.html

2. extraire tous les repertoires et fichiers de cette archive dans votre repertoire N: \

3.3 L’editeur de texte Notepad++Notepad++ 3 est un editeur de texte qui integre la coloration syntaxique 4 de code

source pour plusieurs langages et fichiers (HTML, PHP, Matlab, etc).Le projet Notepad++ est sous licence GNU GPL 5.

3.3.1 Demarrage de Notepad++Pour lancer Notepad++ sur votre poste de travail, il faut choisir dans le menu Demarrer

de Windows l’option Tous les programmes / Internet + Comm / Notepad++.

3.3.2 Creer un nouveau fichierPour creer un nouveau fichier en utilisant Notepad++ :

1. choisir dans le menu de Notepad++ l’option Fichier / Nouveau2. enregistrer des le debut le fichier, dans le bon repertoire (cf. au paragraphe 3.2),

avec une extension .sql en utilisant l’option Fichier / Enregister sous ...Attention ! La presence de l’extension dans le nom du fichier permet de specifier le lan-gage utilise et de beneficier de la coloration syntaxique qui y est associee.

3.3.3 Ouvrir un fichier existantPour ouvrir dans Notepad++ un fichier existant, il faut choisir dans le menu l’optionFichier / Ouvrir ..., et retrouver ensuite le fichier sur le disque.

3. Notepad++ peut etre telecharge sur http://sourceforge.net/projects/notepad-plus/

4. les mots cles du langage ont une couleur specifique5. GNU General Public License est la licence de logiciel libre la plus utilisee

38

3.4 MySQL et phpMyAdmin

MySQL 6 est un Systeme de Gestion de Bases de Donnees (SGBD) qui utilise lelangage SQL. MySQL est un systeme libre (open source) qui fonctionne sur plus de 20plateformes incluant Linux, Windows, Mac OS, etc.

phpMyAdmin est un logiciel gratuit, ecrit en PHP et integre a EasyPHP 7, utilise pouradministrer une base de donnees MySQL a distance via un navigateur Web. Les operationsMySQL les plus utilisees (gestion de bases de donnees, tables, champs, relations, etc.)sont disponibles a travers une interface utilisateur ; des commandes SQL peuvent aussietre executees.

3.4.1 Utiliser MySQL avec phpMyAdmin

1. Lancez EasyPHP sur votre poste de travail (dans le menu Demarrer de Windowsl’option Tous les programmes /EasyPHP).

2. Dans la barre situee sur la derniere ligne de votre ecran, a droite, retrouvez l’iconed’EasyPHP et cliquez dessus avec le bouton droit de la souris. Un menu apparaıt,choisissez “Administration” ; une fenetre doit s’ouvrir dans un navigateur Web (In-ternet Explorer, Firefox, etc).

3. Dans la fenetre du navigateur, dans la categorie “Modules”, sur la ligne “Adminis-tration MySQL” il faut suivre le lien (ou bouton) “ouvrir”.

3.4.2 Operations sur une base de donnees avec MySQL

Les operations possibles sur une base de donnees se trouvent dans le menu horizontalde phpMyAdmin, dans l’onglet “Base de donnees” :

– creer une nouvelle base de donnees,– importer une base de donnees a partir d’un fichier de commandes SQL,– exporter une base de donnees existante dans un fichier de commandes SQL, etc.

Importer une base de donnees a partir d’un fichier de commandes SQL

1. Dans le menu horizontal de phpMyAdmin il faut choisir l’onglet “Importer” ;

2. Dans le groupe “Fichier a importer”, precisez l’emplacement du fichier qui contientla base de donnees ; on peut utiliser l’option “Parcourir” avec le bouton “Choisissezun fichier”.

3. Dans le groupe “Format”, selectionnez le format “SQL”.

4. Executer.

6. http ://www.mysql.fr/7. EasyPHP peut etre telecharge sur http://www.easyphp.org/

39

Exporter une base de donnees

1. Pour exporter toute une base de donnees (toutes les tables) il faut se positionnerdans l’onglet “Base de donnees” et ensuite cocher la base de donnees a exporter ;

2. Dans le menu horizontal de phpMyAdmin il faut choisir l’onglet “Exporter”.

3. Executer.

4. Enregistrer le fichier de commandes SQL genere.

3.4.3 Operations sur les tables d’une base de donneesDans phpMyAdmin, une fois qu’une base de donnees est selectionnee, on trouve dans

la colonne de gauche la liste de toutes ses tables et dans les differents onglets du menuhorizontal toutes les operations qui peuvent etre appliques. L’onglet “Concepteur” permetde visualiser et de modifier le schema de la base de donnee selectionnee.

3.4.4 Operations sur une seule tableDans phpMyAdmin, une fois qu’une des tables d’une base de donnees est selectionnee,

on trouve dans les differents onglets du menu horizontal toutes les operations qui peuventetre appliques :

– afficher les lignes de la table (onglet “Afficher”),– afficher la structure de la table (onglet “Structure”),– consulter le contenu de la table (onglet “Rechercher”),– inserer des nouvelles lignes (onglet “Inserer”), etc.

3.5 Ressources en ligneSites sur l’utilisation de phpMyAdmin :

http://www.siteduzero.com/tutoriel-3-14496-phpmyadmin.html

http://www.6ma.fr/tuto/phpmyadmin+pour+gerer+vos+bases+mysql-96

Sites sur le langage SQL :http://dev.mysql.com/doc/refman/5.6/en/select.html

http://cyberzoide.developpez.com/php4/mysql/

40

Chapitre 4

TD 1 : Travaux diriges en BD, seance 1– Requetes simples sur une table

Pour effectuer ces exercices, vous devez avoir lu, avant de venir en TD, le chapitre 1(pages 7 a 23), ainsi que les paragraphes de 2.3.1 a 2.3.4 (pages 26 a 29).

4.1 Premiers pas

En premier lieu, il vous faut recuperer l’archive archive BD.zip qui se trouve a l’adressehttp://www.agroparistech.fr/Systeme-d-Information.html et suivre la de-marche decrite dans le §3.2.Lancez MySQL avec phpMyAdmin 1 et importez 2 la base de donnees client commande 3,qui se trouve dans le fichier N: \www\BD\Exemples\client commande.sql.

Questions

En utilisant pour chaque table l’onglet ’Afficher’ (ou ’Browse’) de l’interface de MySQLsous phpMyAdmin, et sans faire de requetes en SQL, repondez aux questions suivantes :

1. Combien de lignes existent dans chaque table ?

2. Quel est le prix du produit ’POINTE ACIER 60 (1K)’ ? Combien en reste-t-il enstock ?

3. Quels sont les noms des clients de categorie ’B1’ ?

4. Pour le client ’FERARD’, combien a-t-il passe de commandes ?

5. Pour le client ’VANBIST’, combien a-t-il passe de commandes ?

6. Quel(s) produits(s) a commande le client ’VANBIST’ et en quelle quantite ?

1. voir le paragraphe 3.4.1, page 392. voir le paragraphe 3.4.2, page 393. voir le paragraphe 1.1, page 8

41

Question

En utilisant pour chaque table l’onglet ’Structure’ de l’interface de MySQL sous php-MyAdmin, retrouvez pour chacune des colonnes ADRESSE, DATECOM, QSTOCK,COMPTE, NCOM et NPRO quel est son type et precisez dans quelle table elle se trouve.

champs table typeADRESSE

DATECOM

QSTOCK

COMPTE

NCOM

NPRO

4.2 Exercice 1a : requetes simples sur la base de donneesclient commande

On suppose que phpMyAdmin/MySQL est lance.En utilisant l’editeur de texte Notepad++ 4, ouvrez le fichier exo 1 req simples.sqlqui se trouve dans votre repertoire www\BD\TD.

4.2.1 Exemples de requetesOn suppose que la BD client commande est selectionnee, que son schema est celuide la figure 1.1, page 8, et que la version courante de la BD est celle de la figure 1.2.

Pour chaque requete donnee ci-dessousi) expliquez ce qu’elle fait et trouvez la reponse donnee par MyQSL, sans l’executer ;ii) executez la requete 5 et comparez les resultats obtenus avec vos reponses.

Requete 1.1

1 SELECT *2 FROM produit3 WHERE LIBELLE LIKE ’%ACIER%’4 ORDER BY PRIX

4. voir le paragraphe 3.3, page 385. Copiez la requete a partir du fichier (CTRL+C dans Notepad++), et utilisez l’onglet ’SQL’ dans

MySQL pour la coller (CTRL+V) et l’executer.

42

Requete 1.2

1 SELECT NCOM, DATECOM2 FROM commande3 WHERE NCLI = ’ C400 ’4 AND DATECOM < ’2009−01−01 ’

Requete 1.3

1 SELECT NOM, ADRESSE, COMPTE2 FROM client3 WHERE COMPTE > 04 AND (CAT = ’C1 ’ OR LOCALITE = ’ P a r i s ’)

Requete 1.4

1 SELECT NOM, ADRESSE, COMPTE2 FROM client3 WHERE COMPTE > 0 OR CAT = ’C1 ’ AND LOCALITE = ’ P a r i s ’

Q iii) En supposant maintenant que le contenu des tables n’est pas connu, exprimez leresultat de ces requetes “en francais” (voir les exemples 20 et 21, page 27).

Question 1.1

Dans l’expressionCOMPTE > 0 OR CAT = ’C1’ AND LOCALITE = ’Paris’

dans quel ordre sont evalues les operateurs logiques OR et AND ?

R1 : ((COMPTE > 0 OR CAT = ’C1’) AND LOCALITE = ’Paris’)ou

R2 : (COMPTE > 0 OR (CAT = ’C1’ AND LOCALITE = ’Paris’))Justifiez votre reponse.

4.2.2 Requetes generees par l’interface de phpMyAdmin/MySQLRepondez aux questions suivantes en vous aidant de l’interface de MySQL sous php-MyAdmin : apres avoir identifie et selectionne la table appropriee, utilisez l’onglet ’Re-cherche’ (’Search’) avec ’Options’. Enregistrez les requetes generees sous phpMyAdmindans le fichier exo 1 req simples.sql.

Questions

Q.1.2. Affichez la liste des clients dont le compte est negatif et qui n’habitent pas a Namur.

Q.1.3. Affichez la liste des produits disponibles (nom, prix, quantite) par ordre alphabetique.

43

Attention a la syntaxe MySQL : Il faut faire la difference entre ’ (le delimiteur pourles chaınes de caracteres) et ‘ (le delimiteur pour les noms des tables, colonnes, etc.).

Exemples de chaınes de caracteres : ’2009-01-01’, ’C400’, ’Paris’.Exemples de noms de tables : ‘client‘, ‘client commande‘.‘produit‘,

‘parcelle‘, ‘gie agricole‘.‘agriculteur‘.Exemples de noms de colonnes : ‘LIBELLE‘, ‘QSTOCK‘, ‘client‘.‘NOM‘,

‘Emp Nom‘, ‘intervention‘.‘Int Debut‘.Le delimiteur ’ pour les chaınes de caracteres est obligatoire !Le delimiteur ‘ pour les noms des tables et des champs peut etre omis s’il n’y a pasdes espaces dans ces noms. Pour faciliter la lecture, ce delimiteur n’apparaıt pas dans cedocument.

4.2.3 Requetes en SQL

Utilisez l’onglet ’SQL’ pour ecrire les requetes SQL qui repondent aux questions sui-vantes. Enregistrez ces requetes dans le fichier exo 1 req simples.sql.

Questions

Q.1.4. Affichez en ordre alphabetique la liste des localites ou habitent les clients.

Q.1.5. Affichez les commandes passees pendant les dix derniers jours de l’annee 2008.

Q.1.6. Affichez le stock de chaque produit de type sapin.

Q.1.7. Affichez les produits dont le prix est inferieur a 200 et le stock est superieur a 150.

Q.1.8. Affichez le numero, le nom et la localite des clients de categorie B1 n’habitant pasa Paris.

4.3 Exercice 1b : requetes sur la BD gie agricole

Importez 6 la base de donnees gie agricole qui se trouve dans le fichierN: \www\BD\Exemples\gie agricole.sql.Utilisez l’onglet ’SQL’ pour ecrire les requetes SQL qui repondent aux questions sui-vantes. Enregistrez ces requetes dans le fichier exo 1 req simples.sql.

Questions

Q.1.9. Affichez en ordre alphabetique la liste des noms des agriculteurs.

Q.1.10. Affichez la liste des noms des parcelles dont la superficie est superieure a 500.

Q.1.11. Affichez toutes les informations concernant les parcelles situees a Arith dont lasuperficie est superieure a 200 et inferieure a 500.

6. voir le paragraphe 3.4.2, page 39

44

4.4 Pour terminer cet exercice– Verifiez que vous avez bien enregistre vos requetes dans le fichier exo 1 reqsimples.sql.

– Mettez a jour la date (ligne 4) et l’auteur (ligne 5) du fichier.– Enregistrez ce fichier dans votre repertoire www\BD\TD.

4.5 Questions de revision

4.5.1 Q.R.1

On suppose que la base de donnees gie agricole (cf. figure 1.3) est selectionnee.Expliquez ce que fait la requete suivante et trouvez la reponse donnee par MySQL sur laversion courante de la BD (cf. figure 1.4) :

1 SELECT * FROM parcelle2 WHERE Par_Lieu LIKE ’%Mont%’ AND Par_Superficie >300

Reponse

4.5.2 Q.R.2

On suppose que la base de donnees client commande (cf. figure 1.1) est selection-nee. Expliquez ce que font les requetes suivantes et trouvez les reponses donnees parMySQL sur la version courante de la BD (cf. figure 1.2) :

1 SELECT LIBELLE, PRIX, QSTOCK FROM produit2 WHERE PRIX < 100 AND QSTOCK > 0

1 SELECT LIBELLE, PRIX, QSTOCK FROM produit2 WHERE PRIX < 100 OR QSTOCK > 0

Reponse

4.5.3 Q.R.3

Quelle est la syntaxe generale d’une requete simple d’extraction ?Reponse

45

Chapitre 5

TD 2 : Travaux diriges en BD, seance 2– Requetes avec jointures entre tables

Pour effectuer ces exercices, vous devez avoir lu, avant de venir en TD, les para-graphes de 2.3.5 a 2.3.8 (pages de 29 a 34).

5.1 Exercice 2a : requetes sur la BD client commande

On suppose que phpMyAdmin/MySQL est lance.En utilisant l’editeur de texte Notepad++ 1, ouvrez le fichier exo 2 req jointure.sqlqui se trouve dans votre repertoire www\BD\TD.

5.1.1 Exemples de requetesOn suppose que la BD client commande est selectionnee, que son schema est celuide la figure 1.1, page 8, et que la version courante de la BD est celle de la figure 1.2.

Pour chaque requete donnee ci-dessousi) expliquez ce qu’elle fait et trouvez la reponse donnee par MyQSL, sans l’executer ;ii) executez la requete 2 et comparez les resultats obtenus avec vos reponses.

Attention : Les requetes peuvent etre incompletes ou fausses !

Requete 2.1

1 SELECT NOM, NCOM2 FROM client, commande3 WHERE client.NCLI = commande.NCLI4 AND LOCALITE = ’ Tou louse ’

1. voir le paragraphe 3.3, page 382. Copiez la requete a partir du fichier (CTRL+C dans Notepad++), et utilisez l’onglet ’SQL’ dans

MySQL pour la coller (CTRL+V) et l’executer.

46

Requete 2.2

1 SELECT LIBELLE, PRIX, QCOM2 FROM detail, produit3 WHERE detail.NPRO = produit.NPRO4 AND NCOM = ’ 30188 ’

Requete 2.3

1 SELECT NOM, commande.NCOM, detail.NCOM, LIBELLE, PRIX, QCOM2 FROM client, commande, detail, produit3 WHERE client.NCLI = commande.NCLI4 AND detail.NPRO = produit.NPRO5 AND commande.NCOM = ’ 30188 ’

Q iii) En supposant maintenant que le contenu des tables n’est pas connu, exprimez leresultat de ces requetes “en francais” (voir les exemples 20 et 21, page 27).

5.1.2 Requetes en SQL

Apres avoir identifie les tables a consulter et les conditions de jointure necessaires, uti-lisez l’onglet ’SQL’ pour ecrire les requetes SQL qui repondent aux questions suivantes.Enregistrez ces requetes dans le fichier exo 2 req jointure.sql.

Questions

Q.2.1. Affichez tous les produits commandes (nom, prix, quantite) par le client no ’B512’.

Q.2.2. Affichez tous les produits (nom, prix, quantite) commandes par le client ’FERARD’,par ordre croissant de no de commande puis par ordre alphabetique sur le nom deproduit.

Q.2.3. Quels clients ont commande en 2009 ?

Q.2.4. Quels sont les noms des produits qui ont ete commandes les dix derniers jours del’annee 2008 ?

Q.2.5. Affichez tous les produits commandes (nom, prix, quantite) par le client ’MER-CIER’.

Q.2.6. Quels sont les numeros des produits qui ont ete commandes a la fois le 23/12/2008et le 2/01/2009 ?

Q.2.7. Quels sont les numeros des produits qui ont ete commandes le 2/01/2009, mais pasle 23/12/2008 ? Et quels sont les noms de ces produits ?

47

5.2 Exercice 2b : requetes sur la BD gie agricole

On suppose que phpMyAdmin/MySQL est lance et que la BD gie agricole estselectionnee.Apres avoir identifie les tables a utiliser et les conditions de jointure necessaires, utili-sez l’onglet ’SQL’ pour ecrire les requetes SQL qui repondent aux questions suivantes.Enregistrez ces requetes dans le fichier exo 2 req jointure.sql.

Questions

Q.2.8. Affichez la liste de toutes les parcelles avec le nom de leurs proprietaires.

Q.2.9. Afficher pour chaque intervention le nom de la parcelle concernee.

Q.2.10. Afficher pour chaque intervention le nom de la parcelle concernee et le nom del’employe.

Pour terminer cet exercice

– Verifiez que vous avez bien enregistre vos requetes dans le fichier exo 2 reqjointure.sql.

– Mettez a jour la date (ligne 4) et l’auteur (ligne 5) du fichier .– Enregistrez ce fichier dans votre repertoire www\BD\TD.

5.3 Exercice 3 : fonctions agregativesOn suppose que phpMyAdmin/MySQL est lance.En utilisant l’editeur de texte Notepad++ 3, ouvrez le fichierexo 3 req fonctions.sql qui se trouve dans votre repertoire www\BD\TD.

5.3.1 Exemples de requetes sur la BD client commande

On suppose que la BD client commande est selectionnee, que son schema est celuide la figure 1.1, page 8, et que la version courante de la BD est celle de la figure 1.2.

Pour chaque requete donnee ci-dessousi) expliquez ce qu’elle fait et trouvez la reponse donnee par MyQSL, sans l’executer ;ii) executez la requete 4 et comparez les resultats obtenus avec vos reponses.

Requete 3.1

1 SELECT nom, max(COMPTE)2 FROM client

3. voir le paragraphe 3.3, page 384. Copiez la requete a partir du fichier (CTRL+C dans Notepad++), et utilisez l’onglet ’SQL’ dans

MySQL pour la coller (CTRL+V) et l’executer.

48

Requete 3.2

1 SELECT count(distinct commande.NCOM)2 FROM commande, detail, produit3 WHERE commande.NCOM = detail.NCOM4 AND detail.NPRO = produit.NPRO5 AND LIBELLE like ’%ACIER%’

Requete 3.3

1 SELECT sum(QCOM*PRIX) as MONTANT2 FROM detail, produit3 WHERE detail.NPRO = produit.NPRO4 AND LIBELLE like ’%ACIER%’

Requete 3.4

1 SELECT LIBELLE, PRIX as PrixUnitaire, QCOM, QCOM*PRIX as SousTotal2 FROM detail, produit3 WHERE detail.NPRO = produit.NPRO4 AND NCOM = ’ 30179 ’

Q iii) En supposant maintenant que le contenu des tables n’est pas connu, exprimez leresultat de ces requetes “en francais” (voir les exemples 20 et 21, page 27).

5.3.2 Exercice 3a : fonctions agregatives sur la BD client commande

Utilisez l’onglet ’SQL’ pour ecrire les requetes SQL qui repondent aux questions sui-vantes. Enregistrez ces requetes dans le fichier exo 3 req fonctions.sql.

Questions

Q.3.1 Calculez le montant commande des produits de type sapin.

Q.3.2 Calculez le nombre de produits (references) commandes de type sapin.

Q.3.3 Calculez le nombre d’unites de produits commandes de type sapin.

Q.3.4 Quel est le produit dont la quantite en stock est la plus faible ?

5.3.3 Exercice 3b : fonctions agregatives sur la BD gie agricole

On suppose que phpMyAdmin/MySQL est lance et que la BD gie agricole estselectionnee.Utilisez l’onglet ’SQL’ pour ecrire les requetes SQL qui repondent aux questions sui-vantes. Enregistrez ces requetes dans le fichier exo 3 req fonctions.sql.

49

Questions

Q.3.5 Calculez le nombre de proprietaires de parcelles.

Q.3.6 Calculez la superficies totales des parcelles.

Q.3.7 Affichez le nom de la plus grande parcelle.

5.4 Questions de revision

5.4.1 Q.R.4On suppose que la base de donnees gie agricole (cf. figure 1.3) est selectionnee.

Expliquez ce que fait la requete suivante et trouvez la reponse donnee par MySQL sur laversion courante de la BD (cf. figure 1.4) :

1 SELECT Par_Nom FROM parcelle WHERE Par_Superficie IN2 (SELECT min(Par_Superficie) FROM parcelle)

Reponse

5.4.2 Q.R.5On suppose que la base de donnees client commande (cf. figure 1.1) est selection-

nee. Quelle requete parmi celles donnees ci-dessous calcule le montant total TTC desproduits en stock ?

1 SELECT sum(QSTOCK*PRIX) FROM produit2 SELECT count(QSTOCK) FROM produit3 SELECT count(PRIX) FROM produit4 SELECT sum(PRIX*1.196) FROM produit5 SELECT max(QSTOCK*PRIX*1.196) FROM produit

Reponse

50

5.4.3 Q.R.6Expliquez ce que font les autres requetes du §5.4.2 et trouvez les reponses donnees

par MySQL sur la version courante de la BD (cf. figure 1.2).Reponse

5.4.4 Q.R.7Quelle est la syntaxe generale d’une requete de jointure sur 2 tables ?

Reponse

Quelle est la syntaxe generale d’une requete de jointure sur 3 tables ?Reponse

51

Chapitre 6

TD 3 : Travaux diriges en BD, seance 3– Requetes de mise a jour

Pour effectuer ces exercices, vous devez avoir lu, avant de venir en TD, le §1.5.1, le§1.6, ainsi que les paragraphes de 2.3.9 a 2.2.4 (pages de 35 a 25).

6.1 Exercice 4 : requetes sur la BD client commande

On suppose que phpMyAdmin/MySQL est lance.En utilisant l’editeur de texte Notepad++ 1, ouvrez le fichier exo 1 req simples.sqlqui se trouve dans votre repertoire www\BD\TD.

6.1.1 Exemples de requetesOn suppose que la BD client commande est selectionnee, que son schema est celuide la figure 1.1, page 8, et que la version courante de la BD est celle de la figure 1.2.

Pour chaque requete donnee ci-dessousi) expliquez ce qu’elle fait et trouvez la reponse donnee par MyQSL, sans l’executer ;ii) executez la requete 2 et comparez les resultats obtenus avec vos reponses.

ATTENTION! Le contenu de la base de donnees sera modifie !

Requete 4.1

1 UPDATE produit2 SET QSTOCK = QSTOCK - 103 WHERE NPRO = ’ PH222 ’

1. voir le paragraphe 3.3, page 382. Copiez la requete a partir du fichier (CTRL+C dans Notepad++), et utilisez l’onglet ’SQL’ dans

MySQL pour la coller (CTRL+V) et l’executer.

52

Requete 4.2

1 UPDATE client2 SET CAT = ’A1 ’3 WHERE CAT IS NULL

Requete 4.3

1 INSERT INTO produit ( NPRO, LIBELLE, PRIX, QSTOCK)2 VALUES ( ’ PH122 ’, ’PL HETRE 100 x20x2 ’, ’ 120 ’, ’ 500 ’)

Requete 4.4

1 DELETE FROM detail2 WHERE NCOM = ’ 30188 ’

Requete 4.5

1 DELETE FROM commande2 WHERE NCOM = ’ 30188 ’

Q iii) En supposant maintenant que le contenu des tables n’est pas connu, exprimez leresultat de ces requetes “en francais” (voir les exemples 20 et 21, page 27).

6.1.2 Requetes generees par l’interface de phpMyAdmin/MySQLUtilisez l’interface de phpMyAdmin/MySQL (l’onglet ’Inserer’ ou le bouton ’Modifier’)pour repondre aux questions donnees ci-dessous et enregistrez les requetes generees dansle fichier exo 4 req maj.sql.Remarque : pour les informations qui ne sont pas precisees, proposez des solutions.

Questions

Q.4.1. Ajoutez un nouveau client, nomme ’FRANCK’, qui habite a Lille, au 14 avenue duReclus (son numero de client est ’W100’ et son compte est 0.0).

Q.4.2. Enregistrez une premiere commande du client ’FRANCK’ : 10 unites du produit’PL. HETRE 200x20x2’ (le numero de sa commande est 30189).

Q.4.3. Mettez a jour le stock du produit ’PL. HETRE 200x20x2’.

Q.4.4. Mettez a jour toutes les tables concernees pour enregistrer l’operation suivante : Leclient ’FRANCK’ achete aussi 25 unites du produit ’POINTE ACIER 60 (1K)’ surla meme commande 30189.

Q.4.5. Quelle est la liste des mises a jour a faire pour enregistrer une nouvelle commandepour un client ? Completez le tableau ci-dessous.

53

type de maj table colonne(s)INSERT/UPDATE/DELETE

UPDATE client COMPTE

6.1.3 Requetes en SQLUtilisez l’onglet ’SQL’ pour ecrire les requetes SQL qui repondent aux questions sui-vantes. Sauvegardez ces requetes dans le fichier exo 4 req maj.sql

Questions

Q.4.6. Mettez a jour le compte du client ’FRANCK’ : sa commande est de 2300 et il apaye 2500.

Q.4.7. Ajoutez un nouveau produit, ’PL CHENE 200x20x2’, dont le stock initial est de500 unites et le prix a l’unite est 120.

Q.4.8. Corrigez la quantite commandee du produit ’PA60’ dans la commande ’30185’ :elle est de 10 unites.

Q.4.9. Le client ’FERARD’ ajoute a sa commande du 2/01/2009 cinq unites du produit’PL. HETRE 200x20x2’.

Question

Est-ce que la Requete 4.5 peut etre executee avant la Requete 4.4 (cf §6.1.1) ?1 DELETE FROM commande -- Requete 4.52 WHERE NCOM = ’ 30188 ’;3 DELETE FROM detail -- Requete 4.44 WHERE NCOM = ’ 30188 ’

Justifiez votre reponse :

Si vous avez execute les requetes 4.4 et 4.5 du §6.1.1, avant de faire ce test il fautcompleter la BD avec les requetes d’insertion suivantes :

1 INSERT INTO ‘commande‘ (‘NCOM‘, ‘NCLI‘, ‘DATECOM‘) VALUES2 ( ’ 30188 ’, ’ B512 ’, ’2009−01−03 ’);3 INSERT INTO ‘detail‘ (‘NCOM‘, ‘NPRO‘, ‘QCOM‘) VALUES4 ( ’ 30188 ’, ’ CS464 ’, 180),5 ( ’ 30188 ’, ’PA45 ’, 22),6 ( ’ 30188 ’, ’PA60 ’, 70),7 ( ’ 30188 ’, ’ PH222 ’, 92);

54

6.2 Exercice 5 : Modification et contraintes d’integriteOn suppose que phpMyAdmin/MySQL est lance, que la BD client commande (cf.figure 1.1) est selectionnee et que sa version courante est celle de la figure 1.2.

6.2.1 Question 5aEn utilisant pour chaque table l’onglet ’Structure’ de l’interface de MySQL sous php-MyAdmin, retrouvez sa cle primaire :

cle primairetable nom de la colonne type de la colonneclient

commande

detail

produit

6.2.2 Questions 5bPour chaque question donnee ci-dessous trouvez la reponse en la justifiant :

Q.5.1 Est-il possible d’ajouter un client ayant le meme nom qu’un client deja presentdans la base de donnees ?

Q.5.2 Est-il possible d’ajouter dans la base de donnees une commande ayant pour numerode commande ’30179’ (une commande existante), pour numero de client ’C400’ (un clientexistant) et dont la date de commande le 04/01/2012 (une nouvelle date) ?

Q.5.3 Est-il possible d’ajouter a la commande numero ’30188’ le produit ’PS222’ ? Etle produit ’PH222’ ?

Q.5.4 Est-il possible d’ajouter dans la base de donnees un produit dont le numero est’PH222’ et le libelle ’Planche’ ?

Q.5.5 Quelle est la liste des mises a jour a faire pour supprimer la commande ’30179’ ?

Q.5.6 Est-il possible de supprimer n’importe quel enregistrement de la table detail ?

Q.5.7 Est-il possible de supprimer n’importe quel enregistrement de la table commande ?

55

Q.5.8 Est-il possible de supprimer n’importe quel enregistrement de la table produit ?

Q.5.9 Est-il possible de supprimer n’importe quel enregistrement de la table client ?

Q.5.10 Quelles operations sont a faire si on veut ajouter le numero de telephone dechaque client ?

Q.5.11 Quelles operations sont a faire si on veut ajouter une date de livraison a chaquecommande ?

6.2.3 Question 5c

En utilisant pour chaque table l’onglet ’Structure’ et l’onglet ’Concepteur’ 3 de l’interfacede MySQL sous phpMyAdmin, retrouvez sa/ses cle(s) etrangere(s) ; ensuite retrouvez aquelle colonne elle fait reference en utilisant le lien ’Relation view’ (’vue relationnelle’) :

cle(s) etrangere(s)table nom de la colonne table parent colonne referenceeclient

commande

detail

produit

6.2.4 Question 5d

Quelles sont les cles primaires/etrangeres des tables de la BD gie agricole ?

3. L’onglet ’Concepteur’ est disponible seulement quand une base de donnees est selectionnee. Il n’estpas disponible quand une table d’une base de donnee est selectionnee.

56

6.3 Questions de revision

6.3.1 Q.R.8On suppose que la base de donnees gie agricole (cf. figure 1.3) est selectionnee.

Expliquez ce que fait la requete suivante et trouvez la reponse donnee par MySQL sur laversion courante de la BD (cf. figure 1.4) :

1 DELETE FROM employe2 WHERE Emp_Prn like ’%Henr i%’

Reponse

6.3.2 Q.R.9On suppose que la base de donnees client commande (cf. figure 1.1) est selection-

nee et que sa version courante n’est pas connue. L’instruction suivante

1 INSERT INTO commande (NCOM, NCLI, DATECOM)2 VALUES( ’ 600 ’, ’ 600 ’, ’2012−01−12 ’)

ajoute-elle une ligne dans la table commande ?

1. oui, s’il n’y a pas encore de commande dont le no est 600 et s’il existe un client no600 ;

2. oui, s’il n’y a pas encore de commande pour le client no 600 ;

3. oui, s’il n’y a pas encore de commande dont le no est 600 ;

4. oui, s’il n’y a pas encore de commande passee le 12 janvier 2012 ;

5. non.

Reponse

6.3.3 Q.R.10Quelle est la syntaxe generale d’une requete d’ajout d’une ligne dans une table ?

Reponse

Quelle est la syntaxe generale d’une requete de suppression d’une ligne dans unetable ?Reponse

Quelle est la syntaxe generale d’une requete de modification d’une ligne dans unetable ?Reponse

57

Chapitre 7

TD 4 : Travaux diriges en BD, seance 4– Le systeme d’information Starboat

7.1 Cahier des chargesSur de nombreux canaux francais, la navigation commerciale a laisse la place a la navi-gation de plaisance. Ainsi la societe Starboat s’est fait une reputation dans la location depenichettes aux touristes. Pour ameliorer sa productivite, elle a besoin d’un systeme d’in-formation (SI) afin d’equiper chacun de ses centres de location d’un logiciel de gestionadapte a leurs activites. Nous presentons ci-dessous le cahier des charges du systeme d’in-formation : il decrit le contexte d’utilisation de ce SI et explicite les besoins des clientspar une liste de fonctionnalites attendues.

Vous devez concevoir le modele de donnees et le mettre en œuvre en MySQL.

7.1.1 Description du contexte

Les bateaux

Chaque bateau (penichette) de la societe Starboat– est baptise ;– correspond a un type de modele (classique, terrasse, ou flying bridge) ;– a ete termine a une date donnee ;– a navigue un certain nombre d’heures.

Pour chaque modele de bateau (classique, terrasse, ou flying bridge) des informationscomplementaires sont disponibles :

– la puissance du moteur en CV ;– la presence ou non d’une prise electrique ;– le nombre de couchages.

Les revisions de bateaux

Chaque bateau est revise regulierement par un technicien. A chaque revision, le techniciendevra remplir l’application en indiquant :

58

– la date ;– le nombre d’heures de navigation du bateau au moment de la revision ;– les interventions realisees, sous forme de texte libre.

Les locations de bateaux

Toutes les locations sont enregistrees dans la base. En plus des informations evidentes asaisir pour une location, on souhaite avoir la possibilite de saisir au retour du bateau :

– le nombre d’heures de navigation effectuees ;– le montant verse au titre des dommages infliges au bateau ;– un commentaire libre.

7.1.2 Fonctionnalites attendues du SILe systeme d’information concu pour la societe Starboat doit pouvoir fournir

– la liste de tous les bateaux en cours de location ;– la liste des bateaux accompagnes des informations correspondant a leur modele et

classes par modele ;– la liste de tous les bateaux de type Flying Bridge ;– le nom du dernier bateau revise ;– la liste des bateaux qui ont subi un dommage en cours de location ;– la liste des bateaux disponibles a la location.

7.2 Exercice 6a : le schemaDefinissez le schema de la base de donnees Starboat en precisant :

– quelles sont les tables ;– quelles sont leurs colonnes avec leur type ;– quelles sont leur cle primaire et eventuellement leurs cles etrangeres.

7.3 Exercice 6b : les requetesEcrivez les requetes SQL pour repondre aux besoins enumeres dans le § 7.1.2.

7.4 Exercice 6c : les donneesChargez la base, sachant que la societe Starboat possede 5 bateaux ; enregistrez ensuite2 locations de bateaux et une revision. Testez vos requetes.

59

7.5 Exercice 6d : questionsQuelles operations sont a faire, si on veut integrer dans la base que :

1. La societe achete 3 bateaux de modele ’luxe’.

2. On veut pouvoir connaıtre quel est le drapeau d’un bateau, le no de permis d’unclient, le no de portable d’un technicien.

3. Pour les interventions on dispose d’un catalogue qui donne pour chaque interven-tion sa description, sa duree et son prix.

4. La societe travaille avec 3 autres techniciens et veut savoir, pour chaque interven-tion, quel est le nom du technicien qui a effectue la reparation.

Est-ce qu’on peut ajouter dans la base de donnees

1. un bateau ayant le meme nom qu’un bateau deja present dans la base ?

2. un modele ayant le meme nom qu’un modele deja present dans la base ?

3. une location sur un bateau deja loue ?

4. une revision sur un bateau loue ?

5. une revision sur un bateau deja repare ?

6. deux revisions a la meme date, sur deux bateaux differents ?

ATTENTION! A la fin du TD exportez 1 votre base de donnees Starboat.

1. voir le paragraphe 3.4.2, page 40

60

Index

base de donnees, 7, 8, 10, 12, 23

clecle etrangere, 15, 24, 56cle primaire, 14, 15, 24, 26, 55

colonne, 12, 24colonne obligatoire, 24

conditioncondition de jointure, 30condition de selection, 27, 28

contraintecontrainte d’integrite, 16, 25contrainte d’unicite, 14, 16, 26contrainte referentielle, 16, 17, 26

CREATE DATABASE, 24CREATE TABLE, 24, 25

DELETE FROM, 35, 53DISTINCT, 28DROP, 25

fonctions agregatives, 29, 49, 50COUNT, 29, 49MAX, 29, 48SUM, 29, 33, 49

INSERT INTO, 35, 53

jointure, 30, 31, 34, 35, 46, 47condition de jointure, 30, 46, 47

NULL, 14, 16, 17, 24, 25, 28, 29, 35

operateur, 13in, 30, 33, 34not in, 35operateur de comparaison, 13, 34, 43operateur logique, 13, 43priorite des operateurs, 13, 28, 43

ORDER BY, 29, 42

requete, 21CREATE DATABASE, 24CREATE TABLE, 24, 25DELETE FROM, 35, 53DROP, 25INSERT INTO, 35, 53SELECT-FROM, 26, 27, 31, 33, 42, 43,

45–47UPDATE, 36, 52, 53

schema, 8, 10, 19, 23, 25SELECT-FROM, 26, 27, 31, 33, 42, 43, 45–

47SGBD, 7, 22sous-requete, 30, 33–35SQL, 21, 23

table, 12, 24colonne, 12ligne, 8, 10, 12table enfant, 15, 17, 56table parent, 15, 17, 18, 56

type de donnees, 12, 24, 42, 55booleen, 12boolean, 25

chaıne de caracteres, 12, 44character, 24varchar, 24

numerique, 12decimal, 24float, 24integer, 24numeric, 24

temporel, 12date, 25time, 25

UPDATE, 36, 52, 53

61