Aller au contenu principal
Physique, Chimie et Informatique > Terminales Spé. NSI

Bases de données

Le développement des traitements informatiques nécessite la manipulation de données de plus en plus nombreuses. Leur organisation et leur stockage constituent un enjeu essentiel de performance. Le recours aux bases de données relationnelles est aujourd’hui une solution très répandue. Ces bases de données permettent d’organiser, de stocker, de mettre à jour et d’interroger des données structurées volumineuses utilisées simultanément par différents programmes ou différents utilisateurs. Cela est impossible avec les représentations tabulaires étudiées en classe de première.

Des systèmes de gestion de bases de données (SGBD) de très grande taille (de l’ordre du pétaoctet) sont au centre de nombreux dispositifs de collecte, de stockage et de production d’informations. L’accès aux données d’une base de données relationnelle s’effectue grâce à des requêtes d’interrogation et de mise à jour qui peuvent par exemple être rédigées dans le langage SQL (Structured Query Language). Les traitements peuvent conjuguer le recours au langage SQL et à un langage de programmation.

Il convient de sensibiliser les élèves à un usage critique et responsable des données.

Au programme

Contenus Capacités attendues Commentaire
- Modèle relationnel : relation, attribut, domaine, clef primaire, clef étrangère, schéma relationnel. - Identifier les concepts définissant le modèle relationnel. - Ces concepts permettent d’exprimer les contraintes d’intégrité (domaine, relation et référence).
- Base de données relationnelle. - Savoir distinguer la structure d’une base de données de son contenu. Repérer des anomalies dans le schéma d’une base de données. - La structure est un ensemble de schémas relationnels qui respecte les contraintes du modèle relationnel.
- Les anomalies peuvent être des redondances de données ou des anomalies d’insertion, de suppression, de mise à jour.
- On privilégie la manipulation de données nombreuses et réalistes.
- Système de gestion de bases de données relationnelles. - Identifier les services rendus par un système de gestion de bases de données relationnelles : persistance des données, gestion des accès concurrents, efficacité de traitement des requêtes, sécurisation des accès. - Il s’agit de comprendre le rôle et les enjeux des différents services sans en détailler le fonctionnement.
- Langage SQL : requêtes d’interrogation et de mise à jour d’une base de données. - Identifier les composants d’une requête.
- Construire des requêtes d’interrogation à l’aide des clauses du langage SQL : SELECT, FROM, WHERE, JOIN.
- Construire des requêtes d’insertion et de mise à jour à l’aide de : UPDATE, INSERT, DELETE.
- On peut utiliser DISTINCT, ORDER BY ou les fonctions d’agrégation sans utiliser les clauses GROUP BY et HAVING.

Documents

Sous-sections de Bases de données

Physique, Chimie et Informatique > Terminales Spé. NSI > Bases de données

Introduction aux bases de données

Qu’est-ce qu’une base de données ?

Le nombre d’informations disponibles et les moyen de les diffuser sont en constante progression. La croissance du Web a encore accru ce développement en fournissant l’accès à des bases de données gigantesques et très diverses par l’intermédiaire d’une interface commune (Amazon, Google, Facebook, ...).

Notion de base de données

Tout le monde a une idée naturelle de ce que peut être une base de donnée, nous stockons, sur nos disques durs, non seulement des fichiers musicaux, mais aussi des informations relatives à ces derniers : noms des artistes, des albums, dates d’enregistrements, noms et numéros des morceaux, parfois noms des musiciens, ...

On remarque qu’une caractéristique des données contenues dans une base de données est qu’elles doivent posséder un lien entre elles. En effet, des données choisies au hasard ne peuvent clairement pas constituer une base de données. Une base de données est donc une représentation partielle et (très) simplifiée du monde réel, obtenue par un processus de modélisation.

À retenir

Une base de données est un ensemble d’informations structurées et organisées, mémorisées sur un support permanent.

Info

La définition d’une base de données n’indique pas comment sont stockées les informations, une organisation consistant en l’utilisation de fichiers pourrait tout à fait convenir. Cependant, bien que simple à mettre en œuvre, cette solution pose de nombreux problèmes :

Lourdeur d’accès aux données

En pratique, pour chaque accès aux données (lecture ou écriture), il faut écrire un programme.

Manque de sécurité

Si tout programmeur peut accéder aux données, il est impossible d’assurer leur intégrité.

Pas de contrôle de concurrence

Si plusieurs utilisateurs accèdent au mêmes fichiers, en même temps, des problèmes de concurrence d’accès se posent : quelle modification sera retenue ? quelle valeur sera lue ?

Pour manipuler une base de données, on utilise donc généralement un logiciel spécialisé : un SGBD (Système de Gestion de Bases de Données)[^2].

À retenir

On appelle SGBD (Système de Gestion de Bases de Données) un logiciel complexe, de haut niveau, qui permet de manipuler les informations stockées dans une base de données.

À retenir

On appelle « Système d’information » l’ensemble composé par les bases de données et le SGBD.

Utilisation d’une base de données

La création d’une base de données s’effectue avec un objectif : elle doit permettre de retrouver de l’information par son contenu en se fondant sur des critères de recherche. Par exemple, en 1959, dans quels morceaux et quel(s) album(s) de Miles Davis apparaît John Coltrane ?

Info

La grande différence entre une base de données et un programme écrit pour lire des information dans des fichiers, est qu’une base de donnée doit permettre de répondre à des questions auxquelles ses concepteurs n’avaient pas forcément pensé.

À retenir

Une base de données doit permettre d’assurer l’indépendance entre les données et leur traitement.

Pour réaliser l’indépendance annoncée dans la définition précédente, on associe à la base de données une description que l’on appelle métadonnée (ou catalogue). Cette description décrit la structure interne de la base de données[^3]. En plus de la structure et du type des données, on stocke également à cet endroit les informations concernant les règles de cohérence des données (cf. section suivante sur la qualité d’une base de données).

NumCD Titre Interprète Label Année
1 Bring On the Night Sting 1986
3 Superstition Stevie Wonder Motown 1972
2  Paranoid Android Radiohead Parlophone 1997

Extrait d’une base de données pour une collection de CD musicaux.

Dans l’exemple donné dans le Tableau 1., en plus des informations, la base de données contiendrait les métadonnées suivantes :

  • « NumCD » est un nombre entier supérieur à 1 ;

  • « Titre » est une chaîne de caractères de taille 50 ;

  • « Interprète » est une chaîne de caractères de taille 30 ;

  • « Label » est une chaîne de caractères de taille 20 ;

  • « Année » est un nombre entier supérieur à 1900 et inférieur à l’année en cours.

Remarque
On note que tous les « champs » ne sont pas forcément renseignés dans l’exemple donné.
À retenir

On doit donc retenir que l’utilisateur ou l’application utilisatrice du système d’information ne doit pas avoir connaissance de la structure interne de ce dernier pour l’interroger, on réalise une abstraction des données. C’est la raison pour laquelle on utilise une description des données sous la forme d’un modèle pour permettre une restitution la plus efficace possible de l’information.

Qualité d’une base de données

Info

L’un des objectifs de création d’une base de données est de pouvoir retrouver les données par leur contenu. Il faut donc s’assurer que ces dernières sont de « bonne qualité ».

Cohérence des données contenues dans la base

Par exemple, si un champ doit contenir la qualité d’une personne sous la forme « Monsieur », « Madame », « Mademoiselle », l’entrée de « M. » doit être impossible de façon à ne pas induire des erreurs de recherche sur ce Champ.

Absence de redondance

Dans un carnet d’adresses le stockage du nom de la ville et du code postal pour chaque personne représente une redondance : la même information est stockée plusieurs fois, ce qui consomme de la place inutilement.

Le modèle relationnel des données s’accompagne d’outils qui permettent de détecter, dans une certaine mesure, les problèmes présentés dans cette section.

Modèles de données

À retenir

Les modèles de données correspondent à la manière de structurer l’information dans une base de données.

Ces modèles reposent sur des recherches initiées à partir des années 1960. Parmi tous les modèles développés et utilisés, on ne présentera ici (très succinctement) que deux modèles : le modèle hiérarchique et le modèle relationnel.

Modèle hiérarchique

Avertissement

Ce modèle n’est présenté ici qu’à des fins historiques. Il n’est pas au programme.

Ce modèle est l’un des plus anciens modèles. À l’époque de sa définition, le traitement de l’information était encore très lié à l’organisation des fichiers sur une machine. Les modèles conceptuels des données étaient donc des graphes ou des arbres.

Le modèle « hiérarchique » propose donc une classification arborescente des données, à la manière d’une classification scientifique. Dans ce modèle, chaque enregistrement n’a qu’un seul possesseur, ce qui limite les modélisations de la réalité possibles.

Des SGBD de type « hiérarchique » ou « réseau » (extension du modèle précédent permettant des relations transversales) sont toujours utilisés, pour des raisons d’efficacité, lorsque la structure de données s’y prête.

Le système d’exploitation Windows utilise « une base de registre » pour stocker tous les paramètres de configuration (système et logiciels). Le modèle de données choisi est le modèle « hiérarchique ». La donnée Favoris, par exemple, a pour chemin : HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Applets\Regedit\Favorite (on peut accéder à cette donnée grâce au logiciel Regedit).

Modèle relationnel

Ce modèle a été proposé en 1970 par E. F. Codd dans un article resté célèbre : « A Relational Model of Data for Large Shared Data Banks ». Il cherchait à créer un langage d’interrogation de bases de données plus proche du langage naturel. Dans cette optique, il a fondé sa recherche sur des concepts mathématiques rigoureux, tels que la théorie des ensembles et la logique du premier ordre.

Info

Le modèle « relationnel » permet de modéliser les informations contenues dans les bases de données en utilisant des relations, c’est à dire un ensemble d’attributs.

Présentation du modèle relationnel

Qu’est-ce qu’une relation ?

À retenir

On appelle relation unobjet de la vie réelleauquel on attache un ensemble d’attributs.
Par exemple, un employé possède un nom, un prénom, un matricule, travaille dans un service et a été embauché à une certaine date.

  • Chaque instance d’une relation est appelée un tuple ou n-uplet (en français).

  • Les relations sont d’ordinaire représentées sous la forme de tables à deux dimensions, et on confond les deux concepts. On confond de même ligne dans la table et tuple.

  • Par définition, chaque tuple d’une relation est unique, et est identifié par un attribut ou une combinaison de plusieurs attributs qui forme la clef primaire.

  • L’ordre des tuples dans la relation n’est pas significatif.

  • Le nombre de champs de la relation s’appelle le degré de la relation tandis que le nombre de tuples dans la relation s’appelle la cardinalité de la relation.

Exemple
Base de données formée par deux relations.
Titre Directeur Acteur
Casablanca M. Curtiz Humphrey Bogart
Casablanca M. Curtiz Peter Lore
Les 400 coups F. Truffaut J.-P. Leaud
Star Wars G. Lucas Harrison Ford

Relation Film

Titre  Salle Heure
Casablanca Lucernaire 19:00
Casablanca Studio 20:00
Les 400 coups Sel 20:30
Star Wars Sel 22:15

Relation Séance

  1. Quelle est la cardinalité de la relation Film ?

    Réponse

    La cardinalité d’une relation est le nombre de tuples (ou de lignes) qu’elle renferme. Ici la cardinalité est égale à 4.

  2. Quel est le degré de la relation Séance ?

    Réponse

    Le degré d’une relation est le nombre de champs qui la définissent. Ici, c’est 3.

  3. Quels sont les attributs (champs) de la relation Film ?

    Réponse

    Les attributs de la relation Film sont : Titre, Directeur, Acteur.

  4. Indiquer un tuple de la relation Séance.

    Réponse

    LA cardinatlité de la relation étant égale à 4, on a le choix entre 4 réponses. (Casablanca, Lucernaire, 19:00) fait partie de ces réponses possibles.

Qu’est-ce que le calcul relationnel ?

Info

On accède aux données en utilisant comme langage le calcul relationnel. Le calcul relationnel (très fortement inspiré de la logique du premier ordre en mathématique) s’appuie sur des variables, des prédicats, des connecteurs logiques (Et, Ou, Implique, ...) et des quantificateurs existentiels (Il existe, Pour tout, ...).

Exemple
  • Existe-t-il un film ayant pour acteur Humphrey Bogart diffusé au cinéma Studio ?
  • Existe-t-il au moins un film de M. Curtiz projeté au cinéma Sel ?
  • F. Truffaut a-t-il fait tourner l’acteur Harrison Ford ?

Qu’est-ce qu’une vue ?

Les relations constituant la base de données sont complétées à l’aide de vues :

À retenir

Une vue est une relation, qui au lieu d’être stockée dans la base de données, est le résultat d’une requête.

Une vue peut ensuite être réutilisée exactement comme s’il s’agissait d’une relation stockée dans la base de données.

  1. Quelles sont les films, les salles et les horaires des séances dans lesquels on peut trouver l’acteur Humphrey Bogart ?
    Réponse

    La réponse à cette requête est une vue :

    Casablanca Lucernaire 19:00
    Casablanca Studio 20:00

Qu’est-ce que le langage SQL ?

Toujours dans le cadre de sa recherche, Codd a mis au point, en même temps que le modèle de données relationnel, un langage d’interrogation des données SEQUEL, qui est par la suite devenu SQL (Structured Query Language). La normalisation du langage SQL, dès 1986 par l’ANSI (institut de normalisation américain), puis par l’ISO (organisation internationale de la normalisation) a assuré, pour une grande partie, le succès du modèle relationnel. Les requêtes écrites pour un SGBD fonctionnent en général sans trop de modifications avec un autre SGBD.

Exemple
Traduction en langage SQL de l’interrogation de la question 5.
SELECT Titre, Salle, Heure 
FROM Film F, Séance S
WHERE  F.Titre = S.Titre AND Acteur = "Humphrey  Bogart";

Systèmes de gestion de bases de données

Les SGBD sont des systèmes complexes qu’il n’est pas question de décrire en détail.

Rendre efficace la recherche d’une information dans un fichier informatique n’est pas simple

Comme on l’a déjà vu cette année, lorsqu’on utilise un fichier pour stocker de l’information, il est nécessaire de prévoir un découpage de celui-ci par enregistrements, souvent de taille fixe. Pour passer d’un enregistrement à l’autre, il faut alors avancer la « tête de lecture » de la taille d’un enregistrement.

Info

Dans une base de données, on recherche les données par leur contenu. Pour retrouver l’une d’entre elles, il faut donc parcourir tous les enregistrements un à un (recherche séquentielle) et en examiner le contenu, ce qui est relativement peu efficace.

Une alternative à ce parcours séquentiel est la construction de tables descriptives afin d’accélérer l’accès aux données :

  • Une première table permet l’accès direct à un enregistrement par une « clé » associée à l’adresse mémoire de l’enregistrement.

  • Une seconde table contient l’ordre relatif des enregistrements ordonnés suivant les valeurs d’un champ : on appelle cette table un index. Cette seconde table permet d’employer des méthodes de recherche par le contenu du champ indexé beaucoup plus performantes. La recherche dichotomique est l’une d’entre elles.

On voit, à travers ce simple exemple de recherche du contenu d’un enregistrement, qu’un SGBD doit implémenter des algorithmes sophistiqués.

Fonctionnalités d’un SGBD

Pour concevoir et organiser les fonctionnalités d’un SGBD on peut s’appuyer sur un modèle en trois-couches dont l’objectif est d’obtenir, une indépendance entre les données et les traitements :

Info
Niveau interne ou physique

C’est le niveau le plus proche du matériel. À ce niveau, on gère :

  • le stockage dans les fichiers des données, du schéma, des index, ... ;

  • le partage des données et la gestion de la concurrence d’accès ;

  • la reprise sur panne (fiabilité) ;

  • la distribution des données et l’interopérabilité (accès aux réseaux).

Niveau conceptuel ou logique

À ce niveau, on :

  • définit la structure des données : Langage de Description des Données (LDD) ;

  • définit comment les données peuvent être consultées et mises à jour : Langage de Requêtes (LR) et Langage de Manipulation des Données (LMD) ;

  • gère la confidentialité (sécurité, droits d’accès aux données à travers le réseau, ...) ;

  • maintient l’intégrité des données.

Niveau externe

Le niveau externe sert à décrire les « vues » des utilisateurs, c’est à dire le schéma de visualisation des données, qui est différent pour chaque catégorie d’utilisateur. C’est à ce niveau qu’on retrouve le langage SQL.

Ce découpage permet de modifier un sous-niveau sans toucher aux deux autres.

Transactions

À retenir

On appelle transaction toute opération de modification des données dans la base de données.

À retenir

Une transaction relationnelle garantit qu’une séquence d’opérations se réalise correctement, par exemple en empêchant qu’une somme d’argent ne s’évanouisse dans la nature (avec un compte en banque débité sans qu’un autre ne soit crédité). Même une panne ne doit pas pouvoir conduire à une exécution incorrecte.

Info

Les transactions doivent posséder les propriétés suivantes :

Atomicité

Une transaction est effectuée en totalité ou abandonnée.

Cohérence

Une transaction doit s’effectuer d’un état cohérent de la base vers un autre état cohérent.

Isolement

Des transactions différentes ne doivent pas interférer.

Durabilité

La transaction a des effets permanents, même en cas de panne.

Architectures

Info
Architecture à deux niveaux Client/Serveur

L’architecture à deux niveaux, appelée architecture 2-tiers (tier signifie niveau, étage en anglais), caractérise les systèmes Clients/Serveurs dans lesquels le client demande une ressource que le serveur fournit directement.

La relation est donc directe entre le serveur et le client.

Architecture à trois niveaux

Dans l’architecture à 3 niveaux, appelée architecture 3-tiers, il existe un niveau intermédiaire, c’est-à-dire que l’on a généralement une architecture partagée entre :

  • Le client, qui demande les ressources ;

  • Le serveur d’application (Middleware), qui reçoit les demandes, interroge le serveur et retourne les réponses (après avoir éventuellement effectué un traitement) ;

  • Le serveur secondaire (généralement le SGBD), qui répond aux requêtes du serveur d’application.

Dans l’architecture à 3 niveaux, chaque serveur (niveaux 1 et 2) ffectue une tâche (un service) spécialisée. Ainsi, un serveur peut utiliser les services d’un ou plusieurs autres serveurs afin de fournir son propre service. Par conséquent, l’architecture à trois niveaux est potentiellement une architecture à NN niveaux.

Comparaison des deux types d’architectures

L’architecture à deux niveaux est donc une architecture Client/Serveur dans laquelle le serveur est polyvalent, c’est-à-dire qu’il est capable de fournir directement l’ensemble des ressources demandées par le client.

Dans l’architecture à trois niveaux par contre, les applications au niveau serveur sont délocalisées, c’est-à-dire que chaque serveur est spécialisé dans une tâche (serveur web et serveur de base de données par exemple).

Info

Ainsi, l’architecture à trois niveaux permet :

  • une plus grande flexibilité/souplesse ;

  • une plus grande sécurité (la sécurité peut être définie au niveau de chaque service) ;

  • de meilleures performances (les tâches sont partagées).

Étapes de la conception de bases de données

Info

On peut décomposer le processus de conception d’une base de données en plusieurs étapes :

  • Analyse du système du monde réel à modéliser ;

  • Mise en forme du modèle pour l’intégrer dans le SGBD ;

  • Création effective dans le SGBD des structures et leur remplissage.

Physique, Chimie et Informatique > Terminales Spé. NSI > Bases de données

Le modèle Entités/Associations

Avertissement

Le modèle Entités/Associations en tant que tel n’est pas au programme. Par contre il me semble utile afin de vous faire comprendre comment on modèle le réel et on parvient au final à un schéma relationnel.

L’objectif de ce chapitre n’est donc pas la conception de schémas E/A mais plutôt leur compréhension et leur interprétation.

Ce chapitre présente la première étape du processus de modélisation du monde réel. On commence par recueillir les informations à intégrer dans la base de donnée puis on les transcrit sous une forme qui nous permettra, dans le prochain chapitre, à passer au modèle relationnel (choix de la structure de la base, clés, ...).

Principes généraux

Dans un schéma E/A, on distingue les entités qui constituent la base de données, et les associations entre ces entités. Pour illustrer le besoin de cette distinction, on commence traditionnellement par illustrer les problèmes qui ne manquent pas de survenir lorsqu’on traite une base relationnelle comme un simple fichier texte, sans se soucier de lui donner une structure correcte.

Problèmes lorsque la structure de la base n’a pas été pensée

On commence généralement par créer et étudier une base se limitant à la table suivante :

titre annee nom_realisateur prenom_realisateur annee_naissance
Alien 1979 Scott Ridley 1943
Vertigo 1958 Hitchcock Alfred 1899
Psychose 1960 Hitchcock Alfred 1899
Kagemusha 1980 Kurosawa Akira 1910
Volte-face 1997 Woo John 1946
Pulp Fiction 1995 Tarantino Quentin
Titanic 1997 Cameron James 1954
Sacrifice 1986 Tarkovski Andrei 1932
Inglourious Basterds 2009 Tarantino Quentin 1963
Reservoir Dogs 1992 Tarantino 1963
Avatar 2009 Cameron James 1954
Arizona Dream 1993 Kusturica Emir
Le Ruban blanc 2009 Haneke Michael 1942

Table FilmSimple.

Info

La conception de cette base soufre de graves défauts :

Anomalies lors d’une insertion

Qu’est-ce qui distingue deux films différents ?
La conception de cette base ne permet pas de répondre à cette question puisqu’on peut insérer deux films de même nom mais de réalisateurs différents, par exemple.
Une bonne conception nécessiterait un choix : Deux films peuvent-ils avoir le même titre ? Si non, un film peut-être repéré par son titre, si oui, il reste à définir ce qui identifiera un film.

Anomalies lors d’une modification

Les données sont incohérentes car il manque une date de naissance et un prénom. Toute modification de la table peut conduire à une telle incohérence. De plus, ces modifications doivent être effectuées plusieurs fois, sans contrôle de cohérence.

Pour finir, le Tarantino de « Reservoir Dogs » est-il le même que celui de « Pulp Fiction » ?

Anomalies lors d’une destruction

Toute destruction d’un enregistrement retire les renseignements sur le film et sur le réalisateur : supprimer l’entrée de « Volte-face » efface tout renseignement sur John Woo.

On remarque que c’est la présence de la même information à plusieurs endroits qui est à l’origine de pratiquement tous les problèmes.

Un exemple de structure de base pensée

Info

Pour éviter les anomalies énumérées ci-dessus, il faut :

  1. Être capable de représenter individuellement les films et les réalisateurs, de manière à ce qu’une action sur l’un n’entraîne pas systématiquement une action sur l’autre ;

  2. Définir une méthode d’identification d’un film ou d’un réalisateur, qui permette d’assurer que la même information est représentée une seule fois ;

  3. Préserver le lien entre les films et les réalisateurs, mais sans introduire de redondance.

Les deux premièrs problèmes nécessitent, pour être résolus, d’effectuer des choix. Par exemple : deux films ne peuvent pas avoir le même titre mais deux réalisateurs peuvent avoir le même nom. Afin de pouvoir identifier les réalisateurs, on doit alors leur attribuer un identifiant unique.

titre annee
Alien 1979
Vertigo 1958
Psychose 1960
Kagemusha 1980
Volte-face 1997
Pulp Fiction 1995
Titanic 1997
Sacrifice 1986
Inglourious Basterds 2009
Reservoir Dogs 1992
Avatar 2009
Arizona Dream 1993
Le Ruban blanc 2009

Table des films

id nom_realisateur prenom_realisateur annee_naissance
1 Scott Ridley 1943
2 Hitchcock Alfred 1899
3 Kurosawa Akira 1910
4 Woo John 1946
5 Tarantino Quentin 1963
6 Cameron James 1954
7 Tarkovski Andrei 1932
8 Kusturica Emir 1954
9 Haneke Michael 1942

Table des réalisateurs

Dans les tables ci-dessus, les identifiants (ou clés) sont représentés en gras.

Premier progrès

Il n’y a maintenant plus de redondance dans la base de données.

Problème

Il n’existe aucun lien entre les deux tables. Comment savoir qui a réalisé « Le Ruban blanc » ?

On ajoute alors un attribut dans la table Films :

titre annee id_rea
Alien 1979 1
Vertigo 1958 2
Psychose 1960 2
Kagemusha 1980 3
Volte-face 1997 4
Pulp Fiction 1995 5
Titanic 1997 6
Sacrifice 1986 7
Inglourious Basterds 2009 5
Reservoir Dogs 1992 5
Avatar 2009 6
Arizona Dream 1993 8
Le Ruban blanc 2009 9

Table des films

id nom_realisateur prenom_realisateur annee_naissance
1 Scott Ridley 1943
2 Hitchcock Alfred 1899
3 Kurosawa Akira 1910
4 Woo John 1946
5 Tarantino Quentin 1963
6 Cameron James 1954
7 Tarkovski Andrei 1932
8 Kusturica Emir 1954
9 Haneke Michael 1942

Table des réalisateurs

Cette représentation est correcte : la redondance est réduite au minimum puisque seule la clé identifiant un réalisateur apparaît dans deux tables (on parle de clé primaire dans la cas de la table des réalisateurs et de clé étrangère dans le cas de la table des films). On peut vérifier que les anomalies relevées dans la section précédente ont disparu : on sait ce qui identifie un film, on sait ce qui identifie un réalisateur ; toute modification n’affecte que l’instance visée ; on peut détruire l’entrée d’un film sans affecter celle de son réalisateur.

Info

La modélisation E/A offre une méthode simple pour parvenir au résultat ci-dessus sans avoir à écrire les tables, même dans des cas bien plus complexes que celui présenté ici.

Le modèle E/A : présentation informelle

Info

Un schéma E/A réalise une abstraction de l’application étudiée, pertinente relativement aux objectifs visés et choix effectués. On distingue :

  1. des entités, représentées par des rectangles, ici Film, Artiste, Internaute et Pays ;

  2. des associations entre entités représentées par des liens entre ces rectangles

Chaque entité est caractérisée par un ensemble d’attributs, parmi lesquels un ou plusieurs forment l’identifiant unique (souligné sur la figure ci-dessus).

Info

Les associations sont caractérisées par des cardinalités. La notation « 0,* » sur le lien Réalise du côté de l’entité Film signifie qu’un artiste peut réaliser 0 ou plusieurs films. La cardinalité est essentielle même si elle est forcément discutable puisque résultat d’un choix de conception.

Les cardinalités sont notées à l’aide de deux chiffres : celui de droite indique la cardinalité maximale (elle vaut généralement 1 ou *), celui de gauche indique la cardinalité minimale.

La notation « 0,1 » pour l’association Réalise indique que l’on s’autorise à ne pas connaître le réalisateur du film, « 1,1 » entre Film et Pays indique, au contraire, que l’on doit toujours connaître le pays producteur du film.

Il existe quelques raccourcis :

  • La notation « * » est équivalente à « 0,* » ;

  • La notation « 1 » est équivalente à « 1,1 ».

Info

Un schéma E/A est totalement indépendant de tout choix d’implémentation. Il n’est pas question de type, de structure de données, d’algorithme, de langage, ... C’est, en théorie, la partie la plus stable d’une application utilisant des bases de données.

Le modèle

Le modèle E/A, conçu en 1976, est à la base de la plupart des méthodes de conception. La syntaxe utilisée ici est celle de la méthode UML.

Entités, attributs et identifiants

La première étape d’une conception consiste à identifier les entités tiles.

À retenir

On désigne par entité tout objet identifiable et pertinent pour l’application.

La notion d’identité est primordiale, c’est elle qui permet de distinguer les entités les unes des autres, et donc de dire qu’une information est redondante ou qu’elle ne l’est pas. Il est indispensable de prévoir un moyen technique pour pouvoir effectuer cette distinction entre entités au niveau de la base de données : on parle d’identifiant ou de clé.

Attributs

Les entités sont caractérisées par des propriétés ou attributs : le titre (du film), le nom (de l’acteur), sa date de naissance, ... Un attribut est désigné par un nom et prend ses valeurs dans un domaine énumérable, comme les entiers, les chaînes de caractères, les dates, etc.

Il est important de noter que, selon cette définition, un attribut prend une valeur et une seule. On dit que les attributs sont atomiques.

Types d’entités

Il est maintenant possible de décrire plus précisément les entités par leur type :

À retenir

Le type d’une entité est composé des éléments suivants :

  1. son nom ;

  2. la liste de ses attributs avec (optionnellement) le domaine où l’attribut prend ses valeurs : les entiers, les chaînes de caractères, ... ;

  3. l’indication de (ou des) attribut(s) permettant d’identifier l’entité : ils constituent la clé.

À retenir

Une clé est un sous-ensemble d’attributs minimal d’une entité permettant d’identifier de manière unique cette entité.

Un internaute est caractérisé par plusieurs attributs : son email, son nom, son prénom, ... L’émail constitue une clé naturelle puisqu’on ne trouve pas, en principe, deux internautes ayant la même adresse électronique. L’identification par nom est impossible, mais on pourrait cependant utiliser la paire (nom,preˊnom)(\text{nom}, \text{prénom}). Il est cependant conseillé d’éviter d’utiliser une telle structure de clé car elle pose des problèmes de performances et complique les manipulations SQL par la suite.

Il est possible d’avoir plusieurs clés pour un même ensemble d’entités. Dans ce cas, on en choisit une comme clé primaire, et les autres, comme clés secondaires.

À retenir

Les caractéristiques d’une clé primaire devraient être :

  1. Sa valeur doit apparaître de façon évidente dans le schéma E/A ;

  2. On ne doit jamais avoir à ressentir le besoin de modifier cet attribut ;

  3. Pour des raisons de performance, sa taille de stockage doit être la plus petite possible.

Remarque

Dans la conception choisie, dans l’exemple de la base Film, il a été décidé que deux films ne pouvaient pas avoir le même titre : ce dernier est donc un bon candidat pour être la clé de la table. Quelle clé aurait-il fallu choisir si l’on avait accepté que deux films aient le même titre ? Il aurait été nécessaire d’introduire un champ abstrait, nommé par exemple, destiné à tenir le rôle de clé.

Associations binaires

À retenir

Une association binaire lie deux entités.

On remarque, sur la schéma ci-dessus, que :

  • certains réalisateurs mettent en scène plusieurs films ;

  • inversement, un film est mis en scène par un réalisateur, au maximum.

L’association, pour être la plus explicite possible, doit donc faire apparaître ces différences.

À retenir

La cardinalité d’une association est une paire [min⁡,max⁡][\min, \max] telle que :

  1. Le symbole max⁡\max(cardinalité maximale) désigne le nombre maximal de fois où une entité peut intervenir dans l’association.

    En général, ce nombre est 1 (au plus une fois) ou nn (plusieurs fois, nombre indéterminé), noté par le symbole « * ».

  2. Le symbole min⁡\min(cardinalité minimale) désigne le nombre minimal de fois où une entité peut intervenir dans la relation. En général, ce nombre est 1 (au moins une fois) ou 0.

Les cardinalités maximales sont plus importantes que les cardinalités minimales ou, plus précisément, elles s’avèrent plus difficiles à remettre en cause, une fois que le schéma de base est constitué. Les cardinalités n’expriment pas une vérité absolue, mais des choix de conception.

Info

Dans la notation UML, on indique les cardinalités aux deux extrémités d’un lien d’association entre deux types d’entités TAT_A et TBT_B. Les cardinalités de TAT_A sont placées à l’extrémité du liant allant de TAT_A vers TBT_B alors que les cardinalités pour TBT_B sont placées à l’extrémité du lien allant de TBT_B vers TAT_A.

L’association Réalise se lit : un réalisateur réalise zéro, un ou plusieurs films ; un film est réalisé par zéro ou au maximum un réalisateur.

Remarque

Le schéma ci-dessus ne fait pas apparaître l’entité Réalisateur mais l’entité Artiste. De plus deux associations relient ces entités.
Quelle conception a pu entraîner un tel schéma ? En réalité, un réalisateur peut être acteur dans un film ; il est donc préférable de regrouper les acteurs et les réalisateurs dans type d’entité plus général : Artiste. L’association Réalise ne peut donc plus modéliser le lien entre les entités Film et Artiste ; il est alors nécessaire de créer l’association Joue. On obtient le schéma ci-dessous :

Remarque

Dans le cas d’associations avec des cardinalités multiples de chaque côté, on peut être obligé de lier des attributs à une association (par exemple l’association Joue a pour attribut le rôle). En effet, un attribut ne pouvant prendre qu’une et une seule valeur, on ne peut pas associer l’attribut rôle à l’entité Artiste puisqu’un acteur peut jouer dans plusieurs films (il existe fort peu de chances qu’il tienne toujours le même rôle !). Seules les associations ayant des cardinalités multiples de chaque côté peuvent porter des attributs.

Une association étant définie par un ensemble de couples d’entités, on peut donc se demander comment la caractériser.

À retenir

La clé d’une association (binaire) entre un type d’entité E1E_1 et un type d’entité E2E_2 est le couple constitué de la clé c1c_1 de E1E_1 et de la clé c2c_2 de E2E_2.

En pratique, cette définition est trop contraignante car on peut vouloir autoriser deux entités à être liées plus d’une fois dans une association. Par exemple, un internaute peut être amené à noter plusieurs fois un film, en conservant l’historique de ces notations. Une association binaire entre Internaute et Film est impossible : on ne peut définir qu’un seul lien entre un film donné et un internaute donné !

Pour résoudre ce problème, il est nécessaire d’introduire des types fictifs et des associations multiples, ... ce que nous ne ferons pas dans ce cours.

Exercice

Exercice 1 : choix d’un identifiant

On considère l’entité ci-après, qui décrit des salles de cinémas. Les attributs de cette entité sont les suivants :

  • nom de la salle ;
  • nom du cinéma ;
  • ville du cinéma ;
  • nombre de places ;
  • taille de l’écran.

Que peut-on proposer comme identifiant (clé) pour cette entité ?

Exercice 2 : identification des entités et des associations

  1. On veut modéliser l’activité de vente de billets pour un théâtre. Quelles entités et quelles associations pourrait-on créer ?

  2. Proposer des attributs et les identifiants (clés) pertinents pour décrire ces entités et ces associations.

Exercice 3 : description du monde réel à partir des cardinalités

On donne le schéma E/A d’un séminaire.

Quelle description peut-on faire du lien entre les différentes entités à partir des cardinalités ?

Exercice 4 : cardinalités

On donne le schéma E/A d’une bibliothèque.

Quelles questions faut-il poser aux utilisateurs de la base de données pour déterminer les cardinalités des associations ? Proposer une réponse à ces questions et en déduire les cardinalités pour chaque entité.

Exercice 5 : tournoi de tennis

Le schéma suivant représente des rencontres dans un tournoi de tennis :

  1. Peut-on jouer des matchs de double ?

  2. Un joueur peut-il gagner un match sans y avoir participé ?

  3. Peut-il y avoir deux matchs sur le même terrain à la même heure ?

  4. Connaissant un joueur, peut-on découvrir sur quels terrains il a joué ?

Physique, Chimie et Informatique > Terminales Spé. NSI > Bases de données

Du schéma entités/associations au schéma relationnel

Concepts relationnels

Modèle relationnel

Le modèle relationnel tire son nom de la notion de relation mathématique entre les éléments. Chacun de ces éléments peut prendre des valeurs dans un ensemble défini.

Par exemple, les appareils électroménagers d’une cuisine peuvent être contenus dans l’ensemble des valeurs suivantes : {Réfrigérateur, Cuisinière, Hotte, Robot, Lave-vaisselle}. On peut, par ailleurs, aussi considérer l’ensemble des couleurs que peuvent prendre ces appareils : {Rouge, Vert, Bleu, Jaune, Blanc, Noir, Rose}. Les combinaisons possibles entre les appareils et les couleurs sont au nombre de 35. Parmi toutes ces combinaisons possibles, on effectue une sélection qui représente la description d’une cuisine :

(Réfrigérateur, Rouge)
(Robot, Mauve)
(Cuisinière, Jaune)
(Lave-vaisselle, Rouge)

Cet ensemble de couples de valeurs liées entre elles, que l’on nomme tuples dans le modèle relationnel, représente la relation entre les éléments « Appareil » et « Couleur ». On désigne les éléments constitutifs de ces tuples par les termes attributs (ou champs).

On peut écrire formellement la relation à partir de son schéma relationnel : ma_cuisine(Appareil, Couleur). Cette écriture représente le schéma relationnel de la relation ma_cuisine. Les valeurs énoncées précédemment pour les champs représentent leurs domaines, c’est à dire les ensembles de toutes les valeurs possibles pour un champ.

On voit, à partir de cet exemple, qu’il est nécessaire de préciser les concepts qui seront utilisés dans la suite de ce chapitre.

À retenir

Un schéma relationnel RR est formé par un ensemble fini de nn attributs U={u1,…,un}U = \{ u_1, \ldots, u_n \}. On le note R[u1,…,un]R [u_1, \ldots, u_n] ou R[U]R [U].

  1. Quel est le schéma relationnel de la relation ma_cuisine ?
    Réponse

    Le schéma relationnel de la relation ma_cuisine est ma_cuisine(Appareil, Couleur).

À retenir

L’arité (ou degré) d’un schéma relationnel RR est égale à sonnombre d’attributs*.*

  1. Quelle est l’arité du schéma ma_cuisine(Appareil, Couleur) ?
    Réponse

    L’arité du schéma ma_cuisine(Appareil, Couleur) est 2.

À retenir

Un schéma de base de donnée RBR_B est un ensemble fini de pp schémas relationnels. On le note RB{R1[U1],…,Rp[Up]}R_B \{ R_1 [U_1], \ldots, R_p [U_p] \}.

  1. On donne le schéma de base de donnée suivant :\
Rb { Lecteur(Num_carte, Nom, Num_établissement), 
     Établissement(Num_établissement, Ville, Nom) }
  1. De combien de schémas relationnels ce schéma de base de données est-il constitué ?

    Réponse

    Ce schéma de base de donnée et constitué de deux schéma relationnel : Lecteur et Établissement.

RB{Lecteur(Numero_carte,Nom,Numero_Etablissement),Eˊtablissement(Num_Eˊtablissement,Ville,Nom_Etablissement)}\begin{aligned} R_B & \{ \text{Lecteur} \left( \text{Numero\_carte} , \text{Nom}, \text{Numero\_Etablissement} \right) ,\cr & \text{Établissement} \left( \text{Num\_Établissement}, \text{Ville}, \text{Nom\_Etablissement} \right) \} \end{aligned}

::: {.definition} Definition 5. Un n-uplet (ou tuple) uu sur UU est une fonction de UU dans \ensuremathdom⁡\ensuremath{\operatorname{dom}}. Si uu est un n-uplet sur UU et AϵUA \epsilon U, u(A)u (A) est la valeur de uu pour l’attribut AA.

L’écriture d’un n-uplet peut préciser les attributs : (Appareil : Robot, Couleur : Mauve). :::

::: {.definition} Definition 6. Une relation, ou instance d’un schéma relationnel R[U]R [U], est un ensemble fini II de n-uplets sur UU. La cardinalité de la relation est égale à celle de l’ensemble II.

Une relation est donc totalement décrite par :

  • le schéma relationnel ;

  • les domaines des différents champs ;

  • les tuples ou n-uplets qui la constituent.

On représente une relation par une table, correspondant à la notion de tableau. Les tuples correspondent aux lignes et les attributs de la relation aux colonnes.

Une relation étant constituée d’un ensemble de n-uplets, ceux-ci doivent y être tous différents. :::

La relation (cf. tableau 1) est d’arité 2 et de cardinalité 4.

**Appareil**    **Couleur**

Réfrigérateur Rouge Robot Mauve Cuisinière Jaune Lave-vaisselle Rouge

: Relation

::: {.definition} Definition 7. Une base de données BB, ou instance d’un schéma de base de données RBR_B, est une fonction, dont l’ensemble de définition est un schéma de base de données RBR_B, telle que pour tout rϵRBr \epsilon R_B, B(r)B (r) est une relation.

Une base de données est donc un ensemble de relations reliées entre elles par des liens sémantiques. :::

Notion de clé d’une relation et dépendance fonctionnelle

Lorsqu’on utilise une base de données, il est nécessaire d’accéder à un enregistrement par le contenu d’un ou de plusieurs champs. On nomme clé d’une relation un champ, ou un ensemble de champs, qui permet d’identifier précisément un enregistrement. Une relation peut comprendre plusieurs clés possibles ; ce sont les clés candidates.

::: {.definition} Definition 8. On appelle clé primaire d’une relation la clé minimale parmi toutes les clés candidates de la relation. Une clés primaire doit toujours contenir une valeur et celle-ci doit être unique pour chacun des enregistrements de la relation :::

Dans l’exemple de la relation , le choix de la clé n’est pas simple car :

  • Plusieurs appareils différents peuvent être de même couleur ;

  • Une cuisine peut comporter plusieurs réfrigérateurs, robots, ...

Pourrait-on envisager d’utiliser le couple (appareil, couleur) comme clé ? La réponse est toujours négative, les réfrigérateurs présents dans la pièce peuvent être de même couleur.

Pour désigner une clé (primaire), il faut donc prendre en compte le « sens » des données contenues dans la relation. Si aucun champ ne peut constituer une clé, il est nécessaire d’introduire un champ formel, tel qu’un identifiant numérique.

::: {.definition} Definition 9. Une dépendance fonctionnelle existe entre deux ensembles de champs si les valeurs contenues dans l’un des ensembles de champs permettent de déterminer les valeurs contenues dans l’autre ensemble. :::

Les dépendances fonctionnelles expriment la relation de hiérarchie qui existe entre les champs.

::: {#relation-lecteur} Numero_carte Nom Age Ville Etablissement


      1             Henri       10        Paris        Université Sorbonne
      2           Stanislas     34        Paris        Université Jussieu
      3           Henriette     44        Lyon              CHU Bron
      4           Dominique     19        Nancy        Université Poincaré
      5           Isabelle      56        Nancy               INPL
      6            Olivier      51      Marseille   Université Saint Charles
      7             Henri       98        Paris        Université Sorbonne
      8            Jérôme       23        Nancy               INPL
      9           Laurence      34      Bordeaux    Université Victor Segalen
      10          Christian     41        Paris                ENS
      11           Antoine      16      Marseille   Université Saint Charles
      12          Laurence      34        Paris        Université Jussieu

: Relation lecteur(Numero_carte, Nom, Age, Ville, Etablissement)[

relation−lecteurrelation-lecteur

]{#relation-lecteur label=“relation-lecteur”} :::

Si l’on examine les données de la relation , Tableau 1{reference-type=“ref” reference=“relation-lecteur”}., on remarque :

  • qu’il ne peut y avoir de dépendance fonctionnelle entre les ensembles (Ville, Etablissement) et (Nom, Age). En effet, le couple (Laurence, 34) correspond aux deux couples (Paris, Université de Jussieu) et (Bordeaux, Université Victor Segalen).

    À un couple (Ville, Etablissement) ne correspond donc pas un unique couple (Nom, Age).

  • que si l’on suppose qu’un établissement n’est situé que dans une seule ville, il existe une dépendance fonctionnelle entre le champ « Etablissement » et le champ « Ville ».

    À une valeur du champ « Etablissement » correspond une et une seule valeur du champ « Ville ». Cette valeur n’est cependant pas unique.

  • La valeur du champ « Numero_carte » est unique pour chacun des enregistrements. Ses valeurs sont identifiantes pour tous les champs de la relation. Chaque champ dépend fonctionnellement de ce champ. Ses valeurs ne sont jamais vides, c’est une clé candidate !

    Comme c’est aussi la seule clé de la relation, ce champ constitue la clé primaire.

Tous les champs qui ne font pas partie d’une clé candidate d’une relation possèdent des dépendances fonctionnelles avec cette clé.

On indique la clé primaire d’un schéma relationnel en soulignant le champ, dans le schéma relationnel : lecteur([Numero_carte]{.ul}, Nom, Age, Ville, Etablissement).

Langage SQL

SQL est un langage déclaratif qui permet d’interroger une base de données par l’intermédiaire d’un SGBD sans se soucier de la représentation interne (physique) des données, de leur localisation, des chemins d’accès ou des algorithmes nécessaires. À ce titre, il s’adresse à une large communauté d’utilisateurs potentiels, pas seulement aux informaticiens. On peut l’utiliser de manière interactive, mais également en association avec des interfaces graphiques, ou des langages de programmation (comme Python).

Le langage SQL est composé de différents sous-ensembles :

LMD

Langage de Manipulation des données (DML, Data Manipulation Language).

Il permet la manipulation et la mise à jour des tables, et est composé de quatre ordres fondamentaux : , , , .

LDD

Langage de Définition des Données (DDL, Data Definition Language).

Il permet la définition et la mise à jour du schéma relationnel de la base de données (mode administration). Il est composé des ordres suivants : , , , , , , .

LCD

Langage de Contrôle des Données (DCL, Data Control Language).

Il permet de définir les contraintes d’intégrité, de gérer les accès et les autorisations (administration). Il est composé des ordres : , , .

Le programme cette année se limite au langage de manipulation des données et plus particulièrement à l’ordre .

Opérations de l’algèbre relationnelle et traductions en langage SQL

La manipulation de données dans le modèle relationnel se fait à l’aide d’opérations formelles reposant sur des concepts mathématiques issus de la théorie des ensembles : c’est l’algèbre relationnelle.

::: {.definition} Definition 10. L’algèbre relationnelle consiste en un petit nombre d’opérations de base qui, appliquées à des relations, produisent de nouvelles relations. Ces opérations peuvent être composées pour construire des expressions algébriques de plus en plus complexes. :::

Opérations relationnelles de base

Projection

::: {.definition} Definition 11. La projection consiste à filtrer certains attributs de la relation, ce qui donne au résultat une relation de degré inférieur à celui de la relation de départ.

Pour II une relation de schéma relationnel I[A1,…,An]I [A_{1 }, \ldots , A_n], si 1⩽i<j⩽n1 \leqslant i < j \leqslant n, πAi,…,Aj(I)={(Ai:t(Ai),…,Aj:t(Aj))∣tϵI}\pi_{A_i, \ldots, A_j} (I) = \{ (A_i : t (A_i), \ldots , A_j : t (A_j)) | t \epsilon I \} :::

**Nom**    **Ville**

 Henri       Paris

Stanislas Paris Henriette Lyon Dominique Nancy Isabelle Nancy Olivier Marseille Henri Paris Jérôme Nancy Laurence Bordeaux Christian Paris Antoine Marseille Laurence Paris

: Projection πNom,Ville(lecteur)\pi_{\text{Nom,Ville}} (\text{lecteur})

En langage SQL

SELECT``\{=latex}Nom,Ville``\{=latex}FROM``\{=latex}lecteur``\{=latex};

Sélection (ou restriction)

::: {.definition} Definition 12. La sélection consiste à filtrer des enregistrements de la relation en fonction d’une condition. Le résultat d’une sélection est une relation de même degré mais de cardinalité différente.

Pour II une relation de schéma relationnel I[A1,…,An]I [A_{1 }, \ldots , A_n] et aϵ\ensuremathdom⁡(Ai)a \epsilon \ensuremath{\operatorname{dom}} (A_i), σAi=a(I)={tϵI∣t(Ai)=a}\sigma_{A_i = a} (I) = \{ t \epsilon I | t (A_i) = a \} :::

Numero_carte Nom Age Ville Etablissement


      5           Isabelle     56        Nancy               INPL
      6           Olivier      51      Marseille   Université Saint Charles
      7            Henri       98        Paris       Université Sorbonne

: Selection σAge>50(lecteur)\sigma_{\text{Age} > 50} (\text{lecteur})

Numero_carte Nom Age Ville Etablissement


      1            Henri      10        Paris     Université Sorbonne
      7            Henri      98        Paris     Université Sorbonne

: Selection σNom=”Henri”(lecteur)\sigma_{\text{Nom} = \text{''Henri''}} (\text{lecteur})

Intuitivement, dans une représentation de type table, une sélection conserve uniquement les enregistrements vérifiant la condition.

En langage SQL

SELECT``\{=latex}*``\{=latex}FROM``\{=latex}lecteur``\{=latex}WHERE``\{=latex}Age``\{=latex}>``\{=latex}50``\{=latex};

Affichage de tous les enregistrements d’une relation

Composition d’une sélection et d’une projection

Une sélection sur une relation formant une nouvelle relation, il est possible de « passer » cette dernière en argument d’une opération de projection. La composition se note :

πchamps(σcontrainte(relation))\pi_{\text{champs}} (\sigma_{\text{contrainte}} (\text{relation}))

Nom Ville


Isabelle Nancy Olivier Marseille Henri Paris

: Combinaison d’une sélection et d’une projection πNom,Ville(σAge>50(lecteur))\pi_{\text{Nom}, \text{Ville}} (\sigma_{\text{Age} > 50} (\text{lecteur}))

En langage SQL

SELECT``\{=latex}Nom,Ville``\{=latex}FROM``\{=latex}lecteur``\{=latex}WHERE``\{=latex}Age>50``\{=latex};

Opérateurs de comparaison et connecteurs logiques de SQL

Ci-dessous sont présentés quelques opérateurs et connecteurs qui apparaissent très fréquemment dans les requêtes SQL. Pour une liste plus exhaustive et davantage d’exemples, consulter http://sql.sh.

Opérateur Signification


    $=$              Égal
   $< >$           Différent
    \<             Inférieur
    $>$            Supérieur
   $< =$       Inférieur ou égal
   $> =$       Supérieur ou égal

 **Signification**

 Appartient à un intervalle
 Appartient à un ensemble
 Teste si le champ contient une donnée
 Compare des chaînes de caractères

  **Nom**        **Instrument**

Jaco Pastorius Basse électrique Bill Evans Piano

 Appareil      Couleur        Nom            Instrument

Réfrigérateur rouge Jaco Pastorius Basse électrique Réfrigérateur rouge Bill Evans Piano Robot mauve Jaco Pastorius Basse électrique Robot mauve Bill Evans Piano Cuisinière jaune Jaco Pastorius Basse électrique Cuisinière jaune Bill Evans Piano

: ma_cuisine×musicien\text{ma\_cuisine} \times \text{musicien}

L’exemple précédent a été choisi de façon à illustrer que le résultat d’un produit cartésien peut n’avoir aucun sens dans la réalité.

En langage SQL

il n’est pas nécessaire d’utiliser un quelconque mot clé ; il faut requérir toutes les colonnes depuis les deux tables.

Division Cartésienne

La division cartésienne est le pendant du produit cartésien, tout comme la division euclidienne est le pendant du produit sur des entiers : si on note AA et BB deux relations (ou tables), alors A÷BA \div B est la « plus grande » relation (au sens de l’inclusion) telle que le produit cartésien avec BB redonne (presque) AA. Formellement, c’est la plus grande relation telle qu’il existe une relation CC vérifiant :

[(A÷B)×B]∪C=A     et     [(A÷B)×B]∩C=∅[(A \div B) \times B] \cup C = A \text{ \ \ \ \ et \ \ \ \ } [(A \div B) \times B] \cap C = \varnothing

Il n’existe pas en langage SQL d’opérateur permettant d’implémenter directement la division cartésienne.

Création d’un alias, renommage

Il peut être pénible d’utiliser, dans les requêtes, des noms de champs définis de manière complexe, ou, tout simplement, nommés de façon pas très parlante. On peut alors définir un alias pour renommer temporairement un champ (et même une table).

::: {.definition} Definition 13. Un renommage pour un ensemble fini d’attributs U est une fonction injective de U dans l’ensemble att que l’on notera A1→B1,...,An→BnA_1 \rightarrow B_1, ..., A_n \rightarrow B_n.

L’opération de renommage ρA1→B1,...,An→Bn\rho_{A_1 \rightarrow B_1, ..., A_n \rightarrow B_n} transforme une relation II sur A1,...,AnA_1, ..., A_n en une relation sur B1,...,BnB_1, ..., B_n de de telle sorte que : ρA1→B1,...,An→Bn(I)={vsurB1,…,Bn∣∃uϵI,v(Bi)=u(Ai)pour tout iϵ[1,n]}\rho_{A_1 \rightarrow B_1, ..., A_n \rightarrow B_n} (I) = \left\{ v \text{sur} B_1, \ldots, B_n | \exists u \epsilon I, v (B_i \right) = u (A_i) \text{pour tout } i \epsilon [1 , n] \} :::

La création d’un alias permet de faciliter grandement la lecture d’une requête. Cette opération se note :

Badge Nom Ville


   7        Henri       Paris
   6       Olivier    Marseille
   5       Isabelle     Nancy

: ρNumero_carte→Badge(πNumero_carte,Nom,Ville(σAge>50(lecteur)))\rho_{\text{Numero\_carte} \rightarrow \text{Badge}} (\pi_{\text{Numero\_carte}, \text{Nom}, \text{Ville}} (\sigma_{\text{Age} > 50} (\text{lecteur})))

En langage SQL

on utilise le mot clé , immédiatement après le nom du champ, pour introduire l’alias.

Dans l’exemple précédent, on a illustré l’utilisation de la clause «  » qui permet de trier les résultats suivant l’ordre (par défaut ascendant) défini par certains champs (ici le mot clé indique que c’est l’ordre descendant qui a été choisi).

Jointure

La jointure est l’opération fondamentale de l’algèbre relationnelle qui permet d’exprimer le sens du lien entre les relations, dans le monde réel. Conformément au programme, on ne présente ici que l’opération de jointure symétrique.

::: {.definition} Definition 14. Une jointure permet d’associer plusieurs relations dans une même requête. Le résultat de cette requête est une relation de degré supérieur aux degrés de chacune des relations de départ. L’opération de jointure est symétrique et associative.

La liaison entre les relations s’effectue par le contenu commun d’un champ (champs de jointure).

La jointure des relations et selon une certaine contrainte se note : table1⊳⊲contraintetable2    ou    table1[\ensuremathcontrainte⁡]table2\text{table1} \vartriangleright \hspace{-0.5em} \vartriangleleft_{\text{contrainte}} \text{table2} \text{ \ \ \ ou \ \ \ } \text{table1} [\ensuremath{\operatorname{contrainte}}] \text{table2} :::

Il est bien évidemment possible d’associer une jointure à une projection et une sélection. Ces associations doivent être prises en compte lors de la détermination du degré et de la cardinalité de la relation finale.

::: {#tab:jointure} Numero_carte Nom Num_Etablissement


      1             Henri               1
      2           Stanislas             2
      3           Henriette             1

: Relation Lecteur_bis(Numero_carte‾\underline{\text{Numero\_carte}}, Nom, Numero_Etablissement)[

tab:jointuretab:jointure

]{#tab:jointure label=“tab:jointure”} :::

Num_Etablissement Ville Nom_Etablissement


         1               Paris      Université Jussieu
         2               Lyon            CHU Bron
         3               Nancy      Université Poincaré
         4               Paris      Université Sorbonne

: Relation Etablissement(Num_Etablissement‾\underline{\text{Num\_Etablissement}}, Ville, Nom_Etablissement)

Même si l’on ne dispose pas du modèle conceptuel associé, on constate que l’on peut relier les deux relations par le champ «  ». Les informations concernant l’établissement de la relation «  » sont stockées dans la relation «  » dont la clé est le champ «  ». Pour obtenir la liste des lecteurs ainsi que les informations concernant leur établissement, on effectue une jointure entre ces relations sur le champ «  ».

Schéma de la base de données prenant en compte la jointure : Dans la relation l’attribut est une clé étrangère.

Numero_carte Nom Num_Etablissement Num_Etablissement Ville Nom_Etablissement


      1             Henri               1                       1               Paris      Université Jussieu
      2           Stanislas             2                       2               Lyon            CHU Bron
      3           Henriette             1                       1               Paris      Université Jussieu

: Lecteur_bis⊳⊲Num_Etablissement=Num_EtablissementEtablissement\text{Lecteur\_bis} \vartriangleright \hspace{-0.5em} \vartriangleleft_{\text{Num\_Etablissement} = \text{Num\_Etablissement}} \text{Etablissement}

Le champ «  » figure deux fois dans la relation résultat de la jointure car la première occurrence provient de la relation «  » alors que la seconde provient de la relation «  ».

Afin de ne conserver qu’une occurrence des champs de jointure, on peut réaliser une projection :

Numero_carte Nom Num_Etablissement Ville Nom_Etablissement


      1             Henri               1               Paris      Université Jussieu
      2           Stanislas             2               Lyon            CHU Bron
      3           Henriette             1               Paris      Université Jussieu

: πNumero_carte,Nom,Num_Etablissement,Ville,Nom_Etablissement(Lecteur_bis⊳⊲Num_Etablissement=Num_EtablissementEtablissement)\pi_{\text{Numero\_carte}, \text{Nom}, \text{Num\_Etablissement}, \text{Ville}, \text{Nom\_Etablissement}} \left( \text{Lecteur\_bis} \vartriangleright \hspace{-0.5em} \vartriangleleft_{\text{Num\_Etablissement} = \text{Num\_Etablissement}} \text{Etablissement} \right)

Cette opération, jointure+projection\text{jointure} + \text{projection}, est parfois appelée jointure naturelle.

Les champs de jointure ne doivent pas nécessairement être nommés à l’identique. Nous verrons comment les différentier dans la partie « En langage SQL », page .

On peut considérer l’opération de jointure comme une sélection sur le produit cartésien des deux relations. On ne conserve que les lignes dont le contenu du champ sur lequel s’effectue la jointure est égal.

Numero_carte Nom Num_Etablissement Num_Etablissement Ville Nom_Etablissement


      1             Henri               1                       1               Paris      Université Jussieu
      1             Henri               1                       2               Lyon            CHU Bron
      1             Henri               1                       3               Nancy      Université Poincaré
      1             Henri               1                       4               Paris      Université Sorbonne
      2           Stanislas             2                       1               Paris      Université Jussieu
      2           Stanislas             2                       2               Lyon            CHU Bron
      2           Stanislas             2                       3               Nancy      Université Poincaré
      2           Stanislas             2                       4               Paris      Université Sorbonne
      3           Henriette             1                       1               Paris      Université Jussieu
      3           Henriette             1                       2               Lyon            CHU Bron
      3           Henriette             1                       3               Nancy      Université Poincaré
      3           Henriette             1                       4               Paris      Université Sorbonne

: Lecteur_bis⊳⊲Num_Etablissement=Num_EtablissementEtablissement\text{Lecteur\_bis} \vartriangleright \hspace{-0.5em} \vartriangleleft_{\text{Num\_Etablissement} = \text{Num\_Etablissement}} \text{Etablissement} Les lignes grisées sont celles qui sont sélectionnées lors d’une jointure.

En langage SQL

il existe deux façons de réaliser une opération de jointure en SQL :

  • la première est basée sur l’idée qu’une opération de jointure peut être considérée comme une sélection sur le produit cartésien des deux relations :

    Avec une projection :

    Le traitement de la requête n’est cependant pas, dans ce cas, optimal au niveau du SGBD.

  • la seconde méthode utilise l’opérateur de jointure  :

    Avec une projection :

On remarque qu’un champ est défini de façon unique grâce à la concaténation du nom de la relation et de celui du champ : .[

champ−nom−uniquechamp-nom-unique

]{#champ-nom-unique label=“champ-nom-unique”}

Si les champs portent des noms différents, il n’est pas nécessaire de préciser de quelle relation ils sont issus.

Fonctions de calculs et d’agrégation

Une règle, dans le domaine des bases de données, est que tout ce qui peut se calculer ne doit pas être stocké dans la base. On évite ainsi la perte de place et l’incohérence qui peut découler du stockage d’informations redondantes.

Fonctions de calculs

Le langage SQL ne possède pas d’instruction d’itération ; il possède cependant des fonctions simples de traitement des données d’une colonne (les calculs plus complexes doivent être effectués à l’aide d’un langage de programmation comme Python).

Fonction Opération


             Comptage du nombre d'enregistrements de la table
             Maximum des éléments d'une colonne
             Minimum des éléments d'une colonne
             Moyenne des éléments d'une colonne
             Somme des éléments d'une colonne

: Fonctions statistiques de SQL

Détermination de l’age moyen des lecteurs

:

Comptage du nombre de lecteurs

Dans le cas de la fonction , il n’est pas nécessaire de préciser le champ puisque cette fonction s’applique à la relation toute entière.

Agrégats ou groupage

::: {.definition} Definition 15. Les opérations d’« agrégation » (ou de groupage) regroupent les enregistrements d’une relation par valeurs contenues dans une colonne.

On applique généralement des opérations de calcul sur les « sous-tables » ainsi créées. :::

NumVoit Marque Type Couleur


    1         Peugeot       404         Rouge
    2         Citroen        SM         Noire
    3           Opel         GT        Blanche
    4         Peugeot       403        Blanche
    5         Renault      Alpine       Rose
    6         Renault     Floride       Bleue
    7         Peugeot       806        Blanche

: Relation Voiture(NumVoit,Marque,Type,Couleur)

Affichage des différentes marques

Pour réaliser cette opération avec SQL, on utilise le mot clé suivi du nom de la colonne sur laquelle s’effectue l’agrégat.

SELECT``\ {=latex}Marque``\ {=latex}FROM``\ {=latex}Voiture``\ {=latex}GROUP``\ {=latex}BY``\ {=latex}Marque;

Marque

Peugeot
Citroen
  Opel
Renault

: Différentes marques de voitures

Affichage du nombre de voitures des différentes marques

Lors de cette opération, la fonction est appliquée à chaque « sous-tables » créée.

SELECT``\ {=latex}Marque,``\ {=latex}COUNT(*)``\ {=latex}AS``\ {=latex}Compte``\ {=latex}FROM``\ {=latex}Voiture``\ {=latex}GROUP``\ {=latex}BY``\ {=latex}Marque``\ {=latex};

Marque Compte


Peugeot         3
Citroen         1
  Opel          1
Renault         2

: Comptage du nombre de voitures, pour chaque marque

Affichage de l’age moyen des lecteurs, par université de la relation

:

Restriction sur le résultat d’une requête agrégative

Le résultat de l’opération de groupage peut lui-même être filtré : on effectue une sélection des lignes par rapport au contenu des colonnes obtenues dans la table résultat de la recherche groupée.

Calcul du nombre de voitures par marque dont le nombre est supérieur à 1

SELECT``\{=latex}Marque,``\{=latex}COUNT(*)``\{=latex}AS``\{=latex}Compte
FROM``\{=latex}Voiture
GROUP``\{=latex}BY``\{=latex}Marque
HAVING``\{=latex}Compte``\{=latex}>``\{=latex}1``\{=latex};

Marque Compte


Peugeot         3
Renault         2

: Comptage du nombre de voitures, pour chaque marque, si le nombre est supérieur à 1

Le mot clé permet d’effectuer une sélection sur le résultat de l’opération de groupage. Le mot clé opère une sélection sur les enregistrements de la table avant l’opération de groupage.

Calcul du nombre de voitures, par marque, dont la couleur n’est pas « Rouge »

SELECT``\{=latex}Marque,``\{=latex}COUNT(*)``\{=latex}AS``\{=latex}Compte
FROM``\{=latex}Voiture
WHERE``\{=latex}NOT``\{=latex}(Couleur``\{=latex}=``\{=latex}’Rouge’)
GROUP``\{=latex}BY``\{=latex}Marque

enregistrement.

Physique, Chimie et Informatique > Terminales Spé. NSI > Bases de données

Le langage SQL : Le langage de manipulation de données (LMD)

Introduction

Info

Le langage SQL est composé de différents sous-ensembles :

LMD : Langage de Manipulation des données (DML, Data Manipulation Language)
Permet la manipulation et la mise à jour des tables, composé de quatre ordres fondamentaux : SELECT, UPDATE, INSERT, DELETE.
LDD : Langage de Définition des Données (DDL, Data Definition Language)
Permet la définition et la mise à jour du schéma relationnel de la base de données (mode administration). Composé des ordres suivants : CREATE TABLE, CREATE INDEX, CREATE VIEW, DROP TABLE, DROP INDEX, DROP VIEW, ALTER TABLE.
LCD : Langage de Contrôle des Données (DCL, Data Control Language)
Permet de définir les contraintes d’intégrité, de gérer les accès et les autorisations (administration). Composé des ordres : GRANT, REVOKE, LOCK. Recouvre les déclencheurs (triggers), procédures cataloguées.

Le programme se limite au sous-ensemble LMD. Nous serons tout de même obligés d’utiliser l’ordre de création de table CREATE TABLE.

SQLite

Dans ce cours nous utiliserons SQLite depuis l’interface de Repl.it .

SQLite est un moteur de base de données relationnelle (sous-ensemble d’un SGBD) accessible par le langage SQL. Contrairement aux serveurs de bases de données traditionnels, comme MySQL ou PostgreSQL, sa particularité est de ne pas reproduire le schéma habituel client-serveur mais de fonctionner sans serveur (on parle de base de données « standalone » ou « embarquée »). L’intégralité de la base de données (déclarations, tables, index et données) est stockée dans un fichier indépendant de la plateforme.

Remarque
On peut accéder à SQLite depuis de nombreux langages de programmation. Nous utiliserons dans un premier temps une interface en ligne de commande (CLI) et par la suite Python.

SQLite est le moteur de base de données le plus utilisé au monde.

Avertissement

Chaque SGBD possède, en plus du SQL, des commandes propres : section 3 de la documentation officielle

Ces dernières commencent toujours par un point en SQLite et ne devront pas être apprises !

Création d’une première base de données

On possède un fichier tableur contenant des informations sur quelques acteurs, parmi les plus célèbres.

Nom Prénom Sexe Pays Age
Hanks Tom Homme USA 64
Holland Tom Homme Angleterre 24
Johansson Scarlett Femme USA 36
Johnson Dwayne Homme USA 48
Robbie Margot Femme Australie 30
Cotillard Marion Femme France 45
Gadot Gal Femme Israël 35
Cruise Tom Homme USA 58

On souhaite transformer ce fichier en une base de données contenant, pour l’instant, une seule relation.

  1. Réaliser une copie de l’ environnement de travail sur repl.it

L’instruction de création de la table (relation) est

CREATE TABLE  IF NOT EXISTS "Celebrites" (
    Nom varchar(40),
    Prenom varchar(40),
    Sexe varchar(6),
    Pays varchar(15),
    Age int
);
  1. Indiquer la signification de chaque ligne de la commande.

  2. Incorporer la commande au fichier celebrites.sql et l’exécuter.

  3. Afin de vérifier que la commande a bien créé une table dans la base de données qui réside pour l’instant dans la RAM, ajouter la commande suivante au fichier main.sql

-- Énumérations des tables
.tables

Relancer l’exécution des commandes et observer le résultat dans la console.

  1. Afin d’examiner la structure de la table Celebrites, ajouter l’une des commandes suivantes au fichier main.sql.
-- Structure de la table Celebrites
.schema 'Celebrites'

ou

-- Structure de la table Celebrites
pragma table_info('Celebrites');

La commande permettant d’intégrer le tuple ("Hanks", "Tom", "Homme", "USA", 64) à la relation Celebrites est :

INSERT INTO Celebrites VALUES ("Hanks", "Tom", "Homme", "USA", 64);
Avertissement

La commande, telle qu’elle est donnée ici, impose le respect de l’ordre des informations dans le tuple.

  1. Intégrer la commande précédente au fichier celebrites.sql. Exécuter cette commande. La sortie fait-elle apparaître le résultat de cette commande ?

  2. Afin de vérifier que les informations ont bien été ajoutées à la base de données, ajouter la commande suivante dans le fichier main.sql

SELECT * FROM Celebrites;

Exécuter cette commande.

  1. Ajouter maintenant au fichier celebrites.sql toutes les commandes permettant de peupler la base de données.
    Exécuter ces commandes.

  2. La base de données réside toujours en mémoire et n’a, pour l’instant, pas été sauvegardée dans un fichier. Ajouter la commande suivante à la fin du fichier celebrites.sql

.save "cinema.sqlite"

Vérifier que le fichier cinema.sqlite a bien été créé.

Info

Désormais, à la prochaine utilisation de la base de données il ne sera plus nécessaire de relancer toute la création de la base de données (ce qui pourrait être très très long dans le cas d’une base conséquente) mais seulement la commande

.open "cinema.sqlite"

au début du fichier main.sql (qui ne devra alors plus lire le fichier celebrites.sql).

Premières requêtes : Projection

Avertissement

Toutes les commandes à venir devront être entrées dans le fichier main.sql après la commande de lecture du fichier celebrites.sql (ou après la commande de lecture du fichier cinema.sqlite).

  1. La commande SELECT * FROM Celebrites; est une requête. Que signifie le caractère * dans cette commande ?
    Réponse

    Le caractère * indique que l’on souhaite que la relation retournée par la commande SELECT contienne tous les attributs (champs).

À retenir

Une projection consiste à filtrer certains attributs d’une relation. Le résultat d’une projection est une relation de degré inférieur à celui de la relation de départ.

  1. Adapter la commande précédente de façon à obtenir tous les noms des acteurs présents dans la base de données.

    Réponse
    SELECT Nom 
    FROM Celebrites;

  2. Adapter la commande précédente de façon à obtenir tous les noms et sexes des acteurs présents dans la base de données.

    Réponse
    SELECT Nom, Sexe 
    FROM Celebrites;

Premières requêtes : Sélection

Sélections simples

À retenir

La sélection consiste à filtrer des enregistrements d’une relation en fonction d’une condition. Le résultat d’une sélection est une relation de même degré mais de cardinalité différente.

Une sélection s’effectue en langage SQL à l’aide de la clause WHERE.

  1. Écrire la commande qui retourne toutes les informations relatives aux femmes présentes dans la base de données.

    Réponse
    SELECT * 
    FROM Celebrites
    WHERE Sexe = "Femme";

  2. Adapter la commande précédente de façon à obtenir tous les noms et prénoms des femmes présentes dans la base de données.

    Réponse
    SELECT Nom, prenom 
    FROM Celebrites
    WHERE Sexe = "Femme";

Opérateurs de comparaison

  1. Quels sont les noms de tous les acteurs (sans distinction de sexe) qui n’ont pas pour pays d’origine les USA.

    Réponse
    SELECT Nom
    FROM Celebrites
    WHERE Pays != "USA";

  2. Quels sont les noms des acteurs (sans distinction de sexe) qui ont plus de 40 ans ?

    Réponse
    SELECT Nom
    FROM Celebrites
    WHERE Age > 40;

  3. Quels sont les noms et prénoms des acteurs (sans distinction de sexe) qui ont plus de 40 ans ?

    Réponse
    SELECT Nom, Prenom
    FROM Celebrites
    WHERE Age > 40;

  4. Quels sont les noms des acteurs (sans distinction de sexe) qui ont au plus 36 ans ?

    Réponse
    SELECT Nom
    FROM Celebrites
    WHERE Age <= 36;

  5. Quels sont les noms des actrices françaises ?

    Réponse
    SELECT Nom
    FROM Celebrites
    WHERE Sexe = "Femme" AND Pays = "France";

  6. Quels sont les ages des acteurs américains hommes dont le prénom est Tom ?

    Réponse
    SELECT Age
    FROM Celebrites
    WHERE Sexe = "Homme" AND Prenom = "Tom" AND Pays = "USA";

  7. Quels sont les prénoms des acteurs (sans distinction de sexe) australiens ou de plus de 40 ans ?

    Réponse
    SELECT Prenom
    FROM Celebrites
    WHERE Age > 40 OR Pays = "Australie";

Exercices d’application

Les sportifs stars

Écrire toutes les commandes correspondant au requêtes ci-dessous pour la base de donnée se trouvant à cette adresse

  1. Donner le schéma relationnel de la base de données.
  2. Afficher les noms de tous les sportifs.
  3. Afficher les noms et prénoms de tous les sportifs.
  4. Afficher les noms, prénoms et sports de tous les sportifs.
  5. Afficher le nom des sportifs nés au mois de décembre.
  6. Afficher les noms et prénoms des sportifs nés en juin.
  7. Afficher les noms et prénoms des sportifs nés en 1981.
  8. Afficher les noms et prénoms des sportifs pratiquant le football.

Instagram

Écrire toutes les commandes correspondant au requêtes ci-dessous pour la base de donnée se trouvant à cette adresse

  1. Donner le schéma relationnel de la base de données.
  2. Afficher toutes les informations relatives aux utilisateurs qui ne sont pas des footballeurs.
  3. Afficher toutes les informations relatives aux utilisateurs qui ont plus de 200 million followers.
  4. Afficher toutes les informations relatives des utilisateurs qui ont au plus 200 million followers.
  5. Afficher toutes les informations relatives des utilisateurs qui ont au moins 200 million followers.
  6. Afficher les prénoms, noms et « Instagram handle » des comptes qui ont au maximum 155 millions de followers.
  7. Afficher toutes les informations pour les comptes qui correspondent à des chanteurs et qui ont plus de 200 millions de followers.
  8. Afficher toutes les informations pour les comptes qui correspondent à des footballeurs ou qui ont moins de 150 millions de followers.

Première requêtes : Ordres SQL

Info

On reprend, dans cette partie, l’étude de la relation Celebrites.

À retenir

L’opérateur ORDER BY est utilisé pour trier le résultat d’une projection. Par défaut ce tri s’effectue par ordre croissant (lexicographique ou numérique). Pour trier par ordre décroissant, il faut ajouter le mot clé DESC, pour trier par ordre croissant on utilise ASC.

  1. Rechercher toutes les informations sur les acteurs, triées par age croissant.

    Réponse
    SELECT *
    FROM Celebrites
    ORDER BY Age;

  2. Rechercher toutes les informations sur les acteurs, triées par pays d’origine (par ordre décroissant).

    Réponse
    SELECT *
    FROM Celebrites
    ORDER BY Pays DESC;

  3. Rechercher les noms et prénoms des acteurs triés par age et nom croissants.

    Réponse
    SELECT Nom, Prénom
    FROM Celebrites
    ORDER BY Age, Nom;

  4. Rechercher les noms et prénoms des acteurs triés par age décroissant et nom croissant.

    Réponse
    SELECT Nom, Prénom
    FROM Celebrites
    ORDER BY Age DESC, Nom ASC;

  5. Rechercher les ages et sexes des acteurs, triés par age croissant.

    Réponse
    SELECT Age, Sexe
    FROM Celebrites
    ORDER BY Age;

À retenir

L’opérateur BETWEEN est utilisé pour sélectionner une valeur numérique dans un intervalle.

  1. Écrire une requête qui affiche toutes les informations sur les artistes dont les ages sont compris entre 30 et 60 ans.
    Réponse
    SELECT Age, Sexe
    FROM Celebrites
    WHERE Age BETWEEN 30 AND 60;

Première requêtes : recherche de motifs lors d’une sélection

Info

On reprend, dans cette partie, l’étude de la relation Celebrites.

À retenir

L’opérateur LIKE permet de rechercher un motif lors d’une sélection.

  1. Rechercher les prénoms des acteurs dont le nom commence par la lettre j.

    Réponse
    SELECT Prenom
    FROM Celebrites
    WHERE Nom LIKE 'J%';

  2. Rechercher les noms et prénoms des acteurs dont le sexe commence par la lettre h, trié par nom croissant.

    Réponse
    SELECT Nom, Prenom
    FROM Celebrites
    WHERE Sexe LIKE 'h%'
    ORDER BY Nom;

  3. Rechercher les noms et prénoms des acteurs dont le prénom se termine par la lettre t.

    Réponse
    SELECT Nom, Prenom
    FROM Celebrites
    WHERE Prenom LIKE '%t';

  4. Rechercher les noms et prénoms des acteurs dont le prénom contient la lettre a.

    Réponse
    SELECT Nom, Prenom
    FROM Celebrites
    WHERE Prenom LIKE '%a%';

  5. Rechercher les noms et prénoms des acteurs dont le pays d’origine commence par un a.

    Réponse
    SELECT Nom, Prenom
    FROM Celebrites
    WHERE Pays LIKE 'a%';

  6. Rechercher les noms et prénoms des acteurs dont le pays d’origine se termine par un l.

    Réponse
    SELECT Nom, Prenom
    FROM Celebrites
    WHERE Pays LIKE '%l';

  7. Rechercher les noms et prénoms des acteurs dont le pays d’origine comporte un a.

    Réponse
    SELECT Nom, Prenom
    FROM Celebrites
    WHERE Pays LIKE '%a%';

Exercice d’application : base Animaux

Écrire toutes les commandes correspondant au requêtes ci-dessous pour la base de donnée se trouvant à cette adresse

  1. Rechercher toutes les informations sur les animaux dont le nom commence par la lettre p.

  2. Rechercher toutes les informations sur les animaux dont le nom se termine par la lettre e et qui sont En danger.

  3. Rechercher toutes les informations sur les animaux dont le nom comporte la lettre a et qui sont Vulnérable. Le résultat doit être trié par nom (ascendant).

  4. Afficher le nom de tous les animaux et leur état de préservation.

  5. Afficher le nom de tous les animaux vulnérables par ordre décroissant de la vitesse de déplacement.

  6. Afficher le nom et la durée de vie moyenne des animaux dont le nom comporte un e. L’affichage doit être effectué par durée de vie moyenne croissante.

  7. Afficher les nom, durée de vie moyenne et vitesse de déplacement des animaux dont le nom comporte un o, par vitesse de déplacement décroissante.

Modification des entrées d’une base de données

Info

On reprend, dans cette partie, l’étude de la relation Celebrites.

À retenir

L’ordre UPDATE permet de modifier un enregistrement dans une table. La clause WHERE est utilisée pour sélectionner l’enregistrement.

  1. Faire en sorte que l’age de l’actrice Margot Robbie soit égal à 29 ans.

    Réponse
    UPDATE Celebrites
    SET Age = 29
    WHERE Nom = "Robbie";
    Vérifier que la modification a bien été effectuée.

  2. Modifier le fichier contenant la base de données de façon à ce que la modification, uniquement effectuée en mémoire vive pour l’instant, soit enregistrée de façon permanente.

  3. Le vrai nom de Tom Cruise est Thomas Cruise Mapother IV. Modifier l’entrée dans la base de données.

    Réponse
    UPDATE Celebrites
    SET Nom = "Cruise Mapother IV", prenom = "Thomas"
    WHERE Nom = "Cruise";

À retenir

L’ordre DELETE permet de supprimer un enregistrement d’une table. Si aucune clause WHERE n’est utilisée, tous les enregistrements de la table sont supprimés.

  1. Supprimer l’acteur Tom Holland de la table Celebrites.
    Réponse
    DELETE FROM Celebrites
    WHERE Nom = "Holland";

Exercice d’application : base Superhereos and Villains

Écrire toutes les commandes correspondant au requêtes ci-dessous pour la base de donnée se trouvant à cette adresse

  1. Écrire une requête qui affiche tous les enregistrements de la table « Superheroes and Villains ».

  2. Écrire une requête qui affiche la structure de la table « Superheroes and Villains ».

  3. Écrire une requête qui affiche les noms des superheroes femmes.

  4. Écrire une requête qui affiche les noms et types (gentil ou méchant) des films X-Men ou Guardians of the Galaxy.

  5. Écrire une requête qui affiche tous les noms des personnages par ordre alphabétique.

  6. Écrire une requête qui affiche les types des personnages et leurs noms par ordre alphabétique croissant pour les types mais ordre alphabétique décroissant pour les noms.

  7. Écrire une requête qui affiche les noms des personnages commençant par la lettre M.

  8. Écrire une requête qui affiche les noms des personnages se terminant par la lettre N.

  9. Écrire une requête qui affiche les noms des personnages contenant la lettre I.

  10. Écrire une requête qui modifie le nom du personnage Groot. Le nouveau nom est Baby Groot. Afficher le résultat de cette requête.

  11. Écrire une requête qui supprime l’enregistrement dont le nom est Queen of Hearts. Afficher la table pour vérifier le résultat de la requête.

Exercice d’application à partir de données réelles et conséquentes

Info

Le document de travail se trouve à cette adresse : https://repl.it/@dlatreyte/Indicateurs-lycees-en-France

La base de donnée contient tous les indicateurs des lycées français présentés sur le site de l’éducation nationale et dont les données sont en accès “libre” sur data.gouv.fr . La notice descriptive est fournie.

  • Écrire 10 requêtes qui vous semblerons utiliser au mieux cette base de données.
4 févr. 2021
Physique, Chimie et Informatique > Terminales Spé. NSI > Bases de données

Le langage SQL : Le langage de manipulation de données (LMD), Compléments

Notion de clé d’une relation et dépendance fonctionnelle

Lorsqu’on utilise une base de données, il est nécessaire d’accéder à un enregistrement par le contenu d’un ou de plusieurs champs. On nomme clé d’une relation un champ, ou un ensemble de champs, qui permet d’identifier de façon unique un enregistrement. Une relation peut comprendre plusieurs clés possibles ; ce sont les clés candidates.

À retenir

On appelle clé primaire d’une relation la clé minimale parmi toutes les clés candidates de la relation. Une clé primaire doit toujours contenir une valeur et celle-ci doit être unique pour chacun des enregistrements de la relation

Pour désigner une clé (primaire), il faut donc prendre en compte le « sens » des données contenues dans la relation. Si aucun champ ne peut constituer une clé, il est nécessaire d’introduire un champ formel, tel qu’un identifiant numérique.

À retenir

Une dépendance fonctionnelle existe entre deux ensembles de champs si les valeurs contenues dans l’un des ensembles de champs permettent de déterminer les valeurs contenues dans l’autre ensemble.

Les dépendances fonctionnelles expriment la relation de hiérarchie qui existe entre les champs.

Numero_carte Nom Age Ville Etablissement
1 Henri 10 Paris Université Sorbonne
2 Stanislas 34 Paris Université Jussieu
3 Henriette 44 Lyon CHU Bron
4 Dominique 19 Nancy Université Poincaré
5 Isabelle 56 Nancy INPL
6 Olivier 51 Marseille Université Saint Charles
7 Henri 98 Paris Université Sorbonne
8 Jérôme 23 Nancy INPL
9 Laurence 34 Bordeaux Université Victor Segalen
10 Christian 41 Paris ENS
11 Antoine 16 Marseille Université Saint Charles
12 Laurence 34 Paris Université Jussieu

Relation lecteur(Numero_carte, Nom, Age, Ville, Etablissement)

Si l’on examine les données de la relation, on remarque :

  • qu’il ne peut y avoir de dépendance fonctionnelle entre les ensembles (Ville, Etablissement) et (Nom, Age). En effet, le couple (Laurence, 34) correspond aux deux couples (Paris, Université de Jussieu) et (Bordeaux, Université Victor Segalen).

    À un couple (Ville, Etablissement) ne correspond donc pas un unique couple (Nom, Age).

  • que si l’on suppose qu’un établissement n’est situé que dans une seule ville, il existe une dépendance fonctionnelle entre le champ « Etablissement » et le champ « Ville ».

    À une valeur du champ « Etablissement » correspond une et une seule valeur du champ « Ville ». Cette valeur n’est cependant pas unique.

  • La valeur du champ « Numero_carte » est unique pour chacun des enregistrements. Ses valeurs sont identifiantes pour tous les champs de la relation. Chaque champ dépend fonctionnellement de ce champ. Ses valeurs ne sont jamais vides, c’est une clé candidate !

    Comme c’est aussi la seule clé de la relation, ce champ constitue la clé primaire.

Tous les champs qui ne font pas partie d’une clé candidate d’une relation possèdent des dépendances fonctionnelles avec cette clé.

À retenir

On indique la clé primaire d’un schéma relationnel en soulignant le champ, dans le schéma relationnel : Relation lecteur(Numero_carte, Nom, Age, Ville, Etablissement)

Jointure de deux relations

Qu’est-ce qu’une jointure ?

À retenir

La jointure est l’opération fondamentale de l’algèbre relationnelle qui permet d’exprimer le sens du lien entre les relations, dans le monde réel. Conformément au programme, on ne présente ici que l’opération de jointure symétrique.

À retenir

Une jointure permet d’associer plusieurs relations dans une même requête. Le résultat de cette requête est une relation de degré supérieur aux degrés de chacune des relations de départ. L’opération de jointure est symétrique et associative.

La liaison entre les relations s’effectue par le contenu commun d’un champ (champs de jointure).

Info

Il est bien évidemment possible d’associer une jointure à une projection et une sélection. Ces associations doivent être prises en compte lors de la détermination du degré et de la cardinalité de la relation finale.

Numero_carte Nom Num_Etablissement
1 Henri 1
2 Stanislas 2
3 Henriette 1

Relation Lecteur_bis(Numero_carte, Nom, Numero_Etablissement)

Num_Etablissement  Ville  Nom_Etablissement
1 Paris Université Jussieu
2 Lyon CHU Bron
3 Nancy Université Poincaré
4 Paris Université Sorbonne

Relation Etablissement(Num_Etablissement Ville, Nom_Etablissement)

Même si l’on ne dispose pas du modèle conceptuel associé, on constate que l’on peut relier les deux relations par le champ Numero_Etablissement. Les informations concernant l’établissement de la relation Lecteur_bis sont stockées dans la relation Etablissement dont la clé est le champ Num_Etablissement. Pour obtenir la liste des lecteurs ainsi que les informations concernant leur établissement, on effectue une jointure entre ces relations sur le champ Numero_Etablissement.

Schéma de la base de données prenant en compte la jointure : Dans la relation l’attribut est une clé étrangère.

Numero_carte Nom Num_Etablissement Num_Etablissement Ville Nom_Etablissement
1 Henri 1 1 Paris Université Jussieu
2 Stanislas 2 2 Lyon CHU Bron
3 Henriette 1 1 Paris Université Jussieu

Résultat de la jointure des relations Lecteur_bis et Etablissement.

Le champ Num_Etablissement figure deux fois dans la relation résultat de la jointure car la première occurrence provient de la relation Lecteur_bis alors que la seconde provient de la relation Etablissement.

Afin de ne conserver qu’une occurrence des champs de jointure, on peut réaliser une projection :

Numero_carte Nom Num_Etablissement Ville Nom_Etablissement
1 Henri 1 Paris Université Jussieu
2 Stanislas 2 Lyon CHU Bron
3 Henriette 1 Paris Université Jussieu

Résultat de la jointure des relations Lecteur_bis et Etablissement après projection.

Cette opération, jointure+projection\text{jointure} + \text{projection}, est parfois appelée jointure naturelle.

Les champs de jointure ne doivent pas nécessairement être nommés à l’identique.

Comment effectuer une jointure en langage SQL ?

Il existe deux façons de réaliser une opération de jointure en SQL :

  • la première est basée sur l’idée qu’une opération de jointure peut être considérée comme une sélection sur le produit cartésien des deux relations :
SELECT * 
FROM Lecteur_bis, Etablissement
WHERE Lecteur_bis.Num_Etablissement = Etablissement.Num_Etablissement ;

Avec une projection :

SELECT Lecteur_bis.Numero_carte,Lecteur_bis.Nom,Etablissement.Ville, Etablissement.Nom_Etablissement
FROM Lecteur_bis,Etablissement
WHERE Lecteur_bis.Num_Etablissement = Etablissement.Num_Etablissement ;
  • la seconde méthode utilise l’opérateur de jointure  :
SELECT * FROM Lecteur_bis 
JOIN Etablissement ON Lecteur_bis.Num_Etablissement = Etablissement.Num_Etablissement ;

Avec une projection :

SELECT Lecteur_bis.Numero_carte, Lecteur_bis.Nom, Etablissement.Ville, Etablissement.Nom_Etablissement
FROM Lecteur_bis 
JOIN Etablissement ON Lecteur_bis.Num_Etablissement = Etablissement.Num_Etablissement ;
Avertissement

On remarque qu’un champ est défini de façon unique grâce à la concaténation du nom de la relation et de celui du champ. Si les champs portent des noms différents, il n’est pas nécessaire de préciser de quelle relation ils sont issus.

Fonctions de calculs

Une règle, dans le domaine des bases de données, est que tout ce qui peut se calculer ne doit pas être stocké dans la base. On évite ainsi la perte de place et l’incohérence qui peut découler du stockage d’informations redondantes.

Le langage SQL ne possède pas d’instruction d’itération ; il possède cependant des fonctions simples de traitement des données d’une colonne (les calculs plus complexes doivent être effectués à l’aide d’un langage de programmation comme Python).

Fonction  Opération
COUNT Comptage du nombre d’enregistrements de la table
MAX Maximum des éléments d’une colonne
MIN Minimum des éléments d’une colonne
AVG Moyenne des éléments d’une colonne
SUM Somme des éléments d’une colonne

Exemples

  • Détermination de l’age moyen des lecteurs
 SELECT AVG(Age) AS Age_Moyen FROM lecteur ;
  • Comptage du nombre de lecteurs
SELECT COUNT(*) AS Nombre_Lecteurs FROM lecteur ;

Dans le cas de la fonction , il n’est pas nécessaire de préciser le champ puisque cette fonction s’applique à la relation toute entière.

9 févr. 2021
Physique, Chimie et Informatique > Terminales Spé. NSI > Bases de données

Gestion d'une salle de cinéma

L’objectif de cette séance est de mettre en œuvre tous les concepts du chapitre.

La base de donnée accessible à cette adresse simule de façon simplifiée la gestion d’un cinéma.

  1. Étudier la structure de chacune des relations de la base et établir son schéma relationnel.
    En particulier, faire apparaître les clés primaires et les clés étrangères.

    Réponse
    • Quelles sont les tables de la base ?
    .tables
    • Quelle est la structure de chacune de ces relations ?
    pragma table_info('film');
    pragma table_info('projection');
    pragma table_info('categorie');

    Rappel : ces commandes sont spécifiques à SQLite et ne sont pas au programme. Il est cependant utile de les connaître.

  2. Afficher le contenu de chacune des relations.

    Réponse
    SELECT *
    FROM film;
    
    SELECT *
    FROM projection;
    
    SELECT *
    FROM categorie;

  3. Quelles sont les différentes catégories de film ?

    Réponse
    SELECT codecat, libcat
    FROM categorie;

  4. Quelles sont les noms des salles du cinéma triés dans l’ordre alphabétique ?

    Réponse
    SELECT DISTINCT salle
    FROM projection
    ORDER BY salle;

  5. Quels sont les films (numéro, titre, durée) projetés au cinéma ?

    Réponse
    SELECT DISTINCT film.numfilm, titre, duree
    FROM film, projection
    WHERE film.numfilm = projection.numfilm;

    ou

    SELECT DISTINCT f.numfilm, titre, duree
    FROM film f, projection p
    WHERE f.numfilm = p.numfilm;

    ou

  6. Quels sont les salles qui ont des séances à 11h00 ?

    Réponse
    SELECT DISTINCT salle
    FROM projection
    WHERE seance = '11h00';

  7. Quelle est le titre et la durée du film numéro 20 ?

    Réponse
    SELECT titre, duree
    FROM film
    WHERE numfilm = 20;

  8. Quelle est le titre et la durée (en heure) du film numéro 20 ?

    Réponse
    SELECT titre, (duree/60)
    FROM film
    WHERE numfilm = 20;

  9. Quels sont les noms des films diffusés en salle Sud qui durent entre 60 et 120 minutes ?

    Réponse
    SELECT DISTINCT titre
    FROM film, projection
    WHERE film.numfilm = projection.numfilm
    AND salle = 'Sud'
    AND duree BETWEEN 60 AND 120;

  10. Combien il y a-t-il de films dans la base (dans une colonne nommée : Total films) ?

    Réponse
    SELECT COUNT(*) AS 'Total films'
    FROM film;

  11. Combien il y a-t-il de films produits par Universal dans la base ?

    Réponse
    SELECT production, COUNT(numfilm) AS 'Nbre films'
    FROM film
    WHERE production = (SELECT production 
                        FROM film 
                        WHERE production = "Universal");

  12. Combien il y a-t-il de films dans la catégorie documentaire (libellé catégorie, nombre de films) ?

    Réponse
    SELECT COUNT(*) AS 'Nbre films'
    FROM film f
    JOIN categorie c ON f.codecat = c.codecat
    WHERE libcat = "documentaire";

  13. Quels sont les titres des films de 140 minutes et produits par UGC ?

    Réponse
    SELECT titre
    FROM film
    WHERE duree = 140
    AND production = 'UGC';

  14. Quels sont les titres des films commençant par C ou L (triés par ordre alphabétique) ?

    Réponse
    SELECT titre
    FROM film
    WHERE titre LIKE 'C%'
    OR titre LIKE 'L%'
    ORDER BY titre;

  15. Quels sont les titres des films qui ne commencent pas par la lettre U (triés alphabétiquement) ?

    Réponse
    SELECT titre
    FROM film
    WHERE titre NOT LIKE 'U%'
    ORDER BY titre;

  16. Quels sont les titres et la durée des films qui ne sont pas diffusés en salle nord ?

    Réponse
    SELECT DISTINCT titre, duree
    FROM film, projection
    WHERE film.numfilm = projection.numfilm
    AND salle != 'Nord';

  17. Quels sont les titres des films diffusés à 11h dans les salles Nord ou Centrale ?

    Réponse
    SELECT DISTINCT titre
    FROM film, projection
    WHERE film.numfilm=projection.numfilm
    AND seance LIKE '11h%'
    AND salle IN ('Nord','Centrale');

    ou

    SELECT DISTINCT titre
    FROM film f
    JOIN projection p ON f.numfilm = p.numfilm 
    WHERE seance = '11h00' 
        AND (salle = 'Nord' OR salle = 'Centrale');

  18. Quelle est la durée moyenne des films de la base ?

    Réponse
    SELECT AVG(duree)
    FROM film ;

  19. Quelle est la séance du film diffusé le plus tôt dans la journée du 18/11 ?

    Réponse
    SELECT MIN(seance) AS 'Première séance'
    FROM projection
    WHERE date = '2005/11/18';

  20. Quel est le numéro et le titre des films qui ont la même durée que le film numéro 71 ?
    Utiliser deux requêtes imbriquées.

    Réponse
    SELECT numfilm, titre
    FROM film
    WHERE duree = (SELECT duree
                   FROM film
                   WHERE numfilm = 71);

  21. Quel est le numéro et le titre des films de la même catégorie que Super Mondet XII ?
    Utiliser deux requêtes imbriquées.

    Réponse
    SELECT numfilm, titre
    FROM film
    WHERE codecat = (SELECT codecat
                     FROM film
                     WHERE titre = 'Super Mondet XII');

  22. Quels sont les titres des films diffusés aux horaires suivants : 11h00, 13h10 et 14h50 ?

    Réponse
    SELECT DISTINCT titre
    FROM film f, projection p
    WHERE f.numfilm = p.numfilm
    AND seance IN ('11h00','13h10','14h50');

    ou

    SELECT DISTINCT titre
    FROM film f 
    JOIN projection p ON f.numfilm = p.numfilm
    WHERE seance IN ('11h00', '13h10', '14h50');

  23. Quels sont les films (numéro et titre) plus longs que la moyenne de tous les films ?
    Utiliser deux requêtes imbriquées.

    Réponse
    SELECT numfilm, titre
    FROM film
    WHERE duree > (SELECT AVG(duree)
                   FROM film);

  24. Quels films (numéro, salle et séance) sont programmés aux mêmes horaires ?
    Utiliser une autojointure.

    Réponse
    SELECT DISTINCT p1.numfilm, p1.salle, p1.seance
    FROM projection p1, projection p2
    WHERE p1.seance = p2.seance
    AND p1.numfilm != p2.numfilm;

  25. Quelle est le titre, la salle, la séance et la durée (en heure) des films projeté. En utilisant la fonction ROUND(,<nb décimales>) ?

    Réponse
    SELECT titre, salle, seance, ROUND(duree/60.,1) AS 'duree'
    FROM film f, projection p
    WHERE f.numfilm = p.numfilm;

  26. Quels sont les films (numéro et titre) qui n’ont pas été diffusés ?

    Réponse
    SELECT DISTINCT titre, f.numfilm
    FROM film f, projection p
    WHERE f.numfilm NOT IN (
        SELECT f.numfilm
        FROM film f, projection p
        WHERE f.numfilm = p.numfilm
    );

  27. Quel est le film le plus diffusé ?

    Réponse

On souhaite maintenant ajouter l’attribut tarif à la relation projection. Exécuter la commande suivante :

ALTER TABLE projection ADD tarif float;
  1. Vérifier que la structure de la relation projection a bien été modifiée.

On fait la supposition que le tarif des films dépend du film, de la salle, de la séance et de la date de projection. Le tarif exprime le tarif de base.

  1. Mettre à jour la table projection en fixant un tarif de base à 5 € pour la salle nord, 8 € pour la salle centrale, 6.5 € pour la salle sud.

    Réponse
    UPDATE projection
    SET tarif = 5.00
    WHERE salle = 'Nord';
    
    UPDATE projection
    SET tarif = 8.00
    WHERE salle = 'Centrale';
    
    UPDATE projection
    SET tarif = 6.50
    WHERE salle = 'Sud';

  2. Une erreur s’est glissée dans la table projection : l’horaire de 13h10 est à remplacer par 19h30 pour la salle sud.

    Réponse
    UPDATE Projection
    SET seance = '19h30'
    WHERE seance = '13h10'
        AND salle = "Sud";

  3. Mettre à jour la table projection en ajoutant 1 € au prix des films dont les séances sont à 14h50 et 2 € au prix des films dont les séances sont à 19h30.

    Réponse
    UPDATE Projection
    SET tarif = tarif + 1
    WHERE seance = '14h50';
    
    UPDATE Projection
    SET tarif = tarif + 2
    WHERE seance = '19h30';

  4. Mettre à jour la table projection en augmentant de 10% le tarif des films projectés le 18/11.

    Réponse
    UPDATE projection
    SET tarif = tarif * 1.1
    WHERE date = '2005/11/18';

  5. Mettre à jour la table projection en augmentant de 25% le tarif des films n°10 et n°50.

    Réponse
    UPDATE projection
    SET tarif = tarif * 1.25
    WHERE numfilm = '10'
    OR numfilm = '50';

  6. Mettre à jour la table film en ajoutant les films suivants :

    • 89, Les fourmis de l’espace (documentaire), produit par UGC, 130 minutes.
    • 90, Jet Set (comédie), produit par Paramounth, 100 minutes.
      Réponse
      INSERT INTO film
      VALUES ('89','Les fourmis de l’espace','130','UGC','DOCU');
      INSERT INTO film
      VALUES ('90','Jet Set','100','Paramounth','COMD');
  7. Effacer les enregistrements en date du 17-11-2005.

    Réponse
    DELETE FROM projection
    WHERE date = '2005/11/17';

9 févr. 2021
Physique, Chimie et Informatique > Terminales Spé. NSI > Bases de données

SQL Murder Mystery

Info

Task

A crime has taken place and the detective needs your help. The detective gave you the crime scene report, but you somehow lost it. You vaguely remember that the crime was a ​murder​ that occurred sometime on ​Jan.15, 2018​ and that it took place in ​SQL City​. All the clues to this mystery are buried in a huge database, and you need to use SQL to navigate through this vast network of information. Your first step to solving the mystery is to retrieve the corresponding crime scene report from the police department’s database. Take a look at the SQL reference to learn how to do this! From there, you can use your SQL skills to find the murderer.

  • If you just want to solve the mystery, go to mystery.knightlab.com
  • If you’re new to SQL, you may want to start at our walkthrough . It won’t teach you everything about SQL, but it should teach you all that you need to solve the mystery.

Entity Relationship Diagram of the database

Checking the Solution

Write the following queries in your SQL environment to check whether you’ve found the right murderer:

INSERT INTO solution VALUES (1, "Insert the name of the person you found here");
SELECT value FROM solution;
7 févr. 2021
Physique, Chimie et Informatique > Terminales Spé. NSI > Bases de données

Emprunt de livres dans un CDI

Info

Le fichier de travail se trouve à cette adresse.

La base de données correspond à la modélisation d’emprunt de livres dans un CDI :

  • Chaque élève d’un lycée peut emprunter des livres au CDI, les données concernants ces livres et les emprunts en cours sont stockés dans une base de données.

  • Les auteurs ainsi que les éditeurs figurent également dans cette base.

Travail préalable

  1. Déterminer la structure de la base de données et indiquer son schéma relationnel.

Requêtes simples

Donner le code SQL de chacune des requêtes suivantes.

  1. Afficher tous les noms des auteurs.

  2. Afficher le titre de tous les livres.

  3. Afficher les noms des classes du lycée sans doublon.

  4. Afficher les titres des livres et les annees d’édition classés selon les années.

  5. Quels sont les livres dont le titre contient le mot Astérix ?

Expressions et fonctions

Donner le code SQL de chacune des requêtes suivantes.

  1. Afficher les noms et prénoms des élèves de la classe 1-G1.

  2. Afficher les titres des livres publiés après 2000.

  3. Afficher les isbn des livres dont la date retour est déjà passée (au 25 octobre 2020).

  4. Combien d’auteurs sont présents dans la base de données ?

  5. Quelle est l’annee d’édition du ou des livre(s) le(s) plus ancien(s) ?

Requêtes imbriquées

Donner le code SQL de chacune des requêtes suivantes.

  1. Afficher les titres des livres empruntés.

  2. Afficher, sans doublon, le nom et prénom des élèves qui ont emprunté au moins un livre.

  3. Afficher, sans doublon, le nom et prénom des élèves qui ont emprunté au moins un livre avec une date retour dépassée (au 25 octobre 2020).

  4. Quels sont les editeurs à avoir édité un livre contenant Astérix dans le titre ?

  5. Quel est le titre du ou des livre(s) le(s) plus ancien(s) ?

Jointures

Donner le code SQL de chacune des requêtes suivantes en utilisant la clause JOIN.

  1. Afficher les titres des livres empruntés.

  2. Afficher, sans doublon, le nom et prénom des élèves qui ont emprunté au moins un livre.

  3. Afficher, sans doublon, le nom et prénom des élèves qui ont emprunté au moins un livre avec une date retour dépassée (au 25 octobre 2020).

  4. Qui est l’auteur du livre 1984 ?

  5. Afficher les titres des livres écrit par Pierre Boulle.

  6. Combien de livres ont été écrit par Isaac Asimov ?

  7. Afficher les noms des editeurs ayant édité un écrit par Barjavel.