QMfwkiyr

1ALTER TABLE Shops DROP CONSTRAINT IF EXISTS Shops_fk0;
2
3ALTER TABLE ProductionInfo DROP CONSTRAINT IF EXISTS ProductionInfo_fk0;
4
5ALTER TABLE Production_In_Shop DROP CONSTRAINT IF EXISTS Production_In_Shop_fk0;
6
7ALTER TABLE Production_In_Shop DROP CONSTRAINT IF EXISTS Production_In_Shop_fk1;
8
9DROP TABLE IF EXISTS Shops;
10
11DROP TABLE IF EXISTS Workers;
12
13DROP TABLE IF EXISTS ProductionInfo;
14
15DROP TABLE IF EXISTS Production_In_Shop;
16
17DROP TABLE IF EXISTS ClientBase;
18
19DROP TABLE IF EXISTS Wine_Type;
20
21
22
23CREATE TABLE Shops (
24	IdShop serial NOT NULL UNIQUE,
25	ShopAdress TEXT NOT NULL DEFAULT 'null',
26	AreaSize integer NOT NULL DEFAULT '0',
27	DraftBeer BOOLEAN NOT NULL DEFAULT 'false',
28	IceCream BOOLEAN NOT NULL DEFAULT 'false',
29	CONSTRAINT Shops_pk PRIMARY KEY (IdShop)
30) ;
31
32
33
34
35CREATE TABLE Workers (
36	IdWorker serial NOT NULL ,
37	IdShop integer NOT NULL,
38	Rating integer NOT NULL DEFAULT '0',
39	CONSTRAINT Workers_pk PRIMARY KEY (IdShop)
40) WITH (
41  OIDS=FALSE
42);
43
44
45
46CREATE TABLE ProductionInfo (
47	IdProduction serial NOT NULL UNIQUE,
48	Name TEXT NOT NULL,
49	Cost DECIMAL NOT NULL DEFAULT '0',
50	Category TEXT NOT NULL,
51	CONSTRAINT ProductionInfo_pk PRIMARY KEY (IdProduction)
52) WITH (
53  OIDS=FALSE
54);
55
56
57
58CREATE TABLE Production_In_Shop (
59	IdShop integer NOT NULL,
60	IdProduction integer NOT NULL,
61	ProductionCount integer NOT NULL DEFAULT '0'
62) WITH (
63  OIDS=FALSE
64);
65
66
67
68CREATE TABLE ClientBase (
69	IdClient serial NOT NULL UNIQUE,
70	Name VARCHAR(255) NOT NULL,
71	PhoneNumber integer NOT NULL,
72	DiscountSize DECIMAL NOT NULL DEFAULT '0',
73	TotalBuyingCount integer NOT NULL DEFAULT '0',
74	CONSTRAINT ClientBase_pk PRIMARY KEY (IdClient)
75) WITH (
76  OIDS=FALSE
77);
78
79
80
81CREATE TABLE Wine_Type (
82	IdWine serial NOT NULL,
83	IdProduction integer NOT NULL,
84	WineType TEXT NOT NULL,
85	Alcohol DECIMAL NOT NULL DEFAULT '0',
86	CONSTRAINT Wine_Type_pk PRIMARY KEY (IdProduction)
87) WITH (
88  OIDS=FALSE
89);
90
91
92
93ALTER TABLE Shops ADD CONSTRAINT Shops_fk0 FOREIGN KEY (IdShop) REFERENCES Workers(IdShop);
94
95
96ALTER TABLE ProductionInfo ADD CONSTRAINT ProductionInfo_fk0 FOREIGN KEY (IdProduction) REFERENCES Wine_Type(IdProduction);
97
98ALTER TABLE Production_In_Shop ADD CONSTRAINT Production_In_Shop_fk0 FOREIGN KEY (IdShop) REFERENCES Shops(IdShop);
99ALTER TABLE Production_In_Shop ADD CONSTRAINT Production_In_Shop_fk1 FOREIGN KEY (IdProduction) REFERENCES ProductionInfo(IdProduction);
100
101
102
103SELECT ProductionInfo.cost, ProductionInfo.Name 
104FROM ProductionInfo, Production_In_Shop 
105WHERE ProductionInfo.IdProduction = Production_In_Shop.IdProduction AND Production_In_Shop.ProductionCount > 0;
106
107
108
109SELECT Workers.IdWorker, Workers.Rating
110FROM Workers
111WHERE Workers.Rating > 4;
112
113CREATE INDEX Workers_Rating ON Workers (Rating);
114
115SELECT Shops.ShopAdress
116FROM Shops
117WHERE Shops.DraftBeer AND Shops.IceCream;
118
119SELECT ProductionInfo.IdProduction, ProductionInfo.Name, ProductionInfo.Cost, ProductionInfo.Category, Wine_Type.WineType, Wine_Type.Alcohol 
120FROM ProductionInfo, Wine_Type
121WHERE ProductionInfo.Category = 'Wine';
122
123CREATE FUNCTION discount_size() RETURNS trigger AS $discount_size$
124	BEGIN
125	NEW.DiscountSize :=  0.1;
126	
127	IF NEW.TotalBuyingCount < 3000 THEN
128		NEW.DiscountSize := 0.01;
129	END IF;
130	IF NEW.TotalBuyingCount < 8000 THEN
131		NEW.DiscountSize := 0.02;
132	END IF;
133	IF NEW.TotalBuyingCount < 18000 THEN
134		NEW.DiscountSize := 0.04;
135	END IF;
136	IF NEW.TotalBuyingCount < 36000 THEN
137		NEW.DiscountSize := 0.06;
138	END IF;
139	IF NEW.TotalBuyingCount < 60000 THEN
140		NEW.DiscountSize := 0.08;
141	END IF;
142	
143	
144	RETURN NEW;
145	END;
146$discount_size$ LANGUAGE plpgsql;
147
148CREATE TRIGGER discount_size BEFORE INSERT OR UPDATE ON ClientBase
149	FOR EACH ROW EXECUTE PROCEDURE discount_size();
150
151
152CREATE VIEW TestView AS
153SELECT Workers.IdWorker, Workers.Rating
154FROM Workers
155WHERE Workers.Rating > 4;
156
157CREATE VIEW TestView2 AS
158SELECT Shops.ShopAdress
159FROM Shops
160WHERE Shops.DraftBeer AND Shops.IceCream;
161
162CREATE VIEW TestView3 AS	
163SELECT ProductionInfo.IdProduction, ProductionInfo.Name, ProductionInfo.Cost, ProductionInfo.Category, Wine_Type.WineType, Wine_Type.Alcohol 
164FROM ProductionInfo, Wine_Type
165WHERE ProductionInfo.Category = 'Wine';