Tutoriel de Bases de Données Relationnelles

TP à distance JMerise et SQLite : rétro conception et 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 (chaine de 50 caractères maximum).

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 (chaine de 50 caractères maximum), une année (entier, première sortie du film), un score (moyenne de tous les votes pour ce film, compris entre 0 et 10), un nombre de votants (entier) et un réalisateur (entier, 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 (chaine de 50 caractères maximum).

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, chaine de 50 caractères maximum). Il possède une adresse (chaine de 50 caractères maximum) et un téléphone (chaine de 50 caractères maximum). 

CINESALLES(nomCine, noSalle, nbplaces) : une salle est identifiée par son numéro au sein d'un cinéma (nomCine clé étrangère sur CINEMAS). 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). Il s'agit en fait d'un créneau de planification qui se répète tous les jours de la semaine. Le nombre d'entrées total de de cette programmation pour cette semaine est mémorisé dans nbEntreesCumulees. En aucun cas nbDEntreesCumulees ne peut etre considéré comme étant le nombre d'entrée d'une séance précise.

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 (chaine de 50 caractères maximum).

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 (chaine de 50 caractères maximum).

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

BILLETS(noBillet, nomCine, salle, semaine, heureDebut, jour, idClient) : un billet est identifié par un numéro de billet (entier, clé de la relation). La réservation concerne une "programmation", sur une séance 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).

Préalable à la réalisation du TP 

• Préparez un document de compte-rendu de ce TP, avec vos noms de binome
• Téléchargez JMerise : outil permettant de saisir un schéma entité/association et de le traduire en modèle relationnel, puis en script SQL. Regardez la démo d'utilisation de JMerise avant de commencer.
• Vous aurez aussi besoin de SQLite, par exemple la version online, la plus simple d'utilisation.

Question 1 - Rétro-conception

En vous aidant des règles de passage d'une modélisation Entité/association à une modélisation relationnelle "à l'envers", et des équivalences schéma entité/association - diagramme de classe :

1. Proposez un diagramme de classe UML. Ce schéma devra comporter la description des classes (avec leurs propriétés), des associations (avec leurs propriétés), des héritages, ainsi que les multiplicités minimum et maximum des associations. Ce schéma peut être manuscrit ou saisi à l'aide d'un outil dédié. Ajoutez ce diagramme à votre compte-rendu. Si vous avez émis des hypothèses, précisez les brièvement.
2. Proposez un schéma Entité/Association équivalent à ce diagramme de classe. 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. Utilisez exclusivement le type entier pour les attributs numériques et le type chaîne de caractères taille variable (50 caractères maximum) pour les données chaînes de caractères.  Utilisez les mêmes noms, donnés dans le sujet, pour les relations et les attributs. Ce schéma doit être saisi dans l'outil JMerise. Ajoutez ce schéma à votre compte-rendu. 

 

Question 2 - Traduction 

1. Produire le modèle relationnel abstrait (nommé MPD sous JMerise, cliquez sur Merise/convertir le MCD). Vous devez retrouver le modèle relationnel abstrait donné en énoncé. Pensez à bien fixer vos paramètres & configuration avant la conversion, comme dans la vidéo pour ne pas hériter des attributs de l'entité mère dans l'entité fille et avoir comme cible SQLite ;
2. Vérifiez ce modèle relationnel en le comparant au schéma donné en sujet. Si les schémas ne sont pas équivalents, reprenez votre rétro-conception (question 1). Si les schémas sont équivalents, ajoutez ce schéma à votre compte-rendu.
3. Passez sur la fenêtre contenant le code SQL. Lisez le script, vérifiez la traduction de votre héritage. Si ce script vous convient, ajoutez le à votre compte-rendu.

Question 3 - Contraintes d'intégrité et création de la base de données

1. Donnez 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, ...). Ajoutez cela à votre compte-rendu.
2. Choisissez deux contraintes d'intégrité (de type domaine par exemple) et écrivez les ordres SQL correspondants. Voici ici des exemples de syntaxe de création de schémas. Modifiez votre script en conséquence. Ajoutez ces contraintes à votre compte-rendu. Que faire des autres contraintes (exprimées en français mais non supportées en SQL) ?
3. Créez votre base de données. Exécutez le fichier script SQL obtenu sur SQLite , enrichi de vos contraintes d'intégrité (copiez le script dans la fenêtre au centre en haut et cliquez sur "Run").
   1. Testez la création de votre base de données :
      1. en naviguant dans votre schéma
      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.
4. Peuplez votre base de données avec le script pré-écrit, si vous avez respecté les noms d'attributs  : tuples.

Question 4 - Vues

1. Proposez des schémas externes sous forme de vues SQL pour les demandes d'utilisateurs suivantes :
   • 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. 
2. Testez les vues ainsi définies en ayant peuplé la base de données par des tuples (si vous ne l'avez pas déjà exécuté) ;
3. Ajoutez le script de création de ces vues à votre compte-rendu.

1. Imaginez un petit scénario de  transmission de droits. Ecrivez l'histoire en français.
2. Donnez deux à trois droits associés à ccette histoire en SQL.  SQLite ne gère pas les transmission de droits. Ecrivez simplement ces droits dans votre compte-rendu

En pratique :

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

Finalisation

Déposez votre compte rendu sur l'espace "devoir" du TP sur moodle.