forked from begumyivli/Intro-to-Database
-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathcreateTables.sql
190 lines (172 loc) · 6.88 KB
/
createTables.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
CREATE TABLE Director(
name CHAR(20) not NULL,
password CHAR(20) not NULL,
surname CHAR(20) not NULL,
username CHAR(20),
nation CHAR(20) not NULL,
PRIMARY KEY (username),
);
/*In description it says that every director has nation and if a username
be deleted from user table it should be deleted from director table if it
is a director also*/
/* Audience(username: string)*/
CREATE TABLE Audience(
name CHAR(20) not NULL,
password CHAR(20) not NULL,
surname CHAR(20) not NULL,
username CHAR(20),
PRIMARY KEY (username));
/*if a username be deleted from user table it should be deleted from
audience table if it is an audience also*/
/* Platform(platform_name: string, platform_id: integer)*/
CREATE TABLE Platform(
platform_name CHAR(20) not NULL,
platform_id INTEGER,
PRIMARY KEY (platform_id),
UNIQUE (platform_name));
/* In description it says that every platform name is unique. */
/* Theatre(district: string, capacity: integer, theatre_id: integer, theatre_name: string)*/
CREATE TABLE Theatre(
district CHAR(25),
theatre_name CHAR(25) not NULL,
capacity INTEGER,
theatre_id INTEGER,
PRIMARY KEY (theatre_id));
/* there is no theatre without name, so we added not null constraint */
/* Movie_Session(date: date, time_slot: integer, session_id: integer)*/
CREATE TABLE Movie_Session(
date DATE not NULL,
time_slot INTEGER not NULL,
session_id INTEGER,
constraint CHK_slot CHECK ( time_slot>0 and time_slot<=4),
PRIMARY KEY (session_id));
/* there is no theatre without slot, so we added not null constraint,
and in description it says that time slot cannot be higher than 4 so
we added check constraint */
/* Database_Manager(password: string, manager_username: string)*/
CREATE TABLE Database_Manager(
password CHAR(20) not NULL,
manager_username CHAR(20),
PRIMARY KEY (manager_username));
/* there cannot be a database manager without password */
/* Genre(genre_name: string, genre_id: integer)*/
CREATE TABLE Genre(
genre_name CHAR(20) not NULL,
genre_id INTEGER,
PRIMARY KEY (genre_id),
UNIQUE (genre_name));
/* There cannot be a genre without name and
in description it says that every genre name is unique. */
/* Directed_Movie(movie_name: string, duration: integer, movie_id: integer, username:string)*/
CREATE TABLE Directed_Movie(
movie_id INTEGER,
movie_name CHAR(50) not NULL,
duration INTEGER,
username CHAR(20) not NULL,
avg_rating INTEGER,
PRIMARY KEY (movie_id),
FOREIGN KEY (username) REFERENCES Director(username)
ON DELETE CASCADE);
/* there cannot be a movie without name and without a director so these columns
have not null constraint and because of there cannot be a movie in database without
director, if director is deleted, we will delete the movie*/
/* Classify(movie_id: integer, genre_id: integer)*/
CREATE TABLE Classify(
movie_id INTEGER,
genre_id INTEGER,
PRIMARY KEY (movie_id, genre_id),
FOREIGN KEY (movie_id) REFERENCES Directed_Movie(movie_id)
ON DELETE CASCADE,
FOREIGN KEY (genre_id) REFERENCES Genre(genre_id)
ON DELETE CASCADE);
/* if movie or genre in that relationship is deleted, we delete the row
because it is redundant */
/* Next_To(pre_id: integer, suc_id: integer)*/
CREATE TABLE Next_To(
pre_id INTEGER,
suc_id INTEGER,
PRIMARY KEY (pre_id, suc_id),
FOREIGN KEY (pre_id) REFERENCES Directed_Movie(movie_id)
ON DELETE CASCADE,
FOREIGN KEY (suc_id) REFERENCES Directed_Movie(movie_id)
ON DELETE CASCADE);
/* if predecessor or successor in that relationship is deleted,
we delete the row because it is redundant, there would be no pre-suc
relationship anymore */
/* Located(session_id: integer, theatre_id: integer)*/
CREATE TABLE Located(
session_id INTEGER,
theatre_id INTEGER,
PRIMARY KEY (session_id, theatre_id),
FOREIGN KEY (session_id) REFERENCES Movie_Session(session_id)
ON DELETE CASCADE,
FOREIGN KEY (theatre_id) REFERENCES Theatre(theatre_id)
ON DELETE CASCADE);
/* if session or theatre in that relationship is deleted,
we delete the row because it is redundant, there would be no located
relationship anymore */
/* Play(session_id: integer, movie_id: integer)*/
CREATE TABLE Play(
session_id INTEGER,
movie_id INTEGER,
PRIMARY KEY (session_id, movie_id),
FOREIGN KEY (session_id) REFERENCES Movie_Session(session_id)
ON DELETE CASCADE,
FOREIGN KEY (movie_id) REFERENCES Directed_Movie(movie_id)
ON DELETE CASCADE);
/* if session or movie in that relationship is deleted,
we delete the row because it is redundant, there would be no play
relationship anymore */
/* Rate(username: string, movie_id: integer, rating:real)*/
CREATE TABLE Rate(
username CHAR(20),
movie_id INTEGER,
rating FLOAT not NULL,
PRIMARY KEY (username, movie_id),
FOREIGN KEY (username) REFERENCES Audience(username)
ON DELETE CASCADE,
FOREIGN KEY (movie_id) REFERENCES Directed_Movie(movie_id)
ON DELETE CASCADE);
/* if username or movie in that relationship is deleted, we delete
the row because it is redundant, there would be no rate relationship
anymore, and also the whole point is rating so that cannot be null*/
/* Buy(username: string, session_id: integer)*/
CREATE TABLE Buy(
username CHAR(20),
session_id INTEGER,
PRIMARY KEY (username, session_id),
FOREIGN KEY (username) REFERENCES Audience(username)
ON DELETE CASCADE,
FOREIGN KEY (session_id) REFERENCES Movie_Session(session_id)
ON DELETE CASCADE);
/* if session or username in that relationship is deleted,
we delete the row because it is redundant, there would be no buy
relationship anymore and also we use audience username because only
audiences buy ticket*/
/* Agreement(username: string, platform_id: integer)*/
CREATE TABLE Agreement(
username CHAR(20),
platform_id INTEGER,
PRIMARY KEY (username, platform_id),
FOREIGN KEY (username) REFERENCES Director(username)
ON DELETE CASCADE,
FOREIGN KEY (platform_id) REFERENCES Platform(platform_id)
);
/* if username or platform_id in that relationship is deleted,
we delete the row because it is redundant, there would be no play
relationship anymore and also we use director username because only
directors make an agreement*/
/* Subscribe(username: string, platform_id: integer)*/
CREATE TABLE Subscribe(
username CHAR(20),
platform_id INTEGER,
PRIMARY KEY (username, platform_id),
FOREIGN KEY (username) REFERENCES Audience(username)
ON DELETE CASCADE,
FOREIGN KEY (platform_id) REFERENCES Platform(platform_id)
ON DELETE CASCADE);
/* if platform or username in that relationship is deleted,
we delete the row because it is redundant, there would be no subscribe
relationship anymore and also we use audience username because only
audiences subscribe to platforms*/
/* Because of not null constraint is implicit for primary keys, we didnt write*/