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(idcl 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(refl 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(idcl number, refl varchar2(10), dateachat 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(idcl number, refl 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 ```

Syntax	Example	Reference
# Header	Header	基本排版
- Unordered List	Unordered List
1. Ordered List	Ordered List
- [ ] Todo List	Todo List
> Blockquote	Blockquote
Bold font	Bold font
Italics font	Italics font
~~Strikethrough~~	~~Strikethrough~~
19^th^	19^th
H~2~O	H₂O
++Inserted text++	Inserted text
==Marked text==	Marked text
[link text](https:// "title")	Link
![image alt](https:// "title")	Image
`Code`	`Code`	在筆記中貼入程式碼
```javascript var i = 0; ```	`var i = 0;`
:smile:		Emoji list
{%youtube youtube_id %}	Externals
$L^aT_eX$	L^aT_eX
:::info This is a alert area. :::	This is a alert area.