ASUR_2010 - contenu1

Contenu ASUR – 2010
Extrait du référentiel
UE 2 : Connaissances de base et outils pour les réseaux d’entreprise (130h)
MASbd = Les Bases de données
20h
Introduction théorique
TD 4h
François
Déploiement
TP 4h
François
Gestion des utilisateurs
TP 4h
François
Les backup
TP 4h
François
Notions de SQL
TP 4h
François
Kieffer
Kieffer
Kieffer
Kieffer
Kieffer
Chaque TP ou TD donne lieu à un compte rendu noté ?
1
Théorie des bases de données (rappels?)
Utilité, relevé d'information, méthodes de modélisation, merise et schémas de merise,
1.1
Utilité
Une base de données vise à modéliser une partie du monde réel et sert de support à des
applications informatiques pour stocker, protéger les données formelles de l'organisation et
les manipuler (calculs, statistiques, tris, extractions, mises en corrélations, …).
-
-
-
Modéliser : pour représenter la réalité des données manipulées par l'organisation
(fournisseurs, articles, désignations, prix, adresses de postes, utilisateurs, etc.
…) selon une structure logique, à l'aide de système de modélisation (merise,
UML),
Stocker sous forme de tables de "fiches", enregistrements ou occurrences
d'entité, dans des fichiers ou dans un SGBD(R) que l'on peut sauvegarder ou
précharger,
Protéger : restreindre l'accès aux données aux personnes autorisés, assurer la
préservation des données contre les risques informatiques,
Manipuler en effectuant des extractions grâce à un langage de requêtes (SQL)
Ceci dans le but de conserver les archives de l'entreprise, son savoir-faire, son expertise et
de faciliter la diffusion des informations nécessaires.
Exemple :
Annuaire(nom, prénom, âge, CP, Ville)
tableau Redondances (CP, Ville, …)
2ème tableau : (CP, Ville)
Note : toute situation ne nécessite pas forcément de Base de données.
Exemple :
Un groupe de DB, de disques suffisamment important pour justifier une BDD
Si ce n’est que pour en faire la liste, un tableau suffit.
SGBD : Système de gestion de Bases de Données
SGBDR : SGBR relationnel : il est possible d’établir des relations entre les données de la BDD
Exemples : Oui : Oracle, MySQL, Ingre, SQL Server ; Non : SQL (langage).
Comment faire ?
Réponse : l’analyse.
1. Analyse des besoins : Pourquoi, à quoi ça sert ? Quand ?
2. Comment ? Techniques d’analyse.
Pourquoi une technique d’analyse (Merise ou UML) ?
Les gros volumes de données nécessitent une approche rigoureuse du problème de la
modélisation. Pour parvenir à un résultat cohérent, des méthodistes ont développé une
E:\francois\education\2009-2010\ASUR\ASUR_2010 - contenu1.doc
Francois Kieffer
Page 1 sur 11
méthode (encore en cours d’évolution) nommée Merise et, parallèlement, un langage de
modélisation dit universel (UML).
Après l’analyse des besoins, la première action, dans la création d'une BDD, est de dégager
les concepts, les données élémentaires, les exemples de données de l'interview de
l'organisation.
1.2
Le relevé d'informations
Le relevé d'information est la chose la plus difficile de l'analyse merise ou UML.
Les informations à modéliser sont trouvées lors de l'interview de l'organisation : réunions
d'information avec le personnel, consultation des procédures internes, lecture des
documents d'archives et description des activités et de la structure de l'entreprise,
rétroconception du logiciel existant, audit des pratiques professionnelles.
1.2.1
En théorie c'est ça …
Concepts, données et valeurs
Avant d'aborder le MCD, on va pouvoir relever trois types d'informations qui sont
des concepts, des données ou des valeurs.
Les concepts sont des choses, des objets que l'on va gérer. Ils deviendront
probablement des entités du schéma entité association vu plus loin.
Les données sont des informations élémentaires sur les concepts.
Données et concepts sont souvent masqués par des valeurs de ceux-ci ou exprimés
par des euphémismes ou des synonymes.
Attention !
Les acteurs ne sont pas des informations à mémoriser.
Les personnes sont souvent des acteurs mais parfois des concepts lorsqu'il s'agit de gérer du
personnel.
Donnée
Une donnée est une information élémentaire que l'on ne peut pas décomposer.
Exemple oui : description, prix, quantité, numéro d'identification, nom, prénom, ville, code
postal, etc. …
Exemple non : fournisseur, article, ingénieur
Dans le discours du sujet, une donnée est souvent rattachée à un concept.
Exemple oui : description de l'article, nom de l'employé, numéro du client, numéro de
commande, etc. …
Exemple non : description (tout court, sans dire de quoi)
Concept
Un concept est une information complexe qui peut être décomposée en données.
Exemple oui : article, commande, client, employé, etc. …
Exemple non : description, organisation gérée, tapis Z7 cendré.
Pour détecter un concept, on va relever les noms de choses, d'objets, de personnes ou de
types. Ce seront souvent des concepts.
Ce seront sûrement des concepts si des données y sont rattachées.
Valeur
Certains concepts ou données sont masqués par des valeurs exposant différentes
occurrences de ceux-ci.
Exemple oui : Strasbourg, Nantes, Rennes, Lyon et Mulhouse sont des occurrences du
concept 'ville'.
Ces mêmes valeurs sont, de plus, des occurrences de la donnée 'nom ville'.
Exemple non : type article, ville.
Remarques importantes :
E:\francois\education\2009-2010\ASUR\ASUR_2010 - contenu1.doc
Francois Kieffer
Page 2 sur 11
Dans tous les cas, il ne faut relever que les données et concepts pertinents pour l'étude,
gérés par le système décrit, et ne pas se laisser perturber par des informations qui
constituent le bruit de l'étude.
A noter que les information sur l'entreprise elle même ne sont pas à relever, seules les
données avec lesquelles on travaille sont à noter.
Exemple descriptif : la société InfoTech travaille avec des fournisseurs à Strasbourg, Lyon et Nancy.
InfoTech n'est pas une donnée, c'est la description de l'organisation ;
-
Fournisseur est un concept (dont on ne sait rien d'autre)
Strasbourg, Lyon, … sont des occurrences de la donnée 'nom ville' du concept 'ville'.
À indique qu'il y a un lien, une association, une relation entre les concepts fournisseur et ville
1.2.2
… mais en pratique …
Certains concepts sont ambigus. Ainsi dans certains cas, des concepts seront relégués au
rang de donnée.
Exemple descriptif : la société InfoTech travaille avec des fournisseurs à Strasbourg, Lyon et Nancy.
Les noms des villes ne font pas l'objet d'une gestion particulière.
-
InfoTech n'est pas une donnée, c'est la description de l'organisation ;
Fournisseur est un concept (dont on ne sait rien d'autre)
Strasbourg, Lyon, … sont des occurrences de la donnée 'nom ville' du concept 'ville'.
À indique qu'il y a un lien entre 'Fournisseurs' et 'Ville'. Mais comme la ville n'a pas besoin
d'être de façon précise, codifiée (ajout au sujet) le nom de la ville sera noté seul et le concept
risque de disparaître.
Les données seront consignées dans un dictionnaire des données, les concepts resteront au
brouillon, à titre d'indication pour une future utilisation.
1.3
Représentation (Merise)
Pour représenter une base de données, on utilise souvent la modélisation Merise qui apporte
des règles de représentation et formalise les schémas de modélisation des BDD.
On y trouve des éléments similaires à ceux utilisés dans MS-Access trois niveaux
conceptuels, trois schémas et trois normalisations des noms :
Modèle
Conceptuel
Données
de
Modèle
Logique
de
Données
ou
Modèle
Relationnel
MPD
Graphe des Dépendances
Fonctionnelles ;
Données, dépendances ;
Schéma Conceptuel des
Données
SLD
Entités, attributs, identifiants,
associations, cardinalités, etc. …
Tables, propriétés, clés primaires,
relations
MR
Relations,
propriétés,
clés
primaires, clés étrangères
Implémentation du MR en langage de définition des données
(création des tables de la BDD)
Le MCD contient parfois des informations qu'il est impossible de traduire sous forme
schématique dans les modèles suivants. On aura donc parfois recours à des contraintes
exprimées textuellement.
Voir schéma gestion commerciale……
1.4
Modèle relationnel
Le modèle relationnel est la représentation textuelle du MLD et MCD.
Une relation est un ensemble de données associées de manière cohérentes : elle
appartiennent toutes au même concept et à la même occurrence de ce concept.
E:\francois\education\2009-2010\ASUR\ASUR_2010 - contenu1.doc
Francois Kieffer
Page 3 sur 11
Exemple :
Relation client :
cliNum
C1
C45d
VEBBU2O34
…
Relation Commande
cliRaisonSociale
Toto
Titi
GRRRR
cdeNum
1
24
345
cliZip
68000
67000
68750
cliVille
Colmar
Strasbourg
Bergheim
cdeDate
23/12/2005
01/01/2009
01/01/2009
cliadr1
Ici
La-bas
Quelque-part
cliNum
C1
C1
VEBBU2O34
Le schéma relationnel montre toutes les relations, les attributs, les références de la base.
Dans ce modèle, il manque cependant l'expression des contraintes (une valeur ne doit pas
dépasser une certaine limite, un enregistrement doit être simultané avec la présence d'un
autre dans une autre table, etc. …)
Client(cliNum, cliRaisonSociale, cliadr1, cliZip, cliVille)
Clé primaire : cliNum
Commande(cdeNum, cdeDate, cliNum)
Clé primaire : cdeNum
Clé étrangère : cliNum référence cliNum dans client*
* officiellement, on devrait dire : "l'attribut clinum est en dépendance de référence avec
l'attribut cliNum de la table Client", mais je trouve que c'est un peu long.
Les colonnes sont nommées champ ou attribut
nom de l’attribut est unique dans une relation
nom attribut peu exister plusieurs fois dans des tables différentes. Ce sont des
champs différents mais qui signifient la même chose, même sémantique.
Le domaine est la liste, l’ensemble des valeurs acceptées pour le champ.
Exemple : {Colmar, Strasbourg, Bergheim} est le domaine de ville
-
L’ordre des attribut n’a pas d’importance mais convention : identifiant de
l’occurrence est devant, les champs qui font référence à des identifiants d’autres
tables sont derrière. Les champs sont souvent regroupés par ensembles signifiant
cohérents (adresse : CP, Ville, N°, Rue ; description : libellé, poids, volume ; etc. …).
Les lignes sont nommées enregistrement ou tuple et représentent les valeurs d’une
occurrence, d’un « objet » représenté par la relation.
Exemple :
C1
Toto
68000
Est un enregistrement de la relation Client.
1.5
Colmar
Ici
Privilèges (Droits d'accès)
Pour assurer la sécurité des données, on va mettre en place un système de droits sur les
tables et les champs de celles-ci.
Ces droit sont : création, interrogation, modification, suppression (CIMS).
Création permet de créer des enregistrements, des tables, des utilisateurs.
Interrogation permet d'extraire des enregistrements partiels ou entiers, d'afficher le
contenu de table, la structure des tables, la liste ou les droits des utilisateurs et
groupes.
Modification permet de modifier des valeurs, la structure de tables, les droits des
utilisateurs.
Suppression permet de supprimer des enregistrements, des champs, des tables, des
droits, des utilisateurs ou groupes.
Ils sont données hiérarchiquement selon :
Droits globaux sur la base
E:\francois\education\2009-2010\ASUR\ASUR_2010 - contenu1.doc
Francois Kieffer
Page 4 sur 11
Droits globaux pour la catégorie d'utilisateur (adm, usr)
Droits de groupes puis de sous-groupes
Droits particuliers à un utilisateur.
Cependant, il vaut mieux ne pas multiplier les niveaux et les groupes de droits, afin de ne
pas risquer de maillage des droits :
Droit de création groupe interdit sur une table puis sous groupe autorisé sur une partie de
cette table. Donc en contradiction avec la règle du groupe …
Tentative de cession de privilèges refusés = erreur.
Exemple XXXXX (voir sujet CGO)
Un tableau de définition des privilèges du LID et LMD, table Client
Utilisateur/groupe Consultation
Insertion
modification
adm
Aucun
Concepteur
Oui
Non
Non
Gestion
Oui
Non
Oui
Production
Non
Non
Non
Ventes
Oui
Oui
Oui
Suppression
Oui
Non
Non
Non
Une hiérarchisation de base des privilèges
PUBLIC (Base) = all
adm = all
usr = select, insert, delete, update : all, create, alter, drop, grant, revoke : none
production : insert on client : none, delete on client : none
…
1.6
Exercices
♥=facile, ♦=moyen, ♠=difficile, ♣=expert, $=rusé, sioux, *=squelette de source fourni.
1.6.1
Exo droits ♥
Expliquez quels sont les risques de ne pas respecter la hiérarchisation d’attribution des
droits.
Proposez une solution simple pour pallier à cette difficulté.
1.6.2
Exo MCD ♥
L'entreprise de négoce ECOTECH désire informatiser la gestion de ses fournisseurs et de ses
clients ainsi que des commandes de produits qu'elle passe avec chacun des deux types de
tiers.
→
Déterminez combien de concepts complexes sont en présence.
→
Faites la liste et le schéma des dépendances fonctionnelles entre ces concepts, en
ajoutant les données nécessaires.
→
Imaginez une série d'informations élémentaires pour chaque concept
→
Construisez un schéma conceptuel de cette organisation
1.6.3
Exo MCD ♥
L'entreprise ECOTECH désire modifier son mode de production et effectuer le
reconditionnement des produits vendus. Pour cela, elle s'est dotée de machines de
conditionnement. A cet effet, pour un article M, il est nécessaire d'ajouter un emballage E
pour en faire un produit fini F.
Dans certains cas l'article M pourra figurer en plusieurs exemplaires dans le produit F. Il est
même possible qu'un produit F soit le résultat de l'emballage de différents M en nombre
d'exemplaires multiples
Il est évident qu'un article emballage ne peut être vendu seul et qu'un article de type M non
plus
→
Complétez le modèle conceptuel (DD, GDF, SCD) de l'exercice précédent.
E:\francois\education\2009-2010\ASUR\ASUR_2010 - contenu1.doc
Francois Kieffer
Page 5 sur 11
1.6.4
Exo MCD, MR ♦
L'entreprise PersoTECH gère un parc informatique et le personnel qui y accède. Dans cette
organisation, le réseau offre l'accès à une base de données sensible.
Il devient nécessaire de rationaliser cette gestion en mémorisant les information relatives
aux postes informatiques (n° du poste, n° de série, localisation – publique, production,
interne, labo informatique –, utilisateur habituel), aux utilisateurs (nom, prénom), aux
groupes ou sous groupes auxquels ils appartiennent et aux droits qui leurs sont conférés.
Il y a plusieurs possibilités de restrictions de l'accès aux données (/ex. : accès complet,
consultation complète, modification structurelle, modification des données, écriture
seulement, accès en consultation superficielle)
Les utilisateurs peuvent être regroupés en groupe d'utilisateurs. Chaque groupe appartient à
un groupe de niveau supérieur.
Il existe 3 groupes de plus haut niveau : "Administrateur", "gestionnaire" et "Utilisateur". Les
autres groupes sont toujours rattachés à un groupe de niveau supérieur.
Un groupe ne peut pas appartenir à lui-même.
Exemple :
Administrateur
Gestionnaire
Utilisateur
->
->
->
->
->
(aucun)
Sauvegarde
Architecte
Invité
Standard
->
Gestion
->
->
->
->
->
Std-Comptable
Std-Production
Ges-Achat
Ges-Comptable
Ges-Personnel
Chaque groupe définit un ensemble de droits.
Les règles de droit sont analysées dans l'ordre suivant :
droits de groupe et sous-groupes
droits particuliers de l'utilisateur
droits de la localisation du poste informatique
Les règles d'attribution des droits peuvent ajouter ou supprimer des droits.
Si un droit n'est pas nommément refusé ou offert, il est autorisé.
On doit pouvoir parvenir à un résultat tel qu'un utilisateur qui possède peu de droits n'en
aura pas plus s'il se connecte sur le poste de l'administrateur. De même, l'administrateur
n'aura que peu de droits s'il se connecte sur un poste situé dans un espace public.
Toutes les personnes faisant partie d'un même groupe et n'ayant pas de modifications
particulières en fonction de leur qualification ou localisation auront les mêmes droits.
→
Construisez le modèle relationnel de la base de gestion des droits des utilisateurs.
1.6.5
Exo MCD, MR ♦
Un passionné de la bande dessinée de Tintin (tintinophile) désire mettre en ligne la liste des
jurons prononcés dans les albums de cette série de BD.
Pour cela, il relève les informations suivantes :
les titres, description et dates de 1ère parution des albums,
les nom, prénom, fonction, sexe et rôle (gentil ou non) de chaque personnage et
leur participation à l'un ou l'autre album,
les jurons ("noms" des jurons) ainsi que le personnage et la page où ils sont
prononcés dans un album,
les pays (nom du pays) où se déroulent chaque album.
E:\francois\education\2009-2010\ASUR\ASUR_2010 - contenu1.doc
Francois Kieffer
Page 6 sur 11
Modélisez cette situation :
a) Relevez toutes les données nécessaires à cette gestion dans un dictionnaire des
données (DD) puis construisez le graphe des dépendances fonctionnelles (GDF).
b) Dessinez le schéma conceptuel des données,
c) Rédigez le schéma des relations (MR).
d) Vérifiez qu'il n'y ai aucune possibilité d'erreur dans le schéma
1.6.6
Exo MCD, MR ♦
En association à cette base, le collectionneur décide de vendre différents exemplaires de
chaque tome de Tintin.
Chaque album possède une cote. Cette cote dépend de l'année de réédition voire du n° de
réédition dans l’année.
Cette cote est attribuée par le vendeur en fonction de l’édition du livre.
Il est bien évident qu’un livre neuf n’a pas la même valeur de vente qu’un livre en état
moyen ou ayant des défauts ou dégradé. Selon les années, le prix de vente peut même
dépasser la cote si l’état est exceptionnel (livre ancien et neuf).
Le vendeur peut donc décider de vendre l’ouvrage à la cote ou à un autre prix, arbitraire.
Rédigez la nouvelle mouture ou les compléments au MR précédent.
1.6.7
Exo MCD, MR ♦
Vous êtes chargés de développer une application de gestion de pannes sur un parc
informatique. Lors d'un incident, on désire avoir des informations sur le matériel en jeu
(clavier, écran, imprimante, PC, logiciel, numéro interne, nom, numéro de série, marque,
modèle), sur sa localisation dans l’entreprise. L’auteur de la déclaration de panne indiquera
aussi son nom puis un titre succinct et une description plus longue du dysfonctionnement.
Lorsqu’une panne est traitée par un technicien, celui-ci indique son nom et déclare la panne
résolue (ou non). Auquel cas, de l’état "en cours", l'incident passe à l’état "résolue" ou en
attente si le traitement doit être long (plus d’une intervention ou prestataire externe).
Modélisez cette situation
2
Langage de requête : SQL
Il sert à exploiter, à extraire des données d’une base.
Principe : c’est un langage exécuté sur le serveur et lancé par le client.
Il se compose de quartes parties : le LID (interrogation) le LMD (Modification des données),
le LDD (définition, modification de la structure) et le LCD (contrôle d’accès aux données).
Note sur la présentation. Même si il est possible d’écrire des requêtes en minuscules (et c’est
ce que nous faisons dans les requêtes directes), lorsque SQL est inséré dans le langage
client, on écrira les requêtes en majuscule afin de bien distinguer, visuellement, les deux
langages.
2.1
LID
2.1.1
Commandes de base du LID (Rappels ?)
Le langage d'interrogation des données (LID) est le plus complexe des langages du SQL. La
bonne maîtrise de celui-ci permet de comprendre la philosophie du SQL.
Dans tous les exemples, je ferais un parallèle entre le SQL et le français
Le principe de base est le suivant :
Sélectionner des données de table, données calculées ou constantes
De tables
Où les données d'origine répondent aux conditions exprimées
Groupées par nom de données sélectionnées
Qui répondent aux conditions additionnelles suivantes
Triées selon les noms de champ d'origine ou sélectionnés avec le sens du tri
En anglais
E:\francois\education\2009-2010\ASUR\ASUR_2010 - contenu1.doc
Francois Kieffer
Page 7 sur 11
SELECT liste de champs (originaux ou calculés)
FROM table ou liste de tables
WHERE condition (=expression bouléenne)
GROUP BY liste de champs du select, obligatoire en cas d'agrégats
HAVING condition de type 2
ORDER BY liste de champs avec le sens de tri (asc ou desc)
Contraintes :
Les listes sont une suite d'objets séparés par des virgules : a, b, c, d, …
La liste de champ du select peu contenir des champs des tables citées, des calculs, des
constantes, des fonctions d'agrégats d'enregistrement (somme, comptage, min, max, …)
La liste de champs du group by ne peut avoir que des champs nommés pris parmi la liste
des champs du select, a l'exclusion des champs agrégés. Elle sert à indique les critères de
regroupement des champs agrégés.
La liste des champs de tri est libre. Elle contient le nom d'un champ suivi du sens de tri (asc
ou desc). Le sens asc (ascendant) est le sens par défaut et peut être omis.
Algèbre relationnelle (Initiation)
Projection : sélection de certains champs de la relation, mais pas de tous.
Select CliNum, CliNom from client ;
Restriction : sélection de quelques enregistrements de la relation, mais pas tous.
Select * from Client where CliNum <> ‘C1’ ;
Union : assemblage de deux relations (ensemble d’enregistrements) de forme identique pour
en faire un nouvel ensemble.
Select * from Client where CliCP= 68000
Union
Select * from client where cliVille = ‘Strasbourg’ ;
Différence : intersection entre deux ensembles d’enregistrement de même forme pour en
faire un nouvel ensemble (plus petit ou égal au plus grand des deux).
Select * from Client where CliCP>= 68000
Minus
Select * from client where cliVille ‘Bergheim’ ;
Jointure : assemblage de deux relations de forme différentes en associant le champ de
référence d’une table avec le champ identifiant référencé dan l’autre table.
Exemple :
Client et commande. Le champ cliNum de commande référence le champ CliNum identifiant
de Client. On pourra créer la jointure entre commande et client :
Exemple :
Relation client :
cliNum
C1
C45d
VEBBU2O34
…
Relation Commande
cliRaisonSociale
Toto
Titi
GRRRR
cdeNum
1
24
345
Table jointe :
cdeNum cdeDate
1
23/12/2005
24
01/01/2009
345
01/01/2009
cliNum
C1
C1
VEBBU2O34
E:\francois\education\2009-2010\ASUR\ASUR_2010 - contenu1.doc
cliZip
68000
67000
68750
cdeDate
23/12/2005
01/01/2009
01/01/2009
cliNum
C1
C1
VEBBU2O34
Francois Kieffer
cliVille
Colmar
Strasbourg
Bergheim
cliadr1
Ici
La-bas
Quelque-part
cliNum
C1
C1
VEBBU2O34
cliRaisonSociale
Toto
Toto
GRRRR
cliZip
68000
68000
68750
cliVille
Colmar
Colmar
Bergheim
cliadr1
Ici
Ici
Quelque-part
Page 8 sur 11
Conditions et expressions
Les conditions sont une suite d'opérations booléennes (qui rendent un résultat vrai ou faux)
reliées par des connecteurs logiques (and, or, not and, not or, not) :
Champ1<10 or champ2=champ3 and champ4>=champ5*(3+champ6)
Une opération booléenne est une expression contenant un opérateur booléen :
comparaison (=, <, >, <=, >=, <>) entre deux expressions
in : recherche dans une liste : a in ("v1", "v2", …)
between : "entre", comparaison multiple : exp between exp1 and exp2
Exemples : Soit la base de données Tintin (voir l'exercice concerné)
Liste des titre des albums triés par ordre alphabétique décroissant (de z à a):
Select albTitre from album order by albTitre desc;
Liste des albums datant de 1950 ou plus tôt :
Select albTitre from album where albDate<=1950 ;
Nombre de pays par album
Select albtitre, count(paysNum) from album, seDerouler
where album.albNum=seDerouler.albNum group by albTitre ;
Valeur de la date de parution de l'album le plus ancien
Select min(albDate) from album;
Remarque importante : Toute requête de type Select renvoie une table, une liste ou une
valeur. Ceci est très important car on peut réutiliser une requête dans une autre expression.
Exemple :
Liste des albums antérieurs à "Tintin au Tibet"
Select * from album
Where albDate < (
Select albDate from album where albTitre = "Tintin au Tibet"
)
;
La sous-requête retourne une valeur
Liste des personnages qui ne prononcent pas de juron
Select noPers from personnages
where noPers not in (select distinct noPers from prononcer)
;
La sous-requête retourne une liste
Les groupements (agrégats)
Il est possible de regrouper des lignes pour effectuer des calculs ou des sélection parmi ces
lignes.
On retiendra :
• count(*), compter : select count(*) from article = nombre d'articles de la table
• sum(champ), total, somme :
o select numCommande, sum(prixHT) from commande group by numCde ;
= liste des commande et du total HT de chacune
• avg(champ), moyenne : s'utilise comme sum()
• max(champ), min(champ), maximum et minimum : renvoie la valeur maxi ou mini
du champ indiqué : select max(prix) from article = prix maxi rencontré parmi les
articles, select max(date) from commande = date de la dernière commande.
Note : ces fonctions d'agrégats ne peuvent être utilisées que dans les clauses select et
having, pas dans le where !
E:\francois\education\2009-2010\ASUR\ASUR_2010 - contenu1.doc
Francois Kieffer
Page 9 sur 11
2.2
LMD
Langage de manipulation de données : modification des enregistrements
Ici (et plus loin) plus de concepts ensemblistes (sauf…).
Suppression :
Delete from table
[Where conditions] ;
Ne pas mettre de condition revient à vider la table.
Exemple : delete form Client where cliNum=’C2’ ;
Mise à jour
:
Update table set champ1=expression1[, champ2=expression2]
[Where conditions] ;
Une expression peut être un calcul, un champ, une valeur, le résultat d'une requête
retournant une valeur.
Exemple : update client set cliAdr1="A changé" where cliNum=C1 ;
Insertion
:
Insert into table [(champ1 [, champ2 *])]
Values (valeur1[, valeur2 *]) ;
ou
:
Insert into table [(champ1 [, champ2 *])]
Requête sélection ;
Les valeurs indiquées ou les champs de la requête sélection doivent correspondre aux
champs de la table ou aux champs indiqués, dans l'ordre exact.
Certains langages acceptent de mettre plusieurs listes de valeurs afin d'insérer plusieurs
enregistrements en une seule requête. Ceci n'est pas standard.
Exemple : insert into Client Values (‘C3’, ‘Gros Minet’, 67390, ‘Marckolsheim’, ‘A la niche’) ;
2.3
LDD,
Langage de définition des données : création, modification, destruction des tables, vues et
autres …
Suppression :
Drop
Drop
Drop
Drop
Drop
Création
objet nom-objet ;
trigger nom-objet ;
view nom-objet ;
table nom-objet ;
database nom-objet ;
//
//
//
//
suppression
suppression
suppression
suppression
d'une
d'une
d'une
d'une
procédure automatique
vue dynamique
table
base de données
:
Create objet nom-objet … ;
Create table nom-table (
Liste de champs avec leur définition
[, definition de la clé primaire]
[, liste de contraintes]
) ;
Create view nom-vue as requête sélection ;
Create database nom-base-de-données ;
Create trigger nom-trigger (
Définition de la procédure (voir chapitre spécifique)
);
Modification :
Alter objet … ;
E:\francois\education\2009-2010\ASUR\ASUR_2010 - contenu1.doc
Francois Kieffer
Page 10 sur 11
2.4
LCD
Langage de contrôle des données : définition des utilisateurs, attribution/révocation des
droits. Voir chapitre suivant : la gestion des privilèges et droits.
2.5
Exo
♥=facile, ♦=moyen, ♠=difficile, ♣=expert, $=rusé, sioux, *=squelette de source fourni.
2.5.1
Exo LID ♥ à ♣$
Rédigez les requêtes qui permettent de répondre aux questions suivantes :
Facile
a)
Année de réalisation de TINTIN au CONGO
b)
Prénom du Capitaine HADDOCK
c)
Liste des personnages féminins.
Un peu moins facile
a)
Dans quel(s) pays se déroule l’album ON A MARCHE SUR LA LUNE
b)
La Castafiore prononce t-elle des jurons ? (répondre combien)
c)
Nom des albums dans lesquels TINTIN voyage en Chine.
d)
Numéro(s) de(s) l'album(s) et N° de la (des) page(s) dans lequel on peut lire le
juron suivant : « REVOLUTIONNAIRE EN PEAU DE LAPIN »
e)
Liste des pays visités par le professeur TOURNESOL
f)
Nom et prénom des méchants dans l’album « LE LOTUS BLEU »
g)
Liste des jurons prononcés par MILOU
Difficile
a)
Milou accompagne-t-il TINTIN dans chaque aventure ?
b)
Dans quel album le capitaine HADDOCK fait-il son apparition ?
c)
Classement des personnages par nombre de jurons prononcés ?
2.5.2
Exo LDD ♥
a) Vous êtes chargé de construire la base de données Tintin. Rédigez les requêtes
nécessaires.
b) Il faut gérer le résumé dans la table Album et un commentaire, attaché à chaque
exemplaire.
2.5.3
Exo LMD ♥ ,♦
♦
Le tintinophile possède différents exemplaires des albums, de numéro d'édition variable.
Il souhaite pouvoir enregistrer ses exemplaires dans la base de données et, à l'aide d'une
requête, connaître la valeur cotée de sa collection.
Rédigez le MCD, le MR, les requêtes qui effectuent la modification de la structure de la base
de données.
Rédigez la requête qui permet d'enregistrer l'album 01 (Tintin au Congo) de l'édition n° 8
2.5.4
Exo SQL ♥ ,♦
♦
Reprenez l’exercice sur la gestion de pannes et rédigez les requêtes de création des tables et
des contraintes de références (clés étrangères).
Vous rédigerez aussi les requêtes qui répondent aux questions suivantes :
a) Listez toutes les pannes
b) Listez les pannes non résolues
c) Insérez la panne suivante :
d) Modifiez l’état de la panne XXX de l’état en cours à l’état résolu.
e) Listez et comptez les pannes similaires
f) Supprimez les pannes de plus d’un an
g) Supprimez les pannes du matériel n° XXX
h) Faites la liste des pannes qui ne dépendent pas d’un matériel
i) Listez les matériels qui sont sujet à plus de dix pannes
E:\francois\education\2009-2010\ASUR\ASUR_2010 - contenu1.doc
Francois Kieffer
Page 11 sur 11