· 8 years ago · Dec 09, 2017, 07:00 PM
1------------------------------------------------------------
2-- Script Postgre
3------------------------------------------------------------
4
5drop schema if exists tp11 cascade;
6create schema if not exists tp11;
7
8-- Exercice 1 :
9
10------------------------------------------------------------
11-- Table: Recette
12------------------------------------------------------------
13CREATE TABLE tp11.Recette(
14 idRect SERIAL NOT NULL ,
15 titre VARCHAR (25) ,
16 note INT2 ,
17 image VARCHAR (25) ,
18 nbPartMin INT2 ,
19 nbPartMax INT2 ,
20 cout VARCHAR (25) ,
21 tpsPrepa INT ,
22 tpsCuisson INT ,
23 pays VARCHAR (25) ,
24 niveau VARCHAR (25) ,
25 calorie INT2 ,
26 gluten BOOL ,
27 protide INT2 ,
28 lipide INT2 ,
29 glucide INT2 ,
30 fibre INT2 ,
31 textRec VARCHAR (2000) ,
32 conseil VARCHAR (2000) ,
33 categorie VARCHAR (25) ,
34 CONSTRAINT prk_constraint_Recette PRIMARY KEY (idRect)
35)WITHOUT OIDS;
36
37
38------------------------------------------------------------
39-- Table: TypePlat
40------------------------------------------------------------
41CREATE TABLE tp11.TypePlat(
42 categorie VARCHAR (256) NOT NULL ,
43 CONSTRAINT prk_constraint_TypePlat PRIMARY KEY (categorie)
44)WITHOUT OIDS;
45
46
47------------------------------------------------------------
48-- Table: Ingredient
49------------------------------------------------------------
50CREATE TABLE tp11.Ingredient(
51 idIngr SERIAL NOT NULL ,
52 nomingredient VARCHAR (25) NOT NULL UNIQUE,
53 CONSTRAINT prk_constraint_Ingredient PRIMARY KEY (idIngr)
54)WITHOUT OIDS;
55
56
57------------------------------------------------------------
58-- Table: Necessiter
59------------------------------------------------------------
60CREATE TABLE tp11.necessiter(
61 quantite INT2 ,
62 unite INT2 ,
63 idRect INT NOT NULL ,
64 idIngr INT NOT NULL ,
65 CONSTRAINT prk_constraint_necessiter PRIMARY KEY (idRect,idIngr)
66)WITHOUT OIDS;
67
68
69
70ALTER TABLE tp11.Recette ADD CONSTRAINT FK_Recette_categorie FOREIGN KEY (categorie) REFERENCES tp11.TypePlat(categorie);
71ALTER TABLE tp11.necessiter ADD CONSTRAINT FK_necessiter_idRect FOREIGN KEY (idRect) REFERENCES tp11.Recette(idRect);
72ALTER TABLE tp11.necessiter ADD CONSTRAINT FK_necessiter_idIngr FOREIGN KEY (idIngr) REFERENCES tp11.Ingredient(idIngr);
73
74-- Exercice 2 : (script construit de façon logique et respectant l'ordre des questions)
75
76------------------------------------------------------------
77-- Insertion des valeurs : Ingredient
78------------------------------------------------------------
79INSERT INTO tp11.ingredient (nomingredient) VALUES ('artichaut');
80INSERT INTO tp11.ingredient VALUES (DEFAULT, 'farine');
81
82------------------------------------------------------------
83-- Résolution des problèmes d'insertions : Ingredient
84-- La commande suivante ne fonctionnera pas à cause des "mauvaises" commandes utilisées plus haut :
85-- Par conséquent, cette commande sera en commentaire pour ne pas gêner l'éxecution du script !
86-- INSERT INTO tp11.ingredient VALUES (CURRVAL('tp11.ingredient_idingr_seq'),'chocolat');
87-- Pour se faire, on va utiliser la notion de DEFAULT :
88------------------------------------------------------------
89INSERT INTO tp11.ingredient VALUES (DEFAULT,'chocolat');
90
91------------------------------------------------------------
92-- Insertion des valeurs : TypePlat
93------------------------------------------------------------
94INSERT INTO tp11.typeplat VALUES ('dessert');
95INSERT INTO tp11.recette VALUES ('1','Gâteau au chocolat',3,'',8,8,'Economique','20','25','France','Facile',3200,'false',16,32,11,24,'Faire fondre le chocolat avec le beurre à feu doux.
96Séparer le blanc des jaunes d oeuf. Mélanger le sucre avec les jaunes d oeuf.
97Ajouter la maïzena puis le chocolat fondu.
98Monter les blancs en neige et incorporer les délicatement au mélange.
99Verser le mélange dans un moule beurré.
100Faire cuire au four thermostat 6 pendant 20 à 25mn.','Ce gâteau peut être également servi tiède.','dessert');
101
102-----------------------------------------------------------
103-- Vérification des résultats produits : Recette
104-----------------------------------------------------------
105SELECT * FROM tp11.recette WHERE titre='Gâteau au chocolat' OR idRect=1; -- Vérification approfondie
106
107-----------------------------------------------------------
108-- Gestion des données : TypePlat
109-- UPDATE tp11.typeplat SET categorie=upper(categorie);
110-- Impossible d'update la colonne categorie car elle est déjà utilisée par un attribut nommé dessert !
111-- Pour ne pas gêner l'éxecution du script, la commande utilisée plus haut sera en commentaire !
112-- DELETE FROM tp11.typeplat where categorie='dessert';
113-- Pour ne pas gêner l'éxecution du script, la commande utilisée plus haut sera en commentaire !
114-- Impossible de supprimer l'attribut dessert car il est déjà utilisé dans une autre table !
115-----------------------------------------------------------
116ALTER TABLE IF EXISTS tp11.recette DROP CONSTRAINT IF EXISTS FK_Recette_categorie;
117UPDATE tp11.typeplat SET categorie=upper(categorie);
118-- L'update a réussi !
119SELECT * FROM tp11.recette WHERE titre='Gâteau au chocolat' OR idRect=1; -- Vérification approfondie
120DELETE FROM tp11.typeplat where categorie='DESSERT' OR categorie='dessert';
121SELECT * FROM tp11.recette WHERE titre='Gâteau au chocolat' OR idRect=1; -- Vérification approfondie
122-- L'attribut dessert est encore présent !
123
124------------------------------------------------------------
125-- Script Postgre
126------------------------------------------------------------