\thumb

\begin{qcm}{Vérification de connaissances}{5} % titre , temps
On dispose d'une base de données comportant les tables suivantes:

\Par

\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
clients\\
\qquad (idClient,designation,adresse,cp,ville,email,tel)\\
produits\\
\qquad (idProduit,designation,prix,idEntrepot)\\
commandes\\
\qquad (idCommande,date,idProduit,idClient,quantite)\\
entrepots\\
\qquad (idEntrepot,designation,adresse,cp,ville,superficie)
\end{tabularx}

\Par

Le nom des champs (entre les parenthèses) est choisi de façon implicite pour qu'il n'y ait pas d'ambiguïté. 

\begin{questions}
% Q1
\item La requête :
\begin{listing}[language=SQL]{}{07-ex01.txt}\end{listing}

\Par

\begin{tabularx}{\linewidth}{@{}l@{\;}X@{}}
\choix & affiche le nom de tous les clients par ordre alphabétique de leur ville;\\[8pt]
\choix & affiche le nom de tous les clients selon un ordre alphabétique inverse de leur ville;\\[16pt]
\choix & affiche tous les champs de la table \og clients \fg{}  par ordre alphabétique inverse de leur nom;\\[16pt]
\choix & affiche tous les champs de la table \og clients \fg{}  par ordre alphabétique inverse du nom de leur ville.
\end{tabularx}


% Q2

\item \resetchoix La requête:
\begin{listing}[language=SQL]{}{07-ex02.txt}\end{listing}


\Par

\begin{tabularx}{\linewidth}{@{}l@{\;}X@{}}
\choix & affiche tous les champs des tables \og clients \fg{} et \og entrepots \fg{} qui ont la même ville;\\[16pt]
\choix & affiche tous les champs de la table \og clients \fg{} où le contenu du champ \og ville \fg{} est identique à celui d'au moins un champ de la table\linebreak \og entrepots \fg;\\[24pt]
\choix & affiche tous les champs de la table \og entrepots \fg{} où le contenu du champ \og ville \fg{} est identique à celui d'au moins un champ de la table \og clients \fg;\\[24pt]
\choix & affiche les contenus du champ \og ville \fg{} communs aux tables\linebreak \og entrepots \fg{}  et \og clients \fg.
\end{tabularx}

\newpage

%% Q3

\item\resetchoix La requête:
\begin{listing}[language=SQL]{}{07-ex03.txt}\end{listing}
\Par
est équivalente à:
\Par
\begin{tabularx}{\linewidth}{@{}l@{\;}>{\ttfamily}X@{}}
\choix & SELECT * FROM clients\\
       & WHERE clients.ville = entrepots.ville\\
\choix & SELECT * FROM entrepots\\
       & WHERE clients.ville = entrepots.ville\\
\choix & SELECT * FROM clients,entrepots\\
       & WHERE clients.ville = entrepots.ville\\
\choix & SELECT ville FROM clients,entrepots\\
       & WHERE clients.ville = entrepots.ville
\end{tabularx}

% Q4
%\item\resetchoix La requête:
%\begin{listing}[language=SQL]{}{07-ex04.txt}\end{listing}
%\Par
%\begin{tabularx}{\linewidth}{@{}l@{\;}X@{}}
%\choix & affiche uniquement les clients dont la ville coïncide avec celle d'un entrepôt;\\
%\choix & affiche tous les clients;\\
%\choix & affiche tous les entrepôts;\\
%\choix & n'affiche rien.
%\end{tabularx}

% Q5
\item\resetchoix La requête:\enlargethispage*{4em}
\begin{listing}[language=SQL]{}{07-ex05.txt}\end{listing}
\Par
\begin{tabularx}{\linewidth}{@{}l@{\;}X@{}}
\choix &affiche le total des commandes effectuées après le 2019-09-02;\\
\choix &affiche le total de chaque commande effectuée après le 2019-09-02;\\
\choix & affiche toutes les entrées de la table \og commandes \fg{} où la date est supérieure à 2019-09-02 en effectuant la somme des totaux petit à petit;\\
\choix & n'affiche rien.
\end{tabularx}
\end{questions}
\end{qcm}

\begin{corrige}
\begin{questions}
\item Réponse \choix[d]. En effet, \og SELECT * FROM clients \fg{} signifie que l'on veut voir tous les champs de la table \og clients \fg{} et \og ORDER BY ville DESC \fg{} signifie que l'on souhaite effectuer un tri sur le contenu de \og ville \fg{} mais dans un ordre alphabétique inverse (DESC).

\item Réponse \choix[a]. \og clients INNER JOIN entrepots \fg{} désigne l'intersection des deux tables et \og ON clients.ville = entrepots.ville \fg{} désigne le critère à prendre en compte pour trouver l'intersection (ici, le nom des villes). \og SELECT * \fg{} signifie que l'on souhaite afficher tout donc la requête affiche tous les champs des deux tables où le nom des villes coïncide.

\item Réponse \choix[c]. La requête \texttt{SELECT * FROM clients,entrepots WHERE clients.ville = entrepots.ville} est explicite : on sélectionne tous les champs des deux tables \og clients \fg{} et \og entrepots \fg{}  où le contenu des champs \og ville \fg{} est commun. Elle fait donc exactement la même chose que la requête de la question précédente.

%\item Réponse \choix[b]. La requête \texttt{SELECT * FROM clients LEFT OUTER JOIN entrepots ON (clients.ville = entrepots.ville)} prend en\linebreak compte la table de gauche (LEFT), donc \og clients \fg, et affiche toutes ses entrées en commençant par celles où les noms de villes coïncident. Elle affiche ensuite les entrées où le contenu du champ \og ville \fg ne coïncide avec aucun contenu du champ \og ville \fg{} de la table \og entrepots \fg.

\item Réponse \choix[a]. La requête \texttt{SELECT SUM(commandes.total)} renvoie un tableau d'une ligne et d'une colonne dont la valeur est la somme des entrées de la colonne \og total \fg. Il suffit ensuite de regarder la condition (ici, \og WHERE date > '2019-09-02' \fg{} ainsi que l'alias (ici, \og AS somme \fg, qui signifie que le nom de la somme sera accessible via l'appellation de \texttt{somme}).
\end{questions}
\end{corrige}

%%%%%%%%%%%%%%%%%%%%%

\begin{vf}{Vérification de connaissances}{10} % titre , temps
Les affirmations suivantes sont-elles vraies ou fausses ? Justifier la réponse.

\Par

On se place dans une base de données nommée \og bddPerso \fg.

\begin{questions}
\item Une ligne d'une table de \texttt{bddPerso} est appelée une entrée.

\item Une table de \texttt{bddPerso} est définie par la structure relationnelle\linebreak suivante :

\Par

\qquad\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
clients (idClient , nom , prenom)
\end{tabularx}

\Par

Alors \og nom \fg{} est un attribut de la table \og clients \fg.

\item Dans la table \og clients \fg{} de la question précédente, on peut prendre \og nom \fg{} comme clé primaire.

\item On définit une autre table de la manière suivante :

\Par

\qquad\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
connexions (idCommande , idClient , date)
\end{tabularx}


\Par

où \og idClient \fg{} fait référence à la clé du même nom de la table \og clients \fg.

\Par

\og idClient \fg{} est alors une clé étrangère de la table \og connexions \fg.
\end{questions}
\end{vf}

\begin{corrige}
\begin{questions}
\item \textit{Faux}. Dans une base de données, une ligne d'une table est appelée un \textit{enregistrement}.

\item \textit{Vrai}. Les étiquettes des colonnes d'une table sont appelées les attributs de la table.

\item \textit{Faux}. Une clé primaire a pour but de repérer de manière unique un enregistrement. On ne peut donc pas prendre un attribut (ici \og nom \fg) qui peut prendre plusieurs fois la même valeur. Il serait ici préférable de prendre \og idClient \fg{} comme clé primaire, si cet attribut a été créé comme étant un entier auto-incrémenté (par exemple).

\item \textit{Vrai}. En effet, toute clé primaire d'une table, recopiée en tant qu'attribut d'une autre table, est appelé \textit{clé étrangère} de cette dernière.
\end{questions}
\newpage
\end{corrige}


%%%%%%%%%%%%%%%%%%%%

\begin{exo}{Festival de musique}{Lycée André Theuriet, Civray}{1}{15} 
On dispose d'une base de données d'un festival de musique, où il y a\linebreak plusieurs représentations. Un ou plusieurs musiciens peuvent participer à une représentation, mais un musicien ne peut participer qu'à une seule\linebreak représentation.

La structure de la base de données est la suivante:

\Par

\qquad
\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
Representation (idRep , titreRep , lieu)\\
Musicien (idMus , nom , idRep) \\
Programmer (Date , idRep, tarif)
\end{tabularx}

\Par

Écrire la requête SQL permettant d'afficher:

\begin{questions}
\item La liste des titres des représentations. 
\item La liste des titres des représentations ayant lieu sur le lieu nommé \linebreak \og Dionysos \fg. 
\item La liste des noms des musiciens et les titres des représentations\linebreak auxquelles ils participent. 
\item La liste des titres des représentations, les lieux et les tarifs d'une date précisée. 
\item Le nombre des musiciens qui participent à la représentation numéro 7.
\item Les représentations et leurs dates dont le tarif ne dépasse pas 30 \euro. 
\end{questions}
\end{exo}

\begin{corrige}
\begin{questions}
\item La liste des titres des représentations est donnée par la requête :
\begin{listing}[language=SQL]{}{07-ex06.txt}\end{listing}

\item La liste des titres des représentations ayant lieu sur le lieu nommé\linebreak \og Dionysos \fg est donnée par la requête:
\begin{listing}[language=SQL]{}{07-ex07.txt}\end{listing}


\item La liste des noms des musiciens et les titres des représentations\linebreak auxquelles ils participent est donnée par la requête :
\begin{listing}[language=SQL]{}{07-ex08.txt}\end{listing}

\item La liste des titres des représentations, les lieux et les tarifs d'une date précisée (par exemple le 24/12/2020) est donnée par la requête:\enlargethispage*{3em}

\begin{listing}[language=SQL]{}{07-ex09.txt}\end{listing}

\newpage

\item Le nombre des musiciens qui participent à la représentation numéro 7 est donné par la requête:
\begin{listing}[language=SQL]{}{07-ex10.txt}\end{listing}

\item Les représentations et leurs dates dont le tarif ne dépasse pas 30 \euro{} sont données par la requête:
\begin{listing}[language=SQL]{}{07-ex11.txt}\end{listing}
\end{questions}
\end{corrige}


%%%%%%%%%%%%%%%%%%%

\begin{exo}{Les employés du département}{Lycée Henri Alain-Fournier, Bourges}{1}{15} 
On dispose d'une base de données dont la structure est la suivante :

\Par

\qquad
\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
Departements (DNO, DNOM, DIR, VILLE) 

Employes (ENO, ENOM, PROF, DATEEMB, SAL, COMM, DNO) 
\end{tabularx}

\Par

Donner les requêtes SQL qui permettent d'obtenir:

\begin{questions}
\item la liste des employés ayant une commission (attribut \og COMM \fg déclaré comme booléen);

\item les noms, emplois et salaires des employés obtenus en classant les emplois dans l'ordre alphabétique, et pour chaque emploi, en classant les salaires du plus grands au plus petit;

\item le salaire moyen des employés.

\item le salaire moyen dans le département nommé \og Production \fg\\(\texttt{DNOM = 'Production'}).
%\Par
%\textit{Pour les questions suivantes, on pourra faire appel à la fonction\linebreak d'agrégation GROUP BY (voir remarque du cours à la page \pageref{crs:groupby}).}
%\item Les numéros de département et leur salaire maximum.
%\item Les différentes professions et leur salaire moyen.
%\item Le salaire moyen par profession le plus bas.
\end{questions}
\end{exo}

\begin{corrige}
\enlargethispage*{\baselineskip}
\begin{questions}
\item La liste des employés ayant une commission :
\begin{listing}[language=SQL]{}{07-ex12.txt}\end{listing}

\item

\begin{methode}
Pour obtenir une liste de résultats selon un ordre \textit{croissant} ou \textit{décroissant}, on utilise une requête avec le mot-clé \texttt{ORDER BY ... ASC} (pour un ordre croissant) et \texttt{ORDER BY ... DESC} (pour un ordre\linebreak décroissant).
\end{methode}

\newpage

Les noms, emplois et salaires des employés par emploi croissant, et pour chaque emploi, par salaire décroissant :
\begin{listing}[language=SQL]{}{07-ex13.txt}\end{listing}

\item Le salaire moyen des employés:
\begin{listing}[language=SQL]{}{07-ex14.txt}\end{listing}


\item Le salaire moyen du département \og Production \fg:
\begin{listing}[language=SQL]{}{07-ex15.txt}\end{listing}
%\item Les numéros de département et leur salaire maximum:
%
%\quad\begin{tabularx}{\linewidth}{|>{\ttfamily}X@{}}
%SELECT DNO , MAX ( SAL ) FROM Employes GROUP BY DNO
%\end{tabularx}

%\item Les différentes professions et leur salaire moyen:
%
%\quad\begin{tabularx}{\linewidth}{|>{\ttfamily}X@{}}
%SELECT PROF , MAX ( SAL ) FROM Employes GROUP BY PROF
%\end{tabularx}

%\item  Le salaire moyen par profession le plus bas:
%\quad\begin{tabularx}{\linewidth}{|>{\ttfamily}X@{}}
%SELECT AVG ( SAL ) as moy FROM Employes GROUP BY PROF\\
%ORDER BY moy ASC LIMIT 1
%\end{tabularx}
\end{questions}
\end{corrige}


%%%%%%%%%%%%%%%%%%

\begin{exo}{Notes annuelles des étudiants}{Lycée Sainte-Clotilde, Strasbourg}{2}{20} 
On considère le modèle relationnel suivant concernant la gestion des notes annuelles d'une promotion d'étudiants :

\Par

\qquad
\begin{tabularx}{\linewidth}{|>{\ttfamily\arraybackslash}X@{}}
ETUDIANT\\
\qquad (\underline{NumEtudiant}, Nom, Prenom) \\
MATIERE\\
\qquad (\underline{CodeMat}, LibelleMat, CoeffMat) \\
EVALUER\\
\qquad (\underline{NumEval}, \#NumEtudiant, \#CodeMat, Date, Note)
\end{tabularx}


\Par

Les clés étrangères sont précédées d'un \og \# \fg et les clés primaires sont\linebreak soulignées.

Exprimez en SQL les requêtes suivantes.

\begin{questions}
\item Quel est le nombre total d'étudiants ? 
\item Quelles sont, parmi l'ensemble des notes, la note la plus haute et la note la plus basse ? 
\item Quelles sont les moyennes de chaque étudiant dans chacune des\linebreak matières ? 
\item Quelles sont les moyennes de la classe par matière ? 
\item Quelle est la moyenne générale de chaque étudiant ? 
\item Quelle est la moyenne générale de la promotion ? 
\end{questions}

\textit{Aide :} on pourra utiliser \og GROUP BY \fg{} pour plusieurs questions, qui\linebreak regroupe les entrées. Par exemple, si l'on considère la table suivante :
\Par
\begin{center}
\begin{tabular}{|*3{Zc|}}
\tabulartitle\hline
\multicolumn{3}{|Zc|}{clients}\\
\tabulartitle\hline
id & nom & montant\\
\hline
1 & Marisol Pleureur & 125.65\\
\hline
2 & Jacques Umul & 45.7\\
\hline
3 & Marisol Pleureur & 100.5\\
\hline
4 & Jacques Umul & 78.6\\
\hline
\end{tabular}
\end{center}
\Par
alors, la requête:
\begin{listing}[language=SQL]{}{07-ex19.txt}\end{listing}
\Par
calculera le total des valeurs des attributs \og montant \fg{} en les regroupant par nom, et donnera la table:
\Par
\begin{center}
\begin{tabular}{|*2{Zc|}}
\tabulartitle\hline
nom & total\\
\hline
Jacques Umul & 124.3\\
\hline
Marisol Pleureur & 226.15\\
\hline
\end{tabular}
\end{center}
\end{exo}

\begin{corrige}
\enlargethispage*{4em}
\begin{questions}
\item Le nombre total d'étudiants est donné par la requête:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex20.txt}\end{listing}

\newpage

\item Parmi l'ensemble des notes, la note la plus haute et la note la plus basse sont données par la requête:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex21.txt}\end{listing}

\item Les moyennes de chaque étudiant dans chacune des matières sont données par la requête:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex22.txt}\end{listing}

\item Les moyennes de la classe par matière peuvent être données par la requête :\enlargethispage*{5em}
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex23.txt}\end{listing}
\Par
\begin{center}
\textit{(suite du script page suivante)}
\end{center}

\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex24.txt}\end{listing}


\item %Pour obtenir la moyenne générale de chaque étudiant, on va se servir d'une \textit{vue}, celle obtenue avec la requête de la question 3:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex25.txt}\end{listing}


\item %Pour déterminer la moyenne générale de la promotion, on peut utiliser la vue basée sur la réponse à la question précédente:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex27.txt}\end{listing}

\end{questions}
\end{corrige}


%%%%%%%%%%%%%%%%%%


\begin{exo}{Un cas pratique: site web marchand}{Lycée Jean-Joseph Fourier, Auxerre}{3}{30} 
Un webmaster souhaite créer une base de données nommée \og venteAdonf \fg{} pour un site internet marchand \texttt{vente-a-donf.com} dont la clientèle est\linebreak uniquement française (pour simplifier les formats des enregistrements).

Dans cette base de données, il devra y avoir les quatre tables suivantes :

\begin{itemize}
\item \texttt{users (idUser,nom,prenom,email,rue,cp,ville,tel)}

où \og cp \fg{} désigne le code postal de sa ville de résidence, les autres attributs étant explicites;

\item \texttt{modeles (idModele,nomModele)}

\item \texttt{articles (idArticle,nom,descriptif,idModele,prix)}

où \og idModele \fg{} est une clé étrangère relative à la colonne \texttt{idModele} de la table \texttt{modeles}.


\item \texttt{panier (idPanier,idUser,idArticle,idModele,quantite,}\linebreak\texttt{total)}

Ici, \og total \fg{} représente le résultat de \og quantite * prix de l'article \fg.
\end{itemize}

On décide de mettre en clés primaires les premières colonnes de chaque table.

\begin{questions}
\item \'Ecrire un fichier SQL nommé \og venteAdonf-create.sql \fg{}  permettant de créer ces quatre tables.

\textit{On rappelle qu'un tel fichier est composé de quatre requêtes séparées par un point-virgule.}

\newpage

\item Voici les premiers articles mis en vente :

\begin{center}
\begin{tabular}{|Z{m{2cm}}|Z{m{3cm}}|Z{m{2cm}}|Zc|}
\tabulartitle\hline
\bfseries Nom & \bfseries Descriptif & \bfseries Modèle & \bfseries Tarif\\
\hline
Mug & 
Magnifique mug\newline personnalisable avec votre photo. &
Unique &
7,99 \euro\\
\hline
T-shirt\newline \og I Kiffe\newline Beef \fg{} &
T-shirt unique en son genre. &
S / M / L /\newline XL / XXL &
24,99 \euro\\
\hline
\end{tabular}
\end{center}

\Par

\'Ecrire un fichier SQL nommé \og venteAdonf-insertArt.sql \fg{}  permettant d'insérer ces informations dans la base de données.

\item Le jour de la mise en ligne du site, deux personnes ont passé commande :

\begin{center}
\begin{tabular}{|L|*2{Zc|}}
\hline
\bfseries Nom & Dupont & Aimaun \\\hline
\bfseries Prénom & Jean & Anne \\\hline
\bfseries Email & jdupont@free.fr & amz@free.fr \\\hline
\bfseries Rue & 3 rue des tulipes & 1 rue du glas \\\hline
\bfseries Cp & 75000 & 33000 \\\hline
\bfseries Ville & Paris & Bordeaux \\\hline
\bfseries Tel & +33601020304 & +33799989796 \\\hline
\end{tabular}
\end{center}

\Par

\'Ecrire un fichier SQL nommé \og venteAdonf-insertUsers.sql \fg{}  permettant d'insérer ces informations dans la base de données.


\item Jean Dupont a commandé 2 mugs et 3 tee-shirts (tailles S, M et L). Anne Aimaun a, quant à elle, commandé 1 mug et 1 tee-shirt (taille M).

\begin{questions}
\item \'Ecrire un fichier SQL nommé \og venteAdonf-insertPanier.sql \fg{}\linebreak  permettant d'insérer ces informations dans la base de données.

\item \'Ecrire une requête permettant d'afficher le montant total du panier de Jean Dupont.

\item Si la table \texttt{panier} n'avait pas de colonne \og total \fg, quelle requête permettrait de renvoyer le résultat de la question précédente ?
\end{questions}
\end{questions}
\end{exo}

\begin{corrige}
\begin{questions}
\item Un fichier SQL permettant de créer les quatre tables est le suivant :

\qrset{height=1.1cm,version=5}\rput(-1,-0.7){
\psframebox{\qrcode{https://idl-live.prepamath.fr/ILTNSI/07-ex36.sql}}}
\vspace*{-10pt}
\begin{listing}[language=SQL]{}{07-ex36.sql}\end{listing}

\newpage

La taille des colonnes est ici arbitraire : nous les avons choisi en essayant d'être cohérent. 

Pour ce qui est de la colonne \og tel \fg, nous avons fait le choix de la\linebreak déclarer comme chaîne de caractères (pour que le format des numéros soient de la forme \texttt{+336XXXXXXXX} ou \texttt{+337XXXXXXXX}).

Pour les clés primaires, nous avons fait le choix du format \og BIGINT \fg{} car nous sommes optimistes... et souhaitons beaucoup d'entrées pour cette base de données, qui signifierait un grand succès du site.

\item Pour cette question, il faut anticiper : il faut avant tout enregistrer les\linebreak différents modèles cités (Unique, S, M, L, XL et XXL). Ce n'est\linebreak qu'ensuite que l'on pourra enregistrer les articles. On a alors:
\Par\enlargethispage*{3\baselineskip}

\qrset{height=1.1cm,version=5}\rput(-1,-0.7){
\psframebox{\qrcode{https://idl-live.prepamath.fr/ILTNSI/07-ex39.sql}}}
\vspace*{-10pt}
\begin{listing}[language=SQL]{}{07-ex37.txt}\end{listing}
\Par
\begin{center}
\textit{(suite du fichier page suivante)}
\end{center}
\newpage
\begin{listing}[language=SQL]{}{07-ex38.txt}\end{listing}


\qrset{height=1.1cm,version=5}\rput(-1,-1.7){
\psframebox{\qrcode{https://idl-live.prepamath.fr/ILTNSI/07-ex40.sql}}}
\item 
\begin{listing}[language=SQL]{Insertion des utilisateurs}{07-ex40.sql}
\end{listing}

\qrset{height=1.1cm,version=5}\rput(-1,-1.7){
\psframebox{\qrcode{https://idl-live.prepamath.fr/ILTNSI/07-ex41.sql}}}

\item 
\begin{questions}
\item 
\begin{listing}[language=SQL]{Insertion des commandes dans le panier}{07-ex41-1.txt}\end{listing}
\begin{center}
\textit{(suite page suivante)}
\end{center}
\newpage
\begin{listing}[language=SQL]{}{07-ex41-2.txt}\end{listing}

\Par

\item La requête permettant d'afficher le total du panier de Jean Dupont est la suivante:
\begin{listing}[language=SQL]{}{07-ex42.txt}\end{listing}
\Par

\item 
\qrset{height=1.1cm,version=5}\rput(-1,-0.8){
\psframebox{\qrcode{https://idl-live.prepamath.fr/ILTNSI/07-ex44.sql}}}
\vspace*{-10pt}

\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-ex44.sql}\end{listing}
\end{questions}
\end{questions}
\end{corrige}

\sectionnn{Objectif bac}

%%% NEW 2025

\begin{exo}{Voyages dans l'espace}{Lycée Richelieu, Versailles}{3}{45}
\enlargethispage*{5em}
On considère une gestion simplifiée des voyages dans l'espace. La base de données utilisée est constituée de quatre relations nommées \texttt{Astronaute}, \texttt{Fusee}, \texttt{Equipe} et \texttt{Vol}. Voici le contenu des tables \texttt{Astronaute}, \texttt{Fusee}, \texttt{Equipe} et \texttt{Vol}.
\newpage
\mbox{Les clés primaires sont soulignées et les clefs étrangères sont précédées d'un \#:}
\begin{center}
\begin{tabular}{|c|c|c|c|c|}
\hline\tabulartitle
\multicolumn{5}{|c|}{\ttfamily Astronaute} \\
\hline\tabulartitle
\ttfamily id\_astronaute & \ttfamily nom & \ttfamily prenom & \ttfamily nationalite & \ttfamily nb\_vols \\
\hline
1 & 'PESQUET' & 'Thomas' & 'français' & 2 \\
2 & 'AMSTRONG' & 'Neil' & 'américain' & 8 \\
3 & 'MAURER' & 'Mathias' & 'allemand' & 1 \\
4 & 'MCARTHUR' & 'Megan' & 'américain' & 5 \\
\hline
\end{tabular}
\end{center}

\begin{center}
\begin{tabular}{|c|c|c|c|}
\hline\tabulartitle
\multicolumn{4}{|c|}{\ttfamily Fusee } \\
\hline\tabulartitle
\ttfamily id\_fusee & \ttfamily modele & \ttfamily constructeur & \ttfamily nb\_places \\
\hline
1 & 'Falcon 9' & 'SpaceX' & 6 \\
2 & 'Starship' & 'SpaceX' & 100 \\
3 & 'Soyouz' & 'TsSKB Progress' & 2 \\
4 & 'SLS' & 'Boeing' & 6 \\
\hline
\end{tabular}
\end{center}

% equipe

\begin{minipage}{0.5\linewidth}
\begin{center}
\begin{tabular}{|c|c|}
\hline\tabulartitle
\multicolumn{2}{|c|}{\ttfamily Equipe } \\
\hline \tabulartitle
\ttfamily id\_vol & \ttfamily \#id\_astronaute \\
\hline
1 & 1  \\
1 & 2  \\
1 & 3  \\
2 & 1  \\
2 & 3  \\
3 & 1  \\
3 & 2 \\
3 & 4  \\
4 & 4  \\
4 & 4  \\
\hline
\end{tabular}
\end{center}
\end{minipage}
\begin{minipage}{0.5\linewidth}
\begin{center}
\begin{tabular}{|c|c|c|}
\hline\tabulartitle
\multicolumn{3}{|c|}{\ttfamily Vol }\\
\hline\tabulartitle
\ttfamily id\_vol & \ttfamily \#id\_fusee & \ttfamily Date \\
\hline
 1 & 1 & '12/09/2022' \\
 2 & 4 & '25/10/2022' \\
 3 & 3 & '18/11/2022' \\
 4 & 2 & '23/12/2022' \\
\hline
\end{tabular}
\end{center}
\end{minipage}

On pourra utiliser les mots clés suivants : COUNT, FROM, INSERT, INTO, JOIN, ON, ORDER BY, SELECT, VALUES, WHERE.

\begin{itemize}
    \item Le mot clé COUNT permet de récupérer le nombre d'enregistrements issu de la requête.
    
    Par exemple, la requête suivante renvoie la valeur 4.
    \begin{verbatim}
    SELECT COUNT (*) FROM Astronaute;
    \end{verbatim}
    \item Le mot clé ORDER BY permet de trier les éléments par ordre alphabétique.
    
    Par exemple, la requête suivante :
    \begin{verbatim}
    SELECT modele FROM Fusee ORDER BY modele;
    \end{verbatim}
    renvoie la table:
    \begin{center}
    \begin{tabular}{|c|}
    \hline
    'Falcon 9'\\\hline
'SLS'\\\hline
'Soyouz'\\\hline
'Starship'\\\hline
    \end{tabular}
    \end{center}
\end{itemize}

\begin{questions}
    \item On s'intéresse ici à la notion de clés primaire et étrangère.
    \begin{questions}
        \item Donner la définition d'une clé primaire.
        
        \item Dans la table \texttt{Astronaute}, la clé primaire est \texttt{id\_astronaute}. Expliquer pourquoi cette requête SQL renvoie une erreur :
        \begin{verbatim}
        INSERT INTO Astronaute
        VALUES (3, 'HAIGNERE', 'Claudie', 'français', 3);
        \end{verbatim}
        
        \item Le schéma relationnel de la table \texttt{Astronaute} est :
        
        \hspace{14mm}
        \begin{minipage}{\dimexpr\linewidth-14mm}
        \ttfamily
        Astronaute (\\
        	\hspace*{10mm}\underline{id\_astronaute} : INT, \\
        	\hspace*{10mm}nom : TEXT, \\
        	\hspace*{10mm}prenom : TEXT, \\
        	\hspace*{10mm}nationalite : TEXT, \\
        	\hspace*{10mm}nb\_vols : INT)
        \end{minipage}
        
        Écrire le schéma relationnel de la table \texttt{Fusee} en précisant le domaine de chaque attribut.
    \end{questions}

    \item On s'intéresse ici à la récupération d'informations issues de la base de données.
    \begin{questions}
        \item Écrire le résultat que la requête suivante renvoie :
        \begin{verbatim}
        SELECT COUNT (*)
        FROM Fusee
        WHERE constructeur = 'SpaceX';
        \end{verbatim}
        \item Écrire une requête SQL qui renvoie le modèle et le constructeur des fusées ayant au moins quatre places.
        \item Écrire une requête SQL qui renvoie les noms et prénoms des astronautes dans l'ordre alphabétique du nom.
    \end{questions}

    \item
    \begin{questions}
        \item Recopier et compléter les requêtes SQL suivantes permettant d'ajouter un cinquième vol avec la fusée 'Soyouz' le 12/04/2023 avec l'équipage composé de PESQUET Thomas et MCARTHUR Megan. On ne s'intéresse pas ici à la mise à jour qui suivra.
        \begin{verbatim}
        INSERT INTO Vol VALUES(...);
        INSERT INTO Equipe VALUES(...);
        INSERT INTO ... VALUES (...);
        \end{verbatim}
        \item Écrire une requête SQL permettant d'obtenir le nom et le prénom des astronautes ayant décollé le '25/10/2022'.
    \end{questions}
\end{questions}

\end{exo}

\begin{corrige}
\begin{questions}
\item 
\begin{questions}
\item Une clé primaire est un attribut dont la valeur permet d'identifier de manière unique un t-uplet de la relation.

\item La valeur \og 3 \fg{} a déjà été utilisé pour l'attribut \texttt{id\_astronaute} de la table \texttt{Astronaute}. Nous allons donc avoir une erreur puisque \texttt{id\_astronaute} est la clé primaire de la table \texttt{Astronaute}.

\item Le schéma relationnel de la table \texttt{Fusee} est le suivant:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-voyages-espace-q1c.sql}\end{listing}
\end{questions}

\item
\begin{questions}
\item Le résultat de la requête est: \og 2 \fg. En effet, cette requête compte le nombre d'entrées de la table \texttt{Fusee} où l'attribut \texttt{constructeur} vaut \og SpaceX \fg, et il y a deux entrées concernées.

\item Une requête qui renvoie le modèle et le constructeur des fusées ayant au moins quatre places est:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-voyages-espace-q2b.sql}\end{listing}

\item Une requête qui renvoie les noms et prénoms des astronautes dans l'ordre alphabétique du nom est:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-voyages-espace-q2c.sql}\end{listing}

\end{questions}

\item
\begin{questions}
\item La requête complétée est:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-voyages-espace-q3a.sql}\end{listing}

\item Une requête SQL permettant d'obtenir le nom et le prénom des astronautes ayant décollé le 25/10/2022 est:
\begin{listing}[language=SQL,keywordstyle=*\scshape\color{DO}]{}{07-voyages-espace-q3b.sql}\end{listing}

\end{questions}
\end{questions}
\end{corrige}

%%% NEW 2025

\newpage

\begin{exo}{Collection de CD}{D’après Bac 2024, Asie 2, Jour 2}{3}{45}
\textit{Cet exercice porte sur les bases de données relationnelles, les requêtes SQL et la programmation en Python.}

\Par

L'énoncé de cet exercice utilise des mots-clés du langage SQL suivants :

{\ttfamily
SELECT, FROM, WHERE, JOIN... ON, UPDATE... SET,\\
INSERT INTO... VALUES..., COUNT, ORDER BY.
}

\Par

La clause ORDER BY suivie d'un attribut permet de trier les résultats par ordre croissant de l'attribut précisé. \texttt{SELECT COUNT (*)} renvoie le nombre de lignes d'une requête.

Amélie souhaite organiser sa collection de CD. Elle a commencé par enregistrer toutes les informations sur un fichier CSV mais elle trouve que la recherche d'informations est longue et fastidieuse. Elle repense à son cours sur les bases de données et elle se dit qu'elle doit pouvoir utiliser une base de données relationnelle pour organiser sa collection.

\subsectionnn{Partie A}

Dans cette partie on utilise une seule table.
Voici un extrait de la table \texttt{chanson}.

\begin{tabular}{|c|c|c|c|}
\tabulartitle\hline
\multicolumn{4}{|c|}{\ttfamily Chanson }\\
\hline
\tabulartitle
\ttfamily id & \ttfamily titre & \ttfamily album & \ttfamily groupe \\
\hline
1 & Sunburn & Showbiz & Muse \\
2 & Muscle Museum & Showbiz & Muse \\
3 & Showbiz & Showbiz & Muse \\
4 & New Born & Origin of Symmetry & Muse \\
5 & Sing for Absolution & Absolution & Muse \\
6 & Hysteria & Absolution & Muse \\
7 & Welcome too the Jungle & Appetite for Destruction & Guns N' Roses \\
8 & Muscle Museum & Hullabaloo & Muse \\
9 & Showbiz & Hullabaloo & Muse \\
\hline
\end{tabular}

\begin{questions}
    \item L'attribut titre peut-il être une clé primaire pour la table chanson ?\\ Justifier.

    \item Donner le résultat de la requête suivante :

\begin{verbatim}
SELECT titre, album
FROM Chanson
WHERE groupe = 'Guns N'Roses';
\end{verbatim}

    \item Écrire une requête SQL permettant d'obtenir tous les titres des chansons de l'album \texttt{Showbiz} dans l'ordre croissant.

    \item Écrire une requête SQL permettant d'ajouter la chanson dont le titre est Megalomania de l'album Hullabaloo du groupe Muse.

    \item Amélie a remarqué une faute de frappe dans la chanson Welcome too the Jungle qui s'écrit normalement Welcome to the Jungle.
    Écrire une requête SQL permettant de corriger cette erreur.
\end{questions}

\subsectionnn{Partie B}

Dans cette partie on utilise trois tables.
Voici des extraits des trois tables \texttt{Chanson}, \texttt{Album} et \texttt{Groupe}.

\begin{center}
\begin{tabular}{|c|c|c|}
\tabulartitle\hline
\multicolumn{3}{|c|}{\ttfamily Chanson }\\
\hline
\tabulartitle
\ttfamily id & \ttfamily titre & \ttfamily id\_album \\
\hline
1 & Sunburn & 1 \\
2 & Muscle Museum & 1 \\
3 & Showbiz & 1 \\
4 & New Born & 2 \\
5 & Sing for Absolution & 4 \\
6 & Hysteria & 4 \\
7 & Welcome to the Jungle & 5 \\
8 & Muscle Museum & 3 \\
9 & Showbiz & 3 \\
\hline
\end{tabular}


\Par

\begin{tabular}{|c|c|c|c|}
\tabulartitle\hline
\multicolumn{4}{|c|}{\ttfamily Album }\\
\hline
\tabulartitle
\ttfamily id & \ttfamily titre & \ttfamily année & \ttfamily id\_groupe \\
\hline
1 & Showbiz & 1999 & 1 \\
2 & Origin of Symmetry & 2001 & 1 \\
3 & Hullabaloo & 2002 & 1 \\
4 & Absolution & 2003 & 1 \\
5 & Appetite for Destruction & 1987 & 2 \\
\hline
\end{tabular}

\Par

\begin{tabular}{|c|c|}
\tabulartitle\hline
\multicolumn{2}{|c|}{\ttfamily Groupe }\\
\hline
\tabulartitle
\ttfamily id & \ttfamily nom \\
\hline
1 & Muse \\
2 & Guns N' Roses \\
\hline
\end{tabular}
\end{center}

\begin{questions}
    \item Expliquer l'intérêt d'utiliser trois tables \texttt{Chanson}, \texttt{Album} et \texttt{Groupe} au lieu de regrouper toutes les informations dans une seule table.

    \item Expliquer le rôle de l'attribut \texttt{id\_album} de la table chanson.

    \item Proposer alors un schéma relationnel pour cette version de la base de données. On pensera à bien spécifier les clés primaires en les soulignant et les clés étrangères en les faisant précéder par le symbole \#.

    \item Écrire une requête SQL permettant d'obtenir tous les noms des albums contenant la chanson Showbiz.

    \item Écrire une requête SQL permettant d'obtenir tous les titres avec le nom de l'album des chansons du groupe Muse.

    \item Décrire par une phrase ce qu'effectue la requête SQL suivante :

\begin{verbatim}
SELECT COUNT(*) AS tot
FROM Album AS a
JOIN Groupe AS g ON a.id_groupe = g.id
WHERE g.nom = 'Muse';
\end{verbatim}
\end{questions}

\subsectionnn{Partie C}

Dans cette partie, on utilise Python.
Amélie a remarqué que son professeur ne parle jamais d'ordre alphabétique mais d'ordre lexicographique lorsqu'il fait une requête avec ORDER BY.

Elle a compris qu'il s'agissait de l'ordre du dictionnaire mais elle se demande comment elle pourrait elle-même écrire une fonction ordre\_lex (mot1, mot2) de comparaison entre deux chaînes de caractères en utilisant l'ordre lexicographique. La fonction

ordre\_lex (mot1, mot2) prend en arguments deux chaines de caractères et renvoie un booléen. Une rapide recherche lui permet de trouver le résultat suivant :

Lorsque l'on compare deux chaînes de caractères suivant l'ordre lexicographique, on commence par comparer les deux premiers caractères de chacune des deux chaînes, puis en cas d'égalité on s'intéresse au second, et ainsi de suite. Le classement est donc le même que celui d'un dictionnaire. Si lors de ce procédé on dépasse la longueur d'une seule des deux chaînes, elle est considérée plus petite que l'autre. Lorsqu'on dépasse la longueur des deux chaînes au même moment, elles sont nécessairement égales

Amélie commence par écrire quelques assertions que sa fonction devra vérifier.

\begin{questions}
    \item Compléter les assertions suivantes :

\begin{verbatim}
1 assert ordre_lex("", "a") == True
2 assert ordre_lex("b", "a") == ...
3 assert ordre_lex("aaa", "aaba") == ...
\end{verbatim}

On suppose que les chaînes de caractères mot 1 et mot 2 ne sont composées que des lettres de l'alphabet, en minuscule, et la comparaison entre deux lettres peut se faire avec les opérateurs classiques $==$ et $<$.

Par exemple :

\begin{verbatim}
1 >>> "" < "a"
2 True
3 >>> "b" == "a"
4 False
\end{verbatim}

Enfin, le slice mot1 [1:] renvoie la chaîne de caractère de mot1 privée de son premier caractère.

Par exemple :

\begin{verbatim}
1 >>> mot1 = "abcde"
2 >>> mot2 = mot1[1:]
3 >>> mot2
4 "bcde"
\end{verbatim}

\item Recopier et compléter la fonction récursive ordre\_lex ci-dessous qui prend pour paramètre deux chaînes de caractères mot1 et mot2 et qui renvoie True si mot 1 précède mot 2 dans l'ordre lexicographique.

\begin{listing}{}{07-collectionCD-C2.py} \end{listing}

\item Proposer une version itérative de la fonction \texttt{ordre\_lex}.
\end{questions}

\end{exo}

\begin{corrige}
\subsectionnn{Partie A}

\begin{questions}
\item L’attribut \texttt{titre} ne peut pas être une clé primaire car il est possible qu’il y ait deux titres identiques.

\item La requête donne le résultat suivant :

\begin{center}
\begin{tabular}{|c|c|}
\hline
Welcome too the Jungle & Appetite for Destruction\\
\hline
\end{tabular}
\end{center}

\newpage

\item Une requête SQL est la suivante :

\begin{listing}[language=sql]{}{07-collectionCD-A3.sql}\end{listing}

\item Une requête SQL répondant à la question est la suivante :

\begin{listing}[language=sql]{}{07-collectionCD-A4.sql}\end{listing}

\item Une requête SQL répondant à la question est la suivante :

\begin{listing}[language=sql]{}{07-collectionCD-A5.sql}\end{listing}

\end{questions}

\subsectionnn{Partie B}

\begin{questions}
\item Le regroupement des données en une seule table entrainerait une duplication
des données, d’où l’intérêt d’utiliser trois tables.

\item L’attribut \texttt{id\_album} est une clé étrangère qui permet de lier la table \texttt{Chanson} à la table \texttt{Album}.

\item Un schéma relationnel pour cette version de la base de données est :
\begin{verbatim}
Chanson (id : int, titre : str, #id_album : int)
Album (id : int, titre : str, année : int,
#id_groupe : int)
Groupe (id : int, nom : str)
\end{verbatim}

\item Une requête possible est la suivante :

\begin{listing}[language=sql]{}{07-collectionCD-B4.sql}\end{listing}

\item Une requête possible est la suivante :

\begin{listing}[language=sql]{}{07-collectionCD-B5.sql}\end{listing}

\item Cette requête permet de déterminer le nombre d’albums du groupe Muse présent dans la base de données.
\end{questions}

\subsectionnn{Partie C}

\begin{questions}
\item Les assertions complétées sont :
\begin{verbatim}
assert ordre_lex("", "a") == True
assert ordre_lex("b", "a") == False
assert ordre_lex("aaa", "aaba") == True
\end{verbatim}

\item Le programme complété est le suivant :\enlargethispage*{5em}

\begin{listing}{}{07-collectionCD-C2-corr.py}\end{listing}

\item Une version itérative est la suivante :

\begin{listing}{}{07-collectionCD-C3.py}\end{listing}
\end{questions}
\end{corrige}

%%% NEW 2025

\begin{exo}{Entreprise de location}{Extrait du BAC 2024, Jour 1, Métropole}{3}{45}
\textit{Cet exercice porte sur la programmation Python, les bases de données relationnelles et les requêtes SQL.}

\Par

En particulier, les mots-clés suivants peuvent être utilisés :
\texttt{SELECT}, \texttt{CREATE TABLE}, \texttt{FROM}, \texttt{WHERE}, \texttt{JOIN ON}, \texttt{INSERT INTO}, \texttt{VALUES}, \texttt{UPDATE}, \texttt{SET}, \texttt{COUNT}, \texttt{DELETE}, \texttt{DISTINCT}, \texttt{AND}, \texttt{OR}, \texttt{AS}.

On utilise une base de données relationnelle.
Une entreprise de location de voitures propose à ses clients la possibilité de rapporter le véhicule dans une autre agence. Les informations correspondantes sont rangées dans une base de données. Voici les extraits de deux tables utilisées.

\newpage

\begin{tabular}{|c|c|c|>{\centering\arraybackslash}m{17mm}|>{\centering\arraybackslash}m{15mm}|c|}
\tabulartitle\hline
\multicolumn{6}{|c|}{\ttfamily Agences }\\
\hline
\tabulartitle
\ttfamily Agence & \ttfamily Ville &\ttfamily CP &\ttfamily Departement &\ttfamily Adresse &\ttfamily Telephone \\
\hline
Licorne & Paris & 75001 & Paris & 123 Rue de la Révolution & 0123456789 \\\hline
Le Carosse & Paris & 75001 & Paris & 456 Virtual Street & 0156789012 \\\hline
Vroum & Lyon & 69001 & Rhône & 789 Virtual Lane & 0456789034 \\\hline
Rapide & Toulouse & 31000 & Haute Garonne & 321 Virtual Avenue & 0567890123 \\\hline
Deep Place & Bordeaux & 33000 & Gironde & 987 Virtual Road & 0567890145 \\\hline
\hline
\end{tabular}

\scalebox{0.8}{\begin{tabular}{|c|c|c|c|c|c|c|}
\tabulartitle\hline
\multicolumn{7}{|c|}{\ttfamily Voitures }\\
\hline
\tabulartitle
\ttfamily id\_voiture & \ttfamily marque &\ttfamily modele &\ttfamily kilometrage &\ttfamily nombre\_places &\ttfamily type & carburant \\
\hline
1 & Renault & Clio & 64022 & 5 & Berline & Essence \\\hline
2 & Renault & Clio & 50350 & 5 & Berline & Essence \\\hline
3 & Dacia & Sandero & 62031 & 5 & Berline & Essence \\\hline
4 & Dacia & Sandero & 58955 & 5 & Berline & Essence \\\hline
5 & Dacia & Sandero & 65779 & 5 & Berline & Essence \\\hline
6 & Dacia & Sandero & 56253 & 5 & Berline & Essence \\\hline
7 & Renault & Clio & 49660 & 5 & Berline & Essence \\\hline
8 & Fiat & 500 & 2545 & 4 & Citadine & Electrique \\\hline
9 & Fiat & 500 & 1953 & 4 & Citadine & Electrique \\\hline
10 & Fiat & 500 & 549 & 4 & Citadine & Electrique \\
\hline
\end{tabular}}

\begin{questions}
    \item Donner pour la table Agences un type possible pour l'attribut CP qui indique le code postal.

La fonction \texttt{COUNT} permet de compter le nombre d'enregistrements et le mot-clé \texttt{DISTINCT} permet de ne pas prendre en compte les doublons.

    \item Donner le résultat de la requête suivante pour les extraits des tables données :

\begin{verbatim}
SELECT COUNT(DISTINCT Telephone)
FROM Agences;
\end{verbatim}

    \item Expliquer à quelle condition l'attribut Telephone pourrait servir de clé primaire pour la table Agences.
\end{questions}

Certaines agences ont décidé de partager un même standard téléphonique. On ajoute à la table Agences l'attribut id\_agence qui est utilisé comme clé primaire. 

La nouvelle table obtenue est donnée page suivante.

\scalebox{0.8}{\begin{tabular}{|c|c|c|c|>{\centering\arraybackslash}m{17mm}|>{\centering\arraybackslash}m{15mm}|c|}
\tabulartitle\hline
\multicolumn{7}{|c|}{\ttfamily Agences }\\
\hline
\tabulartitle
\ttfamily id\_agence &\ttfamily  Agence &\ttfamily  Ville &\ttfamily  CP &\ttfamily  Departement &\ttfamily  Adresse &\ttfamily  Telephone \\
\hline
1 & Licorne & Paris & 75001 & Paris & 123 Rue de la Révolution & 0123456789 \\\hline
2 & Le Carosse & Paris & 75001 & Paris & 456 Virtual Street & 0123456789 \\\hline
3 & Vroum & Lyon & 69001 & Rhône & 789 Virtual Lane & 0456789034 \\\hline
4 & Rapide & Toulouse & 31000 & Haute Garonne & 321 Virtual Avenue & 0567890123 \\\hline
5 & Deep Place & Bordeaux & 33000 & Gironde & 987 Virtual Road & 0567890145 \\\hline
\hline
\end{tabular}}

Nous souhaitons créer une table \texttt{couple\_voitures\_agences} qui permettra d'associer la table Agences à la table voitures. On précise que la table voitures a pour clé primaire id\_voiture.
\begin{questions}
\setcounter{enumi}{3}
    \item Décrire les attributs de cette nouvelle table ; une clé primaire sera soulignée et une clé étrangère commencera par un dièse (\#).
    \item Écrire une requête qui permet d'enregistrer dans la table\linebreak \texttt{couple\_voitures\_agences} le fait que le véhicule 2 est associé à l'agence Deep Place.
    \item Écrire une requête qui permet d'actualiser la table pour indiquer que le véhicule 2 se trouve maintenant à l'agence Le Carosse.
    \item Écrire une requête qui permet d'afficher le type, la marque et le nom de l'agence pour l'ensemble des véhicules.
\end{questions}

\end{exo}

\begin{corrige}
\begin{questions}
\item L’attribut \texttt{CP} peut être un \texttt{INT} ou un \texttt{TEXT (STR)}.

\item Dans la mesure où tous les numéros de téléphones figurant dans la table
sont tous distincts, cette requête renvoie le nombre de lignes de cette
table, donc « 5 ».

\item Afin que l’attribut \texttt{Telephone} constitue une clé primaire, il est nécessaire que toutes les agences aient un numéro de téléphone différent.

\item Le schéma relationnel de la table est :

{\ttfamily
couple\_voitures\_agences (\\
\hspace*{5mm} \underline{\# id\_agence} : INT,\\
\hspace*{5mm} \underline{\# id\_voiture} : INT)}

\item Une requête qui permet d’enregistrer dans la table\\ \texttt{couple\_voitures\_agences} le fait que le véhicule 2 est associé à  l’agence Deep Place est :

\begin{listing}[language=sql]{}{07-EntrepriseLocation-QA5.sql}\end{listing}

\item Une requête qui permet d’actualiser la table pour indiquer que le véhicule
2 se trouve maintenant à l’agence Le Carrosse est :

\begin{listing}[language=sql]{}{07-EntrepriseLocation-QA6.sql}\end{listing}

\item Une requête qui permet d’afficher le type, lamarque et le nomde l’agence
pour l’ensemble des véhicules est :

\begin{listing}[language=sql]{}{07-EntrepriseLocation-QA7.sql}\end{listing}
\end{questions}
\end{corrige}

%%% NEW 2025

\begin{exo}{Gestion d’un camping municipal}{D’après BAC 2024, Centres Étrangers Afrique, Jour 1}{3}{45}
\textit{Cet exercice porte sur la programmation Python, la programmation orientée objet, les bases de données relationnelles et les requêtes SQL.}

\Par

L'objectif est de faciliter la gestion du système d'information d'un camping municipal. Les informations nécessaires sont stockées dans une base de données relationnelle composée de trois relations. On pourra utiliser les mots-clés SQL suivants : {\ttfamily AND, FROM, INSERT, INTO, JOIN, ON, SELECT,\\ SET, UPDATE, VALUES, WHERE}.

Voici le schéma des deux premières relations :

\begin{verbatim}
Client (id_client, nom, prenom, adresse, ville, pays, 
telephone)
Reservation (id_reservation, #id_client, #id_emplacement,
nombre_personne, date_arrivee, date_depart)
\end{verbatim}

\newpage

Dans ce schéma :

\begin{itemize}
    \item la clé primaire de chaque relation est définie par son attribut souligné ;
    \item les attributs précédés de \# sont les clés étrangères.
\end{itemize}

La troisième relation est appelée \texttt{Emplacement} et elle contient tous les emplacements du camping. Le tableau ci-dessous en donne un extrait.

\begin{tabular}{|c|c|c|c|}
\tabulartitle\hline
\multicolumn{4}{|c|}{\ttfamily Emplacement }\\
\hline
\tabulartitle
\ttfamily id\_emplacement & \ttfamily nom & \ttfamily localisation & \ttfamily tarif\_journalier \\
\hline
1 & myrtille & A4 & 25 \\
2 & mirabelle & D1 & 35 \\
3 & mangue & B2 & 29.90 \\
4 & mandarine & B1 & 25 \\
5 & mûre & C3 & 29.90 \\
6 & melon & A2 & 25 \\
\hline
\end{tabular}

\begin{questions}
    \item Citer deux avantages à utiliser une base de données relationnelle plutôt qu'un fichier texte ou un fichier tableur.
    \item Quelle doit être la caractéristique d'un attribut pour pouvoir être utilisé en tant que clé primaire?
    \item Dans la relation \texttt{Reservation}, quel est le rôle des clés étrangères \texttt{id\_client} et \texttt{id\_emplacement}?
    \item Donner le schéma relationnel de la relation \texttt{Emplacement} en précisant la clé primaire et le type de chacun des attributs.
    \item À partir de l'extrait du contenu de la relation \texttt{Emplacement} donner le résultat de la requête ci-dessous :

\begin{verbatim}
SELECT id_emplacement, nom, localisation
FROM Emplacement
WHERE tarif_journalier = 25;
\end{verbatim}

    \item Écrire une requête permettant de donner le nom et le prénom de tous les clients habitant à 'Strasbourg'.
    \item Écrire une requête permettant d'ajouter un nouveau client :

\begin{itemize}
    \item \texttt{id\_client}: 42 ;
    \item \texttt{nom}: 'CODD';
    \item \texttt{prénom}: 'Edgar' ;
    \item \texttt{adresse}: '28 rue des Capucines' ;
    \item \texttt{ville}: 'Lyon';
    \item \texttt{pays}: 'France';
    \item \texttt{numéro de téléphone}: '0555555555'.
\end{itemize}

    \item Écrire une requête SQL permettant de récupérer les informations ci-dessous concernant la réservation dont l'identifiant \texttt{id\_reservation} est 18 :

\begin{itemize}
    \item \texttt{Client.nom}
    \item \texttt{Client.prenom}
    \item \texttt{Reservation.nombre\_personne}
    \item \texttt{Reservation.date\_arrivee}
    \item \texttt{Reservation.date\_depart}
    \item \texttt{Emplacement.tarif\_journalier}
\end{itemize}
\end{questions}


\end{exo}

\begin{corrige}
\begin{questions}
\item Nous allons donner trois avantages :
\begin{itemize}
\item la gestion efficace des relations entre les données grâce aux clés primaires
et étrangères ;
\item la non-duplication d’informations ;
\item la possibilité d’effectuer des requêtes complexes pour extraire et manipuler
les données de manière flexible.
\end{itemize}

\item Un attribut utilisé en tant que clé primaire doit être unique pour chaque
enregistrement de la table et ne doit pas contenir de valeurs nulles.

\item Les clés étrangères \texttt{id\_client} et \texttt{id\_emplacement} permettent de créer des relations entre les tables \texttt{Reservation}, \texttt{Client} et \texttt{Emplacement}, assurant ainsi l’intégrité référentielle des données.

\item Le schéma relationnel de la relation \texttt{Emplacement} est :

{\ttfamily
Emplacement (\\
\hspace*{5mm} \underline{id\_emplacement} : INT,\\
\hspace*{5mm} nom : VARCHAR(50),\\
\hspace*{5mm} localisation : VARCHAR(10),\\
\hspace*{5mm} tarif\_journalier : DECIMAL(5,2)\\
)
}

\item Le résultat de la requête est :
\begin{center}
\begin{tabular}{|c|c|c|}
\tabulartitle\hline
\ttfamily id\_emplacement &\ttfamily nom &\ttfamily localisation\\
\hline
1& myrtille& A4\\
\hline
4 &mandarine &B1\\
\hline
6 &melon &A2\\
\hline
\end{tabular}
\end{center}

\item La requête pour obtenir le nom et le prénom de tous les clients habitant
à 'Strasbourg' est :

\begin{listing}[language=sql]{}{07-camping-Q7.sql} \end{listing}

\item La requête pour ajouter un nouveau client est :

\begin{listing}[language=sql]{}{07-camping-Q8.sql} \end{listing}

\newpage

\item La requête SQL pour récupérer les informations concernant la réservation
dont l’identifiant \texttt{id\_reservation} est 18 est :

\begin{listing}[language=sql]{}{07-camping-Q9.sql} \end{listing}

\end{questions}
\end{corrige}

\thumb
\AfficheCorrige
