-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathreset.sql
148 lines (128 loc) · 5.13 KB
/
reset.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
--------------------------------------------------
--- Drop tables if they exist
--------------------------------------------------
DROP TABLE IF EXISTS category CASCADE;
DROP TABLE IF EXISTS simple_category CASCADE;
DROP TABLE IF EXISTS super_category CASCADE;
DROP TABLE IF EXISTS has_category CASCADE;
DROP TABLE IF EXISTS has_other CASCADE;
DROP TABLE IF EXISTS IVM CASCADE;
DROP TABLE IF EXISTS product CASCADE;
DROP TABLE IF EXISTS retail_point CASCADE;
DROP TABLE IF EXISTS installed_at CASCADE;
DROP TABLE IF EXISTS shelf CASCADE;
DROP TABLE IF EXISTS planogram CASCADE;
DROP TABLE IF EXISTS retailer CASCADE;
DROP TABLE IF EXISTS responsible_for CASCADE;
DROP TABLE IF EXISTS replenishment_event CASCADE;
--------------------------------------------------
--- Drop functions/procedures if they exist
--------------------------------------------------s
DROP FUNCTION IF EXISTS trigger_category;
DROP FUNCTION IF EXISTS trigger_replenishment_units;
DROP FUNCTION IF EXISTS trigger_product_on_shelf;
--------------------------------------------------
--- Create/re-create the tables/relations
--------------------------------------------------
CREATE TABLE category (
name VARCHAR(255) NOT NULL,
CONSTRAINT pk_category PRIMARY KEY(name)
);
CREATE TABLE simple_category (
name VARCHAR(255) NOT NULL,
CONSTRAINT pk_simple_category PRIMARY KEY(name),
CONSTRAINT fk_category_simple_category FOREIGN KEY(name) REFERENCES category(name)
);
CREATE TABLE super_category (
name VARCHAR(255) NOT NULL,
CONSTRAINT pk_super_category PRIMARY KEY(name),
CONSTRAINT fk_category_super_category FOREIGN KEY(name) REFERENCES category(name)
);
CREATE TABLE has_other (
category VARCHAR(255) NOT NULL,
super_category VARCHAR(255) NOT NULL,
CONSTRAINT pk_has_other PRIMARY KEY(category),
CONSTRAINT fk_has_other_category FOREIGN KEY(category) REFERENCES category(name),
CONSTRAINT fk_has_other_super_category FOREIGN KEY(super_category) REFERENCES super_category(name)
);
CREATE TABLE product (
ean INT NOT NULL,
cat VARCHAR(255) NOT NULL,
descr VARCHAR(255) NOT NULL,
CONSTRAINT pk_product PRIMARY KEY(ean),
CONSTRAINT fk_product_category FOREIGN KEY(cat) REFERENCES category(name)
);
CREATE TABLE has_category (
ean INT NOT NULL,
name VARCHAR(255) NOT NULL,
CONSTRAINT fk_has_category_product FOREIGN KEY(ean) REFERENCES product(ean),
CONSTRAINT fk_has_category_category FOREIGN KEY(name) REFERENCES category(name)
);
CREATE TABLE IVM (
serial_number INT NOT NULL,
manufacturer VARCHAR(255) NOT NULL,
CONSTRAINT pk_IVM PRIMARY KEY(serial_number, manufacturer)
);
CREATE TABLE retail_point (
name VARCHAR(255) NOT NULL,
county VARCHAR(255) NOT NULL,
district VARCHAR(255) NOT NULL,
CONSTRAINT pk_retail_point PRIMARY KEY(name)
);
CREATE TABLE installed_at (
serial_number INT NOT NULL,
manufacturer VARCHAR(255) NOT NULL,
place VARCHAR(255) NOT NULL,
CONSTRAINT pk_installed_in PRIMARY KEY(serial_number, manufacturer),
CONSTRAINT fk_installed_in_IVM FOREIGN KEY(serial_number, manufacturer) REFERENCES IVM(serial_number, manufacturer),
CONSTRAINT fk_installed_in_retail_point FOREIGN KEY(place) REFERENCES retail_point(name)
);
CREATE TABLE shelf (
nro INT NOT NULL,
serial_number INT NOT NULL,
manufacturer VARCHAR(255) NOT NULL,
height INT NOT NULL,
name VARCHAR(255) NOT NULL,
CONSTRAINT pk_shelf PRIMARY KEY(nro, serial_number, manufacturer),
CONSTRAINT fk_shelf_IVM FOREIGN KEY(serial_number, manufacturer) REFERENCES IVM(serial_number, manufacturer),
CONSTRAINT fk_shelf_category FOREIGN KEY(name) REFERENCES category(name)
);
CREATE TABLE planogram (
ean INT NOT NULL,
nro INT NOT NULL,
serial_number INT NOT NULL,
manufacturer VARCHAR(255) NOT NULL,
faces INT NOT NULL,
units INT NOT NULL,
loc VARCHAR NOT NULL,
CONSTRAINT pk_planogram PRIMARY KEY(ean, nro, serial_number, manufacturer),
CONSTRAINT fk_planogram_shelf FOREIGN KEY(nro, serial_number, manufacturer) REFERENCES shelf(nro, serial_number, manufacturer),
CONSTRAINT fk_planogram_product FOREIGN KEY(ean) REFERENCES product(ean)
);
CREATE TABLE retailer (
tin INT NOT NULL,
name VARCHAR(255) NOT NULL UNIQUE,
CONSTRAINT pk_retailer PRIMARY KEY(tin)
);
CREATE TABLE responsible_for (
name_cat VARCHAR(255) NOT NULL,
tin INT NOT NULL,
serial_number INT NOT NULL,
manufacturer VARCHAR(255) NOT NULL,
CONSTRAINT pk_responsible_for PRIMARY KEY(serial_number, manufacturer),
CONSTRAINT fk_responsible_for_IVM FOREIGN KEY(serial_number, manufacturer) REFERENCES IVM(serial_number, manufacturer),
CONSTRAINT fk_responsible_for_retailer FOREIGN KEY(tin) REFERENCES retailer(tin),
CONSTRAINT fk_responsible_for_category FOREIGN KEY(name_cat) REFERENCES category(name)
);
CREATE TABLE replenishment_event (
ean INT NOT NULL,
nro INT NOT NULL,
serial_number INT NOT NULL,
manufacturer VARCHAR(255) NOT NULL,
instant TIMESTAMP NOT NULL,
units INT NOT NULL,
tin INT NOT NULL,
CONSTRAINT pk_replenishment_event PRIMARY KEY(ean, nro, serial_number, manufacturer, instant),
CONSTRAINT fk_replenishment_event_planogram FOREIGN KEY(ean, nro, serial_number, manufacturer) REFERENCES planogram(ean, nro, serial_number, manufacturer),
CONSTRAINT fk_replenishment_event_retailer FOREIGN KEY(tin) REFERENCES retailer(tin)
);