Tutoriel de Bases de Données Relationnelles

TP de rétro-conception et de protection des informations

 

Soit le schéma relationnel suivant décrivant un système de gestion des films et leur distribution dans des salles de cinéma :

REALISATEURS(idrealisateur, nomrealisateur) : un réalisateur est identifié de manière unique par son identifiant (entier, clé de la relation) et un nom

FILMS (idfilm, titre, annee, score, nbVotants, idRealisateur) : un film est décrit de manière unique par un identifiant (entier, clé de la relation), un titre, une année (première sortie du film), un score (moyenne de tous les votes pour ce film, compris entre 0 et 10), un nombre de votants et un réalisateur (entier positif, clé étrangère sur REALISATEURS)

DOCUMENTAIRES(idfilm, sujet) : un documentaire est un film, identifié par idfilm. Idfilm est une clé étrangère sur FILMS. L’attribut sujet donne le type de sujet traité dans le documentaire : ‘Animalier, ‘Historique’, ‘Voyage’, ‘Société’, ‘Animation’.

COURTMETRAGES(idfilm, subventionne, duree) : un court-métrage est un film, identifié par idfilm. Idfilm est une clé étrangère sur FILMS. L’attribut ‘subventionne’ indique si le film a bénéficé de subventions. Il peut prendre les valeurs ‘oui’ ou ‘non’. La durée du court-métrage est mémorisée en minutes. Un court-métrage ne peut excéder 59 minutes.

ACTEURS (idacteur, nomacteur) : un acteur est décrit de manière unique par un identifiant (entier, clé de la relation) et un nom

DISTRIBUTIONS (idFilm, idActeur, rang) : un acteur peut jouer un rôle dans zéro ou plusieurs films et un film a zéro ou plusieurs acteurs dans sa distribution. L'attribut rang indique le statut de l'acteur dans le film (rang=1 indique que l'acteur est la vedette du film). Le couple (idActeur, idFilm) est la clé de la relation DISTRIBUTIONS. idActeur est une clé étrangère sur ACTEURS et idFilm est une clé étrangère sur FILMS.

CINEMAS(nomCine, tel, adresse) : un cinéma est identifié par son nom (clé de la relation). Il possède une adresse et un téléphone.

CINESALLES(nomCine, noSalle, nbplaces) : une salle est identifiée par son numéro au sein d'un cinéma (nomCine clé étrangère sur CINEMA). Elle possède un nombre de place maximum (entier de 0 à 3000).

PROGRAMMES(nomCine, noSalle, semaine, heureDebut, IdFilm, nbEntreesCumulees) : dans une salle donnée (dans un cinéma donné donc - nomCine, noSalle clé étrangère sur CINESALLES), pour une séance donnée (semaine, heureDebut), il n'est programmé qu'un seul film (idFilm clé étrangère sur FILM). Le nombre d'entrées total de la séance pour cette semaine est mémorisé dans nbEntreesCumulees.

CRITIQUES(idCritique, nomCritique) : un critique de cinéma (i.e. la personne dont le métier est d'être critique) est identifié par un numéro (numCritique, entier, clé de la relation) et possède un nom

AVIS(numCritique, idFilm, note, commentaire) : un critique de cinéma (numCritique clé étrangère sur CRITIQUES) émet un avis sur un film (idFilm clé étrangère sur FILMS). Un critique de cinéma peut emettre au plus un avis sur un film donné. Un critique de cinéma peut emettre de 0 à n avis (sur des films différents donc). Un film peut avoir reçu de 0 à n avis par les critiques de cinéma. L'avis est donné sour la forme d'une note (décimal de 0 à 10) et d'un commentaire.

CLIENTS(idClient,nomClient) : un client possède un identifiant et est caractérisé par un nom.

BILLETS(noBillet, nomCine, salle, semaine, heureDebut, jour, idClient) : un billet réservé à l'avance est identifié par un numéro de billet (entier, clé de la relation). La réservation concerne une "programmation" précise, à savoir une salle de cinéma donné à une séance donnée (nomCine, noSalle, semaine, heureDebut clé étrangère sur PROGRAMMES) et précise le jour de la semaine effectif de la réservation ("lundi", "mardi", "mercredi", "jeudi", "vendredi", "samedi", "dimanche"). Afin de pouvoir retirer le billet en toute sécurité, l'identifiant du client est aussi mémorisé (idClient clé étrangère sur CLIENTS).

Question 1

En vous aidant des règles de passage d'une modélisation Entité/association à une modélisation relationnelle "à l'envers", proposer un schéma Entité/Association équivalent à ce schéma relationnel. Ce schéma devra comporter la description des entités (avec leurs propriétés), des associations (avec leurs propriétés) ainsi que les cardinalités minimum et maximum des associations binaires.

Comment faire ? appliquer "à l'envers" les étapes de transformation vues en cours

En pratique : 

1. Saisir votre schéma sous AMC*Designer (windows). Comment faire pour saisir le schéma E/A obtenu ?. Utiliser exclusivement le type entier pour les attributs numériques et le type chaîne de caractères taille variable (40 caractères maximum) pour les données chaînes de caractères.  Utiliser les mêmes noms, donnés dans le sujet, pour les relations et les attributs.
2. Produire le modèle relationnel abstrait (nommé MPD sous AMC*Designer). Vous devez retrouver le modèle relationnel abstrait donné en énoncé. Si ce n'est pas le cas, reprenez votre rétro-conception. Comment faire ?
3. Générer le code SQL. Comment faire ?

Question 2

Donner les principales contraintes d'intégrité associées à ce schéma (en français). On pourra distinguer entre les divers types de contraintes (mono-relation ou multi-relations, statiques ou dynamiques, ...).

En pratique : 

1. Supprimez les commentaires (lignes commençant par deux tirets) et les créations d'index (create index).
2. Analyser le code SQL produit et le cas échéant simplifier le et/ou modifier le.
3. Ajouter au moins deux contraintes d'intégrité (de type domaine par exemple). Voici une description de la syntaxe des commandes SQL pour ORACLE et pour postgresql. Vous avez aussi ici des exemples de syntaxe de création de schémas
4. Créer votre base de données
   Exécuter le fichier script SQL obtenu. Attention à votre nom d'utilisateur au sein de cette salle !!! Il vous reste ensuite à soumettre le formulaire. 
   Pour exécuter ces ordres, vous devez utiliser :
   • l'environnement complet phppgadmin
   • avec le bon login et mot de passe
5. Tester la création de votre base de données :
   1. en naviguant dan votre schéma sous postgresql
   2. en insérant des tuples dans votre base de données :
      1. avec des données valides pour vérifier que les insertions sont prises en compte
      2. avec des données invalides pour vérifier que les contraintes d'intégrité sont gérées.
      3. avec le script pré-écrit, si vous avez respecté les noms d'attributs  : tuples.

Que faire des autres contraintes (exprimées en français mais non supportées en SQL) ?

Question 3

On suppose que 2 classes d'utilisateurs ont accès à tout ou partie de ce schéma relationnel. L'administrateur de la base, qui initialise la base et donne notamment en régulièrement toutes les programmations de films. 

Et les clients potentiels qui peuvent consulter la base pour 

• connaître les films qui ont un bon score (>=8)
• connaître les films (donner les titres et les identifiants) qui ont un bon avis des critiques (note = 10)
• pour chaque séance de film pour laquelle des réservations ont été réalisées, on aimerait connaitre le nombre de places disponibles. Pour cela il faut faire une première vue qui donne pour chaque représentation (nomcine, nosalle, semaine, heuredebut, jour), le titre du film et le nombre de billets vendus. Cette vue pourra être ensuite utilisée pour comparer le nombre de billets vendus au nombre de places de la salle. 

Définir les vues et les droits associés à chaque classe d'utilisateur.

En pratique :

1. Proposer des schémas externes sous forme de vues SQL pour ces différentes classes d'utilisateurs. Attention à suivre les instructions d'affectation de comptes données en séance. Pour exécuter ces ordres, vous devez utiliser :
   • l'environnement complet phppgadmin
   • avec le bon login et mot de passe
2. Tester les vues ainsi définies en peuplant la base de données par des tuples (si vous ne l'avez pas déjà exécuté).
3. Donner les droits associés à chaque classe d'utilisateurs. Pour tester cela vous vous associerez avec un autre binôme (qui jouera le rôle de guichetier ou de client) qui tentera d'accéder à votre base avant l'affectation des droits puis après.
   • Sur postgresql, vous devez avant toute chose donner le droit USAGE au(x) binome(s) avec qui vous souhaitez travailler. Pour cela :
     • cliquer sur le nom de votre schéma ;
     • cliquer sur "Droits", en haut à droite de la fenêtre ;
     • cliquer sur "Accorder (GRANT)"
     • choisir le(s) binome(s), cocher le droit USAGE
     • cliquer sur "Accorder(GRANT)"
   • Vous pouvez maintenant accorder des droits sur vos objets (table ou vue) à ce(s) binome(s)

GRANT  { SELECT | INSERT | UPDATE | DELETE |   ALL }
    ON { nom_table | nom_vue  | ALL TABLES IN SCHEMA schema_name }
    TO nom_utilisateur [ WITH GRANT OPTION ]