-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathsupermarketsChain.sql
More file actions
184 lines (175 loc) · 11.9 KB
/
supermarketsChain.sql
File metadata and controls
184 lines (175 loc) · 11.9 KB
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
--------------------------------------------------------
-- File created - Íåäåëÿ-Ìàðò-15-2015
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Table MEASURES
--------------------------------------------------------
CREATE TABLE "SUPERMARKETSCHAIN"."MEASURES"
( "ID" NUMBER,
"NAME" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- DDL for Table PRODUCTS
--------------------------------------------------------
CREATE TABLE "SUPERMARKETSCHAIN"."PRODUCTS"
( "ID" NUMBER,
"VENDORID" NUMBER,
"NAME" VARCHAR2(20 BYTE),
"MEASUREID" NUMBER,
"PRICE" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- DDL for Table VENDORS
--------------------------------------------------------
CREATE TABLE "SUPERMARKETSCHAIN"."VENDORS"
( "ID" NUMBER,
"NAME" VARCHAR2(20 BYTE)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
REM INSERTING into SUPERMARKETSCHAIN.MEASURES
SET DEFINE OFF;
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (1,'cubic centimeter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (2,'cubic decimeter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (3,'cubic meter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (4,'milliliter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (5,'centiliter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (6,'deciliter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (7,'liter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (8,'dekaliter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (9,'hectoliter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (10,'milligram');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (11,'centigram');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (12,'decigram');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (13,'gram');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (14,'dekagram');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (15,'hectogram');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (16,'kilogram');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (17,'metric ton');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (18,'millimeter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (19,'centimeter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (20,'decimeter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (21,'meter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (22,'dekameter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (23,'kilometer');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (24,'square centimeter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (25,'square decimeter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (26,'square meter');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (27,'are');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (28,'hectare');
Insert into SUPERMARKETSCHAIN.MEASURES (ID,NAME) values (29,'square kilometer');
REM INSERTING into SUPERMARKETSCHAIN.PRODUCTS
SET DEFINE OFF;
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (6,1,'Lay''s potato chips',13,2.1);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (7,1,'Doritos tortilla',13,2.3);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (8,3,'Cheerios',13,1.6);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (9,3,'Chocapic',13,1.8);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (1,1,'Pepsi',4,1.5);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (2,1,'Mountain Dew',4,1.7);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (3,1,'Diet Pepsi',4,1.3);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (4,1,'7 Up',4,2);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (5,1,'Cheetos',13,1.4);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (10,3,'Cookie Crisp',13,1.2);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (11,3,'Crunch',13,2.2);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (12,3,'Golden Grahams',13,2.4);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (13,3,'Golden Nuggets',13,2.5);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (14,3,'Koko Krunch',13,2.3);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (15,3,'Koko Krunch',13,1.7);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (16,3,'Nesquik Cereal',13,2.2);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (17,3,'Acti-V',4,1.1);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (18,3,'ActiPlus',4,1.3);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (19,3,'Bonka',4,1);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (20,3,'Nescaf?',4,0.5);
Insert into SUPERMARKETSCHAIN.PRODUCTS (ID,VENDORID,NAME,MEASUREID,PRICE) values (21,3,'Aberfoyle',4,0.7);
REM INSERTING into SUPERMARKETSCHAIN.VENDORS
SET DEFINE OFF;
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (1,'Pepsico Inc.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (2,'Tyson Foods Inc.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (3,'Nestle');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (4,'JBS');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (5,'Coca-Cola Co.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (6,'Anheuser-Busch InBev');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (7,'Kraft Foods Inc.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (8,'Smithfield Foods');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (9,'General Mills Inc.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (10,'ConAgra Foods Inc.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (11,'Mars Inc.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (12,'Kellogg Co.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (13,'Dean Foods Co.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (14,'Hormel Foods Corp.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (15,'Cargill Inc.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (16,'MillerCoors LLC');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (17,'Saputo Inc.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (18,'Pilgrim''s Pride');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (19,'Hershey Co.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (24,'E&J Gallo Winery');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (20,'H.J. Heinz Co.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (21,'Maple Leaf Foods');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (22,'Brown-Forman Corp.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (23,'Flowers Foods Inc.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (25,'Parmalat Canada');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (26,'Dole Food Co. Inc.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (27,'Sanderson Farms');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (28,'Rich Products Corp.');
Insert into SUPERMARKETSCHAIN.VENDORS (ID,NAME) values (29,'Beam Inc.');
--------------------------------------------------------
-- DDL for Index MEASURES_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "SUPERMARKETSCHAIN"."MEASURES_PK" ON "SUPERMARKETSCHAIN"."MEASURES" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- DDL for Index VENDORS_PK
--------------------------------------------------------
CREATE UNIQUE INDEX "SUPERMARKETSCHAIN"."VENDORS_PK" ON "SUPERMARKETSCHAIN"."VENDORS" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table VENDORS
--------------------------------------------------------
ALTER TABLE "SUPERMARKETSCHAIN"."VENDORS" ADD CONSTRAINT "VENDORS_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "SUPERMARKETSCHAIN"."VENDORS" MODIFY ("NAME" NOT NULL ENABLE);
ALTER TABLE "SUPERMARKETSCHAIN"."VENDORS" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table MEASURES
--------------------------------------------------------
ALTER TABLE "SUPERMARKETSCHAIN"."MEASURES" ADD CONSTRAINT "MEASURES_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "SUPERMARKETSCHAIN"."MEASURES" MODIFY ("NAME" NOT NULL ENABLE);
ALTER TABLE "SUPERMARKETSCHAIN"."MEASURES" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Constraints for Table PRODUCTS
--------------------------------------------------------
ALTER TABLE "SUPERMARKETSCHAIN"."PRODUCTS" MODIFY ("PRICE" NOT NULL ENABLE);
ALTER TABLE "SUPERMARKETSCHAIN"."PRODUCTS" MODIFY ("MEASUREID" NOT NULL ENABLE);
ALTER TABLE "SUPERMARKETSCHAIN"."PRODUCTS" MODIFY ("NAME" NOT NULL ENABLE);
ALTER TABLE "SUPERMARKETSCHAIN"."PRODUCTS" MODIFY ("VENDORID" NOT NULL ENABLE);
ALTER TABLE "SUPERMARKETSCHAIN"."PRODUCTS" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Ref Constraints for Table PRODUCTS
--------------------------------------------------------
ALTER TABLE "SUPERMARKETSCHAIN"."PRODUCTS" ADD CONSTRAINT "PRODUCTS_MEASURES_FK" FOREIGN KEY ("MEASUREID")
REFERENCES "SUPERMARKETSCHAIN"."MEASURES" ("ID") ENABLE;
ALTER TABLE "SUPERMARKETSCHAIN"."PRODUCTS" ADD CONSTRAINT "PRODUCTS_VENDORS_FK" FOREIGN KEY ("VENDORID")
REFERENCES "SUPERMARKETSCHAIN"."VENDORS" ("ID") ENABLE;