Questionnaire 3

Ce questionnaire se compose de deux parties. La première partie comprend des questions conceptuelles sur les sujets abordés dans le module. La deuxième partie, plus pratique, contient des exercices de formulation ou d’analyse d’expressions en langage SQL.

Première partie

Dans cette partie, nous vous proposons une série de questions qui vous permettront de valider votre compréhension des notions traitées dans les textes du module. Nous vous invitons à bien réfléchir à la question et à sa réponse avant de regarder la solution et, au besoin, à revenir sur le texte pour consolider vos connaissances. Les questions sont indépendantes les unes des autres ; vous n’êtes donc pas tenu d’y répondre dans l’ordre de leur présentation.

Question 1

Parmi les affirmations suivantes, laquelle décrit le mieux le langage SQL ?

  1. SQL est un langage de programmation dédié au développement de bases de données.
  2. SQL est un langage pour définir des requêtes sur des bases de données relationnelles.
  3. SQL est un langage de définition, de mise à jour, d’interrogation, etc. de bases de données relationnelles.

Réponse : c

Explication

Le langage SQL est introduit dans le texte 3.1 « Le langage SQL 2 », aux pages 217 et 218. À proprement parler, SQL n’est pas un langage de programmation (réponse a) comme le sont les langages C et JAVA. De plus, il n’est pas seulement utilisé pour définir des requêtes sur des bases de données relationnelles (réponse b) ; la définition des requêtes est une des facettes du langage SQL.

Question 2

Qu’est-ce qui différencie les langages SQL 1, SQL 2 et SQL 3 ?

  1. SQL 1 constitue le niveau d’entrée du langage, SQL 2 le niveau intermédiaire et SQL 3 le niveau avancé.
  2. SQL 3 est une version corrigée du langage SQL 2 et ce dernier est une version corrigée du langage SQL 1.
  3. SQL 1 est la première version du langage et les versions 2 et 3 sont des extensions de cette première version.

Réponse : c

Explication

L’évolution du langage SQL est présentée dans le texte 3.1 « Le langage SQL 2 », à la page 218.

Question 3

Quel est le rôle d’un curseur pour une transaction ?

  1. Le curseur permet un balayage séquentiel de l’ensemble des tuples d’une base de données.
  2. Le curseur permet d’identifier de manière univoque un tuple dans une base de données.
  3. Le curseur est le résultat de la programmation d’une transaction dans un langage de programmation hôte.

Réponse : a

Explication

Les concepts de transaction et de curseur sont définis dans la section 5 du texte 3.1 « Le langage SQL 2 », à partir de la page 235.

Question 4

Parmi cette liste de contraintes d’intégrité, lesquelles sont des contraintes de comportement ?

  1. Contrainte de domaine.
  2. Contrainte temporelle.
  3. Dépendance d’inclusion.
  4. Contrainte d’entité.
  5. Contrainte de référence.

Réponse : b et c

Explication

La dépendance d’inclusion et la contrainte temporelle sont deux exemples des contraintes de comportement (ou contraintes non structurelles) présentées dans le texte 3.2 « Intégrité et BD actives », à partir de la page 250. Les réponses a, d et e se réfèrent aux contraintes structurelles dont il est question dans la section 3 du texte 2.1 « Le modèle relationnel », à partir de la page 185. 

Question 5

Pourquoi faut-il réaliser un contrôle des contraintes d’intégrité ?

  1. Pour assurer la cohérence d’une base de données après la réalisation de transaction.
  2. Pour vérifier la consistance d’une règle d’intégrité au regard d’un schéma de base de données.
  3. Pour contrôler la cohérence des tuples d’une relation de la base de données.

Réponse : a

Explication

Les moyens de contrôle de la validité des contraintes d’intégrité après la réalisation d’une transaction sont explicités dans la section 4 du texte 3.2 « Intégrité et BD actives », à partir de la page 258.

Question 6

Quel est le rôle d’une vue au regard d’une base de données ?

  1. Une vue est une représentation graphique d’une base de données.
  2. Une vue est une réalisation du niveau externe de l’architecture AINSI/SPARC.
  3. Une vue est une interface utilisateur permet d’interroger une base de données.

Réponse : b

Explication

Le texte 3.3 « La gestion des vues » est dédié à ce concept de vue. Pour un rappel sur le niveau externe de l’architecture à trois niveaux de l’ANSI/SPARC, consultez le texte 1.2 « Objectifs et architecture des SGBD », à la page 18.

Question 7

Quelle est la particularité d’une vue concrète ?

  1. Une vue concrète est une table physique créée à partir d’autres tables et répondant à une requête précise.
  2. Une vue concrète est une vue qui permet une mise à jour de la base de données.
  3. Une vue concrète est une vue qui ne concerne que des relations décrivant des objets réels du monde (ex : livres, automobiles, vins).

Réponse : a

Explication

Le concept de vue concrète est présenté dans la section 5 du texte 3.3 « La gestion des vues », à partir de la page 290. La réponse b correspond à la définition d’une vue mettable à jour, introduite dans la section 4 du texte 3.3 « La gestion des vues ».

Question 8

Quelle est le problème de la mise à jour au travers de vues ?

  1. Le problème est que seule l’opération DELETE est applicable sur la base de données avec les vues.
  2. Le problème est que la structure d’une vue peut empêcher la mise à jour d’une relation de la base de données.
  3. Il n’y en a pas ! Tout vue permet la mise à jour des données contenues dans une base de données.

Réponse : b

Explication

La mise à jour de la base de données au travers des vues est une problématique abordée dans la section 4 du texte 3.3 « La gestion des vues ».

Question 9

Quel est le résultat retourné par la requête suivante en SQL ?

En SQL, la requête
 SELECT NOM, AGE+1
 FROM PERSONNES
 WHERE AGE BETWEEN 0 AND 17 ;

  1. Le nom de toutes les personnes ayant entre 0 et 17 ans.
  2. Le nom de toutes les personnes ayant entre 1 et 18 ans.
  3. Le nom et l’âge de toutes les personnes ayant entre 0 et 17 ans.
  4. Le nom et l’âge de toutes les personnes ayant entre 1 et 18 ans.
  5. Aucune des réponses précédentes.

Réponse : e

Explication

Cette question retourne le nom de toutes les personnes ayant entre 0 et 17 ans ainsi que leur âge incrémenté par 1 (l’âge qu’elles auront l’année prochaine). L’expression des sélections en SQL est présentée dans la section 3.2 du texte 3.1 « Le langage SQL 2 ».

Question 10

Soit T1 (A, B) et T2 (A, C) deux tables ; les attributs A étant de même type dans les deux tables. Exprimez la requête suivante avec une structure SELECT sans question imbriquée.

SELECT *
FROM T2
Where T2.A IN
 SELECT A
 FROM T1

Réponse :

SELECT A C
FROM T1 T2
WHERE T1.A = T2.A

Explication

Dans le premier cas, la sous-question imbriquée fait une projection de la table T1 sur la colonne A et la question globale fait la jointure de ce résultat avec la table T2. Dans le deuxième cas, il s’agit de la jointure naturelle de T1 et T2. La section 3.3 du texte 3.1 « Le langage SQL 2 » présente l’expression des jointures en SQL tandis que la section 3.4 du même texte montre les questions imbriquées.

Deuxième partie

Cette partie vise à vous préparer davantage à la réalisation du travail pratique ainsi qu’aux exercices pratiques de l’examen sous surveillance. Les exercices de cette partie portent tous sur un même cas et doivent être résolus dans l’ordre. Nous vous encourageons à utiliser le logiciel MySQL pour valider vos réponses et pour vous familiariser davantage avec la syntaxe de SQL. N’hésitez pas à aller au-delà des situations présentées en imaginant et en testant d’autres contraintes ou requêtes.

Exercice 1

Une agence de voyages propose des forfaits touristiques à différents endroits et pour différentes catégories de voyage. Pour chaque endroit, l’agence procure les informations suivantes : le nom du lieu, le nom du pays dans lequel se trouve ce lieu, et le niveau de confort disponible dans ce lieu. Une catégorie de voyage est définie en fonction du type de voyage (d’aventure, culturel, de loisir, etc.), de l’organisation du groupe (groupe, famille, individuel, sur mesure) et de l’âge minimal requis pour participer au voyage de cette catégorie. Enfin, un forfait est un type de voyage pour un lieu donné pendant une période particulière de l’année.

Donnez les commandes de SQL permettant de créer la base de données de l’agence de voyages avec les informations précédentes (sans inclure des contraintes).

Réponse :

CREATE SCHEMA AGENCE ;
USE AGENCE ;

CREATE TABLE LIEU (
IDLIEU INT,
NOM CHAR(30),
PAYS CHAR(20),
FACILITES INT) ;

CREATE TABLE CATEGORIE (
ID INT,
NOM CHAR (30),
TYPE CHAR(20),
ORGANISATION INT,
AGE INT) ;

CREATE TABLE FORFAIT (
IDLIEU INT,
CATEGORIE INT,
DATEDEBUT DEC(6),
DATEFIN DEC(6)) ;

Explication

Le modèle de la base comporte deux entités principales : LIEU et CATEGORIE, ainsi qu’une entité qui établit la relation entre elles, FORFAIT. En effet, un forfait est la combinaison d’un lieu et d’une catégorie de voyage. Le schéma se définit donc par ces trois tables. Pour la table LIEU, il y a le nom du lieu ainsi qu’un code numérique l’identifiant de façon unique et, éventuellement, le pays dans lequel se trouve ce lieu. Le dernier attribut, FACILITES, permet de représenter, par un code numérique, le niveau de confort du lieu (par exemple 0 pour un endroit très sauvage et 10 pour un lieu avec toutes les facilités). Les attributs TYPE et ORGANISATION de la table CATEGORIE peuvent, eux aussi, être représentés par un entier ou par une chaîne de caractères (par exemple l’organisation peut être un entier entre 1 et 4 ou un texte pouvant comporter les mots : « groupe », « famille », « individuel » ou « sur mesure »). Les attributs DATEDEBUT et DATEFIN sont décrits par 6 décimales, mais pourraient aussi être définis par le type DATE. La création de schémas et de tables est expliquée dans la section 2.1 du texte 3.1 « Le langage SQL 2 ».

Exercice 2

Modifiez les commandes de création de tables de l’exercice précédent pour ajouter des contraintes d’unicité et de valeur non nulle.

Réponse :

CREATE TABLE LIEU (
IDLIEU INT NOT NULL,
NOM CHAR(30) NOT NULL,
PAYS CHAR(20),
FACILITES INT,
PRIMARY KEY (IDLIEU)) ;

CREATE TABLE CATEGORIE (
ID INT NOT NULL,
NOM CHAR (30),
TYPE CHAR(20) NOT NULL,
ORGANISATION INT,
AGE INT,
PRIMARY KEY (ID)) ;

CREATE TABLE FORFAIT (
IDLIEU INT NOT NULL,
CATEGORIE INT NOT NULL,
DATEDEBUT DEC(6),
DATEFIN DEC(6)
PRIMARY KEY (IDLIEU, CATEGORIE)) ;

Explication

Chaque table a une colonne correspondant à la clé principale de la table. Cette colonne doit être non nulle et doit être indiquée comme étant la clé principale. Pour la table FORFAIT, la clé principale est composée de deux attributs. Par ailleurs, bien qu’on puisse mettre la restriction NOT NULL à tous les attributs pour ne pas avoir des tuples incomplets, ceci peut restreindre la possibilité d’inclure dans la base des informations incomplètes mais utiles (par exemple, il pourrait être utile d’inscrire un forfait, même si on ne connaît pas encore les dates). Les contraintes de colonnes sont dans la section 2.2 du texte texte 3.1 « Le langage SQL 2 ».

Exercice 3

Ajoutez une contrainte de relation à chacune des tables. Pour ajouter une contrainte à une table déjà créée, vous pouvez utiliser la commande ALTER avec la syntaxe suivante :
ALTER TABLE <NOM DE TABLE> ADD CONSTRAINTE <NOM DE CONTRAINTE> <CONTRAINTE> ;

Réponse :

Des contraintes possibles seraient par exemple :

  1. ALTER TABLE CATEGORIE ADD CONSTRAINT age_check CHECK (AGE >= 0 AND AGE < 200)
  1. ALTER TABLE CATEGORIE ADD CONSTRAINT type_domaine CHECK (TYPE IN ("AVENTURE","CULTURE","LOISIR")) ;
  2. ALTER TABLE FORFAIT ADD CONSTRAINT chk_date1 CHECK (DATEDEBUT BETWEEN 010110 AND 311299) ;
  3. ALTER TABLE FORFAIT ADD CONSTRAINT chk_date2 CHECK (DATEDEBUT < DATEFIN) ;
  4. ALTER TABLE FORFAIT ADD CONSTRAINT FK_lieu FOREIGN KEY(IDLIEU) REFERENCES LIEU(IDLIEU) ;
  5. ALTER TABLE FORFAIT ADD CONSTRAINT FK_categorie FOREIGN KEY(CATEGORIE) REFERENCES CATEGORIE(ID) ;

Explication

Les trois premières contraintes indiquent les valeurs possibles pour un attribut. La contrainte 4 met en relation deux attributs d’une table et les deux dernières contraintes concernent la déclaration de clés foraines. Les contraintes de relation sont introduites dans la section 2.2 du texte texte 3.1 « Le langage SQL 2 ».

Exercice 4

Déclarez une vue sur la table LIEU pour ne voir que les lieux touristiques au Canada.

Réponse :

CREATE VIEW NATIONAL (NOM,FACILITES)
AS SELECT NOM,FACILITES
FROM LIEU
WHERE PAYS="CANADA" ;

Explication

Les vues sont introduites dans la section 2.3 du texte 3.1 « Le langage SQL 2 ».

Exercice 5

Écrivez les instructions SQL permettant d’insérer un forfait loisir, valide pour le mois de décembre 2010, pour la ville de Carthagène en Colombie. Supposez qu’aucun tuple n’existe encore dans la base et prenez en compte les contraintes décrites dans l’exercice 3 précédent.

Réponse :

INSERT INTO LIEU (IDLIEU,NOM,PAYS) VALUE(5701,"CARTHAGENE,"COLOMBIE") ;

INSERT INTO CATEGORIE (ID,NOM,TYPE) VALUE(1,"REVE COLONIAL","LOISIR") ;

INSERT INTO FORFAIT(IDLIEU,CATEGORIE,DATEDEBUT,DATEFIN) VALUE(5701,1,011210,311210) ;

Explication

La table FORFAIT a deux clés foraines, l’une correspondant à la clé de la table LIEU et l’autre à celle de la table CATEGORIE. Il faut donc créer un tuple LIEU pour la ville de Carthagène et un tuple CATEGORIE pour la catégorie loisir, avant de pouvoir insérer le forfait. L’insertion de tuples est expliquée dans la section 4 du texte 3.1 « Le langage SQL 2 ».

Exercice 6

Écrivez une requête en SQL pour présenter les forfaits disponibles pour un pays donné, par exemple la France. La requête doit retourner l’identification de la catégorie, le nom du lieu de vacances et les dates de début et de fin du forfait.

Réponse :

SELECT F.CATEGORIE, L.NOM, F.DATEDEBUT, F.DATEFIN
 FROM FORFAIT F, LIEU L
 WHERE F.IDLIEU=L.IDLIEU AND L.PAYS="FRANCE" ;

Explication

La requête fait intervenir deux tables : FORFAIT et LIEU. Cette deuxième est nécessaire pour trouver le nom du lieu. Les requêtes en SQL sont présentées dans la section 3 du texte 3.1 « Le langage SQL 2 ».

Exercice 7

Modifiez la requête précédente pour inclure dans la réponse, au lieu de l’identifiant de la catégorie du forfait, le nom donné à cette catégorie, son type et son organisation.

Réponse :

SELECT C.NOM, C.TYPE, C.ORGANISATION, L.NOM, F.DATEDEBUT, F.DATEFIN
 FROM FORFAIT F, LIEU L, CATEGORIE C
  WHERE F.IDLIEU=L.IDLIEU
 AND L.PAYS="FRANCE"
 AND C.ID=F.CATEGORIE ;

Explication

Cette nouvelle requête fait intervenir les trois tables, car les nouvelles informations à afficher se trouvent dans la table CATEGORIE. Les requêtes en SQL sont présentées dans la section 3 du texte 3.1 « Le langage SQL 2 ».

Exercice 8

Faites la même requête que celle demandée dans les exercices 6 et 7, mais ne présentez que les informations concernant la catégorie, soit son nom, son type et son organisation.

Réponse :

SELECT C.NOM, C.TYPE, C.ORGANISATION
 FROM FORFAIT F, LIEU L, CATEGORIE C
 WHERE F.IDLIEU=L.IDLIEU
  AND L.PAYS="FRANCE"
  AND C.ID=F.CATEGORIE ;

Explication

Bien que pour cette nouvelle requête les informations demandées ne concernent que la table CATEGORIE, la requête fait intervenir les trois tables. Les requêtes en SQL sont présentées dans la section 3 du texte 3.1 « Le langage SQL 2 ».

Exercice 9

Faites la même requête que celle demandée dans l’exercice 8, mais en utilisant des sous-questions. Est-ce que cette requête produit le même nombre de réponses que la requête précédente ? Pourquoi ? Laquelle devrait être modifiée et comment ?

Réponse :

La requête avec des sous-questions pourrait avoir la forme suivante (d’autres options sont possibles) :

SELECT C.NOM, C.TYPE, C.ORGANISATION
FROM CATEGORIE C
WHERE C.ID IN
 (SELECT CATEGORIE
 FROM FORFAIT F
 WHERE F.IDLIEU IN
 (SELECT IDLIEU
 FROM LIEU
 WHERE PAYS="FRANCE")) ;

La solution présentée dans l’exercice précédent n’élimine pas les réponses dupliquées, alors que celle-ci le fait. Pour modifier la requête précédente, il suffit d’ajouter le mot DISTINCT après le premier mot SELECT.

Explication

La requête avec question imbriquée parcourt tous les tuples de la table CATEGORIE et garde le nom, le type et l’organisation que pour la requête dont l’identifiant (unique) satisfait certaines conditions. Puisque dans la table CATEGORIE il n’y a pas d’éléments dupliqués, la réponse ne contient pas non plus de dupliqués. La requête de l’exercice précédent, par contre, fait une jointure de trois tables sur trois attributs. Puisque les valeurs de certains de ces attributs ne sont pas uniques dans toutes les tables (par exemple la catégorie dans la table FORFAIT), la réponse peut donner des éléments dupliqués. Les requêtes en SQL sont présentées dans la section 3 du texte 3.1 « Le langage SQL 2 ».

Exercice 10

Écrivez une requête en SQL pour présenter tous les lieux qui ont des facilités de niveau 7 ou plus pour lesquels l’agence propose des forfaits.

Réponse :

SELECT NOM
FROM LIEU
WHERE (FACILITES >=7) AND
 IDLIEU IN (SELECT DISTINCT IDLIEU FROM FORFAIT) ;

Ou encore

SELECT DISTINCT NOM
FROM LIEU, FORFAIT
WHERE (LIEU.FACILITES>=7) AND (LIEU.IDLIEU = FORFAIT.IDLIEU) ;

Explication

Dans le premier cas, la question imbriquée retourne la liste des identifiants de lieu pour tous les lieux pour lesquels l’agence offre des forfaits. La question globale retourne le nom des lieux ayant des facilités de 7 ou plus et qui figure dans la liste résultante de la question imbriquée. Dans le deuxième cas, la question effectue la jointure de LIEU et FORFAIT sur l’attribut IDLIEU conservant les lieux avec des facilités de 7 ou plus. Les requêtes en SQL sont présentées dans la section 3 du texte 3.1 « Le langage SQL 2 ».