-
Notifications
You must be signed in to change notification settings - Fork 0
/
DB.sql
76 lines (67 loc) · 2.04 KB
/
DB.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
drop table S3T_Supply;
drop table S3T_OrderItem;
drop table S3T_Transaction;
drop table S3T_Staff;
drop table S3T_Customer;
drop table S3T_Product;
drop table S3T_Supplier;
create table S3T_Customer (
id char(11),
point int default 0 not null,
cash number default 0 not null,
PRIMARY KEY (id)
);
create table S3T_Staff (
id char(11),
type int not null,
PRIMARY KEY (id)
);
create table S3T_Supplier(
id char(10),
e_mail varchar(200),
PRIMARY KEY (id)
);
create table S3T_Product (
barcode char(10),
name varchar(20),
price number not null,
promotion int default 0 not null,
discount int default 0 not null,
stockLv int default 0 not null,
replenishLv int default 0 not null,
supplier_id char(10) not null,
PRIMARY KEY (barcode),
FOREIGN KEY (supplier_id) REFERENCES S3T_Supplier(id)
);
create table S3T_Transaction (
id char(10),
cost number default 0 not null,
trans_date date not null,
cust_id char(11) not null,
PRIMARY KEY (id),
FOREIGN KEY (cust_id) REFERENCES S3T_Customer(id)
);
create table S3T_OrderItem(
trans_id char(10) not null,
prod_barcode char(10) not null,
quantity number not null,
PRIMARY KEY (prod_barcode, trans_id),
FOREIGN KEY (trans_id) REFERENCES S3T_Transaction(id),
FOREIGN KEY (prod_barcode) REFERENCES S3T_Product(barcode)
);
create table S3T_Supply(
supplier_id char(10),
prod_barcode char(10),
quantity number not null,
supply_date date not null,
PRIMARY KEY (prod_barcode, supplier_id),
FOREIGN KEY (supplier_id) REFERENCES S3T_Supplier(id),
FOREIGN KEY (prod_barcode) REFERENCES S3T_Product(barcode)
);
INSERT INTO S3T_Customer values ('c0000000001', 21, 200);
INSERT INTO S3T_Staff values ('s0000000001', 2);
INSERT INTO S3T_Staff values ('s0000000002', 1);
INSERT INTO S3T_Staff values ('s0000000003', 0);
INSERT INTO S3T_Supplier values('0000000001', '[email protected]');
INSERT INTO S3T_Product values('0000000001', 'Product_0000000001', 10, 1, 10, 100, 10, '0000000001');
INSERT INTO S3T_Product values('1234567890', 'Product_1234567890', 20, 2, 20, 200, 20, '0000000001');