tpc-c/table_def.sql at main · const-sambird/tpc-c · 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
126
127
128
129
130
131
132
133
134
135
136
DROP TABLE IF EXISTS WAREHOUSE CASCADE;
DROP TABLE IF EXISTS DISTRICT CASCADE;
DROP TABLE IF EXISTS CUSTOMER CASCADE;
DROP TABLE IF EXISTS HISTORY CASCADE;
DROP TABLE IF EXISTS NEW_ORDER CASCADE;
DROP TABLE IF EXISTS ORDERS CASCADE;
DROP TABLE IF EXISTS ORDER_LINE CASCADE;
DROP TABLE IF EXISTS ITEM CASCADE;
DROP TABLE IF EXISTS STOCK CASCADE;

CREATE TABLE WAREHOUSE (
    W_ID INTEGER,
    W_NAME VARCHAR(10),
    W_STREET_1 VARCHAR(20),
    W_STREET_2 VARCHAR(20),
    W_CITY VARCHAR(20),
    W_STATE CHAR(2),
    W_ZIP CHAR(9),
    W_TAX NUMERIC(4,4),
    W_YTD NUMERIC(12,2)
    -- PRIMARY KEY (W_ID)
);

CREATE TABLE DISTRICT (
    D_ID INTEGER,
    D_W_ID INTEGER,
    D_NAME VARCHAR(10),
    D_STREET_1 VARCHAR(20),
    D_STREET_2 VARCHAR(20),
    D_CITY VARCHAR(20),
    D_STATE CHAR(2),
    D_ZIP CHAR(9),
    D_TAX NUMERIC(4,4),
    D_YTD NUMERIC(12,2),
    D_NEXT_O_ID INTEGER
    -- PRIMARY KEY (D_W_ID, D_ID)
);

CREATE TABLE CUSTOMER (
    C_ID INTEGER,
    C_D_ID INTEGER,
    C_W_ID INTEGER,
    C_FIRST VARCHAR(16),
    C_MIDDLE CHAR(2),
    C_LAST VARCHAR(16),
    C_STREET_1 VARCHAR(20),
    C_STREET_2 VARCHAR(20),
    C_CITY VARCHAR(20),
    C_STATE CHAR(2),
    C_ZIP CHAR(9),
    C_PHONE CHAR(16),
    C_SINCE TIMESTAMP,
    C_CREDIT CHAR(2),
    C_CREDIT_LIM NUMERIC(12,2),
    C_DISCOUNT NUMERIC(4,4),
    C_BALANCE NUMERIC(12,2),
    C_YTD_PAYMENT NUMERIC(12,2),
    C_PAYMENT_CNT NUMERIC(4),
    C_DELIVERY_CNT NUMERIC(4),
    C_DATA VARCHAR(500)
    -- PRIMARY KEY (C_W_ID, C_D_ID, C_ID)
);

CREATE TABLE HISTORY (
    H_C_ID INTEGER,
    H_C_D_ID INTEGER,
    H_C_W_ID INTEGER,
    H_D_ID INTEGER,
    H_W_ID INTEGER,
    H_DATE TIMESTAMP,
    H_AMOUNT NUMERIC(6,2),
    H_DATA VARCHAR(24)
);

CREATE TABLE NEW_ORDER (
    NO_O_ID INTEGER,
    NO_D_ID INTEGER,
    NO_W_ID INTEGER
    -- PRIMARY KEY (NO_W_ID, NO_D_ID, NO_O_ID)
);

CREATE TABLE ORDERS (
    O_ID INTEGER,
    O_D_ID INTEGER,
    O_W_ID INTEGER,
    O_C_ID INTEGER,
    O_ENTRY_D TIMESTAMP,
    O_CARRIER_ID INTEGER,
    O_OL_CNT NUMERIC(2),
    O_ALL_LOCAL NUMERIC(1)
    -- PRIMARY KEY (O_W_ID, O_D_ID, O_ID)
);

CREATE TABLE ORDER_LINE (
    OL_O_ID INTEGER,
    OL_D_ID INTEGER,
    OL_W_ID INTEGER,
    OL_NUMBER INTEGER,
    OL_I_ID INTEGER,
    OL_SUPPLY_W_ID INTEGER,
    OL_DELIVERY_D TIMESTAMP,
    OL_QUANTITY NUMERIC(2),
    OL_AMOUNT NUMERIC(6,2),
    OL_DIST_INFO CHAR(24)
    -- PRIMARY KEY (OL_W_ID, OL_D_ID, OL_O_ID, OL_NUMBER)
);

CREATE TABLE ITEM (
    I_ID INTEGER,
    I_IM_ID INTEGER,
    I_NAME VARCHAR(24),
    I_PRICE NUMERIC(5,2),
    I_DATA VARCHAR(50)
    -- PRIMARY KEY (I_ID)
);

CREATE TABLE STOCK (
    S_I_ID INTEGER,
    S_W_ID INTEGER,
    S_QUANTITY NUMERIC(4),
    S_DIST_01 CHAR(24),
    S_DIST_02 CHAR(24),
    S_DIST_03 CHAR(24),
    S_DIST_04 CHAR(24),
    S_DIST_05 CHAR(24),
    S_DIST_06 CHAR(24),
    S_DIST_07 CHAR(24),
    S_DIST_08 CHAR(24),
    S_DIST_09 CHAR(24),
    S_DIST_10 CHAR(24),
    S_YTD NUMERIC(8),
    S_ORDER_CNT NUMERIC(4),
    S_REMOTE_CNT NUMERIC(4),
    S_DATA VARCHAR(50)
    -- PRIMARY KEY (S_W_ID, S_I_ID)
);