ENSP-UYI
3GI 2024-2025
TP sur MySQL
Présentation de la base de données
Une entreprise désire gérer son parc informatique à l’aide d’une base de
données. Le bâtiment est composé de trois étages. Chaque étage possède son
réseau (ou segment distinct) Ethernet. Ces réseaux traversent des salles
équipées de postes de travail. Un poste de travail est une machine sur
laquelle sont installés certains logiciels. Quatre catégories de postes de
travail sont recensées (stations Unix, terminaux X, PC Windows et PC NT).
La base de données devra aussi décrire les installations de logiciels.
Les noms et types des colonnes sont les suivants :
Création des tables
Écrire puis exécuter le script SQL (que vous appellerez [Link]) de
création des tables avec leur clé primaire (en gras dans le schéma suivant) et
les contraintes suivantes :
• Les noms des segments, des salles et des postes sont non nuls.
• Le domaine de valeurs de la colonne ad s’étend de 0 à 255.
• La colonne prix est supérieure ou égale à 0.
• La colonne dateIns est égale à la date du jour par défaut.
1
Structure des tables
Écrire puis exécuter le script SQL (que vous appellerez [Link]) qui
affiche la description de toutes ces tables (en utilisant des commandes
DESCRIBE). Comparer le résultat obtenu avec le schéma ci-dessus.
Destruction des tables
Écrire puis exécuter le script SQL de destruction des tables (que vous
appellerez [Link]).
Lancer ce script puis celui de la création des tables à nouveau.
Insertion de données
Écrire puis exécuter le script SQL (que vous appellerez [Link]) afin
d’insérer les données dans les tables suivantes :
2
Gestion d’une séquence
Dans ce même script, gérer la séquence associée à la colonne numIns
commençant à la valeur 1 de manière à insérer les enregistrements suivants
:
3
Modification de données
Écrire le script [Link] qui permet de modifier (avec UPDATE) la
colonne etage (pour l’instant nulle) de la table Segment, afin d’affecter un
numéro d’étage correct (0 pour le segment 130.120.80, 1 pour le segment
130.120.81, 2 pour le segment 130.120.82).
Diminuer de 10 % le prix des logiciels de type 'PCNT'.
Vérifier :
SELECT * FROM Segment;
SELECT nLog, typeLog, prix FROM Logiciel;
Ajout de colonnes
Écrire le script é[Link] qui contient les instructions nécessaires pour
ajouter les colonnes suivantes (avec ALTER TABLE). Le contenu de ces
colonnes sera modifié ultérieurement.
Modification de colonnes
Dans ce même script, rajouter les instructions nécessaires pour :
• augmenter la taille dans la table Salle de la colonne nomSalle (passer à
VARCHAR(30)) ;
• diminuer la taille dans la table Segment de la colonne nomSegment à
VARCHAR(15) ;
• tenter de diminuer la taille dans la table Segment de la colonne
nomSegment à VARCHAR(14).
Pourquoi la commande n’est-elle pas possible ?
Vérifier la structure et le contenu de chaque table avec DESCRIBE et
SELECT.
4
Ajout de contraintes
Ajouter la contrainte afin de s’assurer qu’on ne puisse installer plusieurs fois
le même logiciel sur un poste de travail donné.
Ajouter les contraintes de clés étrangères pour assurer l’intégrité référentielle
(avec ALTER TABLE… ADD CONSTRAINT…) entre les tables suivantes.
Adopter les conventions recommandées dans le chapitre 1 (comme indiqué
pour la contrainte entre Poste et Types).
Si l’ajout d’une contrainte référentielle renvoie une erreur, vérifier les
enregistrements des tables « pères » et « fils » (notamment au niveau de la
casse des chaînes de caractères, 'Tx' est différent de
'TX' par exemple).
Traitements des erreurs
Tentez d’ajouter les contraintes de clés étrangères entre les tables Salle et
Segment et entre Logiciel et Types (en gras dans le schéma suivant).
5
La mise en place de ces contraintes doit renvoyer une erreur car :
• Il existe des salles ('s22' et 's23') ayant un numéro de segment inexistant
dans la table Segment.
• Il existe un logiciel ('log8') dont le type n’est pas référencé dans la table
Types.
Extraire les enregistrements qui posent problème (numéro des salles pour le
premier cas, numéro de logiciel pour le second). Supprimer les
enregistrements de la table Salle qui posent problème. Ajouter le type de
logiciel ('BeOS', 'Système Be') dans la table Types.
Exécuter à nouveau l’ajout des deux contraintes de clé étrangère. Vérifier
que les instructions ne renvoient plus d’erreur et que les deux requêtes
d’extraction ne renvoient aucune donnée.
Création dynamique de tables
Écrire le script cré[Link] permettant de créer les tables Softs et
PCSeuls suivantes (en utilisant la directive AS SELECT de la commande
CREATE TABLE). Vous ne poserez aucune contrainte sur ces tables. Penser
à modifier le nom des colonnes.
La table Softs sera construite sur la base de tous les enregistrements de la
table Logiciel que vous avez créée et alimentée précédemment. La table
PCSeuls doit seulement contenir les enregistrements de la table Poste, qui
sont de type 'PCWS' ou 'PCNT'. Vérifier :
SELECT * FROM Softs;
SELECT * FROM PCSeuls;
Requêtes monotables
Écrire le script requê[Link] permettant d’extraire, à l’aide d’instructions
SELECT, les données suivantes :
1 Type du poste 'p8'.
2 Noms des logiciels 'UNIX'.
3 Noms, adresses IP, numéros de salle des postes de type 'UNIX' ou 'PCWS'.
4 Même requête pour les postes du segment '130.120.80' triés par numéros
de salles décroissants.
5 Numéros des logiciels installés sur le poste 'p6'.
6 Numéros des postes qui hébergent le logiciel 'log1'.
7 Noms et adresses IP complètes (ex : '[Link]') des postes de type
'TX' (utiliser la fonction de concaténation).
Fonctions et groupements
8 Pour chaque poste, le nombre de logiciels installés (en utilisant la table
Installer).
9 Pour chaque salle, le nombre de postes (à partir de la table Poste).
10 Pour chaque logiciel, le nombre d’installations sur des postes différents.
6
11 Moyenne des prix des logiciels 'UNIX'.
12 Plus récente date d’achat d’un logiciel.
13 Numéros des postes hébergeant 2 logiciels.
14 Nombre de postes hébergeant 2 logiciels (utiliser la requête précédente en
faisant un SELECT dans la clause FROM).
Requêtes multitables
Opérateurs ensemblistes
15 Types de postes non recensés dans le parc informatique (utiliser la table
Types).
16 Types existant à la fois comme types de postes et de logiciels.
17 Types de postes de travail n’étant pas des types de logiciels.
Jointures procédurales
18 Adresses IP complètes des postes qui hébergent le logiciel 'log6'.
19 Adresses IP complètes des postes qui hébergent le logiciel de nom 'Oracle
8'.
20 Noms des segments possédant exactement trois postes de travail de type
'TX'.
21 Noms des salles ou l’on peut trouver au moins un poste hébergeant le
logiciel 'Oracle 6'.
22 Nom du logiciel acheté le plus récent (utiliser la requête 12).
Jointures relationnelles
Écrire les requêtes 18, 19, 20, 21 avec des jointures de la forme
relationnelle. Numéroter ces nouvelles requêtes de 23 à 26.
27 Installations (nom segment, nom salle, adresse IP complète, nom logiciel,
date d’installation) triées par segment, salle et adresse IP.
Jointures SQL2
Écrire les requêtes 18, 19, 20, 21 avec des jointures SQL2 (JOIN, NATURAL
JOIN, JOIN USING).
Numéroter ces nouvelles requêtes de 28 à 31.