Friend/ERD_CREATE.sql at main · robin12303/Friend · 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
CREATE TABLE ANSWER (
	A_NUMBER	NUMBER	NOT NULL,
	B_NUMBER	NUMBER	NOT NULL,
	A_WRITER	VARCHAR2(15)	NULL,
	A_COMMENT_DATE	DATE	NULL,
	A_CONTENT	VARCHAR2(2000)	NULL
);

CREATE TABLE BILL (
	FEE_NUMBER	NUMBER	NOT NULL,
	DETAILED_NUMBER	NUMBER	NOT NULL,
	NAME	VARCHAR2(10)	NULL,
	BILLYEAR_MONTH	NUMBER	NULL,
	CALCUL_START_DATE	DATE	NULL,
	CALCUL_END_DATE	DATE	NULL,
	CUTOFF_DATE	DATE	NULL,
	IMPOSE_STATUS	VARCHAR2(10)	NULL,
	IMPOSE_FINISH	CHAR(1)	NULL,
	ARREARS	NUMBER	NULL,
	ARREARS_FINE	NUMBER	NULL,
	BEFORE_AMOUNT	NUMBER	NULL,
	AFTER_AMOUNT	NUMBER	NULL,
	VBANK	VARCHAR2(20)	NULL,
	PAYMENT_METHOD_ID	VARCHAR2(10)	NULL,
	AMOUNT	NUMBER	NOT NULL,
	LATE_FEE	NUMBER	NULL,
	ADDRESS	VARCHAR2(20)	NULL
);
CREATE TABLE CARD(
 PAYMENT_METHOD_ID VARCHAR2(20) NOT NULL,
 ID VARCHAR2(15) NOT NULL,
 CARD_NUMBER NUMBER NOT NULL,
 VALID_DATE NUMBER NOT NULL,
 CARD_CVC NUMBER NOT NULL,
 PAY_METHOD NUMBER NOT NULL,
 INSTALLMENT_PLAN VARCHAR(255) NOT NULL,
 CARD_PASSWORD NUMBER NOT NULL
);
CREATE TABLE COST (
 DETAILED_NUMBER NUMBER NOT NULL,
 FEE_NUMBER NUMBER NOT NULL,
 ADDRESS VARCHAR2(20) NOT NULL,
 NAME VARCHAR2(10) NOT NULL,
 GENERAL_COST NUMBER NOT NULL,
 CLEAN_COST NUMBER NOT NULL,
 DISINFECT_COST NUMBER NOT NULL,
 ELEVATOR_COST NUMBER NOT NULL,
 GUARD_COST NUMBER NOT NULL,
 FIREENISURANCE_COST NUMBER NOT NULL,
 COMMISSION_COST NUMBER NOT NULL,
 ELECTRIC_COST NUMBER NOT NULL,
 ALLELECTRIC_COST NUMBER NOT NULL,
 TV_COST NUMBER NOT NULL,
 HEATING_COST NUMBER NOT NULL,
 WATER_COST NUMBER NOT NULL,
 ALLWATER_COST NUMBER NOT NULL,
 ETC VARCHAR2(20) NOT NULL,
 REPAIR_COST NUMBER NOT NULL
);
CREATE TABLE INQUIRY_BOARD (
	B_NUMBER	NUMBER	NOT NULL,
	B_TITLE	VARCHAR2(100)	NULL,
	B_PW	NUMBER	NULL,
	B_WRITER	VARCHAR2(15)	NULL,
	B_CONTENT	VARCHAR(2000)	NULL,
	B_DATE	DATE	NULL,
	LOCK_FLAG	CHAR(1)	NULL,
	B_RE_WRITER	VARCHAR2(15)	NULL,
	B_RE_DATE	DATE	NULL
);
CREATE TABLE NOTICE (
	NTC_NUMBER	NUMBER	NOT NULL,
	NTC_TITLE	VARCHAR2(100)	NULL,
	NTC_TXT	VARCHAR2(2000)	NULL,
	NTC_WRITER	VARCHAR2(100)	NULL,
	NTC_DATE	DATE	NULL,
	NTC_CHANGER	VARCHAR2(100)	NULL,
	NTC_CHANGE_DATE	DATE	NULL,
	NTC_RESERVATION_NOTICE	DATE	NULL
);
CREATE TABLE OPERATOR (
 OPER_ID VARCHAR2(20) NOT NULL,
 OPER_NAME VARCHAR2(20) NOT NULL,
 OPER_TEL NUMBER NOT NULL,
 OPER_PHONE NUMBER NOT NULL,
 OPER_EMAIL VARCHAR2(30) NOT NULL
);
CREATE TABLE PARKING_GUEST(
 ID VARCHAR2(15) NOT NULL,
 CAR_NUMBER_VISIT VARCHAR2(20) NOT NULL,
 PHONE_NUMBER_VISIT NUMBER NULL,
 PARKING_STATE_VISIT VARCHAR2(20) NOT NULL,
 PARKING_ENTRANCE_TIME_VISIT	 DATE NOT NULL,
 PARKING_EXIT_TIME_VISIT DATE NOT NULL,
 ADDRESS_VISIT	VARCHAR2(15) NOT NULL
);
CREATE TABLE PARKING_RESIDENT (
    ID VARCHAR2(15) NOT NULL,
    CAR_NUMBER_RESIDENT VARCHAR2(20) NOT NULL,
    PHONE_NUMBER_RESIDENT NUMBER NOT NULL,
    PARKING_STATE_RESIDENT VARCHAR2(20) NOT NULL,
    PARKING_EXIT_TIME_RESIDENT DATE NOT NULL
);
CREATE TABLE PAYMENT (
	FEE_NUMBER	NUMBER	NOT NULL,
	AMOUNT	NUMBER     NOT NULL,
	CURRENCY	VARCHAR2(10) NOT NULL,
	PG	VARCHAR2(30) NOT NULL,
	PAY_METHOD	VARCHAR2(30) NOT NULL,
	CARD_INSTALLMENT	NUMBER NOT NULL,
	NAME	VARCHAR2(20) NOT NULL,
	PHONE_NUMBER	VARCHAR(20) NOT NULL,
	EMAIL	VARCHAR2(50) NOT NULL
);
CREATE TABLE RECEIPT (
 FEE_NUMBER NUMBER NOT NULL,
 APPLY_NUMBER NUMBER NOT NULL,
 RE_NAME VARCHAR2(20) NOT NULL,
 PAY_METHOD NUMBER NOT NULL,
 PAY_AMOUNT NUMBER NOT NULL,
 PG_ID_NUMBER VARCHAR2(20) NOT NULL,
 PAY_DATE DATE NOT NULL,
 RECEIPT_URL VARCHAR2(100) NOT NULL,
 PAY_NAME VARCHAR2(20) NOT NULL,
 PAY_PHONE_NUMBER VARCHAR(20) NOT NULL,
 PAY_EMAIL VARCHAR2(20) NOT NULL,
 STATUS VARCHAR2(20) NOT NULL,
 V_NUMBER VARCHAR(20) NOT NULL
);
CREATE TABLE RESIDENT (
    ID VARCHAR2(15) NOT NULL,
    PASSWORD VARCHAR2(15) NOT NULL,
    EMAIL VARCHAR2(30) NOT NULL,
    PHONE_NUMBER_RESIDENT NUMBER NOT NULL,
    CAR_NUMBER_RESIDENT VARCHAR2(15) NOT NULL,
    ADDRESS VARCHAR2(15) NOT NULL,
    NAME VARCHAR2(15) NOT NULL
);
CREATE TABLE SMS(
 FEE_NUMBER NUMBER NOT NULL,
 PHONE_NUMBER NUMBER NOT NULL,
 FROM_NUMBER NUMBER NOT NULL,
 CONTENT VARCHAR2(100) NOT NULL
);

CREATE TABLE VBANK(
 PAYMENT_METHOD_ID VARCHAR2(20) NOT NULL,
 V_NAME VARCHAR2(20) NOT NULL,
 ADDRESS VARCHAR2(20) NOT NULL,
 V_DATE NUMBER NOT NULL,
 V_NUMBER VARCHAR2(20) NOT NULL,
 DEPOSIT_AMOUNT NUMBER NOT NULL
);