Ignore:
Timestamp:
12/27/22 22:45:56 (18 months ago)
Author:
andrejtodorovski <82031894+andrejtodorovski@…>
Branches:
main
Children:
23a2bc5
Parents:
f094f6c
Message:

Added more data

File:
1 edited

Legend:

Unmodified
Added
Removed

  • database_scripts/kreiranje_v2.sql

    rf094f6c r101cbe2  
    1 drop table if exists users_table cascade;
    2 drop table if exists client cascade;
    3 drop table if exists delivery_man cascade;
    4 drop table if exists warehouseman cascade;
    5 drop table if exists administrator cascade;
    6 drop table if exists review cascade;
    7 drop table if exists repair cascade;
    8 drop table if exists repair_shop cascade;
    9 drop table if exists car_manufacturer cascade;
    10 drop table if exists car cascade;
    11 drop table if exists car_sample cascade;
    12 drop table if exists service_book cascade;
    13 drop table if exists order_table cascade;
    14 drop table if exists part_manufacturer cascade;
    15 drop table if exists part cascade;
    16 drop table if exists delivery cascade;
    17 drop table if exists warehouse cascade;
    18 drop table if exists price cascade;
    19 drop table if exists category cascade;
    20 drop table if exists part_is_from_category cascade;
    21 drop table if exists repair_shop_is_authorized_for_car_make cascade;
    22 drop table if exists order_contains_part cascade;
    23 drop table if exists part_is_in_stock_in_warehouse cascade;
    24 drop table if exists part_is_appropriate_for_car cascade;
    25 
    26 create table users_table(
     1drop table if exists project.users_table cascade;
     2drop table if exists project.client cascade;
     3drop table if exists project.delivery_man cascade;
     4drop table if exists project.warehouseman cascade;
     5drop table if exists project.administrator cascade;
     6drop table if exists project.review cascade;
     7drop table if exists project.repair cascade;
     8drop table if exists project.repair_shop cascade;
     9drop table if exists project.car_manufacturer cascade;
     10drop table if exists project.car cascade;
     11drop table if exists project.car_sample cascade;
     12drop table if exists project.service_book cascade;
     13drop table if exists project.order_table cascade;
     14drop table if exists project.part_manufacturer cascade;
     15drop table if exists project.part cascade;
     16drop table if exists project.delivery cascade;
     17drop table if exists project.warehouse cascade;
     18drop table if exists project.price cascade;
     19drop table if exists project.category cascade;
     20drop table if exists project.part_is_from_category cascade;
     21drop table if exists project.repair_shop_is_authorized_for_car_make cascade;
     22drop table if exists project.order_contains_part cascade;
     23drop table if exists project.part_is_in_stock_in_warehouse cascade;
     24drop table if exists project.part_is_appropriate_for_car cascade;
     25
     26create table project.users_table(
    2727        ID_user serial primary key,
    2828        username varchar(100) unique not null,
    … …  
    3434        ID_administrator integer
    3535        -- constraint for fk_user_by_administrator added later
    36 
    37 );
    38 
    39 create table client(
    40         ID_user integer primary key,
    41         constraint fk_client_is_user foreign key (ID_user) references users_table(ID_user)
    42 );
    43 
    44 create table delivery_man(
     36);
     37
     38create table project.client(
     39        ID_user integer primary key,
     40        constraint fk_client_is_user foreign key (ID_user) references project.users_table(ID_user)
     41);
     42
     43create table project.delivery_man(
    4544        ID_user integer primary key,
    4645        employed_from date not null,
    47         constraint fk_delivery_man_is_user foreign key (ID_user) references users_table(ID_user)
    48 );
    49 
    50 create table warehouseman(
     46        constraint fk_delivery_man_is_user foreign key (ID_user) references project.users_table(ID_user)
     47);
     48
     49create table project.warehouseman(
    5150        ID_user integer primary key,
    5251        employed_from date not null,
    5352        ID_warehouse integer not null,
    54         constraint fk_warehouseman_is_user foreign key (ID_user) references users_table(ID_user)
     53        constraint fk_warehouseman_is_user foreign key (ID_user) references project.users_table(ID_user)
    5554        -- contraint for fk_warehouseman_warehouse added later
    5655);
    5756
    58 create table administrator(
     57create table project.administrator(
    5958        ID_user integer primary key,
    6059        authorized_from date not null,
    6160        authorized_till date not null,
    62         constraint fk_administrator_is_user foreign key (ID_user) references users_table(ID_user)
    63 );
    64 
    65 alter table users_table add constraint fk_user_by_administrator foreign key (ID_administrator)
    66     references administrator(ID_user);
     61        constraint fk_administrator_is_user foreign key (ID_user) references project.users_table(ID_user)
     62);
     63
     64alter table project.users_table add constraint fk_user_by_administrator foreign key (ID_administrator)
     65    references project.administrator(ID_user);
    6766   
    68 create table warehouse(
     67create table project.warehouse(
    6968        ID_warehouse serial primary key,
    7069        warehouse_location varchar(100) not null
    7170);
    7271
    73 alter table warehouseman add constraint fk_warehouseman_warehouse foreign key (ID_warehouse)
    74     references warehouse(ID_warehouse);
     72alter table project.warehouseman add constraint fk_warehouseman_warehouse foreign key (ID_warehouse)
     73    references project.warehouse(ID_warehouse);
    7574   
    76 create table car_manufacturer(
     75create table project.car_manufacturer(
    7776        ID_car_manufacturer serial primary key,
    7877        cm_name varchar(100) not null,
    … …  
    8079);
    8180
    82 create table car(
     81create table project.car(
    8382        ID_car serial primary key,
    8483        in_production_since integer not null,
    … …  
    8685        car_type varchar(100) not null,
    8786        ID_car_manufacturer integer not null,
    88         constraint fk_car_car_manufacturer foreign key (ID_car_manufacturer) references car_manufacturer(ID_car_manufacturer)
    89 );
    90 
    91 create table car_sample(
     87        constraint fk_car_car_manufacturer foreign key (ID_car_manufacturer) references project.car_manufacturer(ID_car_manufacturer)
     88);
     89
     90create table project.car_sample(
    9291        vin integer primary key,
    9392        year_of_production integer not null,
    … …  
    9897        ID_user integer not null,
    9998        ID_car integer not null,
    100         constraint fk_car_sample_user foreign key (ID_user) references client(ID_user),
    101         constraint fk_car_sample_car foreign key (ID_car) references car(ID_car)
    102 );
    103 
    104 create table service_book(
     99        constraint fk_car_sample_user foreign key (ID_user) references project.client(ID_user),
     100        constraint fk_car_sample_car foreign key (ID_car) references project.car(ID_car)
     101);
     102
     103create table project.service_book(
    105104        ID_service_book serial primary key,
    106105        sb_created_on timestamp default now(),
    107106        vin integer not null,
    108         constraint fk_service_book_car_sample foreign key (vin) references car_sample(vin)
    109 );
    110 
    111 create table repair_shop(
     107        constraint fk_service_book_car_sample foreign key (vin) references project.car_sample(vin)
     108);
     109
     110create table project.repair_shop(
    112111        ID_repair_shop serial primary key,
    113112        rs_name varchar(100) not null,
    … …  
    116115);
    117116
    118 create table repair(
     117create table project.repair(
    119118        ID_repair serial primary key,
    120119        vin integer not null,
    121120        ID_repair_shop integer not null,
    122121        ID_service_book integer,
    123         constraint fk_repair_car_sample foreign key (vin) references car_sample(vin),
    124         constraint fk_repair_repair_shop foreign key (ID_repair_shop) references repair_shop(ID_repair_shop),
    125         constraint fk_repair_service_book foreign key (ID_service_book) references service_book(ID_service_book)
    126 );
    127 
    128 create table review(
     122        constraint fk_repair_car_sample foreign key (vin) references project.car_sample(vin),
     123        constraint fk_repair_repair_shop foreign key (ID_repair_shop) references project.repair_shop(ID_repair_shop),
     124        constraint fk_repair_service_book foreign key (ID_service_book) references project.service_book(ID_service_book)
     125);
     126
     127create table project.review(
    129128        ID_review serial primary key,
    130129        review_rating integer not null,
    … …  
    132131        ID_repair integer not null,
    133132        ID_user integer not null,
    134         constraint fk_review_repair foreign key (ID_repair) references repair(ID_repair),
    135         constraint fk_review_user foreign key (ID_user) references client(ID_user)
    136 );
    137 
    138 create table order_table(
     133        constraint fk_review_repair foreign key (ID_repair) references project.repair(ID_repair),
     134        constraint fk_review_user foreign key (ID_user) references project.client(ID_user)
     135);
     136
     137create table project.order_table(
    139138        ID_order serial primary key,
    140139        order_status varchar(100) not null,
    141140        order_date timestamp default now(),
    142141        ID_user integer not null,
    143         constraint fk_order_user foreign key (ID_user) references client(ID_user)
    144 );
    145 
    146 create table part_manufacturer(
     142        constraint fk_order_user foreign key (ID_user) references project.client(ID_user)
     143);
     144
     145create table project.part_manufacturer(
    147146        ID_part_manufacturer serial primary key,
    148147        pm_name varchar(100) not null,
    … …  
    150149);
    151150
    152 create table part(
     151create table project.part(
    153152        ID_part serial primary key,
    154153        part_name varchar(100) not null,
    155154        part_description varchar(300),
    156155        ID_part_manufacturer integer not null,
    157         constraint fk_part_part_manufacturer foreign key(ID_part_manufacturer) references part_manufacturer(ID_part_manufacturer)
    158 );
    159 
    160 create table delivery(
     156        constraint fk_part_part_manufacturer foreign key(ID_part_manufacturer) references project.part_manufacturer(ID_part_manufacturer)
     157);
     158
     159create table project.delivery(
    161160        ID_delivery serial primary key,
    162161        delivery_status varchar(100) not null,
    … …  
    164163        ID_user integer not null,
    165164        ID_order integer not null,
    166         constraint fk_delivered_by_deliver_man foreign key(ID_user) references delivery_man(ID_user),
    167         constraint fk_delivery_for_order foreign key(ID_order) references order_table(ID_order)
    168 );
    169 
    170 create table price(
     165        constraint fk_delivered_by_deliver_man foreign key(ID_user) references project.delivery_man(ID_user),
     166        constraint fk_delivery_for_order foreign key(ID_order) references project.order_table(ID_order)
     167);
     168
     169create table project.price(
    171170        ID_price serial primary key,
    172171        amount integer not null,
    … …  
    174173        price_till date,
    175174        ID_part integer not null,
    176         constraint fk_price_for_part foreign key(ID_part) references part(ID_part),
     175        constraint fk_price_for_part foreign key(ID_part) references project.part(ID_part),
    177176        constraint ck_amount_gt_0 check (amount>0)
    178177);
    179178
    180 create table category(
     179create table project.category(
    181180        ID_category serial primary key,
    182181        category_name varchar(100) not null,
    183182        ID_parent_category integer,
    184         constraint fk_category_parentcategory foreign key (ID_parent_category) references category(ID_category)
    185 );
    186 
    187 create table part_is_from_category(
     183        constraint fk_category_parentcategory foreign key (ID_parent_category) references project.category(ID_category)
     184);
     185
     186create table project.part_is_from_category(
    188187        ID_part integer not null,
    189188        ID_category integer not null,
    190         constraint fk_part_is_from_category_part foreign key (ID_part) references part(ID_part),
    191         constraint fk_part_is_from_category_category foreign key (ID_category) references category(ID_category)
    192 
    193 );
    194 
    195 create table repair_shop_is_authorized_for_car_make(
     189        constraint fk_part_is_from_category_part foreign key (ID_part) references project.part(ID_part),
     190        constraint fk_part_is_from_category_category foreign key (ID_category) references project.category(ID_category)
     191
     192);
     193
     194create table project.repair_shop_is_authorized_for_car_make(
    196195        ID_repair_shop integer not null,
    197196        ID_car_manufacturer integer not null,
    198         constraint fk_repair_shop_is_authorized_for_car_make_repair_shop foreign key (ID_repair_shop) references repair_shop(ID_repair_shop),
    199         constraint fk_repair_shop_is_authorized_for_car_make_car_manufacturer foreign key (ID_car_manufacturer) references car_manufacturer(ID_car_manufacturer)
    200 );
    201 
    202 create table order_contains_part(
     197        constraint fk_repair_shop_is_authorized_for_car_make_repair_shop foreign key (ID_repair_shop) references project.repair_shop(ID_repair_shop),
     198        constraint fk_repair_shop_is_authorized_for_car_make_car_manufacturer foreign key (ID_car_manufacturer) references project.car_manufacturer(ID_car_manufacturer)
     199);
     200
     201create table project.order_contains_part(
    203202        ID_part integer not null,
    204203        ID_order integer not null,
    205204        quantity_order integer not null,
    206         constraint fk_order_contains_part_part foreign key (ID_part) references part(ID_part),
    207         constraint fk_order_contains_part_order foreign key (ID_order) references order_table(ID_order),
     205        constraint fk_order_contains_part_part foreign key (ID_part) references project.part(ID_part),
     206        constraint fk_order_contains_part_order foreign key (ID_order) references project.order_table(ID_order),
    208207        constraint ck_quantity_order check (quantity_order>0)
    209208);
    210209
    211 create table part_is_in_stock_in_warehouse(
     210create table project.part_is_in_stock_in_warehouse(
    212211        ID_part integer not null,
    213212        ID_warehouse integer not null,
    214213        quantity_warehouse integer not null,
    215         constraint fk_part_is_in_stock_in_warehouse_part foreign key (ID_part) references part(ID_part),
    216         constraint fk_part_is_in_stock_in_warehouse_warehouse foreign key (ID_warehouse) references warehouse(ID_warehouse),
     214        constraint fk_part_is_in_stock_in_warehouse_part foreign key (ID_part) references project.part(ID_part),
     215        constraint fk_part_is_in_stock_in_warehouse_warehouse foreign key (ID_warehouse) references project.warehouse(ID_warehouse),
    217216        constraint ck_quantity_warehouse check (quantity_warehouse>0)
    218217);
    219218
    220 create table part_is_appropriate_for_car(
     219create table project.part_is_appropriate_for_car(
    221220        ID_part integer not null,
    222221        ID_car integer not null,
    223         constraint fk_part_is_appropriate_for_car_part foreign key (ID_part) references part(ID_part),
    224         constraint fk_part_is_appropriate_for_car_car foreign key (ID_car) references car(ID_car)
    225 );
     222        constraint fk_part_is_appropriate_for_car_part foreign key (ID_part) references project.part(ID_part),
     223        constraint fk_part_is_appropriate_for_car_car foreign key (ID_car) references project.car(ID_car)
     224);
Note: See TracChangeset for help on using the changeset viewer.