1. Objectifs pédagogiques du TP : 1

2. Connexion à la base de données 1

3. Utilisations de bases de données 2

1.Objectifs pédagogiques du TP :

Cette première séance a pour objectif d'exploiter les fonctionnalités essentielles d'un système de gestion de base de données relationnelles (SGBDR).

• création de tables, modifications– SQL correspondant

• Opérations essentielles : ajout, modification suppression d'information – SQL correspondant

• Intérêts des services d'un SGBDR (intégrité référentielle)

Un compte rendu par personne. Pour le rédiger, des captures d'écran peuvent être utilisées afin de compléter les explications.

Pour votre compte rendu : partir de ce document (ouvert avec un traitement de texte).

Attention, ne pas effectuer de copier-coller à partir du traitement de texte (notamment commandes SQL) car les caractères cachées posent problèmes. Pour les copier-coller, partir du sujet ouvert dans un navigateur.

Ce compte-rendu contiendra les diverses éditions des documents (description des tables et des requêtes, etc…) réalisées au cours de la séance.

L'ensemble de ces comptes rendu pourra être ramassé en fin de cycle de TP.

Toutes les manipulations se feront à partir d'un navigateur. Un serveur de base de données couplé à un serveur web a été mis à votre disposition pour manipuler vos bases de données

2.Connexion à la base de données

Utiliser l'identifiant (login) et le mot de passe communiqués par l'enseignant pour vous connecter au SGBD à l'adresse du serveur.

• Créer une base de données nommée "Votre_loginEXO1".

Exemple, si votre login est sdupont, la base de données s'intitule sdupontEXO1.

Note : Il est maintenant possible d'administrer la base ainsi créée, notamment créer, modifier, parcourir des tables et effectuer diverses opérations à partir de votre navigateur.

1. Est-il nécessaire d'être connecté à l'aide d'un navigateur web pour administrer une base de données ?

2. Sur quelle machine se trouve le système de gestion de base de données ?

3. Vis à vis de la sécurité des informations, que pensez-vous de la manière d'accéder à votre base de données pour l'administrer ?

3.Utilisations de bases de données

On souhaite manipuler et exploiter des données relatives à des films.

Exercice 1 – découverte des requêtes basiques

Dans la base de données exo1, on souhaite créer la table suivante

FILMS (IdFilm, titre, NomGenre)

Le code SQL permettant la création de ces tables est le suivant :

CREATE TABLE FILMS (

IdFilm INTEGER NOT NULL UNIQUE PRIMARY KEY,

titre TEXT NOT NULL,

NomGenre TEXT NOT NULL

);

1. Créer la table dans votre base de données.

2. Quelle sont les significations des éléments suivants :

• NOT NULL

• UNIQUE

3. Ajouter les films suivants en utilisant une requête SQL.

Exemple : le film Les Schtroumpfs, genre Animation ; la requête correspondante est

INSERT INTO FILMS (IdFilm, titre, NomGenre)

VALUES (1,'Les Schtroumpfs', 'Animation');

Note : Science fiction s'écrit une fois avec un tiret (pour Cowboys et envahisseurs) et une fois sans (Xmen). Respecter cette "erreur" volontaire qui sera exploitée par la suite.

1	Les Schtroumpfs	Animation
2	Xmen	Science fiction
3	Cowboys et envahisseurs	Science-fiction

4. Donner la requête SQL permettant d'ajouter le film "Green Lantern" qui a pour identifiant 2 et le genre Science fiction. Que se passe-t-il ? Pourquoi ?

5. Donner la requête SQL permettant d'ajouter le film "Bel ami" qui a pour identifiant 5 (sans spécifier le genre). Que se passe-t-il ? Pourquoi ?

6. Lister tous les éléments d'une table

La requête correspondante est

SELECT * FROM FILMS ;

Exécuter la requête.

Il est possible d'effectuer la même opération à l'aide de la page d'administration de la base : faites l'opération "browse" sur la table FILMS et observer l'affichage du code SQL correspondant.

Note : bon nombre d'autres opérations commandées graphiquement à partir de la page web "admin" correspondent à des requêtes SQL. Elles sont généralement affichées lorsqu'elles sont évoquées.

7. Rechercher des éléments d'une table

La recherche suivant un critère se fait à l'aide de la commande WHERE.

Exemple : rechercher les films de Animation peut se faire avec la commande SQL suivante :

SELECT titre, NomGenre
FROM FILMS
WHERE NomGenre='Animation';

• Effectuer la commande ci-dessus.

• Effectuer la commande suivante :

SELECT titre, NomGenre

FROM FILMS

WHERE NomGenre='Science fiction';

• Combien de films sont listés ? Est-ce normal ?

• Si l'on considère la mémorisation d'un très grand nombre de films, quel est l'autre inconvénient de la structure proposée ?

8. Effacer des données

DELETE FROM FILMS WHERE titre='Les Schtroumpfs';

Inconvénient de cette approche : ne pas faire de faute d'orthographe dans le nom du film. Que faudrait-il faire pour ne pas avoir ce problème ?

9. Modifier des données

UPDATE FILMS SET titre='Xmen Le Commencement'

WHERE IdFilm=2;

10. Effacer une table

DROP TABLE FILMS;

Exercice 2 : services avancés relatifs à une table (liée ou non).

Incrémentation automatique

On peut remarquer qu'il est fastidieux de définir les valeurs que doit prendre la clé primaire pour chaque occurrence. Cet identifiant est pourtant nécessaire (identification unique d'un film, accélération des recherches ultérieures et, - ce qui sera vu par la suite -, liaison entre tables facilitée). Cependant il est possible de demander au système d'affecter automatiquement un ID pour chaque occurrence de la table. Il s'agit de la commande SERIAL pour POSTGRESQL ('Auto-increment' par exemple pour d'autres systèmes).

Nous proposons donc d'inclure cette fonctionnalité. Pour cela, supprimer la table FILMS, puis créer-la de nouveau mais en utilisant la requête SQL suivante :

CREATE TABLE FILMS (

IdFilm SERIAL NOT NULL UNIQUE PRIMARY KEY,

titre TEXT NOT NULL,

NomGenre TEXT NOT NULL

);

1. Donner le code SQL permettant d'insérer un nouveau film (attention, l'identifiant est maintenant géré par le SGBDR^*). Insérer 2 nouveaux films.

2. Supprimer un film. Ajouter un autre film. Observer alors les identifiants des films.

• L'ordre dans l'attribution des identifiants a-t-il une importance ? Justifier.

• Lister le contenu de la table FILMS à l’écran et compter le nombre de films. Comment trouver directement le nombre de films mémorisés sans les dénombrer ? Donner la requête SQL correspondante et vérifier le résultat.

• Peut-on utiliser la plus grande valeur de l'identifiant des films pour dénombrer les films ?

Insertion automatique

Lors d'une insertion de nouvelles informations (nouvelle occurrence ("ligne") dans une table, il se peut que l'on souhaite qu'un champ soit rempli avec une valeur par défaut si aucune valeur particulière soit précisée. On peut demander au SGBDR la prise en charge de cette affectation automatique. La commande correspondante est "defaut" pour PostgrSQL.

Exemple : dans la table EMPLOYES ci-dessous, lors d'une insertion d'un nouvel employé, on mémorise la date de création :

CREATE TABLE EMPLOYES (

IdEmploye SERIAL NOT NULL UNIQUE PRIMARY KEY,

NomEmploye TEXT NOT NULL,

PrenomEmploye TEXT NOT NULL,

DateEmbauche DATE DEFAULT CURRENT_DATE

);

Contrôle sur les champs d'une table

Des contrôles sur les données peuvent être nécessaires lors de l'ajout d'information. Cela évite la saisie de données que l'on ne souhaite pas dans la base. Exemple : nombre positif (et non pas négatif), longueur maximale d'un champ, etc…

Note : le format des champs est déjà vérifié par le SGBDR lorsque l'on spécifie le type de données.

Ces contraintes peuvent être toutes décrites dans l'application, c'est à dire dans le logiciel exploitant une base de données. Par exemple, dans le cas d'une application web, on peut vérifier certaines contraintes sur des valeurs entrées par l'utilisateur (Javascript), avant que ces données soient effectivement insérées dans la base de données. Un message d'alerte peut être géré par le logiciel qui invite par exemple l'utilisateur à ressaisir les données posant problème.

Seulement, si le logiciel n'effectue pas ces tests (oubli, non spécifié dans le cahier des charges, etc…) des données erronées (que l'on sait fausses) peuvent être malgré tout introduites dans la base de données. Afin d'éviter ce problème, dans un contexte de sécurisation de développement, on interdira au niveau de la base/SGBDR, toute introduction de données que l'on sait erronées.

Différentes contraintes sur les données peuvent être données au Système de Gestion de Bases de Données Relationnelles (SGBDR). Suivant le SGBDR, les types de contraintes peuvent être en nombre important. Se référer à sa documentation. Nous allons illustrer quelques exemples dans cet exercice.

• Exemple : la commande CHECK.

Exemple d'utilisation:

CREATE TABLE products (

product_no integer,

name text,

price numeric CHECK (price > 0),

discounted_price numeric CHECK (discounted_price > 0),

CHECK (price > discounted_price)

);

Remarque : la commande 'check' peut être placée au niveau de la déclaration d'un champ (notamment lors d'une contrainte uniquement sur ce champ) ; c'est le cas (voir exemple ci-dessus) sur le prix ('price') qui doit être positif. Mais il est possible de donner cette commande (ou plusieurs) 'check' après les déclarations de champs (notamment lors de contraintes utilisant plusieurs champs) ; c'est le cas, dans l'exemple ci-dessus, du prix soldé 'discounted_price' qui doit être inférieur au prix 'price'

• Exemple : limitation de la longueur (taille maximale) d'un champ.

CREATE TABLE CLIENTS (

IdClient SERIAL NOT NULL UNIQUE PRIMARY KEY,

NomClient VARCHAR(20) NOT NULL,

PrenomClient VARCHAR(20) NOT NULL,

);

3. Donner la requête (SQL) permettant de créer une table CLIENTS suivante :

CLIENTS (IdClient, NomClient, PrenomClient, rue, CP, NomVille, DateCreation)

le nom et le prénom du client seront une chaîne (type VARCHAR(nbcar)) de 20 caractères maximum, la rue pourra comporter plusieurs lignes (type TEXT), le code postal un entier, et le nom de ville une chaîne de 50 caractères maximum.

Rappel : un code postal est supérieur à 0 et inférieur à 100 000. Intégrer ces contraintes.

Par défaut, la date de création du client est la date du jour (de création !).

Exécuter cette requête et vérifier notamment l'impossibilité de :

• Entrer une ville dont le nom de la ville est supérieur à 50 caractères ;

• Un code postal négatif ;

• Un code postal supérieur à 100 000 ;

• Entrer un texte dans le code postal. Pour ce denier cas, expliquer ce qui a permis d'expliciter au SGBDR ce test.

• Vérifier la date de création. Entrez un nouveau client en forçant la date de création au 20 mai de l'année ('May 20, yyyy').

Exercice 3 : tables liées

La structure de la base précédente n'est pas optimale :

• risque d'erreurs d'orthographe lors des recherches,

• occupation mémoire trop importante d'informations redondantes.

Nous proposons donc d'inclure tous ces aspects dans une nouvelle version de la base.

Créer une nouvelle base nommée Votre_loginEXO2 Voici les 2 tables retenues (la table CLIENTS n'est pas utile dans la suite) :

GENRES (IDgenre, NomGenre)

FILMS (IdFilm, titre , RefGenre)

Le code SQL permettant la création de ces tables est le suivant :

CREATE TABLE GENRES (IdGenre SERIAL NOT NULL UNIQUE PRIMARY KEY, NomGenre TEXT NOT NULL);

CREATE TABLE FILMS (IdFilm SERIAL NOT NULL UNIQUE PRIMARY KEY, titre TEXT NOT NULL, RefGenre INTEGER NOT NULL);

REMARQUE IMPORTANTE : les notions de clé étrangère / intégrité référentielle NE SONT PAS implémentées dans cet exemple afin justement de mettre en évidence, par la suite, leurs intérêts.

4. Créer ces 2 tables à l'aide des requêtes SQL.

5. Insérer les données correspondant aux informations ci-dessous. Relever une requête SQL correspondant pour chaque table.

GENRES :

IdGenre	NomGenre
1	Animation
2	Science fiction

FILMS

titre	RefGenre
Les Schtroumpfs	1
Xmen Le Commencement	2
Cowboys et envahisseurs	2

6. Lister les éléments de la table films et vérifier que le système a automatiquement rempli les valeurs du champs IdFilm pour chaque film.

7. Exécuter la commande suivante, qui liste les films uniquement à partir de la table FILMS:

SELECT titre, RefGenre

FROM FILMS;

8. On souhaite voir apparaître le nom des genres (et non leur identifiant) au coté des titres.

Dans un 1^er temps, exécuter la commande suivante :

SELECT *

FROM FILMS, GENRES;

Relever la table résultat. Que constatez-vous ? Pour y remédier, on va associer les 2 champs 'RefGenre' et IdGenre' pour que seules les lignes qui ont la même information dans 'RefGenre' et IdGenre' soient conservées.

SELECT *

FROM FILMS, GENRES

WHERE FILMS.RefGenre=GENRES.IdGenre;

Enfin, pour ne conserver que les colonnes qui nous intéressent (titre et NomGenre), exécuter la commande suivante :

SELECT titre, NomGenre

FROM FILMS, GENRES

WHERE RefGenre=IdGenre;

Une autre façon de faire la même sélection est d’utiliser la syntaxe explicite de jointure INNER JOIN.

SELECT titre, NomGenre

FROM FILMS INNER JOIN GENRES ON RefGenre=IDgenre;

La syntaxe de jointure JOIN est plus riche que celle de la WHERE CLAUSE et permet d’ajouter les lignes des tables qui ne trouvent pas d’équivalent dans la table jointe (soit les films alors que le genre n’existe pas, soit les genres qui n’ont pas de film).

9. On souhaite lister les titres des films pour le genre 'Science fiction' et afficher le nom du genre. Pour cela, exécuter la commande suivante :

SELECT titre, NomGenre

FROM FILMS INNER JOIN GENRES ON RefGenre=IdGenre

WHERE NomGenre='Science fiction';

Autre solution :

SELECT titre, NomGenre

FROM FILMS INNER JOIN GENRES ON RefGenre=IdGenre

WHERE IdGenre=2;

10. Ajouter les informations ci-dessous.

INSERT INTO FILMS (titre, RefGenre)

VALUES ('Arthur', 3);

Exécuter la requête permettant d'obtenir les titres des films et leur genre. Que conclure sur l'affichage des informations relatives au film ‘Arthur' ?

Proposer une requête permettant d'afficher les noms de tous les films et leur genre, mais en affichant également les films sans genre.

Ajouter les informations ci-dessous :

INSERT INTO GENRE (NomGenre) VALUES ('Comédie');

Proposer une requête permettant d'afficher les noms de tous les films et leur genre, mais en affichant également les genres possédés par aucun film.

Il est préférable de faire référence à des informations existantes ; proposer une démarche à suivre lors de l’insertion d’un nouveau film – sans changer la structure de la base - permettant de garantir l'existence des informations qui lui sont liées.

11. Exécuter la requête permettant de donner le titre des films de Science fiction :

SELECT titre, NomGenre

FROM FILMS INNER JOIN GENRES ON RefGenre=IDgenre

WHERE NomGenre='Science fiction';

Relever le résultat.

Modifier l'identifiant du genre 'Science fiction' en le remplaçant par '4'. Requête correspondante :

UPDATE GENRES SET IDgenre=4

WHERE NomGenre='Science fiction';

Exécuter de nouveau la requête permettant de donner le titre des films de Science fiction :

SELECT titre, NomGenre

FROM FILMS INNER JOIN GENRES ON RefGenre=IDgenre

WHERE NomGenre='Science fiction';

Conclure.

Que faudrait-il faire – sans modifier la structure de la base – pour résoudre ce problème ?

Y a-t-il un autre inconvénient à avoir mis à jour l’identifiant du genre Science fiction ?

12. On souhaite supprimer le genre 'Science fiction' et tous les films ayant ce genre. Proposer les requêtes permettant de le faire :

• En utilisant l'identifiant du genre correspondant à Science fiction.

• Sans utiliser l'identifiant du genre, mais uniquement le nom du genre ('Science fiction'). NB : si vous avez effectivement supprimé les films de Science fiction précédemment, vous pouvez tester votre requête sur le genre 'Animation'.