Changeset 101cbe2 for database_scripts


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

Added more data

Location:
database_scripts
Files:
2 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);

  • database_scripts/polnenje_v2.sql

    rf094f6c r101cbe2  
    33('stefanmileski4','stefanmileski4@gmail.com','Stefan Mileski','stefanpassword'),
    44('hristijansazdovski3','hristijansazdovski3@gmail.com','Hristijan Sazdovski','hristijanpassword'),
    5 ('bojantrpeski2','bojantrpeski2@gmail.com','Bojan Trpeski','bojanpassword'),
    6 ('darkosasanski1','darkosasanski1@gmail.com','Darko Sasanski','darkopassword'),
    7 ('denicakjorvezir','denicakjorvezir@gmail.com','Denica Kjorvezir','denicapassword'),
     5('client1','bojantrpeski2@gmail.com','Bojan Trpeski','bojanpassword'),
     6('client2','darkosasanski1@gmail.com','Darko Sasanski','darkopassword'),
     7('client3','denicakjorvezir@gmail.com','Denica Kjorvezir','denicapassword'),
    88('deliveryman1','deliveryman1@gmail.com','Petko Petkovski','petko123'),
    99('deliveryman2','deliveryman2@gmail.com','Marko Markovski','marko123'),
    … …  
    1111('warehouseman1','warehouseman1@gmail.com','Viktor Petrovski','viktor123'),
    1212('warehouseman2','warehouseman2@gmail.com','Vlatko Petrovski','vlatko123'),
    13 ('warehouseman3','warehouseman3@gmail.com','Vido Petrovski','vido123');
    14 
    15 insert into project.users_table (username,email,name_user,password_user,phone_number) values
    16 ('stefanmileski5','stefanmileski5@gmail.com','Stefan Mileski','stefanklient','070 123 456');
     13('warehouseman3','warehouseman3@gmail.com','Vido Petrovski','vido123'),
     14('client4','client4@gmail.com','Client Four','client4'),
     15('client5','client5@gmail.com','Client Five','client5'),
     16('client6','client6@gmail.com','Client Six','client6'),
     17('client7','client7@gmail.com','Client Seven','client7'),
     18('client8','client8@gmail.com','Client Eight','client8'),
     19('client9','client9@gmail.com','Client Nine','client9'),
     20('client10','client10@gmail.com','Client Ten','client10'),
     21('client11','client11@gmail.com','Client Eleven','client11'),
     22('client12','client12@gmail.com','Client Twelve','client12'),
     23('client13','client13@gmail.com','Client Thirteen','client13'),
     24('client14','client14@gmail.com','Client Fourteen','client14'),
     25('client15','client15@gmail.com','Client Fifteen','client15');
     26
     27--insert into project.users_table (username,email,name_user,password_user,phone_number) values
     28--('stefanmileski5','stefanmileski5@gmail.com','Stefan Mileski','stefanklient','070 123 456');
    1729
    1830insert into project.administrator (id_user,authorized_from, authorized_till) values (1,'2022-01-01','2024-01-01'),(2,'2022-01-01','2025-01-01'),(3,'2022-01-01','2026-01-01');
    1931
    20 insert into project.client (id_user) values (13),(4),(5),(6);
     32insert into project.client (id_user) values (4),(5),(6),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23),(24);
    2133
    2234insert into project.delivery_man (id_user,employed_from) values (7,now()),(8,now()),(9,now());
    … …  
    2941('Bosch','Germany'),
    3042('Continental','Belgium'),
    31 ('Mahle','Netherlands');
     43('Mahle','Netherlands'),
     44('Sachs','Germany'),
     45('Brembo','Italy');
    3246
    3347insert into project.car_manufacturer (cm_name,cm_country) values
    … …  
    3953
    4054insert into project.repair_shop (rs_name,rs_location,rs_phone_number) values
    41 ('Toyota Service','Partizanska','070 123 456'),
    4255('Volkswagen Service','Ilinenska','075 876 543'),
    4356('Fiat Service','Vodnjanska','078 555 666'),
     57('Ford Service','Teodosij Gologanov','071 333 444'),
     58('Toyota Service','Partizanska','070 123 456'),
    4459('Hyndai Service','Boris Trajkovski','075 500 000');
    4560
    4661insert into project.car(in_production_since,in_production_till,car_type,id_car_manufacturer) values
    4762(2001,2004,'Golf 4',1),
    48 (2006,2009,'Punto',2),
    4963(2004,2006,'Golf 5',1),
     64(2001,2003,'Punto',2),
     65(2004,2008,'Punto Evo',2),
     66(2009,2012,'Grande Punto',2),
     67(2007,2014,'Doblo',2),
    5068(2000,2008,'Fiesta',3),
     69(2005,2015,'Focus',3),
     70(2003,2006,'Aygo',4),
     71(2007,2015,'Yaris',4),
     72(2015,2022,'Corollaa',4),
    5173(1995,2005,'Coupe',5);
    5274
    5375insert into project.car_sample(vin,year_of_production, engine_power, displacement, fuel_type, km_driven, ID_user, ID_car) values
    54 (1111,2008,65,1200,'Diesel',120000,4,4),
    55 (2222,2010,80,1400,'Diesel',150000,5,4),
    56 (3333,2016,65,1200,'Petrol/Gas',65000,6,2),
    57 (4444,1997,140,2000,'Petrol/Gas',185000,13,5);
     76(4444,2003,65,1200,'Diesel',120000,4,1),
     77(5555,2005,80,1400,'Diesel',150000,5,2),
     78(6666,2002,65,1200,'Petrol/Gas',65000,6,3),
     79(1313,2006,140,2000,'Diesel',185000,13,4),
     80(1414,2006,120,1900,'Diesel',240000,14,5),
     81(1515,2006,95,1600,'Petrol',85000,15,6),
     82(1616,2006,80,1400,'Petrol/Gas',125000,16,7),
     83(1717,2006,69,1200,'Petrol',113000,17,8),
     84(1818,2006,65,1200,'Petrol/Gas',150000,18,9),
     85(1919,2006,140,1600,'Diesel',140000,19,10),
     86(2020,2017,200,2400,'Diesel',20000,20,11),
     87(2121,1996,200,2000,'Petrol/Gas',320000,21,12),
     88(2222,2004,120,1600,'Diesel',115000,22,3),
     89(2323,2003,140,2000,'Petrol/Gas',105000,23,3),
     90(2424,2000,120,1900,'Diesel',150000,24,1);
    5891
    5992insert into project.part(part_name, id_part_manufacturer) values
    … …  
    6194('Wheel 20CM',2),
    6295('Shift 6GEARS',3),
    63 ('Radio WITH AUX',1),
     96('Radio with AUX',1),
    6497('Rear Bumper FOR VW GOLF 4',2),
    65 ('Headlight FOR PUNTO',3);
    66 
    67 insert into project.category(category_name) values ('Brakes'),('Cooling System'),('Electrical'),('Engine components'),('Exterior'),('Filters');
     98('Headlight FOR PUNTO',3),
     99('Air Conditioning',4),
     100('Hand Brake',5),
     101('Siren',4),
     102('Electrical window buttons',5),
     103('Sport filter',4),
     104('Air filter',5),
     105('Fuel filter',4),
     106('Oil filter',5);
     107
     108insert into project.category(category_name) values ('Brakes'),('Cooling System'),('Electrical'),('Engine components'),('Exterior'),('Filters'),('Interior'),('Shift');
    68109
    69110insert into project.order_table(order_status,order_date,id_user) values
    70 ('created',now(),4),
    71 ('created',now(),5),
    72 ('created',now(),6),
    73 ('created',now(),13);
    74 
    75 insert into project.service_book (vin) values (1111),(2222),(3333),(4444);
     111('created',now()-interval'12months',4),
     112('created',now()-interval'6months',4),
     113('created',now()-interval'7months',4),
     114('created',now()-interval'1month',4),
     115('created',now()-interval'11months',5),
     116('created',now()-interval'10months',6),
     117('created',now()-interval'2months',6),
     118('created',now()-interval'9months',13),
     119('created',now()-interval'8months',14),
     120('created',now()-interval'7months',15),
     121('created',now()-interval'6months',16),
     122('created',now()-interval'9months',17),
     123('created',now()-interval'5months',17),
     124('created',now()-interval'4months',18),
     125('created',now()-interval'3months',19),
     126('created',now()-interval'2months',20),
     127('created',now()-interval'1month',21),
     128('created',now()-interval'12months',22),
     129('created',now(),23),
     130('created',now()-interval'3months',24),
     131('created',now(),24);
     132
     133
     134insert into project.service_book (vin) values (4444),(5555),(6666),(1313),(1414),(1515),(1616),(1717),(1818),(1919),(2020),(2121),(2222),(2323),(2424);
    76135
    77136insert into project.delivery (delivery_status, delivery_address,id_user,id_order) values
    78 ('in progress','Aerodrom',7,3),
    79137('finished','Pintija',8,1),
    80 ('in progress','Kisela Voda',9,2),
    81 ('in progress','Kozle',8,4);
     138('finished','Kisela Voda',9,2),
     139('finished','Aerodrom',7,3),
     140('in progress','Kozle',8,4),
     141('finished', 'Radisani',7,5),
     142('finished','Dracevo',8,6),
     143('in progress','Gjorce',9,7),
     144('finished','Novo lisice',7,8),
     145('finished','Lisice',8,9),
     146('finished', 'Butel',7,10),
     147('finished','Pintija',8,11),
     148('finished','Kisela Voda',9,12),
     149('finished','Radisani',7,13),
     150('finished','Nerezi',8,14),
     151('finished', 'Aerodrom',7,15),
     152('finished', 'Aerodrom',9,16),
     153('in progress', 'Novo Lisice',9,17),
     154('finished', 'Butel',7,18),
     155('in progress', 'Kozle',8,19),
     156('finished', 'Aerodrom',9,20),
     157('in progress', 'Gjorce',8,21);
     158
     159
    82160
    83161insert into project.price (amount, price_from, id_part) values
     162(3000,now(),1),
    84163(1000,now(),2),
    85164(2000,now(),3),
    86 (3000,now(),1),
    87165(1500,now(),4),
    88166(3500,now(),5),
    89 (300,now(),6);
    90 
    91 insert into project.repair (vin, id_repair_shop, id_service_book) values (1111,3,1),(2222,2,2),(3333,1,3),(4444, 2, 4);
    92 insert into project.repair (vin, id_repair_shop, id_service_book) values (1111,2,1)
    93 
    94 insert into project.review (review_rating, id_repair, id_user) values (10, 1, 4), (6, 2, 5), (8, 3, 6), (9, 4, 13);
    95 insert into project.review (review_rating, id_repair, id_user) values (7, 5, 4)
    96 
    97 insert into project.part_is_from_category (id_part, id_category) values (5,5), (1,4), (6,5);
    98 
    99 insert into project.repair_shop_is_authorized_for_car_make (id_repair_shop, id_car_manufacturer) values (1,4),(2,1),(3,2),(4,5);
    100 
    101 insert into project.order_contains_part (id_order, id_part, quantity_order) values (1,3,1),(2,4,2),(3,1,2),(4,6,1);
    102 
    103 insert into project.part_is_in_stock_in_warehouse (id_part,id_warehouse,quantity_warehouse) values (1,2,100), (2,1,150), (3,1,50), (4,3,75),(5,1,30),(6,2,70);
    104 
    105 insert into project.part_is_appropriate_for_car (id_part, id_car) values (1,1),(1,2),(1,3),(2,1),(2,4),(2,5),(5,1),(4,1),(4,2),(4,3),(4,4),(4,5),(3,5),(6,2);
    106 
    107 
    108 
     167(300,now(),6),
     168(4300,now(),7),
     169(900,now(),8),
     170(500,now(),9),
     171(1300,now(),10),
     172(3500,now(),11),
     173(3600,now(),12),
     174(4500,now(),13),
     175(4100,now(),14);
     176
     177
     178insert into project.repair (vin, id_repair_shop, id_service_book) values
     179(4444,1,1),
     180(5555,1,2),
     181(6666,2,3),
     182(1313,2,4),
     183(1414,2,5),
     184(1515,2,6),
     185(1616,3,7),
     186(1717,3,8),
     187(1818,4,9),
     188(1919,4,10),
     189(2020,4,11),
     190(2121,5,12),
     191(2222,2,13),
     192(2323,2,14),
     193(2424,1,15);
     194
     195
     196insert into project.part_is_from_category (id_part, id_category) values (1,4),(2,7),(3,8),(4,3),(5,5),(6,5),(7,2),(8,1),(9,3),(10,3),(11,6),(12,6),(13,6),(14,6);
     197
     198insert into project.repair_shop_is_authorized_for_car_make (id_repair_shop, id_car_manufacturer) values (1,1),(2,2),(3,3),(4,4),(5,5);
     199
     200
     201insert into project.review (review_rating, id_repair, id_user) values (10,1,4),(6,2,5),(7,3,6),
     202(8,4,13),(9,5,14),(10,6,15),(5,7,16),(3,8,17),(4,9,18),(10,10,19),(9,11,20),(7,12,21),(8,13,22),(9,14,23),(10,15,24);
     203
     204
     205insert into project.part_is_in_stock_in_warehouse (id_part,id_warehouse,quantity_warehouse) values (1,2,100), (2,1,150), (3,1,50), (4,3,75),(5,1,30),(6,2,70),
     206(7,3,10),(8,2,20),(9,3,50),(10,3,25),(11,1,16),(12,2,24),(13,1,20),(14,2,70);
     207
     208
     209insert into project.part_is_appropriate_for_car (id_part, id_car) values
     210(1,1),(1,2),(2,3),(2,4),(2,5),(2,6),(3,8),(3,11),(3,12),(4,1),(4,2),(4,3),(4,4),(4,5),(4,6),(4,7),(4,8),(5,1),(6,3),(6,4),(7,9),(7,10),
     211(7,11),(8,12),(9,1),(9,2),(9,7),(9,8),(10,1),(10,2),(10,12),(11,3),(11,4),(11,5),(12,7),(12,8),(13,6),(14,1),(14,2);
     212
     213
     214
     215insert into project.order_contains_part (id_order, id_part, quantity_order) values
     216(1,1,2),(1,4,1),(2,14,1),(3,10,2),(3,5,2),(4,4,3),(4,1,1),(5,1,2),(5,4,1),(5,14,1),(6,11,2),(7,2,2),(7,6,1),(7,11,1),
     217(8,2,1),(9,11,2),(10,13,1),(11,9,1),(11,12,1),(12,3,1),(12,4,1),(13,9,2),(14,7,1),(15,7,1),(16,7,1),(17,10,1),(17,8,1),
     218(18,2,1),(19,4,1),(19,6,2),(19,11,2),(20,1,2),(21,9,1);
     219
     220
     221
     222-- Important Use Cases
    109223select p.part_name, project.repair.vin,id_repair_shop  from project.repair
    110224join project.car_sample cs on cs.vin = project.repair.vin
    … …  
    136250select part_name from project.part
    137251
    138 drop view project.repair_shop_reviews_summary
     252-- извештај за сите сервиси и за сите број на reviews и avg рејтинг
     253drop view project.repair_shop_reviews_summary;
    139254
    140255create or replace view project.repair_shop_reviews_summary as
    141 select rs.id_repair_shop as rsid , rs.rs_name as rsname , count(r2.review_rating) as reviewcount ,avg(r2.review_rating)::float as reviewaverage
     256select rs.id_repair_shop as rs_id , rs.rs_name as rs_name , count(r2.review_rating) as review_count ,avg(r2.review_rating)::float as review_average
    142257from project.repair_shop rs
    143258left join project.repair r on r.id_repair_shop = rs.id_repair_shop
    … …  
    147262select * from project.repair_shop_reviews_summary
    148263
     264--извештај за сите доставувачи, која е најдобра нарачка што ја направил и кој производ е најскап во таа нарачка
     265drop view project.deliveryman_summary
     266
     267create or replace view project.deliveryman_summary as
     268        select nar.deliverer, nar.deliverer_username,
     269        max(nar.nar_suma) as najdobra_naracka, max(par.par_suma) as najskap_proizvod
     270        from project.delivery_man d
     271        left join
     272        (
     273                select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num
     274                ,sum(p2.amount*ocp.quantity_order) as nar_suma
     275                from project.delivery_man d
     276                left join project.users_table ut on ut.id_user = d.id_user
     277                left join project.delivery d2 on d2.id_user = d.id_user
     278                left join project.order_contains_part ocp on ocp.id_order = d2.id_order
     279                left join project.part p on p.id_part = ocp.id_part
     280                left join project.price p2 on p2.id_part = p.id_part
     281                group by (d.id_user,ut.username,delivery_num)
     282        ) as nar on d.id_user = nar.deliverer
     283        left join
     284        (
     285                select d.id_user as deliverer, ocp.id_order as order_num,d2.id_delivery as delivery_num, p.part_name as part_name,
     286                sum(p2.amount*ocp.quantity_order) as par_suma
     287                from project.delivery_man d
     288                left join project.users_table ut on ut.id_user = d.id_user
     289                left join project.delivery d2 on d2.id_user = d.id_user
     290                left join project.order_contains_part ocp on ocp.id_order = d2.id_order
     291                left join project.part p on p.id_part = ocp.id_part
     292                left join project.price p2 on p2.id_part = p.id_part
     293                group by (d.id_user,ocp.id_order,d2.id_delivery,p.part_name)
     294        ) as par on nar.deliverer = par.deliverer and nar.delivery_num = par.delivery_num
     295        group by nar.deliverer,nar.deliverer_username
     296        order by najdobra_naracka desc
     297
     298select * from project.deliveryman_summary
     299
     300--извештај за сите доставувачи, број на нарачки, вкупна вредност на сите нарачки што ги направил
     301drop view project.deliveryman_count_sum
     302
     303create or replace view project.deliveryman_count_sum as
     304        select nar.deliverer, nar.deliverer_username, count(distinct(nar.delivery_num)) as vkupno_dostavi,
     305        sum(nar.nar_suma) as vkupna_suma
     306        from project.delivery_man d
     307        left join
     308        (
     309                select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num
     310                ,sum(p2.amount*ocp.quantity_order) as nar_suma
     311                from project.delivery_man d
     312                left join project.users_table ut on ut.id_user = d.id_user
     313                left join project.delivery d2 on d2.id_user = d.id_user
     314                left join project.order_contains_part ocp on ocp.id_order = d2.id_order
     315                left join project.part p on p.id_part = ocp.id_part
     316                left join project.price p2 on p2.id_part = p.id_part
     317                group by (d.id_user,ut.username,delivery_num)
     318        ) as nar on d.id_user = nar.deliverer
     319        group by nar.deliverer,nar.deliverer_username
     320        order by vkupna_suma desc
     321
     322select * from project.deliveryman_count_sum
     323
     324-- извештај за сите категории и за кој прозиводител на коли биле купени највеќе делови од таа категорија
     325select c.id_category, c.category_name, cm.id_car_manufacturer , cm.cm_name ,
     326count(ocp.id_part) as category_by_car
     327from project.category as c
     328left join project.part_is_from_category pifc on pifc.id_category = c.id_category
     329left join project.part p on p.id_part = pifc.id_part
     330left join project.order_contains_part ocp on ocp.id_part = p.id_part
     331left join project.order_table ot on ot.id_order = ocp.id_order
     332left join project.users_table ut on ut.id_user = ot.id_user
     333left join project.car_sample cs on cs.id_user = ut.id_user
     334left join project.car c2 on c2.id_car = cs.id_car
     335left join project.car_manufacturer cm on cm.id_car_manufacturer = c2.id_car_manufacturer
     336group by c.id_category,cm.id_car_manufacturer 
     337order by category_by_car desc
     338
     339
     340-- Извештај за најкупуван артикл, во која количина и од која категорија е истиот
     341select c.category_name , p.part_name , coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
     342        left join project.part_is_from_category pifc on pifc.id_part = p.id_part
     343        left join project.category c on c.id_category = pifc.id_category 
     344        left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part
     345        group by c.id_category , p.id_part 
     346        having coalesce(sum(ocp2.quantity_order), 0)=(
     347                select max(maxkol) from
     348                (
     349                        select coalesce(sum(ocp2.quantity_order),0) as maxkol from project.part p
     350                        left join project.part_is_from_category pifc on pifc.id_part = p.id_part
     351                        left join project.category c on c.id_category = pifc.id_category 
     352                        left join project.order_contains_part ocp2 on ocp2.id_part = p.id_part
     353                        group by c.id_category , p.id_part
     354                ) q1
     355                )
     356
     357               
     358--Извештај за сите делови, со нивни категории и производители, и количината која била нарачана од овие делови во секој од последните 3 месеци.
     359select p.id_part, p.part_name, cat.category_name, pm.pm_name, f.narachani_vo_ovoj_mesec as narachani_ovoj_mesec, s.narachani_vo_prethoden_mesec as narachani_prethoden_mesec, t.narachani_vo_predprethoden_mesec as narachani_predprethoden_mesec
     360from project.part as p
     361join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
     362join project.part_is_from_category as pifc on p.id_part = pifc.id_part
     363join project.category as cat on pifc.id_category = cat.id_category
     364left join project.order_contains_part as ocp on p.id_part = ocp.id_part
     365left join project.order_table as o on ocp.id_order = o.id_order
     366left join (
     367select p.id_part, p.part_name, cat.category_name, pm.pm_name,
     368       count(o.id_order) as narachani_vo_ovoj_mesec
     369from project.part as p
     370join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
     371join project.part_is_from_category as pifc on p.id_part = pifc.id_part
     372join project.category as cat on pifc.id_category = cat.id_category
     373left join project.order_contains_part as ocp on p.id_part = ocp.id_part
     374left join project.order_table as o on ocp.id_order = o.id_order
     375                                  and
     376                              extract(month from o.order_date) = extract(month from now()) and extract(year from o.order_date) = extract(year from now())
     377group by p.id_part, p.part_name, cat.category_name, pm.pm_name
     378) as f on p.id_part = f.id_part and pm.pm_name = f.pm_name
     379left join (
     380select p.id_part, p.part_name, cat.category_name, pm.pm_name,
     381       count(o.id_order) as narachani_vo_prethoden_mesec
     382from project.part as p
     383join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
     384join project.part_is_from_category as pifc on p.id_part = pifc.id_part
     385join project.category as cat on pifc.id_category = cat.id_category
     386left join project.order_contains_part as ocp on p.id_part = ocp.id_part
     387left join project.order_table as o on ocp.id_order = o.id_order
     388                                  and
     389                              extract(month from o.order_date) = extract(month from now()-interval'1 month') and extract(year from o.order_date) = extract(year from now()-interval'1 month')
     390group by p.id_part, p.part_name, cat.category_name, pm.pm_name
     391) as s on p.id_part = s.id_part and pm.pm_name = s.pm_name
     392left join (
     393select p.id_part, p.part_name, cat.category_name, pm.pm_name,
     394       count(o.id_order) as narachani_vo_predprethoden_mesec
     395from project.part as p
     396join project.part_manufacturer as pm on p.id_part_manufacturer = pm.id_part_manufacturer
     397join project.part_is_from_category as pifc on p.id_part = pifc.id_part
     398join project.category as cat on pifc.id_category = cat.id_category
     399left join project.order_contains_part as ocp on p.id_part = ocp.id_part
     400left join project.order_table as o on ocp.id_order = o.id_order
     401                                  and
     402                              extract(month from o.order_date) = extract(month from now()-interval'2 month') and extract(year from o.order_date) = extract(year from now()-interval'2 month')
     403group by p.id_part, p.part_name, cat.category_name, pm.pm_name
     404) as t on p.id_part = t.id_part and pm.pm_name = t.pm_name
     405group by p.id_part, p.part_name, cat.category_name, pm.pm_name, f.narachani_vo_ovoj_mesec, s.narachani_vo_prethoden_mesec, t.narachani_vo_predprethoden_mesec
     406
     407
Note: See TracChangeset for help on using the changeset viewer.