-
Notifications
You must be signed in to change notification settings - Fork 1
/
dbsql.sql
141 lines (103 loc) · 3.13 KB
/
dbsql.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
CREATE DATABASE DB2021Team03;
use DB2021Team03;
##User
CREATE TABLE DB2021_User(
ID int auto_increment not null,
nickname varchar(20) not null,
password varchar(20) not null,
name varchar(20) not null,
birth date,
phone varchar(11),
join_time timestamp not null,
privacy_fg char(1) not null,
admin_fg char(1) not null,
delete_fg char(1) not null,
delete_time timestamp,
primary key(ID)
);
CREATE INDEX Unick ON DB2021_User(nickname);
## Director
CREATE TABLE DB2021_Director(
ID int auto_increment not null,
name varchar(20) not null,
country varchar(20) not null,
birth date,
primary key(ID, name)
);
CREATE INDEX Dname ON DB2021_Director(name);
## Movie(director와 1:N)
CREATE TABLE DB2021_MOVIE(
ID int auto_increment not null,
title varchar(20) not null,
genre varchar(20) not null,
country varchar(20) not null,
running_time int,
opening_date date,
director varchar(20) not null,
plot varchar(3000),
rating float,
age int not null,
primary key(ID),
foreign key(director) references DB2021_Director(name) on delete cascade on update cascade
);
CREATE INDEX Mtitle ON DB2021_MOVIE(title);
## Director_Prize(1:Many)
CREATE TABLE DB2021_Director_Prize(
ID int auto_increment not null,
prize varchar(100) not null,
director varchar(20) not null,
movie varchar(20) not null,
primary key(ID),
foreign key(director) references DB2021_Director(name) on delete cascade on update cascade,
foreign key(movie) references DB2021_MOVIE(title) on delete cascade on update cascade
);
CREATE INDEX DPidx ON DB2021_Director_Prize(director);
## Actor
CREATE TABLE DB2021_Actor(
ID int auto_increment not null,
name varchar(20) not null,
country varchar(20) not null,
birth date,
primary key(ID, name)
);
CREATE INDEX Aname ON DB2021_Actor(name);
## Actor_Movie(Many to Many)
CREATE TABLE DB2021_Actor_Movie(
actor varchar(20) not null,
movie varchar(20) not null,
primary key(actor, movie),
foreign key(actor) references DB2021_Actor(name) on delete cascade on update cascade,
foreign key(movie) references DB2021_Movie(title) on delete cascade on update cascade
);
## Actor_Prize
CREATE TABLE DB2021_Actor_Prize(
ID int auto_increment not null,
prize varchar(100) not null,
actor varchar(20) not null,
movie varchar(20) not null,
primary key(ID),
foreign key(actor) references DB2021_Actor(name) on delete cascade on update cascade,
foreign key(movie) references DB2021_Movie(title) on delete cascade on update cascade
);
CREATE INDEX APidx ON DB2021_Actor_Prize(actor);
## Review
CREATE TABLE DB2021_Review(
ID int auto_increment not null,
movie varchar(20) not null,
nickname varchar(20) not null,
create_time timestamp not null,
rating float not null,
detail varchar(500) not null,
primary key(ID),
foreign key(movie) references DB2021_Movie(title) on delete cascade on update cascade,
foreign key(nickname) references DB2021_User(nickname) on delete cascade on update cascade
);
## 좋아요
CREATE TABLE DB2021_Likes(
ID int auto_increment,
movie varchar(20),
nickname varchar(20),
primary key(ID),
foreign key(movie) references DB2021_Movie(title) on delete cascade on update cascade,
foreign key(nickname) references DB2021_User(nickname) on delete cascade on update cascade
);