Bases de données avancées - people.irisa.frpeople.irisa.fr/Nicolas.Le_Sommer/ens/tds_tps.pdf ·...

19
Bases de données avancées – travaux dirigés et travaux pratiques – Nicolas Le Sommer, Gilles Gaffiot, Didier Bodganiuk et François Morice Département Informatique, IUT de Vannes Université de Bretagne Sud

Transcript of Bases de données avancées - people.irisa.frpeople.irisa.fr/Nicolas.Le_Sommer/ens/tds_tps.pdf ·...

Bases de données avancées

– travaux dirigés et travaux pratiques –

Nicolas Le Sommer, Gilles Gaffiot, Didier Bodganiuk et François Morice

Département Informatique, IUT de VannesUniversité de Bretagne Sud

Travaux dirigés

TD 1 : Rappels sur l'algèbre relationnelle et le langage SQL

Exercice1

Dans le cadre de cet exercice, nous considérons la base de données relationnelle � bibliothèque �dé�nie par les relations :

� auteur (idAuteur : chaîne, nomAuteur : chaîne, prenomAuteur : chaîne),� publication (idPublication : chaîne, titrePublication : chaîne, nbPages : entiers),� motcle (idMot : chaîne, mot : chaîne),� ecritpar (idPublication : chaîne, idAuteur : chaîne),� parlede (idPublication : chaîne, idMot : chaîne),

et par les attributs suivants :� nomAuteur : nom d'un auteur (ex : � Ullman �),� idAuteur : identi�ant d'un auteur (ex : aut12),� titrePublication : titre d'une publication (ex : � le modèle relationnel �),� idPublication : identi�ant d'une publication (ex : pub1),� nbPages : nombre de pages de la publication (ex : 12),� mot : mot clé associé à la publication (ex : SGBD),� idMot : identi�ant du mot clé associé à la publication (ex : mc1).

Questions

1. Donnez une expression relationnelle permettant d'obtenir les titres des publications écritespar � Ullman � qui traitent de � SGBD � ou de � langages �.

2. Donnez une expression relationnelle retournant en résultat le nom et prénom des auteurs quiont écrit une publication ayant un nombre de pages supérieur à 50.

3. Donnez une requête SQL appropriée pour chacune des questions précédentes.

Exercice 2

Considérons la base de données emploi du temps caractérisée par les attributs Enseignant(E), Matière (M), Créneau Horaire (H), Salle (S) et Classe (C), et par les contraintes d'intégritéssuivantes :

� aucun enseignant n'enseigne plus d'une matière ;� un enseignant ne fait jamais cours à plus d'une classe à la fois ;� il n'y a jamais deux enseignants en même temps dans une même salle ;� une classe n'est jamais répartie dans des salles di�érentes lors d'un même enseignement.

Questions

1. Exprimez les contraintes précédentes sous formes de dépendances fonctionnelles

2. Ces dépendances permettent-elles d'assurer que deux classes ne peuvent avoir cours en mêmetemps dans une même salle ?

3. Quelles sont les clés ?

4. Le schéma global est-il en 3NF ?

5. Le schéma {EM, EHC, HCS} est-il valide, préserve-t-il les dépendances fonctionnelles ?

1

TD 2Élaboration d'un schéma relationnel à partir

d'un diagramme de classes UML

Présentation

Dans le cadre de cet exercice, nous nous intéressons à une application de

gestion des épreuves d'athlétisme. Cette application s'appuie sur une base de

données relationnelle pour enregistrer les informations manipulées. La �gure 1

présente un diagramme de classes UML modélisant la base de données utilisée

par cette application.

Fig. 1 � Diagramme de classes modélisant la base de données utilisée comme

support de stockage d'informations par l'application de gestion des épreuves

d'athlétisme.

Travail à réaliser

1. Traduisez à l'aide des règles présentées en cours le diagramme de classes

UML de la �gure 1 en un ensemble de relations, et identi�ez les dépen-

dances fonctionnelles inter-relations. Vous préciserez en outre les contraintes

de cardinalité qui ne sont pas traduites dans ce schéma relationnel. Vous

préciserez pour chaque contrainte leurs caractéristiques : contraintes de

niveau attribut, de niveau table, d'obligation de présence, etc.

1

2. Construisez le graphe des dépendances fonctionnelles inter-relations, et

proposez un ordre de création des tables.

3. Identi�ez les éventuels problèmes rencontrés lors de la mise à jours des

tables.

2

TD 3Élaboration d’un schéma relationnel à partir d’un diagramme

de classes UML

PrésentationDans le cadre de cet exercice, nous nous intéressons à une application bancaire. Cette application s’ap-

puie sur une base de données relationnelle pour enregistrer les informations qu’elle manipule. Le dia-gramme de classes UML modélisant cette base de données est présenté dans la figure 1.

< c o n c e r n e

A g e n c e

1

1..*

0..*

1

1..*

c o n n u d e >

< c o n s e i l l e

< a p p a r t i e n t

TelAgence

0..*

1

N u m A g e n c e ( 1 )

0..*

0..*1 < d e t y p e

L e g e n d e :

(1 ) i nd ique l e s iden t i f i an t s

C l i en t

N o m C l i e n tP r e n o m C l i e n tA d r e s C l i e n t

N u m C l i e n t ( 1 )

O p e r a t i o n

D a t e O p e r a t i o n

N u m O p e r a t i o n ( 1 )

T y p e O p e r a t i o nM o n t a n t

C o m p t e

S o l d e

N o C o m p t e ( 1 )

L i b T y p C p t eN o T y p C p t e ( 1 )

T y p e C o m p t e

1

d i r i g e d a n st r ava i l l e

0..*

ou-ex

1 1

A g e n t

N o m A g e n tP r e n o m A g e n tA d r e s s e A g e n tS a l a i r e A g e n t

N u m A g e n t ( 1 )

e f f e c t u e >

1

0..*

FIG. 1 – Diagramme de classes modélisant la base de données utilisée comme support de stockage d’infor-mations par l’application bancaire considérée.

Ce diagramme UML est completé par l’ensemble de contraintes suivantes :

1. Le montant d’une opération est toujours positif.

2. Le type d’opération est soit RETRAIT soit DEPOT.

3. Le type de compte considéré est soit un compte COURANT soit un compte d’EPARGNE.

4. Par défaut, la date d’opération est la date du jour courant.

1

5. Un client possède toujours un nom et un prénom.

6. Un client est conseillé par un agent qui travaille dans l’agence qui connaît ce client.

7. Un client ne doit pouvoir retirer de l’argent que sur un compte qui lui appartient.

8. Un client ne doit pas pouvoir effectuer un retrait dont le montant est supérieur au solde du compteconcerné par cette opération de retrait.

9. Le directeur d’une agence est mieux payé que les agents de son agence.

10. Aucun salaire ne doit être inférieur au SMIC.

Travail à réaliser1. À l’aide des règles présentées en cours, traduisez le diagramme de classes UML de la figure 1 en un

ensemble de relations. Pour chacune des contraintes mentionnées ci-dessus, vous préciserez si ellepeut (ou non) être implantée par le langage de description des données de SQL. Pour les contraintespouvant être directement implantées au sein du script de création des structures de données, vousindiquerez les moyens utilisés pour vérifier ces contraintes (e.g. contrainte de type CHECK, contrainted’unicité, création de vues).

2. Construisez le graphe des dépendances fonctionnelles inter-relations, et proposez un ordre de créa-tion des tables.

3. Proposez un schéma relationnel (le plus fidèle possible au diagramme UML de la figure 1) en indi-quant les contraintes de cardinalité qui ne sont pas traduites dans ce modèle. Classez ces contraintessuivant leur type : contrainte d’attribut, de table ou/et de base.

4. Identifiez les éventuels problèmes rencontrés lors de la mise à jours des tables.

2

TD 4Définition et mise en œuvre de fonctions et de

procédures en PL/SQL

PrésentationDans le cadre de cet exercice, nous nous intéressons à l’application bancaire du TD

précédent. Afin de faciliter la manipulation et la mise à jour des informations conte-nues dans la base de donnée utilisée par cette application, nous souhaitons définir unensemble de fonctions et de procédures PL/SQL pour automatiser certains des traite-ments appliqués à cette base de données.

Travail à réaliserÉcrivez dans le langage PL/SQL d’Oracle– une fonction getClientId retournant l’identifiant d’un client (ie. NumClient) à

partir de son nom et de son prénom ;– une procédure printAccounts capable d’afficher le numéro et le libellé des dif-

férents comptes possédés par un client à partir de l’identifiant de ce dernier (ie.NumClient) ;

– une fonction hasADebitBalance retournant vrai si le compte spécifié en para-mètre est débiteur, et faux dans le cas contraire ;

– une fonction lastOperations capable de retourner les n dernières opérations ef-fectuées sur un compte donné, n étant un des paramètres de cette procédure ;

– une procédure setAccountManagementFees capable de débiter chaque comptecourant géré par une agence donnée d’un montant de M Euros pour frais degestion de compte.

1

TD 5Définition et mise en œuvre de triggers

PrésentationDans le cadre de cet exercice, nous considérons une nouvelle fois la base de don-

nées présentée dans le TD 3. Afin d’assurer la cohérence des informations contenuesdans cette base de données, nous souhaitons mettre en œuvre des déclencheurs capablesde vérifier les contraintes qui n’ont pas pu être implantées par le langage de descriptionde données de SQL. Ces contraintes sont le suivantes :

– Un client ne doit pouvoir retirer de l’argent que sur un compte qui lui appartient.– Un client ne doit pas pouvoir effectuer un retrait dont le montant est supérieur au

solde du compte concerné par cette opération de retrait.– Le directeur d’une agence est mieux payé que les agents de son agence.

Par ailleurs, on veut pourvoir fournir aux agents qui utilisent la base de données ban-caire une vision des données adaptée à leurs besoins. Ainsi, on souhaite leur offrirnotamment une vue leur permettant de percevoir le nom et le prénom des clients del’agence, ainsi que le numéro et le type du compte –ou des comptes– de ceux-ci.

Travail à réaliserÉcrivez dans le langage PL/SQL d’Oracle les triggers permettant de vérifier les

contraintes mentionnées ci-dessus, puis définissez en algèbre relationnelle et en SQLla vue présentée précédemment.

1

Travaux pratiques

TP 1Interrogation de bases de données relationnelleset création de structures relationnelles simples

Exercice 1Dans ce premier exercice, nous considérons une base de données relationnelle

« épicerie » constituée des relations suivantes :– client (cli_number, cli_name, cli_category)

– cli_number : clé primaire de la relation– cli_categorie : clé étrangère référençant l’attribut cat_label de la rela-

tion client_category

– product(prod_number, prod_label, prod_price)

– prod_number : clé primaire de la relation– client_category(cat_label, cat_reduction)

– cat_label : clé primaire de la relation– delivery(del_prod_num, del_cli_num, del_quantity)

– (del_prod_num, del_cli_num) : clé primaire de la relation– del_prod_num : clé étrangère référençant l’attribut prod_number de la rela-

tion product

– del_cli_num : clé étrangère référençant l’attribut cli_number de la relationclient

Donnez les requêtes SQL permettant de répondre aux questions suivantes :

1. Quels sont les noms des clients de catégorie C qui ont été livrés en cacahuètes ?

2. Quels sont les numéros des produits qui n’ont pas été livrés ?

3. Quels sont les numéros des produits qui ont été livrés au client numéro 3 et auclient numéro 5 ?

4. Quels sont les noms des clients qui ont été livrés en jambon et qui bénéficient deplus de 15% de réduction ?

Pour créer la base de données vous permettant de répondre aux questions précédentes,vous utiliserez le script script_tp-1.sql, script que vous trouverez dans le répertoiregl-5 dans votre forum.

1

Exercice 2Le dictionnaire des données est l’ensemble des informations gérées par le SGBD

afin de savoir quelles sont les données des bases, comment elles sont structurées, im-plantées, qui y a accès, etc. Le dictionnaire regroupe notamment les informations destrois niveaux de schémas : schémas externes, schéma conceptuel et schéma interne.

Dans un SGBD relationnel, le dictionnaire des données est généralement organisésous forme de relations. Le schéma des relations du dictionnaire s’appelle le "métaschéma". L’intérêt de ce choix est que le SGBD (tout comme les utilisateurs) disposede toute la puissance du langage de manipulation des données pour accéder et mettre àjour le dictionnaire.

1. Interrogez le dictionnaire des données Oracle afin de lister les tables présentesdans votre base de données grâce à la commande suivante :select * from user_tables

2. Pour obtenir une description de l’une de ces tables, vous pouvez utiliser la com-mande suivante :describe nom_de_la_table .

Exercice 3Dans le cadre de cet exercice, il vous est demandé de créer, de modifier et de sup-

primer une table, ainsi que d’ajouter, de supprimer et de mettre à jour des données danscette table. Ces opérations pourront être définies au sein d’un script SQL (i.e. fichierayant l’extension .sql).

Question 1 Créez la table acteurs dont le schéma relationnel est le suivant :acteurs (id_acteur : entier ;

prenom : chaîne (15),

nom : chaîne (15)

)

Exécutez le script de création.Exécutez le une nouvelle fois. Que remarquez vous ?

Question 2 Définissez au sein de votre script l’instruction SQL permettant de détruirela table acteurs, et exécutez votre script.

Question 3 Modifiez votre script afin que l’instruction SQL de création de la tableacteurs précise la clé primaire id_acteur et la contrainte de présence obligatoirepour les attributs nom et prenom.

Question 4

– Insérez les tuples suivants dans la table acteurs, et expliquez, le cas échéant,les éventuels messages d’erreurs.(1, 'Alain', 'Delon')

(2,'Jean', 'Gabin')

(3,'Isabelle', 'Adjani')

(1,'Catherine','Deneuve')

(4,'Jean','Reno')

Question 5 Modifiez le second tuple de la table acteurs afin que le champ nom dece tuple prenne la valeur NULL. Expliquez ce qui ce passe.

Question 6 Supprimez le premier tuple de la table acteurs.

TP 2Élaboration d'un schéma relationnel à partird'un diagramme de classes UML et utilisation

du SQL*Loader d'Oracle� 2 séances �

Présentation

Quand on dispose d'un grand volume de données, il est peut être di�cileet laborieux d'insérer chacune de ces données dans les tables de la base dedonnées considérée via la commande INSERT. A�n de faciliter l'insertion dedonnées dans les tables d'une base de données, Oracle o�re un utilitaire dechargement de données baptisé SQL*Loader. Cet utilitaire permet de remplirles tables d'une base de données avec les informations contenues dans des �chiersexternes. Sous linux cet utilitaire prend le nom de sqlldr (ou sqlload). Le principede fonctionnement du SQL*Loader d'Oracle est illustré dans la �gure 1.

Fig. 1 � Principe de fonctionnement de SQL*Loader

Pour insérer les données dans les tables SQL*Loader utilise deux �chiersprincipaux : le �chier de données, qui contient les informations à chargées, et le�chier de contrôle, qui dé�nit des informations telles que le nom du �chier de

1

données et les �chiers d'erreurs, le mode de remplissage de la table (ajout, rem-placement ou insertion), la (ou les) table(s) d'acceuil, les règles d'identi�cationdes champs dans le �chier de données, l'ordre et le type des attributs dans le�chier de données. Le nom du �chier de données est généralement su�xé par.dat et celui du �chier de contrôle par .ctl.

2

Le format du �chier de contrôle est le suivant :

LOAD DATA

INFILE {<fichier de données> | *}

BADFILE <fichier des données erronées>

DISCARDFILE <fichier des données rejetéees>

{APPEND | REPLACE | INSERT} INTO TABLE <table d'accueil>

FIELDS TERMINATED BY '<caractère>' -- séparateur de champs

[OPTIONALLY ENCLOSED BY '<caractère>'] -- emballage des donnés

[TRAILING NULLCOLS] -- derniers champs vides

(

<champ 1> [<type>], -- ordre des champs

<champ 2> [<type>], -- < type> est optionnel

... ,

<champ n> [<type>]

)

[BEGINDATA -- si INFILE = *

<données>] -- si INFILE = *

Les données non chargées sont placées dans des �chiers spéci�ques (cf BAD-FILE et DISCARDFILE).

Le �chier des données rejetées (.bad) contient les données ayant provoquéeune erreur Oracle lors de leur insertion ou les données pour lesquelles il estimpossible de déterminer s'il faut les charger (cf condition WHEN). Le �chierdes données écartées contient les données ne répondant pas aux conditions dechargement. Le chargement s'e�ectue soit en mode ajout (APPEND), soit enmode remplacement des tuples existants (REPLACE) après suppression, soiten mode insertion dans une table obligatoirement vide (INSERT). La liste deschamps indique l'ordre des champs dans le �chier de données (pas dans la tabled'accueil). Le type des champs indique le type dans le �chier de données. Chaquetype est ensuite converti dans le type du champ de la table d'accueil.

Les �chiers peuvent être organisés de la manière suivante :� maTable.sql (script de création de la table d'accueil),� maTable.ctl (�chier de controle du chargement par sqlload),� maTable.dat (�chier des données à charger),� maTable.csh (script csh d'exécution de toutes les opérations).

La liste des �chiers produits par l'appel de sqlldr (ou sqlload) est la suivante :maTable.log (trace d'éxécution du chargement),maTable.bad (données non chargées),maTable.dis (données non chargées).Le script maTable.csh pourrait être par exemple :

# !/usr/bin/tcsh -xvf

# Création de la table d'accueil

3

sqlplus @maTable

# Chargement des données

sqlldr control=maTable

# Examen du fichier .log

more maTable.log

# Examen des données chargées

sqlplus @maTableTests

Travail à réaliser

1. Consultez les di�érents �chiers contenus dans le répertoire gl-5 de votreforum.

2. En vous inspirant des �chiers que vous venez de consultez et en utilisant lesrésultats obtenus en travail dirigé, construisez la base de données relativeà la gestion des épreuves d'athlétisme. Le diagramme UML modélisantcette base de données est présenté dans la �gure ci-dessous.

Fig. 2 � Diagramme de classes modélisant la base de données utilisée commesupport de stockage d'informations par l'application de gestion des épreuvesd'athlétisme.

3. Lorsque vous avez créé l'ensemble de vos tables, initialisez chacune decelles-ci avec au moins cinq tuples en utilisant le SQL Loader d'Oracle.

4. E�ectuez ensuite quelques requêtes sur les tables ainsi remplies a�n d'envéri�er le contenu.

Vous devez rendre un ensemble de scripts SQL contenant toutes les opérationsSQL que vous avez e�ectuées pour créer et initialiser les tables, et véri�er lecontenu de celles-ci. Vous documenterez ce script a�n d'expliquer votre démarcheet vos choix de conception.

4

TP 3Conception d’une base de données relationnelle à partir d’undiagramme de classes UML et implantation de fonctions, de

procédures et de triggers en PL/SQL– 3 séances –

PrésentationDans le cadre de ce travail, nous considérons la base de données bancaire étudiée en travail dirigé. Cette

base de données est décrite par le diagramme de classes UML présenté dans la figure 1.

< c o n c e r n e

A g e n c e

1

1..*

0..*

1

1..*

c o n n u d e >

< c o n s e i l l e

< a p p a r t i e n t

TelAgence

0..*

1

N u m A g e n c e ( 1 )

0..*

0..*1 < d e t y p e

L e g e n d e :

(1 ) i nd ique l e s iden t i f i an t s

C l i en t

N o m C l i e n tP r e n o m C l i e n tA d r e s C l i e n t

N u m C l i e n t ( 1 )

O p e r a t i o n

D a t e O p e r a t i o n

N u m O p e r a t i o n ( 1 )

T y p e O p e r a t i o nM o n t a n t

C o m p t e

S o l d e

N o C o m p t e ( 1 )

L i b T y p C p t eN o T y p C p t e ( 1 )

T y p e C o m p t e

1

d i r i g e d a n st r ava i l l e

0..*

ou-ex

1 1

A g e n t

N o m A g e n tP r e n o m A g e n tA d r e s s e A g e n tS a l a i r e A g e n t

N u m A g e n t ( 1 )

e f f e c t u e >

1

0..*

FIG. 1 – Diagramme de classes modélisant la base de données utilisée comme support de stockage d’infor-mations par l’application bancaire considérée.

Ce diagramme UML est complété par l’ensemble de contraintes suivantes :

1. Le montant d’une opération est toujours positif.

1

2. Le type d’opération est soit RETRAIT soit DEPOT.

3. Le type de compte considéré est soit un compte COURANT soit un compte d’EPARGNE.

4. Par défaut, la date d’opération est la date du jour courant.

5. Un client possède toujours un nom et un prénom.

6. Un client est conseillé par un agent qui travaille dans l’agence qui connaît ce client.

7. Un client ne doit pouvoir retirer de l’argent que sur un compte qui lui appartient.

8. Un client ne doit pas pouvoir effectuer un retrait dont le montant est supérieur au solde du compteconcerné par cette opération de retrait.

9. Le directeur d’une agence est mieux payé que les agents de son agence.

10. Aucun salaire ne doit être inférieur au SMIC.

Ce travail, qui se déroulera sur 3 séances, consistera à concevoir sous Oracle la base de données décriteci-dessus, à implanter des fonctions et des procédures permettant de manipuler facilement le contenu decette base de données, ainsi que des triggers permettant de vérifier certaines propriétés de celle-ci lors del’insertion, de la modification et de la suppression des données.

Travail à réaliser1. Construisez la base de données banquaire sous Oracle en utilisant les informations données en an-

nexe.

2. Lorsque vous avez créé l’ensemble de vos tables, initialisez chacune de celles-ci avec au moins cinqtuples en utilisant le SQL Loader d’Oracle.

3. Effectuez ensuite quelques requêtes sur les tables ainsi remplies afin d’en vérifier le contenu.

4. Implantez les procédures et les fonctions suivantes :– une fonction getClientId retournant l’identifiant d’un client (ie. NumClient) à partir de son nom et

de son prénom ;– une procédure printAccounts capable d’afficher le numéro et le libellé des différents comptes

possédés par un client à partir de l’identifiant de ce dernier (ie. NumClient) ;– une fonction hasADebitBalance retournant vrai si le compte spécifié en paramètre est débiteur, et

faux dans le cas contraire ;– une fonction lastOperations capable de retourner les n dernières opérations effectuées sur un

compte donné, n étant un des paramètre de cette procédure ;– une procédure setAccountManagementFees capable de débiter chaque compte courant géré par

une agence donnée d’un montant de M Euros pour frais de gestion de compte.

5. Définissez un script SQL permettant d’ajouter lors de la création de la base de données les triggersassurant :– qu’un client ne puisse retirer de l’argent que sur un compte qui lui appartient.– qu’un client ne puisse pas effectuer un retrait dont le montant est supérieur au solde du compte

concerné par cette opération de retrait.– que le directeur d’une agence est mieux payé que les agents de son agence.

6. Ajoutez des données dans vos tables afin de vérifier le bon fonctionnement de vos triggers.

Vous devez rendre un ensemble de scripts SQL contenant toutes les opérations SQL que vous avez effec-tuées pour créer et initialiser les tables, et vérifier le contenu de celles-ci. Les procédures, les fonctions etles triggers seront définis au sein de scripts SQL spécifiques. Vous documenterez chacun de ces scripts afind’expliquer votre démarche et vos choix de conception.

2