-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathFAUResidencyDB.sql
322 lines (288 loc) · 12.4 KB
/
FAUResidencyDB.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
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
/*==============================================================*/
/* Database name: RESIDENCY */
/* DBMS name: MySQL 5.0 */
/* Created on: 12/6/2016 11:09:27 PM */
/*==============================================================*/
drop database if exists RESIDENCY;
/*==============================================================*/
/* Database: RESIDENCY */
/*==============================================================*/
create database RESIDENCY;
use RESIDENCY;
/*==============================================================*/
/* Table: CLAIMING_RESIDENCY */
/*==============================================================*/
create table CLAIMING_RESIDENCY
(
CLAIMING_RESIDENCY_SEQ int not null auto_increment,
SUPPORTING_DOCUMENTS_SEQ int,
DEMONSTRATE_RESIDENCY_SEQ int,
PERSON_NAME varchar(200),
CLAIMANT_RELATIONSHIP varchar(100),
ADDRESS varchar(200),
PHONE_NUMBER varchar(50),
RESIDENCY_DATE datetime,
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (CLAIMING_RESIDENCY_SEQ)
);
/*==============================================================*/
/* Table: DEMONSTRATE_RESIDENCY */
/*==============================================================*/
create table DEMONSTRATE_RESIDENCY
(
DEMONSTRATE_RESIDENCY_SEQ int not null auto_increment,
CODE varchar(20),
DESCRIPTION varchar(200),
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (DEMONSTRATE_RESIDENCY_SEQ)
);
/*==============================================================*/
/* Table: EVIDENCE_OF_RESIDENCY */
/*==============================================================*/
create table EVIDENCE_OF_RESIDENCY
(
EVIDENCE_OF_RESIDENCY_SEQ int not null auto_increment,
CODE varchar(20),
DESCRIPTION varchar(200),
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (EVIDENCE_OF_RESIDENCY_SEQ)
);
/*==============================================================*/
/* Table: NON_CITIZEN */
/*==============================================================*/
create table NON_CITIZEN
(
NON_CITIZEN_SEQ int not null auto_increment,
ALIEN_REGISTRATION_NUMBER numeric(10,0) not null,
ISSUE_DATE datetime,
VISA_CATEGORY varchar(20),
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (NON_CITIZEN_SEQ)
);
/*==============================================================*/
/* Table: NON_RESIDENT */
/*==============================================================*/
create table NON_RESIDENT
(
NON_RESIDENT_SEQ int not null auto_increment,
SIGNATURE varchar(200) not null,
DATE datetime not null,
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (NON_RESIDENT_SEQ)
);
/*==============================================================*/
/* Table: QUALIFICATION */
/*==============================================================*/
create table QUALIFICATION
(
QUALIFICATION_SEQ int not null auto_increment,
CODE varchar(20),
DESCRIPTION varchar(200),
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (QUALIFICATION_SEQ)
);
/*==============================================================*/
/* Table: QUALIFICATION_EXCEPTION */
/*==============================================================*/
create table QUALIFICATION_EXCEPTION
(
QUALIFICATION_SEQ int,
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime
);
/*==============================================================*/
/* Table: RESIDENCY_DECLARATION */
/*==============================================================*/
create table RESIDENCY_DECLARATION
(
RESIDENCY_DECLARATION_SEQ int not null auto_increment,
STUDENT_NAME varchar(200),
CLAIMANT_NAME varchar(200),
SIGNATURE varchar(200) not null,
DATE datetime not null,
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (RESIDENCY_DECLARATION_SEQ)
);
/*==============================================================*/
/* Table: STATUS */
/*==============================================================*/
create table STATUS
(
STATUS_SEQ int not null auto_increment,
CODE varchar(15),
DESCRIPTION varchar(200),
primary key (STATUS_SEQ)
);
/*==============================================================*/
/* Table: STUDENT */
/*==============================================================*/
create table STUDENT
(
STUDENT_SEQ int not null auto_increment,
STUDENT_TYPE_SEQ int,
NON_CITIZEN_SEQ int,
NON_RESIDENT_SEQ int,
STUDENT_NAME varchar(40),
Z_NUMBER numeric(8,0),
BIRTHDAY datetime,
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (STUDENT_SEQ)
);
/*==============================================================*/
/* Table: STUDENT_RESIDENCY */
/*==============================================================*/
create table STUDENT_RESIDENCY
(
STUDENT_RESIDENCY_SEQ int not null auto_increment,
CLAIMING_RESIDENCY_SEQ int,
STUDENT_SEQ int,
USER_SEQ int,
RESIDENCY_DECLARATION_SEQ int,
STATUS_SEQ int,
EVIDENCE_OF_RESIDENCY_SEQ int,
YEAR smallint,
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (STUDENT_RESIDENCY_SEQ)
);
/*==============================================================*/
/* Table: STUDENT_TYPE */
/*==============================================================*/
create table STUDENT_TYPE
(
STUDENT_TYPE_SEQ int not null auto_increment,
CODE varchar(20),
DESCRIPTION varchar(200),
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (STUDENT_TYPE_SEQ)
);
/*==============================================================*/
/* Table: SUPPORTING_DOCUMENTS */
/*==============================================================*/
create table SUPPORTING_DOCUMENTS
(
SUPPORTING_DOCUMENTS_SEQ int not null auto_increment,
VOTER_REGISTRATION varchar(50),
VOTER_ISSUE_DATE datetime,
DRIVERS_LICENSE varchar(50),
DRIVERS_ISSUE_DATE datetime,
DRIVERS_CURRENT_DATE datetime,
STATE_ID varchar(50),
STATE_ISSUE_DATE datetime,
STATE_CURRENT_DATE datetime,
VEHICLE_REGISTRATION varchar(50),
VEHICLE_ISSUE_DATE datetime,
VEHICLE_CURRENT_DATE datetime,
PERMANENT_HOME smallint,
HOMESTEAD_EXEMPTION smallint,
HIGHSCHOOL_OFFICIAL_TRANSCRIPT smallint,
HIGHSCHOOL_DATES_ATTENDED varchar(50),
HIGHSCHOOL_GRADUATION datetime,
FULLTIME_EMPLOYMENT smallint,
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (SUPPORTING_DOCUMENTS_SEQ)
);
/*==============================================================*/
/* Table: UPLOADED_DOCUMENTS */
/*==============================================================*/
create table UPLOADED_DOCUMENTS
(
UPLOADED_DOCUMENTS_SEQ numeric(10,0) not null,
FILE_PATH char(10),
primary key (UPLOADED_DOCUMENTS_SEQ)
);
/*==============================================================*/
/* Table: USER */
/*==============================================================*/
create table USER
(
USER_SEQ int not null auto_increment,
USER_ROLE_SEQ int,
FIRST_NAME varchar(50),
LAST_NAME varchar(50),
Z_NUMBER numeric(8,0) not null,
PASSWORD varchar(255) not null,
EMAIL_ADDRESS varchar(50),
ACTIVATED varchar(255),
PHONE_NUMBER varchar(25),
ADDRESS varchar(200),
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (USER_SEQ)
);
/*==============================================================*/
/* Table: USER_ROLE */
/*==============================================================*/
create table USER_ROLE
(
USER_ROLE_SEQ int not null auto_increment,
USER_ROLE_CODE varchar(20),
USER_ROLE_DESCRIPTION varchar(150),
USER_SEQ_ENT_BY numeric(10,0),
DT_ENT datetime,
USER_SEQ_CHG_BY numeric(10,0),
DT_CHG datetime,
primary key (USER_ROLE_SEQ)
);
alter table CLAIMING_RESIDENCY add constraint FK_REFERENCE_7 foreign key (SUPPORTING_DOCUMENTS_SEQ)
references SUPPORTING_DOCUMENTS (SUPPORTING_DOCUMENTS_SEQ) on delete restrict on update restrict;
alter table CLAIMING_RESIDENCY add constraint FK_REFERENCE_8 foreign key (DEMONSTRATE_RESIDENCY_SEQ)
references DEMONSTRATE_RESIDENCY (DEMONSTRATE_RESIDENCY_SEQ) on delete restrict on update restrict;
alter table QUALIFICATION_EXCEPTION add constraint FK_REFERENCE_16 foreign key (QUALIFICATION_SEQ)
references QUALIFICATION (QUALIFICATION_SEQ) on delete restrict on update restrict;
alter table STUDENT add constraint FK_REFERENCE_4 foreign key (STUDENT_TYPE_SEQ)
references STUDENT_TYPE (STUDENT_TYPE_SEQ) on delete restrict on update restrict;
alter table STUDENT add constraint FK_REFERENCE_5 foreign key (NON_CITIZEN_SEQ)
references NON_CITIZEN (NON_CITIZEN_SEQ) on delete restrict on update restrict;
alter table STUDENT add constraint FK_REFERENCE_6 foreign key (NON_RESIDENT_SEQ)
references NON_RESIDENT (NON_RESIDENT_SEQ) on delete restrict on update restrict;
alter table STUDENT_RESIDENCY add constraint FK_REFERENCE_11 foreign key (STUDENT_SEQ)
references STUDENT (STUDENT_SEQ) on delete restrict on update restrict;
alter table STUDENT_RESIDENCY add constraint FK_REFERENCE_13 foreign key (USER_SEQ)
references USER (USER_SEQ) on delete restrict on update restrict;
alter table STUDENT_RESIDENCY add constraint FK_REFERENCE_14 foreign key (RESIDENCY_DECLARATION_SEQ)
references RESIDENCY_DECLARATION (RESIDENCY_DECLARATION_SEQ) on delete restrict on update restrict;
alter table STUDENT_RESIDENCY add constraint FK_REFERENCE_15 foreign key (STATUS_SEQ)
references STATUS (STATUS_SEQ) on delete restrict on update restrict;
alter table STUDENT_RESIDENCY add constraint FK_REFERENCE_17 foreign key (EVIDENCE_OF_RESIDENCY_SEQ)
references EVIDENCE_OF_RESIDENCY (EVIDENCE_OF_RESIDENCY_SEQ) on delete restrict on update restrict;
alter table STUDENT_RESIDENCY add constraint FK_REFERENCE_9 foreign key (CLAIMING_RESIDENCY_SEQ)
references CLAIMING_RESIDENCY (CLAIMING_RESIDENCY_SEQ) on delete cascade on update restrict;
alter table USER add constraint FK_REFERENCE_12 foreign key (USER_ROLE_SEQ)
references USER_ROLE (USER_ROLE_SEQ) on delete restrict on update restrict;