RelationalModel: V3_Init_DB.sql

File V3_Init_DB.sql, 9.6 KB (added by 153093, 9 months ago)
Line 
1drop table if exists public.Wine_Type cascade;
2drop table if exists public.Wine cascade;
3drop table if exists public.Address cascade;
4drop table if exists public.Customer_Type cascade;
5drop table if exists public.Customer cascade;
6drop table if exists public.Vehicle_Type cascade;
7drop table if exists public.Vehicle_Details cascade;
8drop table if exists public.Vehicle cascade;
9drop table if exists public.Payment cascade;
10drop table if exists public.Expense_Type cascade;
11drop table if exists public.Warehouse cascade;
12drop table if exists public.Employee cascade;
13drop table if exists public.Employee_Drives_Vehicle cascade;
14drop table if exists public.Shipment cascade;
15drop table if exists public.Shipment_Has_Expense_Of_Expense_Type cascade;
16drop table if exists public.Shipment_Load cascade;
17DROP FUNCTION IF EXISTS GetShippedWinesReport(INT, INT);
18DROP FUNCTION IF EXISTS GetCustomerShipmentsReport(INT, INT);
19
20
21create table public.Wine_Type(
22 Wine_Type_Id serial primary key,
23 Wine_Type_Name varchar(50) unique not null CHECK (Wine_Type_Name <> ''),
24 Wine_Type_Description varchar(200) not null CHECK (Wine_Type_Description <> ''),
25 Wine_Type_Region varchar(50) not null CHECK (Wine_Type_Region <> '')
26);
27
28create table public.Wine(
29 Wine_Id serial primary key,
30 Wine_Name varchar(50) unique not null CHECK (Wine_Name <> ''),
31 Base_Price double precision not null CHECK (Base_Price <> 0),
32 Year_Produced date not null,
33 Wine_Type_Id integer not null CHECK (Wine_Type_Id <> 0),
34 foreign key (Wine_Type_Id) references public.Wine_Type (Wine_Type_Id)
35);
36
37create table public.Customer_Type(
38 Customer_Type_Id serial primary key,
39 Customer_Type_Name varchar(50) unique not null CHECK (Customer_Type_Name <> ''),
40 Customer_Type_Description varchar(200) not null CHECK (Customer_Type_Description <> '')
41);
42
43create table public.Address(
44 Address_Id serial primary key,
45 Street varchar(70) not null CHECK (Street <> ''),
46 City varchar(50) not null CHECK (City <> ''),
47 Building_Number integer not null CHECK (Building_Number <> 0),
48 Postcode varchar(20) not null CHECK (Postcode <> '')
49);
50
51create table public.Customer(
52 Customer_Id serial primary key,
53 Customer_Name varchar(50) unique not null CHECK (Customer_Name <> ''),
54 Customer_Email varchar(70) not null CHECK (Customer_Email <> ''),
55 Customer_Phone_Number varchar(100) not null CHECK (Customer_Phone_Number <> ''),
56 Customer_Type_Id integer not null CHECK (Customer_Type_Id <> 0),
57 Address_Id integer not null CHECK (Address_Id <> 0),
58 foreign key (Customer_Type_Id) references public.Customer_Type (Customer_Type_Id),
59 foreign key (Address_Id) references public.Address (Address_Id)
60);
61
62create table public.Vehicle_Type(
63 Vehicle_Type_Id serial primary key,
64 Vehicle_Type_Name varchar(50) unique not null CHECK (Vehicle_Type_Name <> '')
65);
66
67create table public.Vehicle_Details(
68 Vehicle_Details_Id serial primary key,
69 Vehicle_Type_Id integer not null CHECK (Vehicle_Type_Id <> 0),
70 Make varchar(50) not null CHECK (Make <> ''),
71 Model varchar(50) not null CHECK (Model <> ''),
72 Capacity integer not null CHECK (Capacity <> 0),
73 foreign key (Vehicle_Type_Id) references public.Vehicle_Type (Vehicle_Type_Id)
74);
75
76create table public.Vehicle(
77 Vehicle_Id serial primary key,
78 Vehicle_Details_Id integer not null CHECK (Vehicle_Details_Id <> 0),
79 Registration varchar(50) unique not null CHECK (Registration <> ''),
80 foreign key (Vehicle_Details_Id) references public.Vehicle_Details (Vehicle_Details_Id)
81);
82
83create table public.Payment(
84 Payment_Id serial primary key,
85 Payment_Status integer not null
86);
87
88create table public.Expense_Type(
89 Expense_Type_Id serial primary key,
90 Expense_Type_Name varchar(50) unique not null CHECK (Expense_Type_Name <> ''),
91 Expense_Type_Description varchar(200) not null CHECK (Expense_Type_Description <> '')
92);
93
94create table public.Warehouse(
95 Warehouse_Id serial primary key,
96 Warehouse_Name varchar(50) unique not null CHECK (Warehouse_Name <> ''),
97 Address_Id integer not null CHECK (Address_Id <> 0),
98 foreign key (Address_Id) references public.Address (Address_Id)
99);
100
101create table public.Employee(
102 Employee_Id serial primary key,
103 Employee_Name varchar(50) not null CHECK (Employee_Name <> ''),
104 Employee_Surname varchar(50) not null CHECK (Employee_Surname <> ''),
105 Warehouse_Id integer not null CHECK (Warehouse_Id <> 0),
106 foreign key (Warehouse_Id) references public.Warehouse (Warehouse_Id)
107);
108
109create table public.Employee_Drives_Vehicle(
110 Employee_Id integer not null CHECK (Employee_Id <> 0),
111 Vehicle_Id integer not null CHECK (Vehicle_Id <> 0),
112 primary key (Employee_Id, Vehicle_Id), -- Composite primary key
113 foreign key (Vehicle_Id) references public.Vehicle (Vehicle_Id),
114 foreign key (Employee_Id) references public.Employee (Employee_Id)
115);
116
117create table public.Shipment(
118 Shipment_Id serial primary key,
119 Employee_Id integer not null CHECK (Employee_Id <> 0),
120 Payment_Id integer not null CHECK (Payment_Id <> 0),
121 Vehicle_Id integer not null CHECK (Vehicle_Id <> 0),
122 Shipment_Date date not null,
123 foreign key (Employee_Id) references public.Employee (Employee_Id),
124 foreign key (Payment_Id) references public.Payment (Payment_Id),
125 foreign key (Vehicle_Id) references public.Vehicle (Vehicle_Id)
126);
127
128create table public.Shipment_Has_Expense_Of_Expense_Type(
129 Expense_Type_Id integer not null CHECK (Expense_Type_Id <> 0),
130 Shipment_Id integer not null CHECK (Shipment_Id <> 0),
131 Amount integer not null CHECK (Amount <> 0),
132 primary key (Expense_Type_Id, Shipment_Id), -- Composite primary key
133 foreign key (Expense_Type_Id) references public.Expense_Type (Expense_Type_Id),
134 foreign key (Shipment_Id) references public.Shipment (Shipment_Id)
135);
136
137create table public.Shipment_Load(
138 Shipment_Id integer not null CHECK (Shipment_Id <> 0),
139 Customer_Id integer not null CHECK (Customer_Id <> 0),
140 Wine_Id integer not null CHECK (Wine_Id <> 0),
141 Wine_Base_Price double precision not null CHECK (Wine_Base_Price <> 0),
142 Wine_Agreed_Price double precision not null CHECK (Wine_Agreed_Price <> 0),
143 Wine_Quantity integer not null CHECK (Wine_Quantity <> 0),
144 primary key (Shipment_Id, Customer_Id, Wine_Id), -- Composite primary key
145 foreign key (Shipment_Id) references public.Shipment (Shipment_Id),
146 foreign key (Customer_Id) references public.Customer (Customer_Id),
147 foreign key (Wine_Id) references public.Wine (Wine_Id)
148);
149
150CREATE FUNCTION GetShippedWinesReport(month INT, year INT)
151RETURNS TABLE(
152 WineName varchar(50),
153 WineTypeName varchar(50),
154 WineRegion varchar(50),
155 YearProduced date,
156 TotalWineBasePrice double precision,
157 TotalWineAgreedPrice double precision,
158 TotalWineQuantity bigint,
159 TotalWineProfit double precision,
160 PercentageDifference double precision
161)
162LANGUAGE plpgsql
163AS $$
164BEGIN
165 RETURN QUERY
166 SELECT
167 w.wine_name as WineName,
168 wt.wine_type_name AS WineTypeName,
169 wt.wine_type_region AS WineRegion,
170 w.year_produced AS YearProduced,
171 SUM(sl.wine_base_price * sl.wine_quantity) AS TotalWineBasePrice,
172 SUM(sl.wine_agreed_price * sl.wine_quantity) AS TotalWineAgreedPrice,
173 SUM(sl.wine_quantity) AS TotalWineQuantity,
174 SUM((sl.wine_agreed_price * sl.wine_quantity) - (sl.wine_base_price * sl.wine_quantity)) AS TotalWineProfit,
175 (SUM((sl.wine_agreed_price * sl.wine_quantity) - (sl.wine_base_price * sl.wine_quantity)) / SUM(sl.wine_base_price * sl.wine_quantity)) * 100 as PercentageDifference
176 FROM shipment_load sl
177 JOIN wine w ON w.wine_id = sl.wine_id
178 JOIN wine_type wt ON wt.wine_type_id = w.wine_type_id
179 JOIN shipment s ON sl.shipment_id = s.shipment_id
180 WHERE EXTRACT(MONTH FROM s.shipment_date) = month
181 AND EXTRACT(YEAR FROM s.shipment_date) = year
182 GROUP BY
183 w.wine_id,
184 w.wine_name,
185 wt.wine_type_name,
186 wt.wine_type_region,
187 w.year_produced
188 order by SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) desc;
189END;
190$$;
191
192CREATE FUNCTION GetCustomerShipmentsReport(month INT, year INT)
193RETURNS TABLE(
194 CustomerName varchar(50),
195 CustomerTypeName varchar(50),
196 TotalWineQuantity bigint,
197 TotalWineProfit double precision,
198 TotalWineBasePrice double precision,
199 TotalWineAgreedPrice double precision,
200 PercentageDifference double precision
201)
202LANGUAGE plpgsql
203AS $$
204BEGIN
205 RETURN QUERY
206 SELECT
207 c.customer_name as CustomerName,
208 ct.customer_type_name as CustomerTypeName,
209 SUM(wine_quantity) AS TotalWineQuantity,
210 SUM(wine_base_price * wine_quantity) AS TotalWineBasePrice,
211 SUM(wine_agreed_price * wine_quantity) AS TotalWineAgreedPrice,
212 SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) as TotalWineProfit,
213 (SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) / SUM(wine_base_price * wine_quantity)) * 100 as PercentageDifference
214FROM shipment_load sl
215join customer c on c.customer_id = sl.customer_id
216join customer_type ct on c.customer_type_id = ct.customer_type_id
217JOIN shipment s ON sl.shipment_id = s.shipment_id
218WHERE EXTRACT(MONTH FROM s.shipment_date) = month AND EXTRACT(YEAR FROM s.shipment_date) = year
219GROUP BY c.customer_name, ct.customer_type_name
220order by SUM((wine_agreed_price * wine_quantity) - (wine_base_price * wine_quantity)) desc;
221END;
222$$;