Série BD-
Exercice1 :
Pour chacune de ces propositions, cocher la (ou les) bonne (s) réponse (s) :
1) Les contraintes de domaine permettent :
L’attribution d’une clé primaire a chaque table
L’attribution des règles de validité des valeurs d’une colonne
L’attribution de l’aspect obligatoire (ou non) des valeurs d’une colonne)
L’attribution des contraintes d’intégrité sur les colonnes
2) Dans une table, une ligne:
Doit contenir un seul enregistrement.
Doit contenir des données moins que le nombre total des colonnes.
Doit contenir une clé primaire.
3) Une clé étrangère
Peut contenir des doublant
Peut être une partie d’une clé primaire d’une autre table
Peut contenir des valeurs Nul
Exercice2 :
Ci-dessous, on présente le contenu des trois tables Client, Article et Facture d’une base de données. Cette base a
été conçue par un débutant et présente certaines anomalies. Le contenu des trois tables est :
Table Client Table Article
CodCl NomCl PrenCl CodArt LibArt PrixArt Avec :
123 Nefzi Hassan 003445 PC HP 1380 CodCl : Code du
426 Fatnassi Aymen client.
004516 PC IBM -1490
456 Awadi Hamma NomCl : Nom du
012365 PC SIEM 1320
426 Saife Klai PrenCl : Prénom du
023146 PC DELL 1200
789 Wahhabi Lased client.
045696 PC SIEM. 1300
CodArt : Code de
098745 IMP. HP 420 l’article
LibArt : Libelle de
Table Facture l'article.
NumFact DatFact CodCl CodArt NB : PrixArt : Prix de
123457 25/01/10 123 012365 On suppose l’article.
199992 26/01/12 426 045696 qu'une NumFact : Numéro
124587 18/02/01 426 004516 facture ne de la facture
123333 22/02/11 456 023146 concerne Facture DatFact :
199965 03/03/12 789 098746 qu'un seul Date de la facture.
123456 03/03/05 123 003445 article.
Question :
1- En se basant sur les contenus de ces tables, il apparaît que trois contraintes d’intégrité n’ont pas été
respectées.
Anomalie Contrainte d’intégrité non respecté Contrainte de correction
2- Déduire la représentation graphique
Problème :
Soit la BD suivante relative à la gestion des notes annuelles d'une classe
CLASSE ( CodC, Libelle, NombElv)
ELEV (NumE, NoPrE, DateN,Sexe, CodC#)
SALLE (CodS, NombPlac, NumE#)
MATIERE (CodM, NomM, Nbrehe, CoeffM)
EPREUVE (NumE#, codM#, DateEpreuve, Note)
1. Déduire la représentation graphique
CLASSE ELEV SALLE EPREUVE MATIERE
CodC NumE CodS NumE # CodM
Libelle NoPrE NombPlac codM # NomM
NombElv DateN NumE# DateEpreuve Nbrehe
Sexe Note CoeffM
CodC#
2. Donner la commande SQL qui permet de créer la base donnée ‘Lycée’
CREATE DATABASE LYCEE ;
3. Donner la commande SQL qui permet de créer les tables de la base de données tout en maintenant les
contraintes d'intégrités référentielles.
CREATE TABLE CLASSE (
CODC INT(8) PRIMARY KEY,
LIBELLE VARCHAR(20),
NOMBELV INT(2)) ;
CREATE TABLE EPREUVE (
NUME INT(8) REFERENCES eleve(Nume) ,
CODM VARCHAR (20) REFERENCES MATIERE (CodM)
DATEEPREUVE DATE,
NOTE DECIMAL(4,2)
PRIMARY KEY (NUME,CODM, DATEEPREUVE));
4. Donner la commande SQL qui permet d’ajouter une colonne nommé « Type_mat » (chaîne de 15
caractères non nuls) à la table MATIERE.
ALTER TABLE MATIERE
ADD COLUMN TYPE_MAT VARCHAR(15) ;
5. Donner la commande SQL qui permet de supprimer la colonne Type_mat
ALTER TABLE MATIERE
DROP COLUMN TYPE_MAT ;
6. Donner la commande SQL d’ajouter une contrainte pour que le note entre 0 et 20
ALTER TABLE EPREUVE
ADD CONSTRAINT a CHECK NOTE>=0 AND N<=20
Ou bien
ADD CONSTRAINT CHECK NOTE BETWEEN 0 AND 20 ;
7. Donner la commande SQL d’ajouter une contrainte pour que par default le nombre d’élève est 25
ALTER TABLE CLASSE
ADD CONSTRAINT CHECK NombElv DEFAULT 25 ;
8. Donner la commande SQL pour désactivé la contrainte appliqué sur le champ Note
ALTER TABLE EPREUVE
DISABLED CONSTRAINT a ;
9. Donner la commande SQL qui permet de renomme la table ELEV a ELEVE
ALTER TABLE ELEV
RENAME TO eleve ;
10. Donner la commande SQL qui permet d'ajouter l’élève suivant:
NumEleve NomPrenEleve CodC
8200 samir saffi 4SI1
8300 Karim bilakhel 4SI1
INSERT INTO ELEVE
VALUES (8200, samir saffi, 4SI1),( 8300, Karim bilakhel, 4SI1) ;
Ou bien
INSERT INTO ELEVE
VALUES (8200, samir saffi, 4SI1;
INSERT INTO ELEVE
VALUES ( 8300, Karim bilakhel, 4SI1) ;
11. Donner la commande SQL qui permet de supprimer l’élève ayant pour numéro 8200 de la table
ELEVE.
DELETE FROM ELEVE
WHERE NUMEELEVE=8200.
12. Effacer les élèves qui ont des dates de naissance inférieure à 31/12/1987
DELETE FROM ELEVE
WHERE DATEN< ‘1987-12-31’ ;
13. Ajouter 2% don les notes des élevés dont la matière STI
UPDATE EPREUVE
SET NOTE=NOTE+NOTE*0.02 /* OU BIEN SET NOTE=NOTE*1.02
WHERE CODM=’4SI ‘;
14. Doubler le coefficient de la matière ‘BD’
UPDATE matiere
SET CoeffM= CoeffM*2
WHERE CODM=’BD‘;
15. Ajouter deux points à tous les élèves qui ont une note inférieure à 6 dans la matière qui a comme
code 523.
Syntaxe de la requête select
16. Afficher les noms des élevés sans répétitions
SELECT [*(tous les champs)/DISTINCT(sans repetition)/NOM DE LA
COLONNE/{MAX ,MIN ,AVG(moyenne) ,SUM(somme) ,COUNT(nombre)}]
FROM table
WHERE condition(s)
OREDER BY(champ) ASC (CROISSANT) DESC(decroissant)
Between (entre) in(dans)
SELECT DISTINCT(nom)
FROM ELEVE ;
17. Afficher la liste des sales dont le nombre de place est entre 15 et 20
SELECT *
FROM SALLE
WHERE NombPlac BETWEEN 15 AND 20 ;
18. Afficher la liste des matières par ordre croissant selon le coefficient
SELECT *
FROM MATIERE
ORDER BY (COEFMAT) ASC ;
19. Afficher les numéros des élèves dons leurs noms commence par S ou termine Z
SELECT NUME
FROM ELEVE
WHERE NOPRE LIKE ‘S%’ or NOPRE LIKE ‘%Z’ ;
Commence par A Nom like ‘A%’
Termine par A Nom like ‘%A’
Contient A Nom like ‘%A%’
2eme caractère A Nom like ‘_A%’
20. Afficher la liste des élèves de la classe ‘3SI 1’ trié par ordre décroissant des dates de naissances
puis par ordre croissant des noms.
SELECT *
FROM ELEVE
WHERE CODC=3SI
ORDER BY (DateN) DESC
ORDER BY (NoPrE) ASC ;
21. Afficher le nom et prénom des élèves dont l’année de naissance est 1995
SELECT NoPrE
FROM ELEVE
WHERE DateN DATNAISS LIKE ‘1995%’ ;
22. Afficher la classe de l’élève ‘Tounsi Mohamed’ sans répétition
23. Afficher les noms des matières dont le nombre d’heure est entre 4 et 8 et leur coefficient dans
l’intervalle 2 et 4
24. Afficher le nombre d’élève dont la note compris entre 6 et 18
25. Afficher les noms des élevés dont les notes >10
26. Afficher la note maximal de l’élevé numéro 8200
27. Afficher la moyenne des note de tous les élevés
28. Afficher les noms et prénoms des élèves de la classe dont le NBREELEV>20
29. Afficher la liste ( noms des mat, et nobrHeu) qui ont un note entre 10 et 20
30. Afficher la liste de tous les élèves dont l’année de naissance est 2004
