Entrepôts de données

Pierre Jourlin

"bon c'est les titres qu'ils nous ont mis, on va pas du tout parler d'entrepôts de données mais de données elles-mêmes"
"je vais aussi faire le lien avec l'IA, et je vais aussi fusionner un peu les deux"

"je vais évaluer sur de la prise de note individuelle mais aussi des compte-rendus de TP"

les données ne s'expriment pas comme les idées s'expriment en langue "naturelle" :

dans la langue naturelle, on a toujours des ambiguïtés que nous savons décoder automatiquement à un niveau inconscient ("si je vous cite Faites entrer l'accusé, "elle est un peu la mère de ses sœurs", vous comprenez qu'on ne parle pas de la mère biologique")
implicite : on n'a pas besoin de tout dire
flou, "c'est un peu comme l'implicite"
"il y a plein d'autres problèmes"
"tout ça ne va pas marcher avec un ordinateur" : il faut de l'explicite, enlever les ambiguïtés, etc.

cahier des charges : "on dit en langue naturelle à un informaticien ce qu'il doit faire, on décrit la problématique et les données,et il le transforme en format informatique"
"on va voir les 3 principaux" : JSON, XML et SQL
"vous verrez que les 3 sont équivalents : ils ont la même puissance d'expression, mais ils sont plus pratiques dans certains cas que d'autres, c'est une question de contexte"

imbrication : "vous pouvez très bien écrire , ça n'a aucun sens mais ce n'est pas un problème, c'est comme en langage naturel"

le XML est un langage de balises
"si vous ne fermez pas une balise ouverte, vous aurez une erreur de syntaxe, comme quand vous écrivez un verbe transitif sans complément en langage naturel"

<vendeur>
	<nom>Jourlin</nom>
	<prénom>Pierre</prénom>
	<adresse>
		<ville>Avignon</ville>
		<pays>France</pays>
	</adresse>
	<client>
		<nom>Jacques</nom>
		<prénom>Paul</prénom>
		<adresse>
			<ville>...</ville>
			<pays>...</pays>
		</adresse>
	</client>
	<client>
		<nom>Sanjuan</nom>
		<prénom>Eric</prénom>
		<adresse>
			<ville>...</ville>
			<pays>...</pays>
		</adresse>

le JSON est fait d'objets : chaînes de caractères, nombres, listes... associés dans des couples attribut-valeur

{
  "vendeur": {
    "nom": "Jourlin",
    "prénom": "Pierre",
    "adresse": {
      "ville": "Avignon",
      "pays": "France"
    },
    "clients": [
      {
        "nom": "Jacques",
        "prénom": "Paul"
      },
      {
        "nom": "Sanjuan",
        "prénom": "Éric"
      }
    ]
  }
}

la valeur d'un attribut peut être n'importe quel objet, y compris un couple attribut-valeur, ou une liste de couples, etc. "vous voyez la récursivité qui arrive"
"vous voyez aussi que contrairement au langage naturel, il n'y a pas d'implicite"
"mais il y a des normes : une fois que j'ai défini un objet JSON, je n'ai pas besoin de le redéfinir"

"ce qui est long, c'est de formaliser le monde : il va falloir simplifier ça, pour qu'il n'y ait plus d'ambiguïté et pouvoir transférer ça à la machine"
"le format informatique, c'est un mélange de langage naturel et de droit"

le SQL est fait de tableaux (cf. le cours de BDD en S1) :

Personne(IDpersonne, nom, prénom, IDadresse) (IDadresse fait référence au tableau Adresse, c'est une clé étrangère)
Adresse(IDadresse, ville, pays)
Vendeur(IDvendeur, IDpersonne)
Client(IDclient, IDpersonne)

Tableau "Personne" :

IDpersonne	nom	prénom	IDadresse
1	Jourlin	Pierre	1
2	Jacques	Paul	2
3	Sanjuan	Eric	3

Tableau "Adresse" :

IDadresse	ville	pays
1	Avignon	France
2	Paris	France
3	Madrid	Espagne

Tableau "Vendeur" :

IDvendeur	IDpersonne
1	1

Tableau "Client" :

IDclient	IDpersonne
1	2
2	3

"en général, les entreprises ont tout dans une base de données interne en SQL ; quand elles veulent en transmettre des bouts à une autre entreprise, elles le convertissent en JSON ; quand elles veulent le transmettre à d'autres humains, elles peuvent le transformer en XML, qui pourra servir ensuite à générer une page HTML, et cætera"

deux manières d'accéder à une base de données SQL :

interface web (phpPgAdmin)
interface en ligne de commande (psql ou RStudio)
- par ex., pour créer un tableau sans données : CREATE TABLE "mgd1" (id INTEGER, prenom TEXT, nom TEXT, adresse_id INTEGER);
- pour voir les tableaux : \d

"je vous propose un scénario de création d'un site d'e-commerce" (= un ERP)
"je vais vous laisser la main sur le choix de la situation"

la première chose à faire : modéliser le réel, en s'appuyant sur la logique des propositions ("pour vérifier un fait dans votre base de données, vous pouvez poser la question en SQL, et on vous répondra Vrai ou Faux")
puis on passe du modèle conceptuel au modèle informatique

modèles conceptuels :

entité-association ("celui qui a le mieux survécu")
la notion d'entité (= quelque chose de délimité) est subjective : "on peut considérer une personne comme une entité, on aura plus de mal avec un nuage parce qu'il y n'y a pas de limite franche"
"autre exemple d'entité : une facture"
"il y en a d'autres"

Personne
Nom
Prénom
~~Âge~~ Date de naissance
ID
etc.

"on préfère mettre la date de naissance que l'âge, parce qu'on peut calculer le second à partir du premier"

Film
Titre
Réalisateur(s)
Durée
Date de réalisation
Genre
ID

modèles infomatiques : une fois qu'on a un bon modèle conceptuel, on peut facilement passer d'un format (SQL, JSON, XML...) à un autre

JSON ne va pas servir pour manipuler les données ("on peut cela dit"), mais surtout pour parler à d'autres machines (en faisant des appels à une API)

CREATE TABLE Personne(Nom VARCHAR(100),
					  Prenom VARCHAR (100),
					  DateNaissance DATE,
					  ID INTEGER PRIMARY KEY)

CREATE TABLE Realise
(ID_Pers INTEGER Foreign Key references Pers(ID),
 ID_Film INTEGER Foreign Key references Fiche(ID),
Date_Real DATE)

ID_Pers	ID_Film	Date_Real
1	2
1	3
2	3

"y'a des entreprises qui ne s’embarrassent pas à créer le modèle conceptuel : ceux qui font ça, ils vont très vite mais ils s'endettent"
"c'est une erreur de croire que s'adresser à un être humain, c'est comme s'adresser à une machine"

clé primaire : identifie un enregistrement unique
mais certaines clés ne sont pas des clés primaires (ex. clés étrangères) : on utilise alors une clé unique
"ça sera moins efficace que la clé primaire, oubliez les clés uniques, on s'en servira pas"

Créer un schéma "cinema" et des tables pour chaque entité dans la base de données PostgreSQL :

via l'interface graphique phpPgAdmin
via une requête SQL

la table a_réalisé permet de modeler la relation entre les films et les personnes (lesquelles peuvent avoir réalisé aucun, un, ou plusieurs films)
PostgreSQL permet de définir les clés primaires et étrangères (dans a_réalisé, les clés IDF et IDP sont toutes les deux primaires)
une fois les clés définies, on peut effectuer une jointure naturelle :

SELECT -- sélectionner
* -- joker pour tout sélectionner
FROM -- depuis les tables suivantes
"Personne" -- nom de la table
NATURAL JOIN "a_réalisé" -- réalise la jointure en fonction des clés existantes
NATURAL JOIN "Film" -- deuxième jointure

"attention aux conventions, vous voyez que j'ai des noms de tables avec des majuscules et d'autres non, ça ne va pas être pratique"

Ajouter une table pour les acteurs ("cette fois-ci je ne vous aide pas")
Importer une BDD externe (https://e-uapv2023.univ-avignon.fr/pluginfile.php/89132/mod_resource/content/1/cinema.sql)
Pour chacune des questions suivantes :
1. traduire la requête en SQL via un LLM
2. tester la requête SQL ainsi obtenue
3. corriger la requête
4. tester la requête corrigée

Quels sont les titres des films dont le genre est Drame ?
Quels films sont projetés au cinéma Le Fontenelle ?
Quels sont les noms et prénoms des réalisateurs ?
Quels sont les noms et prénoms des acteurs ?
Quels sont les noms et prénoms des acteurs qui sont également réalisateurs ?
Quels films (titres) ont été projetés en 2002 ?
Donnez le titre des films réalisés par Lars von Trier.
Quels sont les réalisateurs qui ont réalisé des films d’épouvante et des films dramatiques ?
Quels sont les titres des films où Nicole Kidman a joué un rôle et qui ont été projetés au cinéma Le Fontenelle ?
Quels sont les acteurs qui n’ont pas joué dans des films dramatiques ?
Quels sont les noms et prénoms des individus dont le prénom est à la fois celui d’un acteur et celui d’un réalisateur sans qu’il s’agisse de la même personne ?
Quels acteurs a-t-on pu voir au cinéma Le Fontenelle depuis l’an 2000 ?
Quels sont les films qui ont encore été à l’affiche 5 années après leur sortie ?

Réponses : Entrepôts de données/ChatGPT 1 / Entrepôts de données/ChatGPT 2

Info

en demandant toutes les requêtes en une seule demande, GPT-4 propose deux réponses
les réponses auxquelles il manque des guillemets échouent mais elles sont syntaxiquement exactes
la réponse à la question 12 inclut un "DISTINCT" alors que l'intitulé ne demande pas d'éviter les doublons
La réponse 1 à la question 5 renvoie des résultats erronés

Réponses :

Ne pas confondre guillemets doubles (attributs) et guillemets simples (valeur)
on utilise WHEREpour ne joindre les tables que là où se trouvent les mêmes valeurs
on peut utiliser un NATURAL JOIN, car l'attribut "Num-Ind" existe dans les deux tables
(le prof a fait un NATURAL JOIN mais j'ai utilisé une sous-requête)
il faut joindre Film deux fois (avec deux alias) et trois clauses WHERE
il faut exclure les acteurs qui ont joué dans un film dont le Genre n'est pas Drame (en utilisant une sous-requête avec EXCEPT)
"on va sauter celui-là"

"il faut bien distinguer le format de son interprétation"
"j'essaie de vous faire réaliser trois concepts : la réalité, le format et l'interprétation du format"
certains formats empêchent certains interprétations ("dans le HTML, il n'y a pas de sémantique, les balises ne servent qu'à mettre en forme") ; certains formats peuvent avoir tous types d'interprétations

XPath : langage formel qui permet de décrire le chemin d'accès au contenu d'une balise XML

les URL contiennent deux langages de chemins : un où les arêtes sont représentées par des points ("et où on avance du particulier au général"), un où les arêtes sont représentées par des slashs "et on se déplace du général au particulier, on est d'accord c'est totalement absurde"

TP XML/XPath

tester des expressions sur https://extendsclass.com/xpath-tester.html avec le code HTML fourni sur Moodle :

/html/body/p/author/text()
//date/text()
//h2[@class="chapter"]

avec cd.xml :

/CDlist/CD[publisher="Deutsche Grammophon"]/performance[soloist and orchestra="London Symphony Orchestra"]/soloist/text() ("on n'est pas obligé de mettre solist andmais c'est plus pédagogique")
"on n'a pas de balise pour identifier le CD, il faut en rajouter une (par ex. <title>)"

avec booker.xml :

/booker/award[5]/title/text(), "vous voyez qu'on peut mettre autre chose que des booléens entre les crochets"
/booker/award[year=2000]/title/text()
/booker/award[title="Possession"]/author/text()
/booker/award[author="J M Coetzee"]/title/text()
/booker/award[year>=1995]/author/text() ("c'est un peu ambigu parce qu'on a pas précisé supérieur ou égal")
"la fonction count() ça dépend de l'interpréteur, on va sauter ça"

<?xml></xml> : "ce sont des métadonnées"

"on n'avait pas fait SPARQL et Wikidata ?"
"on ne va pas prendre Wikidata, on va plutôt faire la BnF"

Données	Langage
Tableaux	SQL
XML	XPath
JSON	"ça n'existe pas"
RDF	SPARQL

SELECT
	DISTINCT ?person
	?isni #ce ne sont pas des noms de colonnes, mais des inconnues
WHERE {
  ?person
	isni:identifierValid ?isni ; #nom du catalogue de relation:nom de la relation
	foaf:focus ?identity.
  ?identity
	foaf:familyName "Hugo";
    foaf:givenName "Victor".
}