· 5 years ago · Nov 24, 2020, 01:26 PM
1/*==============================================================*/
2/* DBMS name: MySQL 5.0 */
3/* Created on: 11/24/2020 7:58:41 PM */
4/*==============================================================*/
5
6
7alter table PRODUCT
8 drop foreign key FK_PRODUCT_BELONGSTO_CATEGORY;
9
10alter table PRODUCT
11 drop foreign key FK_PRODUCT_SUPLLIED__VENDOR;
12
13alter table SALESTRANSACTION
14 drop foreign key FK_SALESTRA_BUYSVIA_CUSTOMER;
15
16alter table SALESTRANSACTION
17 drop foreign key FK_SALESTRA_OCCUREDAT_STORE;
18
19alter table SOLDVIA
20 drop foreign key FK_SOLDVIA_SOLDVIA_PRODUCT;
21
22alter table SOLDVIA
23 drop foreign key FK_SOLDVIA_SOLDVIA2_SALESTRA;
24
25alter table STORE
26 drop foreign key FK_STORE_LOCATEDIN_REGION;
27
28drop table if exists CATEGORY;
29
30drop table if exists CUSTOMER;
31
32
33alter table PRODUCT
34 drop foreign key FK_PRODUCT_SUPLLIED__VENDOR;
35
36alter table PRODUCT
37 drop foreign key FK_PRODUCT_BELONGSTO_CATEGORY;
38
39drop table if exists PRODUCT;
40
41drop table if exists REGION;
42
43
44alter table SALESTRANSACTION
45 drop foreign key FK_SALESTRA_BUYSVIA_CUSTOMER;
46
47alter table SALESTRANSACTION
48 drop foreign key FK_SALESTRA_OCCUREDAT_STORE;
49
50drop table if exists SALESTRANSACTION;
51
52
53alter table SOLDVIA
54 drop foreign key FK_SOLDVIA_SOLDVIA_PRODUCT;
55
56alter table SOLDVIA
57 drop foreign key FK_SOLDVIA_SOLDVIA2_SALESTRA;
58
59drop table if exists SOLDVIA;
60
61
62alter table STORE
63 drop foreign key FK_STORE_LOCATEDIN_REGION;
64
65drop table if exists STORE;
66
67drop table if exists VENDOR;
68
69/*==============================================================*/
70/* Table: CATEGORY */
71/*==============================================================*/
72create table CATEGORY
73(
74 CATEGORYID int not null comment '',
75 CATEGORYNAME varchar(100) comment '',
76 primary key (CATEGORYID)
77);
78
79/*==============================================================*/
80/* Table: CUSTOMER */
81/*==============================================================*/
82create table CUSTOMER
83(
84 CUSTOMERID int not null comment '',
85 COSTUMERNAME varchar(100) comment '',
86 CUSTOMERZIP char(50) comment '',
87 primary key (CUSTOMERID)
88);
89
90/*==============================================================*/
91/* Table: PRODUCT */
92/*==============================================================*/
93create table PRODUCT
94(
95 VENDORID int not null comment '',
96 CATEGORYID int not null comment '',
97 PRODUCTID int not null comment '',
98 PRODUCTPRICE char(50) comment '',
99 PRODUCTNAME varchar(100) comment '',
100 primary key (VENDORID, CATEGORYID, PRODUCTID)
101);
102
103/*==============================================================*/
104/* Table: REGION */
105/*==============================================================*/
106create table REGION
107(
108 REGIONID int not null comment '',
109 REGIONNAME varchar(50) comment '',
110 primary key (REGIONID)
111);
112
113/*==============================================================*/
114/* Table: SALESTRANSACTION */
115/*==============================================================*/
116create table SALESTRANSACTION
117(
118 TID int not null comment '',
119 STOREID int comment '',
120 CUSTOMERID int comment '',
121 TDATE date comment '',
122 primary key (TID)
123);
124
125/*==============================================================*/
126/* Table: SOLDVIA */
127/*==============================================================*/
128create table SOLDVIA
129(
130 VENDORID int not null comment '',
131 CATEGORYID int not null comment '',
132 PRODUCTID int not null comment '',
133 TID int not null comment '',
134 primary key (VENDORID, CATEGORYID, PRODUCTID, TID)
135);
136
137/*==============================================================*/
138/* Table: STORE */
139/*==============================================================*/
140create table STORE
141(
142 STOREID int not null comment '',
143 REGIONID int comment '',
144 STOREZIP char(10) comment '',
145 primary key (STOREID)
146);
147
148/*==============================================================*/
149/* Table: VENDOR */
150/*==============================================================*/
151create table VENDOR
152(
153 VENDORID int not null comment '',
154 VENDORNAME varchar(100) comment '',
155 primary key (VENDORID)
156);
157
158alter table PRODUCT add constraint FK_PRODUCT_BELONGSTO_CATEGORY foreign key (CATEGORYID)
159 references CATEGORY (CATEGORYID) on delete restrict on update restrict;
160
161alter table PRODUCT add constraint FK_PRODUCT_SUPLLIED__VENDOR foreign key (VENDORID)
162 references VENDOR (VENDORID) on delete restrict on update restrict;
163
164alter table SALESTRANSACTION add constraint FK_SALESTRA_BUYSVIA_CUSTOMER foreign key (CUSTOMERID)
165 references CUSTOMER (CUSTOMERID) on delete restrict on update restrict;
166
167alter table SALESTRANSACTION add constraint FK_SALESTRA_OCCUREDAT_STORE foreign key (STOREID)
168 references STORE (STOREID) on delete restrict on update restrict;
169
170alter table SOLDVIA add constraint FK_SOLDVIA_SOLDVIA_PRODUCT foreign key (VENDORID, CATEGORYID, PRODUCTID)
171 references PRODUCT (VENDORID, CATEGORYID, PRODUCTID) on delete restrict on update restrict;
172
173alter table SOLDVIA add constraint FK_SOLDVIA_SOLDVIA2_SALESTRA foreign key (TID)
174 references SALESTRANSACTION (TID) on delete restrict on update restrict;
175
176alter table STORE add constraint FK_STORE_LOCATEDIN_REGION foreign key (REGIONID)
177 references REGION (REGIONID) on delete restrict on update restrict;
178
179