Base de données PROJET

ORACLE : TP numéro 2

Requêtes sur la base de données PROJET

Rappels

• Schéma conceptuel
• Contenu des tables

Requêtes
Réponses

Base de données PROJET

Attributs

Nom	Type	Définition
coût	réel	coût d'un projet exprimé en millions de francs
début	date	date début du projet
durée	entier	durée estimée du projet en nombre de mois
id	entier	identitifiant d'un projet, d'une personne ou d'une tâche
formation	chaîne	formation initiale d'une personne
nom	chaîne	nom d'un projet, d'une personne ou d'une tâche
personne	entier	identifiant de personne
projet, projA, projB	entier	identifiant de projet
responsable	entier	identifiant de personne responsable d'un projet
salaire	réel	salaire annuel d'une personne exprimé en euros
tâche	entier	identifiant de tâche

Relations

Nom	Attributs	Clé
personne	id, nom, formation, salaire	id
tâche	id, nom	id
projet	id, nom, responsable, coût, début, durée	id
intervenant	projet, tâche, personne	projet, tâche, personne
collaboration	projA, projB	projA, projB

Contraintes d'intégrité

proj _responsable(projet)    inclus     proj _id(personne)
proj _projet(intervenant)    inclus     proj _id(projet)
proj _tâche(intervenant)    inclus     proj _id(tâche)
proj _personne(intervenant)    inclus     proj _id(personne)
proj _projA(collaboration)    inclus     proj _id(projet)
proj _projB(collaboration)    inclus     proj _id(projet)

Base de données PROJET

Relations en extension à un instant t donné

Relation Personne

 ID NOM FORMATION SALAIRE
 ----- ------------ -------------------- ----------
 1 Joelle botaniste 22761
 2 Andre geographe 24279
 5 Stephane geologue
 4 Alain technicien 21244
 67 Christian ingenieur
 78 Bertrand ingenieur agronome 27314
 19 Gerard hydrologue 23596
 20 Pierre hydrologue 24279
 15 Martine ingenieur 27314
 6 David informaticien 26555
 13 Marie medecin 31866
 14 Anne assistante sociale
 41 Colette medecin 29590
 43 Jean medecin 27314
 50 Fabien veterinaire 28072
 18 Patrice statisticien 25796
 11 Sebastien hydrologue 24279
 7 Rene medecin 30349
 
 

Relation Tâche

 ID NOM
 ----- -------------------------
 1 mesures terrain
 2 releves topographiques
 3 releves hydrographiques
 8 cartographie
 11 surveillance capteurs
 12 vaccination betail
 13 analyse des donnees
 14 bases de donnees
 17 coordination
 21 estimation cout
 34 vaccination population
 56 suivi des plantations
 83 communication population
 
 

Relation Projet

 ID NOM RESPONSABLE COUT DEBUT DUREE
 ----- ------------------------------ ----------- ----- ----------- -----
 1 etude desertification 67 3.0 01-jan-1997 36
 2 installation canaux irrigation 11 03-mar-1998
 3 evaluation etendue epidemie 13 .5 6
 4 campagne vaccination 7 1.0 10-aug-2001
 
 

Relation Collaboration

 PROJA PROJB
 ---------- ----------
 1 2
 3 4
 
 

Relation Intervenant

 PROJET TACHE PERSONNE
 ---------- ---------- ----------
 1 1 1
 1 1 2
 1 1 5
 1 11 4
 1 17 67
 1 56 78
 1 83 78
 1 3 19
 1 13 18
 2 11 4
 2 83 20
 2 3 20
 2 21 15
 3 14 6
 3 83 14
 3 13 18
 4 17 41
 4 34 43
 4 12 50
 
 
 

Reqêtes pour la base Projet

En algèbre relationnelle et SQL

1. Liste des projets dont la durée est supérieure à 1 an.
2. Liste des couples (projA, projB) pour les projets qui sont en collaboration avec le projet 4.
3. Liste des identifiants de projets (soit projA, soit projB) qui sont en collaboration avec le projet 2.
4. Nom des personnes intervenant dans le projet 1.
5. Liste des noms de tâches remplies dans le projet 2.
6. Liste des formations des personnes impliquées dans le projet 3.
7. Liste des projets qui comportent la tâche communication population.
8. Nom du responsable du projet 4.
9. Numéro des personnes qui travaillent sur le même projet que la personne de numéro 5.
10. Liste des noms des personnes qui sont impliquées dans plusieurs projets.
11. Liste des projets pour lesquels le responsable intervient également pour une tâche quelconque.
12. Nom des projets pour lesquels une même personne intervient dans différentes tâches.

En SQL uniquement

Modification de l'affichage

1. Liste des identifiants de personnes dans la relation intervenant en supprimant les doublons.
2. Liste des personnes par ordre croissant d'identifiant.
3. Liste des personnes par ordre alphabétique des formations et salaires décroissants.
4. Nom et formation de toutes les personnes en remplaçant l'intitulé de colonne formation par diplôme.
5. Nom, formation et salaire des personnes en formattant le champ salaire de façon à distinguer les valeurs au-dessus de 1000.
6. Affichage des noms de projets en les réduisant à 20 caractères.
7. Affichage de la date début de la table projet avec le format suivant : 2 chiffres pour le jour, 3 lettres pour le mois et année codée sur 4 chiffres.
8. Affichage de la date début de la table projet avec le mois codé sur 2 chiffres.

Emploi de fonctions d'agrégation : AVG, MAX, MIN, SUM, COUNT

1. Formation pour laquelle le salaire est minimum.
2. Coût moyen des projets.
3. Nom du projet qui a la durée la plus grande.
4. Somme des salaires annuels des médécins.
5. Somme des salaires des personnes qui interviennent dans le projet 2.
6. Nombre de personnes distinctes figurant dans la relation intervenant.

Fonctions d'agrégation calculées pour des groupes de n-uplets

1. Pour chaque personne, nombre de projets où elle intervient.
2. Salaire moyen pour chaque formation.
3. Nombre de personnes intervenant pour chaque projet.
4. Projet ayant le nombre minimum de personnes comme intervenants.

Emploi de IN, BETWEEN, LIKE,...

1. Liste des formations autres que hydrologue, botaniste, medecin.
2. Identifiant et nom des personnes qui ne sont responsables d'aucun projet.
3. Nom des médecins qui ont un salaire compris entre 27 000 et 30 000 euros.
4. Liste des tâches qui comportent le terme relevé.
5. Liste des triplets (id, nom, responsable) des projets qui contiennent le terme canaux dans leur nom.
6. Nom complet des personnes dont le nom commence par la lettre F.

Calcul avec des valeur NULL

1. Liste des projets pour lesquels on connaît le coût (coût non null).

 /*
 En algèbre relationnelle et SQL
 */
 
 /* 1. */ 
 
 SELECT * FROM projet WHERE duree > 12;
 
 /* 2. */ 
 
 SELECT * FROM collaboration WHERE projA = 4 or projB = 4;
 
 /* 3. */
 
 SELECT id FROM projet WHERE id IN (
 (SELECT projA FROM collaboration WHERE projB = 2)
 UNION (SELECT projB FROM collaboration WHERE projA = 2)
 );
 
 /* ou (est aussi autorisé en SQL) */
 
 (SELECT projA FROM collaboration WHERE projB = 2)
 UNION (SELECT projB FROM collaboration WHERE projA = 2)
 
 
 /* 4. */
 
 SELECT nom FROM personne WHERE id IN
 (SELECT personne FROM intervenant WHERE projet = 1);
 
 /* 5. */
 
 SELECT nom FROM tache WHERE id IN
 (SELECT tache FROM intervenant WHERE projet = 2);
 
 /* 6. */
 
 SELECT formation FROM personne WHERE id IN
 (SELECT personne FROM intervenant WHERE projet = 3);
 
 /* 7. */
 
 SELECT * FROM projet WHERE id IN
 (SELECT projet FROM intervenant WHERE tache IN
 (SELECT id FROM tache WHERE nom = 'communication population'));
 
 /* 8. */
 
 SELECT nom FROM personne WHERE id IN
 (SELECT responsable FROM projet WHERE id = 4);
 
 /* 9. */
 
 SELECT personne FROM intervenant WHERE projet IN
 (SELECT projet FROM intervenant WHERE personne = 5);
 
 /* ou */
 
 SELECT R1.personne FROM intervenant R1, intervenant R2 WHERE
 R1.projet = R2.projet and R2.personne = 5;
 
 /* 10. */
 
 SELECT nom FROM personne WHERE id IN
 (SELECT R1.personne FROM intervenant R1, intervenant R2 WHERE
 R1.projet <> R2.projet and R1.personne = R2.personne);
 
 
 /* 11. */
 
 SELECT * FROM projet WHERE responsable IN
 (SELECT personne FROM intervenant);
 
 /* ou (si on considère que la tache doit etre une tache du projet
 dont la personne est responsable) */
 
 SELECT * FROM projet WHERE responsable IN
 (SELECT personne FROM projet,intervenant 
 WHERE responsable = personne and id = projet);
 
 
 /* 12. */
 
 SELECT nom FROM projet WHERE id IN
 (SELECT R1.projet FROM intervenant R1, intervenant R2 WHERE
 R1.tache <> R2.tache 
 and R1.personne = R2.personne 
 and R1.projet = R2.projet);
 
 
 
 /*
 En SQL uniquement
 
 Modification de l'affichage
 */
 
 /* 1. */
 
 SELECT DISTINCT personne FROM intervenant;
 
 /* 2. */
 
 SELECT * FROM personne ORDER BY id ASC;
 
 /* 3. */
 
 SELECT * FROM personne ORDER BY formation ASC, salaire DESC;
 
 /* 4. */
 
 SELECT nom, formation diplome FROM personne;
 
 /* 5. */ 
 
 COLUMN salaire FORMAT 99,999
 
 /* 6. */
 
 SELECT substr(nom,1,20) "nom" FROM projet;
 
 /* ou */
 
 COLUMN nom FORMAT A20 TRUNC
 
 /* 7. */
 
 SELECT to_char(debut,'DD/MON/YYYY') "début" FROM projet;
 
 /* 8. */
 
 SELECT to_char(debut,'DD/MM/YYYY') "début" FROM projet;
 
 
 /*
 Emploi de fonctions d'agrégation : AVG, MAX, MIN, SUM, COUNT
 */
 
 /* 1. */
 
 SELECT formation FROM personne WHERE salaire IN
 (SELECT min(salaire) FROM personne);
 
 /* 2. */
 
 SELECT AVG(cout) "coût moyen" FROM projet;
 
 /* 3. */
 
 SELECT nom FROM projet WHERE duree IN
 (SELECT max(duree) FROM projet);
 
 /* 4. */
 
 SELECT sum(salaire) "somme des salaires" FROM personne
 WHERE formation = 'medecin';
 
 /* 5. */
 
 SELECT sum(salaire) "somme des salaires" FROM personne WHERE id IN
 (SELECT personne FROM intervenant WHERE projet = 2);
 
 /* 6. */
 
 SELECT count(DISTINCT personne) "nombre de personnes" FROM intervenant;
 
 
 /*
 Fonctions d'agrégation calculées pour des groupes de n-uplets
 */
 
 /* 1. */
 
 SELECT personne, count(DISTINCT projet) "nombre de projets" FROM intervenant
 GROUP BY personne;
 
 /* 2. */
 
 SELECT formation, avg(salaire) "salaire moyen" FROM personne
 GROUP BY formation;
 
 /* 3. */
 
 SELECT projet, count(DISTINCT personne) "nombre de personnes" FROM intervenant
 GROUP BY projet;
 
 /* 4. */
 
 SELECT projet FROM intervenant GROUP BY projet HAVING count(DISTINCT personne) IN
 (SELECT min(count(DISTINCT personne)) FROM intervenant
 GROUP BY projet);
 
 
 /*
 Emploi de IN, BETWEEN, LIKE,...
 */
 
 /* 1. */
 
 SELECT formation FROM personne WHERE formation NOT IN ('hydrologue','botaniste','medecin');
 
 /* 2. */
 
 SELECT id, nom FROM personne WHERE id NOT IN (SELECT responsable FROM projet);
 
 /* 3. */
 
 SELECT nom FROM personne WHERE formation = 'medecin' and (salaire BETWEEN 27000 AND 30000);
 
 /* 4. */
 
 SELECT * FROM tache WHERE nom LIKE '%releve%';
 
 /* 5. */
 
 SELECT id, nom, responsable FROM projet WHERE nom LIKE '%canaux%';
 
 /* 6. */
 
 SELECT nom FROM personne WHERE nom LIKE 'F%';
 
 
 /*
 Calcul avec des champs NULL
 */
 
 /* 1. */ 
 
 SELECT * FROM projet WHERE cout IS NOT NULL;
 
 o---> http://icps.u-strasbg.fr/~violard/BD/TP2/index.html
 

__..._.--._.···._.·´¯`·._.  Jean-Christophe PACCHIANA  ._.·´¯`·._.···._.--._...__