INFO606-TP2-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 deayant 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 son numéro. Les informations sur les salles indiquent le bâtiment et l'étage où elles se trouvent. 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

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, ident_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 (MACHINE, 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.

Partie 2: Observation des scripts

Les fichiers SQL de création de la base de données vous sont fournis ici. 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. Utilisez la commande \dS ppour visulaliser la liste des tables système. Parmi ces tables, il existe une table pg_indexes dont nous vous demandons de visualiser le contenu. A quoi correspond ce contenu ? de quoi s'agit-il ? Faites le lien avec les tables de la base que vous avez à manipuler.
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 TP1-BD-L3-2008-2009 ? 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, calcul relationnel 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 et calcul relationnels, 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.