Tutoriel de Bases de Données Relationnelles

Question 1 : 5 points

Question 2 : 2 points

Question 3 : 3 points (Q3.1 = 1 pt, Q3.2 = 1 pt, Q3.3 = 1 pt)

Question 4 : 6 points (Q4.1 = 2 pts, Q4.2 = 2 pts, Q4.3 = 2 pts)

Question 5 : 4 points (Q5.1 = 1,5 pts, Q5.2 = 2,5 pts)

L’école de management de l’INT nous demande de l’aider à mettre en place une application permettant de gérer des questionnaires destinés à tester les connaissances des étudiants. Le schéma relationnel suivant décrit la base de données QUESTIONNAIRE (les clés sont en gras) :

QUESTIONNAIRE(noquestionnaire, objectif, nomenseignant)

QUESTION(noquestionnaire, noquestion, libellé, réponse)

ELEVES(noélève, nom, prénom)

INSCRIPTION(noélève, noquestionnaire, date)

RESULTAT(noélève, noquestionnaire, noquestion, proposition)

La sémantique des diverses relations est la suivante :

QUESTIONNAIRE(noquestionnaire, objectif, nomenseignant) : un questionnaire est identifié de manière unique par un numéro et est décrit par son objectif et par le nom de l’enseignant qui l’a conçu,

QUESTION(noquestionnaire, noquestion, libellé, réponse) : un questionnaire se compose de questions. Chaque question d’un questionnaire est identifiée par un numéro et est décrite par un libellé et la réponse associée. L’attribut réponse peut prendre deux valeurs, vrai ou faux. On peut donc voir un questionnaire comme une liste d’affirmations dont l’étudiant doit dire si elles sont vraies ou fausses,

ELEVES(noélève, nom, prénom) : un élève est identifié de manière unique par son numéro. Il est décrit par son nom et son prénom,

INSCRIPTION(noélève, noquestionnaire, date) : une ligne dans cette relation représente le fait qu’un élève (identifié par son numéro) a répondu à un questionnaire (identifié par son numéro) à une date donnée,

RESULTAT(noélève, noquestionnaire, noquestion, proposition) : une ligne dans cette relation signifie qu’un élève a proposé une réponse (valeur de l’attribut proposition) à une question (identifiée par son numéro) d’un questionnaire (identifié par son numéro). Le domaine de l’attribut proposition est le même que celui de l’attribut réponse de la relation QUESTION.

Donner le schéma Entité-Association correspondant à ce schéma relationnel. Vous préciserez bien les entités, entités faibles (s’il y a lieu), associations, propriétés, clés et cardinalités des associations.

Donner la définition complète de la relation QUESTION dans le langage SQL d'Oracle. Par définition complète, on entend la définition des attributs et de leur domaine, mais aussi la définition de toutes les contraintes d'intégrité associées.

CREATE TABLE QUESTION (

NOQUESTIONNAIRE INTEGER,

NOQUESTION INTEGER,

LIBELLE CHAR(256),

REPONSE CHAR(4) CONSTRAINT CREP

(CHECK REPONSE IN (‘VRAI’, ‘FAUX’)

)

ALTER TABLE QUESTION ADDPRIMARY KEY (NOQUESTIONNAIRE, NOQUESTION)

ALTER TABLE QUESTION ADD CONSTRAINT REFQUEST FOREIGN KEY (NOQUESTIONNAIRE) REFERENCES QUESTIONNAIRE(NOQUESTIONNAIRE)

Exprimer chacune des 3 questions suivantes en algèbre relationnelle.

(Q3.1) Donner le nom et le numéro des élèves qui ont proposé comme réponse la valeur " vrai " à la question numéro 10 du questionnaire de numéro 5.

(Q3.2) Donner la liste des questions auxquelles les étudiants de nom ‘Defude’ ont répondu. Pour chaque question, on veut le noquestionnaire, le noquestion et le libellé.

(Q3.3) Donner les questionnaires (noquestionnaire et objectif) auxquels aucun étudiant n’a répondu.

On fait la différence entre l’ensemble des questionnaires (sous-arbre de gauche) et l’ensemble des questionnaires qui ont obtenu une réponse (sous-arbre de droite).

Soit le schéma relationnel M(NE, NQ, NS, D, P, O, M) avec les dépendances fonctionnelles suivantes (il s’agit de la fermeture transitive) :

NQ -> O; NQ -> NS; NS -> M; NS -> P; NQ -> P;

NQ -> M ; NE, NQ -> D 

(Q5.1) Donner la(les) clé(s) de ce schéma relationnel.

De l’étude de la fermeture transitive on voit qu’aucun attribut tout seul ne dérive tous les autres. Il faut prendre deux attributs pour dériver tous les autres qui sont NQ et NE (ce sont d’ailleurs deux attributs qui ne sont jamais en partie droite des DF). La clé est donc le couple (NQ, NE)

(Q5.2) Donner la forme normale de ce schéma et s’il n’est pas en 3FN proposer une décomposition en 3FN.

Le schéma est en 1FN par définition. Il est en 2FN si aucun attribut non clé ne dépend d’une partie de la clé. Ici les attributs clés sont NQ et NE et les attributs non clés sont D, O, NS, M et P.

Ici on a NQ qui donne O qui est un exemple d’attribut non clé dépendant partiellement d’une clé. Donc le schéma n’est pas en 2FN donc il est en 1FN.

Une décomposition possible du schéma (mais non l’unique) est la suivante :

R1(NS, M, P) en utilisant la DF : NS donne M, P avec NS clé

R2(NQ, O, NS) en utilisant la DF : NQ donne O, NS avec NQ clé

R3(NQ, NE, D) en utilisant la DF : NQ, NE donne D avec NQ, NE clé.