Espace technique ORACLE

Objets Oracle, cas pratique

Dans cette étape du cas pratique, je vous propose de créer quelques objets DB :

1. Créer un trigger sur la table Affectation: A chaque insertion dans cette table, le trigger doit renseigner la date d'affectation à la date du jour.
2. Créer une vue qui donne les informations suivantes :
   Nom employé
   Prénom employé
   Libellé du service
   Titre de la fonction
3. Créer un compteur (Séquence + Trigger) pour gérer automatiquement les N° employés sous format EMP0000001, EMP0000002 ...

PL/SQL Procédures

Dans cette étape du cas pratique, je vous propose de developper les procédures PL/SQL suivantes :

1. EMPLOYE_INSERT : Insérer un employé dans la table EMPLOYE.
2. PROJET_INSERT : Insérer un projet dans la table PROJET.
3. AFFECTATION_UPDATE : Mettre à jour la priorité dans la table AFFECTATION.
4. AFFECTATION_EMP_PRJ : Affecter un employé à un projet.

Requêtes d'extraction des données

Dans cette étape du cas pratique, je vous propose de developper des requêtes SQL. Les premières sont faciles, mais certaines sont interessantes ;-)

Si vous n'êtes pas interessé par les étapes précédentes de ce cas pratique, envoyez moi un email et je vous enverrai les scripts de création des tables et d'insertion des données.

1. Extraire dans un fichier texte les noms concaténés avec les prénoms des employés dont le nom commence par « D », ordonnées par noms décroissants.

2. Extraire les noms, prénoms et dates d'entrées des employés qui sont dans la société depuis le 01/01/2000, ordonnés par date d'entrée décroissante puis noms croissants.

3. Extraire la liste des employés du service informatique qui travaillent sur le projet WEB.

4. Extraire la liste des noms, prénoms des employés et les libelles de leurs services. Il faut afficher même les services qui n'ont pas d'employé.

5. Extraire pour chaque projet, son code et le nombre d'employés travaillant sur ce projet.

6. Extraire la liste des employés ne travaillant sur aucun projet.

7. Extraire les numéros des employés qui travaillent sur deux projets ou plus.

8. Afficher le nom et prénom et la date d'entrée de l'employé le plus ancien dans la société.

9. Afficher la liste des codes fonctions et la moyenne des salaire de employée pour chaque fonction.

10. Afficher pour chaque projet le nombre d'employés qui travaille sur ce projet à chaque fin de mois

Création des objets bases de données

Dans cette étape du cas pratique, on arrive à la génération du modèle physique de données à partir du modèle relationnel.

Ci-dessous les scripts pour générer les objets de la base, il faut se connecter à la base Oracle avec l'utilisateur ATELIER_USER, et lancer les scripts :

• MLD_EMP : Script qui permet de créer les tables et les contraintes
• scripts_insert : Fichier zip qui contient les scripts d'insertion des données dans les tables. Il faut insérer les données en prenant en compte les contraintes d'intégrité référentielles.

GRANT CONNET et RESOURCE à l'utilisateur ORACLE

Dans le cadre du cas pratique, pour pouvoir se connecter avec un utilisateur et créer des objets, il faut se connecter en tant qu'utilisateur SYSTEM et lancer la commande :
GRANT CONNECT, RESOURCE TO ATELIER_USER;

• CONNECT est un rôle qui permet à l'utilisateur de se connecter et d'ouvrir une session dans le serveur ORACLE.
• RESOURCE permet à l'utilisateur de créer des objets ( Tables, Vues, Triggers ... ).

Un ROLE est un ensemble de privilèges ( Système et/ou Objets ) et de ROLES. Plus d'informations sur les rôles :
http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10743/security.htm#i14426

CREATE USER, cas pratique

Dans le cadre du cas pratique, pour se connecter à une base de données ORACLE, il faut fournir un nom d'utilisateur et un mot de passe. La commande pour créer un utilisateur est :

CREATE USER nom_utilisateur IDENTIFIED BY password DEFAULT TABLESPACE nom_tablespace TEMPORARY TABLESPACE TEMP;
Pour connaître toutes les options de la commande CREATE USER :
http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_8003.htm

Dans notre cas pratique :

1. Il faut lancer SQL PLUS et se connecter avec un utilisateur qui a le droit de créer un tablespace ( SYSTEM par exemple )
connect SYSTEM/password@connexion

2. Exécuter la commande :
CREATE USER atelier_user IDENTIFIED BY oracle DEFAULT TABLESPACE nom_tablespace TEMPORARY TABLESPACE TEMP;

Tablespace, cas pratique SQL

Toujours dans le cadre du cas pratique SQL,  nous allons créer un tablespace dans le quel sera stocké notre modèle physique de données.
Les objets BD sont créés physiquement dans des DATA FILES, mais l'administrateur ORACLE ( DBA ) doit créer des espaces de stockage logiques ( Tablespaces ) qui pointent vers des data files.

Un tablespace contient un ou plusieurs DATA FILES, la commande pour créer un table space est :

CREATE TABLESPACE nom_du_tablesapce DATAFILE chemin_et_nom_datafile SIZE taille_du _datafile;

Pour connaître toutes les options de cette commande ( Doc en anglais ) :
http://download-uk.oracle.com/docs/cd/B14117_01/server.101/b10739/tspaces.htm#sthref1066

Dans notre cas pratique :

Nous allons créer le tablespace Atelier :

1. Il faut lancer SQL PLUS et se connecter avec un utilisateur qui a le droit de créer un tablespace ( SYSTEM par exemple )
connect SYSTEM/password@connexion
password est le mot de passe que vous avez spécifié lors de la création de la base de données, et "connexion" est la connexion que vous avez congigurer dans le fichier tnsnames.ora

2. Il faut lancer la commande de création du tablespace :
CREATE TABLESPACE ATELIER DATAFILE 'D:\Datafiles\Atelier01.DBF' SIZE 20 M;
Le tablespace ATELIER contient un seul data file Atelier01.DBF avec une taille de 20 MO dans le répertoire D:\Datafiles.

Configuration SQL *Net

Au cours de cette deuxième étape du cas pratique sur SQL, nous allons essayer de configurer au niveau du client ( exemple : SQL PLUS ) la connexion à un serveur ORACLE.

Pour configurer une connexion "ma_connexion" :
http://drisshazzaf.typepad.com/oracle/2006/12/connexions_orac.html

Il faut utiliser le même numéro de port que le serveur ( c'est le 1521 par défaut ).

Modèle relationnel de données

Dans le cadre des cas pratiques sur les bases de données, générer le modèle relationel à partir du modèle conceptuel ci-dessous :

Bases de données ORACLE : Cas pratique SQL

Ceci est un cas pratique sur la conception d'une base de données et la réalisation avec le SGBD ORACLE, je vous propose de construire la base de données de gestion des employés dans une société. Ceci passera par les étapes suivantes :

1. Générer le modèle relationnel à partir du modèle conceptuel
2. Configurer SQL*Net pour connecter un client à un serveur ORACLE
3. Créer le tablespace pour le modèle physique de données
4. Créer l'utilisateur pour le modèle physique de données
5. Donner les droits necessaires à l'utilisateur
6. Déveloper les scripts de création des objets base de données
7. Déveloper les requêtes d'extraction de données
8. Déveloper les ordres SQL de manipulation de données
9. Déveloper les packages et procédures stockées demandés par l'application.
10. Générer les plans d'execution et optimiser les requêtes SQL