2) Modèle conceptuel
3) Réalisation de la base de données
Création de la base de donnée:
CREATE DATABASE "Echantillon"
W ITH OW NER = postgres
ENCODING = 'SQL_ASCII'
TABLESPACE = pg_default;
Création des tables :
CREATE TABLE client
(
codeclient int4 NOT NULL,
nomclient varchar(20),
prenomclient varchar(20),
rueclient varchar(30),
cpclient char(5),
villeclient varchar(20),
telclient char(10),
CONSTRAINT pk_client PRIM ARY KEY (codeclient)
);
CREATE TABLE typeanalyse
(
reftypeanalyse int4 NOT NULL,
designationtypeanalyse char(10),
prixtypeanalyse numeric(5,2),
CONSTRAINT pk_typeanalyse PRIM ARY KEY (reftypeanalyse)
);
CREATE TABLE echantillon
(
codeechantillon int4 NOT NULL,
dateentree date,
codeclient int4 NOT NULL,
CONSTRAINT pk_echantillon PRIM ARY KEY (codeechantillon),
CONSTRAINT fk_echantil_apparteni_client FOREIGN KEY (codeclient)
REFERENCES client (codeclient)
);
CREATE TABLE realiser
(
codeechantillon int4 NOT NULL,
reftypeanalyse int4 NOT NULL,
daterealisation date,
CONSTRAINT pk_realiser PRIM ARY KEY (reftypeanalyse, codeechantilon),
CONSTRAINT fk_realiser_realiser2_echantil FOREIGN KEY (codeechantillon)
REFERENCES echantillon (codeechantillon) M ATCH SIM PLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_realiser_realser_typeanal FOREIGN KEY (reftypeanalyse)
REFERENCES typeanalyse (reftypeanalyse) M ATCH SIM PLE
ON UPDATE RESTRICT ON DELETE RESTRICT
);
3.2 Modifiez la structure de la table Client afin d'ajouter un champs email.
ALTER TABLE CLIENT
ADD Column Email VARCHAR(30);
4) Requêtes de sélection
4.1 Donnez la liste des clients référencés
SELECT * FROM CLIENT
4.2 Indiquez les noms et prénoms des clients habitant Paris
SELECT NomClient, PrenomClient FROM CLIENT
WHERE VilleClient = 'Paris';
4.3 Donnez le nombre de client référencé dans chaque ville
SELECT VilleClient, Count(*) FROM CLIENT
GROUP BY VilleClient;
4.4 Donnez la même information par ordre décroissant du nombre de client
SELECT VilleClient, Count(*) FROM CLIENT
GROUP BY VilleClient ORDER BY 2 DESC;
4.5 Donnez la liste des clients (nom, prénom) habitant dans le département de la Meuse.
SELECT NomClient, PrenomClient FROM CLIENT
WHERE CPClent LIKE '55%';
4.6 Donnez les noms et prénoms des clients n'ayant pas d'adresse e-mail.
SELECT NomClient, PrenomClient FROM CLIENT
WHERE emai IS NULL;
4.7 Donnez les noms, prénoms et adresses complètes des clients ayant fourni des échantillons, ainsi que la date entre le 01-10-2005 et le 31-12-2005/
SELECT NomClient, PrenomClient, RueClient, VilleClient, CPClient, Dateentree
FROM CLIENT C, ECHANTILLON E
WHERE C.CodeClient = E.CodeClient
AND DateEntree BETW EEN '10-01-2005' AND '12-31-2005'
4.8 Donnez le nombre d'analyse en cours (non réalisées)
SELECT COUNT(*) FROM REALISER
WHERE DateRealisation IS NULL;
4.9 Donnez les noms, prénoms des clients attendant le résultat d'une analyse(analyse non réalisée).
SELECT DISTINCT NomClient, PrenomClient FROM CLIENT C, ECHANTILLON E,
REALISER R
WHERE C.CodeClient = E. CodeClient
AND E.CodeEchantillon = R.CodeEchantillon
AND DateRealisation IS NULL;
4.10 Donnez pour chaque client par ordre alphabétique (nom et prénom) le nombre d'échantillons déposés ainsi que les dates de dépôt du plus ancien et du plus récent.
SELECT NomClient, PrenomClient, count(*), Min(Dateentree), Max(dateentree)
FROM CLIENT C, ECHANTILLON E
WHERE C.CodeClient = E. CodeClient
Group BY NomClient, PrenomClient;
4.11 Donnez la liste des analyses réalisées au mois de mars 2005.
SELECT RefTypeAnalyse, Code Echantillon FROM REALISER
WHERE MONTH(dateRealisation) = 03 AND YEAR(DateRealisation) = 2005;
4.12 Donnez pour chaque échantillon, le nombre prévu d'analyse(réalisées ou non).
SELECT CodeEchantillon, count(*) FROM REALISER
GROUP BY CodeEchantilon;
4.13 Donnez les codes et dates d‘entrée des échantillons pour lesquels aucune analyse n‘a été réalisée.
SELECT CodeEchantillon, DateEntree FROM ECHANTILLON
WHERE CodeEchantilon NOT IN
(SELECT CodeEchantillon FROM REALISER W HERE DateRealisation IS not NULL);
4.14 Donnez les codes et dates d‘entrée des échantillons pour lesquels plus de 5 analyses ont été réalisées ;
SELECT CodeEchantillon, DateEntree FROM ECHANTILLON
WHERECode Echantillon IN
(SELECT CodeEchantillon FROM REALISER
WHERE dateRealisation IS NOT NULL
GROUP BY Code Echantill
HAVING COUNT(*) >5);
4.15 Donnez les désignations des analyses dont le prix est inférieur à celui de l‘analyse possédant la référence ”A102”.
SELECT DesignationTypeANalyse FROM TYPEANALYSE
W HERE PrixTypeAnalyse <
(SELECT PrixTypeAnalyse FROM TYPEANALYSE W HERE DesignationTypeAnalyse =
'A102')
Autre solution :
SELECT TR.DesignationTypeAnalyse FROM TYPEANALYSE TM , TYPEANALYSE TR
WHERE TM .designationTypeAnalyse = 'A102'
AND TR.PrixTypeAnalyse < TM .PrixTypeAnalyse
5) Requêtes de mise à jour
5.1 La base est vierge. Réalisez l'insertion d'un jeu de données dans les différentes tables. Les données seront définies par vous-même à votre convenance.
INSERT INTO CLIENT VALUES (1,'Dupont','Jean','Rue de Jean',
'75000','Paris','01020304',Null);
INSERT INTO CLIENT VALUES (2,'Durand',' erre','Rue de Pierre', '75000', 'Paris',
'01020304', 't o@ lui.fr')
INSERT INTO CLIENT VALUES (3,'Autremont','Paul','Rue de Paul' 55000','Bar le Duc',
'01020304', Null);
INSERT INTO CLIENT VALUES (4,'Reste','Jacques','Rue de Jacques', '57000', 'M etz',
'01020304', 'uneadresse@ unsie');
INSERT INTO TYPEANALYSE VALUES (1,'A200',8.10);
INSERT INTO TYPEANALYSE VALUES (2,'A102',10.00);
INSERT INTO TYPEANALYSE VALUES (3,'A300',12.20);
INSERT INTO TYPEANALYSE VALUES (4,'A400',5);
INSERT INTO TYPEANALYSE VALUES (5,'A500',4.20);
INSERT INTO ECHANTILLON VALUES (1,'05-10-2005',1);
INSERT INTO ECHANTILLON VALUES (2,'12-10-2005',2);
INSERT INTO ECHANTILLON VALUES (3,'10-20-2005',3);
INSERT INTO ECHANTILLON VALUES (4,'02-02-2006',4);
INSERT INTO ECHANTILLON VALUES (5,'12-30-2005',2);
INSERT INTO ECHANTILLON VALUES (6,'10-10-2005',1);
INSERT INTO ECHANTILLON VALUES (7,'05-10-2005',4);
INSERT INTO ECHANTILLON VALUES (8,'05-07-1999',3);
INSERT INTO ECHANTILLON VALUES (9,'02-28-2005',1);
INSERT INTO REALISER VALUES (1,1,'10-12-2005');
INSERT INTO REALISER VALUES (1,2,NULL);
INSERT INTO REALISER VALUES (2,1,'08-10-2006');
INSERT INTO REALISER VALUES (2,2,NULL);
INSERT INTO REALISER VALUES (3,1,'06-15-2005');
INSERT INTO REALISER VALUES (3,2,NULL);
INSERT INTO REALISER VALUES (2,4,'05-20-1999');
INSERT INTO REALISER VALUES (1,4,NULL);
INSERT INTO REALISER VALUES (4,1,'02-28-2005');
INSERT INTO REALISER VALUES (1,3,NULL);
INSERT INTO REALISER VALUES (3,3,'12-30-2005');
INSERT INTO REALISER VALUES (4,4,NULL);
INSERT INTO REALISER VALUES (2,3,'01-29-2006');
INSERT INTO REALISER VALUES (3,4,NULL);
5.2 Augmentez de 10% tous les prix des analyses.
UPDATE TYPEANALYSE
SET PrixTypeAnalyse = PrixTypeAnalyse*1.1;
5.3 Il a été défini un prix plancher de 8 Euros pour toutes les analyses. Mettez à jour la table ANALYSE.
UPDATE TYPEANALYSE
SET PrixTypeAnalyse = 8
W HERE PrixTypeAnalyse <8;
5.4 Aujourd'hui, toutes les analyses en cours ont été réalisées. Mettez à jour la base.
UPDATE REALISER
SET DateRealisation = '02-06-2006'
W HERE DateRealisation IS NULL;
5.5 Le client Dupont vient de fournir son adresse e-mail (Dupont@btsig.fr). Mettre à jour la table correspondante.
UPDATE CLIENT
SET email = 'Dupont@ btsig.fr'
W HERE NomClent = 'Dupont';
5.6 Suite à un bug informatique, des entrées ont été réalisées le 01 février 2007 au lieu du 1er février 2006. Mettez à jour la base.
UPDATE ECHANTILLON
SET DateEntree = '02-01-2006'
W HERE DateEntree = '02-01-2007'
UPDATE REALISER
SET Date Realisation = '02-01-2006'
W HERE DateRealisation = '02-01-2007'
5.7 Afin de préparer la nouvelle campagne, de nouvelles analyses ont été définies.
Ces nouvelles analyses sont disponibles dans une table ANALYSE2006 dont la structure (champs, types de donnée) est identique à TYPEANALYSE. Mettez à jour la table TYPEANALYSE à partir de la table ANALYSE2006.
INSERT INTO TYPEANALYSE
SELECT * FROM ANALYSE2006;
Afin de vous aider dans la
compréhension du problème, réaliser le modèle conceptuel à partir du
modèle relationnel. Complétez, ce schéma, et barrez les symboles non utilisés.
3) Réalisation de la base de données
3.1 Rédigez l'ensemble des requêtes permettant de créer la base de donnée (les contraintes seront nommées).
Création de la base de donnée:
CREATE DATABASE "Echantillon"
W ITH OW NER = postgres
ENCODING = 'SQL_ASCII'
TABLESPACE = pg_default;
Création des tables :
CREATE TABLE client
(
codeclient int4 NOT NULL,
nomclient varchar(20),
prenomclient varchar(20),
rueclient varchar(30),
cpclient char(5),
villeclient varchar(20),
telclient char(10),
CONSTRAINT pk_client PRIM ARY KEY (codeclient)
);
CREATE TABLE typeanalyse
(
reftypeanalyse int4 NOT NULL,
designationtypeanalyse char(10),
prixtypeanalyse numeric(5,2),
CONSTRAINT pk_typeanalyse PRIM ARY KEY (reftypeanalyse)
);
CREATE TABLE echantillon
(
codeechantillon int4 NOT NULL,
dateentree date,
codeclient int4 NOT NULL,
CONSTRAINT pk_echantillon PRIM ARY KEY (codeechantillon),
CONSTRAINT fk_echantil_apparteni_client FOREIGN KEY (codeclient)
REFERENCES client (codeclient)
);
CREATE TABLE realiser
(
codeechantillon int4 NOT NULL,
reftypeanalyse int4 NOT NULL,
daterealisation date,
CONSTRAINT pk_realiser PRIM ARY KEY (reftypeanalyse, codeechantilon),
CONSTRAINT fk_realiser_realiser2_echantil FOREIGN KEY (codeechantillon)
REFERENCES echantillon (codeechantillon) M ATCH SIM PLE
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_realiser_realser_typeanal FOREIGN KEY (reftypeanalyse)
REFERENCES typeanalyse (reftypeanalyse) M ATCH SIM PLE
ON UPDATE RESTRICT ON DELETE RESTRICT
);
3.2 Modifiez la structure de la table Client afin d'ajouter un champs email.
ALTER TABLE CLIENT
ADD Column Email VARCHAR(30);
4.1 Donnez la liste des clients référencés
SELECT * FROM CLIENT
4.2 Indiquez les noms et prénoms des clients habitant Paris
SELECT NomClient, PrenomClient FROM CLIENT
WHERE VilleClient = 'Paris';
4.3 Donnez le nombre de client référencé dans chaque ville
SELECT VilleClient, Count(*) FROM CLIENT
GROUP BY VilleClient;
4.4 Donnez la même information par ordre décroissant du nombre de client
SELECT VilleClient, Count(*) FROM CLIENT
GROUP BY VilleClient ORDER BY 2 DESC;
4.5 Donnez la liste des clients (nom, prénom) habitant dans le département de la Meuse.
SELECT NomClient, PrenomClient FROM CLIENT
WHERE CPClent LIKE '55%';
4.6 Donnez les noms et prénoms des clients n'ayant pas d'adresse e-mail.
SELECT NomClient, PrenomClient FROM CLIENT
WHERE emai IS NULL;
4.7 Donnez les noms, prénoms et adresses complètes des clients ayant fourni des échantillons, ainsi que la date entre le 01-10-2005 et le 31-12-2005/
SELECT NomClient, PrenomClient, RueClient, VilleClient, CPClient, Dateentree
FROM CLIENT C, ECHANTILLON E
WHERE C.CodeClient = E.CodeClient
AND DateEntree BETW EEN '10-01-2005' AND '12-31-2005'
4.8 Donnez le nombre d'analyse en cours (non réalisées)
SELECT COUNT(*) FROM REALISER
WHERE DateRealisation IS NULL;
4.9 Donnez les noms, prénoms des clients attendant le résultat d'une analyse(analyse non réalisée).
SELECT DISTINCT NomClient, PrenomClient FROM CLIENT C, ECHANTILLON E,
REALISER R
WHERE C.CodeClient = E. CodeClient
AND E.CodeEchantillon = R.CodeEchantillon
AND DateRealisation IS NULL;
4.10 Donnez pour chaque client par ordre alphabétique (nom et prénom) le nombre d'échantillons déposés ainsi que les dates de dépôt du plus ancien et du plus récent.
SELECT NomClient, PrenomClient, count(*), Min(Dateentree), Max(dateentree)
FROM CLIENT C, ECHANTILLON E
WHERE C.CodeClient = E. CodeClient
Group BY NomClient, PrenomClient;
4.11 Donnez la liste des analyses réalisées au mois de mars 2005.
SELECT RefTypeAnalyse, Code Echantillon FROM REALISER
WHERE MONTH(dateRealisation) = 03 AND YEAR(DateRealisation) = 2005;
4.12 Donnez pour chaque échantillon, le nombre prévu d'analyse(réalisées ou non).
SELECT CodeEchantillon, count(*) FROM REALISER
GROUP BY CodeEchantilon;
4.13 Donnez les codes et dates d‘entrée des échantillons pour lesquels aucune analyse n‘a été réalisée.
SELECT CodeEchantillon, DateEntree FROM ECHANTILLON
WHERE CodeEchantilon NOT IN
(SELECT CodeEchantillon FROM REALISER W HERE DateRealisation IS not NULL);
4.14 Donnez les codes et dates d‘entrée des échantillons pour lesquels plus de 5 analyses ont été réalisées ;
SELECT CodeEchantillon, DateEntree FROM ECHANTILLON
WHERECode Echantillon IN
(SELECT CodeEchantillon FROM REALISER
WHERE dateRealisation IS NOT NULL
GROUP BY Code Echantill
HAVING COUNT(*) >5);
4.15 Donnez les désignations des analyses dont le prix est inférieur à celui de l‘analyse possédant la référence ”A102”.
SELECT DesignationTypeANalyse FROM TYPEANALYSE
W HERE PrixTypeAnalyse <
(SELECT PrixTypeAnalyse FROM TYPEANALYSE W HERE DesignationTypeAnalyse =
'A102')
Autre solution :
SELECT TR.DesignationTypeAnalyse FROM TYPEANALYSE TM , TYPEANALYSE TR
WHERE TM .designationTypeAnalyse = 'A102'
AND TR.PrixTypeAnalyse < TM .PrixTypeAnalyse
5) Requêtes de mise à jour
5.1 La base est vierge. Réalisez l'insertion d'un jeu de données dans les différentes tables. Les données seront définies par vous-même à votre convenance.
INSERT INTO CLIENT VALUES (1,'Dupont','Jean','Rue de Jean',
'75000','Paris','01020304',Null);
INSERT INTO CLIENT VALUES (2,'Durand',' erre','Rue de Pierre', '75000', 'Paris',
'01020304', 't o@ lui.fr')
INSERT INTO CLIENT VALUES (3,'Autremont','Paul','Rue de Paul' 55000','Bar le Duc',
'01020304', Null);
INSERT INTO CLIENT VALUES (4,'Reste','Jacques','Rue de Jacques', '57000', 'M etz',
'01020304', 'uneadresse@ unsie');
INSERT INTO TYPEANALYSE VALUES (1,'A200',8.10);
INSERT INTO TYPEANALYSE VALUES (2,'A102',10.00);
INSERT INTO TYPEANALYSE VALUES (3,'A300',12.20);
INSERT INTO TYPEANALYSE VALUES (4,'A400',5);
INSERT INTO TYPEANALYSE VALUES (5,'A500',4.20);
INSERT INTO ECHANTILLON VALUES (1,'05-10-2005',1);
INSERT INTO ECHANTILLON VALUES (2,'12-10-2005',2);
INSERT INTO ECHANTILLON VALUES (3,'10-20-2005',3);
INSERT INTO ECHANTILLON VALUES (4,'02-02-2006',4);
INSERT INTO ECHANTILLON VALUES (5,'12-30-2005',2);
INSERT INTO ECHANTILLON VALUES (6,'10-10-2005',1);
INSERT INTO ECHANTILLON VALUES (7,'05-10-2005',4);
INSERT INTO ECHANTILLON VALUES (8,'05-07-1999',3);
INSERT INTO ECHANTILLON VALUES (9,'02-28-2005',1);
INSERT INTO REALISER VALUES (1,1,'10-12-2005');
INSERT INTO REALISER VALUES (1,2,NULL);
INSERT INTO REALISER VALUES (2,1,'08-10-2006');
INSERT INTO REALISER VALUES (2,2,NULL);
INSERT INTO REALISER VALUES (3,1,'06-15-2005');
INSERT INTO REALISER VALUES (3,2,NULL);
INSERT INTO REALISER VALUES (2,4,'05-20-1999');
INSERT INTO REALISER VALUES (1,4,NULL);
INSERT INTO REALISER VALUES (4,1,'02-28-2005');
INSERT INTO REALISER VALUES (1,3,NULL);
INSERT INTO REALISER VALUES (3,3,'12-30-2005');
INSERT INTO REALISER VALUES (4,4,NULL);
INSERT INTO REALISER VALUES (2,3,'01-29-2006');
INSERT INTO REALISER VALUES (3,4,NULL);
5.2 Augmentez de 10% tous les prix des analyses.
UPDATE TYPEANALYSE
SET PrixTypeAnalyse = PrixTypeAnalyse*1.1;
5.3 Il a été défini un prix plancher de 8 Euros pour toutes les analyses. Mettez à jour la table ANALYSE.
UPDATE TYPEANALYSE
SET PrixTypeAnalyse = 8
W HERE PrixTypeAnalyse <8;
5.4 Aujourd'hui, toutes les analyses en cours ont été réalisées. Mettez à jour la base.
UPDATE REALISER
SET DateRealisation = '02-06-2006'
W HERE DateRealisation IS NULL;
5.5 Le client Dupont vient de fournir son adresse e-mail (Dupont@btsig.fr). Mettre à jour la table correspondante.
UPDATE CLIENT
SET email = 'Dupont@ btsig.fr'
W HERE NomClent = 'Dupont';
5.6 Suite à un bug informatique, des entrées ont été réalisées le 01 février 2007 au lieu du 1er février 2006. Mettez à jour la base.
UPDATE ECHANTILLON
SET DateEntree = '02-01-2006'
W HERE DateEntree = '02-01-2007'
UPDATE REALISER
SET Date Realisation = '02-01-2006'
W HERE DateRealisation = '02-01-2007'
5.7 Afin de préparer la nouvelle campagne, de nouvelles analyses ont été définies.
Ces nouvelles analyses sont disponibles dans une table ANALYSE2006 dont la structure (champs, types de donnée) est identique à TYPEANALYSE. Mettez à jour la table TYPEANALYSE à partir de la table ANALYSE2006.
INSERT INTO TYPEANALYSE
SELECT * FROM ANALYSE2006;
0 commentaires:
Enregistrer un commentaire