Exercice : Conception [Technologie Oracle : Extensions procédurales à SQL]

Conception

MCD et MLD [10 min]

L'association MediaTek fait appel à vous afin d'automatiser ses tâches de marketing (analyse des ventes, analyse des profils des clients) et commerciales (facturation). L'objectif est de réaliser une base de données permettant de gérer les clients, produits, et factures.

Les règles suivantes doivent être respectées dans la conception du schéma :

Une facture comporte un ou plusieurs produits.
Un produit peut paraître dans plusieurs factures.
Une facture est associée à un seul client.
Un client peut établir plusieurs factures.

Question

Le MCD E-A vous est fourni par un collègue, faites la traduction en UML.

Solution

Question

Dériver le modèle relationnel.

Solution

Client (#num:integer, nom:string, prenom:string, adresse:string, date_nais:date, tel:string, sexe:char={'m','f'}) 
Produit (#num:integer, designation:string, prix:float, stock:integer) 
Facture (#num:integer, date_etabli:date, client=>Client(num)) 
Ligne_Fact (#facture=>Facture(num), #produit=>Produit(num), qte:integer)

Client (#num:integer, nom:string, prenom:string, adresse:string, date_nais:date, tel:string, sexe:char={'m','f'}) 
Produit (#num:integer, designation:string, prix:float, stock:integer) 
Facture (#num:integer, date_etabli:date, client=>Client(num)) 
Ligne_Fact (#facture=>Facture(num), #produit=>Produit(num), qte:integer)

Implémentation [10 min]

Vous demandez à un collègue de réaliser pour vous les instructions de création et d'initialisation des tables, sur la base des données Oracle dont vous disposez.

-- CREATE TABLE
create table CLIENT (
	num number,
	nom varchar(20),
	prenom varchar(20),
	adresse varchar(128),
	date_nais date,
	tel varchar(20),
	sexe char check (sexe in ('m','f')),
	constraint PK_CLIENT primary key (num)
);
create table FACTURE (
	num number,
	date_etabli date,
	client number,
	constraint PK_FACTURE primary key (num),
	constraint FK_FACTURE_CLIENT foreign key (client) references CLIENT(num)
);
create table PRODUIT (
	num number,
	designation varchar(128),
	prix number,
	stock number,
	constraint PK_PRODUIT primary key (num)
);
create table LIGNE_FACT (
	facture number,
	produit number,
	qte number,
	constraint FK_LIGNE_FACT_FACTURE foreign key (facture) references FACTURE(num),
	constraint FK_LIGNE_FACT_PRODUIT foreign key (produit) references PRODUIT(num),
	constraint PK_LIGNE_FACT primary key (facture, produit)
);

-- CREATE TABLE

create table CLIENT (
	num number,
	nom varchar(20),
	prenom varchar(20),
	adresse varchar(128),
	date_nais date,
	tel varchar(20),
	sexe char check (sexe in ('m','f')),
	constraint PK_CLIENT primary key (num)
);

create table FACTURE (
	num number,
	date_etabli date,
	client number,
	constraint PK_FACTURE primary key (num),
	constraint FK_FACTURE_CLIENT foreign key (client) references CLIENT(num)
);

create table PRODUIT (
	num number,
	designation varchar(128),
	prix number,
	stock number,
	constraint PK_PRODUIT primary key (num)
);

create table LIGNE_FACT (
	facture number,
	produit number,
	qte number,
	constraint FK_LIGNE_FACT_FACTURE foreign key (facture) references FACTURE(num),
	constraint FK_LIGNE_FACT_PRODUIT foreign key (produit) references PRODUIT(num),
	constraint PK_LIGNE_FACT primary key (facture, produit)
);

-- INSERT INTO CLIENT
insert into CLIENT(num, nom, prenom, adresse, date_nais, tel, sexe) values (
	1,
	'coulomb',
	'francois',
	'4, rue liberte',
	to_date('02121980','DDMMYYYY'),
	'06456780',
	'm'
);
insert into CLIENT(num, nom, prenom, adresse, date_nais, tel, sexe) values (
	2,
	'morin',
	'bernard',
	'120, square zola',
	to_date('19081972','DDMMYYYY'),
	'01345678',
	'm'
);
insert into CLIENT(num, nom, prenom, adresse, date_nais, tel, sexe) values (
	3,
	'corday',
	'charlotte',
	'66, bv napoleon III',
	to_date('02101977','DDMMYYYY'),
	'06455790',
	'f'
);
insert into CLIENT(num, nom, prenom, adresse, date_nais, tel, sexe) values (
	4,
	'pierre',
	'alexandre',
	'1, place des martyres',
	to_date('19081972','DDMMYYYY'),
	'01645870',
	'f'
);
commit;

-- INSERT INTO CLIENT

insert into CLIENT(num, nom, prenom, adresse, date_nais, tel, sexe) values (
	1,
	'coulomb',
	'francois',
	'4, rue liberte',
	to_date('02121980','DDMMYYYY'),
	'06456780',
	'm'
);

insert into CLIENT(num, nom, prenom, adresse, date_nais, tel, sexe) values (
	2,
	'morin',
	'bernard',
	'120, square zola',
	to_date('19081972','DDMMYYYY'),
	'01345678',
	'm'
);

insert into CLIENT(num, nom, prenom, adresse, date_nais, tel, sexe) values (
	3,
	'corday',
	'charlotte',
	'66, bv napoleon III',
	to_date('02101977','DDMMYYYY'),
	'06455790',
	'f'
);

insert into CLIENT(num, nom, prenom, adresse, date_nais, tel, sexe) values (
	4,
	'pierre',
	'alexandre',
	'1, place des martyres',
	to_date('19081972','DDMMYYYY'),
	'01645870',
	'f'
);

commit;

-- INSERT INTO PRODUIT
insert into PRODUIT(num, designation, prix, stock) values(
	1,
	'Matrix',
	23.5,
	100
);
insert into PRODUIT(num, designation, prix, stock) values(
	2,
	'Seigneur des anneaux',
	49.30,
	100
);
insert into PRODUIT(num, designation, prix, stock) values(
	3,
	'Mistral Gagnant',
	25.9,
	100
);
commit;

-- INSERT INTO PRODUIT

insert into PRODUIT(num, designation, prix, stock) values(
	1,
	'Matrix',
	23.5,
	100
);

insert into PRODUIT(num, designation, prix, stock) values(
	2,
	'Seigneur des anneaux',
	49.30,
	100
);

insert into PRODUIT(num, designation, prix, stock) values(
	3,
	'Mistral Gagnant',
	25.9,
	100
);

commit;

-- INSERT INTO FACTURE, LIGNE_FACT
-- Facture 1
insert into FACTURE(num, client, date_etabli) values(
	1,
	2,
	to_date('20122000','DDMMYYYY')
);
insert into LIGNE_FACT(facture, produit, qte) values(
	1,
	1,
	1
);
insert into LIGNE_FACT(facture, produit, qte) values(
	1,
	2,
	2
);
-- Facture 2
insert into FACTURE(num, client, date_etabli) values(
	2,
	2,
	to_date('01012001','DDMMYYYY')
);
insert into LIGNE_FACT(facture, produit, qte) values(
	2,
	1,
	2
);
insert into LIGNE_FACT(facture, produit, qte) values(
	2,
	3,
	1
);
insert into LIGNE_FACT(facture, produit, qte) values(
	2,
	2,
	2
);
-- Facture 3
insert into FACTURE(num, client, date_etabli) values(
	3,
	2,
	to_date('17112004','DDMMYYYY')
);
insert into LIGNE_FACT(facture, produit, qte) values(
	3,
	1,
	2
);
insert into LIGNE_FACT(facture, produit, qte) values(
	3,
	2,
	4
);
insert into LIGNE_FACT(facture, produit, qte) values(
	3,
	3,
	1
);
-- Facture 4
insert into FACTURE(num, client, date_etabli) values(
	4,
	3,
	to_date('14012004','DDMMYYYY')
);
insert into LIGNE_FACT(facture, produit, qte) values(
	4,
	2,
	1
);
insert into LIGNE_FACT(facture, produit, qte) values(
	4,
	3,
	1
);
-- Facture 5
insert into FACTURE(num, client, date_etabli) values(
	5,
	4,
	to_date('19022004','DDMMYYYY')
);
insert into LIGNE_FACT(facture, produit, qte) values(
	5,
	1,
	2
);
insert into LIGNE_FACT(facture, produit, qte) values(
	5,
	3,
	3
);
insert into LIGNE_FACT(facture, produit, qte) values(
	5,
	2,
	4
);
-- Facture 6
insert into FACTURE(num, client, date_etabli) values(
	6,
	4,
	to_date('17032004','DDMMYYYY')
);
insert into LIGNE_FACT(facture, produit, qte) values(
	6,
	1,
	7
);
insert into LIGNE_FACT(facture, produit, qte) values(
	6,
	2,
	3
);
insert into LIGNE_FACT(facture, produit, qte) values(
	6,
	3,
	1
);
commit;

-- INSERT INTO FACTURE, LIGNE_FACT

-- Facture 1

insert into FACTURE(num, client, date_etabli) values(
	1,
	2,
	to_date('20122000','DDMMYYYY')
);

insert into LIGNE_FACT(facture, produit, qte) values(
	1,
	1,
	1
);

insert into LIGNE_FACT(facture, produit, qte) values(
	1,
	2,
	2
);

-- Facture 2

insert into FACTURE(num, client, date_etabli) values(
	2,
	2,
	to_date('01012001','DDMMYYYY')
);

insert into LIGNE_FACT(facture, produit, qte) values(
	2,
	1,
	2
);

insert into LIGNE_FACT(facture, produit, qte) values(
	2,
	3,
	1
);

insert into LIGNE_FACT(facture, produit, qte) values(
	2,
	2,
	2
);

-- Facture 3

insert into FACTURE(num, client, date_etabli) values(
	3,
	2,
	to_date('17112004','DDMMYYYY')
);

insert into LIGNE_FACT(facture, produit, qte) values(
	3,
	1,
	2
);

insert into LIGNE_FACT(facture, produit, qte) values(
	3,
	2,
	4
);

insert into LIGNE_FACT(facture, produit, qte) values(
	3,
	3,
	1
);

-- Facture 4

insert into FACTURE(num, client, date_etabli) values(
	4,
	3,
	to_date('14012004','DDMMYYYY')
);

insert into LIGNE_FACT(facture, produit, qte) values(
	4,
	2,
	1
);

insert into LIGNE_FACT(facture, produit, qte) values(
	4,
	3,
	1
);

-- Facture 5

insert into FACTURE(num, client, date_etabli) values(
	5,
	4,
	to_date('19022004','DDMMYYYY')
);

insert into LIGNE_FACT(facture, produit, qte) values(
	5,
	1,
	2
);

insert into LIGNE_FACT(facture, produit, qte) values(
	5,
	3,
	3
);

insert into LIGNE_FACT(facture, produit, qte) values(
	5,
	2,
	4
);

-- Facture 6

insert into FACTURE(num, client, date_etabli) values(
	6,
	4,
	to_date('17032004','DDMMYYYY')
);

insert into LIGNE_FACT(facture, produit, qte) values(
	6,
	1,
	7
);

insert into LIGNE_FACT(facture, produit, qte) values(
	6,
	2,
	3
);

insert into LIGNE_FACT(facture, produit, qte) values(
	6,
	3,
	1
);

commit;

Question

Exécuter le code de votre collègue et vérifier que tout fonctionne.

Solution

Les scripts sont corrects.

Questions de base [10 min]

Écrivez et exécutez les requêtes SELECT suivantes permettant de vérifier le contenu de la base de données.

Question

Écrivez et exécutez la requête permettant d'afficher toutes les lignes de facture, avec pour chacune :

le numéro de la facture,
les nom et prénom du client,
la désignation et le prix du produit,
et la quantité achetée.

Indice

Il faut joindre toutes les tables de la base de données.

Solution

SELECT f.num, c.nom, c.prenom, p.designation, p.prix, lf.qte
FROM client c, produit p, facture f, ligne_fact lf
WHERE p.num=lf.produit
AND lf.facture=f.num
AND f.client=c.num
ORDER BY f.num

SELECT f.num, c.nom, c.prenom, p.designation, p.prix, lf.qte
FROM client c, produit p, facture f, ligne_fact lf
WHERE p.num=lf.produit
AND lf.facture=f.num
AND f.client=c.num
ORDER BY f.num

Question

Écrivez la requête permettant de calculer le chiffre d'affaire, c'est à dire le nombre de produits achetés multiplié par leur prix, pour chaque facture.

Solution

SELECT f.num, f.date_etabli, sum(p.prix*lf.qte) CA
FROM produit p, facture f, ligne_fact lf
WHERE p.num=lf.produit
AND lf.facture=f.num
GROUP BY f.num, f.date_etabli
ORDER BY f.num

SELECT f.num, f.date_etabli, sum(p.prix*lf.qte) CA
FROM produit p, facture f, ligne_fact lf
WHERE p.num=lf.produit
AND lf.facture=f.num
GROUP BY f.num, f.date_etabli
ORDER BY f.num