Différences

Ci-dessous, les différences entre deux révisions de la page.

--- site:enseignement:licence:2i009:tme8_h2 [15/04/2020 16:35] – amann
+++ site:enseignement:licence:2i009:tme8_h2 [16/04/2021 09:52] (Version actuelle) – [Exercice 3] amine
@@ Ligne 1: / Ligne 1: @@
-====== TME 8: création de schémas – contraintes d'intégrité ======
+====== TME 8: création de schémas – contraintes d'intégrité (H2) ======
 <fc #ff0000>version pour H2</fc>
-Ce TME se base sur le TD8 et vise à illustrer la modification des tables et l'interaction avec les contraintes d'intégrité. On utilise un schéma Entreprise similaire à celui vu en TD et rappelé ici.
+===== Exercice 1 =====
+Créez le schéma Entreprise du TD sous H2 et associez aux tables les contraintes d'intégrité correspondantes.
   * **EMPLOYE**(__NumSS__, NomE, PrenomE, VilleE, DateNaiss)
@@ Ligne 10: / Ligne 13: @@
   * **GRILLE_SAL** (__Profil__, salaire)
-===== Exercice 1 =====
+===== Exercice 2 =====
-Créez le schéma du Entreprise sous H2 et associez aux tables les contraintes d'intégrité correspondantes.
+Tester les contraintes existantes sur le schéma précédent en exécutant les commandes spécifiées dans ce fichier: {{ :site:enseignement:licence:2i009:etu-tme8_ddl_ci_h2.pdf |TME8}}
+===== Exercice 3 (facultatif) =====
-Dans ce qui suit, il vous est demandé d'effectuer des insertions de n-uplets dans des tables.
+On considère le schéma relationnel suivant d’une application d’écoute de musique sur Internet de type Deezer :
-==== Syntaxe pour l'insertion ====
+   * ALBUM(albumId, albumTitre, parution, genre, label)
-<code sql>insert into Table values ('val1', 'val2', ....) ;</code>
+   * PISTE(pisteId, albumId, pisteTitre, interId, compId, durée)
+   * ARTISTE(artistId, nom, typeArtiste)
+   * COMPTE(email, tempsEcoute)
+   * ABONNÉ(email)
+   * PLAYLIST(email, pisteId, albumId, durée)
+   * AVIS(email, pisteId, albumId, score)
-Par exemple, pour insérer une employée 'LARS Anna', qui habite 'Paris' et qui est née le 25-
--1975, il suffit d'exécuter l'instruction ci-dessous
-<code sql>insert into employe values('21456','LARS', 'Anna','paris', PARSEDATETIME('25-08-1975', 'dd-mm-yyyy'), sysdate);</code>
-et de constater que la système retourne bien le message
+Chaque album (table ALBUM) est identifié par un identifiant (albumId) et on stocke le titre (albumTitre), l’année de parution, le genre et le label.  Il y a exactement trois genres ('jazz', 'classique', 'rock') et deux labels ('Sony', 'Universal') possibles.
-<code bash>1 ligne(s) insérée(s)</code>
+Les artistes sont stockés dans la table ARTISTE qui contient l’identifiant (artistId) et le nom de l’artiste (32 charactères max). On distingue entre trois types d'artistes ('compositeurs', 'interprêtes', 'interprêtes-compositeurs').
+Les pistes de chaque album (table PISTE) sont identifiées par le numéro de piste (pisteId) et l’identifiant de l’al bum. On stocke le titre de la piste (obligatoire), l’identifiant de l’interprète (interId), l’identifiant du compositeur (compId) et la durée en secondes (spérieur à 0).
+Tous les comptes utilisateurs (table COMPTE) sont identifiés
+par leur email (32 charactères max) et on stocke leur temps d'écoute global (0 par défaut). Les comptes avec un abonnement sont identifiés dans la table ABONNE.
+Tous les utilisateurs peuvent écouter des chansons et donner un avis (score optionnel entre -5 et 5). Uniquement les abonnées peuvent maintenir une playlist (table PLAYLIST).
+Question 1 : Définissez le schéma H2 avec contrainte de clé (PRIMARY KEY et UNIQUE), contrainte référentielle (FOREIGN KEY), contrainte de domaine (valeurs possibles des attributs, NOT NULL) et contrainte de tuple (condition liant les valeurs des différents attributs pour tous les n-uplets
+de la table).
+Question 2: Est-ce qu'il est possible qu'un compositeur d'une piste (table PISTE) soit un interprête dans la table ARTISTE ? Si oui, comment faudrait-il modifier le schéma pour éviter cette incohérence.
-==== Syntaxe pour la suppression ====
-<code sql>delete from Table ;</code>
-===== Exercice 2 =====
-Insérez dans chaque table au moins un n-uplet qui vérifie les contraintes d'intégrité. Vous avez la liberté de choisir les valeurs que vous voulez.
-===== Exercice 3 =====
-Proposez des insertions qui violent les contraintes d'intégrité définies pour chaque table.
-Par exemple, l'instruction ci-dessous:
-<code sql>insert into employe values(null,'LARS', 'Anna','paris', PARSEDATETIME('25-08-1975', 'dd-mm-yyyy'), sysdate);</code>
-viole la contrainte de clé primaire de Employe car elle tente d'insérer un employé sans valeur pour l'attribut clé primaire
-Si vous tentez d'exécuter cette instruction, vous constaterez l'erreur
-<code bash>Violation d'index unique ou clé primaire: "PUBLIC.PRIMARY_KEY_C ON PUBLIC.EMPLOYE(NUMSS) VALUES 1"
-Unique index or primary key violation: "PUBLIC.PRIMARY_KEY_C ON PUBLIC.EMPLOYE(NUMSS) VALUES 1"; SQL statement:
-INSERT INTO employe VALUES(21456,'LARS','Anna',NULL,'Paris',PARSEDATETIME('25-08-1975', 'dd-mm-yyyy'), sysdate) [23505-199] 23505/23505 </code>
-On peut utiliser une syntaxe alternative pour les insertions en spécifiant les attributs dont on renseigne les valeurs comme indiqué ci-dessous
-<code sql>insert into employe (NumSS, NomE, PrenomE, villeE, dateNaiss) values (21456,'LARS','Anna','Paris',PARSEDATETIME('25-08-1975', 'dd-mm-yyyy'));
- ;</code>
-Répondre aux questions suivantes :
-  - Proposer une insertion dans la table Employé qui ne respecte pas la contrainte de clé primaire.
-  - Proposer une insertion dans la table Employé qui ne respecte pas la contrainte de limite d'âge.
-  - Proposer une insertion dans la table Employé qui ne respecte pas la contrainte de longueur de l'attribut NumSS.
-  - Proposer une insertion dans la table Employé qui ne respecte pas la contrainte sur les villes possibles.
-  - Insérer dans la table Employé deux employés avec le même nom et le même prénom.
-  - Proposer une insertion dans la table Grille_SAL qui ne respecte pas la contrainte C8.
-  - Proposer une insertion dans la table Projet qui ne respecte pas la contrainte référentielle vers Employe : insérer un responsable de projet qui n'est pas dans la table Employé
-  - Proposer une insertion dans la table Embauche qui ne respecte pas une des contraintes référentielles : par exemple, associer un employé existant à un projet qui n'existe pas.