CS336/create_db.sql at master · chrisn731/CS336 · 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
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
DROP DATABASE IF EXISTS dbproject;
CREATE DATABASE dbproject;
USE dbproject;

CREATE TABLE users(
	username VARCHAR(30) PRIMARY KEY,
	password VARCHAR(30)
);

CREATE TABLE listings(
	l_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
	itemname VARCHAR(30),
	subcategory VARCHAR(30),
	subattribute VARCHAR(30),
	price DECIMAL(10,2),
	minsale DECIMAL(10,2),
	dt datetime,
	closed int DEFAULT 0
);

#USER POSTS LISTING
CREATE TABLE posts(
	l_id int PRIMARY KEY,
	username VARCHAR(30),
	FOREIGN KEY(username)
		REFERENCES users(username)
			ON DELETE CASCADE
			ON UPDATE CASCADE,
	FOREIGN KEY(l_id)
		REFERENCES listings(l_id)
			ON DELETE CASCADE
);

CREATE TABLE bids(
	b_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
	price DECIMAL(10,2),
	dtime datetime
);

CREATE TABLE auto_bids(
	u_id VARCHAR(30),
	l_id INT,
	increment DECIMAL(10,2),
	b_limit DECIMAL(10,2),
	current_price DECIMAL(10,2),
	PRIMARY KEY(u_id, l_id),
	FOREIGN KEY(u_id)
		REFERENCES users(username)
			ON DELETE CASCADE
			ON UPDATE CASCADE,
	FOREIGN KEY(l_id)
		REFERENCES listings(l_id)
			ON DELETE CASCADE
			ON UPDATE CASCADE
);

#Bids on listings
CREATE TABLE bidson(
	b_id int PRIMARY KEY,
	l_id int,
	FOREIGN KEY(l_id)
		REFERENCES listings(l_id)
			ON DELETE CASCADE,
	FOREIGN KEY(b_id)
		REFERENCES bids(b_id)
			ON DELETE CASCADE
);

#connects buyers to bid ids
CREATE TABLE places(
	b_id int PRIMARY KEY,
	username VARCHAR(30),
	FOREIGN KEY(b_id)
		REFERENCES bids(b_id)
			ON DELETE CASCADE
			ON UPDATE CASCADE,
	FOREIGN KEY(username)
		REFERENCES users(username)
			ON DELETE CASCADE
			ON UPDATE CASCADE
);

CREATE TABLE admin(
	id INT PRIMARY KEY,
	password VARCHAR(30)
);
INSERT INTO admin VALUES(0, "adminpass");

CREATE TABLE customer_rep(
	id INT PRIMARY KEY,
	password VARCHAR(30)
);

#creates customer rep
CREATE TABLE admin_creates(
	aid INT,
    cr_id INT,
    FOREIGN KEY(aid) REFERENCES admin(id),
    FOREIGN KEY(cr_id) REFERENCES customer_rep(id)
);

CREATE TABLE interests(
	username VARCHAR(30),
	interest VARCHAR(30),
	PRIMARY KEY(username, interest),
	FOREIGN KEY(username)
		REFERENCES users(username)
			ON DELETE CASCADE
			ON UPDATE CASCADE
);

CREATE TABLE question(
	q_id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
	q_text VARCHAR(300)
);

#user asks question
CREATE TABLE asks(
	asker VARCHAR(30),
	q_id INT PRIMARY KEY,
	FOREIGN KEY(asker)
		REFERENCES users(username)
			ON DELETE CASCADE
			ON UPDATE CASCADE,
	FOREIGN KEY(q_id) REFERENCES question(q_id)
);

#rep resolves question
CREATE TABLE resolves(
	q_id INT PRIMARY KEY,
	resolver INT,
	resolve_text VARCHAR(300),
	FOREIGN KEY(q_id) REFERENCES question(q_id),
	FOREIGN KEY(resolver) REFERENCES customer_rep(id)
);

CREATE TABLE sales(
	s_id int PRIMARY KEY NOT NULL AUTO_INCREMENT,
	dtime datetime,
	amount DECIMAL(10,2)
);

#sold listing generates sale
CREATE TABLE generates(
	s_id int PRIMARY KEY,
	l_id int,
	FOREIGN KEY(l_id)
		REFERENCES listings(l_id)
			ON DELETE CASCADE,
	FOREIGN KEY(s_id)
		REFERENCES sales(s_id)
			ON DELETE CASCADE
);