Discussion :

[cybernoid]

Bonjour, j'ai déjà rendu ce TD avec lequel j'ai bataillé de nombreuses heures, mais j'aimerai l'avis d'un spécialiste sur les tables et les requetes que j'ai déjà créées. (cela me semble un peu lourd au premier abord, j'ai bossé jusqu'ici uniquement en relationnel, jamais en objet !) Alors si quelqu'un a envie d'avoir un peu mal à la tête je vous remercie par avance !
cordialement
PS: Je bosse sur une Oracle 10 Express, les instructions doivent tourner sur la version 8 aussi (exigence du TD)

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

On considère le schéma relationnel suivant :

SERVICE (numS, nomS, responsableS)
EMPLOYE (numE, nomE, #numS)
ACTIVITE (numA, titreA, #numS)
AFFECTATION (#numE, #numA, nbh)

Modélisant le cas d'une entreprise possédant plusieurs services dans lesquels travaillent des employés sur des activités. Un employé est rattaché à un seul service. Une activité est rattachée à un seul service. Des employés sont affectés aux activités pour un certain nombre d'heures (nbh).

On envisage de se placer dans le modèle objet-relationnel pour lequel il faudra créer des Types Abstraits de Données de manière à ce que le schéma soit réduit à un nombre moindre de tables.

1) On envisage d'abord deux tables ENTREPRISE qui donne par service la liste des employés et la liste des activités et AFFECTATION indiquant quels employés travaillent sur quelles activités.

Entreprise :
Service - Employes - Activites
numS nomS Responsable S numE nomE numA TitreA

Affectation:
numE numA nbh

a) Indiquer en SQL3/Oracle8 comment cette structure peut être créée.



CREATE TYPE TEMPLOYE AS OBJECT
(numE INTEGER,
nomE VARCHAR(30));

CREATE TYPE TSERVICE AS OBJECT
(numS INTEGER,
nomS VARCHAR(30),
responsableS REF TEMPLOYE;


CREATE TYPE TACTIVITE AS OBJECT
(numA INTEGER,
titreA VARCHAR(30));

CREATE TYPE TTABLEACTIVITE AS TABLE OF TACTIVITE ;

CREATE TYPE TTABLEEMPLOYE AS TABLE OF TEMPLOYE ;

CREATE TABLE ENTREPRISE (
Services TSERVICE,
Employes TTABLEEMPLOYE,
Activites TTABLEACTIVITE)
NESTED TABLE Employes STORE AS NEMPL,
NESTED TABLE Activites STORE AS NACT;

CREATE TABLE AFFECTATION (
TEMPLOYE REF TEMPLOYE,
TACTIVITE REF TACTIVITE,
nbh REAL);

b) Formuler la requête R1 suivante : Quelles sont les activités du service numS = 12 ?

SELECT ss.numA FROM the
(SELECT a.activites
FROM (SELECT * FROM ENTREPRISE b WHERE b.services.numS='12') a) ss;

c) L'employé numE=34 du service numS=12 permute avec l'employé numE=56 du service numS=21. Formuler cette mise à jour.

UPDATE ENTREPRISE e SET e.Services.numS=21 WHERE
(SELECT ss.numE FROM the
(SELECT b.employes
FROM ENTREPRISE b) ss)=34;

UPDATE ENTREPRISE e SET e.Services.numS=12 WHERE
(SELECT ss.numE FROM the
(SELECT b.employes
FROM ENTREPRISE b) ss)=56;

Autre solution (qui marche celle la !)

INSERT INTO TABLE(
SELECT h.employes
FROM ENTREPRISE h
WHERE h.services.numS = 21)
(SELECT numE,nomE FROM ENTREPRISE a, TABLE(a.employes) b WHERE b.numE=34 AND a.SERVICES.numS=12);


INSERT INTO TABLE(
SELECT h.employes
FROM ENTREPRISE h
WHERE h.services.numS = 12)
(SELECT numE,nomE FROM ENTREPRISE a, TABLE(a.employes) b WHERE b.numE=56 AND a.SERVICES.numS=21);

DELETE TABLE(
SELECT h.employes
FROM ENTREPRISE h
WHERE h.services.numS = 12) a WHERE a.numE=34;

DELETE TABLE(
SELECT h.employes
FROM ENTREPRISE h
WHERE h.services.numS = 21) a WHERE a.numE=56;



d) Insérer un nouvel employé, DURACUIRE, matricule numE = 78 dans le service numS = 12.

INSERT INTO ENTREPRISE(services,employes) VALUES(TSERVICE(12,NULL,NULL),TTABLEEMPLOYE(TEMPLOYE(78,'DURACUIRE')));

Question subsidiaire : Ma façon de gerer un responsable de service qui est aussi un employé est elle correcte ?


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


2) On envisage ensuite une table structurée comme suit :

activites - employes
numA Titre A - numE NomE nbh numS

a) Indiquer en SQL3/Oracle8 comment cette structure peut être créée.


CREATE OR REPLACE
TYPE TACTIVITE2 AS OBJECT
(numA INTEGER,
titreA VARCHAR(30));

CREATE OR REPLACE
TYPE TEMPLOYE2 AS OBJECT
(numE INTEGER,
nomE VARCHAR(30),
nbh REAL,
nums INTEGER
);

CREATE OR REPLACE
TYPE TTABLEEMPLOYE2 AS TABLE OF TEMPLOYE2 ;

CREATE TABLE ENTREPRISE2
(
ACTIVITES TACTIVITE2,
EMPLOYES TTABLEEMPLOYE2
)
NESTED TABLE EMPLOYES STORE AS NEMPL2;

:

b) Formuler la requête suivante R2 : Calculer le nombre d'heures affectées à chaque activité


SELECT c.numa,SUM(d.nbh)
SELECT a.ACTIVITES.numA, a.ACTIVITES.titreA, SUM(b.nbh) FROM ENTREPRISE2 a, TABLE(a.EMPLOYES) b
GROUP BY a.ACTIVITES.numA, a.ACTIVITES.titreA;

Voila c'est tout pour aujourd'hui ! (merci d'avance !)

[cybernoid]

Malgré les visites, mon appel est resté sans réponse.

Apparemment personne n'utilise SQL3 ici même avec Oracle ?

Ou alors ai-je mal formulé ma demande ?

Ayant fait ce même devoir en SQL2 relationnel, je comprends aisément aujourd'hui le peu d'engouement pour le mode objet.

1) Définition lourde (voir ci dessus)
2) Requetage fastidieux (faire un insert relève de l'exploit)

Le devoir etant rendu, j'attends avec impatience le corrigé qui je l'espère devrait m'éclairer un peu plus

A + et merci quand même aux 40 visiteurs qui ont pris le temps de lire !

[pifor]

Ou alors ai-je mal formulé ma demande ?

Non c'est très clair.

Apparemment personne n'utilise SQL3 ici même avec Oracle ?

Je crois qu'en effet il est vraiment rare d'utiliser ces fonctionnalités. Certains experts recommendent seulement d'utiliser les fonctions PL/SQL sans le stockage...

Vous pouvez aussi essayer de poster votre message sur le forum OTN dédié aux objets..

[cybernoid]

Bon je m'en tire pas mal (16/20) par contre je n'ai pas utilisé les procédures !
grave erreur !



-- Pour la creation de la table Entreprise
CREATE TYPE T_EMPLOYE AS OBJECT(
numE Number,
nomE varchar(30)
);
CREATE TYPE T_SERVICE AS OBJECT (
numS Number,
nomS varchar(30),
responsableS Number
) ;
CREATE TYPE T_ACTIVITE AS OBJECT (
numA Number,
titreA varchar(30)
);
CREATE TYPE T_EMPLOYE_PAR_SERVICE AS TABLE OF T_EMPLOYE;
CREATE TYPE T_ACTIVITES_PAR_SERVICE AS TABLE OF T_ACTIVITE;
CREATE TYPE T_ENTREPRISE AS OBJECT (
lesServices T_SERVICES,
lesEmployesParService T_EMPLOYES_PAR_SERVICES,
lesActivites T_ ACTIVITES_PAR_SERVICE
member procedure permuterEmploye(nA IN number, sA IN number, nB IN number, sB IN number);
CREATE TABLE ENTREPRISE OF T_ENTREPRISE
NESTED TABLE lesEmployesParService STORE AS lesEPS,
NESTED TABLE lesActivites STORE AS lesA;
-- Pour la table Affectation
CREATE TYPE T_AFFECTATION AS OBJECT (
numE Number,
numA Number,
nbh Number
) ;
CREATE TABLE AFFECTATION OF T_AFFECTATION ;

b) Formuler la requête R1 suivante : Quelles sont les activités du service numS = 12 ?

-- R1 :
SELECT e.lesActivites.titreA
FROM Entreprise e
WHERE e.lesServices.Snum=12;

c) L'employé numE=34 du service numS=12 permute avec l'employé numE=56 du service numS=21. Formuler cette mise à jour.

-- Changements dans la table Entreprise
CREATE OR REPLACE procedure permuterEmploye (nA IN number, sA IN number, nB IN number, sB
IN number) is
Nbh34 Number
Nbh56 Number
S12 T_EMPLOYE_PAR_SERVICE
S21 T_EMPLOYE_PAR_SERVICE
Begin
SELECT e.lesEmployesParServices INTO S12 FROM Entreprise e WHERE e.lesServices.Snum=sA;
SELECT e.lesEmployesParServices INTO S21 FROM Entreprise e WHERE e.lesServices.Snum=sB;
INSERT INTO S12 VALUES (SELECT numE, nomE FROM S21 WHERE numE=nB);
INSERT INTO S21 VALUES (SELECT numE, nomE FROM S12 WHERE numE=nA);
DELETE FROM S12 WHERE numE=nA;
DELETE FROM S21 WHERE numE=nB;
End;
--appel de la procedure
permuterEmploye(34,12,56,21)

d) Insérer un nouvel employé, DURACUIRE, matricule numE = 78 dans le service numS = 12.

INSERT INTO (SELECT e.lesEmployesParServices FROM Entreprise e WHERE e.lesServices.numS=12)
VALUES (T_EMPLOYE(78,’DURACUIRE’);

2)
a) Indiquer en SQL3/Oracle8 comment cette structure peut être créée.

-- Pour la creation de la table Entreprise
CREATE TYPE T_EMPLOYE2 AS OBJECT(
numE Number,
nomE varchar(30),
nbh Number,
numS Number
);
CREATE TYPE T_EMPLOYE_PAR_ACTIVITE AS TABLE OF T_EMPLOYE2 ;
CREATE TYPE T_ACTIVITE2 AS OBJECT (
numA Number,
titreA varchar(30)
lesEmployesParActivite T_EMPLOYE_PAR_ACTIVITE
)
NESTED TABLE lesEmployesParActivite STORE AS lesEPA;
CREATE TABLE ENTREPRISE2 OF T_AFFECTATION2 ;

b) Formuler la requête suivante R2 : Calculer le nombre d'heures affectées à chaque activité

SELECT titreA , sum(e.lesEmployesParActivite.nbh)
FROM Entreprise2 e
GROUP BY titreA