This repository has been archived by the owner on Dec 27, 2023. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
creOSMovie.sql
190 lines (158 loc) · 6.44 KB
/
creOSMovie.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
/*==============================================================*/
/* DBMS name: MySQL 5.0 */
/* Created on: 2023/3/17 22:53:36 */
/*==============================================================*/
drop table if exists Company;
drop table if exists Country;
drop table if exists Genre;
drop table if exists Keyword;
drop table if exists Movie;
drop table if exists Movie_Company;
drop table if exists Movie_Country;
drop table if exists Movie_Genre;
drop table if exists Movie_Keyword;
drop table if exists User;
drop table if exists Vote;
/*==============================================================*/
/* Table: Company */
/*==============================================================*/
create table Company
(
id numeric(8,0) not null,
name varchar(200),
deleted numeric(8,0),
primary key (id)
);
/*==============================================================*/
/* Table: Country */
/*==============================================================*/
create table Country
(
id numeric(8,0) not null,
name varchar(200),
deleted numeric(8,0),
primary key (id)
);
/*==============================================================*/
/* Table: Genre */
/*==============================================================*/
create table Genre
(
id numeric(8,0) not null,
name varchar(200) not null,
deleted numeric(8,0),
primary key (id)
);
/*==============================================================*/
/* Table: Keyword */
/*==============================================================*/
create table Keyword
(
id numeric(8,0) not null,
name varchar(0),
deleted numeric(8,0),
primary key (id)
);
/*==============================================================*/
/* Table: Movie */
/*==============================================================*/
create table Movie
(
id numeric(8,0) not null,
adult numeric(8,0),
budget numeric(8,0),
homepage varchar(200),
imgb_id numeric(8,0),
original_language varchar(500),
original_title varchar(500),
overview varchar(500),
popularity numeric(8,0),
poster_path varchar(500),
release_date varchar(500),
revenue numeric(8,0),
runtime numeric(8,0),
status numeric(8,0),
tagline varchar(500),
title varchar(500),
vote_average numeric(8,0),
vote_count numeric(8,0),
allCrew varchar(500),
deleted numeric(8,0),
primary key (id)
);
/*==============================================================*/
/* Table: Movie_Company */
/*==============================================================*/
create table Movie_Company
(
id numeric(8,0),
Com_id numeric(8,0),
deleted numeric(8,0)
);
/*==============================================================*/
/* Table: Movie_Country */
/*==============================================================*/
create table Movie_Country
(
id numeric(8,0),
Cou_id numeric(8,0),
deleted numeric(8,0)
);
/*==============================================================*/
/* Table: Movie_Genre */
/*==============================================================*/
create table Movie_Genre
(
id numeric(8,0),
Gen_id numeric(8,0),
deleted numeric(8,0)
);
/*==============================================================*/
/* Table: Movie_Keyword */
/*==============================================================*/
create table Movie_Keyword
(
id numeric(8,0),
Key_id numeric(8,0),
deleted numeric(8,0)
);
/*==============================================================*/
/* Table: User */
/*==============================================================*/
create table User
(
id int not null auto_increment,
username varchar(20),
telephone varchar(20),
password varchar(35),
deleted numeric(8,0),
primary key (id)
);
/*==============================================================*/
/* Table: Vote */
/*==============================================================*/
create table Vote
(
user_id numeric(8,0) not null,
movie_id numeric(8,0) not null,
rating numeric(8,0),
timestamp numeric(8,0),
deleted numeric(8,0),
primary key (user_id, movie_id)
);
alter table Movie_Company add constraint FK_Movie_Company1 foreign key (id)
references Movie (id) on delete restrict on update restrict;
alter table Movie_Company add constraint FK_Movie_Company2 foreign key (Com_id)
references Company (id) on delete restrict on update restrict;
alter table Movie_Country add constraint FK_Movie_Country1 foreign key (id)
references Movie (id) on delete restrict on update restrict;
alter table Movie_Country add constraint FK_Movie_Country2 foreign key (Cou_id)
references Country (id) on delete restrict on update restrict;
alter table Movie_Genre add constraint FK_Movie_Genre1 foreign key (id)
references Movie (id) on delete restrict on update restrict;
alter table Movie_Genre add constraint FK_Reference_4 foreign key (Gen_id)
references Genre (id) on delete restrict on update restrict;
alter table Movie_Keyword add constraint FK_Movie_Keyword1 foreign key (id)
references Movie (id) on delete restrict on update restrict;
alter table Movie_Keyword add constraint FK_Movie_Keyword2 foreign key (Key_id)
references Keyword (id) on delete restrict on update restrict;