mini projet base de donnée -...

1

Mini projet

Base de donnée

Professeur responsable : Slimane HAMMOUDI

Etudiants I2 : Sébastien FRANÇOIS

Yvonnick BRUNET

2

1. Introduction 3

2. Construction du schéma conceptuel entité-association 3

1) Liste des entités 3

2) Liste des attributs 3

3) Associations entre entités 5

4) Caractéristiques des associations 5

5) Construire le schéma Entité Association 6

6) Vérification du schéma obtenu 6

3. Modélisation en UML du système Agenda 7

7) Diagramme des cas d’utilisations du système de gestion des Agendas 7

8) Diagramme de classe UML équivalent 8 1. Diagramme de classe UML 8 2. Richesse du formalisme et Différences entre diagrammes de classe UML et Entité Association 8

4. Passage du schéma conceptuel Entité-Association au schéma relationnel Logique. 9

9) En déduire l’ensemble des relations formant le schéma relationnel de la base de

données 9

10) Dessiner le diagramme logique de la base de donnée Agenda 11

5. Création, Mise à jour et Interrogation de la base de données « Agenda » 12

11) Création des tables de notre base de données 12

12) L’insertion de tuples dans les différentes tables 14

13) Interrogation de la base de données : 15 3. Le nom, prénom, et département des employés dont on ignore l’adresse électronique, ordonnés par

département et nom. 15 4. Les séminaires futurs du département Informatique 15 5. Les activités personnelles dans l’agenda de Monsieur Schang qui n’ont pas été insérées par lui 17 6. Les conflits d’horaires (entre activités personnelles et départementales) de Monsieur Percher 17 7. L’agenda de Monsieur Boissinot le 09 Janvier 2006 18 8. Le jour où Monsieur Schang a le plus d’activités personnelles 20 9. Les professeurs libres d’électronique le 11 Janvier 2006 pour remplacer Monsieur Guitton de 13H30

à 15H30 20 10. Les personnes qui été absentes aux activités départementales d’automatique 21 11. Le volume horaires des activités personnelles de Monsieur Genet le 10 Janvier 2006 22 12. Les supérieurs hiérarchiques de Monsieur Guitton dans le département électronique 23

6. Conclusion 23

3

1. Introduction

Avec l’avènement de l’informatique, la mise en place d’une stratégie de stockage des données

s’est vite avérée indispensable. Les premiers programmes informatiques stockaient des

informations de manière linéaire, ce qui posait par la suite des problèmes pour effectuer des

recherches et des calculs sur les informations enregistrées.

Des chercheurs ont alors mis au point des modèles permettant de représenter sur informatique

des éléments réels et de leur ajouter des attributs, de pouvoir les ranger, les trier.

C'est Edgar F. Codd qui en juin 1970 rédigea un article décrivant un modèle de donnée

relationnel. Ce modèle a par la suite servi de référence et IBM a développé un langage, le

Structured English Query Language ("SEQUEL") pour mettre en œuvre le modèle de

Codd.

En 1979, Relational Software, Inc. (actuellement Oracle Corporation) présenta la première

version commercialement disponible de SQL.

Par la suite, le SQL sera normalisé par l’ANSI et l’ISO en 1986 et 1987.

L’objectif du mini-projet est de se familiariser à la modélisation d’un Système de Gestion de

Base de Donnée à partir d’un cahier des charges. Celui-ci décrit les besoins pour la création

d’un système l’emploi du temps d’entreprise. Le modèle sera ensuite concrétiser par sa mise

en place dans une base de donnée Oracle, afin que nous puissions nous familiariser avec les

requêtes SQL.

2. Construction du schéma conceptuel entité-association

1) Liste des entités

• Département

• Agenda département

• Activité départementale

• Procès verbal

• Absences

• Employé

• Agenda

• Activités

• Alertes

• Activité Départementale et Verbale

2) Liste des attributs

Département num identifiant du département

nom nom du département

numChef pointe sur le responsable du département

numAgendaDept pointe sur l’agenda du département

Agendadept numAgenda identifiant de l’agenda de département

dateMAJ date de dernière modification de l’agenda

4

Activitésdept numAct identifiant de l’activité départementale

typeD réunion / séminaire / cours / mini projet

descript description précise

dateAct date de l’activité

hDébut heure de début de l’activité

hFin heure de fin de l’activité

dateCreation date de création de l’activité

createur créateur de l’activité

numAgenda pointe sur l’agenda auquel l’activité appartient

Procesverbal numProcesV identifiant du procès verbal

resum résumé de l’activité

Absent numEmployé pointe sur l’employé absent

numActDept pointe sur l’activité

motif motif de l’absence

Employe numEmploye identifiant de l’employé

nom nom de l’employé

prénom prénom de l’employé

telIntern numéro de téléphone interne de l’employé

email adresse email de l’employé

niveau niveau hierarchique

numDept pointe sur le département de l’employé

Agenda numAgenda identifiant de l’agenda

dateCreation date de création de l’agenda

numEmploye pointe sur l’employé associé

Activités numActivité identifiant de l’activité

typeA réunion / séminaire / cours / mini projet

description description précise

dateAct date de l’activité

hDébut heure de début de l’activité

hFin heure de fin de l’activité

dateCreation date de création de l’activité

createur créateur de l’activité

visible activité professionnelle ou personnelle ?

numAgenda pointe sur l’agenda auquel l’activité appartient

Alertes type type d’alerte (email, sms etc…)

delais delais d’envoi du rappel avant l’activité

numActivité pointe sur l’activité associée à l’alerte

ActDeptVerbal numProcesV pointe sur l’activité départementale associée

numProcesV pointe sur le procès verbal associé

5

3) Associations entre entités

Associations Entités liées Emploie Département, employé

Dirige employé, département

Est_absent employé, activités

Possède employé, agenda

Possède département, agenda département

Peut_avoir activités, alertes

Contient agenda, activités

Contient Agenda département, activités départementales

Etablit activités départementales, procès verbal

4) Caractéristiques des associations

Association emploie Sans attributs propre, dimension 2. Elle relie un département et les employés qui y

travaillent. Elle est de cardinalité I-N puisqu’un employé donné n’a qu’un seul

département.

Association dirige Sans attributs propre, dimension 2. Elle relie un employé et le département qu’il dirige.

Elle est de cardinalité I-N car un chef peut avoir plusieurs départements.

Association est_absent Sans attributs propre, dimension 2. Elle relie un employé et l’activité départementale à

laquelle il ne pourra assister. Elle est de cardinalité I-I puisqu’un employé est absent ou

non lors d’une activité.

Associations possède Sans attributs propre, dimension 2. Elle relie un employé et son, agenda, ou bien un

département et son agenda. Elle est de cardinalité I-I puisqu’un employé, un

département n’a qu’un seul agenda.

Association peut_avoir Sans attributs propre, dimension 2. Elle relie une activité personnelle et une alerte. Elle

est de cardinalité I-N pour qu’une activité puisse avoir plusieurs alertes différentes.

Association contient Sans attributs propre, dimension 2. Elle relie un agenda et les activités qu’il contient.

Elle est de cardinalité I-N puisqu’une activité n’est placée que dans un seul agenda,

personnel ou départemental et il peut y avoir plusieurs activités.

Association établit Sans attributs propre, dimension 2. Elle relie une activité départementale et son procès

verbal. Elle est de cardinalité I-I puisqu’il n’y a qu’un procès verbal par activité

départementale.

• Un employé peut être responsable de plusieurs départements.

6

5) Construire le schéma Entité Association

6) Vérification du schéma obtenu

Nous avons simplifié la hiérarchie des employés en n’utilisant pas une table à part entière,

mais en stockant simplement un chiffre représentant le niveau de l’employé en attribut de

l’entité employé.

Le cycle présent entre les entités Employé, Département, Agenda Départemental, Activités

Départementales est normal car la « fermeture » entre Employé et Activités Départementales

est simplement du à la possibilité de signaler des absences.

L’attribut type a été supprimé du procès verbal puisque l’on peut le retrouver grâce à

l’Activité Départementale à laquelle il est associé.

7

3. Modélisation en UML du système Agenda

7) Diagramme des cas d’utilisations du système de gestion des Agendas

8

8) Diagramme de classe UML équivalent

1. Diagramme de classe UML

2. Richesse du formalisme et Différences entre diagrammes de classe UML et Entité Association

On peut donc facilement observer que le schéma conceptuel Entité-Association est très proche

du diagramme de classe du formalisme UML, les représentations des cardinalités sont

simplement inversées, on notera la présence supplémentaire des types des attributs en UML.

Enfin UML est un formalisme, ici nous ne comparons qu’un seul de ses diagrammes, alors

qu’il nous a aussi servi à modéliser le cahier des charges dans le diagramme d’activités.

9

4. Passage du schéma conceptuel Entité-Association au schéma relationnel Logique.

9) En déduire l’ensemble des relations formant le schéma relationnel de la base de données

D’après le schéma conceptuel Entité-Association, on a crée pour chaque entité une table.

Ainsi les premières tables qui ont été crées sont les tables :

- Département

- Employé

- Agenda

- Activités

- Alertes

- Agenda Départemental

- Activités Départementales

- Procès Verbal

Les clés primaires sont de type un nombre pour éviter de se soucier si une clé est belle et bien

unique.

Les cardinalités N-I :

Puis pour des raisons de cardinalités, lorsque l’association binaire un à plusieurs est traduit

par un report de clé. En effet la clé primaire de l’entité participant côté N est ajoutée à l’autre

entité en tant que clé étrangère. Les associations Activités-Alertes, Agenda-Activités,

Département-Employé, Agenda Départemental-Activités Départementales ont toutes des

cardinalités N-I ce qui implique que les clés primaires des premières entités seront des clés

étrangères dans les deuxièmes entités.

Nom des entités Entité avec cardinalité N Entité avec cardinalité I

Clé primaire Clé primaire Clé étrangère

Activités-Alertes numActivite Type, Delais,

numActivite

numActivite

Agenda-Activités numAgenda numActivité numAgenda

Département-

Employé

num numEmployé numDept

Agenda

Départemental-

Activités

Départementales

numAgenda numAct numAgenda

Pour l’entité Alertes, on a décidé de mettre les trois attributs en clé primaire. De cette façon,

on peut envoyer une alerte de n’importe quel type et n’importe quel délai pour une activité

donnée.

Les cardinalités I-I :

Pour chaque association de ce type, on n’a pas crée de table supplémentaire sauf pour le

procès verbal. Ainsi on a simplifié notre base de données et on a fait des choix pour prendre

10

les clés primaires d’une table et la mettre en clé étrangère sur l’autre entité de l’association.

On évite d’avoir des redondances au niveau des clés.

Nom des entités Entité avec cardinalité N Entité avec cardinalité I

Clé primaire Clé primaire Clé étrangère

Employé-Agenda numEmployé numAgenda numEmploye

Employé-

Département

numEmployé Num numChef

Agenda

Départemental-

Département

numAgenda numAct numAgenda

Activités

Départementales-

Procès Verbals

numAct numProcesV

Les associations Employé-Agenda, Employé-Département et Agenda Départemental-

Département sont de type I-I. Les clés primaires des premières entités deviennent des clés

étrangères dans les secondes entités. On a décidé qu’à partir de l’Employé on peut accéder à

l’Agenda de celui-ci. Il en va de même pour accéder à l’Agenda Départemental, il faut passer

par le Département. Apres avoir créé l’Employé, on peut avoir le numéro du chef du

Département.

Par contre, l’association Activités Départementales-Procès Verbaux à nécessiter la création

d’une nouvelle entité appeler ActDeptVerbal. Cette nouvelle entité est la liaison entre

l’Activités Départementales et le Procès Verbal. Normalement cette entité aurait eu le même

traitement que les précédentes. Mais après une multitude de test sous oracle, les mises à jour

de l’attribut numProcesV dans Activités Départementales ne marchaient pas, d’où la création

d’une entité supplémentaire.

Nom des entités Entité de cardinalité I Entité intermédiaire

ActDeptVerbal

Entité de

cardinalité I

Clé primaire Clé

étrangère et

primaire

Clé étrangère

et primaire

Clé primaire

Activités

Départementale

s-Procès

Verbaux

numAct numAct numProcesV numProcesV

Les cardinalités N-N :

L’association Employé-Activités Départementales nécessite la création d’une table

intermédiaire Absent qui fait le lien entre ces deux entités. Les deux clés primaires deviennent

des clés étrangères et aussi des clés primaires.

Nom des entités Entité de

cardinalité N

Entité intermédiaire

Absent

Entité de

cardinalité N

Clé primaire Clé étrangère et

primaire

Clé étrangère et

primaire

Clé primaire

Employé-

Activités

Départementales

numEmployé numEmployé numActDept numActDept

11

L’association Employé-Employé n’a pas nécessité une nouvelle table. En contre partie un

nouvel attribut fut créé dans l’Entité Employé qui se nomme niveau. Ainsi on peut savoir le

supérieur hiérarchique par rapport au plus grand nombre contenue dans cet attribut.

10) Dessiner le diagramme logique de la base de donnée Agenda

12

5. Création, Mise à jour et Interrogation de la base de données « Agenda »

11) Création des tables de notre base de données

La création des tables s’est obtenue par le schéma relationnel logique fait en partie 3.

On a crée les tables avec la commande :

CREATE TABLE nomDeLaTable (les attributs que l’on a besoin) ;

Les tables ne peuvent pas être crées dans n’importe quel ordre puisqu’elles sont reliés entre

elles par des clés primaires et/ou étrangères. En effet si la table ne contient pas de clé

étrangère, elle peut être crée en première. Ainsi les tables PROCESVERBAL et AGENDADEPT

ont été crées en première. Par contre, la table DEPARTEMENT a deux clés étrangères qui sont

numAgenda et numChef appartenant respectivement aux tables AGENDADEPT et

EMPLOYE. C’est pourquoi dans la table DEPARTEMENT l’attribut numChef n’est pas une

clé étrangère pour le moment puisque la table EMPLOYE n’a pas été encore crée. Apres la

création de la table EMPLOYE on applique une commande qui permet de mettre la clé

numChef en clé étrangère dans la table DEPARTEMENT. On fait de même avec les autres

tables en prenant garde sur les clés étrangères.

Ainsi notre fichier createTableAgendaDept.sql nous sert a crée les tables nécessaire

pour faire notre base de données.

DROP TABLE PROCESVERBAL cascade constraints; DROP TABLE AGENDADEPT cascade constraints;

DROP TABLE DEPARTEMENT cascade constraints;

DROP TABLE EMPLOYE cascade constraints;

DROP TABLE ACTIVITESDEPT cascade constraints;

DROP TABLE AGENDA cascade constraints;

DROP TABLE ABSENT cascade constraints;

DROP TABLE ACTIVITES cascade constraints;

DROP TABLE ALERTES cascade constraints;

DROP TABLE ACTDEPTVERBAL cascade constraints;

CREATE TABLE PROCESVERBAL

(NumProcesV NUMBER(8) , resum VARCHAR(1024) NOT NULL, PRIMARY KEY

(NumProcesV));

CREATE TABLE AGENDADEPT

(numAgenda NUMBER(8) , dateMAJ DATE NOT NULL, PRIMARY KEY

(numAgenda));

CREATE TABLE DEPARTEMENT

(num NUMBER(8) , nom VARCHAR(40) NOT NULL,numChef NUMBER(8),

numAgendaDept NUMBER(8) NOT NULL, PRIMARY KEY (num), FOREIGN KEY

(numAgendaDept) References AGENDADEPT);

CREATE TABLE EMPLOYE

(numEmploye NUMBER(8) , nom VARCHAR(30) NOT NULL, prenom

VARCHAR(20), telIntern VARCHAR(14), email VARCHAR(40),niveau

13

NUMBER(8), numDept NUMBER(8) NOT NULL, PRIMARY KEY (numEmploye),

FOREIGN KEY (numDept) References DEPARTEMENT);

ALTER TABLE DEPARTEMENT ADD constraints deptforeign FOREIGN KEY

(numChef) References EMPLOYE;

CREATE TABLE ACTIVITESDEPT

(numAct NUMBER(8), typeD VARCHAR(20) NOT NULL, descript

VARCHAR(1024), dateAct DATE NOT NULL, hDebut DATE, hFin DATE,

dateCreation DATE, createur VARCHAR(20) NOT NULL, numAgenda

NUMBER(8) NOT NULL, PRIMARY KEY (numAct), FOREIGN KEY (numAgenda)

References AGENDADEPT);

CREATE TABLE ACTDEPTVERBAL

(numAct NUMBER(8), numProcesV NUMBER(8), PRIMARY KEY (numAct,

numProcesV), FOREIGN KEY (numAct) References ACTIVITESDEPT, FOREIGN

KEY (numProcesV) References PROCESVERBAL);

CREATE TABLE AGENDA

(numAgenda NUMBER(8) , dateCreation DATE, numEmploye NUMBER(8) NOT

NULL,PRIMARY KEY (numAgenda), FOREIGN KEY (numEmploye) References

EMPLOYE);

CREATE TABLE ABSENT

(numEmploye NUMBER(8) , numActDept NUMBER(8) , motif VARCHAR(1024),

PRIMARY KEY (numEmploye, numActDept), FOREIGN KEY (numEmploye)

References EMPLOYE,FOREIGN KEY (numActDept) References

ACTIVITESDEPT);

CREATE TABLE ACTIVITES

(numActivite NUMBER(8) , typeA VARCHAR(30) NOT NULL, description

VARCHAR(1024), dateAct DATE NOT NULL, hDebut DATE, hFin DATE,

dateCreation DATE, createur VARCHAR(20) NOT NULL, visible NUMBER(1),

numAgenda NUMBER(8) NOT NULL, PRIMARY KEY (numActivite), FOREIGN KEY

(numAgenda) References AGENDA);

CREATE TABLE ALERTES

(type CHAR(8) , delais DATE, numActivite NUMBER(8), PRIMARY KEY

(type,delais,numActivite), FOREIGN KEY (numActivite) References

ACTIVITES);

14

12) L’insertion de tuples dans les différentes tables

Pour les même raison que précédemment, il faut insérer les tuples avec un certains ordres. En

outre l’AGENDADEPT a été inséré avant l’insertion du DEPARTEMENT. Les commandes pour

l’insertion de l’AGENDADEPT et du DEPARTEMENT:

INSERT INTO AGENDADEPT VALUES (un chiffre, une date);

INSERT INTO AGENDADEPT VALUES (seqAgendaDepartement.nextVal,

to_date(sysdate,'dd-mm-yyyy'));

INSERT INTO DEPARTEMENT VALUES (un chiffre, nom du departement, numero du

chef, le chiffre de l’agenda du departement);

INSERT INTO DEPARTEMENT VALUES (seqDepartement.nextVal, 'Informatique',

NULL, seqAgendaDepartement.currVal);

Pour des raisons de commodités, des séquences sont crées pour ne pas se soucier du numéro.

Ce ci se fait par la commande :

DROP SEQUENCE seqAgendaDepartement;

CREATE SEQUENCE seqAgendaDepartement increment by 1 start with 1;

Ensuite, un employé est inséré tout en faisant attention à l’ordre dans lesquels sont mis les

tuples. La logique veut que l’on met les insertions dans cet ordre :

L’insertion d’un employé: INSERT INTO EMPLOYE VALUES (seqEmploye.nextVal, 'ALBERS', 'Patrick',

'02.41.86.67.22', NULL, 10, seqDepartement.currval);

L’insertion de l’agenda de l’employé: INSERT INTO AGENDA VALUES (seqAgenda.nextVal, sysdate, seqEmploye.currval);

L’insertion d’une activité de l’employé: INSERT INTO ACTIVITES VALUES (seqActivites.nextVal, 'cours', 'faire le

chapitre 4 du langage C','10-JAN-06', to_date('7:45','hh24:mi'),

to_date('9:45','hh24:mi'), '03-DEC-05','PLAINCHAULT', 1,

seqAgenda.currVal);

L’insertion d’une alerte: INSERT INTO ALERTES VALUES ('email', to_date('5:30','hh24:mi'),

seqActivites.currVal);

L’insertion d’une activité du département de l’employé: INSERT INTO ACTIVITESDEPT VALUES (seqActiviteDept.nextVal, 'formation',

'les maintenances du reseaux de l eseo', '12-JAN-06',

to_date('14:15','hh24:mi'), to_date('16:15','hh24:mi'), '15-NOV-

05','PLAINCHAULT', seqAgendaDepartement.currVal);

L’insertion d’un procès verbal en rapport avec une activité du département: INSERT INTO PROCESVERBAL VALUES (seqProcesV.nextVal,'determination des

personnes responsables des permanances');

L’insertion d’une table pour faire la liaison avec ACTIVITESDEPT et PROCESVERBAL: INSERT INTO ACTDEPTVERBAL VALUES (seqActiviteDept.currVal,

seqProcesV.currVal);

L’insertion d’un absent à l’activité département: INSERT INTO ABSENT VALUES (2,seqActiviteDept.currVal,'malade');

Les séquences sont incrémentées de un lorsque l’on ajoute à la fin de celle-ci .nextVal et

donnent la valeur courante lorsque l’on met à la fin de la séquence .currVal.

15

13) Interrogation de la base de données :

3. Le nom, prénom, et département des employés dont on ignore l’adresse électronique, ordonnés par département et nom.

La requête est la suivante :

SELECT DISTINCT E.nom as nom_Employe, E.prenom as prenom_Employe, D.nom as

nom_Departement

FROM Employe E, Departement D

WHERE E.numDept = D.num -- Faire un lien entre le département et l’employé

AND E.email is NULL -- Vérifier les personnes qui n’ont pas d’adresses

ORDER BY D.Nom ASC,E.nom ASC; -- Ordonner les départements et les noms des employés

La réponse est la suivante :

NOM_EMPLOYE PRENOM_EMPLOYE NOM_DEPARTEMENT

------------------------------ -------------------- ---------------------

CHAUVEAU Eric Automatique

GENTIL Remi Automatique

GUITTON Damien Electronique

ALBERS Patrick Informatique

CAMP Olivier Informatique

PERCHER Jean-Marc Informatique

Remarque :

Messieurs Chauveau, Gentil Guitton, Albers, Camp et Percher n’ont pas d’adresses et sont

ordonnés par département et nom par ordre croissant.

4. Les séminaires futurs du département Informatique

La requête est la suivante pour les séminaires futurs dans l’Agenda Départemental :

SELECT DISTINCT Acd.dateAct as Seminaire_Depart,Acd.descript as Description

FROM Departement D, AgendaDept Agd, ActivitesDept Acd

WHERE upper( D.nom )= 'INFORMATIQUE' -- Sélectionner le département Informatique

And D.numAgendaDept = Agd.numAgenda -- Faire le lien entre le Département et

l’Agenda Départemental

And Agd.numAgenda = Acd.numAgenda -- Faire le lien entre l’Agenda et l’Activité

Départemental

And upper( Acd.typeD ) = 'SEMINAIRE' -- Sélectionner le type d’Activité

Départemental qui est le Séminaire

And sysdate < Acd.dateAct -- Comparer les dates des Activités

Départemental par rapport à la date d’aujourd’hui

ORDER BY Acd.dateAct ASC; -- Ordonner par date d’Activité Départemental

La requête est la suivante pour les séminaires futurs dans l’Agenda de l’Employé :

SELECT DISTINCT Ac.dateAct as SEMINAIRE, Ac.description as Description

FROM Departement D, Agenda Ag, Activites Ac, Employe E

WHERE upper( D.nom )= 'INFORMATIQUE'

And D.num = E.numDept -- Faire le lien entre le Département et l’Employé

16

And E.numEmploye = Ag.numEmploye -- Faire le lien entre l’Agenda de l’Employé et

l’Employé

And Ag.numAgenda = Ac.numAgenda -- Faire le lien entre l’Agenda et l’Activité de

l’Employé And upper( Ac.typeA ) = 'SEMINAIRE'

And sysdate < Ac.dateAct

ORDER BY Ac.dateAct ASC;

La réponse est la suivante pour les séminaires futurs dans l’Agenda Départemental :

SEMINAIRE

DESCRIPTION

---------------------------------------------------------------------------

27-MAR-06

Utilisation de l informatique pour le biomedicale

28-MAR-06


29-MAR-06


30-MAR-06


31-MAR-06


Remarque :

Du 27 Mars au 31 Mars 2006 se tiendra un séminaire sur l’utilisation de l’informatique pour

le biomédicale, crée par le département.

La réponse est la suivante pour les séminaires futurs dans l’Agenda de l’Employé :

SEMINAIRE

DESCRIPTION

---------------------------------------------------------------------------

13-FEB-06

administration reseau

14-FEB-06


15-FEB-06


16-FEB-06


17-FEB-06


20-FEB-06

administration reseau pour le second groupe

21-FEB-06


22-FEB-06


23-FEB-06


24-FEB-06


Remarque :

Du 13 Février au 17 Février et du 13 Février au 17 Février se tiendra deux séminaires pour

l’administration réseau. Le premier est consacré pour un premier groupe de l’administration et

le deuxième pour un second groupe.

17

5. Les activités personnelles dans l’agenda de Monsieur Schang qui n’ont pas été insérées par lui


SELECT DISTINCT Ac.typeA as Act_de_SCHANG,Ac.dateAct as date_Activite,

to_char(Ac.hDebut,'hh24:mi') as H_Debut,to_char(Ac.hFin,'hh24:mi') as

H_Fin, Ac.createur as Createur

FROM Employe E, Agenda A, Activites Ac

WHERE upper( E.nom ) = 'SCHANG' -- Sélectionner l’Employé qui se nomme Schang

AND E.numEmploye = A.numEmploye -- Faire le lien entre l’Agenda de l’Employé et

l’Employé

AND A.numAgenda = Ac.numAgenda -- Faire Faire le lien entre l’Agenda et l’Activité

de l’Employé

AND upper(Ac.createur) NOT LIKE 'SCHANG' -- Sélectionner les personnes qui créer des

activités autres que monsieur Schang

AND visible = 1 -- Ne prendre que les activités personnelles

ORDER BY Ac.dateAct ASC; -- Ordonner par date d’Activité


ACT_DE_SCHANG DATE_ACTI H_DEB H_FIN CREATEUR

------------------------------ --------- ----- ----- --------------------

mini-projet 04-JAN-06 13:30 17:45 PLAINCHAULT

cours 09-JAN-06 07:45 09:00 PLAINCHAULT




reunion 14-JAN-06 18:00 20:30 PERCHER


seminaire 20-FEB-06 08:30 17:30 PLAINCHAULT





cours 08-SEP-06 07:45 09:30 PLAINCHAULT

cours 10-DEC-06 16:20 17:35 PLAINCHAULT

Remarque :

Certaines activités de Monsieur Schang n’ont pas été crées par lui-même et sont répertoriées

ci-dessus. Elles sont triées par le type d’activité, la date, l’heure de début, l’heure de fin qui

lui est associé et le créateur qui lui a mis une activité ce jour là.

6. Les conflits d’horaires (entre activités personnelles et départementales) de Monsieur Percher


SELECT DISTINCT Ac.dateAct as act_personnelles,

to_char(Ac.hDebut,'hh24:mi') as MrPercher, to_char(Ac.hFin,'hh24:mi') as

problemes,Acd.dateAct as act_departementales, to_char(Acd.hDebut,'hh24:mi')

as MrPercher, to_char(Acd.hFin,'hh24:mi') as problemes

FROM Activites Ac, Agenda Ag, Employe E, Departement D, AgendaDept Agd,

ActivitesDept Acd

WHERE Ac.dateAct = Acd.dateAct -- Sélectionner les mêmes dates d’Activités pour

l’Employé et le Département

18


Départemental

And Ag.numEmploye = E.numEmploye -- Faire le lien entre l’Agenda de l’Employé et

l’Employé

And E.numDept = D.num -- Faire le lien entre le Département et l’Employé

And D.numAgendaDept = Agd.numAgenda -- Faire le lien entre le Département et l’Agenda

Départemental


l’Employé

And upper( E.nom )= 'PERCHER' -- Sélectionner l’Employé qui se nomme Percher

AND Ac.visible = 1 -- Ne prendre que les activités personnelles And (to_char(Ac.hDebut,'hh24:mi') between to_char(Acd.hDebut,'hh24:mi') and

to_char(Acd.hFin,'hh24:mi')

or to_char(Ac.hFin,'hh24:mi') between to_char(Acd.hDebut,'hh24:mi') and

to_char(Acd.hFin,'hh24:mi')

or to_char(Acd.hDebut,'hh24:mi') between to_char(Ac.hDebut,'hh24:mi') and

to_char(Ac.hFin,'hh24:mi')

or to_char(Acd.hFin,'hh24:mi') between to_char(Ac.hDebut,'hh24:mi') and

to_char(Ac.hFin,'hh24:mi')); -- Sélectionner les horaires qui se chevauchent


ACT_PERSO MRPER PROBL ACT_DEPAR MRPER PROBL

--------- ----- ----- --------- ----- -----

12-JAN-06 16:20 17:35 12-JAN-06 16:30 18:30

13-JAN-06 13:30 14:45 13-JAN-06 13:30 18:30

Remarque :

Les activités départementales et personnelles de Monsieur Percher ne sont pas en concordance

le 12 Janvier 2006 et le 13 Janvier 2006. Ceci est vérifié par les horaires qui sont affichés à

coté des dates.

7. L’agenda de Monsieur Boissinot le 09 Janvier 2006

La requête est la suivante pour chercher les activités Départemental de Monsieur Boissinot le

09 Janvier 2006 :

SELECT DISTINCT to_char(Acd.hDebut, 'hh24:mi') as dateDept,

to_char(Acd.hFin, 'hh24:mi') as BOISSINOT, Acd.typeD as TypeDept,

Acd.descript as Description

FROM Departement D, AgendaDept Agd, ActivitesDept Acd, Employe E

WHERE upper( E.nom )= 'BOISSINOT' --Sélectionner l’Employé qui se nomme Boissinot


Départemental

And D.num = E.numDept -- Faire le lien entre le Département et l’Employé


Départemental

And to_date('09-01-2006','dd-MM-YYYY')= Acd.dateAct; -- Sélectionner la date du

09-Janvier-2006

La requête est la suivante pour chercher les activités personnelles de Monsieur Boissinot le

09 Janvier 2006 :

19

SELECT DISTINCT to_char(Ac.hDebut, 'hh24:mi') as dateAct, to_char(Ac.hFin,

'hh24:mi') as BOISSINOT, Ac.typeA as TypeAct, Ac.description as

Description, Ac.visible as professionnel

FROM Agenda Ag, Activites Ac, Employe E

WHERE upper( E.nom )= 'BOISSINOT' --Sélectionner l’Employé qui se nomme Boissinot


l’Employé


l’Employé

And visible = 1 -- Ne prendre que les activités personnelles And to_date('09-01-2006','dd-MM-YYYY')= Ac.dateAct;

La requête est la suivante pour chercher les activités non professionnels de Monsieur

Boissinot le 09 Janvier 2006 :

SELECT DISTINCT to_char(Ac.hDebut, 'hh24:mi') as dateAct, to_char(Ac.hFin,

'hh24:mi') as BOISSINOT, Ac.typeA as TypeAct, Ac.description as

Description, Ac.visible as personnel


WHERE upper( E.nom )= 'BOISSINOT'

And E.numEmploye = Ag.numEmploye

And Ag.numAgenda = Ac.numAgenda

And visible = 0 -- Ne prendre que les activités non professionnelles And to_date('09-01-2006','dd-MM-YYYY')= Ac.dateAct;

La réponse est la suivante pour chercher les activités Départemental de Monsieur Boissinot

le 09 Janvier 2006 :

no rows selected

Remarque :

Aucunes activités Départementales ne figurent dans l’Agenda de Monsieur Boissinot le 09

Janvier 2006.

La réponse est la suivante pour chercher les activités personnelles de Monsieur Boissinot le

09 Janvier 2006 :

DATEA BOISS TYPEACT DESCRIPTION PROFESSIONNEL

----- ----- ---------------------------------------------------------------

09:10 10:25 cours Preparer le cours sur l electronique de Puissance 1

14:55 16:10 cours Commencer le chapitre 1 et finir les rappels 1

Remarque :

Monsieur Boissinot a deux activités personnelles le 09 Janvier 2006 qui sont des cours. Le 1

signifie que se sont des activités personnelles.

La réponse est la suivante pour chercher les activités non professionnelles de Monsieur

Boissinot le 09 Janvier 2006 :

DATEA BOISS TYPEACT DESCRIPTION PERSONNEL

-----------------------------------------------------

18:00 medecin Probleme de voix 0

Remarque :

Monsieur Boissinot a deux activités personnelles le 09 Janvier 2006 qui sont des cours. Le 0

signifie que se sont des activités personnelles.

20

8. Le jour où Monsieur Schang a le plus d’activités personnelles


SELECT DISTINCT Ac.dateAct, count(*)


WHERE Ac.visible = 1 -- Ne prendre que les activités personnelles

and upper( E.nom )= 'SCHANG' -- Sélectionner l’Employé qui se nomme Schang


l’Employé


l’Employé

GROUP BY Ac.dateAct -- Grouper les dates

HAVING count(*)=(select max(count(*))—Compter le nombre de fois que les dates se

répètent et chercher le maximum que les dates se

répètent From Agenda Ag, Activites Ac, Employe E

WHERE Ac.visible = 1

and upper( E.nom )= 'SCHANG'

And E.numEmploye = Ag.numEmploye

And Ag.numAgenda = Ac.numAgenda

Group by Ac.dateAct);


DATEACT COUNT(*)

--------- ----------

11-JAN-06 2

Remarque :

Le nombre de fois que Monsieur Schang a le plus d’activités est le 11 Janvier 2006 avec deux

activités personnelles. Il faut pour cette requête, une imbrication pour compter la première

fois le nombre d’activité dans une journée et ensuite récupérer le jour qui à le plus d’activité.

9. Les professeurs libres d’électronique le 11 Janvier 2006 pour remplacer Monsieur Guitton de 13H30 à 15H30


SELECT DISTINCT E.nom as Pers_a_Re_le_11_01_de_13h30, Es.nom as

a_15h30_par_M, Acs.typeA as qui_a , to_char( Acs.hDebut,'hh24:mi') as de ,

to_char( Acs.hFin,'hh24:mi') as a

FROM Activites Ac, Agenda Ag, Employe E, Departement D, Activites Acs,

Agenda Ags, Employe Es, AgendaDept Agd, ActivitesDept Acd

WHERE Ag.numEmploye = E.numEmploye -- Faire le lien entre l’Agenda de Guitton et

Monsieur Guitton

And Ags.numEmploye = Es.numEmploye -- Faire le lien entre l’Agenda d’un Employé

lambda et l’Employé lambda

And E.numDept = D.num -- Faire le lien entre le Département

d’électronique et Monsieur Guitton

And Es.numDept = D.num -- Faire le lien entre le Département et l’Employé

lambda

21


Monsieur Guitton

And Ags.numAgenda = Acs.numAgenda -- Faire le lien entre l’Agenda et l’Activité de

l’Employé lambda

And upper( D.nom )= 'ELECTRONIQUE' -- Sélectionner le département d’électronique

And upper (E.nom ) = 'GUITTON' -- Sélectionner l’Employé qui se nomme Guitton

AND Ac.visible = 1 -- Ne prendre que les activités personnelles de

Monsieur Guitton

And Acs.visible = 1 -- Ne prendre que les activités personnelles de

l’Employé lambda And Ac.dateAct = to_date('11-01-2006', 'dd-MM-YYYY')

-- Sélectionner la date du 09 Janvier-2006 pour Monsieur Guitton And Acs.dateAct = to_date('11-01-2006', 'dd-MM-YYYY')

-- Sélectionner la date du 09 Janvier-2006 pour l’Employé lambda

And to_date('13:30','hh24:mi') not between Acs.hDebut and Acs.hFin

-- Sélectionner l’heure de début pour lequel l’Employé lambda serait disponible And to_date('15:30','hh24:mi') not between Acs.hDebut and Acs.hFin;

-- Sélectionner l’heure de fin pour lequel l’Employé lambda serait disponible


PERS_A_RE_LE_11_01_DE_13H30 A_15H30_PAR_M QUI_A DE A

----------------------------- --------------- -------- ----- -----------

GUITTON BOUVIER cours 09:10 10:25

GUITTON LE DUFF cours 08:30 10:00

Remarque :

Il faut utiliser deux employés pour faire la comparaison avec Monsieur Guitton et un autre

employé. Ici on considère que Monsieur Guitton a une autre activité personnelle le même jour

et entre 13H30 et 15H30. Messieurs Bouvier et Le Duff sont disponibles aux heures requises.

10. Les personnes qui été absentes aux activités départementales d’automatique


SELECT DISTINCT E.nom as Employe_absent, Ab.motif as raison_absence ,

Acd.dateAct as le, Acd.typeD, Pv.resum as resume_act

FROM Departement D, AgendaDept Agd, ActivitesDept Acd, Employe E, Absent

Ab, ProcesVerbal Pv, ActDeptVerbal Av

WHERE upper( D.nom )= 'AUTOMATIQUE'


Départemental


Départemental

And E.numDept = D.num -- Faire le lien entre le Département et l’Employé

And Acd.numAct = Ab.numActDept -- Faire le lien entre les numéros d’activités

départemental du département et de l’absent

And Av.numAct = Acd.numAct -- Faire le lien entre la table ActDeptVerbal et

ActivitésDept

And Av.numProcesV = Pv.numProcesV -- Faire le lien entre la table ActDeptVerbal et

ProcesVerbal

22

And Ab.numEmploye = E.numEmploye -- Faire le lien entre l’Agenda de l’Employé et

l’Employé

And sysdate > Acd.dateAct -- Comparer les dates des anciennes Activités

Départemental par rapport à la date d’aujourd’hui

ORDER BY Acd.dateAct ASC; -- Ordonner les dates d’activités départementales


EMPLOYE_ABSENT RAISON_ABSENCE LE TYPED

RESUME_ACT

---------------------------------------------------------------------------

CHAUVEAU Embouteillage sur la rocade 02-JAN-06 reunion

Les copy sont totalement corrigees

BOISSINOT Voyage d affaire 02-JAN-06 reunion

Les copy sont totalement corrigees

Remarque :

Messieurs Boissinot et Chauveau seront absent à la réunion de l’activité départementale qui se

tiendra le 05 Novembre 2005. Les raisons de leurs absences sont spécifiées ainsi que le

résumé de ce qui s’est passé durant l’activité départementale.

11. Le volume horaires des activités personnelles de Monsieur Genet le 10 Janvier 2006


SELECT sum(to_char(Ac.hFin,'hh24')*60-

to_char(Ac.hDebut,'hh24')*60+to_char(Ac.hFin,'mi')-

to_char(Ac.hDebut,'mi'))as vol_h_de_GENET_le_10_01


WHERE upper( E.nom )= 'GENET' -- Sélectionner l’Employé qui se nomme Genet


l’Employé


Monsieur Genet

And Ac.visible = 1 -- Ne prendre que les activités personnelles And to_date('10-01-2006','dd-MM-YYYY')= Ac.dateAct;

-- Sélectionner la date du 10 Janvier 2006 pour Monsieur Genet


VOL_H_DE_GENET_LE_10_01

-----------------------

500

Remarque :

La somme du volume horaires dans une journée est faite dans le select. Tout d’abord on

commence par soustraire les heures de fin et de début en multipliant le résultat par 60 pour

avoir le résultat en minutes. Puis on fait de même avec les minutes sans multiplier par 60.

Finalement on somme toutes les minutes contenues dans une journée. On obtient ainsi 500

minutes d’activités personnelles pour Monsieur Genet dans la journée du 10 Janvier 2006.

23

12. Les supérieurs hiérarchiques de Monsieur Guitton dans le département électronique


SELECT DISTINCT Es.nom as superieur_de_guitton, Es.niveau

FROM Activites Ac, Agenda Ag, Employe E, Departement D, Activites Acs,

Agenda Ags, Employe Es, AgendaDept Agd, ActivitesDept Acd

WHERE Ag.numEmploye = E.numEmploye -- Faire le lien entre l’Agenda de Guitton et

Monsieur Guitton

And Ags.numEmploye = Es.numEmploye -- Faire le lien entre l’Agenda d’un Employé

lambda et l’Employé lambda

And E.numDept = D.num -- Faire le lien entre le Département

d’électronique et Monsieur Guitton

And Es.numDept = D.num -- Faire le lien entre le Département et l’Employé

lambda


Monsieur Guitton

And Ags.numAgenda = Acs.numAgenda -- Faire le lien entre l’Agenda et l’Activité de

l’Employé lambda

And upper( D.nom )= 'ELECTRONIQUE' Sélectionner le département d’électronique

And upper (E.nom ) = 'GUITTON' -- Sélectionner l’Employé qui se nomme Guitton

AND E.niveau < Es.niveau -- Comparer les niveaux des personnes par

rapport à Monsieur Guitton

ORDER BY Es.niveau DESC,Es.nom ASC; -- Ordonner dans l’ordre hiérarchique et par nom

dans l’ordre alphabétique


SUPERIEUR_DE_GUITTON NIVEAU

------------------------------ ----------

MADELINE 1000

BOUVIER 500

LE DUFF 500

Remarque :

Il faut utiliser deux employés pour faire la comparaison avec Monsieur Guitton et les autres

supérieurs hiérarchiques. On constate que Monsieur Guitton a trois supérieurs hiérarchiques

qui sont Messieurs Madeline, Bouvier et Le Duff dont les deux derniers ont le même niveau

hiérarchique.

6. Conclusion

Nous avons pu ainsi mieux comprendre le fonctionne d’une base de donnée, et les

mécanismes mis en place pour en faciliter l’utilisation, tel que les séquences, et aussi les

triggers.

Nous avons pu nous rendre compte que malgré les détails du cahier des charges, il nous restait

à choisir la solution à employer dans certains cas.

Nous avons abouti à un projet qui répond à l’ensemble des exigences du cahier des charges.

mini projet base de donnée -...

Documents