Université d’Ottawa Faculté de genie University of Ottawa Faculty of Engineering CSI 2532 EXAMEN FINAL Professeur: Fadi Malek et Herna L. Viktor Durée: 3 heures 29 Avril 2008 Instructions 1. C'est un examen à livre fermé. Les notes et les livres ne sont pas permis. 2. Répondez aux questions sur le questionnaire lui-même. Veuillez écrire à l'endos si vous avez besoin de plus d'espace. 3. Cet examen vaut 60 points. 4. Inscrivez votre nom ainsi que votre numéro d'étudiant au-dessous: Nom: Numéro D'étudiant: Bonne Chance! Page 2 de 13 Question 1 (Diagramme ER - 10 points) On considére la description suivante du système de base de données de Vente aux enchères En ligne cBay dans lequel les membres (les acheteurs et les vendeurs) participent à la vente d'articles. Les exigences de données pour ce système sont résumées comme suit : Le site en ligne a des membres, dont chacun est identifié par un numéro de membre unique et est décrit par l'adresse d'un courrier électronique (email address), le nom, le mot de passe, l'adresse personnelle et le numéro de téléphone. Un membre peut être un acheteur ou un vendeur, ou tous les deux. Un acheteur a son adresse d’expédition (shipping address) enregistré dans la base de données. Un vendeur a un numéro de compte bancaire et un numéro d’acheminement (routing number) enregistré dans la base de données. Les articles à vendre sont placés par un vendeur et sont identifiés par un numéro d'article unique alloué par le système. Les articles sont aussi décrits par un titre d'article, une description, un prix soumissionné de départ (starting bid price), le montant d'augmentation de l’offre (bidding increment), la date de début de la vente aux enchères et la date finale de la vente aux enchères. Les articles sont catégorisés selon une classification hiérarchique fixe. Par exemple, un modem peut être classifié par la hiérarchie suivante : Class : Computer , Subclass : Hardware et Description : Modem Les acheteurs font des offres pour les articles auxquels ils s'intéressent. Le prix soumissionné et le temps de l'offre sont enregistrés. À la fin de la vente aux enchères, le soumissionnaire avec le plus haut prix soumissionné est déclaré le gagnant. La transaction entre le gagnant et le vendeur peut alors procéder. L'acheteur et le vendeur peuvent enregistrer le feed-back quant à leur transaction accomplie. Le feed-back contient une estimation de l'autre parti participant à la transaction (de 1 à 10) et un commentaire. Dessinez un diagramme d'ER pour la base de données de Vente aux enchères En ligne cBay, en utilisant la description donnée ci-dessus. Montrez toutes vos hypothèses clairement. (Utilisez la page suivante pour dessiner votre diagramme d'ER.) 2 Page 3 de 13 Dessinez votre diagramme d'ER ici. 3 Page 4 de 13 Question 2 (Algebre et calcul Relationelle – 8 points) On considére la définition partielle suivante du schéma de base de données de la Musée nationale des arts d’Ottawa. Cette musée contient un certain nombre d'Objets D'art, qui sont classés selon leur Type. Pour chaque Type, nous gardons l’identificateur de type unique, avec une description, le tableau, la sculpture, la statue ou d'autre (comme les photographies ou les imageries). Pour chaque Objet D'art, nous enregistrons le titre, le pays d'origine, le prix d'achat aussi bien que la date de l'acquisition. Les Objets D'art sont des catégories dans les Collections, pour lequel le nom unique (par ex. Le Dessin de Lord Dalhousie, la Renaissance néerlandaise, etc.), type (par ex. Permanent, le Dessin, la Renaissance, etc.) et le nom de la personne de contact sont gardés. Les renseignements d'Artiste sont aussi enregistrés, en incluant son nom, date de naissance, le nombre de prix gagnés et le pays de naissance. Voici le schéma : ArtObject(Aid : integer,Title : string, Origin : string, TypeID : integer, Cost : integer, Acquired : date, Tid : integer, CName : string) Type(TypeID : integer,description : string) Artist(Tid : integer,Tname : string, DoB : date, Awards : integer, Country : string) Collection(Cname : string,CType : string, Contactname : string) A. Écrivez les deux requêtes suivantes dans l'algèbre relationnelle: a. (4 points) Trouvez les noms des Artistes qui ont créé des Objets D'art qui sont contenus dans au moins deux différentes Collections. Par exemple, le Portrait de Vincent van Gogh peut être inclus dans la Collection Permanente, pendant que son tableau de peinture de Sunflowers peut être incluse dans une exposition spéciale des Artistes qui ont vécu dans Provence. 4 Page 5 de 13 b. (2 points) Trouvez les noms des Artistes qui ont gagné plus de trois Prix, mais qui n'ont jamais créé de sculptures. B. (2 points) Expliquez la différence entre le calcul relationnel du domaine et celui du tuple. 5 Page 6 de 13 Question 3 (Modele Relationnel et SQL - 10 points) On considére la définition partielle suivante du schéma de base de données de la Musée nationale des arts d’Ottawa. ArtObject(Aid : integer,T itle : string, Origin : string, T ypeID : integer, Cost : integer, Acquired : date, T id : integer, CName : string) Type(TypeID : integer,description : string) Artist(Tid : integer,T name : string, DoB : date, Awards : integer, Country : string) Collection(Cname : string,CT ype : string, Contactname : string) A. (3 points) La redondance de Données peut causer trois différents types d'anomalies de données. Nommez ces anomalies et, pour chacun, donnez un exemple, en utilisant la base de données de Musée nationale D'art, d'un problème qu'il peut provoquer. B. (2 points) Expliquez, au moyen de votre propre exemple en utilisant la base de données de Musée nationale D'art, qu’est ce q’une contrainte de domaine. 6 Page 7 de 13 C. (2 points) Écrivez la requête SQL pour trouver les noms d'Artistes qui sont énumérés dans notre base de données, mais pour qui nous n'avons jamais vraiment acquis d'Objets D'art. C'est-à-dire, il n'y a aucun Objet D'art de ces Artistes contenus dans notre base de données. D. (3 points) Que trouve t’elle la requête de SQL suivante? SELECT A.Cost, A.Origin FROM ArtObject A WHERE A.Acquired > 1999 GROUP BY A.Cost HAVING 1 < (SELECT count(*) FROM ArtObject A2 WHERE A.Cost = A2.Cost AND A2.Acquired > 1999) 7 Page 8 de 13 Question 4 (Formes normales – 10 points) On considére la relation suivante quant aux Options installées dans les Voitures vendues à une Concession Automobile (car dealership), comme par ex. Le cruise control et l’air climatisé (air conditioning), avec la liste et les prix escomptés de ces Options. CarSale(CarID : integer, OptionT ype : integer, OptionListP rice : currency, SaleDate : date, OptionDiscountedP rice : currency) Supposez que les dépendances fonctionnelles suivantes existent : CarID → SaleDate OptionT ype → OptionListPrice CarID, OptionT ype → OptionDiscountedPrice A. (2 points) Cette relation n’est pas en 3NF. Expliquez pourquoi. B. (2 points) Normaliser cette relation en BCNF. 8 Page 9 de 13 C. (2 points) Déterminez si votre décomposition de la question 4.B est une dépendance préservante. Justifiez votre réponse. D. (2 points) Déterminez si votre décomposition de la question 4.B est sans perte. Justifiez votre réponse. E. (2 points) quelle est la motivation pour mettre une relation dans BCNF ?. 9 Page 10 de 13 Question 5 (Storage et Indexage – 12 points) A. (4 points) Expliquez les termes suivants: a. une entrée de donnée dans l’index de l’alternative 2. (a data entry in an Alternative 2 index ) b. un index non groupé (an unclustered index) c. la charge de travail (the workload) d. une évaluation seulement d'index (index-only evaluation) 10 Page 11 de 13 B. On considére la requête suivante des Artistes du schéma de la base de données de Musée nationale Des arts auparavant présenté . Artist(Tid : integer,T name : string, DoB : date, Awards : integer, Country : string) La requête est comme suit: SELECT A.Tname, A.DoB FROM Artist A WHERE A.Awards < 8 AND A.Awards > 2 Supposez qu'il y a un index d'arbre B+ disponible sur l'attribut de Awards. a. (2 points) Identifiez une raison possible pourquoi un optimisateur peut ne pas trouver un bon plan. b. (2 points) Réécrivez la requête pour qu'un bon plan soit probablement trouvé. 11 Page 12 de 13 C. (4 points) Considérez les relations suivantes, qui décrivent des Joueurs de hockey et les Équipes pour lesquelles ils jouent. Player(pid : int, bonus : currency, teamid : string) Team(teamid : int, location : string, budget : currency) On vous dit que les deux requêtes suivantes sont extrêmement importantes. Trouvez l'endroit de l'Équipe pour laquelle un Joueur specifie par d'utilisateur joue. Vérifiez si le budget d'une Équipe est plus grand que la prime de chaque Joueur dans cette Équipe. a. (2 points) Décrivez quelle structure de fichier que vous choisiriez pour ces relations, étant donné que les mises à jour ne se produisent pas très souvent. b. (2 points) Décrivez quels indexes que vous créeriez pour accélérer les deux requêtes. 12 Page 13 de 13 Question 6 (Traitement des Requête – 10 points) On considére, pour la dernière fois , la définition partielle suivante du schéma de base de données de la Musée nationale des arts. ArtObject(Aid : integer,T itle : string, Origin : string, T ypeID : integer, Cost : integer, Acquired : date, T id : integer, CName : string) Type(TypeID : integer,description : string) Artist(Tid : integer,T name : string, DoB : date, Awards : integer, Country : string) Collection(Cname : string,CT ype : string, Contactname : string) A. (4 points) Completer la phrase suivante : les trois techniques les plus communes utilisées par les algorithmes pour évaluer les opérateurs relationnels sont (a) , (b) et (c) indexing. B. (3 points) Expliquez, au moyen de votre propre exemple en utilisant l'exemple de Musée nationale des arts, quelle est la sélectivité d'un chemin d'accès (an access path). C. (3 points) Expliquez, au moyen de votre propre exemple en utilisant l'exemple de Musée nationale des arts, quelle est la definition de pipelining. 13