x5jCUzGs

1CREATE DATABASE computer_repair_firm
2    WITH
3    OWNER = postgres
4    ENCODING = 'UTF8'
5    LC_COLLATE = 'Russian_Russia.1251'
6    LC_CTYPE = 'Russian_Russia.1251'
7    TABLESPACE = pg_default
8    CONNECTION LIMIT = -1
9    IS_TEMPLATE = False;
10
11COMMENT ON DATABASE computer_repair_firm
12    IS 'База данных информационной системы фирмы по ремонту компьютерной техники';
13
14
15CREATE TABLE IF NOT EXISTS public.clients
16(
17    client_code integer NOT NULL DEFAULT nextval('clients_client_code_seq'::regclass),
18    full_name character varying(255) COLLATE pg_catalog."default",
19    address character varying(255) COLLATE pg_catalog."default",
20    phone_number character varying(15) COLLATE pg_catalog."default",
21    email character varying(255) COLLATE pg_catalog."default",
22    CONSTRAINT clients_pkey PRIMARY KEY (client_code)
23)
24
25TABLESPACE pg_default;
26
27ALTER TABLE IF EXISTS public.clients
28    OWNER to postgres;
29
30
31CREATE TABLE IF NOT EXISTS public.component_order
32(
33    component_code integer NOT NULL,
34    order_number integer NOT NULL,
35    quantity integer,
36    CONSTRAINT component_order_pkey PRIMARY KEY (component_code, order_number),
37    CONSTRAINT component_order_component_code_fkey FOREIGN KEY (component_code)
38        REFERENCES public.components (component_code) MATCH SIMPLE
39        ON UPDATE NO ACTION
40        ON DELETE NO ACTION,
41    CONSTRAINT component_order_order_number_fkey FOREIGN KEY (order_number)
42        REFERENCES public.orders (order_number) MATCH SIMPLE
43        ON UPDATE NO ACTION
44        ON DELETE NO ACTION,
45    CONSTRAINT fk_component_order_components FOREIGN KEY (component_code)
46        REFERENCES public.components (component_code) MATCH SIMPLE
47        ON UPDATE NO ACTION
48        ON DELETE NO ACTION,
49    CONSTRAINT fk_component_order_orders FOREIGN KEY (order_number)
50        REFERENCES public.orders (order_number) MATCH SIMPLE
51        ON UPDATE NO ACTION
52        ON DELETE NO ACTION
53)
54
55TABLESPACE pg_default;
56
57ALTER TABLE IF EXISTS public.component_order
58    OWNER to postgres;
59    
60    
61CREATE TABLE IF NOT EXISTS public.components
62(
63    component_code integer NOT NULL DEFAULT nextval('components_component_code_seq'::regclass),
64    name character varying(255) COLLATE pg_catalog."default",
65    cost numeric(10,2),
66    available_quantity integer,
67    CONSTRAINT components_pkey PRIMARY KEY (component_code)
68)
69
70TABLESPACE pg_default;
71
72ALTER TABLE IF EXISTS public.components
73    OWNER to postgres;
74
75
76CREATE TABLE IF NOT EXISTS public.employee_order
77(
78    order_number integer NOT NULL,
79    employee_id integer NOT NULL,
80    CONSTRAINT employee_order_pkey PRIMARY KEY (order_number, employee_id),
81    CONSTRAINT employee_order_employee_id_fkey FOREIGN KEY (employee_id)
82        REFERENCES public.employees (employee_id) MATCH SIMPLE
83        ON UPDATE NO ACTION
84        ON DELETE NO ACTION,
85    CONSTRAINT employee_order_order_number_fkey FOREIGN KEY (order_number)
86        REFERENCES public.orders (order_number) MATCH SIMPLE
87        ON UPDATE NO ACTION
88        ON DELETE NO ACTION,
89    CONSTRAINT fk_employee_order_employees FOREIGN KEY (employee_id)
90        REFERENCES public.employees (employee_id) MATCH SIMPLE
91        ON UPDATE NO ACTION
92        ON DELETE NO ACTION
93)
94
95TABLESPACE pg_default;
96
97ALTER TABLE IF EXISTS public.employee_order
98    OWNER to postgres;
99
100
101CREATE TABLE IF NOT EXISTS public.employees
102(
103    employee_id integer NOT NULL DEFAULT nextval('employees_employee_id_seq'::regclass),
104    full_name character varying(255) COLLATE pg_catalog."default",
105    "position" character varying(255) COLLATE pg_catalog."default",
106    phone_number character varying(15) COLLATE pg_catalog."default",
107    CONSTRAINT employees_pkey PRIMARY KEY (employee_id)
108)
109
110TABLESPACE pg_default;
111
112ALTER TABLE IF EXISTS public.employees
113    OWNER to postgres;
114
115
116CREATE TABLE IF NOT EXISTS public.order_service
117(
118    order_number integer NOT NULL,
119    service_code integer NOT NULL,
120    quantity integer,
121    CONSTRAINT order_service_pkey PRIMARY KEY (order_number, service_code),
122    CONSTRAINT fk_order_service_orders FOREIGN KEY (order_number)
123        REFERENCES public.orders (order_number) MATCH SIMPLE
124        ON UPDATE NO ACTION
125        ON DELETE NO ACTION,
126    CONSTRAINT fk_order_service_services FOREIGN KEY (service_code)
127        REFERENCES public.services (service_code) MATCH SIMPLE
128        ON UPDATE NO ACTION
129        ON DELETE NO ACTION,
130    CONSTRAINT order_service_order_number_fkey FOREIGN KEY (order_number)
131        REFERENCES public.orders (order_number) MATCH SIMPLE
132        ON UPDATE NO ACTION
133        ON DELETE NO ACTION,
134    CONSTRAINT order_service_service_code_fkey FOREIGN KEY (service_code)
135        REFERENCES public.services (service_code) MATCH SIMPLE
136        ON UPDATE NO ACTION
137        ON DELETE NO ACTION
138)
139
140TABLESPACE pg_default;
141
142ALTER TABLE IF EXISTS public.order_service
143    OWNER to postgres;
144
145
146CREATE TABLE IF NOT EXISTS public.orders
147(
148    order_number integer NOT NULL DEFAULT nextval('orders_order_number_seq'::regclass),
149    client_code integer,
150    device_type character varying(255) COLLATE pg_catalog."default",
151    status character varying(255) COLLATE pg_catalog."default",
152    start_date date,
153    problem_description text COLLATE pg_catalog."default",
154    repair_description text COLLATE pg_catalog."default",
155    repair_cost numeric(10,2),
156    completion_date date,
157    CONSTRAINT orders_pkey PRIMARY KEY (order_number),
158    CONSTRAINT orders_client_code_fkey FOREIGN KEY (client_code)
159        REFERENCES public.clients (client_code) MATCH SIMPLE
160        ON UPDATE NO ACTION
161        ON DELETE NO ACTION
162)
163
164TABLESPACE pg_default;
165
166ALTER TABLE IF EXISTS public.orders
167    OWNER to postgres;
168
169
170CREATE TABLE IF NOT EXISTS public.services
171(
172    service_code integer NOT NULL DEFAULT nextval('services_service_code_seq'::regclass),
173    name character varying(255) COLLATE pg_catalog."default",
174    cost numeric(10,2),
175    description text COLLATE pg_catalog."default",
176    CONSTRAINT services_pkey PRIMARY KEY (service_code)
177)
178
179TABLESPACE pg_default;
180
181ALTER TABLE IF EXISTS public.services
182    OWNER to postgres;