-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathinit_db.sql
200 lines (185 loc) · 6.38 KB
/
init_db.sql
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
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
-- REGION TABLE ----------------------------------------------------------
CREATE TABLE public.region
(
r_regionkey integer NOT NULL,
r_name character(25) COLLATE pg_catalog."default" NOT NULL,
r_comment character varying(152) COLLATE pg_catalog."default",
CONSTRAINT region_pkey PRIMARY KEY (r_regionkey)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.region
OWNER to postgres;
-- NATION TABLE ----------------------------------------------------------
CREATE TABLE public.nation
(
n_nationkey integer NOT NULL,
n_name character(25) COLLATE pg_catalog."default" NOT NULL,
n_regionkey integer NOT NULL,
n_comment character varying(152) COLLATE pg_catalog."default",
CONSTRAINT nation_pkey PRIMARY KEY (n_nationkey),
CONSTRAINT fk_nation FOREIGN KEY (n_regionkey)
REFERENCES public.region (r_regionkey) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.nation
OWNER to postgres;
-- PART TABLE ----------------------------------------------------------
CREATE TABLE public.part
(
p_partkey integer NOT NULL,
p_name character varying(55) COLLATE pg_catalog."default" NOT NULL,
p_mfgr character(25) COLLATE pg_catalog."default" NOT NULL,
p_brand character(10) COLLATE pg_catalog."default" NOT NULL,
p_type character varying(25) COLLATE pg_catalog."default" NOT NULL,
p_size integer NOT NULL,
p_container character (10) COLLATE pg_catalog."default" NOT NULL,
p_retailprice numeric(15,2) NOT NULL,
p_comment character varying(23) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT part_pkey PRIMARY KEY (p_partkey)
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.part
OWNER to postgres;
-- SUPPLIER TABLE ----------------------------------------------------------
CREATE TABLE public.supplier
(
s_suppkey integer NOT NULL,
s_name character(25) COLLATE pg_catalog."default" NOT NULL,
s_address character varying(40) COLLATE pg_catalog."default" NOT NULL,
s_nationkey integer NOT NULL,
s_phone character(15) COLLATE pg_catalog."default" NOT NULL,
s_acctbal numeric(15,2) NOT NULL,
s_comment character varying(101) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT supplier_pkey PRIMARY KEY (s_suppkey),
CONSTRAINT fk_supplier FOREIGN KEY (s_nationkey)
REFERENCES public.nation (n_nationkey) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.supplier
OWNER to postgres;
-- PARTSUPP TABLE ---------------------------------------------------------
CREATE TABLE public.partsupp
(
ps_partkey integer NOT NULL,
ps_suppkey integer NOT NULL,
ps_availqty integer NOT NULL,
ps_supplycost numeric(15,2) NOT NULL,
ps_comment character varying(199) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT partsupp_pkey PRIMARY KEY (ps_partkey, ps_suppkey),
CONSTRAINT fk_ps_suppkey_partkey FOREIGN KEY (ps_partkey)
REFERENCES public.part (p_partkey) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT fk_ps_suppkey_suppkey FOREIGN KEY (ps_suppkey)
REFERENCES public.supplier (s_suppkey) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.partsupp
OWNER to postgres;
-- Customer table ---------------------------------------------------------
CREATE TABLE public.customer
(
c_custkey integer NOT NULL,
c_name character varying(25) COLLATE pg_catalog."default" NOT NULL,
c_address character varying(40) COLLATE pg_catalog."default" NOT NULL,
c_nationkey integer NOT NULL,
c_phone character(15) COLLATE pg_catalog."default" NOT NULL,
c_acctbal numeric(15,2) NOT NULL,
c_mktsegment character(10) COLLATE pg_catalog."default" NOT NULL,
c_comment character varying(117) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT customer_pkey PRIMARY KEY (c_custkey),
CONSTRAINT fk_customer FOREIGN KEY (c_nationkey)
REFERENCES public.nation (n_nationkey) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH(
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.customer
OWNER to postgres;
-- ORDERS TABLE -----------------------------------------------------------------
CREATE TABLE public.orders
(
o_orderkey integer NOT NULL,
o_custkey integer NOT NULL,
o_orderstatus character(1) COLLATE pg_catalog."default" NOT NULL,
o_totalprice numeric(15,2) NOT NULL,
o_orderdate date NOT NULL,
o_orderpriority character(15) COLLATE pg_catalog."default" NOT NULL,
o_clerk character(15) COLLATE pg_catalog."default" NOT NULL,
o_shippriority integer NOT NULL,
o_comment character varying(79) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT orders_pkey PRIMARY KEY (o_orderkey),
CONSTRAINT fk_orders FOREIGN KEY (o_custkey)
REFERENCES public.customer (c_custkey) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.orders
OWNER to postgres;
-- LINEITEM TABLE ----------------------------------------------------------
CREATE TABLE public.lineitem
(
l_orderkey integer NOT NULL,
l_partkey integer NOT NULL,
l_suppkey integer NOT NULL,
l_linenumber integer NOT NULL,
l_quantity numeric(15,2) NOT NULL,
l_extendedprice numeric(15,2) NOT NULL,
l_discount numeric(15,2) NOT NULL,
l_tax numeric(15,2) NOT NULL,
l_returnflag character(1) COLLATE pg_catalog."default" NOT NULL,
l_linestatus character(1) COLLATE pg_catalog."default" NOT NULL,
l_shipdate date NOT NULL,
l_commitdate date NOT NULL,
l_receiptdate date NOT NULL,
l_shipinstruct character(25) COLLATE pg_catalog."default" NOT NULL,
l_shipmode character(10) COLLATE pg_catalog."default" NOT NULL,
l_comment character varying(44) COLLATE pg_catalog."default" NOT NULL,
CONSTRAINT lineitem_pkey PRIMARY KEY (l_orderkey, l_partkey, l_suppkey, l_linenumber),
CONSTRAINT fk_lineitem_orderkey FOREIGN KEY (l_orderkey)
REFERENCES public.orders (o_orderkey) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT fk_lineitem_partkey FOREIGN KEY (l_partkey)
REFERENCES public.part (p_partkey) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION,
CONSTRAINT fk_lineitem_suppkey FOREIGN KEY (l_suppkey)
REFERENCES public.supplier (s_suppkey) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE NO ACTION
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;
ALTER TABLE public.lineitem
OWNER to postgres;