Cours informatique en SGBDR
tutoriel informatique complet pour apprendre l'SGBDR
Chap. I -Introduction
- Base de données :
– collection d'informations ou de données qui existent sur une longue période de temps [UW97] et qui décrivent les activités d'une ou plusieurs organisations [RG00]
– ensemble de données modélisant les objets d'une partie du monde réel et servant de support à une application informatique
[Gar99]
- SGBD : Systèmes de Gestion de Bases de Données
(DataBase Management Systems - DBMS) ensemble de logiciels systèmes permettant aux utilisateurs d'insérer, de modifier, et de rechercher efficacement des données spécifiques dans une grande masse d'informations (pouvant atteindre plusieurs milliards d'octets) partagée par de multiples utilisateurs [Gar99]
SGBD
Principaux composants :
– Système de gestion de fichiers
– Gestionnaire de requêtes
– Gestionnaire de transactions
Principales fonctionnalités :
– Contrôle de la redondance d’information
– Partage des données
– Gestion des autorisations d’accès
– Vérifications des contraintes d’intégrité
– Sécurité et reprise sur panne
Abstraction des données
- Niveau interne ou physique :
– plus bas niveau
– indique comment (avec quelles structures de données) sont stockées physiquement les données
- Niveau logique ou conceptuel :
– décrit par un schéma conceptuel
– indique quelles sont les données stockées et quelles sont leurs relations indépendamment de l’implantation physique
- Niveau externe ou vue :
– propre à chaque utilisateur
– décrit par un ou plusieurs schémas externes
Instances et schéma
- Instances de base de données :
– données de la base à un instant donné
– manipulées par un langage de manipulation de données (DML -Data Manipulation Language)
- Schéma de base de données :
– description de la structure des données
– ensemble de définitions exprimées en langage de description de données (DDL – Data Definition Language)
Petit historique
- 1960 : systèmes de gestion de fichiers
- 1970 : début des SGBD réseaux et hiérarchiques proches des systèmes de gestion de fichiers pas d’interrogation sans savoir où est l'information recherchée ("navigation") et sans écrire de programmes
- 1970 : papier fondateur de CODD sur la théorie des relations fondement de la théorie des bases de données relationnelles
INGRES à Berkeley - langage QUEL
System R IBM à San Jose - langages SEQUEL et QBE
- 1980 : Apparition des SGBD relationnels sur le marché (Oracle, Ingres,
Informix, Sybase, DB2 ...)
- 1990 : début des SBGD orientés objet (Gemstone, O2, Orion, Objectstore,
Versant, Matisse...).
- Aujourd’hui : relationnel-objet, semi-structuré, multimédia ...
Chap II -Modélisation
Méthodologie à suivre pour modéliser un problème
- Déterminer les entités/classes et attributs :
– entité/instance de classe = objet décrit par de l’information
– objet caractérisé uniquement par un identifiant = attribut
– attribut multi-valué ou avec une association 1:N = entité ou instance
– attacher les attributs aux ensemble d’entités/classes qu'ils décrivent le plus directement
– éviter au maximum les identificateurs composites
- Identifier les généralisations-spécialisations/héritage
- Définir les associations
– éliminer les associations redondantes
– éviter les associations n-aires
...
Dépendances fonctionnelles
Ne pas oublier de définir les DF :
Accidente ( Auto_ID, Nom, Prénom, Adresse,
Immatriculation, Marque, Type, Puissance, Année )
Auto_ID → Nom, Prénom, Adresse
Immatriculation → Marque, Type, Puissance, Année
Type → Marque
Auto_ID → Immatriculation et Immatriculation → Auto_ID
Voiture ( Immatriculation, Marque, Puissance, Type, Année,
Auto_ID )
Dépendances fonctionnelles
Ne pas oublier de définir les DF :
Accidente ( Auto_ID, Nom, Prénom, Adresse,
Immatriculation, Marque, Type, Puissance, Année )
Auto_ID → Nom, Prénom, Adresse
Immatriculation → Marque, Type, Puissance, Année
Type → Marque
Auto_ID → Immatriculation et Immatriculation → Auto_ID
Voiture ( Immatriculation, Marque, Puissance, Type, Année,
Auto_ID )
Immatriculation → Auto_ID
+ les Dépendances fonctionnelles de Voiture
Dépendances fonctionnelles
Ne pas oublier de définir les DF :
Accidente ( Auto_ID, Nom, Prénom, Adresse,
Immatriculation, Marque, Type, Puissance, Année )
Auto_ID → Nom, Prénom, Adresse
Immatriculation → Marque, Type, Puissance, Année
Type → Marque
Auto_ID → Immatriculation et Immatriculation → Auto_ID Voiture ( Immatriculation, Marque, Puissance, Type, Année, Auto_ID )
Immatriculation → Auto_ID
+ les Dépendances fonctionnelles de Voiture
Dépendances fonctionnelles
Ne pas oublier de définir les DF :
Accidente ( Auto_ID, Nom, Prénom, Adresse,
Immatriculation, Marque, Type, Puissance, Année )
Auto_ID → Nom, Prénom, Adresse
Immatriculation → Marque, Type, Puissance, Année
Type → Marque
Auto_ID → Immatriculation et Immatriculation → Auto_ID Voiture ( Immatriculation, Marque, Puissance, Type, Année, Auto_ID )
Immatriculation → Auto_ID
+ les Dépendances fonctionnelles de Voiture
Location ( Auto_ID, Immatriculation, Date )
Dépendances fonctionnelles
Ne pas oublier de définir les DF :
Accidente ( Auto_ID, Nom, Prénom, Adresse,
Immatriculation, Marque, Type, Puissance, Année )
Auto_ID → Nom, Prénom, Adresse
Immatriculation → Marque, Type, Puissance, Année
Type → Marque
Auto_ID → Immatriculation et Immatriculation → Auto_ID
Voiture ( Immatriculation, Marque, Puissance, Type, Année,
Auto_ID )
Immatriculation → Auto_ID
+ les Dépendances fonctionnelles de Voiture
Location ( Auto_ID, Immatriculation, Date )
Pas de dépendance non triviale
- Unicité des clés
– ensemble minimal d'attributs dont la connaissance des valeurs permet d'identifier un nuplet unique de la relation considérée
– R a pour clé K si : ∀t1, t2 nuplets d’une instance de R
t1.K ≠ t2.K
- Contraintes de référence
– contrainte référentielle : contrainte d'intégrité portant sur une relation R qui consiste à imposer que la valeur d'un groupe d'attributs apparaissent comme valeur de clé dans une autre relation
– clé étrangère : un groupe d'attributs qui doit apparaître comme clé dans une autre relation
Accident ( Auto_ID, Nom, Prénom, Adresse,
Immatriculation, Marque, Type, Puissance, Année )
Accident ( Auto_ID, Nom, Prénom, Adresse,
Immatriculation, Marque, Type, Puissance, Année )
Clés primaires possibles: Auto_ID ou Immatriculation
Surclé : (Auto_ID, Immatriculation) + d’autres attributs
Accident ( Auto_ID, Nom, Prénom, Adresse, Immatriculation, Marque, Type, Puissance, Année )
Clés primaires possibles: Auto_ID ou Immatriculation
Surclé : (Auto_ID, Immatriculation) + d’autres attributs
Voiture ( Immatriculation, Marque, Puissance, Type, Année,
Auto_ID )
Clé /Clé minimale /Surclé
Accident ( Auto_ID, Nom, Prénom, Adresse,
Immatriculation, Marque, Type, Puissance, Année )
Clés primaires possibles: Auto_ID ou Immatriculation
Surclé : (Auto_ID, Immatriculation) + d’autres attributs
Voiture ( Immatriculation, Marque, Puissance, Type, Année,
Auto_ID )
Clé primaire: Immatriculation
Surclé : (Immatriculation, Marque, Puissance, Type, Année,Auto_ID)
Clé /Clé minimale /Surclé
Accident ( Auto_ID, Nom, Prénom, Adresse,
Immatriculation, Marque, Type, Puissance, Année )
Clés primaires possibles: Auto_ID ou Immatriculation
Surclé : (Auto_ID, Immatriculation) + d’autres attributs
Voiture ( Immatriculation, Marque, Puissance, Type, Année,
Auto_ID )
Clé primaire: Immatriculation
Surclé : (Immatriculation, Marque, Puissance, Type, Année,Auto_ID)
Location ( Auto_ID, Immatriculation, Date )
relationnel Clé /Clé minimale /Surclé
Accident ( Auto_ID, Nom, Prénom, Adresse,
Immatriculation, Marque, Type, Puissance, Année )
Clés primaires possibles: Auto_ID ou Immatriculation
Surclé : (Auto_ID, Immatriculation) + d’autres attributs
Voiture ( Immatriculation, Marque, Puissance, Type, Année,
Auto_ID )
Clé primaire: Immatriculation
Surclé : (Immatriculation, Marque, Puissance, Type, Année,Auto_ID)
Location ( Auto_ID, Immatriculation, Date )
Clé primaire: (Auto_ID, Immatriculation, Date)
Intégrité structurelle
- Valeur nulle
– valeur conventionnelle introduite dans une relation pour représenter une information inconnue ou inapplicable
– tout attribut peut prendre une valeur nulle excepté les attributs de la clé primaire (contrainte d’entité)
- Contraintes de domaine contrainte d'intégrité qui impose qu'une colonne d'une relation doit comporter des valeurs vérifiant une assertion logique
- Algèbre relationnelle
a inspiré le langage SQL
- Calcul relationnel à variable nuplet
a inspiré le langage QUEL du SGBD Ingres
- Calcul relationnel à variable domaine
a inspiré le langage QBE (Query By Example) d’IBM
- SQL (Structured Query Langage)
Opérations unaires :
- sélection des nuplets satisfaisant un certain prédicat
Etudiant(Etudiant_ID, Nom, Prénom, Rue, Ville, Code-Postal,
Téléphone, Fax, Email, NumAnnées)
σ(Ville=‘ Paris ’) (Etudiant)
σ(Ville=‘ Paris ’) ∧ (NumAnnées ≥ 2) (Etudiant)
- projection : élimination de certains attributs d’une relation
ΠNom,Prénom(Etudiant)
ΠNom,Prénom( ó(Ville=‘ Paris ’) (Etudiant) )
Exemples de résultats d’opérations unaires
Relation Enseignant
Résultat de la sélection ó(grade=‘ MCF ’) (Enseignant) :
Π Ensei nant) : Résultat de la requête
Nom,Prénom( g
Opérations binaires
- Union : rassemblement des nuplets de 2 relations compatibles
Enseignant( Enseignant_ID, Département_ID, Nom, Prénom, Grade,
Téléphone, Fax, Email )
rINom,Prénom(Etudiant) lJ rINom,Prénom(Enseignant)
- Différence : des nuplets de 2 relations compatibles
rINom,Prénom(Enseignant) - rINom,Prénom(Etudiant)
- Produit cartésien : combinaison des nuplets de 2 relations
Département(Département_ID, Nom_Département)
Produit cartésien de Enseignant x Departement a pour schéma :
(Enseignant_ID, Enseignant.Département_ID, Nom, Prénom, Grade,
Téléphone, Fax, Email, Département.Département_ID, Nom_Département)
Exemple d’union et de différence
riNom,Prénom(Enseignant) - riNom,Prénom(Etudiant) :
Produit cartésien
NSS Nom Prénom Grade Dept
12345 Manouvrier Maude MCF 1
45678 Toto Titi Prof 2
La relation Enseignant
Dept ID Nom Dept
1 Info
2 Math
La relation Département
Produit cartésien
NSS Nom Prénom Grade Dept
12345 Manouvrier Maude MCF 1
45678 Toto Titi Prof 2
La relation Enseignant
Dept ID Nom Dept
1 Info
2 Math
La relation Département
NSS Nom Prénom Grade Dept Dept_ID Nom_Dept
12345 Manouvrier Maude MCF 1 1 Info
45678 Toto Titi Prof 2 1 Info
12345 Manouvrier Maude MCF 1 2 Math
45678 Toto Titi Prof 2 2 Math
La relation Département × Enseignant
- Renommage :
ΠA’,B’, ...(r A→A’, B →B’, ... )
- Intersection :
r ∩ s = r - (r - s)
- Théta-jointure :
r ∞Θ s = óÈ (r × s)
- Jointure naturelle : r(R) et s(S) avec R ∩ S = {A1,A2, ..., An} r ∞ s = ÐR ∪ S (ó(r.A1=s.A1) ∧ (r.A2=s.A2) ∧ ... ∧ (r.An=s.An) (r × s))
Exemple de renommage et d’intersection
riLast_Name,First_Name(Enseignant Nom →Last Name, Prénom →First_Name ) :
riNom,Prénom(Enseignant) n riNom,Prénom(Etudiant) :
La relation Enseignant:
Enseignement ∞Departement_ID Departement :
Requête qui contient le terme «pour tous »
Soient r(R) et s(S) avec S ç R
la relation r = s a pour schéma R - S
un nuplet t appartient à r = s si :
c t E IIR - S (r)
d b ts nuplet de s, 3 tr dans r qui satisfait :
Š tr(S) = ts(S)
Š tr(R-S) = t
r = s = IIR - S (r) - IIR - S [ ( IIR - S (r) x s ) - IIR - S, S (r) ]
La relation Enseignement:
Chap. IV – Algèbre relationnelle Division
La relation Enseignement:
Étudiant ID, Enseignement ID, Departement ID (Inscri l~tion = Enseignement ID, Departement_ID (Enseignement) :
Chap. IV – Algèbre relationnelle Contraintes et DF
- Expressions des contraintes d’intégrité référentielle :
11Département_ID(Enseignant) Ç 11Département_ID(Département)
11Département_ID(Enseignant) - 11Département_ID(Département) = 0
- Expressions des dépendances fonctionnelles :
X→Y <=> `dr et `dt1, t2 E r on a :
11x(t1) = 11x(t2) => 11Y(t1) = 11Y(t2)
- Projection généralisée :
ajout d’expressions arithmétiques dans une projection
nNom_Client, (Crédit - Débit) (Compte_en_Banque )
- Jointure externe (outer-join) :
– jointure externe à gauche : ]oo
– jointure externe à droite : oo[
– jointure externe : ]oo[
R ]oo S => R oo S et conservation des attributs des nuplets de R qui ne joignent avec aucun nuplet de S (les valeurs des attributs de S sont mises à NULL)
…
Fonction d’agrégation
- Somme des places disponibles dans l’Université
SumCapacité(Salle )
- Nombre moyen de places disponibles dans les salles de l’Université
AvgCapacité (Salle)
- Nombre d’étudiants à l’Université
CountEtudiant ID (Etudiant)
- Capacité de la plus petite salle
MinCapacité(Salle)
- Nombre d’enseignants par départements :
Nom_Départementvio CountEnseignant_ID( Enseignant ∞ Département)
Mise à jour de la base
- Insertion
Salle ← Salle ∪ {(« B », « 038 », 15)}
- Suppression
Salle ← Salle - óSalle ≤ 10 (Salle)
- Mise à jour : utilsation de la projection généralisée
r ← ΠEtudiant_ID [ó(Nom=‘ Dupont ’ )∧ (Prénom=‘ Jacques ’) (Etudiant)]
Etudiant ← σ(Etudiant.Etudiant_ID <> r.Etudiant_ID) (Etudiant)
Mise à jour du ÐEtudiant_ID, Nom, Prénom, Rue, Ville, Code-Postal, téléphone Téléphone ← « 45 12 45 86 », Fax, Email, NumAnnées
[ó(Etudiant.Etudiant_ID =r.Etudiant_ID) (Etudiant)
Vue
Table virtuelle dont le schéma et les instances sont dérivés de la base réelle par une requête et qui est utilisée pour :
- Cacher certaines informations à un groupe d’utilisateurs
- Faciliter l’accès à certaines données
create view nom_vue as < requête >
Exemple :
create view Info Non Confidentielle Etudiant
_
as Π Etudiant_ID, Nom, Prénom, Email (Etudiant)
Structured Query Language
- SQL2 : standard adopté en 1992
- SQL3 : extension de SQL2 avec "gestion" d’objets SQL :
- Langage de Manipulation de Données (DML) : interroger et modifier les données de la base
- Langage de Définition de Données (DDL) : définir le schéma de la base de données
- Langage de contrôle d’accès aux données
n SQL2 - Application à Oracle, Access et RDB
Pierre DELMAL, 2ème Edition, De Boeck Université,
1998 (BU: 005.74 SQL)
n SQL Pour Oracle (avec exercices corrigés)
Christian Soutou, Eyrolles, 2005
n Initiation à SQL (cours et exercices corrigés)
Philip J. Pratt, Eyrolles,2001
n Oracle PL/SQL - Précis & concis
Steven Feuerstein, Bill Pribyl et Chip Dawes, O ’Reilly,
2000
DML
SELECT [DISTINCT] *
FROM table_1 [synonyme_1], table_2 [synonyme_1], ...
[WHERE prédicat_1
AND [ou OR] prédicat_2 ...]
SELECT [DISTINCT] exp_1 [AS nom_1], exp_2 ...
FROM table_1 [synonyme_1], table_2 [synonyme_1], ...
[WHERE prédicat_1
AND [ou OR] prédicat_2 ...]
SELECT Intitulé,
(NbSeances*3) AS NbHeures
FROM Cours
WHERE (NbSeances*3)
BETWEEN 24 AND 27 ;
SELECT Nom, Prénom
FROM Enseignant
WHERE Département_ID IN
(‘ INFO ’, ‘ MATH ’, ‘ ECO ’)
Prédicats du WHERE de la forme :
exp1 = exp2 exp op ANY (SELECT ...)
exp1 != exp2 exp op ALL (SELECT ...)
exp1 > exp2 avec op tel que =, !=, <, > ...
exp1 < exp2
exp1 <= exp2 exp IN (SELECT ...)
exp1 >= exp2 exp NOT IN (SELECT ...)
exp1 BETWEEN exp2 AND exp3
exp1 LIKE exp2
exp1 IN (exp2, exp3, ...)
exp1 NOT IN (exp2, exp3, ...)
exp1 IS NULL
exp1 IS NOT NULL
Clause EXISTS :
- Retourne VRAI si au moins un nuplet est renvoyé par la requête
- FAUX si aucun nuplet n’est retourné.
- La valeur NULL n’a aucun effet sur le booléen résultat
SELECT Nom, Prénom
FROM Enseignant E
WHERE NOT EXISTS
( SELECT *
FROM Reservation_Salle S
WHERE S.Enseignant_ID = E.Enseignant_ID
);
Fonctions de groupe:
COUNT, MIN, MAX, AVG, SUM, ORDER BY, GROUP BY
SELECT COUNT(*)
FROM Etudiant ;
SELECT AVG(Capacité), SUM(Capacité)
FROM Salle ;
SELECT Département_ID, Nom, Prénom
FROM Enseignant
ORDER BY Département_ID DESC, Nom, Prénom ;
SELECT Département_ID, COUNT(*)
FROM Réservation_Salle
GROUP BY Département_ID HAVING COUNT(*) >=4 ;
Jointure :
SELECT Nom, Prénom, Nom_Département
FROM Enseignant E, Département D
WHERE E.Département_ID = D.Département_ID ;
Jointure externe : sous Oracle
SELECT Nom, Prénom, Nom_Département
FROM Enseignant E, Département D
WHERE E.Département_ID = D.Département_ID (+);
S’il existe des enseignants attaché à aucun département, la valeur de
Département_ID sera NULL.
En SQL2 : [RIGHT | LEFT | FULL] OUTER JOIN
Opérateurs ensemblistes : sous Oracle
SELECT Nom,Prénom FROM Enseignant WHERE Département_ID = ‘ INFO ’ INTERSECT
SELECT Nom,Prénom FROM Enseignant WHERE Département_ID = ‘ MATH ’
SELECT Nom,Prénom FROM Enseignant WHERE Département_ID = ‘ INFO ’ UNION
SELECT Nom,Prénom FROM Enseignant WHERE Département_ID = ‘ MATH ’ ORDER BY Nom,Prénom
SELECT Nom,Prénom FROM Enseignant WHERE Département_ID = ‘ INFO ’ MINUS
SELECT Nom,Prénom FROM Enseignant WHERE Département_ID = ‘ MATH ’
MINUS = EXCEPT en standard SQL2
Division :
Livre(ISBN, Titre, Editeur)
Emprunt(EmpruntID,ISBN, DateEmprunt,EtudiantID) Etudiant(EtudiantID,Nom, Prenom)
« Quels livres ont été empruntés par tous les étudiants? »
{t.Titre / Livre(t) n [ V u Etudiant (u)
(∃ v Emprunt(v) n
(v.Etudiant_ID=u.Etudiant_ID) n (v.ISBN=t.ISBN )
)
]
}
Division :
Livre(ISBN, Titre, Editeur)
Emprunt(EmpruntID,ISBN, DateEmprunt,EtudiantID) Etudiant(EtudiantID,Nom, Prenom)
« Quels livres ont été empruntés par tous les étudiants? »
{t.Titre / Livre(t) n [ V u Etudiant (u)
(∃ v Emprunt(v) n
(v.Etudiant_ID=u.Etudiant_ID) n (v.ISBN=t.ISBN )
)
] Il n’y a pas de mot-clé
} "quel que soit " en SQL2
Division :
Livre(ISBN, Titre, Editeur)
Emprunt(EmpruntID,ISBN, DateEmprunt,EtudiantID) Etudiant(EtudiantID,Nom, Prenom)
« Quels livres ont été empruntés par tous les étudiants? »
{t.Titre / Livre(t) ∧ ¬ [ ∃ u Etudiant (u)
{t.Titre / Livre(t) ∧ ∀ u Etudiant (u
¬ (∃ v runt(v) ∧
(∃ v Emprunt(v) ∧
(v.Etudiant_ID=u.Etudiant_ID) ∧ (v.ISBN=t.ISBN ) (v.EtudiantID=u.EtudiantID) ∧ (v.ISBN=t.ISB
)
] Il n’y a pas de mot-clé
} "quel que soit " en SQL2
Division :
Livre(ISBN, Titre, Editeur)
Emprunt(EmpruntID,ISBN, DateEmprunt,EtudiantID) Etudiant(EtudiantID,Nom, Prenom)
« Quels livres ont été empruntés par tous les étudiants? »
{t.Titre / Livre(t) ∧ ¬ [ ∃ u Etudiant (u)
{t.Titre / Livre(t) ∧ ∀ u Etudiant (u
¬ (∃ v runt(v) ∧
(∃ v Emprunt(v) ∧
(v.Etudiant_ID=u.Etudiant_ID) ∧ (v.ISBN=t.ISBN ) (v.EtudiantID=u.EtudiantID) ∧ (v.ISBN=t.ISB
)
] Il n’y a pas de mot-clé
} "quel que soit " en SQL2
SELECT t.Titre FROM Livre t WHERE NOT EXISTS
( SELECT * FROM Etudiant u WHERE NOT EXISTS ( SELECT * FROM Emprunt v
WHERE u.EtudiantID=v.EtudiantID AND v.ISBN=t.ISBN
)
) ;
- Insertion
INSERT INTO table(col1, col2, ... coln)
VALUES (val1, val2, ... valn)
INSERT INTO table(col1, col2, ... coln)
SELECT Sous Oracle
- Suppression
DELETE FROM table
WHERE prédicat
- Mise à jour
UPDATE table
SET col1 = exp1, col2 = exp2 WHERE prédicat
- Transactions : COMMIT, ROLLBACK [TO], SAVE POINT
CREATE TABLE table (col1 type 1 [NOT NULL] ,
col2 type2 [NOT NULL] ...
)
Contraintes :
CONSTRAINT nom_contrainte
PRIMARY KEY (liste attributs clé primaire)
| NOT NULL immédiatement après la déclaration de l’attribut
| CHECK (condition) après la déclaration de l’attribut
| UNIQUE après la déclaration de l’attribut
| FOREIGN KEY (clé étrangère)
REFERENCES nom_table (liste-colonne)
CREATE TABLE table
AS SELECT ...
CREATE TABLE Enseignant (
Enseignant_ID integer,
Departement_ID integer NOT NULL,
Nom varchar(25) NOT NULL,
Prenom varchar(25) NOT NULL,
Grade varchar(25)
CONSTRAINT CK_Enseignant_Grade
CHECK (Grade IN ('Vacataire', 'Moniteur','ATER', 'MCF', 'PROF')),
Telephone varchar(10) DEFAULT NULL,
Fax varchar(10) DEFAULT NULL,
Email varchar(100) DEFAULT NULL,
CONSTRAINT PK_Enseignant PRIMARY KEY (Enseignant_ID), CONSTRAINT "FK_Enseignant_Departement_ID" FOREIGN KEY (Departement_ID)
REFERENCES Departement (Departement_ID)
ON UPDATE RESTRICT ON DELETE RESTRICT
);
Maude Manouvrier - Univ. Paris Dauphine
CREATE TABLE Reservation (
Reservation_ID integer,
Batiment varchar(1) NOT NULL,
Numero_Salle varchar(10) NOT NULL, Enseignement_ID integer NOT NULL, Departement_ID integer NOT NULL, Enseignant_ID integer NOT NULL,
Date_Resa date NOT NULL DEFAULT CURRENT_DATE,
Heure_ Debut time NOT NULL DEFAULT CURRENT_ TIME,
Heure_ Fin time NOT NULL DEFAULT '23:00:00',
Nombre _Heures integer NOT NULL,
CONSTRAINT PK_Reservation PRIMARY KEY (Reservation_ID),
CONSTRAINT "FK_Reservation_Salle" FOREIGN KEY (Batiment,Numero_Salle) REFERENCES Salle (Batiment,Numero_Salle) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT "FK_Reservation_Enseignement" FOREIGN KEY (Enseignement_ID,Departement_ID) REFERENCES Enseignement (Enseignement_ID,Departement_ID) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT "FK_Reservation_Enseignant" FOREIGN KEY (Enseignant_ID) REFERENCES Enseignant (Enseignant_ID) ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT CK_Reservation_Nombre_Heures CHECK (Nombre_Heures >=1),
CONSTRAINT CK_Reservation_HeureDebFin CHECK (Heure_Debut < Heure_Fin)
);
CREATE ASSERTION <nom contrainte>
[ {BEFORE COMMIT |
AFTER {INSERT | DELETE | UPDATE[OF (Attributs)]} ON
<Relation>} ...]
CHECK <Condition>
[FOR [EACH ROW OF] <Relation> ]
CREATE ASSERTION CA_Place_Université
BEFORE COMMIT
CHECK( (SELECT SUM(Capacité) FROM Salle)
>= (SELECT COUNT(*) FROM Etudiant)
)
CREATE [OR REPLACE] TRIGGER nom {BEFORE | AFTER}
événement_déclencheur ON nom_table
[FOR EACH ROW]
[WHEN (condition) ]
bloc PL/SQL sous Oracle
| inst_de_suppr | inst_de_modif | instr_d_ajout | ERROR en SQL2
événement_déclencheur = INSERT, UPDATE, DELETE
- Déclencheur de niveau instruction : pas de clause FOR EACH ROW
- Déclencheur de niveau ligne : variables liens :new et :old – INSERT : valeurs à insérer dans :new.nom_colonne
– UPDATE : valeur originale dans :old.nom_colonne, nouvelle valeur dans :new.nom_colonne
– DELETE : valeur en cours de suppression :old.nom_colonne 74
CREATE OR REPLACE TRIGGER Enseignant_Actif
BEFORE DELETE ON Enseignant
FOR EACH ROW
declare
counter number;
begin
SELECT count(*) INTO counter
FROM Enseignements
WHERE Enseignant_ID = :old.Enseignant_ID;
if counter > 0 then
raise_application_error (-20800, ’Enseignant actif ne
pouvant être supprimé');
end if;
end;
CREATE OR REPLACE TRIGGER UPD_salaire personnel
BEFORE UPDATE salaire ON Personnel
FOR EACH ROW
WHEN (:old.salaire > :new.salaire)
declare
salaire diminution EXCEPTION;
_
Begin
raise salaire_diminution ;
when salaire_diminution then
raise_application_error(-20001, ’Le salaire ne peut pas
diminuer ’)
end;
Sous PostgreSQL :
CREATE OR REPLACE FUNCTION GetSalleCapaciteSuperieurA(int)
RETURNS SETOF Salle
AS '
SELECT * FROM Salle WHERE Capacite > $1;
LANGUAGE SQL;
SELECT * FROM GetSalleCapaciteSuperieurA(300) ;
CREATE OR REPLACE FUNCTION FunctionTriggerReservation()
RETURNS trigger AS
' DECLARE
resa Reservation.Reservation ID%TYPE;
_
BEGIN
SELECT INTO resa Reservation ID
_
FROM Reservation
WHERE ...
IF FOUND THEN RAISE EXCEPTION ''Réservation impossible, sale occupée à la date et aux horaires demandés'';
ELSE RETURN NEW;
END IF;
END;'
LANGUAGE 'plpgsql';
Sous PostgreSQL :
CREATE TRIGGER InsertionReservation
BEFORE INSERT ON Reservation
FOR EACH ROW
EXECUTE PROCEDURE
FunctionTriggerReservation();
ALTER TABLE table
ADD (col1 type1, col2 type2 ...)
| MODIFY (col1 type1, col2 type2 ...)
| DROP PRIMARY KEY
| DROP CONSTRAINT nom_contrainte
DROP TABLE table
CREATE VIEW vue (col1, col2)
AS SELECT ...
DROP VIEW vue
CREATE [UNIQUE] INDEX nom_index ON table (col1,col ...)
Utilisation de commandes SQL à l’intérieur d’un langage hôte :
- Commandes SQL remplacée par des appel de fonctions du langage hôte par le précompilateur.
- Commandes SQL reconnues par EXEC SQL
/* déclaration de variables hôtes */
EXEC SQL BEGIN DECLARE SECTION
char d name[20];
_
char d_ id;
EXEC SQL END DECLARE SECTION
...
EXEC SQL INSERT INTO Department
VALUES (:d_id, :d_name);
Gestion des erreurs :
EXEC INCLUDE SQLCA;
...
EXEC SQL WHENEVER SQLERROR GOTO erreur
...
erreur :
printf(̏erreur : les transactions en cours vont être annulées'\n");
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
Gestion de curseur :
/* Déclaration d'un curseur pour manipuler la table Department */
EXEC SQL DECLARE c1 CURSOR FOR
SELECT * FROM Department ;
/* Ouverture du curseur */
EXEC SQL OPEN c1;
/* Lecture de la première ligne de la table */
EXEC SQL FETCH c1 INTO :d_id, :d_name ;
printf(``Nom du département %s, identifiant :
%s\n'',d name, d id);
_ _
/* fermeture du curseur */
EXEC SQL CLOSE c1;
Chap. VI - SQL Middleware d’accès aux bases de données
Open DataBase Connectivity (ODBC)
- Middleware propriétaire (Windows)
- Architecture logicielle définissant une interface standard d’accès aux SQBD
- A chaque SGBD correspond un pilote (driver)
Þ un même programme peut interroger différentes bases de données dans différents SGBD
Java DataBase Connectivity (JDBC)
ODBC
- Appel des fonctions de l ’API ODBC :
Þ lien entre l’application et le gestionnaire de pilotes
- Gestionnaire de pilotes = DLL qui charge les pilotes associés à chaque source de données (BD + SGBD)
- Pilote = DLL qui contient les appels ODBC et traduit les requêtes en requêtes propres au SGBD
Chap. VI - SQL ODBC
Ilal 1—IAL 1 1C) e ri lur
1
Ilal 1—A 11131113:1=101 n n e.Qt
- Q1
.101 1_!= (3 n n e.it
1
Tria I te. nie nt cie... reique.te
ir
1-- .aiL_Colic3ain r-rot
L.Frie.e4C 4:3 n n eiat
1
11:3L.Fre.e.Eniur
ODBC
Exemple de programme en C sous Visual C++ :
#include <stdio.h> #include <conio.h>
#include <afxdb.h> // MFC ODBC database classes
char *cBASE ; /* Nom de la source de données */
char *cLOGIN ; /* Login utilisateur */
char *cPASSWD ; /* Mot de passe utilisateur */
void main()
{
HENV d_env; /*
HDBC d_ connex; /*
HSTMT curseur; /*
RETCODE retcode; /*
UCHAR ucLastName[20],ucCity[20]; SDWORD ceLastName,ceCity;
char *cREQUETESQL; /* Variable recevant une requête SQL */
/* Saisie du nom de la source de données */ cBASE=(char*)malloc(20);
printf("Nom de la base de donnees :"); scanf("%s",cBASE);
/* Saisie du login */
cLOGIN=(char*)malloc(20); printf("Login :"); scanf("%s",cLOGIN);
/* Saisie du password */
cPASSWD=(char*)malloc(20); printf("Mot de passe : ");
/* Pour ne pas afficher le mot de passe à l'écran */
int iPosCaractere=0; fflush(stdin);
do { if((cPASSWD[iPosCaractere]=_getch())!='\r')
printf("*");
} while(cPASSWD[iPosCaractere++]!='\r' && iPosCaractere <20);
cPASSWD[--iPosCaractere]='\0';
/* Création d'un environnement ODBC */
retcode = SQLAllocEnv(&d_env);
/* Si la création d'un environnement ODBC est correcte */
if (retcode == SQL_SUCCESS)
{
/* Création d'une connexion ODBC */
retcode = SQLAllocConnect(d_env, &d_connex);
/* Si la connexion ODBC s'est bien passée */
if (retcode == SQL_SUCCESS)
{
/* Initialisation du temps de connexion à 5 secondes. */
SQLSetConnectOption(d_connex, SQL_LOGIN_TIMEOUT, 5);
/* Connexion à une source de données */
retcode = SQLConnect(d_connex,(unsigned char*)cBASE,SQL_NTS,(unsigned char*)cLOGIN,SQL_NTS,(unsigned char*)cPASSWD,SQL_NTS);
Longueur de la chaîne ou on indique que la chaîne se termine par le code NULL
/* Si la connexion à la source de données s'est bien passée */ if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
printf("Connection a la base (source de données).\n");
/* Pause dans l'affichage */
printf("Taper une touche pour continuer \n"); getchar();
/* Allocation mémoire du curseur et association du curseur à la source de données identifiée par d_connex. */
retcode = SQLAllocStmt(d connex, &curseur);
_
/* Si l'allocation mémoire du curseur est correcte */ if (retcode == SQL_SUCCESS)
{ /* Création de la requête SQL */
cREQUETESQL = "SELECT Nom, Ville FROM Etudiant";
/* Execution directe de la requête sur la base */
retcode = SQLExecDirect(curseur, (unsigned char*)cREQUETESQL, SQL NTS);
_
Chap. VI - SQL ODBC
/* Tant le parcours du curseur est valide */
while (retcode == SQL_SUCCESS)
{
/* Parcourt de l'enregistrement résultat de la requête */
retcode = SQLFetch(curseur);
/* Si le parcourt est incorrect */
if (retcode == SQL ERROR || retcode == SQL SUCCESS WITH INFO)
{ _ _ _ _
printf("Erreur %d\n",SQL_ ERROR); }
/* Si le parcourt des enregistrements est correct */
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_ INFO)
{
/* récupération des données des colonnes 1 et 2 de la table
résultat */
SQLGetData(curseur, 1, SQL C CHAR, ucLastName, 30, &ceLastName);
_ _
/* Affichage du résultat */
printf("Etudiant : %s %s\n",ucLastName,ucCity);
ENSTA
Mastère Spécialisé en Architecture des Systèmes d’Information
Cours C1-3
Systèmes de Gestion de Bases de Données
(SGBD) relationnels
Maude Manouvrier
Partie II : les SGBD vus du coté Administrateur de Bases de Données
n Architecture générale d’un SGBD
n Organisation des données
n Évaluation et optimisation de requêtes
n Gestion de la concurrence / transactions
n Reprise sur pannes
