Une base RO avec le modèle imbriqué

[45 min]

L'association MediaTek souhaite réaliser la base de données correspondant aux schémas ci-après.

Type Adresse : <num:integer, rue:string, cp:integer, ville:string>
Type ListeAdresse : collection de <Adresse>
Client (#num:integer, nom:string, prenom:string, adresse:ListeAdresse, ddn:date, tel:string, genre:{'m','f'}) 
Produit (#num:integer, designation:string, prix:float, stock:integer) 
Facture (#num:integer, #client=>Client(num), #produit=>Produit, qte:integer)

Réalisez l'implémentation SQL3 sous Oracle.

Produit et client

CREATE TYPE Adresse AS OBJECT (
	num NUMBER(4),
	rue VARCHAR(255),
	cp NUMBER(5),
  ville VARCHAR(255)
);
/
CREATE TYPE ListeAdresse AS TABLE OF Adresse;
/
CREATE TABLE CLIENT (
	num NUMBER(10) PRIMARY KEY,
 	nom VARCHAR(20),
	prenom VARCHAR(20),
	adresse ListeAdresse,
	ddn DATE,
	tel VARCHAR(10),
	genre char(1) check (genre in ('m','f'))
) nested table adresse store as adresseNT;
CREATE TABLE Produit (
	num NUMBER(10) PRIMARY KEY,
	designation VARCHAR(128),
	prix NUMBER(5,2),
	stock NUMBER(4)
);
CREATE TABLE Facture (
  num NUMBER(20) not null,
  client NUMBER(10) REFERENCES client(num) NOT NULL,
  produit NUMBER(10) REFERENCES produit(num) NOT NULL,
  qte NUMBER(4),
  PRIMARY KEY (num, client, produit)
);

Alimenter une base RO

Une fois la base créée, insérer des données pour l'alimenter.

Initialiser les tables Client et Produit avec les données de votre choix (au moins deux clients et deux produits, au moins un client avec deux adresses).

Insertion dans les enregistrements imbriqués

Insertion dans les collections imbriquées

INSERT INTO Client (num, nom, prenom, adresse, ddn, tel, genre) 
VALUES (
1, 'Colomb', 'Christophe', 
ListeAdresse(Adresse(4, 'rue de la Liberté', 60200, 'Compiègne'), Adresse(1, 'rue de Gêne', 60300, 'Senlis')),
to_date('01091451', 'DDMMYYYY'), '0812456589' , 'm');
INSERT INTO Client (num, nom, prenom, adresse, ddn, tel, genre) 
VALUES (
2, 'Morin', 'Bernard', 
ListeAdresse(Adresse(120, 'square Zola', 60001, 'Beauvais')), 
to_date('27051931', 'DDMMYYYY'), '0126483621' , 'm');
INSERT INTO Produit (num, designation, prix, stock)
VALUES (1, 'The Matrix', 23.5, 100) ;
INSERT INTO Produit (num, designation, prix, stock)
VALUES (2, 'The Hobbit', 49.3, 100) ;

Initialiser la table facture avec les données de votre choix (deux factures de deux lignes chacune au moins).

INSERT INTO Facture (num, client, produit, qte)
VALUES (1, 1, 1, 3);
INSERT INTO Facture (num, client, produit, qte)
VALUES (1, 1, 2, 2);
INSERT INTO Facture (num, client, produit, qte)
VALUES (2, 2, 2, 6);
INSERT INTO Facture (num, client, produit, qte)
VALUES (2, 2, 1, 1);

Affichez le contenu des trois tables de la base de données.

Sélection dans les collections imbriquées

SQL*Plus

COLUMN rue FORMAT A20
COLUMN ville FORMAT A10
SELECT c.num, c.nom, c.prenom, a.num, a.rue, a.cp, a.ville
FROM Client c, TABLE(c.adresse) a;
COLUMN designation FORMAT A10
SELECT *
FROM Produit p;
SELECT * 
FROM Facture f;

Interroger une base RO : Utilisation des tables imbriquées

Nous allons à présent interroger la base RO imbriquée.

Écrivez une requête permettant de renvoyer le nombre de produits total achetés par chaque client, ainsi que la moyenne de produit par facture.

Il faut utiliser un regroupement.

SELECT f.client, SUM(f.qte) AS nombre, AVG(f.qte) AS moyenne
FROM Facture f
GROUP BY f.client;

Écrivez une requête permettant de renvoyer les numéros et noms des clients de la ville de Compiègne ayant payé au moins une facture avec au moins deux articles (vous pouvez utiliser une autre ville correspondant à vos données) .

Faites un regroupement sur les numéros de client, noms des client et numéros de facture.

SELECT DISTINCT c.num, c.nom
FROM Facture f, Client c, TABLE(c.Adresse) a
WHERE a.ville = 'Compiègne'
GROUP BY c.num, c.nom, f.num
HAVING SUM(f.qte) > 1;

Notons que La clause GROUP BY spécifie que l'on regroupe les enregistrements par numéro de client, nom de client et facture. Dans ce cas, à quoi sert le SELECT DISTINCT ?

Sans celui-ci on obtiendrait quelque chose comme le tableau ci-après (les enregistrements étant regroupés par client et par facture) :

Résultat sans le DISTINCT et en projetant f.num et lf.qte
f.fkClient.num	f.fkClient.nom	f.num	lf.qte
1	A	1	6
1	A	2	7
1	A	3	6
2	B	4	12
2	B	5	6
...	...	...

Comme on souhaite ne projeter que le numéro et le nom du client, toujours sans le DISTINCT, on obtiendrait :

Résultat sans le DISTINCT
f.fkClient.num	f.fkClient.nom
1	A
1	A
1	A
2	B
2	B
...	...

Le DISTINCT permet donc bien de supprimer les doublons :

Résultat avec le DISTINCT
f.fkClient.num	f.fkClient.nom
1	A
2	B
...	...

Créer une vue ClientR qui affiche le contenu de la table Client en mode relationnel (en supprimant l'imbrication donc).

Sélection dans les collections imbriquées

SQL*Plus

CREATE VIEW ClientR (pknum, nom, prenom, n, rue, cp, ville) AS
SELECT c.num, c.nom, c.prenom, a.num, a.rue, a.cp, a.ville
FROM Client c, TABLE(c.adresse) a;
COLUMN rue FORMAT A20
COLUMN ville FORMAT A10
SELECT * FROM ClientR;

AccueilExercices > Exercice : Une base RO avec le modèle imbriqué< PrécédentSuivant >