480/schema.sql at master · juice176/480 · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
CREATE SCHEMA taxi_management;
SET search_path TO taxi_management;

CREATE TABLE MANAGER(
	NAME TEXT,
	SSN CHARACTER(9),
	EMAIL TEXT,

	CONSTRAINT manager_pkey PRIMARY KEY (SSN)
);

CREATE TABLE ADDRESS(
	ROAD TEXT,
	NUMBER TEXT,
	CITY TEXT,

	CONSTRAINT address_pkey PRIMARY KEY (ROAD, NUMBER, CITY)
);


CREATE TABLE CLIENT(
	NAME TEXT,
	EMAIL TEXT,

	CONSTRAINT client_pkey PRIMARY KEY (EMAIL)
);

CREATE TABLE DRIVER(
	NAME TEXT,

	ROAD TEXT NOT NULL,
	NUMBER TEXT NOT NULL,
	CITY TEXT NOT NULL,

	CONSTRAINT driver_pkey PRIMARY KEY (NAME),
	CONSTRAINT driver_address_fkey FOREIGN KEY (ROAD, NUMBER, CITY) REFERENCES ADDRESS(ROAD, NUMBER, CITY)
);

CREATE TABLE CAR(
	CAR_ID INT,
	BRAND TEXT,

CONSTRAINT car_pkey PRIMARY KEY (CAR_ID)
);

CREATE TABLE MODEL(
	MODEL_ID INT,
	TRANSMISSION TEXT,
	YEAR INT,
	COLOR TEXT,

	CAR_ID INT,

    CONSTRAINT model_pkey PRIMARY KEY (MODEL_ID, CAR_ID),
    CONSTRAINT model_car_fkey FOREIGN KEY (CAR_ID) REFERENCES CAR(CAR_ID)
);

CREATE TABLE RENT(
	RENT_ID SERIAL,
	RENT_DATE DATE,

	EMAIL TEXT NOT NULL,

	NAME TEXT NOT NULL,

	MODEL_ID INT NOT NULL,
	CAR_ID INT NOT NULL,

	CONSTRAINT rent_pkey PRIMARY KEY (RENT_ID),
	CONSTRAINT rent_client_fkey FOREIGN KEY (EMAIL) REFERENCES CLIENT(EMAIL),
	CONSTRAINT rent_driver_fkey FOREIGN KEY (NAME) REFERENCES DRIVER(NAME),
	CONSTRAINT rent_model_fkey FOREIGN KEY (MODEL_ID, CAR_ID) REFERENCES MODEL(MODEL_ID, CAR_ID)
);

CREATE TABLE REVIEW(
	REVIEW_ID SERIAL,
	RATING INT,
	MESSAGE TEXT,

	NAME TEXT NOT NULL,

	EMAIL TEXT NOT NULL,

	CONSTRAINT review_pkey PRIMARY KEY (REVIEW_ID, NAME),
	CONSTRAINT review_driver_fkey FOREIGN KEY (NAME) REFERENCES DRIVER(NAME),
	CONSTRAINT review_client_fkey FOREIGN KEY (EMAIL) REFERENCES CLIENT(EMAIL)
);

CREATE TABLE CREDIT_CARD(
	NUMBER CHARACTER(16),

	EMAIL TEXT,

	ROAD TEXT NOT NULL,
	NUMBER_ADDR TEXT NOT NULL,
	CITY TEXT NOT NULL,

	CONSTRAINT creditcard_pkey PRIMARY KEY (NUMBER),
	CONSTRAINT creditcard_client_fkey FOREIGN KEY (EMAIL) REFERENCES CLIENT(EMAIL),
	CONSTRAINT creditcard_address_fkey FOREIGN KEY (ROAD, NUMBER_ADDR, CITY) REFERENCES ADDRESS(ROAD,NUMBER,CITY)
);


CREATE TABLE HasAddress(
    EMAIL TEXT,

    ROAD TEXT NOT NULL,
    NUMBER TEXT NOT NULL,
    CITY TEXT NOT NULL,

    CONSTRAINT hasaddress_pkey PRIMARY KEY(EMAIL, ROAD, NUMBER, CITY),
    CONSTRAINT hasaddress_client_fkey FOREIGN KEY (EMAIL) REFERENCES CLIENT(EMAIL),
    CONSTRAINT hasaddress_address_fkey FOREIGN KEY (ROAD, NUMBER, CITY) REFERENCES ADDRESS(ROAD,NUMBER,CITY)
);

CREATE TABLE CAN_DRIVE (
	NAME TEXT,

	MODEL_ID INT NOT NULL,
	CAR_ID INT NOT NULL,

    CONSTRAINT candrive_pkey PRIMARY KEY (NAME, MODEL_ID, CAR_ID),
    CONSTRAINT candrive_driver_fkey FOREIGN KEY (NAME) REFERENCES DRIVER(NAME),
    CONSTRAINT candrive_model_fkey FOREIGN KEY (MODEL_ID, CAR_ID) REFERENCES MODEL(MODEL_ID, CAR_ID)
);