\documentclass{scrartcl}
\usepackage[margin=1in, left=0.7in, right=0.7in]{geometry}
\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}
\usepackage[francais]{babel}
\usepackage{amsmath}
\usepackage{amsfonts}
\usepackage{amssymb}
\usepackage{amsthm}
\usepackage{fancyhdr}
\usepackage{hyperref}
%\renewcommand{\familydefault}{\sfdefault}
\usepackage{times}

\def\title#1{\gdef\@title{#1}\gdef\THETITLE{#1}}
\def\author#1{\gdef\@author{#1}\gdef\THEAUTHOR{#1}}
\def\date#1{\gdef\@title{#1}\gdef\THEDATE{#1}}
\newcommand{\psql}{\textsc{PostgreSQL}}
\fancyhead[L]{\textsc{Bases de données -- tp 1}}
\fancyhead[R]{{\THETITLE}} % except the center
\fancyfoot{} % except the center
\fancyfoot[L]{{\THEAUTHOR}}
\fancyfoot[R]{{\THEDATE}}
\renewcommand{\headrulewidth}{0.5pt}
\renewcommand{\footrulewidth}{0.5pt}
\usepackage{exsheets}[2014/09/14] % v0.16 or newer
\SetupExSheets{
  headings = runin ,
  headings-format = \large\bfseries\sffamily ,
  subtitle-format = \large\bfseries\sffamily ,
%  counter-within = section ,
%  counter-format = se.qu\IfQuestionSubtitleT{:} ,
%%%%solution/print = true % uncomment for tutors
}

\usepackage{tikz}
\usetikzlibrary{automata,positioning}

\title{Introduction au modèle relationnel et à {\psql}}
\author{Pierre Senellart \& Louis Jachiet}
\date{7 février 2018}

\begin{document}
\pagestyle{fancy}

\begin{center}
\Huge  \THETITLE
\end{center}

\emph{Le but de ce TP est de se familiariser avec l'utilisation d'un système de
gestion de base de données (en l'occurrence {\psql}) et de {\textsc{sql}} .
Ce TP n'est pas évalué, aucun rendu n'est attendu.}

\section{Modélisation}

On veut représenter les données suivantes. On dispose d'une liste
d'étudiants (avec leurs noms et prénoms) et d'une liste d'examens avec
pour chacun leur type (p.~ex., écrit, oral, devoir à la maison, projet),
leur date, un titre éventuel, un coefficient, et une note maximale (par
exemple, sur 20 ou sur~10). Chaque étudiant a, pour chaque examen, au
plus une note
(éventuellement manquante si l'examen n'a pas encore eu lieu ou si l'étudiant
est absent).

Proposer un schéma relationnel (avec tables, attributs,
    contraintes d'intégrité) pour ces données. On veillera à éviter au
    maximum la redondance de l'information: une donnée élémentaire ne
    doit pas être stockée plusieurs fois dans la base.
    
    Dans un cours
    ultérieur, nous formaliserons cette notion de redondance, et
    proposerons une stratégie générale pour concevoir des schémas de
    bases de données.

\section{Construction de la base}

On utilisera pour ce TP le serveur de bases de données {\psql} à
l'adresse \url{psql.eleves.ens.fr}. Pour cela, il faut d'abord que
vous rendiez à l'adresse web \url{http://psql.eleves.ens.fr} depuis
l'une des machines et que vous créiez un compte : votre identifiant
est le même que celui des machines mais notez bien votre mot de passe,
ce n'est pas le même que celui des machines et il vous resservira
ensuite ! Par ailleurs, ce mot de passe est personnel, vous devez ne
pas le communiquer avec d'autres élèves.

Ces paramètres peuvent être utilisés pour se connecter au serveur de
bases de données: avec le programme de ligne de commande \texttt{psql}
qui s'utilise comme suit: \texttt{psql base identifiant -h serveur}.
La machine \url{psql.eleves.ens.fr} n'est accessible que depuis les
salles informatiques mais vous pouvez aussi vous y connecter après un
\texttt{ssh} sur une des machines, par exemple : \linebreak
\texttt{ssh identifiant@sas.eleves.ens.fr psql base identifiant -h psql.eleves.ens.fr}
\vfill
\begin{question}
 Tester la connexion à la base. La base est vide pour
 l'instant. \og\texttt{\textbackslash h}\fg{} fournit une aide sur les
 commandes \textsc{sql}, tandis que
 \og\texttt{\textbackslash\string?}\fg{} fournit une aide sur les
 commandes propres à l'outil \texttt{psql}.
\end{question}
\vfill

\begin{question}
  Créer en utilisant des commandes \texttt{CREATE TABLE} les tables
    et contraintes d'intégrité proposées dans l'étape précédente. On se
    reportera à la documentation de {\psql} pour sélectionner les
    types de données les plus appropriés pour chaque attribut.
\end{question}
\vfill

\begin{question}
  Vérifier le schéma des tables créées avec la commande
    \og{}\texttt{\textbackslash d}\fg{} suivi du nom de table.
\end{question}
\vfill

\begin{question}
  Remplir ces tables en y ajoutant quelques tuples d'exemple
    d'étudiants et d'examens.
\end{question}
\vfill

\begin{question}
  À l'aide d'un tableur (par exemple LibreOffice Calc), créer un
ensemble de tuples indiquant la note obtenue par chaque étudiant à
chaque examen, dans un format approprié pour l'import à la base.
Sauvegarder le résultat comme un fichier CSV, puis utiliser la
commande \texttt{\textbackslash COPY}, dont vous pouvez consulter la
syntaxe dans la documentation
{\url{https://www.postgresql.org/docs/current/static/app-psql.html\#APP-PSQL-META-COMMANDS-COPY}}.
\end{question}

\section{Algèbre relationnelle et requêtes \textsc{sql}}

  Pour chacune des requêtes suivantes, écrire l'expression de
  l'algèbre relationnelle et la requête \textsc{sql} correspondantes,
  et tester l'exécution de cette requête sur vos données.

\vspace{1em}
  
\begin{question}
 L'ensemble des informations (titre, date, coefficient, note maximale)
 sur tous les examens.
\end{question}

\vspace{1em}

\begin{question}
  L'ensemble des informations sur un examen d'un identifiant donné.
\end{question}

\vspace{1em}

\begin{question}
  L'ensemble des notes de tous les étudiants à un examen donné
    (avec, pour chaque étudiant, son prénom et son nom).
\end{question}

\vspace{1em}

\begin{question}
 Pour chaque étudiant, la moyenne pondérée obtenue à l'ensemble des
 examens, normalisée en une note sur 20.
\end{question}

\vspace{1em}

\end{document}
