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