INFO606-TP2-2010-2011

Sujet du TP2 d'INFO606 en 2010/2011.

Description de la base à modéliser

Le SUMO (Service Universitaire de Mutualisation des Ordinateurs) gère un grand parc de machines à disposition des étudiants, enseignants et personnels de l'Université.

Chaque machine est identifiée par un nom réseau. Lors de l'installation d'une machine, on relève les données pertinentes pour la gestion du réseau et des logiciels: adresse MAC, architecture (ia32, ia64, sparc, powerpc), fréquence du processeur (en MHz), taille de la mémoire vive (en Mio) et taille du disque dur (en Gio). Un serveur DHCP associe à une adresse MAC une adresse IP sur le réseau (certaines adresses MAC peuvent ne pas être référencées).

Sur chaque machine est installé un système d'exploitation (ou plusieurs). Chaque système a un nom et une version, une architecture d'accueil et des besoins matériels minimaux (fréquence, mémoire et disque dur). Un système ne peut être installé que sur une machine ayant une architecture adéquate, c'est-à-dire avec au moins les ressources matérielles minimales. Certains systèmes sont propriétaires et payants : il faut alors acheter des licences pour avoir le droit de les installer. Le nombre d'installations d'un système payant doit être inférieur au nombre de licences dont on dispose.

Chaque machine peut être mise à disposition dans une salle, identifiée par un nom et un bâtiment (par exemple 59B, bâtiment Maurienne). Pour chaque salle, on donne l'indication de l'étage où elle se trouve. Par ailleurs, le nombre de prises Ethernet est limité pour chaque salle : on ne peut pas connecter plus de machines que ce qu'il y a de prises.

Travail à réaliser

Vous traiterez les trois parties suivantes dans un compte-rendu que vous ferez parvenir à votre responsable de TP.

Partie 1: Modèle de données

On propose la collection de relations suivante (les clefs sont en majuscules):

 Systeme (IDENT_SYS, nom, version, edition, arch, freq_min, mem_min, dd_min, payant)
 Licence (NUM_CONTRAT, pour_sys, nb_licences)
 Machine (NOM_MACH, adr_mac, arch, freq_proc, taille_mem, taille_dd)
 Installation (NOM_MACH, IDENT_SYS)
 Dhcp (ADR_MAC, adr_ip)
 Salle (NOM_SALLE, BATIMENT, etage, nb_eth)
 Emplacement (NOM_MACH, nom_salle, batiment)

1. Guidés par la description du modèle, retrouvez le (ou plutôt un) schéma EA qui se traduit en cette collection de relations.
2. Cette collection est-elle en 1FN, en 2FN , en 3FN ? Justifiez votre réponse.
3. Identifiez, parmi les attributs de ces relations, ceux qui sont des références à des clés étrangères.

Partie 2: Observation des scripts

Les fichiers SQL de création de la base de données vous sont fournis ici pour les TR, ici pour les Mass. La base de donnée est hébergée sur le serveur eco.univ-savoie.fr sur lequel vous pouvez vous connecter à travers le protocole SSH: consultez à cet effet la page Consignes générales pour les TP. Dans la création des tables de la base, apparaissent les instructions SQL suivantes:

 constraint systeme_arch_valide check (arch in ('IA32', 'IA64', 'PowerPC', 'SPARC'))
 constraint salle_cle primary key (nom_salle, batiment)
 constraint emplacement_ref_salle foreign key (nom_salle, batiment) references salle(nom_salle, batiment)

1. Que signifient-elles ? Que permettent-elles de représenter ?
2. Quels sont les index qui ont été créés automatiquement par le SGBD sur cette base et pourquoi ? Vous consulterez la table système pg_indexes pour répondre à cette question.
3. Comment crée-t-on les utilisateurs et leur alloue-t-on des droits sur les tables ? Qui a actuellement des droits (et lesquels) sur les tables de la base TP-BD-L3-2010-2011 ? Fouillez dans les tables système pour répondre à cette question.

Partie 3: Requêtes

En préalable à cette partie, nous vous demanderons de compléter la base fournie de manière à ce que vous disposiez d'un jeu de tests suffisant pour vérifier vos requêtes. Vous fournirez les requêtes nécessaires à l'insertion de n-uplets dans les tables ou à la modification des n-uplets existants.

Pour la suite, il vous est demandé de donner l'expression en algèbre relationnelle et SQL des requêtes qui suivent, à exprimer sur le schéma relationnel et la base correspondante, qui vous sont fournis aujourd'hui.

1. Liste des machines de l'architecture IA32.
2. Listes des machines sur lesquelles le système WIN-XP-PRO-IA64 est installé.
3. Liste des machines sur lesquelles est installé un Windows pour architecture IA64.
4. Listes des machines dans la réserve (celles qui ne sont dans aucune salle).
5. Combien de licences pour WIN-XP-PRO-IA32 sont-elles utilisées ?
6. Combien de licences pour Windows XP sur architecture IA32 (toutes éditions confondues) sont-elles disponibles ?
7. Listes des systèmes payants, pour lesquels il n'y a plus de licence disponible.
8. Quels systèmes sont installables sur la machine barbatruc avec les licences disponibles ?
9. Sur quelles machines de la réserve peut-on installer UBUNTU-DESKTOP-8.10-IA32 ?
10. Quel est le système le plus "gourmand" en mémoire vive (celui qui en demande le plus pour fonctionner) ? Ecrire cette requête en algèbre relationnelle puis en SQL SANS AVOIR RECOURS aux primitives de calcul (max notamment) introduites par SQL.
11. Quelles sont les salles dans lesquelles on pourra trouver des machines avec des systèmes windows et d'autres avec des systèmes Ubuntu ?
12. Quelles sont les salles dans lesquelles toutes les machines sont installées sous Ubuntu ?
13. Quelles sont les salles dans lesquelles aucune machine n'est installée sous Ubuntu ?
14. Quelles sont les machines sur lesquelles sont installés tous les systèmes dont dispose l'Université ?
15. Quelle est la salle ayant la configuration la plus hétérogène (il s'agit de la salle qui comporte la plus grande variété de systèmes installés sur ses machines) ?
16. Pour chaque salle, indiquez son nom et le nombre de systèmes différents installés sur les machines qui l'occupent.
17. Pour chaque salle, indiquez le nombre de prises Ethernet libres.
18. Dans quelles salles peut-on encore ajouter une machine ?
19. Listez les adresses IP attribuées par le serveur DHCP.
20. Listez les machines auxquelles le serveur DHCP attribue une adresse, mais qui sont dans la réserve et pas dans une salle.