Université Paris Ouest Nanterre la Défense. UFR SEGMI. Année

Page 1 sur 2
Université Paris Ouest Nanterre la Défense.
UFR SEGMI.
Année Universitaire 2009-2010.
S. Baarir
L2, MIA, Sciences éco-gestion et Langues.
INITIATION AUX BASES DE DONNÉES.
Contrôle Continue.
Samedi 28 Novembre. De 10h à 12h.
Exercice 1 (15 points).
Soient les schémas et tables suivantes, appartenant à une base de données représentant les achats
des clients d’une entreprise informatique :
 Client (IDC, Nom, Prénom, Ville, AnnéeN)
 Produit (IDP, Description, Prix)
 Achat (IDA, RefC, RefP, Quantité, Date)
IDC
Nom
Prénom
Ville
AnnéeN
1
Dupont
Jean
Paris
1963
2
Marc
Nicole
Nanterre
1979
3
Doe
John
Créteil
1970
4
Dubois
Anne
Paris
1983
IDP
Description
Prix
1
LCD
500
2
DVD-R
5,5
3
Clé USB
50,25
IDA
RefC
RefP
Quantité
Date
C1
2
1
1
10/12/2005
C2
3
2
20
11/11/2005
C3
2
3
2
20/05/2005
C4
2
2
10
05/12/2005
C5
4
1
1
30/02/2005
Client
Produit
Achat
Très important :
 Il est interdit de sortir de l’amphi avant la fin de la première heure, et toute
sortie est définitive.
 Seul le cours annoté est autorisé. Il ne peut être échangé.
 Le barème est indicatif est peut être modifié à la correction.

Page 2 sur 2
Questions.
1. Sachant que les champs IDC et IDP sont auto-incrémentés à chaque insertion, donnez le schéma
de la BD en précisant le type de données de chaque attribut (champ), les clés primaires et les clés
externes de chaque table.
Solution (4pts) :
 Client (IDC : NuméroAuto, Nom : text , Prénom : text, Ville :text, AnnéeN,int)
 Produit (IDP : NuméroAuto , Description : text, Prix :numeric)
 Achat (IDA : text, RefC :int , RefP :int , Quantité : int, Date :date)
Remarque : Si l’étudiant n’utilise pas ce format de réponse mais qu’il donne toutes les
informations nécessaires, c’est correcte.
2. Ecrivez les requêtes SQL dont les résultats sont :
a) Les noms et les prénoms des clients nés après 1971.
b) Les noms des clients de Paris qui ont acheté des produits.
c) Les produits qui coûtent plus de 100 €, achetés par Nicole Marc en décembre 2005.
d) Le coût total des achats de chaque client, triés par ordre décroissant.
e) Le nombre de clients qui n’ont rien acheté.
Solution (1pt, 1pt, 2pts, 2pts, 1pt):
a) Select nom, prénom from client where AnnéeN > 1971
b) Select nom from client inner join Achat on client.IDC = Achat.RefC where ville =”paris”
Ou
Select nom from client where IDC in (select RefC from Achat) and ville =”paris”
c) Select Description from (produit inner join achat on produit.IDP = achat.refP) inner join Client
on achat.RefC = Client.IDC where nom = “Marc “ and prénom =”Nicole” and prix > 100 and
year(Date) = 2005 and month(Date)=12
d) Select nom, prénom, sum(prix* quantité) as sommePrix from (produit inner join achat on
produit.IDP = achat.refP) inner join Client on achat.RefC = Client.IDC group by IDC, nom,
prénom order by sum(prix*quantité) desc

Page 3 sur 2
e) Select count(IDC) as NBClient from client where IDC not in (select RefC from Achat)
3. Ecrivez les requêtes SQL de création des différentes tables.
(PS : faire attention aux contraintes d’intégrité référentielle).
Solution (3pts) :
create table Client(
IDC autoincrement NOT NULL,
Nom text,
Prénom text,
Ville text,
AnnéeN int,
constraint C primary key (IDC)
)
create table Produit(
IDP autoincrement NOT NULL,
Description text(30),
Prix numeric,
constraint P primary key (IDP)
)
create table Achat(
IDA text NOT NULL,
RefC int,
RefP int,
Quantité int,
Date date,
constraint A primary key (IDA),
constraint A1 foreign key (RefC) references Client(IDC),
constraint A2 foreign key (RefP) references Produit(IDP)
)
4. Supposons l’existence d’une deuxième table Client-bis, dont le schéma est Client-bis
(CodeClient : Autoincrement, Nom : Text, Adresse : Text).
1. Ecrivez la requête SQL qui permet de transférer les enregistrements de la table Client-bis
vers Client.
Solution (1pt) : Insert into Client (Nom,Ville) select Nom, Adresse from client-bis
Indication pour la question 2 : ACCESS dispose de trois fonctions pour gérer les champs de type
Date :
 La fonction YEAR, qui prend en paramètre une date et retourne l’année correspondant à
cette date. Par exemple, YEAR(#01/02/2000#) rend 2000.
 La fonction MONTH, qui prend en paramètre une date et retourne le mois correspondant à
cette date. Par exemple, MONTH(#01/02/2000#) rend 02.

Page 4 sur 2
 La fonction DAY, qui prend en paramètre une date et retourne le jour correspondant à cette
date. Par exemple, DAY(#01/02/2000#) rend 01.
Exercice 2 (5 points).
Soient les schémas de relations (tables), constituant la base de données représentant les conventions
de stages des étudiants :
 ETUDIANT (N°Etudiant, Nom, Prénom, DateDeNaissance, Spécialité : Texte, AnnéeEtude).
 PROFESSEUR (N°Professeur, Nom, Prénom, Spécialité : Texte).
 ENTREPRISE (N°SIRET, NomEntreprise, SecteurActivité : Texte, Effectif, Adresse).
Un étudiant peut effectuer plusieurs stages durant son cursus universitaires. Un stage est une
association entre un étudiant, un professeur et une entreprise. Nous remarquons que dans son état
actuel, cette base de données ne représente pas cette association !
Questions.
1. Complétez la BD de manière à représenter l’association « stage ».
2. Donnez le schéma de la BD en précisant le type de données le plus approprié pour chaque
attribut (non précisé), les clés primaires et les clés externes.
Solution (2pts, 1pt, 1pt,1pt) :
 Stage (RefEtudiant : int , RefProf : int, RefSiret : int)
 Clefs externes : RefEtudiant (table étudiant) , RefProf (table professeur), RefSiret (table
entreprise).
 Clef primaire : (RefEtudiant , RefProf, RefSiret)
 ETUDIANT (N°Etudiant : int , Nom :text , Prénom :text , DateDeNaissance :date, Spécialité :
Text, AnnéeEtude : int)
 Clef primaire : N°Etudiant
 PROFESSEUR (N°Professeur : int, Nom : text , Prénom :text , Spécialité : Text).
 Clef primaire : N°Professeur
 ENTREPRISE (N°SIRET : int, NomEntreprise :text , SecteurActivité : Text, Effectif : int,
Adresse :text ).
 Clef primaire : N°SIRET
Remarque : Normalement, il faut rajouter à la table stage une (voire des) information(s) (ex :
annéeStage), permettant qu’un étudiant ait plusieurs stages avec le même professeur et la même
entreprise sur plusieurs périodes….mais, on se restreint à cette solution primitive.