Bases de données

Bases de données
1
I. Aperçu de l’architecture de Bases de données et
des SGBD
• SGBD (système de gestion de bases de données) permet
aux utilisateurs de :
– définir, créer, maintenir une BD avec accès contrôlé aux données
• BD : ensemble cohérent de données liées entre elles
• SGBD peut contenir :
– matériel, logiciels (utilitaires), données, utilisateurs,
procédures
– matériel : constitué du système d ’informations (SI)
pour disposer de la base de données et y accéder
2
SQL
3
1. SQL
• Standard de fait normalisé
– pour la gestion des données (Langage de Manipulation
de Données, LMD),
– et aussi pour la gestion du schéma (Langage de
Définition des Données, LDD)
1. Gestion des données
Implante les opérateurs principaux de l'algèbre relationnelle
+ Fonctions ensemblistes et "spéciales"
2. Gestion du schéma
Création, modification de tables, index...
4
2. Forme d'une requête SQL
Select
From
Where
Liste d’attributs
Noms de Relations
Conditions
• Clause Where optionnelle, peut être multiple
– Where age>18 and sexe=1
• Résultat de la requête = une relation
 Aucune modification dans la base
– la relation résultat n'est pas mémorisée dans la base
– les relations initiales ne sont en aucun cas modifiées
5
3. Algèbre relationnelle
• Elle définit des opérations sur les relations
• Dans la plupart des systèmes relationnels, la
réponse à une requête s’obtient par l’utilisation
d’un ou plusieurs opérateurs relationnels
• Ces opérateurs sont définis par une algèbre
relationnelle formalisée
6
Sélection - Exemple
• “ Donnez les commandes passées après le mois
d’Octobre ”
 date>octobre(commande)
date > Octobre
select n°com, date, montant
from commande
where date >"octobre"
Commande
7
Projection - Exemple
 Nom,Dpt(Etudiant)
select distinct nom, Dpt
from Etudiant
Etudiant
n°Et
Dpt
@ Dpt
521
Loulou TC
Lyon
642
Babette
251
Fifi
662
Nom
Nom
Dpt
Loulou
TC
Babette
IF
Fifi
TC
Nom, Dpt
IF Marseille
TC
Lille
Loulou TC
Lille
 Dpt,@Dpt(Etudiant)
Dpt, @Dpt
select distinct Dpt, @Dpt
from Etudiant
Dpt
@ Dpt
TC
Lyon
IF
Marseille
TC
Lille
8
SQL : modification des données
• Chargement d'une nouvelle occurrence
Insert into Etudiant Values (265096582955569, "Dupont
Anne", "4 Rue du Bois, 69001 Lyon");
• Modification d'une occurrence
Update Etudiant
Set adresse = "10 Rue de Pix, 69001 Lyon"
Where nom = "Dupont Anne";
• Suppression d'une occurrence
Delete From Etudiant
Where No-SS = 265096582955569;
9
SQL : recherche des valeurs nulles
• Is [Not] Null permet de vérifier si l'attribut est
renseigné ou non pour une occurrence
• "Donner les n°SS et Nom des étudiants dont on ne
connaît pas le domicile"
Select N°SS-Et, Nom
From Etudiant
Where Adresse Is Null;
N°SS-Et
1640164777888
Nom
Hillman
10
SQL : opérateurs arithmétiques et
fonctions ensemblistes
• Opérateurs arithmétiques
–
–
–
–
addition +
soustraction multiplication *
division /
• Fonctions ensemblistes
–
–
–
–
–
Max : Fournit la valeur maximale
Min : Fournit la valeur minimale
Count : Fournit la cardinalité d’un ensemble
Sum : Somme de toutes les valeurs
Avg : Moyenne de toutes les valeurs
11
SQL : autres opérateurs
• Distinct
– élimine les valeurs dupliquées - select distinct nom from...
• Order by
– présente les résultats triés par ordre ascendant (Asc) [par défaut] ou
descendant (Desc) - select... From... Where... order by nom desc
• Group by
– Partitionne la relation en parties distinctes sur un critère donné
-select… from…where… group by couleur
• Having
– donne un critère de sélection sur chaque sous-groupe de la relation
(sous-groupe défini par group by) - select… from…where… group by
couleur having count(x)>1
• Between
– donne un intervalle de valeurs - where age between 10 and 15
• Like
– donne une forme pour une chaîne de caractères - where nom like 'D%'
12
SQL pour la gestion du schéma
• Dictionnaire
– contient la déclaration des relations et une description
– est stocké sous forme d'une base de données : métabase
• Des instructions SQL spécifiques
–
–
–
–
–
–
déclaration du types des données
définition de contraintes
création/suppression/modification de relations
index et clusters
relations dérivées : vues et photographies
contrôle d'accès
13
Création d'une table - Exemple
• Version de base
Create Table Etudiant
(
n°SS-ET
Number (14),
Nom
Char (20),
Adresse
Varchar2 (300)
);
14
Contraintes
• Peuvent être mises sur la ligne de l'attribut
concerné ou en fin d'instruction create
– Not Null
• l'attribut concerné doit toujours être renseigné
– Primary Key
• clé primaire de la relation (identifiant)
– Unique
• la valeur de l'attribut concerné est différente pour toutes les
occurrences (cas de clés concurrentes)
– References tab(att)
• col prend ses valeurs parmi les valeurs de la clé att de la relation
tab
– et bien d'autres...
15
Contraintes nommées
• Constraint nom_contrainte contrainte
– ajoute des contraintes nommées sur les valeurs
• Permet de les enlever / les réactiver par
programmation
– Alter table… Disable / Enable : Oracle
– Set constraints… Deferred / Immediate : norme SQL2
16
Création d'une table et remplissage en
passant
• Remplissage en passant
Create Table minidepartement (
cle number primary key,
data char(20))
as select dept, nom from Departement);
• crée la table et la remplit en prenant les valeurs de
2 colonnes de la table Département
17
Modification et suppression d'une table
• Ajouter une colonne à une table existante
Alter table Etudiant
add (N°Etud number(5));
• Modifier une colonne existante d'une table
Alter Table Etudiant
modify (Adresse Char(100)) ;
• Supprimer une table
Drop table Etudiant;
18
Contrôle d'accès
• But : protection des données
• Donner des privilèges à certains utilisateurs
– lecture Select
– insertionInsert
– modification Update ou Update <nom attribut>
• Un utilisateur peut
– donner un privilège à un autre,
– en même temps l'autoriser à transmettre ce privilège
=> arbre des privilèges transmis
• Les privilèges qu'un utilisateur donne sont un sousensemble de ses droits propres
19
Sélection (5/25)
• "Donnez tous les renseignements concernant les
étudiants dont le nom est Dupont"
Select
*
From
Where
Etudiant
nom = "Dupont";
N°Et
40
Nom
Dupont
Adresse
Lyon
20
Sélection + projection (6/25)
• Dans la grande majorité des cas, la projection et la
sélection sont associées
• "Donnez les noms des étudiants habitant Lyon"
Select
nom
From
Where
Etudiant
adresse = "Lyon";
Nom
Durand
Dupont
21
Fonctions ensemblistes (17/25)
• "Salaire moyen des maîtres de conférences"
Select Avg (Salaire)
From Enseignant
Where Grade = 'Maître de Conférence';
Avg(Salaire)
10 666,6667
22
Fonctions ensemblistes (18/25)
• "Donnez le nom des maîtres de conférences qui
gagnent plus que la moyenne des salaires des
enseignants"
Select Nom From Enseignant
Where Grade = 'Maitre de Conference'
And Salaire > (Select Avg(Salaire) From Enseignant);
NB : sous Mysql, pas d'imbrication de select.
Mais variables :
select @moySal:=avg(salaire) from Enseignant;
select nom from Enseignant
where grade="Maitre de conf"
and salaire >@moySal;
23
Fonctions ensemblistes (19/25)
• "Donnez le nombre d’UV auxquelles est inscrit
l’étudiant n° 20"
Select Count (N°UV)
From Inscription
Where N°Et = 20;
Count(N°UV)
3
24
Order by (21/25)
• "Noms des assistants par ordre alphabétique"
Select Nom
From Enseignant
Where Grade = 'Assistant'
Order By Nom Asc;
Nom
Dupont
Simon
25
Group by (22/25)
• "Donnez le salaire moyen des enseignants par
grade"
Select Grade, Avg (Salaire)
From Enseignant
Group By Grade;
Grade
Assistant
Maître de Conférence
Avg (Salaire)
8 500
10 666,6667
26
Having (23/25)
• "Donnez le N°SS des enseignants responsables de
plus de 1 UV"
Select Responsable From UV
Group By Responsable
Having Count (N° UV) > 1;
Responsable
70
27
Between (24/25)
• "Donner les noms des enseignants dont le salaire
est entre 10100 et 11100"
Select Nom
From Enseignant
Where Salaire Between 10100 And 11100;
Nom
Grey
28
Like (25/25)
• "Donnez les noms des étudiants dont le nom
commence par D"
Select Nom
From Etudiant
Where Nom Like 'D%';
29