Remerciez-le!

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

Conception de bases de données http://bdd.crzt.fr Conception de bases de donnée

Conception de bases de données http://bdd.crzt.fr Conception de bases de données : Exercices de synthèse STÉPHANE CROZAT 30 juillet 2014 Table des matières 3 Objectifs 7 I - Exercice : Zéro Faute 9 II - Exercice : Annuaire 11 III - Exercice : Métiers 13 IV - Base de données et programmation 15 V - Expertises 19 A. Mise en situation.........................................................................................19 B. Étude relationnel : Rétro-conception UML.......................................................20 C. Exercice : Étude relationnel : Dépendances fonctionnelles................................20 D. Exercice : Étude relationnel : Forme normale.................................................21 E. Exercice : Étude relationnel : Normalisation....................................................21 F. Étude d'exploitation : Implémentation SQL.....................................................21 G. Étude d'exploitation : Surveillance des réserves..............................................22 H. Étude d'exploitation : Comptes « lourds »......................................................22 I. Étude d'exploitation : Transferts....................................................................22 J. Exercice : Étude d'exploitation : Vigilance.......................................................22 K. Exercice : Étude technologique : Fonction Access............................................23 L. Exercice : Étude technologique : Trigger Oracle...............................................23 M. Exercice : Étude technologique : LAPP...........................................................24 N. Étude Relationnel-Objet...............................................................................25 O. Étude attributs multi-valués.........................................................................25 VI - Étudiants et UVs 27 A. Exercice.....................................................................................................27 B. Exercice.....................................................................................................28 C. Exercice.....................................................................................................29 VII - Gestion de comptes bancaires 31 A. Récupération Access....................................................................................31 B. Migration Oracle..........................................................................................33 VIII - Super-héros 35 4 A. Super-héros relationnels..............................................................................35 B. Super-héros relationnels-objets....................................................................36 C. Super-héros sans tête.................................................................................37 D. Super-lents................................................................................................38 E. Super-transferts..........................................................................................39 IX - Friends 41 A. Exercice.....................................................................................................41 B. Exercice.....................................................................................................43 X - E.T. 45 A. Exercice.....................................................................................................45 B. Exercice.....................................................................................................47 XI - Attributs multi-valués 49 A. Documentum..............................................................................................49 B. Fichier CSV.................................................................................................50 C. Insertion de collections à partir de requêtes SELECT........................................52 D. Attributs multi-valués et relationnel..............................................................53 E. Attributs multi-valués et relationnel-objet......................................................55 Solution des exercices 57 Solution des exercices 93 Glossaire 99 Signification des abréviations 101 Index 103 5 Objectifs Ce module permet de réviser par des exercices d'application l'ensemble des concepts et technologies étudiés dans le cadre de la formation à la conception de bases de données (correspondant à l'unité de valeur NF17 à l'UTC). 7 I - Exercice : Zéro Faute I [40 min] L'entreprise ZéroDéfo veut répertorier ses fautes de production. Une faute est définie dans le manuel qualité de l'entreprise comme « un défaut constaté sur une pièce produite en fin de chaîne ». L'entreprise veut associer les fautes aux produits concernés. Chaque faute est classifiée dans des catégories et sous-catégories. Chaque produit est basé sur un modèle.  Pour chaque modèle, on veut gérer son code constitué de 8 caractères alphanumériques, son nom et la date de mise sur le marché.  Pour chaque produit, on veut connaître le modèle associé, le numéro de série (6 chiffres) et le numéro de produit (max. 4 caractères) ainsi que l'année de production. Un produit est identifié par son numéro de série et son numéro de produit. Plusieurs produits partagent le même numéro de série (tous les produits de cette série), et deux produits peuvent avoir (par hasard) le même numéro de produit, dans des séries différentes (qui ont adopté le même système de codage des produits).  Une faute concerne toujours un produit. Elle possède un code unique, un titre et la date de détection. Elle peut éventuellement avoir un commentaire et la date de réparation si le produit a été réparé.  Les fautes sont classifiées dans des sous-catégories et chaque sous- catégories fait partie d'une catégorie. Une faute est toujours classifiée dans une catégorie au moins (elle peut être classifiée dans plusieurs).  Les catégories et les sous-catégories possèdent un nom et, optionnellement, une description. Q u e s t i o n 1 [Solution n°1 p 57] Proposez un diagramme UML répondant aux besoins de cette entreprise. Q u e s t i o n 2 [Solution n°2 p 57] Proposez un modèle relationnel en 3NF basé sur votre modèle UML. Q u e s t i o n 3 [Solution n°3 p 58] Écrivez le code SQL LDD permettant de créer la base de données correspondant au modèle relationnel. Q u e s t i o n 4 [Solution n°4 p 58] Écrivez une requête SQL permettant de lister le nombre de fautes par nom de modèle et par numéro de série. 9 Exercice : Zéro Faute 10 II - Exercice : Annuaire II [30 minutes] Soit le modèle relationnel suivant représentant l'annuaire de l'UTC : 1 personne (nom, prenom, localisation, departement, unite) Soit un extrait de fichier CSV issu de la base de données (Un fichier CSV, Coma Separated Value, est un enregistrement d'une table dans un fichier texte). 1 Dupont;Stéphane;PG M 290;TSH;ICS 2 Dupuis;Katie;PG J 178;TSH;ICS 3 Durand;Paul;CR C 276;GI;Heudiasyc 4 Dusse;Jean-Claude;CR A 320;GI;Heudiasyc 5 Dutour;Jean;CR C 198;GI;LMAC Q u e s t i o n 1 [Solution n°5 p 59] En quelle(s) forme(s) normale(s) est ce modèle ? Q u e s t i o n 2 [Solution n°6 p 59] Écrivez l'opération d'algèbre relationnelle permettant de renvoyer les noms des personnes situées dans le même bureau. Q u e s t i o n 3 [Solution n°7 p 59] Écrivez une transaction permettant de copier tous les enregistrements dans une table de sauvegarde personne_backup de même schéma que personne, juste après avoir vidé personne_backup. En cas de panne pendant la transaction, le système doit revenir à son état initial, c'est à dire que personne_backup n'a été ni vidé ni rempli des données de "personne". Q u e s t i o n 4 [Solution n°8 p 59] Implémentez une solution d'optimisation permettant d'améliorer la vitesse d'exécution suivante (en dehors de la vue concrète) : 1 SELECT * FROM Personne 2 ORDER BY Nom, Prenom Q u e s t i o n 5 [Solution n°9 p 59] Écrivez un déclencheur (syntaxe Oracle) permettant d'assurer que les chaînes du champ "departement" seront toujours en majuscule (Utiliser la fonction UPPER). 11 Exercice : Métiers 12 III - Exercice : Métiers III Soit une base de données composée de deux tables tPersonne et tMetier. 1 tPersonne (#numero, nom, prenom, metier=>tMetier) 2 tMetier (#code, intitule) On souhaite écrire 6 programmes qui permettent d'accéder à BD afin d'afficher la liste des noms et des métiers sur la sortie standard. Q u e s t i o n 1 [Solution n°10 p 59] Écrivez un programme PHP permettant d'interroger la base de données implémentée sous MySQL. Résultat attendu en HTML : 1 <html> 2 Dupont Ingénieur 3 Durand Architecte 4 Dupuis Secrétaire 5 ... 6 </html> Q u e s t i o n 2 [Solution n°11 p 60] Écrivez un programme PHP permettant d'interroger la base de données implémentée sous PosgreSQL (et d'obtenir le même résultat). Q u e s t i o n 3 [Solution n°12 p 60] Écrivez un programme PHP permettant d'interroger la base de données implémentée sous Oracle (et d'obtenir le même résultat). Q u e s t i o n 4 [Solution n°13 p 60] Écrivez un programme VBA permettant d'interroger la base de données implémentée sous Access. Résultat attendu sur la sortie standard (utilisez Debug.Print et l'opérateur de concaténation &) : 1 Dupont Ingénieur 2 Durand Architecte 3 Dupuis Secrétaire 4 ... 13 Q u e s t i o n 5 [Solution n°14 p 60] Écrivez un programme PL/SQL permettant d'interroger la base de données implémentée sous Oracle. Résultat attendu sur la sortie standard (utilisez DBMS_OUTPUT.PUT_LINE et l'opérateur de concaténation ||) : 1 Dupont Ingénieur 2 ... Q u e s t i o n 6 [Solution n°15 p 61] Écrivez un programme Java permettant d'interroger la base de données implémentée sous Oracle. Résultat attendu sur la sortie standard (utilisez System.out.println et l'opérateur de concaténation +) : 1 Dupont Ingénieur 2 ... Exercice : Métiers 14 IV - Base de données et programmation IV Soit une base de données composée d'une unique table T, contenant un unique attribut A. 1 T(#A:string) On souhaite écrire 6 programmes qui permettent d'accéder à la base de données, afin d'afficher la liste des valeurs de A. Exercice 1 : PHP & PosgreSQL [Solution n°1 p 93] Écrivez un programme PHP permettant d'interroger la base de données implémentée sous PosgreSQL. <?php $conn=pg_connect($host,$user,$passwd,$bdd); $query = " "; $result=pg_query( , ); echo "<html>"; while($row = pg_fetch_row( )) { echo " "; } echo "</html>"; pg_close($conn); ?> Exercice 2 : PHP & MySQL [Solution n°2 p 93] Écrivez un programme PHP permettant d'interroger la base de données implémentée sous MySQL. <?php $conn=mysql_connect($host,$user,$passwd); mysql_select_db($bdd, $conn); $query = " "; $result=mysql_query( , ); echo "<html>"; 15 while($row = mysql_fetch_row( )) { echo " "; } echo "</html>"; mysql_close($conn); ?> Exercice 3 : PHP & Oracle [Solution n°3 p 93] Écrivez un programme PHP permettant d'interroger la base de données implémentée sous Oracle. <?php //définition des variables de connexion à ajouter $conn=oci_connect($user, $passwd, $bd)) $query = " "; $statement = oci_parse( , ); oci_execute($statement); echo "<html>"; while($row = oci_fetch_array( )) { echo " "; } echo "</html>"; oci_close($conn); ?> Exercice 4 : Access & VBA [Solution n°4 p 94] Écrivez un programme VBA permettant d'interroger la base de données implémentée sous Access. Sub printA() vSql = " " Set vRs = CurrentDb.CreateQueryDef("", ).OpenRecordset For i = 1 To vRs.RecordCount Debug.Print ! vRs.MoveNext Next i End Sub Base de données et programmation 16 Exercice 5 : Oracle & PL/SQL uploads/Philosophie/ conception-de-bases-de-donnees-exercices-de-synthese-stephane-crozat-http.pdf