[708a5bc] | 1 | drop schema if exists project cascade;
|
---|
| 2 | create schema project;
|
---|
| 3 |
|
---|
| 4 | --use project
|
---|
| 5 |
|
---|
| 6 | drop table if exists users cascade;
|
---|
| 7 | drop table if exists workers cascade;
|
---|
| 8 | drop table if exists managers cascade;
|
---|
| 9 | drop table if exists suppliers cascade;
|
---|
| 10 | drop table if exists articles cascade;
|
---|
| 11 | drop table if exists invoices cascade;
|
---|
| 12 | drop table if exists orders cascade;
|
---|
| 13 | drop table if exists categories cascade;
|
---|
| 14 | drop table if exists locations cascade;
|
---|
| 15 | drop table if exists questions cascade;
|
---|
| 16 | drop table if exists storedArticles cascade;
|
---|
| 17 | drop table if exists invoicedArticles cascade;
|
---|
| 18 | drop table if exists orderedArticles cascade;
|
---|
| 19 | drop table if exists answers cascade;
|
---|
| 20 | drop table if exists question_availability_for_storedarticle cascade;
|
---|
| 21 | drop table if exists article_belongs_to_category cascade;
|
---|
| 22 | drop table if exists supplier_supplies_category cascade;
|
---|
| 23 |
|
---|
| 24 | -------------------------
|
---|
| 25 |
|
---|
| 26 | create table users(
|
---|
| 27 |
|
---|
| 28 | userId serial primary key,
|
---|
| 29 | firstName varchar(100) not null,
|
---|
| 30 | lastName varchar(100) not null,
|
---|
| 31 | username varchar(100) unique not null,
|
---|
| 32 | email varchar(100) not null,
|
---|
| 33 | userPassword varchar(100) not null
|
---|
| 34 |
|
---|
| 35 | );
|
---|
| 36 |
|
---|
| 37 | -------------------------
|
---|
| 38 |
|
---|
| 39 | create table articles(
|
---|
| 40 |
|
---|
| 41 | articleId serial primary key,
|
---|
| 42 | description varchar(400) not null,
|
---|
| 43 | articleName varchar(100) not null,
|
---|
| 44 | imageURL varchar(200),
|
---|
| 45 | maxQuantityPerLocation integer not null
|
---|
| 46 |
|
---|
| 47 | );
|
---|
| 48 |
|
---|
| 49 | -------------------------
|
---|
| 50 |
|
---|
| 51 | create table categories(
|
---|
| 52 |
|
---|
| 53 | categoryId serial primary key,
|
---|
| 54 | categoryName varchar(100) not null,
|
---|
| 55 | description varchar(400) not null
|
---|
| 56 |
|
---|
| 57 | );
|
---|
| 58 |
|
---|
| 59 | -------------------------
|
---|
| 60 |
|
---|
| 61 | create table locations(
|
---|
| 62 |
|
---|
| 63 | locationId serial primary key,
|
---|
| 64 | locationName varchar(100) not null,
|
---|
| 65 | phone varchar(20) not null,
|
---|
| 66 | street varchar(100) not null,
|
---|
| 67 | streetNumber integer not null,
|
---|
| 68 | city varchar(20) not null
|
---|
| 69 |
|
---|
| 70 | );
|
---|
| 71 |
|
---|
| 72 | -------------------------
|
---|
| 73 |
|
---|
| 74 | create table workers(
|
---|
| 75 |
|
---|
| 76 | userId integer primary key,
|
---|
| 77 | locationId integer,
|
---|
| 78 | constraint fk_workers_id foreign key (userId) references users(userId) on delete cascade on update cascade,
|
---|
| 79 | constraint fk_workers_loc foreign key (locationId) references locations(locationId)
|
---|
| 80 |
|
---|
| 81 | );
|
---|
| 82 |
|
---|
| 83 | -------------------------
|
---|
| 84 |
|
---|
| 85 | create table managers(
|
---|
| 86 |
|
---|
| 87 | userId integer primary key,
|
---|
| 88 | constraint fk_managers_id foreign key (userId) references users(userId) on delete cascade on update cascade
|
---|
| 89 |
|
---|
| 90 | );
|
---|
| 91 |
|
---|
| 92 | -------------------------
|
---|
| 93 |
|
---|
| 94 | create table suppliers(
|
---|
| 95 |
|
---|
| 96 | userId integer primary key,
|
---|
| 97 | supplierInfo varchar(300) not null,
|
---|
| 98 | phone varchar(20) not null,
|
---|
| 99 | street varchar(100) not null,
|
---|
| 100 | streetNumber integer not null,
|
---|
| 101 | city varchar(100) not null,
|
---|
| 102 | constraint fk_suppliers_id foreign key (userId) references users(userId) on delete cascade on update cascade
|
---|
| 103 |
|
---|
| 104 | );
|
---|
| 105 |
|
---|
| 106 | -------------------------
|
---|
| 107 |
|
---|
| 108 | create table invoices(
|
---|
| 109 | invoiceId serial primary key,
|
---|
| 110 | customerName varchar(100),
|
---|
| 111 | customerPhone varchar(20),
|
---|
| 112 | street varchar(100),
|
---|
| 113 | streetNumber integer,
|
---|
| 114 | city varchar(100),
|
---|
| 115 | dateCreate timestamp default now(),
|
---|
| 116 | workerUserId integer not null,
|
---|
| 117 | constraint fk_invoice_worker foreign key (workerUserId) references workers(userId)
|
---|
| 118 |
|
---|
| 119 | );
|
---|
| 120 |
|
---|
| 121 | -------------------------
|
---|
| 122 |
|
---|
| 123 | create table orders(
|
---|
| 124 |
|
---|
| 125 | orderId serial primary key,
|
---|
| 126 | status varchar(50) not null,
|
---|
| 127 | supplierRemark varchar(400),
|
---|
| 128 | managerRemark varchar(400),
|
---|
| 129 | dateCreated timestamp default now(),
|
---|
| 130 | dateApproved timestamp,
|
---|
[9dd4182] | 131 | dateDelivered timestamp,
|
---|
[708a5bc] | 132 | priority varchar(50) not null,
|
---|
| 133 | managerUserId integer not null,
|
---|
| 134 | supplierUserId integer not null,
|
---|
| 135 | constraint fk_order_manager foreign key (managerUserId) references managers(userId),
|
---|
| 136 | constraint fk_order_supplier foreign key (supplierUserId) references suppliers(userId)
|
---|
| 137 |
|
---|
| 138 | );
|
---|
| 139 |
|
---|
| 140 | -------------------------
|
---|
| 141 |
|
---|
| 142 | create table storedArticles(
|
---|
| 143 |
|
---|
| 144 | sArticleId serial primary key,
|
---|
| 145 | quantity integer not null,
|
---|
| 146 | locationId integer not null,
|
---|
| 147 | articleId integer not null,
|
---|
| 148 | constraint fk_storedArt_article foreign key (articleId) references articles(articleId),
|
---|
| 149 | constraint fk_storedArt_location foreign key (locationId) references locations(locationId),
|
---|
| 150 | constraint ck_quantity_gt_0 check (quantity>=0)
|
---|
| 151 |
|
---|
| 152 | );
|
---|
| 153 |
|
---|
| 154 | -------------------------
|
---|
| 155 |
|
---|
| 156 | create table invoicedArticles(
|
---|
| 157 |
|
---|
| 158 | iArticleId serial primary key,
|
---|
| 159 | price integer not null,
|
---|
| 160 | quantity integer not null,
|
---|
| 161 | invoiceId integer not null,
|
---|
| 162 | articleId integer not null,
|
---|
| 163 | constraint fk_invoicedArt_article foreign key (articleId) references articles(articleId),
|
---|
| 164 | constraint fk_invoicedArt_invoice foreign key (invoiceId) references invoices(invoiceId),
|
---|
| 165 | constraint ck_price_gt_0 check (price>0),
|
---|
| 166 | constraint ck_quantity_gt_0 check (quantity>0)
|
---|
| 167 |
|
---|
| 168 | );
|
---|
| 169 |
|
---|
| 170 | -------------------------
|
---|
| 171 |
|
---|
| 172 | create table orderedArticles(
|
---|
| 173 |
|
---|
| 174 | oArticleId serial primary key,
|
---|
| 175 | price integer,
|
---|
| 176 | quantity integer not null,
|
---|
| 177 | articleStatus varchar(50) not null,
|
---|
| 178 | orderId integer not null,
|
---|
| 179 | locationId integer not null,
|
---|
| 180 | articleId integer not null,
|
---|
| 181 | constraint fk_orderedArt_article foreign key (articleId) references articles(articleId),
|
---|
| 182 | constraint fk_orderedArt_location foreign key (locationId) references locations(locationId),
|
---|
| 183 | constraint fk_orderedArt_order foreign key (orderId) references orders(orderId),
|
---|
| 184 | constraint ck_price_gt_0 check (price>0),
|
---|
| 185 | constraint ck_quantity_gt_0 check (quantity>0)
|
---|
| 186 |
|
---|
| 187 | );
|
---|
| 188 |
|
---|
| 189 | -------------------------
|
---|
| 190 |
|
---|
| 191 | create table questions(
|
---|
| 192 |
|
---|
| 193 | questionId serial primary key,
|
---|
| 194 | questionText varchar(500) not null,
|
---|
| 195 | dateCreated timestamp default now(),
|
---|
| 196 | workerUserId integer not null,
|
---|
| 197 | managerUserId integer not null,
|
---|
| 198 | constraint fk_question_worker foreign key (workerUserId) references workers(userId),
|
---|
| 199 | constraint fk_question_manager foreign key (managerUserId) references managers(userId)
|
---|
| 200 |
|
---|
| 201 | );
|
---|
| 202 |
|
---|
| 203 | -------------------------
|
---|
| 204 |
|
---|
| 205 | create table answers(
|
---|
| 206 |
|
---|
| 207 | answerId serial,
|
---|
| 208 | questionId integer,
|
---|
| 209 | answerText varchar(500) not null,
|
---|
| 210 | dateCreated timestamp default now(),
|
---|
| 211 | constraint fk_answer_question foreign key (questionId) references questions(questionId) on delete cascade on update cascade,
|
---|
| 212 | constraint pk_answer primary key(questionId, answerId)
|
---|
| 213 |
|
---|
| 214 | );
|
---|
| 215 |
|
---|
| 216 | -------------------------
|
---|
| 217 |
|
---|
| 218 | create table question_availability_for_storedarticle(
|
---|
| 219 |
|
---|
| 220 | questionId integer,
|
---|
| 221 | sArticleId integer,
|
---|
| 222 | constraint fk_question_sarticle_question foreign key (questionId) references questions(questionId),
|
---|
| 223 | constraint fk_question_sarticle_sarticle foreign key (sArticleId) references storedArticles(sArticleId)
|
---|
| 224 |
|
---|
| 225 | );
|
---|
| 226 |
|
---|
| 227 | -------------------------
|
---|
| 228 |
|
---|
| 229 | create table article_belongs_to_category(
|
---|
| 230 |
|
---|
| 231 | articleId integer,
|
---|
| 232 | categoryId integer,
|
---|
| 233 | constraint fk_article_category_article foreign key (articleId) references articles(articleId),
|
---|
| 234 | constraint fk_article_category_category foreign key (categoryId) references categories(categoryId)
|
---|
| 235 |
|
---|
| 236 | );
|
---|
| 237 |
|
---|
| 238 | -------------------------
|
---|
| 239 |
|
---|
| 240 | create table supplier_supplies_category(
|
---|
| 241 |
|
---|
| 242 | userId integer,
|
---|
| 243 | categoryId integer,
|
---|
| 244 | constraint fk_supplier_category_supplier foreign key (userId) references suppliers(userId),
|
---|
| 245 | constraint fk_supplier_category_category foreign key (categoryId) references categories(categoryId)
|
---|
| 246 |
|
---|
| 247 | );
|
---|
[9dd4182] | 248 |
|
---|
| 249 | -------------------------
|
---|
| 250 |
|
---|
| 251 | alter table question_availability_for_storedarticle add constraint pk_question_availability_for_storedarticle primary key (questionId, sArticleId);
|
---|
| 252 |
|
---|
| 253 | -------------------------
|
---|
| 254 |
|
---|
| 255 | alter table article_belongs_to_category add constraint pk_article_belongs_to_category primary key (articleId, categoryId);
|
---|
| 256 |
|
---|
| 257 | -------------------------
|
---|
| 258 |
|
---|
| 259 | alter table supplier_supplies_category add constraint pk_supplier_supplies_category primary key (userId, categoryId);
|
---|