Conception de bases de données. Exercices de synthèse STÉPHANE CROZAT. http bdd.crzt.fr (2)
publicité
Conception de bases de données
Conception de
bases de
données :
Exercices de
synthèse
http://bdd.crzt.fr
STÉPHANE CROZAT
30 juillet 2014
Table des
matières
3
Objectifs
7
I - Exercice : Zéro Faute
9
II - Exercice : Annuaire
11
III - Exercice : Métiers
13
IV - Base de données et programmation
15
V - Expertises
19
A. Mise en situation.........................................................................................19
B. Étude relationnel : Rétro-conception UML.......................................................20
C. Exercice : Étude relationnel : Dépendances fonctionnelles................................20
D. Exercice : Étude relationnel : Forme normale.................................................21
E. Exercice : Étude relationnel : Normalisation....................................................21
F. Étude d'exploitation : Implémentation SQL.....................................................21
G. Étude d'exploitation : Surveillance des réserves..............................................22
H. Étude d'exploitation : Comptes « lourds »......................................................22
I. Étude d'exploitation : Transferts....................................................................22
J. Exercice : Étude d'exploitation : Vigilance.......................................................22
K. Exercice : Étude technologique : Fonction Access............................................23
L. Exercice : Étude technologique : Trigger Oracle...............................................23
M. Exercice : Étude technologique : LAPP...........................................................24
N. Étude Relationnel-Objet...............................................................................25
O. Étude attributs multi-valués.........................................................................25
VI - Étudiants et UVs
27
A. Exercice.....................................................................................................27
B. Exercice.....................................................................................................28
C. Exercice.....................................................................................................29
VII - Gestion de comptes bancaires
31
A. Récupération Access....................................................................................31
B. Migration Oracle..........................................................................................33
VIII - Super-héros
4
35
A. Super-héros relationnels..............................................................................35
B. Super-héros relationnels-objets....................................................................36
C. Super-héros sans tête.................................................................................37
D. Super-lents................................................................................................38
E. Super-transferts..........................................................................................39
IX - Friends
41
A. Exercice.....................................................................................................41
B. Exercice.....................................................................................................43
X - E.T.
45
A. Exercice.....................................................................................................45
B. Exercice.....................................................................................................47
XI - Attributs multi-valués
49
A. Documentum..............................................................................................49
B. Fichier CSV.................................................................................................50
C. Insertion de collections à partir de requêtes SELECT........................................52
D. Attributs multi-valués et relationnel..............................................................53
E. Attributs multi-valués et relationnel-objet......................................................55
Solution des exercices
57
Solution des exercices
93
Glossaire
99
Signification des abréviations
101
Index
103
5
Objectifs
Ce module permet de réviser par des exercices d'application
l'ensemble des concepts et technologies étudiés dans le cadre de la
formation à la conception de bases de données (correspondant à
l'unité de valeur NF17 à l'UTC).
7
Exercice : Zéro
Faute
I
I-
[40 min]
L'entreprise ZéroDéfo veut répertorier ses fautes de production. Une faute est
définie dans le manuel qualité de l'entreprise comme « un défaut constaté sur une
pièce produite en fin de chaîne ». L'entreprise veut associer les fautes aux produits
concernés. Chaque faute est classifiée dans des catégories et sous-catégories.
Chaque produit est basé sur un modèle.
Pour chaque modèle, on veut gérer son code constitué de 8 caractères
alphanumériques, son nom et la date de mise sur le marché.
Pour chaque produit, on veut connaître le modèle associé, le numéro de
série (6 chiffres) et le numéro de produit (max. 4 caractères) ainsi que
l'année de production. Un produit est identifié par son numéro de série et
son numéro de produit.
Plusieurs produits partagent le même numéro de série (tous les produits de
cette série), et deux produits peuvent avoir (par hasard) le même numéro
de produit, dans des séries différentes (qui ont adopté le même système de
codage des produits).
Une faute concerne toujours un produit. Elle possède un code unique, un
titre et la date de détection. Elle peut éventuellement avoir un commentaire
et la date de réparation si le produit a été réparé.
Les fautes sont classifiées dans des sous-catégories et chaque souscatégories fait partie d'une catégorie. Une faute est toujours classifiée dans
une catégorie au moins (elle peut être classifiée dans plusieurs).
Les catégories et les sous-catégories possèdent un nom et, optionnellement,
une description.
Question 1
[Solution n°1 p 57]
Proposez un diagramme UML répondant aux besoins de cette entreprise.
Question 2
[Solution n°2 p 57]
Proposez un modèle relationnel en 3NF basé sur votre modèle UML.
Question 3
[Solution n°3 p 58]
Écrivez le code SQL LDD permettant de créer la base de données correspondant au
modèle relationnel.
Question 4
[Solution n°4 p 58]
Écrivez une requête SQL permettant de lister le nombre de fautes par nom de
modèle et par numéro de série.
9
Exercice : Zéro Faute
10
Exercice :
Annuaire
II
II -
[30 minutes]
Soit le modèle relationnel suivant représentant l'annuaire de l'UTC :
1
personne (nom, prenom, localisation, departement, unite)
Soit un extrait de fichier CSV issu de la base de données (Un fichier CSV, Coma
Separated Value, est un enregistrement d'une table dans un fichier texte).
1
2
3
4
5
Dupont;Stéphane;PG M 290;TSH;ICS
Dupuis;Katie;PG J 178;TSH;ICS
Durand;Paul;CR C 276;GI;Heudiasyc
Dusse;Jean-Claude;CR A 320;GI;Heudiasyc
Dutour;Jean;CR C 198;GI;LMAC
Question 1
[Solution n°5 p 59]
En quelle(s) forme(s) normale(s) est ce modèle ?
Question 2
[Solution n°6 p 59]
Écrivez l'opération d'algèbre relationnelle permettant de renvoyer les noms des
personnes situées dans le même bureau.
Question 3
[Solution n°7 p 59]
Écrivez une transaction permettant de copier tous les enregistrements dans une
table de sauvegarde personne_backup de même schéma que personne, juste après
avoir vidé personne_backup. En cas de panne pendant la transaction, le système
doit revenir à son état initial, c'est à dire que personne_backup n'a été ni vidé ni
rempli des données de "personne".
Question 4
[Solution n°8 p 59]
Implémentez une solution d'optimisation permettant
d'exécution suivante (en dehors de la vue concrète) :
1
2
d'améliorer
la
vitesse
SELECT * FROM Personne
ORDER BY Nom, Prenom
Question 5
[Solution n°9 p 59]
Écrivez un déclencheur (syntaxe Oracle) permettant d'assurer que les chaînes du
champ "departement" seront toujours en majuscule (Utiliser la fonction UPPER).
11
Exercice : Métiers
12
III -
Exercice : Métiers
III
Soit une base de données composée de deux tables tPersonne et tMetier.
1
2
tPersonne (#numero, nom, prenom, metier=>tMetier)
tMetier (#code, intitule)
On souhaite écrire 6 programmes qui permettent d'accéder à BD afin d'afficher la
liste des noms et des métiers sur la sortie standard.
Question 1
[Solution n°10 p 59]
Écrivez un programme PHP
implémentée sous MySQL.
Résultat attendu en HTML :
1
2
3
4
5
6
permettant
d'interroger
la
base
de
données
<html>
<p>Dupont Ingénieur</p>
<p>Durand Architecte</p>
<p>Dupuis Secrétaire</p>
...
</html>
Question 2
[Solution n°11 p 60]
Écrivez un programme PHP permettant d'interroger la
implémentée sous PosgreSQL (et d'obtenir le même résultat).
base
de
données
Question 3
[Solution n°12 p 60]
Écrivez un programme PHP permettant d'interroger
implémentée sous Oracle (et d'obtenir le même résultat).
la
base
de
données
Question 4
[Solution n°13 p 60]
Écrivez un programme VBA permettant d'interroger la base de données
implémentée sous Access.
Résultat attendu sur la sortie standard (utilisez Debug.Print et l'opérateur de
concaténation &) :
1
2
3
4
Dupont Ingénieur
Durand Architecte
Dupuis Secrétaire
...
13
Exercice : Métiers
Question 5
[Solution n°14 p 60]
Écrivez un programme PL/SQL permettant d'interroger la base de données
implémentée sous Oracle.
Résultat attendu sur la sortie standard (utilisez DBMS_OUTPUT.PUT_LINE et
l'opérateur de concaténation ||) :
1
2
Dupont Ingénieur
...
Question 6
[Solution n°15 p 61]
Écrivez un programme Java permettant d'interroger la base de données
implémentée sous Oracle.
Résultat attendu sur la sortie standard (utilisez System.out.println et l'opérateur
de concaténation +) :
1
2
14
Dupont Ingénieur
...
Base de données
et programmation
IV
IV -
Soit une base de données composée d'une unique table T, contenant un unique
attribut A.
1
T(#A:string)
On souhaite écrire 6 programmes qui permettent d'accéder à la base de données,
afin d'afficher la liste des valeurs de A.
Exercice 1 : PHP & PosgreSQL
[Solution n°1 p 93]
Écrivez un programme PHP
implémentée sous PosgreSQL.
<?php
permettant
d'interroger
la
base
de
données
$conn=pg_connect($host,$user,$passwd,$bdd);
$query = "
";
$result=pg_query(
,
);
echo "<html>";
while($row = pg_fetch_row(
))
{
echo "<p>
</p>";
}
echo "</html>";
pg_close($conn);
?>
Exercice 2 : PHP & MySQL
[Solution n°2 p 93]
Écrivez un programme PHP
implémentée sous MySQL.
<?php
permettant
d'interroger
la
base
de
données
$conn=mysql_connect($host,$user,$passwd);
mysql_select_db($bdd, $conn);
$query = "
";
$result=mysql_query(
,
);
echo "<html>";
15
Base de données et programmation
while($row = mysql_fetch_row(
))
{
echo "<p>
</p>";
}
echo "</html>";
mysql_close($conn);
?>
Exercice 3 : PHP & Oracle
[Solution n°3 p 93]
Écrivez un programme PHP
implémentée sous Oracle.
<?php
permettant
d'interroger
la
base
de
données
//définition des variables de connexion à ajouter
$conn=oci_connect($user, $passwd, $bd))
$query = "
";
$statement = oci_parse(
,
);
oci_execute($statement);
echo "<html>";
while($row = oci_fetch_array(
))
{
echo "<p>
</p>";
}
echo "</html>";
oci_close($conn);
?>
Exercice 4 : Access & VBA
[Solution n°4 p 94]
Écrivez un programme VBA
implémentée sous Access.
Sub printA()
vSql = "
permettant
For i = 1 To vRs.RecordCount
vRs.MoveNext
Next i
End Sub
16
la
base
"
Set vRs = CurrentDb.CreateQueryDef("",
Debug.Print
d'interroger
!
).OpenRecordset
de
données
Base de données et programmation
Exercice 5 : Oracle & PL/SQL
[Solution n°5 p 94]
Écrivez un programme PL/SQL permettant d'interroger la base de données
implémentée sous Oracle.
SET SERVEROUTPUT ON;
DECLARE
CURSOR cT IS
vA
;
%TYPE;
BEGIN
OPEN
;
LOOP
FETCH
INTO
;
EXIT WHEN cT%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(
);
END LOOP;
END;
Exercice 6 : Oracle & Java
[Solution n°6 p 94]
Écrivez un programme Java
implémentée sous Oracle.
package nf17;
permettant
d'interroger
la
base
de
données
import java.sql.*;
import oracle.jdbc.OracleDriver;
public class Exercice {
public static void main(String[] args) {
try {
DriverManager.registerDriver (new OracleDriver());
Connection
vCon
=
DriverManager.getConnection("jdbc:oracle:thin:nf17/nf17@localhost:1521:test");
Statement vSt = vCon.createStatement();
String vSql = "
";
ResultSet vRs = vSt.executeQuery(
);
while(vRs.next()) {
System.out.println(
.getString(1));
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
17
Base de données et programmation
}
18
V-
Expertises
V
Mise en situation
19
Étude relationnel : Rétro-conception UML
20
Exercice : Étude relationnel : Dépendances fonctionnelles
20
Exercice : Étude relationnel : Forme normale
21
Exercice : Étude relationnel : Normalisation
21
Étude d'exploitation : Implémentation SQL
22
Étude d'exploitation : Surveillance des réserves
22
Étude d'exploitation : Comptes « lourds »
22
Étude d'exploitation : Transferts
23
Exercice : Étude d'exploitation : Vigilance
23
Exercice : Étude technologique : Fonction Access
23
Exercice : Étude technologique : Trigger Oracle
24
Exercice : Étude technologique : LAPP
24
Étude Relationnel-Objet
25
Étude attributs multi-valués
26
[2 heures]
A. Mise en situation
Vous avez en charge une étude pour la réalisation d'une base de données de
gestion pour une association.
Vous disposez pour seule donnée d'entrée du modèle relationnel MR ci-dessous,
réalisé par un ingénieur qui vient de partir pour une retraite de 6 ans au Népal.
1
2
3
4
Compte (#num:entier, categorie:{A|R}, =solde():reel)
Recette(#num=>Compte, #date:date, montant:reel, annee:entier)
Depense(#num=>Compte, #date:date, montant:reel, annee:entier)
Les comptes A ou « actifs » sont les comptes courants de l'association. Les comptes
R ou « réserve » sont des comptes sur lesquelles les opérations sont rares et
n'interviennent qu'en cas de problème de trésorerie ou d'investissement important.
La notation « =solde():reel » n'est pas usuelle en relationnel. Elle signifie qu'une
19
Expertises
méthode « solde » renvoyant un réel est associée à la relation. Bien entendu cette
méthode ne sera pas implémentée en SQL LDD et devra être programmée à part,
dans une procédure stockée par exemple.
Vous allez réaliser une double étude en relationnel et en relationnel-objet, et tester
différentes technologies.
B. Étude relationnel : Rétro-conception UML
Afin de mener sereinement votre étude, vous décidez de réaliser le modèle
conceptuel MC qui a servi à réaliser le modèle relationnel MR dont vous disposez.
Question
[Solution n°16 p 61]
Réalisez le schéma UML permettant d'arriver à MR. Vous chercherez le meilleur
schéma UML possible.
C. Exercice : Étude relationnel : Dépendances
fonctionnelles
[Solution n°7 p 95]
Analysez la sémantique du schéma MR et sélectionnez, parmi les propositions
suivantes, toutes les dépendances fonctionnelles ayant du sens (on note C, R et D
les relations Compte, Recette et Depense de MR).
C.num→C.categorie
C.categorie→C.num
(R.num, R.date)→R.montant, R.annee
R.num→R.date
R.annee→R.date
R.date→R.annee
(D.num, D.date)→D.montant, D.annee
D.num→D.date
D.annee→D.date
D.date→D.annee
20
Expertises
D. Exercice : Étude relationnel : Forme normale
[Solution n°8 p 95]
Il est évident que ce schéma n'est pas en 3NF, mais est-il en 2NF ? Justifiez.
NB : L'on considérera qu'une date est bien atomique.
Oui, le schéma est en 2NF
Non, le schéma est seulement en 1NF
E. Exercice : Étude relationnel : Normalisation
[Solution n°9 p 96]
Parmi les propositions MR' suivantes, sélectionnez toutes celles qui permettent de
parvenir à un schéma en 3NF, sans perte d'information. Expliquez et critiquez
chaque solution choisie. Si vous avez choisi plusieurs solutions, classez ces
solutions entre elles de la meilleure à la moins bonne.
Compte (num:entier, categorie:{A|R}, =solde():reel)
Recette(num=>Compte, date=>AnneeR, montant:reel)
AnneeR(date:date, annee:entier)
Depense(num=>Compte, date=>AnneeD, montant:reel)
AnneeD(date:date, annee:entier)
Compte (num:entier, categorie:{A|R}, =solde():reel)
Recette(num=>Compte, date=>Annee, montant:reel)
Depense(num=>Compte, date=>Annee, montant:reel)
Annee(date:date, annee:entier)
Compte (num:entier, categorie:{A|R}, =solde():reel)
Recette(num=>Compte, date:date, montant:reel)
Depense(num=>Compte, date:date, montant:reel)
F. Étude d'exploitation : Implémentation SQL
Vous décidez d'ignorer le schéma MR' et de poursuivre avec le schéma relationnel
MR (vous resterez donc avec un schéma redondant).
Vous avez à disposition les types suivants INTEGER(X), DECIMAL(X,Y), CHAR(X) et
VARCHAR(X), ainsi que DATE et DATETIME.
NB : On pose qu'il existera toujours moins de 100 comptes pour l'association.
Question
[Solution n°17 p 62]
Implémentez en SQL standard le schéma MR non normalisé (les types, ainsi que X
et Y devront être bien choisis).
21
Expertises
G. Étude d'exploitation : Surveillance des réserves
Réalisez une simulation de recherche d'opérations sur les comptes de réserve R.
Question
[Solution n°18 p 62]
Écrivez en algèbre relationnelle la suite d'opérations permettant de trouver les
numéros des comptes de catégorie R tels qu'ils ont connu au moins une dépense.
H. Étude d'exploitation : Comptes « lourds »
Vous cherchez à implémenter la question permettant de trouver tous les comptes «
lourds », c'est à dire ayant de très fortes recettes.
Question
[Solution n°19 p 62]
Écrivez en SQL une requête permettant d'afficher la liste des numéros des comptes
dont les recettes sont supérieures à 1.000.000 €, depuis leur création.
I. Étude d'exploitation : Transferts
Etudiez à présent les modalités d'un transfert de compte à compte.
Question
[Solution n°20 p 62]
Ecrivez une transaction permettant de transférer 100€ du compte n°125 au compte
n°222 le 20 juin 2006 à 12h02.
Rappel : Les insertions de date se feront selon la syntaxe standard : 'YYYY/MM/DD
HH:MI'
J. Exercice : Étude d'exploitation : Vigilance
[Solution n°10 p 96]
Le trésorier a besoin de vérifier chaque matin en arrivant, parmi les comptes A de
l'année en cours, lesquels sont très actifs : c'est à dire combien d'opérations de
dépenses sont effectuées sur chaque compte. Sélectionnez la meilleure solution
d'optimisation parmi celles proposées ci-dessous pour cette requête. Expliquez.
22
Expertises
Indexation
Partitionnement horizontal
Partitionnement vertical
Dénormalisation
Vue matérialisée
K. Exercice : Étude technologique : Fonction Access
[Solution n°11 p 96]
Complétez le code VBA ci dessous afin qu'il permettent d'impémenter la méthode «
solde() », qui fait la différence entre les recettes et les dépenses pour un compte
donné.
Expliquez pourquoi, sous Access, les méthodes sont implémentées comme des
fonctions VBA indépendantes des tables ?
Rappel : Le caractère de concaténation de chaîne sous Access est "&" : "A" & "B"
renvoie "AB".
Function solde(pNum As Integer) As Real
Dim vCodeSql As String
Dim vR As Real
Dim vD As Real
vCodeSql =
vR = CurrentDb.CreateQueryDef("", vCodeSql).OpenRecordset.Fields(0)
vCodeSql =
vD = CurrentDb.CreateQueryDef("", vCodeSql).OpenRecordset.Fields(0)
solde=
End Function
L. Exercice : Étude technologique : Trigger Oracle
[Solution n°12 p 97]
Complétez le code PL/SQL ci-dessous afin qu'il implémente un trigger permettant
de systématiquement recalculer la valeur du champs « annee », en fonction du
champs « date » à chaque insertion et mise à jour des données.
Expliquez en quoi c'est une solution au choix précédent qui avait été fait de ne pas
normaliser le schéma.
Rappel : La fonction SQL "YEAR" renvoie l'année d'une date sous la forme d'un
entier.
CREATE TRIGGER RecetteTrig
BEFORE INSERT OR UPDATE
23
Expertises
ON Recette
FOR EACH ROW
BEGIN
END ;
M. Exercice : Étude technologique : LAPP
[Solution n°13 p 97]
Soit la page HTML suivante :
1
2
3
4
5
6
7
8
9
<html xmlns="http://www.w3.org/1999/xhtml">
<body>
<form method="GET" action="insert.php">
Numéro : <input type="text" name="n"/>
Catégorie : <input type="text" name="c"/>
<input type="submit"/>
</form>
</body>
</html>
Complétez le code ci-dessous du fichier "insert.php" qui sera appelé pour insérer un
nouveau compte dans une BD PostgreSQL.
<html xmlns="http://www.w3.org/1999/xhtml">
<body>
<?php
/* Initialisation */
$vserveur="monassos.free.fr" ;
$vport="5432" ;
$vcu="president" ;
$vbase="bdassos" ;
$vcp="bonjour" ;
/* Connexion à la BD */
$c
=
user=
pg_connect("host=
password=
port=
dbname=
");
/* Exécution de la requête*/
$sql = "INSERT INTO Compte (".
.",'".
."')" ;
;
/* Finalisation */
pg_close($c);
echo "<p>Insertion du compte numéro".
?>
</body>
</html>
24
."effectuée</p>" ;
Expertises
N. Étude Relationnel-Objet
Vous prolongez présent votre travail par une étude du Relationnel-Objet et de son
implémentation en SQL3 sous Oracle.
NB : Nous considérerons à présent l'année, dans les relations Recette et Dépense,
comme une méthode et non plus comme un attribut.
Question 1
[Solution n°21 p 62]
Réalisez le modèle MRO, équivalent relationnel-objet du modèle MR. Le modèle
MRO ne devra contenir qu'une seule table. Bien entendu vous créerez autant de
types et de collections (donc de tables imbriquées) que vous le souhaitez.
Question 2
[Solution n°22 p 63]
Déclarez vos types ainsi que votre table, sans implémenter les méthodes (corps de
type).
Question 3
[Solution n°23 p 63]
Implémentez la méthode annee() dans le corps de type correspondant.
Rappel : La fonction PL/SQL "YEAR" renvoie l'année d'une date sous la forme d'un
entier.
Question 4
[Solution n°24 p 63]
Implémentez la méthode solde() dans le corps de type correspondant.
Question 5
[Solution n°25 p 63]
Créez le compte numéro 1, de catégorie A dans votre table, avec une recette de
100 € le 20 juillet 2007 à 11h31.
Rappel : utilisez la fonction Oracle TO_DATE pour insérer des dates sous Oracle
Question 6
[Solution n°26 p 64]
Afin de testez sommairement votre implémentation, écrivez la requête permettant
d'afficher tous les numéros de compte avec tous leurs soldes.
O. Étude attributs multi-valués
Des propriétés complémentaires sont ajoutées à l'entité Compte, sous la forme de
deux attributs multivalués : signatures (qui contient la liste des noms ayant
signature sur le compte) et procurations (qui contient la liste des noms ayant
procuration sur le compte). Pour cette dernière étude le schéma étudié sera
uniquement le schéma conceptuel simplifié MC2 suivant.
25
Expertises
Compte
num:entier
categorie:{A|R}
signatures[1..n]:chaîne
procurations[1..n]:chaîne
Graphique 1 Compte
Question 1
[Solution n°27 p 64]
Donnez la transcription de cette classe en relationnel standard (MR2rc), en
relationnel-objet sous Oracle (MR2ro) et en relationnel sous Documentum (MR2rd)
Question 2
[Solution n°28 p 64]
Donnez l'avantage et l'inconvénient de la méthode relationnel Documentum par
rapport au relationnel classique.
26
VI -
Étudiants et UVs
VI
Exercice
29
Exercice
30
Exercice
32
A. Exercice
On dispose du schéma UML ci-après qui décrit des étudiants, des UV, les notes
obtenues par les étudiants à ces UV, et les diplômes d'origine de ces étudiants.
Étudiants et UVs
{key} désigne des clés candidates, ici toutes les clés ne sont composées que
d'un seul attribut
{local key} désigne une clé locale
un semestre est de la forme 'PYYYY' ou 'AYYYY' (où YYYY désigné une année
sur 4 chiffre) ; exemple : 'A2013', 'P2014...
Rappel : Notion de clé locale dans classes d'association (cf. Transformation des
classes d'association)
Question 1
[Solution n°29 p 64]
Traduire le schéma en modèle logique relationnel (MLD1).
On choisira obligatoirement les clés primaires parmi celles nécessitant le plus petit
nombre de bits possible pour leur codage.
Question 2
[Solution n°30 p 64]
Écrire en SQL la vue vmax1 permettant de renvoyer pour chaque titre d'UV, intitulé
de diplôme et semestre, la note maximale obtenue par un étudiant.
uv
Bases de données
diplome
Deutech UTC
semestre
P2014
max
18
27
Étudiants et UVs
uv
diplome
semestre
max
Bases de données
Deutech UTC
A2013
17
Bases de données
DUT Budapest
P2014
15
Bases de données
DUT Budapest
A2013
16
...
Tableau 1 Exemple de résultat retourné par la vue vmax1
Question 3
[Solution n°31 p 64]
Montrer qu'un choix différent pour les clés primaires aurait permis d'optimiser cette
vue. Proposer le modèle logique relationnel correspondant (MLD2) et la nouvelle vue
vmax2 associée.
Question 4
[Solution n°32 p 65]
Proposer un programme PHP permettant d'afficher en HTML les informations de la
vue vmax1 ou vmax2 (au choix) restreintes à l'année 2014.
On pourra utiliser la fonction fConnect() qui renvoie un identifiant de connexion.
On travaillera avec une base PostgreSQL.
B. Exercice
On rappelle que la présence de redondance dans un modèle relationnel doit être
documentée par la mise en évidence des DF responsables.
Question 1
[Solution n°33 p 65]
Les performance de la page PHP proposée ne sont pas satisfaisantes. La cause du
problème est identifiée comme étant la jointure qui s'exerce entre les tables
etudiant et note.
Proposer une solution de dénormalisation. Proposer le modèle logique relationnel
correspondant (MLD3) et la nouvelle vue vmax3 associée.
Afin de contrôler la redondance introduite par la dénormalisation, on décide de
créer :
une table etucontrole qui contiendra tous les numéros d'étudiants dont les
informations ne sont pas cohérentes (dont le nom ou le diplôme possède
plusieurs valeurs différentes) : etucontrole(#num=>etudiant) ;
un trigger tretudiant qui viendra ajouter les numéros incohérents à chaque
mise à jour ou insertion dans la base de données.
Question 2
[Solution n°34 p 65]
Écrire le trigger tretudiant. Ce trigger testera l’existence préalable de l'étudiant
inséré ou mis à jour et en cas d'incohérence insérera le numéro dans la table
etucontrole.
On utilisera la syntaxe Oracle.
Indice :
28
Étudiants et UVs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TRIGGER tretudiant
BEFORE INSERT OR UPDATE
ON etudiant
FOR EACH ROW
DECLARE
vnum etudiant.num%TYPE;
vnom etudiant.nom%TYPE;
vorigine etudiant.origine%TYPE;
BEGIN
/** On récupère les informations éventuellement déjà
existantes concernant l'étudiant inséré ou mis à jour ;
on utilise la fonction max pour ne retourner qu'un seul tuple **/
/** On teste que ces valeurs sont les mêmes que celles
nouvellement insérées ou mises à jour et sinon on
insère dans la table 'etucontrole' **/
END;
Question 3
[Solution n°35 p 66]
Écrire en SQL la création de la table etucontrole ainsi que la requête permettant
d'obtenir la liste des incohérences.
1
Turing
TC UTC
1
Turingue
TC UTT
3
von Neumann
DUT Budapest
3
Vonneumann
DUT Budapest
3
von Neumann
DUT Budpapest
Tableau 2 Exemple d'incohérences
Question 4
[Solution n°36 p 66]
Écrire la séquence SQL permettant de corriger les informations concernant les
étudiants '1' et '3'. On considérera ici que les lignes correctes sont :
1
Turing
TC UTC
3
von Neumann
DUT Budapest
Préciser pourquoi il est nécessaire de modifier plusieurs tables.
Préciser pourquoi il est nécessaire d'utiliser des transactions.
Préciser pourquoi l'ordre de certaines opérations a de l'importance.
C. Exercice
Question
[Solution n°37 p 67]
Traduire le modèle UML Étudiants et UVs en relationnel-objet en utilisant la
syntaxe SQL3 définie sous Oracle. Utiliser impérativement le modèle table-objet et
29
Étudiants et UVs
les références par OID. Utiliser le modèle imbriqué lorsque c'est pertinent.
30
Gestion de
comptes
bancaires
VII -
VII
Récupération Access
33
Migration Oracle
35
A. Récupération Access
[30 minutes]
Nouvel employé d'une entreprise bancaire, vous avez pour mission de restructurer
une base de données existante pour la gestion des opérations bancaires (débits et
crédits sur un compte).
La base de données existante a été conçue sous MS Access, avec une unique table.
Image 1 Schéma de la seule table de la BD
NB : Un débit est différencié d'un crédit par la valeur de MontantOperation : Si elle
est négative il s'agit d'un débit, si elle est positive il s'agit d'un crédit.
En vue de préparer la re-conception de cette base, un stagiaire a effectué le
recensement des DF de ce schéma.
Voici le résultat de ses travaux, sous la forme de la fermeture transitive des DF :
NumClient→Nom, Prenom
NumCompte→NumClient, Nom, Prenom, Agence, Ville, Pays, Monnaie
Agence→Ville, Pays, Monnaie
Pays→Monnaie
31
Gestion de comptes bancaires
DateOperation, NumCompte→MontantOperation
Question 1
[Solution n°38 p 72]
Trouvez toutes les clés candidates pour ce schéma, puis choisissez une clé primaire.
Justifier.
Question 2
[Solution n°39 p 72]
En quelle forme normale est le schéma de la base existante ? Justifier.
Question 3
[Solution n°40 p 72]
Afin de préparer la décomposition du schéma relationnel, trouvez une couverture
minimale des DFE pertinente.
Question 4
[Solution n°41 p 72]
Décomposer le schéma relationnel de la base existante, en préservant les DF, pour
aboutir à un schéma en 3NF. Choisissez la meilleure clé primaire pour chaque
relation. Présentez le schéma relationnel résultant et justifiez.
Question 5
[Solution n°42 p 72]
A des fins de documentation, retro-concevez le MCD qui aurait permis d'aboutir
directement à ce schéma relationnel.
Question 6
[Solution n°43 p 72]
L'équipe technique ne connaissant pas Access, elle fait appel à vous pour traduire
les requêtes qui existaient dans l'ancien système. On s'attache en particulier à la
requête QBE suivante :
Image 2 Requête RTotalParPersonneEtParMonnaie
32
Gestion de comptes bancaires
Vous avez accès à son équivalent SQL grâce au générateur automatique de SQL de
Access :
1
2
3
SELECT TBank.NumClient, TBank.Monnaie, Sum(TBank.MontantOperation) AS
SommeDeMontantOperation
FROM TBank
GROUP BY TBank.NumClient, TBank.Monnaie;
En SQL (standard), créez une vue équivalente à cette requête QBE, fonctionnant
avec le nouveau schéma.
B. Migration Oracle
[30 minutes]
Récupération des données sous Oracle
Une équipe technique est désignée afin de réaliser la nouvelle base de données que
vous avez préconisée sous Oracle. Une fois le schéma relationnel créé, elle fait
appel à vous pour récupérer les données existantes.
Question 1
[Solution n°44 p 73]
Écrivez une série d'instructions SQL permettant la récupération des données depuis
l'ancienne base de données vers la nouvelle.
Amélioration de l'application
L'équipe technique fait à présent appel à vous pour certains aspects particuliers de
l'application.
Notez la table suivante qui a été ajoutée au schéma relationnel :
1
CREATE TABLE TDeficit (NumCompte number(10));
Question 2
[Solution n°45 p 73]
Écrivez un trigger qui, lorsque l'on insère une opération de débit qui conduit à un
solde négatif du compte, insère le numéro du compte dans la table "TDeficit".
Question 3
[Solution n°46 p 74]
Écrivez une méthode booléenne Java qui prend en paramètre d'entrée un numéro
de client et renvoie vrai si ce client a déjà été en déficit. On notera que la base se
nomme "bank", qu'elle est accédée par la méthode sur la machine
"www.oracle.bank.com" (port "1521"), par l'utilisateur "Java" (mot de passe
"Tango").
Fiabilisation
L'équipe informatique fait maintenant appel à vous pour fiabiliser la base de
données, à présent utilisée. Un problème récurrent survient sur la procédure
PL/SQL "pVirement" donnée ci-dessous. En effet suite à des micro-coupures réseau,
il arrive que le débit du "Compte1" soit effectué, tandis que le crédit du "Compte2"
n'est jamais effectué.
1
2
3
4
CREATE procedure pVirement (Compte1 number, Compte2 number, Montant
number, DateOp date)
IS
BEGIN
INSERT INTO TOperation VALUES (Compte1, DateOp, Montant * -1);
33
Gestion de comptes bancaires
5
6
INSERT INTO TOperation VALUES (Compte2, DateOp, Montant);
END;
Question 4
[Solution n°47 p 74]
Expliquez pourquoi le problème survient. Proposer une solution générale au
problème. Implémentez cette solution en réécrivant la procédure PL/SQL.
Optimisation
L'équipe informatique fait enfin appel à vous pour optimiser la base, et en
particulier la requête suivante qui, étant donné un nombre d'opérations très grand
existant, est trop longue à s'exécuter.
1
2
3
4
SELECT NumCompte, MontantOperation
FROM TOperation
WHERE MontantOperation > 10000;
ORDER BY MontantOperation;
Question 5
[Solution n°48 p 74]
Expliquez pourquoi cette requête sera longue à s'exécuter si le nombre
d'enregistrements est très grand dans la table TOperation. Proposer une solution
générale au problème. Proposer une définition SQL implémentant cette solution
pour ce cas particulier.
34
VIII -
Super-héros
VIII
Super-héros relationnels
39
Super-héros relationnels-objets
40
Super-héros sans tête
41
Super-lents
43
Super-transferts
44
A. Super-héros relationnels
[30 min]
La gamme de super-héros GARVEL veut réaliser la base de données de leurs
figurines articulées. La société a fait réaliser un modèle UML qui doit servir de point
de départ à la mise en œuvre.
Modèle UML Figurines GARVEL
Question 1
[Solution n°49 p 74]
Transformer le modèle UML en modèle relationnel (justifier les passages non
triviaux, en particulier la relation d'héritage).
35
Super-héros
Question 2
[Solution n°50 p 75]
Donner, en algèbre relationnel, la liste de tous les personnages qui sont basés au
repaire 'La Ratcave'.
Question 3
[Solution n°51 p 75]
Donner, en algèbre relationnel, la couleur des torses des personnages habitant la
'GARVEL Tower', pilotant des véhicules aquatiques et ayant comme mentor
Superman.
Question 4
[Solution n°52 p 75]
Donner, en algèbre relationnel, la liste des désignations de toutes les figurines
ayant un prix inférieur à 10 (une seule colonne désignation sera projetée).
B. Super-héros relationnels-objets
[30 minutes]
Modèle UML Figurines GARVEL
Question 1
[Solution n°53 p 76]
Transformer le modèle UML en modèle relationnel-objet. On utilisera les OID et le
nested model.
Question 2
[Solution n°54 p 77]
Donner, en SQL, la liste de tous les personnages qui sont basés au repaire "La
Ratcave".
Question 3
[Solution n°55 p 77]
Donner, en SQL, la couleur des torses des personnages habitant la "GARVEL
Tower", pilotant des véhicules aquatiques et ayant comme mentor Superman.
36
Super-héros
C. Super-héros sans tête
[10 minutes]
Les usines GARVEL construisent des figurines de super-héros à partir des données
présentes dans la base de données PostgreSQL de l'entreprise. Un gros problème
est survenu le mois dernier, lorsque l'usine en charge d'une nouvelle figurine,
Superchild, a livré un million d'exemplaires sans tête. À l'analyse de la base il a en
effet été observé que la base contenait un tuple "Superchild" dans la table
Personnage, et cinq tuples associés dans la table Membre, deux pour les bras, deux
pour les jambes et un pour le torse, mais aucun pour la tête.
Le service qui a opéré la saisie du nouveau personnage assure, sans ambiguïté
possible, que la tête a pourtant été saisie dans la base. En revanche, l'enquête
montre des instabilités de son réseau à cette période.
L'extrait du modèle UML utile au problème est proposé ci-après, ainsi que le code
SQL exécuté via le client psql lors de l'insertion de la nouvelle figurine.
Modèle UML Figurines GARVEL (extrait)
1
2
3
4
5
6
7
8
\set AUTOCOMMIT on
INSERT INTO Personnage (designation, prix, identite_secrete, genre)
VALUES ('Superchild','12','Jordy','superhéros') ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','bras droit','bleu') ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','bras gauche','bleu') ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','jambe gauche','bleu') ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','jambe droite','bleu') ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','torse','rouge') ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','tete','rose') ;
Question 1
[Solution n°56 p 77]
Expliquer la nature du problème qui est probablement survenu. Proposer une
solution générale pour que le problème ne se renouvelle pas, en expliquant
37
Super-héros
pourquoi.
Soyez concis et précis : La bonne mobilisation des concepts du domaine et la
clarté de la rédaction seront appréciées.
Question 2
[Solution n°57 p 77]
Illustrer la solution proposée en corrigeant le code SQL de l'insertion de
"Superchild".
D. Super-lents
[10 minutes]
L'entreprise GARVEL propose des figurines de super-héros à acheter en ligne sur un
site Internet adossé à sa base de données. Son catalogue a atteint cette année le
million de modèles. Depuis quelques temps, et la récente forte augmentation des
modèles au catalogue, les performances des consultations ont beaucoup chuté,
entraînant des temps d'accès lents (plusieurs secondes) et une baisse des actes
d'achat.
La requête la plus utilisée par l'application Web sert à lister tous les super-héros
avec toutes leurs caractéristiques, avec leurs véhicules et leurs repaires (et
également toutes leurs caractéristiques) et à la trier par ordre de prix.
Modèle UML Figurines GARVEL
Soyez concis et précis dans vos réponses ; La bonne mobilisation des concepts du
domaine et la clarté de la rédaction seront appréciées.
Question 1
[Solution n°58 p 78]
Expliquer pourquoi cette requête peut poser des problèmes de performance,
lorsque la base comprend de nombreux enregistrements.
Question 2
[Solution n°59 p 78]
Proposer et justifier une première solution d'optimisation à mettre en œuvre qui
sera utile dans tous les cas et n'aura que peu d'effets indésirables. Puis, expliquer
pourquoi le passage en relationnel-objet de la base de données, combiné à la
solution précédente, peut améliorer considérablement les performances.
38
Super-héros
Question 3
[Solution n°60 p 78]
Proposer deux solutions alternatives qui, si l'on reste en relationnel, permettraient
d'améliorer considérablement les performances. Vous en poserez les avantages,
inconvénients et les mesures à prendre pour contenir ces inconvénients.
E. Super-transferts
[15 minutes]
L'entreprise de ventes de figurines de super-héros GARVEL a monté un partenariat
avec les deux sites de ventes en ligne makemoney.com et dobusiness.com. Chaque
entreprise lui demande de mettre à disposition respectivement un fichier CSV et un
fichier XML pour le transfert du catalogue, stocké dans une base de données
PostgreSQL.
Modèle UML Figurines GARVEL (extrait)
Les paramètres de connexion à la base sont : host=garvel.com
dbname=cat user=customer password=public.
port=5432
Les champs designation et prix sont présents tous les deux dans la vue
vFigurine.
Question 1
[Solution n°61 p 79]
Réaliser un script PHP permettant de se connecter à la base PosgreSQL et d'afficher
la désignation et le prix au format CSV, en suivant l'exemple ci-après.
1
2
3
4
Superman;15
Batman;12
Superchild;12
...
Indice :
1
<?php
39
Super-héros
2
3
4
5
6
7
8
$vConn = pg_connect("...");
$vSql ="...";
$vQuery=pg_query(...);
while ($vResult = pg_fetch_array(...)) {
echo "...";
}
?>
Question 2
[Solution n°62 p 79]
Réaliser un script PHP permettant de se connecter à la base et d'afficher la
désignation et le prix selon un schéma XML, en suivant l'exemple ci-après.
1
2
3
4
5
6
<catalogue>
<figurine designation='Superman' prix='1555'/>
<figurine designation='Batman' prix='12'/>
<figurine designation='Superchild' prix='12'/>
...
</catalogue>
Indice :
1
2
3
4
5
6
7
8
9
10
40
<?php
$vConn = pg_connect("...");
$vSql ="...";
$vQuery=pg_query(...);
echo "...";
while ($vResult = pg_fetch_array(...)) {
echo "...";
}
echo "...";
?>
IX -
Friends
IX
Exercice
47
Exercice
49
A. Exercice
[60 min]
Soit la déclaration d'une base de données Oracle ci-après.
1
2
3
4
5
6
7
8
CREATE TABLE person (
login VARCHAR(10) NOT NULL,
hasfriends NUMBER(10),
isfriendof NUMBER(10),
PRIMARY KEY (login),
CHECK (hasfriends >= 0),
CHECK (isfriendof >= 0)
);
1
2
3
4
5
CREATE TABLE friends (
login VARCHAR(10) REFERENCES person(login),
declarefriend VARCHAR(10) REFERENCES person(login),
PRIMARY KEY (login,declarefriend)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE TRIGGER tr_friends
BEFORE INSERT
ON friends
FOR EACH ROW
BEGIN
-UPDATE person p
SET p.hasfriends = NVL(p.hasfriends,0) + 1
WHERE p.login=:new.login;
-UPDATE person p
SET p.isfriendof = NVL(p.isfriendof,0) + 1
WHERE p.login=:new.declarefriend;
-END;
NB : Les explications devront être fournies dans un français concis, précis et
correct.
Question 1
[Solution n°63 p 79]
Expliquez chaque instruction UPDATE du déclencheur tr_friends. Dessinez les
41
Friends
tables person et friends après l'exécution de la séquence SQL ci-après.
1
2
3
4
5
6
7
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
SELECT
INTO person (login)
INTO person (login)
INTO person (login)
INTO friends VALUES
INTO friends VALUES
INTO friends VALUES
* FROM person;
VALUES ('a');
VALUES ('b');
VALUES ('c');
('a','b');
('a','c');
('c','a');
Question 2
[Solution n°64 p 80]
Expliquez pourquoi l'usage de la fonction NVL est indispensable dans le déclencheur
tr_friends.
Question 3
[Solution n°65 p 80]
Rétro-concevez le modèle logique correspondant. Expliquez pourquoi friends est
une association réflexive dissymétrique.
Question 4
[Solution n°66 p 80]
Expliquez ce que signifie chaque attribut de chaque relation.
Question 5
[Solution n°67 p 80]
Prouvez que ce modèle est en 3NF. Expliquez en quoi le modèle, bien qu'en 3NF,
contient néanmoins de la redondance.
Question 6
[Solution n°68 p 81]
Rétro-concevez le modèle conceptuel correspondant. Utilisez les rôles pour
expliciter les associations réflexives.
Question 7
[Solution n°69 p 81]
Décrivez les tables person et friends après l'exécution de la séquence SQL ciaprès. Expliquez pourquoi cela n'est pas cohérent, et ce qu'il faudrait faire pour le
corriger (en français).
1
2
3
DELETE FROM friends WHERE login='a';
UPDATE friends SET declarefriend='b' WHERE login='c';
SELECT * FROM person;
Question 8
[Solution n°70 p 81]
Proposez une seconde version du déclencheur tr_friends permettant de traiter le
cas précédent.
Question 9
[Solution n°71 p 82]
L'instruction ci-après engendre également une incohérence.
déclencheur tr_person permettant de régler le problème.
1
42
Proposez
INSERT INTO person (login, hasfriends, isfriendof) VALUES ('d',12,7);
un
Friends
Question 10
[Solution n°72 p 82]
Il reste encore un cas possible d'introduction d'incohérence lié à la redondance
initiale, mentionnez-le et donnez un exemple d'instruction SQL posant problème.
Question 11
[Solution n°73 p 82]
Écrire deux fonctions permettant de renvoyer respectivement les valeurs réelles de
hasfriends et isfriendof à partir de la table friends.
CREATE FUNCTION f_hasfriends(v_login person.login%TYPE) RETURN
person.hasfriends%TYPE
CREATE FUNCTION f_isfriendof(v_login person.login%TYPE) RETURN
person.isfriendof%TYPE
Question 12
[Solution n°74 p 82]
En utilisant les fonctions f_hasfriends et f_isfriendof, proposez une seconde
version du déclencheur tr_person permettant de traiter ce dernier cas.
B. Exercice
[30min]
On souhaite à présent proposer une implémentation alternative à l'implémentation
précédente, en utilisant le relationnel-objet. L'enjeu est de ne plus introduire de
redondance et donc de supprimer les déclencheurs contrôlant cette redondance.
On remplacera les attributs hasfriends et isfriendof par les méthodes
hasfriends() et isfriendof().
On profitera également du passage en RO pour :
Utiliser le modèle imbriqué pour l'association N:M
Utiliser les OID à la place des clés étrangères.
Les deux requêtes ci-après devront donc donner les résultats associés.
1
2
SELECT p.login, f.friend.login
FROM person p, TABLE(p.friends) f
login
friend.login
a
b
a
c
c
a
Tableau 3 r1
1
2
SELECT p.login, p.hasfriends(), p.isfriendof()
FROM person p
login
hasfriends()
isfriendof()
a
2
1
b
0
1
c
1
1
43
Friends
Tableau 4 r2
Question
[Solution n°75 p 83]
Proposer une implémentation
correspondant à r1 et r2.
SQL3
sous
Oracle
et
préalable
pourra
être
insérer
les
données
Indice :
Une modélisation logique
méthodologiquement.
44
RO
posée
pour
s'aider
X-
E.T.
X
Exercice
52
Exercice
53
Un ufologue (quelqu'un qui étudie les ovnis et les extra-terrestres) décide de
construire une base de données pour enregistrer les observations effectuées sur
terre. Il pose le MCD suivant.
Image 3 MCD
L'indice de Roswell est obtenu par le calcul suivant :
indice=
nbyeux+ nbnez
nbdoigts
−
nbbouches
nbcouleurs
Formule 1
Le premier extra-terrestre observé est Alf :
il possède 2 yeux, 1 nez, 1 bouche
il est brun et jaune
et il possède 4 membres (2 mains et 2 pieds), de chacun 4 doigts.
Son indice de Roswell est de -5.
A. Exercice
[30 minutes]
Un ufologue (quelqu'un qui étudie les ovnis et les extra-terrestres) décide de
construire une base de données pour enregistrer les observations effectuées sur
terre. Il pose le MCD suivant.
45
E.T.
Image 4 MCD
L'indice de Roswell est obtenu par le calcul suivant :
indice=
nbyeux+ nbnez
nbdoigts
−
nbbouches
nbcouleurs
Formule 2
Le premier extra-terrestre observé est Alf :
il possède 2 yeux, 1 nez, 1 bouche
il est brun et jaune
et il possède 4 membres (2 mains et 2 pieds), de chacun 4 doigts.
Son indice de Roswell est de -5.
Cet exercice s'inscrit dans le cadre du modèle logique relationnel.
Question 1
[Solution n°76 p 85]
Traduire le MCD en relationnel.
Question 2
[Solution n°77 p 85]
Implémenter votre modèle R en SQL.
Question 3
[Solution n°78 p 85]
Ajouter l'extra-terrestre Alf dans la base de données.
Indice :
Assurez vous que toutes les informations concernant Alf sont bien insérées ou
bien aucune, même en cas de panne au milieu du script (NX17, vous pouvez
ignorer ce complément).
Question 4
[Solution n°79 p 85]
Écrivez la requête permettant d'afficher toutes les couleurs de Alf, puis la requête
permettant de connaître le nombre de couleurs de Alf.
Question 5
[Solution n°80 p 85]
Écrivez la requête permettant d'afficher tous les membres de Alf, puis la requête
permettant de connaître le nombre total de doigts de Alf.
Question 6
[Solution n°81 p 86]
Écrivez une fonction PHP permettant d'implémenter la méthode IndiceDeRoswell (la
46
E.T.
fonction prend en entrée une connexion $vConn et un nom d'extra-terrestre $vNom).
Indice :
1
2
3
4
5
function IndiceDeRoswell ($vConn, $vNom) {
// Écriture et exécution des requêtes
...
return vIndice ;
}
B. Exercice
Cet exercice est la suite du précédent, mais il s'inscrit dans le cadre du modèle
logique relationnel-objet.
Question 1
[Solution n°82 p 86]
Traduire le MCD en relationnel-objet.
Indice :
Complément au cours :
En RO, les compositions sont gérées avec le modèle imbriqué, à l'instar des
attributs multi-valués : en effet par définition l'élément composant ne peut être
partagé par plusieurs composites, le modèle imbriqué n'introduit donc pas de
redondance.
1. Créer un type correspondant au schéma de la table des composants
2. Créer une collection de ce type
3. Imbriquer cette collection dans la table des composites
Question 2
[Solution n°83 p 86]
Implémenter votre modèle RO en SQL3 sous Oracle (la méthode IndiceDeRoswell
n'est pas implémentée à ce stade, seul son prototype est déclaré).
Indice :
La contrainte CHECK pour restreindre le type de membre à 'main' ou 'pied' est
ignorée, l'attribut type est simplement instancié comme un CHAR(4).
Question 3
[Solution n°84 p 87]
Ajouter l'extra-terrestre Alf dans la base de données.
Question 4
[Solution n°85 p 87]
Écrivez la requête permettant d'afficher toutes les couleurs de Alf, puis la requête
permettant de connaître le nombre de couleurs de Alf.
Question 5
[Solution n°86 p 87]
Écrivez la requête permettant d'afficher tous les membres de Alf, puis la requête
permettant de connaître le nombre total de doigts de Alf.
47
E.T.
Question 6
[Solution n°87 p 87]
Écrivez la méthode IndiceDeRoswell renvoyant le résultat du calcul.
Question 7
[Solution n°88 p 88]
Écrivez la requête permettant d'afficher l'indice de Roswell de Alf.
48
Attributs multivalués
XI -
XI
Documentum
55
Fichier CSV
56
Insertion de collections à partir de requêtes SELECT
58
Attributs multi-valués et relationnel
59
Attributs multi-valués et relationnel-objet
61
A. Documentum
Le logiciel Documentum est un système de GED. Il s'appuie sur un SGBDR. Afin
d'optimiser ses performances lors d'accès aux méta-données, il implémente une
approche originale pour les attributs multi-valués.
Cela consiste à créer une table pour les attributs mono-valués (de façon classique)
et à ajouter une seconde table unique pour la gestion des attributs multi-valués.
Image 5 Modèle de gestion des attributs dans Documentum
49
Attributs multi-valués
Exemple
: Attributs correspondants à l'exemple précédent.
A ttributs
V aleurs
objec t_nam e
Doc num éro 1
r_objec t_ty pe
dm _doc um ent
authors
­ V inc ent
­ Rém y
k ey words
­ tes t
­ es s ai
­ doc
Tableau 5 Exemples d'attributs gérés dans Documentum
B. Fichier CSV
Définition : Fichier CSV
CSV est un format informatique permettant de stocker des données tabulaires
dans un fichier texte.
Chaque ligne du fichier correspond à une ligne du tableau. Les valeurs de chaque
colonne du tableau sont séparées par un caractère de séparation, en général une
virgule ou un point-virgule. Chaque ligne est terminée par un caractère de fin
de ligne (line break).
Toutes les lignes contiennent obligatoirement le même nombre de valeurs (donc
le même nombre de caractères de séparation). Les valeurs vides doivent être
exprimées par deux caractères de séparation contigus.
La taille du tableau est le nombre de lignes multiplié par le nombre de valeurs dans
une ligne.
La première ligne du fichier peut être utilisée pour exprimer le nom des colonnes.
Syntaxe
1
2
3
4
[NomColonne1;NomColonne2;...;NomColonneN]
ValeurColonne1;ValeurColonne2;...;ValeurColonneN
ValeurColonne1;ValeurColonne2;...;ValeurColonneN
...
Exemple
1
2
3
4
Pierre;Dupont;20;UTC;NF17
Pierre;Dupont;20;UTC;NF26
Paul;Durand;21;UTC;NF17
Jacques;Dumoulin;21;UTC;NF29
Exemple
1
2
50
: Fichier CSV sans entête
: Fichier CSV avec entête
Prenom;Nom;Age;Ecole;UV
Pierre;Dupont;20;UTC;NF17
Attributs multi-valués
3
4
5
Pierre;Dupont;20;UTC;NF26
Paul;Durand;21;UTC;NF17
Jacques;Dumoulin;21;UTC;NF29
Exemple
1
: Valeur nulle
Jacques;Dumoulin;;UTC;NF29
L'âge est inconnu (NULL).
Attention : Variations...
La syntaxe des fichiers CSV n'est pas complètement standardisée, aussi des
variations peuvent exister :
Les chaînes de caractères peuvent être protégées par des guillemets (les
guillemets s'expriment alors avec un double guillemet).
Le caractère de séparation des nombres décimaux peut être le point ou la
virgule (si c'est la virgule, le caractère de séparation doit être différent)
...
Un des problème les plus importants reste l'encodage des caractères qui n'est pas
spécifié dans le fichier et peut donc être source de problèmes, lors de changement
d'OS typiquement.
Méthode
: Usage en base de données
Les fichiers CSV sont très utilisés en BD pour échanger les données d'une table
(export/import).
Les SGBD contiennent généralement des utilitaires permettant d'exporter une
table ou un résultat de requête sous la forme d'un fichier CSV, en spécifiant un
certain nombre de paramètres (caractère de séparation de valeur, caractère de fin
de ligne, présence ou non d'une ligne de définition des noms des colonnes, etc.).
De même ils proposent des utilitaires permettant d'importer un fichier CSV dans
une table (en spécifiant les mêmes paramètres), voire de créer directement une
table à partir du fichier CSV (quand les noms des colonnes sont présents).
Complément
: Fichiers à largeur de colonne fixe
Les fichiers à largeur de colonne fixe n'utilisent pas de séparateur de colonne, mais
imposent le même nombre de caractères pour chaque cellule. L'avantage est de
ne pas avoir à spécifier le caractère de séparation, l'inconvénient est la taille de
fichier supérieure si les valeurs ne font pas toutes la même largeur.
Complément
: XML
Les fichiers XML tendent de plus en plus à remplacer les fichiers CSV car ils
permettent d'être beaucoup plus expressifs sur le schéma d'origine. Ils sont
également plus standards (encodage spécifié, principe de séparation des données
par les tags, etc.). Leur seul inconvénient est d'être plus verbeux et donc plus
volumineux.
Complément
: Tables externes
Certains SGBD, comme Oracle, permettent de créer des tables dites externes, qui
autorisent de créer un schéma de table directement sur un fichier CSV,
permettant ainsi un accès SQL standard à un fichier CSV, sans nécessité de
l'importer d'abord dans une table.
51
Attributs multi-valués
C. Insertion de collections à partir de requêtes
SELECT
Les constructeurs d'objets sont utilisables dans les instructions de type
INSERT ... VALUES, mais ne peuvent être utilisés dans les instructions de type
INSERT ... SELECT. Pour ces dernières une autre méthode de construction est
nécessaire.
Syntaxe : Insérer une collection d'objets depuis une table
Soient les tables tro et t définies par le schéma RO ci-après.
1
2
3
type col : collection de <string>
tro(a_obj:col)
t(a:string)
L'instruction suivante permet d'insérer le contenu de l'attribut a pour tous les
enregistrements de t dans un seul attribut a_obj d'un seul enregistrement de tro
sous la forme d'une collection.
1
2
INSERT INTO tro (a_objet)
VALUES (CAST(MULTISET(SELECT a FROM t
) AS col));
L'instruction CAST (...) AS <type> permet de renvoyer un type donné à
partir d'une expression.
L'instruction MULTISET (...) permet de renvoyer une collection à partir
d'une liste de valeurs.
Méthode
: Approche générale
Soit la table tro définie par le schéma RO ci-après. Elle contient un attribut clé
(pk_id) et un attribut qui est une collection (a_obj).
1
2
type col : collection de <string>
tro(#pk_id:string, a_obj:col)
Soit la table tr définie par le schéma relationnel ci-après.
1
tr(a:string, b:string)
a1
b1
a1
b2
a1
b3
a2
b4
a2
b5
Tableau 6 Extrait de tr
Si l'on souhaite insérer le contenu de tr dans tro de telle façon que les valeurs de
a correspondent à pk_id et celles de b à obj_a, en insérant une ligne pour chaque
valeur ax et que tous les bx correspondant soient stockés dans la même collection,
il faut exécuter une requête INSERT ... SELECT qui fait appel aux instructions
CAST et MULTISET.
52
Attributs multi-valués
1
2
3
4
5
6
7
8
9
10
INSERT INTO tro (pk_id, a_obj)
SELECT
a,
CAST(
MULTISET(
SELECT b
FROM tr tr1
WHERE tr1.a= tr2.a)
AS col)
FROM tr tr2;
a1
(b1, b2, b3)
a2
(b4, b5)
Tableau 7 Extrait de tro après exécution de la requête INSERT
D. Attributs multi-valués et relationnel
[1h]
Mise en contexte
Jeune stagiaire dans une entreprise, vous vous voyez attribuer la mission suivante :
dans le cadre de la migration d'un système de GED, vous devez récupérer des
méta-données stockées dans un système Documentum pour les gérer dans une
base PostgreSQL relationnelle classique.
Le premier jour, vous vous voyez remettre deux fichiers CSV ("documents.csv" et
"documentsMV.csv"), extractions de la base Documentum, dont les premières
lignes sont données ci-après.
documents.csv
1
2
3
4
object_id,titre,categorie,annee
9e8e,La Théorie de la relativité,Sciences,1923
9f5a,L'origine des espèces,Sciences,1859
9a0b,Le voyage au bout de la nuit,Littérature,1932
documentsMV.csv
1
2
3
4
5
6
7
object_id,auteurs,themes,langues
9e8e,Albert Einstein,Énergie nucléaire,Allemand
9e8e,,Physique quantique,
9f5a,,Théorie de l'évolution,Français
9f5a,,Vie,Anglais
9f5a,,Sélection naturelle,
9a0b, Louis-Ferdinand Céline,, Français
Question 1
[Solution n°89 p 88]
Établissez le modèle relationnel R1 d'une base de données qui pourra accueillir ces
deux fichiers (représentez les clés existantes et précisez en quelle forme normale
est ce modèle).
Question 2
[Solution n°90 p 88]
Proposez une formulation du problème sous la forme d'un modèle conceptuel en
UML (en mobilisant la modélisation d'attribut multi-valué de façon classique).
53
Attributs multi-valués
Question 3
[Solution n°91 p 89]
Transformer ce modèle conceptuel en un modèle logique relationnel "classique" R2
(sans tenir compte de la particularité de gestion de Documentum, donc en
transformant classiquement le schéma UML).
Question 4
[Solution n°92 p 89]
Écrivez le code SQL de création de R1 et de R2.
Question 5
[Solution n°93 p 89]
Écrivez la requête de migration des attributs mono-valués de R1 vers R2.
Question 6
[Solution n°94 p 90]
Écrivez les requêtes de migration des attributs multi-valués de R1 vers R2.
Sécurisation de la migration
Avant d'exécuter la migration, l'on vous demande de mettre en place des solutions
pour la contrôler.
Question 7
[Solution n°95 p 90]
Afin de pouvoir vérifier la migration dans R2, écrivez une vue qui permet d'afficher
un enregistrement pour chaque document de R1, avec tous ses attributs monovalués et pour chaque attribut multi-valué, le nombre de valeurs existantes
(object_id, titre, categorie, annee, nbauteurs, nbthemes, nblangues).
Question 8
[Solution n°96 p 90]
Expliquez comment assurer que même en cas de panne la cohérence de la BD
pourra être préservée lors de la migration de R1 vers R2. Complétez les requêtes
de migration que vous avez réalisées à cet effet.
Interface de recherche
Une fois vos données migrées, il vous est demandé de réaliser un petit programme
PHP permettant de faire une recherche dans la base PostgreSQL. Le principe du
programme est le suivant :
1. Il affiche une page HTML avec un formulaire permettant de saisir un unique
mot à rechercher à la fois dans le titre et parmi les auteurs.
2. Il appelle une page PHP qui se connecte à la base de données, exécute une
requête SQL et affiche l'identifiant du ou des documents dont le titre ou un
des auteurs comprend au moins ce mot.
Question 9
[Solution n°97 p 90]
Écrivez le fichier du formulaire HTML.
Question 10
[Solution n°98 p 90]
Écrivez, pour vous préparer à écrire le programme PHP, la requête SQL à exécuter
au sein de celui-ci.
Question 11
[Solution n°99 p 91]
Écrivez le fichier PHP appelé par le formulaire HTML (pour les paramètres de
connexion, vous pouvez reprendre ceux de l'UTC).
54
Attributs multi-valués
E. Attributs multi-valués et relationnel-objet
[1h]
Mise en contexte
Dans la perspective d'une implémentation sous Oracle 9i, vous devez étudier à
présent la même réalisation en relationnel-objet.
Question 1
[Solution n°100 p 91]
Proposer un modèle logique relationnel-objet "classique" R3.
Écrivez le code de création SQL3 de ce modèle.
Indice :
Utilisez le modèle imbriqué.
Question 2
[Solution n°101 p 91]
Écrivez l'instruction SQL de migration R1 vers R3.
Indice :
Utilisez la syntaxe INSERT ... SELECT avec CAST et MULTISET.
Question 3
[Solution n°102 p 92]
Écrivez une requête SQL permettant d'afficher les titres des documents (dans
l'ordre alphabétique), ainsi que tous les thèmes associés à ce titre. L'on recherchera
la forme ci-après afin d'envisager un nouvel export CSV.
1
2
3
4
5
6
L'origine des
L'origine des
L'origine des
La Théorie de
La Théorie de
...
espèces,Théorie de l'évolution
espèces,Vie
espèces,Sélection naturelle
la relativité,Énergie nucléaire
la relativité,Physique quantique
Proposer également une instruction SQL permettant d'optimiser la requête
précédente.
Indice :
Pour obtenir un fichier CSV en sortie, utilisez l'opérateur de concaténation || :
SELECT a || ',' || b ...
55
Solution des
exercices
> Solution n°1 (exercice p. 9)
UML fautes solution
> Solution n°2 (exercice p. 9)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
Modèle(#code : char(8), nom : string, dateMiseEnMarché : date)
avec dateMiseEnMarché {NOT NULL}
Produit(#numeroSérie : number(6), #numéroProduit : char(4),
annéeProduction : uint, modèle => Modèle)
avec annéeProduction {NOT NULL}
avec modèle {NOT NULL}
Catégorie(#nom : string, description : string)
Souscatégorie(#nom : string, description : string, catégorie =>
Catégorie)
avec catégorie {NOT NULL}
Faute(#code : string, titre : string, dateÉtablie : date, dateReparée
: date, commentaire : string, produitSérie => Produit(numéroSérie),
produitNuméro => Produit(numéorProduit))
avec titre {NOT NULL}
avec dateÉtablie {NOT NULL}
avec produitSérie {NOT NULL}
avec produitNuméro {NOT NULL}
FauteSouscatégorie(#faute => Faute(code), souscatégorie =>
Souscatégorie(nom))
57
Solution des exercices
15
PROJ(Faute, code) IN PROJ(FauteSouscatégorie, faute)
> Solution n°3 (exercice p. 9)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
CREATE TABLE tModele
(
code CHAR(8) PRIMARY KEY,
nom VARCHAR NOT NULL,
dateMiseEnMarche DATE
);
CREATE TABLE tProduit
(
numeroSerie NUMERIC(6) NOT NULL,
numeroProduit CHAR(4) NOT NULL,
anneeProduction INTEGER NOT NULL,
modele CHAR(8) NOT NULL,
PRIMARY KEY (numeroSerie, numeroProduit),
FOREIGN KEY (modele) REFERENCES tModele(code) ON DELETE CASCADE
);
CREATE TABLE tCategorie
(
nom VARCHAR PRIMARY KEY,
description VARCHAR
);
CREATE TABLE tSouscategorie
(
nom VARCHAR PRIMARY KEY,
description VARCHAR
);
CREATE TABLE tFaute
(
code VARCHAR PRIMARY KEY,
titre VARCHAR NOT NULL,
dateEtablie DATE NOT NULL,
dateReparee DATE,
commentaire VARCHAR,
produitSerie NUMERIC(6) NOT NULL,
produitNumero CHAR(4) NOT NULL,
FOREIGN KEY (produitSerie, produitNumero) REFERENCES
tProduit(numeroSerie, numeroProduit) ON DELETE CASCADE
);
CREATE TABLE tFauteSouscategorie
(
faute VARCHAR NOT NULL,
souscategorie VARCHAR NOT NULL,
PRIMARY KEY (faute, souscategorie),
FOREIGN KEY (faute) REFERENCES tFaute(code) ON DELETE CASCADE,
FOREIGN KEY (souscategorie) REFERENCES tSouscategorie(nom) ON
DELETE CASCADE
);
> Solution n°4 (exercice p. 9)
58
1
2
3
4
5
6
7
SELECT m.nom, p.numeroSerie, COUNT(*)
FROM tModele m, tProduit p, tFaute f
WHERE m.code = p.modele
AND p.numeroSerie = f.produitSerie
AND p.numeroProduit = f.produitNumero
GROUP BY m.nom, p.numeroSerie
;
1
SELECT m.nom, p.numeroSerie, COUNT(*)
Solution des exercices
2
3
4
5
6
7
8
FROM (tModele m
JOIN tProduit p
ON m.code = p.modele)
JOIN tFaute f
ON (p.numeroSerie = f.produitSerie AND p.numeroProduit =
f.produitNumero)
GROUP BY m.nom, p.numeroSerie
;
> Solution n°5 (exercice p. 11)
Aucune, il n'y a pas de clé et l'attribut localisation n'est pas atomique, donc ce n'est
même pas en 1NF.
> Solution n°6 (exercice p. 11)
1
projection(jointure(personne,personne,localisation=localisation),
nom)
> Solution n°7 (exercice p. 11)
1
2
3
4
5
6
BEGIN TRANSACTION ;
DELETE FROM personne_backup ;
INSERT INTO personne_backup (nom, prenom, localisation, departement,
unite)
SELECT nom, prenom, localisation, departement, unite
FROM personne ;
COMMIT ;
> Solution n°8 (exercice p. 11)
1
CREATE INDEX myindex ON Personne (Nom, Prenom) ;
> Solution n°9 (exercice p. 11)
1
2
3
4
5
6
CREATE TRIGGER trPersonne
BEFORE INSERT OR UPDATE OF departement ON Personne
FOR EACH ROW
BEGIN
:new.departement=UPPER(:new.departement) ;
END ;
> Solution n°10 (exercice p. 13)
1
2
3
4
5
6
7
<?php
//définition des variables de connexion à ajouter
$conn=mysql_connect($host,$user,$passwd);
mysql_select_db($bdd, $conn);
$query = "SELECT nom, intitule FROM tPersonne, tMetier WHERE
metier=code";
$result=mysql_query($query, $conn);
echo "<html>";
59
Solution des exercices
8
9
10
11
12
13
14
while($row = mysql_fetch_row($result))
{
echo "<p>".$row[nom]." ".$row[intitule]."</p>";
}
echo "</html>";
mysql_close($conn);
?>
> Solution n°11 (exercice p. 13)
1
2
3
4
5
6
7
8
9
10
11
12
13
<?php
//définition des variables de connexion à ajouter
$conn=pg_connect($host,$user,$passwd,$bdd);
$query = "SELECT nom, intitule FROM tPersonne, tMetier WHERE
metier=code";
$result=pg_query($conn, $query);
echo "<html>";
while($row = pg_fetch_row($result))
{
echo "<p>".$row[nom]." ".$row[intitule]."</p>";
}
echo "</html>";
pg_close($conn);
?>
> Solution n°12 (exercice p. 13)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
<?php
//définition des variables de connexion à ajouter
$conn=oci_connect($user, $passwd, $bd))
$query = "SELECT nom, intitule FROM tPersonne, tMetier WHERE
metier=code";
$statement = oci_parse($conn, $query);
oci_execute($statement);
echo "<html>";
while($row = oci_fetch_array($statement))
{
echo "<p>".$row[nom]." ".$row[intitule]."</p>";
}
echo "</html>";
oci_close($conn);
?>
> Solution n°13 (exercice p. 13)
1
2
3
4
5
6
7
8
Sub printMe()
vSql = "SELECT nom, intitule FROM tPersonne, tMetier WHERE
metier=code"
Set vRs = CurrentDb.CreateQueryDef("", vSql).OpenRecordset
For i = 1 To vRs.RecordCount
Debug.Print vRs!nom & " " & vRs!intitule
vRs.MoveNext
Next i
End Sub
> Solution n°14 (exercice p. 14)
1
60
SET SERVEROUTPUT ON;
Solution des exercices
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE
CURSOR printMe IS
SELECT nom, intitule FROM tPersonne, tMetier WHERE metier=code;
vNom tPersonne.nom%TYPE;
vIntitule tMetier.intitule%TYPE;
BEGIN
OPEN printMe;
LOOP
FETCH printMe INTO vNom, vIntitule;
EXIT WHEN printMe%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' ' || vNom || ' ' || vIntitule);
END LOOP;
END;
> Solution n°15 (exercice p. 14)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
package nf17;
import java.sql.*;
import oracle.jdbc.OracleDriver;
public class Exercice {
public static void main(String[] args) {
try {
DriverManager.registerDriver (new OracleDriver());
Connection vCon =
DriverManager.getConnection("jdbc:oracle:thin:nf17/nf17@localhost:152
1:test");
Statement vSt = vCon.createStatement();
String vSql = "SELECT nom, intitule FROM tPersonne, tMetier WHERE
metier=code";
ResultSet vRs = vSt.executeQuery(vSql);
while(vRs.next()) {
System.out.println(vRs.getString(1)+" "+vRs.getString(2));
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
> Solution n°16 (exercice p. 20)
Compte
Opération
num
categorie
________________
=solde()
date
montant
annee
Dépense
Recette
Graphique 2 Modèle UML
Il est également possible de faire intervenir une relation d'héritage à la place de
l'attribut catégorie pour discriminer les comptes A et R.
> Solution n°17 (exercice p. 21)
Solution des exercices
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE TABLE Compte (
num INTEGER(2) PRIMARY KEY,
categorie CHAR(1) CHECK (categorie='A' OR 'R')
);
CREATE TABLE Recette (
num INTEGER(2) REFERENCES Compte(num),
date DATETIME,
montant DECIMAL(9,2),
annee INTEGER(4),
PRIMARY KEY (num, date)
) ;
CREATE TABLE Depense (
num INTEGER(2) REFERENCES Compte(num),
date DATETIME,
montant DECIMAL(9,2),
annee INTEGER(4),
PRIMARY KEY (num, date)
) ;
> Solution n°18 (exercice p. 22)
S1 = RESTRICTION (Compte, categorie='R')
S2 = JOINTURENATURELLE (S1, Depense)
S3 = PROJECTION (S2, num)
> Solution n°19 (exercice p. 22)
1
2
3
4
5
SELECT num
FROM Recette
GROUP BY num
HAVING SUM(montant)>1000000
> Solution n°20 (exercice p. 22)
1
2
3
4
5
BEGIN TRANSACTION
INSERT INTO Recette (222, '2006/06/20 12:02', 100, 2006)
INSERT INTO Depense (125, '2006/06/20 12:02', 100, 2006)
COMMIT TRANSACTION
> Solution n°21 (exercice p. 25)
1
2
3
4
5
Type Operation <date:date, montant:reel, =annee():entier>
Type listOperations collection de <Opération>
Type Compte <num:entier, categorie:{A|R}, recettes:listOperations,
depenses:listOperations, =solde():reel>
tCompte de Compte (#num)
> Solution n°22 (exercice p. 25)
62
Solution des exercices
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TYPE Operation AS OBJECT (
date DATETIME,
montant DECIMAL(9,2),
MEMBER FUNCTION annee RETURN INTEGER
);
/
CREATE TYPE listOperations AS TABLE OF Operation;
/
CREATE TYPE Compte AS OBJECT (
num INTEGER(2),
categorie CHAR(1),
recettes listOperations,
depenses listOperations,
MEMBER FUNCTION solde RETURN DECIMAL
);
/
CREATE TABLE tCompte OF Compte (
PRIMARY KEY (num),
CHECK (categorie = 'A' OR 'B')
) NESTED TABLE recettes STORE AS tRecettes,
NESTED TABLE depenses STORE AS tDepenses;
> Solution n°23 (exercice p. 25)
1
2
3
4
5
6
7
8
CREATE TYPE BODY Operation
IS
MEMBER FUNCTION annee RETURN INTEGER IS
BEGIN
RETURN YEAR(self.date) ;
END ;
END ;
> Solution n°24 (exercice p. 25)
1
2
3
4
5
6
7
8
9
10
CREATE TYPE BODY Compte IS
MEMBER FUNCTION solde RETURN INTEGER IS
vR DECIMAL;
vD DECIMAL;
BEGIN
SELECT SUM(R.montant) INTO vR FROM TABLE(self.recettes) R
SELECT SUM(D.montant) INTO vD FROM TABLE(self.depenses) D
RETURN vR-vD;
END ;
> Solution n°25 (exercice p. 25)
1
2
3
4
5
6
7
8
9
INSERT INTO tCompte (num, categorie, recettes) VALUES
(
1,
'A',
listOperations(
Operation(TO_DATE('20070720 11:31','YYYYMMDD HH:MI'), 100)
)
) ;
> Solution n°26 (exercice p. 25)
Solution des exercices
1
2
SELECT c.num, c.solde() FROM tCompte c;
> Solution n°27 (exercice p. 26)
MR2rc
MR2ro
Compte(num:entier, categorie:{A|R})
CompteSig(num=>Compte, signature:chaine)
CompteProc(num=>Compte, procuration:chaine)
Type listNoms collection de <Chaine>
Type
Compte
<num:entier,
categorie:{A|R},
procurations:listNoms>
Table tCompte de Compte (num)
signatures:listNoms,
MR2rd
Compte(num:entier, categorie:{A|R})
CompteMV(num=>Compte, signature:chaine, procuration:chaine)
> Solution n°28 (exercice p. 26)
Avantage : Moins de tables, donc moins de jointures lorsqu'il y a plusieurs
attributs multi-valués
Inconvénient : Apparition de valeurs nulles (l'attribut comportant le plus de
valeurs détermine le nombre de lignes total dans la table des attributs mulivalués)
> Solution n°29 (exercice p. 27)
1
2
3
4
diplome (#code:char(3), intitule:varchar) avec intitule KEY
etudiant (#num:char(10), nom:varchar, origine=>diplome) avec origine
NOT NULL
uv (#code:char(5), titre:varchar) avec titre KEY
note (#etu=>etudiant, #uv=>uv, #semestre:char(5), resultat:integer)
> Solution n°30 (exercice p. 27)
1
2
3
4
5
6
7
CREATE VIEW vmax1 (uv, diplome, semestre, max) AS
SELECT u.titre, d.intitule, n.semestre, max(n.resultat)
FROM etudiant e, uv u, note n, diplome d
WHERE e.num=n.etu
AND u.code=n.uv
AND e.origine=d.code
GROUP BY u.titre, d.intitule, annee;
> Solution n°31 (exercice p. 28)
Le choix des clés primaires oblige à faire deux jointures dans la vue pour récupérer
le titre des UV et intitulé des diplômes. En choisissant ces informations comme clé,
on évite ces jointures.
64
Solution des exercices
1
2
3
4
etudiant (#num:char(10), nom:varchar, origine=>diplome)
uv (#titre:varchar, code:char(5)) avec code KEY
note (#etu=>etudiant, #uv=>uv, #semestre:char(5), note:integer)
diplome (#intitule:varchar, code:char(3)) avec code KEY
1
2
3
4
5
CREATE VIEW vmax2 (uv, diplome, semestre, max) AS
SELECT n.uv, e.origine, semestre, max(n.note)
FROM etudiant e, note n
WHERE e.num=n.etu
GROUP BY n.uv, e.origine, annee;
> Solution n°32 (exercice p. 28)
1
2
3
4
5
6
7
8
9
10
<html xmlns="http://www.w3.org/1999/xhtml">
<body>
<?php
$vConn=fConnect();
$vSql="SELECT * FROM vmax1 WHERE semestre='P2014' OR
semestre='A2014'";
$vQuery=pg_query($vConn, $vSql);
while ($vResult = pg_fetch_array($vQuery)) {
echo "<p>$vResult['uv'] - vResult['diplome'] - vResult['semestre']
- vResult['max'] - </p>";
}
?>
> Solution n°33 (exercice p. 28)
1
2
3
etudiant (#num:char(10), nom:varchar, origine=>diplome, #uv=>uv,
#semestre:char(5), resultat:integer) avec num→nom, num→origine et
avec intitule NOT NULL
uv (#titre:varchar, code:char(5)) avec code KEY
diplome (#intitule:varchar, code:char(3)) avec code KEY
1
2
3
4
CREATE VIEW vmax3 (uv, diplome, annee, max) AS
SELECT uv, origine, annee, max(n.resultat)
FROM etudiant
GROUP BY uv, origine, annee;
Remarque
On aurait pu nommer la table dénormalisée note à la place de etudiant
(par exemple).
L'explicitation des DF est obligatoire
Rappel
Les trois principes à respecter pour introduire de la redondance dans une base de
données (cf. Les trois principes à respecter pour introduire de la redondance dans
une base de données)
> Solution n°34 (exercice p. 28)
1
CREATE TRIGGER tretudiant
65
Solution des exercices
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
BEFORE INSERT OR UPDATE
ON etudiant
FOR EACH ROW
DECLARE
vnom etudiant.nom%TYPE;
vorigine etudiant.origine%TYPE;
BEGIN
/** On récupère les informations éventuellement déjà
existantes concernant l'étudiant inséré ou mis à jour ;
on utilise la fonction max pour ne retourner qu'un seul tuple **/
SELECT max(e.nom), max(e.origine)
INTO vnom, vorigine
FROM etudiant e
WHERE e.num=:new.num;
/** On teste que ces valeurs sont les mêmes que celles
nouvellement insérées ou mises à jour et sinon on
insère une alterte dans la table 'etucontrole' **/
IF (:new.nom!=vnom OR :new.origine!=vorigine) THEN
INSERT INTO etucontrole(num)
VALUES (:new.num);
END IF;
END;
> Solution n°35 (exercice p. 29)
1
2
3
4
5
6
7
8
9
CREATE TABLE etucontrole (
num char(10) REFERENCES etudiant(num) PRIMARY KEY
);
SELECT e.num, e.nom, e.origine
FROM etucontrole c, etudiant e
WHERE c.num=e.num
ORDER BY e.num;
> Solution n°36 (exercice p. 29)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
66
BEGIN TRANSACTION;
DELETE FROM etucontrole
WHERE num='1';
UPDATE etudiant
SET nom='Turing', origine='TC UTC'
WHERE num='1';
COMMIT;
BEGIN TRANSACTION;
DELETE FROM etucontrole
WHERE num='3';
UPDATE etudiant
SET nom='von Neumann', origine='DUT Budapest'
Solution des exercices
19
20
21
WHERE num='3';
COMMIT;
Il faut corriger la table etudiant, et supprimer les incohérences de la liste
gérée dans etucontrole.
Les transactions permettent d’assurer l'atomicité de l'opération, sinon on
pourrait, par exemple, supprimer les enregistrements de la table
etucontrole, sans faire les correction dans etudiant.
Le DELETE doit être avant le UPDATE, car le UPDATE pourra engendrer de
nouvelles incohérences.
> Solution n°37 (exercice p. 29)
Solution avec le modèle imbriqué (table Notes imbriquée dans la table Étudiant,
mais il est aussi possible d'imbriquer la table Notes dans la table UV).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
CREATE OR REPLACE TYPE typDiplome AS OBJECT (
code CHAR(8),
intitule VARCHAR(255)
) ;
/
CREATE OR REPLACE TYPE typUV AS OBJECT (
code CHAR(8),
titre VARCHAR(255)
) ;
/
CREATE OR REPLACE TYPE typNote AS OBJECT (
fk_UV REF typUV,
semestre CHAR(5),
resultat INTEGER
) ;
/
CREATE OR REPLACE TYPE typNotes AS TABLE OF typNote;
/
CREATE OR REPLACE TYPE typEtudiant AS OBJECT (
num CHAR(10),
nom VARCHAR(255),
notes typNotes,
fk_origine REF typDiplome
) ;
/
CREATE TABLE Diplome OF typDiplome (
CONSTRAINT DIPLOME_PK PRIMARY KEY (code),
CONSTRAINT INTITULE_UN UNIQUE (intitule),
intitule NOT NULL
) ;
/
CREATE TABLE UV OF typUV (
CONSTRAINT UV_PK PRIMARY KEY (code),
CONSTRAINT TITRE_UN UNIQUE (titre),
titre NOT NULL
) ;
/
CREATE TABLE Etudiant OF typEtudiant (
CONSTRAINT ETUDIANT_PK PRIMARY KEY (num),
fk_origine NOT NULL,
SCOPE FOR (fk_origine) IS Diplome
)
NESTED TABLE notes STORE AS NT_ETUDIANT_NOTES ;
/
ALTER TABLE NT_ETUDIANT_NOTES ADD (SCOPE FOR (fk_UV) IS UV) ;
/
Comme il n'est pas possible de passer des paramètres dans une instruction Nested
table, le SCOPE FOR est exécuté a posteriori via un ALTER TABLE.
Un jeu de données pour tester l'implémentation
Solution des exercices
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
68
INSERT INTO Diplome (code, intitule) VALUES ('DUTINFO', 'DUT
Informatique');
INSERT INTO Diplome (code, intitule) VALUES ('CPGEMATH', 'Classe
préparatoire aux grandes écoles mathématiques, physique');
INSERT INTO Diplome (code, intitule) VALUES ('TC', 'Tronc commun');
INSERT INTO UV (code, titre) VALUES ('NF17', 'Conception de bases de
données');
INSERT INTO UV (code, titre) VALUES ('NF26', 'Data Warehouse et
Outils décisionnels');
INSERT INTO UV (code, titre) VALUES ('NF29', 'Ingénierie
documentaire');
DECLARE
ref_diplome_tc REF typDiplome;
ref_diplome_dut REF typDiplome;
ref_diplome_cpge REF typDiplome;
ref_uv_nf17 REF typUV;
ref_uv_nf26 REF typUV;
ref_uv_nf29 REF typUV;
BEGIN
SELECT REF(d) INTO ref_diplome_dut
FROM Diplome d
WHERE d.code = 'DUTINFO' ;
SELECT REF(d) INTO ref_diplome_cpge
FROM Diplome d
WHERE d.code = 'CPGEMATH' ;
SELECT REF(d) INTO ref_diplome_tc
FROM Diplome d
WHERE d.code = 'TC' ;
SELECT REF(u) INTO ref_uv_nf17
FROM UV u
WHERE u.code = 'NF17' ;
SELECT REF(u) INTO ref_uv_nf26
FROM UV u
WHERE u.code = 'NF26' ;
SELECT REF(u) INTO ref_uv_nf29
FROM UV u
WHERE u.code = 'NF29' ;
INSERT INTO Etudiant (num, nom, notes, fk_origine) VALUES (
'1',
'Crozat',
typNotes(
typNote(ref_uv_nf17, 'P2013', 6),
typNote(ref_uv_nf26, 'P2014', 10),
typNote(ref_uv_nf29, 'A2014', 8)
),
ref_diplome_tc
);
INSERT INTO Etudiant (num, nom, notes, fk_origine) VALUES (
'2',
'Dumas',
typNotes(
typNote(ref_uv_nf17, 'P2014', 16),
typNote(ref_uv_nf29, 'A2014', 14)
),
ref_diplome_cpge
);
Solution des exercices
64
65
66
67
68
69
70
71
72
73
INSERT INTO Etudiant (num, nom, notes, fk_origine) VALUES (
'3',
'Vincent',
typNotes(
typNote(ref_uv_nf17, 'P2014', 21)
),
ref_diplome_dut
);
END ;
Quelques requêtes pour afficher certaines informations de la base.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Pour afficher quelques informations
SELECT e.nom, n.fk_uv.titre as uv, n.semestre, n.resultat,
e.fk_origine.intitule as origine
FROM Etudiant e, TABLE(notes) n
ORDER BY e.nom ;
-- Affiche la moyenne de chaque étudiant
SELECT e.nom, AVG(n.resultat) as moyenne
FROM Etudiant e, TABLE(notes) n
GROUP BY e.num, e.nom
ORDER BY moyenne DESC;
-- Affiche la moyenne des étudiants qui ont au moins trois notes
SELECT e.nom, AVG(n.resultat) as moyenne
FROM Etudiant e, TABLE(notes) n
GROUP BY e.num, e.nom
HAVING COUNT(n.resultat) > 2;
Solution en gardant la classe d'association.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
CREATE OR REPLACE TYPE typDiplome AS OBJECT (
code CHAR(8),
intitule VARCHAR(255)
) ;
/
CREATE OR REPLACE TYPE typUV AS OBJECT (
code CHAR(8),
titre VARCHAR(255)
) ;
/
CREATE OR REPLACE TYPE typEtudiant AS OBJECT (
num CHAR(10),
nom VARCHAR(255),
fk_origine REF typDiplome
) ;
/
CREATE OR REPLACE TYPE typNote AS OBJECT (
semestre CHAR(5),
resultat INTEGER,
fk_etudiant REF typEtudiant,
fk_uv REF typUV
) ;
/
CREATE TABLE Diplome OF typDiplome (
CONSTRAINT DIPLOME_PK PRIMARY KEY (code),
CONSTRAINT INTITULE_UN UNIQUE (intitule),
intitule NOT NULL
) ;
/
CREATE TABLE UV OF typUV (
CONSTRAINT UV_PK PRIMARY KEY (code),
CONSTRAINT TITRE_UN UNIQUE (titre),
titre NOT NULL
) ;
/
CREATE TABLE Etudiant OF typEtudiant (
CONSTRAINT ETUDIANT_PK PRIMARY KEY (num),
SCOPE FOR (fk_origine) IS Diplome,
fk_origine NOT NULL
) ;
Solution des exercices
41
42
43
44
45
46
47
/
CREATE TABLE Note OF typNote (
SCOPE FOR (fk_etudiant) IS Etudiant,
SCOPE FOR (fk_uv) IS UV,
semestre NOT NULL
) ;
/
Il est impossible sous Oracle de créer une clé primaire constitué d'un attribut REF,
nous ne pouvons donc pas fixer de clé primaire pour la table Note, alors qu'il
faudrait dans l'idéal qu'elle soit constitué de fk_etudiant, fk_uv et semestre.
Un jeu de données pour tester l'implémentation
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
70
INSERT INTO Diplome (code, intitule) VALUES ('DUTINFO', 'DUT
Informatique');
INSERT INTO Diplome (code, intitule) VALUES ('CPGEMATH', 'Classe
préparatoire aux grandes écoles mathématiques, physique');
INSERT INTO Diplome (code, intitule) VALUES ('TC', 'Tronc commun');
INSERT INTO UV (code, titre) VALUES ('NF17', 'Conception de bases de
données');
INSERT INTO UV (code, titre) VALUES ('NF26', 'Data Warehouse et
Outils décisionnels');
INSERT INTO UV (code, titre) VALUES ('NF29', 'Ingénierie
documentaire');
DECLARE
ref_diplome_tc REF typDiplome;
ref_diplome_dut REF typDiplome;
ref_diplome_cpge REF typDiplome;
BEGIN
SELECT REF(d) INTO ref_diplome_dut
FROM Diplome d
WHERE d.code = 'DUTINFO' ;
SELECT REF(d) INTO ref_diplome_cpge
FROM Diplome d
WHERE d.code = 'CPGEMATH' ;
SELECT REF(d) INTO ref_diplome_tc
FROM Diplome d
WHERE d.code = 'TC' ;
INSERT INTO Etudiant (num, nom, fk_origine) VALUES (
'1',
'Crozat',
ref_diplome_tc
);
INSERT INTO Etudiant (num, nom, fk_origine) VALUES (
'2',
'Dumas',
ref_diplome_cpge
);
INSERT INTO Etudiant (num, nom, fk_origine) VALUES (
'3',
'Vincent',
ref_diplome_dut
);
END ;
/
DECLARE
ref_etudiant_1 REF typEtudiant;
Solution des exercices
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
ref_etudiant_2 REF typEtudiant;
ref_etudiant_3 REF typEtudiant;
ref_uv_nf17 REF typUV;
ref_uv_nf26 REF typUV;
ref_uv_nf29 REF typUV;
BEGIN
SELECT REF(e) INTO ref_etudiant_1
FROM Etudiant e
WHERE e.num = '1' ;
SELECT REF(e) INTO ref_etudiant_2
FROM Etudiant e
WHERE e.num = '2' ;
SELECT REF(e) INTO ref_etudiant_3
FROM Etudiant e
WHERE e.num = '3' ;
SELECT REF(u) INTO ref_uv_nf17
FROM UV u
WHERE u.code = 'NF17' ;
SELECT REF(u) INTO ref_uv_nf26
FROM UV u
WHERE u.code = 'NF26' ;
SELECT REF(u) INTO ref_uv_nf29
FROM UV u
WHERE u.code = 'NF29' ;
INSERT INTO Note (semestre, resultat, fk_etudiant, fk_uv) VALUES
('P2013', 6, ref_etudiant_1, ref_uv_nf17) ;
INSERT INTO Note (semestre, resultat, fk_etudiant, fk_uv) VALUES
('P2014', 10, ref_etudiant_1, ref_uv_nf26) ;
INSERT INTO Note (semestre, resultat, fk_etudiant, fk_uv) VALUES
('A2014', 8, ref_etudiant_1, ref_uv_nf29) ;
INSERT INTO
('P2014', 16,
INSERT INTO
('A2014', 14,
Note (semestre,
ref_etudiant_2,
Note (semestre,
ref_etudiant_2,
resultat, fk_etudiant, fk_uv) VALUES
ref_uv_nf17) ;
resultat, fk_etudiant, fk_uv) VALUES
ref_uv_nf29) ;
INSERT INTO Note (semestre, resultat, fk_etudiant, fk_uv) VALUES
('P2014', 21, ref_etudiant_3, ref_uv_nf17) ;
END ;
Quelques requêtes pour afficher certaines informations de la base.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Pour afficher quelques informations
SELECT n.fk_etudiant.nom as nom, n.fk_uv.titre as titre, n.semestre,
n.resultat, n.fk_etudiant.fk_origine.intitule as origine
FROM Note n
ORDER BY nom;
-- Affiche la moyenne de chaque étudiant
SELECT n.fk_etudiant.nom as nom, AVG(n.resultat) as moyenne
FROM Note n
GROUP BY n.fk_etudiant.num, n.fk_etudiant.nom
ORDER BY moyenne DESC;
-- Affiche la moyenne des étudiants qui ont au moins trois notes
SELECT n.fk_etudiant.nom as nom, AVG(n.resultat) as moyenne
FROM Note n
GROUP BY n.fk_etudiant.num, n.fk_etudiant.nom
HAVING COUNT(n.resultat) > 2;
71
Solution des exercices
> Solution n°38 (exercice p. 32)
La seule clé possible est (DateOperation, NumCompte) puisque (NumCompte)
détermine tous les attributs sauf MontantOperation et DateOperation et que
(DateOperation, NumCompte) détermine MontantOperation.
> Solution n°39 (exercice p. 32)
Ce schéma est en 1NF (les attributs sont atomiques et il existe une clé
(DateOperation, NumCompte)), mais pas en 2NF (car NumCompte est une partie de
clé qui détermine des attributs non clé).
> Solution n°40 (exercice p. 32)
NumClient→Nom
NumClient→Prenom
NumCompte→NumClient
NumCompte→Agence
Agence→Ville
Agence→Pays
Pays→Monnaie
DateOperation, NumCompte→MontantOperation
> Solution n°41 (exercice p. 32)
Image 6 Schéma relationnel décomposé
> Solution n°42 (exercice p. 32)
La réponse est triviale, en dehors de la détection de Operation comme entité faible
de Compte.
> Solution n°43 (exercice p. 32)
1
2
3
4
5
72
CREATE VIEW RTotalParPersonneEtParMonnaie (NumClient, Monnaie,
SommeDeMontantOperation)
AS
SELECT TClient.NumClient, TPays.Monnaie,
Sum(TOperation.MontantOperation)
FROM TClient, TPays, TAgence, TCompte, TOperation
WHERE TPays.Pays=TAgence.Pays
Solution des exercices
6
7
8
9
AND TAgence.Agence=TCompte.Agence
AND TClient.NumClient=TCompte.NumClient
AND TCompte.NumCompte=TOperation.NumCompte
GROUP BY TClient.NumClient, TPays.Monnaie;
> Solution n°44 (exercice p. 33)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
INSERT INTO TPays
SELECT DISTINCT Pays, Monnaie
FROM TBank ;
INSERT INTO TAgence
SELECT DISTINCT Agence, Ville, Pays
FROM TBank ;
INSERT INTO TClient
SELECT DISTINCT NumClient, Nom, Prenom
FROM TBank ;
INSERT INTO TCompte
SELECT DISTINCT NumCompte, NumClient, Agence
FROM TBank ;
INSERT INTO TOperation
SELECT NumCompte, DateOperation, MontantOperation
FROM TBank
Remarque : Ordre des instructions
L'ordre des instructions est important afin de respecter les contraintes d'intégrité
référentielle.
Remarque : SELECT DISCTINCT
Le SELECT DISTINCT est important puisque dans la relation initiale "à plat" ce
sont les opérations qui sont représentées, et donc les pays, agences, clients et
comptes sont potentiellement définis plusieurs fois (c'est même de là que provient
le problème de redondance).
On fait néanmoins l'hypothèse que la relation initiale ne contient pas d'erreurs,
malgré l'absence de contrainte d'intégrité, et que par exemple la même opération
n'est pas définie deux fois.
> Solution n°45 (exercice p. 33)
1
2
3
4
5
6
7
8
9
10
11
CREATE TRIGGER trDeficit
after insert on TOperation
for each row
BEGIN
INSERT INTO TDeficit
SELECT TOperation.NumCompte
FROM TOperation
WHERE TOperation.NumCompte=:new.NumCompte
GROUP BY TOperation.NumCompte
HAVING sum(TOperation.MontantOperation) < 0;
END;
73
Solution des exercices
> Solution n°46 (exercice p. 33)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
public boolean NF17(Integer pNumClient) {
try {
DriverManager.registerDriver (new OracleDriver());
Connection vCon =
DriverManager.getConnection( "jdbc:oracle:thin:Java/[email protected].
bank.com:1521:bank");
vCon.setAutoCommit(true);
Statement vSt = vCon.createStatement();
ResultSet vRs = vSt.executeQuery("SELECT count(*) FROM TCompte,
TDeficit WHERE TCompte.NumCompte=TDeficit.NumCompte AND
TCompte.NumClient=" + pNumClient);
vRs.next();
if (vRs.getInt(1)>0) {
return true;
}
else {
return false;
}
} catch (Exception e) {
e.printStackTrace();
}
> Solution n°47 (exercice p. 34)
Le problème survient car la gestion des transactions n'est pas activée (mode
AUTOCOMMIT ON).
1
2
3
4
5
6
7
8
9
10
SET AUTOCOMMIT OFF;
CREATE procedure pVirement (Compte1 number, Compte2 number, Montant
number, DateOp date)
IS
BEGIN
INSERT INTO TOperation VALUES (Compte1, DateOp, Montant * -1);
INSERT INTO TOperation VALUES (Compte2, DateOp, Montant);
COMMIT;
END;
> Solution n°48 (exercice p. 34)
1
CREATE INDEX iMontant ON TOperation(MontantOperation);
> Solution n°49 (exercice p. 35)
1
2
3
4
5
74
Personnage (#designation:string, prix:float, identiteSecrete:string,
genre:{superhéro, supervilain}, mentor=>Personnage(designation))
Vehicule (#designation:string, prix:float, nbPlaces:integer, type:
{terrestre, volant, aquatique}, conducteur=>Personnage(designation))
WITH conducteur NOT NULL
Repaire (#designation:string, prix:float,
emplacementGeographique:string, capacite:integer)
Membre (#proprietaire=>Personnage(designation), #nom:{tête, bras
gauche, bras droit, torse, jambe gauche, jambe droite},
couleur:string)
Abrite (#repaire=>Repaire(designation),
#personnage=>Personnage(designation))
Solution des exercices
6
- Constraint : PROJ(Personnage.designation) IN
PROJ(Membre.proprietaire)
Remarque : Héritage
L'héritage est exclusif, un personnage ne peut pas être un véhicule ou un repaire,
et la classe mère est abstraite. Il s'agit donc de l'un des cas simples de
transformation : héritage par les classes filles.
L'héritage est non complet, un héritage par la classe mère serait une erreur.
L'héritage par référence est une mauvaise solution qui complexifie pour rien le
modèle.
Remarque : Clé candidate "identiteSecrete"
Nous pourrions décider que identiteSecrete est une clé candidate, bien que la
diagramme UML ne le précise pas.
Remarque : Vue vFigurine
La vue vFigurine est optionnelle, la classe mère étant abstraite.
vFigurine = Union ( Union (Projection(Personnage,designation,prix),
(Projection(Vehicule,designation,prix)),
Projection(Repaire,designation,prix))
> Solution n°50 (exercice p. 36)
1
2
R1 = Restriction (Abrite, Abrite.repaire = 'La Ratcave')
R = Jointure (Personnage, R1, R1.personnage = Personnage.designation)
> Solution n°51 (exercice p. 36)
1
2
3
4
5
6
7
8
9
10
11
R1 = Restriction (Abrite, Abrite.repaire = 'GARVEL Tower')
R2 = Restriction (Personnage, Personnage.mentor = 'Superman')
RES1 = Jointure (R1, R2, R1.personnage = R2.designation)
R3 = Restriction (Membre, Membre.nom = 'torse')
RES2 = Jointure (RES1, R3, R3.proprietaire = RES1.designation)
R4 = Restriction (Vehicule, Vehicule.type = 'aquatique')
RES3 = Jointure (RES2, R4, R4.conducteur = RES2.designation)
R = Projection (RES3, RES3.couleur)
> Solution n°52 (exercice p. 36)
1
2
3
4
5
6
7
8
9
10
R1 = Restriction (Personnage, Personnage.prix < 10)
RES1 = Projection (R1, R1.designation)
R2 = Restriction (Vehicule, Vehicule.prix < 10)
RES2 = Projection (R2, R2.designation)
R3 = Restriction (Repaire, Repaire.prix < 10)
RES3 = Projection (R3, R3.designation)
R4 = Union (RES1, RES2)
75
Solution des exercices
11
R = Union (RES3, R4)
Si la vue vFigurine a été préalablement créée
1
2
R1 = Restriction (vFigurine, vFigurine.prix < 10)
R = Projection (R1, R1.designation)
> Solution n°53 (exercice p. 36)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
-- Figurine
Type typeFigurine : <designation, prix>
-- Véhicule
Type typeVehicule : hérite de typeFigurine <type, nb_places>
Vehicule de typeVehicule (#designation)
-- Repaire
Type typeRepaire : hérite de typeFigurine <emplacement_geographique,
capacite>
Repaire de typeRepaire (#designation)
-- Membre
Type typeMembre : <nom, couleur>
Type listeMembres : collection de typeMembre
-- References vers Repaire
Type refRepaire : <fkRepaire =>o Repaire>
Type listeRefRepaires : collection de refRepaire
-- References vers Vehicule
Type refVehicule : <fkVehicule =>o Vehicule>
Type listeRefVehicules : collection de refVehicule
-- Personnage
Type typePersonnage : hérite de Figurine <identite_secrete, genre,
mentor =>o Personnage, membres:listeMembres,
repaires:listeRefRepaires, vehicules:listeRefVehicules>
Personnage de typePersonnage(#designation)
Remarque
Les types non précisés sont ceux du diagramme UML.
Remarque : Héritage
L'héritage est exclusif et la classe mère est abstraite, on choisit un héritage par les
classes filles.
Remarque : Alternatives pour les associations
Pour l'association N:M entre Repaire et Personne, on peut choisir d'imbriquer
une collection de références vers Personnage dans la relation Repaire ; ou
choisir une relation Abrite avec des références vers Repaire et Personnage.
Pour l'association 1:N entre Véhicule et Personne, on peut choisir plus
classiquement une référence depuis Vehicule vers Personnage.
Mais les requêtes demandées ci-après sont plus simples à écrire avec les choix faits
ici.
> Solution n°54 (exercice p. 36)
76
Solution des exercices
1
2
3
SELECT p.designation, p.identite_secrete
FROM Personnage p, TABLE(p.repaires) r
WHERE r.designation = 'La Ratcave' ;
> Solution n°55 (exercice p. 36)
1
2
3
4
5
6
SELECT m.couleur
FROM Personnage p, TABLE(p.vehicules) v, TABLE(p.repaires) r,
TABLE(p.membres) m
WHERE r.designation = 'GARVEL Tower'
AND p.mentor.designation = 'Superman'
AND m.nom = 'torse'
AND v.type = 'aquatique' ;
> Solution n°56 (exercice p. 37)
Explication
1. Le script est en mode AUTOCOMMIT ce qui signifie que chaque instruction
est isolée dans une transaction propre, un COMMIT implicite étant exécuté
après chaque INSERT (voir ci-après).
2. Une défaillance système (probablement une coupure réseau) est survenue
juste avant le dernier INSERT, ce qui a empêché son exécution. Les six
premiers INSERT ont donc été exécutés, mais pas le septième (celui qui
concerne la tête).
1
2
3
4
5
6
7
8
9
10
11
12
13
14
INSERT INTO Personnage (designation, prix, identite_secrete, genre)
VALUES ('Superchild','12','Jordy','superhéros') ;
COMMIT ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','tete','rose') ;
COMMIT ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','bras droit','bleu') ;
COMMIT ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','bras gauche','bleu') ;
COMMIT ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','torse','rouge') ;
COMMIT ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','jambe gauche','bleu') ;
COMMIT ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','jambe droite','bleu') ;
COMMIT ;
> Solution n°57 (exercice p. 38)
AUTOCOMMIT désactivé, BEGIN implicite
Pour gérer ce problème il faut encapsuler les sept INSERT dans une unique
transaction, celle-ci en assure alors l'atomicité : il ne sera plus possible à une
partie seulement des INSERT d'être exécutée.
1
2
\set AUTOCOMMIT off
INSERT INTO Personnage (designation, prix, identite_secrete, genre)
VALUES ('Superchild','12','Jordy','superhéros') ;
77
Solution des exercices
3
4
5
6
7
8
9
INSERT INTO Membre (propriétaire, nom,
('Superchild','tete','rose') ;
INSERT INTO Membre (propriétaire, nom,
('Superchild','bras droit','bleu') ;
INSERT INTO Membre (propriétaire, nom,
('Superchild','bras gauche','bleu') ;
INSERT INTO Membre (propriétaire, nom,
('Superchild','torse','rouge') ;
INSERT INTO Membre (propriétaire, nom,
('Superchild','jambe gauche','bleu') ;
INSERT INTO Membre (propriétaire, nom,
('Superchild','jambe droite','bleu') ;
COMMIT ;
couleur) VALUES
couleur) VALUES
couleur) VALUES
couleur) VALUES
couleur) VALUES
couleur) VALUES
AUTOCOMMIT activé, BEGIN explicite
1
2
3
4
5
6
7
8
9
BEGIN TRANSACTION ;
INSERT INTO Personnage (designation, prix, identite_secrete, genre)
VALUES ('Superchild','12','Jordy','superhéros') ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','tete','rose') ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','bras droit','bleu') ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','bras gauche','bleu') ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','torse','rouge') ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','jambe gauche','bleu') ;
INSERT INTO Membre (propriétaire, nom, couleur) VALUES
('Superchild','jambe droite','bleu') ;
COMMIT TRANSACTION ;
> Solution n°58 (exercice p. 38)
1. Les problèmes de performance sont liés au tri qui s'effectue sur le prix d'une
part ;
2. et aux jointures qui doivent être exécutées entre toutes les tables de la
base pour pouvoir extraire la liste de toutes les figurines avec leurs
caractéristiques.
> Solution n°59 (exercice p. 38)
1. Une première optimisation évidente concerne l'indexation du champ prix,
elle permettra d'accélérer l'opération de tri, et ses effets indésirables
(ralentissement de la mise à jour et augmentation du volume disque) seront
négligeables.
2. Une approche relationnel-objet, centrée sur la table Personnage, permettrait
de récupérer toutes les informations sans faire de jointure, en utilisant
des OID et/ou la mécanique des nested tables. Elle réglerait donc, couplée
avec l'indexation du champ prix, les problèmes de performance.
> Solution n°60 (exercice p. 39)
1. Si l'on devait rester en relationnel on pourrait dénormaliser le modèle de
façon à ne plus avoir à faire de jointure, ou à en faire moins selon le degré
de dénormalisation.
2. Cette solution engendrerait de la redondance, qu'il faudrait contrôler par
ailleurs, au niveau applicatif et/ou au niveau de la base de données, par
des triggers par exemple.
3. Une alternative serait de créer une vue matérialisée qui pré-calculerait les
78
Solution des exercices
jointures (et le tri par la même occasion).
4. L'inconvénient est que cette vue introduirait un décalage entre ce qui est
présent dans la base et ce que l'on visualise sur le site. La calcul de la vue
n'étant que de quelques secondes, on pourra envisager, sans surcharger le
serveur, un nouveau calcul de la vue à chaque mise à jour des données
liées, déclenchée par un trigger par exemple.
(Notons que les solutions de partitionnement ne seraient d'aucun intérêt étant
donné que toutes les données, enregistrements et attributs, doivent être
retournées ; à moins d'une partition sur le prix cela aura même un effet négatif
étant donné qu'il faudra réaliser l'union avant le tri par prix).
> Solution n°61 (exercice p. 39)
1
2
3
4
5
6
7
8
<?php
$vConn = pg_connect("host=garvel.com port=5432 dbname=cat
user=customer password=public");
$vSql ="SELECT designation, prix FROM vFigurine;";
$vQuery=pg_query($vConn, $vSql);
while ($vResult = pg_fetch_array($vQuery)) {
echo "$vResult[designation];$vResult[designation]\n";
}
?>
> Solution n°62 (exercice p. 40)
1
2
3
4
5
6
7
8
9
10
<?php
$vConn = pg_connect("host=garvel.com port=5432 dbname=cat
user=customer password=public");
$vSql ="SELECT designation, prix FROM vFigurine;";
$vQuery=pg_query($vConn, $vSql);
echo "<catalogue>";
while ($vResult = pg_fetch_array($vQuery)) {
echo "<figurine designation='$vResult[designation]'
prix='$vResult[designation]'/>";
}
echo "</catalogue>";
?>
> Solution n°63 (exercice p. 41)
1. La première instruction UPDATE ajoute 1 à l'attribut hasfriends de la
personne qui déclare un nouvel ami
2. La seconde instruction UPDATE ajoute 1 à l'attribut isfriendof de la
personne qui est déclarée comme ami
login
a
b
a
c
c
a
Tableau 8 friends
login
a
declarefriend
hasfriends
2
isfriendsof
1
79
Solution des exercices
login
hasfriends
b
isfriendsof
1
c
1
1
> Solution n°64 (exercice p. 42)
Sous Oracle : "x + Null = Null".
Ici hasfriends et isfriendof peuvent avoir la valeur Null à leur initialisation.
La fonction NVL permet de remplacer ces attributs par 0 quand ils sont nuls, de
façon à pourvoir les incrémenter une première fois.
> Solution n°65 (exercice p. 42)
1
2
person (#login,hasfriends,isfriendof)
friends (#login=>person, declarefriend=>person)
friends exprime une association N:M réflexive car elle associe une classe avec
elle même. Elle est dissymétrique car X peut déclarer Y comme ami, mais cela ne
signifie pas que Y déclare X comme ami : (X,Y) n'implique pas (Y,X).
> Solution n°66 (exercice p. 42)
login
login est l'identifiant d'une personne X
hasfriends
hasfriends est le nombre d'amis déclarés
par X
isfriendof
isfriendof est le nombre de personnes
ayant déclaré X comme ami
Tableau 9 person
login
login est l'identifiant d'une personne X
declarefriend
declarefriend est la déclaration d'un ami
de X
Tableau 10 friends
> Solution n°67 (exercice p. 42)
DF
Chaque relation a une clé et des attributs atomiques. Les deux seules DFE sont
person.login → person.hasfriends et person.login → person.isfriendof. Toutes les
DFE sont donc de type : une clé détermine un attribut. La relation est donc en
BCNF, donc en 3NF.
Il existe néanmoins de la redondance dans la mesure ou la connaissance de la
relation friends permet de calculer hasfriends et isfriendof.
> Solution n°68 (exercice p. 42)
80
Solution des exercices
Modèle UML
> Solution n°69 (exercice p. 42)
login
c
declarefriend
b
Tableau 11 friends
La table friends ne comportera plus qu'une ligne qui déclarera que 'b' est l'ami de
'c', mais la table person sera exactement dans le même état que précédemment,
car il n'y a pas de modification de hasfriends et isfriendof lors de la suppression
ou de la mise à jour, ce qui n'est pas cohérent.
Il faut ajouter une décrémentation de ces attributs dans le déclencheur lors de la
suppression et de la mise à jour dans friends.
> Solution n°70 (exercice p. 42)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE OR REPLACE TRIGGER tr_friends
BEFORE INSERT OR UPDATE OR DELETE
ON friends
FOR EACH ROW
BEGIN
-UPDATE person p
SET p.hasfriends = NVL(p.hasfriends,0) + 1
WHERE p.login=:new.login;
-UPDATE person p
SET p.isfriendof = NVL(p.isfriendof,0) + 1
WHERE p.login=:new.declarefriend;
-UPDATE person p
SET p.hasfriends = p.hasfriends - 1
WHERE p.login=:old.login;
-UPDATE person p
SET p.isfriendof = p.isfriendof - 1
WHERE p.login=:old.declarefriend;
-END;
Remarque
L'usage d'un IF dans le trigger serait plus rigoureux et plus performant, il
permettrait d'éviter des UPDATE inutiles.
81
Solution des exercices
> Solution n°71 (exercice p. 42)
1
2
3
4
5
6
7
8
CREATE OR REPLACE TRIGGER tr_person
BEFORE INSERT
ON person
FOR EACH ROW
BEGIN
:new.hasfriends:=0;
:new.isfriendof:=0;
END;
> Solution n°72 (exercice p. 43)
Le dernier problème est celui de la mise à jour directe des attributs hasfriends et
isfriendof.
Exemple
1
2
3
UPDATE person
SET hasfriends = 1
WHERE login = 'd';
> Solution n°73 (exercice p. 43)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE FUNCTION f_hasfriends(v_login person.login%TYPE) RETURN
person.hasfriends%TYPE
IS
v_hasfriends person.hasfriends%TYPE ;
BEGIN
SELECT COUNT(*) INTO v_hasfriends
FROM friends f
WHERE f.login=v_login;
RETURN v_hasfriends;
END;
-CREATE FUNCTION f_isfriendof(v_login person.login%TYPE) RETURN
person.isfriendof%TYPE
IS
v_isfriendof person.isfriendof%TYPE;
BEGIN
SELECT COUNT(*) INTO v_isfriendof
FROM friends
WHERE declarefriend=v_login;
RETURN v_isfriendof;
END;
> Solution n°74 (exercice p. 43)
1
2
3
4
5
6
7
8
9
82
CREATE OR REPLACE TRIGGER tr_person
BEFORE INSERT OR UPDATE
ON person
FOR EACH ROW
BEGIN
:new.hasfriends:=f_hasfriends(:new.login);
:new.isfriendof:=f_isfriendof(:new.login);
END IF;
END;
Solution des exercices
Complément
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
: Version plus performante
CREATE OR REPLACE TRIGGER tr_person
BEFORE INSERT OR UPDATE
ON person
FOR EACH ROW
BEGIN
-IF INSERTING THEN
:new.hasfriends:=0;
:new.isfriendof:=0;
-ELSE
:new.hasfriends:=f_hasfriends(:new.login);
:new.isfriendof:=f_isfriendof(:new.login);
-END IF;
END;
Cette version est plus performante car elle évite de parcourir la table friends sur
les insertions, alors que l'on sait que les fonctions renverront 0.
> Solution n°75 (exercice p. 44)
1
2
5
6
7
8
9
10
11
12
13
14
15
16
-CREATE OR REPLACE TYPE refperson_type AS OBJECT (friend REF
person_type);
-CREATE OR REPLACE TYPE reflistpersons_type AS TABLE OF
refperson_type;
-CREATE OR REPLACE TYPE person_type AS OBJECT (
login VARCHAR(10),
friends reflistpersons_type,
MEMBER FUNCTION hasfriends RETURN NUMBER,
MEMBER FUNCTION isfriendof RETURN NUMBER
);
-CREATE TABLE person OF person_type (
PRIMARY KEY (login)
)NESTED TABLE friends STORE AS ntfriends;
--
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR REPLACE TYPE BODY person_type IS
-MEMBER FUNCTION hasfriends RETURN NUMBER IS
v_hasfriends NUMBER;
BEGIN
SELECT count(f.friend.login) INTO v_hasfriends
FROM person p, TABLE(p.friends) f
WHERE p.login = SELF.login;
RETURN v_hasfriends;
END;
-MEMBER FUNCTION isfriendof RETURN NUMBER IS
v_isfriendof NUMBER;
BEGIN
SELECT count(p.login) INTO v_isfriendof
FROM person p, TABLE(p.friends) f
WHERE f.friend.login = SELF.login;
RETURN v_isfriendof;
END;
--
3
4
83
Solution des exercices
21
END;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
DECLARE
v_a REF person_type;
v_b REF person_type;
v_c REF person_type;
BEGIN
-INSERT INTO person (login) VALUES ('a');
INSERT INTO person (login) VALUES ('b');
INSERT INTO person (login) VALUES ('c');
-SELECT REF(p) INTO v_a
FROM person p
WHERE login='a';
SELECT REF(p) INTO v_b
FROM person p
WHERE login='b';
SELECT REF(p) INTO v_c
FROM person p
WHERE login='c';
-UPDATE person
SET friends = reflistpersons_type(refperson_type(v_b),
refperson_type(v_c))
WHERE login = 'a';
UPDATE person
SET friends = reflistpersons_type(refperson_type(v_a))
WHERE login = 'c';
-END;
23
24
25
26
27
28
84
Solution des exercices
Remarque
La solution proposée pour créer la base de données ne tient pas compte d'un
problème de séquencement des déclarations, il faudrait utiliser un ALTER (ou un
premier CREATE TYPE incomplet) pour traiter ce problème.
> Solution n°76 (exercice p. 46)
couleur(#fket=>et, #intitule:string)
membre(#fket=>et, #numero:integer, type:{main|pied}, nbdoigts:integer)
et(#nom:string, nbyeux:integer, nbnez:integer, nbbouches:integer)
> Solution n°77 (exercice p. 46)
1
2
3
4
5
CREATE TABLE tET (
nom VARCHAR(20) PRIMARY KEY,
nbyeux NUMBER(2),
nbnez NUMBER(2),
nbbouches NUMBER(2));
1
2
3
4
CREATE TABLE tCouleur (
fket VARCHAR(20) REFERENCES tEt(nom),
intitule VARCHAR(10),
PRIMARY KEY (fket, intitule));
1
2
3
4
5
6
CREATE TABLE tMembre (
fket VARCHAR(20) REFERENCES tEt(nom),
numero NUMBER(10),
type CHAR(4) CHECK (type='main' OR type='pied'),
nbdoigts NUMBER(2),
PRIMARY KEY (fket, numero));
> Solution n°78 (exercice p. 46)
1
2
3
4
5
6
7
8
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
INSERT
COMMIT
INTO
INTO
INTO
INTO
INTO
INTO
INTO
;
tEt VALUES ('Alf',2,1,1);
tCouleur VALUES ('Alf', 'Jaune');
tCouleur VALUES ('Alf', 'Brun');
tMembre VALUES ('Alf', 1, 'main',
tMembre VALUES ('Alf', 2, 'main',
tMembre VALUES ('Alf', 3, 'pied',
tMembre VALUES ('Alf', 4, 'pied',
> Solution n°79 (exercice p. 46)
1
2
3
SELECT intitule
FROM tCouleur
WHERE nom='Alf';
1
2
3
SELECT count(*)
FROM tCouleur
WHERE nom='Alf';
> Solution n°80 (exercice p. 46)
1
SELECT tMembre.*
4);
4);
4);
4);
Solution des exercices
2
3
FROM tMembre
WHERE nom='Alf';
1
2
3
SELECT sum(nbdoigts)
FROM tMembre
WHERE nom='Alf';
> Solution n°81 (exercice p. 46)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
function IndiceDeRoswell($vConn, $vNom)
{
// Récupération des yeux, nez et bouches
$vSql = "SELECT nbyeux, nbnez, nbbouches FROM tET WHERE
nom='$vNom'";
$vQuery = pg_query($vConn,$vSql);
$vResult = pg_fetch_array($vQuery, null, PGSQL_ASSOC);
$nbyeux = $vResult[nbyeux];
$nbnez = $vResult[nbnez];
$nbbouches = $vResult[nbbouches];
// Récupération des couleurs
$vSql = "SELECT count(*) AS nbcouleurs FROM tCouleur WHERE
fket='$vNom'";
$vQuery = pg_query($vConn,$vSql);
$vResult = pg_fetch_array($vQuery, null, PGSQL_ASSOC);
$nbcouleurs = $vResult[nbcouleurs];
// Récupération des doigts
$vSql = "SELECT sum(nbdoigts) AS nbdoigts FROM tMembre WHERE
fket='$vNom'";
$vQuery = pg_query($vConn,$vSql);
$vResult = pg_fetch_array($vQuery, null, PGSQL_ASSOC);
$nbdoigts = $vResult[nbdoigts];
// Calcul de l'indice
$vIndice = ($nbyeux+$nbnez)/$nbbouches-$nbdoigts/$nbcouleurs;
return $vIndice;
}
Complément
Il est possible de vérifier que $nbbouches et $nbcouleurs sont différents de 0 et
que les autres paramètres ne sont pas NULL.
> Solution n°82 (exercice p. 47)
1
2
3
4
5
Type Membre : <numero:string, type:{main|pied}, nbdoigts:integer>
Type ListeMembres : Collection de <membre>
Type ListeCouleurs : Collection de <string>
Type Et <nom:string, nbyeux:integer, nbnez:integer,
nbbouches:integer, couleurs:ListeCouleurs, membres:ListeMembres,
=IndiceDeRoswell():real>
Table tEt de ET (#nom)
> Solution n°83 (exercice p. 47)
1
2
3
4
5
86
CREATE TYPE Membre AS OBJECT (
numero NUMBER(10),
type CHAR(4),
nbdoigts NUMBER(2)
);
Solution des exercices
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
/
CREATE TYPE ListeMembres AS TABLE OF Membre;
/
CREATE TYPE ListeCouleurs AS TABLE OF VARCHAR(10);
/
CREATE TYPE Et AS OBJECT (
nom VARCHAR(20),
nbyeux NUMBER(2),
nbnez NUMBER(2),
nbbouches NUMBER(2),
couleurs ListeCouleurs,
membres ListeMembres,
MEMBER FUNCTION IndiceDeRoswell RETURN REAL);
/
CREATE TABLE tEt OF Et (
PRIMARY KEY (nom))
NESTED TABLE couleurs STORE AS ntcouleurs,
NESTED TABLE membres STORE AS ntmembres;
> Solution n°84 (exercice p. 47)
1
2
3
4
5
6
7
8
INSERT INTO tEt VALUES (
'Alf',
2,
1,
1,
ListeCouleurs('brun', 'jaune'),
ListeMembres(Membre(1,'main',4), Membre(2,'main',4),
Membre(3,'pied',4), Membre(4,'pied',4))
);
> Solution n°85 (exercice p. 47)
1
2
3
SELECT nt.COLUMN_VALUE
FROM tEt t, TABLE (t.couleurs) nt
WHERE t.nom='Alf';
1
2
3
SELECT count(nt.COLUMN_VALUE)
FROM tEt t, TABLE (t.couleurs) nt
WHERE t.nom='Alf';
> Solution n°86 (exercice p. 47)
1
2
3
SELECT nt.*
FROM tEt t, TABLE (t.membres) nt
WHERE t.nom='Alf';
1
2
3
SELECT sum(nt.nbdoigts)
FROM tEt t, TABLE (t.membres) nt
WHERE t.nom='Alf';
> Solution n°87 (exercice p. 48)
1
2
CREATE OR REPLACE TYPE BODY Et
IS
87
Solution des exercices
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MEMBER FUNCTION IndiceDeRoswell RETURN REAL
IS
vNbcouleurs INTEGER;
vNbdoigts INTEGER;
vIndice REAL;
BEGIN
SELECT count(nt.COLUMN_VALUE) INTO vNbcouleurs
FROM tEt t, TABLE (t.couleurs) nt
WHERE t.nom=SELF.nom;
SELECT sum(nt.nbdoigts) INTO vNbdoigts
FROM tEt t, TABLE (t.membres) nt
WHERE t.nom=SELF.nom;
vIndice := ((SELF.nbyeux + SELF.nbnez) / SELF.nbbouches) (vNbdoigts / vNbcouleurs);
RETURN vIndice;
END;
END;
> Solution n°88 (exercice p. 48)
1
2
3
SELECT t.IndiceDeRoswell()
FROM tEt t
WHERE t.nom='Alf';
> Solution n°89 (exercice p. 53)
1
2
documents(#object_id,titre,categorie,annee)
documentsMV(object_id=>documents,auteurs,themes,langues)
Le modèle n'est pas en 1NF car la relation documentsMV n'a pas de clé.
Remarque
La solution documentsMV(#object_id=>documents,#auteurs,#themes,#langues)
n'est pas correcte car les attributs auteurs,themes et langues peuvent être nuls.
> Solution n°90 (exercice p. 53)
documents
#object_id
titre
categorie
annee
auteurs[0..N]
themes[0..N]
langues[0..N]
Tableau 12 Modèle UML de la classe document
Attention
Il ne faut pas sur-interpréter les données présentes, nous sommes certains que les
données sont des attributs (mono et multi-valués). En effet les données étaient
ainsi modélisées dans la base Documentum existante. Les modéliser comme des
classes - par exemple - serait donc une erreur puisqu'une interprétation qui n'aurait
88
Solution des exercices
pas de sens avec les données dont nous disposons.
> Solution n°91 (exercice p. 54)
1
2
3
4
documents(#object_id,titre,categorie,annee)
documentsAuteurs(#object_id=>documents,#auteurs)
documentsThemes(#object_id=>documents,#themes)
documentsLangues(#object_id=>documents,#langues)
> Solution n°92 (exercice p. 54)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
CREATE TABLE R1_documents (
object_id VARCHAR(4),
titre VARCHAR(100),
categorie VARCHAR(50),
annee INTEGER(4),
PRIMARY KEY (object_id)
);
CREATE TABLE R1_documentsMV (
object_id VARCHAR(4),
auteurs VARCHAR(50),
themes VARCHAR(50),
langues VARCHAR(20),
FOREIGN KEY (object_id) REFERENCES R1_documents(object_id)
);
CREATE TABLE R2_documents (
object_id VARCHAR(4),
titre VARCHAR(100),
categorie VARCHAR(50),
annee INTEGER(4),
PRIMARY KEY (object_id)
);
CREATE TABLE R2_documentsAuteurs
object_id VARCHAR(4),
auteurs VARCHAR(50),
PRIMARY KEY (object_id, auteurs),
FOREIGN KEY (object_id) REFERENCES R1_documents(object_id)
);
CREATE TABLE R2_documentsThemes
object_id VARCHAR(4),
themes VARCHAR(50),
PRIMARY KEY (object_id, themes),
FOREIGN KEY (object_id) REFERENCES R1_documents(object_id)
);
CREATE TABLE R2_documentsLangues
object_id VARCHAR(4),
langues VARCHAR(50),
PRIMARY KEY (object_id, langues),
FOREIGN KEY (object_id) REFERENCES R1_documents(object_id)
);
> Solution n°93 (exercice p. 54)
1
2
3
INSERT INTO R2_documents (object_id,titre,categorie,annee)
SELECT object_id,titre,categorie,annee
FROM R1_documents;
> Solution n°94 (exercice p. 54)
89
Solution des exercices
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
INSERT INTO R2_documentsAuteurs (object_id,auteurs)
SELECT object_id,auteurs
FROM R1_documentsMV
WHERE auteurs IS NOT NULL
;
INSERT INTO R2_documentsThemes (object_id,themes)
SELECT object_id,themes
FROM R1_documentsMV
WHERE themes IS NOT NULL
;
INSERT INTO R2_documentsLangues (object_id,langues)
SELECT object_id,langues
FROM R1_documentsMV
WHERE langues IS NOT NULL
;
> Solution n°95 (exercice p. 54)
1
2
3
4
5
CREATE VIEW vR1_documents (object_id, titre, categorie, annee,
nbauteurs, nbthemes, nblangues) AS
SELECT d.object_id, d.titre, d.categorie, d.annee,
count(mv.auteurs), count(mv.themes), count(mv.langues)
FROM R1_documents d INNER JOIN R1_documentsMV mv
ON d.object_id=mv.object_id
GROUP BY d.object_id, d.titre, d.categorie, d.annee;
> Solution n°96 (exercice p. 54)
Il faut en-capsuler les 4 requêtes de migration dans une transaction. Ainsi soit
toutes les instructions seront correctement exécutées, soit aucune. En cas de panne
pendant la migration, le SGBD annulera ce qui a été commencé pour se retrouver
dans l'état avant la migration.
Il suffit de désactiver le mode AUTOCOMMIT s'il est activé et d'ajouter l'instruction
COMMIT à la fin des quatre requêtes INSERT servant à la migration.
> Solution n°97 (exercice p. 54)
formulaire.html
1
2
3
4
5
6
7
8
9
<html>
<body>
<p>Saisissez un mot à rechercher dans le titre ou les auteurs des
documents :</p>
<form method="GET" action="recherche.php">
<input type="text" name="mot"/>
<input type="submit">
</form>
</body>
</html>
> Solution n°98 (exercice p. 54)
$vSql
1
90
SELECT DISTINCT d.object_id AS id
Solution des exercices
2
3
4
FROM R2_documents d LEFT JOIN R2_documentsAuteurs a
ON d.object_id=a.object_id
WHERE d.titre LIKE '%$vMot%' OR a.auteurs LIKE '%$vMot%'
Alternative
1
2
3
4
5
6
7
SELECT object_id AS id
FROM R2_documents
WHERE titre LIKE '%$vMot%'
UNION
SELECT object_id AS id
FROM R2_documentsAuteurs
WHERE auteurs LIKE '%$vMot%'
> Solution n°99 (exercice p. 54)
recherche.php
1
2
3
4
5
6
7
8
9
10
11
12
<? php
$vMot = $_GET[mot] ;
$vConnect = pg_connect("host=... port=... dbname=... user=...
password=...") ;
$vSql = "SELECT ..." ;
$vQuery=pg_query($vConnect, $vSql) ;
while ($vResult = pg_fetch_array($vQuery, null, PGSQL_ASSOC)) {
echo "<p>" ;
echo $vResult["id"] ;
echo "</p>" ;
}
pg_close($vConnect) ;
?>
> Solution n°100 (exercice p. 55)
Modèle RO
1
2
type l_termes : collection de <string>
documents(#object_id:string, titre:string, categorie:string
,annee:string, auteurs:l_termes, themes:l_termes, langues:l_termes)
Code SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE TYPE l_termes AS TABLE OF varchar(50) ;
CREATE TABLE R3_documents (
object_id VARCHAR(4),
titre VARCHAR(100),
categorie VARCHAR(50),
annee INTEGER(4),
auteurs l_termes,
themes l_termes,
langues l_termes,
PRIMARY KEY (object_id))
NESTED TABLE auteurs STORE AS ntauteurs,
NESTED TABLE themes STORE AS ntthemes,
NESTED TABLE langues STORE AS ntlangues ;
> Solution n°101 (exercice p. 55)
1
2
3
4
INSERT INTO R3_documents (object_id,titre,categorie,annee, auteurs,
themes, langues)
SELECT
d.object_id, d.titre, d.categorie, d.annee,
CAST(
Solution des exercices
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
MULTISET(
SELECT auteurs
FROM R1_documentsMV dmv
WHERE auteurs IS NOT NULL AND dmv.object_id=d.object_id)
AS l_termes),
CAST(
MULTISET(
SELECT themes
FROM R1_documentsMV dmv
WHERE themes IS NOT NULL AND dmv.object_id=d.object_id)
AS l_termes),
CAST(
MULTISET(
SELECT langues
FROM R1_documentsMV dmv
WHERE langues IS NOT NULL AND dmv.object_id=d.object_id)
AS l_termes)
FROM R1_documents d;
> Solution n°102 (exercice p. 55)
Affichage
1
2
3
SELECT titre || ',' || t.COLUMN_VALUE
FROM R3_documents d, TABLE(d.themes) t
ORDER BY titre
Optimisation
1
92
CREATE INDEX idx_titre ON R3_documents (titre);
Solution des
exercices
> Solution n°1 (exercice p. 15)
<?php
$conn=pg_connect($host,$user,$passwd,$bdd);
$query = "SELECT A FROM T";
$result=pg_query($conn, $query);
echo "<html>";
while($row = pg_fetch_row($result))
{
echo "<p>$row[A]</p>";
}
echo "</html>";
pg_close($conn);
?>
> Solution n°2 (exercice p. 15)
<?php
$conn=mysql_connect($host,$user,$passwd);
mysql_select_db($bdd, $conn);
$query = "SELECT A FROM T";
$result=mysql_query($query, $conn);
echo "<html>";
while($row = mysql_fetch_row($result))
{
echo "<p>$row[A]</p>";
}
echo "</html>";
mysql_close($conn);
?>
> Solution n°3 (exercice p. 16)
<?php
//définition des variables de connexion à ajouter
$conn=oci_connect($user, $passwd, $bd))
93
Solution des exercices
$query = "SELECT A FROM T";
$statement = oci_parse($conn, $query);
oci_execute($statement);
echo "<html>";
while($row = oci_fetch_array($statement))
{
echo "<p>$row[A]</p>";
}
echo "</html>";
oci_close($conn);
?>
> Solution n°4 (exercice p. 16)
Sub printA()
vSql = "SELECT A FROM T"
Set vRs = CurrentDb.CreateQueryDef("", vSql).OpenRecordset
For i = 1 To vRs.RecordCount
Debug.Print vRs!A
vRs.MoveNext
Next i
End Sub
> Solution n°5 (exercice p. 17)
SET SERVEROUTPUT ON;
DECLARE
CURSOR cT IS SELECT A FROM T;
vA T.A%TYPE;
BEGIN
OPEN cT;
LOOP
FETCH cT INTO vA;
EXIT WHEN cT%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vA);
END LOOP;
END;
> Solution n°6 (exercice p. 17)
package nf17;
import java.sql.*;
import oracle.jdbc.OracleDriver;
public class Exercice {
public static void main(String[] args) {
try {
DriverManager.registerDriver (new OracleDriver());
Connection
vCon
94
=
Solution des exercices
DriverManager.getConnection("jdbc:oracle:thin:nf17/nf17@localhost:1521:test");
Statement vSt = vCon.createStatement();
String vSql = "SELECT A FROM T";
ResultSet vRs = vSt.executeQuery(vSql);
while(vRs.next()) {
System.out.println(vRs.getString(1));
}
}
catch (Exception e)
{
e.printStackTrace();
}
}
}
> Solution n°7 (exercice p. 20)
C.num→C.categorie
C.categorie→C.num
(R.num, R.date)→R.montant, R.annee
R.num→R.date
R.annee→R.date
R.date→R.annee
(D.num, D.date)→D.montant, D.annee
D.num→D.date
D.annee→D.date
D.date→D.annee
> Solution n°8 (exercice p. 21)
Oui, le schéma est en 2NF
Non, le schéma est seulement en 1NF
95
Solution des exercices
R.date→R.annee donc une partie de clé détermine un attribut non-clé.
> Solution n°9 (exercice p. 21)
Compte (num:entier, categorie:{A|R}, =solde():reel)
Recette(num=>Compte, date=>AnneeR, montant:reel)
AnneeR(date:date, annee:entier)
Depense(num=>Compte, date=>AnneeD, montant:reel)
AnneeD(date:date, annee:entier)
Compte (num:entier, categorie:{A|R}, =solde():reel)
Recette(num=>Compte, date=>Annee, montant:reel)
Depense(num=>Compte, date=>Annee, montant:reel)
Annee(date:date, annee:entier)
Compte (num:entier, categorie:{A|R}, =solde():reel)
Recette(num=>Compte, date:date, montant:reel)
Depense(num=>Compte, date:date, montant:reel)
1. La première solution est techniquement correcte, c'est le résultat d'une
décomposition classique. Mais la duplication des tables AnneeD et AnneeR
en fait une mauvaise solution. C'est la plus mauvaise des trois.
2. La seconde solution est le résultat d'une décomposition, puis d'une
mutualisation des tables AnneeD et AnneeR. Elle est correcte, et meilleure
que la précédente, bien que annee puisse être calculé à partir de date, sans
en fait avoir besoin de table du tout.
3. C'est la meilleure solution, qui consiste à redéfinir annee comme un attribut
dérivé de date et à ne pas l'implémenter en relationnel.
> Solution n°10 (exercice p. 22)
Indexation
Partitionnement horizontal
Partitionnement vertical
Dénormalisation
Vue matérialisée
Étant donné que les informations doivent être présentées seulement chaque matin,
la vue matérialisée, qui sera recalculée chaque nuit est la meilleure solution.
L'indexation est inutile, les autres solutions auront des inconvénients que ne
présente pas la vue matérialisée dans ce cas.
> Solution n°11 (exercice p. 23)
Function solde(pNum As Integer) As Real
Dim vCodeSql As String
96
Solution des exercices
Dim vR As Real
Dim vD As Real
vCodeSql = "SELECT SUM(montant) FROM Recette WHERE num=" & pNum
vR = CurrentDb.CreateQueryDef("", vCodeSql).OpenRecordset.Fields(0)
vCodeSql = "SELECT SUM(montant) FROM Depense WHERE num=" & pNum
vD = CurrentDb.CreateQueryDef("", vCodeSql).OpenRecordset.Fields(0)
solde=vR-vD
End Function
> Solution n°12 (exercice p. 23)
CREATE TRIGGER RecetteTrig
BEFORE INSERT OR UPDATE
ON Recette
FOR EACH ROW
BEGIN
:new.annee=YEAR(:new.date) ;
END ;
Le trigger permet de contrôler la redondance et d'assurer que la cohérence des
données est maintenue.
> Solution n°13 (exercice p. 24)
<html xmlns="http://www.w3.org/1999/xhtml">
<body>
<?php
/* Initialisation */
$vserveur="monassos.free.fr" ;
$vport="5432" ;
$vcu="president" ;
$vbase="bdassos" ;
$vcp="bonjour" ;
/* Connexion à la BD */
$c = pg_connect("host=$vserveur port=$vport dbname=$vbase
password=$vcp");
/* Exécution de la requête*/
$sql = "INSERT INTO Compte (".$_GET["n"].",'".$_GET["c"]."')" ;
pg_query($c, $sql) ;
/* Finalisation */
pg_close($c);
echo "<p>Insertion du compte numéro".$_GET["n"]."effectuée</p>" ;
?>
</body>
</html>
user=$vcu
97
Glossaire
Constructeur d'objet
En programmation orientée objet, un constructeur d'objet est une méthode
particulière d'une classe qui permet d'instancier un objet de cette classe. L'appel à
cette méthode de classe a donc pour conséquence la création d'un nouvel objet de
cette classe.
99
Signification des
abréviations
-
BD
CSV
DF
GED
OS
SGBD
SGDBR
XML
Base de Données
Comma Separated Values
Dépendance Fonctionnelle
Gestion Électronique de Documents
Operating Système (Système d'Exploitation)
Système de Gestion de Bases de Données
Système de Gestion de Bases de Données Relationnelles
eXtensible Markup Language
101
Index
Association........................ p. Dénormalisation................. p. Relationnel........................ p.
Attribut............................. p. Normalisation.................... p. UML p.
Classe............................... p. Redondance...................... p.
103
