Bases de données relationnelles

O. Auzende
Ingénierie Web
Bases de données relationnelles
Système de Gestion de Bases de Données
Une base de données est un ensemble de données mémorisé par un ordinateur, organisé selon un modèle et
accessible à de nombreuses personnes.
Un Système de Gestion de Bases de Données (SGBD) représente un ensemble coordonné de logiciels permettant
de décrire, mémoriser, manipuler, traiter, interroger les ensembles de données constituant la base.
Modèle et SGBD relationnels
Le concept de base du modèle relationnel est la relation, de structure tabulaire.
Un SGBD est dit relationnel s’il est implémenté selon ce modèle. Des langages spécifiques permettent alors
d’accéder aux données de manière assez naturelle : SQL, QBE.
Quelques SGBD relationnels : ORACLE, INGRES, SYBASE, DBASE2, ACCESS, mySQ, Postgres….
Une relation a plusieurs attributs. Le degré de la relation est le nombre de ces attributs.
Exemple
La relation ELEVE, de degré 3, comporte les attributs : code_eleve, nom et prenom.
Occurrence : chaque ligne du tableau (un n-uplet) correspond à une occurrence de la relation.
Cardinalité : nombre d’occurrences de la relation dans la base. ELEVE est donc de cardinalité 4.
Clé : chaque relation contient un attribut particulier (ou un ensemble d’attributs) appelé clé, dont la valeur
permet de distinguer une occurrence de toutes les autres. Pour ELEVE, l’attribut clé est code_eleve.
On représente symboliquement une relation R par un schéma SR de la forme :
R(clé, attribut 1, attribut 2, …, attribut n).
On aura donc : ELEVE(code_eleve, nom, prenom).
Exemple de base de données relationnelle
La base de données relationnelle suivante est composée des quatre relations :
ELEVE(code_eleve, nom, prenom)
ENSEIGNANT(code_professeur, nom, prenom)
UV(code_UV, nom, annee, code_professeur)
NOTE(code_eleve, code_UV, note)
Table ELEVE
Table ENSEIGNANT
1
O. Auzende
Ingénierie Web
Table UV
Table NOTE
Les liens entre ces relations sont appelés des jointures :
- l’attribut code_eleve de la relation ELEVE représente le même type
d’information que l’attribut code_eleve de la relation NOTE.
On écrira : ELEVE.code_eleve = NOTE.code_eleve.
- l’attribut code_uv de la relation UV représente le même type
d’information que l’attribut code_uv de la relation NOTE.
On écrira : UV.code_UV = NOTE.code_UV
- l’attribut code_professeur de la relation UV représente le même
type d’information que l’attribut code_professeur de la relation
ENSEIGNANT. On écrira :
UV.code_professeur = ENSEIGNANT.code_professeur
Eléments de SQL, le langage relationnel
Le langage SQL (Structured Query Language) est le langage d’interprétation le plus utilisé. Il permet de créer,
interroger et manipuler les SGBD relationnelles.
Création, modification et suppression de relations
SQL permet de créer une relation par l’instruction CREATE TABLE :
CREATE TABLE Eleve
code_eleve NUMBER(6) NOT NULL, PRIMARY KEY,
nom
CHAR(30),
prenom CHAR(30) ;
Si la clé principale est composée de plusieurs attributs, elle est définie à la fin de l’instruction :
CREATE TABLE Eleve
Code_eleve NUMBER(6) NOT NULL,
nom
CHAR(30),
prenom CHAR(30),
num_ss NUMBER(13) NOT NULL,
PRIMARY KEY(code_eleve, num_ss) ;
SQL permet de modifier une relation par l’instruction ALTER TABLE suivie d’une spécification de
l’opération à effectuer
- L’ajout d’un attribut est déclaré par le mot-clé ADD
ALTER TABLE Etudiant ADD (moyenne NUMBER(2)) ;
- La modification d’un attribut est déclaré par MODIFY
ALTER TABLE Etudiant MODIFY adresse CHAR(100) ;
SQL permet de supprimer une relation se fait par l’instruction DROP TABLE
DROP TABLE Etudiant ;
2
O. Auzende
Ingénierie Web
Insertion, suppression et modification d’occurrences
L’insertion d’une occurrence par SQL se fait grâce à INSERT
INSERT INTO Etudiant VALUES (.., …,…,…) ;
La suppression d’une occurrence se fait par DELETE
DELETE FROM Etudiant
WHERE num_etudiant = … (valeur de la clé) ;
La modification d’une occurrence se fait par UPDATE
UPDATE Etudiant SET adresse=…
WHERE num_etudiant = … (valeur de la clé) ;
Interrogation de la base
La forme générale d’une interrogation SQL (appelée une requête) est :
SELECT attributs FROM relations WHERE conditions ;
Dans la clause optionnelle WHERE, on peut utiliser les opérateurs AND et OR.
SGBD mySQL
MySQL est un Système de Gestion de Bases de Données Relationnelles. Il comprend deux programmes
principaux :
o mysqladmin pour les tâches d’administration du serveur (arrêt et démarrage, création de bases).
o mysql qui lit les requêtes SQL, les transmet au serveur qui les exécute et affiche ensuite le résultat
Sous Windows, le serveur easyPHP couple un serveur Web et un serveur mySQL.
Lancer easyPHP.
Création de la base
On crée la base de données appelée enseignement puis on la remplit à l’aide du fichier
baseenseignement.dump fourni, dont le contenu est le suivant :
CREATE TABLE ELEVE (code_eleve tinyint(4) NOT NULL AUTO_INCREMENT, nom varchar(30), prenom
varchar(30), PRIMARY KEY(code_eleve));
INSERT INTO ELEVE VALUES(1, 'Dupuis', 'Pierre');
INSERT INTO ELEVE VALUES(2, 'Simon', 'Virginie');
...
CREATE
prenom
INSERT
INSERT
...
TABLE ENSEIGNANT (code_professeur tinyint(4) NOT NULL AUTO_INCREMENT, nom varchar(30),
varchar(30), PRIMARY KEY(code_professeur));
INTO ENSEIGNANT VALUES(1, 'Dupont', 'Serge');
INTO ENSEIGNANT VALUES(2, 'Didier', 'François');
CREATE TABLE UV (code_UV tinyint(4) NOT NULL AUTO_INCREMENT, nom varchar(20), annee
tinyint(2), code_professeur tinyint(4), PRIMARY KEY(code_UV));
INSERT INTO UV VALUES(1, 'Mathématiques', 1, 1);
INSERT INTO UV VALUES(2, 'Histoire', 1, 2);
INSERT INTO UV VALUES(3, 'Economie', 1, 5);
INSERT INTO UV VALUES(4, 'Anglais', 1, 3);
INSERT INTO UV VALUES(5, 'Mathématiques', 2, 1);
INSERT INTO UV VALUES(6, 'Géographie', 2, 2);
...
CREATE TABLE NOTE (code_eleve
KEY(code_eleve, code_UV));
INSERT INTO NOTE VALUES(1, 1,
INSERT INTO NOTE VALUES(1, 2,
...
INSERT INTO NOTE VALUES(2, 1,
INSERT INTO NOTE VALUES(2, 2,
...
tinyint(4), code_UV tinyint(4), note tinyint(2), PRIMARY
12);
9);
11);
13);
3
O. Auzende
Ingénierie Web
Exemples de requêtes simples
Recherche des noms et prénoms des élèves
SELECT eleve.nom, eleve.prenom FROM eleve;
Recherche des noms et années des UV
Recherche des attributs des élèves de prénom Virginie
SELECT * FROM eleve WHERE eleve.prenom = 'Virginie'
Recherche des attributs des UV de première année
Liste des noms des UV et noms des enseignants correspondants (cette requête nécessite une jointure)
SELECT uv.nom, enseignant.nom FROM uv, enseignant
WHERE uv.code_professeur = enseignant.code_professeur
Liste des noms des élèves ayant suivi une UV de première année
SELECT
eleve.nomFROM
eleve,
uv,
note
WHERE
eleve.code_eleve
=
note.code_eleve
AND
note.code_uv = uv.code_uv AND uv.annee =1
Pour n’avoir qu’une seule fois chaque élève :
SELECT DISTINCT eleve.nomFROM eleve, uv, note WHERE eleve.code_eleve = note.code_eleve AND
note.code_uv = uv.code_uv AND uv.annee =1
Liste des noms des élèves ayant suivi une UV de mathématiques en deuxième année
Requêtes avec fonctions ensemblistes
Note maximale en anglais première année
SELECT max(note.note) FROM note, uv WHERE note.code_uv = uv.code_uv AND uv.nom = 'Anglais'
AND uv.annee =1
Note minimale en mathématiques première année
SELECT min(note.note) FROM note, uv WHERE note.code_uv = uv.code_uv AND
uv.nom = 'Mathématiques'
4
uv.annee = 1 AND
O. Auzende
Ingénierie Web
Notes minimale et maximale en mathématiques deuxième année
Nombre d’élèves ayant la moyenne en anglais première année
SELECT
count(note.note)
FROM
note,
uv
WHERE
note.code_uv
=
uv.code_uv
AND uv.nom = 'Anglais' AND uv.annee =1 AND note.note >=10
Nombre d’élèves n’ayant pas la moyenne en mathématiques deuxième année
Somme des notes de mathématiques de première année
SELECT
sum(note.note)
FROM
note,
uvWHERE
note.code_uv
=
uv.code_uv
AND
uv.nom
=
'Mathématiques' AND uv.annee =1
Moyenne des notes d’anglais première année
SELECT avg(note.note) FROM note, uv WHERE note.code_uv = uv.code_uv AND uv.nom = 'Anglais'
AND uv.annee =1
Somme et moyenne des notes d’anglais de deuxième année
Autres fonctions, jokers, groupements
Liste des noms des professeurs (par ordre alphabétique)
SELECT DISTINCT enseignant.nom FROM enseignant ORDER BY enseignant.nom
Liste des élèves ayant entre 8 et 12 en mathématiques première année
SELECT
eleve.nom
uv.code_uv
=
FROM
eleve,
note.code_uv
note,
AND
uv
uv.nom
WHERE
=
note.code_eleve
'Mathématiques'
note.note BETWEEN 8 AND 12
Jokers :
_ veut dire un caractère quelconque
% veut dire un nombre quelconque de caractères
Noms des élèves dont la première lettre est un « D »
SELECT eleve.nom FROM eleve WHERE nom LIKE 'D%'
Noms des professeurs contenant un « p »
SELECT enseignant.nom FROM enseignant WHERE nom LIKE '%p%'
5
=
AND
eleve.code_eleve
uv.annee
=1
AND
AND
O. Auzende
Ingénierie Web
Group by :
Nom de l’UV, année de l’UV et nombre d’élèves de l’UV
SELECT uv.nom, uv.annee, count(eleve.nom) FROM eleve, uv, note WHERE eleve.code_eleve =
note.code_eleve AND note.code_uv = uv.code_uv GROUP BY uv.nom, uv.annee
Nom de l’UV, année de l’UV et moyenne des notes de l’UV
GROUP BY est souvent utilisé avec la clause HAVING pour spécifier des caractéristiques du groupement :
Nombre d’élèves de chaque UV de première année
SELECT
uv.nom,
count(eleve.nom)
FROM
eleve,
uv,
note
WHERE
eleve.code_eleve
=
note.code_eleve AND note.code_uv = uv.code_uv GROUP BY uv.nom, uv.annee HAVING uv.annee =1
Nom du professeur, nom de l’UV et moyenne des notes de l’UV pour les professeurs dont le nom commence
par « D »
Bases de données : modélisation
Le modèle entité – association
C’est un modèle de représentation de l’information, permettant de comprendre et de visualiser l’organisation des
données, mais qui n’est pas destiné directement à l’implémentation de ces données.
Lors de la conception d’une base de données, on commence par réaliser un modèle entité-association que l’on
transforme ensuite en modèle relationnel normalisé, implémentable.
Concepts de base
Une entité est un objet ayant une existence propre présentant un intérêt pour l’entreprise : le client Dupuis, le
fournisseur Durand, etc.
Un type d’entité est une classe d’entités ayant en commun un ensemble de propriétés : Client, Fournisseur, etc.
Une entité est générée à partir d’un type d’entité :
Type d’entité Client
Entité client (instance du type d’entité Client)
Une association est un lien entre entités. Il représente un verbe matérialisant une relation entre entités.
Exemple : le client Dupuis achète au fournisseur Durand.
6
O. Auzende
Ingénierie Web
Un type d’association est un lien-type entre types d’entités : “achète” est un type d’association entre les types
d’entités Client et Fournisseur :
Une propriété est une caractéristique d’un type d’entité ou d’un type d’association.
Exemple : adresse est une propriété des types d’entités de type Client et de type Fournisseur.
Un identifiant est une propriété particulière permettant de distinguer entre elles les occurrences d’un type
d’entité ou d’un type d’association. L’identifiant sera souvent utilisé comme clé.
Un type d’entité est entièrement défini par son nom, son identifiant et ses propriétés.
Exemple :
Client
identifiant : num_client
propriétés : nom_client, adresse
L’identifiant d’un type d’association est obtenu en concaténant les identifiants des types d’entité concernés par
l’association.
Exemple :
“achète”
identifiant : num_client, num_fournisseur
Cardinalités
Cardinalité d’une entité dans une association : c’est le nombre de fois minimum et nombre de fois maximum
qu’une même occurrence de l’entité peut intervenir dans les occurrences de l’association :
0
minimum
L’occurrence peut ne pas participer
1
L’occurrence participe obligatoirement L’occurrence peut participer au plus une fois
n (ou ∞)
maximum
L’occurrence peut participer plusieurs fois
Exemples
Type d’association « achète »
Un client particulier achète à un nombre de fournisseurs allant de 0 à n.
Un fournisseur particulier a de 0 à n clients qui achètent ses produits.
Le type d’association « achète » a deux « pattes » de cardinalités minimales 0 et de cardinalités maximales n.
7
O. Auzende
Ingénierie Web
Type d’association « enseignement »
Un élève suit plusieurs UV. Il obtient une note par UV.
Chaque année, une UV peut être ouverte aux élèves ou pas. Si elle est ouverte, une UV est assurée par un
professeur, mais un professeur peut assurer plusieurs UV.
A noter : le type d’association « Enseignant » a une « patte » de cardinalité maximale égale à 1, qui traduit le
fait qu’une UV particulière ne peut être enseignée que par un et un seul enseignant.
Exercice
On dispose de questions à l’aide desquelles on souhaite créer des QCM.
Les questions sont regroupées en catégories, une question n’appartenant qu’à une et une seule catégorie.
Chaque catégorie a un intitulé.
Chaque question comporte un texte (l’énoncé de la question).
A chaque question correspondent n réponses (n étant fixe) qui sont proposées à l’apprenant. Chaque réponse
comporte un texte ; la qualité de la réponse est la valeur attribuée à cette réponse.
Exemple : on propose 4 réponses à une question. Les qualités associées peuvent être 3, -1, -1, -1 ou 2, 2, -2, -2
ou toute autre combinaison de somme nulle.
Construire le modèle entité – association correspondant à cette situation.
8
O. Auzende
Ingénierie Web
Les 3 règles de passage du modèle entité-association au modèle relationnel
Règle 1
Chaque type d’entité donne naissance à une relation du même nom.
o chaque propriété du type d’entité devient un attribut de la relation.
o l’identifiant du type d’entité devient la clé de la relation.
Règle 2
Si un type d’association n’a aucune patte de cardinalité maximale égale à 1, alors :
o ce type d’association devient une relation
o chaque propriété du type d’association devient un attribut de la relation
o l’identifiant du type d’association devient la clé de la relation.
Règle 3
Si un type d’association a une patte dont la cardinalité maximale est égale à 1, alors :
o le type d’association n’est pas transformé en relation, mais est matérialisé par l’ajout d’un attribut
dans la relation source de la patte concernée
o cet attribut est la clé de la relation but de la patte concernée.
Exemples
Type d’association « achète »
Le modèle relationnel comporte les deux relations provenant des types d’entités (règle 1) :
CLIENT(num_client, nom_client, adresse)
FOURNISSEUR(num_fournisseur, nom_fournisseur, adresse)
et la relation issue du type d’association “achète” (règle 2) :
ACHAT(num_client, num_fournisseur).
Type d’association « enseignement »
9
O. Auzende
Ingénierie Web
Le modèle relationnel comporte les relations provenant des types d’entités (règle 1) :
ELEVE(code_eleve, nom, prénom)
ENSEIGNANT(code_professeur, nom, prénom)
UV(…)
la relation issue du type d’association “a suivi” (règle 2) :
NOTE(code_eleve, code_UV, note).
mais la relation UV étant à l’origine d’une patte de cardinalité maximale égale à 1, on ajoute dans UV la clé de
la destination de cette patte (règle 3), donc code_professeur (qui est appelée clé étrangère et signalée par une *) :
UV(code_UV, nom, année, code_professeur*)
On retrouve ainsi les relations présentées page 1.
Exercice
Traduire le modèle entité - association du QCM en modèle relationnel.
10