République Tunisienne
Ministère de l'Enseignement Supérieur et de
Recherche Scientifique
Université Tunis El Manar
Matière :
Enseignante :
Bases de données et interfaçages. TP2 : SQL3 (Partie 2) Dr. Zakia ZOUAGHIA
Classes : 1ING AU : 2025/2026
Exercice 1 :
On considère la base de données représentant la gestion du planning des salles de l’université
dont le schéma conceptuel UML est le suivant :
Classes
Créer les classes figurant sur le diagramme UML ci-dessus (sauf PLANNING) en tenant
compte des liens d’héritage entre elles.
Matérialiser la classe-association PLANNING sous forme d’un type T_Planning incluant en
plus des attributs mentionnés dans le diagramme de classes deux attributs références de salle
et d’enseignement, respectivement.
Tables objets
Créer trois tables objets afin de stocker les données relatives à la gestion du planning :
o Salle (stockage des instances des types T_Salle, T_Salle_cours et T_Salle_info) ;
o Enseignement (stockage des instances des types T_Enseignement, T_CM et T_TD) ;
1
o Planning (stockage des instances du type T_Planning).
Instances
Requêtes
2
1. Liste de toutes les salles. Est-ce qu’un SELECT * fonctionne ?
2. Liste de tous les enseignements. Est-ce qu’un SELECT * fonctionne ?
3. État « brut » du planning. Est-ce qu’un SELECT * fonctionne ? Pourquoi ?
4. Planning des salles : code d’enseignement, numéro de salle, jour, heure de début et
heure de fin de l’enseignement (jointure implicite).
5. Code d’enseignement et numéro de salle pour les enseignements nécessitant un
vidéoprojecteur et affectés à une salle qui n’en dispose pas.
6. Nombre d’enseignements différents par salle.
7. Numéro des salles qui ne figurent pas au planning (salles libres).
Solution :
-- Types
CREATE TYPE T_Salle AS OBJECT(
Numero VARCHAR(20),
Videoprojecteur CHAR(1))
NOT FINAL
/
CREATE TYPE T_Salle_cours UNDER T_Salle(
Capacite NUMBER(3),
Retroprojecteur CHAR(1),
Micro CHAR(1))
/
CREATE TYPE T_Salle_info UNDER T_Salle(
Nb_ordinateurs NUMBER(2),
OS VARCHAR(20))
/
CREATE TYPE T_Enseignement AS OBJECT(
Code VARCHAR(20),
Effectif NUMBER(3),
Videoprojecteur CHAR(1))
NOT FINAL
CREATE TYPE T_CM UNDER T_Enseignement(
Retroprojecteur CHAR(1))
/
CREATE TYPE T_TD UNDER T_Enseignement(
Sur_machine CHAR(1))
/
CREATE TYPE T_Planning AS OBJECT(
Ref_salle REF T_Salle,
Ref_ens REF T_Enseignement,
Jour VARCHAR(10),
Heure_debut NUMBER(4,1),
3
Heure_fin NUMBER(4,1))
/
-- Tables
CREATE TABLE Salle OF T_Salle(
CONSTRAINT Salle_pk PRIMARY KEY(Numero));
CREATE TABLE Enseignement OF T_Enseignement(
CONSTRAINT Ens_pk PRIMARY KEY(Code));
CREATE TABLE Planning OF T_Planning(
-- 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));
-- Instances
INSERT INTO Salle VALUES(
T_Salle_cours('Amphi Cassin', 'O', 400, 'O', 'O'));
INSERT INTO Salle VALUES(
T_Salle_cours('L231', 'N', 80, 'N', 'N'));
INSERT INTO Salle VALUES(
T_Salle_cours('K188', 'N', 50, 'O', 'N'));
INSERT INTO Salle VALUES(
T_Salle_info('L219', 'O', 12, 'Windows'));
INSERT INTO Salle VALUES(
T_Salle_info('K192', 'N', 12, 'Windows/Linux'));
INSERT INTO Enseignement VALUES(
'S0INFO', 500, 'N');
INSERT INTO Enseignement VALUES(
T_CM('S1BDPROGCM', 50, 'O', 'N'));
INSERT INTO Enseignement VALUES(
T_CM('S2BDACM', 25, 'O', 'N'));
INSERT INTO Enseignement VALUES(
T_TD('S1BDPROGTD1', 50, 'O', 'N'));
INSERT INTO Enseignement VALUES(
T_TD('S1BDPROGTD2', 25, 'N', 'O'));
INSERT INTO Enseignement VALUES(
4
T_TD('S2BDATD', 25, 'N', 'N'));
INSERT INTO Planning VALUES(
(SELECT REF(s) FROM Salle s WHERE [Link] = 'L231'),
(SELECT REF(e) FROM Enseignement e WHERE [Link] = 'S1BDPROGCM'),
'Mardi', 8, 9.5);
INSERT INTO Planning VALUES(
(SELECT REF(s) FROM Salle s WHERE [Link] = 'K188'),
(SELECT REF(e) FROM Enseignement e WHERE [Link] = 'S2BDACM'),
'Mercredi', 8, 9.5);
INSERT INTO Planning VALUES(
(SELECT REF(s) FROM Salle s WHERE [Link] = 'L231'),
(SELECT REF(e) FROM Enseignement e WHERE [Link] = 'S2BDACM'),
'Mercredi', 9.5, 11);
INSERT INTO Planning VALUES(
(SELECT REF(s) FROM Salle s WHERE [Link] = 'Amphi Cassin'),
(SELECT REF(e) FROM Enseignement e WHERE [Link] = 'S0INFO'),
'Lundi', 15, 16.5);
INSERT INTO Planning VALUES(
(SELECT REF(s) FROM Salle s WHERE [Link] = 'L231'),
(SELECT REF(e) FROM Enseignement e WHERE [Link] = 'S1BDPROGTD1'),
'Mardi', 9.5, 11);
INSERT INTO Planning VALUES(
(SELECT REF(s) FROM Salle s WHERE [Link] = 'K192'),
(SELECT REF(e) FROM Enseignement e WHERE [Link] = 'S1BDPROGTD2'),
'Mardi', 15, 16.5);
INSERT INTO Planning VALUES(
(SELECT REF(s) FROM Salle s WHERE [Link] = 'K192'),
(SELECT REF(e) FROM Enseignement e WHERE [Link] = 'S2BDATD'),
'Jeudi', 9.5, 11);
-- Requêtes
-- 4 SELECT p.Ref_ens.Code, p.Ref_salle.Numero, Jour, Heure_debut, Heure_fin
FROM Planning p;
5
-- 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 SELECT Numero
FROM Salle s1
WHERE (SELECT REF(s2) FROM Salle s2 WHERE [Link] = [Link])
NOT IN (SELECT Ref_salle FROM Planning);
6
Exercice 2 : Nested Tables
1) Créer les types suivants :
Type typBureau : <centre : char, batiment : char, numero :int>
Type typListeTelephones : collection de <entier>
Type TypSpecialite : <domaine : char, Specialite : char>
Type typListeSpecialites : colelction de <typSpecialite>
2) Créer la table suivante :
tIntervenant (#nom :char, prenom :char, bureau :typBureau, ltelephones :
typListeTelephone, lspecialites : typListeSpecialites)
3) Ecrire en SQL3 les requêtes permettant de :
a. Insérer les différentes données résumées dans la table ci-dessus.
b. Afficher le nom et le numéro de téléphone de tous les enseignants.
c. Afficher le nom, le domaine et la technologie des enseignants qui
travaillent dans le batiments K.
d. Afficher le nom, column_value, Domaine pour tous les enseignants.
7
-- Script de creation des types
CREATE OR REPLACE TYPE typBureau AS OBJECT (
centre char(2),
batiment char(1),
numero number(3)
);
/
CREATE OR REPLACE TYPE typListeTelephones AS TABLE OF
nomber(10);
/
CREATE OR REPLACE TYPE typSpecialite AS OBJECT (
domaine varchar2(15),
technologie varchar2(15)
);
/
CREATE OR REPLACE TYPE typListeSpecialites AS TABLE OF
typSpecialite;
/
-- Script de création de la table tIntervenant
CREATE TABLE tIntervenant (
pknom varchar2(20) PRIMARY KEY,
prenom varchar2(20),
bureau typBureau,
ltelephones typListeTelephones,
lspecialites typListeSpecialites
)
NESTED TABLE ltelephones STORE AS tIntervenant_nt1,
NESTED TABLE lspecialites STORE AS tIntervenant_nt2;
8
-- a) Requête d’insertion des données dans la table tIntervenant
INSERT INTO tIntervenant (pknom , prenom, bureau, ltelephones, lspecialites)
VALUES('Crozat', 'Stéphane',typBureau('PG', 'K', 256),
typListeTelephones(0687990000,0912345678,0344231234),
typListeSpecialites(typSpecialite('BD', 'SGBDR'), typSpecialite('Doc', 'XML'),
typSpecialite('BD','SGBDRO'))
);
9
INSERT INTO tIntervenant (pknom , prenom, bureau, ltelephones, lspecialites)
VALUES('Vincent', 'Antoine',typBureau('R', 'C', 123),
typListeTelephones(0344231235,0687990001),
typListeSpecialites(typSpecialite('IC', 'Ontologie'),
typSpecialite('BD', 'SGBDRO'))
);
-- b) Requête : afficher le nom et le numéro de téléphone de tous les
enseignants
SELECT [Link], t.*
FROM tIntervenant e, TABLE([Link]) t ;
10
-- c) Requête : afficher le nom, le domaine et la technologie des
enseignants qui travaillent dans le batiments K.
SELECT [Link], s.*
FROM tIntervenant e, TABLE([Link] ) s
WHERE [Link] = 'K' ;
-- d) Requête : d’afficher le nom, column_value, Domaine pour tous les
enseignants.
SELECT [Link], t.COLUMN_VALUE, [Link]
FROM tIntervenant e, TABLE ([Link]) t, TABLE ([Link]) s
11
Remarque :
1) Une table relationnelle (pas nécessairement objet-relationnelle) peut contenir une ou
plusieurs tables imbriquées :
(i) NESTED TABLE : collection non-ordonnées et non limitée en nombre d’éléments
(ii) et tableau pré dimensionné (VARRAY) : collection d’éléments de même type,
ordonnées et limitée en taille.
2) Lorsque la table imbriquée est une table de type scalaire (et non une table d'objets d'un
type utilisateur), alors la colonne de cette table n'a pas de nom (puisque le type est
scalaire la table n'a qu'une colonne). Pour accéder à cette colonne, il faut utiliser une
syntaxe dédiée : COLUMN_VALUE.
3) Le principe du modèle imbriqué est qu'un attribut d'une table ne sera plus seulement
valué par une unique valeur scalaire), mais pourra l'être par un vecteur (enregistrement)
ou une collection de scalaires ou de vecteurs, c'est à dire une autre table.
12