Devoir de contrôle n°3 STI : base de données relationnelle et requêtes SQL

Exercice 1 : le schéma de la base « clubs » (8 pts)

Le lycée gère ses clubs avec une base de données MySQL formée de deux tables :

• club (code, nom, salle) : code est la clé primaire ;
• eleve (num, nom, prenom, naissance, classe, points, #code_club) : num est la clé primaire, code_club est une clé étrangère qui référence club(code). Un élève appartient à au plus un club.

• 1) Quelle est la relation entre club et eleve (1:1, 1:n ou n:m) ? Justifier (2 pts).
• 2) Écrire les requêtes CREATE TABLE des deux tables : nom obligatoire, points positifs ou nuls (contrainte CHECK), clé étrangère (4 pts).
• 3) Pour chacune de ces insertions, dire si elle est acceptée et sinon quelle contrainte elle viole : (a) un élève de numéro 101 alors que 101 existe ; (b) un élève dans le club 'MUS' qui n'existe pas ; (c) un élève sans club (2 pts).

Exercice 2 : requêtes SQL (12 pts)

La base « clubs » de l'exercice 1 contient les données suivantes :

• club : (ROB, Robotique, Labo 2), (WEB, Développement web, Salle 12), (ECH, Échecs, sans salle).
• eleve : (101, Ben Ali, Sami, 2009-04-12, 3SI1, 85, ROB), (102, Trabelsi, Rim, 2008-12-30, 3SI2, 92, WEB), (103, Jaziri, Omar, 2009-07-05, 3SI1, 40, ROB), (104, Mansour, Lina, 2009-01-20, 3SI1, 70, sans club), (105, Bouaziz, Aziz, 2008-09-15, 3SI2, 65, ECH).

Écrire les requêtes SQL suivantes, sans jointure ni fonction d'agrégat, et donner leur résultat :

• 1) Le nom et le prénom des élèves de la classe 3SI1, par ordre alphabétique du nom (2 pts).
• 2) Le prénom et les points des élèves qui ont entre 60 et 90 points, du plus grand au plus petit nombre de points (2 pts).
• 3) Les prénoms des élèves dont le nom commence par « B » (2 pts).
• 4) Les prénoms des élèves nés en 2009 qui n'ont pas de club (2 pts).
• 5) Les noms des clubs qui n'ont pas de salle (2 pts).
• 6) Ajouter 10 points à tous les élèves du club Robotique, puis afficher leur prénom et leurs points (2 pts).