I N S T I T U T
S U P E R I E U R
INFORMATIQUE
ةـيــملاـعلإل يـلاـعلا دـهعمـلا
ISI
Année Universitaire 2020-2021
Mastère professionnel en logiciels libres - M1
Bases de données Avancées – Activité 3.1
R. ZAAFRANI, 19/10/2020
Correction de l'activité 3.1
-- Classes
1. CREATE TYPE Salle_t AS OBJECT(
Numero VARCHAR(20),
Videoprojecteur CHAR(1))
NOT INSTANTIABLE
NOT FINAL
/
CREATE TYPE Salle_cours_t UNDER Salle_t(
Capacite NUMBER(3),
Retroprojecteur CHAR(1),
Micro CHAR(1))
/
CREATE TYPE Salle_info_t UNDER Salle_t(
Nb_ordinateurs NUMBER(2),
OS VARCHAR(20))
/
CREATE TYPE Enseignement_t AS OBJECT(
Code VARCHAR(20),
Effectif NUMBER(3),
Videoprojecteur CHAR(1))
NOT FINAL
/
CREATE TYPE CM_t UNDER Enseignement_t(
Retroprojecteur CHAR(1))
/
CREATE TYPE TD_t UNDER Enseignement_t(
Publicité
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 !');
Publicité
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(
Publicité
(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
Publicité
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