Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

D’après un TP deTiphaine Accary-Barbier SLAM3 - BDD TP2 – page1/6 TP : LDD, LMD

D’après un TP deTiphaine Accary-Barbier SLAM3 - BDD TP2 – page1/6 TP : LDD, LMD et vues 1 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 3 é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. On recense 4 types de postes (des stations UNIX, des terminaux X, des PC sous Windows et des PC sous NT). La base parc devra aussi décrire les installations de logiciels. 2 Création des tables Schéma relationnel des tables. Voir vue Concepteur Segment (indIP, nomSegment, etage) Types (typeLP, nomType) Salle (nSalle, nomSalle, nbPoste, indIP) Poste (nPoste, nomPoste, indIP, ad, typePoste, nSalle) Logiciel (nLog, nomLog, dateAch, version, typeLog, prix) Installer (nPoste, nLog, numIns, dateIns, delai) Les noms et type des colonnes sont les suivants : Colonne Commentaire Type indIP Trois premiers groupe IP (exemple 130.120.80) VARCHAR(11) nomSegment Nom du segment VARCHAR(20) Etage Etage du segment TINYINT(1) nSalle Numéro de la salle VARCHAR(7) nomSalle Nom de la salle VARCHAR(20) nbPoste Nombre de postes dans la salle TINYINT(2) nPoste Code du poste de travail VARCHAR(7) nomPoste Nom du poste de travail VARCHAR(20) ad Dernier chiffre du groupe IP INT(3) typePoste Type du poste (UNIX, TX, PCWS, PCNT) VARCHAR(9) nLog Code du logiciel VARCHAR(5) nomLog Nom du logiciel VARCHAR(20) dateAch Date d’achat du logiciel dateTIME version Version du logiciel VARCHAR(7) typeLog Type du logiciel (UNIX, TX, PCWS, PCNT) VARCHAR(9) prix Prix du logiciel DECIMAL(6,2) numIns Numéro séquentiel des installations INTEGER(5) dateIns Date d’installation du logiciel sur le poste dateTIME delai Intervalle entre achat et installation SMALLINT typeLp Types des logiciels et des postes VARCHAR(9) nomType Noms des types (Terminaux X, PC windows, …) VARCHAR(20) Voir ANNEXE pour conseils sur nommage des contraintes (remarque : consignes non prises en compte dans le script fourni !) Compléter le script SQL de création des tables (creParc.sql) avec leur clé primaire et uniquement les contraintes suivantes (pas de Foreign Key pour le moment) : o Les noms des salles, des segments et des postes doivent être renseignés cad non nuls. o Le champ ad peut prendre une valeur entre 0 et 255 o Le prix est >=0 D’après un TP deTiphaine Accary-Barbier SLAM3 - BDD TP2 – page2/6 TP : LDD, LMD et vues o La date d’installation (dateIns) est égale à celle du jour si elle n’est pas renseignée Sous MySQL en mode de commande : Aide en ligne sur contraintes ici o Créer la base parc o Créer un utilisateur parc avec le mot de passe : pwdparc o Lui donner tous les droits sur la base de données parc o Exécuter le script creParc.sql o Montrer les tables Coller ci-dessous, la copie d’écran de l’exécution du script : 3 Structure des tables Ecrire puis exécuter le script SQL (descParc.sql) de description des tables (en utilisant DESCRIBE) Coller ci-dessous, la copie d’écran de l’exécution du script : 4 Destruction des tables Ecrire puis exécuter le script SQL (dropParc.sql) de destruction de vos tables. Coller ci-dessous, la copie d’écran de l’exécution du script : Relancez de nouveau le script de création après avoir constaté que vos tables ont effectivement disparu. 5 Insertion de données Compléter le script SQL insParc.sql afin d’insérer les données suivantes dans la table suivantes segment : Table Segment +------------+-----------------+-------+ | indIP | nomSegment | etage | +------------+-----------------+-------+ | 130.120.80 | Brin RDC | NULL | | 130.120.81 | Brin 1er étage | NULL | | 130.120.82 | Brin 2ème étage | NULL | +------------+-----------------+-------+ Coller ci-dessous, la copie d’écran de l’exécution de la requete select * from Segment ; : Dans ce même script, gérer la séquence associée à la colonne numIns (attribut défini en auto_increment) commençant à la valeur 1 de manière à insérer les enregistrements suivants : Table Installer +--------+------+--------+---------------------+-------+ | nPoste | nLog | numIns | dateIns | delai | +--------+------+--------+---------------------+-------+ | p2 | log1 | 1 | 2013-05-15 00:00:00 | NULL | | p2 | log2 | 2 | 2013-09-17 00:00:00 | NULL | | p4 | log5 | 3 | 2018-09-23 21:39:51 | NULL | | p6 | log6 | 4 | 2013-05-20 00:00:00 | NULL | | p6 | log1 | 5 | 2013-05-20 00:00:00 | NULL | | p8 | log2 | 6 | 2013-05-19 00:00:00 | NULL | | p8 | log6 | 7 | 2013-05-20 00:00:00 | NULL | | p11 | log3 | 8 | 2013-04-20 00:00:00 | NULL | D’après un TP deTiphaine Accary-Barbier SLAM3 - BDD TP2 – page3/6 TP : LDD, LMD et vues | p12 | log4 | 9 | 2013-04-20 00:00:00 | NULL | | p11 | log7 | 10 | 2013-04-20 00:00:00 | NULL | | p7 | log7 | 11 | 2013-04-01 00:00:00 | NULL | +--------+------+--------+---------------------+-------+ Coller ci-dessous, la copie d’écran de l’exécution de la requete select * from Installer ; : 6 Modification de données Ecrire le script modification.sql qui permet de modifier (avec UPDATE) la colonne étage 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 ; Coller ci-dessous, la copie d’écran de l’exécution du script : 7 Ajout de colonnes Ecrire le script evolution.sql qui contient les instructions nécessaires pour ajouter les colonnes suivantes. Le contenu des colonnes sera modifié plus tard. Table Segment nbSalle TINYINT(2) : nb de salles par défaut = 0 nbPoste TINYINT(2) : nb de postes par défaut = 0 Table Logiciel nbInstall TINYINT(2) : nb d’installations par défaut = 0 Table Poste nbLog TINYINT(2) : nb de logiciels installés par défaut = 0 Coller ci-dessous, la copie d’écran de l’exécution du script : 8 Modification de colonnes Dans ce même script, rajouter les instructions nécessaires pour :  augmenter, dans la table Salle, la taille de la colonne nomSalle (VARCHAR(30))  diminuer, dans la table Segment, la taille de la colonne nomSegment (VARCHAR(15)) 9 Ajout de contraintes Toujours dans le script evolution.sql, : Ajouter la contrainte permettant de s’assurer qu’on ne puisse installer plusieurs fois le même logiciel sur un poste de travail particulier. Ajouter des contraintes de clés étrangères pour assurer l’intégrité référentielle entre les tables suivantes (attention réfléchissez au sens de la contrainte) : Segment(indIP)  Poste(indIP) Salle(nSalle)  Poste(nSalle) Poste(nPoste)  Installer(nPoste) Poste(typePoste)  Type(typeLP) Logiciel(nLog)  Installer(nLog) Utiliser les conventions recommandées pour nommer vos contraintes. Si l’ajout d’une contrainte référentielle renvoie une erreur, vérifier les enregistrements de la tables père et fils. D’après un TP deTiphaine Accary-Barbier SLAM3 - BDD TP2 – page4/6 TP : LDD, LMD et vues Le script de destruction du schéma (dropParc.sql) risque de ne plus fonctionner maintenant, pourquoi si c’est le cas ? Modifier le script dropParc.sql en fonction des nouvelles contraintes Lancer ce script puis tous les suivants permettant de recréer vos tables, d’insérer vos données, de modifier les structures et d’évoluer… 10 Traitement des erreurs Essayer d’ajouter les contraintes de clés étrangères suivantes : Segment(indIP)  Salle(indIP) Logiciel(typeLog)  Types(typeLP) La mise en place de ces contraintes doit renvoyer une erreur. Identifiez les enregistrements qui posent problème et mettez en place une solution. Exécuter de nouveau l’ajout des 2 contraintes. Vérifier que les instructions ne renvoient plus d’erreur. 11 Vue monostable Vue sans contraintes Ecrire le script vues.sql permettant de créer :  La vue LogicielsUnix qui contient tous les logiciels de type ‘UNIX’ (en conservant toutes les colonnes). Vérifier la structure et le contenu (DESCRIBE et SELECT)  La vue poste_0 de structure (nPos0, nomPoste0,nsalle0,typeposte0,indIP,ad0) qui contient tous les postes du rez-de-chaussée (étage=0 dans la table segment). Faites une jointure procédurale, sinon la vue sera considérée comme une vue multitable. Vérifier la structure et le contenu de la vue. Insérer deux nouveaux postes dans la vue (un qui soit connecté au segment du rez-de- chaussée et l'autre au segment du premier étage). Vérifier le contenu de la vue et celui de la table. Que pouvez-vous conclure? Supprimer ces deux enregistrements de la table Poste. Que pouvez-vous conclure ? Résoudre une requête complexe Créer la vue SallePrix de structure (nSalle, nomSalle, nbPoste, prixLocation) qui contient les salles et leur prix de location pour la journée. Le montant de la location sera d'abord calculé sur la base de 100 euros par poste (utiliser l'expression 100*nbPoste). Vérifier le contenu de la vue et afficher les salles dont le prix de location dépasse 150 euros. Ajouter la colonne tarif de type SMALLINT(4) à la table Types. Mettre à jour cette table pour insérer les données suivantes : D’après un TP deTiphaine Accary-Barbier SLAM3 - BDD TP2 – page5/6 TP : LDD, LMD et vues Créer la vue SalleIntermediaire de structure (nSalle, typePoste, nombre, tarif), de telle sorte que le contenu de cette vue reflète uploads/Voyage/ slam3-tp2-rappels-et-vues-2.pdf