MediaTek II

[45 min]

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

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

Question

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

Solution

Produit et client
1
CREATE TYPE Adresse AS OBJECT (
2
	num NUMBER(4),
3
	rue VARCHAR(255),
4
	cp NUMBER(5),
5
  ville VARCHAR(255)
6
);
7
/
8
CREATE TYPE ListeAdresse AS TABLE OF Adresse;
9
/
10
CREATE TABLE CLIENT (
11
	num NUMBER(10) PRIMARY KEY,
12
 	nom VARCHAR(20),
13
	prenom VARCHAR(20),
14
	adresse ListeAdresse,
15
	ddn DATE,
16
	tel VARCHAR(10),
17
	genre char(1) check (genre in ('m','f'))
18
) nested table adresse store as adresseNT;
19
20
CREATE TABLE Produit (
21
	num NUMBER(10) PRIMARY KEY,
22
	designation VARCHAR(128),
23
	prix NUMBER(5,2),
24
	stock NUMBER(4)
25
);
26
27
CREATE TABLE Facture (
28
  num NUMBER(20) not null,
29
  client NUMBER(10) REFERENCES client(num) NOT NULL,
30
  produit NUMBER(10) REFERENCES produit(num) NOT NULL,
31
  qte NUMBER(4),
32
  PRIMARY KEY (num, client, produit)
33
);
Alimenter une base RO
Une fois la base créée, insérer des données pour l'alimenter.
Question
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).
Indice
Insertion dans les enregistrements imbriqués
Insertion dans les collections imbriquées
Solution
1
INSERT INTO Client (num, nom, prenom, adresse, ddn, tel, genre) 
2
VALUES (
3
1, 'Colomb', 'Christophe', 
4
ListeAdresse(Adresse(4, 'rue de la Liberté', 60200, 'Compiègne'), Adresse(1, 'rue de Gêne', 60300, 'Senlis')),
5
to_date('01091451', 'DDMMYYYY'), '0812456589' , 'm');
6
7
INSERT INTO Client (num, nom, prenom, adresse, ddn, tel, genre) 
8
VALUES (
9
2, 'Morin', 'Bernard', 
10
ListeAdresse(Adresse(120, 'square Zola', 60001, 'Beauvais')), 
11
to_date('27051931', 'DDMMYYYY'), '0126483621' , 'm');
12
13
INSERT INTO Produit (num, designation, prix, stock)
14
VALUES (1, 'The Matrix', 23.5, 100) ;
15
16
INSERT INTO Produit (num, designation, prix, stock)
17
VALUES (2, 'The Hobbit', 49.3, 100) ;
Question
Initialiser la table facture avec les données de votre choix (deux factures de deux lignes chacune au moins).
Solution
1
INSERT INTO Facture (num, client, produit, qte)
2
VALUES (1, 1, 1, 3);
3
INSERT INTO Facture (num, client, produit, qte)
4
VALUES (1, 1, 2, 2);
5
INSERT INTO Facture (num, client, produit, qte)
6
VALUES (2, 2, 2, 6);
7
INSERT INTO Facture (num, client, produit, qte)
8
VALUES (2, 2, 1, 1);
Question
Affichez le contenu des trois tables de la base de données.
Indice
Sélection dans les collections imbriquées
SQL*Plus
Solution
1
COLUMN rue FORMAT A20
2
COLUMN ville FORMAT A10
3
SELECT c.num, c.nom, c.prenom, a.num, a.rue, a.cp, a.ville
4
FROM Client c, TABLE(c.adresse) a;
5
6
COLUMN designation FORMAT A10
7
SELECT *
8
FROM Produit p;
9
10
SELECT * 
11
FROM Facture f;
Interroger une base RO : Utilisation des tables imbriquées
Nous allons à présent interroger la base RO imbriquée.
Question
É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.
Indice
Il faut utiliser un regroupement.
Solution
1
SELECT f.client, SUM(f.qte) AS nombre, AVG(f.qte) AS moyenne
2
FROM Facture f
3
GROUP BY f.client;
Question
É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) .
Indice
Faites un regroupement sur les numéros de client, noms des client et numéros de facture.
Solution
1
SELECT DISTINCT c.num, c.nom
2
FROM Facture f, Client c, TABLE(c.Adresse) a
3
WHERE f.client = c.num 
4
AND a.ville = 'Compiègne'
5
GROUP BY c.num, c.nom, f.num
6
HAVING SUM(f.qte) > 1;
Complément
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
...
...
Question
Créer une vue ClientR qui affiche le contenu de la table Client en mode relationnel (en supprimant l'imbrication donc).
Indice
Sélection dans les collections imbriquées
SQL*Plus
Solution
1
CREATE VIEW ClientR (pknum, nom, prenom, n, rue, cp, ville) AS
2
SELECT c.num, c.nom, c.prenom, a.num, a.rue, a.cp, a.ville
3
FROM Client c, TABLE(c.adresse) a;
4
5
COLUMN rue FORMAT A20
6
COLUMN ville FORMAT A10
7
SELECT * FROM ClientR;