-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathCREATION.sql
227 lines (189 loc) · 6.9 KB
/
CREATION.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
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
-- ----------------------------------------------------
-- ----------------------------------------------------
-- -- TABLES CREATION SCRIPT -- ASSIGNMENT SOLUTION ---
-- ----------------------------------------------------
-- ----------------------------------------------------
-- -- Course: File Structures and DataBases -----------
-- ----------------------------------------------------
-- -- (c) 2017 Dolores Cuadra & Javier Calle ----------
-- ------ Carlos III University of Madrid -------------
-- ----------------------------------------------------
-- ----------------------------------------------------
-- -- Part I: Destroy (in case) existent tables -------
-- ----------------------------------------------------
DROP TABLE MOVIES CASCADE CONSTRAINTS;
DROP TABLE GENRES_MOVIES CASCADE CONSTRAINTS;
DROP TABLE keywords_movies CASCADE CONSTRAINTS;
DROP TABLE PLAYERS CASCADE CONSTRAINTS;
DROP TABLE CASTS CASCADE CONSTRAINTS;
DROP TABLE SERIES CASCADE CONSTRAINTS;
DROP TABLE SEASONS CASCADE CONSTRAINTS;
DROP TABLE CLIENTS CASCADE CONSTRAINTS;
DROP TABLE PRODUCTS CASCADE CONSTRAINTS;
DROP TABLE CONTRACTS CASCADE CONSTRAINTS;
DROP TABLE TAPS_MOVIES CASCADE CONSTRAINTS;
DROP TABLE TAPS_SERIES CASCADE CONSTRAINTS;
DROP TABLE LIC_MOVIES CASCADE CONSTRAINTS;
DROP TABLE LIC_SERIES CASCADE CONSTRAINTS;
DROP TABLE INVOICES CASCADE CONSTRAINTS;
-- ----------------------------------------------------
-- -- Part II: Create all tables ----------------------
-- ----------------------------------------------------
CREATE TABLE MOVIES(
movie_title VARCHAR2(100),
title_year NUMBER(4),
country VARCHAR2(25),
color VARCHAR2(1),
duration NUMBER(3) NOT NULL,
gross NUMBER(10),
budget NUMBER(12),
director_name VARCHAR2(50) DEFAULT 'Anonymous',
filming_language VARCHAR2(20),
num_critic_for_reviews NUMBER(6),
director_facebook_likes NUMBER(6),
num_voted_users NUMBER(7),
num_user_for_reviews NUMBER(6),
cast_total_facebook_likes NUMBER(6),
facenumber_in_poster NUMBER(6),
movie_imdb_link VARCHAR2(60),
imdb_score NUMBER(2,1),
content_rating VARCHAR2(9),
aspect_ratio NUMBER(4,2) ,
movie_facebook_likes NUMBER(6),
CONSTRAINT MOVIES_PK PRIMARY KEY (movie_title),
CONSTRAINT MOVIES_CH CHECK (COLOR IN ('B','C', null))
)
CLUSTER movie_cluster(movie_title);
CREATE TABLE GENRES_MOVIES (
title VARCHAR2(100),
genre VARCHAR2(70),
CONSTRAINT PK_GENRES_MOVIES PRIMARY KEY (title,genre),
CONSTRAINT FK_GENRES_MOVIES FOREIGN KEY (title) REFERENCES MOVIES ON DELETE CASCADE
)
CLUSTER MOVIE_CLUSTER(TITLE);
CREATE TABLE keywords_movies (
title VARCHAR2(100),
keyword VARCHAR2(150),
CONSTRAINT PK_KEYWORDS_MOVIES PRIMARY KEY (title,keyword),
CONSTRAINT FK_KEYWORDS_MOVIES FOREIGN KEY (title) REFERENCES MOVIES ON DELETE CASCADE
)CLUSTER MOVIE_CLUSTER(TITLE);
CREATE TABLE PLAYERS (
actor_name VARCHAR2(50),
facebook_likes NUMBER(6,0),
CONSTRAINT ACTORS_PK PRIMARY KEY (actor_name));
CREATE TABLE CASTS (
actor VARCHAR2(50),
title VARCHAR2(100),
CONSTRAINT PK_CASTS PRIMARY KEY (actor, title),
CONSTRAINT FK1_CASTS FOREIGN KEY (actor) REFERENCES PLAYERS ON DELETE CASCADE,
CONSTRAINT FK2_CASTS FOREIGN KEY (title) REFERENCES MOVIES ON DELETE CASCADE)
CLUSTER MOVIE_CLUSTER(TITLE);
CREATE TABLE SERIES(
title VARCHAR2(100),
total_seasons NUMBER(3) NOT NULL,
CONSTRAINT PK_SERIES PRIMARY KEY (title)
);
CREATE TABLE SEASONS(
title VARCHAR2(100),
season NUMBER(3),
avgduration NUMBER(3) NOT NULL,
episodes NUMBER(3) NOT NULL,
CONSTRAINT PK_SEASONS PRIMARY KEY (title, season),
CONSTRAINT FK_SEASONS FOREIGN KEY (title) REFERENCES SERIES ON DELETE CASCADE
);
CREATE TABLE CLIENTS (
clientId VARCHAR2(15),
DNI VARCHAR2(9),
name VARCHAR2(100) NOT NULL,
surname VARCHAR2(100) NOT NULL,
sec_surname VARCHAR2(100),
eMail VARCHAR2(100) NOT NULL,
phoneN NUMBER(12),
birthdate DATE,
CONSTRAINT PK_CLIENTS PRIMARY KEY (clientId),
CONSTRAINT UK1_CLIENTS UNIQUE (DNI),
CONSTRAINT UK2_CLIENTS UNIQUE (eMail),
CONSTRAINT UK3_CLIENTS UNIQUE (phoneN),
CONSTRAINT CH_CLIENTS CHECK (eMail LIKE '%@%.%')
)
CLUSTER CLIENT_CLUSTER(CLIENTID);
CREATE TABLE products(
product_name VARCHAR2(25),
fee NUMBER(3) NOT NULL,
type VARCHAR2(1) NOT NULL,
tap_cost NUMBER(4,2) NOT NULL,
zapp NUMBER(2) DEFAULT 0 NOT NULL,
ppm NUMBER(4,2) DEFAULT 0 NOT NULL,
ppd NUMBER(4,2) DEFAULT 0 NOT NULL,
promo NUMBER(3) DEFAULT 0 NOT NULL,
CONSTRAINT PK_products PRIMARY KEY (product_name),
CONSTRAINT CK_products1 CHECK (type IN ('C','V')),
CONSTRAINT CK_products2 CHECK (PROMO <= 100)
);
CREATE TABLE contracts(
contractId VARCHAR2(10),
clientId VARCHAR2(15),
startdate DATE NOT NULL,
enddate DATE,
contract_type VARCHAR2(50),
address VARCHAR2(100) NOT NULL,
town VARCHAR2(100) NOT NULL,
ZIPcode VARCHAR2(8) NOT NULL,
country VARCHAR2(100) NOT NULL,
CONSTRAINT PK_contracts PRIMARY KEY (contractId),
CONSTRAINT FK_contracts1 FOREIGN KEY (clientId) REFERENCES clientS ON DELETE SET NULL,
CONSTRAINT FK_contracts2 FOREIGN KEY (contract_type) REFERENCES products,
CONSTRAINT CK_contracts CHECK (startdate<=enddate)
)
CLUSTER CLIENT_CLUSTER(CLIENTID);
CREATE TABLE taps_movies(
contractId VARCHAR2(10),
view_datetime DATE,
pct NUMBER(3) DEFAULT 0 NOT NULL,
title VARCHAR2(100) NOT NULL,
CONSTRAINT PK_tapsM PRIMARY KEY (contractId,title,view_datetime),
CONSTRAINT FK_tapsM1 FOREIGN KEY (contractId) REFERENCES contracts,
CONSTRAINT FK_tapsM2 FOREIGN KEY (title) REFERENCES movies
)
CLUSTER CONTRACT_CLUSTER(CONTRACTID);
CREATE TABLE taps_series(
contractId VARCHAR2(10),
view_datetime DATE,
pct NUMBER(3) DEFAULT 0 NOT NULL,
title VARCHAR2(100) NOT NULL,
season NUMBER(3) NOT NULL,
episode NUMBER(3) NOT NULL,
CONSTRAINT PK_tapsS PRIMARY KEY (contractId,title,season,episode,view_datetime),
CONSTRAINT FK_tapsS1 FOREIGN KEY (contractId) REFERENCES contracts,
CONSTRAINT FK_tapsS2 FOREIGN KEY (title,season) REFERENCES seasons
)
CLUSTER CONTRACT_CLUSTER(CONTRACTID);
CREATE TABLE lic_movies(
client VARCHAR2(15),
datetime DATE,
title VARCHAR2(100) NOT NULL,
CONSTRAINT PK_licsM PRIMARY KEY (client,title),
CONSTRAINT FK_licsM1 FOREIGN KEY (title) REFERENCES movies,
CONSTRAINT FK_licsM2 FOREIGN KEY (client) REFERENCES clients ON DELETE CASCADE
)
CLUSTER CLIENT_CLUSTER(CLIENT);
CREATE TABLE lic_series(
client VARCHAR2(15),
datetime DATE,
title VARCHAR2(100) NOT NULL,
season NUMBER(3) NOT NULL,
episode NUMBER(3) NOT NULL,
CONSTRAINT PK_licsS PRIMARY KEY (client,title,season,episode),
CONSTRAINT FK_licsS1 FOREIGN KEY (title,season) REFERENCES seasons,
CONSTRAINT FK_licsS2 FOREIGN KEY (client) REFERENCES clients ON DELETE CASCADE
)
CLUSTER CLIENT_CLUSTER(CLIENT);
CREATE TABLE invoices(
clientId VARCHAR2(15),
month VARCHAR2(2) ,
year VARCHAR2(4) ,
amount NUMBER(8,2) NOT NULL,
CONSTRAINT PK_invcs PRIMARY KEY (clientId,month,year),
CONSTRAINT FK_invcs FOREIGN KEY (clientId) REFERENCES clients
)
CLUSTER CLIENT_CLUSTER(CLIENTID);