420-5D5 : Oracle – Labo.example

Contexte

Oracle.Tutorial fournit une base de données ‘Exemple’ pour l’expérimentation avec le SGBD Oracle.

Nous utiliserons cette DB pour les apprentissages sur les fonctions, procédures, déclencheurs, …

Les directives pour l’installation de la BD sont disponibles ici

Voici le schéma de la base de données

Laboratoire partie A

Il faut créer, sur le SGBD à 192.168.138.58 (user:sys, pws: 4205D3) une PDB nommé ‘M’+ votre matricule, avec un utilisateur nommé ‘ot’.

-- Par exemple,
show con_name;
-- Si pas au contexte racine alors:
ALTER SESSION SET CONTAINER = CDB$ROOT

CREATE PLUGGABLE DATABASE M12345678 ADMIN ot IDENTIFIED BY password create_file_dest='/home/oracle' ;
show pdbs;
ALTER SESSION SET CONTAINER = M12345678 ;
ALTER DATABASE OPEN;
GRANT CONNECT, RESOURCE, DBA TO ot;

-- Puis, ouvrir une nouvelle connexion avec l'utilisateur 'ot' 

Créer le schéma de la base de données

-- regions
CREATE TABLE regions
  (
    region_id NUMBER GENERATED BY DEFAULT AS IDENTITY
    START WITH 5 PRIMARY KEY,
    region_name VARCHAR2( 50 ) NOT NULL
  );
-- countries table
CREATE TABLE countries
  (
    country_id   CHAR( 2 ) PRIMARY KEY  ,
    country_name VARCHAR2( 40 ) NOT NULL,
    region_id    NUMBER                 , -- fk
    CONSTRAINT fk_countries_regions FOREIGN KEY( region_id )
      REFERENCES regions( region_id ) 
      ON DELETE CASCADE
  );

-- location
CREATE TABLE locations
  (
    location_id NUMBER GENERATED BY DEFAULT AS IDENTITY START WITH 24 
                PRIMARY KEY       ,
    address     VARCHAR2( 255 ) NOT NULL,
    postal_code VARCHAR2( 20 )          ,
    city        VARCHAR2( 50 )          ,
    state       VARCHAR2( 50 )          ,
    country_id  CHAR( 2 )               , -- fk
    CONSTRAINT fk_locations_countries 
      FOREIGN KEY( country_id )
      REFERENCES countries( country_id ) 
      ON DELETE CASCADE
  );
-- warehouses
CREATE TABLE warehouses
  (
    warehouse_id NUMBER 
                 GENERATED BY DEFAULT AS IDENTITY START WITH 10 
                 PRIMARY KEY,
    warehouse_name VARCHAR( 255 ) ,
    location_id    NUMBER( 12, 0 ), -- fk
    CONSTRAINT fk_warehouses_locations 
      FOREIGN KEY( location_id )
      REFERENCES locations( location_id ) 
      ON DELETE CASCADE
  );
-- employees
CREATE TABLE employees
  (
    employee_id NUMBER 
                GENERATED BY DEFAULT AS IDENTITY START WITH 108 
                PRIMARY KEY,
    first_name VARCHAR( 255 ) NOT NULL,
    last_name  VARCHAR( 255 ) NOT NULL,
    email      VARCHAR( 255 ) NOT NULL,
    phone      VARCHAR( 50 ) NOT NULL ,
    hire_date  DATE NOT NULL          ,
    manager_id NUMBER( 12, 0 )        , -- fk
    job_title  VARCHAR( 255 ) NOT NULL,
    CONSTRAINT fk_employees_manager 
        FOREIGN KEY( manager_id )
        REFERENCES employees( employee_id )
        ON DELETE CASCADE
  );
-- product category
CREATE TABLE product_categories
  (
    category_id NUMBER 
                GENERATED BY DEFAULT AS IDENTITY START WITH 6 
                PRIMARY KEY,
    category_name VARCHAR2( 255 ) NOT NULL
  );

-- products table
CREATE TABLE products
  (
    product_id NUMBER 
               GENERATED BY DEFAULT AS IDENTITY START WITH 289 
               PRIMARY KEY,
    product_name  VARCHAR2( 255 ) NOT NULL,
    description   VARCHAR2( 2000 )        ,
    standard_cost NUMBER( 9, 2 )          ,
    list_price    NUMBER( 9, 2 )          ,
    category_id   NUMBER NOT NULL         ,
    CONSTRAINT fk_products_categories 
      FOREIGN KEY( category_id )
      REFERENCES product_categories( category_id ) 
      ON DELETE CASCADE
  );
-- customers
CREATE TABLE customers
  (
    customer_id NUMBER 
                GENERATED BY DEFAULT AS IDENTITY START WITH 320 
                PRIMARY KEY,
    name         VARCHAR2( 255 ) NOT NULL,
    address      VARCHAR2( 255 )         ,
    website      VARCHAR2( 255 )         ,
    credit_limit NUMBER( 8, 2 )
  );
-- contacts
CREATE TABLE contacts
  (
    contact_id NUMBER 
               GENERATED BY DEFAULT AS IDENTITY START WITH 320 
               PRIMARY KEY,
    first_name  VARCHAR2( 255 ) NOT NULL,
    last_name   VARCHAR2( 255 ) NOT NULL,
    email       VARCHAR2( 255 ) NOT NULL,
    phone       VARCHAR2( 20 )          ,
    customer_id NUMBER                  ,
    CONSTRAINT fk_contacts_customers 
      FOREIGN KEY( customer_id )
      REFERENCES customers( customer_id ) 
      ON DELETE CASCADE
  );
-- orders table
CREATE TABLE orders
  (
    order_id NUMBER 
             GENERATED BY DEFAULT AS IDENTITY START WITH 106 
             PRIMARY KEY,
    customer_id NUMBER( 6, 0 ) NOT NULL, -- fk
    status      VARCHAR( 20 ) NOT NULL ,
    salesman_id NUMBER( 6, 0 )         , -- fk
    order_date  DATE NOT NULL          ,
    CONSTRAINT fk_orders_customers 
      FOREIGN KEY( customer_id )
      REFERENCES customers( customer_id )
      ON DELETE CASCADE,
    CONSTRAINT fk_orders_employees 
      FOREIGN KEY( salesman_id )
      REFERENCES employees( employee_id ) 
      ON DELETE SET NULL
  );
-- order items
CREATE TABLE order_items
  (
    order_id   NUMBER( 12, 0 )                                , -- fk
    item_id    NUMBER( 12, 0 )                                ,
    product_id NUMBER( 12, 0 ) NOT NULL                       , -- fk
    quantity   NUMBER( 8, 2 ) NOT NULL                        ,
    unit_price NUMBER( 8, 2 ) NOT NULL                        ,
    CONSTRAINT pk_order_items 
      PRIMARY KEY( order_id, item_id ),
    CONSTRAINT fk_order_items_products 
      FOREIGN KEY( product_id )
      REFERENCES products( product_id ) 
      ON DELETE CASCADE,
    CONSTRAINT fk_order_items_orders 
      FOREIGN KEY( order_id )
      REFERENCES orders( order_id ) 
      ON DELETE CASCADE
  );
-- inventories
CREATE TABLE inventories
  (
    product_id   NUMBER( 12, 0 )        , -- fk
    warehouse_id NUMBER( 12, 0 )        , -- fk
    quantity     NUMBER( 8, 0 ) NOT NULL,
    CONSTRAINT pk_inventories 
      PRIMARY KEY( product_id, warehouse_id ),
    CONSTRAINT fk_inventories_products 
      FOREIGN KEY( product_id )
      REFERENCES products( product_id ) 
      ON DELETE CASCADE,
    CONSTRAINT fk_inventories_warehouses 
      FOREIGN KEY( warehouse_id )
      REFERENCES warehouses( warehouse_id ) 
      ON DELETE CASCADE
  );

Insérer les enregistrements dans la BD

Il faut utiliser le contenu du fichier ot_data.sql

NOTE: Vous allez rencontrer des problèmes:

• Format des dates
• Référence à la table ‘EMPLOYEES’

À VOUS DE TROUVER DES SOLUTIONS

Tester les données en affichant la liste des employés.

Au besoin, un DROP et on recommence

DROP TABLE order_items;  
DROP TABLE orders;
DROP TABLE inventories;
DROP TABLE products;
DROP TABLE product_categories;
DROP TABLE warehouses;
DROP TABLE employees;
DROP TABLE contacts;
DROP TABLE customers;
DROP TABLE locations;
DROP TABLE countries;
DROP TABLE regions;

Laboratoire partie B

Mettre en place l’infrastructure requise pour les parties évaluées du cours

• Créer une VM dans le cloud de google, sur un projet CSTJ
  • 2 CPU
  • 4 GO de RAM
  • Disque de 40GO
• Installer un OS (Ubuntu?)
• Installer docker.io
• Lancer un conteneur Oracle 23c ou,
  • pour les plus téméraires, installer Oracle 23c dans la VM
• Définir une règle ‘Ingress’ dans le ‘firewall’ du projet google cloud, de type ‘TAG’, pour le port utilisé par Oracle et l’associer à votre VM.
• Mettre en place la base de données Démo
• Tester une connexion à partir de Oracle SQL Developper sur votre poste de travail
  • En cas de problème, il faut proposer une solution, firewall, docker, …?
    • Faire un test à partir d’un terminal sur le serveur: $ sqlplus …
• Voir le document – connexion avec php pour la suite …

NOTE TELLEMENT IMPORTANTE: Il faut FERMER la VM lorsqu’elle n’est pas utilisée!!!

FIN DU DOCUMENT

--- Scratch PAD

--- Exemple d'un champ de type AUTO-INCREMENT
--- GENERATED BY DEFAULT AS IDENTITY (facultaitf)
--- GENERATED ALWAYS AS IDENTITY (séquence automatique obligatoire)
---  (START WITH 10 INCREMENT BY 5)
CREATE TABLE Pays (
    pays_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY ,
    nom VARCHAR2(80)
);

INSERT INTO pays(nom) VALUES ('Québec');
INSERT INTO pays(pays_id, nom) VALUES (2, 'Canada');
SELECT * FROM pays;

----------------------------------------------------------------------------
----------------------------------------------------------------------------
--- Des bandes dessinées
CREATE TABLE Auteurs (
    auteur_id NUMBER PRIMARY KEY,
    nom VARCHAR2(60)
);

INSERT INTO Auteurs (auteur_id, nom) VALUES (1, 'Herge');

CREATE TABLE Albums (
    album_id NUMBER PRIMARY KEY,
    titre VARCHAR2(255),
    annee_publication NUMBER,
    auteur_id NUMBER,
    FOREIGN KEY (auteur_id) REFERENCES Auteurs(auteur_id),
    nombre_pages NUMBER,
    isbn VARCHAR2(20),
    photo_couverture VARCHAR2(255)
);

----

INSERT INTO Albums (titre, annee_publication, auteur_id, nombre_pages, isbn, photo_couverture)

VALUES

    ('Tintin au pays des Soviets', 1929, 1, 144, '978-2203001016', 'tintin_soviets.jpg'),
    ('Tintin au Congo', 1931, 1, 62, '978-2203001023', 'tintin_congo.jpg'),
    ('Tintin en Amérique', 1932, 1, 120, '978-2203001030', 'tintin_amerique.jpg'),
    ('Les Cigares du Pharaon', 1934, 1, 128, '978-2203001047', 'tintin_cigares_pharaon.jpg'),
    ('Le Lotus bleu', 1936, 1, 80, '978-2203001054', 'tintin_lotus_bleu.jpg'),
    ('L''Oreille cassée', 1943, 1, 62, '978-2203001061', 'tintin_oreille_cassee.jpg'),
    ('L''Île noire', 1938, 1, 62, '978-2203001078', 'tintin_ile_noire.jpg'),
    ('L''Étoile mystérieuse', 1942, 1, 62, '978-2203001108', 'tintin_etoile_mysterieuse.jpg'),
    ('Le Secret de La Licorne', 1943, 1, 62, '978-2203001115', 'tintin_secret_licorne.jpg'),
    ('Le Trésor de Rackham le Rouge', 1944, 1, 62, '978-2203001122', 'tintin_tresor_rackham_rouge.jpg');

--- Modifier le champ album_id pour le rendre AUTO-INCREMENT
ALTER TABLE Albums
ALTER COLUMN album_id
SET GENERATED BY DEFAULT;
--- Pour enlever le compteur
ALTER TABLE Albums
ALTER COLUMN album_id
DROP IDENTITY IF EXISTS;

------------------------------------------------

select titre, auteurs.nom from albums inner JOIN auteurs ON albums.auteur_id = auteurs.auteur_id ;   
select al.titre, a.nom from albums al inner JOIN auteurs a ON al.auteur_id = a.auteur_id ;   

.