· 4 years ago · May 23, 2021, 10:48 AM
1drop schema if exists bdz_cars cascade;
2create schema bdz_cars;
3
4drop table if exists bdz_cars.tcl_client;
5drop table if exists bdz_cars.tpurchase;
6drop table if exists bdz_cars.tcl_payment;
7drop table if exists bdz_cars.tcar;
8drop table if exists bdz_cars.tbasic_equip;
9drop table if exists bdz_cars.tcar_str;
10drop table if exists bdz_cars.tcl_country;
11drop table if exists bdz_cars.tcl_charact_mean;
12drop table if exists bdz_cars.tset;
13drop table if exists bdz_cars.tadd_config;
14drop table if exists bdz_cars.tcl_charact;
15
16create table bdz_cars.tcl_payment
17(
18 payment_id serial Primary Key,
19 payment_name character varying(50) not null
20);
21
22create table bdz_cars.tcl_charact
23(
24 charact_id serial Primary key,
25 charact_namech character varying(50) not null ,
26 charct_cost decimal not null,
27 check (charct_cost > 0)
28);
29
30create table bdz_cars.tcl_client
31(
32 client_id serial primary key,
33 datas date not null,
34 first_name character varying(50) not null,
35 surname character varying(50) not null,
36 telephone_number integer not null,
37 pasport character varying(50) not null unique,
38 address character varying(50) not null
39);
40
41create table bdz_cars.tset
42(
43 add_set_id serial primary key,
44 add_set_name character varying(50) not null unique
45);
46
47create table bdz_cars.tcl_country
48(
49 country_id serial Primary Key,
50 country_name character varying(50) not null
51);
52
53create table bdz_cars.tcl_charact_mean
54(
55 charact_mean_id serial Primary Key,
56 charact_parent_id integer not null,
57 charact_mean character varying(50) not null,
58 FOREIGN KEY (charact_parent_id) REFERENCES bdz_cars.tcl_charact_mean (charact_mean_id) on delete cascade
59);
60
61
62
63create table bdz_cars.tcar_str
64(
65 car_str_id serial Primary Key,
66 car_str_parent integer not null,
67 car_str_name character varying(50) not null,
68 FOREIGN KEY (car_str_parent) REFERENCES bdz_cars.tcar_str (car_str_id)
69);
70create table bdz_cars.tbasic_equip
71(
72 basic_equip_id serial Primary Key,
73 country_id integer not null,
74 model_id integer not null,
75 color_id integer not null,
76 engine_id integer not null,
77 number_of_seats_id integer not null,
78 display_id integer not null,
79 car_cost decimal not null,
80 check (car_cost > 0),
81 FOREIGN KEY (country_id) REFERENCES bdz_cars.tcl_country (country_id) on delete cascade,
82 FOREIGN KEY (model_id) REFERENCES bdz_cars.tcar_str (car_str_id) on delete cascade,
83 FOREIGN KEY (color_id) REFERENCES bdz_cars.tcl_charact_mean (charact_mean_id) on delete cascade,
84 FOREIGN KEY (engine_id) REFERENCES bdz_cars.tcl_charact_mean (charact_mean_id) on delete cascade,
85 FOREIGN KEY (number_of_seats_id) REFERENCES bdz_cars.tcl_charact_mean (charact_mean_id) on delete cascade,
86 FOREIGN KEY (display_id) REFERENCES bdz_cars.tcl_charact_mean (charact_mean_id) on delete cascade
87);
88
89
90create table bdz_cars.tcar
91(
92 car_id serial Primary Key,
93 basic_equip_id INteger not null,
94 add_set_id integer,
95 FOREIGN KEY (basic_equip_id) REFERENCES bdz_cars.tbasic_equip (basic_equip_id) on delete cascade,
96 FOREIGN KEY (add_set_id) REFERENCES bdz_cars.tset (add_set_id) on delete cascade
97);
98
99create table bdz_cars.tpurchase
100(
101 purchase_id serial Primary Key,
102 client_id integer not null,
103 payment_id integer not null,
104 car_id integer not null,
105 datas date not null,
106 FOREIGN KEY (client_id) REFERENCES bdz_cars.tcl_client (client_id) on delete cascade,
107 FOREIGN KEY (payment_id) REFERENCES bdz_cars.tcl_payment (payment_id) on delete cascade,
108 FOREIGN KEY (car_id) REFERENCES bdz_cars.tcar (car_id) on delete cascade
109);
110
111
112create table bdz_cars.tadd_config
113(
114 add_config_id serial primary key,
115 add_set_id integer not null,
116 charact_mean_id integer not null,
117 charact_id Integer not null,
118 FOREIGN KEY (add_set_id) REFERENCES bdz_cars.tset (add_set_id) on delete cascade,
119 FOREIGN KEY (charact_mean_id) REFERENCES bdz_cars.tcl_charact_mean (charact_mean_id) on delete cascade,
120 FOREIGN KEY (charact_id) REFERENCES bdz_cars.tcl_charact (charact_id) on delete cascade
121);
122
123
124
125