9.1 Base de données Astérix

Formation ISN II
Les bases de données 1/3
Mercredi 10 juillet 2013
Corinne RAINGUEZ – Lycée du Grand Nouméa
[email protected]
Contenus des interventions
Mercredi 10 juillet
Initiation aux bases de données et SQL
Application pratique avec ACCESS 2003
Mercredi 24 juillet
Bases de données, suite
Eléments de HTML et PHP pour MySQL
Application pratique avec wamp
Mercredi 7 aout
Eléments théoriques complémentaires
2
Application pratique récapitulative, évaluation
Déroulement de la séance du jour
Alternance de contenus
théoriques et d’applications
pratiques à partir d’une BDD
exemple.
13H30 – 16H30
3
Les bases de données en
quelques mots
Les bases de données (BDD) ont été créées pour
faciliter la gestion qualitative et quantitative des
données.
Les SGBD (Access, MySql, Oracle) sont des
applications informatiques permettant de créer et
de gérer des BDD.
Les BDD relationnelles sont les plus répandues,
et on utilise des SGBDR pour les implémenter.
Le langage SQL est le langage commun à tous
les SGBDR, ce qui permet de concevoir des BDD
relativement indépendamment des systèmes
utilisés.
4
Quelques définitions
essentielles
Une BDD est un ensemble volumineux, structuré
et minimalement redondant de données reliées
entre elles, stockées sur supports numériques
centralisés ou distribués, servant pour les
besoins d'une ou plusieurs applications,
interrogeables et modifiables par un ou plusieurs
utilisateurs travaillant potentiellement en
parallèle.
Exemple d'application : gestion d'une compagnie
aérienne.
Un SGBD est un logiciel qui prend en charge la
structuration, le stockage, la mise à jour et la
maintenance d'une base de données. Il est
l'unique interface entre les informaticiens et les
données (définition des données, programmation
des applications), ainsi qu'entre les utilisateurs et
les données (consultation et mise à jour).
Exemples :
Access est une SGBD relationnel Microsoft qui
offre une interface conviviale permettant de
concevoir rapidement des applications de petite
envergure ou de réaliser des prototypes à moindre
frais.
MySQL est un SGDB relationnel libre très utilisé
pour la réalisation de sites web dynamiques.
Présentation de la base de
données Astérix
Le schéma relationnel de la base
astérix.xls
7
Modèle logique
8
Règles de lecture du schéma
relationnel, exemples
Tables VILLAGE, HABITANT
Table FABRIQUER
Table TROPHEE
9
Mots clés associés
Clé primaire
Clé étrangère, secondaire
Règle de gestion
Dépendance fonctionnelle
10
Formes normales
Clé + atomicité(1FN)
Clé composée (2FN)
Pas de dépendance entre
attribut non clé (3FN)
=> Pas de redondance, pas de
perte d’information, pas de
perte de dépendance.
11
• Intégrité référentielle
• Enregistrement, tuple,
occurrence
• Attribut, champ
Présentation de SQL
SQL : STRUCTURED QUERY LANGUAGE
SQL comprend :
 un langage d’interrogation des données
(LID)
 un langage de définition des données
(LDD)
13
 un langage de manipulation des données
(LMD)
 un langage de contrôle des accès à la
base (LCD)
 Il peut être utilisé directement grâce un
SGBDR (Access, SQLite, SQL Server,
Oracle…)
ou alors intégré dans un langage hôte (C#,
PHP, VBScript...).
14
I- Le Langage d’interrogation
de données (LID)
Pour
 sélectionner certaines colonnes
d’une table (projection)
 sélectionner certaines lignes d’une
table - à partir de leur contenu
(sélection)
 combiner des informations venant de
plusieurs tables (jointure)
15
Utilisation d’Access 2003
Ouvrir la BDD « Astérix et Obélix isn » à partir de
l’application Access.
Pour créer une requête (mode
création)
- Sélectionner la(es) table(s) à
utiliser
- Double-cliquer sur les champs à
afficher
Deux options, complémentaires,
avec ACCESS, pour créer une
requête
Le mode QBE (tableau)
Le mode SQL
I-1 la projection
R1 : Liste de tous les habitants ?
22
1
1
22
3
Résultat de la requête :
23
…trié par ordre alphabétique…
Par défaut : ASC
Sinon, préciser <nom-du-champ> DESC
R2 : Liste des provinces et de leur
gouverneur ?
26
Résultat de la requête :
27
R3 : Liste des constituants
principaux des potions ?
29
Résultat de la requête :
R4 : Liste des différents constituants
principaux des potions
Résultat de la requête :
R5 : Toutes les informations sur
les potions ?
Le signe * équivaut ici à afficher toutes les
informations contenues dans la table
Exercice - Rédiger les requêtes
suivantes
1 - Quelles sont les qualités rencontrées dans le village
d’Astérix ?
2 - Nom des resserres et leur superficie ?
3 – Renseignements concernant les provinces ?
4 – Libellés des potions ?
5 – Liste des villages par ordre alphabétique
I-2 la sélection
R6 : Liste des trophées
rapportant 4 points ?
Syntaxe :
SELECT [DISTINCT] {<nom(s) de(s) attribut(s)>|*}
FROM <nom(s) de(s) table(s)>
WHERE <condition(s) de sélection> ;
Résultat de la requête :
R7 : Les noms des habitants
de plus de quarante ans
Résultat de la requête :
R8 : Les noms des habitants
ayant entre quarante et cinquante
ans
Résultat de la requête :
Avec BETWEEN
SELECT Habitant.Nom, Habitant.Age
FROM Habitant
WHERE (((Habitant.Age) Between 40 And
50));
Résultat de la requête :
Les opérateurs utilisés dans les
clauses SELECT et WHERE
R10 : Liste des habitants dont
le nom contient un u
R11 : Liste des villages
des provinces 1 et 2
R12 : Liste des potions dont on
ne connait pas la formule
Exercice 2 - Rédiger les requêtes
suivantes :
1- Donner les noms des trophées de type
« Bouclier »
2 - Combien y a-t-il de huttes dans les
villages d’Aquilona et de Gergovie ?
3 - Quelles sont les quantités, rangées par
ordre décroissant, des potions absorbées
par l’habitant n°3 ?
4 - Quel est le constituant principal de la
potion zen ?
5 - Quelles sont les catégories de trophées
rangées dans les resserres 1, 2 et 3 ?
I-3 la jointure
Syntaxe :
SELECT [DISTINCT] {<nom(s) de(s) attribut(s)>|*}
FROM <nom(s) de(s) table(s)>
WHERE <jointure(s)> ;
R13 : Liste des habitants : nom,
age et qualité
Tables à joindre
Champs de jointure
R14 : Liste des villages, avec
leur province et le nom du
gouverneur
R15 : Nom du village où vit
Astérix
Exercice - Rédiger les requêtes
suivantes :
1 - Nom et qualité des habitants ayant stocké leur
trophée dans la resserre n°3, par ordre croissant
des catégories de trophées ?
2 – Noms et âge des habitants ayant déjà fabriqué
une potion anti-douleur ?
3 – Qualités des habitants dont le nom commence par
B?
4 – Noms des habitants gouvernés par « Yenapus »
et « Garovirus » ?
5 – Liste des trophées gagnés par Astérix ?
Les fonctions d’agrégat
SQL dispose d'une famille de fonctions appelées les
fonctions d'agrégats qui s'appliquent à un ensemble de
valeurs d'une colonne. Ces fonctions permettent d'obtenir
des informations relatives à un ensemble de données.
Count (attribut) dénombre les occurrences d'un attribut
Avg (attribut)calcule la moyenne des valeurs d'un
attribut
Sum (attribut)
calcule la somme des valeurs d'un
attribut
Min (attribut) détermine la plus petite valeur d'un attribut
Max (attribut)détermine la plus grande valeur d'un
attribut
Toutes ces fonctions d'agrégats ignorent les valeurs
nulles mais peuvent avoir des valeurs qui se répètent;
pour préciser qu'il faut tenir compte de toutes les valeurs
ou seulement de celles qui sont distinctes, il faut préciser
ALL ou DISTINCT (par défaut, c'est ALL).
R16 : Compter le nombre de
resserres
R17 : Afficher la moyenne d’age
des habitants
R18 : Calculer la superficie
totale occupée par les resserres
R19 : Quel est l'âge le moins
élevé parmi ceux des habitants ?
Les sous-requêtes
R20 : Liste des habitants qui
ont bu de la potion magique
Autres exemples :
Liste des noms des habitants ayant absorbé une
potion magique
SELECT Nom FROM HABITANT
WHERE NumHab IN
(SELECT NumHab FROM ABSORBER
WHERE NumPotion IN
(SELECT NumPotion FROM
Potion
WHERE LibPotion LIKE 'Potion
magique%')) ;
Liste des noms des habitants ayant absorbé la potion
anti douleur
SELECT Nom FROM HABITANT
WHERE NumHab IN
(SELECT NumHab FROM ABSORBER
WHERE NumPotion =
(SELECT NumPotion FROM Potion
WHERE LibPotion = 'Potion anti douleur'))
Nom de l'habitant le plus vieux
SELECT Nom FROM HABITANT
WHERE Age = (SELECT MAX(Age)
FROM HABITANT)
Exercice - Rédiger les requêtes suivantes :
1 – Quel est le nombre de fois où une potion a été
absorbée ?
2 – Quel est le nom des habitants qui habitent dans le
même village qu’Astérix ?
3 – Afficher le nom et la qualité de la personne la plus
jeune de tous les villages.
4 – Quelle est jusqu’ici la quantité totale de potion
absorbée ?
5 – Quel est le nombre de trophées de type Bouclier
remportés jusqu’ici ?
La clause GROUP BY
Elle permet de constituer des sous-ensembles
d’occurrences dans une relation.
Noter que tous les attributs cités dans le
SELECT (sauf les fonctions d’agrégat) doivent
se retrouver dans le GROUP BY.
Exemple : Combien y a-t-il d’habitants pour
chacune des qualités ?
SELECT Qualité.NumQualité,
Count(Habitant.NumHab) AS CompteDeNumHab
FROM Qualité INNER JOIN Habitant ON
Qualité.NumQualité = Habitant.NumQualité
GROUP BY Qualité.NumQualité;
La clause HAVING
C’est l’équivalent de la clause WHERE pour un
GROUP BY.
Cela sert de critère de sélection pour les
groupes. La condition de HAVING s’applique à
chacun des sous-ensembles et élimine ceux qui
ne le satisfont pas.
Exemple (d’abord sans HAVING) : afficher le
nombre de villages par province
SELECT Province.NumProvince,
Count(Village.NumVillage) AS Nombredevillages
FROM Province INNER JOIN Village ON
Province.NumProvince=Village.NumProvince
GROUP BY Province.NumProvince;
Même question que la précédente en ne retenant
que les provinces qui contiennent plus de 2
villages.
SELECT Province.NumProvince,
Count(Village.NumVillage) AS Nombredevillages
FROM Province INNER JOIN Village ON
Province.NumProvince=Village.NumProvince
GROUP BY Province.NumProvince
HAVING Count(Village.NumVillage)>2;
Remarque : la clause HAVING doit suivre la
clause GROUP BY et doit s’appliquer à un
ensemble de données sur lequel on a pratiqué
une opération.
Exercice - Rédiger les requêtes
suivantes :
1 – Nombre de fois où chaque potion a été absorbée.
2 – Nom des potions qui ont été absorbées plus de deux fois.
3 – Nom des habitants qui ont fabriqué plus d’une fois de la
potion.
4 – Nom et qualité des habitants qui ont gagné plus de deux
trophées, classés par âge décroissant.
5 – Noms des resserres qui contiennent moins de 2
trophées.
Les opérateurs ensemblistes
Ils combinent le résultat de deux requêtes pour en faire
un seul (les deux SELECT doivent avoir le même
nombre d’attributs projetés er ceux-ci doivent être
de même type).
L’union
Exemple : donner le nom des habitants de plus de
65 ans ou ayant absorbé au moins une fois la
potion numéro 4.
SELECT Nom
FROM HABITANT
WHERE Age > 65
UNION
SELECT Nom
FROM HABITANT H, ABSORBER A
WHERE NumPotion = 4 AND H.NumHab =
A.NumHab ;
Remarque : l’opérateur UNION élimine les doublons.
Si l’on veut les garder, il faut ajouter ALL.
L’intersection
On ne garde que les lignes communes aux deux
requêtes.
Exemple : donner le numéro des habitants ayant bu
de la potion numéro 1 et de la potion numéro 5.
SELECT NumHab
FROM ABSORBER
WHERE NumPotion = 1
INTERSECT
SELECT NumHab
FROM ABSORBER
WHERE NumPotion = 5 ;
Autre solution :
SELECT DISTINCT NumHab
FROM Absorber
WHERE numPotion = 1
AND NumHab IN (SELECT NumHab FROM Absorber
WHERE NumPotion = 5);
La différence
On soustrait le résultat de la deuxième requête au
résultat de la première.
Exemple : donner les noms des habitants ayant bu
de la potion numéro 1 mais pas de la potion numéro
5.
SELECT NumHab FROM Absorber
WHERE NumPotion = 1
EXCEPT
SELECT NumHab FROM Absorber
WHERE NumPotion = 5 ;
Ou
SELECT NumHab FROM Absorber
WHERE NumPotion = 1
AND NumHab NOT IN (SELECT NumHab
FROM Absorber
WHERE NumPotion = 5);
Documents présentés
Voir sur le site du pôle science
Logiciels utilisés
Access 2003 (SGBD)
PowerPoint (présentation)
86
Sources
• Base de données proposée par Valérie Descours,
professeur d’informatique au Lycée Mermoz, dans
l’académie de Montpellier.
Pour aller plus loin
• Vidéo conférences en ligne sur les bases de données
sur le site de l’université de Stanford (Jennifer Widom).
http://online.stanford.edu/db-win13
87
Position dans le programme
BO du 13/10/2011
http://www.education.gouv.fr/pid25535/bulletin_officiel.html?cid_bo=57572
Persistance de l'information
Les données, notamment personnelles, sont susceptibles d'être
mémorisées pour de longues périodes sans maîtrise par les
personnes concernées. Prendre conscience de la persistance
de l'information sur les espaces numériques interconnectés.
Comprendre les principes généraux permettant de se comporter
de façon responsable par rapport au droit des personnes dans
les espaces numériques. La persistance de l'information se
manifeste tout particulièrement au sein des disques durs mais
aussi des mémoires caches. Elle interagit avec le droit à la vie
privée et fait naître une revendication du « droit à l'oubli ».
Structuration et organisation de l'information
On manipule de grandes quantités d'informations. Il est
nécessaire de les organiser. …Classer des informations,
notamment sous forme d'une arborescence. On peut ici
étudier le système d'organisation de fichiers en dossiers.
Un ensemble de documents unis par des liens hypertextes
fournit un exemple de classement de type graphe.