Online-Shopping/dbms_shop.sql at master · riak16/Online-Shopping · GitHub

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

CREATE TABLE user(
	userId int NOT NULL,
	password varchar(50) NOT NULL,
	PRIMARY KEY (userId)
);

CREATE TABLE customer(
	firstName varchar(50) NOT NULL,
	lastName varchar(50) NOT NULL,
	email varchar(50) NOT NULL,
	address varchar(50) NOT NULL
);

CREATE TABLE product (
	productId int NOT NULL,
	discount int NOT NULL,
	price int NOT NULL,
	image varchar(50) NOT NULL,
	productName varchar(50) NOT NULL,
	description varchar(50) NOT NULL,
	PRIMARY KEY (productId)
);

CREATE TABLE bankInformation(
	bankNmae varchar(50) NOT NULL,
	AccountNo varchar(50) NOT NULL,
	amt int NOT NULL,
	accountHolderName varchar(50) NOT NULL
);

CREATE TABLE orderTable (
	orderId int NOT NULL,
	shipping_info varchar(50) NOT NULL,
	deliveryDate varchar(50) NOT NULL,
	PRIMARY KEY (orderId)
);

CREATE TABLE views (
	recommendation varchar(50) NOT NULL,
	customerId int NOT NULL,
	productId int NOT NULL,
	FOREIGN KEY (customerId) REFERENCES user(userId) on delete cascade on update cascade,
	FOREIGN KEY (productId) REFERENCES product(productId) on delete cascade on update cascade,
	PRIMARY KEY (customerId, productId)
);


CREATE TABLE history(
	productId int NOT NULL,
	FOREIGN KEY (productId) REFERENCES product(productId) on delete cascade on update cascade,
 	PRIMARY KEY (productId)
);


CREATE TABLE shoppingCart(
	customerId int NOT NULL,
	orderId int NOT NULL,
	quantity int NOT NUlL,
	totalCost int NOT NUlL,
	FOREIGN KEY (customerId) REFERENCES user(userId) on delete cascade on update cascade,
	FOREIGN KEY (orderId) REFERENCES orderTable(orderId) on delete cascade on update cascade,
	PRIMARY KEY (customerId, orderId)
);

CREATE TABLE men(
	productId int NOT NULL,
	size varchar(25) NOT NULL,
	brand varchar(25) NOT NULL,
	FOREIGN KEY (productId) REFERENCES product(productId) on delete cascade on update cascade,
 	PRIMARY KEY (productId)
);

CREATE TABLE women(
	productId int NOT NULL,
	size varchar(25) NOT NULL,
	brand varchar(25) NOT NULL,
	FOREIGN KEY (productId) REFERENCES product(productId) on delete cascade on update cascade,
 	PRIMARY KEY (productId)
);

CREATE TABLE children(
	productId int NOT NULL,
	size varchar(25) NOT NULL,
	brand varchar(25) NOT NULL,
	FOREIGN KEY (productId) REFERENCES product(productId) on delete cascade on update cascade,
 	PRIMARY KEY (productId)
);