hVRw0GmS

1DROP TABLE IF EXISTS client CASCADE;
2DROP TABLE IF EXISTS dealer CASCADE;
3DROP TABLE IF EXISTS contract CASCADE;
4DROP TABLE IF EXISTS repair CASCADE;
5DROP TABLE IF EXISTS master CASCADE;
6DROP TABLE IF EXISTS STO CASCADE;
7DROP TABLE IF EXISTS fix CASCADE;
8
9DROP SEQUENCE IF EXISTS s_client CASCADE;
10DROP SEQUENCE IF EXISTS s_dealer CASCADE;
11DROP SEQUENCE IF EXISTS s_contract CASCADE;
12DROP SEQUENCE IF EXISTS s_repair CASCADE;
13DROP SEQUENCE IF EXISTS s_master CASCADE;
14DROP SEQUENCE IF EXISTS s_STO CASCADE;
15DROP SEQUENCE IF EXISTS s_fix CASCADE;
16
17CREATE SEQUENCE s_client;
18CREATE SEQUENCE s_dealer;
19CREATE SEQUENCE s_contract;
20CREATE SEQUENCE s_repair;
21CREATE SEQUENCE s_master;
22CREATE SEQUENCE s_STO;
23CREATE SEQUENCE s_fix;
24
25
26CREATE TABLE IF NOT EXISTS STO (
27id		INT PRIMARY KEY DEFAULT nextval('s_STO'),
28name		text		NOT NULL,
29address		text 		NOT NULL,
30dateFounding	text		NOT NULL
31);
32
33
34
35CREATE TABLE IF NOT EXISTS client (
36id		INT PRIMARY KEY DEFAULT nextval('s_client'),
37fullName 	text		NOT NULL,
38cityName	text		NOT NULL,
39address		text		NOT NULL,
40phone		int 		NOT NULL
41);
42
43CREATE TABLE IF NOT EXISTS dealer (
44id 		INT PRIMARY KEY DEFAULT nextval('s_dealer'),
45fullName	text		NOT NULL,
46photoNumber  	int 		NOT NULL,
47address		text		NOT NULL,
48phone 		int 		NOT NULL
49);
50
51CREATE TABLE IF NOT EXISTS fix (
52id		INT PRIMARY KEY DEFAULT nextval('s_fix'),
53nameFix		text		NOT NULL,
54specialty	text		NOT NULL
55);
56
57CREATE TABLE IF NOT EXISTS contract (
58id		INT PRIMARY KEY DEFAULT nextval('s_contract'),
59idClient	int 		NOT NULL,
60idDealer	int		NOT NULL,
61dateContract	text		NOT NULL,
62carBrand 	text		NOT NULL,
63photoCarNumber	int 		NOT NULL,
64dateIssue	text		NOT NULL,
65mileage		int 		NOT NULL,
66dateSale 	text		NOT NULL,
67priceSale	decimal(50,2)	NOT NULL,
68commission	decimal(50,2)	NOT NULL,
69notes		text		NOT NULL, /*<-FILE?*/
70FOREIGN KEY (idClient) REFERENCES client(id),
71FOREIGN KEY (idDealer) REFERENCES dealer(id)
72);
73
74
75CREATE TABLE IF NOT EXISTS master (
76id		INT PRIMARY KEY DEFAULT nextval('s_master'),
77name		text 		NOT NULL,
78idSTO		int		NOT NULL,
79specialty	text		NOT NULL,
80experience	int 		NOT NULL,
81salary		decimal(50,2)	NOT NULL,
82phone		int		NOT NULL,
83address		text		NOT NULL,
84FOREIGN KEY (idSTO) REFERENCES STO(id)
85);
86
87CREATE TABLE IF NOT EXISTS repair (
88id		INT PRIMARY KEY DEFAULT nextval('s_repair'),
89idFix		int		NOT NULL,
90idContract	int		NOT NULL,
91idMaster	int 		NOT NULL,
92dateStart	text		NOT NULL,
93repairPeriodDay	int		NOT NULL,
94price		decimal(50,2)	NOT NULL,
95FOREIGN KEY (idFix) REFERENCES fix (id),
96FOREIGN KEY (idContract) REFERENCES contract(id),
97FOREIGN KEY (idMaster) REFERENCES master(id)
98); 
99
100INSERT INTO STO
101VALUES (DEFAULT, 'Koleso', 'Logovor 4', '2000-07-03');
102
103INSERT INTO fix
104VALUES (DEFAULT, 'Motor', 'Dvij');
105
106INSERT INTO client 
107VALUES (DEFAULT, 'Petr Petrov Petrovich', 'Kharkov', 'Kojemako 5a', 38082928);
108
109INSERT INTO dealer
110VALUES (DEFAULT, 'John Bar Fergust', 1, 'Fora 3', 390827232);
111
112INSERT INTO contract
113VALUES (DEFAULT, 1, 1, '2018-01-18', 'BMW', 1, '2012-05-02', 50000, '2012-06-20', 20000, 3000, 'TEXT: SHOTO SLUCHILOS');
114
115INSERT INTO master 
116VALUES (DEFAULT, 'Ivan Pedro Kurion', 1, 'All', 15, 30000, 9023842, 'pr. Lemur 45v');
117
118INSERT INTO repair
119VALUES (DEFAULT, 1, 1, 1, '2018-01-20', 90, 4000);
120
121SELECT * FROM contract;