WikiStart: kreiranje.sql

File kreiranje.sql, 7.4 KB (added by 186102, 3 years ago)
Line 
1CREATE SCHEMA project AUTHORIZATION db_202122z_va_prj_pcpicco_owner;
2
3
4CREATE TABLE project.cart (
5 cart_id int4 NOT NULL,
6 cart_product_list varchar(150) NOT NULL,
7 cart_config_list varchar(150) NOT NULL,
8 CONSTRAINT cart_pkey PRIMARY KEY (cart_id)
9);
10
11
12CREATE TABLE project."configuration" (
13 config_id int4 NOT NULL,
14 config_name varchar(150) NOT NULL,
15 config_type varchar(150) NOT NULL,
16 config_price int4 NOT NULL,
17 CONSTRAINT configuration_pkey PRIMARY KEY (config_id)
18);
19
20CREATE TABLE project.delivery (
21 delivery_id int4 NOT NULL,
22 address varchar NOT NULL,
23 "Date" date NOT NULL,
24 status varchar NOT NULL,
25 CONSTRAINT delivery_pk PRIMARY KEY (delivery_id)
26);
27
28
29CREATE TABLE project."order" (
30 "Order-id" int4 NOT NULL,
31 total_price int4 NOT NULL,
32 "Order-status" varchar NOT NULL,
33 CONSTRAINT order_pk PRIMARY KEY ("Order-id")
34);
35
36CREATE TABLE project.payment (
37 payment_id int4 NOT NULL,
38 creditcard_number int4 NOT NULL,
39 CONSTRAINT payment_pk PRIMARY KEY (payment_id)
40);
41
42
43CREATE TABLE project.price_history (
44 price_history_id int4 NOT NULL,
45 price_history_list varchar NOT NULL,
46 price_date date NOT NULL,
47 CONSTRAINT price_history_pkey PRIMARY KEY (price_history_id)
48);
49
50
51
52CREATE TABLE project.product (
53 product_id serial NOT NULL,
54 product_name varchar(150) NOT NULL,
55 product_price int4 NOT NULL,
56 product_type varchar(150) NOT NULL,
57 product_description varchar(150) NOT NULL,
58 product_availability bool NOT NULL,
59 product_quantity int4 NULL,
60 CONSTRAINT product_pkey PRIMARY KEY (product_id)
61);
62
63
64CREATE TABLE project.store (
65 store_name varchar NOT NULL,
66 store_locations varchar(150) NOT NULL,
67 store_contact varchar(150) NOT NULL,
68 CONSTRAINT store_pkey PRIMARY KEY (store_name)
69);
70
71
72CREATE TABLE project."user" (
73 user_id int4 NOT NULL,
74 user_password varchar(20) NOT NULL,
75 CONSTRAINT user_pkey PRIMARY KEY (user_id)
76);
77
78
79CREATE TABLE project.wishlist (
80 wishlist_id int4 NOT NULL,
81 product_list varchar(150) NOT NULL,
82 config_list varchar(150) NOT NULL,
83 CONSTRAINT wishlist_pkey PRIMARY KEY (wishlist_id)
84);
85
86
87CREATE TABLE project."admin" (
88 user_id int4 NOT NULL,
89 CONSTRAINT admin_pk PRIMARY KEY (user_id),
90 CONSTRAINT admin_fk FOREIGN KEY (user_id) REFERENCES project."user"(user_id)
91);
92
93
94CREATE TABLE project.checkout (
95 order_id int4 NOT NULL,
96 cart_id int4 NOT NULL,
97 CONSTRAINT checkout_pk null,
98 CONSTRAINT checkout_fk FOREIGN KEY (cart_id) REFERENCES project.cart(cart_id),
99 CONSTRAINT checkout_fk_1 FOREIGN KEY (order_id) REFERENCES project."order"("Order-id")
100);
101
102
103CREATE TABLE project.client (
104 "Name" varchar(20) NOT NULL,
105 address varchar(100) NOT NULL,
106 "E-mail" varchar(20) NOT NULL,
107 user_id int4 NOT NULL,
108 CONSTRAINT client_pk PRIMARY KEY (user_id),
109 CONSTRAINT client_fk null
110);
111
112
113CREATE TABLE project.config_to_whishlist (
114 config_id int4 NOT NULL,
115 whishlist_id int4 NOT NULL,
116 CONSTRAINT config_to_whishlist_pk null,
117 CONSTRAINT config_to_whishlist_fk FOREIGN KEY (config_id) REFERENCES project.configuration(config_id),
118 CONSTRAINT config_to_whishlist_fk_1 FOREIGN KEY (whishlist_id) REFERENCES project.wishlist(wishlist_id)
119);
120
121
122CREATE TABLE project."contains" (
123 product_id int4 NOT NULL,
124 config_id int4 NOT NULL,
125 CONSTRAINT contains_pk null,
126 CONSTRAINT contains_fk FOREIGN KEY (product_id) REFERENCES project.product(product_id),
127 CONSTRAINT contains_fk_1 FOREIGN KEY (config_id) REFERENCES project.configuration(config_id)
128);
129
130
131CREATE TABLE project.contains_config (
132 config_id int4 NOT NULL,
133 cart_id int4 NOT NULL,
134 CONSTRAINT contains_config_pk PRIMARY KEY (config_id, cart_id),
135 CONSTRAINT contains_config_fk FOREIGN KEY (cart_id) REFERENCES project.cart(cart_id),
136 CONSTRAINT contains_config_fk_1 FOREIGN KEY (config_id) REFERENCES project.configuration(config_id)
137);
138
139
140CREATE TABLE project.contains_product (
141 product_id int4 NOT NULL,
142 cart_id int4 NOT NULL,
143 CONSTRAINT contains_product_pk PRIMARY KEY (product_id, cart_id),
144 CONSTRAINT contains_product_fk FOREIGN KEY (product_id) REFERENCES project.product(product_id),
145 CONSTRAINT contains_product_fk_1 FOREIGN KEY (cart_id) REFERENCES project.cart(cart_id)
146);
147
148
149CREATE TABLE project.current_price (
150 order_id int4 NOT NULL,
151 price_history_id int4 NOT NULL,
152 CONSTRAINT current_price_pk PRIMARY KEY (order_id, price_history_id),
153 CONSTRAINT current_price_fk FOREIGN KEY (price_history_id) REFERENCES project.price_history(price_history_id),
154 CONSTRAINT current_price_fk_1 FOREIGN KEY (order_id) REFERENCES project."order"("Order-id")
155);
156
157
158CREATE TABLE project.delivers (
159 user_id int4 NOT NULL,
160 delivery_id int4 NOT NULL,
161 CONSTRAINT delivers_pk null,
162 CONSTRAINT delivers_fk null,
163 CONSTRAINT delivers_fk_1 null
164);
165
166CREATE TABLE project.distributor (
167 distributor_name varchar(13) NOT NULL,
168 distributor_surname varchar(13) NOT NULL,
169 distributor_contact varchar(9) NOT NULL,
170 user_id int4 NOT NULL,
171 CONSTRAINT distributor_pk PRIMARY KEY (user_id),
172 CONSTRAINT distributor_fk FOREIGN KEY (user_id) REFERENCES project."user"(user_id)
173);
174
175CREATE TABLE project.has_cart (
176 cart_id int4 NOT NULL,
177 user_id int4 NOT NULL,
178 CONSTRAINT has_cart_pk PRIMARY KEY (cart_id),
179 CONSTRAINT has_cart_fk FOREIGN KEY (user_id) REFERENCES project.client(user_id),
180 CONSTRAINT has_cart_fk_1 FOREIGN KEY (cart_id) REFERENCES project.cart(cart_id)
181);
182
183
184CREATE TABLE project.manages_configuration (
185 user_id int4 NOT NULL,
186 config_id int4 NOT NULL,
187 CONSTRAINT manages_configuration_pk PRIMARY KEY (user_id, config_id),
188 CONSTRAINT manages_configuration_fk FOREIGN KEY (user_id) REFERENCES project.admin(user_id),
189 CONSTRAINT manages_configuration_fk_1 FOREIGN KEY (config_id) REFERENCES project.configuration(config_id)
190);
191
192
193CREATE TABLE project.orders_configuration (
194 order_id int4 NOT NULL,
195 config_id int4 NOT NULL,
196 CONSTRAINT orders_configuration_pk PRIMARY KEY (order_id, config_id),
197 CONSTRAINT orders_configuration_fk FOREIGN KEY (order_id) REFERENCES project."order"("Order-id"),
198 CONSTRAINT orders_configuration_fk_1 FOREIGN KEY (config_id) REFERENCES project.configuration(config_id)
199);
200
201
202CREATE TABLE project.seller_admin (
203 "Name" varchar(20) NOT NULL,
204 surname varchar(20) NOT NULL,
205 "E-mail" varchar(20) NOT NULL,
206 user_id int4 NOT NULL,
207 CONSTRAINT seller_admin_pk PRIMARY KEY (user_id),
208 CONSTRAINT seller_admin_fk FOREIGN KEY (user_id) REFERENCES project."user"(user_id)
209);
210
211
212CREATE TABLE project.updates (
213 user_id int4 NOT NULL,
214 product_id int4 NOT NULL,
215 time_stamp date NOT NULL,
216 CONSTRAINT updates_pk PRIMARY KEY (product_id, user_id),
217 CONSTRAINT updates_fk FOREIGN KEY (product_id) REFERENCES project.product(product_id),
218 CONSTRAINT updates_fk_1 FOREIGN KEY (user_id) REFERENCES project.seller_admin(user_id)
219);
220
221
222CREATE TABLE project.view_product (
223 user_id int4 NOT NULL,
224 product_id int4 NOT NULL,
225 time_stamp date NOT NULL,
226 CONSTRAINT view_product_pk PRIMARY KEY (user_id, product_id),
227 CONSTRAINT view_product_fk FOREIGN KEY (user_id) REFERENCES project.client(user_id),
228 CONSTRAINT view_product_fk_1 FOREIGN KEY (product_id) REFERENCES project.product(product_id)
229);
230
231
232CREATE TABLE project.works_for (
233 user_id int4 NOT NULL,
234 store_name varchar NOT NULL,
235 CONSTRAINT works_for_pk PRIMARY KEY (user_id, store_name),
236 CONSTRAINT works_for_fk FOREIGN KEY (store_name) REFERENCES project.store(store_name),
237 CONSTRAINT works_for_fk_1 FOREIGN KEY (user_id) REFERENCES project.seller_admin(user_id)
238);