NOM (en majuscule):………...…………..
………………………
FACULTE DES SCIENCES ET TECHNIQUES
DEPARTEMENT INFORMATIQUE PRENOM(en majuscule):……….………...…………………......
FILIERE MIP S4 2021-2022 CNE :………………………...………………………….……..….
Contrôle continu : Systèmes d’Information et Bases de Données
Durée 1h15min
Exercice 1 : Requêtes SQL
Soit le schéma d’une base de données relative à la gestion des prêts dans une bibliothèque :
Etudiant(CNE, nom, prenom, date_naissance,ville)
Livre(num_livre, titre, #num_collection, annee_edition)
Collection(num_collection, intitulé_collection, pays) //collection peut être : Vuibert, Dunod et etc.
Prêt(#CNE, #num_livre, Date_Pret, Date_Retour) //la clé primaire est un triplet
NB. Les clés primaires sont soulignées et les clés étrangères sont précédées par #
Un étudiant peut emprunter plusieurs livres. Un même livre peut être prêté par le même étudiant mais à
des différentes dates de prêt. La date de retour reste NULL tant que l’étudiant n’a pas rendu le livre à la
bibliothèque.
Exprimer en SQL les requêtes suivantes :
1. Afficher le nombre de livres pour chaque collection (num_collection et intitulé_collection). Trier
le résultat par ordre alphabétique décroissante des intitulés des collections. (2,5 points)
SELECT c.num_collection,c.intitule_collection,COUNT(l.num_livre) nb_livres
FROM collection c, livre l
WHERE c.num_collection=l.num_collection
GROUP BY c.num_collection
ORDER BY c.intitule_collection DESC ;
2. Afficher le livre (num_livre et titre) le plus prêté par les étudiants. (2,5 points)
SELECT l.num_livre, l.titre, COUNT(p.cne) nb_prets
FROM livre l, pret p
WHERE c.num_livre=p.num_livre
GROUP BY c.num_livre
ORDER BY COUNT(p.cne) DESC
LIMIT 1 ;
3. Afficher pour chaque étudiant (CNE, nom, prénom) le nombre de livres prêtés et qui ne sont pas
encore rendus à la bibliothèque (2,5 points)
SELECT e.CNE, e.nom, e.prenom, COUNT(p.num_livre) nb_prets_non_rendus
FROM etudinat e, pret p
WHERE e.CNE=p.CNE
AND p.date_retour IS NULL
GROUP BY e.CNE ;
1/4
4. Ajouter la contrainte NOT NULL à la colonne date_naissance de la table Etudiant. (2,5 point)
ALTER TABLE etudiant
MODIFY date_naissance DATE NOT NULL ;
5. Donner l’ordre SQL qui permet de créer la table Livre avec toutes ses contraintes (on suppose que les
autres tables sont créées) (3 points)
CREATE TABLE livre(
num_livre INT,
titre VARCHAR(50),
num_collection INT ,
annee_edition YEAR,
CONSTRAINT cle_pk_livre PRIMARY KEY(num_livre),
CONSTRAINT cle_pf_livre_collec FOREIGN KEY(num_collection)
REFERENCES collection(numcollection)
) ENGINE=INOODB ;
Exercice 2:
Soit le schéma (simplifié) de base de données relative à la gestion des professeurs de l’enseignement supérieur.
Chaque professeur appartient à un seul département et chaque département est géré un chef de département. Ce
dernier est choisi parmi les professeur du département.
Departement(num_departement, nom_ departement, #id_chef_departement)
Professeur(matricule, nom, prenom, date_naissance, #num_departement)
NB. - Les clés primaires sont soulignées et les clés étrangères sont précédées par #
- La colonne id_chef_departement fait référence à la colonne matricule de la table Professeur.
- La colonne num_departement de la table Professeur fait référence à la colonne num_departement de
la table Departement.
On suppose que les données suivantes sont déjà mémorisées dans la base de données :
La table « Professeur » La table « Departement »
date num Num Id chef
matricule Nom prénom naissance département département nom département
P20 Adlani Omar 1980-10-12 100 100 Informatique P20
P10 Melouki Zineb 1965-03-25 200 200 Physique P10
P30 Yaaqob Zakaria 1960-09-14 NULL 300 Biologie NULL
P40 Saaidi Ali 1967-04-23 NULL
6. Question : Cocher valide si la commande SQL peut être exécutée sans problème et non valide
dans le cas contraire. Uniquement les lignes ajoutées ou modifiées et valides qu’on peut leurs
faire référence dans les des deux tables. (4 points)
2/4
Commande SQL Valide Nom valide (Justifier pourquoi)
INSERT INTO Professeur
VALUES('P60','Melouki','Zineb','1998-04-
26',NULL) ;
INSERT INTO Professeur
VALUES('P70','Mechkour','Ali','1999-02-
21',300) ;
UPDATE Professeur
SET num_departement=100
WHERE num_departement=NULL ;
INSERT INTO Professeur
VALUES('P10','Aalmi','Kenza,'1997-07- Clé primaire doit être unique
15',200) ;
UPDATE Departement
Père P50 n’existe pas
SET id_chef_departement='P50'
WHERE num_departement =300;
INSERT INTO Departement
VALUES(400,'Chimie','P30') ;
INSERT INTO Departement Clé primaire doit être unique
VALUES(200,'Mathématiques','P40') ;
DELETTE FROM Departement Des professeurs qui sont toujours affectés au
WHERE num_departement=300 ; département 300.
Exercice3 : Question de cours
Vrai Faux
Cocher la bonne réponse : (3 points)
ALTER est une instruction LMD qui permet de mettre à jour les données
stockées dans les tables d’une base de données
Update est une instruction LDD qui permet de modifier la structure des
tables d’une base de données.
DROP est une instruction LMD qui permet de supprimer les lignes d’une
table.
Le type CHAR est plus simple et gère mieux que le type VARCHAR
l’espace mémoire occupé par une chaîne de caractères.
Le couple des contraintes (UNIQUE, NOT NULL) et la contrainte
PRIMARY KEY sont équivalentes
Le moteur de stockage INOODB est plus économique en espace mémoire
que le moteur de stockage MyIsam
3/4
