-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathaccessTelevisionTSQL-database.sql
437 lines (380 loc) · 24.1 KB
/
accessTelevisionTSQL-database.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
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
--TO USE: Simply hit the execute query button. A database will be created and populated with some data! Note
--this must be completed before executing queries.
USE master
GO
IF NOT EXISTS (
SELECT name
FROM sys.databases
WHERE name = 'Access_TV'
)
CREATE DATABASE Access_TV
ELSE
BEGIN
PRINT 'Database already exists!'
END
GO
USE Access_TV
-- Formats can later be implemented using triggers
CREATE TABLE STATION (
STN_ID CHAR(4), --STN_ID FORMAT: XX99
STN_PHONE CHAR(12) NOT NULL, --PHONE NUMBER FORMAT: 999-999-9999
STN_STREET VARCHAR(128) NOT NULL,
STN_CITY VARCHAR(128) NOT NULL,
STN_PROVINCE CHAR(2) NOT NULL
CHECK(STN_PROVINCE IN ('ON', 'QC', 'NS', 'NB', 'MB', 'BC', 'PE', 'SK', 'AB', 'NL', 'NT', 'YT', 'NU')),
STN_POSTAL_CODE CHAR(7) NOT NULL, --POSTAL CODE FORMAT: X9X-9X9
PRIMARY KEY (STN_ID)
);
CREATE TABLE ACCESS_PRODUCER (
AP_ID CHAR(6),
AP_FNAME VARCHAR(128) NOT NULL,
AP_LNAME VARCHAR(128) NOT NULL,
AP_PHONE CHAR(12) NOT NULL, --PHONE FORMAT
AP_EMAIL VARCHAR(320) NOT NULL, --WORK EMAIL FORMAT: [email protected]
AP_STREET VARCHAR(128) NOT NULL,
AP_CITY VARCHAR(128) NOT NULL,
AP_PROVINCE CHAR(2) NOT NULL
CHECK(AP_PROVINCE IN ('ON', 'QC', 'NS', 'NB', 'MB', 'BC', 'PE', 'SK', 'AB', 'NL', 'NT', 'YT', 'NU')),
AP_POSTAL_CODE CHAR(7), --POSTAL CODE FORMAT
STN_ID CHAR(4) NOT NULL,
PRIMARY KEY (AP_ID),
FOREIGN KEY (STN_ID) REFERENCES STATION,
);
CREATE TABLE SHOW (
SHOW_ID CHAR(5),
SHOW_NAME VARCHAR(255) NOT NULL,
SHOW_DESCRIPTION VARCHAR(512),
PRIMARY KEY (SHOW_ID),
);
CREATE TABLE PRODUCTION (
AP_ID CHAR(6),
SHOW_ID CHAR(5),
PRIMARY KEY (AP_ID, SHOW_ID),
FOREIGN KEY (AP_ID) REFERENCES ACCESS_PRODUCER,
FOREIGN KEY (SHOW_ID) REFERENCES SHOW
);
CREATE TABLE SEASON (
SSN_NUM CHAR(2) DEFAULT 01
CHECK(ISNUMERIC(SSN_NUM) = 1), --Checks if value is a number
SHOW_ID CHAR(5),
PRIMARY KEY (SSN_NUM, SHOW_ID),
FOREIGN KEY (SHOW_ID) REFERENCES SHOW ON DELETE CASCADE
);
CREATE TABLE EPISODE (
EP_NUM CHAR(4) DEFAULT 0001
CHECK(ISNUMERIC(EP_NUM) = 1),
SHOW_ID CHAR(5),
EP_DURATION TIME NOT NULL,
EP_DESCRIPTION VARCHAR(512),
EP_CLASS CHAR(1) NOT NULL
CHECK(EP_CLASS >= 'A' AND EP_CLASS <= 'D'), --Checks if value is a character between A or D
EP_IS_HD BIT NOT NULL DEFAULT 1
PRIMARY KEY (EP_NUM, SHOW_ID),
FOREIGN KEY (SHOW_ID) REFERENCES SHOW
);
CREATE TABLE CHANNEL (
CHA_NUM CHAR(3)
CHECK(ISNUMERIC(CHA_NUM) = 1),
STN_ID CHAR(4),
CHA_IS_HD BIT NOT NULL DEFAULT 1
PRIMARY KEY (CHA_NUM, STN_ID),
FOREIGN KEY (STN_ID) REFERENCES STATION
);
CREATE TABLE GUIDE_LISTING (
GL_ID CHAR(6),
STN_ID CHAR(4) NOT NULL,
CHA_NUM CHAR(3) NOT NULL,
SHOW_ID CHAR(5) NOT NULL,
EP_NUM CHAR(4) NOT NULL,
PRIMARY KEY (GL_ID),
FOREIGN KEY (CHA_NUM, STN_ID) REFERENCES CHANNEL (CHA_NUM, STN_ID),
FOREIGN KEY (EP_NUM, SHOW_ID) REFERENCES EPISODE (EP_NUM, SHOW_ID),
CONSTRAINT GUIDE_LISTING_UI1 UNIQUE (STN_ID, CHA_NUM, SHOW_ID, EP_NUM)
);
CREATE TABLE AIR_TIME (
GL_ID CHAR(6) NOT NULL,
AIR_DAY DATE NOT NULL,
AIR_START_TIME TIME NOT NULL,
AIR_END_TIME TIME NOT NULL, --Would like to make this derived from AIR_START_TIME + EPISODE.EPISODE_DURATION
AIR_DURATION
AS DATEDIFF(MINUTE, AIR_START_TIME, AIR_END_TIME),
AIR_COUNT SMALLINT NOT NULL,
PRIMARY KEY (GL_ID, AIR_DAY, AIR_START_TIME),
FOREIGN KEY (GL_ID) REFERENCES GUIDE_LISTING ON DELETE CASCADE
);
CREATE TABLE SHOOT (
SHOOT_ID CHAR(6),
SHOOT_LOCATION VARCHAR(128) NOT NULL,
SHOOT_DAY DATE NOT NULL,
SHOOT_START_TIME TIME NOT NULL,
SHOOT_END_TIME TIME NOT NULL,
SHOOT_DURATION
AS DATEDIFF(MINUTE, SHOOT_START_TIME, SHOOT_END_TIME),
PRIMARY KEY (SHOOT_ID),
);
CREATE TABLE SHOOT_LIST (
EP_NUM CHAR(4),
SHOW_ID CHAR(5),
SHOOT_ID CHAR(6),
PRIMARY KEY (SHOOT_ID, EP_NUM, SHOW_ID),
FOREIGN KEY (SHOOT_ID) REFERENCES SHOOT,
FOREIGN KEY (EP_NUM, SHOW_ID) REFERENCES EPISODE
);
CREATE TABLE TASK (
TASK_ID CHAR(3),
TASK_NAME VARCHAR(128) NOT NULL,
TASK_DESCRIPTION VARCHAR(512)
PRIMARY KEY (TASK_ID)
);
CREATE TABLE OCCUPATION (
OCC_ID CHAR(3),
OCC_NAME VARCHAR(128) NOT NULL,
OCC_DESCRIPTION VARCHAR(128)
PRIMARY KEY (OCC_ID)
);
CREATE TABLE SHOW_PERSONNEL (
SP_ID CHAR(6),
SP_FNAME VARCHAR(128) NOT NULL,
SP_LNAME VARCHAR(128) NOT NULL,
SP_PHONE CHAR(12) NOT NULL, --PHONE NUMBER FORMAT
SP_EMAIL VARCHAR(320) NOT NULL, --EMAIL FORMAT: [email protected]
SP_STREET VARCHAR(128) NOT NULL,
SP_CITY VARCHAR(128) NOT NULL,
SP_PROVINCE CHAR(2) NOT NULL
CHECK(SP_PROVINCE IN ('ON', 'QC', 'NS', 'NB', 'MB', 'BC', 'PE', 'SK', 'AB', 'NL', 'NT', 'YT', 'NU')),
SP_POSTAL_CODE CHAR(7), --POSTAL CODE FORMAT
SP_TOTAL_HOURS NUMERIC(7, 2), --Would like to make this derived: adds up all CREW_MEMBER.CM_MINUTES_WORKED for each SP.
SP_IS_VOLUNTEER BIT NOT NULL,
SP_DOB DATE
CHECK(SP_DOB < GETDATE()),
SP_AGE
AS DATEDIFF(YEAR, SP_DOB, GETDATE()),
SP_JOIN_DATE DATE DEFAULT GETDATE() NOT NULL,
SP_DAYS_ACTIVE
AS DATEDIFF(DAY, SP_JOIN_DATE, GETDATE()),
OCC_ID CHAR(3) NOT NULL,
PRIMARY KEY (SP_ID),
FOREIGN KEY (OCC_ID) REFERENCES OCCUPATION,
);
CREATE TABLE CREW_MEMBER (
SP_ID CHAR(6),
SHOOT_ID CHAR(6),
TASK_ID CHAR(3),
CM_START_TIME TIME NOT NULL,
CM_END_TIME TIME NOT NULL,
CM_MINUTES_WORKED
AS DATEDIFF(MINUTE, CM_START_TIME, CM_END_TIME),
CM_IS_VOLUNTEER BIT NOT NULL,
PRIMARY KEY (SP_ID, SHOOT_ID, TASK_ID),
FOREIGN KEY (SP_ID) REFERENCES SHOW_PERSONNEL,
FOREIGN KEY (SHOOT_ID) REFERENCES SHOOT,
FOREIGN KEY (TASK_ID) REFERENCES TASK,
);
/* LOAD TABLES WITH DATA */
/* STATION */
INSERT INTO STATION VALUES('BC01', '778-423-1353', '108 Bob St', 'Burnaby', 'BC', 'B6X-3F4');
INSERT INTO STATION VALUES('BC02', '778-654-9034', '806 Gregory Ave', 'Victoria', 'BC', 'B5H-9J5');
INSERT INTO STATION VALUES('BC03', '778-345-2657', '243 Full Cres', 'Kelowna', 'BC', 'B2H-0L0');
INSERT INTO STATION VALUES('AB01', '506-930-9557', '3423 Terrence Ave', 'Edmonton', 'AB', 'A5G-8IL');
INSERT INTO STATION VALUES('AB02', '486-382-5867', '459 Phillip St', 'Calgary', 'AB', 'A2C-B2B');
INSERT INTO STATION VALUES('SK01', '206-572-4819', '10A Derp St', 'Saskatoon', 'SK', 'S0J-2Z0');
INSERT INTO STATION VALUES('SK02', '456-123-3560', '2345 Heya Ave', 'Regina', 'SK', 'V5B-7N6');
INSERT INTO STATION VALUES('MB01', '403-483-2350', '502 Noelle Ave', 'Brandon', 'MB', 'M0J-7K8');
INSERT INTO STATION VALUES('MB02', '584-932-7925', '6540 Young Cres', 'Thompson', 'MB', 'M2H-7A9');
INSERT INTO STATION VALUES('MB03', '104-583-7493', '2340 Georgeton Abe', 'Winnipeg', 'MB', 'M0K-9L0');
/* ACCESS_PRODUCER */
INSERT INTO ACCESS_PRODUCER VALUES('137891', 'Jim', 'Johnson', '435-854-8435', '[email protected]', '104 Brokedale', 'Burnaby', 'BC', 'B5N-2N3', 'BC01');
INSERT INTO ACCESS_PRODUCER VALUES('486928', 'Joe', 'Smith', '385-385-1094', '[email protected]', '342 Hey St', 'New Westminster', 'BC', 'B8N-7J9', 'BC01');
INSERT INTO ACCESS_PRODUCER VALUES('184657', 'Franklin', 'Fjordson', '459-392-2754', '[email protected]', '910 Halifax St', 'Surrey', 'BC', 'B3J-0X9', 'BC01');
INSERT INTO ACCESS_PRODUCER VALUES('902756', 'Timothy', 'Turner', '375-394-2749', '[email protected]', '234 Jones Ave', 'Surrey', 'BC', 'B6K-2K0', 'BC01');
INSERT INTO ACCESS_PRODUCER VALUES('184769', 'Susan', 'McCaughlin', '847-384-3756', '[email protected]', '4532 Hello Ave', 'Saskatoon', 'SK', 'S7N-0K9', 'SK01');
INSERT INTO ACCESS_PRODUCER VALUES('396759', 'Emily', 'Jones', '932-394-3393', '[email protected]', '343 Bonjourno Ave', 'Prince Albert', 'SK', 'S3G-7J6', 'SK01');
INSERT INTO ACCESS_PRODUCER VALUES('839562', 'Eric', 'Steen', '374-348-9835', '[email protected]', '451 Bibi Cres', 'Winnipeg', 'MB', 'M2K-9L0', 'MB03');
INSERT INTO ACCESS_PRODUCER VALUES('397363', 'Jackie', 'Krip', '934-535-3452', '[email protected]', '2345 McDonald St', 'Flin Flon', 'MB', 'M5H-2F8', 'MB02');
INSERT INTO ACCESS_PRODUCER VALUES('395769', 'Simon', 'Hiyat', '263-343-6483', '[email protected]', '5345 Didney St', 'The Pas', 'MB', 'M8K-3L4', 'MB01');
INSERT INTO ACCESS_PRODUCER VALUES('542452', 'Chelsie', 'Hoda', '453-453-9434', '[email protected]', '353 Why St', 'Kelowna', 'BC', 'B8P-0O9', 'BC03');
/* SHOW */
INSERT INTO SHOW VALUES('PUNCH', 'Punch! TV', 'The latest news on all comic related goods!');
INSERT INTO SHOW VALUES('MJTOD', 'Moose Jaw Today', 'The best news from the best city, Moose Jaw!');
INSERT INTO SHOW VALUES('POTAT', 'The Potato Network', 'The show about everyone''s fav root vegetable, the succulent potato.');
INSERT INTO SHOW VALUES('TOAST', 'Toast NOW!!!', 'Get excited for toast!!!! RIGHT NOW!!!!');
INSERT INTO SHOW (SHOW_ID, SHOW_NAME) VALUES('NOFUN', 'No Fun Here');
INSERT INTO SHOW VALUES('TIMSH', 'Tim''s Show', 'The show about your favorite Tim, Tim.');
INSERT INTO SHOW VALUES('CATTV', 'Cat TV', '13 hours straight of nothing but cats!');
INSERT INTO SHOW VALUES('KIKTV', 'Kick! TV', 'Learn how to kick some butt here!');
INSERT INTO SHOW VALUES('LOLTV', 'LOL TV', 'Not really funny');
INSERT INTO SHOW VALUES('JERKY', 'Jerky Show', 'All about the snack that better be in your backpack!');
/* PRODUCTION */
INSERT INTO PRODUCTION VALUES('137891', 'PUNCH');
INSERT INTO PRODUCTION VALUES('137891', 'LOLTV');
INSERT INTO PRODUCTION VALUES('184657', 'PUNCH');
INSERT INTO PRODUCTION VALUES('184657', 'KIKTV');
INSERT INTO PRODUCTION VALUES('542452', 'TIMSH');
INSERT INTO PRODUCTION VALUES('396759', 'TOAST');
INSERT INTO PRODUCTION VALUES('396759', 'CATTV');
INSERT INTO PRODUCTION VALUES('542452', 'JERKY');
INSERT INTO PRODUCTION VALUES('542452', 'POTAT');
INSERT INTO PRODUCTION VALUES('542452', 'MJTOD');
/* SEASON */
INSERT INTO SEASON VALUES('01', 'PUNCH');
INSERT INTO SEASON VALUES('02', 'PUNCH');
INSERT INTO SEASON VALUES('03', 'PUNCH');
INSERT INTO SEASON VALUES('01', 'LOLTV');
INSERT INTO SEASON VALUES('02', 'LOLTV');
INSERT INTO SEASON VALUES('01', 'JERKY');
INSERT INTO SEASON VALUES('02', 'JERKY');
INSERT INTO SEASON VALUES('01', 'POTAT');
INSERT INTO SEASON VALUES('01', 'MJTOD');
INSERT INTO SEASON VALUES('01', 'CATTV');
/* EPISODE */
INSERT INTO EPISODE VALUES('0001', 'PUNCH', '00:30:00.00', 'We learn all about Spoderman!', 'A', 1);
INSERT INTO EPISODE VALUES('0002', 'PUNCH', '00:30:00.00', 'We talk to Dave about his collection of Batmans!', 'A', 1);
INSERT INTO EPISODE VALUES('0001', 'CATTV', '13:00:00.00', 'Holy smokes! Cats', 'B', 0);
INSERT INTO EPISODE VALUES('0001', 'POTAT', '01:00:00.00', 'The first episode! Yay!', 'C', 0);
INSERT INTO EPISODE VALUES('0002', 'POTAT', '01:30:00.00', 'Get ready for more potato!', 'D', 1);
INSERT INTO EPISODE VALUES('0003', 'POTAT', '02:30:00.00', 'That''s right we got more potatoes coming your way!', 'A', 0);
INSERT INTO EPISODE VALUES('0001', 'MJTOD', '10:00:00.00', 'Get introduced to the best place on Earth!', 'C', 1);
INSERT INTO EPISODE VALUES('0002', 'MJTOD', '00:30:00.00', 'I want to leave it''s actually not great here.', 'A', 1);
INSERT INTO EPISODE VALUES('0001', 'JERKY', '00:30:00.00', 'We learn how to dry meats!', 'D', 0);
INSERT INTO EPISODE VALUES('0002', 'JERKY', '00:30:00.00', 'Jerry teaches us about the best wood smoke.', 'A', 1);
/* CHANNEL */
INSERT INTO CHANNEL VALUES('101', 'BC01', 1);
INSERT INTO CHANNEL VALUES('102', 'BC01', 0);
INSERT INTO CHANNEL VALUES('200', 'BC02', 1);
INSERT INTO CHANNEL VALUES('202', 'BC02', 0);
INSERT INTO CHANNEL VALUES('340', 'BC03', 1);
INSERT INTO CHANNEL VALUES('324', 'BC03', 0);
INSERT INTO CHANNEL VALUES('564', 'SK01', 1);
INSERT INTO CHANNEL VALUES('242', 'SK01', 0);
INSERT INTO CHANNEL VALUES('459', 'AB01', 1);
INSERT INTO CHANNEL VALUES('534', 'AB02', 0);
/* GUIDE_LISTING */
INSERT INTO GUIDE_LISTING VALUES('483748', 'AB01', '459', 'PUNCH', '0001');
INSERT INTO GUIDE_LISTING VALUES('573849', 'BC01', '101', 'PUNCH', '0001');
INSERT INTO GUIDE_LISTING VALUES('682749', 'BC02', '200', 'MJTOD', '0001');
INSERT INTO GUIDE_LISTING VALUES('397564', 'SK01', '242', 'JERKY', '0002');
INSERT INTO GUIDE_LISTING VALUES('695848', 'BC02', '202', 'CATTV', '0001');
INSERT INTO GUIDE_LISTING VALUES('857403', 'AB01', '459', 'JERKY', '0002');
INSERT INTO GUIDE_LISTING VALUES('838204', 'AB02', '534', 'MJTOD', '0002');
INSERT INTO GUIDE_LISTING VALUES('303458', 'BC03', '340', 'POTAT', '0003');
INSERT INTO GUIDE_LISTING VALUES('392405', 'BC03', '324', 'PUNCH', '0002');
INSERT INTO GUIDE_LISTING VALUES('222342', 'BC02', '200', 'POTAT', '0002');
INSERT INTO GUIDE_LISTING VALUES('471415', 'AB01', '459', 'MJTOD', '0002');
/* AIR_TIME */
INSERT INTO AIR_TIME (GL_ID, AIR_DAY, AIR_START_TIME, AIR_END_TIME, AIR_COUNT) VALUES('471415', '2019-03-25', '10:00:00', '12:00:00', 100);
INSERT INTO AIR_TIME (GL_ID, AIR_DAY, AIR_START_TIME, AIR_END_TIME, AIR_COUNT) VALUES('682749', '2019-03-26', '12:00:00', '14:00:00', 200);
INSERT INTO AIR_TIME (GL_ID, AIR_DAY, AIR_START_TIME, AIR_END_TIME, AIR_COUNT) VALUES('682749', '2019-03-26', '14:00:00', '16:00:00', 200);
INSERT INTO AIR_TIME (GL_ID, AIR_DAY, AIR_START_TIME, AIR_END_TIME, AIR_COUNT) VALUES('392405', '2019-07-01', '20:00:00', '21:00:00', 100);
INSERT INTO AIR_TIME (GL_ID, AIR_DAY, AIR_START_TIME, AIR_END_TIME, AIR_COUNT) VALUES('392405', '2019-09-23', '18:00:00', '19:00:00', 300);
INSERT INTO AIR_TIME (GL_ID, AIR_DAY, AIR_START_TIME, AIR_END_TIME, AIR_COUNT) VALUES('303458', '2019-10-01', '17:00:00', '17:30:00', 300);
INSERT INTO AIR_TIME (GL_ID, AIR_DAY, AIR_START_TIME, AIR_END_TIME, AIR_COUNT) VALUES('397564', '2019-12-09', '17:00:00', '17:30:00', 100);
INSERT INTO AIR_TIME (GL_ID, AIR_DAY, AIR_START_TIME, AIR_END_TIME, AIR_COUNT) VALUES('397564', '2019-05-02', '08:00:00', '09:00:00', 200);
INSERT INTO AIR_TIME (GL_ID, AIR_DAY, AIR_START_TIME, AIR_END_TIME, AIR_COUNT) VALUES('471415', '2019-06-20', '06:00:00', '07:00:00', 200);
INSERT INTO AIR_TIME (GL_ID, AIR_DAY, AIR_START_TIME, AIR_END_TIME, AIR_COUNT) VALUES('695848', '2019-09-10', '05:30:00', '06:00:00', 300);
/* SHOOT */
INSERT INTO SHOOT (SHOOT_ID, SHOOT_LOCATION, SHOOT_DAY, SHOOT_START_TIME, SHOOT_END_TIME) VALUES('001232', 'Comic Book Store', '2019-08-31', '08:00:00', '09:00:00');
INSERT INTO SHOOT (SHOOT_ID, SHOOT_LOCATION, SHOOT_DAY, SHOOT_START_TIME, SHOOT_END_TIME) VALUES('834829', 'Studio', '2019-10-30', '22:00:00', '23:00:00');
INSERT INTO SHOOT (SHOOT_ID, SHOOT_LOCATION, SHOOT_DAY, SHOOT_START_TIME, SHOOT_END_TIME) VALUES('934354', 'Jim''s Hockey Rink', '2019-11-29', '08:00:00', '20:00:00');
INSERT INTO SHOOT (SHOOT_ID, SHOOT_LOCATION, SHOOT_DAY, SHOOT_START_TIME, SHOOT_END_TIME) VALUES('001923', 'Studio', '2019-06-20', '07:00:00', '15:00:00');
INSERT INTO SHOOT (SHOOT_ID, SHOOT_LOCATION, SHOOT_DAY, SHOOT_START_TIME, SHOOT_END_TIME) VALUES('000903', 'Studio', '2019-04-07', '06:00:00', '10:00:00');
INSERT INTO SHOOT (SHOOT_ID, SHOOT_LOCATION, SHOOT_DAY, SHOOT_START_TIME, SHOOT_END_TIME) VALUES('002034', 'Lakefront', '2018-03-09', '11:00:00', '20:00:00');
INSERT INTO SHOOT (SHOOT_ID, SHOOT_LOCATION, SHOOT_DAY, SHOOT_START_TIME, SHOOT_END_TIME) VALUES('034234', 'Potato Shack', '2019-02-23', '12:00:00', '18:00:00');
INSERT INTO SHOOT (SHOOT_ID, SHOOT_LOCATION, SHOOT_DAY, SHOOT_START_TIME, SHOOT_END_TIME) VALUES('000232', 'Radio Shack', '2019-01-01', '16:00:00', '20:00:00');
INSERT INTO SHOOT (SHOOT_ID, SHOOT_LOCATION, SHOOT_DAY, SHOOT_START_TIME, SHOOT_END_TIME) VALUES('243352', 'Seaside', '2019-02-20', '16:00:00', '21:00:00');
INSERT INTO SHOOT (SHOOT_ID, SHOOT_LOCATION, SHOOT_DAY, SHOOT_START_TIME, SHOOT_END_TIME) VALUES('988234', 'Timmy''s House', '2019-03-13', '17:00:00', '19:00:00');
INSERT INTO SHOOT (SHOOT_ID, SHOOT_LOCATION, SHOOT_DAY, SHOOT_START_TIME, SHOOT_END_TIME) VALUES('895432', 'Studio', '2019-10-11', '19:00:00', '22:00:00');
/* SHOOT_LIST */
INSERT INTO SHOOT_LIST VALUES ('0002', 'JERKY', '895432');
INSERT INTO SHOOT_LIST VALUES ('0001', 'PUNCH', '934354');
INSERT INTO SHOOT_LIST VALUES ('0001', 'PUNCH', '002034');
INSERT INTO SHOOT_LIST VALUES ('0002', 'MJTOD', '000903');
INSERT INTO SHOOT_LIST VALUES ('0003', 'POTAT', '000903');
INSERT INTO SHOOT_LIST VALUES ('0001', 'JERKY', '988234');
INSERT INTO SHOOT_LIST VALUES ('0002', 'PUNCH', '834829');
INSERT INTO SHOOT_LIST VALUES ('0002', 'PUNCH', '000232');
INSERT INTO SHOOT_LIST VALUES ('0001', 'JERKY', '834829');
INSERT INTO SHOOT_LIST VALUES ('0003', 'POTAT', '034234');
/* TASK */
INSERT INTO TASK VALUES('001', 'CAMERA_FIXED', 'Shoot the action, but with the comfort of a tripod mounted camera.');
INSERT INTO TASK VALUES('002', 'CAMERA_HANDHELD', 'Get right in there and shoot the action!');
INSERT INTO TASK VALUES('003', 'DIRECTOR', 'Like bossing people round? Gee do we have a job for you!');
INSERT INTO TASK VALUES('004', 'SOUNDBOARD', 'Make people sound good');
INSERT INTO TASK VALUES('005', 'REPLAY', 'This allows us to go back and time and analyze mistakes');
INSERT INTO TASK VALUES('006', 'SOUND_TECH', 'Make people sound good and get blamed when it sounds bad');
INSERT INTO TASK VALUES('007', 'ENGINEER', 'The one who gets called when something goes horribly wrong');
INSERT INTO TASK VALUES('008', 'GRAPHICS', 'Insert graphics');
INSERT INTO TASK VALUES('009', 'EDITOR', 'Fix things when mistakes happen!');
INSERT INTO TASK VALUES('010', 'CRAWL', 'Insert information on the bottom of the screen');
/* OCCUPATION */
INSERT INTO OCCUPATION VALUES('001', 'Farmer', 'Grows tasty food.');
INSERT INTO OCCUPATION VALUES('002', 'Student', 'Studies cool things');
INSERT INTO OCCUPATION VALUES('003', 'Doctor', 'Heals humans');
INSERT INTO OCCUPATION VALUES('004', 'Mechanic', 'Fixes cars');
INSERT INTO OCCUPATION VALUES('005', 'Lawyer', 'Works with law');
INSERT INTO OCCUPATION VALUES('006', 'Baker', 'Bakes yummy treats');
INSERT INTO OCCUPATION VALUES('007', 'Carpenter', 'Constructs things');
INSERT INTO OCCUPATION VALUES('008', 'Programmer', 'Programs things');
INSERT INTO OCCUPATION VALUES('009', 'Cashier', 'Works with money and customers');
INSERT INTO OCCUPATION VALUES('010', 'Plumber', 'Its a me');
/* SHOW_PERSONNEL */
INSERT INTO SHOW_PERSONNEL(SP_ID, SP_FNAME, SP_LNAME, SP_PHONE, SP_EMAIL, SP_STREET, SP_CITY,
SP_PROVINCE, SP_POSTAL_CODE, SP_TOTAL_HOURS, SP_IS_VOLUNTEER, SP_DOB, SP_JOIN_DATE, OCC_ID)
VALUES('475839', 'John', 'Fergus', '309-234-3425', '[email protected]', '324 Dongle St',
'Burnaby', 'BC', 'S4G-3D4', 100, 1, '1970-03-25', '1990-10-16', '003');
INSERT INTO SHOW_PERSONNEL(SP_ID, SP_FNAME, SP_LNAME, SP_PHONE, SP_EMAIL, SP_STREET, SP_CITY,
SP_PROVINCE, SP_POSTAL_CODE, SP_TOTAL_HOURS, SP_IS_VOLUNTEER, SP_DOB, SP_JOIN_DATE, OCC_ID)
VALUES('351345', 'Billy', 'McInnon', '443-264-2345', '[email protected]', '3345 Yo Ave',
'Burnaby', 'BC', 'S9K-8K8', 42, 1, '1992-09-13', '2000-10-01', '004');
INSERT INTO SHOW_PERSONNEL(SP_ID, SP_FNAME, SP_LNAME, SP_PHONE, SP_EMAIL, SP_STREET, SP_CITY,
SP_PROVINCE, SP_POSTAL_CODE, SP_TOTAL_HOURS, SP_IS_VOLUNTEER, SP_DOB, SP_JOIN_DATE, OCC_ID)
VALUES('234622', 'Willy', 'Jamson', '234-624-3784', '[email protected]', '423 Eagle Cres',
'Wakaw', 'SK', 'V8S-9J3', 313, 1, '1990-10-23', '2016-11-23', '005');
INSERT INTO SHOW_PERSONNEL(SP_ID, SP_FNAME, SP_LNAME, SP_PHONE, SP_EMAIL, SP_STREET, SP_CITY,
SP_PROVINCE, SP_POSTAL_CODE, SP_TOTAL_HOURS, SP_IS_VOLUNTEER, SP_DOB, SP_JOIN_DATE, OCC_ID)
VALUES('032452', 'Greg', 'Tuhl', '464-426-2467', '[email protected]', '0983 Hollywood St',
'Weirdale', 'SK', 'X6J-0K5', 1, 0, '2000-01-09', '2010-06-10', '001');
INSERT INTO SHOW_PERSONNEL(SP_ID, SP_FNAME, SP_LNAME, SP_PHONE, SP_EMAIL, SP_STREET, SP_CITY,
SP_PROVINCE, SP_POSTAL_CODE, SP_TOTAL_HOURS, SP_IS_VOLUNTEER, SP_DOB, SP_JOIN_DATE, OCC_ID)
VALUES('129543', 'Steve', 'Bixby', '295-524-5643', '[email protected]', '432 Street St',
'Wakaw', 'SK', 'D7H-0L9', 123, 0, '1998-07-16', '2013-06-01', '001');
INSERT INTO SHOW_PERSONNEL(SP_ID, SP_FNAME, SP_LNAME, SP_PHONE, SP_EMAIL, SP_STREET, SP_CITY,
SP_PROVINCE, SP_POSTAL_CODE, SP_TOTAL_HOURS, SP_IS_VOLUNTEER, SP_DOB, SP_JOIN_DATE, OCC_ID)
VALUES('523426', 'George', 'Fredrick', '309-359-5245', '[email protected]', '524 Homer St',
'Cat City', 'AB', 'Q6F-4K5', 359, 0, '1945-12-05', '2010-10-05', '001');
INSERT INTO SHOW_PERSONNEL(SP_ID, SP_FNAME, SP_LNAME, SP_PHONE, SP_EMAIL, SP_STREET, SP_CITY,
SP_PROVINCE, SP_POSTAL_CODE, SP_TOTAL_HOURS, SP_IS_VOLUNTEER, SP_DOB, SP_JOIN_DATE, OCC_ID)
VALUES('672435', 'Tom', 'Ollie', '993-545-2435', '[email protected]', '1094 Town Ave',
'Victoria', 'BC', 'C9D-9L3', 34, 1, '1997-10-10', '2016-02-01', '007');
INSERT INTO SHOW_PERSONNEL(SP_ID, SP_FNAME, SP_LNAME, SP_PHONE, SP_EMAIL, SP_STREET, SP_CITY,
SP_PROVINCE, SP_POSTAL_CODE, SP_TOTAL_HOURS, SP_IS_VOLUNTEER, SP_DOB, SP_JOIN_DATE, OCC_ID)
VALUES('745342', 'Jesse', 'Pinks', '985-353-3535', '[email protected]', '935 Fun Ave',
'Kelowna', 'BC', 'F7O-9K4', 23, 0, '1967-08-28', '2013-03-08', '001');
INSERT INTO SHOW_PERSONNEL(SP_ID, SP_FNAME, SP_LNAME, SP_PHONE, SP_EMAIL, SP_STREET, SP_CITY,
SP_PROVINCE, SP_POSTAL_CODE, SP_TOTAL_HOURS, SP_IS_VOLUNTEER, SP_DOB, SP_JOIN_DATE, OCC_ID)
VALUES('834523', 'Nathan', 'Jono', '837-353-2357', '[email protected]', '0384 Type St',
'Typeton', 'AB', 'Z6X-C6V', 34, 1, '1980-02-06', '2013-04-05', '009');
INSERT INTO SHOW_PERSONNEL(SP_ID, SP_FNAME, SP_LNAME, SP_PHONE, SP_EMAIL, SP_STREET, SP_CITY,
SP_PROVINCE, SP_POSTAL_CODE, SP_TOTAL_HOURS, SP_IS_VOLUNTEER, SP_DOB, SP_JOIN_DATE, OCC_ID)
VALUES('904352', 'Mark', 'Teya', '934-458-3235', '[email protected]', '413 Yesss Ave',
'Dunwall', 'AB', 'L9H-4O8', 211, 0, '1995-06-10', '2015-10-11', '001');
INSERT INTO SHOW_PERSONNEL(SP_ID, SP_FNAME, SP_LNAME, SP_PHONE, SP_EMAIL, SP_STREET, SP_CITY,
SP_PROVINCE, SP_POSTAL_CODE, SP_TOTAL_HOURS, SP_IS_VOLUNTEER, SP_DOB, SP_JOIN_DATE, OCC_ID)
VALUES('005679', 'Freya', 'Cyr', '634-235-9245', '[email protected]', '826 Nooo St',
'Frankfurt', 'Sk', 'B0N-0B0', 313, 1, '1992-07-11', '2000-10-11', '002');
/* CREW_MEMBER */ --CREW MEMBERS FOR THE FOLLOWING SHOOTS:
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('475839', '001923', '010', '12:00:00', '14:00:00', 1); --N
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('745342', '001923', '007', '08:00:00', '10:00:00', 1); --N
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('672435', '001923', '003', '20:00:00', '22:30:00', 1); --N
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('129543', '000232', '009', '13:00:00', '14:00:00', 1); --PUNCH
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('523426', '000232', '003', '06:00:00', '08:00:00', 1); --PUNCH
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('523426', '000903', '007', '13:30:00', '14:00:00', 1); --MJTOD, POTAT
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('834523', '934354', '001', '10:00:00', '14:30:00', 1); --PUNCH
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('834523', '002034', '004', '10:00:00', '14:30:00', 1); --PUNCH
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('351345', '002034', '004', '10:00:00', '12:00:00', 1); --PUNCH
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('005679', '000903', '007', '13:30:00', '14:00:00', 0); --MJTOD, POTAT
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('904352', '002034', '004', '16:00:00', '20:30:00', 0); --PUNCH
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('834523', '834829', '002', '08:00:00', '16:30:00', 0); --PUNCH, JERKY
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('523426', '988234', '002', '06:00:00', '10:00:00', 0); --JERKY
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('351345', '988234', '001', '06:00:00', '10:00:00', 0); --JERKY
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('351345', '834829', '001', '06:00:00', '10:00:00', 0); --PUNCH, JERKY
INSERT INTO CREW_MEMBER (SP_ID, SHOOT_ID, TASK_ID, CM_START_TIME, CM_END_TIME, CM_IS_VOLUNTEER) VALUES ('351345', '895432', '010', '08:00:00', '20:00:00', 0); --JERKY