RelationalDesign: schema_creation.sql

File schema_creation.sql, 8.4 KB (added by 233051, 4 days ago)
Line 
1DROP SCHEMA IF EXISTS pcforge CASCADE;
2CREATE SCHEMA pcforge;
3SET search_path TO pcforge;
4
5CREATE TABLE users (
6 id SERIAL PRIMARY KEY,
7 username TEXT NOT NULL UNIQUE,
8 password TEXT NOT NULL,
9 email TEXT NOT NULL UNIQUE
10);
11
12CREATE TABLE admins (
13 user_id INTEGER PRIMARY KEY,
14 CONSTRAINT fk_admin_user
15 FOREIGN KEY (user_id) REFERENCES users(id)
16 ON DELETE CASCADE
17 ON UPDATE CASCADE
18);
19
20CREATE TABLE components (
21 id SERIAL PRIMARY KEY,
22 name TEXT NOT NULL,
23 brand TEXT NOT NULL,
24 price NUMERIC NOT NULL,
25 type TEXT NOT NULL,
26 CHECK (type IN (
27 'cpu', 'gpu', 'memory', 'storage', 'power_supply', 'motherboard',
28 'case', 'cooler', 'memory_card', 'optical_drive', 'sound_card',
29 'cables', 'network_adapter', 'network_card'
30 )),
31 img_url TEXT
32);
33
34CREATE TABLE cpu (
35 component_id INTEGER PRIMARY KEY,
36 socket TEXT NOT NULL,
37 cores INTEGER NOT NULL,
38 threads INTEGER NOT NULL,
39 base_clock NUMERIC NOT NULL,
40 boost_clock NUMERIC,
41 tdp NUMERIC NOT NULL,
42 FOREIGN KEY (component_id) REFERENCES components(id)
43 ON DELETE CASCADE
44 ON UPDATE CASCADE
45);
46
47CREATE TABLE gpu (
48 component_id INTEGER PRIMARY KEY,
49 vram NUMERIC NOT NULL,
50 tdp NUMERIC NOT NULL,
51 base_clock NUMERIC,
52 boost_clock NUMERIC,
53 chipset TEXT NOT NULL,
54 length NUMERIC NOT NULL,
55 FOREIGN KEY (component_id) REFERENCES components(id)
56 ON DELETE CASCADE
57 ON UPDATE CASCADE
58);
59
60CREATE TABLE memory (
61 component_id INTEGER PRIMARY KEY,
62 type TEXT NOT NULL,
63 speed NUMERIC NOT NULL,
64 capacity NUMERIC NOT NULL,
65 modules INTEGER NOT NULL,
66 FOREIGN KEY (component_id) REFERENCES components(id)
67 ON DELETE CASCADE
68 ON UPDATE CASCADE
69);
70
71CREATE TABLE storage (
72 component_id INTEGER PRIMARY KEY,
73 type TEXT NOT NULL,
74 capacity NUMERIC NOT NULL,
75 form_factor TEXT NOT NULL,
76 FOREIGN KEY (component_id) REFERENCES components(id)
77 ON DELETE CASCADE
78 ON UPDATE CASCADE
79);
80
81CREATE TABLE power_supply (
82 component_id INTEGER PRIMARY KEY,
83 type TEXT NOT NULL,
84 wattage NUMERIC NOT NULL,
85 form_factor TEXT NOT NULL,
86 FOREIGN KEY (component_id) REFERENCES components(id)
87 ON DELETE CASCADE
88 ON UPDATE CASCADE
89);
90
91CREATE TABLE motherboard (
92 component_id INTEGER PRIMARY KEY,
93 socket TEXT NOT NULL,
94 chipset TEXT NOT NULL,
95 form_factor TEXT NOT NULL,
96 ram_type TEXT NOT NULL,
97 num_ram_slots INTEGER NOT NULL,
98 max_ram_capacity NUMERIC NOT NULL,
99 pci_express_slots NUMERIC NOT NULL,
100 FOREIGN KEY (component_id) REFERENCES components(id)
101 ON DELETE CASCADE
102 ON UPDATE CASCADE
103);
104
105CREATE TABLE pc_case (
106 component_id INTEGER PRIMARY KEY,
107 cooler_max_height NUMERIC NOT NULL,
108 gpu_max_length NUMERIC NOT NULL,
109 FOREIGN KEY (component_id) REFERENCES components(id)
110 ON DELETE CASCADE
111 ON UPDATE CASCADE
112);
113
114CREATE TABLE case_storage_form_factors (
115 case_id INTEGER NOT NULL,
116 form_factor TEXT NOT NULL,
117 num_slots INTEGER NOT NULL,
118 PRIMARY KEY (case_id, form_factor),
119 FOREIGN KEY (case_id) REFERENCES pc_case(component_id)
120 ON DELETE CASCADE
121 ON UPDATE CASCADE
122);
123
124CREATE TABLE case_ps_form_factors (
125 case_id INTEGER NOT NULL,
126 form_factor TEXT NOT NULL,
127 PRIMARY KEY (case_id, form_factor),
128 FOREIGN KEY (case_id) REFERENCES pc_case(component_id)
129 ON DELETE CASCADE
130 ON UPDATE CASCADE
131);
132
133CREATE TABLE case_mobo_form_factors (
134 case_id INTEGER NOT NULL,
135 form_factor TEXT NOT NULL,
136 PRIMARY KEY (case_id, form_factor),
137 FOREIGN KEY (case_id) REFERENCES pc_case(component_id)
138 ON DELETE CASCADE
139 ON UPDATE CASCADE
140);
141
142CREATE TABLE cooler (
143 component_id INTEGER PRIMARY KEY,
144 type TEXT NOT NULL,
145 height NUMERIC NOT NULL,
146 max_tdp_supported NUMERIC NOT NULL,
147 FOREIGN KEY (component_id) REFERENCES components(id)
148 ON DELETE CASCADE
149 ON UPDATE CASCADE
150);
151
152CREATE TABLE cooler_cpu_sockets (
153 cooler_id INTEGER NOT NULL,
154 socket TEXT NOT NULL,
155 PRIMARY KEY (cooler_id, socket),
156 FOREIGN KEY (cooler_id) REFERENCES cooler(component_id)
157 ON DELETE CASCADE
158 ON UPDATE CASCADE
159);
160
161CREATE TABLE memory_card (
162 component_id INTEGER PRIMARY KEY,
163 num_slots INTEGER NOT NULL,
164 interface TEXT NOT NULL,
165 FOREIGN KEY (component_id) REFERENCES components(id)
166 ON DELETE CASCADE
167 ON UPDATE CASCADE
168);
169
170CREATE TABLE optical_drive (
171 component_id INTEGER PRIMARY KEY,
172 form_factor TEXT NOT NULL,
173 type TEXT NOT NULL,
174 interface TEXT NOT NULL,
175 write_speed NUMERIC NOT NULL,
176 read_speed NUMERIC NOT NULL,
177 FOREIGN KEY (component_id) REFERENCES components(id)
178 ON DELETE CASCADE
179 ON UPDATE CASCADE
180);
181
182CREATE TABLE sound_card (
183 component_id INTEGER PRIMARY KEY,
184 sample_rate NUMERIC NOT NULL,
185 bit_depth NUMERIC NOT NULL,
186 chipset TEXT NOT NULL,
187 interface TEXT NOT NULL,
188 channel TEXT NOT NULL,
189 FOREIGN KEY (component_id) REFERENCES components(id)
190 ON DELETE CASCADE
191 ON UPDATE CASCADE
192);
193
194CREATE TABLE cables (
195 component_id INTEGER PRIMARY KEY,
196 length_cm NUMERIC NOT NULL,
197 type TEXT NOT NULL,
198 FOREIGN KEY (component_id) REFERENCES components(id)
199 ON DELETE CASCADE
200 ON UPDATE CASCADE
201);
202
203CREATE TABLE network_adapter (
204 component_id INTEGER PRIMARY KEY,
205 wifi_version TEXT NOT NULL,
206 interface TEXT NOT NULL,
207 num_antennas INTEGER NOT NULL,
208 FOREIGN KEY (component_id) REFERENCES components(id)
209 ON DELETE CASCADE
210 ON UPDATE CASCADE
211);
212
213CREATE TABLE network_card (
214 component_id INTEGER PRIMARY KEY,
215 num_ports INTEGER NOT NULL,
216 speed NUMERIC NOT NULL,
217 interface TEXT NOT NULL,
218 FOREIGN KEY (component_id) REFERENCES components(id)
219 ON DELETE CASCADE
220 ON UPDATE CASCADE
221);
222
223CREATE TABLE build (
224 id SERIAL PRIMARY KEY,
225 user_id INTEGER NOT NULL,
226 name TEXT NOT NULL,
227 created_at DATE NOT NULL,
228 description TEXT,
229 total_price NUMERIC NOT NULL,
230 is_approved BOOLEAN NOT NULL,
231 CONSTRAINT fk_build_user
232 FOREIGN KEY (user_id) REFERENCES users(id)
233 ON DELETE CASCADE
234 ON UPDATE CASCADE
235);
236
237CREATE TABLE build_component (
238 build_id INTEGER NOT NULL,
239 component_id INTEGER NOT NULL,
240 PRIMARY KEY (build_id, component_id),
241 FOREIGN KEY (build_id) REFERENCES build(id)
242 ON DELETE CASCADE
243 ON UPDATE CASCADE,
244 FOREIGN KEY (component_id) REFERENCES components(id)
245 ON DELETE CASCADE
246 ON UPDATE CASCADE
247);
248
249CREATE TABLE favorite_build (
250 build_id INTEGER NOT NULL,
251 user_id INTEGER NOT NULL,
252 PRIMARY KEY (build_id, user_id),
253 FOREIGN KEY (build_id) REFERENCES build(id)
254 ON DELETE CASCADE
255 ON UPDATE CASCADE,
256 FOREIGN KEY (user_id) REFERENCES users(id)
257 ON DELETE CASCADE
258 ON UPDATE CASCADE
259);
260
261CREATE TABLE rating_build (
262 build_id INTEGER NOT NULL,
263 user_id INTEGER NOT NULL,
264 value NUMERIC NOT NULL
265 CHECK (value BETWEEN 1 AND 5),
266 PRIMARY KEY (build_id, user_id),
267 FOREIGN KEY (build_id) REFERENCES build(id)
268 ON DELETE CASCADE
269 ON UPDATE CASCADE,
270 FOREIGN KEY (user_id) REFERENCES users(id)
271 ON DELETE CASCADE
272 ON UPDATE CASCADE
273);
274
275CREATE TABLE review (
276 id SERIAL PRIMARY KEY,
277 build_id INTEGER NOT NULL,
278 user_id INTEGER NOT NULL,
279 content TEXT NOT NULL,
280 created_at DATE NOT NULL,
281 UNIQUE (build_id, user_id),
282 FOREIGN KEY (build_id) REFERENCES build(id)
283 ON DELETE CASCADE
284 ON UPDATE CASCADE,
285 FOREIGN KEY (user_id) REFERENCES users(id)
286 ON DELETE CASCADE
287 ON UPDATE CASCADE
288);
289
290CREATE TABLE suggestions (
291 id SERIAL PRIMARY KEY,
292 user_id INTEGER NOT NULL,
293 admin_id INTEGER,
294 link TEXT NOT NULL,
295 admin_comment TEXT,
296 description TEXT,
297 status TEXT NOT NULL DEFAULT 'pending'
298 CHECK (status in ('pending', 'approved', 'rejected')),
299 component_type TEXT NOT NULL,
300 CHECK (component_type IN (
301 'cpu', 'gpu', 'memory', 'storage', 'power_supply', 'motherboard',
302 'case', 'cooler', 'memory_card', 'optical_drive', 'sound_card',
303 'cables', 'network_adapter', 'network_card'
304 )),
305 FOREIGN KEY (user_id) REFERENCES users(id)
306 ON DELETE CASCADE
307 ON UPDATE CASCADE,
308 FOREIGN KEY (admin_id) REFERENCES admins(user_id)
309 ON DELETE SET NULL
310 ON UPDATE CASCADE
311);
312