-
Notifications
You must be signed in to change notification settings - Fork 0
/
Products ER.sql
128 lines (116 loc) · 5.94 KB
/
Products ER.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
CREATE TABLE "ADDITIONAL_OPTION" (
"PRODUCT_VARIATION_ID" NUMBER(6) NOT NULL,
"SUBCATEGORY_FILTER_ID" NUMBER(6) NOT NULL,
PRIMARY KEY ("PRODUCT_VARIATION_ID", "SUBCATEGORY_FILTER_ID")
);
CREATE TABLE "CATEGORY" (
"CATEGORY_ID" NUMBER(6) NOT NULL,
"DESCRIPTION" VARCHAR2(60) NOT NULL,
CONSTRAINT "CATEGORIES_PK" PRIMARY KEY ("CATEGORY_ID")
);
CREATE TABLE "FILTER" (
"FILTER_ID" NUMBER(6) NOT NULL,
"FILTER_NAME" VARCHAR2(40) NOT NULL,
"FILTER_VALUE" VARCHAR2(40) NOT NULL,
PRIMARY KEY ("FILTER_ID")
);
CREATE TABLE "PRODUCT" (
"PRODUCT_ID" NUMBER(6) NOT NULL,
"TITLE" VARCHAR2(60) NOT NULL,
"DESCRIPTION" VARCHAR2(255),
"SUBCATEGORY_ID" NUMBER(6) NOT NULL,
PRIMARY KEY ("PRODUCT_ID")
);
CREATE TABLE "PRODUCT_VARIATION" (
"PRODUCT_VARIATION_ID" NUMBER(6) NOT NULL,
"PRICE" NUMBER(6,2) NOT NULL,
"DISCOUNTED_PRICE" NUMBER(6,2),
"PRODUCT_ID" NUMBER(6) NOT NULL,
"SUBCATEGORY_FILTER_ID" NUMBER(6) NOT NULL,
PRIMARY KEY ("PRODUCT_VARIATION_ID")
);
CREATE TABLE "SUBCATEGORY" (
"SUBCATEGORY_ID" NUMBER(6) NOT NULL,
"DESCRIPTION" VARCHAR2(80) NOT NULL,
"CATEGORY_ID" NUMBER(6) NOT NULL,
PRIMARY KEY ("SUBCATEGORY_ID")
);
CREATE TABLE "SUBCATEGORY_FILTER" (
"SUBCATEGORY_FILTER_ID" NUMBER(6) NOT NULL,
"SUBCATEGORY_ID" NUMBER(6) NOT NULL,
"FILTER_ID" NUMBER(6) NOT NULL,
PRIMARY KEY ("SUBCATEGORY_FILTER_ID")
);
ALTER TABLE "ADDITIONAL_OPTION" ADD CONSTRAINT "PRVA_ADOP_FK" FOREIGN KEY ("PRODUCT_VARIATION_ID") REFERENCES "PRODUCT_VARIATION" ("PRODUCT_VARIATION_ID");
ALTER TABLE "ADDITIONAL_OPTION" ADD CONSTRAINT "FK_ADDITIONAL_OPTION_ADDITIONAL_OPTION_1" FOREIGN KEY ("SUBCATEGORY_FILTER_ID") REFERENCES "SUBCATEGORY_FILTER" ("SUBCATEGORY_FILTER_ID");
ALTER TABLE "PRODUCT" ADD CONSTRAINT "SUBC_PROD_FK" FOREIGN KEY ("SUBCATEGORY_ID") REFERENCES "SUBCATEGORY" ("SUBCATEGORY_ID");
ALTER TABLE "PRODUCT_VARIATION" ADD CONSTRAINT "PROD_PRVA_FK" FOREIGN KEY ("PRODUCT_ID") REFERENCES "PRODUCT" ("PRODUCT_ID");
ALTER TABLE "PRODUCT_VARIATION" ADD CONSTRAINT "FK_PRODUCT_VARIATION_PRODUCT_VARIATION_1" FOREIGN KEY ("SUBCATEGORY_FILTER_ID") REFERENCES "SUBCATEGORY_FILTER" ("SUBCATEGORY_FILTER_ID");
ALTER TABLE "SUBCATEGORY" ADD CONSTRAINT "FK_SUBCATEGORY_SUBCATEGORY_1" FOREIGN KEY ("CATEGORY_ID") REFERENCES "CATEGORY" ("CATEGORY_ID");
ALTER TABLE "SUBCATEGORY_FILTER" ADD CONSTRAINT "SUBC_SUFI_FK" FOREIGN KEY ("SUBCATEGORY_ID") REFERENCES "SUBCATEGORY" ("SUBCATEGORY_ID");
ALTER TABLE "SUBCATEGORY_FILTER" ADD CONSTRAINT "FK_SUBCATEGORY_FILTER_SUBCATEGORY_FILTER_1" FOREIGN KEY ("FILTER_ID") REFERENCES "FILTER" ("FILTER_ID");
--Scenario 1: Create a New Category
INSERT INTO CATEGORY VALUES (1, 'Masculino');
INSERT INTO CATEGORY VALUES (2, 'Feminino');
--Scenario 2: Create a New Subcategory
INSERT INTO SUBCATEGORY VALUES (1, 'Tênis', 1);
INSERT INTO SUBCATEGORY VALUES (2, 'Tênis', 2);
INSERT INTO FILTER VALUES (1, 'Cor', 'Preto');
INSERT INTO FILTER VALUES (2, 'Cor', 'Azul');
INSERT INTO FILTER VALUES (3, 'Tamanho', '40');
INSERT INTO FILTER VALUES (4, 'Tamanho', '42');
INSERT INTO FILTER VALUES (5, 'Material', 'Couro');
INSERT INTO FILTER VALUES (6, 'Material', 'Tecido');
INSERT INTO SUBCATEGORY_FILTER VALUES (1, 1, 1);
INSERT INTO SUBCATEGORY_FILTER VALUES (2, 1, 2);
INSERT INTO SUBCATEGORY_FILTER VALUES (3, 1, 3);
INSERT INTO SUBCATEGORY_FILTER VALUES (4, 1, 4);
INSERT INTO SUBCATEGORY_FILTER VALUES (5, 1, 5);
INSERT INTO SUBCATEGORY_FILTER VALUES (6, 1, 6);
INSERT INTO SUBCATEGORY_FILTER VALUES (7, 2, 1);
INSERT INTO SUBCATEGORY_FILTER VALUES (8, 2, 2);
INSERT INTO SUBCATEGORY_FILTER VALUES (9, 2, 3);
INSERT INTO SUBCATEGORY_FILTER VALUES (10, 2, 4);
INSERT INTO SUBCATEGORY_FILTER VALUES (11, 2, 5);
INSERT INTO SUBCATEGORY_FILTER VALUES (12, 2, 6);
--Scenario 3: Create a New Product
INSERT INTO PRODUCT VALUES (1, 'Tênis Nike Masculino', 'Pisante naskiera pros boys', 1);
INSERT INTO PRODUCT VALUES (2, 'Tênis Nike Feminino', 'Pisante naskiera pras minas', 2);
INSERT INTO PRODUCT_VARIATION VALUES (1, 120.00, NULL, 1, 1); --Tênis masc. preto
INSERT INTO PRODUCT_VARIATION VALUES (2, 125.00, NULL, 1, 2); --Tênis masc. azul
INSERT INTO PRODUCT_VARIATION VALUES (3, 110.00, NULL, 2, 7); --Tênis fem. preto
INSERT INTO PRODUCT_VARIATION VALUES (4, 115.00, NULL, 2, 8); --Tênis fem. azul
--Tênis preto masc
INSERT INTO ADDITIONAL_OPTION VALUES (1, 3);
INSERT INTO ADDITIONAL_OPTION VALUES (1, 4);
INSERT INTO ADDITIONAL_OPTION VALUES (1, 5);
INSERT INTO ADDITIONAL_OPTION VALUES (1, 6);
--Tênis azul masc
INSERT INTO ADDITIONAL_OPTION VALUES (2, 3);
INSERT INTO ADDITIONAL_OPTION VALUES (2, 4);
INSERT INTO ADDITIONAL_OPTION VALUES (2, 5);
INSERT INTO ADDITIONAL_OPTION VALUES (2, 6);
--Tênis preto fem
INSERT INTO ADDITIONAL_OPTION VALUES (3, 3);
INSERT INTO ADDITIONAL_OPTION VALUES (3, 4);
INSERT INTO ADDITIONAL_OPTION VALUES (3, 5);
INSERT INTO ADDITIONAL_OPTION VALUES (3, 6);
--Tênis azul fem
INSERT INTO ADDITIONAL_OPTION VALUES (4, 3); --tamanho 40
INSERT INTO ADDITIONAL_OPTION VALUES (4, 4); --tamanho 42
INSERT INTO ADDITIONAL_OPTION VALUES (4, 5); --couro
INSERT INTO ADDITIONAL_OPTION VALUES (4, 6); --tecido
--Scenario 4: View Product Characteristics
SELECT UNIQUE P.TITLE AS PRODUTO, C.DESCRIPTION AS CATEGORIA, S.DESCRIPTION AS SUBCATEGORIA,
F.FILTER_NAME, F.FILTER_VALUE, PV.PRICE, F2.FILTER_NAME FILTROS_ADICIONAIS, F2.FILTER_VALUE
FROM PRODUCT P
JOIN SUBCATEGORY S ON S.SUBCATEGORY_ID = P.SUBCATEGORY_ID
JOIN CATEGORY C ON C.CATEGORY_ID = S.CATEGORY_ID
JOIN PRODUCT_VARIATION PV ON PV.PRODUCT_ID = P.PRODUCT_ID
JOIN ADDITIONAL_OPTION AO ON AO.PRODUCT_VARIATION_ID = PV.PRODUCT_VARIATION_ID
JOIN SUBCATEGORY_FILTER SF ON SF.SUBCATEGORY_FILTER_ID = PV.SUBCATEGORY_FILTER_ID --essa linha busca os atributos que definem o valor
JOIN SUBCATEGORY_FILTER SF2 ON SF2.SUBCATEGORY_FILTER_ID = AO.SUBCATEGORY_FILTER_ID --essa busca os adicionais
JOIN FILTER F ON F.FILTER_ID = SF.FILTER_ID
JOIN FILTER F2 ON F2.FILTER_ID = SF2.FILTER_ID
WHERE P.SUBCATEGORY_ID = 1 -- tênis masculinos
ORDER BY F.FILTER_VALUE, FILTROS_ADICIONAIS