· 8 years ago · Jan 15, 2018, 07:48 PM
1/*CREATE DATABASE netflix*/
2/*Bij eerste gebruik kan het voorkomen dat het de database 'netflix' niet gevonden kan worden, haal dan de '/* */' weg hierboven*/
3USE netflix;
4
5DROP TABLE IF EXISTS views;
6DROP TABLE IF EXISTS profiles;
7DROP TABLE IF EXISTS subscribers;
8DROP TABLE IF EXISTS episodes;
9DROP TABLE IF EXISTS movies;
10DROP TABLE IF EXISTS programs;
11DROP TABLE IF EXISTS LANGUAGE;
12DROP TABLE IF EXISTS languages;
13
14CREATE TABLE subscribers
15(
16 id INT NOT NULL PRIMARY KEY,
17 name VARCHAR(100) NOT NULL,
18 city VARCHAR(100) NOT NULL,
19 address VARCHAR(100) NOT NULL
20)
21
22INSERT INTO subscribers(id, name, city, address) VALUES('1215426', 'Fam. van Raalte','Houten', 'Schopenhauerdijkje 5');
23INSERT INTO subscribers(id, name, city, address) VALUES('5602533', 'J. van Betlehem','Breda', 'Nietzschestraat 99');
24INSERT INTO subscribers(id, name, city, address) VALUES('5285824', 'Fam. van Raalte','Breda', 'Kantlaan 11');
25
26CREATE TABLE profiles
27(
28 id INT NOT NULL PRIMARY KEY,
29 name VARCHAR(100) NOT NULL,
30 birthday DATE NOT NULL,
31
32 CONSTRAINT profilesFK1
33 FOREIGN KEY (id)
34 REFERENCES subscribers(id)
35 ON DELETE CASCADE
36 ON UPDATE CASCADE
37
38)
39
40INSERT INTO profiles(id, name, birthday) VALUES('1215426', 'Frank', '1968-1-25')
41INSERT INTO profiles(id, name, birthday) VALUES('5602533', 'Petrus', '1999-6-26')
42INSERT INTO profiles(id, name, birthday) VALUES('5285824', 'Fritz', '1968-8-19')
43
44
45CREATE TABLE languages
46(
47 code VARCHAR(2) NOT NULL PRIMARY KEY,
48 name VARCHAR(32) NOT NULL
49)
50
51INSERT INTO languages(code, name) VALUES('en', 'Engels');
52INSERT INTO languages(code, name) VALUES('nl', 'Nederlands');
53INSERT INTO languages(code, name) VALUES('fr', 'Frans');
54INSERT INTO languages(code, name) VALUES('de', 'Duits');
55
56CREATE TABLE programs
57(
58 id INT NOT NULL PRIMARY KEY,
59 title VARCHAR(50) NOT NULL,
60 genre VARCHAR NOT NULL CHECK (genre IN('action', 'documentary', 'comedy', 'horror', 'thriller')),
61 LANGUAGE VARCHAR(2) NOT NULL,
62 ageminimum INT NOT NULL,
63 ismovie INT NOT NULL CHECK (ismovie IN(0, 1))
64 /* De lengte van een programma wordt in movies & episodes bijgehouden, en in het Java systeem berekend */
65)
66
67INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('1010', 'The Abominable Bride', 'action', 'en', '12', 1)
68INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('8002', 'Pulp Fiction', 'comedy', 'en', '16', 1)
69INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('8010', 'The Good, the Bad and the Ugly', 'thriller', 'en', '16', 1)
70INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('8014', 'Der Untergang', 'action', 'en', '6', 1)
71
72INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('1004', 'Sherlock', 'action', 'en', '16', 0)
73INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('2004', 'Breaking Bad', 'action', 'en', '16', 0)
74INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('2014', 'Breaking Bad', 'action', 'en', '16', 0)
75INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('3002', 'Fargo', 'action', 'en', '16', 0)
76INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('3103', 'Fargo', 'action', 'en', '16', 0)
77
78CREATE TABLE episodes
79(
80 program INT NOT NULL,
81 episodeid VARCHAR(10) NOT NULL,
82 title VARCHAR(50) NOT NULL,
83 LENGTH INT NOT NULL, /* length in minutes */
84
85
86 CONSTRAINT episodesFK
87 FOREIGN KEY (program)
88 REFERENCES programs(id),
89 CONSTRAINT episodesPK
90 PRIMARY KEY (program, episodeId)
91)
92
93INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('1004', 'S02E01', 'A Scandal in Belgravia', '88')
94INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('2004', 'S01E05', 'Gray Matter', '48')
95INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('2014', 'S02E08', 'Better Call Saul', '48')
96INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('3002', 'S01E02', 'The Rooster Prince', '68')
97INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('3103', 'S02E03', 'The Myth of Sisyphus', '68')
98
99CREATE TABLE movies
100(
101 program INT NOT NULL,
102 LENGTH INT NOT NULL,
103
104
105 CONSTRAINT moviesFK
106 FOREIGN KEY (program)
107 REFERENCES programs(id)
108 ON DELETE CASCADE
109 ON UPDATE CASCADE
110)
111
112INSERT INTO movies(program, LENGTH) VALUES('1010', '109')
113INSERT INTO movies(program, LENGTH) VALUES('8002', '154')
114INSERT INTO movies(program, LENGTH) VALUES('8010', '161')
115INSERT INTO movies(program, LENGTH) VALUES('8014', '178')
116
117
118CREATE TABLE views
119(
120 id INT NOT NULL PRIMARY KEY,
121 profile INT NOT NULL,
122 program INT NOT NULL,
123 percentage INT NOT NULL,
124
125 CONSTRAINT viewsFK1
126 FOREIGN KEY (profile)
127 REFERENCES profiles(id)
128 ON DELETE CASCADE
129 ON UPDATE CASCADE
130)
131
132INSERT INTO views(id, profile, program, percentage) VALUES('1215426', '1215426', '1001', '100')
133INSERT INTO views(id, profile, program, percentage) VALUES('5602533', '5602533', '3002', '74')
134INSERT INTO views(id, profile, program, percentage) VALUES('5285824', '5285824', '1010', '5')