-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdatabase_system.sql
256 lines (256 loc) · 6.75 KB
/
database_system.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
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
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
DROP TABLE IF EXISTS Organization_Donate_Card
DROP TABLE IF EXISTS Organization_Donate_Check
DROP TABLE IF EXISTS Organization_Donate
DROP TABLE IF EXISTS Organization_Donor
DROP TABLE IF EXISTS Sponsor
DROP TABLE IF EXISTS Business
DROP TABLE IF EXISTS Church
DROP TABLE IF EXISTS Affiliates
DROP TABLE IF EXISTS External_Organization
DROP TABLE IF EXISTS Donor_Donate_Card
DROP TABLE IF EXISTS Donor_Donate_Check
DROP TABLE IF EXISTS Donor_Donate
DROP TABLE IF EXISTS Donor
DROP TABLE IF EXISTS Reporting
DROP TABLE IF EXISTS Expenses
DROP TABLE IF EXISTS Leads
DROP TABLE IF EXISTS Employee
DROP TABLE IF EXISTS Volunteers
DROP TABLE IF EXISTS Serves
DROP TABLE IF EXISTS Cares
DROP TABLE IF EXISTS Team
DROP TABLE IF EXISTS Insurance_Policy
DROP TABLE IF EXISTS Needs
DROP TABLE IF EXISTS Client
DROP TABLE IF EXISTS Emergency
DROP TABLE IF EXISTS People
CREATE Table People(
SSN INT PRIMARY KEY NOT NULL
,
P_name VARCHAR
(64) ,
Dob INT
,
Race VARCHAR
(64),
Gender VARCHAR
(64) ,
Profession VARCHAR
(64) ,
M_address VARCHAR
(64) ,
E_address VARCHAR
(30) ,
H_number INT
,
W_number INT
,
C_number INT
,
On_list VARCHAR
(64
)
)
CREATE TABLE Emergency
(
SSN INT NOT NULL
,
E_name VARCHAR
(64),
Relationship VARCHAR
(64),
Email_address VARCHAR(30) NOT NULL,
Mailing_address VARCHAR(64) NOT NULL,
Home_number INT NOT NULL,
Work_number INT NOT NULL,
Cell_number INT NOT NULL,
PRIMARY KEY (SSN, Cell_number),
FOREIGN KEY (SSN) REFERENCES People(SSN) ON DELETE CASCADE
)
CREATE TABLE Client(
SSN INT PRIMARY KEY,
d_name VARCHAR(64),
d_phone INT,
a_name VARCHAR(64),
a_phone INT,
date_first_assigned INT,
FOREIGN KEY (SSN) REFERENCES People(SSN) ON DELETE CASCADE
)
CREATE TABLE Needs(
SSN INT,
needs VARCHAR(20),
importance_value INT,
PRIMARY KEY (SSN,Needs),
FOREIGN KEY (SSN) REFERENCES Client(SSN) ON DELETE CASCADE
)
CREATE TABLE Insurance_Policy(
SSN INT,
pol_id VARCHAR(64) PRIMARY KEY,
pro_id VARCHAR(64),
pro_address VARCHAR(64),
i_type VARCHAR(20),
FOREIGN KEY (SSN) REFERENCES Client(SSN) ON DELETE CASCADE,
)
CREATE TABLE Team(
t_name VARCHAR(64) PRIMARY KEY,
t_type VARCHAR(64),
date_formed VARCHAR(64),
)
CREATE TABLE Cares(
SSN INT,
t_name VARCHAR(64),
active VARCHAR(64),
PRIMARY KEY (SSN, t_name),
FOREIGN KEY (SSN) REFERENCES Client(SSN) ON DELETE CASCADE,
FOREIGN KEY (t_name) REFERENCES Team(t_name) ON DELETE CASCADE
)
CREATE TABLE Serves(
SSN INT,
t_name VARCHAR(64),
months INT,
hour INT,
active VARCHAR(64),
PRIMARY KEY (SSN, t_name, months),
FOREIGN KEY (SSN) REFERENCES People(SSN) ON DELETE CASCADE,
FOREIGN KEY (t_name) REFERENCES Team(t_name) ON DELETE CASCADE
)
CREATE TABLE Volunteers(
SSN INT PRIMARY KEY,
date_first_join INT,
date_recent_train INT,
location_recent_train VARCHAR(64),
FOREIGN KEY (SSN) REFERENCES People(SSN) ON DELETE CASCADE
)
CREATE TABLE Employee(
SSN INT PRIMARY KEY,
salary INT,
marital_Status VARCHAR(64),
hired_date INT,
FOREIGN KEY (SSN) REFERENCES People(SSN) ON DELETE CASCADE
)
CREATE TABLE Leads(
SSN INT PRIMARY KEY,
t_name VARCHAR(64),
FOREIGN KEY (SSN) REFERENCES People(SSN) ON DELETE CASCADE,
FOREIGN KEY (t_name) REFERENCES Team(t_name) ON DELETE CASCADE
)
CREATE TABLE Expenses(
SSN INT,
e_date INT,
amount INT,
e_description varchar(64),
PRIMARY KEY (SSN, e_date, amount, e_description),
FOREIGN KEY (SSN) REFERENCES Employee(SSN) ON DELETE CASCADE
)
CREATE TABLE Reporting(
SSN INT,
t_name VARCHAR(64),
r_date INT,
r_description VARCHAR(64),
PRIMARY KEY (t_name,SSN),
FOREIGN KEY (SSN) REFERENCES Employee(SSN) ON DELETE CASCADE,
FOREIGN KEY (t_name) REFERENCES Team(t_name) ON DELETE CASCADE
)
CREATE TABLE Donor(
SSN INT PRIMARY KEY,
anonymous VARCHAR(64),
FOREIGN KEY (SSN) REFERENCES People(SSN) ON DELETE CASCADE
)
CREATE TABLE Donor_Donate(
SSN INT,
d_date INT,
d_amount INT,
d_type VARCHAR(64),
d_campaign VARCHAR(64),
PRIMARY KEY (SSN, d_date ,d_amount),
FOREIGN KEY (SSN) REFERENCES Donor(SSN) ON DELETE CASCADE
)
CREATE TABLE Donor_Donate_Check(
SSN INT,
d_date INT,
d_amount INT,
d_check_num VARCHAR(64),
PRIMARY KEY (SSN,d_date, d_amount, d_check_num),
FOREIGN KEY (SSN, d_date, d_amount) REFERENCES Donor_Donate(SSN, d_date, d_amount) ON
DELETE CASCADE
)
CREATE TABLE Donor_Donate_Card(
SSN INT,
d_date INT,
d_amount INT,
d_card_num INT,
d_card_type VARCHAR(64),
d_card_exp INT,
PRIMARY KEY (SSN,d_date, d_amount, d_card_num),
FOREIGN KEY (SSN, d_date, d_amount) REFERENCES Donor_Donate(SSN, d_date, d_amount) ON
DELETE CASCADE
)
CREATE TABLE External_Organization(
org_name VARCHAR(64) PRIMARY KEY,
org_mailing VARCHAR(64),
org_phone INT,
contact_People VARCHAR(64)
)
CREATE TABLE Affiliates(
SSN INT PRIMARY KEY,
org_name VARCHAR(64),
FOREIGN KEY (SSN) REFERENCES People(SSN) ON DELETE CASCADE,
FOREIGN KEY(org_name) REFERENCES External_Organization(org_name) ON DELETE CASCADE
)
CREATE TABLE Church(
org_name VARCHAR(64) PRIMARY KEY,
religious_affiliation VARCHAR(64),
FOREIGN KEY (org_name) REFERENCES External_Organization(org_name) ON DELETE CASCADE
)
CREATE TABLE Business(
org_name VARCHAR(64) PRIMARY KEY,
b_type VARCHAR(64),
size VARCHAR(64),
website VARCHAR(64),
FOREIGN KEY (org_name) REFERENCES External_Organization(org_name) ON DELETE CASCADE
)
CREATE TABLE Sponsor(
org_name VARCHAR(64),
t_name VARCHAR(64),
PRIMARY KEY (org_name, t_name),
FOREIGN KEY (org_name) REFERENCES External_Organization(org_name) ON DELETE CASCADE,
FOREIGN KEY (t_name) REFERENCES Team(t_name) ON DELETE CASCADE
)
CREATE TABLE Organization_Donor(
org_name VARCHAR(64) PRIMARY KEY,
anonymous VARCHAR(64),
FOREIGN KEY (org_name) REFERENCES External_Organization(org_name) ON DELETE CASCADE
)
CREATE TABLE Organization_Donate(
org_name VARCHAR(64),
donate_date INT,
donate_amount INT,
donate_type VARCHAR(64),
donate_campaign varchar(64),
PRIMARY KEY (org_name, donate_date, donate_amount),
FOREIGN KEY (org_name) REFERENCES Organization_Donor(org_name) ON DELETE CASCADE
)
CREATE TABLE Organization_Donate_Check(
org_name VARCHAR(64),
donate_date INT,
donate_amount INT,
donate_check_num VARCHAR(64),
PRIMARY KEY(org_name, donate_date, donate_amount, donate_check_num),
FOREIGN KEY (org_name, donate_date, donate_amount) REFERENCES
Organization_Donate(org_name, donate_date, donate_amount) ON DELETE CASCADE
)
CREATE TABLE Organization_Donate_Card(
org_name VARCHAR(64),
donate_date INT,
donate_amount INT,
donate_card_num INT,
donate_card_type VARCHAR(64),
donate_card_exp INT,
PRIMARY KEY(org_name, donate_date, donate_amount, donate_card_num),
FOREIGN KEY (org_name, donate_date, donate_amount) REFERENCES
Organization_Donate(org_name, donate_date, donate_amount) ON DELETE CASCADE
)
CREATE INDEX expense_amount_index
ON Expenses(amount)
CREATE INDEX team_date_index
ON Team(date_formed)