EXAMEN BASE DE DONNÉES
Exercice 2 : (12 pts)
« Vous devez établir le Modèle Conceptuel de Données (MCD) gérant les professeurs et élèves d’un établissement scolaire type lycée. Chaque
enseignant est spécialisé dans une ou plusieurs matières et peut enseigner ou pas à des classes. Chaque élève appartient à une classe caractérisée
par son nom, sa spécialité et le nombre d’élèves qu’elle comporte. Au cours d’une année toutes les matières ne sont pas forcément enseignées.
Les élèves suivent des enseignements portant sur plusieurs matières. Chaque élève est évalué trois fois pour chaque matière. On connaît les
noms, prénom, date naissance et adresse des élèves mais juste les nom et prénom des professeurs. En fonction de la classe d’appartenance un
coefficient est affecté à chaque matière »
1
Questions :
1. On utilisant le model des cardinalités vu en cours, compléter le model E/A ? (2 pts)
2. On appliquant les règles de passage vues en cours, déduire le model relationnel
correspondant ? (2 pts)
3. Donner la requête SQL Qui permet:
a. La création de la relation ELEVE ? (1pts)
b. L’insertion de l’article (02, ‘ARIDJ’,’Mohamed’) dans la relation professeur ? (1pts)
c. D’ajouter le champ Tel (N° du téléphone) dans la relation professeur ? (1pts)
d. De donner le nombre d’élève dans cet établissement ? (1pts)
e. De lister le Nom et le prénom de tous les élèves du professeur ARIDJ
Mohamed ? (1pts)
4. Exprimer en algèbre relationnel les requêtes suivantes :
a. « Lister le nom et le prénom des élèves qui ont suivis toute le matières »
(1pts)
b. « lister les noms et les prénoms des professeurs de la matière « bases de
données » (1pts)
c. « lister les noms et les prénoms des professeurs qui n’ont jamais enseignés la
matière « bases de données » (1pts)
2
Exercice 3 : (9 Pts)
Commande Nc Nom Pnom Adr Code_P Dés_P Q_C
001 Mohamed Ali Chlef P1 Ecran 03
001 Mohamed Ali Chlef P2 Processeur 02
002 Adda Karim Alger P1 Ecran 05
002 Adda Karim Alger P3 scaner 05
003 Kadour mohamed Chlef P4 Clavier 06
Nc : Numéro client / Code_p : code produit / Dés_p : désignation (Nom) du produit
Q_c : quantité commandée.
1. Citez les 3 types d’anomalies que peut comporter une relation universelle non
normalisée ? Pour chaque type donner un exemple sur la relation « commande » ? (3
pts)
Soit F= { NcNom
NcPnom
NcAdr
Nc,NomPnom
NomAdr
Code_pDés_p /
Nc,Code_p Q_C /
Q_c Nc}
2. Donner une couverture minimale de F ? (1pt)
3. Donner une clé pour la relation « Commande » ? justifier ? (1 pt)
4. Sous quelle forme normale est le schéma « Commande ? justifier ? (1 pt)
5. On appliquant l’algorithme de décomposition en utilisant le Graphe des DF vu en cours ,
Donner une décomposition de « Commande »en 3ème forme normale ( 3 NF) ? (3 pts)
6. Les schémas obtenus à la question précédente sont-ils en BCNF ? justifier (1 pt)
3
CORRECTION EXAMEN BASE DE DONNÉES
Exercice 1 : (12 pts)
1. On utilisant le model des cardinalités vu en cours, compléter le model E/A ? (2 pts)
N M
N
N M
M
1
N
N M
1
Questions :
2. le model relationnel correspondant (2 pts) :
Elève (Numéroélève,Nomélève, Prénomélève,Datenaissanceélève,Adresseélève,Nomclasse)
Classe(Nomclasse, Nombreélève,Spécialité)
Professeur (Numéroprofesseur,Nomprofesseur,Prénomprofesseur)
Matière(Codematière,Libilématière)
Suivre(Numéroélève,Codematière, Note1,Note2,Note3)
Concerner(Nomclasse,Codematière, Coefficient)
Enseigner(Nomclasse,Numéroprofesseur , Nombre d’heures)
Etrespécialiste( Numéroprofesseur, Codematière)
3. Donner la requête SQL Qui permet:
a. La création de la relation ELEVE ? (1pts)
Create Table Elève
(Numéroélève : Integer NOT NULL,PRIMARY KEY
Nomélève : VarChar(30) NOT NULL
PrénomélèveVarchar(30) NOT NULL
Datenaissanceélève :Date
Adresseélève :VarChar(50) NOT NULL
Nomclasse :Integer FORIENGN KEY )
b. L’insertion de l’article (02, ‘ARIDJ’,’Mohamed’) dans la relation professeur ? (1pts)
Insert Into Professeur Values (02, ‘ARIDJ’,’Mohamed’)
c. D’ajouter le champ Tel (N° du téléphone) dans la relation professeur ? (1pts)
Alter Table Professeur ADD (TEL VarChar(10) NOT NULL)
d. De donner le nombre d’élève dans cet établissement ? (1pts)
Select Count(*) From Elève.
2
e. De lister le Nom et le prénom de tous les élèves du professeur ARIDJ
Mohamed ? (1pts)
SELECT Nomélève, Prénomélève
FROM Elève, Enseigner, Professeur
WHERE (Nomprofesseur=’ARIDJ’ AND
[Link]éroprofesseur= [Link]éroprofesseur AND
Elè[Link], [Link] )
4. Exprimer en algèbre relationnel les requêtes suivantes :
a. « Lister le nom et le prénom des élèves qui ont suivis toute le matières » (1pts)
R1Proj(Suivre / Numéroélève,Codematière)
R2Proj(Classe /Nomclasse)
R3 DIV(R1,R2)
R4Join (Elève , R3/Eleve. Numéroélève=[Link]éroélève)
Res1Proj(R4/ Nomélève, Prénomélève)
b. « lister les noms et les prénoms des professeurs de la matière « bases de
données » (1pts)
R1Select (Matière/ Libilématière=’ bases de données ‘)
R2Join (R1,Etrespécialiste/ [Link]ère =Etrespé[Link]ère )
R3Join(R2,Professeur / R2 Numéroprofesseur=[Link]éroprofesseur)
Res2Proj(R3 / Nomprofesseur,Prénomprofesseur)
c. « lister les noms et les prénoms des professeurs qui n’ont jamais enseignés la
matière « bases de données » (1pts)
R1Proj(Professeur// Nomprofesseur,Prénomprofesseur)
Res3Minus(R1,Res2)
3
Exercice 2 : (9 Pts)
1. les 3 types d’anomalies :
a) anomalie d'insertion : on ne peut insérer un client si il ne commande pas de
produits.
b) anomalie de suppression : si on supprime un client on doit supprimer touts les
produits commandés par ce clients.
c) anomalie de modification : si on change une propriété d’un client (Prnom) on doit
le changer pour toutes les occurrences de ce client.
2. Couverture minimale F^de F ? (1pt)
F^=F-{ DFT+ DFE)
NcNom est DFD et DFE
NcPnom est DFD et DFE
NcAdr est DFT (NcNom et NomAdr alors NcAdr )
Nc,NomPnom est DFE (NcPnom)
NomAdr est DFD et DFE
Code_pDés_p est DFD et DFE
Nc,Code_p Q_C est DFD et DFE
Q_c Nc est DFD et DFE
F^= {NcNom / NcPnom /NomAdr / Code_pDés_p Nc,Code_p Q_C
Q_c Nc}
3. Donner une clé pour la relation « Commande » ? justifier ? (1 pt)
La clé de la relation « Commande » est de Nc,Code_p car touts les autre attribues
sont en DF avec Nc,Code_p
4. Sous quelle forme normale est le schéma « Commande ? justifier ? (1 pt)
a) Commande est elle en 1NF :?
Tout les domaines des attribues de commande sont élémentaire (Type simple) donc
Commande est en 1NF
b) Commande est elle en 2 NF ?
Existe des DF avec la clé qui ne sont pas DFE (Nc,Code_p Nom) Donc
Commande n’est pas en 2NF
