---
# System prepended metadata

title: 'BD2-TP1 : Définition et exploitation de relations sous Oracle'

---

# BD2-TP1 : Définition et exploitation de relations sous Oracle

Nom :			Thuaud				Prénom :    		Marion			 

Nom :			Blevanus				Prénom :	Tom					

[TOC]


## Modalités de connexion

Les noms de « user » Oracle et les mots de passe sont donnés sur Madoc.

## Utilisation de SQL Developer

Dans un terminal, taper la commande : **sqldeveloper &** . 

La fenêtre SQL Developer apparaît. Créer alors une nouvelle connexion (**new... db connection**).

Paramètres à indiquer :

1. Le nom de la machine hôte est : *oracle.ensinfo.sciences.univ-nantes.prive*

2. le nom de l'instance (sid) : *cienetdb*

3. le port est : *1521*

## Utilisation du client Oracle local (sqlplus)

Pour le nom de user Oracle **usr3**, dans un terminal taper la commande : **sqlplus usr3@cienetdb**

Il faut ensuite fournir le mot de passe de ce compte.  On peut alors travailler avec sqlplus.

## Relations à définir et requêtes

Dans le but de mettre en place un système de recommandation dans une librairie, la base de données est constituée des relations suivantes :

- **Clients(<u>idcl</u> number, nom varchar2(20), pren varchar2(15), adr varchar2(30), tel varchar2(12))**
  Les clients ont un identifiant ; on mémorise leur nom, prénom, adresse et numéro de téléphone.

- **Livres(<u>refl</u> varchar2(10), titre varchar2(20), auteur varchar2(20), genre varchar2(15))**
  Les livres ont une référence qui les identifie. On mémorise leur titre, leur auteur, leur genre.

- **Achats(<u>idcl</u> number, <u>refl</u> varchar2(10), <u>dateachat</u> date)**
  On mémorise tous les achats effectués par les clients, avec la date d'achat. Cette date d'achat est comprise entre le 1er janvier 2008 et le 31 décembre 2013.

- **Avis(<u>idcl</u> number, <u>refl</u> varchar2(10), note number(4,2), commentaire varchar2(50))**
  Les clients peuvent indiquer s'ils ont aimé l'ouvrage acheté, en donnant une note, comprise entre 1 et 20 (cela correspond à un nombre d'étoiles) ; ils peuvent aussi laisser un commentaire.

### **Q1**

Introduction :

```text



```

Proposer le modèle conceptuel sous-jacent (utiliser https://dbconcept.tuxfamily.org/online/index.html et mettre le code ci-dessous).

```dbconcept

Clients : idcl [number], nom [varchar2(20)], pren [varchar2(15)], adr [varchar2(30)], tel [varchar2(12)]
Livres : refl [varchar2(10)], titre [varchar2(20)], auteur [varchar2(20)], genre [varchar2(15)]
Achats : idcl [number] , _refl [varchar2(10)], _dateachat [date]
Avis : idcl [number], _refl [varchar2(10)], note [number(4,2)], commentaire [varchar2(50)]

acheter, 0N Clients, 0N Livres : idcl, refl
voter, 0N Clients, 0N Livres :  idcl, refl

```

Implanter ces différents schéma, en veillant à indiquer les clés, les clés étrangères et les autres contraintes qui peuvent être indiquées. Garder dans un fichier toutes les manipulations réalisées. Tester les opérations d'insertion/suppression de tuples.

Commentaire :

```text



```

Création des tables :

```sql

CREATE TABLE IF NOT EXISTS public."Clients"
(
    idcl integer NOT NULL,
    nom character varying(20) COLLATE pg_catalog."default",
    pren character varying(15) COLLATE pg_catalog."default",
    adr character varying(30) COLLATE pg_catalog."default",
    tel character varying(12) COLLATE pg_catalog."default",
    CONSTRAINT "Clients_pkey" PRIMARY KEY (idcl)
)

CREATE TABLE IF NOT EXISTS public."Livres"
(
    refl character varying(10) COLLATE pg_catalog."default" NOT NULL,
    titre character varying(20) COLLATE pg_catalog."default",
    auteur character varying(20) COLLATE pg_catalog."default",
    genre character varying(15) COLLATE pg_catalog."default",
    CONSTRAINT "Livres_pkey" PRIMARY KEY (refl)
)

CREATE TABLE IF NOT EXISTS public."Achats"
(
    idcl integer NOT NULL,
    refl character varying(10) COLLATE pg_catalog."default" NOT NULL,
    dateachat date NOT NULL,
    CONSTRAINT "Achats_pkey" PRIMARY KEY (idcl, refl, dateachat),
    CONSTRAINT "Achats_idcl_fkey" FOREIGN KEY (idcl)
        REFERENCES public."Clients" (idcl) MATCH SIMPLE,
    CONSTRAINT "Achats_refl_fkey" FOREIGN KEY (refl)
        REFERENCES public."Livres" (refl) MATCH SIMPLE
)
CREATE TABLE IF NOT EXISTS public."Avis"
(
    idcl integer NOT NULL,
    refl character varying(10) COLLATE pg_catalog."default" NOT NULL,
    note integer,
    commentaire character varying(50) COLLATE pg_catalog."default",
    CONSTRAINT "Avis_pkey" PRIMARY KEY (idcl, refl),
    CONSTRAINT "Avis_idcl_fkey" FOREIGN KEY (idcl)
        REFERENCES public."Clients" (idcl) MATCH SIMPLE,
    CONSTRAINT "Avis_refl_fkey" FOREIGN KEY (refl)
        REFERENCES public."Livres" (refl) MATCH SIMPLE
)

```

Renseignement des tables :

```sql



```

Ecrire les requêtes SQL permettant d'afficher :

1. Les titres des livres de science-fiction.
   Commentaire :
   
   ```text
     
   ```
   
   Requête :
   
   ```sql
   Select titre from Livres
   where Livres.genre like '%science-fiction%';
   
   
   ```
   
2. Les titres de livre achetés le '05-02-2011'.
   Commentaire :

   ```text
     
   ```

   Requête :

   ```sql
   
    Select refl from Achat
    where Achat.dateachat = '05-02-2011';
   
   ```

3. Les meilleures ventes : les livres (titre, auteur, genre) qui ont été achetés en plus de 10000

   exemplaires.
   Commentaire :

   ```text
   
   
   
   ```

   Requête :

   ```sql
   
   Select titre, auteur, genre, count(refl) from Achat
   join livre on achat.refl = livre.refl
    group by titre 
    having count(refl) > 1000;
   
   
   ```

2. Les livres qui obtiennent une note moyenne supérieure à 16.
   Commentaire : 

   ```text
   
   
   
   ```

   Requête :

   ```sql
   
   
   
   ```

3. Les clients qui n'ont pas renseigné l'attribut "commentaire" d'un livre (quel client, livre, note).
   Commentaire :
   
   ```text
   
   
   
   ```
   
   Requête :
   
   ```sql
   
   
   
   ```
   

Conclusion :

```text



```

## Définition d'une séquence

Une séquence est un objet de la base qui génère des entiers, tous différents. Cela peut être pratique pour définir les valeurs d'une clé. Exemple de définition :

```sql
CREATE SEQUENCE maSequence START WITH 1 INCREMENT BY 1 ;
```

Utilisation dans une insertion :

```sql
INSERT INTO maTable VALUES(maSequence.nextval, 'toto', 5) ;
```

### **Q2.** 

Créez une séquence pour l'attribut clé idcl de la relation Clients.

```sql

CREATE SEQUENCE Clientid START WITH 1 INCREMENT BY 1 ;

```

## Un truc pratique

Nous nous plaçons sous SqlPlus. Supposons que l'on veuille effacer le contenu de toutes les tables d'un utilisateur. Il faut à priori connaître le nom de ses différentes tables et effacer au fur et à mesure dans chacune des tables. On se propose ici d'automatiser ce processus en mémorisant automatiquement dans un fichier l'ensemble des commandes SQL nécessaires.

### **Q3.** 

Tester la requête suivante :

```sql
SELECT 'DELETE FROM ' || table_name || ';' FROM USER_TABLES ;
```

Remarques :

```text




```

### **Q4.** 

L'objectif est maintenant de mémoriser les différentes lignes obtenues dans un fichier. Soit *effacer.sql* ce fichier ; c'est lui qu'on lancera pour effacer le contenu de toutes les tables. Pour le créer, il faut rediriger les sorties vers lui, en ayant soin de supprimer les numéros de lignes et noms de colonnes. Ceci peut se faire par les commandes suivantes :

```sql
SET ECHO OFF
SPOOL effacer.sql
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
<placer ici la requête de la question Q1.>
SPOOL OFF
SET ECHO ON
```

On peut ensuite lancer l'exécution du fichier par **@ effacer.sql** (ou **start effacer.sql**) et voir si toutes les tables sont effacées.

Note : il est possible sous SQL Developer de « exécuter une sortie de script en tant que script » mais il semble difficile de supprimer les noms de colonnes.

Remarques :

```text

SET ECHO OFF
SPOOL effacer.sql
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SET PAGESIZE 0
Select titre from Livres
   where Livres.genre like '%science-fiction%';
SPOOL OFF
SET ECHO ON


```

### **Q5.**

Proposer un autre exemple sur le même modèle.

```sql



```

## Rapports sous sqlplus

Sous sqlplus on peut formater le résultat de requêtes pour être présentées et analysées avant de faire de la programmation dans un langage applicatif. Dans cette partie vous allez faire le réglages nécessaires dans un fichier .sql pour enregistrer le résultat d'une requête sur la base de données du TP1 dans un fichier .lst.

### **Q6.**

 Ajouter l'attribut *prix* de type numérique à la relation Achats du TP1 avec la commande alter table.

```sql



```

### **Q7.** 

Ecrire la requête sql et le code sqlplus permettant d'enregistrer automatiquement dans le fichier "2013-01-28-achats.lst" les numéros de client ainsi que chacun de leurs achats avec le genre et le prix payé. On calculera par client la moyenne des prix payé et l'addition des achats. Le résultat sera ordonné par numéro de client et date d'achat. Le résultat doit rassembler à ceci :

```
       Achats des clients au 28 janvier 2013
Idcl Date d'achat 	Genre				Prix
1    30-JAN-10 			ROMAN  			20.30 
     12-MAY-10 			HISTORIQUE 	49.99
     20-FEB-11			ROMAN 			25
**********											------
avg															31.76
sum															95.29

2 	  21-JUN-90 		POESIE 			15 
**********											------
avg															15
sum															15
```
Requête :

```sql



```

## **Q8**

Conclusion générale au TP :

```text



```
