-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql文.txt
161 lines (128 loc) · 8.16 KB
/
sql文.txt
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
DROP TABLE contribution_t;
DROP TABLE chat_t;
DROP TABLE follow_t;
DROP TABLE belong_department_t;
DROP TABLE groupmember_t;
DROP SEQUENCE user_seq;
DROP TABLE group_t;
DROP TABLE notification_t;
DROP SEQUENCE notification_seq;
DROP TABLE user_t;
DROP TABLE department_t;
DROP SEQUENCE department_seq;
CREATE TABLE department_t(
department_id NUMBER(2),
department_name VARCHAR2(30) UNIQUE NOT NULL,
CONSTRAINT department_id_pk PRIMARY KEY(department_id)
);
CREATE TABLE user_t (
user_id NUMBER(5),
login_id VARCHAR2(20) UNIQUE NOT NULL,
password VARCHAR2(20) NOT NULL,
is_admin NUMBER(1) NOT NULL CONSTRAINT check_is_admin CHECK (is_admin IN (0,1)),
username VARCHAR2(30) NOT NULL,
user_icon BLOB,
user_introduction VARCHAR2(500),
student_id VARCHAR2(20),
admission_year VARCHAR2(4),
department_id NUMBER(2),
CONSTRAINT user_id_pk PRIMARY KEY(user_id),
CONSTRAINT user_department_fk FOREIGN KEY (department_id) REFERENCES department_t(department_id)
);
CREATE SEQUENCE user_seq;
CREATE TABLE group_t(
group_id NUMBER(5),
group_name VARCHAR2(30) NOT NULL,
group_icon BLOB,
CONSTRAINT group_id_pk PRIMARY KEY(group_id)
);
CREATE TABLE groupmember_t(
group_id NUMBER(5),
user_id NUMBER(5),
CONSTRAINT groupmember_unique UNIQUE(group_id, user_id),
CONSTRAINT group_id_fk FOREIGN KEY (group_id) REFERENCES group_t(group_id),
CONSTRAINT group_user_id_fk FOREIGN KEY (user_id) REFERENCES user_t(user_id)
);
CREATE SEQUENCE department_seq;
CREATE TABLE follow_t(
follower_user_id NUMBER(5),
followed_user_id NUMBER(5),
CONSTRAINT follower_user_id_fk FOREIGN KEY (follower_user_id) REFERENCES user_t(user_id),
CONSTRAINT followed_user_id_fk FOREIGN KEY (followed_user_id) REFERENCES user_t(user_id)
);
CREATE TABLE notification_t(
notification_id NUMBER(5) UNIQUE NOT NULL,
notification_content VARCHAR2(4000) NOT NULL,
notification_date DATE DEFAULT SYSDATE NOT NULL
);
CREATE SEQUENCE notification_seq;
CREATE TABLE chat_t(
chat_user_id NUMBER(5),
chat_group_id NUMBER(5),
chat_content VARCHAR2(4000) NOT NULL,
chat_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT chat_user_id_fk FOREIGN KEY (chat_user_id) REFERENCES user_t(user_id),
CONSTRAINT chat_group_id_fk FOREIGN KEY (chat_group_id) REFERENCES group_t(group_id)
);
INSERT INTO department_t VALUES (department_seq.nextVal,'情報処理科');
INSERT INTO department_t VALUES (department_seq.nextVal,'インテリア科');
INSERT INTO department_t VALUES (department_seq.nextVal,'Web動画クリエーター科');
INSERT INTO department_t VALUES (department_seq.nextVal,'環境テクノロジー科');
INSERT INTO department_t VALUES (department_seq.nextVal,'建築監督科');
INSERT INTO department_t VALUES (department_seq.nextVal,'職員')
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Alex', 'Alex', 0, 'AL ABAYAJI ALEXANDRE', 1);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Idei', 'Idei', 0, '出井郁実',1);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Nagase', 'Nagase', 0, '長瀬史也',1);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Nakai', 'Nakai', 0, '中井祐喜',1);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Waka', 'Waka', 0, '若色巧',1);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Yokota', 'Yokota', 0, '横田涼馬',1);
/*
INSERT INTO group_t (group_id, group_name) VALUES (1, 'group1');
INSERT INTO group_t (group_id, group_name) VALUES (2, 'group2');
INSERT INTO groupmember_t VALUES (1, 1);
INSERT INTO groupmember_t VALUES (1, 4);
INSERT INTO groupmember_t VALUES (2, 1);
INSERT INTO groupmember_t VALUES (2, 2);
INSERT INTO groupmember_t VALUES (2, 3);
INSERT INTO groupmember_t VALUES (2, 5);
//ユーザー
//インテリア科
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Tanaka', 'Tanaka', 0, '田中 博貴 ',2);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Gouda', 'Gouda', 0, '剛田 仁志 ',2);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Kamiya', 'Kamiya', 0, '神谷 菜々美 ',2);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Kizima', 'Kizima', 0, '鬼嶋 宏樹',2);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Asagiri', 'Asagiri', 0, '朝桐 優那 ',2);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Yamada', 'Yamada', 0, '山田 将樹',2);
//Web動画クリエーター科
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Kawazaki', 'Kawazaki', 0, '川崎 孝',3);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Nohara', 'Nohara', 0, '野原 ひろし',3);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Shima', 'Shima', 0, '島 耕作',3);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Matumoto', 'Matumoto', 0, '松本 一樹',3);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Osaki', 'Osaki', 0, '大崎 たかし',3);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Imai', 'Imai', 0, '今井 梨沙',3);
//環境テクノロジー科
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Terashima', 'Terashima', 0, '寺島 恵子',4);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Hiraoka', 'Hiraoka', 0, '平岡 雪',4);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Kuriyama', 'Kuriyama', 0, '栗山 かずき',4);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Ishikawa', 'Ishikawa', 0, '石川 廣喜',4);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Takeuti', 'Takeuti', 0, '武内 学',4);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Fuziwara', 'Fuziwara', 0, '藤原 海斗',4);
//建築監督科
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Kanda', 'Kanda', 0, '神田 美優',5);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Nishitani', 'Nishitani', 0, '西谷 隆一',5);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Igarashi', 'Igarashi', 0, '五十嵐 裕斗',5);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Takahata', 'Takahata', 0, '高畑 真緒',5);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Azai', 'Azai', 0, '浅井 仁美',5);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Otake', 'Otake', 0, '大竹 孝史',5);
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Shiotani', 'Shiotani', 0, '塩谷 まさと',5);
//職員
INSERT INTO user_t (user_id, login_id, password, is_admin, username, department_id) VALUES (user_seq.nextVal, 'Ogawa', 'Ogawa', 1, '小川 事務長', 6);
//学科
情報処理科 1
インテリア科 2
Web動画クリエーター科 3
環境テクノロジー科 4
建築監督科 5
職員 6
*/
commit;