Oracle et le Modle Objet Relationnel Gabriel MOPOLOMOKE

Oracle et le Modèle Objet Relationnel Gabriel MOPOLO-MOKE prof. Associé UNSA 2005/2006 Page 1 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel

15. Exercices n PLAN • Objectif du TP • Définition du schéma d'objets • PL/SQL et Procédures stockées • Insertion des objets • Consultation des objets • Mise à jour des objets • Les vues Page 2 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel

15. Exercices n Objectif du TP • Manipuler une base de données objet relationnelle • Les points que nous visons sont : – concevoir et créer le schéma d'une base de données objet relationnelle – consulter les objets du schéma en exploitant entre autre les nouveaux opérateurs tels que TABLE, VALUE, . . . – effectuer des insertions, mises à jour et suppressions dans ce schéma en exit – Manipuler des objets dans PL/SQL – Manipuler les vues objets sur les tables rélationnelles – manipuler des objets volumineux Page 3 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel

15. Exercices n Définition du schéma d'objets • Schéma conceptuel EMPLOYE # * ° * * EMPNO ENAME PRENOMS {de 0 à 4 prénoms} JOB SAL CV DATE_NAISS DATE_EMB Est employé dans emploie DEPT # * * # * ° Page 4 DEPTNO DNAME LOC : indique un identifiant : attribut obligatoire : attribut facultatif Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel

15. Exercices n Définition du schéma d'objets • Description des entités Nom entité Nom des champs type Libellé EMPLOYE EMPNO NUMBER(4) Numéro Employé ENAME varchar 2(15) Nom EMPLOYE : En lettre capitales PRENOMS varray tableau de 4 prénom max JOB Varchar 2(20) Métier de l’employé {Ingénieur, Secrétaire, Directeur, Planton, PDG} SAL number(7, 2) Salaire entre 1500 et 15000 CV CLOB CV DATE_NAISSS date Date de Naissance DATE_EMB date Date embauche : doit être supérieure Date_Naiss number(4) Nr. de département doit DNAME varchar 2(30) Nom du département {Recherche, Resources, Humaines, Marketing, Ventes, Finance} LOC varchar 2(30) Localisation du département DEPTNO entre 1000 et 9999 NOTE : prendre en compte les contraintes attributs UNIQUE ou OBLIGATOIRE schéma page précédente) Page 5 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel être (voir

15. Exercices n Définition du schéma d'Objets • Suggestions – Toutes les tables doivent être des tables objets – les liens 1: N ou N: M sont systématiquement modélisés via le type REF si 1 ou NESTED TABLE si N – les déclarations des types FORWARD ou INCOMPLETS (EMPLOYE_t, DEPT_t) sont à faire – les éléments des Types Tables (listes) sont des références vers les types incomplets précités – les méthodes sont à déclarer dès la création des types – l'action de chaque méthode doit être limitée RESTRICT_REFERENCES – chaque "Type objet" doit contenir une méthode de comparaison MAP ou ORDER – les index seront localisés dans le Tablespace TS_INDEX_RES et les tables dans le Tablespace TS_TABLE_RES les index et les données des LOB internes sont à localiser dans le tablespace TS_LOB_RES. – NOTE : LIRE L'ENSEMBLE DU TP PUIS CHAQUE SECTION ENTIEREMENT Page 6 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel

15. Exercices n Création des types • Les étapes 1. Créer les types FORWARD ou INCOMPLETS appropriés. Il n'est pas nécessaire de définir tous les types en FORWARD 2. Créer le type ARRAY tab_prenoms_t nécessaire pour gérer la liste des PRENOMs 3. Créer le Type Table (liste) nécessaire : ces éléments seront des références vers des EMPLOYE_t 4. Définir maintenant vos types EMPLOYE_T, DEPT_T, … Vous devez y déclarer les champs ou attributs des types, les méthodes et les liens ou Nested Tables Note : pour les méthodes et les liens, voir plus loin. Page 7 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel

15. Exercices n Création des types • Les méthodes 1. Les méthodes de consultation – introduire une méthode de classe qui affiche les informations (, . . . ) sur un DEPT connaissant son numéro • static function get. Dept (deptno) return boolean; – écrire une méthode de classe qui affiche pour un DEPT donné, le nom et les prénoms de chaque Employé qui y travaillent. • Static function get. Info. Emp(deptno) return boolean 2. Les méthodes pour ordonner les éléments des types – écrire pour chaque type une méthode ORDER ou MAP. Il est nécessaire d'utiliser au moins une fois chacune de ces méthodes. Page 8 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel

15. Exercices n Création des types • Les méthodes 3. Les méthodes de gestion des Liens Introduire pour chaque Lien Multivalué les procédures suivantes : - add. Link. Nom. Colonne. Nested(Ref. Type 1 REF type); -- pour ajouter dans la liste - delete. Link. Nom. Colonne. Nested (Ref. Type 1 REF type); -- suppression dans un lien - update. Link. Nom. Colonne. Nested (Ref. Type 1 REF type, Ref. Type 2 REF type); -- modification du lien 4. Notes • ces méthodes DOIVENT être en Lecture seule (PRAGMA RESTRICT_REFERENCES) sauf celles décrites en 3. • dans cette phase, aucune méthode n'a encore été implémentée. Il ne s'agit que des déclarations. Page 9 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel

15. Exercices n Création des tables • Créer les tables EMPLOYE_O, DEPT_O comme étant des tables objets • Les contraintes d'intégrités – Définir les contraintes d'intégrités d'entités et de références sur chacune des tables – Définir les contraintes d'intégrités de domaine (cf. la description des champs page 4 et page 5) • Les Nested Tables – Donner les noms de segments à toutes vos Nested Tables – Format du nom : store. Nom. Col. Nested • Les LOB internes PCTVERSION doit être à 30 Page 10 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel

15. Exercices n Création des tables • Localisation des objets – Les tuples des tables objets sont à localiser dans le Tablespace TS_TABLE_RES – les index des tables objets y compris ceux créés implicitement sont à localiser dans TS_INDEX_RES – les données et les index des LOB internes sont à localiser dans le Tablespace TS_LOB_RES n Création index • Créer un index unique sur la colonne dname de DEPT_o • créer un index sur la colonne implicite Nested_table_id de la Nested Table. Peut - il être unique ? Page 11 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel

15. Exercices n Insertion des objets • Insérer 2 ou 3 objets dans chacune des tables créées précédemment. L'intégrité des objets doit être assurée n Mise à jour des objets • Modifier la localité d'un département connaissant son nom • Modifier la date d'embauche d'un Employé connaissant son nom sachant qu’il doit travailler dans l’un des départements suivants : ‘Recherche’, ‘Finance’ ou ‘Ressources Humaines’ • Supprimer un DEPT Page 12 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel

15. Exercices n Consultation des objets • Faire un Listing des DEPTs triés par nom • Pour un DEPT donné, lister tous les EMPLOYEs qui y travaillent n PL/SQL et Procédures stockées • Implémenter les méthodes décrites plus haut au niveau des types DEPT et EMPLOYE • tester les méthodes Page 13 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel

15. Exercices n Les vues • créer une table relationnelle (non objet) DEPT_r. La structure de cette table est celle du type DEPT_t sans les liens • Insérer 3 ou 4 objets dans cette table • Créer une vue objet DEPT_v masquant toutes les colonnes de cette table. Le type objet associé doit s'appeler old_DEPT_t. Y insérer 2 lignes • Créer un trigger INSTEAD OF qui permet d'insérer à travers la vue dans la table DEPT_r et DEPT_o • Manipuler (insert, update, delete, select) via la vue Page 14 Copyright G. Mopolo-Moké Oracle et le modèle Objet Relationnel