Catégorie Technique Introduction aux Bases de Données

Catégorie Technique
Introduction aux Bases de Données Relationnelles
et au Modèle Entité-Association
Techniques Informatiques
Ludovic Kuty
Ingénieurs Industriels en Informatique
3èmes Bachelier
Groupe B38
Année Académique 2014–2015
12 février 2015 16 :27
Catégorie Technique
Introduction aux Bases de Données Relationnelles
et au Modèle Entité-Association
Techniques Informatiques
Ludovic Kuty
Ingénieurs Industriels en Informatique
3èmes Bachelier
Groupe B38
Année Académique 2014–2015
12 février 2015 16 :27
HEPL 2014–2015
Table des matières
Ludovic Kuty
Table des matières
1
Présentation
1.1 Objectifs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2 Evaluation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2
Introduction
2.1 Concepts généraux . . . . .
2.2 Déﬁnitions . . . . . . . . . .
2.3 Architecture à trois niveaux
2.4 Modélisation . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
9
9
10
11
13
Abstractions
3.1 Classiﬁcations .
3.2 Aggrégations .
3.3 Cardinalités . .
3.4 Généralisations
3
4
5
6
7
7
7
7
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
17
17
18
20
21
Modèle Entité-Association
4.1 Présentation . . . . . .
4.2 Entités . . . . . . . . .
4.3 Attributs . . . . . . . .
4.4 Associations . . . . . .
4.5 Rôle et cardinalité . . .
4.6 Identiﬁants . . . . . . .
4.7 Entité faible . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
25
25
26
26
27
28
29
30
Modèle Relationnel
5.1 Présentation . . . . . . . . . . . .
5.2 Déﬁnitions . . . . . . . . . . . . .
5.3 Clés . . . . . . . . . . . . . . . . .
5.4 Contraintes . . . . . . . . . . . . .
5.5 Algèbre relationnelle . . . . . . .
5.6 Autres méthodes d'interrogation
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
33
33
34
37
37
38
42
Modèle Entité-Association -> Modèle Relationnel
6.1 Présentation . . . . . . . . . . . . . . . . . . . . . . . . . .
6.2 Etape indépendante du modèle logique . . . . . . . . . .
6.3 Etape dépendante du modèle logique . . . . . . . . . . .
6.3.1 Elimination des identiﬁants externes . . . . . . . .
6.3.2 Elimination des attributs composés et multivalués
6.3.3 Entités . . . . . . . . . . . . . . . . . . . . . . . . .
6.3.4 Associations . . . . . . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
45
45
46
49
49
49
50
50
Installation Oracle
7.1 Clients et serveurs .
7.2 Installation . . . . .
7.3 Démarrage . . . . .
7.4 Arrêt . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
55
55
56
57
58
Techniques Informatiques
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
5
HEPL 2014–2015
7.5
8
Table des matières
Ludovic Kuty
Maintenance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
63
63
63
65
66
67
Langage de Déﬁnition de Données (LDD)
9.1 Présentation . . . . . . . . . . . . . . .
9.2 Types de donnée . . . . . . . . . . . .
9.2.1 Types numériques . . . . . . .
9.2.2 Type booléen . . . . . . . . . .
9.2.3 Types caractères . . . . . . . . .
9.2.4 Types binaires . . . . . . . . . .
9.2.5 Types temporels . . . . . . . . .
9.2.6 Types intervalles . . . . . . . .
9.3 Instructions du LDD . . . . . . . . . .
9.3.1 Types de table . . . . . . . . . .
9.3.2 CREATE TABLE . . . . . . . .
9.3.3 DROP TABLE . . . . . . . . . .
9.3.4 ALTER TABLE . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
69
69
70
71
72
73
74
75
78
79
80
81
86
88
10 Langage de Manipulation de Données (LMD)
10.1 Présentation . . . . . . . . . . . . . . . . .
10.2 SELECT . . . . . . . . . . . . . . . . . . . .
10.2.1 Liste de sélection . . . . . . . . . .
10.2.2 Clause FROM . . . . . . . . . . . .
10.2.3 Clause WHERE . . . . . . . . . . .
Les prédicats . . . . . . . . . . . .
Les expressions caractères . . . . .
Les expressions dates-temps . . .
Les expressions intervalles . . . .
Les fonctions ensemblistes . . . . .
10.3 INSERT . . . . . . . . . . . . . . . . . . . .
10.4 UPDATE . . . . . . . . . . . . . . . . . . .
10.5 DELETE . . . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
91
91
92
92
93
94
95
103
105
106
107
119
120
121
9
Langage SQL
8.1 Historique . . . . . . . . . . . .
8.2 Conformité et implémentations
8.3 Concepts du langage SQL . . .
8.4 Notation BNF . . . . . . . . . .
8.5 Interfaçage avec SQL . . . . . .
58
Références
Techniques Informatiques
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
121
6
HEPL 2014–2015
1. Présentation
Ludovic Kuty
Chapitre 1
Présentation
1.1 Objectifs
objectifs & ressources
Objectifs :
• Présenter l'architecture générale des SGBDR
• Présenter les différents niveaux de représentation dans une BD
• Présenter le modèle EA et le modèle relationnel
• Présenter le langage SQL
Support et ressources :
• Email : [email protected]
• Transparents du cours à https://cours.khi.be/techinf/
1.2 Evaluation
évaluation
• Théorie : examen écrit à livre fermé
– Matière du cours
– Exercices relatifs au cours et au laboratoire
• Laboratoire : évaluation continue
– Modélisation d'un problème avec le modèle EA
– Traduction de la modélisation dans le modèle relationnel
– Implémentation dans une BD Oracle
– Spéciﬁcation de différentes contraintes
– Utilisation de SQL pour accéder aux données et aux méta-données
Techniques Informatiques
7
HEPL 2014–2015
Techniques Informatiques
1. Présentation
Ludovic Kuty
8
HEPL 2014–2015
2. Introduction
Ludovic Kuty
Chapitre 2
Introduction
2.1 Concepts généraux
concepts généraux
• Base de données (BD). Conteneur de données informatiques persistentes concernant un sujet donné
• Système de gestion de bases de données (SGBD). Système informatisé de collecte de données. Le SGBD agit sur une BD grâce à des opérations aux opérations ci-dessous. En anglais
on parle de CRUD (Create, read, update and delete).
–
–
–
–
Recherche
Ajout
Modiﬁcation
Effacement
• Utilisateurs
– Utilisateur ﬁnal
– Programmeur (API)
– Administrateur (DBA)
• Programme
concepts généraux
database management
system
user
database
application
program
base de données
Database (DB).
Techniques Informatiques
9
HEPL 2014–2015
2. Introduction
Ludovic Kuty
• Ensemble intégré ‰ chacun possède sa collection de données
• Est une modélisation de la réalité
• Contient les données opérationnelles sur un sujet : entités + associations
Propriétés désirables :
• Etre utilisable concurremment càd par plusieurs utilisateurs en même temps
• Etre non-redondante càd pas de duplication d'information
• Gérer efﬁcacement les ressources (temps, espace)
• Résister aux pannes du système
• Etre sécurisée càd ﬁltrer les accès selon les utilisateurs
système de gestion de bases de données
Database management system (DBMS).
• Permet de mettre en oeuvre les propriétés désirables
• Est le garant de la cohérence de la modélisation
• Isole la BD du monde extérieur ñ point de passage obligé pour tout accès aux données
2.2 Déﬁnitions
déﬁnitions
• Modèle = langage/notation permettant de décrire des schémas
• Schéma = description structurelle de la réalité = modélisation
• Instance de schéma = ensemble des données correspondant à la réalité à l'instant t
• Parmi toutes les données possibles, seulement certaines d'entre elles sont valides par rapport
à un schéma donné. Ce sont des instances du schéma et elles décrivent la partie pertinente
de la réalité (celle qu'on a modélisé)
• Le schéma agit donc comme un ﬁltre pour les instances de DB illégales.1
déﬁnitions
modèle
le modèle fournit les règles
pour structurer les données
perception de
la structure
de la réalité
schéma
le schéma fournit les règles
pour vérifier qu’une instance
est valide
description de
la réalité à un
moment donné
instance
1 Cette manière de voir est très importante car cela montre que les contraintes doivent se trouver le plus près possible
des données de la DB càd dans le schéma.
Techniques Informatiques
10
HEPL 2014–2015
2. Introduction
Ludovic Kuty
2.3 Architecture à trois niveaux
évolution du schéma
2 types d'évolution :
• Structure logique
– Par ex. on décide de stocker le num registre national en + de l'adresse de l'emprunteur
– Par ex. on décide de garder l'historique des emprunts
• Structure physique
– Utilisation d'index
– Utilisation d'enregistrements de taille variable
– Gestion différente des données effacées
architecture à 3 niveaux du SGBD
• Garantit l'indépendance entre les données et les programmes qui les utilisent
• Chaque niveau est représenté par un ou plusieurs schémas décrit avec un langage de déﬁnition de données propre (LDD, data deﬁnition language ou DDL). Dans notre cas, ce sera
toujours SQL
vue externe
(vue individuelle)
schéma
externe 1
schéma
externe 2
vue logique
(vue communautaire)
schéma
logique
vue physique
(vue orientée stockage)
schéma
physique
schéma
externe 3
SGBD
stockage
I/O
niveau logique
• Un schéma logique = description structurelle des données stockées
• Déﬁni avec le modèle de données de la BD (data model). Par ex. modèle relationnel
• Les données de la BD à l'instant t constituent une instance du schéma logique
: Table students : niveau logique
create table students (
id integer,
Techniques Informatiques
11
HEPL 2014–2015
2. Introduction
Ludovic Kuty
name varchar2(30 byte),
login varchar2(30 byte),
age integer,
constraint students_pk primary key (id)
using index pctfree 10 initrans 2 maxtrans 255 nocompress logging
tablespace users enable
) segment creation deferred
pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging
tablespace users ;
niveau externe
• Déﬁni en termes du schéma logique
• Ensemble de schémas déﬁnissant chacun une vue externe2 (external view)
– Personnalise la vision qu'à un utilisateur u des données
– Pour u, la vue externe est la BD
: Table students : niveau externe
create or replace view courseinfo as
select c.name as course, f.name as teacher, count(e.student) as Ðâ
enrollment
from courses c, faculty f, enrolled e, teaches t
where c.id = e.course and f.id = t.teacher and t.course = c.id
group by c.name, f.name;
niveau physique
• Format des données (structuré en bytes) et choix du stockage
• Création d'index pour accélérer l'accès aux données
• Espace d'adressage linéaire ‰ adressage physique sur disque. Pas de notions d'I/O, blocs,
pages, cylindres
: Table students : niveau physique
create table students (
id integer,
name varchar2(30 byte),
login varchar2(30 byte),
age integer,
constraint students_pk primary key (id)
using index pctfree 10 initrans 2 maxtrans 255 nocompress logging
tablespace users enable
) segment creation deferred
pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging
tablespace users ;
schéma externe (ex)
Schéma externe (vue). Permet aux étudiants de trouver pour chaque cours le nom du prof et
le nbre de personnes inscrites
2 Attention,
la vue externe sera elle-même constituée d'un ensemble de vues au sens SQL du terme.
Techniques Informatiques
12
HEPL 2014–2015
2. Introduction
Ludovic Kuty
courseinfo(course : string, teacher : string, enrollment : integer)
: Vue courseinfo
select * from courseinfo order by course;
COURSE
--------course 1
course 2
course 3
course 4
course 5
course 6
course 7
TEACHER
ENROLLMENT
---------- ---------teacher 8
10
teacher 7
19
teacher 7
14
teacher 7
16
teacher 9
15
teacher 3
14
teacher 4
12
schéma logique (ex)
Schéma logique (relationnel)
students(id : integer, name : string, login : string, age : integer)
faculty(id : integer, name : string, sal : real)
courses(id : integer, name : string, credits : integer)
rooms(id : integer, address : string, capacity : integer)
enrolled(student : integer, course : integer, grade : string)
teaches(teacher : integer, course : integer)
meets_in(course : integer, room : integer, time : date)
• Le schéma relationnel est déﬁni dans intro/indep_logical_schema.sql
• Les données initiales sont ajoutées grâce à intro/indep_logical_data.sql
modiﬁcation du schéma logique (ex)
• La relation faculty est remplacée par deux relations pour distinguer ce qui est conﬁdentiel
de ce qui ne l'est pas. De plus de l'information a été ajoutée.
• Il sufﬁt de redéﬁnir la vue courseinfo. Le code qui l'utilise ne doit pas être modiﬁé.
• La couche supplémentaire qu'est le schéma externe permet de se rendre indépendant des
modiﬁcations intervenant au niveau du schéma logique.
: La relation faculty est modiﬁée en deux relations.
faculty_public(id : integer, name : string, office : string)
faculty_private(id : integer, sal : real)
2.4 Modélisation
modélisation
Comment créer les schémas externes, logique et physique ?
Deux méthodes :
• Réalité Ñ schéma conceptuel / vue Ñ schéma conceptuel global Ñ schéma logique Ñ schémas externes
Techniques Informatiques
13
HEPL 2014–2015
2. Introduction
Ludovic Kuty
• Réalité Ñ schéma conceptuel Ñ schéma logique Ñ schémas externe
Ensuite on s'occupe du schéma physique.
Dans tous les cas :
• On crée un schéma conceptuel à partir de la partie pertinente de la réalité
• Il est déﬁni grâce à un modèle de données abstrait (semantic data model). Par ex. le modèle
entité-association (EA, entity-relationship ou ER)
• Il permet de créer un schéma logique par une étape de correspondance (mapping)
• Par ex. on passe du modèle EA au modèle relationnel
modélisation
monde
réel
design
conceptuel
schéma conceptuel
indépendant du
modèle de données
et du SGBD
design
logique
schéma logique
dépend du modèle de
données du SGBD
indépendant du SGBD
design
physique
schéma physique
dépend du SGBD
modélisation
Il existe des étapes additionnelles :
• Analyse des exigences. Ce que les utilisateurs veulent : type de données, contraintes, performance, …Par ex. use cases (Cockburn, UML). Permet de créer le schéma conceptuel
• Rafﬁnement du schéma. Normalisation du schéma logique
• Design sécurité. Identiﬁcation des rôles, accès limités à la BD (vues). Se fait en dernier lieu
intérêt de la modélisation conceptuelle
• Sufﬁsamment simple que pour établir un pont entre utilisateur et concepteur
– Augmente la qualité du schéma
– Rend l'utilisateur plus enclin à utiliser le système développé
• Indépendante du type de DBMS utilisé
Techniques Informatiques
14
HEPL 2014–2015
2. Introduction
Ludovic Kuty
– Le choix du DBMS cible peut être postposé
– Si le DBMS ou les besoins applicatifs changent, on dispose du schéma comme nouveau
point de départ
• Le schéma conceptuel et sa documentation facilite grandement la compréhension des schémas logiques et des applications qui les utilisent ñ maintenance et mise à jour facilitées
Techniques Informatiques
15
HEPL 2014–2015
Techniques Informatiques
2. Introduction
Ludovic Kuty
16
HEPL 2014–2015
3. Abstractions
Ludovic Kuty
Chapitre 3
Abstractions
abstractions
Modélisation ñ abstraction.
Trois mécanismes disponibles dans le modèle de données :
1. La classiﬁcation
2. L'aggrégation
3. La généralisation
3.1 Classiﬁcations
classiﬁcation
• Un concept = une classe d'objets du monde réels avec des propriétés communes
• Un objet peut appartenir à plusieurs classes selon le choix de la classiﬁcation
Classe
est membre de
Objet 1 Objet 2
Objet n
classiﬁcation (exemples)
• Vélo = concept ou classe d'objets = { vélo de ludovic, vélo dans la cour, …}
vélo
vélo de
ludovic
vélo dans
la cour
vélo de
Eddy Merckx
• Mois = concept ou classe d'objets = { janvier, février, …}
Techniques Informatiques
17
HEPL 2014–2015
3. Abstractions
Ludovic Kuty
mois
janvier
février
décembre
3.2 Aggrégations
aggrégation
Une classe est déﬁnie comme un ensemble de classes composantes.
Classe
est une
partie de
Classe 1 Classe 2
Classe n
aggrégation (exemples)
• Vélo = aggrégation de { roue, pédale, guidon }
vélo
roue
guidon
pédale
• Personne = aggrégation de { nom, sexe, salaire }
personne
nom
sexe
salaire
aggrégation (exemples)
• Utilise = aggrégation de { personne, bâtiment }
• Aggrégation binaire.
Techniques Informatiques
18
HEPL 2014–2015
3. Abstractions
Ludovic Kuty
utilise
aggrégation
personne
bâtiment
classification
Jean Pol
Luc
maison
M1
bureaux
B1
et dans les langages de programmation ?
• Il existe des mécanismes semblables dans les langages de programmation
• La généralisation est représentée par l'héritage dans les langages OO
value
classification
aggregation
field types
record type
value
correspondances (aggrégation)
• Une aggrégation établit une correspondance (mapping) entre les éléments de deux ou plusieurs classes
• Deux classes ” binaire, plusieurs classes ” n-aire
• L'aggrégation est représentés graphiquement par un diagramme ensembliste
• Une classe peut participer à plusieurs aggrégations
• Utilise = aggrégation de { personne, bâtiment }
• Possède = aggrégation de { personne, bâtiment }
• La notion de cardinalité intervient
b1
p1
b1
p1
b2
p2
b2
p2
b3
p3
b4
utilise
Techniques Informatiques
b3
p3
b4
possède
19
HEPL 2014–2015
3. Abstractions
Ludovic Kuty
3.3 Cardinalités
cardinalité minimale
• Soit l'aggrégation A entre les classes C1 et C2
• La cardinalité minimale mincard(C1 , A) est le nombre mininum de correspondances auquel
un élément de C1 doit participer
• Dans l'exemple précédent :
– mincard(personne, utilise) = 1
– mincard(batiment, utilise) = 0
– mincard(personne, possede) = 0
– mincard(batiment, possede) = 1
Les deux valeurs importantes sont 0 et 1
• mincard(C1 , A) = 0 ñ participation optionnelle.
• mincard(C1 , A) ą 0 ñ participation obligatoire.
cardinalité maximale
• Soit l'aggrégation A entre les classes C1 et C2
• La cardinalité maximale maxcard(C1 , A) est le nombre maximum de correspondances auquel un élément de C1 peut participer
• Dans l'exemple précédent :
– maxcard(personne, utilise) = n
– maxcard(batiment, utilise) = n
– maxcard(personne, possede) = n
– maxcard(batiment, possede) = 1
Les deux valeurs importantes sont 1 et n (noté également 8).
cardinalité
• Soit A une aggrégation binaire des classes C1 et C2
• Si mincard(C1 , A) = m1 et maxcard(C1 , A) = M1
• Alors la cardinalité de la classe C1 dans A est la paire card(m1 , M1 )
• Idem pour C2
• Tout ceci peut-être généralisé aux aggrégations n-aires. Par exemple la relation "se donne"
dans laquelle participent les classes "cours", "jour" et "local" (sur une semaine)
types de correspondance
• one-to-one. maxcard(C1 , A) = 1 et maxcard(C2 , A) = 1
• one-to-many. Sens C1 vers C2 , maxcard(C1 , A) = n et maxcard(C2 , A) = 1
• many-to-one. Sens C1 vers C2 , maxcard(C1 , A) = 1 et maxcard(C2 , A) = n
• many-to-many. maxcard(C1 , A) = n et maxcard(C2 , A) = n
Techniques Informatiques
20
HEPL 2014–2015
3. Abstractions
one-to-one
one-to-many
Ludovic Kuty
many-to-one
many-to-many
3.4 Généralisations
généralisation
• Une classe est déﬁnie comme un sous-ensemble d'une autre
• Les abstractions déﬁnies pour la classe générique sont héritées par les sous-classes
Classe
est un /
est une
Classe 1 Classe 2
Classe n
généralisation (exemple)
• Un auteur est une personne.
• Un auteur écrit des livres d'un certain genre.
personne
nom
sexe
auteur
genre
correspondances (généralisation)
• Une généralisation établit une correspondance entre la classe générique et les sous-classes
• Chaque élément d'une sous-classe correspond à un seul élément de la classe générique
• Mais un élément de la classe générique peut correspondre à 0, 1 ou plusieurs éléments de
sous-classes ñ notion de couverture
Techniques Informatiques
21
HEPL 2014–2015
3. Abstractions
Ludovic Kuty
généralisation (couverture)
• Chaque élément de la classe auteur correspond à un élément de la classe personne
• Mais, un élément de personne ne correspond pas nécessairement à un élément de auteur
• On dira que la couverture est partielle
personne
auteur
généralisation (couverture)
Soit G la classe générique et C1 , . . . , Cn les sous-classes
• Totale ou partielle. Totale = chaque élément de G correspond à au moins un élément des Ci .
Partielle = D un élément de G qui n'a pas de correspondance dans un Ci
• Exclusive ou imbriquée1 . Exclusive = chaque élément de G correspond à au plus un élément
des Ci . Imbriquée = D un élément de G qui correspond à plusieurs Ci
homme
homme
femme
employé
totale
exclusive
partielle
imbriquée
véhicule
re
vélo
personne
voitu
personne
partielle
exclusive
joueur
tennis
football
totale
imbriquée
généralisation (exemple)
1 Overlapping.
Techniques Informatiques
22
HEPL 2014–2015
Techniques Informatiques
3. Abstractions
Ludovic Kuty
23
HEPL 2014–2015
Techniques Informatiques
3. Abstractions
Ludovic Kuty
24
HEPL 2014–2015
4. Modèle Entité-Association
Ludovic Kuty
Chapitre 4
Modèle Entité-Association
4.1 Présentation
outil de modélisation
• DB-Main 9.1.4 http ://www.db-main.be/.
DB-MAIN est un outil gratuit de modélisation et d'architecture de données. Il est conçu pour aider les développeurs et les analystes dans la plupart des processus d'ingénierie des données, dont :
• Les processus de conception : analyse des besoins, analyse conceptuelle, normalisation, intégration
de schémas, conception logique, conception physique, optimisation, génération de code.
• Les transformations : transformations de schémas, transformations de modèles, ETL.
• La rétro-ingénierie et la compréhension de programmes : analyse de schémas (COBOL, CODASYL,
IMS, IDMS, SQL, XML, ...), analyse de code, rétro-ingénierie des données et des ﬂux de données.
• Maintenance, évolution et intégration : migration de données, évolution des bases de données, analyse
d'impacts, intégration et fédération de bases de données, conception et génération d'accesseurs de
données.
• Et beaucoup d'autres domaines comme les bases de données temporelles et actives, les entrepôts de
données, l'ingénierie XML, ...
présentation du modèle EA
Le modèle entité-association (entity-relationship) est un modèle de données conceptuel.
• Introduit par Peter Chen en 1976 dans l'article "The Entity-Relationship Model - Toward a
Uniﬁed View of Data", ACM Transactions on Database Systems
• Très populaire
• Déﬁnit un certain nombre de concepts qui sont l'expression des mécanismes d'abstraction
vu précédemment : entité, association, attribut, identiﬁant
qualité d'un modèle
Les qualités désirées sont :
• Expressivité. Disposer des concepts nécessaires pour avoir une modélisation proche de la
réalité
• Simplicité. Pour faciliter sa compréhension et son utilisation par les utilisateurs et les concepteurs du schéma
• Minimalité. Chaque concept est unique et ne peut être exprimée à l'aide d'autres concepts
du modèle. Ils sont orthogonaux
Techniques Informatiques
25
HEPL 2014–2015
4. Modèle Entité-Association
Ludovic Kuty
• Formalisation. Les concepts doivent être bien déﬁnis et mathématiquement manipulables
intérêt du modèle ER
Les qualités du modèle ER sont :
• Bonne expressivité. Les trois mécanismes d'abstractions sont présents
• Simplicité moyenne. Les notions de cardinalités et identiﬁants ne sont pas aisées à manipuler. Elles sont cependant très utiles
• Minimalité. Attention, cela ne veut pas dire qu'une seule modélisation de la réalité est
possible. Seuls les attributs composés peuvent être remplacés par un mélange d'entités et
d'associations
• Formellement déﬁni. Chaque concept peut être représenté graphiquement et les diagrammes
sont faciles à lire
4.2 Entités
entité et instance d'entité
Entity.
• Entité. Une classe d'objets du monde réel avec des propriétés communes. Par ex. les livres
de ma collection, les personnes, les écoles de la province, les groupes de l'ISIL
• Instance d'entité. Un objet bien précis de la classe. Par ex. le livre The Pragmatic Programmer,
Alan Turing, la HEPL, le groupe B38
• Chaque entité est représentée par un rectangle
• Chaque entité contient un ensemble d'attributs
4.3 Attributs
attribut
Attribute.
• Attribut. Propriété, caractéristique d'une entité ou d'une association (nom / valeur)
• Domaine. Ensemble de valeurs que peut prendre un attribut. Par ex. ensemble des chaînes
de taille 20, nombres entre 1 et 20
• Les attributs contiennent l'information extensionnelle (les données)
• Les cardinalités s'appliquent également ñ optionnel (0), univalué (1) ou multivalué (n)
• Ils peuvent être composés d'autres attributs
Techniques Informatiques
26
HEPL 2014–2015
4. Modèle Entité-Association
Vue graphique
Ludovic Kuty
Vue textuelle
exemple 1 (entités + domaines)
entité (terminologie)
Attention, plusieurs terminologies existent.
• Entité = classe, instance d'entité = élément de la classe
• Ensemble d'entités = classe, entité = élément de la classe
• Type d'entité = classe, entité = élément de la classe
Il faut donc être conscient de la terminologie choisie. Nous prendrons la première.
4.4 Associations
association et instance d'association
Relationship.
• Association. Représente l'aggrégation de deux ou plusieurs entités. Il s'agit du type de liaison, de relation entre ces entités
• Instance d'association. Etablit une correspondance entre deux ou plusieurs instances d'entité.
• L'association peut avoir des attributs. Par ex. l'association Dirige entre l'entité Employé et
l'entité Département a un attribut depuis qui indique le début d'entrée en fct
• Le nom de l'association est en général un verbe conjugué. Par ex. Fait_partie, A_écrit, Dirige
• L'association est représentée par un losange
exemple 1 (associations)
Techniques Informatiques
27
HEPL 2014–2015
4. Modèle Entité-Association
Vue graphique
Ludovic Kuty
Vue textuelle
4.5 Rôle et cardinalité
rôle
• Participation d'une entité à une association
• Donne un sens à la présence de l'entité (nommé)
• Le rôle possède une cardinalité
• Par ex. l'entité Employé joue le rôle de Directeur dans l'association Dirige
• Par ex. l'entité Personne joue le rôle de Ecrivain dans l'association A_Ecrit
rôle
• Un manager est un Employé et Dirige d'autres employés
• Association réﬂexive
Techniques Informatiques
28
HEPL 2014–2015
4. Modèle Entité-Association
Ludovic Kuty
cardinalité
• Une cardinalité par rôle càd par couple association / entité
• Exprime le nombre de participations possibles d'une instance de l'entité dans l'association
• Constitue une contrainte que les données doivent respecter
Exemples :
• Toute personne peut avoir écrit zéro, un ou plusieurs livres (peut participer zéro, une ou
plusieurs fois à l'association A_écrit) ñ min = 0 et max = n
• Un livre a au moins été écrit par une personne (peut participer une ou plusieurs fois à
l'association A_écrit) ñ min = 1 et max = n
• Un employé ne peut diriger qu'un seul département (ne peut participer que zéro ou une fois
à l'association Dirige) ñ min = 0 et max = 1
cardinalité (exemple)
4.6 Identiﬁants
identiﬁant
Identiﬁer.
• Détermine de manière unique les instances d'une entité E
• Est composé d'attributs de E et/ou d'entités en liaison avec E
• Egalement appelé clé (key) ou clé candidate (candidate key)
Formellement :
• Soit E une entité et A1 , . . . , An des attributs obligatoires univalués de E
• Soit E1 , . . . , Em des entités liées à E par des associations binaires obligatoires one-to-one ou
many-to-one R1 , . . . , Rm ñ mincard(E, Ri ) = maxcard(E, Ri ) = 1
• Si I = tA1 , . . . , An , E1 , . . . , Em u est un identiﬁant de E avec n ě 0, m ě 0, n + m ě 1
• La valeur de I pour un instance e de E est déﬁnie comme étant les valeurs des attributs Ai
et de toutes les instances des Ej
identiﬁant (exemple 1)
identiﬁant (propriétés)
Un identiﬁant I de E est :
Techniques Informatiques
29
HEPL 2014–2015
4. Modèle Entité-Association
Vue graphique
Ludovic Kuty
Vue textuelle
1. Unique. Il ne peut pas y avoir deux instances avec la même valeur pour I
2. Minimal. Si on laisse tomber un des Ai ou Ej , la propriété 1 n'est plus valable
C'est la notion de clé candidate ou de super-clé1 minimale
• Propriété des entités héritées lors d'une généralisation ñ l'identiﬁant de l'entité générique
est aussi identiﬁant de la sous-entité
• La sous-entité peut également avoir des identiﬁants propres
• Par ex. une personne est identiﬁée par son numéro de registre national et un employé (aussi
une personne) par son numéro d'employé au sein de la société pour laquelle il travaille
identiﬁant (classiﬁcation)
• Un identiﬁant est simple si n + m = 1. Il est composé si n + m ą 1
• Un identiﬁant est interne si m = 0. Il est externe si n = 0
• Un identiﬁant est mixte if n ą 0 et m ą 0
Pour souci de simplicité, on préfère un identiﬁant
• Interne à un externe
• Simple à un composé
4.7 Entité faible
entité forte et faible
• Une entité forte (strong entity) est une entité qui peut être identiﬁée de manière interne
• Une entité faible (weak entity) est une entité qui ne peut pas être identiﬁée de manière interne
(externe ou mixte)
Exemple d'entité faible :
1 Une
super-clé est une clé respectant la propriété 1.
Techniques Informatiques
30
HEPL 2014–2015
4. Modèle Entité-Association
Ludovic Kuty
entité faible
Transaction
1
12/2/2015 t1
50 instance
d'entité
≠
1
12/2/2015 t2
50 instance
d'entité
Compte
valeurs de l'identifiant
instance
d'association
valeurs de l'identifiant
c1 100
50
instance
d'entité
instance
d'association
c2 200
50
instance
d'entité
entité faible
• L'entité Transaction comprend deux instances d'entités différentes t1 et t2 même si les valeurs
des trois attributs sont les mêmes
• Même sans aucun attribut dans une entité, on peut avoir des instances d'entité qui sont clairement identiﬁées et différentes. Il n'y aura simplement pas d'identiﬁant
• L'identiﬁant I est formé par l'attribut Numéro et l'entité Compte
• La valeurs des identiﬁants sont :
– Pour t1 , on a 1 et c1
– Pour t2 , on a 1 et c2
et les mécanismes d'abstraction ?
Classiﬁcation.
• Une entité est une classe d'objets du monde réel avec des propriétés communes.
• Une association est une classe de faits élémentaires (atomiques) qui sont en rapport avec deux
ou plusieurs classes.
• Un attribut est une classe de valeurs représentant des propriétés atomiques d'entités ou
d'associations.
Aggrégation.
• Une entité est une aggrégation d'attributs.
• Une association est une aggrégation d'entités et d'attributs.
• Un attribut composé est une aggrégation d'attributs.
Généralisation.
• Capturée par les hiérarchies (super-types / sous-types).
• On peut avoir un sous-ensemble lorsqu'il n'y a qu'une seule sous-entité. C'est un cas particulier de couverture partielle et exclusive.
Techniques Informatiques
31
HEPL 2014–2015
Techniques Informatiques
4. Modèle Entité-Association
Ludovic Kuty
32
HEPL 2014–2015
5. Modèle Relationnel
Ludovic Kuty
Chapitre 5
Modèle Relationnel
5.1 Présentation
généralités
Trois piliers :
• Structurel
– Relation
– Domaine / Attribut
– Clé primaire
– Clé étrangère
• Intégrité. Règles à satisfaire par les relations
– De domaine
– De relation
– De base de données (référentielle, …)
• Manipulation. Opérateurs de manipulation des relations
– Sémantiques (liés aux domaines)
– Ensemblistes : union, intersection, différence, produit cartésien
– Relationnels : restriction, projection, jointure, division
généralités
• E. F. Codd, A Relational Model of Data for Large Shared Data Banks, CACM 13, 1970.
• Bases théoriques solides : théorie des ensembles + logique des prédicats
• Concepts simples ñ facilité d'apprentissage + communicabilité accrue
• Pas impératif ou procédural (le comment) mais déclaratif (le quoi) ñ grande autonomie
généralités
Techniques Informatiques
33
HEPL 2014–2015
5. Modèle Relationnel
Ludovic Kuty
présentation informelle
• Une relation est représentée par une table : structurel et extensionnel
• Une table comprend des lignes et des colonnes
• Une ligne = un tuple et un titre de colonne = un nom d'attribut
• Attention : ordre implicite
Soit la table Livres.
Titre
An Introduction to Database Systems
Database Management Systems
Editeur
Année
Addison-Wesley
2004
McGraw-Hill
2000
5.2 Déﬁnitions
tuple
• Soit des types Ti (ou domaines)
• Soit des noms d'attributs Ai distincts
• Soit des valeurs vi de type Ti
• Un tuple est un ensemble1 de triplets de la forme
xAi , Ti , vi y
Par exemple :
1 Ensemble
au sens mathématique càd pas d'ordre, pas de doublons.
Techniques Informatiques
34
HEPL 2014–2015
5. Modèle Relationnel
Ludovic Kuty
• Le tuple de la 2ème ligne est l'ensemble des triplets
x"Année", Entiers, 2000y
x"Titre", Chaînes de taille 50, "Database Management Systems"y
x"Editeur", Chaînes de taille 30, "McGraw-Hill"y
tuple
• Un attribut est la paire xAi , Ti y. Ai identiﬁe l'attribut de manière unique
• La valeur de l'attribut Ai est vi
• Le type de l'attribut Ai est Ti
• L'ensemble complet des attributs est l'en-tête (heading) du tuple. On dit aussi que c'est le type
du tuple
valeur vs variable
• Une valeur est
– Constante ñ ne peut pas être modiﬁée !
– Immatérielle (espace & temps)
– Nécessite un encodage particulier
– Possède un type
Par ex. le nombre 3, la chaîne "Bonjour".
• Une variable est
– Conteneur pour un encodage d'une valeur
– Matérielle (espace & temps)
– Peut être modiﬁée pour contenir une autre valeur
– Possède un type
Par ex. la variable a de type entier ayant la valeur 3.
relation vs relvar
• Relation ” valeur. La représentation extensionnelle en 2D de la table Livres est la représentation d'une relation
• Relvar ” variable. La table Livres est un relvar. Notion de type ou de structure
• Important pour comprendre les opérations et la nature déclarative du modèle relationnel.
relation
Une relation2 est composée de
• Un en-tête (heading) = en-tête de tuple (cfr. slide 71). C'est son type
• Un corps (body) = ensemble de tuples
Propriétés
• Chaque tuple contient une seule valeur pour chaque attribut. On dit que la relation est normalisée ou en première forme normale (1NF)
2 Càd
une valeur.
Techniques Informatiques
35
HEPL 2014–2015
5. Modèle Relationnel
Ludovic Kuty
• Il n'y a pas d'ordre gauche-droit sur les attributs ‰ table
• Il n'y a pas d'ordre haut-bas sur les tuples ‰ table
• Il n'y a pas de tuples en double ‰ table
mathématiquement
• Un schéma de relation R est ensemble ﬁni d'attributs Ai
• A chaque attribut Ai , on associe un domaine Di
dom(Ai ) = Di
• Le domaine d'un schéma de relation est
dom(R) =
ď
dom(Ai )
i
• Un tuple t est une fonction de R Ñ dom(R)
@A P R : t(A) P dom(A)
mathématiquement
• Une relation est un ensemble ﬁni de tuples
• Un schéma de base de données est un ensemble ﬁni de schémas de relation
• Une base de données est un ensemble ﬁni de relations
Si on tient compte d'un ordre, on peut simpliﬁer :
• Une relation r est un sous-ensemble du produit cartésien des domaines Di (i = 1, . . . , n)
r Ď D1 ˆ D2 ˆ . . . ˆ Dn
• Un tuple t est un élément de r
t = (a1 , a2 , . . . , an ) avec ai P Di
exemple (livres et auteurs)
Schéma Livres
Attribut
Type
Titre
Editeur
Année
Chaînes de taille 50.
Chaînes de taille 20.
Entiers compris entre 1500 et
2100.
Entiers compris entre 1 et 20.
Chaînes de taille 10.
Edition
ISBN
Techniques Informatiques
Schéma Auteurs
Attribut
Nom
Id
Type
Chaînes de taille 30.
Entiers positifs.
36
HEPL 2014–2015
5. Modèle Relationnel
Ludovic Kuty
5.3 Clés
clés
Soit R un relvar et K un ensemble d'attributs de R
• K est une clé candidate3 ssi elle est :
1. Unique. Aucune valeur de R ne contient deux tuples ‰ dont les valeurs pour K sont =
2. Irréductible. Aucun sous-ensemble de K n'est unique
• Contraint les tuples des valeurs du relvar R. Cfr. slides suivants
• Fournit un mécanisme d'adressage des tuples càd permettant de désigner un tuple donné
• Une clé primaire (primary key ou PK) est une clé candidate particulière. Il en existe toujours
une
clés
Soit R1 un relvar et FK un ensemble d'attributs de R1
• FK est une clé étrangère (foreign key) ssi :
1. Il existe un relvar R2 avec une clé candidate FK4
2. A tout moment, chaque valeur de FK d'une valeur de R1 est identique aux valeurs de
FK d'un tuple de R2 5
• Elle permet aux tuples de R1 de référencer un tuple de R2
• Par ex. toute facture fait référence à un client qui doit exister
• En d'autres termes, la FK sert de "pointeur" vers un tuple d'une autre relation
5.4 Contraintes
contraintes d'intégrité
But : garantir la cohérence de la base de donnée.
• BD = modélisation de la réalité.
• BD correcte ô modélisation exacte de la réalité
• BD cohérente ô règles d'intégrité respectées
• Donc, cohérence ‰ correction6
En effet,
• BD correcte ñ BD cohérente, BD incohérente ñ BD incorrecte7
• Mais BD cohérente œ BD correcte
contraintes d'intégrité
Une contrainte d'intégrité
3 Par
rapport aux identiﬁcateurs du modèle EA, seuls les identiﬁcateurs internes sont acceptés ici.
une clé candidate possédant les même attributs que FK.
5 Plus exactement d'un tuple d'une valeur de R .
2
6 Conformité, exactitude
7 Contraposée de l'implication.
4 Avec
Techniques Informatiques
37
HEPL 2014–2015
5. Modèle Relationnel
Ludovic Kuty
• Est une expression booléenne associée à une BD et devant toujours être vériﬁée
• Sert à exprimer de manière formelle une règle liée au problème (business rule)
• Est contrôlée à chaque exécution d'une instruction sur la BD
Types de contraintes8
• De domaine
• De relation. Par ex. clé primaire
• De base de données. Par ex. clé étrangère
intégrité de domaine
Ou intégrité de type.
• Chaque type, par déﬁnition, contraint l'ensemble des valeurs permises. Il s'agit de la contrainte
de type
• Par exemple :
– Les nombres entiers positifs ă 10000
– Les nombres décimaux à 2 décimales
– Les chaînes de caractères de taille 20
• Chaque attribut sera contraint par son type. Sa valeur sera donc limitée aux valeurs permises
par le type. Il s'agit de la contrainte d'attribut
intégrité de relation et de référence
Intégrité de relation.
• Contrainte portant sur un seul relvar
• Par ex. la contrainte de clé primaire
Intégrité de base de données.
• Contrainte portant sur deux ou plusieurs relvars
• Par ex. la contrainte de clé étrangère (ou de référence) encore appelée contrainte d'intégrité
référentielle
5.5 Algèbre relationnelle
algèbre relationnelle
• "Méthode" pour interroger une base de données et en extraire des résultats
• Algèbre ñ valeurs d'un type donné + opérateurs. Par ex. Z et +, ´, ˚
– Type = un relvar R
– Valeur = une relation de relvar R
• Composée d'opérateurs ensemblistes et relationnels
– Ensemblistes : union, intersection, différence et produit cartésien
– Relationnels : sélection, projection, jointure et division
8 Elles
sont toutes déclaratives. On spéciﬁe ce qu'on veut, pas comment on va l'obtenir.
Techniques Informatiques
38
HEPL 2014–2015
5. Modèle Relationnel
Ludovic Kuty
• Respecte la propriété de fermeture (closure)
– Càd que le résultat d'une opération est toujours une relation
– Permet de construire des expressions imbriquées comme avec ((3 ˚ 2) + 7)/2 dans R
exemple (relvars)
• Domaines.
ID : entiers positifs.
TITLE : chaînes de caractères de taille 30.
PUBLISHER : chaînes de caractères de taille 20.
YEAR : entiers de 4 chiffres.
ISBN : chaînes de caractères de format DDDDDDDDDA.
NAME : chaînes de caractères de taille 20.
• Relvars.
Books(bid : ID, title : TITLE, publisher : PUBLISHER, year : YEAR, isbn : ISBN)
Authors(aid : ID, name : NAME)
Wrote(aid : ID, bid : ID)
exemple (relations)
bid
1
2
3
4
5
Relation B
title
publisher
year
isbn
AITDS
DMS
DID
TPP
PR
Addison-Wesley
McGraw-Hill
O'Reilly
Addison-Wesley
Pragmatic Bookshelf
2004
2000
2005
2000
2005
0321197844
0072465352
0596100124
020161622X
0974514055
Relation W
aid
bid
aid
1
2
3
1
4
5
4
5
6
1
2
2
3
4
4
5
5
5
1
2
3
4
5
6
Relation A
name
Date
Ramakrishnan
Gehrke
Hunt
Thomas
Fowler
union
bid
1
2
title
Relation R1
AITDS
DMS
publisher
year
bid
Addison-Wesley
McGraw-Hill
2004
2000
2
5
Relation R2
title
DMS
PR
publisher
year
McGraw-Hill
Pragmatic Bookshelf
2000
2005
L'union R1 Y R2 de deux relations de même type R1 et R2 est une relation
• De même type
• Composée des tuples t tels que t P R1 _ t P R2
bid
1
2
5
title
AITDS
DMS
PR
R1 Y R2
publisher
year
Addison-Wesley
McGraw-Hill
Pragmatic Bookshelf
2004
2000
2005
intersection
Techniques Informatiques
39
HEPL 2014–2015
bid
5. Modèle Relationnel
title
1
2
Relation R1
AITDS
DMS
publisher
year
bid
Addison-Wesley
McGraw-Hill
2004
2000
2
5
Ludovic Kuty
title
Relation R2
DMS
PR
publisher
year
McGraw-Hill
Pragmatic Bookshelf
2000
2005
L'intersection R1 X R2 de deux relations de même type R1 et R2 est une relation
• De même type
• Composée des tuples t tels que t P R1 ^ t P R2
bid
title
R1 X R2
publisher
year
DMS
McGraw-Hill
2000
publisher
year
bid
Addison-Wesley
McGraw-Hill
2004
2000
2
5
2
différence
bid
title
1
2
Relation R1
AITDS
DMS
title
Relation R2
DMS
PR
publisher
year
McGraw-Hill
Pragmatic Bookshelf
2000
2005
La différence R1 zR2 de deux relations de même type R1 et R2 est une relation
• De même type
• Composée des tuples t tels que t P R1 ^ t R R2
R1 zR2
bid
1
title
publisher
year
AITDS
Addison-Wesley
2004
produit cartésien
bid
title
1
2
Relation R1
AITDS
DMS
publisher
year
bid
Addison-Wesley
McGraw-Hill
2004
2000
2
5
title
DMS
PR
Relation R2
publisher
year
McGraw-Hill
Pragmatic Bookshelf
2000
2005
Le produit cartésien R1 ˆ R2 de deux relations R1 et R2 sans attributs communs est une relation
• Dont l'en-tête est l'union des en-têtes de R1 et R2
• Composée des tuples t tels que t = t1 Y t2 avec t1 P R1 ^ t2 P R2
• |R1 ˆ R2 | = |R1 ||R2 |
• Attributs communs ñ renommage
bid
1
1
2
2
R1 ˆ ρ(R2 , bid Ñ bid2, title Ñ title2, publisher Ñ publisher2, year Ñ year2)
title
publisher
year
bid2
title2
publisher2
year2
AITDS
AITDS
DMS
DMS
2000
2005
2000
2005
Addison-Wesley
Addison-Wesley
McGraw-Hill
McGraw-Hill
2004
2004
2000
2000
2
5
2
5
DMS
PR
DMS
PR
McGraw-Hill
Pragmatic Bookshelf
McGraw-Hill
Pragmatic Bookshelf
sélection
bid
1
2
title
Relation R1
AITDS
DMS
publisher
year
bid
Addison-Wesley
McGraw-Hill
2004
2000
2
5
title
DMS
PR
Relation R2
publisher
year
McGraw-Hill
Pragmatic Bookshelf
2000
2005
La sélection σcondition (R) d'une relation R est une relation
Techniques Informatiques
40
HEPL 2014–2015
5. Modèle Relationnel
Ludovic Kuty
• De même type
• Composée des tuples t satisfaisant la condition booléenne portant sur les valeurs des attributs
σyearě2001 (R1 Y R2 )
bid
1
5
title
publisher
year
AITDS
PR
Addison-Wesley
Pragmatic Bookshelf
2004
2005
projection
bid
1
2
title
Relation R1
AITDS
DMS
publisher
year
bid
Addison-Wesley
McGraw-Hill
2004
2000
2
5
title
DMS
PR
Relation R2
publisher
year
McGraw-Hill
Pragmatic Bookshelf
2000
2005
La projection πattributes (R) d'une relation R est une relation
• D'en-tête équivalent dont on a gardé les attributs présents dans la liste attributes
• Dont on a supprimé les doublons apparus après avoir retiré les attributs
πbid,title (R1 )
bid
1
2
title
AITDS
DMS
jointure
Il existe trois types de jointure9 :
• La jointure conditionnelle
R1 ’condition R2 = σcondition (R1 ˆ R2 )
• L'équijointure
R1 ’equalities R2 = σequalities (R1 ˆ R2 )
• La jointure naturelle10
R1 ’ R2 = équijointure sur tous les attributs de même nom
jointure
• Le couple PK / FK établit un "pointeur" entre tuples de relations différentes
• La jointure naturelle permet de suivre ce "pointeur" entre relations et de récupérer les informations
• C'est donc une opération très utilisée
bid
1
2
2
3
4
4
5
5
5
9 Il
B’W
title
publisher
AITDS
DMS
DMS
DID
TPP
TPP
PR
PR
PR
Addison-Wesley
McGraw-Hill
McGraw-Hill
O'Reilly
Addison-Wesley
Addison-Wesley
Pragmatic Bookshelf
Pragmatic Bookshelf
Pragmatic Bookshelf
year
isbn
aid
2004
2000
2000
2005
2000
2000
2005
2005
2005
0321197844
0072465352
0072465352
0596100124
020161622X
020161622X
0974514055
0974514055
0974514055
1
2
3
1
4
5
4
5
6
existe aussi les jointures externes.
les attributs ne sont présents qu'une seule fois dans la relation résultante. On fait donc aussi une projection.
10 Notez que
Techniques Informatiques
41
HEPL 2014–2015
5. Modèle Relationnel
Ludovic Kuty
jointure
Quels sont les titres des livres écrits par l'auteur Date ?
1. Obtenir la liste de tous les livres avec leurs auteurs ñ jointure. Double jointure car l'information
se trouve dans 3 relations
2. Ne garder que les livres écrits par Date ñ sélection
3. Ne garder que le titre des livres ñ projection
πtitle (σname=Date (B ’ W ’ A))
division
R1 ˜R3 R2 est une relation
• De même type que R1
• Dont chaque tuple t est tel que le tuple t Y v P R3 , @v P R2 où t P R1 et v P R2
• En d'autres termes, on trouve tous les tuples de R1 dont les valeurs correspondantes dans
R3 comprennent toutes les valeurs de R2
• La division est un opérateur peu utilisé sans correspondance directe dans la plupart des
SGBDR
• Elle permet de disposer d'un opérateur semblable à @
division
Relation M
sid
pid
s1
s1
s1
s1
s2
s2
s3
s4
s4
p1
p2
p3
p4
p1
p2
p2
p2
p4
Relation S
sid
Relation P1
S ˜M P1
p2
p4
s1
s4
s1
s2
s3
s4
pid
sid
5.6 Autres méthodes d’interrogation
autres méthodes d'interrogation
Il existe 3 méthodes d'interrogation des données.
• L'algèbre relationnelle. Elle est procédurale (‰ déclarative) vis à vis des deux autres
• Le calcul relationnel tuple (TRC). On indique les conditions que doivent satisfaire les tuples.
Par ex. le langage QUEL
– Une variable tuple prend des tuples comme valeur
– Une requête a la forme tT | p(T )u où T est une variable tuple et p un prédicat de la
logique de premier ordre où T est libre
• Le calcul relationnel domaine (DRC). On indique les conditions que doivent satisfaire les
valeurs des attributs des tuples. Par ex. le langage QBE (Query-By-Example)
– Une variable domaine prend des valeurs dans le domaine d'un attribut
Techniques Informatiques
42
HEPL 2014–2015
5. Modèle Relationnel
Ludovic Kuty
– Une requête a la forme t⟨x1 , x2 , . . . , xn ⟩ | p(⟨x1 , x2 , . . . , xn ⟩)u où xi est une variable domaine ou une constante et p un prédicat de la logique de premier ordre où les xi sont
libres
exemples (relations)
exemples (algèbre)
exemples (TRC)
Techniques Informatiques
43
HEPL 2014–2015
5. Modèle Relationnel
Ludovic Kuty
exemples (DRC)
Techniques Informatiques
44
HEPL 2014–2015
6. Modèle Entité-Association -> Modèle Relationnel
Ludovic Kuty
Chapitre 6
Modèle Entité-Association -> Modèle
Relationnel
6.1 Présentation
généralités
• Comment passer du modèle EA au modèle relationnel ?
• But : concevoir le modèle logique.
• Objets du modèle EA Ñ objets du modèle relationnel càd uniquement des relations.
– Entités.
– Associations (binaires, n-aires).
– Identiﬁants (internes, externes).
– Attributs (multivalués, composés).
– Rôles et cardinalités (0, 1 et n).
– Généralisations.
• On parle de correspondance (mapping).
processus de transformation
Le processus peut être décomposé en 2 phases successives :
1. Etape indépendante du modèle logique.
2. Etape dépendante du modèle logique càd relationnel ici.
Techniques Informatiques
45
HEPL 2014–2015
6. Modèle Entité-Association -> Modèle Relationnel
conceptual
schema
Ludovic Kuty
database
load
information
HIGH-LEVEL LOGICAL
DESIGN
conceptual-to-logical
schema + design choices
description
of the
target logical
model
MODEL-DEPENDENT
LOGICAL DESIGN
designer
preferences
performance
criteria
logical schema in
the target model
6.2 Etape indépendante du modèle logique
étape indépendante du modèle logique
• Modélisation de la charge sur la BD : volume des données et accès.
• Modélisation des données dérivées : stockage ou calcul ?
• Simpliﬁcation des généralisations en entités et associations.
• Constitution de groupes d'entités / associations et fusion de ces groupes : efﬁcacité et sécurité.
• Choix de clé primaire.
élimination des généralisations
• Les modèles logiques ne savent pas représenter les généralisations.
• On doit donc les modéliser avec des entités et des associations uniquement.
• Deux choses implicites doivent être modélisées explicitement :
– L'héritage des attributs de la super-entité dans la sous-entité.
– La relation est un entre la sous-entité et la super-entité.
trois possibilités
Pour éliminer les généralisations, on peut :
• Modéliser la hiérarchie avec une seule entité en prenant l'union des attributs des sous-entités
et en les ajoutant aux attributs de la super-entité. Un attribut discriminant est ajouté.
• Ne garder que les sous-entités en propageant les attributs hérités dans les sous-entités.
• Garder toutes les entités et utiliser des associations pour modéliser les relations entre les
super- et les sous-entités.
Techniques Informatiques
46
HEPL 2014–2015
6. Modèle Entité-Association -> Modèle Relationnel
Avant
Ludovic Kuty
Après
Le but est de minimiser le nombre d'accès requis pour une opération donnée. Ce critère nous
permettra de choisir la bonne méthode.
une seule entité (exemple)
• Les attributs des sous-entités deviennent optionnels (Thèse et Durée_Stage).
• Un attribut discriminant est ajouté (Niveau) :
– Cardinalité (1, 1) si généralisation totale et exclusive.
– Cardinalité minimale est 0 si généralisation partielle.
– Cardinalité maximale est n si généralisation imbriquée.
une seule entité (+/-)
Désavantages :
• Peut générer un nombre important de valeurs NULL pour les attributs qui ne s'appliquent
qu'aux sous-entités.
• Les opérations qui n'accèdent qu'aux sous-entités doivent examiner la totalité des instances
de la super-entité.
Avantages :
• Solution la plus simple : aucune association n'est nécessaire.
• S'applique à tous les types de généralisation.
les sous-entités (exemple)
• La super-entité disparaît, ses attributs sont intégrés dans toutes les sous-entités.
• Remarquez la mincard(Manager, Supervise_Par) passe de 1 à 0. De même pour mincard(Projet_Caritatif, Contrib
les sous-entités (+/-)
Désavantages :
• Pas utilisable pour les généralisations partielles ou imbriquées.
• On ne voit plus que les entités Secrétaire, Ingénieur et Manager sont toutes des employés.
• Duplication des attributs de la super-entité dans chacune des sous-entités. Cela pose problème quand il y a beaucoup d'attributs.
Techniques Informatiques
47
HEPL 2014–2015
6. Modèle Entité-Association -> Modèle Relationnel
Avant
Après
Avant
Après
Ludovic Kuty
• Une opération qui n'accédait qu'à la super-entité doit maintenant accéder à chacune des
sous-entités1 .
• Certaines associations doivent être répétées.
Intéressant lorsque :
• Le concept commun représenté par la super-entité n'est pas nécessaire dans le schéma logique.
• Le nombre d'attributs de la super-entité est faible.
• Les opérations sont localisées dans les sous-entités.
toutes les entités (exemple)
• Toujours faisable.
• Chaque lien entre la super-entité et une sous-entité est représenté par une association.
• La participation de la super-entité est optionnelle et celle de la sous-entité obligatoire.
toutes les entités (+/-)
Désavantages :
• Le schéma résultant est complexe. Insérer une instance d'une sous-entité impose d'insérer
une instance pour la super-entité et une pour l'association.
• La représentation explicite des liens est redondante au niveau conceptuel.
Avantages :
• Cette technique fonctionne dans tous les cas de ﬁgure.
• Pratique si la plupart des opérations sont locales à la super-entité ou une des sous-entités.
1 Nouvelles
entités anciennement des sous-entités.
Techniques Informatiques
48
HEPL 2014–2015
6. Modèle Entité-Association -> Modèle Relationnel
Avant
Ludovic Kuty
Après
6.3 Etape dépendante du modèle logique
étape dépendante du modèle logique
1. Elimination des identiﬁants externes.
2. Elimination des attributs composés et multivalués.
3. Transformation des entités.
4. Transformation des associations one-to-one, one-to-many et many-to-many.
6.3.1 Elimination des identiﬁants externes
élimination des identiﬁants externes
Inutilisables dans modèle relationnel ñ identiﬁants internes.
• Soit entité E1 dont la clé primaire est externe ou mixte.
• Soit entité E2 fournit identiﬁcation à E1 grâce à l'association R.
• Clé primaire de E2 est interne.
• Alors, on importe dans E1 la clé primaire de E2 . Ensuite on élimine R.
6.3.2 Elimination des attributs composés et multivalués
élimination des attributs composés et multivalués
Inutilisables dans modèle relationnel ñ attributs simples univalués.
• Pour attribut composé, 2 possibilités :
– Chaque composant devient un attribut. adresse Ñ rue, ville et code postal.
– Chaque composant est intégré dans le même attribut. adresse Ñ adresse (concaténation).
• Pour attribut multivalué (par ex. hobbies, sports, …), on crée une entité contenant :
– L'attribut lui-même.
– L'identiﬁant de l'entité de départ.
– La clé primaire est l'ensemble des attributs.
élimination des attributs composés et multivalués
Si attribut multivalué dans une association R entre E1 et E2 :
Techniques Informatiques
49
HEPL 2014–2015
6. Modèle Entité-Association -> Modèle Relationnel
Avant
Après
Avant
Après
Ludovic Kuty
1. On crée une nouvelle entité E .
2. On ajoute l'attribut multivalué.
3. On ajoute des attributs de E1 et/ou E2 .
• one-to-one ñ E inclut la clé primaire de E1 ou E2 .
• one-to-many ñ E inclut la clé primaire de l'entité du côté many.
• many-to-many ñ E inclut les clés primaires de E1 et E2 .
4. La clé primaire est l'ensemble des attributs.
élimination des attributs composés et multivalués
6.3.3 Entités
entités
• Une entité correspond à une relation.
• Les attributs et la clé primaire sont les mêmes.
• Celle-ci peut être transformée avant cette étape. Cfr. slide 106.
6.3.4 Associations
associations one-to-one
Soit l'association R one-to-one entre E1 et E2
• Participation totale de E1 et E2 ñ réduction à une relation.
– Les clés primaires sont identiques ñ on combine les attributs et on prend une seule fois
la clé primaire.
– Les clés primaires sont différentes ñ on combine les attributs et on choisit une des clés
primaires.
Techniques Informatiques
50
HEPL 2014–2015
6. Modèle Entité-Association -> Modèle Relationnel
Avant
Après
Avant
Après
Ludovic Kuty
associations one-to-one
• Participation partielle de E1 et/ou E2 ñ création, réduction ou incorporation.
– Une entité avec participation partielle soit E1 ñ on place la PK de E1 dans E2 .
– Les deux entités avec participation partielle ñ on crée une relation.
associations one-to-many
Soit l'association R one-to-many entre E1 et E2
• Entité E2 (many) avec participation totale.
– La clé primaire de E1 est incorporée dans E2 .
– Les attributs de R sont incorporés dans E2 .
associations one-to-many
• Entité E2 (many) avec participation partielle ñ création ou incorporation selon le traitement
désiré des valeurs nulles.
• Ex. de création ci-dessous.
associations many-to-many
Soit l'association R many-to-many entre E1 et E2
• On crée une nouvelle relation.
• La clé primaire est la clé de E1 + la clé de E2 .
• On incorpore les attributs de R.
• Idem pour les association N-aire avec N ą 2. La clé primaire doit être réduite si c'est une
super-clé.
associations récursives
Soit l'association récursive R sur E .
Avant
Techniques Informatiques
Après
51
HEPL 2014–2015
6. Modèle Entité-Association -> Modèle Relationnel
Avant
Après
Avant
Après
Ludovic Kuty
• Association one-to-many ñ incorporation des attributs.
• Attention aux valeurs nulles. On peut devoir créer une nouvelle relation.
associations récursives
• Association many-to-many ñ création d'une relation.
• La PK de E est présente 2x dans la relation.
Avant
Techniques Informatiques
Après
52
HEPL 2014–2015
6. Modèle Entité-Association -> Modèle Relationnel
Avant
Après
Avant
Après
Techniques Informatiques
Ludovic Kuty
53
HEPL 2014–2015
6. Modèle Entité-Association -> Modèle Relationnel
Techniques Informatiques
Ludovic Kuty
54
HEPL 2014–2015
7. Installation Oracle
Ludovic Kuty
Chapitre 7
Installation Oracle
composante d'un système Oracle
• Le serveur Oracle comprenant le SGBD et la BD
• Le client Oracle comprenant des librairies pour pouvoir accéder à un serveur Oracle
• Toute une série d'applications clientes diverses comme SQLPlus, Toad, SQL Developer, TOra, …
• Si le serveur est local càd sur la même machine que les applications clientes, alors une installation séparée du client Oracle n'est pas nécessaire. La partie client est incluse dans la partie
serveur
Typiquement :
SQLPlus
Toad
SQL Dev
SGBD
Oracle
+ BD
client
Oracle
utilisation locale
d’une librairie
communication
locale ou distante
7.1 Clients et serveurs
serveurs Oracle
• Oracle Database 10g Express Edition
– Léger (small-footprint)
– Utile pour développement, tests divers, …
– Version Windows x86 et Linux x86
– Documentation pour l'installation
– Archive : entre 165 et 220 MB. SGBD + BD : 1.2GB
• Oracle Database 10g.
– Complète.
– Plusieurs OS supportés dont Windows x861 et Linux x86.
1 Pour
Windows Vista, cfr. Statement of Direction.
Techniques Informatiques
55
HEPL 2014–2015
7. Installation Oracle
Ludovic Kuty
– Documentation pour tous les types d'installation.
– Archive : 650 MB. SGBD + BD : 3.8GB.
• Compatibilité OS, serveurs, clients : matrices de certiﬁcation.
clients Oracle
Trois types de clients Oracle :
• Oracle Database 10g Express Client. Cfr. Express Edition
• Oracle Database 10g Client
– Gourmand en place disque : 500MB
– Plusieurs types d'installation possibles dont Instant Client
– Nécessaire pour faire du développement Pro*C par exemple
• Instant Client
– Utilise très peu de place disque
– Divisés en packages : client de base, JDBC, SQLPlus, …
7.2 Installation
installation SGBD Windows
• Documentation ofﬁcielle pour installation Windows x86
• Attention à l'installation sur une machine conﬁgurée par DHCP. Cfr. sections 2.4.1 et 2.4.5
de la documentation2
installation SGBD Linux
• Gentoo. HOWTO Install Oracle 10g
• Ubuntu. Oracle su Ubuntu3
• Debian. Oracle : Database for Debian
• Documentation ofﬁcielle pour installation Linux x86 et The Oracle-on-Linux Installation Menu
Dans chaque procédure, on retrouve dans l'ordre :
1. Installer les packages adéquats.
2. Dimensionner certains paramètres du kernel
3. Créer le groupe dba, un utilisateur oracle et dimensionner ses limites
4. Installer le SGBD
post-installation
Côté serveur.
1. Démarrer le SGBD, démarrer le listener et démarrer l'interface Web de maintenance (Enterprise Manager) Go To
2 Cela
3 En
ne concerne pas l'Express Edition.
italien mais c'est compréhensible.
Techniques Informatiques
56
HEPL 2014–2015
7. Installation Oracle
Ludovic Kuty
2. Accès à la maintenance de la BD. Go To
3. Création d'un utilisateur. Go To
4. Connaître la version, l'endroit où sont les ﬁchiers de données, …Go To
5. Arrêter le SGBD. Go To
Côté client
1. Conﬁgurer les ﬁchiers sqlnet.ora et tnsnames.ora Go To
7.3 Démarrage
démarrage (Linux)
• Démarrage de la BD
oracle@linux /mnt/oracle $ sqlplus sys as sysdba
SQL*Plus : Release 10.2.0.1.0 - Production on Mar. Mars 20 11 :38 :55 2007
Enter password :
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 444596224 bytes
Fixed Size
1219904 bytes
Variable Size
134218432 bytes
Database Buffers
306184192 bytes
Redo Buffers
2973696 bytes
Database mounted.
Database opened.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
• Démarrage du listener
oracle@linux /mnt/oracle $ lsnrctl start
LSNRCTL for Linux : Version 10.2.0.1.0 - Production on 20-MARS -2007 11 :57 :06
...
Listening on : (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=linux)(PORT=1521)))
...
Listener Parameter File
/mnt/oracle/product/10.2.0.1/network/admin/listener.ora
Listener Log File
/mnt/oracle/product/10.2.0.1/network/log/listener.log
...
The command completed successfully
démarrage (Linux)
• Démarrage de l'Enterprise Manager
oracle@linux /mnt/oracle $ emctl start dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http ://linux :1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 10g Database Control ...................... started.
-----------------------------------------------------------------Logs are generated in directory /mnt/oracle/product/10.2.0.1/linux_db1/sysman/log
démarrage (Windows)
• Démarrage du SGBD ñ démarrage de services Windows. Commande services.msc. Par
ex. :
– Express Edition : OracleServiceXE et OracleXETNSListener
• Il est intéressant de changer le démarrage automatique en manuel pour éviter de tout démarrer à chaque boot de la machine
• On peut scripter le démarrage des services
net start OracleServiceXE
net start OracleXETNSListener
Techniques Informatiques
57
HEPL 2014–2015
7. Installation Oracle
Ludovic Kuty
7.4 Arrêt
arrêt (Linux)
• Arrêt de l'Enterprise Manager
oracle@linux ~ $ emctl stop dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.1.0
Copyright (c) 1996, 2005 Oracle Corporation. All rights reserved.
http ://linux :1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 10g Database Control ...
... Stopped.
• Arrêt du listener
oracle@linux ~ $ lsnrctl stop
LSNRCTL for Linux : Version 10.2.0.1.0 - Production on 21-MARS -2007 11 :47 :23
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
arrêt (Linux)
• Arrêt de la BD.
oracle@linux ~ $ sqlplus sys as sysdba
...
SQL> shutdown transactional
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
• Par ordre de sévérité (du moins sévère ou plus sévère) :
1. SHUTDOWN NORMAL ou SHUTDOWN. On attend que tous les utlisateurs soient déconnectés
2. SHUTDOWN TRANSACTIONAL. On attend que toutes les transactions actives soient terminées
3. SHUTDOWN IMMEDIATE. Déconnecte les utilisateurs et fait un rollback des transactions
en cours
4. SHUTDOWN ABORT. Déconnexion immédiate des utilisateurs. Pas de rollback. La BD
peut se retrouver dans un état incohérent
arrêt (Windows)
• Arrêt SGBD ñ arrêt de services Windows. Commande services.msc. Par ex. :
– Express Edition : OracleServiceXE et OracleXETNSListener
• On peut scripter l'arrêt des services. Remarquez l'inversion de l'ordre par rapport au script
de démarrage
net stop OracleXETNSListener
net stop OracleServiceXE
7.5 Maintenance
accès à la maintenance de la BD
• Avec l'interface Web de l'Enterprise Manager à l'url http://linux:1158/em pour le serveur nommé linux
Techniques Informatiques
58
HEPL 2014–2015
7. Installation Oracle
Ludovic Kuty
• Avec un programme applicatif quelconque comme SQLPlus, Toad, SQL Developer. Il est
nécessaire d'avoir conﬁguré les ﬁchiers tnsnames.ora et sqlnet.ora Go To
création utilisateur sous Oracle
1. Création de l'utilisateur et du schéma associé
CREATE USER nom_utilisateur IDENTIFIED BY mot_de_passe
DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK
2. Modiﬁcation du quota disque de l'utilisateur. Etape facultative
ALTER USER nom_utilisateur QUOTA 15M ON USERS;
3. Attribution d'un ou plusieurs rôles à l'utilisateur ou de privilèges systèmes. Au minimum,
il faut les privilèges systèmes CREATE SESSION et CONNECT
GRANT nom_role TO nom_utilisateur;
GRANT nom_privilege TO nom_utilisateur;
création utilisateur sous Oracle (exemples)
• On crée l'utilisateur test4 avec le mdp testmdp. Initialement le compte n'est pas locked
create user test identified by testmdp account unlock;
• On donne à l'utilisateur la possibilité de se connecter
grant create session to test;
• On crée un autre utilisateur avec un nom composé de caractères spéciaux
create user "éâûô" identified by testmdp account unlock;
• Vériﬁons la liste des utilisateurs créés aujourd'hui
select username from all_users where trunc (created) = trunc
(sysdate);
TEST
éâûô
infos BD
• Connaître la version de la BD
select * from v$version
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux : Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
• Savoir où se trouvent les ﬁchiers
select name from v$datafile;
/mnt/oracle/oradata/db1/system01.dbf
/mnt/oracle/oradata/db1/undotbs01.dbf
/mnt/oracle/oradata/db1/sysaux01.dbf
/mnt/oracle/oradata/db1/users01.dbf
/mnt/oracle/oradata/db1/example01.dbf
4 Notez
que l'utilisateur et le mdp seront conservés en majuscules dans la BD. Il s'agit d'identiﬁants réguliers.
Techniques Informatiques
59
HEPL 2014–2015
7. Installation Oracle
Ludovic Kuty
• Trouver le nom de la BD et le nom du domaine.
select name , value from v$parameter where name = 'db_name' or name = 'db_domain' ;
db_domain khi.be
db_name db1
infos BD
• Connaître le nom global de la BD
select * from global_name;
select * from props$ where name='GLOBAL_DB_NAME' ;
DB1.KHI.BE
• Connaître le nom du service (celui devant être dans tnsnames.ora)
SELECT name,value FROM v$parameter WHERE name = 'service_names' ;
db1.khi.be
select count(*) from v$parameter;
258
les ﬁchiers de conﬁguration du client
• Les ﬁchiers sqlnet.ora et tnsnames.ora indiquent comment accéder aux BDs et où elles
se trouvent
• Ils sont situés dans le sous-répertoire network/admin de l'installation d'Oracle5 .
Serveur : /mnt/oracle/product/10.2.0.1/network/admin
• Pour l'Instant Client, il s'agit de n'importe quel répertoire. Celui-ci est indiqué à l'aide de la
variable d'environnement TNS\_ADMIN
• sqlnet.ora déﬁnit les méthodes utilisées pour savoir où se trouve les BDs. Par ex. LDAP
ou le ﬁchier tnsnames.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
• tnsnames.ora déﬁnit des alias pour se connecter aux BDs
le ﬁchier tnsnames.ora
• Il comprend les informations nécessaires pour accéder aux BDs distantes
• Chaque nouvelle entrée doit commencer en colonne 1
• SERVICE_NAME est le nom de la BD6
DB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = linux)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db1.khi.be)
)
)
accès distant à BD "à la maison"
Pour accéder à sa BD chez soi, il faut :
5 Cela
6 En
peut être dans l'arborescence de l'installation du serveur sur la machine locale.
réalité c'est plus général que cela.
Techniques Informatiques
60
HEPL 2014–2015
7. Installation Oracle
Ludovic Kuty
1. Indiquer au ﬁrewall de laisser passer le traﬁc à destination du port 1521. Ce traﬁc est redirigé
vers le port 1521 du serveur
2. Indiquer au serveur Oracle de toujours utiliser le même port pour servir les requêtes càd le
1521
• Document pdf ﬁrewalls and redirections
• Document pdf use shared socket on Windows
• Document pdf check use shared socket
• Cela revient à ﬁxer la variable d'environnement USE_SHARED_SOCKET à TRUE
Techniques Informatiques
61
HEPL 2014–2015
Techniques Informatiques
7. Installation Oracle
Ludovic Kuty
62
HEPL 2014–2015
8. Langage SQL
Ludovic Kuty
Chapitre 8
Langage SQL
8.1 Historique
historique
• 1970. A Relational Model of Data for Large Shared Data Banks, E. F. Codd, CACM.
• 70s. IBM développe en interne le SGBD System R basé sur l'article de Codd et le langage
Structured English Query Language (SEQUEL) pour l'utiliser. TM ñ changement du nom en
SQL1 .
• ﬁn 70s, début 80s. Des produits apparaissent sur le marché. SQL/DS puis DB2 (IBM), Oracle
V2 (Relational Software Ñ Oracle).
• 1986. SQL-86. Adopté par l'ANSI (American National Standards Institute).
• 1987. SQL-87. Accepté par l'ISO (International Organization for Standardization).
• 1989. SQL-89 ou SQL1. 100 pages.
• 1992. SQL-92 ou SQL2. Révision majeure. 600 pages.
historique
• 1999. SQL :19992 ou SQL3. Ajout des regex, triggers, types non-scalaires, PSMs, CLI, OLB,
…
• 2003. SQL :2003. 2000 pages. Par ex. ajout de XML.
• 2006. SQL :2006. Fonctionnalités liées à XML.
8.2 Conformité et implémentations
conformité
SQL-92.
• Niveau d'entrée (Entry). SQL-89 + extensions.
• Niveau intermédiaire (Intermediate).
1 Ce
2 La
n'est pas un sigle.
nouvelle notation règle des problèmes potentiels liés à Y2K. Les deux-points sont la norme à l'ISO.
Techniques Informatiques
63
HEPL 2014–2015
8. Langage SQL
Ludovic Kuty
• Niveau complet (Full).
SQL :1999.
• Core = Entry SQL-92 + une bonne partie de Intermediate SQL-92 + quelques fonctionnalités
de Full SQL-92 + quelques nouvelles fonctionnalités.
• De nombreuses fonctionnalités ne font pas partie de Core SQL :1999 mais sont implémentables séparément. Certaines sont regroupées en package. On parle alors de Enhanced SQL
:19993 ou Non-Core.
• Packages = groupes de fonctionnalités liées qui peuvent être implémentés séparément.
– Au nombre de neuf dans le standard.
– D'autres packages peuvent être déﬁnis.
implémentations
• Aucun produit ne supporte complètement le standard SQL. Cfr. Is SQL a real standard anymore ?
• On parle plutôt de superset of a subset.
• Consulter la doc de chaque implémentation : Oracle, MySQL, PostgreSQL.
• Oracle 10g, Oracle 10g XE.
• MySQL 5.0 (moteurs MyISAM, InnoDB).
• PostgreSQL.
• Firebird (Fyracle).
• SQLite.
• Ingres (supporte QUEL).
• SQL Server (MS), DB2 (IBM), Informix (IBM), Sybase, ….
utilisations
• Intégration verticale. Utilisé par :
– Utilisateur ﬁnal.
– Le concepteur d'application : schémas logique et externe.
– DBA : schéma physique (optimisation stockage et accès).
– Développeur : programmes applicatifs.
• Intégration horizontale. Un seul langage composé de4 :
– LDD (Data Deﬁnition Language, DDL).
– LMD (Data Manipulation Language, DML).
– LCD (Data Control Language, DCL).
– Transactions, procédures, …
3 Il
semblerait que le terme Enhanced ne soit pas un terme ofﬁciel du standard.
établies dans SQL-92.
4 Catégories
Techniques Informatiques
64
HEPL 2014–2015
8. Langage SQL
Ludovic Kuty
8.3 Concepts du langage SQL
sous-langage de données vs langage hôte
Sous-langage de données (data sublanguage).
• Langage spécialisé dans l'accès aux données (SQL, QUEL, QBE, RDML, …).
• Utilisé conjointement avec un autre langage appelé le langage hôte.
• Permet d'exprimer de manière concise et précise ce que l'on veut.
Langage hôte (host language).
• Langage de programmation familier (Java, C, C++, Cobol, Fortran, …).
• Permet d'exprimer un grand nombre de tâches diverses.
• N'est pas spécialisé pour l'accès aux données.
catalogue (SQL)
Les différents concepts introduisent un système de nommage hiérarchique.
• Environnement SQL = ensemble de catalogues.
• Catalogue = ensemble nommé de schémas.
• Schéma = ensemble nommé d'objets de la BD (par ex. table, procédures).
• Information schema = méta-données sur les schémas du catalogue. Inclus dans le catalogue
et nommé INFORMATION_SCHEMA. On parle aussi de dictionnaire de données.
• Attention, il n'y pas de notion de base de données.
information
schema
object
catalog
table
c
o
l
c
o
l
object
schema
schema
schema
catalogue (Oracle)
• La notion de catalogue n'existe pas avec Oracle.
• La collection de schémas est contenue5 dans une base de données. On peut donc écrire catalogue = BD6 .
• Un schéma contient un ensemble d'objets appartenant à un utilisateur.
• Il y a une correspondance un-à-un7 entre les schémas et les utilisateurs. Les deux portent le
même nom mais ils sont différents.
connexions et sessions
5 Le
terme "contenir" n'a rien à voir avec un quelconque stockage physique.
réalité, on pourrait avoir plusieurs catalogues dans la même base de données.
7 Il existe une bijection entre l'ensemble des utilisateurs et l'ensemble des schémas.
6 En
Techniques Informatiques
65
HEPL 2014–2015
8. Langage SQL
Ludovic Kuty
• Une connexion est une association entre un client SQL et un serveur SQL.
• Une session est le contexte dans lequel un utilisateur (personne ou application) exécute une
séquences d'instructions SQL dans une connexion.
Attention, il s'agit de concepts logiques (pas nécessairement physiques) càd qu'un environnement
réseau n'est pas obligatoire.
8.4 Notation BNF
notation BNF
Backus-Naur Form.
• Syntaxe (méta-syntaxe) pour décrire la syntaxe des langages de programmation.
• Spéciﬁe la grammaire d'un langage context-free.
• Ensemble de règles de dérivation (de production) de la forme
symbole : := expression
• symbole est un non-terminal ou classe syntaxique (parfois indiqué entre <>).
• expression est une expression composée de :
– Symboles non-terminaux.
– Symboles terminaux (mot-clés, ponctuation, espaces, …)
– Le méta-symbole | pour indiquer le choix.
– Les méta-symboles [ et ] pour indiquer l'optionnalité.
– Les méta-symboles { et } pour indiquer la répétition de 0 ou plusieurs fois.
• BNF peut être étendue en ABNF ou EBNF.
notation BNF (exemple)
postal-address : := name-part street-address zip-part
personal-part
: := first-name | initial "."
name-part
: := personal-part SP last-name [SP jr-part] EOL
| personal-part SP name-part
street-address : := street-name SP house-num [SP apt] EOL
zip-part
: := postal-code SP town-name EOL
Exemple de texte qui peut être généré à l'aide de la grammaire précédente :
John R. R. Tolkien
Rue Peetermans 80
4100 Seraing
grammaire BNF de SQL et complexité
La grammaire utilisée dans le cours est celle de SQL :1999.
• Les syntaxes du LDD et du LMD (plus spéciﬁquement du SELECT) sont particulièrement
riches.
• ñ utilisation d'une syntaxe simpliﬁée et approximative dans les slides.
– Certaines règles de production ne sont pas montrées (points de suspension).
– Certaines classes syntaxiques ont été supprimées.
• La syntaxe complète (doc et txt) de SQL :1999 est disponible en BNF sur le site du cours.
Techniques Informatiques
66
HEPL 2014–2015
8. Langage SQL
Ludovic Kuty
8.5 Interfaçage avec SQL
utilisation de SQL
Quatre manières d'utiliser ou d'interfacer SQL dans des applications :
1. Invocation directe d'instructions SQL (direct invocation). Cfr. évaluation dans Toad ou SQL
Developer.
2. Incorporation des instructions SQL dans le langage hôte (embedded SQL). Par exemple, Pro/C,
Pro/Cobol ou SQLJ.
3. Ecriture des instructions dans un module séparé et invocation à partir du langage hôte (module language). Cfr. par exemple PL/SQL de Oracle.
4. Invocation à travers une interface de haut-niveau (call-level interface).
• Ces quatre manière sont reﬂétées au niveau des règles de production de la grammaire SQL.
• Les méthodes 2 et 3 impliquent d'établir une correspondance entre types SQL et types hôtes.
Techniques Informatiques
67
HEPL 2014–2015
Techniques Informatiques
8. Langage SQL
Ludovic Kuty
68
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
Chapitre 9
Langage de Déﬁnition de Données
(LDD)
9.1 Présentation
présentation
Langage de déﬁnition de données ou LDD (Data Deﬁnition Language ou DDL).
Utilisé pour déﬁnir :
• La structure de chaque table1 .
• Le domaine de chaque attribut.
• Les contraintes d'intégrité.
• Les index, la structure physique de chaque table, …
la table DUAL (Oracle)
La table DUAL est très utile pour faire des tests.
• Automatiquement crée par Oracle en même temps que le dictionnaire de données.
• Présente dans le schéma de SYS mais accessible à tous les utilisateurs.
• Possède une seule colonne nommée DUMMY de type VARCHAR2(1).
• Contient un seul enregistrement de valeur X ñ un seul résultat renvoyé.
• Utile pour calculer des expressions avec SQL.
column dummy format A5
select * from dual;
DUMMY
----X
1 row selected.
les identiﬁants
Il existe 2 types d'identiﬁant SQL pour nommer les objets de la BD :
1 Le
schéma de chaque relation.
Techniques Informatiques
69
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
• Les identiﬁants réguliers.2
– Non sensibles à la casse. Par ex. TITRE, titre, Titre et TiTrE sont identiques.
– Le SGBD change toutes les minuscules en majuscules.
– Sont constitués par des caractères alphanumériques (dépend du jeu de caractères) et
l'underscore.
• Les identiﬁants délimités.
– L'identiﬁant est entouré de guillemets.
– Sensibles à la casse. Par ex. "TITRE" et "Titre" sont différents.
– Les guillemets ne sont pas stockés dans le dictionnaire de données.
– Peuvent contenir n'importe quel caractère à n'importe quelle position.
9.2 Types de donnée
types en bref (scalaires)
Principales catégories de types et types principaux.
Catégorie
Types de données
Binaire
Booléen
Caractère
Numérique entier
Numérique
fractionnaire
Numérique approximatif
Date/Heure
BIT, BIT VARYING, BINARY LARGE OBJECT
BOOLEAN
CHARACTER, CHARACTER VARYING, CHARACTER LARGE OBJECT
INTEGER, SMALLINT
Intervalle
DECIMAL, NUMERIC
REAL, FLOAT, DOUBLE PRECISION
DATE, TIME, TIMESTAMP, TIME WITH TIME ZONE, TIMESTAMP
WITH TIMEZONE
Year-month, Day-time
• Selon le SGBDR, les types supportés diffèrent.
types en bref (autres)
• Les collections ou tableaux déﬁnis à l'aide du mot-clé3 ARRAY.
• Le type représentant un tuple dont le constructeur est ROW, appelé anonymous row type.
• Les types déﬁnis par l'utilisateur : structured type et distinct type.
• Le type permettant de référencer ou pointer un tuple d'une table appelé reference type.
• Les locators qui permettent côté client d'éviter de manipuler directement de larges quantités
de données (par ex. BLOB ou CLOB). Ceux-ci ne sont pas stockés dans les tables.
type de données
2 Pour Oracle, certains objets ont obligatoirement un identiﬁant régulier. Certaines règles de nommage doivent être
respectées.
3 On le qualiﬁe de constructeur de type ou type constructor.
Techniques Informatiques
70
HEPL 2014–2015
<data type> : :=
defined type>
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
<predefined type> | <row type> | <user-
| <reference type> | <collection type>
<predefined type> : :=
<character string type>73
| <national character string type>
| <binary large object string type>74
| <bit string type>74
| <numeric type>71
| <boolean type>72
| <datetime type>75
| <interval type>78
9.2.1 Types numériques
types numériques
<numeric type> : := <exact numeric type> | <approximate
numeric type>
<exact numeric type> : := NUMERIC [ ( <precision> [ , <scale>
] ) ]
| DECIMAL [ ( <precision> [ , <scale>
] ) ]
| DEC [ ( <precision> [ , <scale> ] )
]
| INTEGER | INT | SMALLINT
<precision> : := <unsigned integer>
<scale> : := <unsigned integer>
<approximate numeric type> : := FLOAT [ ( <precision> ) ]
| REAL | DOUBLE PRECISION
• Les types numériques exacts comprennent les numériques entiers et les fractionnaires.
types numériques entiers
Représentent les nombres entiers. Ensemble Z.
• INTEGER (abbrev. INT). La précision4 dépend de l'implémentation.
• SMALLINT. La précision dépend de l'implémentation mais n'est pas supérieure à celle de
INTEGER.
types numériques fractionnaires
Représentent les nombres fractionnaires. Ensemble Q.
• NUMERIC(p,s) où
– p est la précision càd le nombre de chiffres signiﬁcatifs.
– s est l'échelle càd le nombre de chiffres après la virgule avec 0 ď s ď p.
– NUMERIC(p) ” NUMERIC(p,0) et NUMERIC utilise une précision par défaut dépendant
de l'implémentation.
– L'implémentation fournit exactement les p et s demandés.
• DECIMAL est comme NUMERIC mais la précision fournie par l'implémentation peut être supérieure à la précision demandée.
4 Le
nombre de chiffres qui peuvent être stockés.
Techniques Informatiques
71
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
types numériques approximatifs
Représentent les nombres pour lesquels la précision ne doit pas être absolue. Ensemble R.
• REAL représente un ﬂottant en simple précision. Dépend de l'implémentation mais habituellement a un correspondant machine.
• DOUBLE PRECISION représente un ﬂottant dont la précision doit être supérieure à celle de
REAL. Dépend de l'implémentation mais habituellement a un correspondant machine.
• FLOAT(p) permet d'indiquer la précision minimale voulue pour le ﬂottant.
types numériques (Oracle)
• Un seul type numérique Oracle : NUMBER(p,s).
• p est la précision càd le nombre de chiffres signiﬁcatifs. Au maximum 38 chiffres décimaux.
• s est l'échelle càd le nombre de chiffres à partir de la virgule jusqu'au chiffre le moins signiﬁcatif avec ´84 ď s ď 127.
– Si s ě 0, il s'agit du nombre de chiffres signiﬁcatifs après la virgule.
– Si s ă 0, il s'agit d'un arrondi à |s| chiffres à gauche de la virgule.
• NUMBER(p) représente un entier (s = 0) et NUMBER prend pour p et s les valeurs maximales
permises.
Type SQL
Type Oracle
INTEGER, INT et SMALLINT
NUMERIC(p,s) et DECIMAL(p,s)
REAL, DOUBLE PRECISION et FLOAT
NUMBER(38)
NUMBER(p,s)
NUMBER
types numériques (tests)
1. Arrondi avec une échelle négative de 2 (2 chiffres à gauche de la virgule).
select cast (125.67 as number (5, -2)) as RES from dual;
100
2. Arrondi avec une échelle positive de 2 (2 chiffres à droite de la virgule).
select cast (125.65532 as number (5, 2)) as RES from dual;
125.66
9.2.2 Type booléen
type booléen
<boolean type> : := BOOLEAN
• Les littéraux booléens sont TRUE, FALSE et NULL.
• Comme nous le verrons au slide 95, la logique est trivalente.
• Ce type n'existe pas dans Oracle.
Techniques Informatiques
72
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
9.2.3 Types caractères
types caractères
<character string type> : := CHARACTER [ ( <length> ) ]
| CHARACTER VARYING ( <length> )
| CHARACTER LARGE OBJECT [ ( <large object length> ) ]
• CHARACTER (abbrev. CHAR).
– CHARACTER(n) Représente les chaînes de caractères de taille ﬁxe n.
– Les chaînes plus courtes que le type sont complétées par des espaces.
– CHARACTER seul ” CHARACTER(1).
• CHARACTER VARYING (abbrev. VARCHAR ou CHAR VARYING).
– CHARACTER VARYING(n) représente les chaînes de caractères de taille variable de taille
maximale n.
– Permet d'éviter les espaces inutiles en ﬁn de chaîne.
– Pas de longueur par défaut.
• CHARACTER LARGE OBJECT (abbrev. CLOB ou CHAR LARGE OBJECT).
• Les litéraux sont indiqués entre apostrophes, ont une taille max. de 4000 bytes et sont considérés de type CHAR.
'La maison d''édition est "O''Reilly"'
types caractères (Oracle)
• Type CHAR :
– Taille maximale de 2000 bytes.
– CHAR(n) ou CHAR(n BYTE) crée une chaîne de n bytes.
– CHAR(n CHAR) crée une chaîne de n caractères. Différent si un caractère peut être codé
sur plus d'un byte.
• Type VARCHAR :
– Taille maximale de 4000 bytes.
– On peut aussi utiliser les mot-clés CHAR ou BYTE.
– Le type Oracle conseillé est VARCHAR2.
• Type CLOB :
– Taille maximale = (4GB5 - 1 byte) * (taille de bloc de BD). Entre 8 et 128 TB.
types caractères (tests)
1. Quel est le jeu de caractères de la BD ?
select value from nls_database_parameters where parameter = '
NLS_CHARACTERSET' ;
WE8MSWIN1252
5 GB
(gigabyte) ‰ Gb (gigabits)
Techniques Informatiques
73
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
2. Nombre de caractères d'une chaîne d'encodage WE8MSWIN1252.
select length ('bonjour') as res from dual;
7
3. Nombre de bytes d'une chaîne d'encodage WE8MSWIN1252.
select lengthb ('bonjour') as res from dual;
7
4. Quel est le jeu de caractères nationaux (Unicode) de la BD ?
select value from nls_database_parameters where parameter = '
NLS_NCHAR_CHARACTERSET' ;
AL16UTF16
5. Nombre de caractères d'une chaîne d'encodage AL16UTF16.
select length (N'bonjour') as res from dual;
7
6. Nombre de bytes d'une chaîne d'encodage AL16UTF16.
select lengthb (N'bonjour') as res from dual;
14
à propos de WE8MSWIN1252
• Jeu de caractères créé par Microsoft.
• Sur-ensemble du jeu de caractères ISO-8859-1 (nommé WE8ISO8859P1 chez Oracle).
• Déﬁnit certains code points non utilisés par ISO-8859-1. Par exemple le symbole Euro.
• Déﬁnit tous les caractères utilisés par ISO-8859-15 (nommé WE8ISO8859P15 chez Oracle)
mais pas de la même manière6 .
• Explications détaillées à propos des jeux de caractères sur Wikipedia.
9.2.4 Types binaires
types binaires
<bit string type> : := BIT [ ( <length> ) ]
| BIT VARYING ( <length> )
<binary large object string type> : :=
BINARY LARGE OBJECT [ ( <large object length> ) ]
• Type BIT.
– Représente les chaînes de bits de taille ﬁxe.
– BIT ” BIT(1).
• Type BIT VARYING.
– Représente les chaînes de bits de taille variable.
6 Càd
pas dans le même ordre.
Techniques Informatiques
74
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
– Il n'y a pas de longueur par défaut.
• Type BINARY LARGE OBJECT (abbrev. BLOB).
– Représente les larges séquences de bytes.
– Il n'y a pas de longueur par défaut.
types binaires (Oracle)
• Les types BIT et BIT VARYING n'ont pas de correspondances chez Oracle.
• Le type BLOB. La taille est la même que pour le type CLOB.
9.2.5 Types temporels
types temporels
<datetime type> : := DATE
| TIME [ ( <time precision> ) ]
[ <with or without time zone> ]
| TIMESTAMP [ ( <timestamp precision> ) ]
[ <with or without time zone> ]
<time precision> : := <time fractional seconds precision>
<time fractional seconds precision> : := <unsigned integer>
<with or without time zone> : := WITH TIME ZONE |
WITHOUT TIME ZONE
<timestamp precision> : := <time fractional seconds precision>
• DATE pour représenter une date sous forme d'année, mois et jour. Il utilise 10 positions.
– Le jour est sur 2 chiffres de 01 à 31.
– Le mois est sur 2 chiffres de 01 à 12.
– L'année est sur 4 chiffres de 0001 à 9999.
• TIME pour représenter un temps sous forme d'heure, minute et seconde. Il utilise 8 positions
ou 9 + le nombre de chiffres de la partie fractionnaire.
– L'heure est sur 2 chiffres de 00 à 23.
– Les minutes sont sur 2 chiffres de 00 à 59.
– Les secondes sont sur 2 chiffres avec une partie fractionnaire optionnelle de 00 à 61.999....
• TIMESTAMP représente une date et une heure. Il utilise 19 positions ou 20 + le nombre de
chiffres de la partie fractionnaire.
– Par défaut, la précision est de 6.
– Avec TIMESTAMP(p) on indique la précision p.
• TIME WITH TIME ZONE équivaut au type TIME avec l'offset par rapport UTC (Universal
Coordinated Time, anc. GMT).
– L'offset est représenté avec un type intervalle INTERVAL HOUR TO MINUTE.
– L'offset varie de -12 :59 à +13 :00.
– Le nombre de positions est 8 + 6 = 14.
– Une partie fractionnaire peut être spéciﬁée.
• TIMESTAMP WITH TIME ZONE équivaut au type TIMESTAMP avec l'offset par rapport à UTC.
Techniques Informatiques
75
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
– Le nombre de positions est 19 + 6 = 25.
– Une partie fractionnaire peut être spéciﬁée.
types temporels (Oracle)
• Le type TIME n'existe pas.
• Le type DATE sert à représenter des dates du calendrier.
– On stocke aussi les heures, minutes et secondes en les ﬁxant à 0 par défaut.
– En plus de l'année, le siècle est représenté.
– Les fonctions TO_DATE et TO_CHAR permettent resp. de passer d'un représentation textuelle à une date et d'une date à une représentation textuelle.
– Le paramètre de session NLS_DATE_FORMAT contrôle le format de la date.
– Ces fonctions utilisent un format de date précis.
• Le type TIMESTAMP sert à stocker des dates-heures précises.
– On stocke l'année, le mois, le jour, l'heure, les minutes et les secondes.
– Les secondes peuvent être fractionnaires. TIMESTAMP(n) stocke n chiffres pour la précision des secondes avec 0 ď n ď 9. Par défaut, c'est 6.
• Le type TIMESTAMP WITH TIME ZONE est comme TIMESTAMP avec un offset UTC en plus.
• Le type TIMESTAMP WITH LOCAL TIME ZONE est comme TIMESTAMP WITH TIME ZONE
mais l'heure est normalisée : l'offset UTC n'est pas stocké dans la BD.
les types temporels (fuseau horaire)
1. Fuseau de la BD.
select dbtimezone from dual;
+00 :00
2. Fuseau de la session.
select sessiontimezone from dual;
+00 :00
3. On examine la liste des fuseaux horaires.
select tzname, tzabbrev from v$timezone_names where tzname
like 'Europe/B%' ;
4. On altère le fuseau horaire de la session.
alter session set time_zone = 'Europe/Brussels' ;
Session altered.
5. On vériﬁe que la modiﬁcation a bien été faite.
select sessiontimezone from dual;
Europe/Brussels
6. Date/Heure courante complète.
select systimestamp from dual;
10-MAR-07 10.41.57.343000 AM +01 :00
Techniques Informatiques
76
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
les types temporels (tests)
1. On utilise un littéral date. Celui-ci s'écrit toujours YYYY-MM-DD. Remarquez le format d'afﬁchage
qui est différent du format du littéral.
select date '2007-01-31' from dual;
31-JAN-07
2. Examinons le format d'afﬁchage du type DATE.
select * from nls_session_parameters where parameter like '
NLS_DATE_FORMAT' ;
DD-MON-RR
3. On peut altérer ce format pour la session en cours.
alter session set nls_date_format = 'DD MM YYYY HH24 :MI :SS' ;
Session altered.
4. Vériﬁons que l'altération a bien eu lieu en afﬁchant une date.
select date '2007-01-31' from dual;
31 01 2007 00 :00 :00
5. En indiquant que la langue de la session est le français, on peut afﬁcher les dates en utilisant
les jours et mois en français.
alter session set nls_date_language = 'FRENCH' ;
select to_char(date '2007-01-31','Day DD FMmonth YYYY') as
res from dual;
Mercredi 31 janvier 2007
1. On afﬁche un littéral TIMESTAMP.
select timestamp '2007-01-31 09 :30 :50.1234' from dual;
31-JAN-07 09.30.50.123400000 AM
2. On afﬁche un littéral TIMESTAMP WITH TIME ZONE.
select timestamp '2007-01-31 08 :00 :00 +01 :00' from dual;
31-JAN-07 08.00.00.000000000 AM +01 :00
3. La même heure en spéciﬁant le fuseau horaire de manière symbolique.
select timestamp '2007-01-31 08 :00 :00 Europe/Brussels' from
dual;
31-JAN-07 08.00.00.000000000 AM EUROPE/BRUSSELS
4. On "caste" le littéral en un TIMESTAMP WITH LOCAL TIME ZONE. Comme le fuseau du
littéral et de la session sont les mêmes, cela revient simplement à éliminer l'offset UTC.
select cast(timestamp '2007-01-31 08 :00 :00 Europe/Brussels'
as timestamp with local time zone) from dual;
31-JAN-07 08.00.00.000000 AM
5. On refait la même opération avec un littéral dont le fuseau horaire est celui de New-York.
Remarquez que l'heure de New-York est convertie en heure de Bruxelles en ajoutant 6h.
Techniques Informatiques
77
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
select cast(timestamp '2007-01-31 08 :00 :00 America/New_York'
as timestamp with local time zone) from dual;
31-JAN-07 02.00.00.000000 PM
6. On peut convertir des dates d'un fuseau horaire à un autre avec la notation AT TIME ZONE.
select timestamp '2007-01-31 08 :00 :00 Europe/Brussels' at
time zone 'America/New_York' from dual;
31-JAN-07 02.00.00.000000000 AM AMERICA/NEW_YORK
9.2.6 Types intervalles
les intervalles
Les intervalles représentent la différence entre deux dates ou deux heures.
• Les intervalles year-month contiennent une année, un mois ou les deux.
– INTERVAL YEAR, INTERVAL YEAR(p), INTERVAL MONTH, INTERVAL MONTH(p), INTERVAL YEAR TO MONTH, INTERVAL YEAR(p) TO MONTH.
– p indique le nombre de chiffres dans le seul champ présent ou dans le champ de tête.
– Par défaut p vaut 2.
• Les intervalles day-time contiennent un jour, une heure, des minutes et/ou des secondes.
– INTERVAL f1 ou INTERVAL f1 TO f2.
– f1 et f2 valent DAY, HOUR, MINUTE ou SECOND.
– Si f1 et f2 sont présents, une valeur de ce type doit contenir toutes les valeurs intermédiaires.
– On peut indiquer une précision pour f1 . Par défaut elle vaut 2.
– On peut indiquer une précision pour f2 s'il s'agit de SECONDS. Par défaut elle vaut 6.
les intervalles (Oracle)
• Les types intervalles Oracle sont quasiment les mêmes que ceux du SQL.
• Pour les intervalles mois-années, la précision porte uniquement sur le champ YEAR.
• Pour les intervalles jours-secondes, les précisions portent sur le champ DAY et sur le champ
SECONDS.
les intervalles (tests)
1. Remarquez l'utilisation d'un littéral intervalle.
select interval '45' month from dual;
+03-09
2. Si le nombre de mois excède 99 ans et 11 mois, on a une erreur car la précision par défaut est
de 2.
select interval '1200' month from dual;
ORA-01873 : the leading precision of the interval is too small
3. Remarquez que l'année est codée sur 4 chiffres.
Techniques Informatiques
78
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
select interval '10-5' year(4) to month from dual;
+0010-05
4. La précision 6 agit sur le nombre de jours et non le nombre d'heures comme on aurait pu le
penser.
select interval '480 :45 :10' hour(6) to second(0) from dual;
+000020 00 :45 :10
9.3 Instructions du LDD
les instructions LDD
<SQL schema statement> : := <SQL schema definition statement>
| <SQL schema manipulation
statement>
<SQL schema definition statement> : := <schema definition>
| <table definition>81
| <view definition>
| <domain definition>79
| <assertion definition>
| <trigger definition>
| ...
Pour tout ce qui concerne la déﬁnition des données, on utilise :
• CREATE pour créer quelque chose.
• ALTER pour modiﬁer quelque chose.
• DROP pour se débarasser de quelque chose.
• SELECT pour obtenir des informations sur quelque chose.
• Les instructions de type DD (déﬁnition de données) ont pour effet de provoquer des actions
de type MD (manipulation de données) sur le dictionnaire de données (information schema).
• L'utilisateur n'a pas accès directement au dictionnaire de données.
création de domaine
<domain definition> : := CREATE DOMAIN <domain name> [ AS ] <
data type>
[ <default clause>80 ]
{ <domain constraint> }
<domain constraint> : := [ <constraint name definition>80 ]
<check constraint definition>80
[ <constraint characteristics>84 ]
• domain name est le nom du domaine déﬁni.
• data type est le nom d'un type existant (cfr. slide 70).
• domain constraint représente une contrainte sur les valeurs du type.
CREATE DOMAIN nom_d AS VARCHAR(30) DEFAULT NULL
CREATE DOMAIN percent_d AS NUMERIC(5,2) CHECK (VALUE BETWEEN
0 AND 100)
Techniques Informatiques
79
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
• Notez que d'après [8], les domaines pourraient disparaître du standard car peu utilisés.
• L'implémentation de Oracle ne contient pas les domaines.
valeur par défaut
<default clause> : := DEFAULT <default option>
<default option> : := <literal>96 | <datetime value function>
105
| USER | CURRENT_USER | CURRENT_ROLE | SESSION_USER
| SYSTEM_USER | CURRENT_PATH
| <implicitly typed value specification>108
• Valeur donné à la colonne lorsque celle-ci n'est pas spéciﬁée explicitement.
• Notez que pour Oracle la valeur par défaut est simplement une expression SQL quelconque.
contraintes de domaine
<constraint name definition> : := CONSTRAINT <constraint name>
<constraint name> : := <schema qualified name>
<check constraint definition> : := CHECK ( <search condition>
94 )
• Le mot-clé VALUE représente la nouvelle valeur.
9.3.1 Types de table
types de tables
• Une table est un multiset7 de lignes (tuples ou enregistrements).
• Une ligne (row) est une séquence8 non vide de valeurs (dans des colonnes).
• Le degré d'une table est le nombre de colonnes et la cardinalité de la table est le nombre de
lignes.
Il existe 3 types de tables :
• Les tables de base (base tables). Elles se déclinent en 4 sous-types différents :
– Persistante.
– Temporaire globale.
– Temporaire locale créée.
– Temporaire locale déclarée.
• Les vues (viewed tables ou views).
• Les tables dérivées (derived tables).
les tables de base
Les données d'une table de base sont stockées sur disque ou en mémoire.
• Persistante.
7 Ensemble
8 Cela
non ordonné pouvant contenir plusieurs éléments identiques.
implique un ordre.
Techniques Informatiques
80
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
– La déﬁnition est stockée dans un schéma.
– Les données sont stockées sur disque.
– Visibilité inter-sessions.
• Temporaire globale et temporaire locale créée.9
– La déﬁnition est stockée dans un schéma.
– Les données sont stockée temporairement pendant la session.
– Le contenu de la table est détruit à la ﬁn de chaque session.
– Visibilité intra-sessions.
• Temporaire locale déclarée.
– La déﬁnition est stockée dans un module SQL.
– Créée lorsqu'une procédure du module est exécutée.
– Pour le reste, idem que les deux précédentes.
les tables dérivées et les vues
• Une table dérivée :
– Est obtenue directement ou indirectement à partir d'une ou plusieurs autres tables grâce
à l'évaluation d'une requête.
– Contient les valeurs des autres tables au moment de l'évaluation.
• Une vue :
– Est une table dérivée nommée déﬁnie à l'aide de CREATE VIEW.
– Peut être modiﬁable ou non.
9.3.2 CREATE TABLE
création de table
<table definition> : := CREATE [ <table scope> ] TABLE <table
name>
<table contents source> ...
<table contents source> : := <table element list> | ...
<table element list> : := ( <table element> { , <table element
> } )
<table element> : :=
<column definition>81
| <table constraint definition>85
| <like clause>
| <self-referencing column specification>
| <column options>
• Les contraintes peuvent apparaître au niveau des colonnes (column definition) ou au
niveau de la table (table constraint definition).
• Documentation Oracle pour CREATE TABLE.
déﬁnition de colonne
9 La
différence entre les deux tient à la visibilité entre modules SQL.
Techniques Informatiques
81
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
<column definition> : := <column name>
( <data type>70 | <domain name> )
[ <default clause>80 ]
{ <column constraint definition>82 }
<column name> : := <identifier>
contrainte de colonne
<column constraint definition> : := [ <constraint name
definition> ]
<column constraint>
[ <constraint
characteristics> ]
<constraint name definition> : := CONSTRAINT <constraint name>
<constraint name> : := <schema qualified name>
<column constraint> : := NOT NULL | <unique specification>82
| <references specification>83 | <check constraint
definition>83
• constraint name permet de nommer explicitement la contrainte. Sinon, le SGBD s'en
charge mais les noms choisis n'ont aucune signiﬁcation.
select constraint_name from user_constraints
where owner = 'LUDO' and table_name = 'LIVRE' ;
SYS_C005313
SYS_C005314
SYS_C005315
SYS_C005316
• Documentation Oracle sur les contraintes.
contraintes UNIQUE & PRIMARY KEY (col.)
<unique specification> : := UNIQUE | PRIMARY KEY
Contrainte de colonne PRIMARY KEY :
• Indique que la colonne est une clé primaire.
Contrainte de colonne UNIQUE :
• Indique que chaque valeur de la colonne doit être unique.
• Si la colonne n'est pas une PK, cela spéciﬁe que c'est une clé secondaire.10
• Attention, plusieurs valeurs peuvent êtres nulles. Il est bon de spéciﬁer également NOT NULL
sur les colonnes.
contrainte UNIQUE (exemple)
create table livre (
id number primary key,
isbn varchar2(10) unique,
auteur number references auteur(id),
année date,
unique (auteur, année));
10 Sauf
pour les valeurs nulles. Ce n'est donc le cas que si on spéciﬁe aussi NOT NULL.
Techniques Informatiques
82
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
• Pour la contrainte de colonne, cfr. slide 82.
• Pour la contrainte de table, cfr. slide 85.
contrainte REFERENCES (col.)
<references specification> : := REFERENCES <referenced table
and columns> ...
<referenced table and columns> : := <table name> [ ( <
reference column list> ) ]
<reference column list> : := <column name list>
<column name list> : := <column name> { , <column name> }
• Indique que la colonne fait référence à la PK d'une autre table.
• Déﬁnit une clé étrangère (FK).
Exemple :
create table livre (
id number primary key,
isbn varchar2(10) unique,
auteur number references auteur(id),
année date,
unique (auteur, année));
contrainte CHECK (col. et table)
<check constraint definition> : := CHECK ( <search condition>
94 )
• Contrainte générale. Peuvent utiliser des sous-requêtes de sélection.
• Ne peut être fausse pour aucune ligne de la table.11
• Si la table est vide, elle est toujours vraie.
• Contrainte de colonne CHECK ne peut référencer que la colonne en cours.
• Contrainte de table CHECK peut référencer toutes les colonnes de la table en cours.
contrainte CHECK (tests)
create table livre (
titre varchar2(30) not null primary key constraint
livre_titre_check check (titre = UPPER(titre)),
cote number(1) constraint livre_cote_check
check (cote between 0 and 5),
etat varchar2(7) constraint livre_etat_check
check (etat in ('bon', 'mauvais')));
insert into livre values ('LA HUITEME COULEUR', 5, 'bon');
1 row created.
insert into livre values ('Le huitième sortilège', 4, 'bon');
ORA-02290 : check constraint (LUDO.LIVRE_TITRE_CHECK) violated
insert into livre values ('TROIS SOEURCIERES', 6, 'bon');
ORA-02290 : check constraint (LUDO.LIVRE_COTE_CHECK) violated
11 Pour
toutes les lignes, elle doit être vraie ou inconnue. Nous reviendrons sur cela.
Techniques Informatiques
83
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
caractéristiques de contrainte
<constraint characteristics> : :=
<constraint check time> [ [ NOT ] DEFERRABLE ]
| [ NOT ] DEFERRABLE [ <constraint check time> ]
<constraint check time> : := INITIALLY DEFERRED |
INITIALLY IMMEDIATE
• INITIALLY DEFERRED indique que les contraintes sur la table ou la colonne ne seront appliquées qu'à la ﬁn de chaque transaction.
• INITIALLY IMMEDIATE indique que les contraintes sur la table ou la colonne seront appliquées à la ﬁn de chaque instruction.
• DEFERRABLE et NOT DEFERRABLE indiquent si l'instruction SET CONSTRAINT peut être utilisée sur la contrainte.
• Documentation Oracle sur SET CONSTRAINT.
• Documentation Oracle sur les contraintes.
caractéristiques de contrainte (tests)
1. On crée la table auteur avec une contrainte NOT NULL sur le nom qui est testée à la ﬁn de
l'instruction mais peut être différée.
create table auteur (
id number primary key,
nom varchar2(30) constraint auteur_nom_nn not null
initially immediate deferrable);
2. Choisissons de différer la veriﬁcation de la contrainte.
set constraint auteur_nom_nn deferred;
3. On insère un tuple dont le num est NULL.
insert into auteur values(0, NULL);
4. On met à jour le nom pour qu'il ne soit plus NULL à l'aide d'une autre instruction.
update auteur set nom = 'Pratchett' where id = 0 ;
5. Enﬁn, on termine la transaction. La vériﬁcation de la contrainte a lieu à ce moment.
commit;
1. On crée la table auteur avec une contrainte NOT NULL sur le nom qui est testée à la ﬁn de
la transaction.
create table auteur (
id number primary key,
nom varchar2(30) constraint auteur_nom_nn not null
initially deferred deferrable);
2. On insère un auteur dont le nom est NULL et on termine la transaction.
insert into auteur values(0, NULL);
commit;
Techniques Informatiques
84
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
3. La vériﬁcation en ﬁn de transaction montre que la contrainte est violée.
ORA-02091 : transaction rolled back
ORA-02290 : check constraint (LUDO.AUTEUR_NOM_NN) violated
contrainte de table
<table constraint definition> : := [ <constraint name
definition>82 ]
<table constraint>
[ <constraint
characteristics> ]
<table constraint> : := <unique constraint definition>85
| <referential constraint definition>85
| <check constraint definition>83
contrainte UNIQUE (table)
<unique constraint definition> : := <unique specification>82
( <unique column list> ) | UNIQUE ( VALUE )
<unique column list> : := <column name list>83
• UNIQUE (VALUE) spéciﬁe que la table ne peut contenir aucun doublon.
• Indiquer qu'une ou plusieurs colonnes forment une clé primaire.
create table auteur (
nom varchar2(30),
prénom varchar2(30),
constraint auteur_pk primary key (nom, prénom));
contrainte FOREIGN KEY (table)
<referential constraint definition> : :=
FOREIGN KEY ( <referencing columns> ) <references
specification>83
<referencing columns> : := <reference column list> ??
• Indiquer qu'une ou plusieurs colonnes forment une clé étrangère.
create table auteur (
id number primary key,
nom varchar2(30));
create table livre (
id number primary key,
isbn varchar2(10) unique,
auteur number,
constraint fk_auteur foreign key (auteur) references auteur(id))
;
accès aux contraintes
Techniques Informatiques
85
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
• A quelles colonnes a-t-on accès dans la table USER_CONSTRAINTS ?
select column_name, comments from dict_columns where
table_name = 'USER_CONSTRAINTS' ;
OWNER
Owner of the table
CONSTRAINT_NAME Name associated with constraint definition
CONSTRAINT_TYPE Type of constraint definition
TABLE_NAME
Name associated with table with constraint
definition
SEARCH_CONDITION Text of search condition for table check
...
• Soit la table livre :
create table livre (
id number constraint pk_livre primary key,
isbn varchar2(10) constraint unique_livre_isbn unique,
auteur number,
edition number constraint check_livre_edition
check (edition between 1 and 10),
constraint fk_livre_auteur foreign key (auteur) references
auteur(id));
• Les contraintes déﬁnies sur cette table sont :
select constraint_name, constraint_type, table_name
from user_constraints
where owner = 'LUDO' and table_name = 'LIVRE' ;
CHECK_LIVRE_EDITION C LIVRE
PK_LIVRE
P LIVRE
UNIQUE_LIVRE_ISBN
U LIVRE
FK_LIVRE_AUTEUR
R LIVRE
9.3.3 DROP TABLE
instruction DROP TABLE
<drop table statement> : := DROP TABLE <table name> <drop
behavior>
<drop behavior> : := CASCADE | RESTRICT
• Supprime la table du schéma.
• L'option RESTRICT empêche la suppression si :
– La table est utilisée dans une requête en cours.
– La table est utilisée dans une vue.
– La table est référencée par une autre table.
• L'option CASCADE force l'effacement de la table et de tout ce qui en dépend comme les vues
ou les contraintes.
instruction DROP TABLE (Oracle)
DROP TABLE nom_table [ CASCADE CONSTRAINTS ] [ PURGE ] ;
Techniques Informatiques
86
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
• Documentation Oracle pour DROP TABLE.
• CASCADE devient CASCADE CONSTRAINTS.
• RESTRICT ne peut pas être mentionnée explicitement.
• PURGE évite de placer la table dans la corbeille si celle-ci existe.
• La corbeille peut être purgée et désactivée en écrivant en tant que SYS :
purge tablespace users;
alter system set recyclebin = off;
l'instruction DROP TABLE (tests)
1. La colonne id_A de B fait référence à une ligne de A.
create table A (id number primary key);
create table B (id number primary key,
id_A number references A(id));
2. On a deux contraintes d'intégrité : une PK (lettre P) et une FK (lettre R).
select constraint_name, constraint_type from user_constraints
where owner = 'LUDO' and table_name = 'B' ;
SYS_C005347
P
SYS_C005348
R
3. On essaie d'effacer la table référencée A.
drop table A;
ORA-02449 : unique/primary keys in table referenced by foreign
keys
4. L'effacement a échoué, il y a toujours 2 tables.
select table_name from tabs;
A
B
5. Cette fois, on recommence avec l'option CASCADE CONSTRAINTS.
drop table A cascade constraints;
Table dropped.
6. La table a bien été effacée.
select table_name from tabs;
B
7. La contrainte correspondante de B aussi.
select constraint_name, constraint_type from user_constraints
where owner = 'LUDO' and table_name = 'B' ;
SYS_C005347
P
Techniques Informatiques
87
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
9.3.4 ALTER TABLE
instruction ALTER TABLE
<alter table statement> : := ALTER TABLE <table name> <alter
table action>
<alter table action> : := <add column definition>88
| <alter column definition>88
| <drop column definition>88
| <add table constraint definition>88
| <drop table constraint definition>88
• Permet d'altérer la structure d'une table càd sa déﬁnition. L'instruction agit donc sur le schéma.
• Avec Oracle, il y a des différences importantes par rapport au standard SQL.
• Documentation Oracle sur ALTER TABLE.
ajout de colonne
<add column definition> : := ADD [ COLUMN ] <column definition
>81
• column definition est la syntaxe utilisée lors du CREATE TABLE.
• Avec Oracle, le mot-clé COLUMN ne peut pas être présent.
modiﬁcation & destruction de colonne
<alter column definition> : := ALTER [ COLUMN ] <column name>
<alter column action>
<alter column action> : := <set column default clause>
| <drop column default clause>
| ...
<set column default clause> : := SET <default clause>
<drop column default clause> : := DROP DEFAULT
<drop column definition> : := DROP [ COLUMN ] <column name> <
drop behavior>86
ajout & destruction de contrainte
<add table constraint definition> : := ADD <table constraint
definition>85
<drop table constraint definition> : := DROP CONSTRAINT <
constraint name> <drop behavior>86
instruction ALTER TABLE (tests)
1. On crée la table A sans PK.
create table A (id number);
Techniques Informatiques
88
HEPL 2014–2015
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
2. On ajoute ensuite la PK.
alter table A add constraint A_pk primary key (id);
Table altered.
3. Vériﬁons que la contrainte a bien été ajoutée.
select constraint_name, constraint_type from user_constraints
where owner = 'LUDO' and table_name = 'A' ;
A_PK
P
4. On ajoute également une colonne.
alter table A add (nom varchar2(30) not null);
5. De même, vériﬁons que la colonne a bien été ajoutée.
select column_name, data_type from user_tab_columns where
table_name = 'A' ;
ID
NUMBER
NOM
VARCHAR2
Techniques Informatiques
89
HEPL 2014–2015
Techniques Informatiques
9. Langage de Déﬁnition de Données (LDD)
Ludovic Kuty
90
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
Chapitre 10
Langage de Manipulation de
Données (LMD)
10.1 Présentation
présentation
Langage de manipulation de données ou LMD (Data Manipulation Language ou DML).
Utilisé pour agir sur les données des tables du schéma càd :
• Rechercher des informations
• Ajouter de nouvelles lignes dans une table
• Modiﬁer la valeur de certains attributs d'une ligne
• Effacer certaines lignes d'une table
instructions du LMD
<direct SQL data statement> : :=
<delete statement : searched>121
| <direct select statement : multiple rows>116
| <insert statement>119
| <update statement : searched>120
| ...
Pour tout ce qui concerne la manipulation des données, on utilise :
• INSERT pour créer quelque chose
• UPDATE pour modiﬁer quelque chose
• DELETE pour se débarasser de quelque chose
• SELECT pour obtenir des informations sur quelque chose
A mettre en correspondance avec les instructions LDD (cfr. slide 79).
origine des exemples
• Les exemples sont basés sur les tables déﬁnies aux pages 91 à 94 de [4]
• Certains exemples proviennent directement de la référence en question
• Le script de création des tables1 est disponible sur le site du cours
1 script_dml_tables_1.sql
Techniques Informatiques
91
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
10.2 SELECT
l'instruction SELECT
<query specification> : := SELECT [ ALL | DISTINCT ] <select
list>92
<from clause>93
[ <where clause>94 ]
[ <group by clause>117 ]
[ <having clause>118 ]
• L'instruction SELECT est aussi appelée SFW2 dans la littérature
• Les opérations effectuées par SELECT sont à mettre en correspondance avec les opérateurs
de l'algèbre relationnelle
– La liste de sélection réalise une projection
– La clause where réalise une sélection
– La jointure et les opérateurs ensemblistes ne sont pas représentés ici
• Le mot-clé DISTINCT permet d'éliminer les tuples en double
• Documentation Oracle sur l'instruction SELECT
10.2.1 Liste de sélection
SELECT (liste de sélection)
<select list> : := * | <select sublist> { , <select sublist> }
<select sublist> : := <derived column> | <qualified asterisk>
<derived column> : := <value expression>99
[ [ AS ] <column
name> ]
<qualified asterisk> : := <asterisked identifier chain> . *
| <all fields reference>
<asterisked identifier chain> : := <asterisked identifier>
{ . <asterisked identifier>
}
<asterisked identifier> : := <identifier>
La liste de sélection peut être :
• Un astérisque pour projeter sur toutes les colonnes de la table obtenue avec le from-where
• Ou une liste de sous-listes séparées par des virgules composées de :
– Colonne dérivée càd une expression quelconque éventuellement renommée
– Astérisque qualiﬁée
SELECT (projection 1)
• Obtenir la liste des nom, prénom et date de naissance de tous les élèves
2 Pour
select-from-where car il s'agit de la forme canonique d'un select.
Techniques Informatiques
92
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
select nom, prenom, date_naissance from eleves;
NOM
PRENOM
DATE_NAISSANCE
------------------------- ----------------------------------------Brisefer
Benoit
10/12/1978
Génial
Olivier
10/04/1978
Jourdan
Gil
28/06/1974
Spring
Jerry
16/02/1974
Tsuno
Yoko
29/10/1977
Lebut
Marc
29/04/1974
Lagaffe
Gaston
8/04/1975
Dubois
Robin
20/04/1976
Walthéry
Natacha
7/09/1977
Danny
Buck
15/02/1973
SELECT (projection 2)
• Obtenir la liste des spécialités des professeurs. On élimine les doublons avec DISTINCT
select distinct specialite from professeurs;
SPECIALITE
-------------------poésie
poo
réseau
sql
• Calculer 5 ˚ 2 + 4
select 5 * 2 + 4 as "Réponse" from dual;
Réponse
---------14
10.2.2 Clause FROM
SELECT (clause FROM)
<from clause> : :=
> }
<table reference>
<table primary> :
correlation name>
FROM <table reference> { , <table reference
: := <table primary> | <joined table>112
:=
<table or query name> [ [ AS ] <
]
Techniques Informatiques
93
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
| <derived table> [ AS ] <correlation
name>
| ( <joined table>112 )
| ...
<derived table> : := <table subquery>
<table subquery> : := <subquery>110
• Composée d'une liste de références à des tables séparées par des virgules
• correlation name est simplement un alias pour référencer la table. Utile comme raccourci
• derived table permet de spéciﬁer des sous-requêtes et joined table de faire des jointures. Cfr. plus loin
• Oracle ne permet pas d'utiliser le mot-clé AS
SELECT (clause FROM, exemples)
• Utilisation d'un alias pour afﬁcher le nom de tous les élèves
select e.nom from eleves e;
NOM
--------Brisefer
Génial
Jourdan
Spring
Tsuno
Lebut
Lagaffe
Dubois
Walthéry
Danny
10.2.3 Clause WHERE
SELECT (clause WHERE)
<where clause> : := WHERE <search condition>230
• La clause where permet de sélectionner des tuples sur base d'une condition. Il s'agit donc
d'un ﬁltrage
• La notion de condition est très générale car elle est entre autre utilisée avec les instructions
UPDATE et DELETE
condition de recherche
<search condition> : := <boolean value expression>
<boolean value expression> : :=
<boolean term>
| <boolean value expression> OR <boolean term>
<boolean term> : :=
<boolean factor>
| <boolean term> AND <boolean factor>
<boolean factor> : := [ NOT ] <boolean test>
Techniques Informatiques
94
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
<boolean test> : := <boolean primary> [ IS [ NOT ] <truth
value> ]
<boolean primary> : := <predicate>95
| ( <boolean value expression> )
| <nonparenthesized value expression primary>100
<truth value> : := TRUE | FALSE | UNKNOWN
• Déﬁnit l'utilisation des opérateurs OR, AND et NOT et leurs règles d'associativité
• La richesse de la condition est cachée dans les classes predicate et nonparenthesized
value expression primary
• Documentation Oracle sur les conditions et plus particulièrement sur les conditions logiques
Les prédicats
prédicats
<predicate> : :=
predicate>97
<comparison predicate>96 | <between
| <in predicate>97 | <like predicate>98
| <null predicate>98 | <quantified comparison
predicate>116
| <exists predicate> | <unique predicate>
| <match predicate> | <overlaps predicate>
| <similar predicate> | <distinct predicate>
| ...
• Un prédicat est une expression qui afﬁrme une relation entre des valeurs
• Le prédicat peut être vrai, faux ou inconnu (dû à la présence de la valeur NULL)
la valeur NULL
• NULL est un ﬂag qui indique l'absence de valeur3 d'une colonne
• Utile si valeur de colonne inconnue ou sans signiﬁcation. Par ex., retenir le numéro de GSM
des personnes alors que certaines n'ont pas de GSM
• NULL impose une logique trivalente ñ on dispose de la valeur booléenne UNKNOWN en plus
de TRUE et FALSE
• Attention, la valeur UNKNOWN se propage dans les comparaisons
• Oracle n'a pas de type booléen. NULL est utilisé pour représenter UNKNOWN
tables de vérité de AND, OR et NOT
AND
T
F
U
OR
T
F
U
3 Notez
T
T
F
U
T
T
T
T
F
F
F
F
F
T
F
U
U
U
F
U
U
T
U
U
que par abus de langage, on dira que la colonne a la valeur NULL.
Techniques Informatiques
95
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
T
F
U
Ludovic Kuty
NOT
F
T
U
comparaisons logiques
<comparison predicate> : :=
<row value expression> <comp op> <row value expression>
<comp op> : := = | <> | < | > | <= | >=
<row value expression> : := <row value special case> | <row
value constructor>96
<row value special case> : := <value specification> | <value
expression>99
<value specification> : := <literal>96 | <general value
specification>
• Documentation Oracle sur les comparaisons logiques
row value constructor
<row value constructor> : :=
<row value constructor element>
| [ ROW ] ( <row value constructor element list> )
| <row subquery>
<row value constructor element> : := <value expression>99
<row value constructor element list> : :=
<row value constructor element>
{ , <row value constructor element> }
<row subquery> : := <subquery>110
• Permet de manipuler des lignes entières
• Très utile lors de comparaisons
• Quasiment pas supporté par Oracle
• Exemple 1 sur le slide 278
littéraux
<literal> : := <signed numeric literal> | <general literal>
<signed numeric literal> : := [ + | - ] <unsigned numeric
literal>
<unsigned numeric literal> : := <exact numeric literal> | <
approximate numeric literal>
<exact numeric literal> : :=
<unsigned integer> [ . [ <unsigned integer> ] ]
| . <unsigned integer>
<approximate numeric literal> : := <mantissa> E <exponent>
<mantissa> : := <exact numeric literal>
<exponent> : := <signed integer>
<signed integer> : := [ + | - ] <unsigned integer>
<general literal> : := <character string literal> | <national
character string literal>
| <bit string literal> | <hex string literal>
Techniques Informatiques
96
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
| <binary string literal> | <datetime literal>
| <interval literal> | <boolean literal>
• Pour info. Cfr doc. SQL pour de plus amples informations
• Documentation Oracle sur les littéraux
comparaisons logiques (exemples)
• Obtenir les nom et prénom des élèves pesant mois de 45 kg et inscrits en 1ère année ou des
élèves inscrits en 2ème année
select nom, prenom from eleves where (poids < 45 and annee =
1) or annee = 2 ;
NOM
PRENOM
------------------------- ------------------------Brisefer
Benoit
Génial
Olivier
Jourdan
Gil
Spring
Jerry
Lebut
Marc
Dubois
Robin
Danny
Buck
prédicat BETWEEN
<between predicate> : := <row value expression>96 [ NOT ]
BETWEEN
<row value expression>96 AND <row
value expression>96
• Obtenir le nom des élèves dont le poids est compris entre 60 et 80 kg
select nom from eleves where poids between 60 and 80 ;
NOM
------------------------Jourdan
Spring
Lebut
Lagaffe
Dubois
• Documentation Oracle sur le prédicat BETWEEN
prédicat IN
<in predicate> : := <row value expression>96 [ NOT ] IN <in
predicate value>
<in predicate value> : :=
<table subquery>93 | ( <in value
list> )
<in value list> : := <row value expression>96 { , <row value
expression>96 }
• Obtenir le nom des professeurs dont la spécialité est poésie ou sql
Techniques Informatiques
97
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
select nom from professeurs where specialite in ('poésie', '
sql');
NOM
------------------------Bottle
Pastecnov
Selector
Pucette
• Documentation Oracle sur le prédicat IN
prédicat LIKE
<like predicate> : := <character like predicate>
<character like predicate> : := <character match value> [ NOT
] LIKE <character pattern>
[ ESCAPE <escape character> ]
<character match value> : := <character value expression>103
<character pattern> : := <character value expression>103
• character pattern est une chaîne quelconque
• Trois caractères jouent un rôle particulier :
– _ remplace un seul caractère
– % remplace un nombre quelconque de caractères (peut être 0)
– Le caractère d'échappement permet d'indiquer littéralement un des deux caractères
précédents. Par ex. pour rechercher n'importe quelle chaîne contenant le caractère %,
on peut écrire :
... LIKE '%@%%' ESCAPE '@' ;
• Documentation Oracle sur le prédicat LIKE
prédicat LIKE (exemple)
• Obtenir le nom des élèves dont le nom commence par la lettre L
select nom from eleves where nom like 'L%' ;
NOM
------------------------Lebut
Lagaffe
prédicat NULL
<null predicate> : := <row value expression>96 IS [ NOT ] NULL
• Le standard fournit un mécanisme de comparaison par rapport à la valeur NULL
• Documentation Oracle sur le prédicat NULL
prédicat NULL (exemple 1)
Techniques Informatiques
98
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
• Obtenir le nom des professeurs dont la spécialité est inconnue
select nom from professeurs where specialite is null;
NOM
------------------------Francesca
• Obtenir le nom des professeurs dont la spécialité est connue
select nom from professeurs where specialite is not null;
NOM
------------------------Bottle
Bolenov
Tonilaclasse
Pastecnov
Selector
Vilplusplus
Pucette
prédicat NULL (exemple 2)
• Contenu de la table test
select * from test;
1
2
(null)
(null)
3
5 rows selected
• Obtenir les IDs non NULL (mauvaise méthode)
select * from test where id <> null;
0 rows selected
• Obtenir les IDs non NULL (bonne méthode)
select * from test where id is not null;
1
2
3
les expressions composées
<value expression> : := <numeric value expression>100
| <string value expression>103 | <datetime value
expression>105
| <interval value expression>106
| <boolean value expression>94
| <row value expression>96
| ...
• Déﬁnit les expressions composées en toute généralité
Techniques Informatiques
99
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
les expressions primaires
<value expression primary> : :=
<parenthesized value expression>
| <nonparenthesized value expression primary>
<parenthesized value expression> : := ( <value expression>99 )
<nonparenthesized value expression primary> : :=
<unsigned value specification> | <column reference>
| <set function specification>107 | <scalar subquery>
| <case expression>108 | <cast specification>108 | ...
<scalar subquery> : := <subquery>110
<unsigned value specification> : := <unsigned literal> | <
general value specification>
<unsigned literal> : := <unsigned numeric literal>96 | <
general literal>96
• Déﬁnit les expressions considérées comme atomiques
• Des parenthèses autour d'une value expression la rendent atomique comme en C un bloc
d'instructions entouré d'accolades est une instruction
• Notez que ces expressions peuvent renvoyer des valeurs de types différents. Elles apparaissent donc dans la déﬁnition de nombreuses autres classes syntaxiques
les expressions numériques
<numeric value expression> : := <term>
| <numeric value expression> + <term>
| <numeric value expression> - <term>
<term> : := <factor> | <term> * <factor> | <term> / <factor>
<factor> : := [ + | - ] <numeric primary>
<numeric primary> : := <value expression primary>100 | <
numeric value function>
<numeric value function> : :=
<position expression>101
| <extract expression>101
| <length expression>102
| <absolute value expression>102
| <modulus expression>103
• La déﬁnition reprend la syntaxe habituelle des expressions arithmétiques
expressions numériques complexes
• Pour chaque spécialiste SQL, obtenir son nom, son salaire mensuel actuel et son augmentation mensuelle depuis son salaire de base
select nom, salaire_actuel / 12, (salaire_actuel salaire_base)/12 from professeurs where specialite = 'sql' ;
NOM
SAL
AUGM
------------------------- ---------- ---------Pastecnov
208333.333
0
Selector
158333.333
0
Techniques Informatiques
100
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Pucette
Ludovic Kuty
208333.333 41666.6666
• Obtenir le nom des professeurs dont l'augmentation relative au salaire de base dépasse 25%
select nom from professeurs where (salaire_actuel salaire_base)/salaire_base > 0.25 ;
Bottle
Bolenov
Francesca
fonction POSITION
<position expression> : := <string position expression> | ...
<string position expression> : := POSITION ( <string value
expression>103 IN
<string value
expression>
103 )
• Pour Oracle, POSITION devient INSTR
• Documentation Oracle sur les fonctions caractères renvoyant un nombre
fonction POSITION (exemples)
Chaîne : CORPORATE FLOOR, sous-chaîne : OR.
• Avec les paramètres par défaut
select instr('CORPORATE FLOOR', 'OR') from dual;
2
• Recherche de la troisième occurence
select instr('CORPORATE FLOOR', 'OR', 1, 3) from dual;
14
• Recherche de la première occurence à partir de la lettre T
select instr('CORPORATE FLOOR', 'OR', 8) from dual;
14
• Recherche à partir de la ﬁn de la troisième occurence
select instr('CORPORATE FLOOR', 'OR', -1, 3) from dual;
2
fonction EXTRACT
<extract expression> : := EXTRACT ( <extract field> FROM <
extract source> )
<extract field> : := <primary datetime field> | <time zone
field>
<extract source> : := <datetime value expression>105 | <
interval value expression>106
Techniques Informatiques
101
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
<primary datetime field> : := <non-second primary datetime
field> | SECOND
<time zone field> : := TIMEZONE_HOUR | TIMEZONE_MINUTE
<non-second primary datetime field> : := YEAR | MONTH | DAY |
HOUR | MINUTE
• Documentation Oracle sur la fonction EXTRACT
fonction EXTRACT (exemples)
• Extraction de l'année d'une date
select extract (year from date '2007-04-20') from dual;
2007
• Extraction des heures
select extract (hour from timestamp '2007-01-31
09 :30 :50.1234') from dual;
9
• Extraction des champs d'un intervalle
select extract(day from interval '485 :45 :10' hour(6) to
second(0)) from dual;
20
select extract(hour from interval '485 :45 :10' hour(6) to
second(0)) from dual;
5
select extract(minute from interval '485 :45 :10' hour(6) to
second(0)) from dual;
45
select extract(second from interval '485 :45 :10' hour(6) to
second(0)) from dual;
10
expression de longueur
<length expression> : :=
<char length expression>
| <octet length expression>
| <bit length expression>
<char length expression> : := ( CHAR_LENGTH |
CHARACTER_LENGTH ) ( <string value expression>103 )
<octet length expression> : := OCTET_LENGTH ( <string value
expression>103 )
<bit length expression> : := BIT_LENGTH ( <string value
expression>103 )
• Pour Oracle, CHAR_LENGTH et son synonyme deviennent LENGTH
• Documentation Oracle sur les fonctions caractères renvoyant un nombre
• Cfr. slide 73
fonction ABS
Techniques Informatiques
102
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
<absolute value expression> : := ABS ( <numeric value
expression>100 )
• Calcule la valeur absolue d'un nombre
• Documentation Oracle sur la fonction ABS
fonction MOD
<modulus expression> : := MOD ( <numeric value expression>100
, <numeric value expression>100 )
• MOD(n1,n2) calcule le reste de la division de n1 par n2 . Les échelles de n1 et n2 doivent être
nulles
• Documentation Oracle sur la fonction MOD
select mod(100, 3) from dual;
1
Les expressions caractères
les expressions caractères
<string value expression> : := <character value expression> |
...
<character value expression> : := <concatenation> | <character
factor>
<concatenation> : := <character value expression> || <
character factor>
<character factor> : := <character primary> [ <collate clause>
]
<character primary> : := <value expression primary>100 | <
string value function>
<string value function> : := <character value function> | ...
<character value function> : :=
<character substring function>103
| <fold>104
| <trim function>104
| ...
• Les syntaxes Oracle et SQL sont très différentes
• Documentation Oracle sur les fonctions caractères renvoyant une chaîne de caractères
fonction SUBSTRING
<character substring function> : := SUBSTRING (
<character value expression> FROM <start position>
[ FOR <string length> ] )
<start position> : := <numeric value expression>100
<string length> : := <numeric value expression>100
• Extrait une sous-chaîne hors d'une chaîne de caractères
Techniques Informatiques
103
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
• Documentation Oracle sur la fonction SUBSTR
• Il existe une syntaxe permettant d'utiliser des expressions régulières. Pour Oracle, il s'agit
de REGEXP_SUBSTR
fonction SUBSTRING (exemples)
• Retourne les 3 derniers caractères
select substr('bonjour', -3) from dual;
our
• Retourne la troisième sous-chaîne
select substr(' :un :deux :trois :quatre :cinq :', 10, 5) from dual
;
trois
• Retourne la troisième sous-chaîne
select regexp_substr(' :un :deux :trois :quatre :cinq :', ' :[^ :]+',
1, 3) from dual;
:trois
fonctions LOWER et UPPER
<fold> : := ( UPPER | LOWER ) ( <character value expression>
103 )
• Documentation Oracle sur les fonctions LOWER et UPPER
• Convertit une chaîne en majuscules
select upper('Chaîne de caracteres') from dual;
CHAÎNE DE CARACTERES
• Convertit une chaîne en minuscules
select lower('CHAÎNE DE CARACTERES') from dual;
chaîne de caractères
fonction TRIM
<trim function> : := TRIM ( <trim operands> )
<trim operands> : := [ [ <trim specification> ] [ <trim
character> ] FROM ] <trim source>
<trim specification> : := LEADING | TRAILING | BOTH
<trim character> : := <character value expression>103
<trim source> : := <character value expression>103
• Documentation Oracle sur les fonctions TRIM, LTRIM et RTRIM
• Elimine les espaces superﬂus en début et ﬁn de chaîne
select '.' || trim('
.un mot.
Techniques Informatiques
un mot
') || '.' from dual;
104
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
• Elimine certains caractères en début de chaîne
select '.' || ltrim('Name : Einstein', 'Name : ') || '.' from
dual;
.Einstein.
• Elimine les points en début de chaîne
select '.' || trim(LEADING '.' FROM '.....Name : ') || '.'
from dual;
.Name : .
Les expressions dates-temps
les expressions dates-temps
<datetime value expression> : := <datetime term>
| <interval value expression>106 + <datetime term>
| <datetime value expression> + <interval term>106
| <datetime value expression> - <interval term>106
<datetime term> : := <datetime factor>
<datetime factor> : := <datetime primary> [ <time zone> ]
<datetime primary> : := <value expression primary>100 | <
datetime value function>
<time zone> : := AT <time zone specifier>
<time zone specifier> : := LOCAL | TIME ZONE <interval primary
>
<datetime value function> : := CURRENT_DATE
| CURRENT_TIME [ ( <time precision> ) ]
| CURRENT_TIMESTAMP [ ( <timestamp precision> ) ]
| LOCALTIME [ ( <time precision> ) ]
| LOCALTIMESTAMP [ ( <timestamp precision> ) ]
• Documentation Oracle sur les fonctions dates-temps
les expressions dates-temps (exemples 1)
• Avec la précision par défaut
select current_date from dual;
04 04 2007 13 :44 :06
select current_timestamp from dual;
04/04/07 13 :44 :05,934389000 EUROPE/BRUSSELS
select localtimestamp from dual;
04/04/07 13 :44 :05,934389000
• En indiquant la précision
select current_timestamp(0) from dual;
04/04/07 13 :46 :51,000000000 EUROPE/BRUSSELS
select localtimestamp(0) from dual;
04/04/07 13 :46 :51,000000000
• Quel jour sera-t-on demain ?
Techniques Informatiques
105
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
select sysdate + 1 from dual;
05 04 2007 14 :27 :57
• Obtenir l'année de la dernière promotion du professeur Pucette
select substr(cast(der_prom as char(20)),7,4) from
professeurs where nom = 'Pucette' ;
select extract(year from der_prom) from professeurs where nom
= 'Pucette' ;
select to_char(der_prom, 'YYYY') from professeurs where nom =
'Pucette' ;
select to_number(to_char(der_prom, 'YYYY')) from professeurs
where nom = 'Pucette' ;
1996
Les expressions intervalles
les expressions intervalles
<interval value expression> : := <interval term>
| <interval value expression> + <interval term>
| <interval value expression> - <interval term>
| ( <datetime value expression>105 - <datetime term> ) <
interval qualifier>
<interval term> : := <interval factor>
| <interval term> * <factor>
| <interval term> / <factor>
| <term> * <interval factor>
<interval factor> : := [ + | - ] <interval primary>
<interval primary> : := <value expression primary>100 | <
interval absolute value function>
<interval absolute value function> : := ABS ( <interval value
expression> )
les expressions intervalles (exemples 1)
• Pour chaque professeur, afﬁcher sa date d'embauche, sa date de dernière promotion et le
nbre d'années entre les deux
select nom, to_char(der_prom,'DD MM YYYY') as "der_prom",
to_char(date_entree,'DD MM YYYY') as "date_entree", extract (
year from (der_prom - date_entree) year to month) as "années"
from professeurs;
NOM
der_prom
date_entree
années
------------------------- ---------- ----------- ---------Bottle
01 10 1988 01 10 1970
18
Bolenov
01 10 1998 15 11 1968
29
Tonilaclasse
01 01 1989 01 10 1979
9
Pastecnov
01 10 1975
Selector
15 10 1982
Vilplusplus
05 06 1994 25 04 1990
4
Francesca
11 01 1998 01 10 1975
22
Techniques Informatiques
106
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Pucette
29 02 1996 06 12 1988
Ludovic Kuty
7
• Afﬁcher l'âge moyen des élèves
select avg(extract(year from current_date) - extract(year
from date_naissance)) from eleves;
31.4
les expressions intervalles (exemples 2)
• Afﬁcher le nom des profs pour lesquels le nombre de mois entre la dernière promotion et le
date d'embauche est supérieur à 50
select nom from professeurs where months_between(der_prom,
date_entree) > 50 ;
Bottle
Bolenov
Tonilaclasse
Francesca
Pucette
Les fonctions ensemblistes
les fonctions ensemblistes
<set function specification> : := COUNT ( * ) | <general set
function> | ...
<general set function> : := <set function type> ( [ <set
quantifier> ] <value expression>99 )
<set function type> : := <computational operation>
<computational operation> : := AVG | MAX | MIN | SUM | EVERY |
ANY | SOME | COUNT
<set quantifier> : := DISTINCT | ALL
• Appelées aussi fonctions d'agrégation
fonction COUNT (exemples)
• Obtenir le nombre d'élèves inscrits en deuxième année
select count(*) from eleves where annee = 2 ;
5
• Les colonnes dont la valeur est NULL ne sont comptées qu'avec l'astérisque
create table test (id number);
insert into test (select rownum from all_objects where
rownum between 1 and 50);
insert into test (select null from all_objects where rownum
between 1 and 50);
select count(*) from test;
100
select count(id) from test;
50
Techniques Informatiques
107
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
fonction AVG (exemple)
• Obtenir le poids moyen des élèves de 1ère année
select avg(poids) from eleves where annee = 1 ;
48.4
fonction SUM (exemple)
• Obtenir le total des points de l'élève numéro 3
select sum(points) from resultats where num_eleve = 3 ;
65
fonctions ensemblistes (remarques)
• L'utilisation d'une des fonctions dans la clause de sélection empêche d'utiliser autre chose
que ces fonctions dans le reste de la clause
select num_eleve, num_cours, max(points) from resultats;
ORA-00937 : not a single-group group function
• Avec SUM et AVG l'argument doit être de type numérique. Avec les autres, le type peut être
caractère ou date
• Les valeurs NULL sont éliminées sauf pour COUNT(*)
• Documentation Oracle sur les fonctions d'agrégation et plus particulièrement COUNT, AVG,
MAX, MIN et SUM
la spéciﬁcation CAST
<cast specification> : := CAST ( <cast operand> AS <cast
target> )
<cast operand> : := <value expression>99 | <implicitly typed
value specification>
<cast target> : := <domain name> | <data type>
<implicitly typed value specification> : := <null
specification> | <empty specification>
<null specification> : := NULL
• SQL est fortement typé ñ besoin de pouvoir changer le type d'une expression
• Déjà utilisé dans le chapitre sur le DDL aux slides 72 et ??
• Oracle possède un certain nombre de règles de conversion et de fonctions de conversion
• Documentation Oracle sur la fonction CAST
l'expression CASE (1)
<case expression> : := <case abbreviation> | <case
specification>109
<case abbreviation> : := NULLIF ( <value expression>99 , <
value expression>99 )
| COALESCE ( <value expression>99 { , <value expression>
99 } )
Techniques Informatiques
108
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
• COALESCE renvoie la première expression non nulle de la liste
• NULLIF compare les deux expressions. Si égales, elle renvoie NULL sinon elle renvoie l'expression
1
• Documentation Oracle sur NULLIF et COALESCE
l'expression CASE (1, exemples)
• Si on a un facteur de réduction on l'utilise, sinon on prend le prix de base. S'il n'y a pas de
prix, on ﬁxe un prix standard de 100
SELECT id, reduction, prix_base, COALESCE(reduction*prix_base
, prix_base, 100) "Sale" FROM produit;
ID REDUCTION PRIX_BASE
Sale
-- --------- --------- ---------1
0.9
50
45
2
50
50
3
100
l'expression CASE (2)
<case specification> : := <simple case> | <searched case>
<simple case> : := CASE <case operand>
{ <simple when clause> }
[ <else clause> ]
END
<case operand> : := <value expression>99
<simple when clause> : := WHEN <when operand> THEN <result>
<when operand> : := <value expression>99
<result> : := <result expression> | NULL
<result expression> : := <value expression>99
<else clause> : := ELSE <result>
<searched case> : := CASE
{ <searched when clause> }
[ <else clause> ]
END
<searched when clause> : := WHEN <search condition> THEN <
result>
• Correspond au switch-case du C mais possède une valeur et est plus générale
• Avec Oracle, on utilise la fonction DECODE
l'expression CASE (2, exemples)
• Afﬁchage de résultats (TB, B, S, …) en toutes lettres
select cote from resultat;
TB
B
S
Sselect decode(cote, 'TB', 'Très bien', 'B', 'Bien', 'S', '
Satisfaisant', 'I', 'Insuffisant', 'M', 'Mauvais') from
resultat;
Très bien
Techniques Informatiques
109
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
Bien
Satisfaisant
(null)
les sous-requêtes
<subquery> : := ( <query expression> )
<query expression> : := [ <with clause> ] <query expression
body>
<query expression body> : := <non-join query expression>111 |
<joined table>112
<with clause> : := WITH [ RECURSIVE ] <with list>
<with list> : := <with list element> { , <with list element> }
<with list element> : := <query name>
[ ( <with column list> ) ]
AS ( <query expression> )
[ <search or cycle clause> ]
• Mécanisme très puissant (en terme d'expressivité) du SQL
• Oracle ne permet pas d'indiquer les éléments optionnels de la classe with list element
• La clause WITH permet d'assigner un nom à une requête et d'y faire référence en de multiples
endroits dans la requête principale
les sous-requêtes (exemple 1)
• Obtenir les nom et prénoms des élèves qui pratiquent du surf au niveau 1
select nom, prenom from eleves where num_eleve in (select
num_eleve from activites_pratiquees where niveau = 1 and nom
= 'surf')
NOM
PRENOM
------------------------- ------------------------Brisefer
Benoit
Spring
Jerry
Tsuno
Yoko
• Obtenir le nom des élèves qui jouent dans l'équipe Amc indus
select nom from eleves where num_eleve in
(select num_eleve from activites_pratiquees where (niveau,
nom) in
(select niveau, nom from activites where equipe = 'Amc indus
'));
Brisefer
Génial
Tsuno
Dubois
Walthéry
Danny
les sous-requêtes (exemple 2)
Sous-requêtes corrélatives.
Techniques Informatiques
110
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
• Obtenir les nom et prénoms des élèves qui pratiquent du surf au niveau 1
select nom, prenom from eleves e
where (1, 'Surf') in (select ap.niveau, ap.nom
from activites_pratiquees ap
where ap.num_eleve = e.num_eleve);
NOM
PRENOM
------------------------- ------------------------Brisefer
Benoit
Spring
Jerry
Tsuno
Yoko
• Obtenir le nom, le poids et l'année des élèves dont le poids est supérieur au poids moyen
des élèves étant dans la même année d'études
select nom, poids, annee from eleves e_ext
where poids > (select avg(poids) from eleves e_int
where e_int.annee = e_ext.annee);
NOM
POIDS ANNEE
------------------------- ----- ----Spring
78
2
Lebut
75
2
Lagaffe
61
1
Walthéry
59
1
Danny
82
2
les sous-requêtes (exemple 3)
Sous-requêtes corrélatives.
• Obtenir les nom et prénoms des élèves qui pratiquent du surf au niveau 1
select nom, prenom from eleves
where 0 < (select count (*) from activites_pratiquees ap
where ap.num_eleve = eleves.num_eleve
and niveau = 1
and nom = 'Surf');
NOM
PRENOM
------------------------- ------------------------Brisefer
Benoit
Spring
Jerry
Tsuno
Yoko
les opérateurs ensemblistes
<non-join query expression> : :=
<non-join query term>
| <query expression body> UNION [ ALL | DISTINCT ] [ <
corresponding spec> ] <query term>
| <query expression body> EXCEPT [ ALL | DISTINCT ] [ <
corresponding spec> ] <query term>
<non-join query term> : := <non-join query primary>
| <query term> INTERSECT [ ALL | DISTINCT ] [ <
corresponding spec> ] <query primary>
<non-join query primary> : := <simple table> | ( <non-join
query expression> )
<simple table> : := <query specification>92 | ...
Techniques Informatiques
111
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
• Déﬁnit les opérateurs ensemblistes
• Avec Oracle, l'opérateur EXCEPT s'appelle MINUS. La syntaxe est plus réduite pour Oracle
l'opérateur UNION
• UNION effectue l'union ensembliste (rien ou mot-clé DISTINCT) des tables générées par les
requêtes
• ALL indique que les doublons doivent être gardés
• Attention, les tables doivent êtres union-compatibles
• Obtenir pour chaque professeur son nom et sa spécialité. Si spécialité inconnue, on afﬁche
****
select nom, specialite from professeurs where specialite is
not null
union
select nom, '****' from professeurs where specialite is null;
Bolenov réseau
Bottle poésie
Francesca ****
Pastecnov sql
Pucette sql
Selector sql
Tonilaclasse poo
Vilplusplus poo
l'opérateur INTERSECT
• Obtenir les nom et prénom des élèves pesant moins de 45 kilos et inscrits en 1ère année
select nom, prenom from eleves where poids < 45
intersect
select nom, prenom from eleves where annee = 1 ;
Brisefer Benoit
Génial Olivier
la jointure
<joined table> : :=
<cross join>112
| <qualified join>113
| <natural join>114
| <union join>115
• Documentation Oracle sur les jointures
le produit cartésien
<cross join> : := <table reference>93
primary>93
CROSS JOIN <table
• Même chose qu'indiquer plusieurs tables dans la clause FROM. Cfr. slide 227
Techniques Informatiques
112
HEPL 2014–2015
create
where
create
where
select
2500
select
2500
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
table table_1 as (select rownum as id from all_objects
rownum between 1 and 50);
table table_2 as (select rownum as id from all_objects
rownum between 1 and 50);
count(*) from table_1 cross join table_2;
count(*) from table_1, table_2;
la jointure qualiﬁée
<qualified join> : := <table reference> [ <join type> ] JOIN <
table reference> <join specification>
<join type> : := INNER | <outer join type> [ OUTER ]
<outer join type> : := LEFT | RIGHT | FULL
<join specification> : := <join condition> | <named columns
join>
<join condition> : := ON <search condition>
<named columns join> : := USING ( <join column list> )
<join column list> : := <column name list>
les jointures internes
• Il s'agit des jointures les plus communes et de la jointure par défaut
• Jointure de deux tables ne contenant que les lignes qui satisfont la condition
• Le mot-clé INNER est facultatif
• Deux types de spéciﬁcations :
– Soit, on nomme les colonnes avec USING. Il s'agit d'une équijointure. Peut être exprimé
avec ON
– Soit, on indique la condition à satisfaire avec ON
jointure interne avec USING (exemple)
• Obtenir le nom et le prénom des élèves qui pratiquent du surf au niveau 1
select eleves.nom, eleves.prenom from eleves inner join
activites_pratiquees using (num_eleve) where niveau = 1 and
activites_pratiquees.nom = 'Surf' ;
Brisefer
Benoit
Spring
Jerry
Tsuno
Yoko
ou
select eleves.nom, eleves.prenom from eleves inner join
activites_pratiquees on eleves.num_eleve =
activites_pratiquees.num_eleve where niveau = 1 and
activites_pratiquees.nom = 'Surf' ;
ou
Techniques Informatiques
113
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
select eleves.nom, prenom from eleves, activites_pratiquees
where eleves.num_eleve = activites_pratiquees.num_eleve and
niveau= 1 and activites_pratiquees.nom = 'Surf' ;
la jointure naturelle
<natural join> : := <table reference> NATURAL [ <join type> ]
JOIN <table primary>
• La jointure naturelle est une équijointure sur toutes les colonnes de même nom
• Cette opération est très courante
la jointure naturelle (exemples)
• Obtenir le nom et le prénom des élèves qui pratiquent du surf au niveau 1. Ce problème peut
être solutionné par jointure naturelle après renommage
select nom, prenom from eleves natural inner join (select
num_eleve, nom as acti_nom, niveau from activites_pratiquees)
where niveau = 1 and acti_nom = 'Surf' ;
• Obtenir le nom des cours donnés par Bottle
with j1 as (select * from professeurs natural join charge)
select cours.nom from j1 join cours using (num_cours) where
j1.nom = 'Bottle' ;
Réseau
Sgbd
l'auto-jointure
• Jointure d'une table avec elle-même
• Il est nécessaire de qualiﬁer les tables explicitement
• Exemple : obtenir les paires de noms de professeurs qui ont la même spécialité
select x.nom, y.nom from professeurs x, professeurs y where x
.specialite = y.specialite and x.nom < y.nom;
Pucette
Selector
Pastecnov
Selector
Tonilaclasse
Vilplusplus
Pastecnov
Pucette
la jointure externe
• Le résultat contient les lignes de :
– La jointure interne sur les deux mêmes tables
– Les lignes de la première table (LEFT OUTER JOIN), de la seconde table (RIGHT OUTER
JOIN) ou des deux tables (FULL OUTER JOIN) qui n'ont pas de correspondances dans
l'autre table
Techniques Informatiques
114
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
la jointure externe (exemple, 1)
• Cfr. [4], pages 113 et 114
• Table des employés
select * from employes
NUM NOM
N_CONJ
--- ---------- -----1 Julie
1
2 Jean
3 Rik
4 Steve
2
• Table des conjoints
select
N_CONJ
-----1
2
* from conjoints
SEXE
---M
F
la jointure externe (exemple, 2)
• Jointure naturelle interne
select n_conj, num, nom from employes natural inner join
conjoints
N_CONJ NUM NOM
------ --- ---------1
1 Julie
2
4 Steve
• Jointure naturelle externe gauche
select n_conj, num, nom from employes natural left outer join
conjoints
N_CONJ NUM NOM
------ --- ---------1
1 Julie
2 Jean
3 Rik
2
4 Steve
la jointure union
• Ne donne que les lignes des deux tables qui ne possèdent pas de correspondance dans l'autre
table
• t1 UNION JOIN t2 est la même chose que
( t1 FULL OUTER JOIN t2 ) EXCEPT ( t1 INNER JOIN t2 )
• Inconnu d'Oracle
Techniques Informatiques
115
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
les quantiﬁcateurs
<quantified comparison predicate> : := <row value expression>
96 <comp op>96 <quantifier> <table subquery>93
<quantifier> : := <all> | <some>
<all> : := ALL
<some> : := SOME | ANY
• Correspond au quantiﬁcateur existentiel (D) et au quantiﬁcateur universel (@)
le quantiﬁcateur existentiel (ANY)
• Vrai si pour au moins une des valeurs de la sous-requêtes la comparaison est vraie. Faux
sinon
• Synonyme de SOME
• Obtenir le nom et le poids des élèves de 1ère année plus lourds qu'un élève quelconque de
2ème année
select nom, poids from eleves where annee = 1
and poids > any (select poids from eleves where annee = 2);
NOM
POIDS
------------------------- ----Lagaffe
61
• Obtenir le nom des élèves qui pratiquent du surf au niveau 1
select nom from eleves where num_eleve = any
(select num_eleve from activites_pratiquees where niveau = 1
and nom = 'Surf');
Brisefer
Spring
Tsuno
le quantiﬁcateur universel (ALL)
• Vrai si la comparaison est vraie pour toutes les valeurs de la sous-requete
• Ou si la sous-requête génère une table vide
• Obtenir le nom et le poids des élèves de 1ère année plus lourds que n'importe quel élève de
2ème année
select nom, poids from eleves where annee = 1
and poids > all (select poids from eleves where annee = 2);
0 rows selected
clause ORDER BY
<direct select statement : multiple rows> : := <query
expression> [ <order by clause> ]
<order by clause> : := ORDER BY <sort specification list>
<sort specification list> : := <sort specification> { , <sort
specification> }
Techniques Informatiques
116
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
<sort specification> : := <sort key> [ <collate clause> ] [ <
ordering specification> ]
<sort key> : := <value expression>99
<ordering specification> : := ASC | DESC
• Utilisé pour trier le résultat d'une requête
• Toutes valeurs NULLs sont identiques
• NULL est plus petit ou plus grand que n'importe quelle valeur4
clause ORDER BY (exemple 1)
• Obtenir la liste des élèves classés par année et par ordre alphabétique
select nom, prenom, annee
NOM
------------------------Brisefer
Génial
Lagaffe
Tsuno
Walthéry
Danny
Dubois
Jourdan
Lebut
Spring
from eleves order by annee, nom;
PRENOM
ANNEE
------------------------- ----Benoit
1
Olivier
1
Gaston
1
Yoko
1
Natacha
1
Buck
2
Robin
2
Gil
2
Marc
2
Jerry
2
ou
select nom, prenom, annee from eleves order by 3, 1 ;
clause ORDER BY (exemple 2)
• Afﬁcher en ordre décroissant les points de Tsuno obtenus dans chaque cours sur 100
select cours.nom, points * 5 from resultats r, eleves e,
cours c
where e.num_eleve = r.num_eleve
and r.num_cours = c.num_cours
and e.nom = 'Tsuno'
order by 2 desc;
NOM
POINTS*5
-------------------- ---------Sgbd
65
Analyse
65
Sgbd
32.5
Réseau
25
clause GROUP BY
<group by clause> : := GROUP BY <grouping specification>
<grouping specification> : :=
4 Dans
le tri effectué par ORDER BY, pas dans les comparaisons.
Techniques Informatiques
117
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
<grouping column reference>
| ...
<grouping column reference> : := <column reference> [ <collate
clause> ]
• Permet de grouper les lignes de table pour lesquelles les colonnes de groupement sont égales
en une seule ligne
• ñ chaque item de la select list doit fournir une seule valeur par groupe
clause GROUP BY (exemple)
• Obtenir pour chaque élève de 1ère année son nom et sa moyenne
select nom, avg(points) from eleves natural inner join
resultats
where annee = 1
group by eleves.nom;
NOM
AVG(POINTS)
------------------------- ----------Génial
9.5
Tsuno
9.375
Walthéry
15.875
Brisefer
13.375
Lagaffe
10.375
• Obtenir le maximum parmi les totaux de chaque élèves
select max(points)
from (select sum(points) as points
from resultats
group by num_eleve);
MAX(POINTS)
----------65
clause HAVING
<having clause> : := HAVING <search condition>94
• Permet de ﬁltrer les groupes obtenus par la clause GROUP BY
• HAVING est aux groupes ce que WHERE est aux lignes
clause HAVING (exemple)
• Obtenir la moyenne des points de chaque élève de 1ère année dont le total des points est
supérieur à 40
select nom, avg(points) from eleves natural inner join
resultats
where annee = 1
group by nom
having sum (points) > 40 ;
NOM
AVG(POINTS)
------------------------- -----------
Techniques Informatiques
118
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Walthéry
Brisefer
Lagaffe
Ludovic Kuty
15.875
13.375
10.375
10.3 INSERT
l'instruction INSERT
<insert statement> : := INSERT INTO <insertion target> <insert
columns and source>
<insertion target> : := <table name>
<insert columns and source> : := <from subquery> | <from
constructor> | ...
<from subquery> : :=
[ ( <insert column list> ) ]
...
<query expression>110
<from constructor> : :=
[ ( <insert column list> ) ]
...
<contextually typed table value constructor>
<contextually typed table value constructor> : :=
VALUES ( <value expression>99
{ , <value expression>99 } )
• Attention, la syntaxe a été volontairement simpliﬁée
• Documentation Oracle sur l'instruction INSERT
• Ne pas oublier le COMMIT
l'instruction INSERT (exemple)
create table a (num1 number primary key, num2 number default
20, num3 number default 30);
insert into a values (1, 1, 1);
insert into a (num1, num2) values (2, 2);
insert into a (num1) values (3);
insert into a (num1, num3) values (4, 4);
insert into a select rownum + 4,rownum + 4,rownum + 4
from all_objects
where rownum between 1 and 3 ;
select * from a ;
NUM1 NUM2 NUM3
---- ---- ---1
1
1
2
2
30
3
20
30
4
20
4
5
5
5
6
6
6
7
7
7
ROWNUM
Techniques Informatiques
119
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
• Attention, la pseudo-colonne ROWNUM renvoie un numéro pour chaque ligne sélectionnée
par une requête.
• La sélection se fait donc avant d'attribuer une valeur à ROWNUM.
• Par conséquent, la requête ci-dessous ne renvoie aucune ligne. En effet :
– La première ligne renvoyée a un ROWNUM de 1. La condition est fausse.
– La seconde ligne renvoyée est la première ligne de la requête. La condition est fausse.
– Et ainsi de suite jusqu'à la dernière ligne.
SELECT * FROM employees WHERE ROWNUM > 1 ;
10.4 UPDATE
l'instruction UPDATE
<update statement : searched> : :=
UPDATE <target table>
SET <set clause list>
[ WHERE <search condition>94 ]
<set clause list> : := <set clause> { , <set clause> }
<set clause> : :=
<update target> <equals operator> <update
source>
| ...
<update target> : := <column name> | ...
<update source> : := <value expression>99
| ...
• Permet de modiﬁer des données
• Attention, la syntaxe a été volontairement simpliﬁée
• Documentation Oracle sur l'instruction UPDATE
• Ne pas oublier le COMMIT
l'instruction UPDATE (exemple)
• Augmenter de 10% les points de Lagaffe dans le cours Sgbd de 2ème année
update resultats set points = points * 1.10
where num_eleve = (select num_eleve
from eleves
where nom = 'Lagaffe')
and num_cours = (select num_cours
from cours
where nom = 'Sgbd'
and annee = 2);
select points from resultats where ...
12.1
Techniques Informatiques
120
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Ludovic Kuty
10.5 DELETE
l'instruction DELETE
<delete statement : searched> : := DELETE FROM <target table> [
WHERE <search condition>94 ]
• Permet d'effacer des données
• Documentation Oracle sur l'instruction DELETE
• Ne pas oublier le COMMIT
l'instruction DELETE (exemple)
• Supprimez tous les résultats concernant les cours donnés par Pucette
delete from resultats
where num_cours
in (select num_cours
from charge natural inner join professeurs
where nom = 'Pucette');
40 rows deleted
Techniques Informatiques
121
HEPL 2014–2015
10. Langage de Manipulation de Données (LMD)
Techniques Informatiques
Ludovic Kuty
122
HEPL 2014–2015
Bibliographie
Ludovic Kuty
Bibliographie
[1] C. J. Date An Introduction to Database Systems. Addison-Wesley, 8ème édition, 2004. éditeur,
amazon.
[2] C. J. Date Database In Depth. O'Reilly, 1ère édition, 2005. éditeur, amazon.
[3] R. Ramakrishnan et J. Gehrke Database Management Systems. McGraw-Hill, 2ème édition,
2000. amazon.
[4] Pierre Delmal SQL2 -- SQL3, applications à Oracle. De Boeck, 3ème édition, 2001. éditeur,
amazon.
[5] Pierre Delmal SQL2, de la théorie à la pratique. De Boeck, 2ème édition, 1995.
[6] Batini, Ceri, Navathe Conceptual Database Design. Addison-Wesley, 1ère édition, 1991. éditeur,
amazon.
[7] S. Bagui et R. Earp Database Design Using Entity-Relationship Diagrams. Auerbach Publications,
1ère édition, 2003. amazon.
[8] Jim Melton et Alan R. Simon SQL : 1999 - Understanding Relational Language Components.
Morgan Kaufmann, 2ème édition, 2001. éditeur, amazon.
[9] Thomas Kyte Expert Oracle Database Architecture : 9i and 10g Programming Techniques and Solutions. APress, 1ère édition, 2005. éditeur, amazon.
[10] Sam R. Alapati Expert Oracle Database 10g Administration. APress, 1ère édition, 2005. éditeur,
amazon.
[11] Osmar R. Zaïane CMPT 354 Database Systems and Structures. Simon Fraser University, 1998.
URL.
Techniques Informatiques
123

Catégorie Technique Introduction aux Bases de Données

Documents connexes

Produits

Soutien

Catégorie Technique Introduction aux Bases de Données

Documents connexes

Ajouter ce document à la (aux) collections

Ajouter ce document à enregistré

Suggérez-nous comment améliorer StudyLib