Examen de rattrapage 2020-2021 Bases de Données Relationnelles L3 MIDO Maude Ma

Examen de rattrapage 2020-2021 Bases de Données Relationnelles L3 MIDO Maude Manouvrier Juin 2021, 2 heures Document autorisé : 2 feuilles A4 manuscrites. Le barème indiqué est approximatif (sur 40). Exercice 1 - Algèbre relationnelle, calcul relationnel à variable nuplet et SQL (20 points soit 50%) Soit le schéma d'une base de données, décrit en SQL ci-dessous, permettant de gérer des feuilles d'émargement d'examens. Lorsqu'un(e) étudiant(e) signe une feuille d'émargement pour un examen donné, l'attribut Presence a pour valeur TRUE. On supposera qu'il n'existe pas d'homonyme 1. CREATE TABLE Examen ( ExID serial, DateEx Date NOT NULL, Lieu varchar(100) NOT NULL, CONSTRAINT UN_Examen UNIQUE (DateEx,Lieu), CONSTRAINT PK_Examen PRIMARY KEY(ExID) ); CREATE TABLE Etudiant ( EtID serial, Nom varchar(100) NOT NULL, Prenom varchar(100) NOT NULL, CONSTRAINT UN_Etudiant UNIQUE (Nom,Prenom), CONSTRAINT PK_Etudiant PRIMARY KEY(EtID) ); CREATE TABLE Emargement ( ExID integer, EtID integer, Present boolean DEFAULT FALSE, CONSTRAINT PK_Emargement PRIMARY KEY (ExID, EtID), CONSTRAINT "FK_Emargement_Examen" FOREIGN KEY (ExID) REFERENCES Examen (ExID) ON UPDATE RESTRICT ON DELETE RESTRICT, CONSTRAINT "FK_Emargement_Etudiant" FOREIGN KEY (EtID) REFERENCES Etudiant (EtID) ON UPDATE RESTRICT ON DELETE RESTRICT ) ; Dans la suite, vous pourrez abréger le nom des relations par Ex, Et, Em, ainsi que les noms des attributs par leurs initiales (ex. Nom par N, DateEx par D, etc.). 1. Pour chaque requête ci-dessous, que se passe-t-il lorsqu'on exécute la requête sur les instances données page 2 ? (a) INSERT INTO Examen VALUES (2,'22/06/21','Amphi 4'); Expliquer. (b) INSERT INTO Emargement VALUES (1,3,False); Expliquer. (c) INSERT INTO Etudiant(Nom,Prenom) VALUES ('Zarela', 'Maude'); Expliquer. 1. i.e. il n'existe pas 2 étudiant(e)s avec le même nom et le même prénom. 1 2. Exprimer les requêtes suivantes en algèbre relationnelle, en calcul relationnel à variable nuplet et en SQL. Aucune explication demandée. Vos requêtes doivent fonctionner sur les instances des relations données à la n de cette page, mais aussi sur toutes les autres instances possibles. Vous pouvez utiliser une requête précédente en nommant la requête Ri, où i est un numéro de la requête. (a) Quels étudiant(e)s (en donnant leur nom et leur prénom) doivent passer un examen en 'Amphi 4' OU un examen en 'Amphi 8' ? (b) Quels étudiant(e)s (en donnant leur nom et leur prénom) doivent passer un examen en 'Amphi 4' ET un examen en 'Amphi 8' ? (c) Quels étudiant(e)s (en donnant leur nom et leur prénom) ont été absent(e)s (i.e. sont associé(e)s à False sur la feuille d'émargement) à au moins un examen ? (d) Quels étudiant(e)s (en donnant leur nom et leur prénom) ont toujours été présent(e)s à tous les examens (i.e. apparaissent présent(e)s (True sur la feuille d'émargement) pour tous les examens de la base de données) ? 3. Exprimer en en algèbre relationnelle et en calcul relationnel à variable nuplet, la requête sui- vante (Aucune explication demandée) : SELECT D,L FROM Examen ex WHERE NOT EXISTS (SELECT * FROM Etudiant et WHERE NOT EXISTS (SELECT * FROM Emargement em WHERE ex.ExID=em.ExID AND et.EtID=em.EtID)) 4. Créer une vue en SQL permettant d'a cher, pour chaque étudiant(e) (en donnant son nom et son prénom), le nombre d'examens qu'il(elle) doit passer (i.e. le nombre de fois où il(elle) apparaît sur une feuille d'émargement). Aucune explication demandée. 5. Exprimer en calcul relationnel à variable nuplet et en SQL la requête suivante : Quels sont les étudiant(e)s (en donnant leur nom et leur prénom) devant passer le plus grand nombre d'examens ? Vous pouvez utiliser ou supposer existante la vue de la question précédente. Aucune explication demandée. Table 1  Une instance de la relation Examen de l'exercice 1 ExID DateEx Lieu 1 22/06/2021 Amphi 4 Table 2  Une instance de la relation Etudiant de l'exercice 1 EtID Nom Prenom 1 Computing Claude 2 Gamotte Albert Table 3  Une instance de la relation Emargement de l'exercice 1 ExID EtID Present 1 1 TRUE 1 2 FALSE 2 Exercice 2 - Passage au relationnel (7 points soit 17.5%) On souhaite dé nir une base de données permettant de gérer des jurys de concours (ex. concours de danse, de chant etc.). La modélisation Entité/Association et celle en UML sont représentées sur la gure page 4. Un(e) candidat(e) peut passer plusieurs épreuves. Pour chaque épreuve et chaque candidat(e), un membre du jury at- tribue une note. Un historique des notes (comprenant une note nale calculée à une certaine date à partir des notes aectées au candidat) est conservé pour chaque candidat(e) après chaque épreuve. Les identi ants ne sont pas représentés sur le schéma. Chaque ensemble d'entités ou classe possède un attribut unique (non représenté sur le schéma) de type entier dont la valeur s'incrémente automatiquement. Déduisez le schéma relationnel de la base de données correspondante. Vous préciserez les clés primaires (arti cielles) des relations en les soulignant ainsi que les clés étrangères en les signalant par un # et en précisant à quoi elles font référence. Aucune explication n'est demandée. Dans votre schéma relationnel, chaque relation doit être spéci ée de la manière suivante : Nom(att1,...,attn) où Nom est le nom de la relation et att1, ..., attn sont des noms d'attributs. Le nom de la relation doit obligatoirement avoir un lien avec les noms des ensembles d'entités (classes) ou des associations du schéma de modélisation. Exercice 3 - Dépendances fonctionnelles et normalisation (13 points soit 32.5%) Soit une relation R(NomEtudiant, PrenomEtudiant, NumCarteEtudiant, Formation, Cours, Note), dont une partie d'une de ses instances est donnée par le tableau ci-dessous. Pour simpli er, vous pourrez abréger les attributs par NE, PE, NumC, F, E, C, et N. NomEtudiant PrenomEtudiant NumCarteEtudiant Formation Enseignant Cours Note GAMOTTE Albert 12345 L3 MIDO M. Manouvrier BD 15 COMPUTING Claude 45678 L3 MIDO M. Manouvrier BD 12 ZARELA Maude 34567 L3 MIDO C. Murat RO 14 COMPUTING Claude 45678 L3 MIDO C.Murat RO 10 1. Déterminer la famille de dépendances fonctionnelles F, associée à R, sachant qu'on a les contraintes suivantes (à une contrainte correspond une DF) : (a) Un(e) étudiant(e) est identi é(e) par le numéro de sa carte d'étudiant(e). Mais, il peut y avoir des homonymes (i.e. des étudiant(e)s ayant le même nom et le même prénom). (b) Un(e) étudiant(e) est dans une et une seule formation. (c) Un cours, dans une formation donnée, est enseigné par un(e) et un(e) seul(e) enseignant(e). Mais un même cours, dans deux formations diérentes, peut être donné par deux enseignant(e)s diérent(e)s. (d) Un(e) étudiant(e) a une et une seule note par cours, mais suit plusieurs cours de sa formation. 2. A partir de la famille F, déduite de la question précédente, déterminer les clés minimales de R. Aucune explication demandée. 3. En quelle forme normale est la relation R ? Expliquer. 4. Proposer une décomposition de R qui soit sans perte d'information et sans perte de dépendances, contenant le moins de relations possible et dont les relations sont toutes BCNF. Expliquer. 3 Evaluation Note Nom Prenom Jury Note Finale Date Historique Nom Prenom Jury donne Note Nom Prenom Nom Prenom (b) Modelisation UML passe donne * * * Epreuve Description Date 1 1 * passe 0:N 0:P Epreuve Description Date 0:N 1:1 1:1 0:N a pour historique Note Finale Date Historique Candidat(e) Candidat(e) (a) Modelisation Entite/Association Figure 1  Schémas de modélisation de l'exercice 2. 4 uploads/Voyage/ sujet-examen-juin-2021.pdf

Tags
Voyageexamen chaque relation constraint étudiant(e)s
Documents similaires
  • 25
  • 0
  • 0
Afficher les détails des licences
Licence et utilisation
Gratuit pour un usage personnel Attribution requise
Partager
  • Détails
  • Publié le Dec 15, 2022
  • Catégorie Travel / Voayage
  • Langue French
  • Taille du fichier 0.1577MB