TP Modélisation

Pour les diagrammes Entités/Associations, il est recommandé d’utiliser le logiciel https://www.mocodo.net/.

Correction du schéma Stanford

Reprendre le script SQL du schéma Stanford du TP Introduction à SQL pour ajouter les contraintes de clefs primaires et étrangères attendues et prendre les types PostgreSQL les mieux adaptés.

Tous les INSERT déjà présents dans le script doivent continuer à fonctionner. Vérifier ensuite que les insertions suivantes provoquent une erreur :

• un étudiant avec un identifiant mais qui aurait deux noms différents ;
• un établissement avec un nom mais dans deux états différents ;
• la candidature d’un étudiant qui n’existe pas dans la table des étudiants ;
• la candidature d’un étudiant existant à un établissement inexistant ;
• une décision qui ne soit ni Y ni N.

Production de modèle E/A depuis une spécification

L’université souhaite gérer ses bâtiments et leur entretien. On considère le Cahier des Charges (CdC) suivant :

• l’université gère un ensemble de bâtiments, qui possèdent un nom unique. On souhaite stocke leur année de construction et leur adresse.
• chaque bâtiment peut avoir plusieurs niveaux, repérés par leur numéro en partant du 0 pour le rez-de-chaussée. Chaque niveau a une surface donnée et un booléen permettant de savoir si l’étage est accessible aux personnes en fauteuil roulant ou pas.

Analyse d’un modèle E/A

On donne la modélisation suivante en E/A et le code https://www.mocodo.net/ correspondant :

Batiment: nom_bat[text], annee_bat[smallint], adresse_bat[text]
Possede, 1N Batiment, 11 Niveau
Niveau: num_niveau[smallint], surface_niveau[int], acces_hand_niveau[bool]

1. traduire ce modèle en schéma relationnel.
2. ce modèle n’est pas conforme au CdC, pourquoi ?
3. corriger votre schéma relationnel pour le rendre conforme au CdC.
4. répercuter cette modification sur votre modèle E/A.

Compléter le schéma

On étend ensuite le CdC avec les nouvelles spécifications suivantes :

• chaque salle de l’université est répertoriée ; elle possède un numéro unique au sein de l’étage, une surface et une type (Bureau, TD, TP, Cours, stockage etc…).
• les agents d’entretien ont un nom, un prénom et un numéro de sécurité sociale qui est unique
• chaque agent est affecté à un ensemble de salles de l’université ; chaque salle ne peut être associée qu’à un seul agent au maximum.

1. Proposer un nouveau schéma E/A avec cette extension de la spécification.
2. Traduire ce schéma E/A en schéma relationnel.

Requêtes SQL sur l’université

Écrire les requêtes SQL suivantes sur le schéma précédents :

1. Donner la liste des étages dont toutes les salles sont du même type. NB : pas besoin de faire division relationnelle, un COUNT suffit
2. Pour chaque bâtiment, donner le nombre de salles qui sont accessibles aux handicapés et le nombre de salles qui ne le sont pas.

Le gestionnaire de QCMs

On donner le schéma relationnel en SQL PostgreSQL d’une application de gestion de QCMs. On donne aussi un jeu de données d’exemple. On gère des questionnaires de plusieurs questions, chaque question a plusieurs réponse possibles, appellées ici propositions. On en donne une représentation graphique ci-dessous :

Analyse du schéma

On va d’abord analyser le schéma donné en répondant aux questions suivantes (en justifiant les réponses) :

1. Qu’est que le type UUID de l’attribut api_key ?
2. Qu’est ce qui justifie la contrainte UNIQUE de l’attribut api_key ?
3. Peut on choisir l’identifiant quiz_id d’un quiz lors d’un INSERT ?
4. Si on supprime un utilisateur, supprime-t’on aussi tous ses quizs ?
5. Chaque question a un auteur unique, vrai ou faux ?
6. Chaque proposition a un auteur unique, vrai ou faux ?
7. Peut-on avoir deux fois la même valeur de proposition_id dans la table proposition pour des questions différentes ?
8. Pourquoi selon vous le concepteur n’a pas utilisé de GENERATED ALWAYS AS IDENTITY pour question_id ?
9. Peut-on avoir un quiz sans questions ?
10. Peut-on avoir une question sans propositions ?
11. Une question a-t’elle toujours au moins une proposition correcte ?
12. Peut-on avoir plusieurs répondants qui donnent la même réponse à une question ?
13. Peut-on répondre à un questionnaire qui est fermé (attribut open) ?
14. Peut-on répondre plusieurs fois à une même question à des dates différentes ?
15. Peut-on répondre plusieurs réponses à une question donnée ?
16. Peut-on répondre à son propre questionnaire ?
17. Peut-on donner une réponse qui ne soit pas une proposition de cette question ?

Rétro-conception

Proposer un schéma E/A qui une fois transformé en relationnel représenterait au mieux ce schéma. Comparer les différentes possibilités. NOTA BENE je ne sais pas s’il est possible d’avoir un modèle E/A qui produise exactement ce schéma là, il faut donc choisir la meilleure variante et sans doutes commenter le schéma pour représenter les contraintes qui ne sont pas exprimée.

Requêtes SQL sur les QCMs

Écrire les requêtes SQL suivantes :

1. pour chaque quiz calculer le score obtenu par chaque répondant, en comptant +2 pour une bonne réponse et -1 pour une fausse. Vous pourrez avoir besoin de soit :
   • l’expression conditionnelle CASE, documentation ;
   • la clause FILTER des aggrégats, documentation dont on reparlera.
2. pour chaque question donner son nombre de propositions (possiblement 0) et vérifier si dans celles-ci au moins une est correcte ;
3. pour chaque couple d’utilisateur et quiz, déterminer si l’utilisateur a répondu à toutes les questions du quiz. Le faire uniquement pour les utilisateurs dont l’identifiant ne commence pas par user.

Résultats attendus

Avec quelques colonnes intermédiaires non demandées (pour faciliter la conception des requêtes)

    title    |  user_id   | nb_correctes | nb_fausses | score
-------------+------------+--------------+------------+-------
 QCM quiz #0 | other.user |            0 |          1 |    -1
 QCM quiz #0 | test.user  |            2 |          0 |     4
 QCM quiz #1 | other.user |            0 |          1 |    -1
 QCM quiz #1 | test.user  |            1 |          0 |     2

quiz_id | question_id |                       sentence                        | nb_questions | une_correcte
---------+-------------+-------------------------------------------------------+--------------+--------------
       0 |           0 | Qui a inventé le lambda calcul ?                      |            2 | t
       0 |           1 | Qui a inventé le JavaScript ?                         |            3 | t
       1 |           0 | En quel année le standard ES2015 a-t'il été proposé ? |            2 | t
       1 |           1 | Cette question a-t'elle une réponse ?                 |            1 | f
       1 |           2 | Cette autre question a-t'elle aussi une réponse ?     |            0 | Ø

 quiz_id |    user_id    | nb_questions | nb_reponses | toutes_repondues
---------+---------------+--------------+-------------+------------------
       0 | other.user    |            2 |           1 | f
       1 | other.user    |            3 |           1 | f
       2 | other.user    |            0 |           0 | t
       0 | romuald.thion |            2 |           0 | f
       1 | romuald.thion |            3 |           0 | f
       2 | romuald.thion |            0 |           0 | t
       0 | test.user     |            2 |           2 | t
       1 | test.user     |            3 |           1 | f
       2 | test.user     |            0 |           0 | t