· 9 years ago · Dec 09, 2016, 10:41 PM
1CREATE DATABASE IF NOT EXISTS lave_bachelor;
2
3
4CREATE TABLE IF NOT EXISTS Countries (
5 ID INTEGER NOT NULL,
6 Name VARCHAR(100) NOT NULL,
7 Symbol VARCHAR(10) NOT NULL,
8 Note VARCHAR(200),
9 CONSTRAINT Countries_PK PRIMARY KEY (ID),
10 CONSTRAINT Countries_NameU UNIQUE (Name)
11);
12
13
14
15
16CREATE TABLE Cities (
17 ID INTEGER NOT NULL,
18 Name VARCHAR(100) NOT NULL,
19 ID_COUNTRIES INTEGER NOT NULL,
20 Note VARCHAR(200),
21 CONSTRAINT Cities_PK PRIMARY KEY (ID),
22 CONSTRAINT Cities_ID_COUNTRIES_FK FOREIGN KEY (ID_COUNTRIES) REFERENCES Countries(ID)
23 ON UPDATE CASCADE ON DELETE CASCADE,
24 CONSTRAINT Cities_Name_CountriesU UNIQUE(Name, ID_COUNTRIES)
25);
26/*
27CREATE VIEW V_Cities_Countries AS
28SELECT ci.name AS City, co.name AS Country, co.symbol
29FROM Cities ci
30INNER JOIN Countries co ON ci.id_countries=co.id;
31*/
32
33
34CREATE TABLE Currencies (
35 ID INTEGER NOT NULL,
36 Name VARCHAR(50) NOT NULL,
37 Symbol VARCHAR(5) NOT NULL,
38 CONSTRAINT Currencies_PK PRIMARY KEY (ID),
39 CONSTRAINT Currencies_NameU UNIQUE(Name),
40 CONSTRAINT Currencies_SymbolU UNIQUE(Symbol)
41);
42
43
44
45
46CREATE TABLE Currencies_Rates (
47 ID INTEGER NOT NULL,
48 Conversion_Rate DECIMAL(20, 6) NOT NULL, # maybe change the '20' to something smaller
49 Date_Rate TIMESTAMP NOT NULL,
50 ID_CURRENCIES INTEGER NOT NULL,
51 CONSTRAINT Currencies_H_PK PRIMARY KEY (ID),
52 CONSTRAINT Currencies_H_ID_Currencies_FK FOREIGN KEY (ID_CURRENCIES) REFERENCES Currencies(ID)
53 ON UPDATE CASCADE ON DELETE CASCADE,
54 CONSTRAINT Currencies_H_Date_CurrencyU UNIQUE(Date_Rate, ID_CURRENCIES)
55);
56
57
58/*
59CREATE VIEW V_Currencies_History AS
60SELECT c.name, c.symbol, cr.conversion_rate, cr.date_rate
61FROM Currencies c
62INNER JOIN Currencies_Rates cr ON cr.id_currencies=c.id;
63
64
65#SELECT * FROM V_Currencies_History WHERE symbol='CAD';
66*/
67
68
69CREATE TABLE Categories (
70 ID INTEGER NOT NULL,
71 Name VARCHAR(50) NOT NULL,
72 Note VARCHAR(200),
73 CONSTRAINT Categories_PK PRIMARY KEY (ID),
74 CONSTRAINT Categories_NameU UNIQUE(Name)
75);
76
77
78CREATE TABLE Sizes (
79 ID INTEGER NOT NULL,
80 Name VARCHAR(50) NOT NULL,
81 Note VARCHAR(200),
82 CONSTRAINT Sizes_PK PRIMARY KEY (ID),
83 CONSTRAINT Sizes_NameU UNIQUE(Name)
84);
85
86
87CREATE TABLE Users (
88 ID INTEGER NOT NULL,
89 Login VARCHAR(50) NOT NULL,
90 Password_Hash VARCHAR(200) NOT NULL,
91 Is_Admin INTEGER NOT NULL,
92 CONSTRAINT Users_PK PRIMARY KEY (ID),
93 CONSTRAINT Users_LoginU UNIQUE(Login),
94 CONSTRAINT Users_Password_HashU UNIQUE(Password_Hash)
95);
96
97
98CREATE TABLE Customers (
99 ID INTEGER NOT NULL,
100 Identification_Number VARCHAR(10) NOT NULL,
101 First_Name VARCHAR(50) NOT NULL,
102 Last_Name VARCHAR(50) NOT NULL,
103 Gender VARCHAR(1) NOT NULL,
104 Company_Name VARCHAR(200),
105 VAT VARCHAR(50),
106 ID_COUNTRIES INTEGER NOT NULL,
107 ID_CITIES INTEGER NOT NULL,
108 Street VARCHAR(200) NOT NULL,
109 Postal_Code VARCHAR(20) NOT NULL,
110 Email VARCHAR(200) NOT NULL,
111 Phone VARCHAR(50) NOT NULL,
112 ID_CURRENCIES INTEGER NOT NULL,
113 Default_Discount INTEGER DEFAULT 0,
114 ID_USERS INTEGER,
115 CONSTRAINT Customers_PK PRIMARY KEY (ID),
116 CONSTRAINT Customers_Identification_NumberU UNIQUE(Identification_Number),
117 CONSTRAINT Customers_GenderCH CHECK (Gender='M' OR Gender='F'),
118 CONSTRAINT Customers_ID_COUNTRIES_FK FOREIGN KEY (ID_COUNTRIES) REFERENCES Countries(ID)
119 ON UPDATE CASCADE ON DELETE CASCADE,
120 CONSTRAINT Customers_ID_CITIES_FK FOREIGN KEY (ID_CITIES) REFERENCES Cities(ID)
121 ON UPDATE CASCADE ON DELETE CASCADE,
122 CONSTRAINT Customers_ID_CURRENCIES_FK FOREIGN KEY (ID_CURRENCIES) REFERENCES Currencies(ID)
123 ON UPDATE CASCADE ON DELETE CASCADE,
124 CONSTRAINT Customers_ID_USERS_FK FOREIGN KEY (ID_USERS) REFERENCES Users(ID)
125 ON UPDATE CASCADE ON DELETE CASCADE
126);
127
128
129CREATE TABLE Producers (
130 ID INTEGER NOT NULL,
131 Short_Name VARCHAR(50) NOT NULL,
132 Full_Name VARCHAR(200) NOT NULL,
133 First_Name VARCHAR(50),
134 Last_Name VARCHAR(50),
135 VAT VARCHAR(50),
136 ID_COUNTRIES INTEGER NOT NULL,
137 ID_CITIES INTEGER NOT NULL,
138 Street VARCHAR(200) NOT NULL,
139 Postal_Code VARCHAR(20) NOT NULL,
140 Email VARCHAR(200) NOT NULL,
141 Phone VARCHAR(50) NOT NULL,
142 ID_USERS INTEGER,
143 CONSTRAINT Producers_PK PRIMARY KEY (ID),
144 CONSTRAINT Producers_ID_COUNTRIES_FK FOREIGN KEY (ID_COUNTRIES) REFERENCES Countries(ID)
145 ON UPDATE CASCADE ON DELETE CASCADE,
146 CONSTRAINT Producers_ID_CITIES_FK FOREIGN KEY (ID_CITIES) REFERENCES Cities(ID)
147 ON UPDATE CASCADE ON DELETE CASCADE,
148 CONSTRAINT Producers_ID_USERS_FK FOREIGN KEY (ID_USERS) REFERENCES Users(ID)
149 ON UPDATE CASCADE ON DELETE CASCADE
150);
151
152
153CREATE TABLE Invoices (
154 ID INTEGER NOT NULL,
155 Name VARCHAR(50) NOT NULL,
156 ID_CUSTOMERS INTEGER NOT NULL,
157 ID_USERS INTEGER NOT NULL,
158 Date_Issued TIMESTAMP NOT NULL,
159 CONSTRAINT Invoices_PK PRIMARY KEY (ID),
160 CONSTRAINT Invoices_NameU UNIQUE(Name),
161 CONSTRAINT Invoices_ID_CUSTOMERS_FK FOREIGN KEY (ID_CUSTOMERS) REFERENCES Customers(ID)
162 ON UPDATE CASCADE ON DELETE CASCADE,
163 CONSTRAINT Invoices_ID_USERS_FK FOREIGN KEY (ID_USERS) REFERENCES Users(ID)
164 ON UPDATE CASCADE ON DELETE CASCADE
165);
166
167
168CREATE TABLE Products (
169 ID INTEGER NOT NULL,
170 Name VARCHAR(50) NOT NULL,
171 Price_Producer DECIMAL(10, 2) NOT NULL,
172 Price_Customer DECIMAL(10, 2) NOT NULL,
173 QR_Code # not sure
174 ID_CATEGORIES INTEGER NOT NULL,
175 ID_PRODUCERS INTEGER NOT NULL,
176 Availability INTEGER NOT NULL,
177 ID_SIZES INTEGER NOT NULL,
178 CONSTRAINT Items_PK PRIMARY KEY (ID),
179 CONSTRAINT Items_ID_CATEGORIES_FK FOREIGN KEY (ID_CATEGORIES) REFERENCES Categories(ID)
180 ON UPDATE CASCADE ON DELETE CASCADE,
181 CONSTRAINT Items_ID_PRODUCERS_FK FOREIGN KEY (ID_PRODUCERS) REFERENCES Producers(ID)
182 ON UPDATE CASCADE ON DELETE CASCADE,
183 CONSTRAINT Items_ID_SIZES_FK FOREIGN KEY (ID_SIZES) REFERENCES Sizes(ID)
184 ON UPDATE CASCADE ON DELETE CASCADE,
185 CONSTRAINT Items_AvailabilityCH CHECK (Availability>=0)
186);
187
188
189CREATE TABLE Images (
190 ID INTEGER NOT NULL,
191 File_Path VARCHAR(50) NOT NULL,
192 ID_PRODUCTS INTEGER NOT NULL,
193 CONSTRAINT Images_PK PRIMARY KEY (ID),
194 CONSTRAINT Images_File_PathU UNIQUE(File_Path),
195 CONSTRAINT Images_ID_PRODUCTS_FK FOREIGN KEY (ID_PRODUCTS) REFERENCES Products(ID)
196 ON UPDATE CASCADE ON DELETE CASCADE
197);
198
199
200
201
202INSERT INTO Countries (ID, Name, Symbol) VALUES (1, 'Poland', 'PL');
203INSERT INTO Countries (ID, Name, Symbol) VALUES (2, 'Germany', 'DE');
204INSERT INTO Countries (ID, Name, Symbol) VALUES (3, 'France', 'FR');
205INSERT INTO Countries (ID, Name, Symbol) VALUES (4, 'Canada', 'CA');
206INSERT INTO Countries (ID, Name, Symbol) VALUES (5, 'United States of America', 'US');
207
208
209INSERT INTO Cities (ID, Name, ID_COUNTRIES) VALUES (1, 'Warsaw', 1);
210INSERT INTO Cities (ID, Name, ID_COUNTRIES) VALUES (2, 'Lodz', 1);
211INSERT INTO Cities (ID, Name, ID_COUNTRIES) VALUES (3, 'Berlin', 2);
212INSERT INTO Cities (ID, Name, ID_COUNTRIES) VALUES (4, 'New York', 5);
213INSERT INTO Cities (ID, Name, ID_COUNTRIES) VALUES (5, 'Ottawa', 4);
214INSERT INTO Cities (ID, Name, ID_COUNTRIES) VALUES (6, 'Springdale', 5);
215INSERT INTO Cities (ID, Name, ID_COUNTRIES) VALUES (7, 'Burgoberbach', 2);
216INSERT INTO Cities (ID, Name, ID_COUNTRIES) VALUES (8, 'Rzeszów', 1);
217
218
219INSERT INTO Currencies (ID, Name, Symbol) VALUES (1, 'Euro', 'EUR');
220INSERT INTO Currencies (ID, Name, Symbol) VALUES (2, 'US Dollar', 'USD');
221INSERT INTO Currencies (ID, Name, Symbol) VALUES (3, 'British Pound', 'GBP');
222INSERT INTO Currencies (ID, Name, Symbol) VALUES (4, 'Canadian Dollar', 'CAD');
223INSERT INTO Currencies (ID, Name, Symbol) VALUES (5, 'Swiss Franc', 'CHF');
224INSERT INTO Currencies (ID, Name, Symbol) VALUES (6, 'Polish Zloty', 'PLN');
225
226
227INSERT INTO Currencies_Rates (ID, Conversion_Rate, Date_Rate, ID_CURRENCIES) VALUES (1, 1.05531, '2016-11-24', 2);
228INSERT INTO Currencies_Rates (ID, Conversion_Rate, Date_Rate, ID_CURRENCIES) VALUES (2, 0.847750, '2016-11-24', 3);
229INSERT INTO Currencies_Rates (ID, Conversion_Rate, Date_Rate, ID_CURRENCIES) VALUES (3, 1.42434, '2016-11-24', 4);
230INSERT INTO Currencies_Rates (ID, Conversion_Rate, Date_Rate, ID_CURRENCIES) VALUES (4, 1.07346, '2016-11-24', 5);
231INSERT INTO Currencies_Rates (ID, Conversion_Rate, Date_Rate, ID_CURRENCIES) VALUES (5, 4.42238, '2016-11-24', 6);
232INSERT INTO Currencies_Rates (ID, Conversion_Rate, Date_Rate, ID_CURRENCIES) VALUES (6, 1.05897, '2016-11-25', 2);
233INSERT INTO Currencies_Rates (ID, Conversion_Rate, Date_Rate, ID_CURRENCIES) VALUES (7, 0.849787, '2016-11-25', 3);
234INSERT INTO Currencies_Rates (ID, Conversion_Rate, Date_Rate, ID_CURRENCIES) VALUES (8, 1.42962, '2016-11-25', 4);
235INSERT INTO Currencies_Rates (ID, Conversion_Rate, Date_Rate, ID_CURRENCIES) VALUES (9, 1.07335, '2016-11-25', 5);
236INSERT INTO Currencies_Rates (ID, Conversion_Rate, Date_Rate, ID_CURRENCIES) VALUES (10, 4.41699, '2016-11-25', 6);
237
238
239INSERT INTO Customers (ID, Identification_Number, First_Name, Last_Name, Gender, ID_COUNTRIES, ID_CITIES, Street, Postal_Code, Email, Phone, ID_CURRENCIES) VALUES (1, '0000000001', 'Richard', 'McFarlin', 'M', 5, 6, '3240 Green Hill Road', 'AR 72764', 'RichardRMcFarlin@teleworm.us', '479-316-2396', 2);
240INSERT INTO Customers (ID, Identification_Number, First_Name, Last_Name, Gender, ID_COUNTRIES, ID_CITIES, Street, Postal_Code, Email, Phone, ID_CURRENCIES) VALUES (2, '0000000002', 'Lisa', 'Sanger', 'F', 2, 7, 'Heiligengeistbrücke 65', '91595', 'LisaSanger@teleworm.us', '09805 81 50 95', 1);
241INSERT INTO Customers (ID, Identification_Number, First_Name, Last_Name, Gender, ID_COUNTRIES, ID_CITIES, Street, Postal_Code, Email, Phone, ID_CURRENCIES) VALUES (1, 6473297654, ‘Bogusława’, ‘Michalska’, ‘F’, 1, 8, ‘Południowa 101’, ‘35-302’, ‘BoguslawaMichalska@teleworm.us’, ‘88 239 63 33’, 6);
242
243
244INSERT INTO Users (ID, Login, Password_Hash, Is_Admin) VALUES (1, 'root', 'toorhash', 1);
245
246
247#INSERT INTO Products (ID, Name, Price_Producer, Price_Customer, ) VALUES ();