Contrôle Bases de Données

13 décembre 1999

 

 

 

Durée : 1h30. Documents autorisés.

Barème : I.1 (4 points), I.2 (4 points), I.3 (4 points), II.1-6 (6 points), II.7(2 points).

Recommandations : choisir des solutions simples et lisible et des réponses succinctes.

Partie I : Conception de Base de Données & SQL

La société Xtélécom est une jeune entreprise spécialisée dans la téléphonie mobile. Notre entreprise cherche a adapter son système de facturation à l’offre commerciale qu’elle propose pour la fidélisation de ses clients. Dans cet objectif, vous êtes chargé d’une étude conceptuelle de la base de données support de son futur système de facturation.

Description de l’existant :

Chaque client de notre société souscrit à une offre de fidélisation. La date de la souscription, sa date d’effet ainsi que la durée minimum de la souscription sont mémorisés. Une offre de fidélisation possède un code, une désignation, un tarif mensuel forfaitaire et regroupe un ensemble de services " offerts " et un ensemble de services payants. Ces services peuvent être élémentaires ou génériques. Un service générique regroupe un ensemble de services élémentaires : par exemple, le service " appel en France " regroupe les appels vers un téléphone fixe, les appels vers un téléphone mobile Xtélécom et les appels vers des mobiles d’autres opérateurs. La gestion des services payants est basé sur le système des crédits mensuels : par exemple, le prix de l’offre Pack Xtélécom  donne droit à un crédit de 2h de communication mensuelle en France. La gratuité d’un service peut être limitée dans le temps. Les dépassements de crédit sont calculés selon un prix unitaire, fixé pour chaque type de communication téléphonique. Le prix unitaire correspond à une minute de communication, sachant que la première minute est indivisible, les suivantes sont divisibles en quantum de 15 secondes.

En dehors des services inclus dans l’offre à laquelle il a adhéré, le client a accès à tous les services disponibles pour le public. Chaque service possède un code et une désignation. Le prix mensuel des services nom minutés, c’est-à-dire ceux qui ne correspondent pas à des communications téléphoniques, est fixé de façon forfaitaire (ex. 25 F par mois pour le signal d’appel). Le coût des appels à l’étranger dépend du pays appelé.

Pour chaque client on mémorise son numéro, son nom, son prénom, son adresse, le code postal et la ville ou il réside. Après chaque appel téléphonique effectué par un client, on mémorise le numéro appelé, la date et la durée de la communication. Lorsque le client dispose d’un crédit pour le service appelé, on soustrait de ce crédit la durée de l’appel. Afin de faciliter l’établissement des factures, on mémorise dans la base de données le coût de chaque appel effectué hors crédit.

Travail demandé :

  1. Proposer un schéma conceptuel pour cette base de données sous forme d’un diagramme Entité/Association. L’ajout d’information non contenue dans le texte ci-dessus doit être justifié.
  2. Déduire un schéma relationnel, préciser les clés primaires et le degré de normalisation du schéma obtenu.
  3. Donner l’expression en SQL des requêtes suivantes concernant la consommation de l’abonné numéro 06.1878.2000 en décembre 99 :
  1. Liste des communications (date, numéro composé et durée) effectuées hors crédits.
  2. Liste (désignation du service, prix ) des services non minutés utilisés.
  3. Montant de la facture.

Partie II : Contraintes d’intégrité, vues et dépendances fonctionnelles

  1. A quoi servent les contraintes d’intégrité en Bases de Données ?
  2. A quoi servent le dépendances fonctionnelles en Bases de Données ?
  3. Les dépendances fonctionnelles expriment elles des contraintes d’intégrité ? Justifier la réponse.
  4. A quoi servent les vues en Bases de Données ?
  5. Les vues se basent elles sur les dépendances fonctionnelles ? Justifier la réponse.
  6. Les vues servent elles à exprimer des contraintes d’intégrité ? Justifier la réponse.

Soit R(A, B, C, D) une relation et F ={A->B, B->C, C->A, AB->D, BC->DA, ACD->B} l'ensemble des dépendances fonctionnelles associées à R. Proposer une décomposition de R pour obtenir un schéma en 3ème forme normale, sans perte de dépendances fonctionnelles et sans perte d’information.