Travaux Dirigés de Fonctionnement d'un SGBD

Ce sujet est proposé par Isabelle Mirbel et Pierre Crescenzo, inspirés par Richard Grin.

Vous pouvez accéder au support de CM et au sujet des autres TD. Vous pouvez voir ces supports d'enseignement intéressants ou cet autre.

Schéma Relationnel

Durant ce TD et les prochains, vous utiliserez le schéma relationnel suivant :

• EMP(Matr, NomE, Poste, Sup, DatEmb, Sal, Comm, Dept)
• DEPT(Dept, NomD, Lieu)
• PROJET(CodeP, NomP)
• PARTICIPATION(Matr, CodeP, FonctionP)

Installation de l'environnement de travail

Suivez les indications données ci-dessous (valides depuis le réseau de la Faculté des Sciences de Nice).

1. Créez un répertoire ~/oracle.
2. Placez les fichiers contraintes.sql, inibase.sql, login.sql et tnsnames.ora dans ce répertoire.
3. Placez le script Shell debsql dans un des répertoires de votre PATH, par exemple ~/bin. Rendez ce script exécutable s'il ne l'est pas déjà.

Testez maintenant votre connexion au serveur de bases de données en suivant les indications données ci-dessous.

1. Lancez la commande debsql : une nouvelle fenêtre s'ouvre.
2. Utilisez l'identifiant et le mot de passe personnels que vous donne l'enseignant pour vous connecter. (Le tout premier mot de passe demandé par le logiciel est systématiquement considéré comme faux. Ne vous inquiétez pas et recommencez.)
3. Changez votre mot de passe pour en mettre un plus sûr : pour cela, utilisez la commande
   grant connect to votre-nom-de-login identified by votre-nouveau-mot-de-passe;
   Attention, votre mot de passe apparaîtra en clair sur votre écran, faites donc ce changement discrètement.
4. Lancez la commande
   @ inibase
   pour créer les données de la base sur laquelle vous travaillerez durant les travaux dirigés. Vous aurez des messages d'erreur normaux indiquant que des tables n'existent pas : en effet, la procédure tente de supprimer d'éventuelles anciennes tables avant de les (re)créer. Pour voir les commandes de création lancées, consultez inibase.sql.
5. Lancez la commande
   select nome, sal from emp;
   pour voir si vous avez bien des données dans la table emp.
6. Quittez le logiciel debsql par la commande
   exit
   

Interrogation de la base par une interface Web

• L'outil lancé par la commande debsql que vous venez de tester n'est pas pratique : son interface de saisie et de visualisation est très peu ergonomique. Aussi, nous vous proposons d'utiliser plutôt, pour les travaux dirigés, une interface Web qui vous donnera accès également au serveur de bases de données configuré pour les exercices. Pour cela, il vous suffit de vous rendre à l'adresse http://sirocco.unice.fr/isqlplus/ (accessible depuis le réseau de la Faculté des Sciences de Nice). Pour vous connecter, laissez le champ Identificateur de connexion vide.
• L'interface de saisie ne conserve pas la trace des commandes que vous tapez. Aussi, nous vous conseillons très fermement, durant tous vos travaux dirigés, de sauvegarder toutes vos commandes dans un fichier grâce à un éditeur de textes, emacs par exemple.
• Comme pour debsql, lancez la commande
  select nome, sal from emp;
  pour voir si vous avez bien des données dans la table emp.

Quelques premières requêtes SQL faciles, pour prendre en main l'interface Web

1. Affichez toutes les données de la table emp.
2. Affichez seulement la liste des matricule et nom des employés.
3. Affichez le nom des employés du département 20.
4. Trouvez le poste des employés dont le salaire est supérieur à 3000 €.
5. Affichez le nom des commerciaux et ingénieurs avec, pour chacun, le total de ce qu'il perçoit (salaire + commission).

Créations de table

Créez les tables projet et participation pour traduire le schéma relationnel donné au début de ce sujet. N'oubliez pas les contraintes d'intégrité.

Dictionnaire des données

1. Lancez la commande
   select * from user_tables;
   
2. Faites afficher le nom de toutes vos tables.
3. Listez toutes les tables du dictionnaire des données (dict). Consultez quelques tables données par cette liste. Sélectionnez les colonnes qui vous intéressent en vous aidant de la commande describe.
4. En utilisant la vue user_tab_columns, faites afficher le nom et type des colonnes de la table emp avec le nombre de chiffres significatifs et le nombre de chiffres après la virgule si la colonne est numérique.

Utilisation du dictionnaire des données pour voir les contraintes d'intégrité

Toute modification des données de la base doit respecter les règles imposées par les contraintes d'intégrité. Lancez la commande
@ contraintes
pour faire afficher les contraintes des tables que vous utilisez. Consultez et étudiez contraintes.sql. Voyez en particulier comment le dictionnaire des données est interrogé.

Modifications simples des données

1. Créez un nouveau département : 50, COMPTABILITE, TOULOUSE.
2. Créez un nouveau département : 60, DIRECTION.
3. Dupliquez l'enregistrement du département 20 en lui affectant le numéro de département 25 (en utilisant un select pour récupérer les informations sur le département 20).
4. Le département 60 déménage à Bordeaux. Enregistrez ce fait dans la base.
5. Promouvez M. Mercier au poste de PDG dans le département 60.
6. M. Biraud prend sa retraite. Retirez-le de la table des employés.
7. Augmentez de 10 % le salaire de tous les employés du département 10.
8. M. Simon est embauché comme commercial avec un salaire de 1500 €. Ajoutez-le à la base avec le matricule 7910, dans le département 30.