Remerciez-le!

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

Institut Supérieur d’Informatique – Bases de données évoluées On souhaite impla

Institut Supérieur d’Informatique – Bases de données évoluées On souhaite implanter sous Oracle une base de données permettant de faire le suivi de sportifs lors de compétitions internationales multisports (type jeux olympiques) du point de vue des contrôles antidopage. L’analyse conceptuelle des besoins exprimés par les instances chargées de ces contrôles est donnée ci-dessous sous forme d’un diagramme de classes UML. Base de données 1. Créer un ensemble de types correspondant au diagramme de classes ci-dessus. Les associations 1–N du modèle doivent être traduites au niveau physique sous forme de collections multiniveaux. Convention de nommage des types : préfixer le nom des classes par T_ (ex. T_Athlete) et des collections par TAB_ (ex. TAB_Controle). 2. Définir une table nommée Equipe d’objets de type T_Equipe. Donner les commandes permettant d’afficher la structure de cette table ainsi que celle de ses tables imbriquées. 3. Peupler la table Equipe avec les données ci-dessous, puis afficher son contenu. NumEq CodePays Sport NumDossard Nom Prénom NumCtrl Type DateCtrl Result. 1 USA 100 mètres 1 Green Maurice 1 Sanguin 03/11/05 N 2 Lewis Carl 2 Sanguin 03/11/05 N 2 USA Basket 23 Jordan Michael 3 Urinaire 03/11/05 N 8 Bryant Kobe 4 Urinaire 03/11/05 N 34 O Neal Shaquille 5 Urinaire 03/11/05 N 3 UK 100 mètres 4 Sphinx Le 6 Sanguin 03/11/05 P 7 Sanguin 04/11/05 N 4 UK Aviron 5 Smith John 8 Urinaire 03/11/05 N 6 Smith Jack 9 Urinaire 03/11/05 P 10 Urinaire 04/11/05 P 5 FRA Aviron 9 Dupond Albert 11 Urinaire 03/11/05 N 7 Martin Maurice 12 Urinaire 03/11/05 N 6 FRA Basket 10 Bilba Jim 13 Urinaire 03/11/05 N 11 Parker Tony 14 Urinaire 03/11/05 N 12 Diaw Boris Bases de données avancées – TD n°2 1 1 EQUIPE NumEq : NUMBER(4) CodePays : VARCHAR(3) Sport : VARCHAR(15) ATHLETE NumDossard : NUMBER(5) Nom : VARCHAR(20) Prenom : VARCHAR(20) * Athletes 1 CONTROLE NumCtrl: NUMBER(6) Type : VARCHAR(15) DateCtrl : DATE Resultat : CHAR(1) * Controles Requêtes de « désemboîtement » 1. Liste des équipes (CodePays, Sport) avec les athlètes (Nom, Prénom) les constituant (effectuer une pseudojointure). 2. Idem - Liste des équipes (CodePays, Sport) avec les athlètes (Nom, Prénom) les constituant - en utilisant un curseur imbriqué. La différence avec le résultat de la première requête ? 3. Nom et prénom des athlètes triés par pays et par ordre alphabétique 4. Numéro et date des contrôles antidopage effectués par chaque équipe (pseudojointures). 5. Idem en utilisant des curseurs imbriqués. 6. Nom et prénom des athlètes de l’équipe n° 2. 7. Liste des contrôles effectués par l’athlète n° 4 de l’équipe n° 3. 8. Nombre d’athlètes dans l’équipe n° 6. 9. Nombre total d’athlètes par pays. 10. Nombre de contrôles par athlète (ordre alphabétique). 11. Nombre de contrôles positifs par athlète (ordre alphabétique). 12. Nombre de contrôles positifs par pays (ordre alphabétique). 13. Date de dernier contrôle pour chaque athlète (ordre alphabétique). 14. Équipe(s) les plus contrôlées. 15. Équipes dont aucun athlète n’a été contrôlé positif. Mises à jour des tables imbriquées 1. Insérer l’athlète <30, ‘Pietrus’, ‘Michael’, pas de contrôle> dans l’équipe n° 6. 2. Insérer les contrôles <15, ‘Urinaire’, ‘23/01/2015’, ‘N’> et <16, ‘Sanguin’, ‘23/01/2015’, ‘N’> pour l’athlète n° 30 de l’équipe n° 6. 3. Supprimer tous les contrôles de l’athlète n° 4 de l’équipe n° 3. 4. Modifier à « Négatif » le résultat des contrôles de l’athlète n° 6 de l’équipe n° 4 passés avant le 04/11/2015. Bases de données avancées – TD n°2 2 1) -- Types CREATE TYPE T_Controle AS OBJECT( NumCtrl NUMBER(6), Type VARCHAR(15), DateCtrl DATE, Resultat CHAR(1)) / CREATE TYPE TAB_Controle AS TABLE OF T_Controle / CREATE TYPE T_Athlete AS OBJECT( NumDossard NUMBER(5), Nom VARCHAR(20), Prenom VARCHAR(20), Controles TAB_Controle) / CREATE TYPE TAB_Athlete AS TABLE OF T_Athlete / CREATE TYPE T_Equipe AS OBJECT( NumEq NUMBER(4), CodePays VARCHAR(3), Sport VARCHAR(15), Athletes TAB_Athlete) / 2) -- Table CREATE TABLE Equipe OF T_Equipe (CONSTRAINT pk_equipe PRIMARY KEY(NumEq)) NESTED TABLE Liste_Athletes STORE AS table_imbriquee_athletes (NESTED TABLE Liste_Controles STORE AS table_ imbriquee_controles); DESC Equipe DESC table_imbriquee_athletes DESC table_imbriquee_controles 3) INSERT INTO Equipe VALUES(1, 'USA', '100 metres', TAB_Athlete(T_Athlete(1, 'Green', 'Maurice', TAB_Controle(T_Controle(1, 'Sanguin', '3/11/2005', 'N'))), T_Athlete(2, 'Lewis', 'Karl', TAB_Controle(T_Controle(2, 'Sanguin', '3/11/2005', 'N'))))); INSERT INTO Equipe VALUES(2, 'USA', 'Basket', TAB_Athlete(T_Athlete(23, 'Jordan', 'Michael', TAB_Controle(T_Controle(3, 'Urinaire', '3/11/2005', 'N'))), T_Athlete(8, 'Bryant', 'Kobe', TAB_Controle(T_Controle(4, 'Urinaire', '3/11/2005', 'N'))), T_Athlete(34, 'O Neal', 'Shaquille', TAB_Controle(T_Controle(5, 'Urinaire', '3/11/2005', 'N'))))); INSERT INTO Equipe VALUES(3, 'UK', '100 metres', TAB_Athlete(T_Athlete(4, 'Sphynx', 'Le', TAB_Controle(T_Controle(6, 'Sanguin', '3/11/2005', 'P'), T_Controle(7, 'Sanguin', '4/11/2005', 'N'))))); Bases de données avancées – TD n°2 Correction du TD n°2 3 INSERT INTO Equipe VALUES(4, 'UK', 'Aviron', TAB_Athlete(T_Athlete(5, 'Smith', 'John', TAB_Controle(T_Controle(8, 'Urinaire', '3/11/2005', 'N'))), T_Athlete(6, 'Smith', 'Jack', TAB_Controle(T_Controle(9, 'Urinaire', '3/11/2005', 'P'), T_Controle(10, 'Urinaire', '4/11/2005', 'P'))))); INSERT INTO Equipe VALUES(5, 'FRA', 'Aviron', TAB_Athlete(T_Athlete(9, 'Dupond', 'Albert', TAB_Controle(T_Controle(11, 'Urinaire', '3/11/2005', 'N'))), T_Athlete(7, 'Martin', 'Maurice', TAB_Controle(T_Controle(12, 'Urinaire', '3/11/2005', 'N'))))); INSERT INTO Equipe VALUES(6, 'FRA', 'Basket', TAB_Athlete(T_Athlete(10, 'Bilba', 'Jim', TAB_Controle(T_Controle(13, 'Urinaire', '3/11/2005', 'N'))), T_Athlete(11, 'Parker', 'Tony', TAB_Controle(T_Controle(14, 'Urinaire', '3/11/2005', 'N'))), T_Athlete(12, 'Diaw', 'Boris', TAB_Controle()))); -- Requêtes -- 1 SELECT e.CodePays, e.Sport, a.Nom, a.Prenom FROM Equipe e, TABLE(e.Athletes) a; -- 2 SELECT e.CodePays, e.Sport, CURSOR(SELECT a.Nom, a.Prenom FROM TABLE(e.Athletes) a) FROM Equipe e; Cette seconde requête affiche d’abord le pays et le sport de la première équipe, puis sa liste d’athlètes (noms et prénoms des athlètes de l’équipe), ensuite le pays et le sport de la seconde équipe et ses athlètes, et ainsi de suite. La première requête donne un résultat sous forme tabulaire avec le pays, le sport, le nom et le prénom de chaque athlète. -- 3 SELECT e.CodePays, a.Nom, a.Prenom FROM Equipe e, TABLE(e.Athletes) a ORDER BY e.CodePays, a.Nom, a.Prenom; -- 4 SELECT e.NumEq, c.NumCtrl, c.DateCtrl FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles) c; -- 5 SELECT e.NumEq, CURSOR(SELECT CURSOR(SELECT c.NumCtrl, c.DateCtrl FROM TABLE(a.Controles) c) FROM TABLE(e.Athletes) a) FROM Equipe e; -- 6 SELECT a.Nom, a.Prenom FROM TABLE (SELECT Athletes FROM Equipe WHERE NumEq = 2) a; Bases de données avancées – TD n°2 4 -- 7 SELECT * FROM TABLE (SELECT Controles FROM TABLE (SELECT Athletes FROM Equipe WHERE NumEq = 3) WHERE NumDossard = 4); -- 8 SELECT COUNT(*) FROM TABLE (SELECT Athletes FROM Equipe WHERE NumEq = 6); -- 9 SELECT CodePays, COUNT(*) FROM Equipe e, TABLE (e.Athletes) GROUP BY CodePays; -- 10 SELECT a.Nom, a.Prenom, COUNT(*) FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles) GROUP BY a.Nom, a.Prenom ORDER BY a.Nom, a.Prenom; -- 11 SELECT a.Nom, a.Prenom, COUNT(*) FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles) c WHERE c.Resultat = 'P' GROUP BY a.Nom, a.Prenom ORDER BY a.Nom, a.Prenom; -- 12 SELECT e.CodePays, COUNT(*) FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles) c WHERE c.Resultat = 'P' GROUP BY e.CodePays ORDER BY e.CodePays; -- 13 SELECT a.Nom, a.Prenom, MAX(c.DateCtrl) FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles) c GROUP BY a.Nom, a.Prenom ORDER BY a.Nom, a.Prenom; -- 14 SELECT e.NumEq, e.CodePays, e.Sport FROM Equipe e, TABLE(e.Athletes) a, TABLE(a.Controles) GROUP BY e.NumEq, e.CodePays, e.Sport HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM Equipe e1, TABLE(e1.Athletes) a1, TABLE(a1.Controles) GROUP BY e1.NumEq); Bases de données avancées – TD n°2 5 -- 15 SELECT NumEq, CodePays, Sport FROM Equipe e1 WHERE NOT EXISTS( SELECT * FROM Equipe e2, TABLE(e2.Athletes) a, TABLE(a.Controles) c WHERE e1.NumEq = e2.NumEq AND c.Resultat = 'P'); -- Mises à jour -- 1 INSERT INTO TABLE (SELECT Athletes FROM Equipe WHERE NumEq = 6) VALUES(30, 'Pietrus', 'Michael', TAB_Controle()); -- 2 INSERT INTO TABLE ( SELECT a.Controles FROM Equipe, TABLE(Athletes) a WHERE NumEq = 6 AND NumDossard = 30) VALUES(15, 'Urinaire', '23/01/2015', 'N'); INSERT INTO TABLE ( SELECT a.Controles FROM Equipe, TABLE(Athletes) a WHERE NumEq = 6 AND NumDossard = 30) VALUES(16, 'Sanguin', '23/01/2015', 'N'); -- 3 DELETE FROM TABLE ( SELECT a.Controles FROM Equipe, TABLE(Athletes) a WHERE NumEq = 3 AND NumDossard = 4); -- 4 UPDATE TABLE ( SELECT a.Controles FROM Equipe, TABLE(Athletes) a WHERE NumEq = 4 AND NumDossard = 6) SET Resultat = 'N' WHERE DateCtrl < '04/11/2015'; Bases de données avancées – TD n°2 6 uploads/Management/ chapitre3-td2-bda-3ing-glsi-c 2 .pdf