Correction de l'activité 3.1 – Bases de données Avancées

Sur_machine CHAR(1))

/

2. CREATE TYPE Planning_t AS OBJECT(

Ref_salle REF Salle_t,

Ref_ens REF Enseignement_t,

Jour VARCHAR(10),

Heure_debut NUMBER(4,1),

Heure_fin NUMBER(4,1))

/

-- Tables objets

CREATE TABLE Salle OF Salle_t (

CONSTRAINT Salle_pk PRIMARY KEY(Numero));

CREATE TABLE Enseignement OF Enseignement_t (

CONSTRAINT Ens_pk PRIMARY KEY(Code));

CREATE TABLE Planning OF Planning_t (

--CONSTRAINT Planning_pk PRIMARY KEY(Ref_salle, Ref_ens) impossible !

CONSTRAINT Planning_ref_salle Ref_salle REFERENCES Salle,

CONSTRAINT Planning_ref_salle_null CHECK (Ref_salle IS NOT NULL),

CONSTRAINT Planning_ref_ens Ref_ens REFERENCES Enseignement,

CONSTRAINT Planning_ref_ens_null CHECK (Ref_ens IS NOT NULL));

Bases de données Avancées – Activité 3.1

1

CREATE OR REPLACE TRIGGER trig_pk_planning

BEFORE INSERT OR UPDATE ON PLANNING

FOR EACH ROW

DECLARE

n INTEGER;

e EXCEPTION;

BEGIN

SELECT COUNT(*) INTO n FROM PLANNING p

WHERE p.ref_salle = :NEW.ref_salle and p.ref_ens = :NEW.ref_ens;

IF n > 1 THEN

RAISE e;

END IF;

EXCEPTION

WHEN e THEN RAISE_APPLICATION_ERROR(-20501, 'Clé primaire existante !');

END;

/

-- Instances

INSERT INTO Salle VALUES(

Salle_cours_t ('Grand amphi', 'O', 400, 'O', 'O'));

INSERT INTO Salle VALUES(

Salle_cours_t ('A1', 'N', 80, 'N', 'N'));

INSERT INTO Salle VALUES(

Salle_cours_t ('A2', 'N', 50, 'O', 'N'));

INSERT INTO Salle VALUES(

Salle_info_t ('I1', 'O', 15, 'Windows'));

INSERT INTO Salle VALUES(

Salle_info_t ('I2', 'N', 15, 'Windows/Linux'));

INSERT INTO Enseignement VALUES(

'S0INFO', 500, 'N');

INSERT INTO Enseignement VALUES(

CM_t ('S1BDPROGCM', 50, 'O', 'N'));

INSERT INTO Enseignement VALUES(

CM_t ('S2BDACM', 25, 'O', 'N'));

INSERT INTO Enseignement VALUES(

TD_t ('S1BDPROGTD1', 50, 'O', 'N'));

INSERT INTO Enseignement VALUES(

TD_t ('S1BDPROGTD2', 25, 'N', 'O'));

INSERT INTO Enseignement VALUES(

TD_t ('S2BDATD', 25, 'N', 'N'));

INSERT INTO Planning VALUES(

(SELECT REF(s) FROM Salle s WHERE s.Numero = 'A1'),

(SELECT REF(e) FROM Enseignement e WHERE e.Code = 'S1BDPROGCM'),

'Mardi', 8, 9.5);

INSERT INTO Planning VALUES(

(SELECT REF(s) FROM Salle s WHERE s.Numero = 'A2'),

(SELECT REF(e) FROM Enseignement e WHERE e.Code = 'S2BDACM'),

Bases de données Avancées – Activité 3.1

2

'Mercredi', 8, 9.5);

INSERT INTO Planning VALUES(

(SELECT REF(s) FROM Salle s WHERE s.Numero = 'A1'),

(SELECT REF(e) FROM Enseignement e WHERE e.Code = 'S2BDACM'),

'Mercredi', 9.5, 11);

INSERT INTO Planning VALUES(

(SELECT REF(s) FROM Salle s WHERE s.Numero = 'Grand amphi'),

(SELECT REF(e) FROM Enseignement e WHERE e.Code = 'S0INFO'),

'Lundi', 15, 16.5);

INSERT INTO Planning VALUES(

(SELECT REF(s) FROM Salle s WHERE s.Numero = 'A1'),

(SELECT REF(e) FROM Enseignement e WHERE e.Code = 'S1BDPROGTD1'),

'Mardi', 9.5, 11);

INSERT INTO Planning VALUES(

(SELECT REF(s) FROM Salle s WHERE s.Numero = 'I2'),

(SELECT REF(e) FROM Enseignement e WHERE e.Code = 'S1BDPROGTD2'),

'Mardi', 15, 16.5);

INSERT INTO Planning VALUES(

(SELECT REF(s) FROM Salle s WHERE s.Numero = 'I2'),

(SELECT REF(e) FROM Enseignement e WHERE e.Code = 'S2BDATD'),

'Jeudi', 9.5, 11);

-- Requêtes

-- 1

SELECT VALUE(s) FROM Salle s;

Select * retourne seulement les numéros de salle et l’état du vidéo projecteur (attributs de Salle_t).

-- 2

SELECT VALUE(e) FROM Enseignement e;

Select * retourne seulement les codes d’enseignement, l’effectif et l’état du vidéo projecteur (attributs de

enseignement_t).

-- 3

SELECT * FROM Planning;

Select * fonctionne car la classe Planing_t n’admet pas d’objets de sous-classes.

-- 4

SELECT p.Ref_ens.Code, p.Ref_salle.Numero, Jour, Heure_debut, Heure_fin

FROM Planning p;

-- 5

SELECT p.Ref_ens.Code, p.Ref_salle.Numero

FROM Planning p

WHERE p.Ref_ens.Videoprojecteur = 'O'

AND p.Ref_salle.Videoprojecteur = 'N';

-- 6

SELECT p.Ref_salle.Numero, COUNT(DISTINCT p.Ref_ens)

FROM Planning p

GROUP BY p.Ref_salle.Numero;

-- 7

Bases de données Avancées – Activité 3.1

3

SELECT Numero FROM Salle s

WHERE s.Numero NOT IN (SELECT p.Ref_salle.Numero FROM Planning p);

SELECT Numero FROM Salle s1

WHERE (SELECT REF(s2) FROM Salle s2 WHERE s2.Numero = s1.Numero)

NOT IN (SELECT Ref_salle FROM Planning);

-- 8

SELECT s.Numero

FROM Salle s

WHERE OBJECT_VALUE IS OF(Salle_cours_t);

-- 9

SELECT s.Numero, TREAT(OBJECT_VALUE AS Salle_info_t).Nb_ordinateurs

FROM Salle s

WHERE OBJECT_VALUE IS OF(Salle_info_t);

-- 10

SELECT s.Numero, TREAT(OBJECT_VALUE AS Salle_info_t).Nb_ordinateurs

FROM Salle s;

-- 11

SELECT p.Ref_ens.Code, TREAT(DEREF(p.Ref_ens) AS CM_t).Retroprojecteur,

p.Ref_salle.Numero, TREAT(DEREF(p.Ref_salle) AS Salle_cours_t).Retroprojecteur

FROM Planning p

WHERE DEREF(p.Ref_ens) IS OF(CM_t)

AND DEREF(p.Ref_salle) IS OF(Salle_cours_t);

Bases de données Avancées – Activité 3.1

4